数据库操作练习.doc

/*实验数据库操作*/-创建数据库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 database 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 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,大学英语,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(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 TABLE 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,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,070201002,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_from 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表中的score列的检查约束设置为=0且=0 and score=2008-操作.4：在查询student表班学生的学号、姓名、性别和入学成绩-测试数据INSERT INTO student VALUES(080808001,李四,男,1993-5-11,95,2012-9-1,湖南长沙,5,2)select st_id,st_nm,st_sex,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 score75-操作.11：查询选修了门以上课程的学生学号select st_id from slt_couse group by st_id having COUNT(*)2-操作.12：明细汇总年龄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 studentwhere st_score85 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 student 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 con 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 and 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/*实验数据查询（）子查询*/-操作.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) 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 score60)order by st_nm asc-通过子查询实现：使用比较运算符select st_id,st_nm,st_sex from student a where(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-操作.6：查询其它班比班任一学生的号课程成绩高的学生信息（ANY/ALL）select * from slt_couse where 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-操作.7：查询大于等于分且且比课程平均成绩低的学生课程信息（BetweenAnd）select * from slt_couse a where a.score between 60 and(select AVG(b.score)from slt_couse b where b.cs_id=1003)-操作.8：查询系主任为“赵虎”的系的所有学生信息-通过子查询实现：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=赵虎)/*实验数据查询（）数据更新与子查询*/-操作.1：将班所有学生信息插入到表student01(st_id,st_nm,st_sex)insert into student01 select st_i