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