MySQL多表勾兑_学习MySQL之多表操作（三）

##多表查询

##使用数据库 mytestUSEmytest;

##删除，并重新创建表 t_deptDROP TABLEt_dept;CREATE TABLEt_dept (

deptnoINT(11) NOT NULL,

dnameVARCHAR(20) NOT NULL,

locVARCHAR(40),UNIQUE INDEXuk_deptno(deptno)

)

##创建测试表 t_aCREATE TABLEt_A (

aIdINT (11) AUTO_INCREMENT, ##主键，自增

aNameVARCHAR (20) NOT NULL,

locVARCHAR (40),

cIdINT(11),PRIMARY KEYpk_aId (aId)

) ;

##创建测试表 t_bCREATE TABLEt_B (

bIdINT (11) AUTO_INCREMENT, ##主键，自增

bStandVARCHAR (40) NOT NULL,

aIdINT (11) NOT NULL,PRIMARY KEYpk_bId (bId),UNIQUE INDEXuk_aId (aId) ##唯一索引

) ;

##创建测试表 t_cCREATE TABLEt_c (

cIdINT (11) AUTO_INCREMENT, ##主键，自增

cvalueVARCHAR (20) NOT NULL,PRIMARY KEYpk_cId (cId)

);

##创建测试表 t_dCREATE TABLEt_d(

xnameVARCHAR(11),

xsexVARCHAR(11)

);

##创建测试表 t_eCREATE TABLEt_e(

xnameVARCHAR(11),

xsexVARCHAR(11)

);

##删除唯一索引ALTER TABLEt_bDROP KEYuk_aId;

##插入测试数据INSERT INTOt_dept(deptno,dname,loc)VALUES(10,'生产','生产部'),

(20,'业务','业务部'),

(30,'品质','品质部'),

(40,'人事','人事部'),

(50,'工程','工程部');INSERT INTOt_a (aName, loc,cId)VALUES('Jay', 'Jay Chou',1),

('Join', 'Join Chang',3),

('Hebe', 'Hebe Young',2),

('Jack', 'Jack Son',2),

('Smile', 'Smile Kiss',1) ;INSERT INTOt_b (bStand, aId)VALUES('Jay-Jay Chou', 1),

('Hebe-Hebe Young', 3),

('Jack-Jack Son', 4),

('Smile-Smile Kiss', 5) ;INSERT INTOt_c (cvalue)VALUES ('鸡'),('牛'),('马');INSERT INTOt_d (xname,xsex)VALUES ('章子怡','女'),('张筱雨','女'),('张雨涵','男'),('张予曦','女'),('张曦予','女');INSERT INTOt_e (xname,xsex)VALUES ('张曦予','女'),('汪峰','男'),('张馨予','女');------------- 内联、外联不推荐使用，根据笛卡尔积，执行效率低于子查询 ----------- --

##内联 □■□

##INNER JOIN …… ON。显示主副表数据交集SELECTa.aid,a.aName,a.loc,b.bStand,c.cvalueFROM t_a ASaINNER JOIN t_b ASbON a.aId =b.aIdINNER JOIN t_c AScON a.cId=c.cId;

##外联 ■■□OR□■■

##外联显示的数据以主表为准，不管副表是否有对应数据都会显示，副表无数据的，显示null。■■□

##内联，副表无对应数据，则不会显示出。简而言之，显示数据为主副表的 交集。□■□

##左联：以本表为主表，左联表为副表。■■□SELECTa.aid,a.aName,a.loc,b.bStandFROM t_a ASaLEFT JOIN t_b ASbON a.aid=b.aid;

##右联：以本表为副表，右联表为主表。□■■SELECTa.aid,a.aName,a.loc,b.bStandFROM t_a ASaRIGHT JOIN t_b ASbON a.aid=b.aid;

##合并查询：SELECT * FROM t_d UNION SELECT * FROM t_e; ## UNION，去掉两表的重复数据。 ■■■SELECT * FROM t_d UNION ALL SELECT * FROM t_e; ## UNION ALL, 未去掉两表的重复数据。 ■■■■------------- 子查询 推荐使用，根据笛卡尔积，执行效率高 ----------- --

SELECT COUNT(*) FROM t_a AS a,t_b ASb; ##笛卡尔积SELECT * FROMt_aWHERE cId=(SELECT cId FROM t_a WHERE aName = 'Jay'); ##结果集可以是多行SELECT * FROMt_aWHERE (aId,loc) = ( SELECT aId,loc FROM t_a WHERE cId=3); ##结果集只能是单行SELECT * FROMt_employeeWHERE deptno IN ( SELECT deptno FROMt_dept);SELECT * FROMt_employeeWHERE deptno NOT IN ( SELECT deptno FROMt_dept);

##使用ANY。在使用上，用<= 和 >=更有意义 ■■□

##

##>ANY (>=ANY) ，结果集是比最大值(包含)小的结果。

##=ANY，与使用 IN一样。SELECT sal FROMt_employeeWHERE job='SALESMAN';SELECT * FROMt_employeeWHERE sal>=ANY(SELECT sal FROM t_employee WHERE job='SALESMAN');

##使用ALL。在使用上，用< 和 >更有意义 ■□□

##

##>ALL (>=ALL) ，结果集是比最小值(包含)更小的结果。

##=ANY，与使用 IN一样。SELECT sal FROMt_employeeWHERE job='SALESMAN';SELECT * FROMt_employeeWHERE sal>ALL(SELECT sal FROM t_employee WHERE job='SALESMAN');

##使用EXISTS与IN 的使用效果类似，以下两条SQL语句执行结果一致。SELECT *

FROM t_dept ASdWHERE EXISTS( SELECT * FROM t_employee WHERE deptno=d.deptno);SELECT * FROM t_dept ASdWHERE deptno IN( SELECT deptno FROMt_employee);--综合测试 --

##计算笛卡尔积SELECT COUNT(empno) FROMt_employee;SELECT COUNT(deptno) FROMt_dept;

SELECT COUNT(*) FROM t_employee,t_dept;

##使用内联，无字段为NULL，但效率低(笛卡尔积为 9X5=45)SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) AS number, AVG(e.sal) ASaverageFROM t_dept AS d INNER JOIN t_employee ASeON d.deptno =e.deptnoGROUP BYd.deptno;

##使用左联，有字段为NULL,且效率低(笛卡尔积为 9X5=45)SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) AS number,AVG(e.sal) ASaverageFROM t_dept AS d LEFT JOIN t_employee ASeON d.deptno=e.deptnoGROUP BYd.deptno;

##使用右联，虽无字段为NULL，但无法保证其他情况没NULL，且效率低(笛卡尔积为 9X5=45)SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) AS number,AVG(e.sal) ASaverageFROM t_employee AS e LEFT JOIN t_dept ASdON d.deptno=e.deptnoGROUP BYd.deptno;

##使用子查询。最好为每个表派生别名，并指出每个字段是哪张表的。(防止字段之间重名)

##效率高，笛卡尔积为(4+9=13)SELECT d.deptno,d.dname,d.loc,e.number,e.averageFROM t_dept ASd,

(SELECT deptno, COUNT(empno) AS number,AVG(sal) ASaverageFROM t_employee GROUP BY deptno) ASeWHERE d.deptno=e.deptno;