学习MySQL之多表操作(三)
##多表查询 ##使用数据库 mytest USE mytest; ##删除,并重新创建表 t_dept DROP TABLE t_dept; CREATE TABLE t_dept ( deptno INT(11) NOT NULL, dname VARCHAR(20) NOT NULL, loc VARCHAR(40), UNIQUE INDEX uk_deptno(deptno) ) ##创建测试表 t_a CREATE TABLE t_A ( aId INT (11) AUTO_INCREMENT, ##主键,自增 aName VARCHAR (20) NOT NULL, loc VARCHAR (40), cId INT(11), PRIMARY KEY pk_aId (aId) ) ; ##创建测试表 t_b CREATE TABLE t_B ( bId INT (11) AUTO_INCREMENT, ##主键,自增 bStand VARCHAR (40) NOT NULL, aId INT (11) NOT NULL, PRIMARY KEY pk_bId (bId), UNIQUE INDEX uk_aId (aId) ##唯一索引 ) ; ##创建测试表 t_c CREATE TABLE t_c ( cId INT (11) AUTO_INCREMENT, ##主键,自增 cvalue VARCHAR (20) NOT NULL, PRIMARY KEY pk_cId (cId) ); ##创建测试表 t_d CREATE TABLE t_d( xname VARCHAR(11), xsex VARCHAR(11) ); ##创建测试表 t_e CREATE TABLE t_e( xname VARCHAR(11), xsex VARCHAR(11) ); ##删除唯一索引 ALTER TABLE t_b DROP KEY uk_aId; ##插入测试数据 INSERT INTO t_dept(deptno,dname,loc) VALUES (10,'生产','生产部'), (20,'业务','业务部'), (30,'品质','品质部'), (40,'人事','人事部'), (50,'工程','工程部'); INSERT INTO t_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 INTO t_b (bStand, aId) VALUES ('Jay-Jay Chou', 1), ('Hebe-Hebe Young', 3), ('Jack-Jack Son', 4), ('Smile-Smile Kiss', 5) ; INSERT INTO t_c (cvalue) VALUES ('鸡'),('牛'),('马'); INSERT INTO t_d (xname,xsex) VALUES ('章子怡','女'),('张筱雨','女'),('张雨涵','男'),('张予曦','女'),('张曦予','女'); INSERT INTO t_e (xname,xsex) VALUES ('张曦予','女'),('汪峰','男'),('张馨予','女'); -- ----------- 内联、外联不推荐使用,根据笛卡尔积,执行效率低于子查询 ----------- -- ##内联 □■□ ##INNER JOIN …… ON。显示主副表数据交集 SELECT a.aid,a.aName,a.loc,b.bStand,c.cvalue FROM t_a AS a INNER JOIN t_b AS b ON a.aId = b.aId INNER JOIN t_c AS c ON a.cId=c.cId; ##外联 ■■□ OR □■■ ##外联显示的数据以主表为准,不管副表是否有对应数据都会显示,副表无数据的,显示null。■■□ ##内联,副表无对应数据,则不会显示出。简而言之,显示数据为主副表的 交集。□■□ ##左联:以本表为主表,左联表为副表。■■□ SELECT a.aid,a.aName,a.loc,b.bStand FROM t_a AS a LEFT JOIN t_b AS b ON a.aid=b.aid; ##右联:以本表为副表,右联表为主表。□■■ SELECT a.aid,a.aName,a.loc,b.bStand FROM t_a AS a RIGHT JOIN t_b AS b ON 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 AS b; ##笛卡尔积 SELECT * FROM t_a WHERE cId=(SELECT cId FROM t_a WHERE aName = 'Jay'); ##结果集可以是多行 SELECT * FROM t_a WHERE (aId,loc) = ( SELECT aId,loc FROM t_a WHERE cId=3); ##结果集只能是单行 SELECT * FROM t_employee WHERE deptno IN ( SELECT deptno FROM t_dept); SELECT * FROM t_employee WHERE deptno NOT IN ( SELECT deptno FROM t_dept); ##使用ANY。在使用上,用 <= 和 >= 更有意义 ■■□ ## <ANY (<=ANY) ,结果集是比最小值(包含)大的结果。 ## >ANY (>=ANY) ,结果集是比最大值(包含)小的结果。 ## =ANY,与使用 IN 一样。 SELECT sal FROM t_employee WHERE job='SALESMAN'; SELECT * FROM t_employee WHERE sal>=ANY(SELECT sal FROM t_employee WHERE job='SALESMAN'); ##使用ALL。在使用上,用 < 和 > 更有意义 ■□□ ## <ALL (<=ALL) ,结果集是比最大值(包含)更大的结果。 ## >ALL (>=ALL) ,结果集是比最小值(包含)更小的结果。 ## =ANY,与使用 IN 一样。 SELECT sal FROM t_employee WHERE job='SALESMAN'; SELECT * FROM t_employee WHERE sal>ALL(SELECT sal FROM t_employee WHERE job='SALESMAN'); ##使用 EXISTS 与IN 的使用效果类似,以下两条SQL语句执行结果一致。 SELECT * FROM t_dept AS d WHERE EXISTS( SELECT * FROM t_employee WHERE deptno=d.deptno); SELECT * FROM t_dept AS d WHERE deptno IN( SELECT deptno FROM t_employee); -- 综合测试 -- ##计算笛卡尔积 SELECT COUNT(empno) FROM t_employee; SELECT COUNT(deptno) FROM t_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) AS average FROM t_dept AS d INNER JOIN t_employee AS e ON d.deptno = e.deptno GROUP BY d.deptno; ##使用左联,有字段为NULL,且效率低(笛卡尔积为 9X5=45) SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) AS number,AVG(e.sal) AS average FROM t_dept AS d LEFT JOIN t_employee AS e ON d.deptno=e.deptno GROUP BY d.deptno; ##使用右联,虽无字段为NULL,但无法保证其他情况没NULL,且效率低(笛卡尔积为 9X5=45) SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) AS number,AVG(e.sal) AS average FROM t_employee AS e LEFT JOIN t_dept AS d ON d.deptno=e.deptno GROUP BY d.deptno; ##使用子查询。最好为每个表派生别名,并指出每个字段是哪张表的。(防止字段之间重名) ##效率高,笛卡尔积为(4+9=13) SELECT d.deptno,d.dname,d.loc,e.number,e.average FROM t_dept AS d, (SELECT deptno, COUNT(empno) AS number,AVG(sal) AS average FROM t_employee GROUP BY deptno) AS e WHERE d.deptno=e.deptno;
所有代码,均为自学时用到的测试与注释,知识细节或知识点不会面面俱到,亦不会有任何讲解,只做为自己学习复习用。