MySQL 数据库的数据增删改查使用

[TOC]
## 1. 基础设置
创建三张表，分别为学生表【my_student】、班级表【my_classs】、学生班级关联表【my_studentClasssRelation】,创建语句请[点击这里查看](/blog/info-md.html/35A410F82B2B5445?type=195)

## 2. 新增数据命令
### 2.1. 基础新增
#### 2.1.1. 全表字段插入
__语法：__insert into 表 values([值1],[值2],[值3],[值4],……),([值1],[值2],[值3],[值4],……)
给全表字段插入数据，不需要指定字段列表，要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致，凡是非数值数据要使用单引号包裹,一次性可以插入多条

```SQL
-- 单条插入
insert into my_student values('2000-10-01',1,'曹操','男',1.72,56,'汉');
-- 多条插入
insert into my_student values('2001-02-01',2,'孙权','男',1.62,46,'汉'),('1998-02-01',3,'孙策','男',1.82,50,'汉');
```
__结果：__

| birthday   | id | name | gender | height | age | nation |
|------------|----|------|--------|--------|-----|--------|
| 2000-10-01 |  1 | 曹操 | 男     |   1.72 |  56 | 汉     |
| 2001-02-01 |  2 | 孙权 | 男     |   1.62 |  46 | 汉     |
| 1998-02-01 |  3 | 孙策 | 男     |   1.82 |  50 | 汉     |

#### 2.1.2. 指定字段插入
__语法：__insert into 表 ([属性1],[属性2],[属性3],[属性4],……) values([值1],[值2],[值3],[值4],……),([值1],[值2],[值3],[值4],……)
给部分字段插入数据，需要选定字段列表，字段列表出现的顺序可以与表设计字段的顺序不一致，但是值列表的顺序必须与选定字段列表的顺序一致,同样一次性也可以插入多条

```SQL
-- 跳过单条插入直接插入多条
insert into my_student(birthday,id,name,gender) values('1988-03-11',4,'刘备','男'),('2001-04-21',5,'刘禅','男');
```
__结果：__

| birthday   | id | name | gender | height | age | nation |
|------------|----|------|--------|--------|-----|--------|
| 1988-03-11 |  4 | 刘备 | 男     |   NULL |  NULL | NULL |
| 2001-04-21 |  5 | 刘禅 | 男     |   NULL |  NULL | NULL |

### 2.2. 蠕虫复制
蠕虫复制其实就是查询插入，从已有表复制数据到指定的表中，不要求是同样的表结构，只要数据符合数据类型即可插成功

__使用场景：__

+ 快速的膨胀数据，蠕虫复制可以自己复制自己，增长速度成指数增长，这样可以快速的增加数据量，可用来测试表的压力和查询效率
+ 可以汇总多张表数据，保存到新的表中，提高数据查询的效率，常用于汇总统计报表

__语法：__insert into 表1 ([表1.属性1],[表1.属性2],[表1.属性3],[表1.属性4],……) select [表2.值1],[表2.值2],0,rank(),…… from 表2
根据查询的结果插入新数据，这里需要指明新插入数据的列属性，select中除了查询结果的表属性，也可以是一个常量，或一个函数，常量则插入的记录中对应列都是这个常量值，函数则根据函数结果插入到对应列中，既然是查询自然可以通过where条件进行数据的筛选，使用时需注意插入指定的列和查询输出的列表需要一一对应

```SQL
-- 将学生id和班级id相同记录更新到学生班级关联表中
insert into my_studentClasssRelation (student,classs,level)
select my_student.id, my_classs.id, my_classs.level
from my_student,my_classs where my_student.id = my_classs.id;
```
__结果：__

| id | student | classs | level | createTime | endTime | endReason |
|----|---------|--------|-------|------------|---------|-----------|
|  1 |       2 |      2 |     7 | 2023-03-03 | NULL    | NULL      |
|  2 |       3 |      3 |     7 | 2023-03-03 | NULL    | NULL      |
|  3 |       4 |      4 |    10 | 2023-03-03 | NULL    | NULL      |
|  4 |       5 |      5 |    10 | 2023-03-03 | NULL    | NULL      |

### 2.3. 主键冲突
在数据插入的时候，如果主键对应的值已经存在，插入会因为主键冲突而失败，主键冲突（Duplicate key）处理的办法有以下两种

__一般的使用场景：__表中没有逻辑主键id、或主键并没有设置自增长而是靠人为填写的、再或则是采用业务主键插入数据的时候，比如订单号是主键，这时可能不确定订单号是否已经被使用了，就可以使用主键冲突的方式处理，下面提供了两种解决方式

#### 2.3.1. 更新
主键冲突时，采用更新的数据的方式处理，这种情况更新字段中必须有主键字段

__语法：__insert into 表 ([主键字段],[属性2],[属性3],[属性4],……) values([主键值1],[值2],[值3],[值4],……),([值1],[值2],[值3],[值4],……) on duplicate key update 属性2 = 值2,属性3 = 值3……;

__示例：__

原数据

| id   | birthday | name | gender | height | age | nation |
|--|--|--|--|--|--|--|
| 2 |  1988-03-11 | 刘备 | 男     |   NULL |  NULL | NULL |

操作语句

```SQL
-- 添加时主键冲突后，采用更新的方式修改数据
insert into my_student(id,birthday,name,gender) values(2,'1988-09-11','刘玄德','男') on duplicate key update birthday = '1988-09-11', name = '刘玄德', gender = '男';
-- Query OK, 2 rows
-- 上面的语句执行完后，受影响的行数有2条，即使插入失败也算数据受到影响，
```

执行完上面语句后

| id   | birthday | name | gender | height | age | nation |
|--|--|--|--|--|--|--|
| 2 |  1988-09-11 | 刘玄德 | 男     |   NULL |  NULL | NULL |

#### 2.3.1. 替换
主键冲突时，采用替换的方式处理，这种情况更新字段中必须有主键字段

__语法：__replace into 表 ([主键字段],[属性2],[属性3],[属性4],……) values([主键值1],[值2],[值3],[值4],……),([值1],[值2],[值3],[值4],……);

__示例：__

原数据

| id   | birthday | name | gender | height | age | nation |
|--|--|--|--|--|--|--|
| 2 |  1988-09-11 | 刘玄德 | 男     |   NULL |  NULL | NULL |

```SQL
-- 插入时主键冲突后，采用删除原来数据再新增的替换方式更新数据
replace into my_student(id,birthday,name,gender,height,age) values(2,'1988-09-11','刘玄德','男',172,52);
-- Query OK, 2 rows
-- 上面的语句执行完后，受影响的行数有2条，那是因为先删除了原来的记录，然后新增了新的数据。
--------------------------------------------------------------
-- 执行替换方式如果没有主键冲突，那么就没有删除的动作，直接新增
replace into my_student(id,birthday,name,gender,height,age) values(10,'1998-09-11','刘协','男',172,26);
-- Query OK, 1 rows
```

执行完上面语句后

| id   | birthday | name | gender | height | age | nation |
|--|--|--|--|--|--|--|
| 2 |  1988-09-11 | 刘玄德 | 男     |   172 |  52 | NULL |
| 10 |  1998-09-11 | 刘协 | 男     |   172 |  26 | NULL |

## 3. 查询数据命令
### 3.1. 查看所有数据
__语法：__select * from 表
查询表的所有数据，星符号代表的就是这个查询结果的所有列，如果是多张表联合查询则代表的就是这些表的所有列

```SQL
select * from my_student;
select all * from my_student;
-- 上面的两条语句是等效的，查询的结果是一样的
```
__结果：__

### 3.2. 条件查询
条件查询就是 `where` 子句，用来判断数据，筛选数据的，`where` 子句返回结果 0 和 1，0：false，1：true，所以它是一个判断表达式，常用的运算分为比较运算符和逻辑运算符

+ 比较运算符：> , < , >= , <= , != , <> , = , like , between and , in
 - between and：between左边的值必须小于或等于右边的值
+ 逻辑运算符：and , or , not
 - AND和OR一起使用时，如果没有使用()小括号包裹，则都会优先执行AND，在执行OR，不分顺序的
+ 关键字：interval
 - interval是间隔的意思，例如interval 3 year为间隔3年

__语法：__select [属性1],[属性2],[属性3] from 表 [where [属性1 = 值1] AND [属性2 = '值2']]
查询指定的字段只需要在`select`后面跟随指定字段即可，条件查询则需要使用关键`where`然后在后面跟随bool表达式，字段值除数字外都需要添加单引号包裹

__Where原理：__where 是唯一一个直接从磁盘获取数据的时候，就开始判断数据的的有效性，从磁盘取出一条数据后，就开始进行where判断，结果成立则放到内存，失败则放弃

```SQL
-- 查询id=1的学生信息
select id,name,birthday,age from my_student where id = 1;
-- in 查询id为1,3,5的信息，（落在集合中）
select id,name,birthday,age from my_student where id in (1,3,5);
-- between 查询id从2到6的数据，包含了2和6的数据（使用not between 就是排除这个范围的数据）
select id,name,birthday,age from my_student where id between 2 and 6;
-- interval 查询迄今3年内的数据
select * from my_student where birthday > now() - interval 3 year;
```
__结果：__

| id | name | birthday | age |
|----|------|----------|-----|
|  1 | 曹操  |2000-10-01|  56 |

### 3.3. 字段别名
字段别名使用关键字 `AS`，有时为了更清晰的理解字段意义可以使用别名来输出列名，还有如果在使用多表联合查询时，可能会出现表与表之间有字段重名的情况，这时使用字段别名就可以很好的区分出来了

__语法：__select [属性1] AS '别名1',[属性2] AS '别名2' from 表

```SQL
-- 查询id=1的学生信息
select name AS 名字,birthday AS 生日,age AS 年龄 from my_student where id = 1;
```

__结果：__上面语句字段都使用了别名，在输出结果后可以看到，列的表头显示的都是别名

| 名字 | 生日 | 年龄 |
|--|--|--|
| 曹操  |2000-10-01|  56 |

### 3.4. 去重查询
这里去重指的是：查出来的结果，`所有的字段都相同`，这样的记录才能有效的去重查询

__语法：__select distinct * from 表

__示例：__下面为my_student表的数据源

| id | name | gender | height | age | nation | birthday   |
|----|------|--------|--------|-----|--------|------------|
|  1 | 曹操 | 男     |   1.72 |  56 | 汉     | 2000-10-01 |
|  2 | 曹操 | 男     |   1.72 |  46 | 汉     | 2001-02-01 |
|  3 | 曹操 | 男     |   1.82 |  50 | 汉     | 1998-02-01 |

```SQL
select distinct name,gender,height from my_student
```

结果：my_student表中id=1和id=2在上面的查询语句中，name、gender、height三个字段满足了有效重复，而id=3的记录因为height字段不一样无法满足有效重复，因此查询结果如下

| name | gender | height |
|------|--------|--------|
| 曹操 | 男     |   1.72 |
| 曹操 | 男     |   1.82 |

### 3.5. 多表查询
从一张表中取一条数据，匹配另外一张表的全部数据，最后结果是得到两张表的笛卡尔积,如果还有三张表，则将前面两张组合得到的新数据源与第三张表再取笛卡尔积，以此类推多表就是重复笛卡尔积的动作

__语法：__select * from 表1,表2,……

__示例：__分别有学生表my_student和老师表my_teacher，它们的数据源如下

学生表：

| id | birthday   | name   | gender | height | age  | nation |
|----|------------|--------|--------|--------|------|--------|
|  2 | 1988-09-11 | 刘玄德 | 男     |   1.72 |   58 | 汉     |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |

老师表：

| id | name | gender | subject | age  |
|----|------|--------|---------|------|
|  2 | 曹操 | 男     |       1 |   58 |
|  3 | 曹仁 | 男     |       2 |   56 |

查询语句：
```SQL
-- 查询每个学生与每个老师的对应关系
select * from my_student,my_teacher;
```
结果：

| id | birthday   | name   | gender | height | age  | nation | id | name | gender | subject | age  |
|----|------------|--------|--------|--------|------|--------|----|------|--------|---------|------|
|  2 | 1988-09-11 | 刘玄德 | 男     |   1.72 |   58 | 汉     |  3 | 曹仁 | 男     |       2 |   56 |
|  2 | 1988-09-11 | 刘玄德 | 男     |   1.72 |   58 | 汉     |  2 | 曹操 | 男     |       1 |   58 |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |  3 | 曹仁 | 男     |       2 |   56 |
|  3 | 2021-11-12 | 刘禅   | 男     |   1.82 |   16 | 汉     |  2 | 曹操 | 男     |       1 |   58 |

> PS：多表查询，数据量大时会严重影响查询性能，慎用

### 3.6. 子查询
将查询的结果当成一张新表，再对它进行查询，这就是子查询

__语法：__select \* from (select \* from 表1) as 表2

__示例：__数据源取的[多表查询的示例](#h4-3-5-)

查询语句：
```SQL
-- 查询学生id是2的学生以及对应老师的笛卡尔积
select * from (select * from my_student where id = 2) as T ,my_teacher;
```
结果：

## 4. 更新数据命令
__语法：__update 表 [属性1 = 值1],[属性2 = 值2],[属性3 = 值3] from 表 [where [属性1 = 值1] AND [属性2 = '值2']]
更新数据时建议都要添加`where`条件，例如要将[属性1]的值更新为2，我们固定的就可以添加`where [属性1] <> 2`,这样做的好处就是更精准更新记录，避免了原本值就是2的数据进行了更新操作，减少I/O的开销

```SQL
-- 将id=1的学生生日修改为1963-10-01
update my_student set birthday = '1963-10-01' where id = 1;
-- 更新完成结果
select id,name,birthday from my_student where id = 1;
```
__结果：__

| id | name | birthday |
|----|------|----------|
|  1 | 曹操  |1963-10-01|

> PS：更新语句不一定会成功，例如更新的数据不存在，又或者更新的内容没有变更，所有判断更新是否成功的依据是要看更新命令执行后受影响的行数来判定，在更新指令执行完成后会返回提示Row matched:1 Changed:1 Warnings:0，Changed值大于0（受影响行数大于0）表示更新成功了

### 4.1. 限制记录数更新
在满足条件的数据中限制更新的数量，例如：老师布置的作业，完成的作业的学生有10个，但老师只想奖励前三个完成作业的同学，给前三个完成作业的同学标记上奖励标记。

__语法：__update 表 set 字段 = 值 where 字段2 = 值2 [limit 数值]，这里的数值就是限制的数量

```SQL
-- 更新my_work表中状态是1的数据，将它的flag字段更新为1，但如果满足条件的数据大于3条，那也只会更新前3条
update my_work set flag = 1 where status = 1 limit 3
```

## 5. 删除数据命令
__语法：__delete from 表 [where [属性1 = 值1] AND [属性2 = '值2']]
删除操作数据不可逆，操作前需谨慎，尽量细化`where`条件，没有`where`条件将对整个表进行删除操作

```SQL
-- 将id=1的学生生日修改为1963-10-01
delete from my_student where id = 1;
-- 更新完成结果
select id,name,birthday from my_student;
```

__结果：__

| id | name | birthday   |
|----|------|------------|
|  2 | 孙权 | 2001-02-01  |
|  3 | 孙策 | 1998-02-01  |

>PS：如果表中有主键自增长字段，清空记录或删除最新的记录，自增长不会还原，如果要自增从新开始，需要使用`truncate`

### 5.1. 限制记录数删除
在满足条件的数据中限制删除的数量，用法和限制更新类似

__语法：__delete from 表 where 字段 = 值 [limit 数值]，这里的数值就是限制的数量

```SQL
-- 删除my_work表中状态是0的数据，但如果满足条件的数据大于3条，那也只会删除前3条记录
delete from my_work where status = 0 limit 3
```

### 5.2. Truncate清空表
Truncate的是实现原理是，先删除原来的表，然后根据之前保存在数据库文件中的表结构语句，再重新创建新表

__语法：__truncate 表

```SQL
truncate my_work
```