SQL操作复习题及复习资料.docx

《SQL操作复习题及复习资料.docx》由会员分享，可在线阅读，更多相关《SQL操作复习题及复习资料.docx（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 数据库操作1创建数据库：操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为，初始大小为1MB，最大尺寸为5MB，增长速度为10%。参考答案：create database TestON primary(name = test_data, = d:testtest_data.mdf,size = 5MB,maxsize = unlimited, = 1MB)LOG ON(name = test_log, = d:testtest_log.ldf,siz

2、e = 1MB,maxsize = 5MB, = 10%)GO2查看数据库属性：操作1.2：使用T-SQL语句查看数据库test属性参考答案：EXEC sp_helpdb test3删除数据库：操作1.3：使用T-SQL语句删除数据库test参考答案：drop database Test2 表操作1创建表：操作2.1：创建学生表：表名：student说明：学生基本信息表属性列数据类型长度空值列约束说明st_idnVarChar9Not NullPK学生学号st_nmnVarChar8Not Null学生姓名st_sexnVarChar2Null学生性别st_birthdatetimeNull出

3、生日期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 datetim

4、e 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 nVa

5、rChar(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 ,

6、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)N

7、ULL)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_c

8、ount3删除表操作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 TAB

9、LE 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.sltdateGOEXEC sp_unbindefault slt_couse.sltdateGODROP DEFAULT df_todayGO4检查约束( CHECK )操作3.4：将s

10、lt_couse表中的score列的检查约束设置为=0且=0 AND score= 2008：在查询student表080808班学生的学号、姓名、性别与入学成绩SELECT st_id, st_nm, st_sex, st_score FROM studentWHERE Left(st_id,6)=0808082使用逻辑表达式表示查询条件：查询student表中非11系的学生信息SELECT * FROM student WHERE NOT (st_dpid = 11)：查询选修了1002号课程且成绩在60以下的学生学号SELECT st_id FROM slt_couseWHERE (cs

11、_id=1002) AND (score75：查询选修了2门以上课程的学生学号SELECT st_id FROM slt_couseGROUP BY st_id HAVING COUNT(*)2：明细汇总年龄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()-DATEP

12、ART(yy,st_birth)：按班级明细汇总成绩85分的学生，汇总学生数、均分SELECT st_nm, LEFT(st_id,6) AS 班级, st_scoreFROM studentWHERE st_score85ORDER 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 = idO

13、RDER 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 = _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 = idORDER BY a.st_i

14、d操作9.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩SELECT a.st_id, st_nm, cs_nm, scoreFROM slt_couse a INNER JOIN student b ON a.st_id = _idINNER JOIN couse c ON _id = _idORDER BY b.st_id操作9.5：查询选修了1002课程的学生学号、姓名及1001课程成绩SELECT a.st_id, st_nm, scoreFROM student a，slt_couse bWHERE a.st_id = id AND b.cs_id = 1002ORD

15、ER BY b.st_id操作9.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩SELECT a.st_id, st_nm, scoreFROM student a, slt_couse b, couse cWHERE a.st_id=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 = idWHERE b

16、.cs_id IS NULLORDER BY b.st_id操作9.8：用右外连接查询选修各个课程的学生学号10 数据查询（6）子查询：用子查询对各班人数进行查询（新增列）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：用子查询对各课程的选课人数进行查询（新增列）SELECT DISTINCT a.cs_id, 人数

17、= ( 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：查询选修了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通过子查询实现：使用比较运算符SE

18、LECT 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 ) 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

19、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=1003 )：

20、查询系主任为“赵虎”的系的所有学生信息通过子查询实现：IN运算符SELECT * FROM student WHERE EXISTS( SELECT * FROM dept WHERE st_dpid = dp_id AND dp_drt=赵虎 )通过子查询实现：=运算符SELECT * FROM student WHERE st_dpid =( SELECT dp_id FROM dept WHERE dp_drt=赵虎 )11 数据查询（7）数据更新及子查询：将070511班所有学生信息插入到表student01(st_id,st_nm,st_sex)INSERT INTO student

21、01SELECT st_id,st_nm,st_sex FROM studentWHERE LEFT(st_id,6)=070511操作11.2：生成1002号课程的成绩单student02(st_id,st_nm, score)INSERT INTO student02SELECT a.st_id,st_nm,score FROM student a, slt_couse bWHERE a.st_id=b.st_id AND cs_id=1002：将有不及格成绩的学生的st_mnt值更改为3UPDATE student SET st_mnt=3WHERE st_id IN( SELECT D

22、ISTINCT st_id FROM slt_couse WHERE score 60)：将没有被选修的课程的学分更改为0UPDATE couse SET cs_sc=0WHERE cs_id NOT IN( SELECT DISTINCT cs_id FROM slt_couse)：删除5系学生的选课信息DELETE FROM slt_couseWHERE st_id IN( SELECT st_id FROM student WHERE st_dpid = 5)：删除学分为0的选课信息DELETE FROM slt_couseWHERE cs_id IN( SELECT DISTINCT cs_id FROM slt_couse WHERE cs_id=0)