MySQL一些知识的简单整理

零.一些表格

常用的条件表达式

条件	表达式举例1	表达式举例2	说明
使用=判断相等	score = 80	name = ‘abc’	字符串需要用单引号括起来
使用>判断大于	score > 80	name > ‘abc’	字符串比较根据ASCII码，中文字符比较根据数据库设置
使用>=判断大于或相等	score >= 80	name >= ‘abc’
使用<判断小于	score < 80	name <= ‘abc’
使用<=判断小于或相等	score <= 80	name <= ‘abc’
使用<>判断不相等	score <> 80	name <> ‘abc’
使用LIKE判断相似	name LIKE ‘ab%’	name LIKE ‘%bc%’	%表示任意字符，例如’ab%‘将匹配’ab’，‘abc’，‘abcd’

一.查询

1.基础查询：

SELECT*FROM students;

2.排序：

order by 是根据结果集，按照某个字段进行排序，默认是从低到高排序，desc 反过来
where 是根据条件进行筛选

select*from students order by score;

3.分页查询：

limit offset 是mysql 特有的关键字，用于分页查询。例如，LIMIT 3 OFFSET 0 就是指将结果集按每页三条记录，查询第一页

select*from students limit 3 offset 0;

4.聚合查询（也叫分组查询）：

有四个自带方法，COUNT(),MAX(),AVG(),MIN()

- 根据 班级id 进行分组
select COUNT(*) from students group by class_id;

group by 聚合查询关键字，指定字段进行**分组。**可以指定多个字段，按前后顺序进行分组

例：给你一张学生表，查询出每个班级男生和女生的平均分

±---------±--------------±-----±----±--------±---------------+

| Field | Type | Null | Key | Default | Extra |
±---------±--------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| class_id | int(11) | YES | MUL | NULL | |
| name | varchar(50) | YES | | NULL | |
| gender | enum(‘M’,‘F’) | YES | | NULL | |
| score | int(11) | YES | | NULL | |
±---------±--------------±-----±----±--------±---------------+

首先对班级进行分组，然后在根据性别（枚举类型）进行分组，使用 AVG 对分完的组进行聚合查询出对应的平均分：

-- 对多个字段进行分组
select class_id,gender,AVG(score) 平均分 from students group by class_id,gender;

结果集：

±---------±-------±----------+
| class_id | gender | 平均分 |
±---------±-------±----------+
| 1 | M | 80.0000 |
| 1 | F | 70.0000 |
| 2 | M | 82.5000 |
| 3 | M | 87.6667 |
| 3 | F | 87.0000 |
| 4 | F | 77.0000 |
±---------±-------±----------+

查询的字段必须是根据xx字段分组的。那四个方法就可以对其他字段进行计算：：条数、最大值、平均值、最小值。

加条件限制：

where：用于对分组聚合前的数据进行筛选

如果我们想对语句加上条件限制，还是使用 where？

比如，查询班级为 1 ，2的男女生的平均分。

select class_id,gender,avg(score) from students  where class_id = 1 or class_id = 2 group by class_id,gender;

查询结果：

±---------±-------±-----------+
| class_id | gender | avg(score) |
±---------±-------±-----------+
| 1 | M | 80.0000 |
| 1 | F | 70.0000 |
| 2 | M | 82.5000 |
±---------±-------±-----------+
3 rows in set (0.03 sec)

这时候还是用到 where，不过 where 不能放到最后。因为 where 是用来对删选聚合前的表，先筛选，再分组聚合。因为分组聚合后，没法再进行筛选了。如果想对聚合后的数据进行筛选，需要用到 having

having：用于对分组聚合后的数据进行筛选

比如，查询班级为 1 ，2的男女生的平均分高于 70 分的数据。

select class_id,gender,avg(score) from students where class_id = 1 or class_id = 2 group by class_id,gender having avg(score) > 70;

只需要在分组后的数据进行筛选即可

查询结果：

mysql> select class_id,gender,avg(score) from students where class_id = 1 or class_id = 2 group by class_id,gender having avg(score) > 70;
±---------±-------±-----------+
| class_id | gender | avg(score) |
±---------±-------±-----------+
| 1 | M | 80.0000 |
| 2 | M | 82.5000 |
±---------±-------±-----------+
2 rows in set (0.00 sec)

5.多表查询：

select*from [table1],[table2];

这种查询称为笛卡尔查询，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。
通过条件来对结果集进行筛选，删选出正确信息。

班级表：

mysql> select*from classes;
±—±-------+
| id | name |
±—±-------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
±—±-------+

4 rows in set (0.01 sec)

学生表：

mysql> select*from students;;
±—±---------±----------±-------±------+
| id | class_id | name | gender | score |
±—±---------±----------±-------±------+
| 2 | 2 | 大牛 | M | 80 |
| 3 | 1 | 小明 | M | 80 |
| 4 | 1 | 小红 | F | 70 |
| 7 | 2 | 小虎 | M | 85 |
| 8 | 3 | 小丽 | F | 87 |
| 9 | 4 | 小粉 | F | 77 |
| 10 | 3 | 小灰灰 | M | 88 |
| 11 | 3 | 小凯 | M | 78 |
| 12 | 3 | 小白 | M | 97 |
±—±---------±----------±-------±------+

9 rows in set (0.00 sec)

select*from students,classes;

结果：

mysql> select*from students,classes;
±—±---------±----------±-------±------±—±-------+
| id | class_id | name | gender | score | id | name |
±—±---------±----------±-------±------±—±-------+
| 2 | 2 | 大牛 | M | 80 | 1 | 一班 |
| 2 | 2 | 大牛 | M | 80 | 2 | 二班 |
| 2 | 2 | 大牛 | M | 80 | 3 | 三班 |
| 2 | 2 | 大牛 | M | 80 | 4 | 四班 |
| 3 | 1 | 小明 | M | 80 | 1 | 一班 |
| 3 | 1 | 小明 | M | 80 | 2 | 二班 |
| 3 | 1 | 小明 | M | 80 | 3 | 三班 |
| 3 | 1 | 小明 | M | 80 | 4 | 四班 |
| 4 | 1 | 小红 | F | 70 | 1 | 一班 |
| 4 | 1 | 小红 | F | 70 | 2 | 二班 |
| 4 | 1 | 小红 | F | 70 | 3 | 三班 |
| 4 | 1 | 小红 | F | 70 | 4 | 四班 |
| 7 | 2 | 小虎 | M | 85 | 1 | 一班 |
| 7 | 2 | 小虎 | M | 85 | 2 | 二班 |
| 7 | 2 | 小虎 | M | 85 | 3 | 三班 |
| 7 | 2 | 小虎 | M | 85 | 4 | 四班 |
| 8 | 3 | 小丽 | F | 87 | 1 | 一班 |
| 8 | 3 | 小丽 | F | 87 | 2 | 二班 |
| 8 | 3 | 小丽 | F | 87 | 3 | 三班 |
| 8 | 3 | 小丽 | F | 87 | 4 | 四班 |
| 9 | 4 | 小粉 | F | 77 | 1 | 一班 |
| 9 | 4 | 小粉 | F | 77 | 2 | 二班 |
| 9 | 4 | 小粉 | F | 77 | 3 | 三班 |
| 9 | 4 | 小粉 | F | 77 | 4 | 四班 |
| 10 | 3 | 小灰灰 | M | 88 | 1 | 一班 |
| 10 | 3 | 小灰灰 | M | 88 | 2 | 二班 |
| 10 | 3 | 小灰灰 | M | 88 | 3 | 三班 |
| 10 | 3 | 小灰灰 | M | 88 | 4 | 四班 |
| 11 | 3 | 小凯 | M | 78 | 1 | 一班 |
| 11 | 3 | 小凯 | M | 78 | 2 | 二班 |
| 11 | 3 | 小凯 | M | 78 | 3 | 三班 |
| 11 | 3 | 小凯 | M | 78 | 4 | 四班 |
| 12 | 3 | 小白 | M | 97 | 1 | 一班 |
| 12 | 3 | 小白 | M | 97 | 2 | 二班 |
| 12 | 3 | 小白 | M | 97 | 3 | 三班 |
| 12 | 3 | 小白 | M | 97 | 4 | 四班 |
±—±---------±----------±-------±------±—±-------+
36 rows in set (0.00 sec)

查询完有 4 * 9 = 36 条结果，其中正确的结果实际才占用九条，大多数都是错误的结果。

所以需要进行条件筛选

select*from students,classes where students.class_id = classes.id;

这个实际上就是连接查询。

mysql> select*from students,classes where students.class_id = classes.id;

±—±---------±----------±-------±------±—±-------+
| id | class_id | name | gender | score | id | name |
±—±---------±----------±-------±------±—±-------+
| 2 | 2 | 大牛 | M | 80 | 2 | 二班 |
| 3 | 1 | 小明 | M | 80 | 1 | 一班 |
| 4 | 1 | 小红 | F | 70 | 1 | 一班 |
| 7 | 2 | 小虎 | M | 85 | 2 | 二班 |
| 8 | 3 | 小丽 | F | 87 | 3 | 三班 |
| 9 | 4 | 小粉 | F | 77 | 4 | 四班 |
| 10 | 3 | 小灰灰 | M | 88 | 3 | 三班 |
| 11 | 3 | 小凯 | M | 78 | 3 | 三班 |
| 12 | 3 | 小白 | M | 97 | 3 | 三班 |
±—±---------±----------±-------±------±—±-------+

9 rows in set (0.00 sec)

6.连接查询：

先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

实际上连接查询是多表查询的一种。

1）内连接

取两个表的交集

有两种方式：

- 第一种
select*from table1,table2 where table1.id = table2.table1_id;
- 第二种
select*from table1 join table2 on table1.id = table2.table1_id;;

第一种在上面已经展示过了，现在是第二种连接查询的方式。

mysql> select*from students join classes on students.class_id = classes.id;
±—±---------±----------±-------±------±—±-------+
| id | class_id | name | gender | score | id | name |
±—±---------±----------±-------±------±—±-------+
| 2 | 2 | 大牛 | M | 80 | 2 | 二班 |
| 3 | 1 | 小明 | M | 80 | 1 | 一班 |
| 4 | 1 | 小红 | F | 70 | 1 | 一班 |
| 7 | 2 | 小虎 | M | 85 | 2 | 二班 |
| 8 | 3 | 小丽 | F | 87 | 3 | 三班 |
| 9 | 4 | 小粉 | F | 77 | 4 | 四班 |
| 10 | 3 | 小灰灰 | M | 88 | 3 | 三班 |
| 11 | 3 | 小凯 | M | 78 | 3 | 三班 |
| 12 | 3 | 小白 | M | 97 | 3 | 三班 |
±—±---------±----------±-------±------±—±-------+
9 rows in set (0.00 sec)

2）左连接

以 join 左侧的表为主

select*from table1 left join table2 on table1.id = table2.table1_id;

这种查询会尽可能的去展示左侧的表的内容。

插入对应数据：

mysql> insert into classes value(null,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students value(null,null,null,null,null);
Query OK, 1 row affected (0.01 sec)

然后我们现在先看看内连接的结果

select*from students,classes where students.class_id = classes.id;

由于正确的数据，并没有匹配到新增的，因为班级id没有为空的，学生表最后一条没有绑定班级。

再来进行左连接，看看结果

select*from students left join classes on students.class_id = classes.id;

左边的表为学生表，所以会尽可能的展示学生表的内容。

3）右连接

以 join 右侧的表为主

select*from table1 right join table2 on table1.id = table2.table1_id;

进行右连接：

select*from students right join classes on students.class_id = classes.id;

右连接会凸显出右边的表的数据。

二.删除

删除数据库：

drop database [数据库名];

删除表：

drop table [表名];

删除表中的数据：

delete from [table] where [指定条件];

如果没有指定条件，则是删除整张表的数据。当然，表是还在的，只是数据没了，成空表。

三.增加

insert into [table] (输入要插入的字段) value (对应字段的具体数据);
- 默认插入所有数据
insert into [table] value (对应字段的具体数据);
- 示例
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

四.修改

update [table] set [字段]=[修改后的数据] where [条件];
- 示例
UPDATE students SET name='大牛', score=66 WHERE id=1;

五.一些常用的 sql 语句

https://www.liaoxuefeng.com/wiki/1177760294764384/1246617682185952

TODO

六.索引

添加索引：

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

可单列也可多列。
索引的作用在于提高查询效率，但缺点就是插入、修改、删除数据会变慢，因为需要修改索引。索引越多，速度就越慢。所以适合引入索引的情况在于查询多，增删改少。

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。

七.事务

1.什么是事务？

在执行 SQL 语句的时候，某些业务要求，一系列操作必须全部执行，而不能执行一部分，另一部分不执行。

例如转账操作：

-- 从 id = 1 的账户转账给 id = 2 的账户 100 元
-- 一: 将 id = 1 的 A 账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

对于转账操作来说，这两条 SQL 语句必须全部执行，不能只执行一个。如果第一条成功，而第二条失败了，则第一条语句得进行一个回退。

这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

2.事务的四个特性

从以上转账的典型例子，可以得知事务的四个特性：

• 原子性（Atomicity）

​ 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

• 一致性（Consistency）

​ 数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

• 隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

• 持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。（事务满足持久化是为了能应对系统崩溃的情况。）

系统发生崩溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。

其中他们之间的关系：

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对系统崩溃的情况。

3.事务的隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

1）Read Uncommitted

Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

2）Read Committed

在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

3）Repeatable Read

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

4）Serializable

Serializable 是最严重的隔离级别，所有事务按照次序依次执行，因此脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

参考：
https://www.liaoxuefeng.com/wiki/1177760294764384
https://www.runoob.com/mysql/mysql-tutorial.html