MySQL查询

1
2
3
4
5
6
7
select *
from emp, dept, (select * from emp)
where
group by
having
order by
limit

多表查询分类

  • 合并结果集(了解)
  • 连接查询
  • 子查询

合并结果集

要求被合并的表中,列的类型和列数相同

  • 不去除重复行

    1
    2
    3
    SELECT * FROM1
    UNION ALL
    SELECT * FROM2;
  • 去除重复行

    1
    2
    3
    SELECT * FROM1
    UNION
    SELECT * FROM2;

连接查询

各种内连接,外链接关系图

图片参考自此处

MySql不支持全外连接,需要用UNION来模拟实现

分类

  • 内连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接(MySQL不支持)
  • 自然连接(属于一种简化方式,将ON子句省略了

内连接

内连接查询出的所有记录都满足条件

  • 标准(ON其实和WHERE是一样的,只是标准用法惯用的格式)

    1
    2
    3
    4
    SELECT * 
    FROM1 别名1
    INNER JOIN2 别名2
    ON 别名1.xx=别名2.xx
  • 方言(最好别用)

    1
    2
    3
    SELECT * 
    FROM1 别名1, 表2 别名2
    WHERE 别名1.xx=别名2.xx
  • 自然

    1
    2
    3
    SELECT * 
    FROM1 别名1
    NATURAL JOIN2 别名2

外连接

  • 左外连接
    (左表记录无论是否满足条件都会查询出来,而右表只有满足条件才能出来。左表中不满足条件的记录,右表部分都为NULL)

    1
    2
    3
    4
    SELECT * 
    FROM1 别名1
    LEFT [OUTER] JOIN2 别名2
    ON 别名1.xx=别名2.xx

    LEFT默认就是OUTER,所以OUTER一般省略不写

  • 左外自然

    1
    2
    3
    SELECT * 
    FROM1 别名1
    NATURAL LEFT JOIN2 别名2
  • 右外
    (右表记录无论是否满足条件都会查询出来,而左表只有满足条件才能出来。右表不满足条件的记录,其左表部分都为NULL)

    1
    2
    3
    SELECT * 
    FROM1 别名1
    RIGHT JOIN2 别名2
  • 右外自然

    1
    2
    3
    SELECT * 
    FROM1 别名1
    NATURAL RIGHT JOIN2 别名2

子查询

查询中有查询(查看select关键字的个数!)

  1. 出现的位置:

    • where后作为条件存在
    • from后作为表存在(多行多列)
  2. 条件

    • (**)单行单列:SELECT FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
    • (*)多行单列:SELECT FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)
    • ()单行多列:SELECT FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)
    • (**)多行多列:SELECT FROM 表1 别名1 , (SELECT ….) 别名2 WHERE 条件

来自网上的一个练习

连接查询, 子查询练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
/*1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/
/*
列:部门编号、部门名称、部门位置、部门人数(分组)
列:dept、emp(部门人数没有员工表不行)
条件:没有
分组条件:人数>1

部门编号、部门名称、部门位置在dept表中都有,只有部门人数需要使用emp表,使用deptno来分组得到。
我们让dept和(emp的分组查询),这两张表进行连接查询
*/
SELECT
z.*,d.dname,d.loc
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
WHERE z.deptno=d.deptno;

/**************************************************/

/*2. 列出薪金比关羽高的所有员工。*/
/*
列:所有
表:emp
条件:sal>关羽的sal,其中关羽的sal需要子查询
*/
SELECT *
FROM emp e
WHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽');

/**************************************************/

/*3. 列出所有员工的姓名及其直接上级的姓名。*/
/*
列:员工名、领导名
表:emp、emp
条件:领导.empno=员工.mgr

emp表中存在自身关联,即empno和mgr的关系。
我们需要让emp和emp表连接查询。因为要求是查询所有员工的姓名,所以不能用内连接,因为曾阿牛是BOSS,没有上级,内连接是查询不到它的。
*/
SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno;

/**************************************************/

/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
/*
列:编号、姓名、部门名称
表:emp、dept
条件:hiredate < 领导.hiredate

emp表需要查。部门名称在dept表中,所以也需要查。领导的hiredate需要查,这说明需要两个emp和一个dept连接查询
即三个表连接查询
*/
SELECT e.empno, e.ename, d.dname
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno
LEFT JOIN dept d ON e.deptno=d.deptno
WHERE e.hiredate<m.hiredate;

/**************************************************/

/*5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。*/
/*
列:员工表所有列、部门名称
表:emp, dept
要求列出没有员工的部门,这说明需要以部门表为主表使用外连接
*/
SELECT e.*, d.dname
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno;

/**************************************************/

/*6. 列出所有文员的姓名及其部门名称,部门的人数。*/
/*
列:姓名、部门名称、部门人数
表:emp emp dept
条件:job=文员
分组:emp以deptno得到部门人数
连接:emp连接emp分组,再连接dept
*/
SELECT e.ename, d.dname, z.cnt
FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d
WHERE e.deptno=d.deptno AND z.deptno=d.deptno;

/**************************************************/

/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/
/*
列:工作,该工作人数
表:emp
分组:使用job分组
分组条件:min(sal)>15000
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000;

/**************************************************/

/*8. 列出在销售部工作的员工的姓名,假定不知道销售部的部门编号。*/
/*
列:姓名
表:emp, dept
条件:所在部门名称为销售部,这需要通过部门名称查询为部门编号,作为条件
*/
SELECT e.ename
FROM emp e
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');

/**************************************************/

/*9. 列出薪金高于公司平均薪金的所有员工信息,所在部门名称,上级领导,工资等级。*/
/*
列:员工所有信息(员工表),部门名称(部门表),上级领导(员工表),工资等级(等级表)
表:emp, dept, emp, salgrade
条件:sal>平均工资,子查询
所有员工,说明需要左外
*/
SELECT e.*, d.dname, m.ename, s.grade
FROM emp e
NATURAL LEFT JOIN dept d
LEFT JOIN emp m ON m.empno=e.mgr
LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp);

/**************************************************/

/*10.列出与庞统从事相同工作的所有员工及部门名称。*/
/*
列:员工表所有列,部门表名称
表:emp, dept
条件:job=庞统的工作,需要子查询,与部门表连接得到部门名称
*/
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');

/**************************************************/

/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/
/*
列:姓名、薪金、部门名称(需要连接查询)
表:emp, dept
条件:sal > all(30部门薪金),需要子查询
*/

SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30);

/**************************************************/

/*12.列出在每个部门工作的员工数量、平均工资。*/
/*
列:部门名称, 部门员工数,部门平均工资
表:emp, dept
分组:deptno
*/
SELECT d.dname, e.cnt, e.avgsal
FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;

0%