5.MySQL数据库高级操作

思考：MySQL中的基本数据操作，包括增删改查，通常可以满足一般简单的数据操作需求。但是数据的操作可能是复杂的，这个时候基本的SQL操作满足不了怎么办？

引入：基础操作可以解决很多问题，但是实际开发中，基本上不会是太多单表操作，因此还会需要一些高级的SQL操作来解决实际的问题。这些高级操作基本上都是针对数据的。

• 新增数据
  • 蠕虫复制
  • 主键冲突
• 查询数据【重要】
  • 查询选项
  • 别名
  • 数据源
  • where子句
  • group by子句
  • having子句
  • order by子句
  • limit子句
• 更新数据
  • 限制更新
• 删除数据
  • 限制删除
  • 清除数据

总结：实际开发中，高级SQL操作能够让一些复杂的数据需求变得简单，而这些高级操作又是项目开发所必须的。

一、新增数据【了解】

思考：数据的插入是通过insert指令将数据一条一条插入到数据库的，有的时候希望快速让数据表的数据增加的话，有没有什么解决方案呢？

引入：数据进入数据库理论上应该是用户业务操作产生，因此数据并不存在批量进入的需求。但是在进行数据测试的时候会需要用到批量产生数据，因此就要用到一些高级数据插入。

1.蠕虫复制【了解】

定义：蠕虫复制就是从已有表中复制数据直接插入到另外一张表，而且还可以快速将数据实现成倍增加。

1. 语法结构：insert into 表名 [(字段列表)] select */[字段列表] from 已经存在数据的表;

   需求：在my_database1中创建一个表my_clone_table，然后从my_database数据库中的my_table表中取出数据存入my_clone_table中

   #创建数据表
   mysql> use my_database1;
   Database changed
   mysql> create table my_clone_table like my_database.my_table;
   Query OK, 0 rows affected (0.76 sec)
   
   #复制数据
   mysql> insert into my_clone_table select * from my_database.my_table;
   Query OK, 5 rows affected (0.51 sec) #5行全部数据实现复制插入
   Records: 5  Duplicates: 0  Warnings: 0
   

总结

1. MySQL可以通过蠕虫复制insert into 表名 [(字段列表)] select */字段列表 from 有可用数据表名;来实现快捷数据递增

2. 蠕虫复制不会用在开发过程中，通常是用在数据表的效率测试

---

思考：在操作数据库的时候，有些主键字段并非使用逻辑字段，而是使用业务数据作为主键。数据插入可能冲突，数据更新可能又不存在对应主键，二者该如何实现呢？

引入：在实际业务中，会碰到需要更新主键对应的其他字段数据，但是又不确定主键是否存在的情况，这个时候就只能先查一下主键是不是存在，然后再进行更新或者插入操作。但是这种操作比较麻烦，针对这种特殊情况，MySQL提供了一种一次性搞定的方式：Replace

2.Replace替换【掌握】

定义：Replace替换，即目标不存在执行插入，目标存在执行替换的SQL操作

1. Replace语法：replace into 表名 [(字段列表)] values(值列表)；

mysql> insert into my_auto values(11,'Lilei',22);
ERROR 1062 (23000): Duplicate entry '11' for key 'PRIMARY'  #主键已经存在11这个值
mysql> replace into my_auto values(11,'Lilei',22);
Query OK, 2 rows affected (0.44 sec)

2. Replace原理

• replace值列表中如果对应主键字段为NULL，系统执行插入操作
• replace值列表中如果有主键字段值，系统就会先去匹配表中是否有该主键
  • 如果匹配不到数据：执行insert操作
  • 如果匹配到数据：则先删除原有数据，然后插入数据
• replace每次需要检索数据，所以没有insert效率高（insert直接找空行即可）

注意：replace在主键冲突的时候是先删除数据后新增数据，所以replace值列表中的数据要与字段本身的需求相满足，必须给所有非空又没有默认值的字段提供数据。

总结

1. 可以使用replace into 表名 [(必要字段列表)] values(值列表)方式来处理可能存在的主键冲突问题
2. replace的原理是不主键冲突则插入，主键冲突则先删除后插入
3. replace的效率没有insert指令高，因此不要轻易使用replace代替insert

---

二、查询数据【掌握】

思考：在进行某些数据查询的时候，好像select会查出所有的数据，如果有的时候只想看看某个字段中出现了哪些不同的数据，这个时候该怎么办呢？

引入：MySQL中的Select查询本质上是有默认==查询选项==的，而这个选项可以用来控制查询结果的显示。

1.查询选项【掌握】

定义：select查询的时候，实际上是存储指令可以控制数据本身的效果的。即主要对于出现的重复数据进行管理。

1. select选项使用指令：select select选项 */字段列表 from 表名;

• all：默认的select选项值，表示保留所有查到的结果
• distinct：去重的，表示对于重复数据只显示一条记录

#使用all关键字
mysql> select all * from my_database1.my_clone_table;
mysql> select distinct * from my_database1.my_clone_table;

2. distinct去重针对的是所有查出来的字段数据（结果），是记录相同则去重，而不是某个字段值重复

mysql> select distinct * from my_database1.my_clone_table;  #针对所有字段，整条记录对比
mysql> select distinct age from my_database1.my_clone_table; #针对age字段数据对比

总结：select查询选项可以帮助我们在进行数据查询的时候进行数据的重复性筛选，默认操作为保留所有查询结果，如果要进行去重操作，可以使用distinct查询选项

---

思考：数据库字段的名字设计的时候，最好的方式是与表名保持一定关系，同时做到见名知意。那么这个时候如果字段的名字很长，那么意味着其他客户端用起来就比较麻烦了，这个时候该怎么取舍呢？

引入：其实MySQL字段或者表名字的定义是为了方便DBA进行数据管理，但是如果是其他软件来访问MySQL，那么访问效果就不那么友好了（名字很长），如果能够变得简单一点的话就好了。因此MySQL提供了一种别名机制。

2.别名【掌握】

定义：别名，即对某些较长名字或者冲突名字（重复）指定一个新的名字，在最后查看数据的时候就可以通过新的名字来进行查看。

1. 别名分为两种：字段别名和表别名，使用方式基本一致：字段名/表名 [as] 别名

mysql> select id as index_id, name '姓名', age '年龄' from my_auto as a;
+----------+-------+------+
| index_id | 姓名  | 年龄 |
+----------+-------+------+
|        1 | Lily  |   19 |
|       10 | Han   |   22 |
|       11 | Lilei |   22 |
|       12 | Tom   |   30 |
+----------+-------+------+
4 rows in set (0.00 sec)

总结

1. 可以通过字段名/表名 [as] 新名字;来实现别名操作
2. 别名的目的是为了让名字变得简洁或者更加方便使用（不重复字段）
3. 别名只能在查询数据的SQL指令中使用（方便操作者使用）
4. 别名一般是在比较复杂的SQL指令中用到，如果SQL指令本身很简单就不用别名

---

思考：在select查询过程中，所有的数据都是来源于某一张，是不是所有的数据都是直接从表中获取呢？

引入：数据的来源称之为数据源，MySQL中数据都存储在数据表中，所以数据表就是数据源。但是MySQL为了方便用户的查询操作，并不强制所有数据源都是一张实际存在的表。

3.数据源【掌握】

定义：凡是跟在from关键字之后，用来提供数据的部分都称之为数据源。MySQL中绝大部分的数据源都是一张实际表，但是根据实际数据操作，也可以来源是多张表或者一个数据查询得到的表。

1. 单表数据源：所有数据的需求就是在一张表

mysql> select * from my_year;

2. 多表数据源：数据需要从多张表获取，这时可以直接在from关键字后跟多张表，使用逗号,分隔即可

mysql> select * from my_year,my_wide;

注意1：此时的查询结果行和列的组成方式是这样的：行数 = 第一张表行数 * 第二张表行数；列数 = 第一张表列数据 + 第二张表列数

mysql> select * from my_year,my_wide;
#my_year中2行数据2列字段，my_wide中1行数据2列字段
+------+------+------+------+
| y1   | y2   | id1  | id2  |
+------+------+------+------+
| 1916 | 2020 | -128 | -128 |
| 2069 | 1970 | -128 | -128 |
+------+------+------+------+
2 rows in set (0.42 sec)

注意2：多表数据源要考虑查询出来的字段名字重复问题（即多张表中有同名字段），这个时候可以使用字段别名和表别名，在选择字段输出的时候使用表名/表别名.字段名

mysql> select * from my_table,my_auto;
+------------+------+-------+----+-------+-----+
| number     | age  | name  | id | name  | age |#同名name和age字段，不好区分使用（PHP会自动合并）
+------------+------+-------+----+-------+-----+
| 0000000001 |   18 | Jack  |  1 | Lily  |  19 |
| 0000000001 |   18 | Jack  | 10 | Han   |  22 |
+------------+------+-------+----+-------+-----+

#使用别名处理
mysql> select t.*,a.id,a.name a_name,a.age a_age from my_table t,my_auto a;
+------------+------+-------+----+--------+-------+
| number     | age  | name  | id | a_name | a_age |
+------------+------+-------+----+--------+-------+
| 0000000001 |   18 | Jack  |  1 | Lily   |    19 |
| 0000000001 |   18 | Jack  | 10 | Han    |    22 |
+------------+------+-------+----+--------+-------+
#t是my_table的别名
#a是my_auto的别名
#a_name是表my_auto中name字段的别名
#a_age是表my_auto中age字段的别名

3. 查询数据源：有的数据来源并非是一张表或者多张表，而是一条查询结果。MySQL规定使用select查询的结果可以作为数据源，但是必须给查询的结果一个别名（假装表名）

mysql> select * from (select * from my_auto) as a;

注意：查询数据源不会像上述SQL指令这么简单，查询数据源的本质是子查询（后续会学）。

总结

1. MySQL中的数据源可以是单表、多表或者一条查询结果
2. 多表数据源在开发中很常见（但是需要配合其他指令），需要注意表中同名字段重复出现问题
3. 使用查询结果作为数据源的时候必须为查询数据源指定别名

---

思考：我们在进行数据查询的时候，是不是总是会查询全部数据呢？

引入：动态网站的特点是按需提供数据，即不同用户看到的数据会不同。MySQL作为数据管理的核心，可以通过where条件判定来实现绝大部分数据的筛选。

4.where子句【掌握】

定义：where条件判定，就是通过在where关键字后使用相关条件判定表达式来实现目标数据的筛选。

1. where语法：where 条件表达式

mysql> select * from my_auto where id = 1;

2. where表达式分类：where后可以跟几类运算符表达式

• 比较运算符
  • >：字段 > 指定值，成功返回true，失败返回false
  • <：字段 < 指定值，成功返回true，失败返回false
  • =====：字段 = 指定值，MySQL中=就是相等比较，相等返回true，不等返回false
  • >=：字段 >= 指定值，成功返回true，失败返回false
  • <=：字段 <= 指定值，成功返回true，失败返回false
  • <>|!=：字段 <>指定值，字段不等于指定值，不等返回true，相等返回false
  • in：字段 in (指定值列表)，字段在某些指定值内存在返回true，不存在返回false
  • between and：字段 between 指定小值 and 指定大值，字段在区间内返回true，否则返回false

#between and
mysql> select * from my_auto where age between 20 and 22;
#in
mysql> select * from my_auto where id in (1,2,3);

• 逻辑运算符
  • &&/==and==：逻辑与，两个条件表达式都满足返回true，否则返回false
  • ||/==or==：逻辑或，两个条件表达式一个满足返回true，都不满足返回false
  • !/==not==：逻辑非，对原结果进行取反操作

mysql> select * from my_auto where id > 10 and age > 20;
mysql> select * from my_auto where id > 10 && age > 20;

• 空运算符
  • is null：字段 is null，字段数据为空返回true，否则返回false
  • is not null：字段 is not null，字段数据不为空返回true，为空返回false

mysql> select * from my_table where number is null;
+--------+------+------+
| number | age  | name |
+--------+------+------+
| NULL   | NULL | NULL |
+--------+------+------+
1 row in set (0.36 sec)

• 匹配运算符
  • like：字段 like '模式'，模式匹配成功返回true，失败返回false

mysql> select * from my_auto where name like 'Li%';
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Lily  |  19 |
| 11 | Lilei |  22 |
+----+-------+-----+
2 rows in set (0.00 sec)

总结

1. where子句的目的是通过条件匹配进行数据的筛选，数据筛选的原理是在数据表（磁盘）进行
2. where中可以通过多种运算符来实现数据匹配：比较运算、逻辑运算、空运算和匹配运算
3. 在使用多种运算符的时候，需要考虑运算符的优先级
4. 如果明确数据目标的情况下，应该尽可能的使用条件判定来减少计算浪费

---

思考：如果在进行数据查询的时候，想根据某个数据进行分组，那么这种时候该怎么操作呢？

引入：在MySQL中，where条件判定可以用来筛选部分数据，使得满足条件的数据成为一组。但是如果还需要其他效果，如果统计组内记录数，还得依赖group by分组统计。

5.group by子句【掌握】

定义：group by即分组的意思，是根据某个具体的字段，将相同的值分到一组中。

先创建一张表，并且插入数据

create table my_student(
id int primary key auto_increment,
name varchar(10) not null,
gender enum('男','女','保密'),
age tinyint unsigned not null,
class_name varchar(10) not null comment '班级名称'
)charset utf8;

insert into my_student values(null,'鸣人','男',18,'木叶1班'),
(null,'佐助','男',18,'木叶1班'),
(null,'佐井','男',19,'木叶2班'),
(null,'大蛇丸','男',28,'木叶0班'),
(null,'卡卡西','男',29,'木叶0班'),
(null,'小樱','女',18,'木叶1班'),
(null,'雏田','女',18,'木叶1班'),
(null,'我爱罗','男',19,'木叶1班'),
(null,'博人','男',8,'木叶10班'),
(null,'向日葵','女',6,'木叶10班'),
(null,'鼬','男',28,'木叶0班');

1. group by语法：where 字句后 group by 字段名

mysql> select class_name from my_student group by class_name;

2. group by分组原理

• 按照分组字段，将获取到的记录分成几块
• 保留每块的第一条记录

3. group by目的：group by分组的目的是为了实现==分组统计==，分组统计主要用到以下聚合函数

• count(*/字段名)：统计分组字段对应的记录数量
• max(字段名)：统计分组后某个字段的最大值
• min(字段名)：统计分组后某个字段的最小值
• avg(字段名)：统计分组后某个字段的平均值
• sum(字段名)：统计分组后某个字段的和
• group_concat(字段名)：统计分组后，某个分组字段的字符串拼接

mysql> select max(age),min(age),avg(age),sum(age),group_concat(name) names,count(class_name) counts,class_name from my_student group by class_name;
+----------+----------+----------+----------+----------------------------+--------+------------+
| max(age) | min(age) | avg(age) | sum(age) | names                      | counts | class_name |
+----------+----------+----------+----------+----------------------------+--------+------------+
|       29 |       28 |  28.3333 |       85 | 大蛇丸,卡卡西,鼬           |      3 | 木叶0班    |
|        8 |        6 |   7.0000 |       14 | 博人,向日葵                |      2 | 木叶10班   |
|       19 |       18 |  18.2000 |       91 | 鸣人,佐助,小樱,雏田,我爱罗 |      5 | 木叶1班    |
|       19 |       19 |  19.0000 |       19 | 佐井                       |      1 | 木叶2班    |
+----------+----------+----------+----------+----------------------------+--------+------------+
4 rows in set (0.00 sec)

4. group by注意事项

• MySQL5.7.5版本后，凡是使用到group by分组统计后，select后的字段信息必须只能是分组字段本身或者使用的聚合函数统计；5.7.5以前允许查看其它非分组字段（每组第一条记录对应字段值）
• count函数在进行字段统计时，不会统计字段值为NULL的字段
• group by子句必须在where子句之后使用（可以没有where子句）：where是从磁盘读取数据，group by是针对已经进入内存的数据进行分组
• 如果想访问其他非分组统计字段，那么可以使用系统函数any_value(想要查看的字段)来实现（也可以修改MySQL配置文件中的严格模式，网上很多方案，输入错误即可查到）

mysql> select max(age),class_name,any_value(name) as name from my_student group by class_name;
+----------+------------+--------+
| max(age) | class_name | name   |
+----------+------------+--------+
|       29 | 木叶0班    | 大蛇丸 |
|        8 | 木叶10班   | 博人   |
|       19 | 木叶1班    | 鸣人   |
|       19 | 木叶2班    | 佐井   |
+----------+------------+--------+
4 rows in set (0.04 sec)

5. 多分组：group by可以针对多个字段进行分组，即根据某个字段分组后，再对分组进行其他字段分组。多分组语法为：group by 字段1,字段2...字段N;

mysql> select class_name,gender,count(*) from my_student group by class_name,gender;
+------------+--------+----------+
| class_name | gender | count(*) |
+------------+--------+----------+
| 木叶0班    | 男     |        3 |
| 木叶10班   | 男     |        1 |
| 木叶10班   | 女     |        1 |
| 木叶1班    | 男     |        3 |
| 木叶1班    | 女     |        2 |
| 木叶2班    | 男     |        1 |
+------------+--------+----------+
6 rows in set (0.00 sec)

6. 分组排序：group by在进行字段分组的同时，也会分组字段进行排序，默认排序为升序ASC，可以使用DESC降序排序，排序语法为 group by 字段 [ASC|DESC];

ysql> select class_name,group_concat(name) from my_student group by class_name desc;
+------------+----------------------------+
| class_name | group_concat(name)         |
+------------+----------------------------+
| 木叶2班    | 佐井                       |
| 木叶1班    | 鸣人,佐助,小樱,雏田,我爱罗 |
| 木叶10班   | 博人,向日葵                |
| 木叶0班    | 大蛇丸,卡卡西,鼬           |
+------------+----------------------------+
4 rows in set (0.00 sec)

7. 回溯统计：回溯统计是指当数据被分组后，系统会在最后额外按照分组字段进行一次统计，此时分组字段会被置空。回溯统计语法为：group by 字段1,字段2...字段N with rollup;

mysql> select class_name,group_concat(name) from my_student group by class_name with rollup;
+------------+--------------------------------------------------------------+
| class_name | group_concat(name)                                           |
+------------+--------------------------------------------------------------+
| 木叶0班    | 大蛇丸,卡卡西,鼬                                             |
| 木叶10班   | 博人,向日葵                                                  |
| 木叶1班    | 鸣人,佐助,小樱,雏田,我爱罗                                   |
| 木叶2班    | 佐井                                                         |
| NULL       | 大蛇丸,卡卡西,鼬,博人,向日葵,鸣人,佐助,小樱,雏田,我爱罗,佐井 |        
+------------+--------------------------------------------------------------+
5 rows in set (0.03 sec)
#class_name作为分组字段进行了一次回溯（只有一次分组）

总结

1. 可以使用group by 字段名来根据字段进行分组
2. 分组的目的是为了进行数据统计，因此配合使用聚合函数：sum()，max()，min()，avg()，count()和group_concat()
3. 分组统计在不同版本中，对于非分组字段（没有使用聚合函数）有查询区别：5.7.5以前允许查询，5.7.5以后不允许查询
4. group by子句与where子句同时存在的时候，必须在where子句之后
5. group by可以实现多字段分组
6. group by分组后会自动根据分组字段进行升序排序，可以控制排序方式为降序DESC
7. group by分组统计可以进行回溯统计，即查询对应字段未分组之前效果

---

思考：where子句是在group by子句之前，但是如果某些结果必须是通过计算之后才能判定结果的，那么这个时候怎么办呢？

引入：group by本身是分组统计的，那么统计之后的确是有需求可能会进行判定的，这个时候就需要在分组统计后再对结果进行条件限定，这个时候可以在group by子句之后使用having子句。

6.having子句【掌握】

定义：where是从磁盘读取数据时进行判定，而在数据进入到内存之后where就不能生效，having是完全针对进入内存后的数据进行判定。

1. having语法：having几乎能做where所能做的所有事情：having 条件判定

mysql> select * from my_student having id = 1;
+----+------+--------+-----+------------+
| id | name | gender | age | class_name |
+----+------+--------+-----+------------+
|  1 | 鸣人 | 男     |  18 | 木叶1班    |
+----+------+--------+-----+------------+
1 row in set (0.00 sec)

2. having主要针对group by后的统计结果进行判定

mysql> select count(*) number,class_name,group_concat(name) from my_student group by class_name having number > 1;
+--------+------------+----------------------------+
| number | class_name | group_concat(name)         |
+--------+------------+----------------------------+
|      3 | 木叶0班    | 大蛇丸,卡卡西,鼬           |
|      2 | 木叶10班   | 博人,向日葵                |
|      5 | 木叶1班    | 鸣人,佐助,小樱,雏田,我爱罗 |
+--------+------------+----------------------------+
3 rows in set (0.40 sec

注意：having子句中用到的字段，必须在select后出现过，即字段从磁盘读入到内存中。

3. having条件判定中可以直接使用聚合函数

mysql> select count(*),class_name,group_concat(name) from my_student group by class_name having count(*) > 1;
+----------+------------+----------------------------+
| count(*) | class_name | group_concat(name)         |
+----------+------------+----------------------------+
|        3 | 木叶0班    | 大蛇丸,卡卡西,鼬           |
|        2 | 木叶10班   | 博人,向日葵                |
|        5 | 木叶1班    | 鸣人,佐助,小樱,雏田,我爱罗 |
+----------+------------+----------------------------+
3 rows in set (0.00 sec)

总结

1. having是一种和where类似的条件判定子句
2. having是针对内存中的数据进行条件判定的，因此可以使用字段别名和聚合函数
3. 如果是字段本身条件筛选，不是进行分组统计后的话，一定使用where

---

思考：在很多实际需求中，用户可以根据看到的某些字段数据进行排序操作，那么这个时候必须要进行分组统计吗？

引入：分组的目标是统计，如果只是单纯的想对某个字段数据进行排序，不能使用group by分组。这个时候可以使用order by来实现排序

7.order by子句【掌握】

定义：order by即通过对某个字段，使用表对应的校对集实现升序或者降序排序。

1. order by语法：order by 字段 [ASC|DESC]; 其中ASC是升序（默认），DESC为降序

mysql> select * from my_student order by age;
+----+--------+--------+-----+------------+
| id | name   | gender | age | class_name |
+----+--------+--------+-----+------------+
| 10 | 向日葵 | 女     |   6 | 木叶10班   |
|  9 | 博人   | 男     |   8 | 木叶10班   |
|  1 | 鸣人   | 男     |  18 | 木叶1班    |
|  2 | 佐助   | 男     |  18 | 木叶1班    |
|  6 | 小樱   | 女     |  18 | 木叶1班    |
|  7 | 雏田   | 女     |  18 | 木叶1班    |
|  3 | 佐井   | 男     |  19 | 木叶2班    |
|  8 | 我爱罗 | 男     |  19 | 木叶1班    |
|  4 | 大蛇丸 | 男     |  28 | 木叶0班    |
| 11 | 鼬     | 男     |  28 | 木叶0班    |
|  5 | 卡卡西 | 男     |  29 | 木叶0班    |
+----+--------+--------+-----+------------+
11 rows in set (0.00 sec)

2. 中文排序：绝大部分字符集的校对集默认是ci即不区分大小写，如果要对中文进行排序，那么必须指定排序方式（可以事先设定表的校对集），order by的中文方式：order by convert(字段 using 字符集) [collate 校对集];

mysql> select * from my_student order by convert(name using gbk); #使用默认校对集gbk_chinese_ci
+----+--------+--------+-----+------------+
| id | name   | gender | age | class_name |
+----+--------+--------+-----+------------+
|  9 | 博人   | 男     |   8 | 木叶10班   |
|  7 | 雏田   | 女     |  18 | 木叶1班    |
|  4 | 大蛇丸 | 男     |  28 | 木叶0班    |
|  5 | 卡卡西 | 男     |  29 | 木叶0班    |
|  1 | 鸣人   | 男     |  18 | 木叶1班    |
|  8 | 我爱罗 | 男     |  19 | 木叶1班    |
| 10 | 向日葵 | 女     |   6 | 木叶10班   |
|  6 | 小樱   | 女     |  18 | 木叶1班    |
| 11 | 鼬     | 男     |  28 | 木叶0班    |
|  3 | 佐井   | 男     |  19 | 木叶2班    |
|  2 | 佐助   | 男     |  18 | 木叶1班    |
+----+--------+--------+-----+------------+
11 rows in set (0.36 sec)

注意：中文的排序方式是按拼音进行排序的（GBK/GB2312），utf8不支持中文比较，一般情况下不会对中文进行排序

3. 多字段排序：即对某个字段排序后，再将排序字段内的其他字段进行排序，排序语法为：order by 字段1 [ASC|DESC],字段2 [ASC|DESC] ...

mysql> select * from my_student order by gender,age desc;
+----+--------+--------+-----+------------+
| id | name   | gender | age | class_name |
+----+--------+--------+-----+------------+
|  5 | 卡卡西 | 男     |  29 | 木叶0班    |
|  4 | 大蛇丸 | 男     |  28 | 木叶0班    |
| 11 | 鼬     | 男     |  28 | 木叶0班    |
|  3 | 佐井   | 男     |  19 | 木叶2班    |
|  8 | 我爱罗 | 男     |  19 | 木叶1班    |
|  1 | 鸣人   | 男     |  18 | 木叶1班    |
|  2 | 佐助   | 男     |  18 | 木叶1班    |
|  9 | 博人   | 男     |   8 | 木叶10班   |
|  6 | 小樱   | 女     |  18 | 木叶1班    |
|  7 | 雏田   | 女     |  18 | 木叶1班    |
| 10 | 向日葵 | 女     |   6 | 木叶10班   |
+----+--------+--------+-----+------------+
11 rows in set (0.00 sec)

总结

1. 可以使用order by 字段名 [ASC|DESC];来对某个字段进行升序|降序排序（整个记录跟着排序）
2. order by可以对多个字段进行排序，排序原则是在第一个字段排序好后（有类似分组效果），然后对应另外字段进行排序
3. 如果碰到需要使用中文进行排序，可以利用gbk排序，使用order by convert(中文字段 using gbk);

---

思考：每次数据查看的时候，不是通过条件获取指定记录就是获取全部记录，有的时候想获取一定数量的记录该怎么实现呢？

引入：查询数据的量可以在查询出来之后，再通过其他手段来实现数据控制（如PHP控制），但是这种时候会出现带宽浪费，增加服务器负荷。因此，mysql提供了一种可以获取指定数量记录的方式limit子句

8.limit子句【掌握】

定义：limit限制的意思，即在获取记录的时候，可以按照设定的记录数来获取对应的数据。

1. 简易版limit：限制获取的记录数，语法规则为：limit 具体数量;

mysql> select * from my_student limit 3;
+----+------+--------+-----+------------+
| id | name | gender | age | class_name |
+----+------+--------+-----+------------+
|  1 | 鸣人 | 男     |  18 | 木叶1班    |
|  2 | 佐助 | 男     |  18 | 木叶1班    |
|  3 | 佐井 | 男     |  19 | 木叶2班    |
+----+------+--------+-----+------------+
3 rows in set (0.00 sec)

2. limit在设定的数量大于记录数量时，就是获取全部数据

mysql> select * from my_student limit 30;
+----+--------+--------+-----+------------+
| id | name   | gender | age | class_name |
+----+--------+--------+-----+------------+
|  1 | 鸣人   | 男     |  18 | 木叶1班    |
|  2 | 佐助   | 男     |  18 | 木叶1班    |
|  3 | 佐井   | 男     |  19 | 木叶2班    |
|  4 | 大蛇丸 | 男     |  28 | 木叶0班    |
|  5 | 卡卡西 | 男     |  29 | 木叶0班    |
|  6 | 小樱   | 女     |  18 | 木叶1班    |
|  7 | 雏田   | 女     |  18 | 木叶1班    |
|  8 | 我爱罗 | 男     |  19 | 木叶1班    |
|  9 | 博人   | 男     |   8 | 木叶10班   |
| 10 | 向日葵 | 女     |   6 | 木叶10班   |
| 11 | 鼬     | 男     |  28 | 木叶0班    |
+----+--------+--------+-----+------------+
11 rows in set (0.00 sec)

3. 综合版limit：限制获取记录数的同时，还可以设定获取数据的起始位置，语法规则为：limit 起始数量,获取数量;

mysql> select * from my_student limit 3,3;
+----+--------+--------+-----+------------+
| id | name   | gender | age | class_name |
+----+--------+--------+-----+------------+
|  4 | 大蛇丸 | 男     |  28 | 木叶0班    |
|  5 | 卡卡西 | 男     |  29 | 木叶0班    |
|  6 | 小樱   | 女     |  18 | 木叶1班    |
+----+--------+--------+-----+------------+
3 rows in set (0.00 sec)

4. limit分页功能实现：分页就是按照起始根据页码不断的计算起始位置，去获取不同数据

• 页码：外部传入
• 数据长度：明确规范数据
• 起始位置：（页码 - 1） * 数据长度
• 分页SQL：limit 起始位置,数据长度;

mysql> select * from my_student limit 0,3; #第一页
mysql> select * from my_student limit 3,3; #第二页
mysql> select * from my_student limit 6,3; #第三页

总结

1. 简易limit可以用来限制数据获取的数量，通常是从第1条记录到指定数量
2. 综合limit可以实现分页，通过计算起始位置和数据长度控制

---

9.查询总结【掌握】

1. MySQL的高级查询操作在实际开发中应用非常频繁
2. SQL查询的完整语句为：select 查询选项 */字段列表[别名]/聚合函数 from 数据源 where子句 group by子句 having子句 order by子句 limit子句;
3. 字段别名、聚合函数、where条件筛选、group by分组统计、order by排序和limit数量控制都是很常用

---

三、更新数据【了解】

思考：如果某些特定情况下在进行数据更新的时候，不是通过条件来限定，而是针对某部分数据的话，应该如何实现呢？

引入：首先，数据的更新在实际业务中一定是根据具体条件而定的，因此update操作一定是通过where条件限定来进行具体更新，极少出现批量。但是MySQL也提供了一种可能存在的批量限制更新方式。

1.限制更新【了解】

定义：限制更新，即在更新的时候，可以限制更新的记录数

1. 更新时使用简易limit限制更新记录数：update 表名 set 字段 = 新值 where 条件判定 limit 更新数量；

mysql> update my_student set age = age + 1 where class_name = '木叶1班' limit 2;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

总结：限制更新是指利用简易limit实现条件筛选的数据后进行部分数据更新，这种应用场景极少，所以可以作为了解知识即可。

---

四、删除数据【掌握】

思考：更新可以限制更新记录数，删除也可以吗？

引入：更新和删除的本质都是数据写操作，因此删除也可以使用限制删除。

1.限制删除【了解】

定义：限制删除，即在设定了删除条件后，可以控制满足条件的删除数量

1. 删除的时候使用简易limit限制删除数量：delete from 表名 where 条件 limit 删除数量;

mysql> delete from my_auto limit 1;
Query OK, 1 row affected (0.48 sec)

总结：限制删除和限制更新一样，没有太多实际业务需求对应，所以只需要了解一下该知识即可。

---

思考：有主键的表在删除数据的时候，发现如果重新插入数据自动增长的话，逻辑主键的值不是从1重新开始，这个时候该怎么办呢？

引入：数据表的自动增长不会受delete的影响，因为其属于表层。所以如果要删除数据的同时希望自动增长从1开始，那么需要重设auto_increment的值。但是MySQL提供了一种简单的方式truncate

2.清除数据【掌握】

定义：清除数据与delete删除数据不同，清除数据是将表结构和数据删除，然后重建一张新表，因此可以实现表结构的auto_increment重置为初始值。

1. 清除语法：truncate 表名;

mysql> show create table my_auto\G
*************************** 1. row ***************************
       Table: my_auto
Create Table: CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> truncate my_auto;
Query OK, 0 rows affected (0.32 sec)

mysql> show create table my_auto\G
*************************** 1. row ***************************
       Table: my_auto
Create Table: CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

总结：truncate清除表数据的同时可以清除结构性的变化，实现表重置。但是在清除表之前切记对重要数据进行备份安排。