在讲解查询前，我为大家准备了一个数据表。这个表中存放着银行的余额和用户的基本信息。
我们定义了一个表结构，表名为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)