T-SQL语句操作(二)

1、实验目的
  熟练掌握T-SQL的统计功能与视图、关系图、触发器和存储过程的使用
2、实验内容与要求
（1）SELECT语句的数据统计功能
（2）视图的创建与应用
（3）数据库关系图的使用
（4）触发器的使用
（5）存储过程的使用
要求：给出完整的T-SQL语句与操作结果或SSMS的操作界面。
3、实验环境
Microsoft SQL Server (Express) + SQL Server Management Studio

SELECT语句的数据统计功能

SELECT数据统计的语句格式：

1
2
3
4
SELECT [<分组列表>,]{<统计表达式> [[AS] <别名>]}[,……n] FROM <数据源> 
	[WHERE <条件表达式>]
	[GROUP BY  <分组列表> [HAVING <分组筛选条件>]]
	[ORDER BY <排序列表>]

说明：
（1） <统计表达式>是由T-SQL统计函数组成，一般要定义一个别名。
（2） GROUP BY子句表示要以<分组列表>进行分组统计，若省略，则不分组，得到一个整个结果集的统计结果。
（3）HAVING短语与GROUP BY配合使用，它对分组统计的结果进行筛选， 语句中的<统计表达式>应出现在 <分组筛选条件>中。
（4）注意WHERE子句与HAVING短语的区别。WHERE决定参与统计 的记录，HAVING决定统计后的结果。

常用的统计函数
1）COUNT——计数
格式：COUNT(*| [DISTINCT]<列名>)

2）MAX——求最大值
格式：MAX(<列名>)

3）MIN——求最小值
格式：MIN(<列名>)

4）AVG——求平均值
格式：AVG(<数值型列名>)

5）SUM——求和
格式：SUM(<数值型列名>)

实现下列数据查询、统计功能:

1）统计计算机学院的教师人数

1
2
3
select COUNT(*) from tab_teacher 
where instno=(
select instno from tab_institute where instname='计算机学院')

2）统计网络工程专业每个学生的选课课程数

1
2
3
4
5
6
select sc.sno,s.sname,COUNT(*) as '课程数'
from tab_score sc
join tab_student s on sc.sno=s.sno
join tab_major m on s.majorno=m.majoyno
where m.majoyname='网络工程'
group by sc.sno,s.sname

3）统计“数据库”课程的最高分、最低分和平均成绩

1
2
3
4
5
select MAX(sc.score) as '最高分',
MIN(sc.score) as '最低分',
AVG(sc.score) as '平均分'
from tab_score sc where cno=(
select cno from tab_course where cname='数据库')

4）查询“数据库”课程高于平均分的学生学号和成绩

1
2
3
4
5
6
7
8
select sc.sno,sc.score
from tab_score sc
join tab_course c on sc.cno=c.cno
where c.cname='数据库'
and sc.score>(
select AVG(score) 
from tab_score
where cno=c.cno)

5）查询“数据库”课程成绩最高的学生学号和姓名（采用MAX函数和子查询）

1
2
3
4
5
6
7
8
select sc.sno,s.sname 
from tab_score sc
join tab_student s on sc.sno=s.sno
join tab_course c on sc.cno=c.cno
where c.cname='数据库'
and sc.score=(
select MAX(score) from tab_score
where cno=c.cno)

6）查询平均分高于75分的课程号

1
2
3
4
select sc.cno from tab_score sc
where 
(select AVG(score) from tab_score
where cno=sc.cno)>75

7）查询计算机学院平均分高于75分的学生学号

1
2
3
4
5
6
7
select s.sno from tab_student s
join tab_score sc on sc.sno=s.sno
join tab_major m on s.majorno=m.majoyno
join tab_institute i on m.instno = i.instno
where i.instname='计算机学院'
group by s.sno
having AVG(sc.score)>75

8）统计有不及格成绩的学生人数

1
2
3
4
select COUNT(DISTINCT s.sno) as '不及格人数'
from tab_student s
join tab_score sc on sc.sno=s.sno
where sc.score<60

9）查询选课人数少于10人的课程号

1
2
3
4
SELECT cno
FROM tab_score
GROUP BY cno
HAVING COUNT(*) < 10;

视图的创建与应用

利用SSMS创建视图

下面以创建与tab_score对应的成绩视图View_score为例，说明视图的创建过程。
（1）打开SSMS，打开“stuinfo”数据库文件夹，选择“视图”。
（2）单击右键，在快捷菜单中选择“新建视图…”。
（3）在“添加表”对话框中选择视图所需要的表，本例需要选择tab_student、tab_course、tab_teacher和tab_score这4个表。表的选择顺序没有特别要求，但一般先选择外部键所在的表（tab_score），在选择主键所在的表（另外3个表），然后关闭对话框。
（4）选择视图的列名，在表区的4个表中选择视图要映射的列（主键和外部键一般选外部键，即1:n联系的n端表中的列）。本例选择tab_score中的sno、cno、tno、score，在另外3个表中分别选sname、cname、tname。此时可在视图列区用鼠标拖动列名左边的小方块可以调整列在语句中的位置。
（5）点击“保存”或“关闭”按钮，在“选择名称”对话框中输入视图名“View_score”，单击“确定”，操作结束。

利用T-SQL创建视图

语句格式

1
CREATE VIEW <视图名> [(<视图列表>)] AS <子查询> [WITH CHECK OPTION]

说明：
1） WITH CHECK OPTION选项，表示在对视图进行UPDATE、INSERT和DELETE操作时要满足视图<子查询>中的WHERE条件。
2） <视图列表>用来指定视图中的列名，一般省略不用。视图中的列名由<子查询>的SELECT语句的查询列表指定，若其中出现来自多表的同名列或表达式可以定义列别名。

利用SSMS和T-SQL创建下列视图:
（1）创建视图View_score，包括学号、姓名、专业名称、学院名称、课程号、课程名、课程类型、开课学期，学分，教师号、教师名，职称，成绩等列内容。

1
2
3
4
5
6
7
8
create view view_score as
select sc.sno,s.sname,m.majoyname,i.instname,sc.cno,c.cname,c.ctype,c.term,c.credit,sc.tno,t.tname,t.title,sc.score
from tab_score as sc
join tab_student as s on s.sno = sc.sno
join tab_major as m on s.majorno = m.majoyno
join tab_institute as i on m.instno = i.instno 
join tab_course as c on c.cno = sc.cno
join tab_teacher as t on t.tno = sc.tno

（2）创建视图View_score_XY01和View_score_XY02 ，视图包含View_score的所有列，但前者只有“计算机学院”学生，后者只有“网络空间安全学院”的学生。

1
2
3
create view view_score_XY01 as 
select * from view_score as svc
where svc.instname='计算机学院'
1
2
3
create view view_score_XY02 as 
select * from view_score as svc
where svc.instname='网络空间安全学院'

（3）创建视图View_majoy，视图列包括专业号、专业名称、学院号、学院名称。

1
2
3
4
create view view_majoy as 
select m.majoyno,m.majoyname,i.instno,i.instname
from tab_major as m
join tab_institute as i on i.instno = m.instno

（4）创建视图View_teacher，视图列包括教师号、教师名称，性别、职称、学院号、学院名称。

1
2
3
4
create view view_teacher as 
select t.tno,t.tname,t.tsex,t.title,t.instno,i.instname
from tab_teacher as t
join tab_institute as i on t.instno = i.instno

（5）创建视图View_course，视图列包括课程号、课程名称，课程类型、开课学期、学分、专业号、专业名称、学院号、学院名称。

1
2
3
4
5
create view view_course as
select c.cno,c.cname,c.ctype,c.term,c.credit,c.majoyno,m.majoyname,i.instno,i.instname
from tab_course as c
join tab_major as m on c.majoyno = m.majoyno
join tab_institute as i on i.instno = m.instno

（6）创建视图View_student，视图列包括学号、姓名，性别、生日、班级、年级、专业号、专业名称、学院号、学院名称。

1
2
3
4
5
create view view_student as
select s.sno,s.sname,s.ssex,s.Birthday,s.class,s.grade,s.majorno,m.majoyname,i.instno,i.instname
from tab_student as s 
join tab_major as m on s.majorno = m.majoyno
join tab_institute as i on m.instno = i.instno

利用视图实现数据查询和统计:

1）查询全校的教师的工号、姓名、所在学院名

1
select tno,tname,instname from view_teacher

2）查询授课教师的工号、姓名、讲授课程名

1
select tno,tname,cname from view_score

3）查询 “数据结构”课程的学生的学号、姓名、课程名、成绩、任课教师名

1
2
3
select sno,sname,cname,score,tname
from view_score
where cname='数据库'

4）查询“计算机学院”所有学生的学号、姓名、课程名、成绩、任课教师名

1
2
3
select sno,sname,cname,score,tname 
from view_score
where instname='计算机学院'

5）查询所有授课的“教授”的工号、姓名和授课课程名

1
2
3
select tno,tname,cname 
from view_score
where title='教授'

6）统计“数据库”课程的最高分、最低分和平均成绩

1
2
3
4
select MAX(sc.score) as '最高分',
MIN(sc.score) as '最低分',
AVG(sc.score) as '平均分'
from view_score sc where cname='数据库'

7）查询“数据库”课程高于平均分的学生学号和成绩

1
2
3
4
select sno,score
from view_score 
where cname='数据库'
and score>(select AVG(score) from view_score where cname='数据库')

8）查询平均分最高的课程号、课程名、任课教师、平均成绩

1
2
3
4
select TOP 1 cno,cname,tname,AVG(score) as avg_score
from view_score 
group by cno,cname,tname
order by avg_score DESC

9）查询平均分高于75分的学号、姓名、平均成绩、专业名称、学院名称

1
2
3
4
5
select sno,sname,AVG(score) as avg_score,majoyname,instname
from view_score
group by sno,sname,majoyname,instname
having AVG(score) > 75 
order by avg_score

10）查询计算机学院平均分高于75分的学生学号、姓名、平均成绩、专业名称

1
2
3
4
5
select sno,sname,AVG(score) as avg_score,majoyname,instname
from view_score
group by sno,sname,majoyname,instname
having AVG(score) > 75 and instname='计算机学院'
order by avg_score

数据库关系图的关系

在SSMS中，通过创建关系图可以定义基本表的主键与外部键约束。
（1）创建数据库“stuinfo1”
（2）利用SELECT的INTO子句将“stuinfo”数据库中的基本复制到“stuinfo1”数据库。T-SQL语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
USE stuinfo1

SELECT * INTO tab_institute FROM stuinfo.dbo.tab_institute

SELECT * INTO tab_majoy FROM stuinfo.dbo.tab_majoy

SELECT * INTO tab_teacher FROM stuinfo.dbo.tab_teacher

SELECT * INTO tab_course FROM stuinfo.dbo.tab_course

SELECT * INTO tab_student FROM stuinfo.dbo.tab_student

SELECT * INTO tab_score FROM stuinfo.dbo.tab_score

（3）打开 “stuinfo1”数据库文件夹，选中“数据库关系图”，点击右键在快捷菜单中点击“新建数据库关系图”。
（4）在“添加表”对话框中将基本表添加到关系图编辑框中
（5）定义各个基本表的主键
（6）定义各基本表之间的外部键约束，同时关系图也创建了。
（7）关闭关系图编辑器，保存关系图。
（在实验报告中给出每个步骤的操作界面）

触发器的使用

  1. 操作准备
    （1）在tab_student表中添加一个学生记录，如:
    s020，王五，男，1999-8-9, …”。
    （2）在tab_score表中添加该学生的选课记录和成绩。
  2. 操作场景：学生“王五”退学处理。
  3. 具体操作要求如下：
    设计一个删除tab_student表中记录的触发器，如果删除的记录是“王五”，则将该学生记录复制tab_student1表中，同时在tab_score表中删除该学生选课记录，并将该学生的记录复制tab_score1表中；否则禁止删除记录。
  4. 创建触发器
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE TRIGGER trig_delete  ON tab_student
        FOR DELETE
        AS IF '王五' NOT IN (SELECT sname  FROM deleted)
            BEGIN 
    	       PRINT ' This record not is 王五, Can not delete! '
    	       ROLLBACK TRANSACTION
    		END
        ELSE
    	BEGIN 
    	     INSERT INTO tab_student1 SELECT * FROM deleted
    	     INSERT INTO tab_score1 SELECT tab_score.* FROM tab_score,deleted 
    				WHERE tab_score.sno=deleted.sno
    	     DELETE FROM tab_score WHERE sno IN (select sno from deleted)
    	END
  5. 运行触发器
    在SSMS的查询编辑器执行以下命令
    （1）DELETE FROM tab_student WHERE sname <> ‘王五’
    然后打开tab_student、tab_student1、tab_score和tab_score1观察表记录。

（2）DELETE FROM tab_student WHERE sname= ‘王五’
然后打开tab_student、tab_student1、tab_score和tab_score1观察表记录。

存储过程

存储过程是一个由SQL 语句和流程控制语句组成的程序，它存储在数据库内，可由应用程序通过一个调用执行。
存储过程允许用户声明变量、有条件执行以及其它的编程功能，可包含程序流以及对数据库的查询。它可以接受参数，也可返回参数，还可返回单个或多个结果集。
存储过程具有以下优点：
（1）可以在单个存储过程中执行一系列 SQL 语句。
（2）可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
（3）存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。

  1. 创建存储过程
    语句格式：
    1
    2
    3
    4
    5
    CREATE PROCEDURE <存储过程名>  
        [<参数名1> <数据类型1>] [ VARYING ] [ = <初值> ] [ OUTPUT ]] [ ,...n ] 
        [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }]   
        [ FOR REPLICATION ]
        AS  <SQL语句组>
  2. 执行存储过程
    语句格式：
    1
    EXECUTE <存储过程名> [<参数1> [OUTPUT]] [ ,...n ]
  3. 删除存储过程
    语句格式：
    1
    DROP PROCEDURE <存储过程名>  
  4. 举例
    例1 查询所有学生的选课和成绩信息。
    （1）存储过程创建
    1
    2
    3
    4
    5
    6
    7
    8
    USE stuinfo
    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'stu_info_all' AND type = 'P') 
    DROP PROCEDURE stu_info_all

    CREATE PROCEDURE stu_info_all 
    AS
    SELECT a.sno,sname,cname,score FROM tab_scores a INNER JOIN  	     	tab_student b ON a.sno = b.sno INNER JOIN tab_course c ON a.cno = c.cno

    （2）存储过程执行
    1
    EXECUTE stu_info_all

例2 根据姓名查询某个学生的学号、姓名、选修课程名和成绩信息。
（1）存储过程创建

1
2
3
4
5
6
7
8
9
10
11
USE stuinfo
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'stu_info_name' AND type = 'P') 
DROP PROCEDURE stu_info_name

CREATE PROCEDURE stu_info_name
@sname varchar(10) 
AS
SELECT a.sno,sname,cname,score FROM tab_scores a INNER JOIN 
	tab_student b ON a.sno = b.sno INNER JOIN tab_course c ON a.cno = c.cno
WHERE sname LIKE '%'+@sname+'%'

（2）存储过程执行

1
EXECUTE stu_info_name '王'

例3 根据姓名查询某个学生的平均成绩，并以参数形式返回。
（1）存储过程创建

1
2
3
4
5
6
7
8
9
10
11
USE stuinfo
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'stu_avg_score' AND type = 'P') 
DROP PROCEDURE stu_avg_score

CREATE PROCEDURE stu_avg_score
@sname varchar(10), 
@avg_score smallint OUTPUT
AS
SELECT @avg_score=AVG(score) FROM tab_score 
WHERE sno=(SELECT sno FROM tab_student WHERE sname=@sname)

（2）存储过程执行 

1
2
3
4
5
6
DECLARE @avg_score1 smallint
EXECUTE stu_avg_score '李四',@avg_score1 OUTPUT
IF @avg_score1 is NULL
PRINT 'ERROR: No this Student!'
ELSE
PRINT 'The average score is '+CAST(@avg_score1 AS char(3))

例4 查询某一学期所有学生的姓名、最高分、最低分和平均成绩。

（1）存储过程创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
USE stuinfo
/**************创建视图****************/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'View_score' AND type = 'V') 
	DROP VIEW View_score

CREATE VIEW View_score
AS
SELECT a.sno, sname, a.cno, cname, a.tno, tname,term,score
FROM tab_score a INNER JOIN tab_student b ON a.sno = b.sno 
	INNER JOIN tab_teacher c ON a.tno = c.tno
	INNER JOIN tab_course d ON a.cno = d.cno
	
/**************创建存储过程****************/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'stu_max_score' AND type = 'P') 
DROP PROCEDURE stu_max_score

CREATE PROCEDURE stu_max_score
@term smallint 
AS
SELECT sname AS 姓名,max(score)AS 最高分,min(score)AS 最低分,AVG(score) AS 平均分	FROM View_score 	WHERE term=@term GROUP BY sname

（2）存储过程执行

1
EXECUTE stu_max_score  1