数据库实验内容答案.doc

实验内容：实验一：数据库的操作使用Management Studio和sql语句分别完成以下操作：1.创建一个名为“SM”的数据库，数据文件初始大小为3MB，最大为50MB，数据库自动增长，增长方式按10%；日志文件初始大小为2MB，数据大小不受限制，按1MB增长。 create database sm on ( name=smdata, filename=e:smdata.mdf, size=3, maxsize=50, filegrowth=10%) log on (name=smlog, filename=e:smlog.ldf, size=2, maxsize=unlimited, filegrowth=1)2.修改数据库“SM”，将数据文件名改成“sm_data”,初始大小改成5MB alter database sm modify file ( name=smdata, newname=sm_data, size=5) 3.分别查看数据库“SM”，该数据库中的文件和文件组。 exec sp_helpfile sm exec sp_helpfilegroup sm4.删除数据库“SM”。 drop database sm 实验二：创建表1. 在数据库SM中创建学生表student,课程表course,选课表scstudent(sid,sno,clno,sname,ssex,sage,sbir)说明：sid int identity(1,1) 序号 sno 为主关系键，为字符类型 学号 clno 字符类型， 班级号 sname 字符类型，并不为空 ssex 字符类型，check的值的范围为男女 sbir 日期类型 出生日期 sage int； use sm create table student ( sid int identity(1,1), sno char(10) constraint pk_st primary key, clno char(10), sname varchar(20) not null, ssex char(2) constraint ck_ssex check(ssex in(男,女), sbir datetime, sage int )course(cno,cname,ccredits,ctno,cpno,ctime)说明：cno 字符类型，主关系键 cname 字符类型，唯一键 ccredits 学分，精确数值型，精确长度为2，小数位为1 ctno ，cpno 字符类型 ctime 整型 create table course (cno char(4) constraint pk_c primary key, cname varchar(20) constaint uk_cname unique, ccredit decimal(2,1), ctno char(2), cpno char(4), ctime tinyint )sc(sno,cno,score)说明：sno+cno为主键，并且sno是student的外部键，cno是course的外部键。 score精确数值型，精确长度为4，小数位为1 create table sc ( sno char(10) constraint fk_sno foreign key references student(sno), cno char(4) constriant fk_cno foreign key references course(cno), score decimal(4,1), constraint pk_sc primary key(sno,cno) )2.使用Management Studio对数据库SM中的表插入数据 实验三：表的维护1.用sql语句修改表course的列属性,将cname的长度改为40,且不允许空 alter table course drop constraint uk_cname alter table course alter column cname char(40) not null2.用sql语句向表student中增加列email,且要求输入的电子邮件地址必须包括 alter table student add email varchar(20) constraint ck_email check(email like %) 3.用sql语句删除表student中的列sbir alter table student drop column sbir 4.删除sname列上的约束。 alter table student alter column sname varchar(20) null5.删除表student drop table student实验四：简单数据查询在实验二的基础上，再在sm数据库中新建表teacher，包括如下数据项 teacher(tno,tname,age,sal,dno) tno为教职工编号，tname姓名，age年龄，sal为月薪，dno为部门号在student，course，sc，teacher四张表中进行下列查询1，查询所有0002部门职工的信息； select * from teacher where dno=00022，查询1984年和1985年出生的女生的信息； select * from student where year(sbir) in(1984,1985) and ssex=女 3，查询0001部门、0002部门或0003部门的职工信息； select * from teacher where dno in(0001,0002,0003)4，查询学号为03004的同学正在学习的课程； select cno from sc where sno=030045，查询不姓王或李的同学的信息。 select * from student where sname like 王李%6，查询有多少名学生的物理课成绩不及格； select count(sno) from sc,course where o=o and cname=物理 and score60 and o=o group by sno 实验五 复杂查询(自连接和外连接不考）1. 查询体育课成绩不及格的男生名单 select sname from student,course,sc where student.sno=sc.sno and o=o and cname=体育 and ssex=男 and score602. 将04001班全体学生的成绩置0 sc(sno,cno,score) student(sno,clno.) update sc set score=0 where sno in( select sno from student where clno=04001)3. 删除04002班全体学生的选课记录 delete from sc where sno in( select sno from student where clno=04002)4. 查询所有选修了001号课程的学生的姓名 select sname from student,sc where student.sno=sc.sno and cno=0015. 查询其他班中比04001班所有学生年龄都小的学生，并按年龄的降序输出 select * from student where clno04001 and sage0 exec sp_bindrule rule1,teacher.sal 实验九 存储过程1.在学生表中，根据姓名查询并显示该同学的信息，否则显示“查无此人”（使用ifelse语句） create proc p2(sname varchar(20) -创建存储过程 as if exists( select * from student where sname=sname) select * from student where sname=sname else print 查无此人 exec p2 刘燕 -执行存储过程 2. 查看并显示选修指定课程的人数。 create proc p3(cno char(4) as declare n int select n=count(sno) from sc where cno=cno print 有+convert(varchar(10),n)+个人选修这门课 exec p3 1001 3. 1+2+.+100（使用while语句） declare i int,s int set i=1 set s=0 while (i=90 then 一等 when avg=80 then 二等 when avg=70 then 三等 else 补考 end exec p4 201320180305 实验十 触发器 s(sno,sname,ssex,sbirthday) c(cno,cname,credit,ctime,cnum) sc(sno,cno,score) 1.不允许向数据表s添加数据 create trigger tr1 on s for insert as print 任何人不得添加数据 rollback create trigger tr1 on s instead of insert as select * from s 2.不允许修改数据表s中李四的数据 inserted 表格中保存了即将被添加的记录行,结构与原表相同 deleted 表格中保存即将被删除的记录行,结构与原表相同 修改数据相当于删除旧记录添加新记录 create trigger tr2 on s for update as if exists(select * from deleted where sname=李四) rollback 3.删除学生信息的同时删除该生的选课信息 create trigger tr3 on s for delete as delete from sc where sno in (select sno from deleted) 4.每添加一条选课记录，课程表中相应课程的选修人数加1 * create trigger tr4 on sc for insert as update c set cnum=cnum+1 where o = (select cno from inserted) 5. 每当添加、删除、修改s表中数据,相应班级人数进行更新 * s(sno,sname,ssex,sbirthday,clno) class(clno,cnum)- 班级