表之間關(guān)系

一對一

? 一夫一妻
?

一對多關(guān)系

? 一個人可以擁有多輛汽車师崎，要求查詢某個人擁有的所有車輛扯躺。

? 創(chuàng)建Person表

? 創(chuàng)建Car表

多對多關(guān)系

? 學(xué)生選課舱痘，一個學(xué)生可以選修多門課程钓觉，每門課程可供多個學(xué)生選擇派殷。
? 一個學(xué)生可以有多個老師还最，而一個老師也可以有多個學(xué)生

? 1.創(chuàng)建老師表

? 2.創(chuàng)建學(xué)生表

? 3.創(chuàng)建學(xué)生與老師關(guān)系表

? 4.添加外鍵

? 為什么要拆分表，避免大量冗余數(shù)據(jù)的出現(xiàn)

多表查詢

合并結(jié)果集

什么是合并結(jié)果集

? 合并結(jié)果集就是把兩個select語句的查詢結(jié)果合并到一起

合并結(jié)果集的兩種方式

? UNION
? 合并時去除重復(fù)記錄
? UNION ALL
? 合并時不去除重復(fù)記錄

? 格式：
? UNION
? SELECT * FROM 表1 UNION SELECT * FROM 表2毡惜；
? SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
? 示例
? 創(chuàng)建表

? UNION

? UNION ALL

? 注意事項

? 被合并的兩個結(jié)果：列數(shù)拓轻、列類型必須相同。

連接查詢

什么是連接查詢

? 也可以叫跨表查詢虱黄，需要關(guān)聯(lián)多個表進行查詢

什么是笛卡爾集

? 假設(shè)集合A={a,b}悦即，集合B={0,1,2}，
? 則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}橱乱。
? 可以擴展到多個集合的情況

? 同時查詢兩個表辜梳，出現(xiàn)的就是笛卡爾集結(jié)果

?

? 查詢時給表起別名

?

多表聯(lián)查，如何保證數(shù)據(jù)正確
在查詢時要把主鍵和外鍵保持一致

?

? 主表當(dāng)中的數(shù)據(jù)參照子表當(dāng)中的數(shù)據(jù)
? 原理
? 逐行判斷泳叠，相等的留下作瞄，不相等的全不要

? 根據(jù)連接方式分類

內(nèi)連接

? 等值連接
? 兩個表同時出現(xiàn)的id號（值）才顯示

? 與多表聯(lián)查約束主外鍵是一樣，只是寫法改變了危纫。
? ON后面只寫主外鍵
? 如果還有條件直接在后面寫where

? 多表聯(lián)查后面還有條件就直接寫and

? 多表連接

? 建立學(xué)生宗挥，分?jǐn)?shù)，科目表

? 使用99連接法

?

? 使用內(nèi)聯(lián)查詢

? 非等值連接
? 示例表

建表語句

CREATE TABLE `emp` (

  `empno` int(11) NOT NULL,
  `ename` varchar(255) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `mgr` varchar(255) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `comm` double DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES (7369, '孫悟空', '職員', '7902', '2010-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, '孫尚香', '銷售人員', '7698', '2011-2-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, '李白', '銷售人員', '7698', '2011-2-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, '程咬金', '經(jīng)理', '7839', '2011-4-2', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, '妲己', '銷售人員', '7698', '2011-9-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, '蘭陵王', '經(jīng)理', '7839', '2011-5-1', 2854, NULL, 30);
INSERT INTO `emp` VALUES (7782, '虞姬', '經(jīng)理', '7839', '2011-6-9', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, '項羽', '檢查員', '7566', '2017-4-19', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7839, '張飛', '總裁', NULL, '2010-6-12', 5000, NULL, 10);
INSERT INTO `emp` VALUES (7844, '蔡文姬', '銷售人員', '7698', '2011-9-8', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, '阿珂', '職員', '7788', '2017-5-23', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, '劉備', '職員', '7698', '2011-12-3', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, '諸葛亮', '檢查員', '7566', '2011-12-3', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7934, '魯班', '職員', '7782', '2012-1-23', 1300, NULL, 10);

CREATE TABLE `dept` (
  `deptno` bigint(2) NOT NULL AUTO_INCREMENT COMMENT '表示部門編號种蝶，由兩位數(shù)字所組成',
  `dname` varchar(14) DEFAULT NULL COMMENT '部門名稱契耿，最多由14個字符所組成',
  `local` varchar(13) DEFAULT NULL COMMENT '部門所在的位置',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES (10, '財務(wù)部', '北京');
INSERT INTO `dept` VALUES (20, '調(diào)研部', '上海');
INSERT INTO `dept` VALUES (30, '銷售部', '王者峽谷');
INSERT INTO `dept` VALUES (40, '運營部', '騰訊大樓');

CREATE TABLE `salgrade` (
  `grade` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '工資等級',
  `lowSalary` int(11) DEFAULT NULL COMMENT '此等級的最低工資',
  `highSalary` int(11) DEFAULT NULL COMMENT '此等級的最高工資',
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `salgrade` VALUES (1, 700, 1200);
INSERT INTO `salgrade` VALUES (2, 1201, 1400);
INSERT INTO `salgrade` VALUES (3, 1401, 2000);
INSERT INTO `salgrade` VALUES (4, 2001, 3000);
INSERT INTO `salgrade` VALUES (5, 3001, 9999);

?

查詢所有員工的姓名，工資螃征，所在部門的名稱以及工資的等級
? 1.查詢所有員工的姓名搪桂，工資

? 2.查詢所有員工的姓名，工資和所有部門

?

? 3.查詢所有員工的姓名盯滚，工資和所在部門及工資等級

外連接

? 左外連接（左連接）
? 兩表滿足條件相同的數(shù)據(jù)查出來踢械，如果左邊表當(dāng)中有不相同的數(shù)據(jù)，也把左邊表當(dāng)中的數(shù)據(jù)查出來魄藕。
? 左邊表當(dāng)中的數(shù)據(jù)全部查出内列，右邊表當(dāng)中，只查出滿足條件的內(nèi)容

?
? 使用內(nèi)連接時背率，周七不會查出來话瞧，沒有成績嫩与，缺考了。把所有考過試的學(xué)生分?jǐn)?shù)查出來移稳。

?

? 使用左連接查詢所有學(xué)生及學(xué)生的考試分?jǐn)?shù)

? 左連接會把左表當(dāng)中的數(shù)據(jù)全部查出蕴纳，右表當(dāng)中只查出滿足條件的數(shù)據(jù)
? 可以省略outer不寫
? 查詢時，兩個表可以不需要建立主外鍵約束

? 右外連接（右連接）

? 右連接會把右當(dāng)中的數(shù)據(jù)全部查出个粱，左表當(dāng)中只查出滿足條件的數(shù)據(jù)

?

? 右邊表當(dāng)中 的所有數(shù)據(jù)全部查出古毛，左邊表只查出滿足條件的記錄
? 站在表的角度去看，使用左連接就把左邊表當(dāng)中的內(nèi)容全部查出都许，右邊查出滿足條件的稻薇。
? 使用右連接，就把右邊表當(dāng)中的數(shù)據(jù)全部查出胶征。左邊查出滿足條件的塞椎。

自然連接

? 連接查詢會產(chǎn)生無用笛卡爾集，我們通常使用主外鍵關(guān)系等式來去除它睛低。
? 而自然連接無需你去給出主外鍵等式案狠，它會自動找到這一等式
? 也就是說不用去寫條件

        select * from stu natural join score;

? 要求
? 兩張連接的表中列名稱和類型,需要完全一致的列作為條件
? 會去除相同的列

子查詢

什么是子查詢

? 一個select語句中包含另一個完整的select語句。
? 或兩個以上SELECT钱雷，那么就是子查詢語句了骂铁。

子查詢出現(xiàn)的位置

? where后，把select查詢出的結(jié)果當(dāng)作另一個select的條件值
? from后罩抗，把查詢出的結(jié)果當(dāng)作一個新表拉庵；

? 示例表

?

使用

? 查詢與項羽同一個部門人員工

? 1.先查出項羽所在的部門編號

? 2.再根據(jù)編號查同一部門的員工

? 把第1條查出來的結(jié)果當(dāng)?shù)?條語句的條件

? 查詢工資高于程咬金的員工
? 1.查出程咬金的工資

? 2.再去根據(jù)查出的結(jié)果查詢出大于該值的記錄員工名稱

? 工資高于30號部門所有人的員工信息
? 1.先查出30號部門工資最高的那個人

? 2.再到整個表中查詢大于30號部門工資最高的那個人

?

查詢工作和工資與妲己完全相同的員工信息
? 1.先查出妲已的工作和工資

? 2.根據(jù)查詢結(jié)果當(dāng)作條件再去查詢工作和工資相同的員工

? 由于是兩個條件，使用 IN進行判斷

有2個以上直接下屬的員工信息
? 1.對所有的上級編號進行分組
?

? 2.找出大于2個的套蒂，大于2個說明有兩個下屬

? 3.把上條的結(jié)果當(dāng)作員工編號時行查詢

? 查詢員工編號為7788的員工名稱钞支、員工工資、部門名稱操刀、部門地址

自連接

? 求7369員工編號烁挟、姓名、經(jīng)理編號和經(jīng)理姓名

? 以上這種方法只能查詢出一個經(jīng)理的名稱

? 自連接：自己連接自己骨坑，起別名