第六章表的增删改查

本章开始介绍表的增删改查，是数据库操作中最为重要的部分，尤其是数据查找，基本数据库的所有操作的核心都在于查找。

插入

在建表过后我们就要进行数据的插入，在此我们可以进行数据的全列插入也可以进行多行数据的指定列插入，这里有最常用的几种语法，接下来开始分别介绍。首先我们先建一个表，以这个表为例进行举例。

MariaDB [student]> create table student 
    -> (
    ->     id int primary key auto_increment,
    ->     name varchar(20) not null,
    ->     class_id int 
    -> );
Query OK, 0 rows affected (0.00 sec)

单行整列插入

接下来我们对整列数据都进行插入，一次只插入一行。

MariaDB [student]> insert into student values(1, '钢铁侠', 1000);
Query OK, 1 row affected (0.08 sec)

MariaDB [student]> insert into student values(2, '擎天柱', 1001);
Query OK, 1 row affected (0.00 sec)

然后我们打印一下整表的数据看下结果。

MariaDB [student]> select * from student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 钢铁侠    |     1000 |
|  2 | 擎天柱    |     1001 |
+----+-----------+----------+
2 rows in set (0.00 sec)

我们已经将数据插入进去了。

多行指定列插入

当我们想要一次插入多行并且指定特殊列给定数据时就可以使用以下的语法，并且未赋值的列会自动赋予默认值。

1
2
3

MariaDB [student]> insert into student(name,class_id) values('大黄蜂',1003),('蜘蛛侠',1004);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看一下数据。

MariaDB [student]> select * from student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 钢铁侠    |     1000 |
|  2 | 擎天柱    |     1001 |
|  3 | 大黄蜂    |     1003 |
|  4 | 蜘蛛侠    |     1004 |
+----+-----------+----------+
4 rows in set (0.00 sec)

可以看到数据已经插入，并且插入了两行，由于我对id字段设置了自增长，于是即使我不给它值也会自动帮我们填充。

插入否则更新

这种插入方式针对的是当我们设置的主键或者唯一键产生冲突时形成替代方案的做法。当我们插入一个数据如果数据中的主键或者唯一键与已经存在的数据冲突我们就会对其进行数据更新，至于更新哪些数据都是由我们来指定的。

1
2
3

MariaDB [student]> insert into student values(1, '奇异博士', 1000)
    -> on duplicate key update name = '奇异博士', class_id = 1000;
Query OK, 2 rows affected (0.00 sec)

打印当前数据。

MariaDB [student]> select * from student;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  2 | 擎天柱       |     1001 |
|  3 | 大黄蜂       |     1003 |
|  4 | 蜘蛛侠       |     1004 |
+----+--------------+----------+
4 rows in set (0.00 sec)

由于我们擦汗如的数据主键冲突我们将目标主键那一行其他字段的值进行了修改，当然如果没有冲突的话就会整行插入新的数据了。不过此时要注意我们更新不可更新主键或唯一键的值。

1
2

MariaDB [student]> insert into student values(1, '奇异博士', 1000) on duplicate key update id = 2, name = '奇异博士', class_id = 1000;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

替换

这种语法也是针对处理主键或唯一键的冲突数据的，如果插入的数据不产生冲突则直接插入，如果产生冲突则完全替换原数据。

MariaDB [student]> replace into student values(2, '绿巨人', 1001);
Query OK, 2 rows affected (0.01 sec)

MariaDB [student]> select * from student;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  2 | 绿巨人       |     1001 |
|  3 | 大黄蜂       |     1003 |
|  4 | 蜘蛛侠       |     1004 |
+----+--------------+----------+
4 rows in set (0.00 sec)

以上这几种插入语法都十分简单，最为常用的是前两种插入，我们之前也有大量使用，掌握起来也很简单。

查找

查找是本章的重点，也是数据库最为常用的操作。

全列查询

全列查询我们之前经常使用，语法及案例如下。

MariaDB [student]> select * from student;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  2 | 绿巨人       |     1001 |
|  3 | 大黄蜂       |     1003 |
|  4 | 蜘蛛侠       |     1004 |
+----+--------------+----------+
4 rows in set (0.00 sec)

指定列查询

如果我们只想查询表中指定字段的数据，则可以使用以下的语法。

MariaDB [student]> select id, name from student;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 奇异博士     |
|  2 | 绿巨人       |
|  3 | 大黄蜂       |
|  4 | 蜘蛛侠       |
+----+--------------+
4 rows in set (0.00 sec)

查询字段为表达式

我们可以将数据进行一些处理之后再进行查询，比如我们将id字段整体加1之后再打印。

MariaDB [student]> select id + 1, name, class_id from student;
+--------+--------------+----------+
| id + 1 | name         | class_id |
+--------+--------------+----------+
|      2 | 奇异博士     |     1000 |
|      3 | 绿巨人       |     1001 |
|      4 | 大黄蜂       |     1003 |
|      5 | 蜘蛛侠       |     1004 |
+--------+--------------+----------+
4 rows in set (0.00 sec)

我们甚至可以直接查询表达式。

MariaDB [student]> select id, name, class_id, 10 from student;
+----+--------------+----------+----+
| id | name         | class_id | 10 |
+----+--------------+----------+----+
|  1 | 奇异博士     |     1000 | 10 |
|  2 | 绿巨人       |     1001 | 10 |
|  3 | 大黄蜂       |     1003 | 10 |
|  4 | 蜘蛛侠       |     1004 | 10 |
+----+--------------+----------+----+
4 rows in set (0.00 sec)

我们还可以将多个字段进行操作之后打印。

MariaDB [student]> select id, name, class_id + id from student;
+----+--------------+---------------+
| id | name         | class_id + id |
+----+--------------+---------------+
|  1 | 奇异博士     |          1001 |
|  2 | 绿巨人       |          1003 |
|  3 | 大黄蜂       |          1006 |
|  4 | 蜘蛛侠       |          1008 |
+----+--------------+---------------+
4 rows in set (0.01 sec)

如果我们觉得列名太长的话我们可以使用as语法指定别名。

MariaDB [student]> select id, name, class_id + id as 表达式 from student;
+----+--------------+-----------+
| id | name         | 表达式    |
+----+--------------+-----------+
|  1 | 奇异博士     |      1001 |
|  2 | 绿巨人       |      1003 |
|  3 | 大黄蜂       |      1006 |
|  4 | 蜘蛛侠       |      1008 |
+----+--------------+-----------+
4 rows in set (0.00 sec)

结果去重

MariaDB [student]> insert into student(name, class_id) values('葫芦娃', 1000);
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> select * from student;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  2 | 绿巨人       |     1001 |
|  3 | 大黄蜂       |     1003 |
|  4 | 蜘蛛侠       |     1004 |
|  5 | 葫芦娃       |     1000 |
+----+--------------+----------+
5 rows in set (0.00 sec)

我们插入了一条新的数据，之后我们发现葫芦娃的班级和奇异博士的班级重复了，我想要去重，该怎么办呢。

MariaDB [student]> select distinct class_id from student;
+----------+
| class_id |
+----------+
|     1000 |
|     1001 |
|     1003 |
|     1004 |
+----------+
4 rows in set (0.00 sec)

加入distinct关键字即可去重，不过我们要注意，只有打印出来的字段完全一致才会进行去重，当我们如果打印了多个字段，但是两行数据间各字段的值并不都是相同的则不会去重。

MariaDB [student]> select distinct name, class_id from student;
+--------------+----------+
| name         | class_id |
+--------------+----------+
| 奇异博士     |     1000 |
| 绿巨人       |     1001 |
| 大黄蜂       |     1003 |
| 蜘蛛侠       |     1004 |
| 葫芦娃       |     1000 |
+--------------+----------+
5 rows in set (0.00 sec)

where条件

如果说查询是数据库中最重要操作，那么where条件则是查询中最重要的语法。所谓where条件不过是再查询中加入条件，查询目标要求的数据。

在where条件中我们也许会用到以上的操作符，接下来举几个例子。

MariaDB [student]> select * from student where class_id = 1000;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  5 | 葫芦娃       |     1000 |
+----+--------------+----------+
2 rows in set (0.00 sec)

这个案例是查找class_id为1000的学生。

MariaDB [student]> select * from student where class_id = 1000 or class_id = 1001;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  2 | 绿巨人       |     1001 |
|  5 | 葫芦娃       |     1000 |
+----+--------------+----------+
3 rows in set (0.00 sec)

这个案例是查找class_id为1000或者为1001的学生。

MariaDB [student]> select * from student where id between 1 and 3;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  2 | 绿巨人       |     1001 |
|  3 | 大黄蜂       |     1003 |
+----+--------------+----------+
3 rows in set (0.00 sec)

这个案例是查找id在1和3之间的学生。

MariaDB [student]> select * from student where id in (2,3,5,6);
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  2 | 绿巨人    |     1001 |
|  3 | 大黄蜂    |     1003 |
|  5 | 葫芦娃    |     1000 |
+----+-----------+----------+
3 rows in set (0.00 sec)

这个案例是查找id为2或者3或者5或者6的学生。

MariaDB [student]> select * from student where name like '奇%';
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
+----+--------------+----------+
1 row in set (0.00 sec)

这个案例是查找姓名开头为奇的学生，这里用到了模糊查找，%则表示匹配任意多个包括0个字符。

MariaDB [student]> select * from student where name like '奇___';
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
+----+--------------+----------+
1 row in set (0.00 sec)

而这个案例中的模糊查找用到了_则严格匹配指定数量的字符，如果我们在后面只打一个呢么是查找不到的。

1 2	MariaDB [student]> select * from student where name like '奇_'; Empty set (0.00 sec)

关于where的操作就先说到这里，这个操作十分灵活，还需要在实践中去拓宽。

结果排序

我们查询的结果打印出来往往是无序的，是根据插入的先后进行打印的，因此我们需要对数据进行排序，这就要用到接下来的语法。

MariaDB [student]> select * from student order by class_id;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  5 | 葫芦娃       |     1000 |
|  2 | 绿巨人       |     1001 |
|  3 | 大黄蜂       |     1003 |
|  4 | 蜘蛛侠       |     1004 |
+----+--------------+----------+
5 rows in set (0.00 sec)

默认的排序顺序是升序的我们可以通过加入DESC改变为降序。

MariaDB [student]> select * from student order by class_id desc;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  4 | 蜘蛛侠       |     1004 |
|  3 | 大黄蜂       |     1003 |
|  2 | 绿巨人       |     1001 |
|  1 | 奇异博士     |     1000 |
|  5 | 葫芦娃       |     1000 |
+----+--------------+----------+
5 rows in set (0.01 sec)

这里要注意的是NULL被视为最小的值，同时如果查询没有加入order by那么这个数据就是无序的，这点不要混淆。

同时排序中可以按照表达式排序，也可以使用别名。

MariaDB [student]> select id, name, class_id, id + class_id as 表达式 from student order by 表达式;
+----+--------------+----------+-----------+
| id | name         | class_id | 表达式    |
+----+--------------+----------+-----------+
|  1 | 奇异博士     |     1000 |      1001 |
|  2 | 绿巨人       |     1001 |      1003 |
|  5 | 葫芦娃       |     1000 |      1005 |
|  3 | 大黄蜂       |     1003 |      1006 |
|  4 | 蜘蛛侠       |     1004 |      1008 |
+----+--------------+----------+-----------+
5 rows in set (0.00 sec)

筛选分页结果

我们在对一个表进行查询时有时表中数据会很多，一次查询会导致卡死，于是我们可以进行筛选分页，分波查询。

MariaDB [student]> select * from student order by id limit 3 offset 0;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 奇异博士     |     1000 |
|  2 | 绿巨人       |     1001 |
|  3 | 大黄蜂       |     1003 |
+----+--------------+----------+
3 rows in set (0.01 sec)

以上的操作我们将数据按id字段进行排序，并且分页显示，一页三个，以上我们打印了第一页。

MariaDB [student]> select * from student order by id limit 3 offset 3;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  4 | 蜘蛛侠    |     1004 |
|  5 | 葫芦娃    |     1000 |
+----+-----------+----------+
2 rows in set (0.00 sec)

由于第二页不足三个，但是打印不会有影响。

修改

说完查找，修改和删除就显得简单多了，首先是修改。

MariaDB [student]> select id, name from student where id = 1;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 奇异博士     |
+----+--------------+
1 row in set (0.01 sec)

我现在想要把id为1的学生名字改成钢铁侠，可以使用以下的语法。

MariaDB [student]> update student set name = '钢铁侠' where id = 1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [student]> select id, name from student where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 钢铁侠    |
+----+-----------+
1 row in set (0.00 sec)

这样就已经修改完成了，这个语法是最为常用的修改语法，可以修改单个数据，也可以修改整表数据。

MariaDB [student]> update student set class_id = class_id + 1;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [student]> select * from student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 钢铁侠    |     1001 |
|  2 | 绿巨人    |     1002 |
|  3 | 大黄蜂    |     1004 |
|  4 | 蜘蛛侠    |     1005 |
|  5 | 葫芦娃    |     1001 |
+----+-----------+----------+
5 rows in set (0.01 sec)

不过要注意修改全表的语句要慎用。

删除

删除的语法也很简单，删除单行。

MariaDB [student]> delete from student where id = 5;
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> select * from student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 钢铁侠    |     1001 |
|  2 | 绿巨人    |     1002 |
|  3 | 大黄蜂    |     1004 |
|  4 | 蜘蛛侠    |     1005 |
+----+-----------+----------+
4 rows in set (0.00 sec)

这样就删除了单行的数据，同时也可以删除整表的数据，但同时要慎用。

MariaDB [student]> delete from student;
Query OK, 4 rows affected (0.00 sec)

MariaDB [student]> select * from student;
Empty set (0.00 sec)

但是删除整表数据不会重置auto_increment数值。
相比于删除整表还有一种截断表的操作，起作用也可以删除整表数据，但是其只能对整表操作，并且由于不会对数据进行操作所以速度更快，并且会重置auto_increment值。

MariaDB [student]> insert into student(name, class_id) values('关羽',1003),('刘备',1004);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [student]> select * from student;
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
|  6 | 关羽   |     1003 |
|  7 | 刘备   |     1004 |
+----+--------+----------+
2 rows in set (0.00 sec)

我们接着上次删除的表继续插入数据，自增长从6开始，之后我们截断表。

MariaDB [student]> truncate student;
Query OK, 0 rows affected (0.00 sec)

MariaDB [student]> select * from student;
Empty set (0.00 sec)

数据也同样被清楚了，但是auto_increment也同时被重置。

MariaDB [student]> insert into student(name, class_id) values('钢铁侠',1000);
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> select * from student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 钢铁侠    |     1000 |
+----+-----------+----------+
1 row in set (0.00 sec)

MariaDB [student]> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                       |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

聚合函数

这些就是常用的聚合函数，接下来我们举几个例子看下他们的实际用法。

MariaDB [student]> select * from student;
+----+--------------+----------+
| id | name         | class_id |
+----+--------------+----------+
|  1 | 钢铁侠       |     1000 |
|  2 | 蜘蛛侠       |     1001 |
|  3 | 绿巨人       |     1002 |
|  4 | 奇异博士     |     1003 |
|  5 | 美国队长     |     1000 |
+----+--------------+----------+
5 rows in set (0.00 sec)

我们先向表中插入测试数据。

MariaDB [student]> select count(id) from student;
+-----------+
| count(id) |
+-----------+
|         5 |
+-----------+
1 row in set (0.00 sec)

返回id数据的数量，不会将NULL包含进去。

MariaDB [student]> select sum(id) from student;
+---------+
| sum(id) |
+---------+
|      15 |
+---------+
1 row in set (0.00 sec)

显示id的和。

MariaDB [student]> select avg(id) from student;
+---------+
| avg(id) |
+---------+
|  3.0000 |
+---------+
1 row in set (0.00 sec)

显示id的平均值。

group by语句

group by是分组语句，用于按照某一数据进行分组计算打印。

MariaDB [student]> select avg(id), name, class_id from student group by class_id;
+---------+--------------+----------+
| avg(id) | name         | class_id |
+---------+--------------+----------+
|  3.0000 | 钢铁侠       |     1000 |
|  2.0000 | 蜘蛛侠       |     1001 |
|  3.0000 | 绿巨人       |     1002 |
|  4.0000 | 奇异博士     |     1003 |
+---------+--------------+----------+
4 rows in set (0.00 sec)

以上的就是按照class_id进行分组，然后打印出各班级的平均id的值。

第六章 表的增删改查

插入