11：高级部分 -MySQL
作者：Yeats_Liao
- 2022-10-30 江西
本文字数：7443 字
阅读完需：约 24 分钟
（一）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: NULLMax_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: NULLMax_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: VIEW2 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`()beginupdate 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)
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【Yeats_Liao】的原创文章。
原文链接:【http://xie.infoq.cn/article/953fcc0869fda93b258663a84】。文章转载请联系作者。
Yeats_Liao
关注
Hello，World！ 2022-10-02 加入
这里更多的是记录个人学习，如果有侵权内容请联系我！ 个人邮箱是：yeats_liao@foxmail.com
评论