MySQL学习笔记
行也成文 5/30/2018 mysql
# 创建数据库和表
- 创建数据库
create database <db-name>
- 创建表
create table <table-name> (
`<column-1-name>` <data-1-type>,
`<column-2-name>` <data-2-type>
)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 增删改查
- 增
insert into <table-name>(<column-1>, <column-2>) values(...)
- 删
delete from <table-name> ...
- 改
updata <table-name> set <column-name> = <new-value>
- 查
select <column> from <table-name>
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 示例
create database bsys
- 创建表Student
create table Student (
`sno` char (10) primary key,
`sname` char(20),
`ssex` char(2),
`sbirth` date,
`sdept` char(20)
)
- 创建表 Book
create table Book (
`bno` char(10) primary key,
`bname` char(30),
`author` char(20),
`publish` char(30),
`price` float
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table Student
(
sno char (10) primary key,
sname char(20),
ssex char(2),
sbirth date,
sdept char(20)
)
create table Book
(
bno char(10) primary key,
bname char(30),
author char(20),
publish char(30),
price float
)
create table Br
(
sno char(10),
bno char(10),
bdate date,
rdate date,
fun float
)
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
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
- 添加字段
alter table Book
add column pdate date
1
2
2
- 增/删/改/查数据
- 增
insert into Student
values('1001','张三','男','1998-7-12','微电系'),
('1002','李四','男','1999-5-4','计算机系'),
('1003','王麻子','女','1995-3-9','微电系')
insert into Book
values('b01','数据库',NULL,'清华大学出版社',NULL,'2001-9-15'),
('b02','c语言',NULL,'人民邮电出版社',NULL,NULL)
insert into Br
values('1002','b01','2006-11-5','2006-11-20','0')
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
- 删
delete from student
where sdept='微电系'
1
2
2
- 调用函数
insert into Br
datediff(bdate,rdate)
1
2
2
# 例二
- 第一部分
/*创建数据库*/
create table student
(
sno char (10) primary key,
sname char(20),
ssex char(2),
sage smallint,
sdept char(20)
)
create table sc
(
sno char(10),
cno char(10),
grade float,
primary key(sno,cno)
)
alter table student
add column zhuzi char(30) /*增加一列*/
/*插入数据*/
insert into student
values ('1001','张三','男',18,'行知')
insert into student
values
('1001','张军','男',18,'电气'),
('1002','李力','女',17,'计算机'),
('1003','张佳','女',19,'机械'),
('1004','宋丽琳','女',20,'电气')
/*增 数据*/
update student,sc
set student.sno='1007',sc.sno='1008'
where student.sno='1001'and sc.sno='1001'
/*查 数据*/
/*临时添加一项出生年... '..' */
select sno '学号', sname '姓名', 2018-sage '出生年'
from student
/*查询选了课的同学的学号(消除重复行)distinct*/
select distinct sno
from sc
/*查询选了课的同学的学号(全部显示)*/
select sno, cno
from sc
/*查询年龄大与19的学生的所有信息 >*/
select *
from student
where sage>19
/*查询2000年后出生的学生 >=*/
select sno,sname,2018-sage
from student
where 2018-sage>=2000
/*查询年龄17到19之间的学生 between...and...*/
select *
from student
where sage between 17 and 19
/*查询经管和计算机的学生*/
select *
from student
where sdept in('计算机','经管')
/*查询姓张的同学的学号和姓名 % (任意长度)和 _ （固定长度）模糊查找*/
select sno,sname
from student
where sname like '张%'
/*查询计算机系和经管的女同学 and ，or*/
select *
from student
where ssex='女' and (sdept = '计算机' or sdept='经管')
/*统计学生总人数（其实是统计行数）*/
select count(*) as 人数
from student
/*统计男生人数*/
select count(*) as 人数
from student
where ssex='男'
/*统计男生人数*/
select count(sno) as 人数
from student
where ssex='男'
/*统计选课人数*/
select count(distinct sno) as 人数
from sc
/*1001同学的最高分*/
select max(grade) as 最高分
from sc
where sno='1001'
/*统计从c01这门课的总分*/
select sum(grade) as 总分
from sc
where sno='1001'
/*统计从c01这门课的平均分*/
select avg(grade) as 平均分
from sc
where sno='1001'
/*统计男女各有多少人*/
select ssex '性别',count(sno)
from student
group by ssex
/*统计男女各有多少人*/
select sdept '系',count(sno)
from student
group by sdept
/*统计每个学生各选了多少门课*/
select sno '学号',count(cno)
from sc
group by sno
/*统计每个学生各选了多少门课*/
select sno '学号'
from sc
group by sno
having count(cno)>3
/*统计两门在90分以上的学生的学号*/
select sno '学号'
from sc
where grade>=90
group by sno
having count(cno)>2
/*统计每个学生的选课门数，并按门数降序排序*/
select sno,count(cno)
from sc
group by sno
order by count(cno) desc
/*查询学生的选课情况*/
select sname,cname,grade
from student,course,sc
where student.sno=sc.sno and
course.cno =sc.cno
/* 查询每门课程的间接先修课*/
select c1.cno,c2.cpon
from course c1,course c2
where c1.cpon=c2.cno
/*查询所有学生的选课门数 */
select student.sno,count(cno)
from student left join sc
on(student.sno=sc.sno)
group by student.sno
select cname,max(grade)
from course,sc
where course.cno = sc.cno
group by course.cno
order by max(grade) desc
/*step1:查询张军在哪个系*/
/*
select sdept
from student
where sname='张'
*/
/*step2:查询电气的学生*/
/*
select *
from student
where sdept='电气' and sname!='张君'
*/
/*step3:合并*/
select *
from student
where sdept =(
select sdept
from student
where sname='张军')
and sname!='张军'
/*查询选修了数据库这门课的学生的学号和姓名*/
/*1.多表方式*/
select sc.sno,sname
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and cname='数据库'
/*2.嵌套查询*/
select cno
from course
where cname='数据库'
/*step2:查询选修了c01的学生的学号*/
select sno
from sc
where cno='c01'
/*step3:查询1001，1003的学号和姓名*/
select sno,sname
from student
where sno in ('1001','1003')
/*step4:合并 */
select sno,sname
from student
where sno in (select sno
from sc
where cno=(select cno
from course
where cname='数据库')
)
/*step4:合并 */
select student.sno,sname
from student,sc
where student.sno=sc.sno and cno=(select cno
from course
where cname='数据库'
)
/*查询每个学生成绩超过他自己平均分的课程号和课程名*/
/*
* step1:查询sc表的第一个学生1001的平均分
* step2:查询sc表1001超过89分的课程号
* step3:重复step1，step2
*/
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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
- 第二部分
-- //嵌套第5题
select sdept
from student left join br on (student.sno=br.sno)
group by sdept
having count(student.sno)<2
-- //exists第3题
select sname
from br,book,student
where br.bno=book.bno and student.sno=br.sno
and bname='数据库原理' and
not exists (
select *
from br,book
where br.bno=book.bno and bname='C语言'
)
-- //exist第8题
select count(*) from a where no='".$no."' and pd='".$mm."'"
select count(*) from a where no='1' and pd='aa'
select * count(sno) from book,br
where book.booknumber=br.bno and bno='b01'
/*临时添加一项出生年*/
select sno '学号', sname '姓名', 2018-sage '出生年'
from student
/*查询选了课的同学的学号(消除重复行) distinct*/
select distinct sno
from sc
/*查询选了课的同学的学号(全部显示)*/
select sno, cno
from sc
/*查询年龄大与19的学生的所有信息*/
select *
from student
where sage>19
/*查询2000年后出生的学生 */
select sno,sname,2018-sage
from student
where 2018-sage>=2000
/*查询年龄17到19之间的学生 between...and...*/
select *
from student
where sage between 17 and 19
/*查询经管和计算机的学生*/
select *
from student
where sdept in('计算机','经管')
/*查询姓张的同学的学号和姓名 % (任意长度)和_（固定长度）模糊查找*/
select sno,sname
from student
where sname like '张%'
/*查询计算机系和经管的女同学 and ，or*/
select *
from student
where ssex='女' and (sdept = '计算机' or sdept='经管')
/*统计学生总人数（其实是统计行数）*/
selectcount(*) as 人数
from student
/*统计男生人数*/
select count(*) as 人数
from student
where ssex='男'
/*统计男生人数*/
select count(sno) as 人数
from student
where ssex='男'
/*统计选课人数*/
select count(distinct sno) as 人数
from sc
/*1001同学的最高分*/
select max(grade) as 最高分
from sc
where sno='1001'
/*统计从c01这门课的总分*/
select sum(grade) as 总分
from sc
where sno='1001'
/*统计从c01这门课的平均分*/
select avg(grade) as 平均分
from sc
where sno='1001'
/*统计男女各有多少人*/
select ssex '性别',count(sno)
from student
group by ssex
/*统计男/女各有多少人*/
select sdept '系',count(sno)
from student
group by sdept
/*统计每个学生各选了多少门课*/
select sno '学号',count(cno)
from sc
group by sno
/*统计每个学生各选了多少门课*/
select sno '学号'
from sc
group by sno
having count(cno)>3
*统计两门在90分以上的学生的学号*
select sno '学号'
from sc
where grade>=90
group by sno
having count(cno)>2
*统计每个学生的选课门数，并按门数降序排序*
select sno,count(cno)
from sc
group by sno
order by count(cno) desc
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
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
## Last Updated:04/04/2018 查询学生的选课情况
select sname,cname,grade
from student,course,sc
where student.sno=sc.sno and
course.cno =sc.cno
1
2
3
4
2
3
4
查询每门课程的间接先修课
select c1.cno,c2.cpon
from course c1,course c2
where c1.cpon=c2.cno
1
2
3
2
3
查询所有学生的选课门数
select student.sno,count(cno)
from student left join sc
on(student.sno=sc.sno)
group by student.sno
1
2
3
4
2
3
4
## Last updated：2018-04-11 查询与张军在同一系的学生的信息
step1:查询张军在哪个系
select sdept
from student
where sname='张军'
1
2
3
2
3
step2:查询电气的学生
select *
from student
where sdept='电气' and sname!='张军'
1
2
3
2
3
step3:合并
select *
from student
where sdept =(
select sdept
from student
where sname='张军'
) and sname!='张军'
1
2
3
4
5
6
7
2
3
4
5
6
7
查询选修了数据库这门课的学生的学号和姓名
1.多表方式
select sc.sno,sname
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and cname='数据库'
1
2
3
2
3
2.嵌套查询
select cno
from course
where cname='数据库'
1
2
3
2
3
step2:查询选修了c01的学生的学号
select sno
from sc
where cno='c01'
1
2
3
2
3
step3:查询1001，1003的学号和姓名
select sno,sname
from student
where sno in ('1001','1003')
1
2
3
2
3
step4:合并
select sno,sname
from student
where sno in (select sno
from sc
where cno=(select cno
from course
where cname='数据库')
)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
step4:合并
select student.sno,sname
from student,sc
where student.sno=sc.sno and cno=(select cno
from course
where cname='数据库'
)
1
2
3
4
5
6
2
3
4
5
6
查询每个学生成绩超过他自己平均分的课程号和课程名
step1:查询sc表的第一个学生1001的平均分 step2:查询sc表1001超过89分的课程号 step3:重复step1，step2
select sno,cno
from sc sc1
where grade=(
select avg(grade)
from sc sc2
where sc2.sno=sc1.sno
)
1
2
3
4
5
6
7
2
3
4
5
6
7
查询选修门数超过张佳佳的选修门数的学生的学号
step1：查询张佳佳选了几门 step2：查询超过两门的学生的学号 step3：合并
select sc.sno,count(cno)
from sc,student
where sc.sno=student.sno and sname!='张佳佳'
group by sno
having count(cno)>=(
select count(cno)
from student,sc
where student.sno=sc.sno and sname = '张佳佳'
)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
## Updated:2018-4-18 exists查询 查询选修了c01课程的学生的姓名
/*多表*/
select sname
from student,sc
where student.sno=sc.sno and cno='c01'
/*嵌套*/
select sname
from student
where sno in(
select sno
from sc
where cno='c01'
)
/*exists嵌套*/
select sname
from student
where exists (
select *
from sc
where cno='c01' and sc.sno=student.sno
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
查询选修了全部课程的学生的姓名
/*多表连接查询*/
select student.sno,sname
from student,sc
where sc.sno=student.sno
group by sname
having count(sc.cno)=(
select count(cno)
from course
)
/*not exists*/
select sname
from student
where not exists (
select *
from course
where not exists (
select *
from sc
where sc.cno=course.cno and
sc.sno=student.sno
)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
查询选修了1003同学选修的全部课程的学生的学号和姓名
select sno,count(cno)
from sc
where cno in(
select cno
from sc
where sno='1003' and sno!='1003'
)
group by sno
having count(cno)=(
select count(cno)
from sc
where sno='1001'
)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
查询不存在这样的课程，学生s1选修了，而学生xxx没有选修
select distinct sno
from sc s1
where not exists(
select *
from sc s2
where sno='1003' and not exists (
select *
from sc s3
where s3.cno=s2.cno and
s3.sno=s1.sno and
sno!='1003'
)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
课后作业1：查询没有选修c03课程的学生学号
select sno
from student
where not exists (
select *
from sc
where cno='c03' and sc.sno=student.sno
)
1
2
3
4
5
6
7
2
3
4
5
6
7
课后作业2：查询学号1001学生没有选修的科目
1
## Updated：2018-04-25 视图 1.创建视图用于查询学生的选课情况，包括姓名，课程名和成绩
create view v1
as
select sname,cname,grade
from student,sc,course
where student.sno=sc.sno and
course.cno=sc.cno
1
2
3
4
5
6
2
3
4
5
6
视图已创建
查询学生的选课情况
select *
from v1
1
2
2
查询张军同学的选课情况
select *
from v1
where sname='张军'
1
2
3
2
3
2.创建视图用于统计学生的选课门数
create view v2(no,shu)
as
select student.sno,count(cno)
from student left join sc on (student.sno=sc.sno)
group by student.sno
1
2
3
4
5
2
3
4
5
select *
from v2
1
2
2
3.删除视图
drop view v2
1
4.创建一个视图用于查询计算机系的学生信息，学号，姓名，性别，系别
create view v6
as
select cno,cname,ccredit,chour
from course
where chour=48
1
2
3
4
5
2
3
4
5
插入数据--
insert into v6
values('c40','英语',3,48)
1
2
2
插入不适合数据--成功，编译器不会检测输入
insert into v6
values('c40','英语',3,48)
1
2
2
加入使用增删改时的检测，使编译器检测输入
create view v6
as
select cno,cname,ccredit,chour
from course
where chour=48
with check option
1
2
3
4
5
6
2
3
4
5
6
创建存储过程p1，输入学号和课程号，显示成绩
delimiter $$
create procedure p1(in xh char(10),in ch char(10),out cj float);
begin
cj=(select grade from sc where sno=xh and cno=ch);
end $$
delimiter;
1
2
3
4
5
6
2
3
4
5
6
创建一个存储过程，用于删除指定的学生
delimiter //
create procedure p2(in xm char(10))
begin
delete from student where sname=xm;
end;
//
delimiter ;
1
2
3
4
5
6
7
2
3
4
5
6
7
创建存储过程p1，输入学号和课程号，显示等级
1.delimiter的语法：前面定义一个符号，后面也用这个符号收回 后面的delimiter要有< space > + ';'
2.elseif连写
delimiter $$
create procedure p1(in xh char(10),in ch char(10),out dj char(10))
begin
declare cj float;
set cj=(select grade from sc where sno=xh and cno=ch);
if cj>=90 then
set dj='优';
elseif cj>=80 then
set dj='良';
elseif cj>=70 then
set dj='中';
elseif cj>=60 then
set dj='及格';
else
set dj='不及格';
end if;
end;
$$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
delimiter调用
call p1('1001','c01',@k);
select @k;
1
2
2
## Update:2018-05-02 函数
1.函数 function 2.存储过程 procedure 创建一个函数用于统计某门课程的选修人数
delimiter //
create function f1(ch char(10))
returns int
/*对比：create procedure p1(in ch char(10),out shu int)*/
begin
declare shu int;
/*compare：set shu=(select count(cno) from sc where cno=ch)*/
select count(sno) into shu from sc where cno=ch;
return shu;
end;
//
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
函数调用
select f1('c01')
1
讨论：存储过程与函数的异同 have take a photo;
# update:2018--05-07 每当学生选修一门课，该课程人数自动加1
create trigger tr1 before update
1
当更新sc表成绩小于60分时，自动将其成绩改为60 当update sc表时，自动grade=60
delimiter //
create trigger tr2 before update
on sc for each row
begin
if new.grade<60 then
set new.grade=60;
end if;
end;
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
当学生选课时，自动检测其学号和课程号是否存在，如果有一个不存在，则显示您的学号或课程号不存在，不能选课 signal sqlstate 'HY000' set message_text=msg; 抛出错误
delimiter //
create trigger tr3 before insert
on sc for each row
begin
declare sshu int;
declare cshu int;
declare msg varchar(20);
select count(*) into sshu from student where sno=new.sno;
select count(*) into cshu from course where cno=new.cno;
if sshu=0 or cshu=0 then
set msg="你的学号或课程号不存在，不能选课"；
signal sqlstate 'HY000' set message_text=msg;
end;
end if;
end;
//
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
临时添加一项出生年
select sno '学号', sname '姓名', 2018-sage '出生年'
from student
1
2
2
查询选了课的同学的学号(消除重复行) distinct
select distinct sno
from sc
1
2
2
查询选了课的同学的学号(全部显示)
select sno, cno
from sc
1
2
2
查询年龄大与19的学生的所有信息 >
select *
from student
where sage>19
1
2
3
2
3
查询2000年后出生的学生 >=
select sno,sname,2018-sage
from student
where 2018-sage>=2000
1
2
3
2
3
查询年龄17到19之间的学生 between...and...
select *
from student
where sage between 17 and 19
1
2
3
2
3
查询经管和计算机的学生
select *
from student
where sdept in('计算机','经管')
1
2
3
2
3
查询姓张的同学的学号和姓名 % (任意长度)和 _ （固定长度）模糊查找
select sno,sname
from student
where sname like '张%'
1
2
3
2
3
查询计算机系和经管的女同学 and ，or
select *
from student
where ssex='女' and (sdept = '计算机' or sdept='经管')
1
2
3
2
3
order by
## Last Updated：03/28/2018
统计学生总人数（其实是统计行数）
select count(*) as 人数
from student
1
2
2
统计男生人数
select count(*) as 人数
from student
where ssex='男'
1
2
3
2
3
统计男生人数
select count(sno) as 人数
from student
where ssex='男'
1
2
3
2
3
统计选课人数
select count(distinct sno) as 人数
from sc
1
2
2
1001同学的最高分
select max(grade) as 最高分
from sc
where sno='1001'
1
2
3
2
3
统计从c01这门课的总分
select sum(grade) as 总分
from sc
where sno='1001'
1
2
3
2
3
统计从c01这门课的平均分
select avg(grade) as 平均分
from sc
where sno='1001'
1
2
3
2
3
统计男女各有多少人
select ssex '性别',count(sno)
from student
group by ssex
1
2
3
2
3
统计男女各有多少人
select sdept '系',count(sno)
from student
group by sdept
1
2
3
2
3
统计每个学生各选了多少门课
select sno '学号',count(cno)
from sc
group by sno
1
2
3
2
3
统计每个学生各选了多少门课
select sno '学号'
from sc
group by sno
having count(cno)>3
1
2
3
4
2
3
4
统计两门在90分以上的学生的学号
select sno '学号'
from sc
where grade>=90
group by sno
having count(cno)>2
1
2
3
4
5
2
3
4
5
统计每个学生的选课门数，并按门数降序排序
select sno,count(cno)
from sc
group by sno
order by count(cno) desc
1
2
3
4
2
3
4
## Last Updated:04/04/2018 查询学生的选课情况
select sname,cname,grade
from student,course,sc
where student.sno=sc.sno and
course.cno =sc.cno
1
2
3
4
2
3
4
查询每门课程的间接先修课
select c1.cno,c2.cpon
from course c1,course c2
where c1.cpon=c2.cno
1
2
3
2
3
查询所有学生的选课门数
select student.sno,count(cno)
from student left join sc
on(student.sno=sc.sno)
group by student.sno
1
2
3
4
2
3
4
## Last updated：2018-04-11 查询与张军在同一系的学生的信息
step1:查询张军在哪个系
select sdept
from student
where sname='张军'
1
2
3
2
3
step2:查询电气的学生
select *
from student
where sdept='电气' and sname!='张军'
1
2
3
2
3
step3:合并
select *
from student
where sdept =(
select sdept
from student
where sname='张军'
) and sname!='张军'
1
2
3
4
5
6
7
2
3
4
5
6
7
查询选修了数据库这门课的学生的学号和姓名
1.多表方式
select sc.sno,sname
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and cname='数据库'
1
2
3
2
3
2.嵌套查询
select cno
from course
where cname='数据库'
1
2
3
2
3
step2:查询选修了c01的学生的学号
select sno
from sc
where cno='c01'
1
2
3
2
3
step3:查询1001，1003的学号和姓名
select sno,sname
from student
where sno in ('1001','1003')
1
2
3
2
3
step4:合并
select sno,sname
from student
where sno in (select sno
from sc
where cno=(select cno
from course
where cname='数据库')
)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
step4:合并
select student.sno,sname
from student,sc
where student.sno=sc.sno and cno=(select cno
from course
where cname='数据库'
)
1
2
3
4
5
6
2
3
4
5
6
查询每个学生成绩超过他自己平均分的课程号和课程名
step1:查询sc表的第一个学生1001的平均分 step2:查询sc表1001超过89分的课程号 step3:重复step1，step2
select sno,cno
from sc sc1
where grade=(
select avg(grade)
from sc sc2
where sc2.sno=sc1.sno
)
1
2
3
4
5
6
7
2
3
4
5
6
7
查询选修门数超过张佳佳的选修门数的学生的学号
step1：查询张佳佳选了几门 step2：查询超过两门的学生的学号 step3：合并
select sc.sno,count(cno)
from sc,student
where sc.sno=student.sno and sname!='张佳佳'
group by sno
having count(cno)>=(
select count(cno)
from student,sc
where student.sno=sc.sno and sname = '张佳佳'
)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
## Updated:2018-4-18 exists查询 查询选修了c01课程的学生的姓名
/*多表*/
select sname
from student,sc
where student.sno=sc.sno and cno='c01'
/*嵌套*/
select sname
from student
where sno in(
select sno
from sc
where cno='c01'
)
/*exists嵌套*/
select sname
from student
where exists (
select *
from sc
where cno='c01' and sc.sno=student.sno
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
查询选修了全部课程的学生的姓名
/*多表连接查询*/
select student.sno,sname
from student,sc
where sc.sno=student.sno
group by sname
having count(sc.cno)=(
select count(cno)
from course
)
/*not exists*/
select sname
from student
where not exists (
select *
from course
where not exists (
select *
from sc
where sc.cno=course.cno and
sc.sno=student.sno
)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
查询选修了1003同学选修的全部课程的学生的学号和姓名
select sno,count(cno)
from sc
where cno in(
select cno
from sc
where sno='1003' and sno!='1003'
)
group by sno
having count(cno)=(
select count(cno)
from sc
where sno='1001'
)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
查询不存在这样的课程，学生s1选修了，而学生xxx没有选修
select distinct sno
from sc s1
where not exists(
select *
from sc s2
where sno='1003' and not exists (
select *
from sc s3
where s3.cno=s2.cno and
s3.sno=s1.sno and
sno!='1003'
)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
课后作业1：查询没有选修c03课程的学生学号
select sno
from student
where not exists (
select *
from sc
where cno='c03' and sc.sno=student.sno
)
1
2
3
4
5
6
7
2
3
4
5
6
7
课后作业2：查询学号1001学生没有选修的科目
1
## Updated：2018-04-25 视图 1.创建视图用于查询学生的选课情况，包括姓名，课程名和成绩
create view v1
as
select sname,cname,grade
from student,sc,course
where student.sno=sc.sno and
course.cno=sc.cno
1
2
3
4
5
6
2
3
4
5
6
视图已创建
查询学生的选课情况
select *
from v1
1
2
2
查询张军同学的选课情况
select *
from v1
where sname='张军'
1
2
3
2
3
2.创建视图用于统计学生的选课门数
create view v2(no,shu)
as
select student.sno,count(cno)
from student left join sc on (student.sno=sc.sno)
group by student.sno
1
2
3
4
5
2
3
4
5
select *
from v2
1
2
2
3.删除视图
drop view v2
1
4.创建一个视图用于查询计算机系的学生信息，学号，姓名，性别，系别
create view v6
as
select cno,cname,ccredit,chour
from course
where chour=48
1
2
3
4
5
2
3
4
5
插入数据--
insert into v6
values('c40','英语',3,48)
1
2
2
插入不适合数据--成功，编译器不会检测输入
insert into v6
values('c40','英语',3,48)
1
2
2
加入使用增删改时的检测，使编译器检测输入
create view v6
as
select cno,cname,ccredit,chour
from course
where chour=48
with check option
1
2
3
4
5
6
2
3
4
5
6
创建存储过程p1，输入学号和课程号，显示成绩
delimiter $$
create procedure p1(in xh char(10),in ch char(10),out cj float);
begin
cj=(select grade from sc where sno=xh and cno=ch);
end $$
delimiter;
1
2
3
4
5
6
2
3
4
5
6
创建一个存储过程，用于删除指定的学生
delimiter //
create procedure p2(in xm char(10))
begin
delete from student where sname=xm;
end;
//
delimiter ;
1
2
3
4
5
6
7
2
3
4
5
6
7
创建存储过程p1，输入学号和课程号，显示等级
1.delimiter的语法：前面定义一个符号，后面也用这个符号收回 后面的delimiter要有< space > + ';'
2.elseif连写
delimiter $$
create procedure p1(in xh char(10),in ch char(10),out dj char(10))
begin
declare cj float;
set cj=(select grade from sc where sno=xh and cno=ch);
if cj>=90 then
set dj='优';
elseif cj>=80 then
set dj='良';
elseif cj>=70 then
set dj='中';
elseif cj>=60 then
set dj='及格';
else
set dj='不及格';
end if;
end;
$$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
delimiter调用
call p1('1001','c01',@k);
select @k;
1
2
2
## Update:2018-05-02 函数
1.函数 function 2.存储过程 procedure 创建一个函数用于统计某门课程的选修人数
delimiter //
create function f1(ch char(10))
returns int
/*对比：create procedure p1(in ch char(10),out shu int)*/
begin
declare shu int;
/*compare：set shu=(select count(cno) from sc where cno=ch)*/
select count(sno) into shu from sc where cno=ch;
return shu;
end;
//
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
函数调用
select f1('c01')
1
讨论：存储过程与函数的异同 have take a photo;
# update:2018--05-07 每当学生选修一门课，该课程人数自动加1
create trigger tr1 before update
1
当更新sc表成绩小于60分时，自动将其成绩改为60 当update sc表时，自动grade=60
delimiter //
create trigger tr2 before update
on sc for each row
begin
if new.grade<60 then
set new.grade=60;
end if;
end;
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
当学生选课时，自动检测其学号和课程号是否存在，如果有一个不存在，则显示您的学号或课程号不存在，不能选课 signal sqlstate 'HY000' set message_text=msg; 抛出错误
delimiter //
create trigger tr3 before insert
on sc for each row
begin
declare sshu int;
declare cshu int;
declare msg varchar(20);
select count(*) into sshu from student where sno=new.sno;
select count(*) into cshu from course where cno=new.cno;
if sshu=0 or cshu=0 then
set msg="你的学号或课程号不存在，不能选课"；
signal sqlstate 'HY000' set message_text=msg;
end;
end if;
end;
//
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17