MySQL进阶

最新推荐文章于 2023-10-26 22:53:01 发布

Aeroblaze

最新推荐文章于 2023-10-26 22:53:01 发布

阅读量122

点赞数

分类专栏： # MySQL

本文链接：https://blog.youkuaiyun.com/qq_43492699/article/details/109450010

版权

MySQL 专栏收录该内容

7 篇文章

订阅专栏

文章目录

sql_mode
case when
@i
事务
- 隐式提交
存储过程
触发器
视图
MySQL优化的方法

sql_mode

sql_mode是mysql里的一个环境变量，定义了MySQL应该支持的语法和数据校验

select sid,sname from student group by sid,sname;

在MySQL基础里有提到过group by里的字段必须和select里的字段相同，否则报错
其实报错并不是一定会出现的，这里如果报错，原因来自sql_mode里的一个参数

查看当前数据库里的sql_mode

select @@sql_mode;

参数ONLY_FULL_GROUP_BY:如果select里的列没有在group by里出现，则不合法

临时修改sql_mode，重启SQL则失效

set @@sql_mode = 'sql_mode参数列表';

在这里插入图片描述

修改mysql配置文件，重启SQL仍生效

case when

case when是SQL里的流程控制语句，类似编程语言里的if，其格式为

case when 条件1 and 条件2 then 1 else 0 END

这条语句的含义为，当条件1与条件2满足时，自增1，否则自增0，应用情况如下：

sum(case when sc.score>=60 then 1 else 0 END)

表示的含义为sc表中的score大于60分时，sum里的内容会+1，可以用于计算及格率

@i

SQL语句也可以创建变量，在某些应用场景里还是很需要的

select b.cid,b.avg_sc,@i := @i+1 as rk
from (select @i := 0) as a,
(select sc.cid,round(avg(sc.score),2) as avg_sc 
from sc 
group by cid 
order by avg_sc desc) as b;

事务

事务是由一系列对数据进行操作的语句组成的基本逻辑单元

事务的四个特性（ACID）：

Atomicity（原子性）：要么全都做了，要么全都没做，事务中出错会自动回滚
Consistency（一致性）：事务要保持一致性，不可破坏数据库里的约束条件
Isolation（隔离性）：多个事务并行调度时事物具有隔离性互相不影响
Durability（持久性）：一个事务执行完毕，结果不能丢失，对数据库影响是永久的

事务的四个隔离级别：

读未提交（READ_UNCOMMITTED）：安全性最低，会引发三种并发问题
读已提交（READ_COMMITTED）：不会出现脏读，但会出现不可重复读
可重复读（REPEATABLE_READ）：只会出现幻读的并发问题
顺序读（SERIALIZABLE）：完全不支持并发，让事务排队进行数据读取

四种隔离级别从上至下，并发性依次降低，安全性依次增高

事务的并发问题：

脏读（Dirty Read）：读取了没有提交的数据，事务A读了事务B回滚前修改的数据
不可重复读（Unrepeatable Read）：同一条命令返回的不同结果集，事务A多次读取同一条数据，事务B在这个过程中修改了数据，导致事务A得到的结果不一致
幻读（Phantom Read）：重复查询的过程中，数据发生了量的变化

事务的语法：

start transaction;/ begin;
commit; 使得当前的修改提交
rollback; 使得当前的修改回滚

隐式提交

DQL：查询语句
DML：写操作（添加，更改，删除）
DDL：定义语句（建库，建表，修改表，索引操作，存储过程，视图）
DCL：控制语言（给用户授权或者删除授权）

DDL里的语句都是隐式提交，执行这些语句等同于执行commit提交事务

存储过程

存储过程是将一条或多条SQL语句进行封装，以便未来进行使用，类似编程语言的函数

-- 定义存储过程
-- \d修改SQL语句的默认结尾符;为//而BEGIN与END类似函数体语句
\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),@i,0);
set @i=@i+1;
end while;
end;
//
-- 执行存储过程
call p1()
-- 查看存储过程
show create procedure p1\G
-- 删除存储过程
drop procedure p1

存储过程相当于把业务逻辑封装在数据库内部，可以在一定程度上提高性能

触发器

MySQL中的触发器类似存储过程，但不同于存储过程需要调用，它在事件发生时执行

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON tbl_name FOR EACH ROW trigger_stmt
说明：
# trigger_name：触发器名称
# trigger_time:触发时间，可取值：BEFORE或AFTER
# trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
# tb1_name：指定在哪个表上
# trigger_stmt：触发处理SQL语句。
-- 查看所有的 触发器
show triggers\G;
-- 删除触发器
drop trigger trigger_name;

如果触发器中SQL有语法错误，则整个操作都会报错。触发器用于在某事件发生时执行CURD操作，主要的参数有触发器名称、触发时间、触发事件、触发器的表格、触发时的SQL语句

视图

视图不是表，视图只是代表一段定义好的SQL查询语句，对表的操作也可以对视图进行

创建视图:
create view v_users as select id,name,age from users where age >= 25 and age
<= 35;
-- Query OK, 0 rows affected (0.00 sec)
view视图的帮助信息:
? view
ALTER VIEW
CREATE VIEW
DROP VIEW
查看当前库中所有的视图
show tables; --可以查看到所有的表和视图
show table status where comment='view'; --只查看当前库中的所有视图
删除视图v_t1:
drop view v_t1;