查询、修改、删除表记录
我们刚刚学习了插入记录到数据表中, 显然,接下来我们要学习的就是如何查询表记录
查询表记录
前面,我们已经在user表中插入了一批数据。
要查询该表中所有的记录,使用 SQL语句 select 进行查询
如下所示
得到的结果是
+----+----------+-----------+---------------+
| 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 这两列的信息,就可以这样写
得到的结果是
+----+----------+
| id | username |
+----+----------+
| 1 | byhy1 |
| 2 | byhy2 |
| 3 | byhy3 |
| 4 | byhy4 |
| 5 | byhy5 |
+----+----------+
5 rows in set (0.00 sec)
查询过滤
有的时候,一张数据表里面可能有海量的记录,比如上百万条,甚至上亿条记录(想象一下淘宝的用户表有多少)。
我们有时候只需要查询其中符合某些条件的部分信息。 这就叫过滤查询。
根据进过滤条件查询,我们使用 where从句
过滤条件
比如,上面的user表,我们只需要查询 用户名为 byhy3 的那个用户的信息,就可以这样
得到的结果是
+----+----------+-----------+---------------+
| id | username | password | realname |
+----+----------+-----------+---------------+
| 3 | byhy3 | password3 | 白月黑羽3 |
+----+----------+-----------+---------------+
1 row in set (0.00 sec)
其中 username='byhy3'
里面的等号就是一个过滤条件的 操作符
,表示过滤条件是查找记录的username字段值等于byhy3。
如果我们只需要查询 id为 2 的那个用户的信息,就可以这样
除了等于条件(使用 =), 还有
- 不等于(使用 <> 或者 !=)
- 大于(使用 > )
- 小于(使用 < )
- 大于等于(使用 >= )
- 小于等于(使用 <= )
- 包含字符 (使用 like 和 % )
- 在集合里面 (使用 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的用户。就可以这样
得到的结果是
+----+----------+-------+------+
| 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,比如
如果查询条件是日期时间在某个范围之内,使用 between and 关键字,如下所示
或者关系
如果你要查询的记录过滤条件是多个, 只要满足其中一个条件即可
,就使用 or 关键字连接多个 过滤条件
比如,我们 要查询 id > 10 或
等级 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 |
+----+----------+-------+------+
如果你直接这样写
运行一下,结果却发现,如下
+----+----------+-------+------+
| 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 先运算,如下所示。
子查询
已知有如下两张数据表,
表名:学生表
id | 姓名 | 性别 | 电话号码 | 学历 |
---|---|---|---|---|
1 | 白月黑羽 | 男 | 13000000001 | 本科 |
2 | 紫气一元 | 男 | 13000000002 | 硕士 |
3 | 无形冥主 | 男 | 13000000003 | 博士 |
4 | 拜月魔巫 | 女 | 13000000004 | 本科 |
表名:成绩表
id | 姓名 | 课程 | 分数 |
---|---|---|---|
1 | 白月黑羽 | 语文 | 100 |
2 | 紫气一元 | 英语 | 85 |
3 | 无形冥主 | 语文 | 100 |
4 | 无形冥主 | 英语 | 98 |
5 | 拜月魔巫 | 数学 | 100 |
要查询 考试成绩有100分的学生的电话号码
可以使用如下SQL语句
过滤条件 是 姓名在一个集合中,但是这个集合不是预先就知道的,而是 另外一个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分的学生的电话号码
其实,还可以使用 连接查询
,如下
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语句
结果如下
可以发现 语文 重复了两次。
因为有 两条记录 语文考试成绩为 100分。
但是我们现在的目的是 : 只要看有哪些学科,当然不希望重复。
这时,可以使用关键字 distinct
。
如下
就可以去掉结果中重复的内容了。
查询结果排序
我们经常需要,查询一个结果,并且按照某个字段的值进行排序。
比如,上面的查询
结果需要按照 coin 的值 由小到大排序,怎么办?
这时,可以使用 order by
比如
表示查询结果记录 根据 coin 字段的值 升序(ASC) 排列。 所谓升序,就是从小到大。
缺省就是按照升序排列,当然你也可以指定是升序,像这样
如果你想 反过来,降序排列,则使用 desc
,像这样
有时候,你希望 显示记录 先根据 coin排列 ,coin相同的再根据 level 进行排序,就可以这样 写
你希望 显示记录 先根据 coin 升序 排列 ,coin相同的再根据 level 降序 排序,就可以这样 写
查询结果分组合并
前面的成绩表,假设有如下内容
+----+--------------+--------+--------+
| 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
函数 ,如下
Group By
是按照什么分组的意思,经常和 SUM
、 COUNT
一起使用
SUM 是字段值求和, 当然这个字段值应该是数字
COUNT 是字段值出现次数
比如,我们要统计各学生参加了几次考试,就可以这样
结果如下
+--------------+--------------+
| 姓名 | 考试次数 |
+--------------+--------------+
| 白月黑羽 | 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
指定从结果集中, 从第几条记录开始
获取结果。
比如
-
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页
应用程序记录下最后一行的id,如果是58,再取出下1页的时候,就是
这种方法的缺陷是,不方便随意跳转到其中一页。
需要先通过offset的方法 获取前一页的 最后一条记录的ID
比如,要跳转到第20页,根据公式
第20页 开始的 offset 是 95,第19页的最后一条记录的offset 就是 94
那么先执行 如下 SQL语句
得到 19页的最后一条记录的ID,假如是 95643, 然后再执行
记录数量、平均值、最大值、最小值
有的时候,我们要获取符合某种条件的记录 有多少条。
比如 ,我们想知道 学生表中 姓名以 白月黑羽 开头的 有多少条记录。
可以使用 SQL函数 count ,如下所示
count是 SQL 函数,用来返回记录数量。
常用的SQL函数,还有
- sum
计算某列数字的总和,比如
就可以获取成绩表中所有记录分数 得分总和
- avg
计算某列数字的平均,比如
就可以获取成绩表中所有记录平均之后的得分
- max
得到某列数字的最大值,比如
就可以获取成绩表中所有记录的最高得分
- min
得到某列数字的最小值,比如
就可以获取成绩表中所有记录的最低得分
修改表记录
我们经常需要更新表记录信息,比如一个用户更新了他的头像,消费后余额更新等等。
MySQL更新记录使用的是 update
语句。
前面我们曾经创建过customer1 表,如果我们要更新其中用户名为 cus16 这条记录,把coin值设置为 100,就可以这样写
注意, 这里也用到了 where 从句,限制了更新的 只是 username为cus16的这些记录。
否则,如果没有where 从句,像这样
就会更新该表中所有的记录,把所有的记录的coin 全部设置为 100
where 从句也是用来,过滤要更新的记录的, 用法和select里面where 从句的用法是一样的。
如果我们要更新多个列里面的内容,比如要把username为cus16的记录,把coin值设置为 100,level值设置为 30,就可以这样写
另外SQL语句中间是可以换行的,像下面这样,也是可以的
您需要高效学习,找工作? 点击咨询 报名实战班
点击查看学员就业情况
删除表记录
我们有时也需要删除表记录。
MySQL删除记录使用的是 delete from
语句。
前面我们曾经创建过customer1 表,如果我们要删除其中cus16这条记录,可以这样写
注意, 这里也用到了where 从句,限制了 删除的 只是 username为cus16的这些记录。
否则,如果没有where 从句,像这样
就会删除该表中所有的记录。
删除表中所有的记录,但是表还在,这个和删除表 drop table 是不同的。
where 从句也是用来过滤要删除的记录的, 用法和select里面where 从句的用法是一样的。
同时修改记录
如果有多个客户端同时修改一个记录,会不会导致覆盖问题呢?
Mysql 有锁机制,会保证当一个修改正在进行时,另外的修改会等待前面的操作完成。
所以不必担心这种情况。 客户端程序也无需写任何检测是否有修改正在进行的代码。