MySQL的多表操作
1 多表關(guān)系
MySQL多表之間的關(guān)系可以概括為：一對一贞绳、一對多/多對一關(guān)系，多對多
1.1 一對一關(guān)系
一個學(xué)生只有一張身份證；一張身份證只能對應(yīng)一學(xué)生。
在任一表中添加唯一外鍵玄渗，指向另一方主鍵座菠，確保一對一關(guān)系。
一般一對一關(guān)系很少見藤树，遇到一對一關(guān)系的表最好是合并表浴滴。
1.2 一對多/多對一關(guān)系
部門和員工
分析：一個部門有多個員工，一個員工只能對應(yīng)一個部門
實現(xiàn)原則：在多的一方建立外鍵也榄，指向一的一方的主鍵
1.3 多對多關(guān)系
學(xué)生和課程
分析：一個學(xué)生可以選擇很多門課程巡莹，一個課程也可以被很多學(xué)生選擇
原則：多對多關(guān)系實現(xiàn)需要借助第三張中間表司志。中間表至少包含兩個字段甜紫，將多對多的關(guān)系，拆成一對多的關(guān)系骂远，中間表至少要有兩個外鍵囚霸，這兩個外鍵分別指向原來的那兩張表的主鍵
1.4 什么是外鍵約束
MySQL 外鍵約束（FOREIGN KEY）是表的一個特殊字段，經(jīng)常與主鍵約束一起使用激才。對于兩個具有關(guān)聯(lián)關(guān)系的表而言拓型，相關(guān)聯(lián)字段中主鍵所在的表就是主表（父表），外鍵所在的表就是從表（子表）瘸恼。
外鍵用來建立主表與從表的關(guān)聯(lián)關(guān)系劣挫，為兩個表的數(shù)據(jù)建立連接，約束兩個表中數(shù)據(jù)的一致性和完整性东帅。比如压固，一個水果攤，只有蘋果靠闭、桃子帐我、李子、西瓜等 4 種水果愧膀，那么拦键，你來到水果攤要買水果就只能選擇蘋果、桃子檩淋、李子和西瓜芬为，其它的水果都是不能購買的。
定義一個外鍵時，需要遵守下列規(guī)則：
- 主表必須已經(jīng)存在于數(shù)據(jù)庫中缅刽，或者是當(dāng)前正在創(chuàng)建的表禀倔。
- 必須為主表定義主鍵。
- 主鍵不能包含空值莲镣，但允許在外鍵中出現(xiàn)空值。也就是說涎拉，只要外鍵的每個非空值出現(xiàn)在指定的主鍵中瑞侮，這 個外鍵的內(nèi)容就是正確的的圆。
- 在主表的表名后面指定列名或列名的組合。這個列或列的組合必須是主表的主鍵或候選鍵半火。
- 外鍵中列的數(shù)目必須和主表的主鍵中列的數(shù)目相同越妈。
- 外鍵中列的數(shù)據(jù)類型必須和主表主鍵中對應(yīng)列的數(shù)據(jù)類型相同。
1.5 創(chuàng)建外鍵約束
方式1-在創(chuàng)建表時設(shè)置外鍵約束
[constraint <外鍵名>] foreign key 字段名 [钮糖，字段名2梅掠，…] references <主表名> 主鍵列1 [，主鍵列2店归，…]
方式2-在創(chuàng)建表時設(shè)置外鍵約束
外鍵約束也可以在修改表時添加阎抒，但是添加外鍵約束的前提是：從表中外鍵列中的數(shù)據(jù)必須與主表中主鍵列中的數(shù)據(jù)一致或者是沒有數(shù)據(jù)。
alter table <數(shù)據(jù)表名> add constraint <外鍵名> foreign key(<列名>) references <主表名> (<列名>);
在外鍵約束下的數(shù)據(jù)操作必須先給主表添加數(shù)據(jù)消痛，給從表添加數(shù)據(jù)時且叁，外鍵列的值不能隨便寫，必須依賴主表的主鍵列
主表的數(shù)據(jù)被從表依賴時秩伞，不能刪除逞带，否則可以刪除，從表的數(shù)據(jù)可以隨便刪除
1.6 刪除外鍵約束
當(dāng)一個表中不需要外鍵約束時纱新，就需要從表中將其刪除展氓。外鍵一旦刪除，就會解除主表和從表間的關(guān)聯(lián)關(guān)系
alter table <表名> drop foreign key <外鍵約束名>;
1.7 多對多關(guān)系下的外鍵約束
-- 學(xué)生表和課程表(多對多)
-- 1 創(chuàng)建學(xué)生表student(左側(cè)主表)
create table if not exists student(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);
-- 2 創(chuàng)建課程表course(右側(cè)主表)
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);
-- 3創(chuàng)建中間表student_course/score(從表)
create table score(
sid int,
cid int,
score double
);
-- 4建立外鍵約束(2次)
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
2 多表操作
多表查詢就是同時查詢兩個或兩個以上的表脸爱，因為有的時候用戶在查看數(shù)據(jù)的時候,需要顯示的數(shù)據(jù)來自多張表遇汞。多表查詢有以下分類：
- 交叉連接查詢
select * from A,B;
- 內(nèi)連接查詢(使用的關(guān)鍵字 inner join -- inner可以省略)
# 隱式內(nèi)連接（SQL92標(biāo)準(zhǔn)）：
select * from A,B where 條件;
# 顯示內(nèi)連接（SQL99標(biāo)準(zhǔn)）：
select * from A inner join B on 條件;
- 外連接查詢(使用的關(guān)鍵字 outer join -- outer可以省略)
# 左外連接：left outer joinselect * from A left outer join B on 條件;# 右外連接：right outer joinselect * from A right outer join B on 條件;# 滿外連接: full outer joinselect * from A full outer join B on 條件;
- 子查詢：select的嵌套
- 表自關(guān)聯(lián)：將一張表當(dāng)成多張表來用
2.1 交叉連接查詢
- 交叉連接查詢返回被連接的兩個表所有數(shù)據(jù)行的笛卡爾積、
- 笛卡爾積可以理解為一張表的每一行去和另外一張表的任意一行進行匹配
- 假如A表有m行數(shù)據(jù)阅羹，B表有n行數(shù)據(jù)勺疼，則返回m*n行數(shù)據(jù)
- 笛卡爾積會產(chǎn)生很多冗余的數(shù)據(jù)，后期的其他查詢可以在該集合的基礎(chǔ)上進行條件篩選
/* 格式：select * from 表1,表2,表3….; */-- 交叉連接查詢select * from dept3,emp3;
2.2 內(nèi)鏈接查詢
內(nèi)連接查詢求多張表的交集
/*格式：隱式內(nèi)連接（SQL92標(biāo)準(zhǔn)）：select * from A,B where 條件; 顯示內(nèi)連接（SQL99標(biāo)準(zhǔn)）：select * from A inner join B on 條件;*/-- 查詢每個部門的所屬員工select * from dept3,emp3 where dept3.deptno = emp3.dept_id;select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
2.3 外鏈接查詢
外連接分為左外連接（left outer join）捏鱼、右外連接(right outer join)执庐，滿外連接(full outer join)。
注意：oracle里面有full join,可是在mysql對full join支持的不好导梆。我們可以使用union來達到目的轨淌。
/*格式：左外連接：left outer join select * from A left outer join B on 條件;右外連接：right outer join select * from A right outer join B on 條件; 滿外連接: full outer join select * from A full outer join B on 條件;*/-- 外連接查詢-- 查詢哪些部門有員工，哪些部門沒有員工use mydb3;select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;-- 查詢哪些員工有對應(yīng)的部門看尼，哪些沒有select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;-- 使用union關(guān)鍵字實現(xiàn)左外連接和右外連接的并集select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_idunion select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
2.4 子查詢
子查詢就是指的在一個完整的查詢語句之中递鹉，嵌套若干個不同功能的小查詢，從而一起完成復(fù)雜查詢的一種編寫形式藏斩，通俗一點就是包含select嵌套的查詢躏结。子查詢可以返回的數(shù)據(jù)類型一共分為四種：
- 單行單列：返回的是一個具體列的內(nèi)容，可以理解為一個單值數(shù)據(jù)狰域；
- 單行多列：返回一行數(shù)據(jù)中多個列的內(nèi)容媳拴；
- 多行單列：返回多行記錄之中同一列的內(nèi)容黄橘，相當(dāng)于給出了一個操作范圍；
- 多行多列：查詢返回的結(jié)果是一張臨時表
-- 查詢年齡最大的員工信息屈溉，顯示信息包含員工號塞关、員工名字，員工年齡select eid,ename,age from emp3 where age = (select max(age) from emp3);-- 查詢年研發(fā)部和銷售部的員工信息子巾，包含員工號帆赢、員工名字select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研發(fā)部' or name = '銷售部') ;-- 查詢研發(fā)部20歲以下的員工信息,包括員工號、員工名字线梗，部門名字select eid,age,ename,name from (select * from dept where name = '研發(fā)部 ')t1,(select * from emp3 where age <20)t2
- 子查詢關(guān)鍵字-ALL
select …from …where c > all(查詢語句)-- 等價于：select ...from ... where c > result1 and c > result2 and c > result3
- ALL: 與子查詢返回的所有值比較為true 則返回true
- ALL可以與=椰于、>、>=缠导、<廉羔、<=溉痢、<>結(jié)合是來使用僻造，分別表示等于、大于孩饼、大于等于髓削、小于、小于等于镀娶、不等于其中的其中的所有數(shù)據(jù)立膛。
- ALL表示指定列中的值必須要大于子查詢集的每一個值，即必須要大于子查詢集的最大值梯码；如果是小于號即小于子查詢集的最小值宝泵。同理可以推出其它的比較運算符的情況。
- 子查詢關(guān)鍵字-ANY和SOME
select …from …where c > any(查詢語句)--等價于：select ...from ... where c > result1 or c > result2 or c > result3
- ANY:與子查詢返回的任何值比較為true 則返回true
- ANY可以與=轩娶、>儿奶、>=、<鳄抒、<=闯捎、<>結(jié)合是來使用，分別表示等于许溅、大于瓤鼻、大于等于、小于贤重、小于等于茬祷、不等于其中的其中的任何一個數(shù)據(jù)。
- 表示制定列中的值要大于子查詢中的任意一個值并蝗，即必須要大于子查詢集中的最小值祭犯。同理可以推出其它的比較運算符的情況
- SOME和ANY的作用一樣耐朴，SOME可以理解為ANY的別名
- 子查詢關(guān)鍵字-IN
select …from …where c in(查詢語句)--等價于：select ...from ... where c = result1 or c = result2 or c = result3
- IN關(guān)鍵字，用于判斷某個記錄的值盹憎，是否在指定的集合中
- 在IN關(guān)鍵字前邊加上not可以將條件反過來
- 子查詢關(guān)鍵字-EXISTS
select …from …where exists(查詢語句)
- 該子查詢?nèi)绻坝袛?shù)據(jù)結(jié)果”(至少返回一行數(shù)據(jù))筛峭， 則該EXISTS() 的結(jié)果為“true”，外層查詢執(zhí)行
- 該子查詢?nèi)绻皼]有數(shù)據(jù)結(jié)果”（沒有任何數(shù)據(jù)返回）陪每，則該EXISTS()的結(jié)果為“false”影晓，外層查詢不執(zhí)行
- EXISTS后面的子查詢不返回任何實際數(shù)據(jù)，只返回真或假檩禾，當(dāng)返回真時 where條件成立
- 注意挂签，EXISTS關(guān)鍵字，比IN關(guān)鍵字的運算效率高盼产，因此饵婆，在實際開發(fā)中，特別是大數(shù)據(jù)量時戏售，推薦使用EXISTS關(guān)鍵字
2.5 自關(guān)聯(lián)查詢
MySQL有時在信息查詢時需要進行對表自身進行關(guān)聯(lián)查詢侨核，即一張表自己和自己關(guān)聯(lián)，一張表當(dāng)成多張表來用灌灾。注意自關(guān)聯(lián)時表必須給表起別名搓译。
select 字段列表 from 表1 a , 表1 b where 條件;# 或者 select 字段列表 from 表1 a [left] join 表1 b on 條件;
