数据库上机实验总结(含代码)

实验一

（1）无条件单表查询

select sname NAME,'year of birth:' BIRTH,2004-sage BIRTHDAY,LOWER(sdept) DEPARTMENT FROM student;

（2）有条件单表查询

SELECT sname,sdept,sage FROM student WHERE sage NOT BETWEEN 20 AND 23;

（3）单表嵌套（一层）查询

SELECT sno,sname,sdept FROM student WHERE sdept IN(SELECT sdept FROM student WHERE sname='刘晨');

（4）复合条件多表查询

SELECT student.sno,sname,cname,grade FROM student ,sc,course WHERE student.sno=sc.sno AND https://www.360docs.net/doc/1d12638454.html,o=https://www.360docs.net/doc/1d12638454.html,o;

（5）使用COUNT（）的单表查询

SELECT COUNT(*) FROM student;

（6）使用AVG（）的单表查询

SELECT AVG(grade) '平均成绩' from SC where CNO='1';

（7）查询结果分组

SELECT cno,COUNT(sno) '人数' FROM sc GROUP BY cno;

（8）查询结果排序

SELECT * FROM student ORDER BY sdept,sage DESC;

（9）使用通配符的查询

SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'刘%';

（10）使用换码字符的单表查询

SELECT cno,ccredit FROM course WHERE cname LIKE 'DB\_Design'ESCAPE'\';

（11）插入单个元组

插入一个新学生元组

Insert into student (sno,sname,ssex,sdept,sage) values ('200215128','陈冬','男','IS',18)

（12）插入子查询结果

对每一个系，求学生平均年龄，并把结果存入数据库

Create table dept_age(sdept char(15),avg_age int)

Insert into dept_age(sdept,avg_age) select sdept,avg(sage) from student group by sdept

（13）修改某个元组的值

将学生200215121的年龄改为22岁

Update student set sage=’22’ where sno=’200215121’

（14）修改多个元组的值

将所有学生的年龄增加一岁

Update student set sage=sage+1

（15）删除一个元组的值

删除学号为200215128的学生记录

delete from student where sno='200215128'

（16）建立视图

建立信息系学生的视图

create view is_student as select sno,sname,sage from student where sdept='IS'

×（17）查询视图

查询选修了1号课程的信息系学生信息

Select is_student.sno,sname from is_student,sc where is_student.sno=sc.sno and https://www.360docs.net/doc/1d12638454.html,o=’1’

×（18）更新视图

将信息系学生视图is_student中学号为95001的学生姓名改为李楠

update is_student set sname='李楠' where sno='95002'

将下列问题用SQL命令表示：

1．查询‘IS’系学生的学号、姓名、性别。

SELECT sno,sname,ssex FROM student WHERE sdept='IS';

2．查询‘IS’系年龄在20岁以下的学生。

SELECT * FROM student WHERE sdept='IS'AND sage<20;

3．查询所有不姓‘刘’的学生的学号、姓名、性别。

SELECT sname,sno,ssex FROM student WHERE sname NOT LIKE'刘%';

4. 查询student表中学生的总人数。

SELECT COUNT(*) '总人数' FROM student;

5. 查询和‘李勇’同性别的所有同学的姓名。

SELECT sname from student where ssex in(select ssex from student where sname='李勇');

6. 查询和‘李勇’同性别并同系的所有同学的姓名。

Select sname from student where ssex in (select ssex from student where sname='李勇') and sdept in (select sdept from student where sname='李勇')

7. 查询选修2号课程的学生的学号。

Select sno from sc where cno='2'

8. 求3号课程的平均成绩。

Select avg(grade) from sc where cno=’3’

9.查询选修2号课程的学生的最高分。

Select max(grade) from sc where cno=’2’

10.按成绩降序排列，输出‘IS’系学生选修了2号课程的学生的姓名和成绩。

Select sname,grade from student,sc where sdept='IS' and cno='2' and student.sno=sc.sno order by grade

SQL查询分析器下建数据库的命令代码：

create database 霍双双200826352

on(name='霍双双200826352_data',filename='E:\霍双双20082635\霍双双200826352_data.mdf',size=10mb,maxsize=50mb,filegrowth=10%)

log on(name='霍双双200826352_log',filename='E:\霍双双20082635\霍双双200826352_log.ldf',size=10mb,maxsize=50mb,filegrowth=10%)

在查询分析器重建立各表的命令代码：

建立student表：

create table student

(sno char(5) primary key,sname char(20),ssex char(2),sage int,sdept char(15))

建立course表：

create table course

(cno char(2)primary key,cname char(15),cpno char(2),ccredit int)

建立cs表：

use 霍双双200826352

create table sc

(sno char(5),cno char(2),grade smallint,primary key(sno,cno),foreign key(sno)references student(sno),foreign key(cno)references course(cno))

实验二

T-SQL查询、存储过程、触发器、完整性上机作业题

第一部分：T-SQL程序设计

（1）.如果3号课程的平均成绩在80分以上，则输出“3号课程成绩良好”，否则输出“3号成绩一般”declare @avg float set @avg=(select avg(grade)from sc where cno='3')if @avg>80print'3号课程成绩良好'else print'3号成绩一般'

（2）计算并输出95003号学生的平均成绩，若无该生信息，则显示“该生未选课”,提示信息.

declare @avg float if(select count(*)from sc where sno='95003')=0 print '该生未选课' else begin select @avg=avg(grade)from sc where sno='95003' print'95003号学生平均成绩' print @avg end

（3）.如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息

declare @text char(10) if exists(select grade from SC where grade>90)select Sno,Cno,Grade from SC where Grade>90 else begin set @text='没有学生的课程成绩在90分以上' print @text end

×(4).利用游标逐行显示student表中的记录。

declare stu cursor for select *from student open stu fetch next from stu while @@fetch_status=0 fetch next from stu close stu deallocate stu

(5).用自定义函数计算全体男生的平均年龄

create function avg_age(@sex char(2)) returns int

as begin declare @aver int select @aver=(select avg(Sage) from Student where Ssex=@sex )return @aver end

declare @aver1 int,@sex char(2) set @sex='男' select @aver1=dbo.avg_age(@sex)

select @aver1 as '全体男生的平均年龄'

go

(6).显示course表中课程名的前2个字符。

select substring(Cname,1,2) from Course

(7).在一列中显示student中各元组的学号中的年级，列名显示为“年级”；另一列中显示学号中的学生序列号，列名显示为“序号”。

select substring(Sno,1,2) 年级,substring(Sno,3,len(Sno)-1) 序号 from Student order by Sno

(8).在选课表中显示学号、课程号，并根据成绩：0-59显示“不合格”；60-79显示“合格”；80-89显示“良好”；90-100显示“优秀。”

select Sno as '学号',Cno as '课程号', grade =case when Grade<=59 then '不合格' when Grade>=60 and Grade<=79 then '合格' when Grade>=80 and Grade<=89 then '良好' else '优秀' end from SC

第二部分：存储过程

（1）创建一个为worker表添加职工记录的存储过程Addworker

go

if exists(select name from sysobjects where name='Addworker' and type='P') drop procedure Addworker go

create proc Addworker @职工号char(4),@姓名char(8),@性别char(2),@出生日期datetime,@党员否char(2),@参加工作 datetime,@部门号 char(4)

as

insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号)values(@职工号,@姓名,@性别,@出生日期,@党员否,@参加工作,@部门号)

go

exec Addworker '16','王璐','女','1988-11-20','否','2010-08-21','11'

（2）创建一个存储过程Delworker删除worker表中指定职工号的记录

go

if exists(select name from sysobjects where name='Delworker' and type='P') drop procedure Delworker

go

create procedure Delworker @职工号char(4) as delete from worker where 职工号=@职工号

go

exec Delworker '16'

（3）显示存储过程Delworker的定义信息。

Sp_helptext Delworker

（4）删除存储过程Addworker和Delworker。

drop procedure Addworker, Delworker

（5）创建并执行以下存储过程：

a.从数据库表中查询，返回学生学号、姓名、课程名、成绩

use 霍双双200826351

go

if exists(select name from sysobjects where name='select_stu' and type='P') drop procedure select_stu go

create procedure select_stu

as select SC.Sno,Sname,Cname,Grade from Student,SC,Course where SC.Sno=Student.Sno and https://www.360docs.net/doc/1d12638454.html,o=https://www.360docs.net/doc/1d12638454.html,o

go

exec select_stu

b.从数据库表中查询指定学号的学生学号，姓名，该存储过程接受与传递参数，精确匹配的值

use 霍双双200826351

go

if exists(select name from sysobjects where name='select_sno' and type='P') drop procedure select_sno go

create procedure select_sno @Sno char(5)

as select Sno,Sname from Student where Sno=@Sno

go

exec select_sno '95002'

×第三部分：触发器

(1)在表depart上创建一个触发器 depart_update , 当更改部门号时同步更改 worker表中对应的部门号。

Go

If exists(select name from sysobjects where name='depart_update'and type='tr') drop trigger depart_update

go

Create trigger depart_update on depart for update as set worker.部门号=（select 部门号 from inserted）where worker.部门号=(select 部门号from deleted)

(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。Go

If exists(select name from sysobjects where name='worker_delete'and type='tr') drop trigger worker_delete

go

create trigger worker_delete on worker for delete as delete salary where salary.职工号=(select 职工号 from deleted)

(3) 删除触发器depart_update

(4) 删除触发器worker_delete

(5)在数据库中创建一个触发器，向选课表添加一条纪录时，检查该纪录的学号在学生表中是否存在，检查该纪录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。

Go

If exists(select name from sysobjects where name='add_student'and type='tr') drop trigger add_student

go

create trigger add_student on sc for insert as

go

第四部分：数据库完整性

1、实施worker表的“性别”字段默认值为“男”的约束

create default default_sex as '男'

go

sp_bindefault'default_sex','worker.性别'

2、实施salary表的“工资”字段值在0~9999的约束、

create rule salary_rule as @salary='[0~9999]'

go

sp_bindrule 'salary_rule','salary.工资'

3、实施depart表的“部门号”字段值唯一的非聚集索引的约束

4、为worker表建立外键“部门号”，参考表depart的“部门号”列。

5、建立一个规则 sex：@性别=’男’OR @性别=’女’,将其绑定到worker表的“性别”列上。

Create rule sex as @性别='男'OR @性别='女'

Go

Sp_bindrule 'sex','worker.性别'

6、删除1小题所建立的约束。

7、删除2小题所建立的约束。

8、删除3小题所建立的约束

9、删除4小题所建立的约束

10.解除5小题所建立的绑定并删除规则sex