MySQL中SQL语句2
上一片介绍了一些基本的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(11) | NO | PRI | NULL | auto_increment | | concent | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 2 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 0 ; declare rand_char varchar(30) ; while i < count_sum do select cast( floor(rand()*10) as signed) into m; select substring(MD5(RAND()),1,m) into rand_char; insert into t1(concent) values(rand_char); set i = i+1; end while; end && delimiter ;
执行结果如下：【因为进行null的限制，因此插入的数值可能为空】【注意上面cast函数转换为整型时，不能使用int，必须使用signed类型。】
mysql> select * from t1; +----+---------+ | id | concent | +----+---------+ | 1 | yu | | 2 | zhang | | 3 | gu | +----+---------+ 3 rows in set (0.00 sec) mysql> call insert_data(20); Query OK, 1 row affected (0.08 sec) mysql> select * from t1; +----+-----------+ | id | concent | +----+-----------+ | 1 | yu | | 2 | zhang | | 3 | gu | | 4 | | | 5 | a234d | | 6 | e50351bf0 | | 7 | fc | | 8 | 40e | | 9 | dac02c4 | | 10 | b2e8696d5 | | 11 | 392cb5 | | 12 | e | | 13 | | | 14 | abdab2 | | 15 | | | 16 | 26f0 | | 17 | 6fda4 | | 18 | 1854e0 | | 19 | a61433f5 | | 20 | 8 | | 21 | 64 | | 22 | 9 | | 23 | | +----+-----------+ 23 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("10002"); +---------------------+ | query_info("10002") | +---------------------+ | 72527 | +---------------------+ 1 row in set (0.00 sec) mysql> select query_info("23452"); +---------------------+ | query_info("23452") | +---------------------+ | 66727 | +---------------------+ 1 row in set (0.00 sec) mysql>
查看存储过程和存储函数
mysql> show create function query_info\G #方法1 *************************** 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(11) 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 1 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 0 ; declare rand_char varchar(30) ; while i < count_sum do select cast( floor(rand()*10) as signed) into m; select substring(MD5(RAND()),1,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+1; end while; end && delimiter ;
加入了判断之后，发现还是会插入空值，不知道是哪点的问题。这里先学会if条件判断的使用
if表达式
通过实例来说明用法：
mysql> set @name="wxz"; #定义一个会话变量 Query OK, 0 rows affected (0.00 sec) mysql> select if (@name="wxz", "1","2") as result; #若是第一个表达式值为ture，则返回第二个表达式，否则返回第三个表达式 +--------+ | result | +--------+ | 1 | +--------+ 1 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(1,0); +-------------+ | ifnull(1,0) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select ifnull(0,1); #注意数字0也是数值，非空的 +-------------+ | ifnull(0,1) | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> select ifnull(NULL,1); #空返回第二个表达式 +----------------+ | ifnull(NULL,1) | +----------------+ | 1 | +----------------+ 1 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 10 then insert into t2 values("update", now()); when 20 then insert into t2 values("delete", now()); end case; end && delimiter ; mysql> call insert_data(10); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +-------------+---------------------+ | action_name | action_time | +-------------+---------------------+ | insert | 2019-02-24 14:16:19 | | delete | 2019-02-24 18:03:05 | | update | 2019-02-24 18:03:13 | +-------------+---------------------+ 3 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 0; test_loop :loop set i = i + 1; 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(20); Query OK, 1 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 0; test_loop :loop set i = i + 1; if i = 6 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(10); Query OK, 1 row affected (0.04 sec) mysql> select concent from t1; +---------+ | concent | +---------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 7 | | 8 | | 9 | | 10 | +---------+ 9 rows in set (0.01 sec) mysql>
repeat语句
repeat语句时有条件控制的循环语句，当满足特定条件时，就会跳出循环语句。
repeat语句还可以作为表达式使用：
mysql> select repeat("a",3); +---------------+ | repeat("a",3) | +---------------+ | aaa | +---------------+ 1 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 1; test_repeat: repeat insert into t1(concent) values(repeat("a",i)); set i = i + 1; until i = count end repeat test_repeat; end && delimiter ;
结果如下：
mysql> call insert_data(10); Query OK, 1 row affected (0.05 sec) mysql> select * from t1; +-----+-----------+ | id | concent | +-----+-----------+ | 165 | a | | 166 | aa | | 167 | aaa | | 168 | aaaa | | 169 | aaaaa | | 170 | aaaaaa | | 171 | aaaaaaa | | 172 | aaaaaaaa | | 173 | aaaaaaaaa | +-----+-----------+ 9 rows in set (0.00 sec) mysql>
游标使用
