SQL 实验详细源码参考
-- ----------------------------------------------------------------------------------
2 -- 数据库初始化 （实验二 数据描述、定义实验）
3 -- ----------------------------------------------------------------------------------
4 create database s_13420228;
5 use s_13420228;
6
7 create table student(
8 sno varchar(8),
9 sname varchar(20) not null, -- 姓名
10 sage smallint, -- 年龄
11 ssex char(8), -- 性别
12 sdept varchar(20) , -- 所在系
13 constraint s_pk primary key (sno), -- 主码
14 constraint s1 check(sage between 16 and 40),
15 constraint s2 check(ssex in(‘male‘,‘female‘)),
16 constraint s3 check(grade between 0 and 100)
17 );
18
19 create table course(
20 cno varchar(7), -- 课程号
21 cname varchar(50), -- 课程名
22 cpno varchar(50), -- 先行课
23 ccredit numeric(2,1), -- 学分,精确存储3.5
24 constraint c_pk primary key (cno,cname), -- 主码
25 constraint s4 foreign key (cpno) references course (cno) -- 先行课必须要存在
26 );
27
28 create table sc( -- 学生选课表
29 sno varchar(8), -- 学号
30 cno varchar(7), -- 课程号
31 grade smallint, -- 成绩
32 constraint sc_pk primary key (sno,cno), -- 主码
33 constraint s5 foreign key (sno) references student(sno) on delete cascade on update cascade, -- sc中的学号必须存在,级联删除、更新
34 constraint s6 foreign key (cno) references course(cno) on delete cascade on update cascade -- sc中课程号必须存在
35 );
36
37 insert into course values(‘01‘, ‘Database‘, null, 4.0); -- 插入数据 course
38 insert into course values(‘02‘, ‘Operating System‘, null, 3.5);
39 insert into course values(‘03‘, ‘Computer Networking‘, null, 4.0);
40 insert into course values(‘04‘, ‘Data Structures‘, null, 4.0);
41 insert into course values(‘05‘, ‘The C++ Programming Language‘, null, 4.0);
42 insert into course values(‘06‘, ‘The C Programming Language‘, null, 4.0);
43 insert into course values(‘07‘, ‘Physical Principles‘, null, 2.0);
44 insert into course values(‘08‘, ‘Music Video Production‘, null, 3.0);
45 insert into course values(‘09‘, ‘Computational Biology‘, null, 3.0);
46 insert into course values(‘10‘, ‘Genetics‘, null, 3.0); -- Biology
47 insert into course values(‘11‘, ‘Intro. to Biology‘, null, 3.0);
48
49 insert into student values(‘13420201‘, ‘Zhang‘, 18,‘male‘, ‘CS‘); -- 插入数据 student
50 insert into student values(‘13420202‘, ‘Shankar‘, 20,‘male‘, ‘CS‘);
51 insert into student values(‘13420203‘, ‘Brandt‘, 18,‘male‘, ‘CS‘);
52 insert into student values(‘13420204‘, ‘Chavez‘, 19,‘female‘, ‘CS‘);
53 insert into student values(‘13312205‘, ‘Peltier‘, 21,‘female‘, ‘Music‘);
54 insert into student values(‘13312213‘, ‘Levy‘, 20,‘female‘, ‘Music‘);
55 insert into student values(‘13312137‘, ‘Williams‘,20, ‘male‘, ‘Music‘);
56 insert into student values(‘13523111‘, ‘Bourikas‘, 19,‘male‘, ‘Biology‘);
57 insert into student values(‘13523222‘, ‘Smith‘, 19,‘male‘, ‘Biology‘);
58 insert into student values(‘13453334‘, ‘Snow‘, 19,‘male‘, ‘Physics‘);
59
60 insert into sc values(‘13420201‘, ‘01‘, 80); -- 插入数据 sc CS
61 insert into sc values(‘13420201‘, ‘02‘, 70);
62 insert into sc values(‘13420201‘, ‘03‘, 90);
63 insert into sc values(‘13420201‘, ‘04‘, 40);
64 insert into sc values(‘13420201‘, ‘05‘, 60);
65 insert into sc values(‘13420201‘, ‘06‘, 90);
66
67 insert into sc values(‘13420202‘, ‘01‘, 80);
68 insert into sc values(‘13420202‘, ‘03‘, 90);
69 insert into sc values(‘13420202‘, ‘04‘, 30);
70 insert into sc values(‘13420202‘, ‘05‘, 70);
71 insert into sc values(‘13420202‘, ‘06‘, 90);
72
73 insert into sc values(‘13420203‘, ‘01‘, 80);
74 insert into sc values(‘13420203‘, ‘02‘, 90);
75 insert into sc values(‘13420203‘, ‘03‘, 90);
76 insert into sc values(‘13420203‘, ‘04‘, 85);
77 insert into sc values(‘13420203‘, ‘05‘, 75);
78 insert into sc values(‘13420203‘, ‘06‘, 90);
79
80 insert into sc values(‘13420204‘, ‘01‘, 55);
81 insert into sc values(‘13420204‘, ‘02‘, 55);
82 insert into sc values(‘13420204‘, ‘03‘, 90);
83 insert into sc values(‘13420204‘, ‘04‘, 55);
84 insert into sc values(‘13420204‘, ‘05‘, 70);
85 insert into sc values(‘13420204‘, ‘06‘, 90);
86
87 insert into sc values(‘13312205‘, ‘08‘, 90); -- Music
88 insert into sc values(‘13312213‘, ‘08‘, 60);
89 insert into sc values(‘13312137‘, ‘08‘, 60);
90
91 insert into sc values(‘13523111‘, ‘09‘, 60); -- Biology
92 insert into sc values(‘13523111‘, ‘10‘, 59);
93 insert into sc values(‘13523111‘, ‘11‘, 80);
94
95 insert into sc values(‘13453334‘, ‘07‘, 70); -- Physics
96
97 update course set cpno = ‘04‘ where cno = ‘01‘; -- 更新 course
98 update course set cpno = ‘04‘ where cno = ‘02‘;
99 update course set cpno = ‘05‘ where cno = ‘03‘;
100 update course set cpno = ‘05‘ where cno = ‘04‘;
101 update course set cpno = ‘06‘ where cno = ‘05‘;
102
103 -- --------------------初始化完成----------------------------------------------------------
104
105 -- 建立索引可以加快查询速度
106 create index grade_index on sc(grade); -- 多值索引
107 create unique index sno_index on student(sno); -- 唯一索引，每一个索引值只对应一个唯一的数据记录
108 drop index grade_index on sc; -- 删除索引
109 drop index sno_index on student;
110
111 alter table student add birthday date; -- 添加属性列
112 alter table student drop sage; -- 删除属性列
113
114 alter table course drop foreign key s3; -- 删除约束s3
115 alter table course add constraint s3 foreign key (cpno) references course (cno)
116 on delete cascade on update cascade; -- 先行课必须要存在,添加级联删除、更新
117
118 delete from course where cno = ‘04‘; -- 删除特定元组
119
120 delete from student; -- 删除关系student的所有元组
121 delete from sc;
122 delete from course;
123 drop table student; -- 完全删除student，包括student这个模式及其所有元组
124 drop table sc;
125 drop table course;
126
127 -- ----------------------------------------------------------------------------------
128 -- (实验三 数据更新实验)
129 -- ----------------------------------------------------------------------------------
130
131 -- 插入某个学生的选课信息(外码约束，拒绝的)
132 insert into sc values(‘13436214‘,‘01‘,0);
133
134 -- 求各系学生的平均成绩，并把结果存入数据库
135 create table sdept_avg_grade (
136 sdept varchar(20),
137 avg_grade smallint,
138 constraint sag_pk primary key (sdept),
139 constraint s7 check(avg_grade between 0 and 100)
140 -- constraint s8 foreign key (sdept) references student(sdept) -- 由于外码属性必须为莫关系的主码，此外码定义语句错误
141 );
142
143 insert into sdept_avg_grade
144 select sdept,avg(grade) from sc,student where sc.sno = student.sno group by sdept ;
145
146 drop tables sdept_avg_grade;
147
148 -- 将“CS”系全体学生的成绩置零
149 update sc set grade = 0 where ‘CS‘ = (
150 select sdept from student where sc.sno = student.sno);
151
152 -- 删除“CS”系全体学生的选课记录
153 delete from sc where ‘CS‘ = (
154 select sdept from student where sc.sno = student.sno);
155
156 -- 删除学号为“13420201”的相关信息(级联删除cascade)
157 delete from student where sno = ‘13420201‘;
158
159 -- 将学号为“13420202”的学生的学号修改为“12420202”（级联更新）
160 update student set sno = ‘12420202‘ where sno = ‘13420202‘;
161
162 -- 把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S_GRADE（SNO，AVG_GRADE）
163 create table s_grade(
164 sno varchar(8),
165 avg_grade smallint,
166 constraint sg_pk primary key (sno),
167 constraint s9 foreign key (sno) references student(sno),
168 constraint s10 check(grade between 0 and 100)
169 );
170 insert into s_grade
171 select sc.sno,avg(grade) from sc,student where sc.sno = student.sno and ssex = ‘male‘
172 group by sc.sno having avg(grade) > 80;
173
174 select * from s_grade;
175 drop table s_grade;
176
177 -- 把选修了课程名为“Data Structures”的学生的成绩提高10%；
178 update sc set grade = grade * 1.1 where ‘Data Structures‘ = (
179 select cname from course where sc.cno = course.cno );
180
181 select sno,grade from sc,course where sc.cno = course.cno and cname = ‘Data Structures‘;
182 select avg(grade) from sc,course where sc.cno = course.cno and cname = ‘Data Structures‘;
183
184
185 -- ---------------------------------------------------------------------------------------------
186 -- (实验四 数据查询实验)
187 -- ---------------------------------------------------------------------------------------------
188
189 -- 1． 查询学生的基本信息；
190 select * from student;
191
192 -- 2． 查询“CS”系学生的基本信息
193 select * from student where sdept = ‘CS‘;
194
195 -- 3． 查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；
196 select sno,sname from student where sdept = ‘CS‘ and sage not between 19 and 21;
197
198 -- 4． 找出最大年龄；
199 select max(sage) from student;
200
201 -- 5． 找出“CS”系年龄最大的学生，显示其学号、姓名；
202 select sno,sname from student where sdept = ‘CS‘ and sage = (
203 select max(sage) from student where sdept = ‘CS‘);
204
205 -- 6． 找出各系年龄最大的学生，显示其学号、姓名；
206 select sno,sname from student as s1 where sage = (
207 select max(sage) from student as s2 where s1.sdept = s2.sdept);
208
209 -- 7． 统计“CS”系学生的人数；
210 select count(sno) as number_of_student from student where sdept = ‘CS‘;
211
212 -- 8． 统计各系学生的人数，结果按升序排列；
213 select sdept, count(sno) as number_of_student