• 九：SQL之DQL数据查询语言多表操作

    前言：

    一：数据准备

    　　员工表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','赵丽颖');
    数据准备 emp dept

    二：多表查询

    　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)
    ANY SOME ALL

    　　　　2.6.2：带EXISTS关键字查询

    　　　　EXISTS后面的参数是任意一个子查询，

    　　　　如果子查询有返回记录行，则为TRUE外层查询语句将会进行查询

    　　　　如果子查询没有返回任何记录，则为FALSE，外层语句将不会执行

    案例1：查询出所有有部门的员工的信息

    案例2：查询出部门id为6的员工的信息

    案例3：查询出员工id为3的员工的部门的信息

    案例4：

    　　
  • 相关阅读:
    moss文档库操作的几个类
    Infopath web浏览中的多项选择功能
    国际软件项目经理的七大素质转
    InfoPath中的Rich Text Box中如何加“回车”
    设计模式——策略模式
    设计模式——状态模式
    设计模式——备忘模式
    设计模式——中介者模式
    关与BaseDataList类型绑定ArrayList
    设计模式——命令模式
  • 原文地址：https://www.cnblogs.com/wdmlsx/p/9270123.html
Copyright © 2020-2023  润新知