数据库操作练习

1、/*实验数据库操作*/-创建数据库create database testON(NAME = test_data, FILENAME = 'D:testtest_data.mdf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 1MB )LOG ON(NAME = test_log, FILENAME = 'D:testtest_log.ldf',SIZE = 1MB,MAXSIZE = 5MB,FILEGROWTH = 10% )-查看数据库属性exec sp_helpdb test-删除数据库drop databa

2、se test/*实验表操作*/USE testCREATE 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

3、 tinyint NULL-学生职务)CREATE TABLE couse-课程表(cs_id nVarChar(4) primary key NOT NULL ,-课程编号cs_nm nVarChar(20) NOT NULL ,-课程名称cs_tm int NULL ,-课程学时cs_sc int NULL-课程学分)insert into couse values('C002','高等数学','60','0')insert into couse values('1001','大学英语',

4、9;60','3')insert into couse values('1002','数据结构','56','2')CREATE TABLE slt_couse-选课表(cs_id nVarChar(4) constraint fk_cs_id foreign key(cs_id) references couse(cs_id) NOT NULL ,-课程编号st_id nVarChar(9) constraint fk_st_id foreign key(st_id) references student

5、(st_id) NOT NULL ,-学生编号score int NULL ,-课程成绩sltdate datetime NULL,-选课日期PRIMARY KEY(cs_id,st_id)CREATE TABLE dept-院系表(dp_id nVarChar(2) NOT NULL ,-系编号dp_nm nVarChar(20)NOT NULL ,-院系名称dp_drtnVarChar(8) NULL ,-院系主任dp_telnVarChar(12) NULL-联系电话)-操作.5：为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为，允许为空）ALTER TAB

6、LE dept ADD dp_count nvarchar(3) NULL-操作.6：修改“dept”表的“dp_count”列数据类型为intALTER TABLE dept ALTER COLUMN dp_count INT-操作.7：删除“dept”表的“dp_count”列ALTER TABLE dept DROP COLUMN dp_count-操作.8：删除“dept”表DROP TABLE dept INSERT INTO student VALUES('070201002','张三','男','1994-5-11'

7、,'95','2013-9-1','湖南长沙','5','1')INSERT INTO couse VALUES('C001','数据库','72','4')insert into slt_couse values('C002','070201002','90','2014-1-1')INSERT INTO slt_couse VALUES('C001','0702

8、01002','85','2013-9-13')INSERT INTO dept VALUES('5','软件学院','李静云',)/*实验数据完整性*/-操作.1：将student表中的st_sex列属性更改为NOT NULLalter table student alter column st_sex nVarChar(2) NOT NULL-操作.2：将student表中的st_from列默认值设置为“陕西省”alter table student add constraint default_fro

9、m default '陕西省' for st_from/*操作.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。*/create default df_today as getdate()exec sp_bindefault df_today, 'slt_couse.sltdate' exec sp_unbindefault 'slt_couse.sltdate' drop default df_today-操作.4：将slt_couse表中的s

10、core列的检查约束设置为>=0且<=100alter table slt_couse add constraint chk_score check(score>=0 and score<=100)/*操作.5：创建规则约束对象rl_sex，用于检查性别的取值仅限于“男”和“女”，并将其绑定到student表中的st_sex列，然后取消绑定，最后删除规则约束对象rl_sex*/create rule rl_sex as chksex in('男','女')exec sp_bindrule rl_sex, 'student.st_s

11、ex'exec sp_unbindrule 'student.st_sex'drop rule rl_sex-操作.6：将dept表中的dp_id列设置为主键alter table dept add constraint pk_dp_id primary key(dp_id)-操作.7：将dept表中的dp_nm列设置为唯一性约束alter table dept add constraint un_dp_nm unique(dp_nm)-操作.8：向slt_couse表中添加标识列id，第行默认值为，相邻两个标识列间的增量为ALTER TABLE slt_couse A

12、DD id INT IDENTITY(1,1) NOT NULL-操作.9：被参照表为dept，参照表为studentalter table student add constraint fk_id foreign key(st_dpid) REFERENCES dept(dp_id)/*实验数据更新*/-操作.1：向dept表插入一条记录，系号，系名自动控制系，系主任为李其余，电话INSERT INTO dept VALUES('11', '自动控制系', '李其余', '81234567')/*操作.2：向student表插入

13、一条记录，学号，姓名为王小五，性别为男，出生日期为年月日，系号为，其余字段为NULL或默认值*/INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)VALUES ('070201001', '王小五', '男', '1990.9.9', '11' )INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)VALUES ('070201003', '张倩'

14、, '女', '1994.9.9', '11' )-操作.3：向couse表插入一条记录，课程号，课程名为操作系统，其余字段为NULL或默认值INSERT INTO couse(cs_id, cs_nm) VALUES ('1234', '操作系统')-操作.4：向slt_couse表插入一条记录，课程号，学名，其余字段为NULL或默认值INSERT INTO slt_couse(cs_id,st_id) VALUES ('1234', '070201001')-操作.5：修改stu

15、dent表记录，将王小五的入学成绩改为update student set st_score='85' where st_nm='王小五'-操作.6：修改couse表记录，将所有记录的学分改为，学时改为update couse set cs_tm='64',cs_sc='4'-操作.7：修改slt_couse表记录，将课程号为，学名为的记录的成绩改为update slt_couse set score='77'where cs_id='1234' and st_id='070201001&#

16、39;-操作.8：删除slt_couse表记录，将课程号为，学名为的记录删除delete from slt_couse where cs_id='1234' and st_id='070201001'-操作.9：删除couse表记录，将课程号为的记录删除delete from couse where cs_id='1234'/*实验数据查询（）简单查询*/-操作.1：查询所有系的信息select * from dept-操作.2：查询所有的课程号与课程名称select cs_id,cs_nm from couse-操作.3：在查询student表

17、时使用列表达式：入学成绩+400select st_id,st_nm,st_score,st_score+400 as '新成绩' from student-操作.4：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话select dp_id as '系号',dp_nm as '系名',dp_drt as '系主任',dp_tel as '联系电话' from dept-操作.5：使用"="号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分sel

18、ect '课程号'=cs_id,'课程名'=cs_nm,'学时'=cs_tm*16,'学分'=cs_scfrom couse-操作.6：查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任select '系号：',dp_id,'系名：',dp_nm,'系主任：',dp_drtfrom dept-操作.7：显示所有学生的学号、姓名、性别和入学年份select st_id,st_nm,st_sex,datepart(YY,st_date) as '入学年

19、份' from student-操作.8：显示所有学生学号、姓名、性别和班级(学号前位)select st_id,st_nm,st_sex,LEFT(st_id,6) as 'class' from student-(7)消除查询结果中的重复项-操作.9：显示所有学生班级select distinct LEFT(st_id,6) as 'class' from student-操作.10：显示前条学生记录信息select top 5 * from student-操作.11：显示前%条学生记录信息select top 25 percent * from

20、student-操作.12：显示前n条学生记录信息，n为局部变量declare n intset n = 5 select top (n)* from student/*实验数据查询（）条件查询*/*1使用关系表达式表示查询条件*/-操作.1：查询dept表中系号为的院系信息select * from dept where dp_id='11'-操作.2：查询student表中系的学生学号、姓名、性别和所在系编号select st_id,st_nm,st_sex,st_dpid from student where st_dpid='11'-操作.3：查询stu

21、dent表中年及以后入学的学生信息select * from student where datepart(yy,st_date)>=2008-操作.4：在查询student表班学生的学号、姓名、性别和入学成绩-测试数据INSERT INTO student VALUES('080808001','李四','男','1993-5-11','95','2012-9-1','湖南长沙','5','2')select st_id,st_nm,st_se

22、x,st_score from student where LEFT(st_id,6)='080808'/*2使用逻辑表达式表示查询条件*/-操作.5：查询student表中非系的学生信息select * from student where not st_dpid='11'-操作.6：查询选修了号课程且成绩在以下的学生学号select st_id from slt_couse where cs_id='1002' and score<'60'-操作.7：查询年入学的系所有男生信息select * from student

23、where DATEPART(YY,st_date)=2007 and st_dpid='11' and st_sex='男'-操作.8：查询系和系的学生信息select * from student where st_dpid='11' or st_dpid='12'-操作.9：查询系和系所有年入学的学生信息select * from student where (st_dpid='11' or st_dpid='12')and DATEPART(YY,st_date)=2007/*使用LIKE关

24、键字进行模糊查询*/-操作.10：查询所有“计算机”开头的课程信息-测试数据insert into couse values('C003','计算机基础','72','3')select * from couse where cs_nm like '计算机%'-操作.11：查询所有由三个字组成的“王”姓学生信息select * from student where st_nm like '王_'-操作.12：查询所有课程名中包含“信息”的课程信息-测试数据insert into couse valu

25、es('C004','信息化教学研究','70','3')select * from couse where cs_nm like '%信息%'-操作.13：查询学生姓名介于王姓到张姓的信息select * from student where st_nm like '王&nbhy;张%'/*使用BetweenAnd关键字进行查询*/-操作.14：查询在.7.1到.6.30之间出生的学生信息-测试数据insert into student values('090901001'

26、,'林夕','女','1990.5.3','55','2009-9-1','湖南益阳','5','2')select st_id,st_nm,st_birth,st_sex,st_from from student where st_birth between '1989.7.1' and '1990.6.30'-操作.15：查询选修了号课程且成绩在到之间的学生选课信息-测试数据insert into slt_couse values(

27、'1001','090901001','76','2012-11-2')select * from slt_couse where (score between '60' and '80')and cs_id='1001'/*使用IN关键字进行查询*/-操作.16：查询系、系、系的学生信息select * from student where st_dpid in('11','12','13')-操作.17：查询所有张，王，李，赵姓的学

28、生的学号、姓名、性别select st_id,st_nm,st_sex from student where LEFT(st_nm,1) in('张','王','李','赵')/*使用NOT NULL关键字进行查询*/-操作.18：查询所有生源为非空的学生信息select *from student where st_from is not null-操作.19：查询选修了号课程且成绩为空的学生选课信息select * from slt_couse where cs_id='1001' and score is n

29、ull/*实验数据查询（）查询排序与查询结果存储*/-操作.1：查询课程信息，按课程名称降序排序select * from couse order by cs_nm desc-操作.2：查询选修了号课程成绩非空的学生学号和成绩，并按成绩降序排序select st_id,score from slt_couse where cs_id='1001' and score is not null order by score desc-操作.3：查询系学生学号、姓名和年龄，按年龄升序排序select st_id,st_nm,datediff(yy,st_birth,GETDATE()

30、 as 'age' from student where st_dpid='11' order by age asc-操作.4：查询学生信息，按姓名升序排序，再按系号降序排序select * from student order by st_nm asc, st_dpid desc-操作.5：创建学生表副本student01，仅保留学生学号、姓名和性别select st_id,st_nm,st_sex into student01 from studentselect * from student01-操作.6：查询陕西籍学生，将结果保存在新表st_shanxi

31、select * into st_shanxi from studentwhere st_from='陕西省'-操作.7：查询选修了号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001select * into slt1001 from slt_couse where cs_id='1001' order by st_id asc-操作.8：用局部变量stage保存学生张三的年龄declare stage intselect stage =DATEDIFF(YY,st_birth,GETDATE() from studentwhere st_nm

32、='张三'-操作.9：用局部变量name和stscore保存班按学号排序后最后一个学生的姓名和入学成绩declare name nVarChar(8), stscore int select name=st_nm,stscore=st_score from student where LEFT(st_id,6) ='070101' order by st_id /*实验数据查询（）查询统计与汇总*/ -操作.1：查询课程总数select COUNT(*) as '课程总数' from couse-操作.2：查询选修号课程的学生人数select C

33、OUNT(st_id) as '学生人数' from slt_couse where cs_id='1001'-操作.3：查询被选修课程的数量select COUNT(cs_id) from slt_couse-操作.4：查询选修班学生的平均入学成绩select AVG(st_score) as '平均入学成绩' from student where LEFT(st_id,6)='070101'-操作.5：查询号学生选修课程的数量、总分以及平均分select COUNT(cs_id) as '课程数量',SUM(s

34、core) as '总分',AVG(score) as '平均分' from slt_couse where st_id='070101001'-操作.6：查询选修号课程的学生人数、最高分、最低分和平均分select COUNT(*) as '学生人数',MAX(score) as '最高分', MIN(score) as '最低分',AVG(score) as '平均分' from slt_couse where cs_id='1001'-操作.7：求各个课程号和相

35、应的选课人数select cs_id,COUNT(cs_id) from slt_couse group by cs_id-操作.8：统计各班人数select LEFT(st_id,6) as '班级',COUNT(st_id) as '人数' from studentgroup by LEFT(st_id,6) -操作.9：依次按班级、系号对学生进行分类统计人数、入学平均分select LEFT(st_id,6) as '班级',st_dpid as '系号' ,COUNT(st_id) as '人数',AVG(

36、st_score) as '入学平均分' from student group by LEFT(st_id,6),st_dpid-操作.10：查询选修了均分在以上的课程号及均分select cs_id as '课程编号',AVG(score) as '均分' from slt_cousegroup by cs_id having AVG(score)>75-操作.11：查询选修了门以上课程的学生学号select st_id from slt_couse group by st_id having COUNT(*)>2-操作.12：明细

37、汇总年龄<20的学生，并汇总学生数量、平均年龄select st_nm,DATEDIFF(YY,st_birth,GETDATE() as 'age' from student where DATEDIFF(YY,st_birth,GETDATE()<20 compute count(st_nm),AVG(DATEDIFF(YY,st_birth,GETDATE()-操作.13：按班级明细汇总成绩<85分的学生，汇总学生数、均分select st_nm,LEFT(st_id,6) as '班级',st_score from studentwhe

38、re st_score<85 order by 班级 compute count(st_nm),avg(st_score) by 班级/*实验数据查询（）连接查询*/-操作.1：用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号select a.st_id,a.st_nm,a.st_sex,b.cs_id from student a,slt_couse bwhere a.st_id=b.st_id -操作.2：用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号select a.st_id,a.st_nm,a.st_sex,b.cs_id from stude

39、nt a join slt_couse bon a.st_id=b.st_id -操作.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩select a.st_id,a.st_nm,b.cs_nm,c.score from student a,couse b,slt_couse cwhere a.st_id=c.st_id and b.cs_id=c.cs_id-操作.4：用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩select a.st_id,a.st_nm,b.cs_nm,c.score from student a join slt_couse c

40、on a.st_id=c.st_id join couse b on b.cs_id=c.cs_id-操作.5：查询选修了课程的学生学号、姓名及课程成绩select a.st_id,a.st_nm,b.score from student a,slt_couse bwhere a.st_id=b.st_idand b.cs_id='1002'-操作.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩select a.st_id,a.st_nm,c.score from student a,couse b,slt_couse cwhere a.st_id=c.st_id a

41、nd b.cs_id=c.cs_id and b.cs_nm='数据结构'-操作.7：用左外连接查询没有选修任何课程的学生学号、姓名select a.st_id,a.st_nm from student a left outer join slt_couse b on a.st_id=b.st_id where b.cs_id is null-操作.8：用右外连接查询选修各个课程的学生学号select a.st_id,b.cs_id from slt_couse a right outer join couse b on a.cs_id=b.cs_id/*实验数据查询（）子查询

42、*/-操作.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 -操作.2：用子查询对各课程的选课人数进行查询（新增列）select distinct a.cs_id ,'人数'=(select COUNT(st_id)from slt_couse b where a.cs_id=b.cs_id)

43、 from slt_couse a -操作.3：查询选修了课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序-通过子查询实现：使用IN关键字select st_id,st_nm,st_sex from student where st_id in(select st_id from slt_couse where cs_id='1002' and score<60)order by st_nm asc-通过子查询实现：使用比较运算符select st_id,st_nm,st_sex from student a where(select score from slt

44、_couse b where a.st_id=b.st_id and cs_id='1002')<60-操作.4：查询“东方红”同学所在班的学生信息，并按姓名降序排序-通过子查询实现：IN运算符select st_id,st_nm,st_sex from student where LEFT(st_id,6) in(selectLEFT(st_id,6) from student where st_nm='东方红')order by st_nm desc-通过自连接查询实现：JOINselect a1.st_id,a1.st_nm,a1.st_sex from student a1 join student a2 onLEFT(a1.st_id,6)=LEFT(a2.st_id,6) where a2.st_nm='东方红' order by st_nm desc-操作.5：查询其它班比班某一学生的号课程成绩高的学生信息（ANY/ALL）select * from slt_couse where score > any(select score from slt_couse where cs_id='1002' and