11：高级部分-MySQL

目录

• ​​（一）view 视图​​

• ​​1. 开场​​
• ​​2. view视图创建、使用以及作用​​
• ​​3. 显示视图​​
• ​​4. 更新和删除视图​​
• ​​5. 视图算法： temptable, merge​​

• ​​（二）transaction 事务​​

• ​​1. 事务的提出​​
• ​​2. transaction​​
• ​​3. rollback to point​​
• ​​4. ACID​​
• ​​5. 注意事项​​

• ​​（三）索引​​

• ​​1. 四大索引​​

• ​​（四）存储过程​​

• ​​1. delimiter定界符​​
• ​​2. procedure存储过程的用途​​

• ​​（五）有趣的函数​​

• ​​1. number​​
• ​​2. string​​
• ​​3. others​​

（一）view 视图

1. 开场

高级部分不属于实习内容，已经超过了实习范围

尤其是培训机构不会讲这些，主要是经验规范之谈

2. view视图创建、使用以及作用

视图主要负责筛选，有意隐藏敏感数据与结构

create view vw_stu as 
select * from eatery natural on stu;

create view vw_stu_eatery_na as 
select * from stu natural join eatery;

3. 显示视图

终端上体现

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| eatery            |
| emp               |
| score             |
| stu               |
| t_1               |
| t_10              |
| t_11              |
| t_12              |
| t_2               |
| t_3               |
| t_4               |
| t_5               |
| t_6               |
| t_7               |
| t_8               |
| t_9               |
| teacher           |
| vw_stu            |
| vw_stu_eatery_na  |
+-------------------+
19 rows in set (0.00 sec)

mysql> desc vw_stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| stuId | int(4)      | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查看数据库所有视图

mysql> show create view vw_stu;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View
                                                                                  | character_set_client | collation_connection |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vw_stu | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_stu` AS select `stu`.`stuId` AS `stuId`,`stu`.`name` AS `name` from `stu` | utf8mb4              | utf8mb4_general_ci   |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

视图其实就是一张虚拟的表，所以​​show table status​​

但视图与普通的表不同，所以​​status where comment='view'​​

​​\G​​表示全部

mysql> show table status where comment ='view' \G
*************************** 1. row ***************************
           Name: vw_stu
         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
*************************** 2. row ***************************
           Name: vw_stu_eatery_na
         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
2 rows in set (0.02 sec)

4. 更新和删除视图

更新视图

alter view vw_stu as 
select name from stu;

删除视图

drop view vw_stu

5. 视图算法： temptable, merge

视图中使用子查询会出现问题

视图算法有合并算法​​merge​​​、 临时表算法​​temptable​​​、未定义​​undefined​​

（二）transaction 事务

1. 事务的提出

假设你下单一个东西，按常理来说是不是taobao的钱数据增加了，你的钱数据减少了

那如果没付款，那这钱去哪了呢？确认收货的钱又去哪了呢？

难道没付款也是taobao的钱数据增加了，你的钱数据减少了吗？

引用自菜鸟教程：MySQL事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务

2. transaction

开始事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    636.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> update wallet set balance=balance-50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update wallet set balance=balance+50 where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

一旦​​commit​​​就不能​​rollback​​​了，先​​rollback​​​再​​commit​​，查询结果未提交，可以回溯

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update wallet set balance=balance+50 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

先​​commit​​​再​​rollback​​，查询结果已经提交，无法回溯

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update wallet set balance=balance+50 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

3. rollback to point

相当于快照功能，设置回滚点​​savepoint​​​，回到回滚点​​rollback to point​​

mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
+----+-----------+
4 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values(5,1000);
Query OK, 1 row affected (0.01 sec)

mysql> savepoint five;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values(6,5000);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint six;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
|  5 |   1000.00 |
|  6 |   5000.00 |
+----+-----------+
6 rows in set (0.00 sec)

mysql> rollback to five;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
|  5 |   1000.00 |
+----+-----------+
5 rows in set (0.00 sec)

4. ACID

事务是必须满足4个条件（ACID）：

• 原子性（Atomicity，或称不可分割性）一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
• 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏
• 隔离性（Isolation，又称独立性）：并发事务同时对其数据进行读写和修改的能力
• 持久性（Durability）：对数据的修改就是永久的

5. 注意事项

事务不是所有数据库都能用，指定数据库引擎为INNODB才能用

（三）索引

1. 四大索引

索引 ​​index​​

优点：帮助你快速查询到数据

缺点：数据设置为索引，增删改查效率低且占空间

创建目的：索引数据经常被查询，用于提高查询效率

索引分类：普通索引​​index​​​、主键索引​​primary key​​​ 、唯一索引​​unique​​、联合索引、全文索引、空间索引

mysql不支持中文，全局索引搜索引擎可以用sphinx

（四）存储过程

1. delimiter定界符

数据库管理员（Database Administrator，简称DBA）模块化设计

存储过程相当于一个函数，调用大量SQL

当有很多sql语句，每一个分号就是一条语句，直接发送服务区肯定不行

mysql> ;
ERROR:
No query specified

怎么解决这个问题呢?

​​delimiter​​用于设定SQL语句分隔符，可以设置以双斜线为结尾

mysql> delimiter //
mysql> select * from wallet;
    -> //
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  4 |   1000.00 |
|  3 | 888888.00 |
+----+-----------+
4 rows in set (0.00 sec)

用完之后就还原

mysql> delimiter ;
mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  4 |   1000.00 |
|  3 | 888888.00 |
+----+-----------+
4 rows in set (0.00 sec)

2. procedure存储过程的用途

创建存储过程

mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> update wallet set balance=balance+50;
    -> update teacher set name='Frank';
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc();
Query OK, 4 rows affected (0.01 sec)

mysql> select * from wallet natural join  teacher;
+----+-----------+-------+--------+----------+
| id | balance   | name  | phone  | address  |
+----+-----------+-------+--------+----------+
|  1 |    550.87 | Frank | NULL   | 暂时未知 |
|  2 |    786.57 | Frank | NULL   | 暂时未知 |
|  3 | 888938.00 | Frank | 123456 | ShangHai |
|  4 |   1050.00 | Frank | NULL   | NULL     |
+----+-----------+-------+--------+----------+
4 rows in set (0.00 sec)

显示存储过程

mysql> delimiter ;
mysql> show create procedure proc;
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                       | Create Procedure
                                               | character_set_client | collation_connection | Database Collation |
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| proc      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()
begin
update wallet set balance=balance+50;
update teacher set name='Frank';
end | gbk                  | gbk_chinese_ci       | gbk_chinese_ci     |
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

显示所有存储过程

mysql> show procedure status \G

删除存储过程

mysql> drop procedure proc;
Query OK, 0 rows affected (0.01 sec)

（五）有趣的函数

1. number

随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7103542850449256 |
+--------------------+
1 row in set (0.00 sec)

随机排序

mysql> select * from wallet order by rand() ;
+----+-----------+
| id | balance   |
+----+-----------+
|  4 |   1050.00 |
|  3 | 888938.00 |
|  1 |    550.87 |
|  2 |    786.57 |
+----+-----------+
4 rows in set (0.00 sec)

向上取整

mysql> select ceil(3.1);
+-----------+
| ceil(3.1) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

向下取整

mysql> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

四舍五入

mysql> select round(3.1);
+------------+
| round(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

截取数字

mysql> select truncate(3.141592654,2);
+-------------------------+
| truncate(3.141592654,2) |
+-------------------------+
|                    3.14 |
+-------------------------+
1 row in set (0.00 sec)

2. string

字符串操作
小写字母变大写

mysql> select ucase('abc');
+--------------+
| ucase('abc') |
+--------------+
| ABC          |
+--------------+
1 row in set (0.00 sec)

大写字母变小写

mysql> select lcase('ABC');
+--------------+
| lcase('ABC') |
+--------------+
| abc          |
+--------------+
1 row in set (0.00 sec)

从左截取字符串

mysql> select left('ABC',1);
+---------------+
| left('ABC',1) |
+---------------+
| A             |
+---------------+
1 row in set (0.00 sec)

从右截取字符串

mysql> select right('ABC',1);
+----------------+
| right('ABC',1) |
+----------------+
| C              |
+----------------+
1 row in set (0.00 sec)

分割字符串

mysql> select substring('ABC',1,2);
+----------------------+
| substring('ABC',1,2) |
+----------------------+
| AB                   |
+----------------------+
1 row in set (0.00 sec)

​​concat()​​函数用于将多个字符串连接成一个字符串

mysql> select concat('ABC','DEF');
+---------------------+
| concat('ABC','DEF') |
+---------------------+
| ABCDEF              |
+---------------------+
1 row in set (0.00 sec)

mysql> select concat(name,'|',stuId) from stu;
+------------------------+
| concat(name,'|',stuId) |
+------------------------+
| frank|4                |
| Tom|5                  |
+------------------------+
2 rows in set (0.00 sec)

3. others

设置年月日

mysql>  select year(now()) year, month(now()) month, day(now()) day;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2021 |    10 |    1 |
+------+-------+------+
1 row in set (0.00 sec)

加密字符串

mysql> select sha("123");
+------------------------------------------+
| sha("123")                               |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+
1 row in set (0.00 sec)

​​ifnull()​​​函数用于判断第一个表达式是否为​​NULL​​​，如果为​​NULL​​​则返回第二个参数的值，如果不为​​NULL​​则返回第一个参数的值

mysql> select ifnull(null,"frank");
+----------------------+
| ifnull(null,"frank") |
+----------------------+
| frank                |
+----------------------+
1 row in set (0.00 sec)

mysql> select ifnull("123","frank");
+-----------------------+
| ifnull("123","frank") |
+-----------------------+
| 123                   |
+-----------------------+
1 row in set (0.00 sec)