查询、修改、删除表记录

我们刚刚学习了插入记录到数据表中，显然，接下来我们要学习的就是如何查询表记录

查询表记录

前面，我们已经在user表中插入了一批数据。

要查询该表中所有的记录，使用 SQL语句 select 进行查询

如下所示

select * from user;

得到的结果是

+----+----------+-----------+---------------+
| id | username | password  | realname      |
+----+----------+-----------+---------------+
|  1 | byhy1    | password1 | 白月黑羽1     |
|  2 | byhy2    | password2 | 白月黑羽2     |
|  3 | byhy3    | password3 | 白月黑羽3     |
|  4 | byhy4    | password4 | 白月黑羽4     |
|  5 | byhy5    | password5 | 白月黑羽5     |
+----+----------+-----------+---------------+
5 rows in set (0.00 sec)

这样就可以查看user 表中的所有记录的所有列的值。

上面，select 语句中的 * 表示查看所有的列

有的时候，表中的列很多，我们并不想查看所有的列的内容。

这时候，可以指定要查看的列。

如果，这个user表，我们只想查看 id和username 这两列的信息，就可以这样写

select id,username  from user;

得到的结果是

+----+----------+
| id | username |
+----+----------+
|  1 | byhy1    |
|  2 | byhy2    |
|  3 | byhy3    |
|  4 | byhy4    |
|  5 | byhy5    |
+----+----------+
5 rows in set (0.00 sec)

查询过滤

点击这里，看视频讲解，学习下面内容

有的时候，一张数据表里面可能有海量的记录，比如上百万条，甚至上亿条记录(想象一下淘宝的用户表有多少)。

我们有时候只需要查询其中符合某些条件的部分信息。这就叫过滤查询。

根据进过滤条件查询，我们使用 where从句

过滤条件

比如，上面的user表，我们只需要查询用户名为 byhy3 的那个用户的信息，就可以这样

select *  from user where username='byhy3';

得到的结果是

+----+----------+-----------+---------------+
| id | username | password  | realname      |
+----+----------+-----------+---------------+
|  3 | byhy3    | password3 | 白月黑羽3     |
+----+----------+-----------+---------------+
1 row in set (0.00 sec)

其中 username='byhy3' 里面的等号就是一个过滤条件的 操作符 ，表示过滤条件是查找记录的username字段值等于byhy3。

如果我们只需要查询 id为 2 的那个用户的信息，就可以这样

select *  from user where id=2;

除了等于条件（使用 =），还有

不等于（使用 <> 或者 !=）

select *  from user where id!=1;

大于（使用 > ）

select *  from user where id>3;

小于（使用 < ）

select *  from user where id<3;

大于等于（使用 >= ）

select *  from user where id>=3;

小于等于（使用 <= ）

select *  from user where id<=3;

包含字符（使用 like 和 % ）

/* 以 byhy 开头*/
select *  from user where username like "byhy%";

/* 以 33 结尾*/
select *  from user where username like "%33";

/* 以 byhy 开头 并且 以 33 结尾*/
select *  from user where username like "byhy%33";

/* 包含 byhy */
select *  from user where username like "%byhy%";

在集合里面（使用 in）

/* id 在指定集合里面 */
select * from user where id in (1,2,3);

/* usernmae 在指定集合里面 */
select * from user where username  in ("byhy1","byhy2");

并且关系

如果你要查询的记录过滤条件是多个，并且要 同时满足 ，就使用 and 关键字连接多个过滤条件

比如，我们用下面的语句创建客户表 customer1，并且插入一些数据

CREATE TABLE customer1 (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(150)  NOT NULL,
  `level` int NOT NULL,
  `coin` int NOT NULL,
  PRIMARY KEY (id)
) ;

INSERT INTO customer1 (username,level,coin) VALUES 
    ('cus01',15, 5),
    ('cus02',5,  315),
    ('cus03',3,  215),
    ('cus04',11, 6615),
    ('cus05',22, 115),
    ('cus06',6,  4415),
    ('cus07',7,  315),
    ('cus08',4,  15),
    ('cus09',9,  315),
    ('cus10',44, 45),
    ('cus11',2,  215),
    ('cus12',1,  815),
    ('cus13',15, 13),
    ('cus14',25, 1566),
    ('cus15',12, 125),
    ('cus16',8,  75);

我们要查询 id > 10 并且等级 level值小于 15的用户。就可以这样

select *  from customer1 where id > 10 and level<15;

得到的结果是

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
| 11 | cus11    |     2 |  215 |
| 12 | cus12    |     1 |  815 |
| 15 | cus15    |    12 |  125 |
| 16 | cus16    |     8 |   75 |
+----+----------+-------+------+
4 rows in set (0.00 sec)

如果你有更多的过滤条件，就继续在后面加and，比如

select *  from customer1 where id > 10 and level<15 and coin<300;

如果查询条件是日期时间在某个范围之内，使用 between and 关键字，如下所示

SELECT * FROM  customer1
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2020-09-29 10:15:55')

或者关系

如果你要查询的记录过滤条件是多个， 只要满足其中一个条件即可 ，就使用 or 关键字连接多个过滤条件

比如，我们要查询 id > 10 或 等级 level值小于 15的用户。就可以这样

select *  from customer1 where id > 10 or level<15;

得到的结果是

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
|  2 | cus02    |     5 |  315 |
|  3 | cus03    |     3 |  215 |
|  4 | cus04    |    11 | 6615 |
|  6 | cus06    |     6 | 4415 |
|  7 | cus07    |     7 |  315 |
|  8 | cus08    |     4 |   15 |
|  9 | cus09    |     9 |  315 |
| 11 | cus11    |     2 |  215 |
| 12 | cus12    |     1 |  815 |
| 13 | cus13    |    15 |   13 |
| 14 | cus14    |    25 | 1566 |
| 15 | cus15    |    12 |  125 |
| 16 | cus16    |     8 |   75 |
+----+----------+-------+------+
13 rows in set (0.00 sec)

如果我们要查询 id > 8 或者等级 level值 < 15的用户，但是 同时还要满足条件coin=315 的。

满足条件的只有下面这3条记录

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
|  2 | cus02    |     5 |  315 |
|  7 | cus07    |     7 |  315 |
|  9 | cus09    |     9 |  315 |
+----+----------+-------+------+

如果你直接这样写

select *  from customer1 where id > 8 or level<15 and coin=315;

运行一下，结果却发现，如下

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
|  2 | cus02    |     5 |  315 |
|  7 | cus07    |     7 |  315 |
|  9 | cus09    |     9 |  315 |
| 10 | cus10    |    44 |   45 |
| 11 | cus11    |     2 |  215 |
| 12 | cus12    |     1 |  815 |
| 13 | cus13    |    15 |   13 |
| 14 | cus14    |    25 | 1566 |
| 15 | cus15    |    12 |  125 |
| 16 | cus16    |     8 |   75 |
+----+----------+-------+------+

原来 or 和 and 同时出现的时候， and 优先级高，所以会先计算 level<15 and coin=315 结果再和前面的 id > 8 进行 or运算。

怎么办？

可以使用括号，让 or 先运算，如下所示。

select *  from customer1 where (id > 8 or level<15)  and coin=315;

子查询

已知有如下两张数据表，

表名：学生表

id	姓名	性别	电话号码	学历
1	白月黑羽	男	13000000001	本科
2	紫气一元	男	13000000002	硕士
3	无形冥主	男	13000000003	博士
4	拜月魔巫	女	13000000004	本科

表名：成绩表

id	姓名	课程	分数
1	白月黑羽	语文	100
2	紫气一元	英语	85
3	无形冥主	语文	100
4	无形冥主	英语	98
5	拜月魔巫	数学	100

要查询考试成绩有100分的学生的电话号码

可以使用如下SQL语句

SELECT 电话号码 FROM 学生表
WHERE 姓名 IN (
  SELECT 姓名 FROM 成绩表 WHERE 分数=100
);

过滤条件是姓名在一个集合中，但是这个集合不是预先就知道的，而是另外一个select查询的结果。

另外一个select 术语叫 子查询 （subquery）。

MySQL执行语句时，会先执行子查询，获取分数为100分的学生姓名，放入集合中。然后再执行外层查询。

可以使用下面的SQL语句来创建表和记录，验证一下

CREATE TABLE `学生表` (
    id INT AUTO_INCREMENT,
    姓名 VARCHAR(10) NOT NULL,
    性别 VARCHAR(1) NOT NULL,
    电话号码 VARCHAR(15) NOT NULL,
    学历     VARCHAR(15) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE `成绩表` (
    id INT AUTO_INCREMENT,
    姓名 VARCHAR(255) NOT NULL,
    课程 VARCHAR(15) NOT NULL,
    分数 INT NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO 学生表 (姓名,性别,电话号码,学历) VALUES 
    ('白月黑羽','男', '13000000001', '本科'),
    ('紫气一元','男', '13000000002', '硕士'),
    ('无形冥主','男', '13000000003', '博士'),
    ('拜月魔巫','女', '13000000004', '本科');


INSERT INTO 成绩表 (姓名,课程,分数) VALUES 
    ('白月黑羽',  '语文', 100),
    ('紫气一元',  '英语', 85),
    ('无形冥主',  '语文', 100),
    ('无形冥主',  '英语', 98),
    ('拜月魔巫',  '数学', 100);

执行查询结果如下

mysql> SELECT 电话号码 FROM 学生表
    -> WHERE 姓名 IN (SELECT 姓名 FROM 成绩表 WHERE 分数=100);
+--------------+
| 电话号码     |
+--------------+
| 13000000001  |
| 13000000003  |
| 13000000004  |
+--------------+
3 rows in set (0.00 sec)

多表连接查询

上面的示例中，我们使用子查询得到考试成绩有100分的学生的电话号码

其实，还可以使用 连接查询 ，如下

SELECT 电话号码 FROM 学生表
INNER JOIN 成绩表 ON  学生表.姓名 = 成绩表.姓名
WHERE 成绩表.分数=100;

INNER JOIN 语句必须紧跟在 FROM 语句后面，表示连接哪张表，连接条件是什么。

本例中，连接的是 成绩表，连接条件是 相同的姓名

MySQL 在执行上面的SQL语句，会依次

查询 学生表 里面的每条记录
针对这条记录，在 成绩表中 找姓名字段和本记录中姓名字段相同的记录

如果找到，将两条记录连接起来，形成一条包含两条记录内容的新的记录行，存在内存中

如果找不到，忽略这条记录

比较成绩表中这条记录的分数字段是否等于100 ，如果相等，就放入结果集中否则忽略这条记录

本例中的连接查询 INNER JOIN 是内连接，是最常用的一种连接查询。

还有3 种外连接查询：RIGHT OUTER JOIN、LEFT OUTER JOIN、FULL OUTER JOIN，因为不是特别常用，这里我们暂不介绍，感兴趣的朋友可以自行网上查阅相关资料。

查询结果去重

前面示例中，我们创建了这样的一张成绩表

+----+--------------+--------+--------+
| id | 姓名         | 课程   | 分数   |
+----+--------------+--------+--------+
|  1 | 白月黑羽     | 语文   |    100 |
|  2 | 紫气一元     | 英语   |     85 |
|  3 | 无形冥主     | 语文   |    100 |
|  4 | 无形冥主     | 英语   |     98 |
|  5 | 拜月魔巫     | 数学   |    100 |
+----+--------------+--------+--------+

如果我们想查看有哪些学科学生考试得过满分，

可以写这样的SQL语句

select 课程  from 成绩表 where 分数=100;

结果如下

+--------+
| 课程   |
+--------+
| 语文   |
| 语文   |
| 数学   |
+--------+

可以发现语文重复了两次。

因为有两条记录语文考试成绩为 100分。

但是我们现在的目的是：只要看有哪些学科，当然不希望重复。

这时，可以使用关键字 distinct 。

如下

select distinct 课程  from 成绩表 where 分数=100;

就可以去掉结果中重复的内容了。

查询结果排序

点击这里，看视频讲解，学习下面内容

我们经常需要，查询一个结果，并且按照某个字段的值进行排序。

比如，上面的查询

select *  from customer1 where id > 10;

结果需要安装 coin 的值由小到大排序，怎么办？

这时，可以使用 order by

比如

select *  from customer1 where id > 10 
order by coin;

表示查询结果记录根据 coin 字段的值升序（ASC）排列。所谓升序，就是从小到大。

缺省就是按照升序排列，当然你也可以指定是升序，像这样

select *  from customer1 where id > 10 
order by coin asc;

如果你想反过来，降序排列，则使用 desc ，像这样

select *  from customer1 where id > 10 
order by coin desc;

有时候，你希望显示记录先根据 coin排列，coin相同的再根据 level 进行排序，就可以这样写

select *  from customer1 where id > 10 
order by coin, level;

你希望显示记录先根据 coin 升序排列，coin相同的再根据 level 降序排序，就可以这样写

select *  from customer1 where id > 10 
order by coin, level desc;

查询结果分组合并

前面的成绩表，假设有如下内容

+----+--------------+--------+--------+
| id | 姓名         | 课程   | 分数   |
+----+--------------+--------+--------+
|  1 | 白月黑羽     | 语文   |    100 |
|  2 | 紫气一元     | 英语   |     85 |
|  3 | 无形冥主     | 语文   |    100 |
|  4 | 无形冥主     | 英语   |     98 |
|  5 | 拜月魔巫     | 数学   |    100 |
|  6 | 白月黑羽     | 数学   |     90 |
|  7 | 白月黑羽     | 英语   |     96 |
|  8 | 紫气一元     | 数学   |     85 |
|  9 | 紫气一元     | 语文   |     85 |
| 10 | 拜月魔巫     | 语文   |     80 |
+----+--------------+--------+--------+

如果我们要得到各学生考试总分，如下

+--------------+--------+
| 姓名         | 总分   |
+--------------+--------+
| 白月黑羽     |    286 |
| 紫气一元     |    255 |
| 无形冥主     |    198 |
| 拜月魔巫     |    180 |
+--------------+--------+

可以使用 Group By 结合 SUM 函数，如下

SELECT 姓名, SUM(分数) AS 总分
FROM 成绩表 
Group By 姓名

Group By是按照什么分组的意思，经常和 SUM 、 COUNT 一起使用

SUM 是字段值求和，当然这个字段值应该是数字

COUNT 是字段值出现次数

比如，我们要统计各学生参加了几次考试，就可以这样

SELECT 姓名, COUNT(课程) AS 考试次数
FROM 成绩表 
Group By 姓名

结果如下

+--------------+--------------+
| 姓名         | 考试次数     |
+--------------+--------------+
| 白月黑羽     |            3 |
| 紫气一元     |            3 |
| 无形冥主     |            2 |
| 拜月魔巫     |            2 |
+--------------+--------------+

查询结果分页

有的时候，SQL语句查询的结果可能有成千上万条记录（想象一下淘宝搜索手机）。如果一下子全部返回，会耗费大量的CPU和内容，也是不需要的。

这时候，我们的SQL语句可以指定 分页 ，每次只返回一页数据。

我们执行下面的SQL语句再插入一些数据到学生表中

INSERT INTO 学生表 (姓名,性别,电话号码,学历) VALUES 
    ('白月黑羽101','男', '13100000101', '本科'),
    ('白月黑羽102','男', '13100000102', '本科'),
    ('白月黑羽103','男', '13100000103', '本科'),
    ('白月黑羽104','男', '13100000104', '本科'),
    ('白月黑羽105','男', '13100000105', '本科'),
    ('白月黑羽106','男', '13100000106', '本科'),
    ('白月黑羽107','男', '13100000107', '本科'),
    ('白月黑羽108','男', '13100000108', '本科'),
    ('白月黑羽109','男', '13100000109', '本科'),
    ('白月黑羽110','男', '13100000110', '本科'),
    ('白月黑羽111','男', '13100000111', '本科'),
    ('白月黑羽112','男', '13100000112', '本科'),
    ('白月黑羽113','男', '13100000113', '本科'),
    ('白月黑羽114','男', '13100000114', '本科'),
    ('白月黑羽115','男', '13100000115', '本科'),
    ('白月黑羽116','男', '13100000116', '本科'),
    ('白月黑羽117','男', '13100000117', '本科'),
    ('白月黑羽118','男', '13100000118', '本科'),
    ('白月黑羽119','男', '13100000119', '本科'),
    ('白月黑羽120','男', '13100000120', '本科'),
    ('白月黑羽121','男', '13100000121', '本科'),
    ('白月黑羽122','男', '13100000122', '本科');

然后，如果我们设置每页最多5条记录，返回指定的一页，比如第2页，怎么做呢？

有两种方法

LIMIT + OFFSET

mysql 的 LIMIT 指定最多获取 多少条 记录。

mysql 的 OFFSET 指定从结果集中， 从第几条记录开始 获取结果。

比如

select * from 学生表
ORDER BY id
limit 5 offset 5;

limit 5 指定最多获取5条记录，
offset 5 指定从结果集中，第6条记录开始。因为第1条记录offset为0

offset 和页码的对应关系为 OFFSET = (PAGE-1) * LIMIT

如果limit 是 5 ，offset 第一页取值是0，第2页是5，第3页是10，第4页是15 ...

LIMIT + ID 方法

上面的 LIMIT + OFFSET 方法有个重大缺陷，当offset 取值很大时（比如100000），查询耗时巨大。

具体原因参考这篇文章和这篇reddit文章

可以使用 LIMIT + 标志列 的方法解决。

标志列就是每行取值唯一的列，最典型的就是自增型的ID。

每次分页都指定 ID 的范围即可。

比如取出第1页

select * from 学生表
where id>0 and 其他查询条件
ORDER BY id
limit 5

应用程序记录下最后一行的id，如果是58，再取出下1页的时候，就是

select * from 学生表
where id>58
ORDER BY id
limit 5

这种方法的缺陷是，不方便随意跳转到其中一页。

需要先通过offset的方法获取前一页的最后一条记录的ID

比如，要跳转到第20页，根据公式

OFFSET = (PAGE-1) * LIMIT
       = (20-1) * 5
       = 95

第20页开始的 offset 是 95，第19页的最后一条记录的offset 就是 94

那么先执行如下 SQL语句

select id from 学生表
ORDER BY id
limit 1 offset 94;

得到 19页的最后一条记录的ID，假如是 95643，然后再执行

select * from 学生表
where id>95643
ORDER BY id
limit 5

记录数量、平均值、最大值、最小值

有的时候，我们要获取符合某种条件的记录有多少条。

比如，我们想知道学生表中姓名以白月黑羽开头的有多少条记录。

可以使用 SQL函数 count ，如下所示

select count(*) from 学生表 where 姓名 like "白月黑羽%";

count是 SQL 函数，用来返回记录数量。

常用的SQL函数，还有

sum

计算某列数字的总和，比如

select sum(分数) from 成绩表;

就可以获取成绩表中所有记录分数得分总和

avg

计算某列数字的平均，比如

select avg(分数) from 成绩表;

就可以获取成绩表中所有记录平均之后的得分

max

得到某列数字的最大值，比如

select max(分数) from 成绩表;

就可以获取成绩表中所有记录的最高得分

min

得到某列数字的最小值，比如

select min(分数) from 成绩表;

就可以获取成绩表中所有记录的最低得分

修改表记录

点击这里，看视频讲解，学习下面内容

我们经常需要更新表记录信息，比如一个用户更新了他的头像，消费后余额更新等等。

MySQL更新记录使用的是 update 语句。

前面我们曾经创建过customer1 表，如果我们要更新其中用户名为 cus16 这条记录，把coin值设置为 100，就可以这样写

update customer1 set coin=100 where username='cus16';

注意，这里也用到了 where 从句，限制了更新的只是 username为cus16的这些记录。

否则，如果没有where 从句，像这样

update customer1 set coin=100;

就会更新该表中所有的记录，把所有的记录的coin 全部设置为 100

where 从句也是用来，过滤要更新的记录的，用法和select里面where 从句的用法是一样的。

如果我们要更新多个列里面的内容，比如要把username为cus16的记录，把coin值设置为 100，level值设置为 30，就可以这样写

update customer1 set coin=100,level=30 where username='cus16';

另外SQL语句中间是可以换行的，像下面这样，也是可以的

update customer1 
set 
  coin=100,
  level=30 
where username='cus16';

您需要高效学习，找工作？点击咨询报名实战班

点击查看学员就业情况

删除表记录

点击这里，看视频讲解，学习下面内容

我们有时也需要删除表记录。

MySQL删除记录使用的是 delete from 语句。

前面我们曾经创建过customer1 表，如果我们要删除其中cus16这条记录，可以这样写

delete from customer1  where username='cus16';

注意，这里也用到了where 从句，限制了删除的只是 username为cus16的这些记录。

否则，如果没有where 从句，像这样

delete from customer1;

就会删除该表中所有的记录。

删除表中所有的记录，但是表还在，这个和删除表 drop table 是不同的。

where 从句也是用来过滤要删除的记录的，用法和select里面where 从句的用法是一样的。

同时修改记录

如果有多个客户端同时修改一个记录，会不会导致覆盖问题呢？

Mysql 有锁机制，会保证当一个修改正在进行时，另外的修改会等待前面的操作完成。

所以不必担心这种情况。客户端程序也无需写任何检测是否有修改正在进行的代码。

查询、修改、删除表记录

查询表记录

查询过滤

过滤条件

并且关系

或者关系

子查询

多表连接查询

查询结果去重

查询结果排序

查询结果分组合并

查询结果分页

LIMIT + OFFSET

LIMIT + ID 方法

记录数量、平均值、最大值、最小值

修改表记录

您需要高效学习，找工作？ 点击咨询 报名实战班

点击查看学员就业情况

删除表记录

同时修改记录

您需要高效学习，找工作？点击咨询报名实战班