《MySQL数据库原理及应用》课件第5章数据库的高级应用.pptx

1、MySQL数据库原理及应用课件第5章数据库的高级应用第5章 数据库的高级应用任务要求：为了提高学生信息管理系统中数据的安全性、完整性和查询速度，在应用系统开发过程中要充分利用索引、视图、存储过程和函数、触发器、事务等来提高系统的性能。学习目标：了解索引、视图、游标、存储过程和函数、触发器及事务的作用 掌握索引、视图、游标、存储过程和函数、触发器及事务的创建方法 掌握索引、视图、游标、存储过程和函数、触发器及事务的修改及删除方法第5章 数据库的高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁第第5 5章章 数据库的数据库的高级应用高

2、级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁5.1.1 索引概述5.1.2 索引的类型5.1.3 索引的设计原则5.1.4 创建索引5.1.5 删除索引第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁【任务分析】【任务分析】设计人员在数据库中怎样合理地设计索引，提高数据的查询速度和效率。【课堂任务】【课堂任务】本节要理解索引的概念及作用。索引的概念及类型索引的创建和管理第第5 5章章 数据库的数据库的高级应用高级应用5.1 索

3、引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁索引是一个单独的、物理的数据库结构，是某个表中一列或者若干列的集合以及相应的标识这些值所在的数据页的逻辑指针清单。索引是依赖于表建立的，提供了数据库中编排表中数据的内部方法。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就存放在索引页面上。索引一旦创建，将由数据库自动管理和维护。例如，向表中插入、更新和删除一条记录时，数据库会自动在索引中做出相应的修改。在编写SQL查询语句时，具有索引的表与不具有索引的表没有任何区别，索引只是提供一种快速访问指定记录的方法。第第5 5章章 数据库

4、的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁1 1索引可以提高数据的访问速度索引可以提高数据的访问速度只要为适当的字段建立索引，就能大幅度提高下列操作的速度。（1）查询操作中WHERE子句的数据提取。（2）查询操作中ORDER BY子句的数据排序。（3）查询操作中GROUP BY子句的数据分组。（4）更新和删除数据记录。2 2索引可以确保数据的唯一性索引可以确保数据的唯一性唯一性索引的创建可以保证表中数据记录不重复。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础

5、5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁索引的类型1 1普通索引和唯一索引普通索引和唯一索引普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引是指索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。2 2单列索引和组合索引单列索引和组合索引单列索引是指一个索引只包含单个列，一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事

6、务5.7 锁索引的类型3 3全文索引全文索引全文索引是指在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。4 4空间索引空间索引空间索引是对空间数据类型的字段建立的索引。MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只有在存储引擎MyISAM的表中创建。

7、对于初学者来说，这类索引很少会用到。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁索引的设计原则索引设计不合理或缺少索引都会对数据库和应用性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑以下准则。1 1索引并非越多越好索引并非越多越好2 2避免对经常更新的表进行过多的索引避免对经常更新的表进行过多的索引3 3数据量小的表最好不要使用索引数据量小的表最好不要使用索引4 4在不同值少的列上不要建立索引在不同值少的列上不要建立索引5 5指定唯一索引是由某种数据本身的特指定

8、唯一索引是由某种数据本身的特征来决定征来决定6 6为经常需要排序、分组和联合操作的为经常需要排序、分组和联合操作的字段建立索引字段建立索引第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁创建索引创建索引是指在某个表的一列或多列上建立一个索引，以提高对表的访问速度。在实际创建索引之前，有如下几个注意事项。当给表创建UNIQUE约束时，MySQL会自动创建唯一索引。索引的名称必须符合MySQL的命名规则，且必须是表中唯一的。可以在创建表时创建索引，或是给现存表创建索引。只有表的所有者才能给表创建

9、索引。创建唯一索引时，应保证创建索引的列不包括重复的数据，并且没有两个或两个以上的空值（NULL）。因为创建索引时将两个空值也视为重复的数据，如果有这种数据，必须先将其删除，否则索引不能被成功创建。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁创建索引1 1在创建表时创建索引在创建表时创建索引（1）使用Navicat工具创建索引。（2）使用SQL语句创建索引。在使用CREATE TABLE语句创建表时直接创建索引，此方式简单、方便。其语法格式如下。CREATE TABLE(,UNIQUE|

10、FULLTEXT|SPATIAL UNIQUE|FULLTEXT|SPATIAL 索引名索引名(属性名属性名(长度长度)ASC|DESC,)ASC|DESC,);第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁创建索引例例1 1为为studentstudent表表snosno列创建唯一索引列创建唯一索引id_snoid_sno，索引排列顺序为降序。，索引排列顺序为降序。CREATE TABLE student(UNIQUE INDEX id_sno(sno)DESC);例例2.2.为为scs

11、c表的表的snosno和和cnocno列创建普通索列创建普通索引引id_scid_sc，索引排列顺序为升序。，索引排列顺序为升序。CREATE TABLE sc(INDEX id_sc(sno,cno)ASC);第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁创建索引2 2在现存表中创建索引在现存表中创建索引可以使用Navicat工具ALTER TABLE语句或CREATE INDEX语句创建。CREATE UNIQUE|FULLTEXT|SPATIAL INDEX ON (属性名(长度)A

12、SC|DESC,);例：为表student的sbirthday列创建一个普通索引id_birth。CREATE INDEX id_birth ON student(sbirthday);第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁删除索引删除索引当索引不再需要时可以将其删除。在MySQL中，可用Navicat管理工具和SQL语句删除索引。1 1使用管理工具使用管理工具NavicatNavicat删除索引删除索引2 2使用使用SQLSQL语句删除索引语句删除索引使用SQL语言的DROP I

13、NDEX语句可删除索引，语句格式如下。DROP INDEX ON;DROP INDEX id_sno ON student;DROP INDEX id_sc ON sc;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁【任务分析】【任务分析】设计人员在数据库中怎样合理地设计视图，以提高数据的存取性能和操作速度。【课堂任务】【课堂任务】本节要理解视图的作用及使用。视图的概念及作用视图的创建、修改和删除第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础

14、5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁5.2.1 视图概述5.2.2 视图的创建5.2.3 视图的使用5.2.4 视图的修改5.2.5 视图的删除第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图是从一个或者几个基本表或者视图中导出的虚拟表，是从现有基表中抽取若干子集组成用户的“专用表”，这种构造方式必须使用SQL中的SELECT语句来实现。在定义一个视图时，只是把其定义存放在数据库中，并不直接存储视图对应的数据，直到用户使用视图时才去查找对应的数据。第第5 5章章 数

15、据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁使用视图具有如下优点。（1）简化对数据的操作。（2）自定义数据。（3）数据集中显示。（4）导入和导出数据。（5）合并分割数据。（6）安全机制。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的创建1.使用Navicat管理工具创建2使用SQL语句创建视图在SQL中，使用CREATE VIEW语句创建视图，语法格式如下。CREATE VIEW view_na

16、me(Column,n)AS select_statement WITH CHECK OPTION;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的创建【例5.4】有条件的视图定义。定义视图v_student，查询所有选修数据库课程的学生的学号（sno）、姓名（sname）、课程名称（cname）和成绩（degree）。CREATE VIEW v_student AS SELECT A.sno,sname,cname,degree FROM student A,course B,sc

17、C WHERE A.sno=C.sno AND B.cno=C.cno AND cname=数据库;视图定义后，可以像基本表一样进行查询。例如，若要查询例5.4定义的视图v_student，可以使用如下命令。SELECT*FROM v_student;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的使用视图的使用主要包括视图的检索，以及通过视图对基表进行插入、修改、删除操作。视图的检索几乎没有什么限制，但是对通过视图实现表的插入、修改、删除操作则有一定的限制条件。第第5 5章章 数据库

18、的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的使用1 1使用视图进行数据检索使用视图进行数据检索视图的查询总是转换为对它所依赖的基本表的等价查询。利用SQL的SELECT命令和Navicat都可以对视图进行查询，其使用方法与基本表的查询完全一样。2 2通过视图修改数据通过视图修改数据视图也可以使用INSERT命令插入行，当执行INSERT命令时，实际上是向视图所引用的基本表插入行。视图中的INSERT命令与在基本表中使用INSERT命令的格式完全一样。第第5 5章章 数据库的数据库的高级应用高级应用5.1

19、 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的使用【例5.5】利用视图向表student中插入一条数据。V1_student是创建的视图，脚本如下。CREATE VIEW V1_studentAS SELECT sno,sname,saddress FROM student;执行下面脚本。INSERT INTO V1_student VALUES(2005020301,王小龙,山东省临沂市);第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.

20、7 锁视图的使用查看结果的脚本如下。SELECT sno,sname,ssex,saddress FROM student;从图5.10所示的执行结果可以看出，数据在基本表中已经正确插入。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的使用如果视图中有下面所述属性，则插入、更新或删除操作将失败。（1）视图定义中的FROM子句包含两个或多个表，且SELECT选择列表达式中的列包含来自多个表的列。（2）视图的列是从集合函数派生的。（3）视图中的SELECT语句包含GROUP BY子句或DI

21、STINCT选项。（4）视图的列是从常量或表达式派生的。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的使用【例5.6】将例5.5中插入的数据删除。DELETE FROM V1_student WHERE sname=王小龙;这个例子执行后会将基本表student中的所有sname为“王小龙”的行删除。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁视图的修改1 1使用使用Navic

22、atNavicat修改视图修改视图2 2使用使用SQLSQL语句修改视图语句修改视图在SQL语句中，使用ALTER VIEW命令修改视图，语法格式如下。ALTER VIEW view_name(Column,n)AS select_statement WITH CHECK OPTION;【例5.7】修改例5.5中的视图V1_student。ALTER VIEW V1_student AS SELECT sno,sname FROM student;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7

23、 锁视图的删除1 1使用使用NavicatNavicat删除视图删除视图2 2使用使用SQLSQL语句删除视图语句删除视图语法格式如下。DROP VIEW view,n;DROP VIEW命令可以删除多个视图，各视图名之间用逗号分隔，删除视图必须拥有DROP权限。【例5.8】删除视图V1_student。DROP VIEW V1_student;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁【任务分析】【任务分析】设计人员要编写存储过程和函数、触发器及事务，首先要掌握SQL语言的语法规范及

24、语言基础。【课堂任务】【课堂任务】本节要熟悉SQL语言。SQL的语法规范SQL的语言基础常用函数游标的基本操作第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁5.3.1 SQL语言基础5.3.2 MySQL常用函数5.3.3 游标第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础SQL语言是一系列操作数据库及数据库对象的命令语句，因此了解基本语法和流程语句的构成是

25、必须的，主要包括常量和变量、表达式、运算符、控制语句等。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础1 1常量与变量常量与变量（1）常量。常量也称为文字值或标量值，是指程序运行中值始终不改的量。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁常量类型常量类型常量表示说明常量表示说明字 符 串 常字 符 串 常量量包括在单引号（包括在单引号（）或双引号（）或双

26、引号（）中，由字母（）中，由字母（a az z、A AZ Z）、数字字符（）、数字字符（0 09 9）以及特殊字符（如感叹号（）以及特殊字符（如感叹号（!）、）、atat符（符（）和数字号（）和数字号（#）组成。）组成。示例：示例：ChinaChina、Output X is:Output X is:、NhelloNhello（UnicodeUnicode字符串常量，只能用单引号括起字符串）字符串常量，只能用单引号括起字符串）十 进 制 整十 进 制 整型常量型常量使用不带小数点的十进制数据表示。使用不带小数点的十进制数据表示。示例：示例：12341234、654654、+2008+2008、

27、-123-123十 六 进 制十 六 进 制整型常量整型常量使用前缀使用前缀0 x0 x后跟十六进制数字串表示。后跟十六进制数字串表示。示例：示例：0 x1F000 x1F00、0 xEEC0 xEEC、0X190X19日期常量日期常量使用单引号（使用单引号（）将日期时间字符串括起来。）将日期时间字符串括起来。MySQLMySQL是按是按年年-月月-日的顺序表示日期的。中间的间隔符可以用日的顺序表示日期的。中间的间隔符可以用“-”，也，也可以使用如可以使用如“”、“/”、“”或或“%”等特殊符号。等特殊符号。示例：示例：2009-01-032009-01-03、2008/01/092008/0

28、1/09、2010121020101210实型常量实型常量有定点表示和浮点表示两种方式。有定点表示和浮点表示两种方式。示例：示例：897.1897.1、-123.03-123.03、19E2419E24、-83E2-83E2位字段值位字段值使用使用bvaluebvalue符号写位字段值。符号写位字段值。valuevalue是一个用是一个用0 0和和1 1写成写成的二进制值。直接显示的二进制值。直接显示bvaluebvalue的值可能是一系列特殊的符的值可能是一系列特殊的符号。例如，号。例如，b0b0显示为空白，显示为空白，b1b1显示为一个笑脸图标。显示为一个笑脸图标。示例：示例：SELECT

29、 BIN(b111101+0),OCT(b111101+0)SELECT BIN(b111101+0),OCT(b111101+0)布尔常量布尔常量布尔常量只包含两个可能的值：布尔常量只包含两个可能的值：TRUETRUE和和FALSEFALSE。FALSEFALSE的数的数字值为字值为“0 0”，TRUETRUE的数字值为的数字值为“1 1”。示例：获取示例：获取TRUETRUE和和FALSEFALSE的值：的值：SELECT TRUE,FALSESELECT TRUE,FALSENULLNULL值值NULLNULL值可适用于各种列类型，它通常用来表示值可适用于各种列类型，它通常用来表示“没有

30、值没有值”、“无数据无数据”等意义，并且不同于数字类型的等意义，并且不同于数字类型的“0 0”或字符串类或字符串类型的空字符串型的空字符串第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础（2）变量。变量就是在程序执行过程中，变量就是在程序执行过程中，其值是可以改变的量。其值是可以改变的量。可以利用变量存储程序执行过程中涉及的数据，如计算结果、用户输入的字符串以及对象的状态等。变量由变量名和变量值构成，其类型与常量变量由变量名和变量值构成，其类型与常量一样。变量名不

31、能与命令和函数名相同，一样。变量名不能与命令和函数名相同，这里的变量和在数学中所遇到的变量的概念基本上是一样的，可以随时改变它所对应的数值。在MySQL系统中，存在3种类型的变量：第1种是系统变量 全局（global）变量 会话（session）变量第2种是用户变量第3种是局部变量第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础（2）变量。全局变量和会话变量。全局变量和会话变量的区别：全局变量在MySQL启动时由服务器自动将它们初始化为默认值，主要影响整个mysq

32、l实例的全局设置，大部分全局变量都是作为mysql的服务器调节参数存在。对全局变量的修改会影响到整个服务器。会话变量在每次建立一个新的连接时，由MySQL来初始化。对会话变量的修改，只会影响到当前的会话，也就是当前的数据库连接。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础（2）变量。全局变量和会话变量。大多数的系统变量应用于其他SQL语句时，必须在名称前加两个符号。如：select version，current_date;a.显示系统变量清单 SHOW gl

33、obal|session VARIABLES LIKE 字符串 如:show variables like a%b.修改系统变量的值 在MySQL中，有的系统变量的值是不能改变的，如version和系统日期，而有些系统变量是可以通过SET语句来修改的，如：SET global.sort_buffer_size=25000；第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础（2）变量。全局变量和会话变量。【例5.9】将全局系统变量sort_buffer_size的值改

34、为25000，执行命令如下。SET global.sort_buffer_size=25000;【例5.10】对于当前会话，把系统变量SQL_SELECT_LIMIT的值设置为100。这个变量决定了SELECT语句的结果集中的最大行数。SET SESSION.SQL_SELECT_LIMIT=100;SELECT LOCAL.SQL_SELECT_LIMIT;如果要将一个系统变量值设置为MySQL默认值，可以使用default关键字。例如：SET LOCAL.SQL_SELECT_LIMIT=DEFAULT；第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL

35、编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础（2）变量。用户变量。用户可以在表达式中使用自己定义的变量，这样的变量叫做用户变量。用户变量在被引用时要在其名称前加上标志定义和初始化一个用户变量可以使用SET或SELECT语句如:创建用户变量name并赋值为”王小强”。SET name=王小强 或SET name:=王小强 或SELECT name:=王小强第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础（2）变量。用

36、户变量。【例5.11】创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3。SET user1=1,user2=2,user3=3;或SELECT user1:=1,user2:=2,user3:=3;【例5.12】创建用户变量user4，它的值为user3的值加1。SET user4=user3+1;或SELECT user4:=user3+1;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础（2）变量。用户变量。在一个用户变量被创建后，它可

37、以以一种特殊形式的表达式用于其他SQL语句中。【例5.13】查询上例中创建的变量name的值和变量user1、user2、user3和user4的值。SELECT name;+-+|name|+-+|王小强|+-+1 row in setSELECT user1,user2,user3,user4;+-+-+-+-+|user1|user2|user3|user4|+-+-+-+-+|1|2|3|4|+-+-+-+-+1 row in set第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁S

38、QLSQL语言基础语言基础（2）变量。用户变量。【例5.14】使用查询结果给变量赋值。USE gradem；SET student=(SELECT sname FROM student WHERE sno=2007010120);【例5.15】查询表student中名字等于例5.14中student值的学生信息。SELECT sno,sname,sbirthday FROM student WHERE sname=student;【例5.16】利用SELECT语句将表中数据赋值给变量。select name:=password from suser limit 0,1;第第5 5章章 数据库的

39、数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础（2）变量。用户变量。【例5.17】查看“gradem”数据库中的学生信息，而条件只是查看student表中“系别”为“软件工程系”的学生信息。USE gradem;SET 系别=软件工程系;SELECT sno,sname,saddress FROM student WHERE sdept=系别;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5

40、.7 锁SQLSQL语言基础语言基础（2）变量。局部变量。例如，定义局部变量myvar，数据类型为INT，默认值为10，代码如下。DECLARE myvar int DEFAULT 10;下面给局部变量myvar赋值为100，代码如下。SET myvar=100;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础2 2表达式表达式在SQL语言中，表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常都有返回值。与常量和变量一样，表达式的值也具有某种

41、数据类型。根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期型表达式。表达式一般用在SELECT及SELECT语句的WHERE子句中。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言基础语言基础2 2表达式表达式例如，如下所示为一个使用表达式的SELECT查询语句。SELECT A.sno,AVG(degree)AS 平均成绩,CONCAT(sname,SPACE(6),ssex,SPACE(4),classno,班,SPACE(4),left(classno

42、,4),年级)AS 考生信息FROM sc A INNER JOIN student B ON A.sno=B.snoGROUP BY A.sno,sname,ssex,classnoORDER BY 平均成绩 DESC;+-+-+-+|sno|平均成绩|考生信息|+-+-+-+|2007030436|94.5000|徐小栋 男 20070304班 2007年级|2007030409|94.0000|刘明海 男 20070304班 2007年级|2007030408|93.0000|刘众林 男 20070304班 2007年级|2007030420|92.7500|李鹏飞 男 20070304

43、班 2007年级|2007010106|92.6667|孙晋梅 女 20070101班 2007年级|第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础3 3SQLSQL流程控制语句流程控制语句结构化程序设计语言的基本结构是顺序结构、条件分支结构和循环结构。顺序结构是一种自然结构，条件分支结构和循环结构需要根据程序的执行情况对程序的执行顺序进行调整和控制。在SQL语言中，流程控制语句就是用来控制程序执行流程的语句，也称流控制语句或控制流语句。在MySQL中，这些流程

44、控制语句和局部变量只能在存储过程或函数、触发器或事务的定义中。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础3 3SQLSQL流程控制语句流程控制语句（1）BEGINEND语句块。BEGINEND可以定义SQL语句块，这些语句块作为一组语句执行，允许语句嵌套。关键字BEGIN定义SQL语句的起始位置，END定义同一块SQL语句的结尾。它的语法格式如下。BEGINsql_statement|statement_block;END;其中，sql_statement是

45、使用语句块定义的任何有效的SQL语句；statement_block是使用语句块定义的任何有效SQL语句块。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础3 3SQLSQL流程控制语句流程控制语句（2）IFELSE条件语句。用于指定SQL语句的执行条件。如果条件为真，则执行条件表达式后面的SQL语句。当条件为假时，可以用ELSE关键字指定要执行的SQL语句。它的语法格式如下。IF search_condition THEN statement_listELSEI

46、F search_condition THEN statement_listELSE statement_listEND IF;其中，search_condition是返回true或false的逻辑表达式。如果逻辑表达式中含有SELECT语句，必须用圆括号将SELECT语句括起来。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础3 3SQLSQL流程控制语句流程控制语句【例5.18】使用IFELSE条件语句查询计算机系的办公室位置。如果查询结果为空，则显示“办公地

47、点不详”，否则显示其办公地点。IF(SELECT office FROM department WHERE deptname=计算机工程系)IS NULL THENBEGIN SELECT 办公地点不详 AS 办公地点;SELECT*FROM department WHERE deptname=计算机工程系;END;ELSE SELECT office FROM department WHERE deptname=计算机工程系;END IF;IFELSE语句可以进行嵌套，即在SQL语句块中可能包含一个或多个IFELSE语句。第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视

48、图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础3 3SQLSQL流程控制语句流程控制语句（3）CASE分支语句。CASE关键字可根据表达式的真假来确定是否返回某个值，可以允许使用表达式的任何位置使用这一关键字。使用CASE语句可以进行多个分支的选择，CASE语句具有如下两种格式。简单格式：将某个表达式与一组简单表达式进行比较以确定结果。简单CASE格式的语法如下。CASE input_expressionWHEN when_expression THEN result_expression;nELSE else_result_ex

49、pression;END CASE;第第5 5章章 数据库的数据库的高级应用高级应用5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础3 3SQLSQL流程控制语句流程控制语句 搜索格式：计算一组布尔表达式以确定结果。搜索CASE格式的语法如下。CASEWHEN Boolean_expression THEN result_expression;nELSE else_result_expression;END CASE;语句中参数的含义与CASE格式的参数含义类似。第第5 5章章 数据库的数据库的高级应用高级应用

50、5.1 索引5.2 视图5.3 SQL编程基础5.4 存储过程和函数5.5 触发器5.6 事务5.7 锁SQLSQL语言语言基础基础3 3SQLSQL流程控制语句流程控制语句例如，统计学生的不及格门数，利用CASE语句显示档次（较多、一般、较少、没有）。declare dj int default 0;SELECT count(*)into dj FROM sc WHERE degree=100 THEN SELECT 不及格门数较多 as 档次;WHEN dj=50 AND dj=1 AND dj 50 THEN SELECT 不及格门数较少 as 档次;ELSE SELECT 没有不及格的