( 目录)
（一）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)