SQL数据定义与数据查询.doc

1、数据库上机报告数据定义与数据查询一、 写明实验所采用的实验环境：硬件环境：普通联网的PC机；操作系统：Windows 2000 或者 Windows XP；数据库管理系统：MS SQL Server 2005。二、 给出实验内容与完成情况：（一）使用命令行方式建立学生课程数据库(1) 用 SQL 语言建立学生课程数据库，数据库命名：SC_学号后四位及姓名，如SC_3112liyan；CREATE DATABASE SC_1224cxj ON (NAME = SC_1224cxj_dat,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSS

2、QL SC_1224cxj_dat.mdf)LOG ON (NAME= SC_1224cxj_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL SC_1224_log.ldf);(2) 用 SQL 语言在学生课程数据库中建立 3 个表，要求进行完整性定义：学生关系 Student(Sno，Sname，Ssex，Sage，Sdept) 其中 Sno 不能为空，值是唯一的，并且 Sname 取值也唯一，Sage 自定义约束“大于 16”课程关系 Course(Cno，Cname ，Cpno ，Ccredit) 其中 Cno 不

3、能为空，值是唯一的，并且 Cname 取值也唯一教师关系 Teacher(Tno，Tname ，Department，Email，Salary) 其中 Tno 不能为空，值是唯一的选修关系 SC(Sno，Cno， Grade) 其中 Sno 和 Cno 不能为空，(Sno, Cno)为主码，Grade 的数据类型为 int建立 Student 表：CREATE TABLE Student(Sno CHAR(9) PRIMARY KEY,Sname CHAR(40) UNIQUE,Ssex CHAR(2),Sage SMALLINT CHECK(Sage16),Sdept CHAR(20);建立

4、Course表：CREATE TABLE Course(Cno CHAR(4) PRIMARY KEY,Cname CHAR(40) UNIQUE,Cpno CHAR(4),Credit SMALLINT,CONSTRAINT FK_C FOREIGN KEY (Cpno) REFERENCES Course(Cno);建立Teacher表：CREATE TABLE Teacher(Tno CHAR(4) PRIMARY KEY,Tname CHAR(40),Department CHAR(20),Email CHAR(100),Salary INT );建立 SC 表：CREATE TABL

5、E SC(Sno CHAR(9),Cno CHAR(4),Grade SMALLINT,PRIMARY KEY(Sno,Cno),CONSTRAINT FK_SC1 FOREIGN KEY(Sno) REFERENCES Student(Sno),CONSTRAINT FK_SC2 FOREIGN KEY(Cno) REFERENCES Course(Cno);(3) 修改数据表： 将 SC 表中 Grade 的数据类型改为 float；ALTER TABLE SC ALTER COLUMN Grade FLOAT; 在 Student 表中增加一个属性 Scome 记录学生的入学时间，其数据

6、类型为DATETIME；ALTER TABLE Student ADD Scome DATETIME; 删除 Student 表中新增加 Scome 属性；ALTER TABLE Student DROP COLUMN Scome; 删除表 Teacher 表中的 Email 属性；ALTER TABLE Teacher DROP COLUMN Email; 删除 Student 表中对 Sname 属性创建的 Unique 约束。 （无法删除未建立名字的约束）建立约束语句：ALTER TABLE Student ADD CONSTRAINT S_CHECK UNIQUE(Sname);删除约

7、束语句：ALTER TABLE Student DROP S_CHECK;*(4) 用 SQL 语言分别在 3 个数据表中插入数据（具体数据可见教材 P.82）：INSERTINTO StudentVALUES(200215121,李勇 ,男, 20,CS);INSERTINTO StudentVALUES(200215122,刘晨 ,女, 19,CS);INSERTINTO StudentVALUES(200215123,王敏 ,女, 18,MA);INSERTINTO StudentVALUES(200215125,张立 ,男, 19,IS);INSERTINTO CourseVALUES

8、(1,数据库,5,4 );INSERTINTO CourseVALUES(2,数学,NULL,2);INSERTINTO CourseVALUES(3,信息系统,1,4 );INSERTINTO CourseVALUES(4,操作系统,6,3 );INSERTINTO CourseVALUES(5,数据结构,7,4 );INSERTINTO CourseVALUES(6,数据处理,NULL,2);INSERTINTO CourseVALUES(7,PALCAL语言 ,6,4);INSERTINTO SCVALUES(200215121,1,92);INSERTINTO SCVALUES(200

9、215121,2,85);INSERTINTO SCVALUES(200215121,3,88);INSERTINTO SCVALUES(200215122,2,90);INSERTINTO SCVALUES(200215122,3,80);(5) 创建索引： 在 Student 表上创建按 sage 降序排列的索引 NON_Stusage；CREATE INDEX NON_Stusage ON Student(Sage DESC); 在 Student 表上为 Sno 列创建一个聚簇索引 CLU_Stusno；CREATE CLUSTERED INDEX CLU_Stusno ON Stud

10、ent(Sno); 在 Student 表上为 Sname 列创建一个聚簇索引 CLU_Stusname；（能否创建成功？为什么？）不能创建，因为已经存在一个聚簇索引，一个基本表中只能存在一个聚簇索引。 在 Course 表上创建按 ccredit 升序排列的唯一性索引；CREATE UNIQUE INDEX UNI_Ccre ON Course(Ccedit); 在 SC 表上按 Sno 升序和 Cno 降序建立唯一索引 UN_SC。CREATE UNIQUE INDEX UN_SC ON SC(Sno,Cno DESC);(6)删除索引 CLU_Stusno、UN_SC：DROP INDE

11、X Student.CLU_Stusno;DROP INDEX SC.UN_SC(7) 删除 Teacher 表DROP TABLE Teacher;(8) 保存 SQL 文件，文件名：SC-姓名学号后四位.sql，如 SC-xukai3112.sql（二）使用命令行方式建立 SPJ 数据库(1) 用 SQL 语言建立 SPJ 数据库，数据库命名：SPJBASE-学号后四位及姓名，如 SPJBASE-3112liyan；CREATE DATABASE SPJBASE_1224cxjON (NAME = SPJBASE_1224cxj_dat,FILENAME=C:Program FilesMi

12、crosoft SQL ServerMSSQL.1MSSQL SC_1214fanglingzhi _dat.mdf)LOG ON (NAME= SPJBASE_1224cxj_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL SC_1214zhuyameng _log.ldf)(2) 用 SQL 语言在其中建立 S 表、P 表、J 表和 SPJ 表（要求进行完整性定义），表结构如下；厂家 S（SNO，SNAME ，STATUS，CITY） ，其中 STATUS（状态）为整型，其他均为字符型产品 P（PNO ，PNAME，

13、COLOR，WEIGHT） ，其中 WEIGHT（重量）为浮点型，其他均为字符型工程 J（JNO，JNAME ， CITY） ，其中各属性均为字符型供应 SPJ（SNO ，PNO，JNO，QTY） ，其中 QTY（供应量）为整型，其他均为字符型建立 S 表：CREATE TABLE Ss(SNO CHAR(9) PRIMARY KEY,SNAME CHAR(40),STATUS INT,CITY CHAR(20);建立 P 表：CREATE TABLE P(PNO CHAR(9) PRIMARY KEY,PNAME CHAR(40),COLOR CHAR(20),WEIGHT FLOAT);建

14、立 J 表：CREATE TABLE J(JNO CHAR(9) PRIMARY KEY,JNAME CHAR(40),CITY CHAR(20);建立 SPJ 表：CREATE TABLE SPJ(SNO CHAR(9),PNO CHAR(9),JNO CHAR(9), QTY INT,PRIMARY KEY(SNO,PNO,JNO),CONSTRAINT FK_SPJ1 FOREIGN KEY(SNO) REFERENCES S(SNO),CONSTRAINT FK_SPJ2 FOREIGN KEY(PNO) REFERENCES P(PNO),CONSTRAINT FK_SPJ3 FOR

15、EIGN KEY(JNO) REFERENCES J(JNO);*(3) 用 SQL 语言分别在 4 个数据表中输入一定的记录；省略(4) 保存 SQL 文件，文件名：SPJ-姓名学号后四位.sql，如 SPJ-xukai3112.sql（三）针对学生课程数据库查询(1) 查询全体学生的学号与姓名。SELECT Sno,SnameFROM Student;(2) 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,SdeptFROM Student;(3) 查询全体学生的详细记录。SELECT *FROM Student;(4) 查全体学生的姓名及其出生年份。SELECT Sn

16、ame, SageFROM Student;(5) 查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。SELECT Sname,Sage,LOWER(Sdept)FROM Student;(6) 使用列别名改变查询结果的列标题。SELECT Sname NAME,Year of Birth: BIRTH,2000-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENTFROM Student;(7) 查询选修了课程的学生学号。SELECT DISTINCT SnoFROM SC;(8) 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname,S

17、age FROM Student WHERE Sage=20 AND Sage3;(34) 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。SELECT Sno, COUNT(*)FROM SCWHERE Grade=90GROUP BY SnoHAVING COUNT(*)=3;(35) 查询学生20060711选修课程的总学分。SELECT Sum(Ccredit)FROM SC,CourseWHERE Sno=20060711 ANDSC.Cno=Course.Cno;(36) 查询每个学生选修课程的总学分。SELECT Sum(Ccredit)FROM SC,Cou

18、rseWHERE SC.Cno=Course.CnoGROUP BY Sno;(37) 查询每个学生及其选修课程的情况。SELECT Student.*,SC.*FROM Student,SCWHERE Student.Sno=SC.Sno;(38) 查询每一门课的间接先修课（即先修课的先修课）。SELECT FIRST.Cno,SECOND.CpnoFROM Course FIRST,Course SECONDWHERE FIRST.Cpno=SECOND.Cno;(39) 查询每个学生及其选修课程的情况包括没有选修课程的学生（用外连接）。SELECT Student.*, SC.*FROM

19、 Student join SC on Student.Sno = SC.Sno;(40) 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。SELECT Student.Sno, Student.SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90;(41) 查询每个学生的学号、姓名、选修的课程名及成绩。SELECT Student.Sno,Sname,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno=SC.Sno ANDSC.Cno=Co

20、urse.Cno;(42) 查询与“刘晨” 在同一个系学习的学生（分别用嵌套查询和连接查询）。嵌套查询：SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept=(SELECT SdeptFROM StudentWHERE Sname=刘晨);连接查询：FROM Student S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname=刘晨;(43) 查询选修了课程名为“信息系统” 的学生学号和姓名。SELECT S1.Sno,S1.Sname,S1.SdeptSELECT Sno,SnameFROM StudentW

21、HERE Sno IN(SELECT Sno FROM SC WHERE Cno IN(SELECT CnoFROM CourseWHERE Cname=信息系统);(44) 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄。SELECT Sname,SageFROM StudentWHERE SageIS;(45) 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL谓词和集函数。ALL谓词：SELECT Sname,SageFROM StudentWHERE SageIS;集函数：SELECT Sname,SageFROM StudentWHERE Sag

22、eIS;(46) 查询所有选修了1号课程的学生姓名。（分别用嵌套查询和连查询）嵌套查询：SELECT SnameFROM StudentWHERE Sno IN(SELECT Sno FROM SC WHERE Cno=1);连接查询：SELECT Student.SnameFROM Student,SCWHERE Student.Sno=SC.Sno AND C.Cno=1;(47) 查询没有选修1号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM SCWHERE Sno=Student.Sno ANDCno=1);(

23、48) 查询选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM CourseWHERE NOT EXISTS(SELECT *FROM SCWHERE SC.Sno=Student.Sno ANDCno=Course.Cno);(49) 查询至少选修了学生95002选修的全部课程的学生号码。SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.Sno=95002ANDNOT EXISTS(SELECT *FROM S

24、C SCZWHERE SCZ.Sno=SCX.Sno ANDSCZ.Cno=SCY.Cno)ANDSno95002;(50) 查询计算机科学系的学生及年龄不大于19岁的学生的信息。SELECT *FROM StudentWHERE Sdept=CSUNION SELECT *FROM StudentWHERE Sage(SELECT STATUSFROM SWHERE SNAME=Clark);5查询供应 P1，P2 两种产品的厂家名。SELECT SNAMEFROM S, SPJWHERE S.SNO=SPJ.SNO ANDPNO=P1INTERSECTSELECT SNAMEFROM S,

25、SPJWHERE S.SNO=SPJ.SNO ANDPNO=P2;6查询由 S1 提供产品的工程名。SELECT DISTINCT JNAMEFROM J,SPJWHERE SPJ.JNO=J.JNO AND SNO=S1;7查询使用了颜色为 Red 产品，在城市 Athens 的工程名。SELECT JNAMEFROM JWHERE CITY=Athens ANDJNO IN(SELECT JNOFROM SPJWHERE PNO IN(SELECT PNOFROM PWHERE COLOR=Red);8求使用了全部零件的工程名。SELECT JNAMEFROM JWHERE NOT EXI

26、STS(SELECT *FROM PWHERE NOT EXISTS(SELECT *FROM SPJWHERE SPJ.JNO=J.JNO ANDSPJ.PNO=P.PNO);9查询未采用由 London 供应者提供颜色为 Red 零件的工程名。SELECT JNAMEFROM JWHERE NOT EXISTS(SELECT *FROM S,P,SPJWHERE SPJ.SNO=S.SNO ANDSPJ.PNO=P.PNO ANDSPJ.JNO=J.JNO ANDCITY=London ANDCOLOR=Red);10查询全部由厂家 S2 提供零件的工程名。SELECT JNAMEFROM

27、 JWHERE NOT EXISTS(SELECT *FROM SWHERE SNO=S2 ANDNOT EXISTS(SELECT *FROM SPJWHERE SPJ.SNO=S.SNO ANDSPJ.JNO=J.JNO);11求供给 London 的所有工程的零件名。SELECT PNAMEFROM PWHERE NOT EXISTS(SELECT *FROM JWHERE CITY=London ANDNOT EXISTS(SELECT *FROM SPJWHERE SPJ.JNO=J.JNO ANDSPJ.PNO=P.PNO);12查询由 London 的厂商供给 London 的工

28、程的产品名。SELECT PNAMEFROM S,P,J,SPJWHERE SPJ.SNO=S.SNO ANDSPJ.PNO=P.PNO ANDSPJ.JNO=J.JNO ANDS.CITY=London ANDJ.CITY=London;13查询由供应者 S1 提供零件的工程项目总数。SELECT COUNT(DISTINCT JNO)FROM SPJWHERE SNO=S1;14查询由供应者 S1 提供零件 P1 的总量及总供应量。SELECT COUNT(QTY),SUM(QTY)FROM SPJWHERE SNO=S1 AND PNO=P1;15查询至少使用 Nut 和 Cam 两种产

29、品的工程号和工程名。SELECT DISTINCT JNO,JNAMEFROM JWHERE NOT EXISTS(SELECT *FROM PWHERE (PNAME=Nut ORPNAME=Cam) ANDNOT EXISTS(SELECT *FROM SPJWHERE SPJ.PNO=P.PNO ANDSPJ.JNO=J.JNO);16查询至少使用了 S1 所提供的全部零件的工程名。SELECT JNAMEFROM JWHERE NOT EXISTS(SELECT *FROM SWHERE SNO=S1 ANDNOT EXISTS(SELECT *FROM SPJWHERE SPJ.SN

30、O=S.SNO ANDSPJ.JNO=J.JNO);17查询不使用产品 P2 的工程号和工程名。SELECT JNO,JNAMEFROM JWHERE JNO NOT IN(SELECT JNOFROM SPJ,PWHERE SPJ.PNO=P.PNO ANDP.PNO=P2);18查询不供应红色产品的厂家号和厂家名。SELECT SNO,SNAMEFROM SWHERE SNO NOT IN(SELECT SNOFROM P,SPJWHERE P.COLOR=Red ANDSPJ.PNO=P.PNO ANDSPJ.SNO=S.SNO);19查询只使用厂家 S1 供应产品的工程号和工程名。SE

31、LECT DISTINCT J.JNO,JNAMEFROM S,J,SPJWHERE SPJ.SNO=S.SNO ANDSPJ.JNO=J.JNO ANDS.SNO NOT IN(SELECT SNOFROM SWHERE SNO!=S1);20查询只供应红色产品的厂家号和厂家名。SELECT S.SNO,SNAMEFROM S,P,SPJWHERE SPJ.SNO=S.SNO ANDSPJ.PNO=P.PNO ANDCOLOR NOT IN(SELECT COLORFROM PWHERE COLOR!=Red);三、 列出没有解决的问1删除 Student 表中对 Sname 属性创建的 Unique 约束。怎样可以删除未建立名字的约束？需要在图形界面下， 查看系统默认的约束是如何命名的，然后再删除。2STATUS 为什么会被系统自动变成蓝色，是不是因为 STATUS 为系统自动保留字符？一般系统的保留字符是不能够作为表的名字或字段的名字的，可能是其他的原因。