查询记录

查询记录

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

我们定义了一个表结构，表名为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;

表结构和数据展示如下：

QQ截图20161009154618.png

QQ截图20161009154630.png

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

基础查询

QQ截图20161009154649.png

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

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)

指定字段查询

QQ截图20161009154709.png

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

QQ截图20161009154725.png

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

条件查询 where

QQ截图20161009154741.png

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 后面的添加的是一个字段的 ‘=’。

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

QQ截图20161009154754.png

逻辑运算符

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

QQ截图20161009154833.png

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)

结果集排序

QQ截图20161009154905.png

在 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 后面可以跟多个不同的字段排序，并且排序字段的不同结果集的顺序也不同，如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序。

QQ截图20161009154934.png

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

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 关键字结果集数量限制。

QQ截图20161009154951.png

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)

限制结果集并排序

QQ截图20161009155004.png

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条记录怎么办？

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

QQ截图20161009155016.png

QQ截图20161009155026.png

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

依此类推... ...

统计类函数使用

1. 如果我们想知道总用户数怎么办？

2. 查询谁是数据表里的首富怎么办？

3. 如果我们想知道用户的平均金额怎么办？

4. 如果我们想知道所有用户的总金额怎么办？

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

QQ截图20161009155039.png

QQ截图20161009155132.png

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

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

QQ截图20161009155147.png

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用的很少。这个知识点设置为了解级别。

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

QQ截图20161009155200.png

QQ截图20161009155215.png

在上面的基础上统计总数，下例结果中，最后多了一个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是筛选记录。

QQ截图20161009155232.png

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 条件]

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

最终的语法总结如下：

QQ截图20161009155246.png

QQ截图20161009155305.png

我们进行一次整体的给合使用，查询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)

继续学习