Mysql视图、触发器、存储过程、函数

1 Mysql视图

1.1 视图概述

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。

1.1.1 视图的概念

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

视图是存储在数据库中的查询的SQL语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。

总结：小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

1.1.2 视图的作用

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图的作用归纳为如下几点。

1、简单性

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2、安全性

视图的安全性可以防止未授权用户查看特定的行或列，使有权限用户只能看到表中特定行的方法，如下：

（1）在表中增加一个标志用户名的列。

（2）建立视图，使用户只能看到标有自己用户名的行。

（3）把视图授权给其他用户。

3、逻辑数据独立性

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。

（1）如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

（2）如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。

（3）如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

（4）如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而使数据库可以不动。

1.2 创建视图

创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。

1.2.1 查看创建视图的权限

创建视图需要具有CREATE VIEW的权限。同时应该具有查询涉及的列的SELECT权限。可以使用SELECT语句来查询这些权限信息。查询语法如下：

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';

参数说明：

Select_priv：属性表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。
Create_view_priv：属性表示用户是否具有CREATE VIEW权限；
mysql.user：表示MySQL数据库下面的user表。
用户名：参数表示要查询是否拥有权限的用户，该参数需要用单引号引起来。

示例：查询MySQL中root用户是否具有创建视图的权限。

SELECT * FROM mysql.user WHERE user='root';

1.2.2 创建视图

MySQL中，创建视图是通过CREATE VIEW语句实现的。其语法如下：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

参数说明：

ALGORITHM：可选项，表示视图选择的算法。
视图名：表示要创建的视图名称。
属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

示例：创建视图。

CREATE OR REPLACE VIEW view_user
AS
    SELECT id,name FROM tb_user;

示例：创建视图同时，指定属性清单。

CREATE OR REPLACE VIEW view_user (a_id,a_name)
AS
    SELECT id,name FROM tb_user;

创建视图时需要注意以下几点：

运行创建视图的语句需要用户具有创建视图（create view）的权限，若加了[or replace]时，还需要用户具有删除视图（drop view）的权限；
select语句不能包含from子句中的子查询；
select语句不能引用系统或用户变量；
select语句不能引用预处理语句参数；
在存储子程序内，定义不能引用子程序参数或局部变量；
在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用check table语句；
在定义中不能引用temporary表，不能创建temporary视图；
在视图定义中命名的表必须已存在；
不能将触发程序与视图关联在一起；
在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。

1.3 修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。

示例：修改视图

ALTER VIEW view_user
AS
    SELECT id,name FROM tb_user where id  in (select id from tb_user);

说明：ALTER VIEW语句改变了视图的定义，该语句与CREATE OR REPLACE VIEW语句有着同样的限制，如果删除并重新创建一个视图，就必须重新为它分配权限。

1.4 删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROP VIEW语句来删除视图。但是，用户必须拥有DROP权限。

示例：删除视图。

DROP VIEW IF EXISTS view_user;

1.5 MySQL视图中使用IF和CASE语句

在创建视图时，经常需要使用到MySQL的流程控制语句，如：IF语句和CASE语句。

示例：创建MySQL视图中使用IF和CASE语句。

（1）创建员工信息表。

— 判断数据表是否存在，存在则删除

DROP TABLE IF EXISTS tb_staff;

— 创建数据表

CREATE TABLE IF NOT EXISTS tb_staff
( 
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '编号',
    NAME VARCHAR(50) NOT NULL COMMENT '姓名',
    sex INT COMMENT '性别（1：男；2：女；）',
    dept_code VARCHAR(10) COMMENT '部门编号',
    is_post BIT COMMENT '是否在职（0：否；1：是）'
) COMMENT = '员工信息表';

（2）新增员工数据。

— 新增数据

INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_01',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_02',2,'1002',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_03',1,'1003',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_04',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_05',2,'1008',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_06',1,'1001',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_07',2,'1002',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_08',1,'1003',0);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_09',1,'1001',1);
INSERT INTO tb_staff(NAME,sex,dept_code,is_post) VALUES('pan_junbiao的博客_10',2,'1008',0);

查询员工信息表结果：

（3）创建员工视图，在视图中使用IF和CASE语句。

— 创建视图

CREATE OR REPLACE VIEW view_staff
AS
    SELECT id 
    ,NAME
    ,IF(sex=1,'男','女') AS sex_name
    ,CASE dept_code
        WHEN '1001' THEN '研发部'
        WHEN '1002' THEN '人事部'
        WHEN '1003' THEN '财务部'
        ELSE '其他'
    END AS dept_name
    ,IF(is_post,'在职','离职') AS is_post_name
    FROM tb_staff
;

查询员工视图结果：

1.6 Mysql中默认的视图

1.6.1 information_schema.tables视图

information_schema.tables视图常用列属性

DESC information_schema.TABLES
TABLE_SCHEMA    ---->所有数据库的库名
TABLE_NAME      ---->所有表的表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

1.6.2 information_schema.tables视图的案例说明

查询整个数据库中所有库和所对应的表信息

SELECT TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME)
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;

统计所有库下的表个数

SELECT TABLE_SCHEMA,COUNT(TABLE_NAME)
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;

查询所有innodb引擎的表及所在的库

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE ENGINE='innodb';

统计mysql数据库下每张表的磁盘空间占用

数据库下表所占用空间的计算

表中的平均行（字节）*表的行数+索引的占用空间大小
AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
单位为字节
SELECT TABLE_SCHEMA,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,"KB") AS size_KB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mysql';
#CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,"KB")
#拼接单位KB

统计每个数据库所占用的磁盘空间

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;

1.7 视图总结

2 触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

2.1 创建触发器

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

#准备表
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);
#创建触发器
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
    IF NEW.success = 'no' THEN #等值判断只有一个等号
            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
      END IF ; #必须加分号
END//
delimiter ;
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');
#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

2.2 使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

2.3 删除触发器

drop trigger tri_after_insert_cmd;

3 存储过程

3.1 介绍

SQL的存储过程和视图一样，都是对SQL代码进行封装，可以反复利用。它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。存储过程可以说是由SQL语句和流控制语句构成的语句集合，它和我们之前学到的函数一样，可以接受输入参数，也可以返回输出参数给调用者，返回计算结果。

使用存储过程的优点：

#1. 用于替代程序写的SQL语句，实现程序与sql解耦
#2. 基于网络传输，传别名的数据量小，而直接传sql数据量

使用存储过程的缺点：

#1. 程序员扩展功能不方便

补充：程序与数据库结合使用的三种方式

#方式一：
    MySQL：存储过程
    程序：调用存储过程
#方式二：
    MySQL：
    程序：纯SQL语句
#方式三：
    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）

3.2 创建简单存储过程（无参）

delimiter //
create procedure p1()
BEGIN
    select * from blog;
    INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
#在mysql中调用
call p1() 
#在python中基于pymysql调用
cursor.callproc('p1') 
print(cursor.fetchall())

3.3 创建存储过程（有参）

对于存储过程，可以接收参数，其参数有三类：
#in          仅用于传入参数用
#out        仅用于返回值用
#inout     既可以传入又可以当作返回值

#in传入参数
delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
BEGIN
    select * from blog where id > n1;
END //
delimiter ;
#在mysql中调用
call p2(3,2)
#在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())

#out返回值
delimiter //
create procedure p3(
    in n1 int,
    out res int
)
BEGIN
    select * from blog where id > n1;
    set res = 1;
END //
delimiter ;
#在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
call p3(3,@res);
select @res;
#在python中基于pymysql调用
cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())

#inout即可传入也可返回
delimiter //
create procedure p4(
    inout n1 int
)
BEGIN
    select * from blog where id > n1;
    set n1 = 1;
END //
delimiter ;
#在mysql中调用
set @x=3;
call p4(@x);
select @x;
#在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

3.4 执行存储过程

#在mysql中调用存储过程
-- 无参数
call proc_name()
-- 有参数，全in
call proc_name(1,2)
-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)
执行存储过程

3.5 删除存储过程

drop procedure proc_name;

3.6 关于存储过程使用的争议

尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在着很多争议，比如有些公司对于大型项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使用需求差别这么大呢？

我们得从存储过程的特点来找答案。

你能看到存储过程有很多好处。

首先存储过程可以一次编译多次使用。存储过程只在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。其次它可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。还有一点，存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。最后它可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。同时在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程，这是为什么呢？

存储过程虽然有诸如上面的好处，但缺点也是很明显的。

它的可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

其次调试困难，只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。

此外，存储过程的版本管理也很困难，比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

最后它不适合高并发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

了解了存储过程的优缺点之后，我想说的是，存储过程既方便，又有局限性。尽管不同的公司对存储过程的态度不一，但是对于我们开发人员来说，不论怎样，掌握存储过程都是必备的技能之一。

3.7 存储过程总结

4 函数

4.1 常用函数大全

MySQL中提供了许多内置函数，例如：

一、数学函数
    ROUND(x,y)
        返回参数x的四舍五入的有y位小数的值
    RAND()
        返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
    AVG(col)返回指定列的平均值
    COUNT(col)返回指定列中非NULL值的个数
    MIN(col)返回指定列的最小值
    MAX(col)返回指定列的最大值
    SUM(col)返回指定列的所有值之和
    GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果    
三、字符串函数
    CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接（自定义连接符）
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
    CONV(N,from_base,to_base)
        进制转换
        例如：
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。
    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。
    LOWER(str)
        变小写
    UPPER(str)
        变大写
    REVERSE(str)
        返回字符串 str ，顺序和字符顺序相反。
    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'
        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'
        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'
        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'
        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'
        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'
四、日期和时间函数
    CURDATE()或CURRENT_DATE() 返回当前的日期
    CURTIME()或CURRENT_TIME() 返回当前的时间
    DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
    DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
    DAYOFYEAR(date)   返回date是一年的第几天(1~366)
    DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
    FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
    HOUR(time)   返回time的小时值(0~23)
    MINUTE(time)   返回time的分钟值(0~59)
    MONTH(date)   返回date的月份值(1~12)
    MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
    NOW()    返回当前的日期和时间
    QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
    WEEK(date)   返回日期date为一年中第几周(0~53)
    YEAR(date)   返回日期date的年份(1000~9999)
    重点:
    DATE_FORMAT(date,format) 根据format字符串格式化date值
       mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
       mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
       mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
        ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
       mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
        ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
       mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
       mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'
五、加密函数
    MD5()    
        计算字符串str的MD5校验和
    PASSWORD(str)   
        返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
六、控制流函数            
    CASE WHEN[test1] THEN [result1]...ELSE [default] END
        如果testN是真，则返回resultN，否则返回default
    CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
        如果test和valN相等，则返回resultN，否则返回default
    IF(test,t,f)   
        如果test是真，返回t；否则返回f
    IFNULL(arg1,arg2) 
        如果arg1不是空，返回arg1，否则返回arg2
    NULLIF(arg1,arg2) 
        如果arg1=arg2返回NULL；否则返回arg1        
七、控制流函数小练习
#7.1、准备表
/*
Navicat MySQL Data Transfer
Source Server         : localhost_3306
Source Server Version : 50720
Source Host           : localhost:3306
Source Database       : student
Target Server Type    : MYSQL
Target Server Version : 50720
File Encoding         : 65001
Date: 2018-01-02 12:05:30
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(255) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(10) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `num` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(255) DEFAULT NULL,
  `s_age` int(10) DEFAULT NULL,
  `s_sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` int(10) NOT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');
#7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select  score.c_id,
          course.c_name, 
      sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
      sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
      sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
      sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;

第四章 Mysql视图、触发器、存储过程、函数