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;
视图的作用在于,它可以重用SQL语句,简化复杂的SQL操作,同时提高数据的安全性
需要注意的是:视图不可以索引,也不可以有触发器或默认值
MySQL优化的方法
- 建立索引
- 将子查询语句转化为join
- 使用limit进行分页查询
- 使用like进行模糊匹配,开头不可以用%
- 使用redis作为查询缓存,避免重复多次直接访问关系型数据库