基础语法

增删改查

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

索引

索引底层数据结构

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

http://xianzilei.cn/blog/31

  • B树其所有节点都会存放 key 和 data ,但B+树只有叶子节点会存放 key 和 data ,其余节点值存放key。也就是说B树所有节点都可以保存数据,但B+树只有叶子节点保存数据,其内部节点相当于一个查找的索引。
  • B+树的叶子节点通过一条链相互连接,而B树叶子节点是单独的。
  • B+树相当于B树检索更加稳定,B树可能没有走到叶子节点就查到数据了,而B+树数据都是存在与叶子节点上的,且叶子节点还可进行顺序查找,不用返回上一节点。

索引类型

主键索引

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

二级索引(辅助索引)

二级索引的叶子节点存储的数据是主键,也就是说可通过二级索引确定主键的位置。

  • 唯一索引

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

  • 普通索引

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

  • 前缀索引

    前缀索引只适合字符串类型数据。前缀索引对文本的前几个字符创建索引。

  • 全文索引

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

  • 联合索引

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

聚集索引与非聚集索引

聚集索引(innodb)

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

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

非聚集索引(myisam)

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

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

InnoDB与MyISAM

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

事务

数据库事务

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

ACID

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

并发事务带来的问题

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

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

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

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

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

事务的隔离级别

隔离级别依次递增

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