实验二 - 数据的查询、更新

实验二 数据的查询、更新 徐龙琴设计制作

实验二 数据的查询、更新

一、实验目的

1、掌握用户自定义数据类型的方法 2、掌握用T-SQL语句进行数据的插入、修改、删除的方法 3、熟练掌握SELECT语句，能够运用该语句完成各种查询 二、实验要求 1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作； 2、能认真独立完成实训内容； 3、实验后做好实验总结，根据实验情况完成总结报告。 三、实验内容 1、用T-SQL语句，创建一用户自定义数据类型：名称为“char20”，数据类型为varchar，长度为20，允许为空。 提示：sp_addtype [@typename=]用户自定义类型的名字 [, @phystype=]系统类型名 [, [@nulltype=] ' not null | null '] [, [@owner=] '拥有该类型的用户名 '] 例：自定义一个名为address的类型，其所属系统类型为varchar，长度为80，不能为空。 sp_addtype address,' varchar(80)', ' not null ' sp_addtype char20,'varchar(20)',null 2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修表，并向各表插入如下相应的数据。 学生表:Student(Sno,Sname,Ssex,Sage,Sdept) 其中Sno 为主键、Ssex取值为男或女、Sage在15到30之间: Sno 95001 95002 95003 95004 Sname 李敏勇 刘晨 王敏 张立 Ssex 男 女 女 男 Sage 20 19 18 18 Sdept CS IS MA IS create database 学生课程数据库 create table Student ( Sno )

int primary key , Sname char(10) not null, Ssex Sage

char(2) check (Ssex in ('男','女')), smallint check(Sage between 15 and 45),

Sdept char(20) default 'CS',

实验二 数据的查询、更新 徐龙琴设计制作

insert into Student values('95001','李敏勇', '男','20', 'CS') insert into Student values('95002','刘晨', '女','19', 'IS') insert into Student values('95003','王敏', '女','18', 'MA') insert into Student values('95004','张立', '男','18', 'IS') 课程表:Course(Cno,Cname,Cpno,Credeit,remarks) 其中Cno为主键、Teacher的类型为char20即为用户定义的数据类型; Cno 1 2 3 4 5 6 7 create table Course ( ) insert into Course values('1','数据库', '5', '4', insert into Course values('2','数学', NULL, '2', insert into Course values('3','信息系统', '1', '4', insert into Course values('4','操作系统', '6', '3', insert into Course values('5','数据结构', '7', '4', insert into Course values('6','数据处理', NULL, '2', '王芳') '刘新') '刘新') '高升') '宋明') '张彬') Cno Cpno char(4) char(2) char20 primary key, , , Cname char(10) not null, Credit char(2) Teacher Cname 数据库 数学 信息系统 操作系统 数据结构 数据处理 Pascal语言 Cpno 5 NULL 1 6 7 NULL 6 Credit 4 2 4 3 4 2 4 Teacher 王芳 刘新 刘新 高升 宋明 张彬 李磊 insert into Course values('7','Pascal语言', '6', '4', '李磊') 学生选修表:SC(Sno,Cno,Grade) 其中Sno,Cno为主键同时又为外键、Grade值在0到100; Sno 95001 95001 95001 95002 95003 95004

Cno 1 2 3 2 2 2 Grade 92 85 88 90 55 70 实验二 数据的查询、更新 徐龙琴设计制作

create table SC ( )

insert into SC values('95001','1', '92') insert into SC values('95001','2', '85') insert into SC values('95001','3', '88') insert into SC values('95002','2', '90') insert into SC values('95003','2', '55') insert into SC values('95004','2', '70')

Sno Cno

int

,

,

check (Grade between 0 and 100),

char(4)

Grade int

primary key (Sno,Cno),

foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno)

3、用T-SQL语句，修改上面所建学生课程数据库中数据: 1) 向学生表:Student中加入一条记录：(95030,谢非,男,22,CS)并保存

insert into Student values('95030','谢非','男','22','CS')

2) 将李敏勇的数据库的成绩改为98分

select Sno from Student where Sname='李敏勇' select Cno from Course where Cname='数据库' update SC set Grade='98'

where Sno=95001 and Cno=1

3) 删除学生表Student中谢非的记录并保存

delete from Student where Sname='谢非'

4) 能不能从Student表中删除李敏勇学生的记录,为什么?能不能删除王敏, 张立两个学生的记录?

delete delete from Student

from Student

where Sname='李敏勇' where Sname='王敏' and Sname='张立'

实验二 数据的查询、更新 徐龙琴设计制作

4、用T-SQL语句，完成下面简单的查询 1）查询全体学生的学号、姓名及年龄.

select Sno,Sname,Sage from Student

2）查询全体学生的姓名, 年龄及所在系（要用小写字母表示系名，并用“系名”来表示 列名）。

select Sname,Sage,系名=LOWER(Sdept) from Student

3）查询选修了课程的学生学号

select distinct Sno from SC

4）查询信息系全体学生的姓名

select Sname from Student where Sdept='IS'

5）查询所有年龄在20岁以下的学生姓名及其年龄 select Sname ,Sage from Student where Sage<20

6）查询年龄在20到18间的学生的姓名,系别及年龄

select Sname,Sdept,Sage from Student where Sage between 18 and 20

7）查询年龄不在23到19间的学生的姓名,系别及年龄

select Sname,Sdept,Sage from Student where Sage not between 19 and 23

8）查询不是信息系(IS)和计算机系(CS)学生的姓名和性别

select Sname ,Ssex from Student where Sdept not in('is','cs')

9）查询所有姓刘的学生的姓名,学号和性别

select Sname,Sno,Ssex from Student

where Sname like '刘%'

实验二 数据的查询、更新 徐龙琴设计制作

10）查询姓“张”且名为一个汉字的学生的姓名

select Sname from Student

where Sname like '张_'

11）查询名字中第2个字为”敏”字的学生姓名和学号

select Sname ,Sno from Student

Where Sname like '_敏%'

12）查询所有不姓刘的学生姓名

select Sname from Student

where Sname not like '刘%'

13）查询全体学生情况,结果按所在系升序排列,同一系中的学生按年龄降序 select * from Student

order by Sdept asc,Sage desc

14）查询学生表中所有学生信息，要求只显示前10％行数据

Select top 10 percent * from Student

15）按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前2

行数据,若第3行后的数据也等于70分也要显示。

Select top 2 with ties Sno from SC

where Grade>=70 order by Grade desc 16)查询每个学生的学号、课程号及分数，同时统计每个学生的总分

select Sno,Cno,Grade from SC compute SUM(Grade) by Sno

17)查询每个学生的各科分数、最高分、最低分、总分、平均分

select * from SC order by Sno,Cno,Grade

compute max(Grade),min (Grade),sum(Grade),avg(Grade)by Sno

5、用T-SQL语句完成下面的查询 1)查询学生的总人数

实验二 数据的查询、更新 徐龙琴设计制作

select count(*) from student

2)查询选修了课程的学生人数

select count(distinct Sno) from SC

3)计算选2号课程的学生平均成绩

select AVG(Grade) from SC where Cno='2'

4)查询选修2号课程的学生最高分数

select MAX(Grade) from SC where Cno='2'

5)求各个课程号及相应的选课人数

select Cno, COUNT(Sno) from SC group by Cno

6)查询选修了2门以上的课程的学生学号 select Sno from SC group by Sno having count(*)>2

7)查询每个学生及其选修课程的情况 select Student.Sno,Cname from SC,Course,Student where Student.Sno=SC.Sno and

Course.Cno=SC.Cno

8)查询每一门课的间接先修课(即先修课的先修课)

select first.Cno, second.Cpno from Course first,Course second where first.Cno=second.Cno

9)查询选修2号课程且成绩在90分以上（包括90分）的所有学生。

select Student.Sno,Sname from Student,SC

where Student.Sno=SC.Sno and SC.Grade>=90 and SC.Cno='2'

实验二 数据的查询、更新 徐龙琴设计制作

6. 用T-SQL语句完成下面的查询

1)查询与“刘晨”在同一个系学习的学生

select Sno,Sname,Sdept from Student where Sdept IN

(select Sdept from Student

where Sname='刘晨')

2)查询选修了课程名为“数学”的学生学号和姓名

select Student.Sno,Student.Sname from Course,Student,SC

where Student.Sno=SC.Sno and Course.Cno=SC.Cno

and Course.Cname='数学'

3)查询其它系中比信息系中某一学生年龄小的学生姓名和年龄 select Sname,Sage from Student where Sage

select Sage from Student where Sdept='IS')

and Sdept<>'IS'

4)查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄

select Sname,Sage from Student where Sage

select Sage from Student where Sdept='CS')

and Sdept<>'CS'

5)查询所有选修了2号课程的学生姓名

select Sname from Student where EXISTS

(select * from SC

where Sno=Student.Sno and Cno='2')

6)查询没有选修3号课程的学生姓名

实验二 数据的查询、更新 徐龙琴设计制作

select Sname from Student where NOT EXISTS (select * from SC

where Sno=Student.Sno and Cno='3')

7、用T-SQL语句完成下面的复杂查询

1)至少选修刘老师所授课程中一门课程的女学生姓名

select Sname

from Student,Course,SC

where Student.Sno=SC.Sno and Course.Cno=SC.Cno

and Course.Teacher like'刘%'and Student.Ssex='女'

2)检索王同学不学的课程的课程号

select Cno from Course where Cno not in

(Select SC.Cno

from Student,SC,Course where Student.Sno=SC.Sno and Course.Cno=SC.Cno

and Student.Sname like '王%' )

select Cno from SC

where Cno not in(select Cno

from Student，SC

where Student.Sno=SC.Sno and Sname like'王%')

3)检索全部学生都选修的课程的课程号与课程名。

select Cno,Cname from Course where not exists (select *

from Student where not exists (select * from SC

where SC.Sno=Student.Sno and SC.Cno=Course.Cno)

)

4)检索选修课程包含刘老师所授课的学生学号。

实验二 数据的查询、更新 徐龙琴设计制作

select Sno from SC x

where not exists (

select * from Course

where Teacher like '刘%' and not exists ( select *

from SC y

where y.Cno=Course.Cno and y.Sno=x.Sno)

)

5)求选修课程号为2的学生的平均年龄。

Select AVG(Sage) from Student,SC

where Student.Sno=SC.Sno and SC.Cno='2'

6)求刘老师所授课程的每门课程的学生平均成绩。

Select Teacher ,Cname ,AVG(Grade) from Student,SC,Course

where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Teacher like '刘%'

group by Teacher ,Course.Cno,Cname

7)检索学号比刘同学大，而年龄比他小的学生姓名。 select Sname from Student where Sno>(

select Sno from Student where Sname='刘同') and Sage<( select Sage from Student where Sname='刘同')

8)求年龄大于女同学平均年龄的男同学姓名和年龄。

select Sname,Sage from Student where Ssex ='男'and Sage>( select avg(Sage)

from Student where Ssex='女' )

实验二 数据的查询、更新 徐龙琴设计制作

9)求年龄大于所有女同学年龄的男学生姓名和年龄。

select Sname,Sage from Student

where Ssex='男' and Sage>all(

select Sage

from Student where Ssex='女')

10)检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S（SNO,SNAME,SEX）。

select Sno SNO ,Sname SNAME,Ssex SEX into S from Student where Sno in (

select Sno from SC

where Grade>=80)

11)把选课数学课不及格的成绩全改为空值。

update SC set Grade ='' where Sno=(

select Sno from SC

where Grade<60 )

and Cno=(

select Cno from Course where Cname='数学')

12)把王同学的选课信息全部删去。

delete from SC where Sno=( select Sno from Student

where Sname like '王%')

13)把低于总平均成绩的男同学成绩提高5％。

update SC

set Grade=Grade*1.05 where Cno in (

select SC.Cno from SC,Student

实验二 数据的查询、更新 徐龙琴设计制作

where Ssex='男' and Grade<(

select AVG (Grade) from SC)

)

14)检索没有选修‘1’课程的学生学号和姓名

select Sno,Sname from Student where Sno!=(

select Student.Sno from Student,SC,Course

where Course.Cno=1 and Student.Sno=SC.Sno and Course.Cno=SC.Cno)

15)检索至少有一门课程超过学生‘95001’一门成绩的学生学号

select distinct SC.Sno from Student,SC

where SC.Sno!=95001 and SC.Sno=Student.Sno and Grade> any(

select Grade from SC

where Sno=95001)

16)向学生选修课程表中插入元组“学生95003选修课程1”。

insert into SC(Sno,Cno)values ('95003','1')

17)求出女同学的每一年龄组（超过10人）有多少人？要求查询结果按人数升序排列，人数

相同的按年龄降序排列。

select Sage, COUNT(Sno) from Student where Ssex='女'

group by Sage having COUNT(*)>10 order by COUNT(Sno),Sage desc

18)定义视图S_AVG，其中包括学生学号以及每个学生选修课程的门数（要求成绩非空）和

平均成绩。

create view S_AVG(学号,选课门数,平均成绩) as

select Sno,COUNT(Cno),AVG(Grade) from SC

where grade is not null group by Sno

实验二 数据的查询、更新 徐龙琴设计制作

四、思考题

1、在创建基本表时是否可以缺省主码？

答：可以，在定义基本表时可以定义主码也可以先不定义主码。

2、简述GROUP BY 、COMPUTE 、COMPUTE BY的区别？ 答：

①·GROUP BY 生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。

②·COMPUTE 生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子聚合，或 SELECT 语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在 COMPUTE 子句中指定，而不是在选择列表中。

③·GROUP BY子句返回的结果集中只有合计数据，而没有原始的详细记录。COMPTE生成合计作为附加的汇总列出现在结果集的最后。当与BY一起使用时，COMPUTE 子句在结果集内生成控制中断和分类汇总。

本文来源：https://www.bwwdw.com/article/4qmw.html