前言：
一：数据准备
员工表emp 和部门表 dept
注意：我在录入员工表的时候，特意添加了两条没有部门的员工，他们的部门id对应为null;
--分别创建部门和员工表，并实现一对多关系
DROP TABLE dept;
CREATE TABLE `dept`(
`d_id` INT(5) PRIMARY KEY AUTO_INCREMENT COMMENT '这是部门id主键自增长',
`d_name` VARCHAR(30) NOT NULL UNIQUE COMMENT '这是部门名称唯一非空'
); CREATE TABLE `emp`(
`e_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '这是员工表的主键自增长',
`e_uname` VARCHAR(10) NOT NULL UNIQUE COMMENT '这是登录名',
`e_name` VARCHAR(20) NOT NULL COMMENT '这是真实姓名',
`e_pwd` VARCHAR(20) DEFAULT '' COMMENT '这是密码默认为123456',
`e_d_id` INT(5),
CONSTRAINT `emp_fk` FOREIGN KEY (`e_d_id`) REFERENCES dept (`d_id`)
); --准备数据
--先给dept录入测试数据
INSERT INTO dept VALUES(NULL,'财务部'),(NULL,'技术部'),(NULL,'营销部'),(NULL,'采购部'),(NULL,'市场部');
INSERT INTO dept VALUES(NULL,'行政部'),(NULL,'后勤部'),(NULL,'总经办'),(NULL,'人事部'),(NULL,'策划部');
--给emp录入测试数据
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('lqd','刘强东',20);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('mht','马化腾',17);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('my','马云',8);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('lyf','刘亦菲',16);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('wjl','王健林',9);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('zzt','章泽天',17);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('ldh','刘德华',10);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('dl','丁磊',16);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('zhy','周鸿祎',18);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('zzy','章子怡',9);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('abm','奥巴马',19);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('zjl','周杰伦',7);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('zcy','张朝阳',16);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('zj','周洁',19);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('syp','苏有朋',20);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('fxg','冯小刚',20);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('wsc','王思聪',10);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('hxn','韩晓诺',19);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('zy','曾洋',7);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('hh','韩寒',6);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('mbp','姆巴佩',17);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('gjm','郭敬明',7);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('wsc','王宝强',10);
INSERT INTO emp (e_uname,e_name,e_d_id)VALUES('xjy','许家印',6);
INSERT INTO emp (e_uname,e_name)VALUES('wyf','吴亦凡');
INSERT INTO emp (e_uname,e_name)VALUES('zly','赵丽颖');
数据准备 emp dept
二：多表查询
2.1：取别名
当同时查询两个表时，每次写表名和字段名比较麻烦，可以给表娶一个别名 det AS d 字段 d_name就可以表示成d.d_name
给字段其别名有助于显示的更加清楚
2.1参考代码：
mysql> select * from emp AS e;
+------+---------+--------+--------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id |
+------+---------+--------+--------+--------+
| 1 | lqd | 刘强东 | 123456 | 20 |
| 2 | mht | 马化腾 | 123456 | 17 |
| 3 | my | 马云 | 123456 | 8 |
| 4 | lyf | 刘亦菲 | 123456 | 16 |
| 5 | wjl | 王健林 | 123456 | 9 |
| 6 | zzt | 章泽天 | 123456 | 17 |
| 7 | ldh | 刘德华 | 123456 | 10 |
| 8 | dl | 丁磊 | 123456 | 16 |
| 9 | zhy | 周鸿祎 | 123456 | 18 |
| 10 | zzy | 章子怡 | 123456 | 9 |
| 11 | abm | 奥巴马 | 123456 | 19 |
| 12 | zjl | 周杰伦 | 123456 | 7 |
| 13 | zcy | 张朝阳 | 123456 | 16 |
| 14 | zj | 周洁 | 123456 | 19 |
| 15 | syp | 苏有朋 | 123456 | 20 |
| 16 | fxg | 冯小刚 | 123456 | 20 |
| 17 | wsc | 王思聪 | 123456 | 10 |
| 18 | hxn | 韩晓诺 | 123456 | 19 |
| 19 | zy | 曾洋 | 123456 | 7 |
| 20 | hh | 韩寒 | 123456 | 6 |
| 21 | mbp | 姆巴佩 | 123456 | 17 |
| 22 | gjm | 郭敬明 | 123456 | 7 |
| 24 | xjy | 许家印 | 123456 | 6 |
+------+---------+--------+--------+--------+
23 rows in set (0.00 sec) mysql> select * from dept;
+------+--------+
| d_id | d_name |
+------+--------+
| 19 | 人事部 |
| 17 | 后勤部 |
| 10 | 市场部 |
| 18 | 总经办 |
| 7 | 技术部 |
| 20 | 策划部 |
| 8 | 营销部 |
| 16 | 行政部 |
| 6 | 财务部 |
| 9 | 采购部 |
+------+--------+
10 rows in set (0.00 sec) mysql> select d_name as '部门名称' from dept;
+----------+
| 部门名称 |
+----------+
| 人事部 |
| 后勤部 |
| 市场部 |
| 总经办 |
| 技术部 |
| 策划部 |
| 营销部 |
| 行政部 |
| 财务部 |
| 采购部 |
+----------+
10 rows in set (0.00 sec) mysql> SELECT e.e_uname,e.e_pwd FROM emp e;
+---------+--------+
| e_uname | e_pwd |
+---------+--------+
| lqd | 123456 |
| mht | 123456 |
| my | 123456 |
| lyf | 123456 |
| wjl | 123456 |
| zzt | 123456 |
| ldh | 123456 |
| dl | 123456 |
| zhy | 123456 |
| zzy | 123456 |
| abm | 123456 |
| zjl | 123456 |
| zcy | 123456 |
| zj | 123456 |
| syp | 123456 |
| fxg | 123456 |
| wsc | 123456 |
| hxn | 123456 |
| zy | 123456 |
| hh | 123456 |
| mbp | 123456 |
| gjm | 123456 |
| xjy | 123456 |
+---------+--------+
23 rows in set (0.00 sec)
起别名查询
2.2：普通双表查询
又称为隐式内连接，下面会讲
查询员工信息要求显示其所在部门（在公司部门中的员工）
2.2参考代码：
mysql> SELECT * FROM emp e,dept d WHERE e.e_d_id=d.d_id;
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 11 | abm | 奥巴马 | 123456 | 19 | 19 | 人事部 |
| 14 | zj | 周洁 | 123456 | 19 | 19 | 人事部 |
| 18 | hxn | 韩晓诺 | 123456 | 19 | 19 | 人事部 |
| 2 | mht | 马化腾 | 123456 | 17 | 17 | 后勤部 |
| 6 | zzt | 章泽天 | 123456 | 17 | 17 | 后勤部 |
| 21 | mbp | 姆巴佩 | 123456 | 17 | 17 | 后勤部 |
| 7 | ldh | 刘德华 | 123456 | 10 | 10 | 市场部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
| 9 | zhy | 周鸿祎 | 123456 | 18 | 18 | 总经办 |
| 12 | zjl | 周杰伦 | 123456 | 7 | 7 | 技术部 |
| 19 | zy | 曾洋 | 123456 | 7 | 7 | 技术部 |
| 22 | gjm | 郭敬明 | 123456 | 7 | 7 | 技术部 |
| 1 | lqd | 刘强东 | 123456 | 20 | 20 | 策划部 |
| 15 | syp | 苏有朋 | 123456 | 20 | 20 | 策划部 |
| 16 | fxg | 冯小刚 | 123456 | 20 | 20 | 策划部 |
| 3 | my | 马云 | 123456 | 8 | 8 | 营销部 |
| 4 | lyf | 刘亦菲 | 123456 | 16 | 16 | 行政部 |
| 8 | dl | 丁磊 | 123456 | 16 | 16 | 行政部 |
| 13 | zcy | 张朝阳 | 123456 | 16 | 16 | 行政部 |
| 20 | hh | 韩寒 | 123456 | 6 | 6 | 财务部 |
| 24 | xjy | 许家印 | 123456 | 6 | 6 | 财务部 |
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 10 | zzy | 章子怡 | 123456 | 9 | 9 | 采购部 |
+------+---------+--------+--------+--------+------+--------+
23 rows in set (0.00 sec)
普通查询
查询结果分析：将员工表中目前自己有部门的数据全部都查出来了
没有部门的员工并没有查出来
2.3：内连接查询
使用关键字 INNER JOIN ---INNER 可以省略
内连接：select * from A, inner B join on 条件
2.3内连接查询：
---DOS命令下的查询结果
mysql> SELECT * FROM emp e INNER JOIN dept d ON e.e_d_id=d.d_
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 11 | abm | 奥巴马 | 123456 | 19 | 19 | 人事部 |
| 14 | zj | 周洁 | 123456 | 19 | 19 | 人事部 |
| 18 | hxn | 韩晓诺 | 123456 | 19 | 19 | 人事部 |
| 2 | mht | 马化腾 | 123456 | 17 | 17 | 后勤部 |
| 6 | zzt | 章泽天 | 123456 | 17 | 17 | 后勤部 |
| 21 | mbp | 姆巴佩 | 123456 | 17 | 17 | 后勤部 |
| 7 | ldh | 刘德华 | 123456 | 10 | 10 | 市场部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
| 9 | zhy | 周鸿祎 | 123456 | 18 | 18 | 总经办 |
| 12 | zjl | 周杰伦 | 123456 | 7 | 7 | 技术部 |
| 19 | zy | 曾洋 | 123456 | 7 | 7 | 技术部 |
| 22 | gjm | 郭敬明 | 123456 | 7 | 7 | 技术部 |
| 1 | lqd | 刘强东 | 123456 | 20 | 20 | 策划部 |
| 15 | syp | 苏有朋 | 123456 | 20 | 20 | 策划部 |
| 16 | fxg | 冯小刚 | 123456 | 20 | 20 | 策划部 |
| 3 | my | 马云 | 123456 | 8 | 8 | 营销部 |
| 4 | lyf | 刘亦菲 | 123456 | 16 | 16 | 行政部 |
| 8 | dl | 丁磊 | 123456 | 16 | 16 | 行政部 |
| 13 | zcy | 张朝阳 | 123456 | 16 | 16 | 行政部 |
| 20 | hh | 韩寒 | 123456 | 6 | 6 | 财务部 |
| 24 | xjy | 许家印 | 123456 | 6 | 6 | 财务部 |
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 10 | zzy | 章子怡 | 123456 | 9 | 9 | 采购部 |
+------+---------+--------+--------+--------+------+--------+
23 ROWS IN SET (0.00 sec) --省略了INNER
mysql> SELECT * FROM emp e JOIN dept d ON e.e_d_id=d.d_id;
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 11 | abm | 奥巴马 | 123456 | 19 | 19 | 人事部 |
| 14 | zj | 周洁 | 123456 | 19 | 19 | 人事部 |
| 18 | hxn | 韩晓诺 | 123456 | 19 | 19 | 人事部 |
| 2 | mht | 马化腾 | 123456 | 17 | 17 | 后勤部 |
| 6 | zzt | 章泽天 | 123456 | 17 | 17 | 后勤部 |
| 21 | mbp | 姆巴佩 | 123456 | 17 | 17 | 后勤部 |
| 7 | ldh | 刘德华 | 123456 | 10 | 10 | 市场部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
| 9 | zhy | 周鸿祎 | 123456 | 18 | 18 | 总经办 |
| 12 | zjl | 周杰伦 | 123456 | 7 | 7 | 技术部 |
| 19 | zy | 曾洋 | 123456 | 7 | 7 | 技术部 |
| 22 | gjm | 郭敬明 | 123456 | 7 | 7 | 技术部 |
| 1 | lqd | 刘强东 | 123456 | 20 | 20 | 策划部 |
| 15 | syp | 苏有朋 | 123456 | 20 | 20 | 策划部 |
| 16 | fxg | 冯小刚 | 123456 | 20 | 20 | 策划部 |
| 3 | my | 马云 | 123456 | 8 | 8 | 营销部 |
| 4 | lyf | 刘亦菲 | 123456 | 16 | 16 | 行政部 |
| 8 | dl | 丁磊 | 123456 | 16 | 16 | 行政部 |
| 13 | zcy | 张朝阳 | 123456 | 16 | 16 | 行政部 |
| 20 | hh | 韩寒 | 123456 | 6 | 6 | 财务部 |
| 24 | xjy | 许家印 | 123456 | 6 | 6 | 财务部 |
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 10 | zzy | 章子怡 | 123456 | 9 | 9 | 采购部 |
+------+---------+--------+--------+--------+------+--------+
23 ROWS IN SET (0.00 sec)
--分析--
--效果和普通查询一样，只是查询方式不同
--因此，普通查询又称为隐式内连接查询
内连接查询，及总结
2.4：外连接查询
使用关键字 OUTER JOIN--OUTER 可以省略
2.4.1：左外连接：select * from A left join B on 条件
2.4.1左外连接参考：
SELECT * FROM emp e LEFT OUTER JOIN dept d ON d.d_id=e.e_d_id;
--这里我在dept表中添加了一个部门 预算部，但是员工中没有人在这个部门
INSERT INTO dept VALUES(NULL,'预算部'); --DOS命令下使用左外连接的查询结果
mysql> SELECT * FROM emp e LEFT OUTER JOIN dept d ON d.d_id=e.e_d_id;
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 1 | lqd | 刘强东 | 123456 | 20 | 20 | 策划部 |
| 2 | mht | 马化腾 | 123456 | 17 | 17 | 后勤部 |
| 3 | my | 马云 | 123456 | 8 | 8 | 营销部 |
| 4 | lyf | 刘亦菲 | 123456 | 16 | 16 | 行政部 |
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 6 | zzt | 章泽天 | 123456 | 17 | 17 | 后勤部 |
| 7 | ldh | 刘德华 | 123456 | 10 | 10 | 市场部 |
| 8 | dl | 丁磊 | 123456 | 16 | 16 | 行政部 |
| 9 | zhy | 周鸿祎 | 123456 | 18 | 18 | 总经办 |
| 10 | zzy | 章子怡 | 123456 | 9 | 9 | 采购部 |
| 11 | abm | 奥巴马 | 123456 | 19 | 19 | 人事部 |
| 12 | zjl | 周杰伦 | 123456 | 7 | 7 | 技术部 |
| 13 | zcy | 张朝阳 | 123456 | 16 | 16 | 行政部 |
| 14 | zj | 周洁 | 123456 | 19 | 19 | 人事部 |
| 15 | syp | 苏有朋 | 123456 | 20 | 20 | 策划部 |
| 16 | fxg | 冯小刚 | 123456 | 20 | 20 | 策划部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
| 18 | hxn | 韩晓诺 | 123456 | 19 | 19 | 人事部 |
| 19 | zy | 曾洋 | 123456 | 7 | 7 | 技术部 |
| 20 | hh | 韩寒 | 123456 | 6 | 6 | 财务部 |
| 21 | mbp | 姆巴佩 | 123456 | 17 | 17 | 后勤部 |
| 22 | gjm | 郭敬明 | 123456 | 7 | 7 | 技术部 |
| 24 | xjy | 许家印 | 123456 | 6 | 6 | 财务部 |
| 25 | wyf | 吴亦凡 | 123456 | NULL | NULL | NULL |
| 26 | zly | 赵丽颖 | 123456 | NULL | NULL | NULL |
+------+---------+--------+--------+--------+------+--------+
25 ROWS IN SET (0.00 sec)
--结果分析
--一共25条结果
--左边表中不符合要求的数据也查了出来，两个没有部门的员工也查了出来
--右边表中不符合要求的数据并没有被查出来,预算部并不在查询结果中
左外连接 总结
2.4.2：右外连接：select * from A right join B on 条件
2.4.2右外连接参考：
--DOS命令下使用右外连接的查询结果
mysql> SELECT * FROM emp e RIGHT OUTER JOIN dept d ON d.d_id=e.e_d_id;
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 11 | abm | 奥巴马 | 123456 | 19 | 19 | 人事部 |
| 14 | zj | 周洁 | 123456 | 19 | 19 | 人事部 |
| 18 | hxn | 韩晓诺 | 123456 | 19 | 19 | 人事部 |
| 2 | mht | 马化腾 | 123456 | 17 | 17 | 后勤部 |
| 6 | zzt | 章泽天 | 123456 | 17 | 17 | 后勤部 |
| 21 | mbp | 姆巴佩 | 123456 | 17 | 17 | 后勤部 |
| 7 | ldh | 刘德华 | 123456 | 10 | 10 | 市场部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
| 9 | zhy | 周鸿祎 | 123456 | 18 | 18 | 总经办 |
| 12 | zjl | 周杰伦 | 123456 | 7 | 7 | 技术部 |
| 19 | zy | 曾洋 | 123456 | 7 | 7 | 技术部 |
| 22 | gjm | 郭敬明 | 123456 | 7 | 7 | 技术部 |
| 1 | lqd | 刘强东 | 123456 | 20 | 20 | 策划部 |
| 15 | syp | 苏有朋 | 123456 | 20 | 20 | 策划部 |
| 16 | fxg | 冯小刚 | 123456 | 20 | 20 | 策划部 |
| 3 | my | 马云 | 123456 | 8 | 8 | 营销部 |
| 4 | lyf | 刘亦菲 | 123456 | 16 | 16 | 行政部 |
| 8 | dl | 丁磊 | 123456 | 16 | 16 | 行政部 |
| 13 | zcy | 张朝阳 | 123456 | 16 | 16 | 行政部 |
| 20 | hh | 韩寒 | 123456 | 6 | 6 | 财务部 |
| 24 | xjy | 许家印 | 123456 | 6 | 6 | 财务部 |
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 10 | zzy | 章子怡 | 123456 | 9 | 9 | 采购部 |
| NULL | NULL | NULL | NULL | NULL | 22 | 预算部 |
+------+---------+--------+--------+--------+------+--------+
24 ROWS IN SET (0.00 sec)
--结果分析
--一共24条记录，
--右边表中的数据不符合要求的也查了出来，因为没有员工在预算部
--左边表中值有符合条件的记录被查了出来，两个没有部门的员工不在结果中
右外连接 总结
外连接查询总结
左外连接：将符合条件的数据都查出来，然后把不符合条件的左边的表中的数据也查出来
右外链接：将符合条件的数据都查出来，然后把不符合条件的又边的表中的数据也查出来
2.5：复合查询：
使用 WHERE、AND、LIKE、GROUP BY、 HAVING 、ORDER BY 等条件组合对查询结果进行过滤
2.5复合查询代码练习
--查询出在我们公司部门的，并且用户名是w开头的员工和其部门信息
SELECT * FROM emp e JOIN dept d ON e.e_d_id=d.d_id WHERE e.e_uname LIKE 'w%'; mysql> SELECT * FROM emp e JOIN dept d ON e.e_d_id=d.d_id WHERE e.e_uname LIKE 'w%';
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
+------+---------+--------+--------+--------+------+--------+
2 ROWS IN SET (0.00 sec) --或者：
mysql> SELECT * FROM emp e JOIN dept d ON e.e_d_id=d.d_id AND e.e_uname LIKE 'w%';
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
+------+---------+--------+--------+--------+------+--------+
2 ROWS IN SET (0.00 sec) --查询出员工用户名是w开头的信息及其部门信息
mysql> SELECT * FROM emp e LEFT JOIN dept d ON e.e_d_id=d.d_id WHERE e.e_uname LIKE 'w%';
+------+---------+--------+--------+--------+------+--------+
| e_id | e_uname | e_name | e_pwd | e_d_id | d_id | d_name |
+------+---------+--------+--------+--------+------+--------+
| 5 | wjl | 王健林 | 123456 | 9 | 9 | 采购部 |
| 17 | wsc | 王思聪 | 123456 | 10 | 10 | 市场部 |
| 25 | wyf | 吴亦凡 | 123456 | NULL | NULL | NULL |
+------+---------+--------+--------+--------+------+--------+
3 ROWS IN SET (0.00 sec) --查询出公司所有部门 及其部门员工情况
SELECT * FROM dept d LEFT JOIN emp e ON e.e_d_id=d.d_id;
mysql> SELECT * FROM dept d LEFT JOIN emp e ON e.e_d_id=d.d_id;
+------+--------+------+---------+--------+--------+--------+
| d_id | d_name | e_id | e_uname | e_name | e_pwd | e_d_id |
+------+--------+------+---------+--------+--------+--------+
| 19 | 人事部 | 11 | abm | 奥巴马 | 123456 | 19 |
| 19 | 人事部 | 14 | zj | 周洁 | 123456 | 19 |
| 19 | 人事部 | 18 | hxn | 韩晓诺 | 123456 | 19 |
| 17 | 后勤部 | 2 | mht | 马化腾 | 123456 | 17 |
| 17 | 后勤部 | 6 | zzt | 章泽天 | 123456 | 17 |
| 17 | 后勤部 | 21 | mbp | 姆巴佩 | 123456 | 17 |
| 10 | 市场部 | 7 | ldh | 刘德华 | 123456 | 10 |
| 10 | 市场部 | 17 | wsc | 王思聪 | 123456 | 10 |
| 18 | 总经办 | 9 | zhy | 周鸿祎 | 123456 | 18 |
| 7 | 技术部 | 12 | zjl | 周杰伦 | 123456 | 7 |
| 7 | 技术部 | 19 | zy | 曾洋 | 123456 | 7 |
| 7 | 技术部 | 22 | gjm | 郭敬明 | 123456 | 7 |
| 20 | 策划部 | 1 | lqd | 刘强东 | 123456 | 20 |
| 20 | 策划部 | 15 | syp | 苏有朋 | 123456 | 20 |
| 20 | 策划部 | 16 | fxg | 冯小刚 | 123456 | 20 |
| 8 | 营销部 | 3 | my | 马云 | 123456 | 8 |
| 16 | 行政部 | 4 | lyf | 刘亦菲 | 123456 | 16 |
| 16 | 行政部 | 8 | dl | 丁磊 | 123456 | 16 |
| 16 | 行政部 | 13 | zcy | 张朝阳 | 123456 | 16 |
| 6 | 财务部 | 20 | hh | 韩寒 | 123456 | 6 |
| 6 | 财务部 | 24 | xjy | 许家印 | 123456 | 6 |
| 9 | 采购部 | 5 | wjl | 王健林 | 123456 | 9 |
| 9 | 采购部 | 10 | zzy | 章子怡 | 123456 | 9 |
| 22 | 预算部 | NULL | NULL | NULL | NULL | NULL |
+------+--------+------+---------+--------+--------+--------+
24 ROWS IN SET (0.00 sec) --统计出部门员工数大于等于3人的所有部门
SELECT COUNT(*) AS 部门总人数,d.d_id,d.d_name,GROUP_CONCAT(e.e_name) AS 部门内员工姓名
FROM dept d LEFT JOIN emp e
ON e.e_d_id=d.d_id
GROUP BY d.d_name
HAVING COUNT(*) >=3; --DOS命令行执行结果
mysql> SELECT COUNT(*) AS 部门总人数,d.d_id,d.d_name,GROUP_CONCAT(e.e_name) AS
部门内员工姓名 FROM dept d LEFT JOIN emp e ON e.e_d_id=d.d_id GROUP BY d.d_name
HAVING COUNT(*) >=3;
+------------+------+--------+----------------------+
| 部门总人数 | d_id | d_name | 部门内员工姓名 |
+------------+------+--------+----------------------+
| 3 | 19 | 人事部 | 奥巴马,周洁,韩晓诺 |
| 3 | 17 | 后勤部 | 马化腾,章泽天,姆巴佩 |
| 3 | 7 | 技术部 | 周杰伦,曾洋,郭敬明 |
| 3 | 20 | 策划部 | 刘强东,苏有朋,冯小刚 |
| 3 | 16 | 行政部 | 刘亦菲,丁磊,张朝阳 |
+------------+------+--------+----------------------+
5 ROWS IN SET (0.00 sec) --语句执行顺序分析
--1：执行表dept和emp的笛卡尔积
--2：执行ON条件判断
--3：执行ORDER BY*过滤
--4：执行HAVING 条件判断
多条件复合查询
复合查询总结：
写sql的时候关注每一步产生的临时表的状态
注意好sql的执行顺序就可以了
2.6：子查询
将查询一张表得到的结果来充当另一个查询的条件
2.6.1：带ANY、SOME、ALL关键字的子查询
ANY和SOME相同都表与子查询中任意一个结果比较为TRUE则符合查询条件
ALL表示满足所有条件
2.6.1代码参考
-----子查询练习数据准备
CREATE TABLE tb1(
num INT(5)
); CREATE TABLE tb2(
num INT(5)
);
INSERT INTO tb1 VALUES (12),(43),(-35),(41),(39);
INSERT INTO tb2 VALUES (25),(-16),(8),(41),(17); --ANY SOME ALL
--ANY(SQL语句) 结果的最小值
--SOME(SQL语句) 结果的最小值 SELECT num AS num_tb1 FROM tb1 WHERE num > ANY(SELECT num FROM tb2); --DOS运行结果
mysql> SELECT num AS num_tb1 FROM tb1 WHERE num > ANY(SELECT num FROM tb2);
+---------+
| num_tb1 |
+---------+
| 12 |
| 43 |
| 41 |
| 39 |
+---------+
4 ROWS IN SET (0.00 sec) SELECT num AS num_tb1 FROM tb1 WHERE num > SOME(SELECT num FROM tb2); --DOS运行结果
mysql> SELECT num AS num_tb1 FROM tb1 WHERE num > SOME(SELECT num FROM tb2);
+---------+
| num_tb1 |
+---------+
| 12 |
| 43 |
| 41 |
| 39 |
+---------+
4 ROWS IN SET (0.00 sec) --ALL(SQL语句) 结果的最大值
SELECT num AS num_tb1 FROM tb1 WHERE num >= ALL(SELECT num FROM tb2); --DOS运行结果
mysql> SELECT num AS num_tb1 FROM tb1 WHERE num >= ALL(SELECT num FROM tb2);
+---------+
| num_tb1 |
+---------+
| 43 |
| 41 |
+---------+
2 ROWS IN SET (0.00 sec)
ANY SOME ALL
2.6.2：带EXISTS关键字查询
EXISTS后面的参数是任意一个子查询，
如果子查询有返回记录行，则为TRUE外层查询语句将会进行查询
如果子查询没有返回任何记录，则为FALSE，外层语句将不会执行
案例1：查询出所有有部门的员工的信息
案例2：查询出部门id为6的员工的信息
案例3：查询出员工id为3的员工的部门的信息
案例4：