第38次文章：数据库结尾

2019 年 10 月 8 日
筆記

本周介绍一下数据库中的最后一种语言，TCL事务控制语言。以及存储过程和函数，还有最后的流程过程结构。顺利结束数据库的基础内容。同时祝各位同学国庆快乐呀！

下面首先补充上周文章（第37次文章：数据库基本语法）中的一个小尾巴~对于DDL语言最后的约束部分，我们再补充以下几点：

5、主键和唯一

（1）区别

一个表至多有一个主键，但是可以有多个唯一。
主键不允许为空，但是唯一可以为空，但是也只能有一个空值。

（2）相同点

都具有唯一性。
都支持组合键，但不推荐。

6、外键

（1）用于限制两个表的关系，从表的字段值引用了主表的某字段的值。

（2）外键列和主表的被引用列要求类型一致，意义一样，名称无要求。

（3）主表的被引用列要求是一个key（一般就是主键）。

（4）插入数据，先插入主表；删除数据，先删除从表。

可以通过以下两种方式来删除主表的记录

#方式一：级联删除  alter table stuinfo add constraint fk_stu_major foreign key(majorid) refferences major(majorid) on delete casecade;  #方式二：级联置空  alter table stuinfo add constraint fk_stu_major foreign key(majorid) refferences major(majorid) on delete set null;

tips：

列级约束可以在一个字段上追加多个约束条件，中间用空格隔开，没有顺序要求。

7、自增长列

（1）不用手动插入值，可以自动提供序列值，默认从1开始，步长为1

（2）设置自增长的方法

在列级约束后面增加约束条件：auto_increment_increment

（3）对起始值和步长的改变

如果要更改起始值，需要手动插入值；

如果要更改步长，需要设置系统变量：set auto_increment_increment=值;

（4）一个表至多有一个自增长列

（5）自增长列只能支持数值型

（6）自增长列必须为一个key

TCL语言

TCL全称为transaction concrol language 事务控制语言。

一、事务

1、含义

一个或一组SQL语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

2、事务的acid属性【面试题】

（1）原子性（atomicity）

事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

（2）一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

（3）隔离性（isolation）

一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

（4）持久性（durability）

一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

3、事务的创建

（1）隐式事务

事务没有明显的开启和结束的标记，本身就是一条事务可以自动提交。比如insert、update、delete语句delete from 表 where id=1;

（2）显式事务

事务具有明显的开启和结束的标记。

前提：必须先设置自动提交功能为禁用

set autocommit = 0;

步骤1：开启事务set autocommit=0;start transaction；#可以省略

步骤2：编写事务中的sql语句（select insert update delete）

语句1;

语句2;

savepoint 回滚点名；#设置回滚点：

….

步骤3：结束事务commit;提交事务rollback：回滚事务

回滚到指定的地方：rollback to 回滚点名;

4、并发事务

（1）事务的并发问题是如何发生的？

多个事务同时操作同一个数据库的相同数据时。

（2）并发问题有哪些？

脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据。

不可重复读：一个事务多次读取，结果不一样。

幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“插入”的数据。

（3）如何解决并发问题

通过设置隔离级别来解决并发问题。

（4）事务的隔离级别

mysql中默认第三个隔离级别 repeatable read

oracle中默认第二个隔离级别 read committed

5、查看与设置隔离级别

select @@tx_isolation;#查看隔离级别

set session | global transaction isolation level 隔离级别;#设置隔离级别

6、案例演示

#演示事务的使用步骤  CREATE TABLE account(    id INT,    username VARCHAR(20),    balance INT  );  INSERT INTO account VALUES(1,'张无忌',1000),(4,'赵敏',1000);    #案例1：提交事务  #开启事务  SET autocommit = 0;  START TRANSACTION;  #编写一组事务的语句  UPDATE account SET balance=500 WHERE username='张无忌';  UPDATE account SET balance=1500 WHERE username='赵敏';  #结束事务  COMMIT;    #案例2：回滚  #开启事务  SET autocommit = 0;  START TRANSACTION;  #编写一组事务的语句  UPDATE account SET balance=1000 WHERE username='张无忌';  UPDATE account SET balance=1000 WHERE username='赵敏';  #结束事务  ROLLBACK;    #2、演示savepoint的使用  SELECT * FROM account;  SET autocommit = 0;  DELETE FROM account WHERE id=1;  SAVEPOINT a;  DELETE FROM account WHERE id=4;  ROLLBACK TO a;    #3、delete和truncate在事务使用时的区别  #演示delete  SET autocommit=0;  START TRANSACTION;  DELETE FROM account;  ROLLBACK;  #演示truncate  SET autocommit=0;  START TRANSACTION;  TRUNCATE account;  ROLLBACK;

结果：

我们先创建表格account，并插入两行数据，效果如下：

在案例1中，我们完成两行命令之后，提交事务。最后的结果如下：

然后我们继续执行案例2，由于案例2执行之后，我们使用的结束符为回滚，所以表中的数据并不会被改变，所得结果如下：

可以发现，回滚之后的事务，并没有对之前表格有所改变，两行命令都没有得到执行后的效果。所以回滚之后，整个数据依旧处于之前的一致状态。这就是回滚。

后面我们继续演示一下savepoint的用法，将其回滚到指定的断点处。结果如下：

整个事务的，我们在中间设置了一个断点，并且在最后的事务结束地方指定回滚，所以我们的整个事务仅仅完成了前半部分。

在最后对delete和truncate进行演示的时候，使用delete语句时，原始表格可以回滚到初始状态，而truncate删除表格的话，即使在最后增加了rollback，但是整张表格依旧被删除了。这属于两种删除方式的区别。

tips：在对事务的理解时，我们着重需要注意到事务的4条特性，以及事务的开启和结束用法。

二、视图

1、含义

（1）mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。

（2）好处

简化sql语句
提高了sql的重用性
保护基表的数据，提高了安全性

2、创建

create view 视图名

查询语句;

3、修改

方式一：

create or replace view 视图名

查询语句;

方式二：

alter view 视图名

查询语句

4、删除

drop view 视图1，视图2 ，…

5、查看

show create view 视图名;

6、使用

insert，delete，update，select

【注意】视图一般用于查询，而不是更新，所以具备以下特点的视图都不允许更新：

（1）包含分组函数：distinct、group by、having、union

（2）join

（3）常量视图

（4）where后的子查询用到了from中的表

（5）用到了不可更新的视图

7、案例演示

我们用一道经典案例将上面的事务和视图串在一起进行一个统一讲解。案例如下所示：

#1、创建表Book，字段如下  /*  bid  整型，要求主键  bname  字符型，要求设置唯一主键，并且非空  price  浮点型，要求有默认值10  btypeId  类型编号，要求引用bookType 表中的id字段    已知bookType表（不用创建），字段如下：  id  name  */    CREATE TABLE book(    bid INT PRIMARY KEY,    bname VARCHAR(20) UNIQUE NOT NULL,    price DOUBLE DEFAULT 10,    btypeId INT,    CONSTRAINT fk_book_bookType FOREIGN KEY(btypeId)  REFERENCES bookType(id)  )    #2、开启事务  #向表中插入1行数据，并结束  SET autocommit = 0;  START TRANSACTION  INSERT INTO book VALUES(1,'千只鹤',20,1)  COMMIT;    #3、创建视图，实现查询价格大于100的书名和类型名  CREATE OR REPLACE VIEW v  AS  SELECT b.bname,bt.name  FROM book b JOIN bookType bt ON b.btypeId = bt.id  WHERE price>100;    #4、修改视图，实现查询价格在90~120之间的书名和价格  CREATE OR REPLACE VIEW v  AS  SELECT bname,price  FROM book  WHERE price BETWEEN 90 AND 120;    #5、删除刚才建的视图  DROP VIEW v;

tips：

（1）第一道题简单的回顾一下表格的创建。注意一点的，添加外键的时候，列级约束虽然语法不会报错，但是没有效果，所以外键需要添加在表级约束上。同时注意一下，上面我们所写创建表格的代码，属于一个模板，在创建表的时候，一般会将约束添加在列级约束上面，对于外键才会添加在标记约束上。

（2）第二道题主要回顾一下事务机制，需要注意的就是整个显式事务的开启与提交。首先依旧是首先关闭事务的自动提交，然后开启事务，编写事务语句，最后提交事务。

（3）第三题和第四题主要是视图的创建和修改，一般创建的时候使用的是create view 修改的时候使用的时候create or replace view ，但是个人习惯直接使用create or replace ，这样可以避免忘了是否已经创建相关视图，导致语法报错。这会具有更好的容错性。

（4）第五题主要涉及了视图的删除，所使用的语法和DDL语法是一致的，使用drop view ，该命令可以同时删除多个视图，使用逗号隔开。

8、视图和表的对比

变量

一、系统变量

1、基本内容

（1）说明

变量由系统提供的，不用自定义。

（2）基本语法

查看系统变量

show 【global|session】 variable 【like ''】；如果没有显式声明global还是session，则默认是session

查看指定的系统变量的值

select @@【global|session】.变量名；如果没有显式声明global还是session，则默认是session

为系统变量赋值

方式一：

set 【global|session】变量名=值;如果没有显式声明global还是session，则默认是session

方式二：

set @@global.变量名=值;为全局变量赋值

set @@变量名=值;为局部变量赋值

2、全局变量

（1）服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对所有连接（会话）有效。

（2）不能跨服务器，重启服务器后就失效了。

3、会话变量

（1）服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）。

（2）不能跨连接，在其他连接中就失效了。

4、简单演示

#1。全局变量  #（1）查看所有的全局变量  SHOW GLOBAL VARIABLES;  #（2）查看部分全局变量  SHOW GLOBAL VARIABLES LIKE '%char%';  #（3）查看某个指定的全局变量的值  SELECT @@global.autocommit;  #（4）为某个指定的全局变量赋值  SET @@global.autocommit = 2;  SET GLOBAL autocommit=3;    #2、会话变量  #（1）查看会话变量  SHOW SESSION VARIABLES;  SHOW VARIABLES;  #（2）查看部分的会话变量  SHOW SESSION VARIABLES LIKE '%char%';  SHOW VARIABLES LIKE '%cahr%';  #（3）查看某个指定的会话变量的值  SELECT @@tx_isolation;  SELECT @@session.tx_isolation;  #（4）为某个指定的会话变量赋值  #方式一：  SET @@tx_isolation = 'read_uncommitted';  #方式二：  SET SESSION tx_isolation = 'read_uncommitted';

tips：两种系统变量的语法可以说是完全一致，在不指定global和session的时候，默认为session。两种方式的使用，当使用“@@”符号的时候，就是区域.变量名，并不难记！对比着使用还是很简单的呀！

二、自定义变量

1、用户变量

（1）作用域：针对当前连接（会话）生效。

（2）位置：begin end 里面，也可以放在外面。

（3）使用

声明并赋值

set @变量名=值;

set @变量名:=值

select @变量名:=值

更新值

方式一：

set @变量名=值;

set @变量名:=值

select @变量名:=值

方式二：

select 值 into @变量名 from 表;

使用

select @变量名;

2、局部变量

（1）作用域：仅仅在定义它的begin end中有效

（2）位置：只能放在begin end中，而且只能放在第一句

（3）使用

声明

declare 变量名类型【default 值】;

赋值或更新

方式一：

set 变量名=值;

set 变量名:=值

select @变量名:=值

方式二：

select 值 into 变量名 from 表;

使用

select 变量名;

3、语法示例

#1、用户变量  #案例  #声明并初始化  SET @name = 'join';  SET @name = 100;  SET @count = 1;  #赋值  SELECT COUNT(*) INTO @count FROM myemployees.employees;  #查看  SELECT @count;

tips：在上面的案例中，给出了用户变量的使用方法。用户变量与系统变量的调用方法区别在于'@'符号的个数。而局部变量需要放在begin end中才会生效。所以我们仅仅举例用户变量，对于局部变量的使用，我们结合后面的存储过程和函数中来讲解。

存储过程和函数

一、基本概述

1、说明

存储过程和函数都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字。

2、好处

（1）提高重用性。

（2）SQL语句简单。

（3）减少了和数据库服务器连接的次数，提高了效率。

3、区别

（1）存储过程：可以有0个返回，也可以有多个返回，适合做批处理插入、批处理更新。

（2）函数：有且仅有1个返回，适合做处理数据后返回一个结果。

二、存储过程

1、创建

create procedure 存储过程名（参数模式参数名参数类型）

begin

存储过程体

end

注意：

（1）参数模式：in out inout，其中in可以省略。

（2）存储过程体的每一条SQL语句都需要用分号结尾。

2、调用

call 存储过程名（实参列表）

举例：

（1）调用in模式的参数

call 存储过程名（‘值’）;

（2）调用out模式的参数

set @name;

call 存储过程名（@name);

select @name；

（3）调用inout模式的参数

set @name=值；

call spl（@name）；

select @name；

3、查看

show create procedure 存储过程名；

4、删除

drop procedure 存储过程名；

5、案例

我们用几道存储过程的经典例题来将上面的语法串一下吧！

delimiter $  #一、创建存储过程，实现传入用户名和密码，插入到admin表中  SELECT * FROM admin;  CREATE PROCEDURE test1(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))  BEGIN    INSERT INTO admin(admin.username,admin.password) VALUES(username,PASSWORD);  END;  CALL test1('peng','123456');  SELECT * FROM admin;$    #二、创建存储过程或函数实现传入女神编号，返回女神名称和女神电话  SELECT * FROM beauty;  CREATE PROCEDURE test2(IN id INT,OUT beautyName VARCHAR(20),OUT phone VARCHAR(20))  BEGIN    SELECT b.name,b.phone INTO beautyName,phone    FROM beauty b    WHERE b.id = id;  END;  CALL test2(2,@bname,@p);  SELECT @bname,@p;$    #三、创建存储过程或函数实现传入两个女神生日，返回大小  CREATE PROCEDURE test3(IN beauty1 DATETIME,IN beauty2 DATETIME,OUT result VARCHAR(20))  BEGIN    SELECT IF(beauty1<beauty2,'女神1较大','女神2较大') INTO result;  END;  CALL test3('1990-10-1',NOW(),@result);  CALL test3('1990-10-1','1989-10-1',@result);  SELECT @result;    #四、创建存储过程或函数实现传入一个日期，格式化成××年××月××日并返回  CREATE PROCEDURE test4(IN tim1 DATETIME,OUT tim2 VARCHAR(50))  BEGIN    SELECT DATE_FORMAT(tim1,'%Y年%c月%e日') INTO tim2;  END;  CALL test4(NOW(),@nowdate);  SELECT @nowdate;$    #五、创建存储过程或函数，实现传入女神名称，返回：女神 and 男神 格式化的字符串  #如 传入：小昭   返回：小昭 and 张无忌  DROP PROCEDURE test5;  CREATE PROCEDURE test5(IN beautyname VARCHAR(20),OUT result VARCHAR(50))  BEGIN    SELECT CONCAT(beautyname,' AND ',IFNULL(bo.boyName,'null'))    FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id    WHERE b.name = beautyname    INTO result;  END;  CALL test5('苍老师',@result1);  SELECT @result1;$  CALL test5('赵敏',@result);  SELECT @result;$    #六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表中的记录  DROP PROCEDURE IF EXISTS test6;  CREATE PROCEDURE test6(IN startindex INT,IN num INT)  BEGIN    SELECT * FROM beauty    LIMIT startindex,num;  END;  CALL test6(3,4);$

tips：

（1）需要注意一点的是，在使用存储过程时，编写的存储体里面的语句都是需要使用分号结尾，为了区分整个存储过程与单独存储体的结束，我们需要自定义一个结束符，使用的定义语句为：delimiter '符号' 。在后面结束整个存储过程的时候，我们就需要使用自己定义好的符号来作为整个存储过程的结束。在上面的案例中，我们使用的是$符号作为结束符，所以每道题的后面都是以$符号结尾。

（2）对于有返回值的存储过程，我们需要在存储过程的外面提前定义一个用户变量，在调用存储过程的时候，将此用户变量传入到存储过程中，作为一个接收返回值的变量，最后通过查看此用户变量的方式来查看返回值。

三、函数

1、创建

create function 函数名（参数名参数类型） returns 返回类型

begin

函数体

end

注意：函数体中肯定需要有return语句

2、调用

select 函数名（实参列表）；

3、查看

show create function 函数名；

4、删除

drop function 函数名；

5、案例

#案例1：根据部门名，返回该部门的平均工资  /*  注意:  1.参数列表 包含两部分：  参数名 参数类型    2.函数体：肯定会有return语句，如果没有会报错  如果return语句没有放在函数体的最后，也不会报错，但是不建议    return 值;  3.函数体中仅有一句话，则可以省略begin end  4.使用delimiter语句设置结束标记  */  DROP FUNCTION myf3;  CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE(10,2)  BEGIN    SET @mean_sal = 0;#定义一个用户变量    SELECT AVG(salary) INTO @mean_sal    FROM employees e JOIN departments d ON e.department_id = d.department_id    WHERE d.department_name = deptName;    RETURN @mean_sal;  END;  SELECT myf3('IT');$    #案例2：创建函数，实现传入两个float，返回二者之和  CREATE FUNCTION myf4(a FLOAT,b FLOAT) RETURNS FLOAT  BEGIN    DECLARE c FLOAT;    SELECT a+b INTO c;    RETURN c;  END;  SELECT myf4(5.0,6.0);$

tips：

（1）在我们最开始学习mysql的时候，曾经介绍过一些基本的函数，比如avg，max，min等等，当时就说过函数的一个特点，函数必定会有且仅有一个返回值。所以我们自定义的函数同样会具有这些特点。

（2）由于我们的函数语法将输入和输出值的位置单独分开了，所以在我们使用相应语法的时候，不需要去指定每个参数的参数模式，只需要在固定的位置填写参数名以及参数类型就好了。

（3）由于函数需要有一个返回值，所以我们在方法体中，需要在最前面定义一个局部变量，作为传出数据，如案例2中所示。但是同样的，我们也可以通过定义一个用户变量来作为输出值，如案例1所示。

流程控制结构

一、基本分类

（1）顺序结构：程序从上往下依次执行。

（2）分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行。

（3）循环结构：程序满足一定条件下，重复执行一组语句。

二、分支结构

1、if函数

（1）功能：实现简单双分支

（2）语法：if（条件，值1，值2）

（3）位置：可以作为表达式放在任何位置

（4）执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

2、case结构

（1）功能：实现多分支

（2）语法1：类似于java中的switch语句，一般用于实现等值判断

case 表达式或字段

when 值1 then 语句1;

when 值2 then 语句2;

…

else 语句n；

end 【case】

（3）语法2：类似于java中的多重if语句，一般用于实现区间判断

case

when 条件1 then 语句1;

when 条件2 then 语句2;

…

else 语句n；

end 【case】

（4）位置

可以放在任何位置。如果放在begin end外面，作为表达式结合着其他语句使用；如果放在begin end里面，一般作为独立的语句使用。

（5）特点

可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end 中或begin end的外面；可以作为独立的语句去使用，只能放在begin end中。
如果when中的值满足或条件成立，则执行对应的then后面的语句，并且结束case；如果都不满足，则执行else中的语句或值
else可以省略，如果else省略了，并且所有when条件都不满足，则返回null。

3、if 结构

（1）功能：实现多分支

（2）语法：

if 条件1 then 语句1；

elseif 条件2 then 语句2；

…

else 语句n；

end if；

（3）位置：只能放在begin end中。

4、案例

#案例1：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A，80-90，显示B，60-80显示C，否则，显示D  DELIMITER $  DROP PROCEDURE IF EXISTS testp1;  CREATE PROCEDURE testp1(IN score INT,OUT grade VARCHAR(20))  BEGIN    CASE    WHEN score BETWEEN 90 AND 100 THEN SET grade='A';    WHEN score BETWEEN 80 AND 90 THEN SET grade='B' ;    WHEN score BETWEEN 60 AND 80 THEN SET grade='C';    ELSE SET grade='D';    END CASE;  END;  CALL testp1(85,@a);  SELECT @a;$    #案例2：根据传入的成绩，来显示等级，比如传入的成绩：90-100，返回A，80-90，返回B，60-80，返回C，否则，返回D  CREATE FUNCTION testf1(score INT) RETURNS  CHAR(1)  BEGIN    DECLARE grade CHAR;    IF score BETWEEN 90 AND 100 THEN SET grade='A';    ELSEIF score BETWEEN 80 AND 90 THEN SET grade='B';    ELSEIF score BETWEEN 60 AND 80 THEN SET grade='C';    ELSE SET grade='D';    END IF;    RETURN grade;  END;  SELECT testf1(85);$

tips：

（1）对于case语句，主要需要注意一下两种语法的区别，一个是作为等值进行判断，还有一个是作为条件语句进行判断。

（2）对于if结构，我们可以将其类比于java中的if函数进行使用。对于if函数，我们在前面的学习中已经讲解过用法，所以在上面的案例中并没有给出if函数的使用。

三、循环结构

1、基本说明

（1）位置：只能放在begin end中

（3）特点：都能实现循环结构

（3）对比

单重循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称。
loop 一般用于实现简单的死循环，一般需要搭配着leave关键字使用。
while 先判断后执行。
repeat 先执行后判断，无条件至少执行一次。

2、三种循环语法

（1）while

语法：

【名称：】while 循环条件 do

循环体

end while 【名称】；

（2）loop

语法：

【名称：】 loop

循环体

end loop【名称】；

（3）repeat

【名称：】 repeat

循环体

until 循环结束条件；

end repeat【名称】；

3、循环控制语句

leave：类似于java中的break，用于跳出所在循环体。
iterate：类似于java中的continue，用于结束本次循环，继续下一次。

4、案例

#添加leave语句  #案例：批量插入，根据次数插入到admin表中的多条记录，如果次数>20则停止  TRUNCATE TABLE admin;  CREATE PROCEDURE testp3(IN num INT)  BEGIN    DECLARE c INT DEFAULT 1;    a: WHILE c<=num DO      INSERT INTO admin(`username`,`password`) VALUES(CONCAT('xiaopeng',c),'66666');      IF c>=20 THEN LEAVE a;      END IF;      SET c=c+1;    END WHILE a;  END;$  CALL testp3(100);$    #添加iterate语句  #案例：批量插入，根据次数插入到admin表中的多条记录，仅仅插入偶数次  TRUNCATE TABLE admin;  DROP PROCEDURE IF EXISTS testp4;  CREATE PROCEDURE testp4(IN num INT)  BEGIN    DECLARE c INT DEFAULT 0;    a: WHILE c<=num DO      SET c=c+1;      IF MOD(c,2)!=0 THEN        ITERATE a;      END IF;      INSERT INTO admin(`username`,`password`) VALUES(CONCAT('xiao',c),'66666');    END WHILE a;  END;  CALL testp4(100);$

tips：

（1）上面的案例中，我们都在进行插入数据的操作，根据不同的条件，搭配使用条件控制语句。

（2）三种循环语句，使用最多的属于while结构，所以此处我们仅列举while结构语法进行讲解。其他的语法可以自己去模拟一下。