# 高级数据库操作

## 1、新增数据

#### 多数据插入

只要写一次insert指令，但是可以直接插入多条记录

基本语法：insert into 表名 \[(字段列表)] values(值列表), (值列表)…;

```
mysql> insert into class values(4,'四年级',45);
Query OK, 1 row affected (0.06 sec)

mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
+----------+------------+--------------+
4 rows in set (0.02 sec)
mysql> insert into class values(5,'五年级',49),(6,'六年级',56);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
+----------+------------+--------------+
6 rows in set (0.02 sec)
```

#### 主键冲突

主键冲突：在有的表中，使用的是业务主键（字段有业务含义），但是往往在进行数据插入的时候，又不确定数据表中是否已经存在对应的主键。

1、 主键冲突更新：

类似插入数据语法，如果插入的过程中主键冲突，那么采用更新方法。

Insert into 表名 \[(字段列表)] values(值列表) on duplicate key update 字段 = 新值;

2、 主键冲突替换：

当主键冲突之后，干掉原来的数据，重新插入进去。

Replace into 表名 \[(字段列表)] values(值列表);

#### 蠕虫复制

蠕虫复制：一分为二，成倍的增加。从已有的数据中获取数据，并且将获取到的数据插入到数据表中。

基本语法：

Insert into 表名 \[(字段列表)] select \*/字段列表 from 表;

注意：

1、 蠕虫复制的确通常是重复数据，没有太大业务意义：可以在短期内快速增加表的数据量，从而可以测试表的压力，还可以通过大量数据来测试表的效率（索引）

2、 蠕虫复制虽好，但是要注意主键冲突。

## 2、更新数据

1、 在更新数据的时候，特别要注意：通常一定是跟随条件更新

Update 表名 set 字段名 = 新值 where 判断条件;

```
mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
|        7 | 六年级     |           66 |
+----------+------------+--------------+
7 rows in set (0.34 sec)

mysql> update class set class_name = '七年级' where class_number = '66';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
|        7 | 七年级     |           66 |
+----------+------------+--------------+
7 rows in set (0.03 sec)
```

2、 如果没有条件，是全表更新数据。但是可以使用limit 来限制更新的数量；

Update 表名 set 字段名 = 新值 \[where 判断条件] limit 数量;

改变4个a变成e

Update my\_simple set name = ‘e’ where name = ‘a’ limit 4;

## 3、删除数据

1、 删除数据的时候尽量不要全部删除，应该使用where进行 判定；

```
mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
|        7 | 七年级     |           66 |
+----------+------------+--------------+
7 rows in set (0.03 sec)

mysql> delete from class where class_id = 7;
Query OK, 1 row affected (0.05 sec)

mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
+----------+------------+--------------+
6 rows in set (0.03 sec)
```

2、 删除数据的时候可以使用limit来限制要删除的具体数量

Delete删除数据的时候无法重置auto\_increment

## 4、查询数据

完整的查询指令：

Select select选项 字段列表 from 数据源 where条件 group by分组 having条件 order by排序 limit限制;

Select选项：系统该如何对待查询得到的结果

All：默认的，表示保存所有的记录

```
select all * from tables;
```

Distinct：去重，去除重复的记录，只保留一条（所有的字段都相同）

```
select distinct * from tables;
```

字段列表：有的时候需要从多张表获取数据，在获取数据的时候，可能存在不同表中有同名的字段，需要将同名的字段命名成不同名的：别名 alias

基本语法：字段名 \[as] 别名

```
select distinct name as name1,name name2 from table;
```

```
mysql> select all * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
+----------+------------+--------------+
6 rows in set (0.07 sec)

mysql> select class_id as id,class_name name,class_number num from class;
+------+-----------+------+
| id   | name      | num  |
+------+-----------+------+
|    1 | 一年级    |   60 |
|    2 | 二年级    |   40 |
|    3 | 三年级    |   50 |
|    4 | 四年级    |   45 |
|    5 | 五年级    |   49 |
|    6 | 六年级    |   56 |
+------+-----------+------+
6 rows in set (0.05 sec)
```

## 5、From数据源

From是为前面的查询提供数据：数据源只要是一个符合二维表结构的数据即可。

### 1、单表数据

From 表名;

```
select name1,name2,name3 from table;
```

### 2、多表数据

从多张表获取数据，基本语法：from 表1,表2…

```
select * from table1,table2;
```

结果：两张表的记录数相乘，字段数拼接

本质：从第一张表取出一条记录，去拼凑第二张表的所有记录，保留所有结果。得到的结果在数学上有一个专业的说法：笛卡尔积，这个结果出了给数据库造成压力，没有其他意义：应该尽量避免出现笛卡尔积。

### 3、动态数据

From后面跟的数据不是一个实体表，而是一个从表中查询出来得到的二维结果表（子查询）。

基本语法：from (select 字段列表 from 表) as 别名;

```
select * from (select int_id,int_name from my_int) as tables;
```

## 6、Where子句

Where字句：用来从数据表获取数据的时候，然后进行条件筛选。

数据获取原理：针对表去对应的磁盘处获取所有的记录（一条条），where的作用就是在拿到一条结果就开始进行判断，判断是否符合条件：如果符合就保存下来，如果不符合直接舍弃（不放到内存中）

Where是通过运算符进行结果比较来判断数据。

```
select name from tables where age = 20;
```

## 7、Group by子句

group by表示分组的含义：根据指定的字段，将数据进行分组：分组的目标是为了统计

### 1、分组统计

基本语法： group by 字段名;

```
select * from table group by class_id;
```

Group by是为了分组后进行数据统计的，如果只是想看数据显示，那么group by没什么含义：group by将数据按照指定的字段分组之后，只会保留每组的第一条记录。

利用一些统计函数：（聚合函数）

count()：统计每组中的数量，如果统计目标是字段，那么不统计为空NULL字段，如果为\*那么代表统计记录

avg()：求平均值

sum()：求和

max()：求最大值

min()：求最小值

Group\_concat()：为了将分组中指定的字段进行合并（字符串拼接）

```
select class_id,group_concat(int_name,int_number) from table group by class_id;
```

多分组

将数据按照某个字段进行分组之后，对已经分组的数据进行再次分组

基本语法：group by 字段1,字段2; //先按照字段1进行排序，之后将结果再按照字段2进行排序，以此类推。

```
select class_id,group_concat(int_name) from table group by class_id，class_name;
```

分组排序

Mysql中，分组默认有排序的功能：按照分组字段进行排序，默认是升序

基本语法：group by 字段 \[asc|desc]，字段 \[asc|desc]

```
select class_id,group_concat(int_name) from table group by class_id asc，class_name desc;
```

### 2、回溯统计

当分组进行多分组之后，往上统计的过程中，需要进行层层上报，将这种层层上报统计的过程称之为回溯统计：每一次分组向上统计的过程都会产生一次新的统计数据，而且当前数据对应的分组字段为NULL。

基本语法：group by 字段 \[asc|desc] with rollup;

```
select class_id,cout(*) from table group by class_id with rollup;
```

多分组回溯统计

```
select class_id,cout(*) from table group by class_id,class_name with rollup;
```

## 8、Having子句

Having的本质和where一样，是用来进行数据条件筛选。

1、 Having是在group by子句之后：可以针对分组数据进行统计筛选，但是where不行

查询班级人数大于等于4个以上的班级

Where不能使用聚合函数：聚合函数是用在group by分组的时候，where已经运行完毕

Having在group by分组之后，可以使用聚合函数或者字段别名（where是从表中取出数据，别名是在数据进入到内存之后才有的）

```
select class_id,count(*) as number from my_student group by class_id having number >= 4;
```

强调：having是在group by之后，group by是在where之后：where的时候表示将数据从磁盘拿到内存，where之后的所有操作都是内存操作。

## 9、Order by子句

Order by排序：根据校对规则对数据进行排序

基本语法：order by 字段 \[asc|desc]; //asc升序，默认的

```
select * from tables order by stu_height asc;
```

Order by也可以像group by一样进行多字段排序：先按照第一个字段进行排序，然后再按照第二个字段进行排序。

Order by 字段1 规则,字段2 规则;

```
select * from tables order by class_id desc,stu_height;
```

## 10、Limit子句

Limit限制子句：主要是用来限制记录数量获取

### 1、记录数限制

纯粹的限制获取的数量：从第一条到指定的数量

基本语法： limt 数量;

```
select * from class limit 2;
```

Limit通常在查询的时候如果限定为一条记录的时候，使用的比较多：有时候获取多条记录并不能解决业务问题，但是会增加服务器的压力。

### 2、分页

利用limit来限制获取指定区间的数据。

基本语法：limit offset,length; //offset偏移量：从哪开始，length就是具体的获取多少条记录

Mysql中记录的数量从0开始

Limit 0,2; 表示获取前两条记录

```
mysql> select * from class limit 0,3;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
+----------+------------+--------------+
3 rows in set (0.05 sec)

mysql> select * from class limit 3,3;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
+----------+------------+--------------+
3 rows in set (0.05 sec)
```

注意：limit后面的length表示最多获取对应数量，但是如果数量不够，系统不会强求


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://beret81.gitbook.io/study-notes/mysql-shu-ju-ku/untitled-1.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.