MySQL-04 高级特性

学习要点

MySQL系统函数
MySQL视图
MySQL存储过程
MySQL自定义函数
MySQL触发器
MySQL事务
MySQL事件

MySQL系统函数

数学函数

获取整数的函数：CEIL(x)、CEILING(x)、FLOOR(x)

CEIL(x)、CEILING(x)：返回不小于x的BIGINT整数值。

FLOOR(x)：向下取整，返回BIGINT。

获取随机数函数：RAND()、RAND(X)

返回[0,1]之间的浮点数。X用来产生重复的随机数。

上机练习1：#获得[1-99]之间的随机数

SELECT FLOOR(RAND()*98)+1;

ROUND(x)、ROUND(x，y)、TRUNCATE(x，y)

ROUND(x)四舍五入。

ROUND(x，y)返回接近x的数，保留小数点y位。y为负数则去除小数点左边y位。

TRUNCATE(x，y)返回舍去小数点后y位的数字。y为负数则去除小数点左边y位。

字符串函数

计算字符串字符数的函数：CHAR_LENGTH()
计算字符串长度的函数：LENGTH()
字母大小写转换函数：LOWER()、LCASE()、UPPER()、UCASE()
字符串比较函数：STRCMP(S1,S2) 相等返回0，S1<S2返回-1，否则返回1
字符串截取：SUBSTRING(Str，offset，len)。Offset为负数则从字符串结尾往字符串开始方向计算。
获取当前日期函数：CURDATE()、CURRENT_DATE()
获取当前时间函数：CRUTIME()、CURRENT_TIME()
获得当前时间：now()；
数据表中自动插入时间戳：字段类型设置为：timestamp 默认值设置为：default current_timestamp

日期和时间函数

获取当前日期函数：CURDATE()、CURRENT_DATE()
获取当前时间函数：CRUTIME()、CURRENT_TIME()
获得当前时间：now()；
数据表中自动插入时间戳：字段类型设置为：timestamp 默认值设置为：default current_timestamp

条件判断函数

IF(expr，v1，v2)：expr表达式成立，返回V1,否则返回V2。
IFNULL(v1,v2)：V1不为空，返回V1；否则返回V2。

上机练习2：修改学生信息返回，地址为null返回‘地址为空’

CASE函数

CASE 表达式

WHEN V1 THEN …

WHEN V2 THEN …

WHEN V3 THEN …

…

ELSE …

END

用法示例：

系统信息函数

获取MySQL版本号：VERSION()
获取当前进程的mysql访问id：CONNECTION_ID()

获取最后一个生成的ID的值的函数：LAST_INSERT_ID();

加密函数

PASSWORD(str)加密：系统加密使用，比如数据库管理员用户加密。
MD5(str)加密：应用程序使用。
ENCODE(被加密字符串,加密密码)，DECODE(已加密字符串，解密密码)

其他函数

IP地址与数字相互转换函数：INET_ATION(‘a.b.c.d’)、INET_NTION(num)

转换算法：a*256*256*256+b*256*256+c*256+d*1

MySQL视图

视图的定义

视图是一个虚拟表：表示一张表的部分数据或多张表的综合数据；其结构和数据是建立在对表的查询基础上。
实际上视图就是一条SELECT或者INSERT、UPDATE、DELETE语句。
视图中不存放数据。数据存放在视图所引用的原始表中。
一个原始表，根据不同用户的不同需求，可以创建不同的视图
MySQL从5.0版本开始支持视图。

为什么使用视图

不同的人员关注不同的数据
保证信息的安全性

视图的用途

筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库

如何创建视图

1、方式一：通过视图创建工具可视化创建视图

2、方式二：通过SQL语句创建视图

语法格式：

CREATE  [OR  REPLACE]  VIEW  view_name  AS  SELECT_statement

创建单表视图（支持增删改查操作）

创建多表视图（不推荐进行增删改）

查看视图

使用DESC查看视图结构

使用 [ SHOW TABLE STATUS LIKE ‘视图名’] 查看视图基本信息

使用 [SHOW CREATE VIEW ‘视图名’] 查看视图详细信息

从information_schema.views表中查看数据库所有已定义的视图

修改视图

CREATE  [OR REPLACE]  view_name  AS  SELECT_statement

语法同创建视图。

或者：

ALTER view_name  AS  SELECT_statement

更新视图

语法格式：

UPDATE 视图名称  SET 字段=value

注意：会更新原始表数据

从视图中delete一条记录，也将会删除原始表数据。

以下情况，无法通过视图更新操作：

1、视图中不包含基本表中的非空字段；

2、SELECT子句中使用了数学表达式；

3、SELECT子句中使用了聚合函数；

4、SELECT子句中使用了：DISTINCT,UNION,TOP,GROUP BY或者HAVING子句

删除视图

DROP  VIEW [ IF EXISTIS ] 视图名

上机练习3：创建视图vw_getresult，实现以下功能

MySQL存储过程

存储过程定义

存储过程就是一条或者多条SQL语句的集合，保存在服务器数据库里。

优点：

1、避免在网上从传输大量的SQL语句，提高网络速度；

2、防止SQL语句被截取和盗用，提高信息安全性；

3、存储过程保存在服务器端，已经被编译，执行效率高。

缺点：移植性差。

语法格式

CREATE  PROCEDURE sp_name ( [proc_parameter])

[characteristics … ]  routine_body

proc_parameter：存储过程参数，有三种类型

　　　　[ IN | OUT | INOUT ] proc_parameter

characteristics:存储过程特性。

　　　　包含语言、结果、SQL限制、权限、备注等信息的设置。

routine_body:用BEGIN … END 来表示的SQL代码块

创建无参存储过程

在控制台创建：

　　DELIMITER：将MySQL的结束符设置为//。因为存储过程中的“;”会被控制台终端当成SQL的结束符号。重新定义结束符号：DELIMITER ; //。

在navicat查询编辑器中创建存储过程

执行存储过程

删除存储过程

查看数据库中已定义的存储过程或者函数

查看存储过程定义

上机练习4：创建存储过程sp_getteaminfo()，实现球队信息的查询。

创建带输入参数的存储过程

创建

调用

创建带输入输出参数的存储过程

创建

调用

方式1：在控制台调用，设定自定义变量@变量名接收输出参数。

方式2：在navicat存储过程编辑器中输入参数运行，多个参数之间‘，’号隔开，字符串要使用单引号。

上机练习5：创建存储过程sp_getstudentnamebyno(IN inno,OUT name CHAR(20))，实现学生信息查询。

MySQL自定义函数

语法格式

CREATE  FUNCTION func_name ( [func_parameter])

RETURNS type

[characteristics … ]  routine_body

func_name自定义函数名
RETURNS type：函数返回的数据类型
func_parameter：自定义函数参数，有三种类型

　　　　[ IN | OUT | INOUT ] func_parameter

characteristics:存储过程特性。

　　　　包含语言、结果、SQL限制、权限、备注等信息的设置。

routine_body:用BEGIN … END 来表示的SQL代码块。

自定义函数示例

注意：

1、和存储过程不同，函数的参数不需要指定IN、OUT、INOUT。

2、函数内只有一条执行SQL语句，可以不使用BEGIN-END。否则需要BEGIN-END。

3、函数调用直接使用SELECT调用。

上机练习6：创建函数fn_getstudentnamebyno(inno INT)，实现学生信息查询。

MySQL触发器

触发器的定义

触发器与存储过程一样，都是存储在MySQL的一段程序。

存储过程的执行采用CALL调用的方式，而触发器的执行是由事件来触发执行的，这些事件包括：INSERT、UPDATE、DELETE。

语法格式

CREATE  TRIGGER  trigger_name  trigger_time  trigger_event

ON  table_name  FOR  EACH  ROW  trigger_stmt

参数说明：

trigger_name：触发器名称
trigger_time：触发时机，值为after或者before
trigger_event：触发事件——INSERT、UPDATE、DELETE
table_name：建立出发器的数据表
trigger_stmt：触发器程序体
FOR EACH ROW：通知触发器数据表行记录变更的时候触发

创建一条语句的触发器

创建表tgtest和触发器tg_sum_tritest_amount：

#创建ACCOUNT表

DROP TABLE IF EXISTS tgtest;

CREATE TABLE IF NOT EXISTS tgtest(

	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT'编号',

  amount INT UNSIGNED NOT NULL COMMENT'金额'

)ENGINE=INNODB DEFAULT CHARSET=UTF8;

#创建触发器：向tritest插入数据前，对amount字段进行求和

CREATE TRIGGER tg_sum_tritest_amount BEFORE INSERT

    ON tgtest

   FOR EACH ROW SET @sum=@sum+NEW.amount;

查看数据库中触发器:

SELECT * FROM information_schema.TRIGGERS;

测试触发器：

#测试触发器

SET @sum=0;

INSERT INTO tgtest VALUES(DEFAULT,3),(DEFAULT,5);

SELECT @sum;

测试结果：

删除触发器：

# 删除触发器

DROP TRIGGER tg_sum_tritest_amount;

创建多条执行语句的触发器

问题：有两张MyISAM类型的数据表，一张是商品表，一张是订单表，当增删改订单表的时候，如何使用触发器修改商品表的库存？

示例代码：

#创建商品表

DROP TABLE IF EXISTS good;

CREATE TABLE IF NOT EXISTS good(

 gid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '商品编号',

 gname char(20)  NOT NULL COMMENT '品名',

 gcount INT UNSIGNED NOT NULL COMMENT '库存'

)ENGINE=MyISAM  DEFAULT CHARSET=UTF8 AUTO_INCREMENT=100000;

#创建订单表

DROP TABLE IF EXISTS saleorder;

CREATE TABLE IF NOT EXISTS saleorder(

 oid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单编号',

 gid BIGINT UNSIGNED NOT NULL COMMENT '商品id',

 ocount INT UNSIGNED NOT NULL COMMENT '订单商品数量'

)ENGINE=MyISAM  DEFAULT CHARSET=UTF8 AUTO_INCREMENT=3502002016000;

# 商品表插入数据

INSERT INTO good(gname,gcount) VALUES('iphone x',100);

SELECT * FROM good;

# 创建订单时候修改商品表的库存

DROP TRIGGER tg_corder_mgood;

CREATE TRIGGER tg_corder_mgood AFTER INSERT ON saleorder

FOR EACH ROW

UPDATE good SET gcount=gcount-NEW.ocount WHERE gid=NEW.gid;

# 下单

INSERT INTO saleorder(gid,ocount) VALUES(100000,11); 

# 查看商品表和订单表的数据

SELECT * FROM good;

SELECT * FROM saleorder;

# 修改订单时候修改商品表的库存

DROP TRIGGER tg_morder_mgood;

CREATE TRIGGER tg_morder_mgood AFTER UPDATE ON saleorder

FOR EACH ROW

UPDATE good SET gcount=gcount+OLD.ocount-NEW.ocount WHERE gid=OLD.gid;

# 修改订单

UPDATE saleorder SET ocount=ocount-1 WHERE oid=3502002016000; -- 订单商品数量-1

SELECT * FROM good;

SELECT * FROM saleorder; 

# 删除订单的时候修改商品表的库存

DROP TRIGGER tg_dorder_mgood;

CREATE TRIGGER tg_dorder_mgood AFTER DELETE ON saleorder

FOR EACH ROW

UPDATE good SET gcount=gcount+OLD.ocount WHERE gid=OLD.gid;

# 删除订单

DELETE FROM saleorder WHERE oid=3502002016000;

SELECT * FROM good;

SELECT * FROM saleorder;

NEW和OLD：使用“NEW.字段名”和“OLD.字段名”的SQL语句变量称为过渡变量。

NEW：只能存在于INSERT语句中。“NEW.字段名”表示获取引起触发器触发的SQL语句中对应字段的值。

OLD：只能存在于DELETE语句中。“OLD.字段名”表示获取引起触发器触发的SQL语句对应数据表中相应记录对应字段的值。

只有在UPDATE语句中可以同时存在NEW和OLD关键字。

上机练习7：创建多条语句触发器，实现订单表的增删改的同时触发修改商品表信息。

MySQL事务

事务的定义

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元

事务的特性

事务必须具备以下四个属性，简称ACID 属性：

原子性（Atomicity）：事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行。
一致性（Consistency）：当事务完成时，数据必须处于一致状态。
隔离性（Isolation）：并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务。
永久性（Durability）：事务完成后,它对数据库的修改被永久保持。
注意：InnoDB和BDB引擎表才支持事务。
问题：解决转账问题。

事务综合示例：

USE mybank;

# 模拟银行数据表1

DROP TABLE IF EXISTS bank1;

CREATE TABLE IF NOT EXISTS bank1(

  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '账号',

  username CHAR(20) NOT NULL COMMENT '户名',

  amount INT UNSIGNED NOT NULL COMMENT '余额'

)ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1120553244123;

# 模拟银行数据表2

DROP TABLE IF EXISTS bank2;

CREATE TABLE IF NOT EXISTS bank2(

  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '账号',

  username CHAR(20) NOT NULL COMMENT '户名',

  amount INT UNSIGNED NOT NULL COMMENT '余额'

)ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=2234753244567;

# 两个银行分别初始化两个账户

INSERT INTO bank1(id,username,amount) VALUES(DEFAULT,'张三',800);

INSERT INTO bank2(id,username,amount) VALUES(DEFAULT,'李四',1200);

# 查询数据

SELECT * FROM bank1;

SELECT * FROM bank2;

#模拟转账

-- 张三转账850元给李四

UPDATE bank2 SET amount=amount+850 WHERE id=2234753244567; -- 李四

UPDATE bank1 SET amount=amount-850 WHERE id=1120553244123; -- 张三

/** 解决方案1 */

#关闭自动事务提交

SET AUTOCOMMIT =0; -- 1表示开启

UPDATE bank2 SET amount=amount+850 WHERE id=2234753244567; -- 李四

UPDATE bank1 SET amount=amount-850 WHERE id=1120553244123; -- 张三

ROLLBACK; -- 异常则回滚

COMMIT; -- 提交事务

/** 解决方案2 */

# 开启事务

SET AUTOCOMMIT =1; -- 自动提交事务打开

START TRANSACTION; -- 开始事务（事务打开的情况下需要调用该语句，否则无法回滚）

UPDATE bank2 SET amount=amount+850;

UPDATE bank1 SET amount=amount-850;

COMMIT; -- 提交事务（关闭事务）

ROLLBACK; -- 放弃事务（关闭事务）

# 折返点

SELECT * FROM bank1;

SET AUTOCOMMIT =0;

UPDATE bank1 SET amount=amount+1;

UPDATE bank1 SET amount=amount+1;

UPDATE bank1 SET amount=amount+1;

SAVEPOINT a_ponit;

UPDATE bank1 SET amount=amount+1;

UPDATE bank1 SET amount=amount+1;

UPDATE bank1 SET amount=amount+1;

UPDATE bank1 SET amount=amount+1;

ROLLBACK TO SAVEPOINT a_ponit;

ROLLBACK; -- 全部回滚

COMMIT; -- 提交事务

# 使用存储过程实现转账事务处理

-- 转账存储过程

DROP PROCEDURE IF EXISTS transamount;

CREATE PROCEDURE transamount ()

BEGIN

  DECLARE trans_error INT DEFAULT 0; -- 声明变量trans_error保存错误信息

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET trans_error = 1; -- 定义错误处理方式：continue或者exit，这里选择continue

  SET AUTOCOMMIT =0; -- 关闭MySQL事务自动提交

  START TRANSACTION; -- 开启事务

    UPDATE bank1 SET amount=amount-850;

    UPDATE bank2 SET amount=amount+850;

  IF trans_error = 1 THEN

         ROLLBACK; -- 错误则回滚

     SELECT '转账失败';

  ELSE

        COMMIT; -- 转账成功提交事务

   SELECT '转账成功';

  END IF;

  SET AUTOCOMMIT =1; -- 打开MySQL事务自动提交

END;

# 测试存储过程转账

SELECT * FROM bank1;

SELECT * FROM bank2;

CALL transamount();

上机练习8：创建存储过程实现转账事务要求。

MySQL事件

在系统管理或者数据库管理中，经常要周期性的执行某一个命令或者SQL语句。Mysql在5.1以后推出了事件调度器(Event Scheduler)，和linux的cron功能一样，能方便地实现 mysql数据库的计划任务，而且能精确到秒。使用起来非常简单和方便。

如何开启事件

查看mysql服务器上的事件是否开启

SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;

SHOW PROCESSLIST; -- 查看mysql服务器在运行线程

如果看到event_scheduler为on或者PROCESSLIST中显示有event_scheduler的信息说明就已经开启了事件。如果显示为off或者在PROCESSLIST中查看不到event_scheduler的信息，那么就说明事件没有开启，我们需要开启它。

如何开启事件？

通过动态参数修改

SET GLOBAL event_scheduler = ON;  //重启后失效

更改配置文件然后重启

my.ini文件的[mysqld]部分添加：event_scheduler=ON

通过制定事件参数启动

mysqld ... --event_scheduler=ON

Mysql事件的语法

创建事件语法

CREATE

    [DEFINER = { user | CURRENT_USER }]  EVENT  [IF NOT EXISTS] event_name  ON SCHEDULE schedule

    [ON COMPLETION [NOT] PRESERVE]  [ENABLE | DISABLE | DISABLE ON SLAVE]  [COMMENT 'comment']

    DO event_body; 

schedule:

    AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...]  [ENDS timestamp [+ INTERVAL interval] ...]

interval:

  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

参数说明：

DEFINER: 定义事件执行的时候检查权限的用户。

ON SCHEDULE schedule: 定义执行的时间和时间间隔。

ON COMPLETION [NOT] PRESERVE: 定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。

ENABLE | DISABLE | DISABLE ON SLAVE: 定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。

COMMENT 'comment': 定义事件的注释。

修改事件语法

ALTER

    [DEFINER = { user | CURRENT_USER }]  EVENT event_name  [ON SCHEDULE schedule]  [ON COMPLETION [NOT] PRESERVE]

    [RENAME TO new_event_name]  [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment']   [DO event_body]

删除事件语法

DROP EVENT [IF EXISTS] event_name

Mysql事件应用举例

简单应用

# 开启事件（MySQL服务重启失效）

SET GLOBAL event_scheduler = ON; 

# 创建事件测试表

DROP TABLE IF EXISTS etest;

CREATE TABLE IF NOT EXISTS etest (

  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

  `data` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000;

# 创建一个每隔10秒往test表中插入一条数据的事件（立刻开始运行）

DROP EVENT IF EXISTS ev_insert_data;

CREATE EVENT IF NOT EXISTS ev_insert_data ON SCHEDULE EVERY 10 SECOND

    ON COMPLETION PRESERVE

    DO INSERT INTO etest(id,data) VALUES(DEFAULT,NOW());

# 查询数据

SELECT * FROM etest;

# 创建一个1分钟后清空test表数据的事件

DROP EVENT  IF EXISTS  ev_clear_data;

CREATE EVENT IF NOT EXISTS ev_clear_data

    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE

    DO TRUNCATE TABLE etest;  

# 某一时刻清除数据

DROP EVENT  IF EXISTS  ev_clear_data_attime;

CREATE EVENT IF NOT EXISTS ev_clear_data_attime

    ON SCHEDULE AT TIMESTAMP '2018-10-10 00:00:00'

    DO TRUNCATE TABLE etest;  

# 某个时间段执行事件

DROP EVENT IF EXISTS ev_do_between_time;

CREATE EVENT IF NOT EXISTS ev_do_between_time

    ON SCHEDULE EVERY 3 SECOND

       STARTS '2018-08-22 21:49:00'

       ENDS '2018-08-22 21:49:00'+ INTERVAL  10 MINUTE

    ON COMPLETION PRESERVE

    DO INSERT INTO etest(id,data) VALUES(DEFAULT,NOW());

MySQL事件通常用来调用存储过程

# 调用存储过程(一分钟后转账)

DROP EVENT IF EXISTS ev_call_trans_proc;

CREATE EVENT IF NOT EXISTS ev_call_trans_proc

    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE

    DO call transamount();

SELECT * FROM bank1;

SELECT * FROM bank2;

巴特西

MySQL-04 高级特性

学习要点

MySQL系统函数

数学函数

上机练习1：#获得[1-99]之间的随机数

字符串函数

日期和时间函数

条件判断函数

上机练习2：修改学生信息返回，地址为null返回‘地址为空’

系统信息函数

加密函数

其他函数

MySQL视图

视图的定义

为什么使用视图

视图的用途

如何创建视图

查看视图

修改视图

更新视图

删除视图

上机练习3：创建视图vw_getresult，实现以下功能

MySQL存储过程

存储过程定义

语法格式

创建无参存储过程

上机练习4：创建存储过程sp_getteaminfo()，实现球队信息的查询。

创建带输入参数的存储过程

创建带输入输出参数的存储过程

上机练习5：创建存储过程sp_getstudentnamebyno(IN inno,OUT name CHAR(20))，实现学生信息查询。

MySQL自定义函数

语法格式

自定义函数示例

上机练习6：创建函数fn_getstudentnamebyno(inno INT)，实现学生信息查询。

MySQL触发器

触发器的定义

语法格式

创建一条语句的触发器

创建多条执行语句的触发器

上机练习7：创建多条语句触发器，实现订单表的增删改的同时触发修改商品表信息。

MySQL事务

事务的定义

事务的特性

事务综合示例：

上机练习8：创建存储过程实现转账事务要求。

MySQL事件

如何开启事件

Mysql事件的语法

Mysql事件应用举例

最新文章

热门文章