基础语法

增删改查

1
2
3
4
5
6
7
8
9
10
# 添加
insert into `表`(`字段`) values ('值')
# 删除,truncate清全表
delete from `表` where <条件>
truncate table `表`
# 修改
update `表` set `字段` = '值' where <条件>
# 查询 后面可以使用很多的关键字辅助查询
# select - from - where - group by - having - order by
select `字段` from `表` where···

distinct

去重查询

1
select distinct `字段`, `字段` from `表`

concat

连接字段

1
select concat('我的名字是:',`name`) as 学生姓名 from `users`

limit

limit一般有两个参数,第一个表示返回数据的起始位,第二个表示要返回的行数。若只有一个参数,即省去了第一个参数,使用默认值0。

1
2
3
4
# 返回前5行数据(默认从第0行开始,返回后5行数据)
select * from `表` limit 5
# 从第3行开始,返回后3行数据,也就是456
select * from `表` limit 33

order by

排序,ASC升序(默认),DESC降序

1
2
select * from `表`
order by `字段` ASC / DESC

where

可使用的判断语句

操作符 说明
= 等于
< 小于
> 大于
!= 不等于
<= 小于等于
>= 大于等于
BETWEEN 在两个值之间
IS NULL 为 NULL 值
  • and、or可用于来连接多个条件判断。优先处理and。

  • in常用于匹配一组值,也可以跟一个查询语句,匹配子查询得到的一组值

    1
    select * from `表` where `字段` in ('值'···)
  • not相当于否定,可加在一般的判断语句前面表否定

通配符(模糊查询)

通配符用于过滤,但只能用于文本字段。

要使用like进行通配符匹配。就是使用两个通配符进行字符串拼接,然后进行模糊查询。

  • % 可替换0或多个任意字符
  • _ 只可替换1个任意字符

计算函数

函数对select后的查询字段操作即可。

函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
1
SELECT SUM(money) AS `sum` FROM `account`

group by,having

通过查询语句查找表的某个字段,然后按照字段进行分组展示。

首先可以通过where对原表数据进行筛选操作,然后使用group by对查询数据进行分组操作,分组后的数据若还需要筛选,则使用having操作。

1
2
3
4
SELECT `字段`, COUNT(*) AS 别名 FROM `表`
WHERE <对原表数据操作>
GROUP BY `字段`
HAVING <对分组数据操作>

子查询

就是查询语句套查询,使用子查询的结果来进行操作。

1
2
SELECT * FROM users
WHERE `name` IN (SELECT `password` FROM users)

子查询密码字段的结果,查询姓名,其中姓名和子查询的密码字段有相同值则筛选出来。

连接(join on)

内连接(inner join)

查询用户的邮箱与账户的金额,找到两个表id相同的数据进行返回。inner join来连接查询后,使用on进行条件筛选。

1
2
3
SELECT a.money, u.email
FROM account AS a INNER JOIN users AS u
ON a.id = u.id

自连接

表与表自身进行连接查询,相当于复制一个表,两个相同的表进行连接查询。

1
2
3
SELECT u1.email, u1.id
FROM users AS u1 INNER JOIN users AS u2
ON u1.id = u2.id

外连接(左外连、右外连、全外连)

左连接用户表与账户表,返回需要的字段,使用左右连接时,出来查询两个表符合的字段,还要展示左表或右表的剩余全部内容。没有字段则记为null,但一定要有。

1
2
3
SELECT u.id, u.name, u.password
FROM users AS u LEFT JOIN account AS a
ON a.id = u.id

组合查询

组合多个查询语句,但每个查询的返回格式必须一样,如列相同等。

1
2
3
4
5
SELECT `name` FROM users WHERE id = 1
UNION
SELECT `name`FROM users WHERE id = 2
UNION
SELECT `name` FROM users WHERE id = 3

三大范式

https://zhuanlan.zhihu.com/p/194270249

  • 1NF

    数据库每一列(每个属性字段),都是不可分割的原子数据项。也就说字段内容可以二次拆分,那么就不满足1NF。

    比如学生课程关系表,不可以一个学生直接对应一个课程的信息集合,而是分多个条目进行记录。

  • 2NF

    表中非主键字段应该和主键完全依赖,而不是部分依赖。

    当表中主键由多个字段组成时会出现该问题,部分字段只和其中一个主键有关联,比方说订单表主键由订单号 + 产品组成,一些订单具体数据信息应该单独与订单号记录,而不是混合在订单产品表中。

  • 3NF

    非主键字段不能传递依赖于主键,也就是不能产生依赖传递关系。主键外的所有字段互不依赖。

    比如有ABC三个字段,A是主键,C是通过B与A主键进行关联,此时就违反了3NF。需要拆分成AB,BC两张表,消除传递依赖。

索引

索引底层数据结构

使用B树与B+树引擎的区别

  • B树其所有节点都会存放 key 和 data ,但B+树只有叶子节点会存放 key 和 data ,其余内部节点存放key。也就是说B树所有节点都可以保存数据,索引很大,但B+树只有叶子节点保存数据,其内部节点相当于一个查找的索引。
  • B+树的叶子节点通过链表相连,而B树叶子节点是单独的。
  • B+树相当于B树检索更加稳定,B树可能没有走到叶子节点就查到数据了,而B+树数据都是存在与叶子节点上的,且叶子节点还可进行顺序查找,不用返回上一节点。B+树更便于区间查找和遍历。
  • 为什么不用红黑树,因为B、B+树都是一个节点包含多个key的数据结构,它们的树高相比红黑树更低,而数据库磁盘IO次数就是由树高决定的,使用B+树结构IO效率更高。

索引类型

  • 主键索引(唯一索引)

    数据表的主键就是主键索引。主键和主键索引一致,只能有一个,且主键不为空,不能重复。聚簇索引。

  • 二级索引(辅助索引)

    InnoDB中非主键索引的就是二级索引,二级索引的叶子节点存储的数据是主键值,可通过二级索引确定主键的位置,也就是回表查询。

  • 唯一索引

    唯一索引的属性列不可出现重复数据,但允许数据为null,一张表允许多个唯一索引。一般是为了保证数据列的唯一性,不是为了查找性能考虑。

  • 普通索引

    普通索引唯一的作用就是为了快速查询数据,一张表允许多个普通索引,且允许数据重复和null。

  • 前缀索引

    索引很长的字符列,导致索引很大且慢。前缀索引只适合字符串类型数据。前缀索引对文本的前几个字符创建索引,节省索引空间,提高索引效率。

  • 哈希索引

    基于哈希表实现,为了精确匹配索引所有列的查询才有效。无法用于排序,不支持部分索引列的匹配查询,哈希索引始终使用索引列的全部内容计算哈希值。

  • 全文索引

    全文索引主要为了检索大文本数据的关键字信息。

  • 多列索引

    多个列组成的索引即联合索引。遵循最左匹配原则。

聚簇索引与非聚簇索引(InnoDB)

聚簇索引

一个表只有一个聚集索引。聚集索引就是索引结构与数据一起存放的索引。主键索引是聚集索引。B+树结构,叶子节点data存储数据。

  • 优点:聚集索引查询速度很快,使用的是B+树的存储结构。
  • 缺点:依赖有序数据,因为B+树数据有序,遇到无序数据我们还要先进行排序处理,而像UUID这种杂乱数据处理效率就更低了。

InnoDB默认将逐渐设置为聚簇索引,若没有定义主键,InnoDB会选择一个唯一的非空索引代替。若没有这种索引,InnoDB会隐式定义一个主键作为聚簇索引。

非聚簇索引

二级索引是非聚集索引。叶子节点存放主键值,一般是查询到主键值后对主键索引进行回表查询。叶子节点data存储指向数据的指针。

  • 缺点:也依赖有序数据。且非聚集索引可能发生回表查询。

InnoDB与MyISAM

  • 事务:InnoDB支持事务操作,MyISAM不支持
  • 锁:InnoDB使用行级锁,MyISAM使用表级锁
  • 索引:InnoDB是聚集表,MyISAM是非聚集表
  • 外键:InnoDB支持外键,MyISAM不支持

最左匹配与覆盖索引

首先说说多列索引的最左匹配原则,一个多列索引(a|b|c),可以匹配三种查询条件,a、ab、abc,且where查询语句先后不影响,只要有符合的列就可以使用索引,因为MySQL的优化器会优化执行的顺序。而bc的查询条件是无法使用该多列索引的。

然后说说覆盖索引,如果我们要查询的字段全部被一个索引所包含,那么该查询只需要去扫描索引树而无需回表。(回表查询就是查二级索引拿主键值,再去查主键索引拿数据)这里覆盖索引只有部分覆盖也是可以的。

所以有时会出现特殊情况,导致我们误以为最左匹配失效,例如我们给表中所有字段设置为多列索引,那么对该表的查询一定会走覆盖索引的操作,此时我们无论怎么写where条件语句,都不会全表查询,导致最左匹配看似失效了。

事务

数据库事务

多个对数据库操作构成的一个逻辑整体,这就是数据库事务,这个逻辑整体的所有操作,要么全部成功,要么全部不成功。

ACID

  • 原子性:原子性表示事务是最小的执行单位,这个整体只能同时完成操作,不能只完成部分操作。
  • 一致性:执行事务前后,数据整体要保持一致,也就是把事务当作一个整体,比如进行转账操作,无论操作是否成功,账户之间的操作的金额整体是不变的。
  • 隔离性:并发访问数据库时,一个事务的执行不受其他事务干扰,事务之间时相互隔离的。
  • 持久性:一个事务执行提交后,它在数据库中的改变是永久的,数据库发生故障也不会影响数据。

并发事务带来的问题

多个事务并发运行,经常会有多个用户操作同一数据,并发是不可避的,但会导致一些问题,一般是因为并发破坏了事务的隔离性才导致以下问题的发生。

  • 脏读:一个事务A正在访问数据并进行修改,但修改还未提交到数据库中,此时另一个事务B访问该数据并使用,由于这个数据还未提交,事务A后续可能对事务进行回滚操作,那么数据就没有进行修改,事务B读取到的数据就是脏数据。

  • 丢失修改:事务A读取数据时,同时另一个事务B也访问该数据,此时事务A先修改数据,然后事务B也修改数据,最后事务B的修改操作会覆盖事务A的修改操作。

  • 不可重复读:事务A对同一数据进行多次读取操作,在A进行多次读取之间,有事务B对该数据进行了修改操作,导致事务A同一数据的多次读取结果不一致。

  • 幻读:事务A对多条数据进行读取操作,但中途事务B执行了插入或删除操作,随后事务A再次对多条数据进行读取,发现和前一次读取结果不一致。

事务的隔离级别

隔离级别依次递增

  • 读取未提交:允许读取未提交的数据,所有并发问题都会出现。
  • 读取已提交:允许事务读取已提交的数据,也就是说一个事务所做的修改在提交前对其他事务是不可见的。可以阻止脏读的发生。
  • 可重复读:保证在同一个事务中,多次读取同一数据是一致的,除非事务本身进行修改操作。可阻止脏读与不可重复读。
  • 可串行化:完全服从ACID的隔离级别。强制事务串行执行,多个事务间互不干扰,不会出现并发相关问题。该隔离级别需要进行加锁操作,以确保同时间只有一个事务执行,也就是串行执行。