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

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

实验二 数据的查询、更新

一、实验目的

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)查询学生的总人数

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库实验二 - 数据的查询、更新在线全文阅读。