項目4使用數據庫

——圖書借閱數據庫任務1數據操縱任務1數據操縱4.1.1【實訓4–1】數據插入4.1.2【實訓4–2】數據更新4.1.3【實訓4–3】數據刪除4.1.4數據操縱與數據約束任務2理解數據完整性約束任務3簡單數據查詢任務4復雜數據查詢4.1.1【實訓4–1】數據插入根據Jitor校驗器的要求，在dbForge上完成“【實訓4–1】數據插入”省略列名列表，指定主鍵值省略列名列表，主鍵值為null，自動增量列出所有列，主鍵值為null，自動增量Insertinto表名values(1,值1,值2…);Insertinto

表名values(null,值1,值2…);Insert

into(主鍵,列1,列2…)表名values(null,值1,值2…);Insertintolib_uservalues(1,'2249739484','管理員','admin','123456',2,'M',);Insertintolib_uservalues(null,'2249733829','杰克','jack','123456',1,'M',);Insertintolib_user(id_lib_user,col_rfid,col_name,col_account,col_password,col_type,col_sex,col_mobile)values(null,'2249731839','艾米','amy','123456',1,'F',);【實訓4-1】4.1.1【實訓4–1】數據插入（續）列出所有列，不含自動增量的主鍵僅列出所有非空列，不含自動增量的主鍵下述3類列可以省略自增量的列應該省略，其值由程序根據規則自動填入。或用用null作為它的值允許為空的列，省略時，其值為空。有默認約束的列，省略時，其值為默認值Insert

into(列1,列2…)表名values(值1,值2…);Insert

into(列1,…)表名values(值1,…);Insertintolib_user(col_rfid,col_name,col_account,col_password,col_type,col_sex,col_mobile)values('2249738325','張三','zhangs','123456',0,'M',);Insertintolib_user(col_rfid,col_name,col_account,col_password)values('2249738303','李四','lisi','123456');4.1.2【實訓4–2】數據更新根據Jitor校驗器的要求，在dbForge上完成“【實訓4–2】數據更新”更新指定行的一列數據更新指定行的多列數據Update表名set列名=值whereid=主鍵;Update表名set列名1=值1,列名2=值2…

whereid=主鍵;Updatelib_usersetcol_name='王五'whereid_lib_user=5;Updatelib_usersetcol_account='wangwu',col_password='abcde'whereid_lib_user=5;【實訓4-2】4.1.2【實訓4–2】數據更新（續）更新多行數據更新所有行的數據這時沒有where子句，將更新表中所有行的數據，因此操作時需要特別謹慎。Update表名set列名1=值1,列名2=值2…

where條件;Update表名set列名1=值1,列名2=值2…;Updatelib_usersetcol_password=col_accountwherecol_type=0;Updatelib_usersetcol_password='abc123';4.1.3【實訓4–3】數據刪除根據Jitor校驗器的要求，在dbForge上完成“【實訓4–3】數據刪除”刪除指定的一行刪除部分行刪除所有行——Delete語句刪除所有行——Truncate語句Deletefrom表名whereid=主鍵;Deletefrom表名where條件;Deletefrom表名;Truncate表名;Deletefromlib_userwhereid_lib_user=2;Deletefromlib_lendingwherecol_returned_date<'2021-12-30';Deletefromlib_lending;Truncate表名;【實訓4-3】4.1.4數據操縱與數據約束任務2理解數據完整性約束任務1數據操縱任務2理解數據完整性約束4.2.1實體完整性約束（主鍵約束）4.2.2參照完整性約束（外鍵約束）4.2.3其他完整性約束任務3簡單數據查詢任務4復雜數據查詢4.2.1實體完整性約束（主鍵約束）主鍵應當是對用戶沒有意義的，在理論上，只要是具有唯一性的列都可以作為主鍵，例如“身份證號”“學號”等屬性，但實際上通行的做法是采用無業務含義的主鍵主鍵應該是單屬性的，以便提高查詢和連接操作的效率。在理論上，允許多屬性的組合作為主鍵，但實際上通行的做法是采用單屬性的主鍵主鍵的值應當由計算機自動生成，手動輸入主鍵的值既不能保證它的唯一性，也極大地增加大地增加了輸入工作的難度永遠也不要更新主鍵，主鍵的作用只是標識一行，沒有其他的用途，所以也就沒有理由，更沒有必要去更新它。如果更新主鍵，那么還要更新相應的外鍵，這樣容易導致錯誤4.2.2參照完整性約束（外鍵約束）外鍵的值只能取被參照的表的主鍵的值外鍵的值允許為空外鍵的值允許為空，表示主從表之間的聯系不是非常緊密。例如學生的籍貫外鍵，在輸入學生的入學數據時，可以暫時不輸入，留待日后補錄外鍵的值非空外鍵的值非空，表示主從表之間的聯系非常緊密。例如學生的班級，在輸入學生的入學數據時是必須輸入的，因為一個學生不可能在入學時是沒有班級的外鍵的值不允許重復如果外鍵的值不允許重復，表示主從表是一對一的聯系。就是在一對多的聯系中，將“多”的一方限制為不允許重復，從而成為一對一的聯系多個外鍵一張表可能與多張表有聯系，這時每個外鍵表示與一張表的聯系。有多少個外鍵，就表示與多少張表有聯系一張表也可以有多個外鍵參照同一張表，這時每個外鍵都有不同的含義，例如借閱表有3個外鍵，都是參照用戶表的，它們分別表示借閱人、借出經手人和歸還經手人4.2.3其他完整性約束1．非空約束非空約束是很容易理解的一種約束，即指定該列的值是否允許取空值。需要注意的是，取空值和值為0或值為空字符串是不同的2．唯一性約束具有唯一性約束的屬性不允許出現重復的值。唯一性約束可以是單屬性的，也可以是多屬性的屬性組主鍵默認具有唯一性約束。唯一性約束與主鍵的區別如下唯一性約束根據業務需求，不允許出現空值或允許出現一次空值，主鍵則不允許出現空值一張表允許有多個唯一性約束，但只能有一個主鍵約束3．默認約束默認約束用于當插入行時，如果沒有為該列提供值，該列的值將被賦給默認約束指定的值。更新數據時，默認約束不起作用任務3簡單數據查詢任務1數據操縱任務2理解數據完整性約束任務3簡單數據查詢4.3.1【實訓4–4】單表查詢4.3.2【實訓4–5】理解查詢條件4.3.3【實訓4–6】排序和分頁4.3.4【實訓4–7】聯合查詢任務4復雜數據查詢4.3.1【實訓4–4】單表查詢根據Jitor校驗器的要求，在dbForge上完成“【實訓4–4】單表查詢”1.查詢所有數據Select*from表名;Select*fromlib_user;微課：4-1單表查詢【實訓4-4】4.3.1【實訓4–4】單表查詢（續）選擇列1)選擇列（無標題）2)列的別名（列標題）Select列名1AS別名1,列名2AS別名2…from表名;Selectcol_nameas姓名,col_accountas賬號,col_mobileas手機fromlib_user;Select列名1,列名2…from表名;Selectcol_name,col_account,col_mobilefromlib_user;4.3.1【實訓4–4】單表查詢（續）選擇列（續）3)消除重復數據Selectdistinct

列名1,列名2…from表名;Selectcol_sexfromlib_user;Selectdistinctcol_sexfromlib_user;4.3.1【實訓4–4】單表查詢（續）3.選擇行（下一小節詳細講解）4.計算列1)常量Select*from表名

where條件;Select常量,列名1,列名2…from表名;Select'圖書',col_titlefromlib_book;Select*fromlib_userwherecol_sex='F';4.3.1【實訓4–4】單表查詢（續）2)表達式3)If表達式Select表達式,列名1AS別名1,列名2AS別名2…from表名;Selectif(條件,值1,值2),列名1,列名2…from表名;Selectconcat(col_author,'著：',col_title)fromlib_book;Selectcol_nameas姓名,col_sex,if(col_sex='M','男','女')性別fromlib_user;4.3.2【實訓4–5】理解查詢條件根據Jitor校驗器的要求，在dbForge上完成“【實訓4–5】理解查詢條件”Select*from表名

where條件;【實訓4-5】1．關系表達式1．關系表達式關系運算符可以連接列名和常量（或表達式），從而形成關系表達式，用于查詢條件。等于、大于、小于、大于等于、小于等于、不等于：=、>、<、>=、<=、<>可以比較數字、字符串、日期時間例如如查詢用戶表中所有男性的姓名、電話和性別的代碼如下。查詢價格小于50元的圖書的代碼如下。Selectcol_name,col_mobile,col_sexfromlib_userwherecol_sex='M';Select*fromlib_bookwherecol_price<50

;2．范圍查詢2．范圍查詢用于查詢表達式的值是否在（不在）一個連續的范圍在…范圍之間、不在…范圍之間：between…and…、notbetween…and…可用于數字、字符串、日期時間例如查詢價格是40～50元的圖書（包括40和50）的代碼如下查詢在2020/04/08與2020-04-12之間借出的圖書的代碼如下，日期要用單引號引起來，年、月、日的分隔符可以用斜線，也可以用減號Select*fromlib_bookwherecol_pricebetween40and50;Select*fromlib_lendingwherecol_lending_datebetween'2020/04/08'and'2020-04-12';3．集合查詢3．集合查詢用于查詢表達式的值是否在（不在）一個不連續的集合中。在…集合之內、不在…集合之內：in(…)、notin(…)可用于數字、字符串、日期時間例如查詢圖書ID是1或3的圖書副本的代碼如下，由于不是連續的數字，所以應該使用集合查詢，而不是用范圍查詢Select*fromlib_copywhereid_lib_bookin(1,3);4．模糊查詢4．模糊查詢模糊查詢利用通配符來達到不精確匹配的查詢要求類似于、不類似于：like、notlike，并且配合通配符使用，通配符有兩種，如表4.4所示。僅用于字符串，不用于數字和日期時間例如查詢書名中，以“SQLServer”開始的圖書（只有一本）的代碼如下。查詢書名中，包含“SQLServer”的圖書（共有兩本）的代碼如下。Selectcol_author,col_titlefromlib_bookwherecol_titlelike'sqlserver%';Selectcol_author,col_titlefromlib_bookwherecol_titlelike'%sqlserver%';5．邏輯表達式5．邏輯表達式需要使用多個查詢條件時，可以使用and、or等將查詢條件連接起來，形成邏輯表達式。并且、或者、不：and、or、not僅用于連接其他查詢條件，一般不用直接于數字、字符串和日期例如例如查詢用戶表中姓“李”（條件一）的女性（條件二）的代碼如下。例如查詢用戶表中姓“李”（條件一）或者女性（條件二）的代碼如下。Selectcol_nameas姓名,col_sexas性別fromlib_userwherecol_namelike'李%'andcol_sex='F';Selectcol_nameas姓名,col_sexas性別fromlib_userwherecol_namelike'李%'or

col_sex='F';6．空值判斷6．空值判斷判斷值是否為空為空、不為空：isnull、isnotnull，不能用等于號“=”，用英語的語法isnull或者isnotnull可用于任何數字類型例如查詢用戶表中“手機”值為空的數據，代碼如下。查詢用戶表中“手機”值不為空的數據，代碼如下。Select*fromlib_userwherecol_mobileisnull;Select*fromlib_userwherecol_mobileisNOTnull;4.3.3【實訓4–6】排序和分頁根據Jitor校驗器的要求，在dbForge上完成“【實訓4–6】排序和分頁”1．升序排序將數據從小到大進行排序，用asc表示，這是默認的排序方式，因此可以省略asc。例如對用戶表以“賬號”列為升序排序的代碼如下。2．降序排序降序排序是從大到小進行排序，用desc表示。例如對相同的數據根據“賬號”列進行降序排序的代碼如下Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_accountasc;Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_accountdesc

;【實訓4-6】升序和降序的結果升序：從小到大降序：從大到小4.3.3【實訓4–6】排序和分頁（續）3．多個排序列也可以指定多個排序列，這時先以第一排序列進行排序，如果第一排序列的排序相同，再按第二排序列進行排序，以此類推。下述代碼是對用戶表中的“性別”列和“賬號”列進行排序，即先按“性別”列進行排序，如果“性別”列相同，再按“賬號”列進行排序。下述代碼將“賬號”列改為降序，其余不變Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_sex,col_account;Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_sex,col_accountdesc;多個排序列的結果先按第一排序進行排序第一排序相同的，再按第二排序進行排序如果第一排序沒有相同的，第二排序是不起作用的如果子句中的兩個列對調，成為orderbycol_account,col_sex，那么第二個列col_sex是沒有作用的，因為每行的賬號都不同，按第一列col_account排序就已經把所有行排好序了4.3.3【實訓4–6】排序和分頁（續）4．分頁查詢如果表中的數據太多，例如達到100萬行，這時顯示所有行將是不現實的，可以指定只顯示其中的部分行，使用limit關鍵字來實現。例如下述代碼是只顯示前3行limit關鍵字通常用于分頁，例如百度的查詢結果是分頁顯示的。分頁的語法格式如下假設tab_abc表有100行以上的數據，以每頁50行的要求，查詢第3頁的數據，代碼如下。其中，100=（頁號-1）*每頁行數=（3-1）*50。Select*fromtab_abclimit100,50;Select*fromlib_userlimit3;Select列名列表from表名limit(頁號-1)*每頁行數,每頁行數;4.3.4【實訓4–7】聯合查詢根據Jitor校驗器的要求，在dbForge上完成“【實訓4–7】聯合查詢”注意以下幾點兩個查詢的列數相同，如果不相同，則會出錯兩個查詢對應列的含義相同，如果不相同，則會造成誤解列名以第一個查詢的列名為準，第二個查詢的列名不顯示（被忽略）兩個查詢有各自的查詢條件等子句，只有排序子句是針對整個聯合的，并且在最后排序列名使用第一個查詢的列名，在上述例子中，如果按“性別”列排序時應用col_sex，而不能用sex。Selectid_lib_user,col_name,col_sex,col_mobilefromlib_userunionSelectid,name,sex,mobilefromlib_staff;【實訓4-7】聯合查詢的結果左圖：兩條查詢語句單獨執行，結果是獨立的，為便于比較，將其上下放置右圖：兩條查詢語句改為聯合查詢，變成一條語句，結果就是單獨查詢時的結果的上下聯合任務4復雜數據查詢任務1數據操縱任務2理解數據完整性約束任務3簡單數據查詢任務4復雜數據查詢4.4.1【實訓4–8】內連接查詢4.4.2【實訓4–9】外連接查詢4.4.3【實訓4–10】自連接查詢4.4.4【實訓4–11】統計與分組統計4.4.5【實訓4–12】綜合練習4.4.1【實訓4–8】內連接查詢根據Jitor校驗器的要求，在dbForge上完成“【實訓4–8】內連接查詢”1．兩張表的內連接（聯系人數據庫）Selecttab_contact.id_contactas人員id,tab_contact.col_nameas姓名,tab_contact_type.col_nameas聯系人類型,tab_contact_type.id_contact_type類型idfromtab_contact

innerjointab_contact_type

on

tab_contact.id_contact_type=tab_contact_type.id_contact_type;from

人員表

innerjoin類型表

on

人員表的外鍵=類型表的主鍵可以省略關鍵字inner微課：4-2內連接【實訓4-8】內連接的查詢結果內連接在關系數據庫查詢操作中是一項最重要的技能內連接是外鍵參照（引用）主表的主鍵的概念在查詢中的體現內連接是數據庫設計中，拆分表之后，再合并兩張表的數據的關鍵技術沒有內連接查詢，就沒有關系數據庫技術參考下圖中的數據，深入理解上面的的概念4.4.1【實訓4–8】內連接查詢（續）2．三張表的內連接（聯系人數據庫）先連接兩張表，再與第三張連接Selecttab_contact.col_nameas姓名,

tab_contact_info.col_infoas聯系方式,tab_contact_info.col_noteas說明,tab_contact_type.col_nameas聯系人類型fromtab_contact_infoinnerjointab_contactontab_contact_info.id_contact=tab_contact.id_contactinnerjointab_contact_typeontab_contact.id_contact_type=tab_contact_type.id_contact_type;三張表的內連接兩張表連接之后，再連接到第三張表4.4.1【實訓4–8】內連接查詢（續二）3．多張表的內連接（圖書借閱數據庫）Select列名列表from表名1innerjoin表名2on表名1.外鍵1=表名2.主鍵2innerjoin…on…innerjoin表名non表名n.外鍵n=表名m.主鍵m;Selectlib_book.col_title書名,lib_book.col_author作者,lib_user.col_name借閱者姓名,lib_copy.col_bar_code副本條碼,lib_lending.col_lending_date借書時間,lib_lending.col_returned_date還書時間fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_book;4.4.1【實訓4–8】內連接查詢（續三）5張表的內連接的例子注意：編寫連接查詢語句時，一般是從下向上連接，要一級一級地連接不論是從下向上連接，還是從上向下連接，都不能跳過某一級直接連到更高或更低的的一級。Selectlib_publisher.col_name出版社,lib_book.col_title書名,lib_book.col_author作者,lib_user.col_name借閱者姓名,lib_copy.col_bar_code副本條碼,lib_lending.col_lending_date借書時間,lib_lending.col_returned_date還書時間fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_bookinnerjoinlib_publisheronlib_book.id_lib_publisher=lib_publisher.id_lib_publisher;4.4.1【實訓4–8】內連接查詢（續四）4．連接查詢與其他子句（圖書借閱數據庫）from,where,orderby,limit子句的次序Selectlib_book.col_title書名,lib_book.col_author作者,lib_user.col_name借閱者姓名,lib_copy.col_bar_code副本條碼,lib_lending.col_lending_date借書時間,lib_lending.col_returned_date還書時間fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_bookwherecol_returned_dateisnullorderbylib_user.col_name;Select列名列表from表1innerjoin表2on連接條件where選擇條件orderby排序列limit行數;4.4.2【實訓4–9】外連接查詢根據Jitor校驗器的要求，在dbForge上完成“【實訓4–9】外連接查詢”男生四位，女生三位，共七人兩位單身的男生，一位單身的女生，以及兩對夫妻微課：4-3外連接和自連接【實訓4-9】4.4.2【實訓4–9】外連接查詢（續一）1．內連接作為對比，先看內連接的結果結果是兩對夫妻問題：如果還想列出單身女生的信息，如何編寫Select語句？答案見下頁Select

as男性,man.wifeas妻子ID,

as女性frommanjoinwomanonman.wife=woman.id;4.4.2【實訓4–9】外連接查詢（續二，右外連接）2．右外連接right

join可以列出右邊表的所有信息，即woman表，女生的信息問題：如果還想列出的是單身男生，而不是單身女生的信息，如何編寫Select語句？答案見下頁Select

as男性,man.wifeas妻子ID,

as女性fromman

rightjoinwomanonman.wife=woman.id;4.4.2【實訓4–9】外連接查詢（續三，左外連接）3．左外連接leftjoin可以列出左邊表的所有信息，即man表，男生的信息問題：如果還想同時列出單身男生和單身女生的信息，又要如何編寫Select語句？答案見下頁Select

as男性,man.wifeas妻子ID,

as女性fromman

leftjoinwomanonman.wife=woman.id;4.4.2【實訓4–9】外連接查詢（續四，全外連接）4．全外連接（full?，SQLServer使用full，但是MySQL不支持）列出兩邊表的所有數據正確的代碼是將右外連接rightjoin和左外連接leftjoin的結果合并union

到一起還要加上distinct，去除重復的已婚夫妻Select

as男性,man.wifeas妻子ID,

as女性fromman

fulljoinwomanonman.wife=woman.id;Select

as男性,man.wifeas妻子ID,as女性fromman

right

outerjoinwomanonman.wife=woman.iduniondistinctSelect

,man.wife,fromman

left

outerjoinwomanonman.wife=woman.id;4.4.3【實訓4–10】自連接查詢根據Jitor校驗器的要求，在dbForge上完成“【實訓4–10】自連接查詢”一個家庭，6人，父母、祖父母、外祖父母，其中祖母已逝，不在數據庫中【實訓4-10】4.4.3【實訓4–10】自連接查詢（續一）1．查詢每個人的父親Select

姓名,

父親frommemberasmejoinmemberasfatheronme.father_id=father.id;me（自己）father（父親）物理上一張表member邏輯上兩張表asme,asfather4.4.3【實訓4–10】自連接查詢（續二）2．查詢每個人的父親和母親Select

姓名,

父親,

母親frommemberasmejoinmemberasfatheronme.father_id=father.idjoinmemberasmotheronme.mother_id=mother.id;物理上一張表member邏輯上三張表asme,asfather,asmother4.4.3【實訓4–10】自連接查詢（續三）3．查詢所有人的父親和母親（包括父母已去世）Select

姓名,

父親,

母親frommemberasme

left

joinmemberasfatheronme.father_id=father.id

left

joinmemberasmotheronme.mother_id=mother.id;4.4.3【實訓4–10】自連接查詢（續四）4．查詢所有人的父親和母親（父母至少一人健在）Select

姓名,

父親,

母親frommemberasmeleftjoinmemberasfatheronme.father_id=father.idleftjoinmemberasmotheronme.mother_id=mother.idwhereme.father_idisnotnull--父親健在orme.mother_idisnotnull;--或母親健在想一想，如何查詢祖父母？如何查詢外祖父母？4.4.4【實訓4–11】統計與分組統計根據Jitor校驗器的要求，在dbForge上完成“【實訓4–11】統計與分組統計”【實訓4-11】4.4.4【實訓4–11】統計與分組統計（續一）1．簡單的統計Selectcount(*)圖書數量,round(avg(col_price),2)平均價格,min(col_price)最低價格,max(col_price)最高價格fromlib_book;這時，結果永遠只有一行4.4.4【實訓4–11】統計與分組統計（續二）2．分組統計Selectid_lib_publisher,count(*)圖書數量,round(avg(col_price),2)平均價格,min(col_price)最低價格,max(col_price)最高價格fromlib_bookgroupbyid_lib_publisher;這時，有多少個組，就有多少行例中，三個出版社就有三行4.4.4【實訓4–11】統計與分組統計（續三）3．having子句Selectid_lib_publisher,count(*)圖書數量,round(avg(col_price),2)平均價格,min(col_price)最低價格,max(col_price)最高價格fromlib_bookgroupbyid_lib_publisher

having

平均價格<80;Having用于對統計結果進行過濾例中，只列出平均價格低于80元的出版社4.4.4【實訓4–11】統計與分組統計（續四）having和where的比較Selectid_lib_publisher,count(*)圖書數量,round(avg(col_price),2)平均價格,min(col_price)最低價格,max(col_price)最高價格fromlib_bookwherecol_price<80groupbyid_lib_publish