主题：[推荐]第六部分课堂讲稿——SQL语言之二

3 数据操纵语句
对数据记录进行操纵的语句，包括插入，删除和更新。

3．1 插入
1）插入单条记录
insert into stu(number,name,sex,birthday,height) values('000111','JIM',1,'1981-01-03',1.77)
注意：不能违反约束

2）插入多条记录
select * into stu1 from stu where 1=0
insert into stu1 select * from stu where sex=1

3．2 删除
delete from stu1 where number in (select number from stu)
注意：
1）做好备份，不能恢复
2）不能使用投影，如：delete * from stu1

结合子查询的删除：
delete from stu where number in (select number from grade group by number having avg(grade)>=90)

3．3 更新
update stu1 set name='Jim'
update stu1 set name='Jack' where number='000111'

update grade set grade=grade+1 where number=(select number from stu where name='黎明')

4 数据定义语句
对各种非记录的数据库对象进行操作的语句。

根据定义方法分为：创建、删除和更改。
根据定义对象分为：数据库、表、…。

结合SQL Server2000数据库管理系统讲解数据库和表的数据定义操作。

5 视图
以有色眼镜为例，比喻视图的作用；以企业中不同用户具有不同级别的查看能力，介绍视图的实际作用。

5．1 介绍图形界面的视图操作方法和视图的数据定义语句
create view mstu
as
select * from stu where sex=1

演示利用视图进行的各种单视图查询和多表连接查询，如：
select * from mstu
等价于
select * from stu where sex=1

select * from mstu inner join grade on mstu.number=grade.number
等价于
select * from stu inner join grade on stu.number=grade.number where sex=1

修改视图：
alter view mstu as select name from stu where sex=1
删除视图：
drop view mstu

5．2 视图、基本表和查询
强调三者的联系和区别：
1）查询、视图的数据都来源于基本表。
2）从本质上看，查询和视图都是sql语句，但是查询只是一条执行的sql语句，而视图可以理解为固化在数据库中的sql语句。
3）查询是只读的，而视图可以更新基本表。

5．3 关于视图的更新问题
1）单表的简单更新
create view mstu
as
select * from stu where sex=1

update mstu set height=height*100

2）使用聚合运算时不可更新
create view stuInfo
as
select max(height) as 'maxheight',count(*) as 'count' from stu

update stuinfo set count=count+1
错误：视图或函数 'stuinfo' 不可更新，因为它包含聚合。

3）使用多表连接时不可更新
create view stuGrade
as
select name,course,grade from stu inner join grade on stu.number=grade.number

insert into stuGrade values('Sam','C01',90)
错误：视图或函数 'stuGrade' 不可更新，因为修改会影响多个基表。

4）违反逻辑的更新（with check option）
select * from mstu
insert into mstu(number,sex) values('000207',0)
select * from mstu
情况：可以插入记录，却没有增多记录。

create view mstu
as
select * from stu where sex=1
with check option

insert into mstu(number,sex) values('000207',0)
错误：试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。

5．4 其他数据库管理系统中的视图
1）VFP中的本地视图和远程视图（连接SQL Server数据库和更新Access数据库）
2）Access中的查询视图

6 存储过程
6．1 作用
将逻辑代码封装进数据库，实现开发者和数据库管理者之间的有效分隔。

6．2 存储过程的相关数据定义语句
create proc stu_proc1
as select count(*) from stu

alter proc stu_proc1
as select * from stud

drop proc stu_proc1

6．3 使用存储过程
1）定义为：
create proc stu_proc1
as select count(*) from stu

使用方法为：
exec stu_proc1
或者使用VB等语言开发ADO系统模拟访问，可以进一步演示数据库结构变化和应用程序的变化带来的困难和存储过程解决方法的灵活性。

2）定义为：
create proc stu_proc2
as
begin
select min(name)
from stu inner join grade on stu.number=grade.number
group by stu.number
having avg(grade)>85
end

使用方法为：
exec stu_proc2
或者使用VB等语言开发ADO系统模拟访问，可以进一步演示数据库结构变化和应用程序的变化带来的困难和存储过程解决方法的灵活性。

6．4 传入参数
create proc stu_proc3
as
select name from stu

create proc stu_proc4
@pname varchar(8)
as
select course,grade
from stu inner join grade on stu.number=grade.number
where name=@pname
使用方法为：
exec stu_proc4 '黎明'
或者使用VB等语言开发ADO系统模拟访问，可以进一步演示数据库结构变化和应用程序的变化带来的困难和存储过程解决方法的灵活性。

6．5 其他存储过程的注意事项
可使用with recompile 和with encryption
两者都可放在参数之后，语句之前
其中with recompile会导致每次调用存储过程都会编译，效率变低，但执行效率可能优化，或加入到exec 存储过程名称 with recompile中，则会根据需要创造新的查询计划。
如：
create proc stu_proc5
with recompile
as
select * from stu

其中with encryption可以用于对存储过程进行加密，如：
create proc stu_proc6
with encryption
as
select * from stu

可以使用一些脚本破解之：exec sp_decrypt 'stu_proc6'。

7 扩展的SQL语言
很多数据库管理系统对标准SQL语言进行了扩展，以充分发掘系统能力，但是在一定程度上也导致兼容性的丧失。如SQL Server中的Transact－SQL和Oracle中的PL－SQL等。

7．1 可以进行编程的语法
7．1．1 使用变量
1）局部变量
DECLARE @name VARCHAR(20)

SET @name='黎明'
--print @name
--select @name

SELECT * FROM stu WHERE name = @name

2）全局变量
select @@VERSION
select @@SERVERNAME

select * from stu where sex=0
select @@ROWCOUNT

7．1．2 使用语句
1）顺序

2）分支
求绝对值：
declare @int int
set @int=-1
IF @int<0
BEGIN
     set @int=-@int
END
select @int

如有男生则显示男生记录，否则显示女生记录：
IF (SELECT COUNT(*) FROM stu where sex=1)>0
   BEGIN
     SELECT * FROM stu where sex=1
   END
ELSE
   BEGIN
     SELECT * FROM stu where sex=0
   END

3）循环
打印1到100的整数：
DECLARE @a int
set @a=1
WHILE @a<=100
BEGIN
    print @a
    set @a=@a+1
END

不停给所有学生的所有课程成绩加一，直至全班平均分超过90或者有分数达到100分：
USE students
select * into grade_bak from grade
select * from grade
GO

WHILE (SELECT AVG(grade) FROM grade) < 90
BEGIN
    UPDATE grade SET grade = grade + 1
    SELECT MAX(grade) FROM grade
    IF (SELECT MAX(grade) FROM grade) = 100
        BREAK
END
PRINT '成绩太高'

select * from grade
GO

drop table grade
select * into grade from grade_bak
drop table grade_bak

7．2 扩展标准SQL的功能
7．2．1 结合SQL语句的case语句
显示男女生的汉字性别：
SELECT
number,name,
   CASE  
      WHEN sex =1 THEN '男'
      WHEN sex =0 THEN '女'
      ELSE 'NULL'
   END AS "性别"
FROM stu

将男生的身高更新为1.0，女生的身高更新为2.0：
USE students
select * into stu_bak from stu
select * from stu
GO

UPDATE stu SET height = CASE WHEN sex = 1 THEN 1.0 ELSE 2.0 END

select * from stu
GO

drop table stu
select * into stu from stu_bak
drop table stu_bak

7．2．2 使用函数
利用自定义函数显示男女生的汉字性别：
CREATE FUNCTION sextoCh(@s int)
RETURNS nchar(1)
--RETURNS char(2)
AS
BEGIN
IF @s=1
RETURN '男'
RETURN '女'
END
GO

SELECT number,name,dbo.sextoCh(sex) from stu
GO

DROP FUNCTION sextoCh
GO

7．2．3 使用统计查询功能
1）compute
select number,grade from grade order by number compute avg(grade)
select number,grade from grade order by number compute avg(grade) by number
2）rollup
select number,avg(grade) from grade group by number,grade with rollup
3）cube
select number,course,avg(grade) from grade group by number,course with cube