MySQL数据库：表的增删查改
2023-12-22 16:09:34
目录
一.?向表中添加数据
1.1?insert插入
语法：insert into?表名称 (字段1,?字段2, ... ...)
? ? ? ? ? ?values (值11,?值12, ... ...), values (值21,?值22, ... ...);
解释：如果不显示指出字段，那么就是对表中全部字段都进行插入，如果需要多行插入，那么就在values后面给出多组数据即可。
以下面的students表为例，演示通过insert插入单行和多行数据的方法。
mysql> create table students (
-> id int unsigned primary key,
-> name varchar(20) not null,
-> qq varchar(20)
-> );
Query OK, 0 rows affected (0.20 sec)insert into?插入单行数据：
mysql> insert into students values (1, '孙权', '11111');
Query OK, 1 row affected (0.04 sec)
mysql> insert into students values (2, '刘备', NULL);
Query OK, 1 row affected (0.04 sec)insert into?同时插入多行数据：
mysql> insert into students (id, name) values
-> (3, '曹操'), (4, '吕布');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0通过select * from students指令查看插入结果，可见成功插入了4行数据：
mysql> select * from students;
+----+--------+-------+
| id | name | qq |
+----+--------+-------+
| 1 | 孙权 | 11111 |
| 2 | 刘备 | NULL |
| 3 | 曹操 | NULL |
| 4 | 吕布 | NULL |
+----+--------+-------+
4 rows in set (0.01 sec)1.2?主键（唯一键）冲突问题
直接使用insert into向表中插入数据，如果发生了主键（唯一键）冲突，那么数据就会插入失败。
mysql> insert into students values (3,'曹植',22222);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'如果不改变主键（唯一键）值，有两种方式可以在主键冲突的情况下插入或修改数据：
- 通过on duplicate判断，在发生冲突时更改相应行的内容。
- 通过replace指令，发生主键或唯一键冲突的时候，先删除发生冲突的行，再重新插入。
使用duplicate在发生主键或唯一键冲突时更新字段值：?
语法：insert into ...... on duplicate key update?字段1=值1,?字段2=值2, ...... ;
解释：如果发生冲突，那么就将发生冲突的行的相应字段值更新；如果没有发生冲突，那么就相当于正常的insert into插入操作。
在不同的情况下，duplicate影响的行数有所不同：
- 如果不发生冲突，那么单纯的进行插入，影响1行数据。
- 如果发生冲突，但update的新值与原来的表中数据一样，那么影响0行数据。
- 如果发生冲突，update的新值与原来表中的数据不同，那么影响2行数据。
mysql> insert into students values (3,'曹操',22000);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into students values (3,'曹操',22000)
-> on duplicate key update name='曹操',qq='22000';
Query OK, 2 rows affected (0.01 sec)使用replace在发生冲突时进行整行替换：
语法：replace into?表名称 (字段1, 字段2, ......) values (值1,值2, ......);
解释：如果主键（唯一键）冲突，那么删除原始行后更新，如果不冲突直接插入数据。
在不同的情况下，replace影响的行数也有所不同：
- 如果发生冲突，那么影响2行数据。
- 如果不发送冲突，那么影响1行数据。
mysql> replace into students values(3,'曹阿瞒',22200); -- 主键冲突
Query OK, 2 rows affected (0.04 sec)
mysql> replace into students values(5,'袁绍',33333); -- 不冲突
Query OK, 1 row affected (0.03 sec)
row_count函数：查看上次操作受影响的行数。
语法：select row_count();
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)二.?表的查询
本章使用下面代码创建的exam_result表，插入多行数据来演示表的查询操作。
mysql> create table exam_result (
-> id int primary key auto_increment,
-> name varchar(10) not null,
-> math float(4,2) comment '数学成绩',
-> chinese float(4,2) comment '语文成绩',
-> english float(4,2) comment '英语成绩'
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into exam_result (name,math,chinese,english) values
-> ('周瑜',99,82,85),
-> ('孙策',78,87,95),
-> ('孙仲谋',64,88,58),
-> ('刘备',59,73,45),
-> ('曹操',87,76,90),
-> ('吕布',77,84,62);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 02.1?select指定列查询
全列查询：
在实际项目中一般不采用全列查询，因为一般生产实践中的数据库会存储海量数据，并且数据库为远端网络服务，进行全列查询容易因资源不足而造成卡死。?
语法：select * from TableName;
解释：*为通配符，会显示表中全部的内容，
mysql> select * from exam_result;
+----+-----------+-------+---------+---------+
| id | name | math | chinese | english |
+----+-----------+-------+---------+---------+
| 1 | 周瑜 | 99.00 | 82.00 | 85.00 |
| 2 | 孙策 | 78.00 | 87.00 | 95.00 |
| 3 | 孙仲谋 | 64.00 | 88.00 | 58.00 |
| 4 | 刘备 | 59.00 | 73.00 | 45.00 |
| 5 | 曹操 | 87.00 | 76.00 | 90.00 |
| 6 | 吕布 | 77.00 | 84.00 | 62.00 |
+----+-----------+-------+---------+---------+
6 rows in set (0.00 sec)指定列查询：
在select后面指定要进行查询的列，在不同列之间通过逗号进行分隔。
语法：select?列1, 列2, ......?from?表名称;
mysql> select id,name,math from exam_result; -- 查询学号、姓名和数学成绩
+----+-----------+-------+
| id | name | math |
+----+-----------+-------+
| 1 | 周瑜 | 99.00 |
| 2 | 孙策 | 78.00 |
| 3 | 孙仲谋 | 64.00 |
| 4 | 刘备 | 59.00 |
| 5 | 曹操 | 87.00 |
| 6 | 吕布 | 77.00 |
+----+-----------+-------+
6 rows in set (0.00 sec)
可以查询用户自定义的表达式：
mysql> select 10, english+10, math+chinese+english from exam_result;
+----+------------+----------------------+
| 10 | english+10 | math+chinese+english |
+----+------------+----------------------+
| 10 | 95.00 | 266.00 |
| 10 | 105.00 | 260.00 |
| 10 | 68.00 | 210.00 |
| 10 | 55.00 | 177.00 |
| 10 | 100.00 | 253.00 |
| 10 | 72.00 | 223.00 |
+----+------------+----------------------+
6 rows in set (0.00 sec)在向前端界面打印查询结果时，对用户自定义的表达式进行重命名：
语法：select?表达式 [as] 新名称 from?表名称;
解释：as?可以省略，重命名是在对表中数据查询完成后，向前端打印的时候才执行的。
下面的代码打印出students中所有学生的总成绩(math + chinese + english)并重命名为total。
mysql> select id,name,math+english+chinese total from exam_result;
+----+-----------+--------+
| id | name | total |
+----+-----------+--------+
| 1 | 周瑜 | 266.00 |
| 2 | 孙策 | 260.00 |
| 3 | 孙仲谋 | 210.00 |
| 4 | 刘备 | 177.00 |
| 5 | 曹操 | 253.00 |
| 6 | 吕布 | 223.00 |
+----+-----------+--------+
6 rows in set (0.00 sec)去重查询：
语法：select distinct?字段 from?表名称;
解释：from执行优先级高于distinct，先完成筛选再去重输出。
示例：对数学成绩去重输出
mysql> update exam_result set math=78 where id=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select math from exam_result;
+-------+
| math |
+-------+
| 99.00 |
| 78.00 |
| 64.00 |
| 59.00 |
| 87.00 |
| 78.00 |
+-------+
6 rows in set (0.00 sec)
mysql> select distinct math from exam_result;
+-------+
| math |
+-------+
| 99.00 |
| 78.00 |
| 64.00 |
| 59.00 |
| 87.00 |
+-------+
5 rows in set (0.00 sec)2.2?where条件筛选
where筛选条件，一般通过 算数运算符 和?逻辑运算符 来指定。表1和表2为where条件筛选中常用的算数和逻辑运算符。
| 运算符 | 含义 |
|---|---|
| >? >=? <? <= | 大于、大于等于、小于、小于等于 |
| = | 相等比较，NULL不安全，若NULL=NULL会被判断为假 |
| <=> | 相等比较，NULL安全，NULL<=>NULL为真 |
| !=、<> | 不等于比较，含义相同，NULL!=NULL和NULL<>NULL均为假 |
| in(选项1,?选项2, ... ...) | 如果为选项中任意一个，那么为真 |
| between a0 and a1 | 判断val是否满足 a0 <= val <= a1 |
| is null | 是NULL |
| is not null | 不是NULL |
| like | 模糊匹配，%可以匹配任意个字符，包括0个，_匹配1个字符 |
| 运算符 | 含义 |
|---|---|
| and | 如果多个条件都为真，那么返回真，否则返回假 |
| or | 如果多个条件之一为真，那么返回真，否则返回假 |
| not | 如果条件为真，返回假；条件为假，返回真 |
下面通过实际的案例，来熟悉每个操作符的使用方法：
案例：筛选总分(math + chinese + english)?大于 240?分的学生
注意：通过运算符进行条件筛选，不能使用别名，因为别名是在筛选完成后，向前端输出的时候，才会被重命名出来的。
mysql> select id,name,math+english+chinese total from exam_result where total > 240;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
mysql> select id,name,math+english+chinese total from exam_result
-> where math + chinese + english > 240;
+----+--------+--------+
| id | name | total |
+----+--------+--------+
| 1 | 周瑜 | 266.00 |
| 2 | 孙策 | 260.00 |
| 5 | 曹操 | 253.00 |
+----+--------+--------+
3 rows in set (0.00 sec)案例：筛选数学成绩为99分或59分的学生
可以通过等于 运算符 + 逻辑或 来达到目的，也可以使用 in(option,...) 进行筛选。
mysql> select id,name,math from exam_result where math=59 or math=99;
+----+--------+-------+
| id | name | math |
+----+--------+-------+
| 1 | 周瑜 | 99.00 |
| 4 | 刘备 | 59.00 |
+----+--------+-------+
2 rows in set (0.00 sec)
mysql> select id,name,math from exam_result where math in(59,99);
+----+--------+-------+
| id | name | math |
+----+--------+-------+
| 1 | 周瑜 | 99.00 |
| 4 | 刘备 | 59.00 |
+----+--------+-------+
2 rows in set (0.00 sec)案例：筛选出英语成绩 大于等于80 &&?小于等于95 的学生
可以使用?比较运算符?+?and，和between ... and ...?两种方式来实现。
mysql> select id,name,english from exam_result where english>=80 and english<=95;
+----+--------+---------+
| id | name | english |
+----+--------+---------+
| 1 | 周瑜 | 85.00 |
| 2 | 孙策 | 95.00 |
| 5 | 曹操 | 90.00 |
+----+--------+---------+
3 rows in set (0.00 sec)
mysql> select id,name,english from exam_result where english between 80 and 95;
+----+--------+---------+
| id | name | english |
+----+--------+---------+
| 1 | 周瑜 | 85.00 |
| 2 | 孙策 | 95.00 |
| 5 | 曹操 | 90.00 |
+----+--------+---------+
3 rows in set (0.00 sec)案例：筛选出孙某同学和孙姓同学的全部成绩
通过name like '孙%'?筛选孙姓同学，通过name like '孙_'?筛选孙某同学。
mysql> select * from exam_result where name like '孙%';
+----+-----------+-------+---------+---------+
| id | name | math | chinese | english |
+----+-----------+-------+---------+---------+
| 2 | 孙策 | 78.00 | 87.00 | 95.00 |
| 3 | 孙仲谋 | 64.00 | 88.00 | 58.00 |
+----+-----------+-------+---------+---------+
2 rows in set (0.00 sec)
mysql> select * from exam_result where name like '孙_';
+----+--------+-------+---------+---------+
| id | name | math | chinese | english |
+----+--------+-------+---------+---------+
| 2 | 孙策 | 78.00 | 87.00 | 95.00 |
+----+--------+-------+---------+---------+
1 row in set (0.00 sec)案例：查询1.1章节表students中，qq为空和不为空的学生
mysql> select * from students where qq is null;
+----+--------+------+
| id | name | qq |
+----+--------+------+
| 2 | 刘备 | NULL |
| 4 | 吕布 | NULL |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from students where qq is not null;
+----+-----------+-------+
| id | name | qq |
+----+-----------+-------+
| 1 | 孙权 | 11111 |
| 3 | 曹阿瞒 | 22200 |
| 5 | 袁绍 | 33333 |
+----+-----------+-------+
3 rows in set (0.00 sec)案例：对NULL进行 = 和 <=>?运算符比较测试
验证了=运算符对NULL不安全，<=>运算符对NULL安全，NULL和0并不相等。
mysql> select NULL=NULL,NULL=0,NULL=1;
+-----------+--------+--------+
| NULL=NULL | NULL=0 | NULL=1 |
+-----------+--------+--------+
| NULL | NULL | NULL |
+-----------+--------+--------+
1 row in set (0.00 sec)
mysql> select NULL<=>NULL, NULL<=>0, NULL<=>1;
+-------------+----------+----------+
| NULL<=>NULL | NULL<=>0 | NULL<=>1 |
+-------------+----------+----------+
| 1 | 0 | 0 |
+-------------+----------+----------+
1 row in set (0.00 sec)案例：对NULL进行!=和<>比较测试
NULL!=NULL和NULL<>NULL的比较结果均为假，证明!=和<>没有区别。
mysql> select NULL!=NULL,NULL!=0,NULL!=1;
+------------+---------+---------+
| NULL!=NULL | NULL!=0 | NULL!=1 |
+------------+---------+---------+
| NULL | NULL | NULL |
+------------+---------+---------+
1 row in set (0.00 sec)
mysql> select NULL<>NULL,NULL<>0,NULL<>1;
+------------+---------+---------+
| NULL<>NULL | NULL<>0 | NULL<>1 |
+------------+---------+---------+
| NULL | NULL | NULL |
+------------+---------+---------+
1 row in set (0.00 sec)2.3?结果排序
语法：select ...?from?表名称 [where ...]?order by 排序参数?[desc/asc]
解释：
- desc为降序，asc为升序，如果不显示指定默认排升序
- order?by的参数可以使用重命名后的参数名，因为order by是在全部输出结果都被确定下来的之后才进行排序的。
- NULL默认比任何值都小。
案例：将学生总成绩从高到低进行排序（排降序）
mysql> select id,name,math+chinese+english total from exam_result
-> order by total desc;
+----+-----------+--------+
| id | name | total |
+----+-----------+--------+
| 1 | 周瑜 | 266.00 |
| 2 | 孙策 | 260.00 |
| 5 | 曹操 | 253.00 |
| 6 | 吕布 | 224.00 |
| 3 | 孙仲谋 | 210.00 |
| 4 | 刘备 | 177.00 |
+----+-----------+--------+
6 rows in set (0.00 sec)案例：将学生成绩按照数学降序、语文升序、英语升序的顺序排序
结论：对于多参数排序，排序的优先级按照从前到后的顺序书写。
mysql> select * from exam_result
-> order by math desc, chinese, english;
+----+-----------+-------+---------+---------+
| id | name | math | chinese | english |
+----+-----------+-------+---------+---------+
| 1 | 周瑜 | 99.00 | 82.00 | 85.00 |
| 5 | 曹操 | 87.00 | 76.00 | 90.00 |
| 6 | 吕布 | 78.00 | 84.00 | 62.00 |
| 2 | 孙策 | 78.00 | 87.00 | 95.00 |
| 3 | 孙仲谋 | 64.00 | 88.00 | 58.00 |
| 4 | 刘备 | 59.00 | 73.00 | 45.00 |
+----+-----------+-------+---------+---------+
6 rows in set (0.00 sec)案例：将1.1章节中定义的表，按照qq升序排序
验证了NULL比任何值都小的结论。?
mysql> select * from students order by qq asc;
+----+-----------+-------+
| id | name | qq |
+----+-----------+-------+
| 2 | 刘备 | NULL |
| 4 | 吕布 | NULL |
| 1 | 孙权 | 11111 |
| 3 | 曹阿瞒 | 22200 |
| 5 | 袁绍 | 33333 |
+----+-----------+-------+
5 rows in set (0.00 sec)案例：where和order by结合使用 --?查询不姓孙的同学的数学成绩，并将数学成绩排降序
order by的优先级低于where，应当先通过where筛选条件，然后在order by进行排序。
mysql> select id,name,math from exam_result
-> where not name like '孙%'
-> order by math desc;
+----+--------+-------+
| id | name | math |
+----+--------+-------+
| 1 | 周瑜 | 99.00 |
| 5 | 曹操 | 87.00 |
| 6 | 吕布 | 78.00 |
| 4 | 刘备 | 59.00 |
+----+--------+-------+
4 rows in set (0.00 sec)2.4?分页显示结果
语法：select ... from?表名称 [where ...] [order by ...]?limit s, n
有3种方式，可以实现使用limit进行分页显示的功能。
- limit n --?从下标为0的行开始，显示n行信息；
- limit s,n --?从下标s处开始，显示n行信息，起始下标为0，不足n行信息就显示到末尾；
- limit n offset s --?从下标s处开始，显示n行信息，这种方式更加直观，推荐使用，但是此时s不能给定0，否则报错。
案例：筛选出总分>=210分的学生，排升序，从下标1开始显示4行数据。
mysql> select id, name, math + chinese + english total from exam_result
-> where math + chinese + english >= 210 order by total
-> limit 3 offset 1;
+----+--------+--------+
| id | name | total |
+----+--------+--------+
| 6 | 吕布 | 224.00 |
| 5 | 曹操 | 253.00 |
| 2 | 孙策 | 260.00 |
+----+--------+--------+
3 rows in set (0.00 sec)案例：查看全部学生的数学成绩，将成绩按照升序排序，从下标0开始显示4行数据。
mysql> select id,name,math from exam_result order by math asc limit 4;
+----+-----------+-------+
| id | name | math |
+----+-----------+-------+
| 4 | 刘备 | 59.00 |
| 3 | 孙仲谋 | 64.00 |
| 6 | 吕布 | 78.00 |
| 2 | 孙策 | 78.00 |
+----+-----------+-------+
4 rows in set (0.00 sec)2.5?向表中插入查询数据
语法：insert into?目标表名称 select ... from?表名称 [where ...] [order by ...] [limit ...]
通过将表中数据去重，来演示向表中插入查询数据的方法，表去重可以按照以下步骤实现：
- 创建一张与源表结构相同的表。
- 将表中去重查询后的数据插入到刚才创建的新表中去。
- 将源表和存放去除数据的表进行重命名。
创建结构相同的表：create table?新建表名称 like?源表名;
重命名表：rename table?原表名1 to?新表名1,?原表名2 to?新表名2;
mysql> create table duplicate (
-> id int unsigned not null,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.21 sec)
// 向duplicate表中插入有重复的测试数据
mysql> insert into duplicate values
-> (1,'aaa'),(1,'aaa'),
-> (2,'bbb'),(2,'bbb'),
-> (3,'ccc'),(4,'ddd');
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
// 创建与duplicate表具有相同结构的新表no_duplicate
mysql> create table no_duplicate like duplicate;
Query OK, 0 rows affected (0.27 sec)
// 向no_duplicate中插入表duplicate的去重查询数据
mysql> insert into no_duplicate select distinct * from duplicate;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
// 重命名表
mysql> rename table duplicate to old_duplicate,
-> no_duplicate to duplicate;
Query OK, 0 rows affected (0.08 sec)
mysql> select * from duplicate;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+----+------+
4 rows in set (0.00 sec)三.?更改表的数据
通过update指令，可以更改表中的数据。
语法：update?表名称?set?字段名=值? [where ...] [order by ...] [limit ...]??
解释：
- 更改的范围限于通过where、order by 和 limit 筛选出来的内容
- 如果不指定条件，那么会更改全部内容
案例：将孙姓同学的英语成绩全部改为80分。
mysql> select id,name,english from exam_result where name like '孙%';
+----+-----------+---------+
| id | name | english |
+----+-----------+---------+
| 2 | 孙策 | 95.00 |
| 3 | 孙仲谋 | 58.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)
mysql> update exam_result set english=80 where name like '孙%';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select id,name,english from exam_result where name like '孙%';
+----+-----------+---------+
| id | name | english |
+----+-----------+---------+
| 2 | 孙策 | 80.00 |
| 3 | 孙仲谋 | 80.00 |
+----+-----------+---------+
2 rows in set (0.00 sec)案例：将总分后3名的学生的英语成绩+10分
mysql> select id,name,english,math+chinese+english total from exam_result order by total asc;
+----+-----------+---------+--------+
| id | name | english | total |
+----+-----------+---------+--------+
| 4 | 刘备 | 45.00 | 177.00 |
| 6 | 吕布 | 62.00 | 224.00 |
| 3 | 孙仲谋 | 80.00 | 232.00 | -- 刘备、吕布、孙仲谋的英语成绩应当+10
| 2 | 孙策 | 80.00 | 245.00 |
| 5 | 曹操 | 90.00 | 253.00 |
| 1 | 周瑜 | 85.00 | 266.00 |
+----+-----------+---------+--------+
6 rows in set (0.01 sec)
mysql> update exam_result set english=english+10 order by math+chinese+english limit 3;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select id,name,english,math+chinese+english total from exam_result order by total asc;
+----+-----------+---------+--------+
| id | name | english | total |
+----+-----------+---------+--------+
| 4 | 刘备 | 55.00 | 187.00 |
| 6 | 吕布 | 72.00 | 234.00 |
| 3 | 孙仲谋 | 90.00 | 242.00 |
| 2 | 孙策 | 80.00 | 245.00 |
| 5 | 曹操 | 90.00 | 253.00 |
| 1 | 周瑜 | 85.00 | 266.00 |
+----+-----------+---------+--------+
6 rows in set (0.00 sec)
案例：将所有同学的英语成绩+5
只需要update，不用通过where筛选条件。
mysql> select id,name,english from exam_result;
+----+-----------+---------+
| id | name | english |
+----+-----------+---------+
| 1 | 周瑜 | 85.00 |
| 2 | 孙策 | 80.00 |
| 3 | 孙仲谋 | 90.00 |
| 4 | 刘备 | 55.00 |
| 5 | 曹操 | 90.00 |
| 6 | 吕布 | 72.00 |
+----+-----------+---------+
6 rows in set (0.01 sec)
mysql> update exam_result set english=english+5;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select id,name,english from exam_result;
+----+-----------+---------+
| id | name | english |
+----+-----------+---------+
| 1 | 周瑜 | 90.00 |
| 2 | 孙策 | 85.00 |
| 3 | 孙仲谋 | 95.00 |
| 4 | 刘备 | 60.00 |
| 5 | 曹操 | 95.00 |
| 6 | 吕布 | 77.00 |
+----+-----------+---------+
6 rows in set (0.00 sec)四.?删除表中数据
4.1?delete删除
语法：delete from?表名称 [where ...] [order by ...] [limit ...]
解释：delete执行按行删除，如果不指定条件，那么全表数据都会被删除。
案例：删除总成绩后2名的学生信息?
mysql> select id, name, math + chinese + english total from exam_result order by total;
+----+-----------+--------+
| id | name | total |
+----+-----------+--------+
| 4 | 刘备 | 192.00 |
| 6 | 吕布 | 239.00 |
| 3 | 孙仲谋 | 247.00 |
| 2 | 孙策 | 250.00 |
| 5 | 曹操 | 258.00 |
| 1 | 周瑜 | 271.00 |
+----+-----------+--------+
6 rows in set (0.00 sec)
mysql> delete from exam_result order by math + chinese + english asc limit 2;
Query OK, 2 rows affected (0.04 sec)
mysql> select id, name, math + chinese + english total from exam_result order by total;
+----+-----------+--------+
| id | name | total |
+----+-----------+--------+
| 3 | 孙仲谋 | 247.00 |
| 2 | 孙策 | 250.00 |
| 5 | 曹操 | 258.00 |
| 1 | 周瑜 | 271.00 |
+----+-----------+--------+
4 rows in set (0.00 sec)案例：delete删除表中全部数据
因为全表删除是一种十分不推荐的行为，因此，单独创建for_delete表，其中包含自增长主键，并插入3行数据，直接通过delete from for_delete删除表中全部数据。
mysql> create table for_delete (
-> id int unsigned primary key auto_increment,
-> name varchar(10) not null
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> insert into for_delete (name) values ('zhang'),('wang'),('li');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from for_delete;
+----+-------+
| id | name |
+----+-------+
| 1 | zhang |
| 2 | wang |
| 3 | li |
+----+-------+
3 rows in set (0.00 sec)
mysql> delete from for_delete;
Query OK, 3 rows affected (0.04 sec)
mysql> select * from for_delete;
Empty set (0.00 sec)当使用delete删除表中全部数据时，再次插入数据，如果不显示给定自增主键值，那么就会采用被删除之前自增主键的最大值+1，并不会回归初始值。
mysql> insert into for_delete (name) values ('zhou'),('zheng');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from for_delete;
+----+-------+
| id | name |
+----+-------+
| 4 | zhou |
| 5 | zheng |
+----+-------+
2 rows in set (0.00 sec)4.2?表截断
语法：truncate?表名称;
表截断操作truncate能够实现删除整张表数据的操作，但是却区别于delete：
- truncate只能对整张表操作，不能筛选条件，而delete能够筛选条件。
- truncate实际上不对数据进行操作，执行“删除”操作不经过事务，删除效率高于delete。
- truncate会初始化自增主键的值，而delete不会。
下面代码创建了for_truncate表，在中插入测试数据，通过truncate删除全部数据，然后再次插入数据，可以观察到truncate将auto_increment值初始化的作用，并且truncate操作受影响和行数为0。
-- 创建truncate测试用表
mysql> create table for_truncate (
-> id int unsigned primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.17 sec)
-- 插入测试数据
mysql> insert into for_truncate (name) values ('A'),('B'),('D');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | D |
+----+------+
3 rows in set (0.01 sec)
-- 通过truncate操作删除表中全部数据
mysql> truncate for_truncate;
Query OK, 0 rows affected (0.19 sec)
-- 输出表中全部数据，确定数据被删除
mysql> select * from for_truncate;
Empty set (0.00 sec)
-- 再次插入新数据
mysql> insert into for_truncate (name) values ('D');
Query OK, 1 row affected (0.03 sec)
-- 自增主键值被初始化，从1开始
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)五.?聚合函数
| 函数名 | 功能 |
|---|---|
| count([distinct]?参数) | 获取查询到的数据量，NULL不进行统计 |
| sum([distinct]?参数) | 获取查询到的数据的总和，不是数值没有意义 |
| avg([distinct]?参数) | 获取查询到的数据的平均值，不是数值没有意义 |
| min([distinct]?参数) | 获取查询到的数据的最小值，不是数值没有意义 |
| max([distinct]?参数) | 获取查询到的数据的最大值，不是数值没有意义 |
对于表中的函数，如果声明distinct，则表示对查询到的数据去重后再操作。?
案例：count函数的使用，查询学生数量
mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)案例：查询表students中qq数量（结果去除NULL）
mysql> select count(qq) from students;
+-----------+
| count(qq) |
+-----------+
| 3 |
+-----------+
1 row in set (0.02 sec)
案例：统计所有学生语文成绩总分
mysql> select sum(chinese) from exam_result;
+--------------+
| sum(chinese) |
+--------------+
| 333.00 |
+--------------+
1 row in set (0.00 sec)案例：统计所有学生总分平均分
mysql> select avg(math+chinese+english) from exam_result;
+---------------------------+
| avg(math+chinese+english) |
+---------------------------+
| 256.500000 |
+---------------------------+
1 row in set (0.00 sec)
案例：获取语文成绩最低分
mysql> select min(chinese) from exam_result;
+--------------+
| min(chinese) |
+--------------+
| 76.00 |
+--------------+
1 row in set (0.00 sec)案例：获取英语成绩最高分
mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
| 95.00 |
+--------------+
1 row in set (0.01 sec)六. 分组查询
通过group by语句，可以实现分组进行查询。
语法：select ... from 表名称 group by 字段;
以oracle 9i中的经典雇员信息测试表（scott数据库）为例，演示group by的使用方法。其中包含三个子表，分别为：dept（部门信息表）、emp（雇员信息表）、salgrade（薪资等级表）。下面为这三张表的结构。
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| salgrade |
+-----------------+
3 rows in set (0.00 sec)
mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO | | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno | int(6) unsigned zerofill | NO | | NULL | |
| ename | varchar(10) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int(4) unsigned zerofill | YES | | NULL | |
| hiredate | datetime | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| comm | decimal(7,2) | YES | | NULL | |
| deptno | int(2) unsigned zerofill | YES | | NULL | |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.02 sec)
mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES | | NULL | |
| losal | int(11) | YES | | NULL | |
| hisal | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)下面通过案例来演示group by操作的使用方法。
案例：统计不同部门(deptno)雇员的平均薪资、最高薪资和最低薪资。
mysql> select avg(sal),max(sal),min(sal),deptno from emp group by deptno;
+-------------+----------+----------+--------+
| avg(sal) | max(sal) | min(sal) | deptno |
+-------------+----------+----------+--------+
| 2916.666667 | 5000.00 | 1300.00 | 10 |
| 2175.000000 | 3000.00 | 800.00 | 20 |
| 1566.666667 | 2850.00 | 950.00 | 30 |
+-------------+----------+----------+--------+
3 rows in set (0.00 sec)
案例：按照部门(deptno)和工种(job)统计平均薪资、最高薪资和最低薪资。
mysql> select deptno,job,avg(sal),max(sal),min(sal) from emp group by deptno,job;
+--------+-----------+-------------+----------+----------+
| deptno | job | avg(sal) | max(sal) | min(sal) |
+--------+-----------+-------------+----------+----------+
| 10 | CLERK | 1300.000000 | 1300.00 | 1300.00 |
| 10 | MANAGER | 2450.000000 | 2450.00 | 2450.00 |
| 10 | PRESIDENT | 5000.000000 | 5000.00 | 5000.00 |
| 20 | ANALYST | 3000.000000 | 3000.00 | 3000.00 |
| 20 | CLERK | 950.000000 | 1100.00 | 800.00 |
| 20 | MANAGER | 2975.000000 | 2975.00 | 2975.00 |
| 30 | CLERK | 950.000000 | 950.00 | 950.00 |
| 30 | MANAGER | 2850.000000 | 2850.00 | 2850.00 |
| 30 | SALESMAN | 1400.000000 | 1600.00 | 1250.00 |
+--------+-----------+-------------+----------+----------+
9 rows in set (0.00 sec)案例：查看平均薪资小于2300的部门的平均薪资
通过having，可以实现对group by后的结果进行过滤，但是不可以使用where过滤group by的结果，因为having的执行优先级低于group by，where高于group by。
mysql> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal < 2300;
+--------+-------------+
| deptno | avg_sal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
2 rows in set (0.00 sec)七. 总结
- 通过insert into指令，可以实现按行插入数据。如果发生主键或唯一键冲突，可以使用on duplicate或replace在发生主键或唯一键冲突时替换原始数据。
- 通过select可以查看行，通过where可以按条件筛选查看，order by可以对结果进行升序或降序显示，limit可以实现分页显示。
- update可以指定行来更改表中数据。
- delete和truncate都可以实现对表中数据的删除，但是delete可以指定条件删除，truncate只能对整表操作，truncate相比于delete更快，它不走事务，不实际对数据进行操作。
- 通过聚合函数可以实现对平均值、和、最大值、最小值等的计算，group by可以指定字段进行分组查询。
文章来源:https://blog.csdn.net/weixin_43908419/article/details/135132055
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！