MySQL数据库的条件查询（1）

一：条件查询

where

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

select * from 表名 where 条件;
例：
select * from students where id=1;

where后面支持多种运算符，进行条件的处理

比较运算符
逻辑运算符
模糊查询
范围查询
空判断

比较运算符

等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>

例1：查询编号大于3的学生	select * from students where id > 3;
例2：查询编号不大于4的学生	select * from students where id <= 4;
例3：查询姓名不是“黄蓉”的学生	select * from students where name != '黄蓉';
例4：查询没被删除的学生	select * from students where is_delete=0;

逻辑运算符

例5：查询编号大于3的女同学	select * from students where id > 3 and gender=0;
例6：查询编号小于4或没被删除的学生	select * from students where id < 4 or is_delete=0;

模糊查询

like
%表示任意多个任意字符
_表示一个任意字符

例7：查询姓黄的学生	select * from students where name like '黄%';
例8：查询姓黄并且“名”是一个字的学生	select * from students where name like '黄_';
例9：查询姓黄或叫靖的学生	select * from students where name like '黄%' or name like '%靖';

范围查询

in表示在一个非连续的范围内
between ... and ...表示在一个连续的范围内

例10：查询编号是1或3或8的学生	select * from students where id in(1,3,8);
例11：查询编号为3至8的学生	select * from students where id between 3 and 8;
例12：查询编号是3至8的男生	select * from students where (id between 3 and 8) and gender=1;

空判断

注意：null与''是不同的
判空is nu
判非空is not null

例13：查询没有填写身高的学生	select * from students where height is null;
例14：查询填写了身高的学生	select * from students where height is not null;
例15：查询填写了身高的男生	select * from students where height is not null and gender=1;

优先级

优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用

二：排序

语法：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

说明

将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
排序使用 order by 关键字
默认按照列值从小到大排列（asc）
asc从小到大排列，即升序
desc从大到小排序，即降序

例1：查询未删除男生信息，按学号降序	select * from students where gender=1 and is_delete=0 order by id desc;
例2：查询未删除学生信息，按名称升序	select * from students where is_delete=0 order by name;
例3：显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时按照身高从高-->矮排序	select * from students order by age desc,height desc;

三. 聚合函数

1. 聚合函数的介绍

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。

常用的聚合函数:

count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值

求总行数count

求最大值max

求最小值min

求和sum

求平均值avg

-- 返回非NULL数据的总行数.
select count(height) from students; 
-- 返回总行数，包含null值记录;
select count(*) from students;

-- 查询女生的编号最大值
select max(id) from students where gender = 2;

-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

-- 查询男生的总身高
select sum(height) from students where gender = 1;
-- 平均身高
select sum(height) / count(*) from students where gender = 1;

-- 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
-- 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;

说明

ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。

2. 聚合函数的特点

聚合函数默认忽略字段为null的记录要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。

四. 分组

1. 分组查询介绍--group by

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

group by可用于单个字段分组，也可用于多个字段分组

分组查询基本的语法格式如下：

group by 列名 [having 条件表达式] [with rollup]

说明:

列名: 是指按照指定字段的值进行分组。
having 条件表达式: 用来过滤分组后的数据。
with rollup：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

组合使用：

group by
group by + group_concat()
group by + 聚合函数
group by + having
group by + with rollup

group by的使用

group by + group_concat()的使用

group_concat(字段名):

　　统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割

group by + 集合函数

通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
| 11 | 金星      |   33 | 162.00 | 中性   |      3 |          |
| 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
| 13 | 周杰      |   34 | 176.00 | 女     |      5 |           |
| 14 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
+----+-----------+------+--------+--------+--------+-----------+

select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

根据gender字段来分组，gender字段的全部值有4个'男','女','中性','保密'，所以分为了4组

当group by单独使用时，只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大

select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
| 中性   | 金星                                                       |
| 保密   | 凤姐                                                       |
+--------+-----------------------------------------------------------+


select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age)    |
+--------+----------------------+
| 男     | 29,59,36,27,12       |
| 女     | 18,18,38,18,25,12,34 |
| 中性   | 33                   |
| 保密   | 28                   |
+--------+----------------------+


分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+

分别统计性别为男/女的人的个数
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
+--------+----------+

group by + having的使用

having 条件表达式：用来分组查询后指定一些条件来输出查询结果
having作用和where一样，但having只能用于group by

group by + with rollup的使用

with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+


select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+