MySQL notes 5
1 | /* Author: Chenhui Wang |
这节的东西比较重要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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189-- between 语句
select name from instructor where salary between 100 and 100000; -- 省去了<= 和>= 逻辑运算
-- 集合运算: union(并), intersect(交), except(补)
-- 求并集
(select course_id from section where semester = "Fall" and year = 2009)
union
(select course_id from section where semester = "Spring" and year = 2010);
/*
注意，union自动去除重复
result
+-----------+
| course_id |
+-----------+
| CS-101 |
| CS-347 |
| PHY-001 |
| CS-315 |
| CS-319 |
| FIN-201 |
| HIS-351 |
| MU-119 |
+-----------+
*/
-- 用union all 代替 union 即可得到全部结果，包括重复的
-- 求交集，但是MySQL没有intersect关键字，可以用其他方法实现
create table result select course_id from section where semester = "Fall" and year = 2009;
create table result2 select course_id from section where semester = "Spring" and year = 2010;
select course_id from result inner join result2 using (course_id) --引入了using用法
/*
result table
+-----------+
| course_id |
+-----------+
| CS-101 |
| CS-315 |
| CS-319 |
| CS-319 |
| FIN-201 |
| HIS-351 |
| MU-119 |
+-----------+
result2 table
+-----------+
| course_id |
+-----------+
| CS-101 |
| CS-347 |
| PHY-001 |
+-----------+
等效交集运算后的结果
+-----------+
| course_id |
+-----------+
| CS-101 |
+-----------+
*/
-- using格式:join..using (A1, A2...An)，作用是给定属性表Ai，必须在join的两个表中都存在这个属性并且mutually相等。
select course_id from result inner join result2 using (course_id); -- 也能得到上述结果。
-- 补集实现，同样MySQL没有except，只能通过别的来实现。比如求result2关于result的补集，即result2有result1没有
select a.course_id from result2 as a left join result as b on a.course_id = b.course_id where b.course_id is null;
/*
+-----------+
| course_id |
+-----------+
| CS-347 |
| PHY-001 |
+-----------+
*/
-- 聚集函数有五个，AVG(), MIN(), MAX(), SUM(), COUNT();
select count(distinct ID) from teaches where semester = "Spring" and year = 2010;
select dept_name, avg(salary) as avg_salary from instructor; -- 算整体的avg_salary
select dept_name, avg(salary) as avg_salary from instructor group by dept_name; -- 分别算每个department 的avg_salary, 这是group by的作用
-- 下面这条命令是统计各系在2010年春季只教一门课的人数
select dept_name, count(distinct ID) from instructor natural join teaches where semester = "Spring" and year = 2010 group by dept_name;
-- Important! select 后面如果跟聚集函数的话，另一个则必须在group by后面，像上面的dept_name一样，否则会报错
-- having 语句，不针对单个元组，而是针对group by后的组；
select dept_name, avg(salary) from instructor having avg(salary) > 42000; -- 和上面分组一样，只是又筛选了系平均工资大于42000的。再一次印证了不是针对单个元组
-- 空值NULL对聚集函数的影响：所有聚集函数除了count(*)以外，都忽略输入中的NULL值。所以如果输入都是NULL，就会出现sum(NULL)的情况。规定count(NULL)为0，其他聚集函数都会返回NULL；
select sum(salary) from instructor; -- 假设salary中有NULL值，SQL并不认为sum(salary)为NULL，而是认为sum函数应该忽略salary中的NULL值
-- 嵌套子查询！！！！！！！！select from (select from (select from......))
-- 用in 或者 not in来实现嵌套。 in测试元组是否是集合中的成员(集合是由select产生的)
-- 例：找出2009年秋季和2010年春季同时开学的所有课程
-- 第一种方法，用交集。先找2009年秋季开学，再找2010年春季开学，求交集
select course_id from result natural join result2; -- done
-- 第二种方法，用嵌套子查询
select distinct course_id from section where semester = "Fall" and year = 2009
and course_id in (select course_id from section where semester = "Spring" and year = 2010); -- done;
-- 集合的比较
select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = "Biology"; -- 从instructor中挑出来比生物系工资至少多的人
select name from instructor where salary > some (select salary from instructor where dept_name = "Biology") -- 与上面的等价
-- 当然，也有< some, <= some, >= some, <> some, < all. all对应比所有的都大
select dept_name from instructor group by dept_name
having max(salary) > all (select max(salary) from instructor group by dept_name); -- 从instructor中选出平均工资最大的系
-- 相关子查询：来自外层查询的一个相关名称S，可以用在where中的子查询S.course_id = T.course_id
select course_id from section as S where semester = "Fall" and year = 2009
and exists (select * from section as T where semester = "Spring" and year = 2010 and S.course_id = T.course_id); --麻烦啊
-- from子句中的子查询，感觉还是用having更简单一些
select dept_name, asshole from (select dept_name, avg(salary) as asshole from instructor group by dept_name)
as T where asshole > 4200; -- 注意！！from () as T 一定要有后面的as T. 否则会报Error: Every derived table must have its own alias
-- 用from或者where子查询会使代码变得难懂，下面介绍with的用法。
with max_budget(value) as (select max(budget) from department) select budget from department, max_budget where department.budget = max_budget.value; -- 选择最大budget的系
-- 定义了临时变量max_budget，并且有值value。
with dept_total(dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name),
dept_total_avg(value) as (select avg(value) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.value >= dept_total_avg.value;
-- ...........把上面这个改成子查询
/*
Appendix
Instructor table
+-------+------------+-----------+----------+
| ID | name | dept_name | salary |
+-------+------------+-----------+----------+
| 10101 | Srinivasan | Comp.Sci. | 65000.00 |
| 12121 | Wu | Finance | 90000.00 |
| 15151 | Mozart | Music | 40000.00 |
| 22222 | Einstein | Physics | 95000.00 |
| 32343 | El Said | History | 60000.00 |
| 33456 | Gold | Physics | 87000.00 |
| 45565 | Katz | Comp.Sci. | 75000.00 |
| 58583 | Califieri | History | 62000.00 |
| 76543 | Singh | Finance | 80000.00 |
| 83821 | Brandt | Comp.Sci. | 92000.00 |
| 98345 | Kim | Elec.Eng. | 80000.00 |
+-------+------------+-----------+----------+
Course table
+-----------+---------------------------+-----------+---------+
| course_id | title | dept_name | credits |
+-----------+---------------------------+-----------+---------+
| BIO-101 | Intro.to Biology | Biology | 4 |
| BIO-301 | Genetics | Biology | 4 |
| BIO-399 | Computational Biology | Biology | 3 |
| CS-101 | Intro.to Computer Science | Comp.Sci. | 4 |
| CS-190 | Game Design | Comp.Sci. | 4 |
| CS-315 | Robotics | Comp.Sci. | 3 |
| CS-319 | Image Processing | Comp.Sci. | 3 |
| CS-347 | Database System Concepts | Comp.Sci. | 3 |
| EE-181 | Intro.to Digital Systems | Elec.Eng. | 3 |
| FIN-201 | Investment Banking | Finance | 3 |
| HIS-351 | World History | History | 3 |
| MU-119 | Music Video Production | Music | 3 |
| PHY-001 | Physical Principles | Physics | 4 |
+-----------+---------------------------+-----------+---------+
teaches table
+-------+-----------+--------+----------+------+
| ID | course_id | sec_id | semester | year |
+-------+-----------+--------+----------+------+
| 10101 | CS-101 | 1 | Fall | 2009 |
| 45565 | CS-101 | 1 | Spring | 2010 |
| 83821 | CS-190 | 1 | Spring | 2009 |
| 83821 | CS-190 | 2 | Spring | 2009 |
| 10101 | CS-315 | 1 | Spring | 2010 |
| 45565 | CS-319 | 1 | Spring | 2010 |
| 10101 | CS-347 | 1 | Fall | 2009 |
| 10101 | EE-181 | 1 | Spring | 2009 |
| 98345 | EE-181 | 1 | Spring | 2009 |
| 12121 | FIN-201 | 1 | Spring | 2010 |
| 32343 | HIS-351 | 1 | Spring | 2010 |
| 15151 | MU-119 | 1 | Spring | 2010 |
| 22222 | PHY-001 | 1 | Fall | 2009 |
+-------+-----------+--------+----------+------+
department table
+-----------+----------+-----------+
| dept_name | building | budget |
+-----------+----------+-----------+
| Biology | Watson | 90000.00 |
| Comp.Sci. | Taylor | 100000.00 |
| Elec.Eng. | Taylor | 85000.00 |
| Finance | Painter | 120000.00 |
| History | Painter | 50000.00 |
| Music | Packard | 80000.00 |
| Physics | Watson | 70000.00 |
+-----------+----------+-----------+
*/