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_infoG  #看到的是视图的定义
*************************** 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_infoG                  #方法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> 

游标使用