Mysql增删改查之查询记录

在讲解查询前，我为大家准备了一个数据表。这个表中存放着银行的余额和用户的基本信息。

我们定义了一个表结构，表名为money。

创建表的语句如下：

CREATE TABLE money (
id INT NOT NULL AUTO_INCREMENT ,
username VARCHAR(50) NOT NULL ,
balance FLOAT NOT NULL ,
province VARCHAR(20) NOT NULL ,
age TINYINT UNSIGNED NOT NULL ,
sex TINYINT NOT NULL ,
PRIMARY KEY (id(10))
) ENGINE = InnoDB CHARACTER SET utf8;

表结构和数据展示如下：

id	username	balance	province	age	sex
1	王宝强	120.02	上海	29	1
2	范冰冰	260.23	山东	40	0
3	黄晓明	150.86	山东	40	1
4	井柏然	810	辽宁	27	1
5	李冰冰	20.15	黑龙江	43	0
6	成龙	313	山东	63	1
7	杨幂	123	北京	30	0
8	刘诗诗	456	北京	29	1
9	柳岩	23.4	湖南	36	0
10	赵本山	3456	辽宁	63	1
11	汪峰	34.32	北京	44	1
12	郭德纲	212	天津	43	1

注：
balance 是指余额
province 是指省份

基础查询

类别	详细解示
基本语法	select * from 表;
示例	select * from money;
示例说明	查询money表中所有字段中的所有结果

注：”*” 是一种正则表达式的写法，表示匹配所有，上面的查询语句和下面的是等价：

mysql> select * from money;
+----+-----------+---------+-----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+-----------+-----+-----+
| 1 | 王宝强 | 120.02 | 湖北 | 29 | 1 |
| 2 | 范冰冰 | 260.23 | 山东 | 40 | 0 |
| 3 | 黄晓明 | 150.86 | 山东 | 40 | 1 |
| 4 | 井柏然 | 810 | 辽宁 | 27 | 1 |
| 5 | 李冰冰 | 20.15 | 黑龙江 | 43 | 0 |
| 6 | 成龙 | 313 | 山东 | 63 | 1 |
| 7 | 杨幂 | 123 | 北京 | 30 | 0 |
| 8 | 刘诗诗 | 456 | 北京 | 29 | 1 |
| 9 | 柳岩 | 23.4 | 湖南 | 36 | 0 |
| 10 | 赵本山 | 3456 | 辽宁 | 63 | 1 |
| 11 | 汪峰 | 34.32 | 北京 | 44 | 1 |
| 12 | 郭德纲 | 212 | 天津 | 43 | 1 |
+----+-----------+---------+-----------+-----+-----+
12 rows in set (0.00 sec)

指定字段查询

类别	详细解示
基本语法	select 字段 from 表;
示例	select id,username, balance from money;
示例说明	查询money表中id,username, balance字段中的所有结果

mysql> select id,username, balance from money;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | 王宝强 | 120.02 |
| 2 | 范冰冰 | 260.23 |
| 3 | 黄晓明 | 150.86 |
| 4 | 井柏然 | 810 |
| 5 | 李冰冰 | 20.15 |
| 6 | 成龙 | 313 |
| 7 | 杨幂 | 123 |
| 8 | 刘诗诗 | 456 |
| 9 | 柳岩 | 23.4 |
| 10 | 赵本山 | 3456 |
| 11 | 汪峰 | 34.32 |
| 12 | 郭德纲 | 212 |
+----+-----------+---------+
12 rows in set (0.00 sec)

查询单个字段不重复记录 distinct

类别	详细解示
基本语法	select distinct 字段 from 表;
示例	select distinct age deptno from money;
示例说明	查询money表中年龄唯一的所有结果

mysql> select distinct age deptno from money;
+--------+
| deptno |
+--------+
| 29 |
| 40 |
| 27 |
| 43 |
| 63 |
| 30 |
| 36 |
| 44 |
+--------+
8 rows in set (0.00 sec)

条件查询 where

类别	详细解示
基本语法	select 字段 from 表 where where条件;
示例	select * from money where age = 29;
示例说明	查询money表中年龄为29的所有结果

mysql> select * from money where age = 29;
+----+-----------+---------+----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+----------+-----+-----+
| 1 | 李文凯 | 120.02 | 湖北 | 29 | 1 |
| 8 | 刘诗诗 | 456 | 北京 | 29 | 1 |
+----+-----------+---------+----------+-----+-----+
2 rows in set (0.00 sec)

where后可接的条件

比较运算符结果集中将符合条件的记录列出来。上面的例子中，where 后面的田间是一个字段的 ‘=’。

除此之外，还可以使用>、<、>=、<=、!=等比较运算符；

符号	说明
>	大于
<	小于
>=	大于等于
<=	小于等于
!=	不等于
=	等于

逻辑运算符

多个条件还可以使用 or 、 and 等逻辑运算符进行多条件联合查询

符号	说明
or	或者
and	并且

我们来看一下多个条件的例子：

类型	详细内容
示例	select * from money where id <10 and
说明	查询所有字段要求id小于10 并且province='湖北'

mysql> select * from money where id <10 and province='湖北';
+----+-----------+---------+----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+----------+-----+-----+
| 1 | 王宝强 | 120.02 | 湖北 | 29 | 1 |
+----+-----------+---------+----------+-----+-----+
1 row in set (0.00 sec)

结果集排序

类别	详细解示
基本语法	select 字段 from 表 order by 字段排序关键词
示例	select id,username, balance from money order by balance desc;
示例说明	查询money表中的id,username,balance字段，按照余额进行降序排序

排序用到的关键词：

关键词	说明
asc	升序排列，从小到大（默认）
desc	降序排列，从大到小

在 select 出来之后的结果集中排序使用 order by ，其中 desc 和 asc 是排序顺序中的关键字。desc 表示按照字段进行降序排列，asc 表示升序排列，如果不写关键字默认升序排列。

mysql> select id,username, balance from money order by balance desc;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | 赵本山 | 3456 |
| 4 | 井柏然 | 810 |
| 8 | 刘诗诗 | 456 |
| 6 | 成龙 | 313 |
| 2 | 范冰冰 | 260.23 |
| 12 | 郭德纲 | 212 |
| 3 | 黄晓明 | 150.86 |
| 7 | 杨幂 | 123 |
| 1 | 王宝强 | 120.02 |
| 11 | 汪峰 | 34.32 |
| 9 | 柳岩 | 23.4 |
| 5 | 李冰冰 | 20.15 |
+----+-----------+---------+
12 rows in set (0.00 sec)

多字段排序

order by 后面可以跟多个不同的字段排序，并且排序字段的不同结果集的顺序也不同，如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序。

类别	详细解示
基本语法	select 字段 from 表 order by 字段1 排序关键词,... ...字段n desc\|asc;
示例	select id,username, balance from money order by balance desc,age asc;
示例说明	查询money表中的id,username,balance字段，按照余额进行降序排序,若余额全都一样，则再使用age进行升序排序

* 注：如果第一个字段已经将结果给排好。第二个字段排序字段不生效。本例中，第二个字段无效。*

mysql> select id,username, balance from money order by balance desc,age asc;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | 赵本山 | 3456 |
| 4 | 井柏然 | 810 |
| 8 | 刘诗诗 | 456 |
| 6 | 成龙 | 313 |
| 2 | 范冰冰 | 260.23 |
| 12 | 郭德纲 | 212 |
| 3 | 黄晓明 | 150.86 |
| 7 | 杨幂 | 123 |
| 1 | 王宝强 | 120.02 |
| 11 | 汪峰 | 34.32 |
| 9 | 柳岩 | 23.4 |
| 5 | 李冰冰 | 20.15 |
+----+-----------+---------+
12 rows in set (0.00 sec)

结果集限制

对于查询或者排序后的结果集，如果希望只显示一部分而不是全部，使用 limit 关键字结果集数量限制。

类别	详细解示
基本语法	select 字段 from 表 limit 数量;
示例	select id,username, balance from money limit 5;
示例说明	显示前五个用户

mysql> select * from money limit 5;
+----+-----------+---------+-----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+-----------+-----+-----+
| 1 | 王宝强 | 120.02 | 湖北 | 29 | 1 |
| 2 | 范冰冰 | 260.23 | 山东 | 40 | 0 |
| 3 | 黄晓明 | 150.86 | 山东 | 40 | 1 |
| 4 | 井柏然 | 810 | 辽宁 | 27 | 1 |
| 5 | 李冰冰 | 20.15 | 黑龙江 | 43 | 0 |
+----+-----------+---------+-----------+-----+-----+
5 rows in set (0.00 sec)

限制结果集并排序

类别	详细解示
基本语法	select 字段 from 表 order by 字段关键词 limit 数量
示例	select id,username, balance from money order by balance desc limit 5;
示例说明	按照钱来排序，显示前五个最有钱的用户

mysql> select id,username, balance from money order by balance desc limit 5;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | 赵本山 | 3456 |
| 4 | 井柏然 | 810 |
| 8 | 刘诗诗 | 456 |
| 6 | 成龙 | 313 |
| 2 | 范冰冰 | 260.23 |
+----+-----------+---------+
5 rows in set (0.00 sec)

结果集区间选择

假设我从第0条开始取了3条记录。又想再从第3条开始取3条记录。再想从第6条开始取4条记录怎么办？

这时候就需要使用到结果集区间选择。

类别	详细解示
基本语法	select 字段 from 表 limit 偏移量,数量
示例	select id,username, balance from money limit 0,3;
示例说明	从第一条开始取三条记录

注：第一条记录为0。

mysql> select id,username, balance from money limit 0,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | 王宝强 | 120.02 |
| 2 | 范冰冰 | 260.23 |
| 3 | 黄晓明 | 150.86 |
+----+-----------+---------+
3 rows in set (0.00 sec)

从第三条开始再取三条呢？

mysql> select id,username, balance from money limit 3,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 4 | 井柏然 | 810 |
| 5 | 李冰冰 | 20.15 |
| 6 | 成龙 | 313 |
+----+-----------+---------+
3 rows in set (0.00 sec)

通过上面的这个思路，显示就完成了分页。
每页显示10条记录，那么：

第1页为 limit 0,10
第2页为 limit 10,10
第3页为 limit 20,10

依此类推... ...

统计类函数使用

如果我们想知道总用户数怎么办？
查询谁是数据表里的首富怎么办？
如果我们想知道用户的平均金额怎么办？
如果我们想知道所有用户的总金额怎么办？

统计类函数最常用的我们有四个：

函数	说明
sum	求和
count	统计总数
max	最大值
min	最小值
avg	平均值

注：当然你知道其他的mysql函数也可以使用。不过，在实际工作中，大公司的很多大中型项上很少使用，他们都有专门的计数服务器。因为，mysql的计算量本身很大，为了减少压力通常我们将实际的计算任务交给业务服务器或其他服务器来完成。

类别	详细解示
基本语法	select 函数(字段) from 表
示例	select count(id) from money
示例说明	查询money表的id总数

mysql> select count(id) from money;
+-----------+
| count(id) |
+-----------+
| 12 |
+-----------+
1 row in set (0.00 sec)

你还可以给字段取别名哟！使用as关键字。

mysql> select count(id) as zongshu from money;
+---------+
| zongshu |
+---------+
| 12 |
+---------+
1 row in set (0.00 sec)

查询平均金额

mysql> select avg(balance) from money;
+--------------------+
| avg(balance) |
+--------------------+
| 498.24833393096924 |
+--------------------+
1 row in set (0.00 sec)

查询总金额

mysql> select sum(balance) from money;
+-------------------+
| sum(balance) |
+-------------------+
| 5978.980007171631 |
+-------------------+
1 row in set (0.00 sec)

查询最大金额

mysql> select max(balance) from money;
+--------------+
| max(balance) |
+--------------+
| 3456 |
+--------------+
1 row in set (0.00 sec)

查询最小金额

mysql> select min(balance) from money;
+--------------------+
| min(balance) |
+--------------------+
| 20.149999618530273 |
+--------------------+
1 row in set (0.00 sec)

分组 group by

我们拿金额表里面的省份进行分组数据，分组数据后你会发现。有相同的省份会去掉。即，一个省份为一个组。

类别	详细解示
基本语法	select * from 表 group by 字段
示例	select * from money group by province;
示例说明	按照地区进行分组

mysql> select * from money group by province;
+----+-----------+---------+-----------+-----+-----+
| id | username | balance | province | age | sex |
+----+-----------+---------+-----------+-----+-----+
| 7 | 杨幂 | 123 | 北京 | 30 | 0 |
| 12 | 郭德纲 | 212 | 天津 | 43 | 1 |
| 2 | 范冰冰 | 260.23 | 山东 | 40 | 0 |
| 1 | 王宝强 | 120.02 | 湖北 | 29 | 1 |
| 9 | 柳岩 | 23.4 | 湖南 | 36 | 0 |
| 4 | 井柏然 | 810 | 辽宁 | 27 | 1 |
| 5 | 李冰冰 | 20.15 | 黑龙江 | 43 | 0 |
+----+-----------+---------+-----------+-----+-----+

统计分组（分类）各总数：

mysql> select deptno, count(1) from emp group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 1 |
| 2 | 5 |
| 3 | 1 |
| 5 | 4 |
+--------+----------+
4 rows in set (0.04 sec)

统计省份数量后再进行分组显示

mysql> select count(province),province from money group by province;
+-----------------+-----------+
| count(province) | province |
+-----------------+-----------+
| 3 | 北京 |
| 1 | 天津 |
| 3 | 山东 |
| 1 | 湖北 |
| 1 | 湖南 |
| 2 | 辽宁 |
| 1 | 黑龙江 |
+-----------------+-----------+
7 rows in set (0.00 sec)

在分组基础上进行统计

with rollup用的很少。这个知识点设置为了解级别。

它的主要功能是对于分组的数据进行统计后，再进行一次总数统计。

类别	详细解示
基本语法	select * from 表 group by 字段 with rollup
示例	select count(province),province from money group by province with rollup;
示例说明	对分组的数再次进行统计

在上面的基础上统计总数，下例结果中，最后多了一个12 NULL。

mysql> select count(province),province from money group by province with rollup;
+-----------------+-----------+
| count(province) | province |
+-----------------+-----------+
| 3 | 北京 |
| 1 | 天津 |
| 3 | 山东 |
| 1 | 湖北 |
| 1 | 湖南 |
| 2 | 辽宁 |
| 1 | 黑龙江 |
| 12 | NULL |
+-----------------+-----------+
8 rows in set (0.00 sec)

结果再过滤having

having子句与where有相似之处但也有区别,都是设定条件的语句。

having 是筛选组而where是筛选记录。

类别	详细解示
基本语法	select * from 表 group by 字段 having 条件
示例	select count(province) as result ,province from money group by province having result >2;
示例说明	对地区分组并统计总数，将分组结果中大于2的分组地区显示出来

mysql> select count(province) as result ,province from money group by province having result >2;
+--------+----------+
| result | province |
+--------+----------+
| 3 | 北京 |
| 3 | 山东 |
+--------+----------+
2 rows in set (0.00 sec)

整体使用SQL

我们在上面的语句中都是单一使用的某些语句，没有整体使用过。

我们现在将语句进行整合后，配合使用一次。整体的SQL语句配合使用的语法结构如下：

SELECT
[字段1 [as 别名1],[函数(字段2) ,]......字段n]
FROM 表名
[WHERE where条件]
[GROUP BY 字段]
[HAVING where_contition]
[order 条件]
[limit 条件]

注：上面的语句中可以[] 代表可选。

最终的语法总结如下：

关键词	说明
select	选择的列
from	表
where	查询的条件
group by	分组属性 having 分组过滤的条件
order by	排序属性
limit	起始记录位置，取记录的条数

我们进行一次整体的给合使用，查询money表字段：id,username,balance,province 要求id>1 余额大于50，使用地区进行分组。我们使用用户id进行降序，要求只准显示3条。

最后将SQL语句写成，查询出来的结果如下：

mysql> select id,username,balance,province from money where id > 1 and balance > 50 group by province order by id desc limit 3;
+----+-----------+---------+----------+
| id | username | balance | province |
+----+-----------+---------+----------+
| 12 | 郭德纲 | 212 | 天津 |
| 7 | 杨幂 | 123 | 北京 |
| 4 | 井柏然 | 810 | 辽宁 |
+----+-----------+---------+----------+
3 rows in set (0.00 sec)

Weiter lernen