四川师范大学 数据库实验报告.docx

1、四川师范大学 数据库实验报告实验一熟悉SQL SERVER的环境（验证型实验 2学时）1.目的要求：1） 了解SQL Server management studio的使用2.实验内容：回答下面每一个问题,写出实验步骤1） 在“已注册服务器窗口”中注册sql server数据库服务器展开【 数据库引擎】节点，右击Local Server Group节点，选择【新建服务器注册】命令，设置相应属性，单击【保存】即可。2） 在“对象资源管理器”中创建名字为sc的数据库展开节点，右击【数据库】节点，选择【新建数据库】命令，在【数据库名称】后填写sc,设置相应属性，单击【确定】即可。3） 在sc数据库中

2、创建一个名字为student的基本表 展开【数据库】节点，选择数据库并展开，右击表节点，选择【新建表】命令，设置行列信息，保存即可。4） 在查询窗口中里创建名为S_C的数据库CREATE DATABASE S_C5） 在查询窗口中使用sql语言创建名字为course的基本表CREATE TABLE course3.主要设备及软件1） PC2） Microsoft SQL Server 2005实验二建立表格，并插入若干记录（验证型实验 2学时）1.目的要求：1） 学会使用Create Table 和 Insert 语句2.实验内容1） 使用sql语言建立student,course和sc共三张

3、表格（包括主键,外码的指定）,分析具体情况适当给出一些用户自定义的约束.CREATE TABLE Student(Sno char(9) PRIMARY KEY, Sname char(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept char(20);CREATE TABLE Course(Cno char(4) PRIMARY KEY, Cname char(14), Cpno char(4), Ccredit smallint, FOREIGN KEY(Cpno) REFERENCES Course(Cno),);CREATE TABLE

4、SC(Sno char(9), Cno char(4), Grade smallint, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno),);2） 使用Insert语句向这三张表格里添加至少10条记录(数据如教材56页所示),如果出现错误,分析错误原因INSERTINTOStudentVALUES(95001,李勇,男,20,CS);INSERTINTOStudentVALUES(95002,刘晨,女,20,IS);INSERTINTO

5、StudentVALUES(95003,王敏,女,20,MA);INSERTINTOStudentVALUES(95004,张立,男,20,IS);SELECT*FROMStudent;INSERTINTOCourse(Cno,Cname,Ccredit)VALUES(1,数据库,4);INSERTINTOCourse(Cno,Cname,Ccredit)VALUES(2,数学,2);INSERTINTOCourse(Cno,Cname,Ccredit)VALUES(3,信息系统,4);INSERTINTOCourse(Cno,Cname,Ccredit)VALUES(4,操作系统,3);IN

6、SERTINTOCourse(Cno,Cname,Ccredit)VALUES(5,数据结构,4);INSERTINTOCourse(Cno,Cname,Ccredit)VALUES(6,数据处理,2);INSERTINTOCourse(Cno,Cname,Ccredit)VALUES(7,PASCAL语言,4);UPDATECourseSETCpno=5WHERECno=1;UPDATECourseSETCpno=1WHERECno=3;UPDATECourseSETCpno=6WHERECno=4;UPDATECourseSETCpno=7WHERECno=5;UPDATECourseSE

7、TCpno=6WHERECno=7;SELECT*FROMCourse;INSERTINTOSCVALUES(95001,1,92);INSERTINTOSCVALUES(95001,2,85);INSERTINTOSCVALUES(95001,3,88);INSERTINTOSCVALUES(95002,2,90);INSERTINTOSCVALUES(95002,3,80);SELECT*FROMSC;3） 在“对象资源管理器”中实现(1)题中的三张表展开【数据库】节点，单击【表】节点，选择【新建表】，填入如下数据保存的时候保存为Student就行了展开【数据库】节点，单击【表】节点，选择

8、【新建表】，填入如下数据保存的时候保存为Course设置外键如图所示展开【数据库】节点，单击【表】节点，选择【新建表】，填入如下数据保存的时候保存为Course设置外键如图所示4） 在“对象资源管理器”中向这(1)题中的三张表添加至少10条记录(数据如教材56页所示),如果出现错误,分析错误原因第一张表中插入数据第二张表中插入数据第三张表插入数据3.主要设备及软件1） PC2） Microsoft SQL Server 2005实验三修改表格结构，修改和删除表格中的数据（验证型实验 4学时）1.目的要求：1) 用ALTER语句修改表结构：添加列，修改列定义，删除列。使用UPDATE和DELET

9、E语句修改和删除Student,sc, course表格中的数据。2.实验内容如下所示，创建表s，并完成随后的操作Create table s(sno char(2) primary key,sname char(10);1) 向表中添加属性列status,数据类型为intALTERTABLEsADDstatusint;2) 向表中添加属性列city,数据类型为varchar(20),并限定其取值范围为“上海”，“北京”，“天津”之一ALTERtablesADDcityvarchar(20);AltertablesaddconstraintCK_citycheck(city=上海orcity=

10、北京orcity=天津);3) 修改属性列status的数据类型为smallintALTER table s ALTER COLUMN status smallint;4) 删除（3）题中取值范围的约束ALTERtablesDROPCK_city;5) 删除属性列statusALTERtablesDROPCK_city;如教材85页所示，建立student, sc, course等表,并插入若干数据，完成如下操作：列出没有成绩的学生的学号和课程号SELECTSno,CnoFROMscWHEREGradeISNULL;列出2号课程成绩在70分到80分学生的学号SELECTSnoFROMscWHE

11、REGrade= 2 ANDGradebetween 70 AND 80;查询所有2005级的学生的姓名，性别和所在系SELECTSname,Ssex,SdeptFROMstudentWHERESnoLIKE2005%;查询计科系2004级全体学生的所有信息SELECT*FROMstudentWHERESdept=jsANDSnoLIKE2004%;查询计科系2006级3班和4班学生的姓名和性别SELECTSname,SsexFROMstudentWHERESdept=jsAND(SnoLIKE2006_3%ORSnoLIKE2006_4%);查询数学系所有学生的姓名，性别和出生年份|SELE

12、CTSname,Ssex,2012-SageFROMstudentWHERESdept=math;将course表中名为PASCAL语言的课程更名为“C语言”UPDATECourseSETCname=C语言WHERECname=PASCAL；将所有课程的学分增加1分UPDATECourseSETCcredit=Ccredit+1；删除没有选课成绩的选课记录DELETEFROMscWHEREGradeISNULL;删除“IS”系的所有学生信息DELETEFROMstudentWHERESdept=IS;删除所有的课程信息DELETEFROMCourse;3.主要设备及软件1） PC2） Micr

13、osoft SQL Server 2005实验四查询（多表查询，嵌套查询，分组查询）（验证型实验 12学时）1.目的要求：1) 实现单表和多表的普通查询和嵌套查询。包括返回单值的子查询和返回多值的子查询。使用5个聚合函数以及GROUP BY子句和HAVING子句实现分组查询.2.实验内容有如下关系模式，分析每个关系模式的主码，外码，完成后面的查询职员表：Emp(eid:integer;ename:string,salary:real)部门表：Dept(did:integer,dname:string,managerid:integer,floornum:integer)职员与部分的关系表：Wo

14、rks(eid:integer,did:integer)；Works表表示：一个职员可以在多个部门工作，一个部门有多个职员Dept表中managerid可以取值null，表示尚未任命部门经理，floornum可以取值null，表示尚未分配工作地点用单表查询完成如下操作：1) 输出所有员工的姓名和工资SELECTename,salaryFROMEmp;2) 输出薪水少于10 000或者大于100 000的雇员的名字SELECTenameFROMEmpWHEREsalary 100000;3) 输出所有姓“欧阳”，且全名为四个字的雇员的姓名和工资SELECTename,salaryFROMEmpW

15、HEREenameLIKE欧阳_;4) 输出薪水在20 000和50 000之间的雇员的名字SELECTenameFROMEmpWHEREsalaryBETWEEN 20000 AND 50000;5) 输出部门名字中含有“_”的所有部门的名字和楼层号SELECTdname,floornumFROMDeptWHEREdnameLIKE%_%ESCAPE;6) 查询公司的员工数SELECTCOUNT(*)FROMEmp;7) 查询所有还没有部门经理的部门的名字和编号SELECTdname,didFROMDeptWHEREmanageridisNULL;8) 查询所有已分配楼层的部门的所有信息SE

16、LECT*FROMDeptWHEREfloornumisNOTNULL;用连接查询完成如下操作：1) 查询“电视”部门的职工人数SELECTCOUNT(did)FROMWorksWHEREWorks.did=(SELECTdidFROMDeptWHEREdname=电视);2) 输出每个部门的名字和平均工资selectdname,avg(salary)fromemp,dept,workswhereemp.eid=works.eidanddept.did=works.didgroupbydname;3) 查询每个部门的部门编号，及其拥有的雇员的人数selectDept.did,Count(eid

17、)fromDept,WorksWHEREDept.did=Works.didGROUPBYDept.did;4) 查询在第10层工作，同时薪水少于￥50000的所有雇员的名字SELECTDISTINCTenameFROMEmp,Dept,WorksWHEREEmp.eid=Works.eidANDWorks.did=Dept.didANDDept.floornum= 10 ANDsalary 50000;5) 输出同时管理三个或者更多部门的管理者的名字select ename from dept,emp where dept.managerid=emp.eid group by manager

18、id,ename having count(did)=3;6) 输出管理在同一层上10个以上部门的所有管理者的名字SELECT ename FROM Dept,Emp WHERE Emp.eid = Dept.managerid groupby managerid, floornum,ename havingCOUNT(did) 10;7) 输出雇员“刘丽”工作的部门的名字SELECT dname FROM Emp,Dept,Works WHERE Emp.ename =刘丽AND Emp.eid = Works.eid AND Works.did = Dept.did;用嵌套查询完成如下操作

19、：1) 查询工资最高的雇员的名字SELECT ename FROM Emp WHERE salary=(SELECT MAX(salary) FROM Emp);2) 查询工资最低的雇员的名字及其所在部门的编号和名字SELECT ename,Dept.did,dname FROM Emp,Dept,Works WHERE salary =(SELECTMIN(salary)FROM Emp)AND Emp.eid = Works.eid AND Works.did = Dept.did;3) 输出与Santa工作部门相同的所有雇员的所有信息SELECT Emp.eid,ename,salary

20、 FROM Emp,Dept,Works WHERE Emp.eid = Works.eid AND Works.did = Dept.did AND Dept.did in(SELECT did FROM Works WHERE eid =(SELECT eid FROM Emp WHERE ename=Santa);4) 找出薪水在20 000以上，并且在电视部门或者玩具部门工作的雇员的名字SELECTDISTINCT ename FROM Emp,Dept,Works WHERE Emp.eid = Works.eid AND Works.did = Dept.did AND salar

21、y 20000 AND dname in(SELECT dname FROM Dept WHERE dname=电视or dname=玩具);5) 输出与刘丽在同一层工作的雇员的名字SELECTDISTINCT ename FROM Emp,Dept,Works WHERE Emp.eid = Works.eid AND Works.did = Dept.did AND floornum in(SELECTDISTINCT floornum FROM Dept,Emp,Works WHERE Dept.did = Works.did AND Works.eid = Emp.eid AND Em

22、p.ename =刘丽);6) 输出比所在部门的经理挣的还要多的雇员的名字SELECTDISTINCT ename FROM Emp,Dept,Works WHERE salary All(SELECT salary FROM Emp,Dept WHERE Emp.eid = Dept.managerid AND Emp.eid = Works.eid AND Works.did = Dept.did AND ename in(SELECT ename FROM Emp);7) 输出满足如下条件的各个部门的名字：经理的姓为张，同时他的薪水既不是本部门最高也不是最低SELECTDISTINCT

23、dname FROM Works,Dept,emp where Dept.managerid=emp.eid and ename like张%AND salary(SELECTmin(salary)from empwhere eid in(select eid from works where works.did=dept.did);8) 输出比“玩具”部门所有职工工资都高的雇员的姓名SELECT ename FROM Emp WHERE salary (SELECTmax(salary)FROM Emp,Works,Dept WHERE Emp.eid = Works.eidAND Work

24、s.did = Dept.did AND Dept.dname=玩具);9) 输出比“电视”部门职工平均工资高的雇员的姓名SELECT ename FROM Emp WHERE salary (SELEcTAVG(salary)FROM Emp,Works,Dept WHERE Emp.eid = Works.eid AND Works.did=Dept.did AND Dept.dname =电视GROUPBY Dept.did);10) 找出所有有职工的部门的名字和楼层号SELECT dname,floornum FROM Dept WHERE didin(SELECT DISTINCT

25、did FROM Works);11) 查询所有没有职工的部门编号和名字SELECT did,dname FROM Dept WHERE didnotin(SELECT DISTINCT did FROM Works);12) 输出同时在玩具部门和糖果部门工作的雇员的名字和薪水SELECTDISTINCT ename,salary FROM Emp ,Works,Dept WHERE Emp.eid=Works.eid AND Works.eid in(SELECT eid FROM Works,Dept WHERE Works.did = Dept.did AND Dept.dname=玩具

26、)AND Works.eid in(SELECT eid FROM Works,Dept WHERE Works.did = Dept.did AND Dept.dname=糖果);3.主要设备及软件1） PC2） Microsoft SQL Server 2005实验五为表格建立约束，修改约束和查询约束（验证型实验 4学时）1.目的要求：1） 使用ALTER语句和CREATE语句建立、修改、删除和查询约束2.实验内容：执行以下SQL语句，完成随后的操作，若有错误，分析错误原因并改正错误：create table student(sno char(9) not null , sname cha

27、r(10),ssex char(2),sage tinyint,sdept varchar(40);create table course(cno char(4) not null, cname varchar(30),cpno char(4),credit tinyint)create table sc(sno char(9),char(5),grade numeric(3,1);出错第三句改为create table sc(sno char(9), cno char(5), grade numeric(3,1)1) 在student表中，使sdept只能取值“计算机科学学院”，“数软学院”

28、，“电子工程学院”，“化学与材料科学学院”AltertablestudentaddconstraintCK_sdeptcheck(sdept=计算机科学学院orsdept=数软学院orsdept=电子工程学院orsdept=化学与材料科学学院);2) 在student表中sage有默认值18ALTERTABLEstudentADDDEFAULT(18)FORsage;3) 为student表建立主键ALTER TABLE student ADD PRIMARY KEY(Sno)；4) 为course表建立主键和外键，其中外键约束名为C_FK_CPNOALTERTABLEcourseADDpri

29、marykey(cno),C_FK_CPNOCHAR(4)FOREIGNKEY(cpno)REFERENCEScourse(cno);5) 为course表建立检查约束，限定credit的取值只能取3，2，4，5；ALTERTABLEcourseADDCHECK(creditin(3,2,4,5);6) 为course表建立唯一约束，确保每们课程名字唯一ALTERTABLEcourseADDUNIQUE(cname);7) 为sc表建立主键和外键，并给出相应的约束名ALTER TABLE sc ADD constraint PK_sno_cno PRIMARY KEY(sno,cno),FK_

30、sno CHAR(9) FOREIGNKEY(sno) REFERENCES student(sno),FK_cno CHAR(4) FOREIGN KEY(cno) REFERENCES course(cno);8) 在course表中插入元组(1,数据库，5，4)和(2,数学，null,2),若不能正确插入，分析原因，给出解决办法不能正确插入，因为数据类型不对，更改方案INSERTINTOcourse(cno,cname,cpno,credit)VALUES(1,数据库,5,4);INSERTINTOCourse(cno,cname,cpno,credit)VALUES(2,数学,null,2);若前面的题中建立了约束关系，那删掉9) 在sc表中插入元组（95001,1,92）和