SQL Server 数据库使用练习

实验环境

  • Microsoft SQL Server 2017 Express Edition
  • Microsoft SQL Server Management Studio 17.6

基本表的建立与修改

成绩管理数据库GradeManager包括四个表: 学生表Student; 课程表Course; 班级表Class; 成绩表Grade.

学生表 Student

表结构定义

属性名数据类型可否为空含义
Snochar(7)学号(唯一)
Snamevarchar(20)学生姓名
Ssexchar(2)性别
Sagesmallint年龄
Clnochar(5)所在班级
1
2
3
4
5
6
7
-- 建立学生表
CREATE TABLE Student(
Sno char(7) NOT NULL UNIQUE,
Sname varchar(20) NOT NULL,
Ssex char(2) NOT NULL,
Sage smallint,
Clno char(5) NOT NULL);

表数据内容

SnoSnameSsexSageClno
2000101李勇2000311
2000102刘诗晨1900311
2000103王一鸣2000312
2000104张婷婷2100312
2001101李勇敏1901311
2001102贾向东2201311
2001103陈宝玉2001311
2001104张逸凡2101311
1
2
3
4
5
6
7
8
9
-- 填充学生表
INSERT INTO Student VALUES('2000101','李勇','男',20,'00311');
INSERT INTO Student VALUES('2000102','刘诗晨','女',19,'00311');
INSERT INTO Student VALUES('2000103','王一鸣','男',20,'00312');
INSERT INTO Student VALUES('2000104','张婷婷','女',21,'00312');
INSERT INTO Student VALUES('2001101','李勇敏','女',19,'01311');
INSERT INTO Student VALUES('2001102','贾向东','男',22,'01311');
INSERT INTO Student VALUES('2001103','陈宝玉','男',20,'01311');
INSERT INTO Student VALUES('2001104','张逸凡','男',21,'01311');

课程表 Couse

表结构定义

属性名数据类型可否为空含义
Cnochar(1)课程号(唯一)
Cnamevarchar(20)课程名称
Creditsmallint学分
1
2
3
4
5
-- 建立课程表
CREATE TABLE Course(
Cno char(1) NOT NULL UNIQUE,
Cname varchar(20) NOT NULL,
Credit smallint);

表数据内容

CnoCnmaeCredit
1数据库4
2离散数学3
3管理信息系统2
4操作系统4
5数据结构4
6数据处理2
7C语言4
1
2
3
4
5
6
7
8
-- 填充课程表
INSERT INTO Course VALUES('1','数据库',4);
INSERT INTO Course VALUES('2','离散数学',3);
INSERT INTO Course VALUES('3','管理信息系统',2);
INSERT INTO Course VALUES('4','操作系统',4);
INSERT INTO Course VALUES('5','数据结构',4);
INSERT INTO Course VALUES('6','数据处理',2);
INSERT INTO Course VALUES('7','C语言',4);

班级表 CLass

表结构定义

属性名数据类型可否为空含义
Clnochar(5)班级号(唯一)
Specialityvarchar(20)班级所在专业
Inyearchar(4)入校年份
Numberint班级人数
Monitorchar(7)班长学号
1
2
3
4
5
6
7
-- 建立班级表
CREATE TABLE Class(
Clno char(5) NOT NULL UNIQUE,
Speciality varchar(20) NOT NULL,
Inyear char(4) NOT NULL,
Number int,
Monitor char(7));

表数据内容

ClnoSpecialityInyearNumberMonitor
00311计算机软件20001202000101
00312计算机应用20001402000103
01311计算机软件20012202001103
1
2
3
4
-- 填充班级表
INSERT INTO Class VALUES('00311','计算机软件','2000',120,'2000101');
INSERT INTO Class VALUES('00312','计算机应用','2000',140,'2000103');
INSERT INTO Class VALUES('01311','计算机软件','2001',220,'2001103');

成绩表 Grade

表结构定义

属性名数据类型可否为空含义
Snochar(7)学号
Cnochar(1)课程号
Gmarknumeric(4,1)成绩
1
2
3
4
5
-- 建立成绩表
CREATE TABLE Grade(
Sno char(7) NOT NULL,
Cno char(1) NOT NULL,
Gmark numeric(4,1));

表数据内容

SnoCnoGmark
2000101192
20001013NULL
2000101586
2000102178
2000102655
2000103365
2000103678
2000103566
2000104154
2000104683
2001101270
2001101465
2001102280
20011024NULL
2001103183
2001103276
2001103456
2001103788
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 填充成绩表
INSERT INTO Grade VALUES('2000101','1',92);
INSERT INTO Grade VALUES('2000101','3',NULL);
INSERT INTO Grade VALUES('2000101','5',86);
INSERT INTO Grade VALUES('2000102','1',78);
INSERT INTO Grade VALUES('2000102','6',55);
INSERT INTO Grade VALUES('2000103','3',65);
INSERT INTO Grade VALUES('2000103','6',78);
INSERT INTO Grade VALUES('2000103','5',66);
INSERT INTO Grade VALUES('2000104','1',54);
INSERT INTO Grade VALUES('2000104','6',83);
INSERT INTO Grade VALUES('2001101','2',70);
INSERT INTO Grade VALUES('2001101','4',65);
INSERT INTO Grade VALUES('2001102','2',80);
INSERT INTO Grade VALUES('2001102','4',NULL);
INSERT INTO Grade VALUES('2001103','1',83);
INSERT INTO Grade VALUES('2001103','2',76);
INSERT INTO Grade VALUES('2001103','4',56);
INSERT INTO Grade VALUES('2001103','7',88);

基本表的修改

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
-- 习题3.11

-- (1) 给学生表增加一属性 Nation(民族),数据类型为 Varchar(20);
ALTER TABLE Student
ADD Nation varchar(20);

-- (2) 删除学生表中新增的属性Nation;
ALTER TABLE Student
DROP COLUMN Nation;

-- (3) 向成绩表中插入记录('2001110','3',80);
INSERT INTO Grade(Sno, Cno, Gmark)
VALUES('2001110','3',80);

-- (4) 将学号为"2001110"的学生成绩修改为70分;
UPDATE Grade
SET Gmark = 70
WHERE Sno = '2001110';

-- (5) 删除学号为"2001110"的学生的成绩记录;
DELETE FROM Grade
WHERE Sno = '2001110';

-- (6) 在学生表的 Clno 属性上创建一个名为 IX_Class 的索引, 以班级号的升序排序;
CREATE INDEX IX_Class ON Student (Clno);

-- (7) 删除学生表上的 IX_Class 索引.
DROP INDEX Student.IX_Class;

SELECT语句的基本使用

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
-- 习题3.12

-- (1) 找出所有被学生选修了的课程号;
SELECT DISTINCT Cno FROM Grade;

-- (2) 找出 01311 班女学生的个人信息;
SELECT * FROM Student WHERE Clno = '01311' AND Ssex = '女';

-- (3) 找出 01311 班和 01312 班的学生姓名, 性别, 出生年份;
SELECT Sname, Ssex, (2018 - Sage) AS BirthYear
FROM Student WHERE Clno IN ('01311', '01312');

-- (4) 找出所有姓李的学生的个人信息;
SELECT * FROM Student WHERE Sname LIKE '李%';

-- (5) 找出学生李勇所在班级的学生人数;
--- 写法1: 查询与李勇班级号相同的 Student 即学生元组个数
SELECT COUNT(Sno) FROM Student
WHERE Clno = (SELECT Clno FROM Student WHERE Sname = '李勇');
--- 写法2: 查询 Class 的班级人数属性 Number
SELECT Number FROM Class C, Student S
WHERE C.Clno = S.Clno AND Sname = '李勇';
--- 两种写法的区别在于如何理解题目要求的"学生人数",
--- 前者是数据库中的学生数据个数, 后者是班级的人数数据.

-- (6) 找出名为操作系统的课程平均成绩, 最高分, 最低分;
SELECT AVG(Gmark), MAX(Gmark), MIN(Gmark)
FROM Course C,Grade G
WHERE C.Cno = G.Cno AND Cname = '操作系统';

-- (7) 找出选修了课程的学生人数;
SELECT COUNT(DISTINCT Sno) FROM Grade;

-- (8) 找出选修了课程操作系统的学生人数;
SELECT COUNT(DISTINCT Sno)
FROM Course C,Grade G
WHERE C.Cno = G.Cno AND Cname = '操作系统';

-- (9) 找出 2000 级计算机软件班的成绩为空的学生姓名.
SELECT DISTINCT Sname
FROM Student S,Class C,Grade G
WHERE S.Clno = C.Clno AND S.Sno = G.Sno
AND Inyear = '2000' AND Speciality = '计算机软件'
AND Gmark IS NULL;

SELECT语句的嵌套使用

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
-- 习题3.13

-- (1) 找出与李勇在同一个班级的学生信息;
SELECT * FROM Student
WHERE Clno = (SELECT Clno FROM Student WHERE Sname = '李勇')
AND Sname <> '李勇';

-- (2) 找出所有与李勇有相同选修课程的学生信息;
SELECT * FROM Student
WHERE Sno IN (SELECT Sno FROM Grade
WHERE Cno IN (SELECT Cno FROM Student S, Grade G
WHERE S.Sno = G.Sno AND S.Sname = '李勇'))
AND Sname <> '李勇';

-- (3) 找出年龄介于学生李勇和25岁之间的学生信息(已知李勇年龄小于25岁);
SELECT * FROM Student
WHERE Sage BETWEEN (SELECT Sage FROM Student WHERE Sname = '李勇') AND 25;
--- BETWEEN...AND...查询结果包含范围左右边界

-- (4) 找出选修了课程操作系统的学生学号和姓名;
SELECT S.Sno, Sname FROM Student S, Course C, Grade G
WHERE S.Sno = G.Sno AND C.Cno = G.Cno AND Cname = '操作系统';

-- (5) 找出没有选修1号课程的学生姓名;
SELECT Sname FROM Student
WHERE Sno NOT IN (SELECT Sno FROM Grade WHERE Cno = 1);
--- 相关子查询
SELECT Sname FROM Student S
WHERE NOT EXISTS (SELECT Sno FROM Grade
WHERE S.Sno = Sno AND Cno = 1);

-- (6) 找出选修了全部课程的学生姓名.
SELECT Sname FROM Student S
WHERE NOT EXISTS (SELECT * FROM Course C
WHERE NOT EXISTS (SELECT * FROM Grade G
WHERE G.Cno = C.Cno AND G.Sno = S.Sno));
--- (找出没有一门课程是其未选修的学生姓名)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 习题3.14

-- (1) 查询选修了3号课程的学生学号及其成绩, 并按成绩降序排列;
SELECT Sno, Gmark FROM Grade
WHERE Cno = 3
ORDER BY Gmark DESC;

-- (2) 查询全体学生信息, 查询结果按班级号升序排列, 同一班级学生按年龄降序排列;
SELECT * FROM Student
ORDER BY Clno, Sage DESC;

-- (3) 求每个课程号及相应的选课人数;
SELECT Cno, COUNT(Sno) FROM Grade
GROUP BY Cno;

-- (4) 查询选修了3门以上课程的学生学号.
SELECT Sno FROM Grade
GROUP BY Sno
HAVING COUNT(Cno) > 3;

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
-- 习题3.15

-- (1) 将01311班的全体学生的成绩置零;
UPDATE Grade
SET COLUMN Gmark = 0
WHERE Sno IN (SELECT Sno FROM Student WHERE Clno = '01311');

-- (2) 删除2001级计算机软件的全体学生的选课记录;
DELETE FROM Grade
WHERE Sno IN (SELECT S.Sno FROM Student S, Class C
WHERE S.Clno = C.Clno AND Speciality = '计算机软件');

-- (3) 学生李勇已退学, 从数据库中删除有关他的记录;
UPDATE Class -- 如果李勇是某班班长(Monitor), 则将该班 Monitor 属性置空以保证参照完整性
SET Monitor = NULL
WHERE Monitor = (SELECT Sno FROM Student WHERE Sname = '李勇');
UPDATE Class -- 更新李勇所在班级人数
SET Number = Number - 1
WHERE Clno = (SELECT Clno FROM Student WHERE Sname = '李勇');
DELETE FROM Grade -- 先删引用
WHERE Sno = (SELECT Sno FROM Student WHERE Sname = '李勇');
DELETE FROM Student -- 再删本体
WHERE Sname = '李勇';

-- (4) 对每个班, 求学生的平均年龄, 并把结果存入数据库.
--- 写法1: 建立视图存放查询结果
CREATE VIEW Cl_avg_age
AS SELECT Clno, AVG(Sage) AS avg_age FROM Student;
--- 写法2: 在原 Class 表中增加一列属性存放查询结果
ALTER TABLE Class
ADD avg_age smallint NULL;
UPDATE Class
SET avg_age = (SELECT AVG(Sage) FROM Student
GROUP BY Clno HAVING Clno = Class.Clno);

视图的建立及操作

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
-- 习题3.16

-- (1) 建立 01311 班选修了1号课程的学生视图 Stu_01311_1
CREATE VIEW Stu_01311_1
AS SELECT * FROM Student
WHERE Sno IN (SELECT S.Sno
FROM Student S, Grade G
WHERE S.Sno = G.Sno
AND S.Clno = '01311'
AND G.Cno = 1);

-- (2) 建立 01311 班选修了 1 号课程并且成绩不及格的学生视图 Stu_01311_2
CREATE VIEW Stu_01311_2
AS SELECT * FROM Student
WHERE Sno IN (SELECT S.Sno
FROM Student S, Grade G
WHERE S.Sno = G.Sno
AND S.Clno = '01311'
AND G.Cno = 1
AND Gmark < 60);

-- (3) 建立视图 Stu_year , 由学生学号, 姓名, 出生年份组成
CREATE VIEW Stu_year(Sno, Sname, BirthYear)
AS SELECT Sno, Sname, (2018 - Sage) FROM Student;

-- (4) 查询 1990 年以后出生的学生姓名
SELECT Sname FROM Stu_year WHERE BirthYear > 1990;

-- (5) 查询 01311 班选修了 1 号课程并且成绩不及格的学生的学号, 姓名, 出生年份
SELECT * FROM Stu_year
WHERE Sno IN (SELECT Sno FROM Stu_01311_2);

创建触发器和存储过程

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
-- 在查询分析器(Query Analyzer)中创建以下触发器, 并验证其语法的正确性:

-- 1.
-- 为 Student 表创建一插入和更新触发器 tri_ins_upd_student:
-- 当插入新的学生或者更新学生所在班级号时, 检查该班级的学生人数有没有超过 40 人 ,
-- 如果没有则插入或者更新成功, 如果超出 40 人, 操作回滚.
CREATE TRIGGER tri_ins_upd_student
ON Student
AFTER INSERT,UPDATE
AS
IF 40 < (SELECT Number FROM Class WHERE Clno = (SELECT Clno FROM inserted))
ROLLBACK TRANSACTION

-- 2.
-- 为数据库 GradeManager 创建一存储过程 ap_returncount:
-- 功能是: 输入学生学号, 输出该学生所在的班级人数.
CREATE PROCEDURE ap_returncount
@Sno char(7)
AS
DECLARE @count int
SELECT @count = Number FROM Class
WHERE Clno = (SELECT Clno FROM Student WHERE Sno = @Sno)
RETURN @count


-- 3.
-- 在 Enterprise Manager 中展开 GradeManager 数据库, 展开 Trigger,
-- 查看刚创建的触发器 tri_ins_upd_student, 修改触发器定义,
-- 使之调用存储过程 ap_returncount 实现原来的功能.
CREATE TRIGGER tri_ins_upd_student
ON Student
AFTER INSERT,UPDATE
AS
DECLARE @count int
EXECUTE @count = ap_returncount (SELECT Sno FROM inserted)
IF 40 < @count
ROLLBACK TRANSACTION

参考资料

教材: 《数据库原理(第四版)》张红娟 - 亚马逊