数据库原理与应用教程

SQL-SERVER Markdown

• author : smartZhou
• speciality : Computer Science and Technology
• My GitHub : https://github.com/smartZdw
• My blogs : https://www.cnblogs.com/smartZhou

习题六 表的操作

6.1 创建Inventory数据库

创建inventory(仓库库存)的数据库,并设置数据库的主文件名为inventory_data,初始大小为10MB,日志文件中为inventory_log,初始大小为2MB.所有文件都放在目录C:\Code\DATA.中,要求:inventroy_data最大为无限大,增长速度为20%,日志文件初始大小为2MB,最大为5MB, 增长速度为1MB。

CREATE DATABASE inventory
ON PRIMARY                      --primary data file 
(
    NAME = inventory_data,      --The logical name of the primary file 
    FILENAME = 'C:\Code\DATA\inventory_data.mdf',   --path 
    SIZE = 10 MB,                                   --initial size
    MAXSIZE = UNLIMITED,                            --max size is unlimited 
    FILEGROWTH = 20%                                --file growth rate 
)
LOG ON                          --log file 
(                                       
    NAME = 'inventory_log',     --The logical name of the log file 
    FILENAME = 'C:\Code\DATA\inventory_log.ldf',    --path 
    SIZE = 2 MB,                                    --initial size
    MAXSIZE = 5 MB,                                 --max size if unlimited 
    FILEGROWTH = 1 MB                               --file growth rate 
)

6.2 创建goods商品表

--创建goods商品表
CREATE TABLE goods 
(
    gno char(6)           NOT NULL PRIMARY KEY,     --商品编号
    gname nvarchar(10)    NOT NULL,                 --商品名称 
    price float           NOT NULL,                 --单价
    producer nvarchar(30) NOT NULL                  --生产商
)

6.3 创建store仓库表

CREATE TABLE store 
(
    stno char(3)          NOT NULL PRIMARY KEY,                         --仓库编号  
    address nvarchar(30)  NOT NULL,                                     --仓库地址
    telephone varchar(11) CHECK(telephone >= '0' and telephone <= '9'), --电话
    capacity smallint                                                   --容量 
)

6.4 创建invent库存情况表

CREATE  TABLE invent
(
    stno char(3) CONSTRAINT fk_insto FOREIGN KEY REFERENCES store(stno),--仓库编号   外键
    gno  char(6) CONSTRAINT fk_ingno FOREIGN KEY REFERENCES goods(gno), --商品编号   外键
    number int,                                                         --库存数量 
    constraint pk_sg PRIMARY KEY(stno,gno)
)

6.5 创建manager管理员表

CREATE TABLE manager
(
    mno char(3) PRIMARY KEY,                                                --管理员编号 
    mname nvarchar(10) NOT NULL,                                            --管理员姓名 
    sex nchar(1) CHECK(sex = '男' or sex = '女'),                           --性别 
    birthday date CHECK(birthday >= '1957-1-1' and birthday <= '2010-1-1'), --出生年月
    stno char(3) CONSTRAINT fk_mstno FOREIGN KEY REFERENCES store(stno)     --仓库编号   外键
)

6.6 给inventory库的各个表插入数据

6.6.1 给goods商品表插入数据

insert into inventory.dbo.goods(gno,gname,price,producer) 
values('bx-179','冰箱',3200,'青岛海尔')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('bx-340','冰箱',2568,'北京雪花')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('ds-001','电视',1580,'四川长虹')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('ds-018','电视',2980,'青岛海尔')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('ds-580','电视',6899,'南京熊猫')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('kt-060','空调',3560,'青岛海尔')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('kt-330','空调',4820,'青岛海信')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('xyj-01','洗衣机',980,'无锡小天鹅')
insert into inventory.dbo.goods(gno,gname,price,producer)
values('xyj-30','洗衣机',858,'南京熊猫')

6.6.2 给store仓库表插入数据

insert into inventory.dbo.store(stno,address,telephone,capacity)
values('001','1号楼105','89000001',67)
insert into inventory.dbo.store(stno,address,telephone,capacity)
values('002','1号楼106','89000002',78)
insert into inventory.dbo.store(stno,address,telephone,capacity)
values('003','2号楼101','89000003',56)
insert into inventory.dbo.store(stno,address,telephone,capacity)
values('004','2号楼102','89000004',77)
insert into inventory.dbo.store(stno,address,telephone,capacity)
values('005','3号楼104','89000005',80)
insert into inventory.dbo.store(stno,address,telephone,capacity)
values('006','3号楼108','89000006',65)

6.6.3 给invent库存情况表插入数据

insert into inventory.dbo.invent(stno,gno,number)
values('004','bx-179',5)
insert into inventory.dbo.invent(stno,gno,number)
values('002','bx-179',12)
insert into inventory.dbo.invent(stno,gno,number)
values('003','bx-340',10)
insert into inventory.dbo.invent(stno,gno,number)
values('001','ds-001',20)
insert into inventory.dbo.invent(stno,gno,number)
values('003','ds-018',8)
insert into inventory.dbo.invent(stno,gno,number)
values('001','ds-018',16)
insert into inventory.dbo.invent(stno,gno,number)
values('002','ds-580',15)
insert into inventory.dbo.invent(stno,gno,number)
values('004','kt-060',9)
insert into inventory.dbo.invent(stno,gno,number)
values('001','kt-060',13)
insert into inventory.dbo.invent(stno,gno,number)
values('004','xyj-01',10)
insert into inventory.dbo.invent(stno,gno,number)
values('003','xyj-30',21)

6.6.4 给manager管理员表插入数据

insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('101','张力','男','1989-2-3 0:00:00','001')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('102','李明','男','1979-7-23 0:00:00','001')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('103','王辉','男','1978-9-18 0:00:00','002')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('104','张凤玉','女','1978-9-12 0:00:00','002')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('105','刘晓宏','男','1990-5-25 0:00:00','003')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('106','郑文杰','男','1972-9-6 0:00:00','003')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('107','明宇','男','1989-4-2 0:00:00','004')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('108','詹虎新','男','1989-7-29 0:00:00','004')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('109','李品慧','女','1973-9-28 0:00:00','005')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('110','刘利华','男','1980-5-3 0:00:00','005')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('111','王文宇','男','1980-5-23 0:00:00','006')
insert into inventory.dbo.manager(mno,mname,sex,birthday,stno)
values('101','王玮','女','1978-8-13 0:00:00','006')

6.7 查询结果

select * from goods
select * from store 
select * from invent 
select * from manager  

6.8 查询效果图

习题七 数据库查询

• 1.针对teaching数据库中的3个表，试用T-SQL的查询语句实现下列查询!

7.1 创建teaching教学库

CREATE DATABASE teaching 
ON PRIMARY
(
    NAME = teaching_data,
    FILENAME = 'C:\Code\DATA\Teaching\teaching_data.mdf', --请先确保已存在此路径 
    SIZE = 5MB, 
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%    
)
LOG ON 
(
    NAME = teaching_log,
    FILENAME = 'C:\Code\DATA\Teaching\teaching_data.ldf',
    SIZE = 8MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
)

7.2 在teaching教学库中创建学生表

CREATE TABLE student 
(
    sno       char(7)      PRIMARY KEY, --学号 
    sname     nvarchar(10) NOT NULL,    --姓名 
    ssex      nchar(1)     NOT NULL,    --性别 
    sage      tinyint,                  --年龄 
    en_time   date,                     --入学年份
    specialty nvarchar(20),             --专业 
    grade     nvarchar(3)               --年级 
)

7.3 在teaching教学库中创建课程表

CREATE TABLE course
(
    cno       char(4)      PRIMARY KEY,
    cname     nvarchar(20) NOT NULL,
    classhour tinyint,
    credit    tinyint
)

7.4 在teaching教学库中创建选课表

CREATE TABLE sc 
(
    sno char(7),
    cno char(4),
    score int,
    CONSTRAINT pk_js PRIMARY KEY(sno,cno)   --sno和cno的组合为主键
)

7.5 为student学生表插入数据

insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1302001','张明明','男',20,'2013-9-6','Computer Science','13级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1302005','郑丽','女',21,'2013-9-6','Computer Science','13级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1401001','朱一虹','女',19,'2014-9-9','Computer Science','14级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1302003','沈艳','女',20,'2014-9-5','Electronic','14级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1401003','赵丽红','女',20,'2014-9-5','Computer Science','14级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1404001','李宏伟','男',19,'2014-9-5','Communication','14级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1404006','刘景鹏','男',19,'2014-9-5','Communication','14级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1501001','张玲','女',19,'2015-9-1','Electronic','15级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1501008','张玲','女',18,'2015-9-1','Electronic','15级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1502001','张强','男',18,'2015-9-1','Computer Science','15级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1502003','张强','男',19,'2015-9-6','Electronic','15级')
insert into teaching.dbo.student(sno,sname,ssex,sage,en_time,specialty,grade) 
values('1502005','刘梅','女',19,'2015-9-1','Computer Science','15级')

7.6 为course课程表插入数据

insert into teaching.dbo.course(cno,cname,classhour,credit)
values('C001','C++语言',3,4)
insert into teaching.dbo.course(cno,cname,classhour,credit)
values('C004','操作系统',3,3)
insert into teaching.dbo.course(cno,cname,classhour,credit)
values('E002','电子技术',5,5)
insert into teaching.dbo.course(cno,cname,classhour,credit)
values('R005','软件工程',3,3)
insert into teaching.dbo.course(cno,cname,classhour,credit)
values('X003','信号原理',4,3)

7.7 为teaching教学库中的sc选课表插入数据

insert into sc(sno,cno,score)
values('1302001','C001',96)
insert into sc(sno,cno,score)
values('1302005','C001',78)
insert into sc(sno,cno,score)
values('1401003','C001',72)
insert into sc(sno,cno,score)
values('1402001','C001',85)
insert into sc(sno,cno,score)
values('1302001','C004',76)
insert into sc(sno,cno,score)
values('1302005','C004',92)
insert into sc(sno,cno,score)
values('1401003','C004',73)
insert into sc(sno,cno,score)
values('1404001','E002',76)
insert into sc(sno,cno,score)
values('1302001','X003',85)
insert into sc(sno,cno,score)
values('1501001','X003',NULL)
insert into sc(sno,cno,score)
values('1501008','X003',NULL)

(1)查询学生们有哪些专业，只显示专业列，过滤掉重复行。

select distinct specialty from student

(2)统计学生选修的课程门数

select count(distinct cno) from course 

(3)求选修C004课程的学生的平均年龄

select avg(sage) from student,sc 
where student.sno=sc.sno and cno='C004'

(4)求学分为3的每门课程的学生平均成绩

select course.cno,avg(score) from course,sc
where course.cno=sc.cno and credit=3
group by course.cno

(5)统计每门课程的学生选修人数，超过两人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列.

select cno,count(*) as 选修人数 from sc
group by cno
having count(*)>2
order by count(*) desc,cno

(6)查询所有姓"刘"的学生的姓名和年龄

select sname,sage from student
where sname like '刘%'

(7)在sc表中检索"成绩"为空值的学生的学号和课程号

select sno,cno from sc
where score is NULL

(8)查询没有学生选修的课的课程号和课程名

select cno,cname from course 
where cno NOT IN (select cno from sc)

(9)求年龄大于男同学平均年龄的女学生的姓名和年龄

select sname,sage from student 
where ssex='女' and sage > 
(select AVG(sage) from student where ssex = '男')

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

select sname,sage from student
where ssex = '女' and 
sage > (select MAX(sage) from student where ssex = '男')
或者
select sname,sage from student 
where ssex = '女' and 
sage > all(select sage from student where ssex = '男')

(11)查询所有与"刘宏伟"同年级，同专业，但比"沈艳"年龄大的学生的姓名，年龄和性别。

select sname,sage,ssex from student
where grade=(select grade from student where sname = '刘宏伟') and specialty = (select specialty from student where sname = '刘宏伟') 
and sage > (select sage from student where sname = '沈艳')

(12)查询选修C001号课程的学生中成绩最高的学生的学号

select sno from sc 
where score = (select MAX(score) from sc where cno = 'C001')

(13)查询学生的姓名及其所选修课程的课程号和成绩

select cno,score from student,sc 
where student.sno = sc.sno
或者
select cno,score 
from student inner join sc 
where student.sno = sc.sno 

(14)查询选修两门以上课程的学生的平均成绩(不及格的课程不参与统计)，并要求按平均成绩的降序排列出来

select sno,AVG(score) as 平均成绩 from sc 
where score > 60
group by sno 
having count(*) > 2
order by AVG(score) desc

(15)求每个学生的平均成绩，只取前5名

select Top 5 sno,AVG(score) as 平均成绩 from sc 
group by sno 
order by AVG(score) desc

(16)查询每个学生的总学分

select sno,SUM(credit) from sc,course 
where sc.cno = course.cno
group by sno 

(17)查询每门课成绩最低的学生的学号和课程号

select sno,cno from sc sc1 
where score = (select MIN(score) from sc sc2 where sc1.cno = sc2.cno)

• 2.使用T-SQL语句对inventory数据库完成下列查询
  (1)查询青岛海尔生产的商品信息

select * from goods 
where producer='青岛海尔

(2)查询001号仓库存储的商品的编号和数量

select gno,number from invent 
where stno = '001'

(3)查询所有商品的种类名称

select distinct gname from goods 

(4)查询单价在2000~3000之间的商品信息

select * from goods 
where price between 2000 and 3000

(5)查询 goods商品表中所有商品的信息，其中单价以八折显示

select gno 商品编号,gname 商品名称,price*0.8 单价八折,producer 生产商
from goods 

(6)查询青岛海尔和青岛海信生产的商品的信息

select * from goods 
where producer = '青岛海尔' OR producer = '青岛海信'

(7)查询李明管理的仓库存储的商品的信息

select * from goods 
where gno IN 
(select gno from invent where stno IN
(select stno from manager where mname = '李明')
)

(8)查询2号楼101仓库的管理员的姓名和年龄

select mname,year(getdate())-year(birthday) as 年龄 from manager  
where stno IN (select stno from store where address ='2号楼101仓库')

(9)查询不是青岛生产的商品的信息

select * from goods 
where producer NOT LIKE '青岛%'

(10)查询库存总量最少的仓库的编号

select TOP 1 stno from invent 
group by stno 
order by SUM(number) ASC

(11) 查询各生产厂家的商品库存总量，并存入库存总量表

select producer,SUM(number) as 库存总量 
INTO 库存总量 from goods,invent 
where goods.gno = invent.gno 
group by producer 

(12) 将张力管理的仓库的电话改为89000008

UPDATE store SET telephone ='89000008' 
where stno = (select stno from manager where mname = '张力')

(13)删除四川长虹的产品的库存信息

    DELETE invent 
    where gno IN (select gno from goods where producer = '四川长虹')

习题八 视图与索引

• 1.引入视图的主要目的是什么?

答:数据库的基本表是按照设计人员的观点设计的，并不一定符合用户的需求。SQL SERVER可以根据用户需求重新定义表的数据结构，这种数据结构就是视图。视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，其结构和数据是建立在对表的查询基础上的。

• 2.在删除视图时所对应的数据表会删除吗？

答:不会

• 3.简述视图的优点。

答:使用视图有很多优点，主要表现在:

1.为用户集中数据，简化用户的数据查询和处理。
2.保证数据的逻辑独立性。
3.重新定制数据，使得数据便于共享;合并分割数据，有利于数据输出到应用程序中。
4.数据保密

• 4.可更新视图必须满足哪些条件？

本题略.

• 5.创建索引的必要性和作用是什么？

答:数据库的索引就类似书籍的目录，如果想快速查找而不是逐页查找指定的内容，可以通过目录中的章节的页号找到其对应的内容。类似地，索引通过记录表中的关键值指向表中的记录，这样数据库引擎就不用扫描整个表而定位到相关的记录。相反，如果没有索引，则会导致SQL SERVER搜索表中所有记录，以获取匹配结果。

• 6.创建索引的优点。

索引的优点包括：

1.大大加快数据的检索速度，这是创建索引的最主要的原因。
2.创建唯一性索引，保证表中每一行数据的唯一性。
3.加速表和表之间的连接。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.查询优化器可以提高系统的性能，但它是依靠索引起作用的。

• 7.聚集索引和非聚集索引有何异同？

答:聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。非聚集索引不会对表和视图进行排序。如果表中不存在聚集索引，则表是未排序的。

• 8.在sql-server Management Studio中创建一个名为 "invent_info"的库存信息视图，要求包含库存数据库中四个表的所有列。然后分析此视图是为可更新视图。

Create View invent_info
AS
    select a.gno,a.gname,g.price,g.producer,
           b.stno,b.address,b.telephone,b.capacity,
           c.stno,c.gno,c.number,
           d.mno,d.mname,d.sex,d.birthday,d.stno
    from goods a,store b,invent c,manager d
    where a.gno=c.gno and b.stno=c.stno and b.stno=d.stno 
GO

• 9.在SQL-SERVER Managerment studio中创建一个包含计算机专业学生基本信息的视图computer_stu,然后分析此视图是否为可更新视图，说明理由。

Create View computer_stu
AS
    select * from student 
    where specialty='computer science'
GO

• 10 .使用T-SQL语句创建一个每个学生的平均成绩的视图avgscore。要求包含学生的学号和姓名，然后分析此视图是否为可更新视图，说明理由

create View avgscore 
as
    select student.sno,sname,AVG(score) from student,sc
    where student.sno = sc.sno 
    group by student.sno,sname  
GO
ii)由于平均成绩固定，为不可更新视图!

• 11.使用T-SQL语句创建一个每个年级，每个专业各科平均成绩的视图。

Create View AvgScorePlus
As
    Select grade，specialty,cno，avg(score) AS 平均成绩 from student,sc 
    where student.sno=sc.sno 
    group by grade,specialty,cno
GO    

• 12.在sql-server managerment studio中按照sc表的成绩升序创建一个普通索引（非唯一，非聚集），并举例说明什么查询语句会利用索引加快查询速度。

Create index index_score 
ON sc(score)
下面的T-SQL语句在执行的时系统就可以利用此索引来加快查询速度:
i)查询分数大于85分的学生的学号和姓名
select sno,sname,score from student,sc 
where student.sno = sc.sno AND score > 85 
ii)查询40~80分之间的分数最高的三位同学
select TOP 3 * from sc where score between 40 and 80
order by score DESC

• 13.使用T-SQL语句按照goods表的单价列降序创建一个普通索引，并举例说明什么查询语句会利用此索引加快查询

Create index price_index    
ON goods(price DESC)
下面的T-SQl语句在执行的时系统就可以利用此索引来加快查询速度:
i)查询单价在2000~4000之间的所有商品信息
select * from goods where price between 2000 and 4000
ii)删除单价小于1000的商品
delete from goods where price < 1000

• 14.使用T-SQL语句按照manager表的出生年月列升序创建一个普通索引，并举例说明什么查询语句会利用此索引加快查询速度。

Create index Date_index 
ON manager(birthday ASC)
下面的T-SQL语句在执行时系统就可以利用此索引来加快查询速度:
i)查询出生日期大于1978-11-1的管理员的所有信息
select * from manager where birthday > '1978-11-1'
ii)删除出生日期为 1978-9-19 0:00:00的管理员的信息
delect from manager where birthday='1978-9-18 0:00:00'

习题九 T-SQL编程

• 3.什么是局部变量？什么是全局变量？如何表示它们？

答：全局变量由系统提供且预先声明，通过在名称前加两个@符号区别于局部变量。用户只能使用全局变量，不能对它们进行修改。全局变量的作用范围是整个sql-server系统，任何程序都可以随时调用它们。

• 7.给出以下T-SQL语句的运行结果。

DECLARE @d SMALLDATETIME 
SET @d='2016-1-26'
SELECT @d+10,@d-10

答：2016-02-05,2016-01-16

• 6.使用T-SQL语句计算下列表达式并给出运算结果。
  (1)9-3*5/2+6%4　　　　　(2)5&2|4　　　　　(3)'你们'+'好'　　　　　(4) ~10
  　
  答:

(1)1
(2)1
(3)你们好
(4) 10的二进制为:0000 1010
        按位取反:1111 0101 此为补码
转换成原码要取反:1000 1010
           再加1:1000 1011 
           即-11
知识点:原码转补码:符号位不变，取反+1
补码转原码:补码的补码就是原码！给补码再求补码就可以了

• 8.什么是批处理，使用批处理有何限制？批处理的结束符是什么？

答:批处理是包含一个或多个T-SQL语句的集合，从应用程序一次地发送到SQL Server 2008进行执行，因此可以节省系统开销。SQL Server将此批处理语句编译为一个可执行单元，称为执行计划，批处理的结束符为GO。

• 9.注释有几类？它们分别是什么？

答:在T-SQL中可使用两类注释符。

(1)ASNI标准的注释符--用于单行注释。
(2)/*......*/,可以在程序中多行文字标示为注释。

• 10.针对teaching库，使用流程控制语句查询学号为132001的学生的各科成绩，如果没有这个学生的成绩，就显示“此学生无成绩”，如果根本查询不到此学生的存在，就显示“没有此学生”

IF EXISTS(select * from student where stno='132001')
    IF EXISTS(select * from sc where stno='132001')
        select cno,score from sc 
        where stno='132001'
    ELSE 
        PRINT "此学生无成绩"
ELSE 
    PRINT "没有此学生"

• 11.针对 teaching库，用函数实现求某个学院选修某门课的学生人数，然后调用此函数。

create function select_course(@p char(10),@cn char(4)) returns float 
AS 
    BEGIN
        DECLARE @cout float 
        select @cou=(select count(*) from student,sc where student.sno=sc.sno and cno=@cn and specialty=@p)
        RETURN @cou
    END
GO
查询计算机科学专业(Computer science)，选修课程号(cno)为C001的学生人数.
select dbo.select_course('Computer science','C001')

• 12.针对teaching库，用函数实现查询某个学院所有学生所选的每门课的平均课程，然后调用此函数。

Create Function(@p char(10)) returns float 
AS 
    begin 
        declare @aver float    
        select @aver=(select cno,avg(score) from  student,sc)
        where student.sno=sc.sno and specialty=@p
        group by cno)
    return @aver
    END
GO

• 13.针对inventory库中的goods表，查询商品的价格等级，商品号，商品名和价格等级(单价1000元以内为"低价商品",1000~3000元为"中等价位商品",3000元以上为"高价商品")

USE INVENTORY 
GO
select gno,gname,
CASE 
    when 单价<1000 then '低价商品'
    when 单价<3000 then '中等价位商品'
    when 单价>=3000 then '高价商品'
END AS 价格等级 from goods

• 14.简述游标的概念及类型

答:游标是处理数据的一种方法，它允许应用程序对查询语句SELECT返回的结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作。为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力，我们可以把游标当作一次指针，它可以指定结果集中的任何位置，然后允许用户对指定位置的数据进行处理。　
　　SQL Server支持三种类型的游标:T-SQL游标，API服务器游标和客户游标。　　
　　由于API游标和T-SQL游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。服务器游标包含以下四种:静态游标，动态游标，只进游标，键集驱动游标。  

• 15.使用T-SQL扩展方式声明一个游标，查询student表中所有男生的信息，并读取数据。要求读取最后一条记录；读取第1条记录；读取第5条记录；读取当前记录指针位置后的第3条记录。

    --定义Man_Cursor游标
    Declare Man_Cursor Cursor 
    static for 
    select * from student where ssex='男'
    --打开游标
    Open Man_Cursor 
    --提取游标
    Fetch last from Man_Cursor 
    Fetch absolute 1 from Man_Cursor 
    Fetch absolute 5 from Man_Cursor
    Fetch relative 3 from Man_Cursor 
    --关闭游标
    Close Man_Cursor 
    --释放游标
    Deallocate Man_Cursor 

习题十 存储过程和触发器

• 1.简述存储过程和触发器的优点。

答:存储过程最主要的特点是当写完一个存储过程后即被翻译成可执行码存储在系统表内，当作是数据库的对象之一，一般用户只要执行存储过程，并且提供存储过程所需的参数就可以得到所要的结果而不必要再去编辑T-SQL命令。
    由于在触发器中可以包含复杂的处理逻辑，因此，应该将触发器用来保持低级的数据的完整性，而不是返回大量的查询结果。
    使用触发器主要可以实现以下操作:

(1)强制比CHECK 更复杂的数据的完整性
(2)使用自定义的错误提示信息
(3)实现数据库中多表的级联修改
(4)比较数据库修改后数据的状态
(5)调用更多的存储过程
(6)维护非规范化数据

• 2.简述SQL Server2008中存储过程和触发器的分类。

答:

    (1)存储过程分为系统存储过程，本地存储过程，临时存储过程，远程存储过程和扩展存储过程.
    (2)触发器:DML触发器是当数据库服务器中发生数据操作语言(DML)事件时会自动执行的存储过程。DDL触发器是在响应数据定义语言(DDL)语句时触发，一般用于数据库中执行管理任务。

• 3.创建存储过程，从课程表中返回指定的课程的信息。该存储过程对传递的参数进行匹配，如果没有提供参数，则返回所有课程的信息。

CREATE PROC Course_Info @name varchar(20)='%'
AS
    select * from course 
    WHERE cname LIKE @name 
GO

• 4.创建两个带参数的存储过程:插入某个学生选修的某门课的信息;删除某个学生选修的某门课的信息。

CREATE PROC Insert_Delete @addCno char(4),@delCno char(4)
AS
    UPDATE sc SET cno=@addCno
    DELETE sc SET 
GO

• 5.创建存储过程，计算指定学生(姓名)的总成绩，在存储过程中使用一个输入参数（姓名）和一个输出参数（总成绩）。

USE teaching 
GO
CREATE PROC SUM_score @name nvarchar(10),@sum_S int  OUTPUT
AS
    select @sum_S=SUM(score) from student,sc 
    where student.sno=sc.sno and sname=@name 
GO

• 6.在inventory数据库创建存储过程，若某商品（如编号为ds-018的商品）进了一批货，则如果某仓库目前根本没有任何商品的库存，就存入此仓库；否则修改库存总量最少的那个仓库的库存，如果那个仓库目前没有这种商品，就直接向库存情况表添加一行，否则修改该商品的库存数量的值为原值加上进货量（用形参变量表示进货量）

CREATE PROC @spno char(6),@number int  
AS 
    DECLARE @cno char(3)
    IF exists(select * from invent where stno NOT INT 
        (select stno from invent))
    BEGIN 
        select Top 1 @cno=stno from invent where stno NOT IN
        (select stno from invent)
        Insert invent values(@cno,@spno,@number)
    END 
    ELSE
        BEGIN
            select Top 1 @cno=stno from invent 
            group by stno 
            order by sum(number)
            IF exists(select *from invent where stno=@cno and gno=@spno)
            UPDATE invent set number=number+@number where stno=@cno and gno=@spno
    ELSE
        Insert invent values(@cno,@spno,@number)
    END
GO

• 7.为dept表创建一个实现级联删除的触发器，当执行删除时激活该触发器，同时删除gongcheng表中的相应记录(leader表)。

Create Trigger del_tr ON dept 
For delete 
AS
    delete from gongcheng where leader=(select leader from deleted)
GO

• 8.在teaching 数据库中创建一个学生党费表st_dues，属性为sno,sname,dues,含义为学号，姓名，党费。sno是主键，也是外键(参考student表的sno);创建一个触发器，保证只能在每年的6月和12月交党费，如果在其他时间录入则显示提示信息，然后用相关命令语句触发此触发器。

USE teaching 
GO 
Create Table st_dues 
(
    sno char(7) PRIMARY KEY foreign key references student(sno),
    sname char(6),
    dues int 
)
Create Trigger trig_dues    
ON st_dues for INSERT
AS  
--DATEPART(datepart,date_expression):以整数值的形式返回日期的指定部分,此部分由datepart来指定。
    if not(DATEPART(mm,getdate())='06' or DATEPART(mm,getdate())='12')
    BEGIN
    print'系统提示:只能在每年的6月和12月交党费'
    rollback --垃圾滚
END
GO

• 9.在score表上创建触发器，用于登记修改成绩列数据者及修改时间等信息.

use teaching 
go
Create Trigger Update_info 
ON score For UPDATE
AS
    /********/
GO

• 10.在inventory数据库的invent表上创建一个触发器，用于实现复杂的约束;在对Invent表进行插入和修改时按仓库的容量进行约束，即如果此仓库还有空间则可以增加库存，否则提示容量不足。然后用相关命令语句触发此触发器，例如在修改001号仓库的库存数量时触发了此触发器，如图所示 图片链接。
  

Create trigger zhou_trl ON invent 
FOR insert,update 
AS 
    Declare @s int,@s1 int,@NO char(3)
    select @S=SUM(invent.number) from invent,inserted
        where invent.stno=inserted.stno
    select @s1=capacity,@NO=inserted.stno from invent,inserted 
    where invent.stno=inserted.stno
    if @S>@S1
    BEGIN
        PRINT @NO+'号仓库的容量为'+CAST(@s1 as char(3))+',已满。'
        ROLLBACK
    END

持续更新中.........................

Copyright： ©smartZhou2018-11-11　　　本文由16计本小白提供