【SQL语言】数据库原理与设计

第五章 SQL语言

5.3 简单查询（对一个表）

BETWEEN...AND

SELECT * FROM course
WHERE 学时 BETWEEN 50 AND 70

GROUP BY

SELECT 班号,count(*) as '人数' FROM student
GROUP BY 班号

HAVING子句只能用于GROUP BY子句中

SELECT 班号,count(*) as '人数' FROM student
GROUP BY 班号 HAVING count(*) >= 2

• 若同时含有WHERE、GROUP BY、HAVING，先WHERE在GROUP BY最后HAVING。

ORDER BY

SELECT * FROM course
ORDER BY 课程号 DESC

• 若想对多列排序，在列与列之间要加上逗号。

5.4 连接查询

内连接

1.等值连接
select * from class , student
where class.班号= student.班号

2.不等值连接
查询比王辉所在班班号大的所有班的班主任编号
--x、y是设置出来的临时参数
SELECT 班主任编号 FROM student x, class y
WHERE x.班号<y.班号 and 姓名='王辉'

3.自连接
查询与“王辉”同班的学生信息。
SELECT y.姓名,y.学号,y.性别,y.出生日期,y.班号FROM student x, student y
WHERE x.姓名='王辉' and x.班号=y.班号

外连接

1.左外连接
按照课程号相等的条件，实现表course与表score的左外连接
SELECT * FROM course LEFT OUTER JOIN
score ON score.课程号=course.课程号

2.右外链接
SELECT * FROM course right OUTER JOIN score
ON score.课程号=course.课程号

3.全外连接
SELECT * FROM course full OUTER JOIN score
ON score.课程号=course.课程号

交叉连接

SELECT * FROM student CROSS JOIN class

5.5 聚集函数

COUNT([DISTINCT|ALL]*) 		统计元组个数
COUNT([DISTINCT|ALL]<列名>)  统计一列中值的个数
SUM([DISTINCT|ALL]<列名>)    计算一列值的总和（此列必须是数值型）
AVG([DISTINCT|ALL]<列名>)    计算一列值的平均值（此列必须是数值型）
MAX([DISTINCT|ALL]<列名>)   求一列值中的最大值
MIN ([DISTINCT|ALL]<列名>)  求一列值中的最小值

SELECT COUNT(*) as 人数 FROM Student

SELECT SUM(学时) as 总学时数 FROM course

SELECT AVG(学时) as 平均学时 FROM course

SELECT MAX(学时) as 最多学时, MIN(学时) as 最少学时 FROM course

5.6 嵌套查询

SELECT * FROM course
WHERE 学时 = (SELECT max(学时) FROM course)

SELECT * FROM course
WHERE 学时<(SELECT 学时 FROM course WHERE 课程名='系统分析与设计')

5.7 集合运算

UNION，是并集

SELECT 班号 FROM student
UNION
SELECT 班号 FROM class

INTERSECT，是交集

SELECT 班号 FROM student
INTERSECT
SELECT 班号 FROM class

EXCEPT，减法运算

查询在班级表中有而在学生表中没有的班号。
SELECT 班号 FROM class
EXCEPT
SELECT 班号 FROM student

5.8 视图的创建和使用

1.创建
CREATE VIEW STUDENT_V_C51
AS SELECT * FROM STUDENT WHERE 班号='C51'

CREATE VIEW STUDENT_V_W(姓名,班号)
AS SELECT 姓名, 班号 FROM STUDENT WHERE 性别='女'

2.修改
ALTER VIEW STUDENT_V_C51(学号,姓名,性别, 出生日期) AS
SELECT 学号,姓名,性别,出生日期
FROM STUDENT
WHERE 班号='C51'

应用

1.使用视图隐藏列和行
CREATE VIEW MarketDepartmentProjectView AS
SELECT ProjectID, ProjectName, StartDate, EndDate
FROM Project
WHERE Department=‘Marketing‘

2. 使用视图显示计算列的结果
创建一个工作总时数的计算列的视图。
CREATE VIEW ProjectWorkedHoursView AS
SELECT project.ProjectID, ProjectName, MaxHours AS
ProjectMaxHours, SUM(WorkedHours) AS ProjectWorkedHours
FROM project, assignment
WHERE project.ProjectID = assignment.ProjectID
GROUP BY project.ProjectID, ProjectName, MaxHours

3. 使用视图隐藏复杂的SQL语法
查询学号、姓名、课程号、课程名和成绩。
方法一：用连接查询实现。
SELECT x.学号, x.姓名, y.课程号, y.课程名, z.成绩
FROM student x, course y, score z
WHERE x.学号= z.学号 and z.课程号= y.课程号
方法二：先创建一个包含学号、姓名、课程号、课程名和成绩的视图
S_SC_C，然后通过查询该视图获得所需数据。
首先创建视图S_SC_C：
CREATE VIEW S_SC_C(学号,姓名,课程号,课程名,成绩) AS
SELECT x.学号, x.姓名, y.课程号, y.课程名, z.成绩
FROM student x, course y, score z
WHERE x.学号= z.学号 and y.课程号= z.课程号
然后查询视图中的数据：
SELECT * FROM S_SC_C

4. 计算和内置函数的分层
WHERE子句中不能使用计算列和内置函数。为了解决这个问题，可以采用多层
视图来实现相应的功能。
在2中创建的工作总时数视图的基础上，再创建一个项目超时视图。
CREATE VIEW ProjectOverWorkedHoursView AS
SELECT ProjectID, ProjectName, ProjectMaxHours, ProjectWorkedHours
FROM ProjectWorkedHoursView
WHERE ProjectWorkedHours > ProjectMaxHours
之后，利用该视图可直接查询各超时的项目。
SELECT ProjectID, ProjectName, ProjectMaxHours, ProjectWorkedHours,
ProjectWorkedHours – ProjectMaxHours AS OverHeadHours
FROM ProjectOverWorkedHoursView
ORDER BY ProjectID

5.9 更新操作

插入数据

INSERT INTO Student (学号,姓名) 
VALUES (‘08006’, '王凯')

INSERT INTO Student
VALUES ('08007','刘积云' ,'男','1991-07-22','c63')

INSERT INTO score_c1
SELECT 学号，成绩
FROM score
WHERE 课程号='C1

修改数据

update class 
set 班名='计算机', 班主任编号='03001'
where 班号='c62

删除数据

DELETE FROM student
WHERE 姓名='刘积云'

第六章 高级SQL语言

6.2 常量与变量

局部变量的赋值

1.Set赋值
创建变量var1和var2并赋值，然后输出变量的值。
在查询分析器中输入：
Declare @var1 char(10), @var2 char(20)
Set @var1=‘中国’
Set @var2=@var1+’是伟大国家’
Select @var1,@var2
Go
2.用查询赋值
Declare @st varchar(10), @sv varchar(5)
Select @st=姓名, @sv=性别 from student where 学号=‘000001’)
Select @st, @sv

6.4 T-SQL流控语句

begin...end语句块

分支语句

1.if语句
If(
select avg(成绩) 
from score, course
where score.课号=course.课号 and course.课名=’数据库原理’)>75
	select ‘数据库原理平均成绩高于75’
else
	select ‘数据库原理平均成绩低于75

2.case语句
Select 学号,姓名,专业,性别=
	Case 性别
		When 0 then ‘女’
		When 1 then ‘男’
		Else ‘未知’
	End
From student
或者
Select 学号,姓名,专业,性别=
	Case
		When 性别=0 then ‘女’
		When 性别=1 then ‘男’
		Else ‘未知’
	End
From student

循环

1.while循环
While exists(Select * From 员工表 Where 工资*0.3 < 300)
	Begin
		Update 员工表 Set 工资=工资+500
		If (Select Max(工资) From 员工表) > 15000
		Break
	End

2.Waitfor语句
晚上10点盘点总库存量和库存总值
Begin
	Waitfor time ‘22:00’
	Select Sum(amount) as 总库存量, Sum(price) as 库存总值 from inventory
End

3.Goto语句

try...catch
说明： Try…Catch语句提供了容错处理功能，如果TRY块内
部发生错误，则会将控制传递给CATCH块中包含的另一个语
句组。 TRY…CATCH构造捕捉所有严重级别大于10但不终止
数据库连接的错误。

显示相应的错误信息
Begin try
  Select distinct 班号 From student
  Select 45/0 as ‘结果’
End try
Begin catch
  Select error_message() as ‘错误信息’
End catch

6.5 函数

CREATE FUNCTION distance
(@x1 int, @y1 int, @x2 int, @y2 int)
RETURNS float
AS
  BEGIN
	declare @dis float
	set @dis = SQRT( POWER(@x1-@x2, 2) +POWER(@y1-@y2, 2))
	RETURN @dis
END

6.6 游标

声明游标->打开游标->读取数据->关闭游标->删除游标

1.利用游标逐条读取记录并打印。
Declare ex_cursor Cursor, @bh varchar(6), @xm varchar(8)
For Select 编号, 姓名 From 员工表
	Where 所属部门=‘项目部’ Order by 姓名
For Read Only
Open ex_cursor  -- 打开游标
Fetch From ex_cursor INTO @bh, @xm  --使用游标
Print @bh, @xm
While @@FETCH_STATUS=0
	Begin
		Fetch From ex_cursor INTO @bh, @xm
		Print @bh, @xm
	End

2.使用游标变量
Declare @cursor_var cursor
Set @cursor_var=cursor scroll dynamic
	For Select 学号，姓名
		From student
		Where 姓名 like‘王%’ /*给游标变量赋值*/
Open @cursor_var
Fetch next from @cursor_var
Fetch next from @cursor_var

3.关闭游标xs_cur2
close xs_cur2

4.删除游标xs_cur2
Deallocate xs_cur2

6.7 存储过程

创建存储过程

创建存储过程student_info, 以便使用姓名和课程名查询学生的成绩.
Create procedure student_info @name char(8),@cname char(16)
As 
	Select student.学号,姓名,成绩
	From student ,score
	Where student.学号=score.学号 and 姓名=@name and score. 课程号=Course.课程号 and 课程名=@cnmae
Go
执行存储过程：
Execute student_info ‘王林’, ’计算机基础’

查看存储过程

1.通过sp_helptext查看存储过程的文本。
Sp_helptext student_info
显示create procedure student_info 的创建文本
2.通过sp_help系统查看存储过程的基本信息。
查看student_info的基本信息，包括名称，创建者、类型、时间等信息
EXEC sp_help student_info
3.通过使用sp_depends、 sys.sql_dependencies对象目录视图查看存储过程。
查看student_infoxinxi
Exec sp_depends student_info

修改存储过程

修改存储过程student_info ，将参数改为学号
Alter procedure student_info @number char(6),@cname char(16)
	As
	Select student.学号,课程名,成绩
	From student, course, score
	Where student.学号=score.学号 and 
	student.学号=@number and 
	score.课号=course.课号 and 课名=@cnmae
	Go

删除存储过程

删除存储过程student_info
If exists(
select name 
from sysobjects 
where name=’student_info’)
Drop procedure student_info

使用存储过程

1.直接执行student_info存储过程
Execute student_info ‘王林’, ’计算机基础在'
2.用With recompile选项创建不会被缓存的存储过程(该存储过程将在每次执行时都重新编译)student_info1，并执行。
Create procedure student_info1
With recompile
As
	Select 学号,姓名,出生日期,班级
	From student
	Where 性别=’女’
Execute student_info1

注意：如果指定了for replication 则不能使用此选项
3.用With encryption创建加密的存储过程student_info2并执行。
Create procedure student_info2
With encryption
As
	Select 学号,姓名,出生日期,班级
	From student
	Where 性别=’女’
Exec student_info2
注意：加密后的存储过程，通过SP_HELPTEXT查看显示文本已加密（不能查看）
4.创建临时存储过程student_info3，并执行。
Create procedure #student_info3
With recompile
As
	Select 学号,姓名,出生日期,班级
	From student
	Where 性别=’女’
Exec #student_info3
注意：‘#’表示局部临时存储过程，‘##’表示全局临时存储过程。当sql server关闭时存储过程自动删除。
5.创建带参数的存储过程student_info4。
Create procedure student_info4 @sex char(2)
As
	Select 学号,姓名,出生日期,班级
	From student
	Where 性别=@sex
Exec student_info4 ‘男
6.创建有默认值的参数的存储过程
student_info5。
Create procedure #student_info5 @sex char(2)=’男’
As
	Select 学号,姓名,出生日期,班级
	From student
	Where 性别=@sex
Exec student_info5
7.创建带输出参数的存储过程student_info6。
Create procedure student_info6
	@name char(8)=’王一林’
	@cavg int output
As
	Select @cavg=avg(成绩)
	From student, score, course
	Where student.学号=score.学号 and score.课程号=course.课程号 and 姓名=@name
说明：
用到output参数，执行存储过程时需要接收参数的变量然后输出。
Declare @cc int
Exec student_info6 ‘王一林’, @cc output
Select ‘王一林的平均成绩是’+@cc

6.8 触发器

创建insert触发器

1.在student表中创建一个insert类型触发器，当向STUDENT表中插入数据时，检查其出生日期是否晚于2003-12-31，但不拒绝数据插入。
create trigger my_trigger on STUDENT for insert
as
begin
	declare @xh char(10), @xm char(10), @sr date
	select @xh=学号, @xm=姓名, @sr=出生日期 from inserted
	if @sr>'2004-01-01'
	begin
		print '学号：'+@xh+'：姓名：'+@xm+'：出生日期：'+convert(varchar(10), @sr)
		print '该生年龄偏小，出生日期晚于1004-01-01，请核实出生日期'
	end
end

2.创建一个简单的insert类型触发器，要求能实现当向STUDENT表中插入新数据时，能看到新插入的数据和所有的数据。
/*创建insert类型触发器*/
create trigger my_trigger1 on STUDENT
for insert
as
begin
	select * from inserted
	select * from STUDENT
end
go

3.创建delete类型触发器
create trigger my_trigger2 on STUDENT
for delete
as
begin
	select * from deleted
	select * from STUDENT
end
go

4.创建update类型触发器
Create Trigger my_trigger3 On Course
After Update, Insert
As
begin
	select * from inserted
	select * from deleted
	select * from STUDENT
end
go

5.创建instead of触发器
在student表上创建一个instead of insert触发器，当向表中插入的记录中的班号在class表中不存在时将班号改为NULL后插入。
create Trigger mt_trigger4 On student Instead of Insert
As
begin 
	declare @xh char(10), @xm char(10), @xb char(2), @sr date, @bh char(10)
	select @xh=学号, @xm=姓名, @xb=性别, @sr=出生日期, @bh=班号 from inserted
	if @bh is not null and not exists (select * from class where 班号=@bh)
	begin
		insert into student values(@xh,@xm,@xb,@sr,null)
		print '新插入记录的班号不存在，该记录的班号已被设置为null'
	end
	else insert into student values (@xh,@xm,@xb,@sr,@bh)
end 

修改触发器

将my_trigger1 insert触发器改为update触发器
alter trigger my_trigger1
	on student
	for update
as
	print ‘执行修改操作’
go

删除触发器

删除my_trigger1触发器。
If exists (
select name 
from sysobjects 
where name=’ my_trigger1’)
Drop trigger my_trigger1