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