資料庫進階2

關聯查詢

概念

當在查詢時，我們所需要的資料不在一張表中，可能在兩張表或多張表中。此時我們需要同時操作這些表，即關聯查詢。

等值連接配接

在做多張表查詢時，這些表中應該存在着有關聯的兩個字段，我們使用某一張表中的一條記錄與另外一張表通過相關聯的兩個字段進行比對，組合成一條記錄。

笛卡爾積

在做多張表查詢時，我們使用某一張表中的每一條記錄與另一張表的所有記錄進行組合

比如表A有x條，表B有y條，最終組合數為x*y,這個值就是笛卡爾積，通常沒有意義。

内連接配接

隻要使用了join on,就是内連結，查詢效果與等值連接配接一樣。

用法：表A [inner]  join  表B  on 關聯條件【後再添加條件可以使用where也可以使用and】

外連接配接

在做多張表查詢時，我們所需要的資料，除了滿足關聯條件的資料外，還有不滿足關聯條件的資料。此時需要使用外連接配接。會涉及到兩個概念：

驅動表(主表)：除了顯示滿足條件的資料，還需要顯示不滿足條件的資料的表

從表(副表)：隻顯示滿足關聯條件的資料的表

外連接配接分類	左外連接配接	表A left [outre] join 表B on 關聯條件。 表A是驅動表，表B是從表
右外連接配接	表A right [outer] join 表B on 關聯條件。 表B是驅動表，表A是從表
全外連接配接	兩張表的資料不管滿不滿足條件，都做顯示。 表A full [outer] join 表B on 關聯條件

   PS:mysql不支援全外連接配接

自連接配接

在多張表進行關聯查詢時，這些表的表名是同一個。即自連接配接。

等值連接配接練習：查詢部門表的名稱及其每個部門的員工的員工編号和姓名及其職位，部門編号，按照部門号降序排序

select e.deptno,d.dname,e.empno,e.ename,e.job from emp e,dept d where e.deptno = d.deptno order by e.deptno DESC;

内連接配接練習1：使用内連接配接修改上述練習

select dept.deptno,dname,empno,ename,job from emp  join dept on emp.deptno = dept.deptno order by dept.deptno DESC;

内連接配接練習2：查詢20和30部門的部門名稱，部門位址及其員工的姓名和工資

select emp.deptno,dname,loc,ename,sal+ifnull(comn,0) from emp join dept on emp.deptno = dept.deptno and emp.deptno in (20,30) order by emp.deptno ASC;

左外連接配接練習:查詢員工表的所有資訊及其員工所在部門的資訊

select emp.* ,dept.* from emp left join dept on emp.deptno = dept.deptno;

右外連接配接練習：使用右外連接配接查詢部門表的所有資訊及其員工資訊

select emp.*,dept.* from emp right join dept on emp.deptno = dept.deptno;

全外連接配接練習：使用全外連接配接查詢部門表的所有資訊及其員工資訊//mysql不支援全外連接配接

select emp.*,dept.* from emp full join dept on emp.deptno = dept.deptno;

自連接配接練習1：查詢員工的編号，姓名及其上司編号和姓名

select e1.empno "員工編号" ,e1.ename "員工姓名",e2.empno "上司編号" ,e2.ename "上司姓名" from emp e1 join emp e2 on e1.mgr = e2.empno or e1.mgr is null;

自連接配接練習2：查詢上司的姓名及其下屬的姓名

select e1.ename "上司姓名",e2.ename "下屬姓名" from emp e1 join emp e2 on e1.empno = e2.mgr and e2.mgr ;

 進階關聯查詢

有的時候我們要查詢的資料，一個簡單的查詢語句滿足不了，并且我們使用的資料，表中不能直覺展現出來。

而是預先經過一次查詢才會有所展現。那麼先執行的查詢，我們稱之為子查詢。被子查詢嵌入的查詢語句稱之為父查詢。

比如需求：查詢工資大于員工JONES工資的員工的資訊。

   子查詢傳回的資料特點：

   (1)可能是單行單列的資料。

   (2)可能是多行單列的資料

   (3)可能是單行多列的資料

   (4)可能是多行多列的資料

   1）子查詢可以在where子句中

   2）子查詢可以在from子句中

   3)子查詢可以在having子句中

   4)子查詢可以在select子句中，相當于外連接配接中的另一種寫法

子查詢在where子句中

練習1：查詢工資大于員工JONES工資的員工的資訊

select * from emp where sal > (select sal from emp where ename = 'JONES');

練習2：查詢員工表中工資大于10号部門的平均工資的員工的姓名，職位，工資及其部門号

select ename,job,sal,deptno from emp where sal > (select avg(ifnull(sal,0)) from emp where deptno = 10);

練習3：查詢工資等于每個部門的平均工資的人員資訊

select * from emp where sal in (select avg(ifnull(sal,0)) from emp group by deptno) ;

練習4：查詢工資大于所有部門的平均工資的人員資訊

select * from emp where sal >all (select avg(ifnull(sal,0)) from emp group by deptno) ;

練習5：查詢工資大于任意部門的平均工資的人員資訊

select * from emp where sal >any (select avg(ifnull(sal,0))from emp group by deptno) ;

練習6：先将clark和scott的獎金修改成300，查詢工資和獎金與員工scott相同的其他員工資訊

update emp set comn = 300 ; select * from emp where sal = (select sal from emp where ename = 'scott') and comn = (select comn from emp where ename = 'scott') and ename != 'scott'; select * from emp where (sal,comn) = (select sal,comn from emp where ename = 'scott') and ename != 'scott';

子查詢在from子句中

練習1：查詢每個員工的工資，姓名和其部門的平均工資。

第一步：查詢每個部門部門的平均工資

select avg(ifnull(sal,0)) from emp group by deptno

第二步：做關聯查詢（将上一步的查詢語句看成一張表）

select ename,sal,t.avg_ from emp e join (select deptno, avg(ifnull(sal,0)) 'avg_' from emp group by deptno) t on e.deptno = t.deptno ;

練習2：查詢大于本部門平均工資的員工的資訊

select * from emp e join (select deptno, avg(ifnull(sal,0)) 'avg_' from emp group by deptno) t on e.deptno = t.deptno and e.sal>t.avg_;

子查詢在having子句中

練習1：查詢部門平均工資小于10号部門平均工資的部門平均工資，工資之和，最大工資，最小工資，總人數

select avg(ifnull(sal,0)),sum(sal),max(sal),min(sal),count(*) from emp group by deptno having avg(ifnull(sal,0)) < (selectavg(ifnull(sal,0))from emp where deptno = 10);

子查詢在select子句中

練習1：查詢每個員工的姓名，工資，及其部門的平均工資

select ename,sal,(select avg(ifnull(sal,0)) from emp a where a.deptno = b.deptno) avg_sal from emp b order by deptno ASC; //SUPERMAN 的平均工資是null,因為a.deptno= b.deptno 無法控制比較null

練習2：查詢每個員工的姓名，工資，及其部門的平均工資,工資之和

select ename,sal,(select avg(ifnull(sal,0)) from emp a where a.deptno = b.deptno) avg_sal ,(select sum(sal) from emp c where c.deptno = b.deptno) sum_sal from emp b order by deptno ASC;

限制Constraint

對插入表中字段的資料起到一定的條件限制。

限制分五種：

  主鍵限制：primary key                     

                 要求作為主鍵的字段的字段值非空且唯一

               【一般不存放主要資訊，比如序号，一般需要一定的順序】

  非空限制：not null                           

                要求：有非空限制的字段不可以為null值【可以重複】

  唯一性限制：unique                       

                要求：有唯一性限制的字段不可以重複，但是可以為null，

               【null可以重複插入，null和null不相等】

  檢查性限制:check(條件)--mysql不支援該寫法       

                 enum()--例：規定性别enum('f','m')   check(gender in ('f','m'))

                 要麼符合檢查限制的條件，要麼可以為null           

  外鍵限制：foreign key           

                 要求：有外鍵限制的字段A必須依賴于另外一個字段B，且字段B必須要有主鍵限制。

                 字段A的值要麼是null，要麼是字段B中的值           

  序列：

               作為主鍵的字段，通常不是表中的主要資訊，可以用來當成資訊記錄的序号，序号最好是有序的序列。

               作用：用來給作為主鍵限制的字段進行有序的自增。

  auto_increment 關鍵字:

               用來對有主鍵限制的字段做自增操作。預設序列是從1開始，每次自增+1；

主鍵限制練習：建立表t_01字段tid int設定為主鍵限制，tname varchar(20)

create table t_01(tid int primary key,tname varchar(20));

非空限制

create table t_02(tid int primary key,tname varchar(20) not null);

唯一性限制

create table t_03(tid int primary key,tname varchar(20) not null,idcard varchar(18) unique);

檢查性限制

create table t_04(tie int primary key,tname varchar(20) not null,idcard varchar(18) unique,gender enum('f','m'));

外鍵限制

create table t_04(tie int primary key,tname varchar(20) not null,idcard varchar(18) unique,gender enum('f','m'),foreign key(mgr) references t_05(empno));

序列

create table t_06(tid int primary key auto_crement,tname varchar(20) not null); insert into t_06 valules(1001,'zs'); insert into t_06 valules(null,'ls');//tid會自動自增

 分頁查詢

 需求：當每次查詢的記錄數比較大，通常一頁顯示不下，此時我們可以進行分頁查詢。

--關鍵字limit--用法：limit begin,size;

                    --begin :記錄的開始行數。偏移量

                    --size:每頁的最大記錄數。

--注意：limit後面可以是一個參數，

--     一個參數時，是指每次最大的查詢記錄數，limit size;

--     預設記錄數從0開始，而不是1.

查詢員工表，查詢第一頁的資料，每頁10條

select * from emp limit 0,10;

查詢員工表，查詢第二頁的資料，每頁5條

select * from emp limit 5,5;

按照部門号升序排序，查詢第二頁的資料，每頁5條

select * from emp order by deptno ASC limit 5,5;

按照員工編号降序排序，查詢第二頁的資料，每頁5條

select * from emp order by deptno DESC limit 5,5; select * from emp order by deptno DESC;

使用子查詢，先排序，再查詢每頁的資料。 --      按照員工工作降序排序，查詢第二頁的資料，每頁6條

select e.* from (select * from emp order by sal DESC) e limit 6,6;

執行效率與優化 --建立了一張表temp_001,插入了10w條資料。 --需求：每頁100條記錄，查詢第1頁的資料 --                  查詢第11頁的資料 --                  查詢101頁的資料 --                  查詢1001頁的資料

select * from temp_001 limit 0,100; select * from temp_001 limit 1000,100; select * from temp_001 limit 10000,100; select * from temp_001 limit 100000,100;

當我們在做分頁查詢的時候，查詢的效率與偏移量有關系，偏移量越大，效率越低，消耗的時間就會越多。 --如上述分頁查詢 --如何優化分頁查詢 --(1)我們可以使用子查詢先确定作為tid偏移量的位置。 --   select tid from temp_001 limit 100000,1; --(2)再進行分頁查詢 --   select * from emp where tid>=(查出id的值) limit 100; select * from temp_001 where tid>=(select tid from temp_001 limit 100000,1) limit 100; --還有一種優化：首先确定tid必須是連續的資料。 --使用between and select * from temp_001 where tid between 145000 and 145200 limit 100;

視圖

VIEW，表的一部分投影。也是資料庫裡一個對象。（表的虛拟部分）

--視圖的作用：因為視圖中含有某表中的部分資料，不在視圖中的字段有隐藏效果。相對來說，安全。

--視圖可以像表一樣，進行增删改查

--通過視圖進行DML操作，一定會對表有影響。

--通過表進行DML操作，可能會對視圖有影響。

建立視圖

create view ViewName as subQuery

将員工表中10号部門的資料封裝到一個視圖view_emp_deptno_10

create view view_emp_deptno_10 as select * from emp where deptno = 10;

通過視圖，插入資料 empno:9001,ename:'zs',部門号:10

insert into view_emp_deptno_10 values(9001,'zs',null,null,null,null,null,10);

查詢表emp,查詢視圖

select * from emp; select * from view_emp_deptno_10;