MySQL数据库：表的增删查改

发布时间：2023年12月22日

目录

一.?向表中添加数据

1.1?insert插入

1.2?主键（唯一键）冲突问题

二.?表的查询

2.1?select指定列查询

2.2?where条件筛选

2.3?结果排序

2.4?分页显示结果

2.5?向表中插入查询数据

三.?更改表的数据

四.?删除表中数据

4.1?delete删除

4.2?表截断

五.?聚合函数

六. 分组查询

七. 总结

一.?向表中添加数据

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: 0

2.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条件筛选中常用的算数和逻辑运算符。

表1 算数运算符
运算符含义
>? >=? <? <=大于、大于等于、小于、小于等于
=相等比较，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个字符
表2 逻辑运算符
运算符含义
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)

五.?聚合函数

表3 聚合函数
函数名功能
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
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章