新编数据库技术-MySQL 课件 王若宾 第6-11章 视图和索引-数据库应用系统综合实列

新编数据库技术—MySQL北方工业大学02索引01视图

第6章视图和索引视图的增删改查索引的增删改查视图的算法索引的算法02了解视图的基础算法04了解索引的基础算法03掌握索引的基础操作01掌握视图的基础操作学习目标进入本章学习第6章视图和索引视图的增删改查索引的基本应用视图的算法索引的算法视图01索引02视图01视图是一种数据库对象，其本质上式一种虚拟表。视图的作用ab……cde………fg……基础表1基础表2基础表3视图1视图不是真正的表，视图要建立在表的基础上。视图只存储了结构，其数据是从视图所基于的表中获取的。aedf…………视图的优点通过只允许用户看到视图中所定义的数据而不是视图引用表中的全部数据以达到聚焦业务的目的。聚焦业务视图通过查询定义，若定义视图的查询本身就是一个复杂的查询，把查询以视图的方式固定下来，在每一次执行相同查询时，不必重新编写复杂的查询语句，只需要运行视图即可。简化操作当表中包含很多行和列的数据时，由于数据量太大，往往需要在设计时就对表进行水平分割或垂直分割，但这就导致表的结构发生变化，这种变化对前端应用程序访问数据库中的数据造成不利影响。使用视图就可以保持原有的总体结构关系，同时还可以让各个表结构更简单，数据容量也会更小。合并分割数据通过为不同用户提供不同的视图，可以实现让不同的用户以不同的方式看到不同或相同的数据集。当有不同层级用户共用同一数据库时，这种操作就显得尤为重要，即为不同的用户实现了数据定制。定制数据通过视图用户只能查看和修改他们所能看到的数据，其它数据既不可见也不可以访问。更高的安全性视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分隔开来。应用和数据相互独立不同角度对视图的理解数据库外部用户数据库内部不同角度视图可视同表，对表执行的操作，如增、删、改、查等，可以应用于大部分视图。视图反映的是用户的数据需求，它相当于一个能够被存储并使用的查询。视图的数据来源于一张或多张表，它是由查询定义的虚拟表，仅存储虚拟表的结构，实际数据仍然在视图所基于的表中。020301谢谢观看新编数据库技术—MySQL北方工业大学01任务(1)创建视图v_student，要求该视图包含学生表（tb_student）中学院（school）为“信息学院”的所有同学的学号（stu_num）、姓名（name）和学院（school）以及专业（major）；(2)修改已建立的视图v_student，使之只显示年级为17级的同学的学号（stu_num）、姓名（name）和学院（school）以及专业（major）；(3)删除已建立的视图v_student；(4)创建视图v_bibliography，要求该视图包含书目表（tb_bibliography）中种类（category）为TP的所有书目的ISBN号（ISBN），书目名称（name）和作者（author）。02分析本题的设计体现的是视图聚焦业务的优点。设想如下情境：视图v_student的用户是信息学院的数据管理员，则该视图使得“信息学院”的数据管理员只关注自己学院所涉及的数据，达到了聚焦业务的目的；同时“信息学院”的数据管理员不能访问其他学院的学生数据，提高了数据的安全性。03实施视频演示。案例6.1创建、修改、删除单表视图SQL代码USElib;#创建单表视图CREATEVIEW

v_studentASSELECTstu_num,name,school,majorFROMtb_student

WHEREschoo1="信息学院":#修改单表视图ALTERVIEWv_studentASSELECTstu_num,name,school,majorFROM

tb_studentWHEREschool=“信息学院”AND1eft(stu_num,2)=’17';#删除单表视图DROPVIEWv_student;#创建单表视图供后续使用CREATEVIEWv_bibliographyASSELECTISBN,name,categoryFROM

tb_bibliographyWHEREcategory=’TP’:谢谢观看新编数据库技术—MySQL北方工业大学01任务(1)创建视图v_student，要求该视图包含学生表（tb_student）中学院（school）为“信息学院”的所有同学的学号（stu_num）、姓名（name）和学院（school）以及专业（major）；(2)修改已建立的视图v_student，使之只显示年级为17级的同学的学号（stu_num）、姓名（name）和学院（school）以及专业（major）；(3)删除已建立的视图v_student；(4)创建视图v_bibliography，要求该视图包含书目表（tb_bibliography）中种类（category）为TP的所有书目的ISBN号（ISBN），书目名称（name）和作者（author）。02分析本题的设计体现的是视图聚焦业务的优点。设想如下情境：视图v_student的用户是信息学院的数据管理员，则该视图使得“信息学院”的数据管理员只关注自己学院所涉及的数据，达到了聚焦业务的目的；同时“信息学院”的数据管理员不能访问其他学院的学生数据，提高了数据的安全性。03实施视频演示。案例6.1创建、修改、删除单表视图SQL代码USElib;#创建单表视图CREATEVIEW

v_studentASSELECTstu_num,name,school,majorFROMtb_student

WHEREschoo1="信息学院":#修改单表视图ALTERVIEWv_studentASSELECTstu_num,name,school,majorFROM

tb_studentWHEREschool=“信息学院”AND1eft(stu_num,2)=’17';#删除单表视图DROPVIEWv_student;#创建单表视图供后续使用CREATEVIEWv_bibliographyASSELECTISBN,name,categoryFROM

tb_bibliographyWHEREcategory=’TP’:谢谢观看新编数据库技术—MySQL北方工业大学01任务查看创建的所有视图02分析视图是虚拟的表，查看视图的方法与查看表相同。在workbench中，Views目录下存储着已创建的视图。也可以通过SQL代码显示已创建的视图。03实施视频演示。案例6.2查看已经创建的视图SQL代码USE

lib;

#查看已建立的单表视图

SHOW

TABLES;

谢谢观看新编数据库技术—MySQL北方工业大学01任务查看v_bibliography的视图结构。02分析查看视图结构的方法与查看表结构方法相似。在workbench中，可以通过图形化界面点击查看已创建的视图结构。也可以通过SQL代码显示已创建的视图结构。03实施视频演示。案例6.3查看视图的结构SQL代码USE

lib;

#显示已创建视图的结构

DESC

v_bibliography;

谢谢观看新编数据库技术—MySQL北方工业大学01任务查看v_bibliography的创建视图的语句。02分析查看创建视图语句的方法与查看建表语句的方法相似，使用SHOW关键字。03实施视频演示。案例6.4查看建立视图的语句SQL代码USE

lib;

#显示创建视图的SQL语句

SHOW

CREATE

VIEW

v_bibliography;谢谢观看新编数据库技术—MySQL北方工业大学01任务通过视图v_bibliography查询书籍名(name)为“大学计算机”的ISBN号（ISBN）。02分析查看视图数据的方法与查看表数据的方法相似，使用SELECT关键字。03实施视频演示。案例6.5查看视图数据SQL代码USE

lib;

#显示创建视图的SQL语句

SELECT

ISBN

FROM

v_bibliography

WHERE

name='大学计算机';谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）创建视图v_record，要求该视图包含tb_record表中的借阅编号（rid）和借书时间（borrow_time）以及还书时间（return_time），tb_student表中的学号（stu_num）和学生姓名（name），tb_book表中的索书号（bar_code）和tb_bibliography表中的书目名称（name）。（2）使用v_record视图，显示“邓承明”借过的书籍。02分析本题体现出了视图简化操作的特点，通过建立多表视图，对用户屏蔽了多表数据的复杂性，大大简化了用户对数据的操作。把复杂的查询以视图的方式固定下来，在每一次执行相同查询时，不必重新编写复杂的查询语句，只需要像查询表一样查询已建立的视图即可。03实施视频演示。案例6.6创建多表视图SQL代码USElib;CREATEVIEWv_recordASSELECTr.stu_num,s.nameASs_name,r.barcode,bi.nameASb_name,borrow_time,return_timeFROMtb_recordrLEFTJOINtb_studentsONs.stu_num=r.stu_numLEFTJOINtb_bookbONb.barcode=r.barcodeLEFTJOINtb_bibliographybiONbi.ISBN=b.ISBN;SELECT*FROMv_recordWHEREs_name=“邓承明”;谢谢观看新编数据库技术—MySQL北方工业大学01任务创建视图v_borrowing，要求在上一案例中已经创建的v_reocord视图上创建，v_borrowing包含视图v_record中的学生姓名（name）并且计算所有同学借阅过图书的本数。02分析视图不仅仅可以基于表创建，同时，视图可以在另一个视图上创建。03实施视频演示。案例6.7基于已有视图创建新的视图SQL代码USElib;CREATEVIEWv_borrowingASSELECTs_name,COUNT(*)AScntFROMv_recordGROUPBYs_name;SELECT*FROMv_borrowing;谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）创建v_student2视图，要求用temptable算法，该视图包含表tb_student表中的学号（stu_num），姓名（name），生日（birth），专业（major）列的全部数据并且按照生日（birth）进行降序排列。（2）创建v_student3视图，要求用merge算法，该视图包含表tb_student表中的学号（stu_num），姓名（name），生日（birth），专业（major）列的全部数据并且按照生日（birth）进行降序排列。02分析视图算法是系统对视图以及外部查询视图的select语句的一种解析方式。视图算法分为三种：1.undefined：未定义（默认的），由系统自己选择。2.temptable：临时表算法：mysql先根据定义视图的SELECT语句创建一个临时表，然后针对该临时表执行输入查询。3.merge：合并算法：系统将首先把视图对应的select语句与外部查询视图的select语句进行合并，然后执行，由于merge执行的效率高，系统一般会选择merge作为视图的默认算法。03实施视频演示。案例6.8依据不同的算法创建视图SQL代码USE

lib;

#使用TEMPTABLE算法创建视图CREATE

ALGORITHM=TEMPTABLE

VIEW

v_student2

AS

SELECT

stu_num,

name,

birth,

major

FROM

tb_student

ORDER

BY

birth

DESC;

#使用MERGE算法创建视图CREATE

ALGORITHM=MERGE

VIEW

v_student3

AS

SELECT

stu_num,

name,

birth,

major

FROM

tb_student

ORDER

BY

birth

DESC;

谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）更改视图v_student3学号(stu_num)为“17130202”的同学姓名(name)为“周睿睿1”。（2）更改视图v_student2学号为“17130202”的同学姓名为“周睿睿1”。02分析使用temptable算法创建的视图，是无法更新的视图。03实施视频演示。案例6.9可更新视图示例1SQL代码USE

lib;#更新MERGE算法创建的视图

UPDATE

v_student3

SET

name="周睿睿1"

WHERE

stu_num="17130202";#更新TEMPTABLE算法创建的视图

UPDATE

v_student2

SET

name="周睿睿1"

WHERE

stu_num="17130202";

谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）更改视图v_student3学号(stu_num)为“17130202”的同学姓名(name)为“周睿睿”。（2）更改视图v_record学号为“17130202”的同学姓名为“周睿睿1”。02分析在要创建可更新视图，定义视图的SELECT语句不能包含以下任何元素：聚合函数，如：MIN，MAX，SUM，AVG，COUNT等。DISTINCT子句/GROUPBY子句/HAVING子句/UNION或UNIONALL子句外连接SELECT子句中的子查询或引用该表的WHERE子句中的子查询出现在FROM子句中引用FROM子句中的不可更新视图仅引用文字值对基表的任何列的多次引用03实施视频演示。案例6.10可更新视图示例2SQL代码USE

lib;

#该视图不包含不可更新视图的元素，因此可更新UPDATE

v_student3

SET

name="周睿睿"

WHERE

stu_num="17130202";#定义视图的SELECT语句中使用了左连接

UPDATE

v_record

SET

name="周睿睿1"

WHERE

stu_num="17130202";

谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）创建视图v_student4，要求该视图包含学生表（tb_student）中学院（school）为“信息学院”的所有同学的学号（stu_num）、姓名（name）和学院（school）以及专业（major）。（2）在视图v_student3中添加一条记录（“17130205”，“马又云1”，“1999-03-28”，“数据科学与大数据技术”）（3）在视图v_student4中添加一条记录（“17130206”，“马又云2”，“理学院”，“数据科学与大数据技术”）02分析在MySQL中，视图可分为普通视图与检查视图。没有withcheckoption时,表示with_check_option的值为0，即为普通视图。普通视图不具备检查功能。含有withcheckoption子句，在通过检查视图更新基表数据时,只有满足检查条件的更新语句才能成功执行。03实施视频演示。案例6.11检查视图的应用SQL代码USE

lib;

#创建检测视图，与v_student3（即普通视图）做比较CREATE

VIEW

v_student4

AS

SELECT

stu_num,name,school,major

FROM

tb_student

WHERE

school='信息学院'

WITH

CHECK

OPTION;

#向普通视图中插入数据INSERT

INTO

v_student3

VALUES("17130205","马又云1","1999-03-28","数据科学与大数据技术");

#向检测视图中插入数据INSERT

INTO

v_student4

VALUES("17130206","马又云2","理学院","数据科学与大数据技术");

谢谢观看新编数据库技术—MySQL北方工业大学第6章视图和索引视图的增删改查索引的基本应用视图的算法索引的算法视图01索引02索引概述MySQL的索引是为了加速对数据进行检索而创建的一种分散的、物理的数据结构。数据库中的索引的形式与图书的目录类似,索引可以看作是针对键值的指针，键值就像目录中的标题，指针相当于页码。索引的工作机制类似按照图书目录查找对应页码的内容，这样读者就不必顺序翻遍整本书去查找内容，类似的，使用索引可以不必扫描整个数据表去找到想要的数据行。索引分类索引的关键字是index。普通索引是MySQL中的基本索引类型，允许在定义索引的列中插人重复值和空值。普通索引主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。也可通过修改表的方法增加主键,但一个表只能有一个主键索引。主键索引unique索引列的值必须唯一,允许有空值。如果是组合索引，则列值的组合必须唯一，在一个表上可以创建多个唯一性索引。唯一性索引全文索引是指在定义索引的列上支持值的全文查找，允许在这些索引列中插人重复值和空值。该索引只能对char、varchar和text类型的列编制索引，并且只能在MyISAM表中编制。全文索引空间索引是对空间数据类型的字段建立的索引。创建空间索引的列，必须将其声明为notnull,空间索引只有在存储引擎MyISAM的表中创建。空间索引谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）建立表tb_test1，定义id的类型为int，name的类型为char(10)。（2）建立表tb_test2，定义id的类型为int，name的类型为char(10)。02分析为了更好体现索引在数据检索方面的效率优势，先建立以上两个表，只有在检索大量数据时，才能看出检索花费时间的不同。因此，先在两个表中存储大量数据，本题先创建一个可以添加100000条数据的储存过程。03实施视频演示。案例6.12建立含有索引的表SQL代码USE

lib;

#创建tb_test1表CREATE

TABLE

tb_test1(

id

INT,

name

CHAR(10)

)ENGINE

INNODB

CHARSET

UTF8;

#创建tb_test2表CREATE

TABLE

tb_test2(

id

INT,

name

CHAR(10)

)ENGINE

INNODB

CHARSET

UTF8;

SQL代码USE

lib;

DROP

PROCEDURE

IF

EXISTS

`auto_insert`;

DELIMITER

$$

CREATE

PROCEDURE

auto_insert()

BEGIN

DECLARE

i

INT

DEFAULT

1;

WHILE(i<100000)DO

#向两表中插入数据

INSERT

INTO

tb_test1

VALUES(i,CONCAT('name',i));

INSERT

INTO

tb_test2

VALUES(i,CONCAT('name',i));

SET

i=i+1;

END

WHILE;

END$$

DELIMITER

;

#调用存储过程CALL

auto_insert();#显示所有数据SELECT

*

FROM

tb_test1;

SELECT

*

FROM

tb_test2

;

谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）在tb_test2表的name列上建立一个普通索引index_name。（2）在tb_student表的学院（school）列和专业（major）列建立一个复合索引index_sm。02分析创建索引的语句和建表语句类似，使用CREATE关键字创建索引。03实施视频演示。案例6.13使用CREATEINDEX创建索引SQL代码USE

lib;

#在tb_test2创建普通索引CREATE

INDEX

index_name

ON

tb_test2(name);

#在tb_student创建符合索引

CREATE

INDEX

index_sm

ON

tb_student(school,

major);

谢谢观看新编数据库技术—MySQL北方工业大学01任务建立tb_test3表，定义id的类型为int，name的类型为char(10)，id字段建立主键索引，name字段建立唯一性索引。02分析索引可以在建立数据表时直接创建。03实施视频演示。案例6.14使用CREATETABLE创建索引SQL代码USE

lib;

CREATE

TABLE

tb_test3(

id

INT,

name

CHAR(10),

PRIMARY

KEY(id),

#创建唯一键索引UNIQUE(name)

)ENGINE

INNODB

CHARSET

UTF8;

谢谢观看新编数据库技术—MySQL北方工业大学01任务在tb_test1表的id列上添加一个普通索引index_id。02分析修改表结构也可以创建索引。03实施视频演示。案例6.15使用ALTERTABLE语句创建索引SQL代码USE

lib;

#修改表结构ALTER

TABLE

tb_test1

ADD

INDEX

index_id(id);

谢谢观看新编数据库技术—MySQL北方工业大学01任务（1） 查看tb_test1表上建立的索引。（2） 查看tb_test2表上建立的索引。02分析本案例实现如何显示已经建立的索引。03实施视频演示。案例6.17查看创建的索引SQL代码USE

lib;

SHOWINDEXFROMtb_test1;SHOWINDEXFROMtb_test2;

Thankyouforwatching谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）查询tb_test1表中name为“name543”的记录，关注所需要的时间。（2）查询tb_test2表中，name为“name543”的记录，关注所需要的时间。02分析本案例体现了索引可以显著提高数据信息查询速度。但是并不是加了索引，就会提高效率。03实施视频演示。案例6.16使用索引SQL代码USE

lib;

SELECT

*

FROM

tb_test1

WHERE

name="name543"LIMIT0,10000;

SELECT

*

FROM

tb_test2

WHERE

name="name543"LIMIT0,10000;

对于建立索引提出以下原则：一个表创建大量索引会影响insert、update和delete语句的性能。应避免对经常更新的表创建过多的索引，要限制索引的数目；若表的数据量大，对表数据的更新较少而查询较多,可以创建多个索引来提高性能。在包含大量重复值的列上创建索引,查询的时间会较长；经常需要排序、分组和联合操作的字段一定要建立索引，即将用于join、where判断和orderby排序的字段上创建索引；在视图上创建索引可以显著地提升查询性能；尽量不要对数据库中某个含有大量重复值的字段建立索引，在这样的字段上建立索引有可能降低数据库的性能；在主键上创建索引，在InnoDB中通过主键来访问数据效率是非常高的。但是每个表只能创建一个主键索引；要限制索引的数目，对于不再使用或者很少使用的索引要及时删除。谢谢观看新编数据库技术—MySQL北方工业大学01任务删除tb_test1表id列的主键索引。02分析为了能够正常演示删除索引的效果，需要提前建立索引，详见代码。03实施视频演示。案例6.18删除索引SQL代码USE

lib;

CREATEINDEXindex_idONtb_test1(id);SHOWINDEXFROMtb_test1;DROPINDEXindex_idONtb_test1;SHOWINDEXFROMtb_test1;Thankyouforwatching谢谢观看新编数据库技术—MySQL北方工业大学理论点睛——二分查找二分查找建立在有序数组的基础上。如果中间元素是要查找的元素，则搜素过程结束；如果某一特定元素大于或者小于中间元素，则在数组大于或小于中间元素的那一半中查找；和上一步操作相同，把新得到的中间元素和要查询的元素比较。如果在某一步数组为空，则代表找不到。135711131719232931374143475359230low8mid16highBinarysearchbestcasesteps：0Sequentialsearch

steps：02313571113171923293137414347535912345679101112131415123456791011121314150816理论点睛——哈希散列法原理是首先根据key值和哈希函数创建一个哈希表（散列表），当想要查询元素时，根据键值，通过散列函数，可以定位到想要查询元素的位置。相比较而言，哈希散列法可以用于未排序序列的查找，而二分查找则需要预先排序。k2k1k4k3k5k6h（x）谢谢观看新编数据库技术—MySQL北方工业大学第7章MySQL编程基础SQL构成及MySQL对它的扩展01变量02常量03流程控制语句05运算符04学习目标掌握常用运算符初步理解函数及其自定义方法掌握流程控制语句了解变量、常量及其分类，掌握变量的使用方法SQL构成及MySQL对它的扩展01SQL语言的三种类型——数据定义语言（DDL）数据操作语言（DML）数据控制语言（DCL）SQL的发展历程19741976197919821985由Boyce和Chamberlin提出，当时称SEQUEL。IBM公司的Sanjase研究所在研制RDBMSSYSTEMR时改为SQL。ORACLE公司发布第一个基于SQL的商业化RDBMS产品。IBM公司出版第一个RDBMS语言SQL/DS。IBM公司发布它的第一个RDBMS--DB2。1986美国国家标准化组织ANSI宣布SQL作为数据库工业标准。ISO和IEC发布了SQL国际标准，称为SQL-92（SQL2）19921999SQL99标准，即SQL-93（SQL3）20162016年12月14日，ISO/IEC发布了最新版本的数据库语言SQL标准（ISO/IEC9075:2016）。引入对JSON的支持。……2019当前的SQL标准是SQL:2019，它增加了对多维数组的支持SQL简单易学，特定语句均由描述性单词组成。2SQL不是某个特定数据库供应商所专有，它支持所有的关系型数据库。1SQL不区分大小写。4SQL使用灵活方便，同时可以进行高级数据库操作。3SQL的特点创建（create）创建数据库、创建表、创建索引01删除（drop）删除数据库、删除表、删除索引02修改（alter）修改数据库、修改表03数据定义语言DDL（DataDefinitionLanguage）#建库语句CREATEDATABASElibrary;#建表语句CREATE

TABLE

tb_student

(

stu_num

CHAR(11),

name

VARCHAR(20),

text

TINYTEXT

);

……查找（select）01插入（insert）02更新（update）03删除（delete）04#建库语句CREATEDATABASElibrary;#建表语句CREATE

TABLE

tb_student

(

stu_num

CHAR(11),

name

VARCHAR(20),

text

TINYTEXT

);

……数据操纵语言DML（DataManipulationLanguage）数据控制语言DCL（DataControlLanguage）#建库语句CREATEDATABASElibrary;#建表语句CREATE

TABLE

tb_student

(

stu_num

CHAR(11),

name

VARCHAR(20),

text

TINYTEXT

);

……授予权限（grant）01收回权限（revoke）02进入本章学习新编数据库技术—MySQL北方工业大学第7章MySQL编程基础SQL构成及MySQL对它的扩展01变量02常量03流程控制语句05运算符04变量02系统变量和自定义变量系统变量全局变量概述：启动时由服务器自动初始化为默认值，可以通过更改my.ini文件中的设置来实现。作用域：对所有会话（连接）有效，但是不能跨越重启。如果要想每次启动也修改，则需要修改配置文件。会话变量概述：会话变量在每次建立一个新连接时，由MySQL初始化，MySQL会将当前所有全局变量的值复制一份作为会话变量。作用域：仅对当前会话（连接中）有效，当前会话中断则作用域失效。系统变量全局变量和会话变量对比类型作用域特点全局变量服务器全局变量在MySQL启动时由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini文件中的设置来实现。会话变量会话连接会话变量在每次建立一个新连接时，由MySQL初始化，MySQL会将当前所有全局变量的值复制一份作为会话变量。谢谢观看新编数据库技术—MySQL北方工业大学01任务(1)显示所有全局变量。(2)显示所有会话变量。02分析本案例通过显示所有全局变量和会话变量让读者了解系统变量的名称和值。随着MySQL学习的进一步深入，特别是掌握更多DBA（DataBaseAdministrator）相关专业技能后，读者对系统变量的理解会更到位。03实施视频演示。案例7.1显示系统变量SQL代码SHOWGLOBALVARIABLES;SHOWSESSIONVARIABLES;谢谢观看新编数据库技术—MySQL北方工业大学用户变量概述：用户变量与会话相关，当客户端建立与MySQL服务器的连接时，可以定义用户变量来存储和使用在该会话期间的值。作用域：仅对当前连接（会话）有效，作用域等同于会话变量。自定义变量01任务(1)声明两个用户变量m和n，并均赋值为1。(2)把m和n的值加和并赋值给新声明的变量sum，显示sum的值。02分析使用关键字SET声明用户变量并进行求和运算，使用SELECT显示变量的值。03实施视频演示。案例7.2用户变量的声明及使用SQL代码SET@m=1;SET@n=1;SET@sum=@m+@n;SELECT@sum;谢谢观看新编数据库技术—MySQL北方工业大学局部变量概述：局部变量一般用于SQL的语句块中，它需要先声明，然后赋值使用。作用域：主要作用于存储过程和函数中。自定义变量01任务(1)声明两个局部变量m和n，并均赋值为1。(2)把m和n的值加和并赋值给新声明的变量sum，显示sum的值。02分析局部变量仅在定义它的BEGIN/END语句块中有效，因此需放在BEGIN/END语句块中，如不在BEGIN/END中进行局部变量的声明和实用，则系统会给出错误提示。03实施视频演示。案例7.3局部变量的声明及使用SQL代码DELIMITER$DROPPROCEDUREIFEXISTS`sumint`$CREATEPROCEDUREsumint()BEGINDECLAREmINTDEFAULT1;DECLAREnINTDEFAULT

1;DECLAREsumINT;SETsum=m+nSELECTsum;END$DELIMITER;CALLsumint();用户变量和局部变量对比类型作用域定义位置语法用户变量当前会话会话任何地方加@符号，不用指定类型局部变量定义在BEGIN/END中BEGIN/END第一句话一般不加@，需要指定类型谢谢观看新编数据库技术—MySQL北方工业大学MySQL编程基础04算术运算符比较运算符逻辑运算符位运算符算术运算符运算符号作用+加法-减法*乘法/除法DIV整除%或MOD取余，返回余数01任务（1）声明一个用户变量num，设置初始值为0。（2）进行+、-、*、/、%、/0以及%0算术运算，数值自拟，并显示结果。02分析如果运算的结果为null，不一定是算术的变量为null，也有可能/0或%0。用字符串表示数字在用于计算时，会自动转化成数字。当执行转化时。MySQL执行两个基本规则：1）如果是数字的字符串用于算术，那么将会转换成对应的数字；2）如果一个包含字符和数字混合的字符串不能被正确地转换成数字，那么会被转换成0。03实施视频演示。案例7.4使用算术运算符SQL代码SET@num=0;SELECT@num,@num+7,@num+21.5,@num/2,@num*2,@num%4,@num/0,@num%0;比较运算符在ASCⅡ表中’o’大于’d’，因此，’good’>’god’为真。运算符作用=等于<>!=不等于<=>严格比较两个NULL值是否相等<=小于或等于<

小于>=大于或等于>

大于运算符作用ISNULL为空ISNOTNULL不为空BETWEENAND介于两值之间IN在集合中NOTIN不在集合中LIKE模糊匹配REGEXP正则式匹配谢谢观看新编数据库技术—MySQL北方工业大学比较运算符运算符作用=等于<>!=不等于<=>严格比较两个NULL值是否相等<=小于或等于<

小于>=大于或等于>

大于运算符作用ISNULL为空ISNOTNULL不为空BETWEENAND介于两值之间IN在集合中NOTIN不在集合中LIKE模糊匹配REGEXP正则式匹配比较运算符在ASCⅡ表中’o’大于’d’，因此，’good’>’god’为真。01任务输入并执行以下SELECT语句，检验自己的判断是否正确。SELECT1=0,2=2,'2'=2,'0.03'=0,'a'='b',(1+3)=(2+2),NULL=NULL;SELECT'good'<>'god',1<>2,4!=4,5.5!=5,(1+3)!=(2+1),NULL<>NULL;SELECT1<=>0,'2'<=>2,2<=>2,'0.02'<=>0,'b'<=>'b',(1+3)<=>(2+2),NULL<=>NULL;02分析在SELECT语句中使用=、<>或!=、<=>判断是否相等的相关规则如下：(1)使用‘=’运算符判断数字、表达式、字符串是否相等，如相等，返回值为1，否则为0。(2)使用‘<>或！=’运算符判断数字、表达式、字符串是否不相等，如不相等，返回值为1，否则为0。(3)使用‘<=>’运算符判断数字、表达式、字符串是否完全相等，如完全相等，返回值为1，否则为0。当判断NULL时，如两个均为NULL，则返回1，否则返回0。如果有一个参与比较的是NULL，则比较结果为NULL；若字符串和数据进行比较，则MySQL自动将字符串转换为数字然后进行比较。03实施视频演示。案例7.5判断相等关系谢谢观看新编数据库技术—MySQL北方工业大学01任务输入并执行以下SELECT语句，检验自己的判断是否正确。SELECT'good'<='god',1<=2,4<=4,5.5<5,(1+3)<=(2+1),NULL<=NULL;SELECT'good'<'god',1<2,4<4,5.5<5,(1+3)<(2+1),NULL<NULL;SELECT'good'>='god',1>=2,4>=4,5.5>=5,(1+3)>=(2+1),NULL>=NULL;SELECT'good'>'god',1>2,4>4,5.5>5,(1+3)>(2+1),NULL>NULL;02分析在SELECT语句中使用‘<=、<、>=、>’进行比较判断的相关规则如下：(1)使用‘<=’运算符判断数字、表达式、字符串是否小于或等于右边操作数，如小于或等于，返回值为1，否则为0。(2)使用‘<’运算符判断数字、表达式、字符串是否小于右边操作数，如小于，返回值为1，否则为0。(3)使用‘>=’运算符判断数字、表达式、字符串是否大于或等于右边操作数，如大于或等于，返回值为1，否则为0。(4)使用‘>’运算符判断数字、表达式、字符串是否大于右边操作数，如大于，返回值为1，否则为0。在SELECT语句中使用‘<=、<、>=、>’进行比较判断时，如果有一个参与比较的是NULL，则比较结果为NULL，同为NULL时则无法比较，其结果仍为NULL.若字符串和数据进行比较，则MySQL自动将字符串转换为数字然后进行比较。03实施视频演示。案例7.6判断大小关系谢谢观看新编数据库技术—MySQL北方工业大学01任务输入并执行以下SELECT语句，检验自己的判断是否正确。SELECTNULLISNULL,ISNULL(NULL),ISNULL(10),10ISNOTNULL;SELECT2BETWEEN4AND6,4BETWEEN4AND6,8BETWEEN4AND6;SELECT2in(1,3,5,'thanks'),'thanks'in(1,3,5,'thanks'),2notin(1,3,5,'thanks'),'thanks'notin(1,3,5,'thanks');02分析相关规则如下：(1)ISNULL运算符是检验一个操作数是否为NULL，如果为NULL，返回值为1，否则为0;ISNOTNULL检验一个值是否非NULL，如果非NULL，返回值为1，否则为0。(2)BETWEENAANDB：若一个操作数在A和B之间，则返回值为1，否则为0。(3)IN是用来判断操作数是否在IN表中，如果在，返回值为1，否则为0；NOTIN用来判断操作数是否不在IN表中，如果不在，返回值为1，否则为0。03实施视频演示。案例7.7使用操作数运算符谢谢观看新编数据库技术—MySQL北方工业大学01任务输入并执行以下SELECT语句，检验自己的判断是否正确。SELECT'study'LIKE'study','study'LIKE'stud_','study'LIKE'%d','study'LIKE'st_','study'LIKENULL;SELECT'sky'REGEXP'^s','sky'REGEXP'y$','s'REGEXP'[a-i]','s'REGEXP'[^a-i]';02分析相关规则如下：(1)LIKE运算符用来匹配字符串，ALIKEB，如果满足匹配，返回值为1，否则为0；当A或B有一个为NULL时，结果为NULL。(2)REGEXP运算符用来匹配字符串，AREGEXPB，如果满足条件，返回值为1，否则为0；若A或B任意一个为NULL，则结果为NULL。在SELECT语句中经常使用‘LIKE’、‘REGEXP’运算符对字符串判断。在LIKE运算符中，‘%’匹配任意数目字符，‘_’只能匹配一个字符。03实施视频演示。案例7.8使用字符串匹配运算符通配符通配符作用例子%包括零个或多个字符组成的任意字符串‘马%’可以匹配到’马诗’和’马又云’_（下划线）任意一个字符‘马_’可以匹配到’马诗’匹配符匹配符规则例子^匹配以该字符后面的字符开头的字符串。’^s’能匹配到’sky’、’sk’等以’s’开头的字符串$匹配以该字符后面的字符结尾的字符串。’y$’能匹配到’sky’、’ky’等以’y’结尾的字符串.匹配除'\n'之外的任何单个字符。要匹配包括'\n'在内的任何字符，请使用'[.\n]'的模式。‘s.y’能匹配到’sky’、’say’等’s’和’y’之间含有任意一个字符的字符串[…]匹配在方括号内的任意字符。使用’-’界定匹配范围[abc]匹配a、b或c。[a-z]匹配任意字母，[0-9]匹配任意数字。[^…]匹配不在方括号内的任意字符。界定符‘-’的使用同[…]。[^abc]匹配除a、b、c以外的其他字符。*匹配一个或多个在它前面的字符。x*匹配任意数量的x字符，[0-9]*匹配任意数量的数字，而.*匹配任意数量的任意字符。p1|p2|p3匹配p1或p2或p3。'foot|d'能匹配'foot'或'food'，'foo（t|d）'则匹配'zood'或'food'。+匹配前面的子表达式一次或多次。'zo+'能匹配'zo'以及'zoo'，但不能匹配'z'。+等价于{1,}。{n}n是一个非负整数。匹配确定的n次。'o{2}'不能匹配'Bob'中的'o'，但是能匹配'food'中的两个o。{n,m}m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次。‘o{2,3}’能匹配‘food‘或’foood’中的’o’。谢谢观看新编数据库技术—MySQL北方工业大学逻辑运算符运算符作用NOT或！逻辑非AND或&&逻辑与OR或||逻辑或XOR逻辑异或AF=^A0110ABF=AB000010100111ABF=A+B000011101111ABF=^AB+A^B000011101110非与或异或01任务输入并执行以下SELECT语句，检验自己的判断是否正确。SELECTNOT10,NOT(1-1),!-5,!NULL,!1+1,NOT(1+1);SELECT1AND-1,1AND0,1&&NULL,0&&NULL，0||NULL;SELECT1OR2,1OR-1OR0,1ORNULL,0ORNULL,NULLORNULL;SELECT1XOR1,0XOR0,1XOR0,1XORNULL,1XOR1XOR1;02分析使用逻辑运算符对操作数进行逻辑判断时要掌握运算规则。如果存在一个数据为NULL且没有数据为0，则结果返回NULL。03实施视频演示。案例7.9逻辑运算符谢谢观看新编数据库技术—MySQL北方工业大学或位运算符AF=^A0110ABF=AB000010100111ABF=A+B000011101111ABF=^AB+A^B000011101110运算符作用~按位非&按位与|按位或^按位异或!取反<<

左移>>

右移与非异或左移4<<24十进制转二进制100100数字向左移两位右边用0补齐1000010000二进制16转十进制右移16>>216十进制转二进制1000010000数字向左移两位00100右边用0补齐100二进制4转十进制01任务输入并执行以下SELECT语句，检验自己的判断是否正确。SELECT10|15,9|4|2;SELECT10&15,9&4&2;SELECT1^0,1^1,10^15;02分析进行位运算前需要先把十进制数转化为二进制数，然后按位操作。03实施视频演示。案例7.10或、与、异或运算符10|1510十进制转二进制101015十进制转二进制11111010111111111111十进制转二进制15谢谢观看新编数据库技术—MySQL北方工业大学01任务输入并执行以下SELECT语句，检验自己的判断是否正确。SELECT1<<2,4<<2;SELECT16>>2,1>>1;SELECT5&~1;02分析进行位运算前需要先把十进制数转化为二进制数，然后按位操作。03实施视频演示。案例7.11左移、右移、取反运算符左移1<<21十进制转二进制11数字向左移两位右边用0补齐100100十进制转二进制4谢谢观看新编数据库技术—MySQL北方工业大学MySQL编程基础条件语句05条件控制语句——IF条件语句单分支IF结构双分支IF结构多分支IF结构01任务（1）创建一个PROCEDURE，命名为ex_IF，设置参数score，数据类型设为FLOAT。（2）使用IF条件语句对score进行分级，判断依据是：90及以上为A等，以A计；80-90（含80）为B等，以B计；70-80（含70）为C等，以C计；60-70（含60）为D等，记为D；60以下不及格，记为F。02分析创建的PROCEDURE，即存储过程（第八章学习的内容）。这里主要关注在BEGIN/END语句块中套用IF结构进行条件判断的SQL语句。03实施视频演示。案例7.12用IF实现成绩等级判断SQL代码DELIMITER$CREATEPROCEDUREex_IF(scoreFLOAT)BEGINDECLAREchCHAR;IFscore>=90

THENSETch='A';ELSEIFscore>=80

THENSETch='B';ELSEIFscore>=70THENSETch='C';ELSEIFscore>=60

THENSETch='D';ELSESETch='F';ENDIF;SELECTch;END$DELIMITER;CALLex_IF(66);谢谢观看新编数据库技术—MySQL北方工业大学CASE语句用于实现比IF语句更为复杂的条件判断，多数情况可以用多分支IF结构代替。相比较而言，CASE结构更清晰易懂。CASE语句的用法格式：case表达式whenvalue1then语句块1;[whenvalue2then语句块2;]…[else语句块n;]endcase;条件控制语句——CASE语句01任务（1）创建一个PROCEDURE，命名为ex_CASE，设置参数score，数据类型设为FLOAT。（2）使用CASE条件语句对score进行分级，判断依据是90及以上为A等，以A计；80-90（含80）为B等，以B计；70-80（含70）为C等，以C计；60-70（含60）为D等，记为D；60以下不及格，记为F。02分析通过创建的存储过程，掌握在BEGIN/END语句块中套用CASE结构进行条件判断。03实施视频演示。案例7.13用CASE实现成绩等级判断SQL代码DELIMITER$CREATEPROCEDUREex_CASE(scoreFLOAT)BEGINDECLAREchCHAR;CASEWHENscore>=90

THENSETch='A';WHENscore>=80THENSETch='B';WHENscore>=70

THENSETch='C';WHENscore>=60THENSETch='D';ELSESETch='F';ENDCASE;SELECTch;END$DELIMITER;CALLex_CASE(56);谢谢观看新编数据库技术—MySQL北方工业大学MySQL编程基础循环语句05满足条件表达式，会执行循环体中的语句；直到不满足条件时，跳出循环。WHILE语句的用法格式如下：while循环条件do循环体;endwhile;循环语句——WHILE01任务（1）创建PROCEDURE，并插入参数Insertcount，数据类型设为INT。（2）声明所需变量total、i，并分别设置DEFAULT为0和1.（3）在是否使用标签的情况下利用while语句进行求和运算。02分析创建PROCEDURE，声明变量并设置DEFAULT，之后使用while语句实现循环。请注意，在循环中可以配合iterate和leave控制循环的有限执行，其中iterate表示（继续）迭代，而leave则表示跳出循环，不论是否达到终止循环的条件。03实施视频演示。案例7.14有标签和无标签WHILE循环语句实例谢谢观看新编数据库技术—MySQL北方工业大学01任务（1）创建PROCEDURE，并插入参数InsertCount，数据类型设为INT。（2）声明所需变量total、i，并分别设置DEFAULT为0和1。（3）使用repeat语句进行求和运算。02分析创建PROCEDURE，声明变量total、i，并设置DEFAULT，利用repeat语句实现循环。请注意until的语法表达，在repeat循环中，仅当until的条件得到满足时才结束循环，这点和while有所不同。03实施视频演示。案例7.15REPEAT循环语句实例循环语句——LOOP语句LOOP语句相当于while(true)，因其没有停止循环的条件，所以其内部语句块会被重复执行，直到使用leave（相当于break）退出循环语句。LOOP语句的用法格式如下：loop循环体;if条件表达式thenleave;endif;endloop;谢谢观看新编数据库技术—MySQL北方工业大学循环语句——LOOP语句LOOP语句相当于while(true)，因其没有停止循环的条件，所以其内部语句块会被重复执行，直到使用leave（相当于break）退出循环语句。LOOP语句的用法格式如下：loop循环体;if条件表达式thenleave;endif;endloop;01任务（1）创建PROCEDURE，代入参数insertCount，数据类型设为INT。（2）声明所需变量total、i，并分别设置DEFAULT为0和1.（3）使用loop语句进行求和运算，设置循环条件实现累加1~10。02分析创建PROCEDURE，声明变量total、i，并分别设置DEFAULT，利用loop语句实现循环。请注意跳出循环的条件设置。03实施视频演示。案例7.16

LOOP循环语句实例循环总结WHILEREPEATLOOP控制循环：ITERATELEAVE谢谢观看新编数据库技术—MySQL北方工业大学第8章复用和存储自动化游标02存储过程和函数01触发器03第8章 复用和存储自动化存储过程01游标03函数02触发器0401理解存储过程和函数的基本概念掌握存储和函数的基本用法02理解游标的基本概念掌握游标的基本用法03理解触发器的基本概念掌握触发器的基本用法学习目标存储过程和函数01存储过程和函数都是封装和复用思想在SQL层面的具体实现概述SQL中的函数是完成某种特定功能的多条SQL语句集合，与其它编程语言中的函数在机制上是一样的。存储过程与函数很相似，它也是若干条SQL语句的集合，用于完成特定的功能。那么为什么还要专门设计存储过程这一数据库对象呢？一方面，存储过程体现更多与数据库相关的特质，比如存储过程可以控制事务，而函数通常不可以；另一方面，二者适用场景、调用机制不同，配合使用可以更有效地解决问题。总体而言，函数适合轻量级计算，而存储过程适合处理更复杂的数据库任务。谢谢观看新编数据库技术—MySQL北方工业大学01任务(1)使用关键字DELIMITER修改MySQL语句分隔符为&&，创建存储过程select_status，查询图书表（tb_book）索书号（barcode）为‘D924.01’的书籍状态（status）。(2)使用关键词CALL调用存储过程select_status。(3)使用DROPPROCEDURE语句删除存储过程select_status。02分析存储过程将有限的SQL语句组成一个集合从而实现一种特定的功能。MySQL的语法规定使用BEGIN和END作为存储过程语句体开始和结束的标志。在问题(1)中使用关键词CREATEPROCEDURE定义了一个名为select_status的存储过程并将存储过程的主体存放在BEGIN和END之间，存储过程的主体由有限的SQL语句组成，用户可根据需要编写相应的SQL语句实现所需功能。03实施视频演示。案例8.1创建、调用和删除存储过程SQL代码#使用关键词DELIMITER临时重新定义分隔符以避免冲突DELIMITER

&&

CREATE

PROCEDURE

select_status()

BEGIN

#存储过程的主体由有限的SQL语句组成

SELECT

status

FROM

tb_book

WHERE

barcode='D924.01';

END

&&

DELIMITER

;

#使用关键字CALL调用存储过程，返回了存储过程中的查询结果集CALL

select_status();

DROP

PROCEDURE

IF

EXISTS

select_status;

谢谢观看新编数据库技术—MySQL北方工业大学01任务(1)使用关键字DELIMITER修改MySQL语句分隔符为&&，创建函数select_barcode，查询借阅表（tb_record）借阅号（rid）为5的索书号（barcode）。(2)使用关键词SELECT调用函数select_barcode。(3)使用DROPFUNCTION语句删除函数select_barcode。02分析一般来说，函数的实现的功能针对性比较强。自定义函数用SELECT调用，这不同于存储过程，因此它可以作为查询语句的一个部分被调用。使用关键词DROPFUNCTION删除自定义函数。03实施视频演示。案例8.2创建、调用和删除函数SQL代码DELIMITER

&&

CREATE

FUNCTION

select_barcode()

RETURNS

CHAR(9)

DETERMINISTIC

–

声明不会改变数据

BEGIN

RETURN(SELECT

barcode

FROM

tb_record

WHERE

rid=5);

END

&&

DELIMITER

;

SELECT

select_barcode();

DROP

FUNCTION

select_barcode;

可被外部程序调用不包含RETURN语句存储过程的参数有IN、OUT、INOUT三种模式关键词CALL调用存储过程可被外部程序和其它SQL语句调用只有输入参数，且必须声明函数返回类型直接对函数进行调用自定义函数至少包含一条RETURN语句，且只能够返回单一变量谢谢观看新编数据库技术—MySQL北方工业大学01任务(1)创建存储过程return_ISBN,携带一个CHAR类型的IN模式参数，一个INT类型的OUT模式参数，使用IN模式参数传入索书号（barcode)检索图书表(tb_book)对应索书号的ISBN号（ISBN），OUT模式参数返回ISBN号。(2)创建存