最新SQL-SERVER-上机实验.doc

Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateSQL-SERVER-上机实验SQL实验与练习题参考答案实验1 数据库操作1创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。参考答案：create database TestON primary(name = test_data,filename = 'd:testtest_data.mdf',size = 5MB,maxsize = unlimited,filegrowth = 1MB)LOG ON(name = test_log,filename = 'd:testtest_log.ldf',size = 1MB,maxsize = 5MB,filegrowth = 10%)GO2查看数据库属性：操作1.2：使用T-SQL语句查看数据库test属性参考答案：EXEC sp_helpdb test3删除数据库：操作1.3：使用T-SQL语句删除数据库test参考答案：drop database Test实验2 表操作1创建表：操作2.1：创建学生表：表名：student说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnVarChar9Not NullPK学生学号st_nmnVarChar8Not Null学生姓名st_sexnVarChar2Null学生性别st_birthdatetimeNull出生日期st_scoreintNull入学成绩st_datedatetimeNull入学日期st_fromnChar20Null学生来源st_dpidnVarChar2Null所在系编号st_mnttinyintNull学生职务参考答案：USE testGOCREATE TABLE student(st_id nVarChar(9) primary key NOT NULL ,st_nm nVarChar(8) NOT NULL ,st_sex nVarChar(2) NULL ,st_birth datetime NULL ,st_score int NULL ,st_date datetime NULL ,st_ from nVarChar(20)NULL ,st_dpid nVarChar(2) NULL ,st_ mnt tinyint NULL)GO操作2.2：创建课程信息表：表名：couse说明：课程信息表属性列数据类型长度空值列约束说明cs_idnVarChar4Not NullPK课程编号cs_nmnVarChar20Not Null课程名称cs_tmintNull课程学时cs_scintNull课程学分参考答案：USE testGOCREATE TABLE couse(cs_id nVarChar(4) primary key NOT NULL ,cs_nm nVarChar(20) NOT NULL ,cs_tm int NULL ,cs_sc int NULL)GO操作2.3：创建选课表：表名：slt_couse说明：选课表属性列数据类型长度空值列约束说明cs_idnVarChar4Not NullFK课程编号st_idnVarChar9Not NullFK学生编号scoreintNull课程成绩sltdatedatetimeNull选课日期参考答案：USE testGOCREATE TABLE couse(cs_id nVarChar(4) NOT NULL ,st_id nVarChar(9) NOT NULL ,score int NULL ,sltdate datetime NULL)GO操作2.4：创建院系信息表：表名：dept说明：院系信息表属性列数据类型长度空值列约束说明dp_idnVarChar2Not Null系编号dp_nmnVarChar20Not Null院系名称dp_drtnVarChar8Null院系主任dt_telnVarChar12Null联系电话参考答案：USE testGOCREATE TABLE dept(dp_id nVarChar(2) NOT NULL ,dp_nm nVarChar(20)NOT NULL ,dp_drtnVarChar(8) NULL ,dp_telnVarChar(12)NULL)GO2修改表结构：（1）向表中添加列：操作2.5：为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）参考答案：ALTER TABLE dept ADD dp_count nvarchar(3) NULL（2）修改列数据类型：操作2.6：修改“dept”表的“dp_count”列数据类型为int参考答案：ALTER TABLE dept ALTER COLUMN dp_count int NULL（3）删除表中指定列：操作2.7：删除“dept”表的“dp_count”列参考答案：ALTER TABLE dept DROP COLUMN dp_count3删除表操作2.8：删除“dept”表参考答案：DROP TABLE student4向表中输入数据记录操作2.9：分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录实验3 数据完整性1空值约束( NULL )操作3.1：将student表中的st_sex列属性更改为NOT NULL参考答案：ALTER TABLE student ALTER COLUME st_nm nVarChar(8) NOT NULL2默认值约束( DEFAULT )操作3.2：将student表中的st_from列默认值设置为“陕西省”参考答案：ALTER TABLE student ADD DEFAULT '陕西省' FOR st_from3默认值对象操作3.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。参考答案：CREATE DEFAULT df_today AS Getdate( )GOEXEC sp_bindefault df_today, 'slt_couse.sltdate'GOEXEC sp_unbindefault 'slt_couse.sltdate'GODROP DEFAULT df_todayGO4检查约束( CHECK )操作3.4：将slt_couse表中的score列的检查约束设置为>=0且<=100参考答案：ALTER TABLE slt_couse ADD CHECK (score>=0 AND score<=100)5规则约束对象操作3.5：创建规则约束对象rl_sex，用于检查性别的取值仅限于“男”和“女”，并将其绑定到student表中的st_sex列，然后取消绑定，最后删除规则约束对象rl_sex。参考答案：CREATE RULE rl_sex AS chksex 男 OR chksex=女或CREATE RULE rl_sex AS chksex IN (男, 女)GOEXEC sp_bindrule rl_sex, 'student.st_sex'GOEXEC sp_unbindrule 'student.st_sex'GODROP RULE rl_sexGO6主键操作3.6：将dept表中的dp_id列设置为主键参考答案：ALTER TABLE dept ADD PRIMARY KEY (dp_id)7唯一性约束( UNIQUE )操作3.7：将dept表中的dp_nm列设置为唯一性约束参考答案：ALTER TABLE dept ADD UNIQUE (dp_nm)8标识列操作3.8：向slt_couse表中添加标识列id，第1行默认值为1，相邻两个标识列间的增量为1参考答案：ALTER TABLE slt_couse ADD id INT IDENTITY(1,1) NOT NULL9外键（ FOREIGN KEY ）操作3.9：被参照表为dept，参照表为student参考答案：ALTER TABLE studentADD FOREIGN KEY (st_dpid) REFERENCES dept(dp_id)实验4 数据更新1表中插入数据操作4.1：向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567INSERT INTO dept VALUES('11', '自动控制系', '李其余', '81234567')操作4.2：向student表插入一条记录，学号070201001，姓名为王小五，性别为男，出生日期为1990年9月9日，系号为11，其余字段为NULL或默认值INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)VALUES ('070201001', '王小五', '男', '1990.9.9', '11' )操作4.3：向couse表插入一条记录，课程号1234，课程名为操作系统，其余字段为NULL或默认值INSERT INTO couse(cs_id, cs_nm) VALUES ('1234', '操作系统')操作4.4：向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值INSERT INTO slt_couse(cs_id, st_id) VALUES ('1234', '070201001')2修改表中数据操作4.5：修改student表记录，将王小五的入学成绩改为88UPDATE student SET st_score=88 WHERE st_nm='王小五'操作4.6：修改couse表记录，将所有记录的学分改为4，学时改为64UPDATE couse SET cs_tm=64, cs_sc=4操作4.7：修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77UPDATE slt_couse SET score=77 WHERE cs_id='1234' AND st_id='070201001'操作4.8:将“软件学院”学生的成绩加5分update slt_couse set score=score+5 where st_id in(select st_id from student where st_dpid in(select st_dpid from dept where dp_nm='软件学院')3删除表中数据操作4.9：删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除DELETE FROM slt_couse WHERE cs_id='1234' AND st_id='070201001'操作4.10：删除couse表记录，将课程号为1234的记录删除DELETE FROM couse WHERE cs_id='1234'操作4.11：将“软件学院”学生的选课记录删除delete slt_cousefrom slt_couse,dept,studentwhere slt_couse.st_id=student.st_id and student.st_dpid=dept.dp_id and dept.dp_nm='软件学院'实验5 数据查询（1）简单查询（1）查询表中所有的列操作5.1：查询所有系的信息SELECT * FROM dept（2）查询表中指定列的信息操作5.2：查询所有的课程号与课程名称SELECT cs_id, cs_nm FROM couse（3）在查询列表中使用列表达式操作5.3：在查询student表时使用列表达式：入学成绩+400SELECT st_id, st_nm, st_score, st_score+400 AS new_scoreFROM student（4）重新命名查询结果操作5.4：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话SELECT dp_id AS 系号, dp_nm AS 系名, dp_drt AS 系主任, dp_tel AS联系电话FROM dept操作5.5：使用"="号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分SELECT 课程号=cs_id, 课程名=cs_nm, 学分=cs_sc, 学时=cs_sc*16FROM couse（5）增加说明列操作5.6：查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任SELECT '系号:', st_id, '系名:', st_nm, '系主任:', st_drtFROM dept（6）查询列表中使用系统函数操作5.7：显示所有学生的学号、姓名、性别和入学年份SELECT st_id, st_nm, st_sex, DATEPART(yy,st_birth) AS 入学年份FROM student操作5.8：显示所有学生学号、姓名、性别和班级(学号前6位)SELECT st_id, st_nm, st_sex, LEFT(st_id, 6) AS 班级FROM student（7）消除查询结果中的重复项操作5.9：显示所有学生班级SELECT DISTINCT LEFT(st_id,6) AS 班级 FROM student（8）取得查询结果的部分行集操作5.10：显示前5条学生记录信息SELECT TOP 5 * FROM student操作5.11：显示前25%条学生记录信息SELECT TOP 25 PERCENT * FROM student操作5.12：显示前n条学生记录信息，n为局部变量DECLARE n INTSET n = 4SELECT TOP n * FROM student实验6 数据查询（2）条件查询1使用关系表达式表示查询条件操作6.1：查询dept表中系号为11的院系信息SELECT * FROM dept WHERE dp_id = '11'操作6.2：查询student表中11系的学生学号、姓名、性别和所在系编号SELECT st_id, st_nm, st_sex, st_dpid FROM studentWHERE st_dpid = '11'操作6.3：查询student表中2008年及以后入学的学生信息SELECT * FROM studentWHERE DATEPART( yy, st_date )>= 2008操作6.4：在查询student表080808班学生的学号、姓名、性别和入学成绩SELECT st_id, st_nm, st_sex, st_score FROM studentWHERE Left(st_id,6)='080808'2使用逻辑表达式表示查询条件操作6.5：查询student表中非11系的学生信息SELECT * FROM student WHERE NOT (st_dpid = '11')操作6.6：查询选修了1002号课程且成绩在60以下的学生学号SELECT st_id FROM slt_couseWHERE (cs_id='1002') AND (score<60)操作6.7：查询2007年入学的11系所有男生信息SELECT * FROM studentWHERE DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男'操作6.8：查询11系和12系的学生信息SELECT * FROM studentWHERE st_dpid='11' OR st_dpid='12'操作6.9：查询11系和12系所有2007年入学的学生信息SELECT * FROM studentWHERE (st_dpid='11' OR st_dpid='12') AND DATEPART(yy,st_date)=20073使用LIKE关键字进行模糊查询操作6.10：查询所有“计算机”开头的课程信息SELECT * FROM couse WHERE cs_nm LIKE '计算机%'操作6.11：查询所有由三个字组成的“王”姓学生信息SELECT * FROM student WHERE st_nm LIKE '王_'操作6.12：查询所有课程名中包含“信息”的课程信息SELECT * FROM couse WHERE cs_nm LIKE '%信息%'操作6.13：查询学生姓名介于王姓到张姓的信息SELECT * FROM studentWHERE st_nm LIKE '王-张%'4使用BetweenAnd关键字进行查询操作6.14：查询在1989.7.1到1990.6.30之间出生的学生信息SELECT st_id, st_nm, st_sex, st_birth FROM studentWHERE st_birth BETWEEN '1981.7.1' AND '1999.6.30'操作6.15：查询选修了1001号课程且成绩在60到80之间的学生选课信息SELECT * FROM slt_couseWHERE cs_id='1001' AND (score BETWEEN 60 AND 80)5使用IN关键字进行查询操作6.16：查询11系、12系、13系的学生信息SELECT * FROM student WHERE st_dpid IN ('11', '12','13')操作6.17：查询所有张，王，李，赵姓的学生的学号、姓名、性别SELECT st_id, st_nm, st_sex FROM studentWHERE Left(st_nm,1) IN ('张','王','李','赵')6使用NOT NULL关键字进行查询操作6.18：查询所有生源为非空的学生信息SELECT * FROM student WHERE st_from IS NOT NULL操作6.19：查询选修了1001号课程且成绩为空的学生选课信息SELECT * FROM slt_couse WHERE cs_id='1001' AND score IS NULL实验7 数据查询（3）查询排序与查询结果存储操作7.1：查询课程信息，按课程名称降序排序SELECT * FROM couse ORDER BY cs_nm DESC操作7.2：查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序SELECT st_id, score FROM slt_corseWHERE cs_id='1001' AND score IS NOT NULLORDER BY score DESC操作7.3：查询11系学生学号、姓名和年龄，按年龄升序排序SELECT st_id,st_nm,DATEPART(yy,GETDATE( )-DATEPART(yy,st_birth) AS ageFROM studentORDER BY age ASC操作7.4：查询学生信息，按姓名升序排序，再按系号降序排序SELECT * FROM student ORDER BY st_nm, st_dpid DESC操作7.5：创建学生表副本student01，仅保留学生学号、姓名和性别SELECT st_id, st_nm, st_sex INTO student01 FROM student操作7.6：查询陕西籍学生，将结果保存在新表st_shanxiSELECT * INTO st_shanxiFROM studentWHERE st_from='陕西省'操作7.7：查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001SELECT * INTO slt1001 FROM slt_corseWHERE cs_id='1001' ORDER BY st_id操作7.8：用局部变量stage保存学生张三的年龄DECLARE stage intSELECT stage = DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)FROM studentWHERE st_nm='张三'操作7.9：用局部变量name和stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩DECLARE name nVarChar(8), stscore intSELECT name = st_nm, stscore = st_scoreFROM studentWHERE LEFT(st_id,6)='070101'ORDER BY st_id实验8 数据查询（4）查询统计与汇总操作8.1：查询课程总数SELECT COUNT( * ) FROM couse操作8.2：查询选修1001号课程的学生人数SELECT COUNT(st_id)FROM slt_couseWhere cs_id = '1001'操作8.3：查询被选修课程的数量SELECT COUNT( DISTINCT cs_id ) FROM slt_couse操作8.4：查询选修070101班学生的平均入学成绩SELECT AVG(st_score)FROM studentWHERE LEFT(st_id,6)='070101'操作8.5：查询070101001号学生选修课程的数量、总分以及平均分SELECT COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分FROM slt_couseWHERE st_id='070101001'操作8.6：查询选修1001号课程的学生人数、最高分、最低分和平均分SELECT COUNT(*) AS 学生人数, MAX(score) AS 最高分, MIN(score) AS 最低分, AVG (score) AS 平均分FROM slt_couseWHERE cs_id='1001'操作8.7：求各个课程号和相应的选课人数SELECT cs_id, COUNT(st_id)FROM slt_couse GROUP BY cs_id操作8.8：统计各班人数SELECT LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数FROM studentGROUP BY LEFT(st_id,6)操作8.9：依次按班级、系号对学生进行分类统计人数、入学平均分SELECT st_dpid AS 系号, LEFT(st_id,6) AS 班级, COUNT(st_nm) AS 人数, AVG(st_score) AS 均分FROM student GROUP BY LEFT(st_id,6), st_dpid操作8.10：查询选修了均分在75以上的课程号及均分SELECT cs_id AS 课程编号, AVG(score) AS 均分FROM slt_couseGROUP BY cs_id HAVING AVG(score)>75操作8.11：查询选修了2门以上课程的学生学号SELECT st_id FROM slt_couseGROUP BY st_id HAVING COUNT(*)>2操作8.12：明细汇总年龄<20的学生，并汇总学生数量、平均年龄SELECT st_nm,DATEPART(yy,GETDATE( )-DATEPART(yy,st_birth) AS 年龄FROM studentWHERE DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)<20COMPUTE COUNT(st_nm),AVG(DATEPART(yy,GETDATE()-DATEPART(yy,st_birth)操作8.13：按班级明细汇总成绩<85分的学生，汇总学生数、均分SELECT st_nm, LEFT(st_id,6) AS 班级, st_scoreFROM studentWHERE st_score<85ORDER BY 班级COMPUTE COUNT(st_nm), AVG(st_score) BY 班级实验9 数据查询（5）连接查询操作9.1：用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号SELECT a.st_id, st_nm, st_sex, cs_idFROM student a, slt_couse bWHERE a.st_id = b.st_idORDER BY a.st_id操作9.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号SELECT a.st_id, st_nm, st_sex, cs_idFROM student a INNER JOIN slt_couse bON a.st_id = b.st_idORDER BY a.st_id操作9.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩SELECT a.st_id, st_nm, cs_nm, scoreFROM student a, slt_couse b, couse cWHERE a.st_id = b.st_id AND b.cs_id = c.cs_idORDER BY a.st_id操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩SELECT a.st_id, st_nm, cs_nm, scoreFROM slt_couse a INNER JOIN student b ON a.st_id = b.st_idINNER JOIN couse c ON a.cs_id = c.cs_idORDER BY b.st_id操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩SELECT a.st_id, st_nm, scoreFROM student a，slt_couse bWHERE a.st_id = b.st_id AND b.cs_id = '1002'ORDER BY b.st_id操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩SELECT a.st_id, st_nm, scoreFROM student a, slt_couse b, couse cWHERE a.st_id=b.st_id AND b.cs_id=c.cs_id AND c.cs_nm='数据结构'ORDER BY a.st_id操作9.7：用左外连接查询没有选修任何课程的学生学号、姓名SELECT a.st_id, st_nm, scoreFROM student a LEFT OUTER JOIN slt_couse b ON a.st_id = b.st_idWHERE b.cs_id IS NULLORDER BY b.st_id操作9.8：用右外连接查询选修各个课程的学生学号SELECT b.cs_id, a.st_idFROM slt_couse a Right OUTER JOIN couse b ON a.cs_id = b.cs_idORDER BY b.cs_id实验10 数据查询（6）子查询操作10.1：用子查询对各班人数进行查询（新增列）SELECT DISTINCT LEFT(a.st_id,6) AS 班级, 人数 = ( SELECT COUNT(st_id) FROM student b WHERE LEFT(a.st_id,6) = LEFT(b.st_id,6)FROM student a ORDER BY LEFT(a.st_id,6) ASC操作10.2：用子查询对各课程的选课人数进行查询（新增列）SELECT DISTINCT a.cs_id, 人数 = ( SELECT COUNT(st_id) FROM slt_couse b WHERE a.cs_id = b.cs_id)FROM slt_couse a ORDER BY a.cs_id ASC操作10.3：查询选修了1002课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序通过子查询实现：使用IN关键字SELECT st_id, st_nm, st_sex FROM studentWHERE st_id IN( SELECT st_id FROM slt_couse WHERE cs_id='1002' AND score < 60)ORDER BY st_nm通过子查询实现：使用比较运算符SELECT st_id, st_nm, st_sexFROM student aWHERE ( SELECT score FROM slt_couse b WHERE a.st_id = b.st_id AND cs_id = '1002' ) < 60ORDER BY st_nm操作10.4：查询“东方红”同学所在班的学生信息，并按姓名降序排序通过子查询实现：IN运算符SELECT st_id, st_nm, st_sex FROM student WHERE LEFT(st_dpid,6) IN(SELECT Left(st_dpid,6) FROM student WHERE st_nm='东方红')ORDER BY st_nm DESC通过自连接查询实现：JOINSELECT s1.st_id, s1.st_nm, s1.st_sex FROM student s1 JOIN student s2 ON LEFT(s1.st_dpid,6) = LEFT(s2.st_dpid,6)WHERE s2.st_nm='东方红'ORDER BY s2.st_nm操作10.5：查询其它班比070511班某一学生的1002号课程成绩高的学生信息（ANY/ALL）SELECT * FROM slt_couseWHERE score > ANY(SELECT score FROM slt_couse WHERE cs_id = '1002' AND LEFT(st_id,6)='070511')AND LEFT(st_id,6) <> '070511' AND cs_id = '1002'操作10.6：查询其它班比070511班任一学生的1002号课程成绩高的学生信息（ANY/ALL）SELECT * FROM slt_couseWHERE score > ALL(SELECT score FROM slt_couse WHERE cs_id = '1002' AND LEFT(st_id,6)='070511')AND LEFT(st_id,6) <> '070511' AND cs_id = '1002'操作10.7：查询大于等于60分且且比1003课程平均成绩低的学生课程信息（BetweenAnd）SELECT * FROM slt_couse aWHERE a.score BETWEEN 60 AND ( SELECT AVG(b.score) FROM slt_couse bWHERE b.cs_id='