-- 作者:admin
-- 发布时间:2008/2/20 15:11:15
-- [推荐]第六部分课堂讲稿——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
[此贴子已经被作者于2010-12-11 19:49:25编辑过]
|