数据库系统实验报告

1、本文格式为word版，下载可任意编辑数据库系统实验报告 数据 库系统 试验 班 班 级 姓 姓 名 学 学 号 任课老师 计算机学院 本 试验一：基本 sql 语言 1. 试验目标 1.1. 娴熟把握基本的 sql 语言。能够利用 sql 定义语言（ddl）创建、删除和修改数据库的基本对象（表、视图、约束和索引等），利用 sql 查询语言（select）对数据中数据进行检索，利用 sql 修改语言（insert、update、delete）对数据中数据进行插入、修改和删除操作。能够利用 sql 掌握语言（dcl）将数据库的基本对象（表和视图等）的各种权限授予其它用户。 1.2. 熟识 sql

2、server 2021 的交互式 sql 语言的操作环境。能够利用 查询分析器进行 sql 语言的编辑和提交。 2. 试验要求 2.1. 写出以下各个试验的 sql 语句。 2.2. 在 sql server 2021 的 查询分析器环境中执行 sql 语句，写出每次执行的结果。 2.3. 对于错误的执行结果，分析产生错误的缘由，并写出订正错误的方案。 2.4. sql 语句的书写规范，要求每个查询子句独占一行，如： select sno, sname from s where sage=20 ; 留意：每次试验之后请作好 数据库 备份。 下次试验之前进行数据库恢复。 3. 试验课时 本次试验

3、共计 10 课时。 4. 试验数据 本次试验中用到 6 个关系表（当前用户）：course（课程）、dept（系）、s（同学）、prof（老师）、pc（授课）、sc（选修）。 4.1. 关系模式。 snossnamesexagedno cno coursecname pcnocredit pno prof pname sal age dno dno dept dname dean snocnosc scorepnocnopc 4.2. 6 个关系表的定义。 course （课程） dept （系） s （学 生） 字段 名称 类型 说明 sno 同学号 varchar(10) pk，not n

4、ull sname 同学名 varchar(10) not null sex 性别 varchar(10) 0，女；1，男 字段 名称 类型 说明 cno 课程号 varchar(10) pk，not null cname 课程名 varchar(10) not null pcno 先修课程号 varchar(10) credit 学分 real 字段 名称 类型 说明 dno 系号 varchar(10) pk，not null dname 系名 varchar(10) not null dean 系主任号 varchar(10) 系主任作为老师的编号 age 年龄 real dno 所在系

5、号 varchar(10) fk prof （老师） 字段 名称 类型 说明 pno 老师号 varchar(10) pk，not null pname 老师名 varchar(10) not null sal 工资额 float age 年龄 real dno 所在系号 varchar(10) fk sc （选修） 字段 名称 类型 说明 sno 同学号 varchar(10) pk， not null fk cno 课程号 varchar(10) fk score 成果 real pc （授课） 字段 名称 类型 说明 pno 同学号 varchar(10) pk， not null fk

6、 cno 课程号 varchar(10) fk 4.3. 6 个关系表的元组数据。 course （课程） cno cname pcno credit c1 数据库 c5 4 c2 离散数学 c7 2 c3 组合数学 c2 2 c4 操作系统 c9 4 c5 数据结构 c2 3 c6 近世代数 c7 2 c7 高等数学 4 c8 c 语言 c2 2 c9 数理规律 4 dept （系） dno dname dean d1 计算机 p01 d2 数学 p04 d3 物理 p07 s （同学） sno sname sex age dno s1 宋江 1 59 d1 s2 吴用 1 48 d1 s3

7、 鲁智深 1 42 d1 s4 孙二娘 0 40 d2 s5 林冲 1 51 d2 s6 李逵 1 46 d2 s7 武松 1 36 d3 s8 扈三娘 0 30 d3 prof （老师） pno pname sal age dno p1 刘备 1800 59 d1 p2 诸葛亮 1600 48 d1 p3 关羽 1400 d1 p4 曹操 2200 40 d2 p5 司马懿 2021 61 d2 p6 张辽 1700 46 d2 p7 孙权 2100 66 d3 p8 周瑜 1900 30 d3 p9 鲁肃 1500 50 d3 sc （选修） sno cno score s1 c1 80

8、s1 c2 60 s1 c3 40 s1 c4 90 s1 c5 70 s1 c6 60 s1 c7 50 s1 c8 70 s1 c9 80 s2 c1 80 s2 c2 60 s2 c3 40 s2 c4 90 s3 c5 70 s3 c6 60 s4 c1 70 s4 c2 60 s4 c3 55 s4 c4 85 s4 c5 70 s4 c6 70 s4 c7 80 s4 c8 40 s4 c9 70 s5 c3 40 s5 c4 90 s5 c5 70 s5 c6 60 s5 c7 50 s6 c1 80 s6 c2 60 s6 c3 40 s6 c4 90 s7 c1 30 s7

9、c2 80 s7 c3 40 s7 c4 70 s7 c5 80 s7 c6 60 s7 c7 90 s7 c8 50 s7 c9 80 s8 c2 60 s8 c3 55 s8 c4 85 pc （授课） pno cno p1 c1 p2 c2 p3 c3 p4 c4 p5 c5 p6 c6 p7 c7 p8 c8 p9 c9 5. 试验步骤 5.1. sql server 2021 数据库的安装 、 启动 和创建新的数据库。 。 sql server 2021 已安装在本机，一般采纳的是 windows 认证方式。因此当你以默认 windows 用户 administrator 登陆进wi

10、ndow 操作系统之后，你就可以直接通过 服务管理器启动 sql server 2021。 启动胜利之后的状态如下（启动胜利之后，可以关闭 服务管理器）： 之后可以通过打开查询分析器登陆到 sql server 2021，建立和 dbms 的会话。 windows 的系统管理员默认对应 sql server 的系统管理员用户sa。因此， 当你以 windows 的系统管理员身份登陆到 sql server 之后，你在 sql server 中对应的用户名就是 sa。你可以对 sql server 执行利用此 windows默认用户身份登录 sql server 任何操作了（当然你也可以创建一个

11、新的用户，该用户最好在 windows 和sql server 中以相同的名字同时创建）。 利用 企 业管理器创建新的数据库 test。test 数据库作为本次试验用数据库，在 test 数据库中创建本次试验的全部数据库对象。 5.2. create 语句 利用 create 语句创建以上 6 个关系表。同时利用 insert 语句将试验数据插入到相应的表中。 1) 创建 course（课程）的 create 语句。 表的创建 sql 语句、消失的主要错误及缘由： create table course( cno varchar(10) primary key not null, cname

12、varchar(10) not null, pcno varchar(10), creatit real) 向表插入数据的 sql 语句、消失的主要错误及缘由： insert into course(cno,cname,pcno,creatit) values("c1"," 数据库","c5",4) insert into course(cno,cname,pcno,creatit) values("c2"," 离散数学","c7",2) insert into course

13、(cno,cname,pcno,creatit) values("c3"," 组合数学","c2",2) insert into course(cno,cname,pcno,creatit) values("c4"," 操作系统","c9",4) insert into course(cno,cname,pcno,creatit) values("c5"," 数据结构","c2",3) insert into cou

14、rse(cno,cname,pcno,creatit) values("c6"," 近世代数","c7",2) insert into course(cno,cname,pcno,creatit) values("c7"," 高等数学",null,4) insert into course(cno,cname,pcno,creatit) values("c8","c 语言","c2",2) insert into course(cno,

15、cname,pcno,creatit) values("c9"," 数理规律",null,4) insert into course(cno,cname,pcno,creatit) values("c1"," 数据库","c5",4) 型 中，字符型 varchar 类型的要用单引号，数字型的 real ，float 类型的不用加单引号，上面的4 应改为 4 insert into course(cno,cname,pcno,creatit) values("c9",&quo

16、t; 数理规律",4) values 值中若为空，应写为 null ，不是用两个单引号 表示 2) 创建 dept（系）的 create 语句。 表的创建 sql 语句、消失的主要错误及缘由： create table dept( dno varchar(10) primary key not null, dname varchar(10) not null, dean varchar(10) 向表插入数据的 sql 语句、消失的主要错误及缘由： insert into dept(dno,dname,dean) values("d1"," 计算机&quo

17、t;,"p01") insert into dept(dno,dname,dean) values("d2"," 数学","p04") insert into dept(dno,dname,dean) values("d3"," 物理","p07") 3) 创建 s（同学）的 create 语句。 表的创建 sql 语句、消失的主要错误及缘由： create table s( sno varchar(10) primary key not null, sn

18、ame varchar(10) not null, sex varchar(10) check(sex=0 or sex=1), age real , dno varchar(10) foreign key references dept(dno) not null) 为 有 外 键 约 束 时 格 式 为 dno varchar(10) foreign key references dept(dno) not null 向表插入数据的 sql 语句、消失的主要错误及缘由： insert into s(sno,sname,sex,age,dno) values("s1",&

19、quot; 宋江","1",59,"d1") insert into s(sno,sname,sex,age,dno) values("s2"," 吴用","1",48,"d1") insert into s(sno,sname,sex,age,dno) values("s3"," 鲁智深","1",42,"d1") insert into s(sno,sname,sex,age,dno

20、) values("s4"," 孙二娘","0",40,"d2") insert into s(sno,sname,sex,age,dno) values("s5"," 林冲","1",51,"d2") insert into s(sno,sname,sex,age,dno) values("s6"," 李逵","1",46,"d2") insert int

21、o s(sno,sname,sex,age,dno) values("s7"," 武松","1",36,"d3") insert into s(sno,sname,sex,age,dno) values("s8"," 扈三娘","0",30,"d3") 4) 创建 prof（老师）的 create 语句。 表的创建 sql 语句、消失的主要错误及缘由： create table prof( pno varchar(10) primary

22、 key not null, pname varchar(10) not null, sal float, age real, dno varchar(10) foreign key references dept(dno) not null) insert into prof(pno,pname,sal,age,dno) values("p1"," 刘备",1800,59,"d1") insert into prof(pno,pname,sal,age,dno) values("p2"," 诸葛亮&qu

23、ot;,1600,48,"d1") insert into prof(pno,pname,sal,age,dno) values("p3"," 关羽",1400,null,"d1") insert into prof(pno,pname,sal,age,dno) values("p4"," 曹操",2200,40,"d2") insert into prof(pno,pname,sal,age,dno) values("p5",&quo

24、t; 司马懿",2021,61,"d2") insert into prof(pno,pname,sal,age,dno) values("p6"," 张辽",1700,46,"d2") insert into prof(pno,pname,sal,age,dno) values("p7"," 孙权",2100,66,"d3") insert into prof(pno,pname,sal,age,dno) values("p8&quo

25、t;," 周瑜",1900,30,"d3") insert into prof(pno,pname,sal,age,dno) values("p9"," 鲁肃",1500,50,"d3") 向表插入数据的 sql 语句、消失的主要错误及缘由： insert into prof(pno,pname,sal,age,dno) values("p1"," 刘备",1800,59,"d1") insert into prof(pno,pname

26、,sal,age,dno) values("p2"," 诸葛亮",1600,48,"d1") insert into prof(pno,pname,sal,age,dno) values("p3"," 关羽",1400,null,"d1") insert into prof(pno,pname,sal,age,dno) values("p4"," 曹操",2200,40,"d2") insert into prof(

27、pno,pname,sal,age,dno) values("p5"," 司马懿",2021,61,"d2") insert into prof(pno,pname,sal,age,dno) values("p6"," 张辽",1700,46,"d2") insert into prof(pno,pname,sal,age,dno) values("p7"," 孙权",2100,66,"d3") insert int

28、o prof(pno,pname,sal,age,dno) values("p8"," 周瑜",1900,30,"d3") insert into prof(pno,pname,sal,age,dno) values("p9"," 鲁肃",1500,50,"d3") 5) 创建 sc（选修）的 create 语句。 create table sc( sno varchar(10) foreign key references s(sno) not null, cno varc

29、har(10) foreign key references course(cno) not null, score real, primary key(sno,cno) 有多个字段的约束必需定义为表的约束形式 当 当 sno,cno 都是主键时格式应为： primary key(sno,cno) 向表插入数据的 sql 语句、消失的主要错误及缘由： insert into sc(sno,cno,score) values ("s1","c1",80) insert into sc(sno,cno,score) values ("s1"

30、;,"c2",60) insert into sc(sno,cno,score) values ("s1","c3",40) insert into sc(sno,cno,score) values ("s1","c4",90) insert into sc(sno,cno,score) values ("s1","c5",70) insert into sc(sno,cno,score) values ("s1","c6&q

31、uot;,60) insert into sc(sno,cno,score) values ("s1","c7",50) insert into sc(sno,cno,score) values ("s1","c8",70) insert into sc(sno,cno,score) values ("s1","c9",80) insert into sc(sno,cno,score) values ("s2","c1",80) ins

32、ert into sc(sno,cno,score) values ("s2","c2",60) insert into sc(sno,cno,score) values ("s2","c3",40) insert into sc(sno,cno,score) values ("s2","c4",90) insert into sc(sno,cno,score) values ("s3","c5",70) insert into sc(

33、sno,cno,score) values ("s3","c6",60) insert into sc(sno,cno,score) values ("s4","c1",70) insert into sc(sno,cno,score) values ("s4","c2",60) insert into sc(sno,cno,score) values ("s4","c3",55) insert into sc(sno,cno,scor

34、e) values ("s4","c4",85) insert into sc(sno,cno,score) values ("s4","c5",70) insert into sc(sno,cno,score) values ("s4","c6",70) insert into sc(sno,cno,score) values ("s4","c7",80) insert into sc(sno,cno,score) values (&

35、quot;s4","c8",40) insert into sc(sno,cno,score) values ("s4","c9",70) insert into sc(sno,cno,score) values ("s5","c3",40) insert into sc(sno,cno,score) values ("s5","c4",90) insert into sc(sno,cno,score) values ("s5"

36、;,"c5",70) insert into sc(sno,cno,score) values ("s5","c6",60) insert into sc(sno,cno,score) values ("s5","c7",50) insert into sc(sno,cno,score) values ("s6","c1",80) insert into sc(sno,cno,score) values ("s6","c2&q

37、uot;,60) insert into sc(sno,cno,score) values ("s6","c3",40) insert into sc(sno,cno,score) values ("s6","c4",90) insert into sc(sno,cno,score) values ("s7","c1",30) insert into sc(sno,cno,score) values ("s7","c2",80) ins

38、ert into sc(sno,cno,score) values ("s7","c3",40) insert into sc(sno,cno,score) values ("s7","c4",70) insert into sc(sno,cno,score) values ("s7","c5",80) insert into sc(sno,cno,score) values ("s7","c6",60) insert into sc(

39、sno,cno,score) values ("s7","c7",90) insert into sc(sno,cno,score) values ("s7","c8",50) insert into sc(sno,cno,score) values ("s7","c9",80) insert into sc(sno,cno,score) values ("s8","c2",60) insert into sc(sno,cno,scor

40、e) values ("s8","c3",55) insert into sc(sno,cno,score) values ("s8","c4",85) 6) 创建 pc（授课）的 create 语句。 表的创建 sql 语句、消失的主要错误及缘由： create table pc( pno varchar(10) foreign key references prof(pno) not null, cno varchar(10) foreign key references course(cno) not nul

41、l) 向表插入数据的 sql 语句、消失的主要错误及缘由： insert into pc(pno,cno) values("p1","c1") insert into pc(pno,cno) values("p2","c2") insert into pc(pno,cno) values("p3","c3") insert into pc(pno,cno) values("p4","c4") insert into pc(pno,cno

42、) values("p5","c5") insert into pc(pno,cno) values("p6","c6") insert into pc(pno,cno) values("p7","c7") insert into pc(pno,cno) values("p8","c8") insert into pc(pno,cno) values("p9","c9")*/ 5.3. selec

43、t 语句 7) 给出全部老师的信息。 该查询的 sql 语句、消失的错误及缘由： select * from prof 8) 出全部老师的姓名及税后工资额（税额 5%）。 该查询的 sql 语句、消失的错误及缘由： select pname,sal*0.95 from prof 9) 找出全部选修课程的同学。 该查询的 sql 语句、消失的错误及缘由： select distinct sno from sc all在实际代码中，不加，否则无效 10) 找出工资低于 1500 的职工的姓名、工资、系名。 该查询的 sql 语句、消失的错误及缘由： select pname,sal,dname f

44、rom prof,dept where sal1500 and prof.dno=dept.dno 11) 列出教授"数学'课程的老师的教工号及姓名。 该查询的 sql 语句、消失的错误及缘由： select prof.pno,pname from prof,pc,course where prof.pno=pc.pno and pc.cno=course.cno and course.cname="数学" 数学，在查询中应当为"哲学"，在做只是链接时，需要对 pno 前加限制，否则会消失对象不明确 12) 列出工资在 500800 之

45、间的老师姓名。 该查询的 sql 语句、消失的错误及缘由： select pname from prof where sal between 500 and 800 13) 给出全部老师的姓名，所纳税额及税后工资额（税额 5%）。 该查询的 select 语句： 该查询的 sql 语句、消失的错误及缘由： select pname,sal*0.05 as tax,sal*0.95 as income from prof %号不识别，应当写成小数形式 14) 求每一课程的间接先行课(即先行课的先行课)。 该查询的 sql 语句、消失的错误及缘由： select c1.cno,c2.cno fro

46、m course as c1,course as c2 where c1.pcno=c2.cno 15) 找出工资比所在系主任工资高的老师姓名及工资。 该查询的 sql 语句、消失的错误及缘由： select p1.pname,p1.sal from prof as p1,prof as p2,dept where p1.dno=dept.dno and dept.dean=p2.pno and p1.salp2.sal 16) 求选修了 c1 或 c2 课程的同学姓名。 该查询的 sql 语句、消失的错误及缘由： select sname from s,sc where sc.sno=s.s

47、no and (sc.cno="c1" or sc.cno="c2") select sname from s,sc where s.sno=sc.sno and cno in ("c1","c2") 17) 列出姓名以"'打头的老师的全部信息。 该查询的 sql 语句、消失的错误及缘由： select * from prof where pname like "%" 18) 列出名称中含有 4 个字符以上，且倒数第 3 个字符是"d'，倒数第 2 个字符是&

48、quot;_'的老师的全部信息。 该查询的 sql 语句、消失的错误及缘由： select * from prof where pname like "%d_"escape "" 19) 按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列。 该查询的 sql 语句、消失的错误及缘由： select pname,dean from prof,dept where prof.dno=dept.dno order by dean asc,pname desc 20) 求选修了 c1 或 c2 号而没有选 c3 号课程的同学号。 该查询的 sq

49、l 语句、消失的错误及缘由： select distinct sno from sc where cno in ("c1","c2") and cno not in ("c3") 21) 列出各系的老师的最高、最低、平均工资。 该查询的 sql 语句、消失的错误及缘由： select dno,max(sal),min(sal),avg(sal) from prof group by dno 22) 列出计算机系总分超过 300 分的同学的平均成果及学号。该查询的 sql 语句、消失的错误及缘由： select avg(score),s

50、.sno from s,sc,dept where s.dno=dept.dno and dname="计算机" and s.sno=sc.sno group by s.sno having sum(score)=300 23) 列出选修了两门课程以上的同学的平均成果及学号。 该查询的 sql 语句、消失的错误及缘由： select avg(score),sno from sc group by sno having count(*)=2 24) 求选修了课程的同学人数。 该查询的 sql 语句、消失的错误及缘由： select count(distinct sno) fr

51、om sc 25) 求各系 60 岁以上老师的平均工资。 该查询的 sql 语句、消失的错误及缘由： select avg(sal) from prof where age60 group by dno 26) 找出年龄值为空的老师姓名。 该查询的 sql 语句、消失的错误及缘由： select pname from prof where age is null 27) 列出选修了 c1 号和 c2 号课程的同学的学号。 该查询的 sql 语句、消失的错误及缘由： select sno from sc where cno="c1" and sno in (select sn

52、o from sc where cno="c2" ) 28) 找出平均成果最高的同学号。 该查询的 sql 语句、消失的错误及缘由： select sno from sc group by sno having avg(score)=all (select avg(score) from sc group by sno) 子查询返回的值多于一个。当子查询跟随在 =、!=、=、= 之后，或子查询用作表达式时，这种状况是不允许的。要加上 all 29) 求 s1 号同学所选课程的课程记录。 该查询的 sql 语句、消失的错误及缘由： select * from course w

53、here exists (select * from sc where sc.cno=course.cno and sc.sno="s1") 30) 列出选修了全部课程的同学名。 该查询的 sql 语句、消失的错误及缘由： select sname from s where not exists (select * from course where not exists (select * from sc where sc.cno=course.cno and sc.sno=s.sno) ) 31) 列出至少选修了 s1 号同学选修的全部课程的同学名。 该查询的 sql

54、语句、消失的错误及缘由： select sname from s where not exists ( select * from sc sc1 where sc1.cno="s1" and not exists (select * from sc sc2 where sc2.cno=sc1.cno and sc2.sno=s.sno) ) 32) 找出全部只教授一门课程的老师姓名。 该查询的 sql 语句、消失的错误及缘由： select pname from prof where pno in (select pno from pc group by(pno) havi

55、ng count(pno)=1) unique 在查询分析器中不识别，用 in 以及 group by 来实现相同功能 33) 找出至少选修了两门课程的同学姓名。 该查询的 sql 语句、消失的错误及缘由： select sname from s where sno in (select sno from sc group by(sno) having count(sno)=2) 34) 找出平均成果及格的同学及其平均分数。 该查询的 sql 语句、消失的错误及缘由： select sname,avg_score from s,(select sno,avg(score) from sc gr

56、oup by sno having avg(score)=60) as sc_avg(sno,avg_score) where s.sno=sc_avg.sno 利用派生关系解决属性之间的问题，重新生成一个临时表并加以引用 5.4. insert 语句 35) 将平均成果大于 60 的同学加入到 excellent 中（先自行定义创建 excellent 表）。 该插入的 sql 语句、消失的错误及缘由： create table excellent( sno varchar(10), score real) insert into excellent(sno,score) select sn

57、o,avg(score) from sc group by sno having avg(score)90 5.5. update 语句 36) 将老师工资上调 5%。 该更新的 sql 语句、消失的错误及缘由： update prof set sal=sal*1.05 37) 将 d1 系系主任的工资改为该系的平均工资。 该更新的 sql 语句、消失的错误及缘由： update prof set sal=(select avg(sal) from prof where dno="d1" ) where pno=(select dean from dept where dn

58、o="d1" ) 38) 工资超过 2021 的缴纳 10%所得税，其余的缴纳 5%所得税。求老师的税后工资。 该更新的 sql 语句、消失的错误及缘由： update prof set sal=sal*0.9 where sal2021 update prof set sal=sal*0.95 where sal=2021 39) 将计算机系同学的选课成果上调 20%。 该更新的 sql 语句、消失的错误及缘由： update sc set score=score*1.2 where exists (select * from s,dept where s.dno=dep

59、t.dno and dept.dname="计算机" and s.sno=sc.sno) 5.6. delete 语句 40) 删除张辽老师全部的任课记录。 该删除的 sql 语句、消失的错误及缘由： delete from pc where pno in (select pno from prof where pname="张辽") delete 语句中不能使用连接，应当考虑使用子查询 并且 from 后面的表明只能为一个 41) 清除 excellent 表中全部记录。 该删除的 sql 语句、消失的错误及缘由： delete from excellent 42) 删除低于平均工资的老师记录。 该删除的 sql 语句、消失的错误及缘由： delete from prof where sal(sel