超详细超全面《MYSQL》的地表最强总结

• 当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

非空约束

• 如果一个列添加了非空约束，那么这个列的字段不能为空，但可以重复

唯一约束

• 如果一个列添加了唯一约束，那么这个列的字段必须唯一，但可以为空

函数

基础函数

upper

数据转大写

length

数据的长度

substr

截取

concat

拼接数据

replace

替换数据

ifnull

如果是null，则替换指定数据

round()

round(comm):直接四舍五入取整
round(comm,1)：四舍五入保留一位
ceil(comm)：向上取整
floor(comm):向下取整

now

now（）年月日时分秒
curdate（）：年月日
curtime（）：时分秒

hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;
select now(),year(now()),month(now()),day(now()) from emp ;

条件查询

distinct

使用distinct关键字，去除重复的记录行

where

like

通配符%代表0到n个字符，通配符下划线_代表1个字符

lower

数据转小写

null

select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的

between and

limit

分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

• select * from emp limit 2 --列出前两条

• select * from emp limit 1,2 --从第二条开始,展示2条记录

• select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

order by

SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序

聚合函数 aggregation

count

select count(*) from emp --底层优化了
select count(1) from emp --效果和*一样
select count(comm) from emp --慢,只统计非NULL的

max/min

select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组

sum/avg

select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数

group by

对于查询的结果进行分组统计
group表示分组，having类似于where过滤返回的结果

事务

什么是事务？

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

事务4个特性的ACID

一般来说，事务必须满足四个条件：原子性，一致性，隔离性，持久性

原子性：，一个事务中的所有操作，要么全完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中会被回滚，到事务开始的状态，就像从来没执行过一样。
一致性：在事务的开始执行和事务结束后，数据库的完整性没有被破坏，表示写入的数据资料必须完全符合所有的预设规则，这包含资料的精确度，这包含资料的精确度，串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务对其数据进行修改和读写功能，隔离性可以防止多个事务并发执行时由于交叉执行而导致的数据不一致
持久性：事务处理结束后，对数据库的修改时永久的，故系统故障也不会丢失

隔离级别

读未提交（Read uncommitted）安全性最差，可能发生并发数据问题，性能最好

读提交（read committed） Oracle默认的隔离级别

可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般

串行化（Serializable）表级锁，读写都加锁，效率低下,安全性高,不能并发

查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

开启事务：start transaction；
开启事务：start transaction；
在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！SELECT @@tx_isolation;Repeatable Read（可重读）MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

事务处理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务

事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行

事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的

MySQL默认数据库的事务是开启的，执行SQL后自动提交。

MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

提交commit

#多条语句时，批量执行，事务提交

#有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱

#mysql的事务默认就是开启的 -- 多条语句一起操作时,要么一起成功要么一起失败

回滚 rollback

#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

表强化

非空约束：not null

唯一约束：unique

创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：

主键约束：primary key

主键是一条记录的唯一标识，具有唯一性，不能重复

外键约束：foreign key

默认约束：default

检查约束：check

很少使用，了解即可，录入age超过200将报错

多表联查

笛卡尔积

案例：SELECT * FROM dept,emp

上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。
这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

三种连接join

内连接 inner join

INNER JOIN两边都对应有记录的才展示，其他去掉

左（外）连接 left join

INNER JOIN两边都对应有记录的才展示，其他去掉

右（外）连接 right join

INNER JOIN两边都对应有记录的才展示，其他去掉

子查询

子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。

案例：

SQL的执行顺序

(1) FROM [left_table] 选择表

(2) ON <join_condition> 链接条件

(3) <join_type> JOIN <right_table> 链接

(4) WHERE <where_condition> 条件过滤

(5) GROUP BY <group_by_list> 分组

(6) AGG_FUNC(column or expression),... 聚合

(7) HAVING <having_condition> 分组过滤

(8) SELECT (9) DISTINCT column,... 选择字段、去重

(9) ORDER BY <order_by_list> 排序

(10) LIMIT count OFFSET count; 分页

索引

定义

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

分类

单值索引：一个索引只包括一个列，一个表可以有多个列

唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引

复合索引：一个索引同时包括多列

创建索引

查看索引，主键会自动创建索引

show index from dept

创建普通索引

create index 索引名字 on 表名(字段名); #创建索引
create index loc_index on dept(loc); #创建索引

创建唯一索引

#创建唯一索引--索引列的值必须唯一
CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
CREATE UNIQUE INDEX bindex ON dept(loc)

创建复合索引

#创建唯一索引--索引列的值必须唯一
CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
CREATE UNIQUE INDEX bindex ON dept(loc)

删除索引

alter table dept drop index fuhe_index

索引扫描类型

ALL 全表扫描，没有优化，最慢的方式

index 索引全扫描，其次慢的方式

range 索引范围扫描，常用语<，<=，>=，between等操作

ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询

const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况

null MySQL不访问任何表或索引，直接返回结果

最左特性

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)、(k1,k3)和(k1,k2,k3)索引，这就是最左匹配原则，也称为最左特性。

为何索引快

明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

排序，tree结构，类似二分查找

索引表小

优点

索引是数据库优化

表的主键会默认自动创建索引

每个字段都可以被索引

大量降低数据库的IO磁盘读写成本，极大提高了检索速度

索引事先对数据进行了排序，大大提高了查询效率

缺点

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间

索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”

虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件

随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

甜心小旭

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
超详细超全面《MYSQL》的地表最强总结

目录每日必看SQL语句分类DML（Data Manipulation Language）数据操纵语言DML（Data Manipulation Language）数据操纵语言DML（Data Manipulation Language）数据操纵语言DQL（Data Query Language）数据库查询语言常用操作对库的常用操作对表的常用操作表记录的常用操作数据类型字段约束函数基础函数upperlengthsubstrconcat
复制链接

扫一扫