一、表的關(guān)系分析：

用戶和訂單：一個用戶可以有多個訂單，但每個訂單只能屬于一個用戶，所以是一對多的關(guān)系。
商品和分類：一個產(chǎn)品只能有一種分類，而一個分類可以有多種產(chǎn)品，所以是多對一的關(guān)系。
訂單和商品：一個訂單可以有多個商品，一個商品可以有多個訂單，所以是多對多的關(guān)系。

二、主從表與主外鍵

當(dāng)兩張表的關(guān)系是一對多的時候，一代表主表，多代表從表，在從表設(shè)置外鍵執(zhí)行主表的主鍵

表category.png

表product

注意插入數(shù)據(jù)時需要先設(shè)置 set names gbk

• 聲明外鍵約束:
  alter table 從表 add [constraint] [外鍵名稱] foreign key (從表外鍵字段名) references 主表 （主表的主鍵）
  注意：外鍵名稱用于刪除外鍵約束的，一般建議以“_fk”結(jié)尾
• 刪除外鍵約束：
  alter table 從表 drop foreign key 外鍵名稱
  
  Paste_Image.png

注意事項

• 從表外鍵不能添加主表中不存在的記錄
• 主表不能刪除從表中已經(jīng)引用的記錄

三、建表規(guī)則總結(jié)

一對多的建表原則：在多的一方創(chuàng)建一個字段，字段作為外鍵指向的一方的主鍵

一對多的建表原則

多對多建表原則：兩張表之間多對多的關(guān)系需要通過創(chuàng)建第三張表，也稱為中間表，中間表中至少有有兩個字段分別作為外鍵指向各自一方的主鍵

多對多建表原則

四、多表查詢

• 內(nèi)連接（用的比較多）

  • 普通(顯示)內(nèi)連接：前提條件需要有外鍵，提交的關(guān)鍵字是 inner join ... on
    select * from A inner join B on 條件;
  • 隱式內(nèi)連接（用的是最多的）：可以省略inner join ... on 關(guān)鍵字
    select * from A,B where 條件

• 外連接

  • 左連接（看左表,即sql語句中 left 關(guān)鍵字前的表）：前提條件需要外鍵，使用關(guān)鍵字 left join ... on
    select * from dept left outer join emp on dept.did = emp.dno

  • 右連接（看右表與左連接相反）

    
    
    內(nèi)連接和外連接的區(qū)別

• 子查詢：一條select語句結(jié)果作為另一條select語法的一部分（查詢條件、查詢結(jié)果、表等）

五、案例：

• 本題用到下面三個關(guān)系表：
  CARD 借書卡。 CNO 卡號，NAME 姓名，CLASS 班級
  BOOKS 圖書。 BNO 書號，BNAME 書名,AUTHOR 作者，PRICE 單價，QUANTITY 庫存冊數(shù)
  BORROW 借書記錄。 CNO 借書卡號，BNO 書號，RDATE 還書日期
  備注：限定每人每種書只能借一本；

• 要求實現(xiàn)如下15個處理：

1．找出借書超過5本的讀者,輸出借書卡號及所借圖書冊數(shù)。(2分)
SELECT CNO,COUNT(*) FROM BORROW GROUP BY CNO HAVING COUNT(*)>5
2．查詢借閱了"水滸"一書的讀者，輸出姓名及班級。(3分)
SELECT * FROM CARD c WHERE EXISTS(SELECT * FROM BORROW a，BOOKS b WHERE a.BNO = b.BNO AND b.BNAME=N‘水滸’ AND a.CNO=c.CNO)
3．查詢過期未還圖書，輸出借閱者（卡號）、書號及還書日期。(3分)
SELECET CNO AS "卡號"，BNO as "書號"，RDATE as "還書日期" FROM BORROW WHERE RDATE < NOW();
4．查詢書名包括"網(wǎng)絡(luò)"關(guān)鍵詞的圖書，輸出書號、書名、作者。(2分)
SELECET BNO,BNAME,AUTHOR FROM BOOKS WHERE BNAME LIKE "%網(wǎng)絡(luò)%"
5．查詢現(xiàn)有圖書中價格最高的圖書，輸出書名及作者。(2分)
SELECET BNAME,AUTHOR FROM BOOKS WHERE PRICE = (SELECT MAX(PRICE) FROM BOOKS)
6．查詢當(dāng)前借了"計算方法"但沒有借"計算方法習(xí)題集"的讀者，輸出其借書卡號，并按卡號降序排序輸出。(4分)
SELECET bo.CNO FORM BORROW bo,BOOKS WHERE bo.BNO = BOOKS.BNO and BOOKS.BNAME="計算方法" AND NOT EXISTS(SELECT * FORM BORROW r，BOOKS b WHERE r.BNO = b.BNO AND b.BNAME="計算方法題集") ORDER BY bo.CNO DESC;
7．將"C01"班同學(xué)所借圖書的還期都延長一周。(2分)
UPDATA bo SET RDATE=DATEADD(Day,7,bo.RDATE) FROM CARD c,BORROW bo WHERE c.CNO=b.CNO AND c.CLASS="c01";
8．從BOOKS表中刪除當(dāng)前無人借閱的圖書記錄。(2分)
DELETE FROM BOOKS WHRER BNO NOT IN (SELECT DISTINCT BNO FROM BORROW)
9．在BORROW表上建立一個觸發(fā)器，完成如下功能：如果讀者借閱的書名是"數(shù)據(jù)庫技術(shù)及應(yīng)用"，就將該讀者的借閱記錄保存在BORROW_SAVE表中（注ORROW_SAVE表結(jié)構(gòu)同BORROW表）。(4分)

10．建立一個視圖，顯示"力01"班學(xué)生的借書信息（只要求顯示姓名和書名）。(3分)

11．查詢當(dāng)前同時借有"計算方法"和"組合數(shù)學(xué)"兩本書的讀者，輸出其借書卡號，并按卡號升序排序輸出。(3分)

• 運用到的知識點

  
  
  排序和聚合
  
  
  分組和常見查詢語句格式
  
  
  多表查詢
  
  
  子查詢