上一片介绍了一些基本的SQL的增删改查，这一片会介绍一些进阶的SQL语句使用。
MySQL中的视图
视图是什么？当我们总是查询几张表的某个字段时，可以创建一张虚拟表，把这几个字段写入这个虚拟的表，这样之后，再查询这些字段，我们可以直接查询这个虚拟的表，这个虚拟的表就叫做视图。（不知道说清没，先记一句，视图是一个虚拟的表，）
创建视图的定义：
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION] #CREATE： 表明是创建视图。
#[OR REPLACE]：表明若视图存在时，则替换视图的定义，不存在则创建。
＃[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
UNDEFINED：让MySQL选择使用哪种算法。
MERGE：直接从物理表中根据定义取数据。
TEMPTABLE：把检索的结果放入临时表，然后再执行查询。
#DEFINER = { user | CURRENT_USER ：视图的定义者
#SQL SECURITY { DEFINER | INVOKER }：指定谁有权限来执行，definer表示定义者自己执行，invoker：调用者可以执行。默认是definer
#WITH [CASCADED | LOCAL] CHECK OPTION ]
CASCADED：表示视图创建时要满足所有视图和表的条件。
LOCAL：更新视图时，满足视图本身的定义条件即可。
这个参数的使用，可以参考https://blog.csdn.net/luyaran/article/details/81018763
因为视图在实际中不常用，在这里我们仅仅使用一个例子来说明视图的用法而已。
#创建视图，这是一个单表视图
CREATE
OR REPLACE ALGORITHM = MERGE VIEW emp_info (emp_no, NAME, gender) AS SELECT
emp_no,
concat(first_name, "", last_name) AS full_name,
gender
FROM
employees WITH CASCADED CHECK OPTION；
#查询视图
mysql> select * from emp_info limit 5;
+--------+------------------+--------+
| emp_no | name | gender |
+--------+------------------+--------+
| 10001 | GeorgiFacello | M |
| 10002 | BezalelSimmel | F |
| 10003 | PartoBamford | M |
| 10004 | ChirstianKoblick | M |
| 10005 | KyoichiMaliniak | M |
+--------+------------------+--------+
5 rows in set (0.00 sec) #对视图的增，删，改会影响到基表的，因为视图只是一个虚拟表，而视图中的数据，都是从基表而来的。
#查看视图
mysql> show create table emp_info\G #看到的是视图的定义
*************************** 1. row ***************************
View: emp_info
Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`172.16.100.19` SQL SECURITY DEFINER VIEW `emp_info` AS select `employees`.`emp_no` AS `emp_no`,concat(`employees`.`first_name`,'',`employees`.`last_name`) AS `name`,`employees`.`gender` AS `gender` from `employees` WITH CASCADED CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec) mysql> show table status like "emp_info"\G #视图，全部为空，只有最后一个comment值为VIEW
*************************** 1. row ***************************
Name: emp_info
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec) mysql> select * from information_schema.views where TABLE_NAME="emp_info"\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: emp_info
VIEW_DEFINITION: select `employees`.`employees`.`emp_no` AS `emp_no`,concat(`employees`.`employees`.`first_name`,'',`employees`.`employees`.`last_name`) AS `name`,`employees`.`employees`.`gender` AS `gender` from `employees`.`employees`
CHECK_OPTION: CASCADED
IS_UPDATABLE: YES
DEFINER: root@172.16.100.19
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec) #删除视图
DROP VIEW emp_info;
视图的特点【摘抄自：https://blog.csdn.net/yu0_zhang0/article/details/78223259】：
- 1 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的关系。
- 2 视图是由基本表（实表）产生的表（虚表）。
- 3 视图的建立和删除不影响基本表。
- 4 对视图的内容更新（添加、删除、修改）直接影响基本表。
- 5 当视图来自多个基本表时，不允许添加和删除。
因为视图用的不多，因此视图不会过多的介绍，如果想了解更多的关于视图，上面引用的两个博客可以参考下！
触发器：
- 触发器对性能有损耗，应当非常慎重使用。
- 对于事务表，触发器执行失败则整个语句回滚。
- row格式主从复制，触发器不会在从库执行。
- 使用触发器时应防止递归执行。
触发器语法如下：
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body trigger_time: { BEFORE | AFTER } #在action之前或者之后执行 trigger_event: { INSERT | UPDATE | DELETE } #激活触发器的动作 trigger_order: { FOLLOWS | PRECEDES } other_trigger_name #当前触发条件可以激活不只一个触发器时，这个参数指定激活触发器的顺序，
follows表示当前定义的触发器在现有触发器之后执行，PRECEDES表示当前定义的触发器在现有触发器之前执行。
触发器实例：
有两个表在t1表中插入数据，在t2表中插入对t1表的操作动作，以及时间。
#触发器如下：
delimiter &&
create trigger record_action after insert #在t1表中插入之后触发触发器
on t1 for each row
begin
insert into t2 values("insert", now());
end
&&
delimiter ; #验证数据：
mysql> select * from t2;
Empty set (0.00 sec)
mysql> insert into t1 values(1,"yu"); #在t1表中插入一条数据
Query OK, 1 row affected (0.09 sec) mysql> select * from t2; #在t2表中查看触发器插入的数据
+-------------+---------------------+
| action_name | action_time |
+-------------+---------------------+
| insert | 2019-02-24 14:16:19 |
+-------------+---------------------+
1 row in set (0.00 sec)
mysql> drop trigger record_action; #删除触发器
Query OK, 0 rows affected (0.01 sec)
存储过程和存储函数
存储过程和存储函数的区别在于，存储函数返回值，但是存储过程不返回值。
定义如下：
#语句摘录自官网，因为这两个语法参数基本一样，因此合在一起
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body // PROCEDURE标识为存储过程 CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type //存储函数需要return返回值
[characteristic ...] routine_body //FUNCTION标识为存储函数 proc_parameter: //存储过程的参数，分别表示输入|输出 |输入输出 参数名 参数类型
[ IN | OUT | INOUT ] param_name type func_parameter: //存储函数的参数，参数名，参数类型
param_name type type: //存储函数return返回的是，任何合法的MySQL数据类型
Any valid MySQL data type characteristic: //说明routine_body数据类型
COMMENT 'string' //说明信息
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER } routine_body:
Valid SQL routine statement //任何合法的sql语句
characteristic参数各个取值介绍如下：
实例如下：
存储过程实例：写一个存储过程向表t1中插入数据。表结构如下：
mysql> desc t1;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int() | NO | PRI | NULL | auto_increment |
| concent | varchar() | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
rows in set (0.00 sec)
存储过程如下： 因为id字段为自增，只要插入concent字段即可。首先产生一个随机整数m，然后利用substring函数生成随机长度的字符rand_char,把rand_char的值插入到表中。
delimiter &&
create procedure insert_data(in count_sum int)
language sql
begin
declare m int ;
declare i int default ;
declare rand_char varchar() ;
while i < count_sum do
select cast( floor(rand()*) as signed) into m;
select substring(MD5(RAND()),,m) into rand_char;
insert into t1(concent) values(rand_char);
set i = i+;
end while;
end &&
delimiter ;
执行结果如下：【因为进行null的限制，因此插入的数值可能为空】【注意上面cast函数转换为整型时，不能使用int，必须使用signed类型。】
mysql> select * from t1;
+----+---------+
| id | concent |
+----+---------+
| | yu |
| | zhang |
| | gu |
+----+---------+
rows in set (0.00 sec) mysql> call insert_data();
Query OK, row affected (0.08 sec) mysql> select * from t1;
+----+-----------+
| id | concent |
+----+-----------+
| | yu |
| | zhang |
| | gu |
| | |
| | a234d |
| | e50351bf0 |
| | fc |
| | 40e |
| | dac02c4 |
| | b2e8696d5 |
| | 392cb5 |
| | e |
| | |
| | abdab2 |
| | |
| | 26f0 |
| | 6fda4 |
| | 1854e0 |
| | a61433f5 |
| | |
| | |
| | |
| | |
+----+-----------+
rows in set (0.00 sec) mysql>
存储过程执行结果
存储函数的示例
表的数据来自mysql官方的测试数据库。存储函数如下，主要是输入工号，返回最新的薪水【注意是最新的薪水，而不是最高的薪水】
delimiter &&
create function query_info(emp_id int)
returns int
deterministic
begin
return (
SELECT
salary
FROM
employees.salaries
WHERE
from_date = (SELECT
MAX(from_date)
FROM
employees.salaries
WHERE
emp_no = emp_id)
AND emp_no = emp_id
);
end &&
delimiter ;
执行过程如下：
mysql> select query_info("");
+---------------------+
| query_info("") |
+---------------------+
| |
+---------------------+
row in set (0.00 sec)
mysql> select query_info("");
+---------------------+
| query_info("") |
+---------------------+
| |
+---------------------+
row in set (0.00 sec)
mysql>
查看存储过程和存储函数
mysql> show create function query_info\G #方法1
*************************** . row ***************************
Function: query_info
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`%` FUNCTION `query_info`(emp_id int) RETURNS int()
DETERMINISTIC
begin
return (
SELECT
salary
FROM
employees.salaries
WHERE
from_date = (SELECT
MAX(from_date)
FROM
employees.salaries
WHERE
emp_no = emp_id)
AND emp_no = emp_id
);
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
row in set (0.00 sec)
mysql> select * from information_schema.routines where ROUTINE_NAME = "query_info"; #方法2 #查看存储过程或存储函数的状态
mysql> show function status like "query_info"; #存储过程使用procedure
+-----------+------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----------+------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| employees | query_info | FUNCTION | root@% | 2019-02-24 16:31:07 | 2019-02-24 16:31:07 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+-----------+------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
条件判断
if语句
if条件语句的基本格式如下：
IF search_condition THEN
statement_list
[ELSEIF search_condition THEN]
statement_list ...
[ELSE
statement_list]
END IF
上面的存储过程，我们没有对插入的值进行非空判断，加入判断如下：
delimiter &&
create procedure insert_data(in count_sum int)
language sql
begin
declare m int ;
declare i int default ;
declare rand_char varchar() ;
while i < count_sum do
select cast( floor(rand()*) as signed) into m;
select substring(MD5(RAND()),,m) into rand_char;
if rand_char is null
then
set rand_char = "a";
else
insert into t1(concent) values(rand_char);
end if;
set i = i+;
end while;
end &&
delimiter ;
加入了判断之后，发现还是会插入空值，不知道是哪点的问题。这里先学会if条件判断的使用
if表达式
通过实例来说明用法：
mysql> set @name="wxz"; #定义一个会话变量
Query OK, rows affected (0.00 sec) mysql> select if (@name="wxz", "","") as result; #若是第一个表达式值为ture，则返回第二个表达式，否则返回第三个表达式
+--------+
| result |
+--------+
| |
+--------+
row in set (0.00 sec) mysql> select if (@name="jobs", "1","2") as result;
+--------+
| result |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec) mysql>
ifnull语句
IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns
expr2. mysql> select ifnull(,);
+-------------+
| ifnull(,) |
+-------------+
| |
+-------------+
row in set (0.00 sec) mysql> select ifnull(,); #注意数字0也是数值，非空的
+-------------+
| ifnull(,) |
+-------------+
| |
+-------------+
row in set (0.00 sec) mysql> select ifnull(NULL,); #空返回第二个表达式
+----------------+
| ifnull(NULL,) |
+----------------+
| |
+----------------+
row in set (0.00 sec)
case语句
case语句有两种写法如下：
#第一种：数值比较
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE #第二种表达式比较
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
实例如下：
delimiter &&
create procedure insert_data(in count int)
language sql
begin
case count
when then insert into t2 values("update", now());
when then insert into t2 values("delete", now());
end case;
end &&
delimiter ; mysql> call insert_data();
Query OK, row affected (0.00 sec) mysql> select * from t2;
+-------------+---------------------+
| action_name | action_time |
+-------------+---------------------+
| insert | -- :: |
| delete | -- :: |
| update | -- :: |
+-------------+---------------------+
rows in set (0.00 sec)
循环语句
while循环
几乎在每一种程序语言中都会用到while循环，在上面我们写存储函数的时候用到了while循环，这里不再举例，仅列出语句格式。
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label] #标记开始和结束的label标签可以省略
loop循环
loop循环的基本格式如下，
[begin_label:] LOOP
statement_list
END LOOP [end_label]
loop循环没有跳出循环的语句，在程序中使用leave语句跳出循环。
delimiter &&
create procedure insert_data(in count int)
language sql
begin
declare i int default ;
test_loop :loop
set i = i + ;
insert into t1(concent) values(i);
if i > count then
leave test_loop;
end if;
end loop test_loop;
end &&
delimiter ;
执行：
mysql> call insert_data();
Query OK, row affected (0.09 sec)
leave语句与iterate语句
在c语言和python中都有continue语句和break语句，break语句时跳出循环体，而continue语句是跳出本次循环进行下一次循环。而这里leave语句跳出了loop的循环体，作用就相当于break语句，而iterate语句就相当于continue语句，跳出本次循环。
delimiter &&
create procedure insert_data(in count int)
language sql
begin
declare i int default ;
test_loop :loop
set i = i + ;
if i = then
iterate test_loop;
elseif i > count then
leave test_loop;
else
insert into t1(concent) values(i);
end if;
end loop test_loop;
end &&
delimiter ;
如上当i=6的时候会跳出本次循环（也就是6不会插入到表中），而当i>10的时候会结束循环。
mysql> call insert_data();
Query OK, row affected (0.04 sec) mysql> select concent from t1;
+---------+
| concent |
+---------+
| |
| |
| |
| |
| |
| |
| |
| |
| |
+---------+
rows in set (0.01 sec) mysql>
repeat语句
repeat语句时有条件控制的循环语句，当满足特定条件时，就会跳出循环语句。
repeat语句还可以作为表达式使用：
mysql> select repeat("a",);
+---------------+
| repeat("a",) |
+---------------+
| aaa |
+---------------+
row in set (0.00 sec)
repeat语句格式如下：
[begin_label:] REPEAT
statement_list
UNTIL search_condition #表示跳出循环的条件
END REPEAT [end_label]
#实例如下：
delimiter &&
create procedure insert_data(in count int)
language sql
begin
declare i int default ;
test_repeat: repeat
insert into t1(concent) values(repeat("a",i));
set i = i + ;
until i = count
end repeat test_repeat;
end &&
delimiter ;
结果如下：
mysql> call insert_data();
Query OK, row affected (0.05 sec)
mysql> select * from t1;
+-----+-----------+
| id | concent |
+-----+-----------+
| | a |
| | aa |
| | aaa |
| | aaaa |
| | aaaaa |
| | aaaaaa |
| | aaaaaaa |
| | aaaaaaaa |
| | aaaaaaaaa |
+-----+-----------+
rows in set (0.00 sec) mysql>