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;

表结构和数据展示如下:

idusernamebalanceprovinceagesex
1王宝强120.02上海291
2范冰冰260.23山东400
3黄晓明150.86山东401
4井柏然810辽宁271
5李冰冰20.15黑龙江430
6成龙313山东631
7杨幂123北京300
8刘诗诗456北京291
9柳岩23.4湖南360
10赵本山3456辽宁631
11汪峰34.32北京441
12郭德纲212天津431

注:
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

依此类推... ...

统计类函数使用

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

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

函数说明
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)

继续学习
||
<?php echo "Hello Mysql"; ?>
提交重置代码