8 .数据库-查-高级查询
8 .数据库-查-高级查询
- 聚合函数
为了要给数据做统计，例如算出某个字段的最大值，所以MYSQL给出了很多聚合函数来供给用户做统计、运算操作：
注意：运算函数字段类型要是数值类型！不是也可以 但会返回错误的结果。
1.1 COUNT()函数
COUNT函数用来统计记录的条数，语法格式如下：
SELECT COUNT(*) FROM 表名;
注意！ COUNT和括号之间没有空格！！！
例： 查询tb_bihu这个表中有多少条记录：
SELECT COUNT(*) FROM tb_bihu;
1.2 SUM()函数
SUM()函数 是求和函数，用于求出表中所有值的总和，语法格式入下：
SELECT SUM(字段名) FROM 表名;
注意的是，字段一定要是数值类型的啊！不然会返回一个我也看不懂的随机数?
例：求出tb_bihu表中，全部人的年龄总和：
SELECT SUM(age) FROM tb_bihu;
1.3 AVG() 函数
用于求出指定字段所有值的平均值，语法格式如下：
SELECT AVG(字段名) FROM 表名;
例：求出tb_bihu这个表中全部人总年龄的平均值
SELECT AVG(age) FROM tb_bihu;
1.4 MAX() 函数
MAX() 函数 用于求出某个字段的最大值，语法格式如下：
SELECT MAX(字段) FROM 表名;
例：求出tb_bihu 这个表中，年龄最大的人的记录：
SELECT MAX(age) FROM tb_bihu;
1.5 MIN() 函数
MIN () 函数 用于求出某个字段的最大值，语法格式如下：
SELECT MIN(字段) FROM 表名;
例：求出tb_bihu 这个表中，年龄最小的人的记录：
SELECT MIN (age)FROM tb_bihu;
- 对查询结果排序
从表中查询出来的结果可能是无序的，或者排的序不是用户说心仪的，所以为了满足用户，MYSQL 可以用 ORDER BY 关键字进行对结果排序，语法格式如下：
SELECT… ORDER BY 字段名1 [ ASC | DESC ], 字段名2 [ ASC | DESC ]
其中 指定的字段名1 和 字段名2 是对查询结果排序的依据，参数ASC表示按照升序进行排序，DESC则是按照降序进行排序。默认情况下，按照ASC方式进行排序！
例：查询tb_bihu表中的全部记录，并按照年龄age 进行排序（默认升序）：
SELECT * FROM tb_bihu ORDER BY age;
默认是按 ASC 升序排序，你也可以降序降序排序：
SELECT * FROM tb_bihu ORDER BY age DESC;
因为ORDER BY 关键字允许多个字段排序，所以他会按照顺序来排，分主排、次排…
所以如果字段1 和 字段2 的值一样的话 那么就会按照字段二（次排）来排序：
例：查询tb_bihu表中的全部记录，并按照sex[0/1]性别升序排序，且按照年龄升序排序：
SELECT * FROM tb_bihu ORDER BY sex DESC , age DESC;
- 分组查询
对数据统计时，有时候需要按照一定的类别进行分组统计，语法格式如下：
SELECT…GROUP BY 字段名1,字段名2,…[ HAVING 条件表达式 ];
其中：
l 字段1 和 字段2等 是对查询结果分组的依据；
l HAVING关键字指定条件表达式对分组后的内容进行过滤；
l 需要注意的是，GROUP BY 一般和聚合函数一起用，如果查询的字段出现在GROUP BY 后，却没有包含在聚合函数中，该字段显示的是分组后的第一条记录的值，这样有可能导致查询的结果不是我们心仪的。
l 由于分组复杂，下面分几种情况对其讲解：
3.1 单独使用GROUP BY 分组
单独使用GROUP BY 关键字的话，查询的每个分组的一条记录：
例: tb_bihu 这个表中 用age年龄进行分组查询：
SELECT * FROM tb_bihu GROUP BY sex;
|
id |
name |
sex |
age |
|
12 |
cc |
Null |
Null |
|
2 |
hjy |
0 |
19 |
|
1 |
hgy |
1 |
18 |
所以！你看下 他只有三条记录，age 有空的 有男的 有女的 所以说：
单独使用不配合聚合函数使用的话 会只查询每个分组的一条记录！
3.2 GROUP BY 和 聚合函数一起使用
它和聚合函数一起使用可以统计出某个或者某些字段在一个分组中的最大、最小、平均值等….
例：计算tb_bihu表中男、女 和 没填(Null)各有多少人：
SELECT COUNT(*) , sex FROM tb_bihu GROUP BY sex;
|
id |
sex |
|
1 |
Null |
|
6 |
0 |
|
5 |
1 |
可以看出 未填写性别一人 ，男6人,女1人， 分析：
查询字段 ： 1.聚合函数查询字段总结、2.查询sex性别字段
分组字段： 1.按照sex 性别来分。
3.3 GROUP BY 和 HAVING 关键字一起使用
HAVING关键字 和 WHERE 关键字的作用相同，都用于设置条件表达式对查询结果进行过滤，HAVING 关键字后面可以跟聚合函数，而WHERE不能，通常情况下 HAVING 关键字和 WHERE关键字一起使用，对于分组后的结果进行过滤。
这里举2个例子 即可完成学习：
例1：计算tb_bihu表中男、女 各有多少人 【注意审题啊 只要查询男和女】：
SELECT COUNT(*),sex FROM tb_bihu GROUP BY sex HAVING sex = 1 OR sex = 0;
可以看出 其实呢 HAVING 后面接的就是条件，分组的条件。
提示！！！ COUNT 函数 和 MIN 函数 是两个不一样的函数，前者为指定字段的所有数，一个是指定字段的所有值。
例2： 查询tb_bihu这个表中 按照age年龄分组，各男女的年龄，而且 总年龄要小于90记录：
SELECT sex, SUM(age) FROM tb_bihu GROUP BY sex HAVING (sex = 0 OR sex = 1) AND SUM(age) < 90;
记住HAVING后面那个 括号括号可以改变运算的优先级 所以先计算OR在计算AND哦。
3.4 使用LIMIT限制查询结果的数量
查询数据时，可能会返回多条记录，有时可能只需要每一条或者某一捆（几条），比如分页功能，一页10条记录，那么LIMIT 就来啦：
语法格式：
SELECT…LIMIT [ OFFSET, ] 记录数
其中：
LIMIT 后面可以跟两个参数,第一个参数OFFSET表示偏移量，如果为0，那么从第一条记录开始，意思是就是说 他的偏移量是从0开始的 默认是0；第二个参数呢 是记录数，表示要查询多少条记录出来：
例 查询tb_bihu这个表中 前四条记录：
SELECT * FROM tb_bihu LIMIT 4;
因为偏移量默认是0 从第一条开始，所以呢 直接写4 就是查询4条出来。
例2：查询tb_bihu这个表中，第5到 第 10 的记录：
SELECT * FROM tb_bihu LIMIT 4,5;
3.5 函数（列表）
下面列举函数，但具体的自行看功能和自行去实践：
MYSQl中提供了丰富的函数，这些函数可以简化用户对数据的操作，包括了：
数学函数、字符串函数、日期和时间函数、条件判断函数、加密函数等…
函数很多很多，下面列举常用的：
函数表1 : 数学函数
|
函数名称 |
作用 |
|
ABS() |
返回x的绝对值 |
|
SQRT() |
返回x的非负2次方跟 |
|
MOD(X,Y) |
返回x被y出后的余数 |
|
CEILING(X) |
返回不小于x的最小整数 |
|
FLOOR(X) |
返回不大于x的最大整数 |
|
ROUND(X,Y) |
对x进行四舍五入操作，小数点保留y位 |
|
TRUNCATE(X,Y) |
舍去x中小数点y位后面的数 |
|
SIGN(X) |
返回x的符号，-1、0或者1 |
函数表2:字符串函数
|
函数名称 |
作用 |
|
LENGTH(str) |
返回字符串str的长度 |
|
CONCAT(s1,s2,…) |
返回一个或多个字符串连接产生的新的字符串 |
|
TRIM(str) |
删除字符串两端的空格（删首尾空） |
|
REPLACE(str,s1,s2) |
使用字符串s2替换字符串str中所有的字符串s1 |
|
SUBSTRING(str,n,len) |
返回字符串的字串，起始位置为n，长度为len |
|
REVERSE(str) |
返回字符串反转后的结果 |
|
LOCATE(s1,str) |
返回字串s1在字符串str中的起始位置 |
函数表3：日期和时间函数
|
CURDATE() |
获取系统当前日期 |
|
CURTIME() |
获取系统当前时间 |
|
SYSDATE() |
获取当前系统日期和时间 |
|
TIME_TO_SEC() |
返回将时间转换为秒的结果 |
|
ADDDATE() |
执行日期的加运算 |
|
SBUDATE() |
执行日期的减运算 |
|
DATE_FORMAT() |
格式化输出日期的时间值 |
函数表4：条件判断函数
|
IF(expr,v1,v2) |
如果exor表达式为true返回r1 ，否则返回r2 |
|
IFNULL(v1,v2) |
如果v1不为NULL 返回v1 否则返回v2 |
|
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2 …] [ELSE rn] END |
如果expr值等于v1、v2等,则返回对应位置THEN后面的结果，否则返回ELSE后的结果rn |
函数表5：加密函数
|
MD5(str) |
对字符串str进行MD5加密 |
|
ENCODE(str,pwd_str) |
使用pwd作为密码加密字符串str |
|
DECODE(str,pwd_str) |
使用pwd作为密码解密字符串str |
上面那么多 自己去测试 下面测试一下 IF 和 CONCAT函数：
例：查询tb_bihu这个表中全部记录，查询的各个字段值用 – 号连接起来：
SELECT CONCAT(id,'-',name,'-',age) FROM tb_bihu;
例： 用IF函数查询是sex是1的话 打印‘男’ 不然打印’女’ :
SELECT name, IF(sex = 1,'男','女') FROM tb_bihu;
例： 显示系统当前时间：
SELECT CURDATE(),CURTIME(),CURRENT_DATE()+0;
CURDATE()和CURRENT_DATE()都可以查询到当前时间的年月日,CURRENT_DATE()+0是将当前查询到的时间转成数字输出
- 为表和字段取别名
查询数据时 可以为字段和表取一个别名，这个别名可以代替表和字段，即：
4.1为表取别名：
语法格式：
SELECT * FROM 表名 [AS] 别名;
其中AS可以不写 省略掉。
例：为tb_bihu这个表起一个别名，然后查询这个表中sex为0的记录：【查询所有女】
SELECT * FROM tb_bihu AS tb WHERE tb.sex = 0;
4.2 给字段取别名：
语法格式：
SELECT * 字段名 [AS] 别名 [ ,字段名 [AS] 别名,… ] FROM 表名;
例：查询tb_bihu这个表中全部的 name 和 age ，并给他们起别名：
SELECT name AS 名字, age AS 年龄 FROM tb_bihu;
单表查询暂时那么多，含有很多隐藏的操作，自己慢慢学
本文来自博客园，作者：咸瑜，转载请注明原文链接：https://www.cnblogs.com/bi-hu/p/14831709.html
