高级部分
降低SQL复杂度
create view vm_stu as select name,phone from student;
#有了视图之后可以直接再navicat中查看，也可以用语句
select * from vm_stu;
create view vm_stu_all as select name,phone,score from student inner join score on student.id=score.stuId;
显示视图
mysql> show tables;
+----------------------+
| Tables_in_leo_school |
+----------------------+
| count |
| eatery |
| info |
| score |
| stu |
| student |
| t_1 |
| t_2 |
| t_3 |
| teacher |
| vw_stu |
| vw_stu_all |
| websites |
+----------------------+
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 `vm_stu` AS select `student`.`name` AS `name`,`student`.`phone` AS `phone`
from `student` | utf8mb4 | utf8mb4_general_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
mysql> show table status where comment='view' \G;
#查出所有的引擎名
中端上默认显示视图
更新和删除视图
#修改
alter view vw_stu_all as select name from student
#删除
drop view vw_stu_all
视图算法temptable，merge
临时表算法
合并算法
子查询用到视图里
事务的提出
要么一块执行，要么回滚
transaction
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 200.00 |
| 2 | 256.55 |
| 3 | 35.60 |
+----+---------+
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> update wallet set balance=balance+50 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#但是此时的数据库里的数据还没有改变
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 200.00 |
| 2 | 256.55 |
| 3 | 35.60 |
+----+---------+
mysql> commit;#提交之后改变
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 150.00 |
| 2 | 206.55 |
| 3 | 35.60 |
+----+---------+
#回滚
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update wallet set balance=balance-50;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 150.00 |
| 2 | 206.55 |
| 3 | 35.60 |
+----+---------+
3 rows in set (0.00 sec)
只能再commit之前回滚，一旦commit，就不能再使用rollback
淘宝收货，金额转账等案例
rollback to 回滚点
类似git和虚拟机里的快照
https://www.cnblogs.com/lxwphp/p/11348530.html
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 150.00 |
| 2 | 206.55 |
| 3 | 35.60 |
| 4 | 100.00 |
| 5 | 300.00 |
+----+---------+
5 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into wallet values(6,600);
Query OK, 1 row affected (0.01 sec)
mysql> savepoint six;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into wallet values(7,700);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint seven;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 150.00 |
| 2 | 206.55 |
| 3 | 35.60 |
| 4 | 100.00 |
| 5 | 300.00 |
| 6 | 600.00 |
| 7 | 700.00 |
+----+---------+
7 rows in set (0.00 sec)
#回滚到six点之前的数据
mysql> rollback to six;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 150.00 |
| 2 | 206.55 |
| 3 | 35.60 |
| 4 | 100.00 |
| 5 | 300.00 |
| 6 | 600.00 |
+----+---------+
6 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 150.00 |
| 2 | 206.55 |
| 3 | 35.60 |
| 4 | 100.00 |
| 5 | 300.00 |
| 6 | 600.00 |
+----+---------+
6 rows in set (0.00 sec)
ACID
事务的四种特性：
A：atomicity （原子性）
C： consistency （一致性）
I：isolation （隔离性）
D：durability （持久性）
注意事项
创建数据库的时候必须保证引擎是innodb，才能使用事务
索引 index
优点：查询速度快
缺点：占空间，增删改效率变低
四种索引：主键索引，唯一键索引，普通索引，全局索引
#普通索引
mysql> create index balance_index on wallet(balance);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
#唯一键索引
mysql> create unique index balance_index on t_2(score1);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
#更新索引,因为已经存在报错，格式如下
mysql> alter table balance add index balance_index(balance);
ERROR 1146 (42S02): Table 'leo_school.balance' doesn't exist
#删除索引
mysql> drop index balance_index on wallet;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
delimiter
存储过程，可以增删改查，也可以使用事务
模块化设计
mysql> delimiter //
mysql> ;
-> ;;
-> //
mysql> delimiter ;
procedure 存储过程的用途
mysql> delimiter //
mysql> create procedure proc()
-> begin
-> update wallet set balance=balance+50;
-> update t_3 set name='Tom';
#update语句应该被transaction包起来
-> end //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call proc();#执行proc()
Query OK, 7 rows affected (0.01 sec)
mysql> select * from wallet;
+----+---------+
| id | balance |
+----+---------+
| 1 | 200.00 |
| 2 | 256.55 |
| 3 | 85.60 |
| 4 | 150.00 |
| 5 | 350.00 |
| 6 | 650.00 |
+----+---------+
6 rows in set (0.00 sec)
mysql> select * from t_3;
+------+------+
| id | name |
+------+------+
| 1 | Tom |
| 2 | Tom |
| 3 | Tom |
| 4 | Tom |
| 5 | Tom |
| 6 | Tom |
| 7 | Tom |
+------+------+
7 rows in set (0.00 sec)
#删除
mysql> drop procedure proc;
Query OK, 0 rows affected (0.01 sec)
#显示存储过程
mysql> show create procedure proc;
#显示所有的存储过程
mysql> show procedure status \G;
number
mysql> select rand();#生成随机数 +---------------------+ | rand() | +---------------------+ | 0.28059853825773906 | +---------------------+ #抽奖 mysql> select * from student order by rand() limit 1; +------+------+--------+------+----------+ | id | name | gender | age | phone | +------+------+--------+------+----------+ | 6 | leo | 1 | 24 | 43212354 | +------+------+--------+------+----------+ #向上取整 mysql> select ceil(3.1); +-----------+ | ceil(3.1) | +-----------+ | 4 | +-----------+ #向下取整 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 | +------------+ #截取几位小数 mysql> select truncate(3.1415926,2); +-----------------------+ | truncate(3.1415926,2) | +-----------------------+ | 3.14 | +-----------------------+ #随机排序 mysql> select * from student order by rand(); +------+-------+--------+------+-------------+ | id | name | gender | age | phone | +------+-------+--------+------+-------------+ | 6 | leo | 1 | 24 | 43212354 | | 5 | Maria | 2 | 19 | 156459628 | | 2 | Jack | 1 | 28 | 17777777777 | | 4 | Mary | 2 | 19 | 1235457952 | | 3 | Jerry | 1 | 20 | 12356548 | +------+-------+--------+------+-------------+
string
#转换成大写
mysql> select ucase('hello');
+----------------+
| ucase('hello') |
+----------------+
| HELLO |
+----------------+
#转换成小写
mysql> select lcase('HELLO');
+----------------+
| lcase('HELLO') |
+----------------+
| hello |
+----------------+
#左截取
mysql> select left('HELLO',2);
+-----------------+
| left('HELLO',2) |
+-----------------+
| HE |
+-----------------+
#右截取
mysql> select right('HELLO',2);
+------------------+
| right('HELLO',2) |
+------------------+
| LO |
+------------------+
#从某一个位置截取
mysql> select substring('HELLO',2,3);
+------------------------+
| substring('HELLO',2,3) |
+------------------------+
| ELL |
+------------------------+
#拼接字符串
mysql> select concat('HELLO','WORLD');
+-------------------------+
| concat('HELLO','WORLD') |
+-------------------------+
| HELLOWORLD |
+-------------------------+
mysql> select concat(name,'|',age) from student;
+----------------------+
| concat(name,'|',age) |
+----------------------+
| Jack|28 |
| Jerry|20 |
| Mary|19 |
| Maria|19 |
| leo|24 |
+----------------------+
other
#获取当前时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-08-03 12:53:22 |
+---------------------+
#https://blog.csdn.net/weixin_38842096/article/details/85268649 时间戳函数用法
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1596430457 |
+------------------+
mysql> select year(now()) year, month(now()) month, day(now()) day;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2020 | 8 | 3 |
+------+-------+------+
#加密
mysql> select sha("123123");
+------------------------------------------+
| sha("123123") |
+------------------------------------------+
| 601f1889667efaebb33b8c12572835da3f027f78 |
+------------------------------------------+
浙公网安备 33010602011771号