HUST数据库实验

本文最后更新于：2022年1月13日 下午

HUST网安数据库实验

单纯的sql语句练习。。。

如有错误，还请指正，不胜感激！🤣

实验一.SQL基础练习

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
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
1.create database CSEDB_学号;
//2-8为前面几个表操作语句的联系，但一顿搞之后得到的表结构可能与指导书上的结构不一致
//在进行insert之前，可以先把库删了，重新建库，直接使用4.5.(1)里的三个建表语句
2.创建表student(具体数据类型char的大小感觉多少就行，影响不大)
mysql> create table Student(
    -> Sno char(9) primary key,
    -> Sname char(20) unique,
    -> Ssex char(2),
    -> Sage  int,
    -> Sdept char(20),
    -> Scholarshhip char(2))
    -> ;
Query OK, 0 rows affected (0.12 sec)

mysql> show columns from student;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| Sno          | char(9)  | NO   | PRI | NULL    |       |
| Sname        | char(20) | YES  | UNI | NULL    |       |
| Ssex         | char(2)  | YES  |     | NULL    |       |
| Sage         | int      | YES  |     | NULL    |       |
| Sdept        | char(20) | YES  |     | NULL    |       |
| Scholarshhip | char(2)  | YES  |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
6 rows in set (0.02 sec)

ps: 唯一键可以为空，主键不能为空

3.创建表course
mysql> create table Course(
    -> Cno char(4) primary key,
    -> Cname char(40) unique,
    -> Cpno char(4),
    -> Ccredit int)
    -> ;
Query OK, 0 rows affected (0.07 sec)

mysql> show columns from course;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| Cno     | char(4)  | NO   | PRI | NULL    |       |
| Cname   | char(40) | YES  | UNI | NULL    |       |
| Cpno    | char(4)  | YES  |     | NULL    |       |
| Ccredit | int      | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4.创建表sc
mysql> create table sc(
    -> sno char(5),
    -> cno char(3),
    -> grade int,
    -> primary key (sno,cno)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show columns from Sc;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| Sno   | char(9) | NO   | PRI | NULL    |       |
| Cno   | char(4) | NO   | PRI | NULL    |       |
| Grade | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5，删除表
mysql> show tables;
+----------------------------+
| Tables_in_csedb_u201911749 |
+----------------------------+
| course                     |
| sc                         |
| student                    |
+----------------------------+
3 rows in set (0.00 sec)

mysql> drop tables sc;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------------------+
| Tables_in_csedb_u201911749 |
+----------------------------+
| course                     |
| student                    |
+----------------------------+
2 rows in set (0.00 sec)

6.更改表结构（增加/删除一列，更改表字段类型）
mysql> show columns from student;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| Sno          | char(9)  | NO   | PRI | NULL    |       |
| Sname        | char(20) | YES  | UNI | NULL    |       |
| Ssex         | char(2)  | YES  |     | NULL    |       |
| Sage         | int      | YES  |     | NULL    |       |
| Sdept        | char(20) | YES  |     | NULL    |       |
| Scholarshhip | char(2)  | YES  |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
//添加列 alter table 表名 add column(加不加都行) 列名  列属性;
mysql> alter table student add Scome datetime;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
//删除列 alter table 表名 drop column(加不加都行) 列名;
mysql> alter table student drop column scome;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
//修改列属性 alter table 表名 modify column(加不加都行) 列名  列属性;
mysql> alter table student modify column sage smallint;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| Sno          | char(9)  | NO   | PRI | NULL    |       |
| Sname        | char(20) | YES  | UNI | NULL    |       |
| Ssex         | char(2)  | YES  |     | NULL    |       |
| Sage         | smallint | YES  |     | NULL    |       |
| Sdept        | char(20) | YES  |     | NULL    |       |
| Scholarshhip | char(2)  | YES  |     | NULL    |       |
| Scome        | datetime | YES  |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
7 rows in set (0.00 sec)

7.创建索引，删除索引
mysql> create unique index stusno on student(sno);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1
##第一种删除方式
mysql> drop index stusno on student;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
##第二种删除方式
mysql> alter table student drop index stusno;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
##第三种删除方式，删除主键索引
alter table 表名 drop primary key;
##第四种删除方式，删除外键索引
alter table 表名 drop foreign key 外键名；//未检验

8.按照指导书的要求，建立三个表之间的关系，就是设置外键
mysql> alter table course add foreign key (cpno) references course(cno);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sc add foreign key (sno) references student(sno);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sc add foreign key (cno) references course(cno);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
注意外键约束！！！
ps:设置外键之后，如果想要修改父表的话，需要设置一个级联操作命令，当在操作父表时，子表会受到相同的影响，如果没有设置级联操作，更改父表会报错。
级联操作：
	主表更新
	语法形式为：on update[级联操作]
	主表删除
	语法形式为：on delete[级联操作]
	这里的级联操作常见的有三种形式：
	cascade：同步操作，或者串联操作！也就是当主表记录删除或更新的时候，从表也进行相应的删除	   更新！
	set null：设置为null，也就是当主表记录删除或更新的时候，从表中的外键字段设置为空
	restrict：拒绝主表的更新或删除
	
9.添加数据(insert)
语法规则：
	1. 指定向那些列插入值：
	insert into table_name ( field1, field2,...fieldN )
                       values
                       ( value1, value2,...valueN );
    2. 如果插入的数据覆盖所有的列，则不用指定插入列的范围
    insert into table_name
    				   values
    				   ( value1, value2,...valueN );
ps: 所有符号都必须是英文符号
向student表中插入数据：
mysql> insert into student values('200215121','李勇','男',20,'CS','否');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values('200215122','刘晨','女',19,'CS','否');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values('200215123','王敏','女',18,'MA','否');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values('200215125','张立','男',19,'IS','否');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   20 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   19 | CS    | 否          |
| 200215123 | 王敏  | 女   |   18 | MA    | 否          |
| 200215125 | 张立  | 男   |   19 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
4 rows in set (0.01 sec)
向course表中插入数据：
insert into course values('1','数据库',NULL,4);
insert into course values('2','数学',NULL,2);
insert into course values('3','信息系统',NULL,4);
insert into course values('4','操作系统',NULL,3);
insert into course values('5','数据结构',NULL,4);
insert into course values('6','数据处理',NULL,2);
insert into course values('7','java',NULL,4);
向sc表中插入数据：
insert into SC values('200215121','1',92);
insert into SC values('200215121','2',85);
insert into SC values('200215121','3',88);
insert into SC values('200215122','2',90);
insert into SC values('200215122','3',80);

10.更新数据(update)
语法规则：
	UPDATE [LOW_PRIORITY] [IGNORE] table_name 
	SET field1=new-value1, field2=new-value2
	[WHERE Clause]
	[ORDER BY ...]
	[LIMIT ROW_COUNT]
更新courseb表数据：
update Course set Cpno = '5' where Cno = '1';
update Course set Cpno = '1' where Cno = '3';
update Course set Cpno = '6' where Cno = '4';
update Course set Cpno = '7' where Cno = '5';
update Course set Cpno = '6' where Cno = '7';

11.删除数据(delete)
语法规则：
	delete from table_name [where clause]
	如果没有指定where，则删除表中所有数据

mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   20 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   19 | CS    | 否          |
+-----------+-------+------+------+-------+-------------+
2 rows in set (0.01 sec)

mysql> delete from student;
Query OK, 2 rows affected (0.02 sec)

mysql> select * from student;
Empty set (0.00 sec)

12.扩展练习：
（1）查询全体学生的学号、姓名和年龄；
mysql> select sno,sname,sage
    -> from student;
+-----------+-------+------+
| sno       | sname | sage |
+-----------+-------+------+
| 200215121 | 李勇  |   20 |
| 200215122 | 刘晨  |   19 |
| 200215123 | 王敏  |   18 |
| 200215125 | 张立  |   19 |
+-----------+-------+------+
4 rows in set (0.00 sec)
（2）查询所有计算机系学生的详细记录；
mysql> select *
    -> from student
    -> where sdept = 'CS';
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   20 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   19 | CS    | 否          |
+-----------+-------+------+------+-------+-------------+
2 rows in set (0.00 sec)
（3）找出考试成绩为优秀（90 分及以上）或不及格的学生的学号、课程号及成绩；
mysql> select *
    -> from sc
    -> where grade >= 90 or grade <60;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 200215121 | 1   |    92 |
| 200215122 | 2   |    90 |
+-----------+-----+-------+
2 rows in set (0.00 sec)
（4）查询年龄不在 19~20 岁之间的学生姓名、性别和年龄；
mysql> select sname,ssex,sage
    -> from student
    -> where sage not in (19,20);
+-------+------+------+
| sname | ssex | sage |
+-------+------+------+
| 王敏  | 女   |   18 |
+-------+------+------+
1 row in set (0.00 sec)
（5）查询数学系（MA）、信息系（IS)的学生的姓名和所在系；
mysql> select sname,sdept
    -> from student
    -> where sdept in ('CS','MA');
+-------+-------+
| sname | sdept |
+-------+-------+
| 李勇  | CS    |
| 刘晨  | CS    |
| 王敏  | MA    |
+-------+-------+
3 rows in set (0.00 sec)
（6）查询名称中包含“数据”的所有课程的课程号、课程名及其学分；
mysql> select cno,cname,ccredit
    -> from course
    -> where cname like '%数据%';
+-----+----------+---------+
| cno | cname    | ccredit |
+-----+----------+---------+
| 1   | 数据库   |       4 |
| 5   | 数据结构 |       4 |
| 6   | 数据处理 |       2 |
+-----+----------+---------+
3 rows in set (0.00 sec)
（7) 找出所有没有选修课成绩的学生学号和课程号；
mysql> select distinct sno,cno
    -> from student,course
    -> where sno not in(select distinct sno from sc) and cno not in (select distinct cno from sc);
+-----------+-----+
| sno       | cno |
+-----------+-----+
| 200215123 | 6   |
| 200215125 | 6   |
| 200215123 | 4   |
| 200215125 | 4   |
| 200215123 | 7   |
| 200215125 | 7   |
| 200215123 | 5   |
| 200215125 | 5   |
+-----------+-----+
8 rows in set (0.00 sec)
（8）查询学生 200215121 选修课的最高分、最低分以及平均成绩；
mysql> select max(grade),min(grade),avg(grade)
    -> from sc
    -> where sno = '200215121';
+------------+------------+------------+
| max(grade) | min(grade) | avg(grade) |
+------------+------------+------------+
|         92 |         85 |    88.3333 |
+------------+------------+------------+
1 row in set (0.01 sec)
（9）查询选修了 2 号课程的学生的学号及其成绩，查询结果按成绩升序排列；
mysql> select sno,grade
    -> from sc
    -> where cno = '2'
    -> order by grade;
+-----------+-------+
| sno       | grade |
+-----------+-------+
| 200215121 |    85 |
| 200215122 |    90 |
+-----------+-------+
2 rows in set (0.00 sec)
（10）查询每个系名及其学生的平均年龄。
mysql> select sdept,avg(sage)
    -> from student
    -> group by sdept;
+-------+-----------+
| sdept | avg(sage) |
+-------+-----------+
| CS    |   19.5000 |
| MA    |   18.0000 |
| IS    |   19.0000 |
+-------+-----------+
3 rows in set (0.01 sec)
 （思考：如何查询学生平均年龄在 19 岁以下（含 19 岁）的系别及其学生的平均年龄？）
 mysql> select sdept,avg(sage)
    -> from student
    -> group by sdept
    -> having avg(sage)<=19;
+-------+-----------+
| sdept | avg(sage) |
+-------+-----------+
| MA    |   18.0000 |
| IS    |   19.0000 |
+-------+-----------+
2 rows in set (0.01 sec)

ps：where用于过滤指定的行，它后面的判断数据必须是存在的行，having用于对前面select的结果进行筛选，但having不一定非要配合group by使用，如果我前面select的结果中没有，就不能用having
总的来说，where直接对数据表中的字段进行直接筛选，而having是对前面筛选的结果进行筛选
参考链接:https://blog.csdn.net/yexudengzhidao/article/details/54924471
参考链接:https://www.jianshu.com/p/e66abfadcdca

实验二.SQL复杂操作练习

1

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
237
238
239
240
241
242
243
244
（1）查询每门课程及其被选情况（输出所有课程中每门课的课程号、课程名称、选修该课程的学生
学号及成绩--如果没有学生选择该课，则相应的学生学号及成绩为空值）。
mysql> select course.cno,cname,sno,grade
    -> from course left outer join sc on (course.cno = sc.cno);
+-----+----------+-----------+-------+
| cno | cname    | sno       | grade |
+-----+----------+-----------+-------+
| 1   | 数据库   | 200215121 |    92 |
| 2   | 数学     | 200215122 |    90 |
| 2   | 数学     | 200215121 |    85 |
| 3   | 信息系统 | 200215122 |    80 |
| 3   | 信息系统 | 200215121 |    88 |
| 4   | 操作系统 | NULL      |  NULL |
| 5   | 数据结构 | NULL      |  NULL |
| 6   | 数据处理 | NULL      |  NULL |
| 7   | java     | NULL      |  NULL |
+-----+----------+-----------+-------+
9 rows in set (0.00 sec)

（2）查询与“张立”同岁的学生的学号、姓名和年龄。（要求使用至少 3 种方法求解）
第一种：
mysql> select sno,sname,sage
	-> from student
	-> where sage = (select sage from student where sname = '张立');
+-----------+-------+------+
| sno       | sname | sage |
+-----------+-------+------+
| 200215122 | 刘晨  |   19 |
| 200215125 | 张立  |   19 |
+-----------+-------+------+
第二种：
mysql> select sno,sname,sage
    -> from student
    -> where sage in(select sage from student where sname = '张立');
+-----------+-------+------+
| sno       | sname | sage |
+-----------+-------+------+
| 200215122 | 刘晨  |   19 |
| 200215125 | 张立  |   19 |
+-----------+-------+------+
2 rows in set (0.00 sec)
2 rows in set (0.01 sec)
第三种：(无语-_-)
mysql> select s1.sno,s1.sname,s1.sage
    -> from student s1,student s2
    -> where s1.sage = s2.sage and s2.sname = '张立';
+-----------+-------+------+
| sno       | sname | sage |
+-----------+-------+------+
| 200215122 | 刘晨  |   19 |
| 200215125 | 张立  |   19 |
+-----------+-------+------+
2 rows in set (0.00 sec)

（3）查询选修了 3 号课程而且成绩为良好（80~89 分）的所有学生的学号和姓名。
mysql> select student.sno,student.sname
    -> from student,sc
    -> where student.sno = sc.sno and sc.cno = '3' and sc.grade >=80 and sc.grade <=89;
+-----------+-------+
| sno       | sname |
+-----------+-------+
| 200215121 | 李勇  |
| 200215122 | 刘晨  |
+-----------+-------+
2 rows in set (0.00 sec)

（4）查询学生 200215122 选修的课程号、课程名
mysql> select course.cno,course.cname
    -> from course,sc
    -> where course.cno = sc.cno and sc.sno = '200215122';
+-----+----------+
| cno | cname    |
+-----+----------+
| 2   | 数学     |
| 3   | 信息系统 |
+-----+----------+
2 rows in set (0.00 sec)
 （思考：如何查询学生 200215122 选修的课程号、课程名及成绩？）
 mysql> select course.cno,course.cname,sc.grade
    -> from course,sc
    -> where course.cno = sc.cno and sc.sno = '200215122';
+-----+----------+-------+
| cno | cname    | grade |
+-----+----------+-------+
| 2   | 数学     |    90 |
| 3   | 信息系统 |    80 |
+-----+----------+-------+
2 rows in set (0.00 sec)

（5）找出每个学生低于他所选修课程平均成绩 5 分以上的课程号。（输出学号和课程号）
mysql> select sno,cno
    -> from sc s1
    -> where s1.grade+5< (select avg(grade) from sc s2 where s1.sno = s2.sno);
Empty set (0.00 sec)

mysql> select sno,cno
    -> from sc s1
    -> where s1.grade+5<= (select avg(grade) from sc s2 where s1.sno = s2.sno);
+-----------+-----+
| sno       | cno |
+-----------+-----+
| 200215122 | 3   |
+-----------+-----+
1 row in set (0.00 sec)

（6）查询比所有男生年龄都小的女生的学号、姓名和年龄。
//当前表
mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   21 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   20 | CS    | 否          |
| 200215123 | 王敏  | 女   |   19 | MA    | 否          |
| 200215125 | 张立  | 男   |   20 | IS    | 否          |
| 200215126 | 张三  | 女   |   19 | IS    | 否          |
| 200215128 | 李四  | 男   |   21 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
6 rows in set (0.00 sec)

mysql> select sno,sname,sage
    -> from student
    -> where ssex = '女' and sage <(select min(sage) from student where ssex = '男');
+-----------+-------+------+
| sno       | sname | sage |
+-----------+-------+------+
| 200215123 | 王敏  |   19 |
| 200215126 | 张三  |   19 |
+-----------+-------+------+
2 rows in set (0.01 sec)
（7）查询所有选修了 2 号课程的学生姓名及所在系。
mysql> select sname,sdept
    -> from student,sc
    -> where sc.cno = '2' and student.sno = sc.sno;
+-------+-------+
| sname | sdept |
+-------+-------+
| 李勇  | CS    |
| 刘晨  | CS    |
+-------+-------+
2 rows in set (0.00 sec)

（8）使用 update 语句把成绩为良的学生的年龄增加 2 岁，并查询出来。
mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   20 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   19 | CS    | 否          |
| 200215123 | 王敏  | 女   |   18 | MA    | 否          |
| 200215125 | 张立  | 男   |   19 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
4 rows in set (0.00 sec)

mysql> select * from sc;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 200215121 | 1   |    92 |
| 200215121 | 2   |    85 |
| 200215121 | 3   |    88 |
| 200215122 | 2   |    90 |
| 200215122 | 3   |    80 |
+-----------+-----+-------+
5 rows in set (0.00 sec)

mysql> update student
    -> set student.sage = student.sage+2
    -> where student.sno in(
    -> select sc.sno
    -> from sc
    -> where sc.grade >=80 and sc.grade<=89);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   22 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   21 | CS    | 否          |
| 200215123 | 王敏  | 女   |   18 | MA    | 否          |
| 200215125 | 张立  | 男   |   19 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
4 rows in set (0.00 sec)

（9）使用 insert 语句增加两门课程：C 语言和人工智能，并查询出来
mysql> select * from course;
+-----+----------+------+---------+
| Cno | Cname    | Cpno | Ccredit |
+-----+----------+------+---------+
| 1   | 数据库   | 5    |       4 |
| 2   | 数学     | NULL |       2 |
| 3   | 信息系统 | 1    |       4 |
| 4   | 操作系统 | 6    |       3 |
| 5   | 数据结构 | 7    |       4 |
| 6   | 数据处理 | NULL |       2 |
| 7   | java     | 6    |       4 |
+-----+----------+------+---------+
7 rows in set (0.00 sec)

mysql> insert into course(cno,cname)
    -> values ('22','C语言');
Query OK, 1 row affected (0.00 sec)

mysql> insert into course(cno,cname)
    -> values ('88','人工智能');
Query OK, 1 row affected (0.00 sec)

mysql> select * from course;
+-----+----------+------+---------+
| Cno | Cname    | Cpno | Ccredit |
+-----+----------+------+---------+
| 1   | 数据库   | 5    |       4 |
| 2   | 数学     | NULL |       2 |
| 22  | C语言    | NULL |    NULL |
| 3   | 信息系统 | 1    |       4 |
| 4   | 操作系统 | 6    |       3 |
| 5   | 数据结构 | 7    |       4 |
| 6   | 数据处理 | NULL |       2 |
| 7   | java     | 6    |       4 |
| 88  | 人工智能 | NULL |    NULL |
+-----+----------+------+---------+
9 rows in set (0.00 sec)

（10）使用 delete 语句把人工智能课程删除，并查询出来。
mysql> delete from course
    -> where cname = '人工智能';
Query OK, 1 row affected (0.00 sec)

mysql> select * from course;
+-----+----------+------+---------+
| Cno | Cname    | Cpno | Ccredit |
+-----+----------+------+---------+
| 1   | 数据库   | 5    |       4 |
| 2   | 数学     | NULL |       2 |
| 22  | C语言    | NULL |    NULL |
| 3   | 信息系统 | 1    |       4 |
| 4   | 操作系统 | 6    |       3 |
| 5   | 数据结构 | 7    |       4 |
| 6   | 数据处理 | NULL |       2 |
| 7   | java     | 6    |       4 |
+-----+----------+------+---------+
8 rows in set (0.00 sec)

实验三.SQL高级实验

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
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
(1)创建 CS 系的视图 CS_View

mysql> create view cs_view as
    -> select *
    -> from student
    -> where sdept = 'CS';
Query OK, 0 rows affected (0.04 sec)

mysql> select * from cs_view;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   20 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   19 | CS    | 否          |
+-----------+-------+------+------+-------+-------------+
2 rows in set (0.02 sec)
(2)在视图 CS_View 上查询 CS 系选修了 1 号课程的学生

mysql> select cs_view.sno,sname,sage
    -> from cs_view,sc
    -> where cs_view.sno = sc.sno and sc.cno = '1';
+-----------+-------+------+
| Sno       | Sname | Sage |
+-----------+-------+------+
| 200215121 | 李勇  |   20 |
+-----------+-------+------+
1 row in set (0.02 sec)
(3)创建 IS 系成绩大于 80 的学生的视图 IS_View
mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   20 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   19 | CS    | 否          |
| 200215123 | 王敏  | 女   |   18 | MA    | 否          |
| 200215125 | 张立  | 男   |   19 | IS    | 否          |
| 200215126 | 张三  | NULL | NULL | IS    | NULL        |
| 200215128 | 李四  | NULL | NULL | IS    | NULL        |
+-----------+-------+------+------+-------+-------------+
6 rows in set (0.00 sec)

mysql> select * from sc;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 200215121 | 1   |    92 |
| 200215121 | 2   |    85 |
| 200215121 | 3   |    88 |
| 200215122 | 2   |    90 |
| 200215122 | 3   |    80 |
| 200215125 | 3   |    79 |
| 200215126 | 3   |    90 |
| 200215128 | 1   |    88 |
+-----------+-----+-------+
8 rows in set (0.00 sec)

mysql> create view is_view as
    -> select student.sno,sname,sage,ssex,sdept,cno,grade
    -> from student,sc
    -> where sdept = 'IS' and student.sno = sc.sno and sc.grade >=80;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from is_view;
+-----------+-------+------+------+-------+-----+-------+
| sno       | sname | sage | ssex | sdept | cno | grade |
+-----------+-------+------+------+-------+-----+-------+
| 200215126 | 张三  | NULL | NULL | IS    | 3   |    90 |
| 200215128 | 李四  | NULL | NULL | IS    | 1   |    88 |
+-----------+-------+------+------+-------+-----+-------+
2 rows in set (0.01 sec)
(4)在视图 IS_View 查询 IS 系成绩大于 80 的学生
mysql> select * from is_view where grade >=80;
+-----------+-------+------+------+-------+-----+-------+
| sno       | sname | sage | ssex | sdept | cno | grade |
+-----------+-------+------+------+-------+-----+-------+
| 200215126 | 张三  | NULL | NULL | IS    | 3   |    90 |
| 200215128 | 李四  | NULL | NULL | IS    | 1   |    88 |
+-----------+-------+------+------+-------+-----+-------+
2 rows in set (0.01 sec)
(5)删除视图 IS_View
mysql> drop view is_view;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from is_view;
ERROR 1146 (42S02): Table 'csedb_u201911749.is_view' doesn't exist
(6) 利用可视化窗口创建 2 个不同的用户 U1 和 U2,利用系统管理员给 U1 授予 Student 表的
查询和更新的权限，给 U2 对 SC 表授予插入的权限。然后用 U1 登录，分别 1）查询学生表
的信息；2）把所有学生的年龄增加 1 岁，然后查询；3）删除 IS 系的学生；4）查询 CS 系
的选课信息。用 U2 登录，分别 1）在 SC 表中插入 1 条记录（‘200215122’，‘1’，75）；2）
查询 SC 表的信息，3）查询视图 CS_View 的信息。
//创建用户u1
mysql> create user 'u1'@'%' identified by '123456';
Query OK, 0 rows affected (0.05 sec)
//创建用户u2
mysql> create user 'u2'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
//为u1设置权限
mysql> grant select,update on student to 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)
//为u2设置权限
mysql> grant insert on sc to 'u2'@'%';
Query OK, 0 rows affected (0.01 sec)
//刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
//查看用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: 'u1'@'%';                       |
| User: 'u2'@'%';                       |
| User: 'mysql.infoschema'@'localhost'; |
| User: 'mysql.session'@'localhost';    |
| User: 'mysql.sys'@'localhost';        |
| User: 'root'@'localhost';             |
+---------------------------------------+
6 rows in set (0.00 sec)
//命令行以不同的用户登录，先设置mysql环境变量，cmd命令行使用：mysql -u u1 -p 密码 以用户u1登录
###############################################################################
C:\Users\Ke>mysql -u u1 -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| csedb_u201911749   |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)

mysql> use csedb_u201911749
Database changed
mysql> show tables;   //可以看到，u1用户由于管理员设置的权限，只能看到student
+----------------------------+
| Tables_in_csedb_u201911749 |
+----------------------------+
| student                    |
+----------------------------+
1 row in set (0.00 sec)
//u1查询student表
mysql> select * from student;
+-----------+--------+------+------+-------+-------------+
| Sno       | Sname  | Ssex | Sage | Sdept | Scholarship |
+-----------+--------+------+------+-------+-------------+
| 200215121 | 李勇   | 男   |   20 | CS    | 否          |
| 200215122 | 刘晨   | 女   |   19 | CS    | 否          |
| 200215123 | 王敏   | 女   |   18 | MA    | 否          |
| 200215125 | 张立   | 男   |   19 | IS    | 否          |
| 200215126 | 张三   | NULL |   18 | IS    | NULL        |
| 200215128 | 李四   | NULL |   20 | IS    | NULL        |
+-----------+--------+------+------+-------+-------------+
6 rows in set (0.00 sec)
//u1更新年龄
mysql> update student
    -> set sage = sage+1;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 0
//u1重新查询student
mysql> select * from student;
+-----------+--------+------+------+-------+-------------+
| Sno       | Sname  | Ssex | Sage | Sdept | Scholarship |
+-----------+--------+------+------+-------+-------------+
| 200215121 | 李勇   | 男   |   21 | CS    | 否          |
| 200215122 | 刘晨   | 女   |   20 | CS    | 否          |
| 200215123 | 王敏   | 女   |   19 | MA    | 否          |
| 200215125 | 张立   | 男   |   20 | IS    | 否          |
| 200215126 | 张三   | NULL |   19 | IS    | NULL        |
| 200215128 | 李四   | NULL |   21 | IS    | NULL        |
+-----------+--------+------+------+-------+-------------+
6 rows in set (0.00 sec)

mysql> delete from student
    -> where sdept = 'IS';
ERROR 1142 (42000): DELETE command denied to user 'u1'@'localhost' for table 'student'   //u1没有权限
mysql> select sc.sno,sc.cno
    -> from student,sc
    -> where student.sno = sc.sno;
ERROR 1142 (42000): SELECT command denied to user 'u1'@'localhost' for table 'sc'  //u1没有权限
##############################################################################
C:\Users\Ke>mysql -u u2 -p   //以u2的身份登录
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| csedb_u201911749   |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> use csedb_u201911749;
Database changed
mysql> show tables;   //u2只能看到sc表
+----------------------------+
| Tables_in_csedb_u201911749 |
+----------------------------+
| sc                         |
+----------------------------+
1 row in set (0.00 sec)

mysql> insert into sc
    -> values('200215122','1',75);
Query OK, 1 row affected (0.01 sec)   //u2具有插入权限，插入成功

mysql> select * from sc;
ERROR 1142 (42000): SELECT command denied to user 'u2'@'localhost' for table 'sc'  //u2没有权限
mysql> select * from view cs_view;
ERROR 1142 (42000): SELECT command denied to user 'u2'@'localhost' for table 'view'  //u2没有权限
############################################################################
(7) 用系统管理员登录，收回 U1 的所有权限
mysql> revoke select,update on student from 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)
##########################################################################
(8) 用 U1 登录，查询学生表的信息
C:\Users\Ke>mysql -u u1 -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18

mysql> use csedb_u201911749
Database changed
mysql> select * from student;
ERROR 1142 (42000): SELECT command denied to user 'u1'@'localhost' for table 'student' //u1已经没有的select权限
###########################################################################
(9) 用系统管理员登录
(10) 对 SC 表建立一个更新触发器，当更新了 SC 表的成绩时，如果更新后的成绩大于等于
95，则检查该成绩的学生是否有奖学金，如果奖学金是“否”，则修改为“是”。如果修改后的
成绩小于 95，则检查该学生的其他成绩是不是有大于 95 的，如果都没有，且修改前的成绩
是大于 95 时，则把其奖学金修改为”否”。然后进行成绩修改，并进行验证是否触发器正确
执行。1）首先把某个学生成绩修改为 98，查询其奖学金。2）再把刚才的成绩修改为 80，
再查询其奖学金。
//定义触发器
mysql> delimiter $  //把终结符改为$,这样begin和end之间使用的‘;’会被存储进去
mysql> create trigger sctr after update on sc
    -> for each row
    -> begin
    -> declare max_grade int;
    -> set max_grade = (select max(grade) from sc where sc.sno=new.sno);
    -> update student set student.scholarship = '是' where student.sno = new.sno and max_grade >=95;
    -> update student set student.scholarship = '否' where student.sno = new.sno and max_grade <95;
    -> end$
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
//初始数据如下：
mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   21 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   20 | CS    | 否          |
| 200215123 | 王敏  | 女   |   19 | MA    | 否          |
| 200215125 | 张立  | 男   |   20 | IS    | 否          |
| 200215126 | 张三  | NULL |   19 | IS    | 否          |
| 200215128 | 李四  | NULL |   21 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
6 rows in set (0.00 sec)

mysql> select * from sc;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 200215121 | 1   |    92 |
| 200215121 | 2   |    85 |
| 200215121 | 3   |    88 |
| 200215122 | 1   |    75 |
| 200215122 | 2   |    90 |
| 200215122 | 3   |    80 |
| 200215125 | 3   |    79 |
| 200215126 | 3   |    90 |
| 200215128 | 1   |    88 |
+-----------+-----+-------+
9 rows in set (0.00 sec)
//修改200215121的1号课成绩为98
mysql> update sc set grade = 98 where sno = '200215121' and cno = '1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
//查看student，奖学金由否改为了是
mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   21 | CS    | 是          |
| 200215122 | 刘晨  | 女   |   20 | CS    | 否          |
| 200215123 | 王敏  | 女   |   19 | MA    | 否          |
| 200215125 | 张立  | 男   |   20 | IS    | 否          |
| 200215126 | 张三  | NULL |   19 | IS    | 否          |
| 200215128 | 李四  | NULL |   21 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
6 rows in set (0.00 sec)
//修改刚才的成绩为88
mysql> update sc set grade = 88 where sno = '200215121' and cno = '1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
//修改项的奖学金由是改成了否
mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   21 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   20 | CS    | 否          |
| 200215123 | 王敏  | 女   |   19 | MA    | 否          |
| 200215125 | 张立  | 男   |   20 | IS    | 否          |
| 200215126 | 张三  | NULL |   19 | IS    | 否          |
| 200215128 | 李四  | NULL |   21 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
6 rows in set (0.00 sec)
（11）删除刚定义的触发器
//删除前查询触发器
mysql> show triggers from csedb_u201911749\G;
*************************** 1. row ***************************
             Trigger: sctr
               Event: UPDATE
               Table: sc
           Statement: begin
declare max_grade int;
set max_grade = (select max(grade) from sc where sc.sno=new.sno);
update student set student.scholarship = '是' where student.sno = new.sno and max_grade >=95;
update student set student.scholarship = '否' where student.sno = new.sno and max_grade <95;
end
              Timing: AFTER
             Created: 2021-11-12 21:32:29.26
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified
//删除触发器
mysql> drop trigger sctr;
Query OK, 0 rows affected (0.02 sec)
//重新查询，触发器已被删除
mysql> show triggers from csedb_u201911749\G;
Empty set (0.00 sec)

ERROR:
No query specified
/*
*注意一下12-13这两个个，不知道为啥我第一次创建了一个临时表去保存过程和函数的查询数据，其实不*用创建这个表
*不必将select的结果insert到临时表，再去查临时表，直接在存储过程里去select即可，调用之后会*显示select的数据
*但是对于函数调用，函数调用是无法返回一个表的，因此借助临时表还是有必要的
*/
（12）定义一个存储过程计算 CS 系的课程的平均成绩和最高成绩，在查询分析器或查询编
辑器中执行存储过程，查看结果。
mysql> select * from student;
+-----------+-------+------+------+-------+-------------+
| Sno       | Sname | Ssex | Sage | Sdept | Scholarship |
+-----------+-------+------+------+-------+-------------+
| 200215121 | 李勇  | 男   |   21 | CS    | 否          |
| 200215122 | 刘晨  | 女   |   20 | CS    | 否          |
| 200215123 | 王敏  | 女   |   19 | MA    | 否          |
| 200215125 | 张立  | 男   |   20 | IS    | 否          |
| 200215126 | 张三  | NULL |   19 | IS    | 否          |
| 200215128 | 李四  | NULL |   21 | IS    | 否          |
+-----------+-------+------+------+-------+-------------+
6 rows in set (0.00 sec)
mysql> select * from sc;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 200215121 | 1   |    88 |
| 200215121 | 2   |    85 |
| 200215121 | 3   |    88 |
| 200215122 | 1   |    75 |
| 200215122 | 2   |    90 |
| 200215122 | 3   |    80 |
| 200215125 | 3   |    79 |
| 200215126 | 3   |    90 |
| 200215128 | 1   |    88 |
+-----------+-----+-------+
9 rows in set (0.00 sec)
mysql> delimiter $ //创建存储过程
mysql> create procedure cs_grade(
    -> out avg_grade int,
    -> out max_grade int)
    -> begin
    -> select avg(grade)
    -> into avg_grade
    -> from student,sc
    -> where student.sdept = 'cs' and student.sno = sc.sno;
    -> select max(grade)
    -> into max_grade
    -> from student,sc
    -> where student.sdept = 'cs' and student.sno = sc.sno;
    -> end $
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ; //重新定义终结符为';'
mysql> call cs_grade(@avg_grade,@max_grade);//调用存储过程
Query OK, 1 row affected (0.00 sec)

mysql> select @avg_grade,@max_grade;
+------------+------------+
| @avg_grade | @max_grade |
+------------+------------+
|         84 |         90 |
+------------+------------+
1 row in set (0.00 sec)

（13）定义一个带学号为参数的查看某个学号的所有课程的成绩，查询结果要包含学生姓
名。进行验证。
//先创建临时表保存数据
mysql> create temporary table stu_info(
    -> sno  char(9) null,
    -> sname char(20) null,
    -> cno char(5) null,
    -> cname char(20)null,
    -> grade int);
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $  //改变终结符
//建立存储过程，传入sid char(9) ,insert into table select.....把select的结果插入table
mysql> create procedure stu_grade(
    -> in sid char(9))
    -> begin
    -> insert into stu_info
    -> select sc.sno,sname,sc.cno,cname,grade from student,course,sc where sid = sc.sno and sc.sno = student.sno and sc.cno =course.cno;
    -> end$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;  //未调用存储过程前，临时表为空
mysql> select * from stu_info;
Empty set (0.00 sec)
//调用存储过程
mysql> call stu_grade('200215121');
Query OK, 3 rows affected (0.01 sec)
//查看临时表结果
mysql> select * from stu_info;
+-----------+-------+------+----------+-------+
| sno       | sname | cno  | cname    | grade |
+-----------+-------+------+----------+-------+
| 200215121 | 李勇  | 1    | 数据库   |    88 |
| 200215121 | 李勇  | 2    | 数学     |    85 |
| 200215121 | 李勇  | 3    | 信息系统 |    88 |
+-----------+-------+------+----------+-------+
3 rows in set (0.00 sec)
（14）把上一题改成函数。再进行验证。
//先把创建函数的功能打开，检查下面的变量是不是on
mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> set global log_bin_trust_function_creators =1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)
//创建函数，传入sid
mysql> delimiter $
mysql> create function stu_grade2(sid char(9))
    -> returns tinyint
    -> begin
    -> insert into stu_info
    -> select sc.sno,sname,sc.cno,cname,grade from student,course,sc where sid = sc.sno and sc.sno = student.sno and sc.cno =course.cno;
    -> return 1;
    -> end$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select * from stu_info; //未调用函数前的临时表
+-----------+-------+------+----------+-------+
| sno       | sname | cno  | cname    | grade |
+-----------+-------+------+----------+-------+
| 200215121 | 李勇  | 1    | 数据库   |    88 |
| 200215121 | 李勇  | 2    | 数学     |    85 |
| 200215121 | 李勇  | 3    | 信息系统 |    88 |
+-----------+-------+------+----------+-------+
3 rows in set (0.00 sec)
//执行函数，显示的1时返回值1
mysql> select stu_grade2('200215122');
+-------------------------+
| stu_grade2('200215122') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.01 sec)
//再次查看临时表
mysql> select * from stu_info;
+-----------+-------+------+----------+-------+
| sno       | sname | cno  | cname    | grade |
+-----------+-------+------+----------+-------+
| 200215121 | 李勇  | 1    | 数据库   |    88 |
| 200215121 | 李勇  | 2    | 数学     |    85 |
| 200215121 | 李勇  | 3    | 信息系统 |    88 |
| 200215122 | 刘晨  | 1    | 数据库   |    75 |
| 200215122 | 刘晨  | 2    | 数学     |    90 |
| 200215122 | 刘晨  | 3    | 信息系统 |    80 |
+-----------+-------+------+----------+-------+
6 rows in set (0.00 sec)
（15）在 SC 表上定义一个完整性约束，要求成绩再 0-100 之间。定义约束前，先把某个学
生的成绩修改成 120，进行查询，再修改回来。定义约束后，再把该学生成绩修改为 120，
然后进行查询

mysql> update sc set grade = 120
    -> where sno = '200215121' and cno ='1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sc;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 200215121 | 1   |   120 |
| 200215121 | 2   |    85 |
| 200215121 | 3   |    88 |
| 200215122 | 1   |    75 |
| 200215122 | 2   |    90 |
| 200215122 | 3   |    80 |
| 200215125 | 3   |    79 |
| 200215126 | 3   |    90 |
| 200215128 | 1   |    88 |
+-----------+-----+-------+
9 rows in set (0.00 sec)

mysql> update sc set grade = 88
    -> where sno = '200215121' and cno ='1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sc;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 200215121 | 1   |    88 |
| 200215121 | 2   |    85 |
| 200215121 | 3   |    88 |
| 200215122 | 1   |    75 |
| 200215122 | 2   |    90 |
| 200215122 | 3   |    80 |
| 200215125 | 3   |    79 |
| 200215126 | 3   |    90 |
| 200215128 | 1   |    88 |
+-----------+-----+-------+
9 rows in set (0.00 sec)
//设置用户自定义约束
mysql> alter table sc add constraint check_grade check(grade >=0 and grade <=100);
Query OK, 9 rows affected (0.09 sec)
Records: 9  Duplicates: 0  Warnings: 0
//更新失败，提示检查约束不通过
mysql> update sc set grade = 120
    -> where sno = '200215121' and cno ='1';
ERROR 3819 (HY000): Check constraint 'check_grade' is violated.

实验四.数据库设计

实验四要求

与其说是数据库设计，不如说使用高级语言来操作数据库，编写一个信息管理系统的应用。

我使用Maven导入Mybatis，利用Mybatis框架来操作数据库，相比JDBC来说相对简单，不必去进行数据库连接等操作，通过配置mybatis-config.xml来保证与数据库的连接，通过配置Mapper.xml来添加开发过程中使用到的sql语句，实现数据操作，剩下的就是在高级语言层面的用户交互与数据处理工作了。

经典增删改查🤣😂

HUST数据库实验-基于Mybatis的学生信息管理系统

参考链接

MySQL 教程 | 菜鸟教程 (runoob.com)

【触发器】MySQL触发器使用详解 - QiaoZhi - 博客园 (cnblogs.com)

mysql存储过程详细教程 - 简书 (jianshu.com)

MySQL存储过程入门 - MySQL教程™ (yiibai.com)

SQL INSERT INTO SELECT 语句 | 菜鸟教程 (runoob.com)

mysql里怎样创建函数-mysql教程-PHP中文网

MySql创建自定义函数（Function）_万里归来少年心-CSDN博客_mysql 自定义函数

mybatis – MyBatis 3 | 入门

Mybatis框架入门教程 (biancheng.net)

HUST
HUST

本文作者: ziyikee
本文链接: https://ziyikee.fun/2021/11/20/HUST%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AE%9E%E9%AA%8C/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！