一、MySQL之函数（单行函数、分组函数）
1、概述
调用语法：select函数名（实参列表）；
分组函数和单行函数的区别：
单行函数：将一个数据进行处理，返回一个值
分组函数（聚合函数）：将虚拟表看做一个组，处理一组数据，返回一个值（在一个行的集合（一组行）上进行操作，对每个组给一个结果）常见的分组函数：
sum（字段）：求该字段的所有值的和
avg（字段）：求该字段的平均值
max（字段）：求最大值
min（字段）：求最小值
count(字段)：计算该字段中的非空值的个数特点：
1.分组函数可以搭配筛选条件使用；
2.分组函数的参数可以作为字段或者表达式；
3.分组函数都忽略null值；
4.分组函数都支持的类型：
|--- max、min、count 支持任意类型
|--- sum、avg 仅仅支持数值类型
5.count的使用；
|--- count(*)或count(常量值)：统计结果集中的行数
|--- count（distinct字段）：实现去重后的统计
6.和分组函数一同查询的字段不能是任意字段，可以是group by后面的字段；
分类：
（1）、字符函数
concat (连接)、upper、lower、replace、substr、length、trim、lpad (左填充)、rpad (左填充)、instr (获取字串第一次出现的索引)（2）、数学函数
ceil (向上取整)、floor (向下取整)、round、mod (取模)、truncate (截断)、rand (取随机数0~1)（3）、日期函数
now str_to_date(当前日期时间)、year (年)、month (月)、day (日期)、date_format (日期格式转字符日期)、str_to_date (字符日期转日期格式)、curdate (当前日期)、curtime (当前时间)、hour (小时)、datediff (返回两个日期相差的天数)、monthname (以英文形式返回月)（4）、其它函数
database (数据库)、version (当前版本)、user (当前用户)、password('字符') 自动加密
2、结构
SELECT select_expr [, select_expr ...]
[FROM table_references]
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
分组SELECT的基本格式：
select [聚合函数] 字段名 from 表名
[where 查询条件]
[group by 字段名]
[having 过滤条件]
(1)、group by子句
根据给定列或者表达式的每一个不同的值将表中的行分成不同的组，使用组函数返回每一组的统计信息
规则：
①出现在SELECT子句中的单独的列，必须出现在GROUP BY子句中作为分组列
②分组列可以不出现在SELECT子句中
③分组列可出现在SELECT子句中的一个复合表达式中
④如果GROUP BY后面是一个复合表达式，那么在SELECT子句中，它必须整体作为一个表达式的一部分才能使用。
对于分组聚合注意：
通过select在返回集字段中，这些字段要么就要包含在group by语句后面，作为分组的依据，要么就要被包含在聚合函数中。我们可以将group by操作想象成如下的一个过程：首先系统根据select语句得到一个结果集，然后根据分组字段，将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在与group by语句后面作为分组依据的字段就很有可能出现多个值，但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这个时候就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是前面讲到的聚合函数，这也就是为什么这些函数叫聚合函数了。
(2)、having子句
having子句：对分组结果进行过滤:
|--- 不能使用WHERE子句对分组后的结果进行过滤；
|--- 不能在WHERE子句中使用组函数，仅用于过滤行；
WHERE子句比GROUP BY先执行，而组函数必须在分完组之后才执行，且分完组后必须使用having子句进行结果集的过滤.
having子语句与where子语句区别：
|--- where子句在分组前对记录进行过滤；
|--- having子句在分组后对记录进行过滤；
mysql> select salary,count(*) from salary_tab
-> where salary>=2000
-> group by salary
-> having count(*)>=0;
+---------+----------+
| salary | count(*) |
+---------+----------+
| 2000.00 | 1 |
| 3000.00 | 1 |
+---------+----------+
(1）HAVING可以单独使用而不和GROUP BY配合，如果只有HAVING子句而没有GROUP BY，表中所有的行分为一组
(2）HAVING子句中可以使用组函数
(3）HAVING子句中的列，要么出现在一个组函数中，要么出现在GROUP BY子句中(否则出错)
mysql> select town,count(*)
-> from PLAYERS
-> group by town
-> having birth_date>'1970-01-01';
ERROR 1054 (42S22): Unknown column 'birth_date' in 'having clause'
mysql> select town,count(*)
-> from PLAYERS
-> group by town
-> having town in ('Eltham','Midhurst');
+----------+----------+
| town | count(*) |
+----------+----------+
| Eltham | 2 |
| Midhurst | 1 |
+----------+----------+
2 rows in set (0.00 sec)
3、实例
（1）、简单的查询：
SELECT SUM(salary) 和,AVG(salary) 平均值, MAX(salary)最高工资,MIN(salary) 最低工资,COUNT(salary) 个数
FROM employees;
（2）、添加筛选条件:
案例：查询有奖金的员工的平均工资
SELECT AVG(salary) 平均工资
FROM employees
WHERE commission_pct IS NOT NULL;
（3）、分组函数的参数可以是表达式
案例：查询年薪的平均值
SELECT MAX(salary*12*(1+IFNULL(commission_pct)))
FROM employees;
（4）、指定一个列进行分组
select salary,count(*) from salary_tab
where salary>=2000
group by salary;
+---------+----------+
| salary | count(*) |
+---------+----------+
| 2000.00 | 1 |
| 3000.00 | 1 |
+---------+----------+
（5）、指定多个分组列，‘大组中再分小组’
select userid,count(salary) from salary_tab
where salary>=2000
group by salary,userid;
+--------+---------------+
| userid | count(salary) |
+--------+---------------+
| 2 | 1 |
| 3 | 1 |
+--------+---------------+
（6）、根据表达式分组
select year(payment_date),count(*)
from PENALTIES
group by year(payment_date);
+--------------------+----------+
| year(payment_date) | count(*) |
+--------------------+----------+
| 1980 | 3 |
| 1981 | 1 |
| 1982 | 1 |
| 1983 | 1 |
| 1984 | 2 |
+--------------------+----------+
（7）、带有排序的分组：如果分组列和排序列相同，则可以合并group by和order by子句
select teamno,count(*)
from MATCHES
group by teamno
order by teamno desc;
+--------+----------+
| teamno | count(*) |
+--------+----------+
| 2 | 5 |
| 1 | 8 |
+--------+----------+
select teamno,count(*)
from MATCHES
group by teamno desc; #可以把desc(或者asc)包含到group by子句中简化
+--------+----------+
| teamno | count(*) |
+--------+----------+
| 2 | 5 |
| 1 | 8 |
+--------+----------+
二、集合查询操作
union用于把两个或者多个select查询的结果集合并成一个
SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]
默认情况下，UNION = UNION DISTINCT
①进行合并的两个查询，其SELECT列表必须在数量和对应列的数据类型上保持一致；
②默认会去掉两个查询结果集中的重复行；默认结果集不排序；
③最终结果集的列名来自于第一个查询的SELECT列表
UNION ALL不去掉结果集中重复的行
注：联合查询结果使用第一个select语句中的字段名
mysql> select * from t1;
+------+------+
| num | addr |
+------+------+
| 123 | abc |
| 321 | cba |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | A |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from t1
-> union
-> select * from t2;
+------+------+
| num | addr |
+------+------+
| 123 | abc |
| 321 | cba |
| 1 | a |
| 2 | A |
+------+------+
4 rows in set (0.00 sec)
如果要对合并后的整个结果集进行排序，ORDER BY子句只能出现在最后面的查询中
注意：
在去重操作时，如果列值中包含NULL值，认为它们是相等的
三、连接查询（多表查询）
。。。。。。。。。。
待续...
。。。。。。。。。。
子查询
（1）、按子查询出现的位置
select 后面 （ 仅仅支持标量子查询 ）；
from 后面 （ 支持表子查询 ）；
✳where 后面 （ 支持标量子查询、行子查询、列子查询 ）；
exists 后面 （ 支持表子查询 ）；（2）、按结果集的行列数不同
标量子查询 （ 结果集只有一行一列 ）；
列子查询（ 结果集只有一列多行 ）；
行子查询 （ 结果集只有一行多列 ）；
表子查询（ 结果集一般为多行多列 ）；
where或having后面
（1）、标量子查询（单行子查询）；
（2）、列子查询（多行子查询）；
（3）、行子查询（多列子查询）；
特点：
①子查询放在()内；
②一般放在条件右侧；
③标量子查询，一般搭配单行操作等；
④列子查询搭配多行操作等；
四、其它
1、分支结构——case结构
情况1：类似与java中的 switch 语句，一般用于实现等值判断
语法：
CASE 变量 | 表达式 | 字段
WHEN 要判断的值 THEN 返回值1 或 语句1；
WHEN 要判断的值 THEN 返回值2 或 语句1；
....
ELSE 要返回的值n 或 语句n；
END CASE；
情况2：类似与java中的 if 语句，一般用于实现区间判断
CASE 变量 | 表达式 | 字段
WHEN 要判断的条件1 THEN 返回值1 或 语句1；
WHEN 要判断的条件2 THEN 返回值2 或 语句2；
....
ELSE 要返回的值n 或 语句n；
END CASE；
特定：
可以作为表达式，嵌套在其它语句中使用，可以放在任何地方， BEGIN END 中或 BEGIN END 的外面；
可以作为独立的语句去使用，只能放在 BEGIN END 中；
如果WHEN中的值满足或条件成立，则执行对应的 THEN后面的语句，并且结束 CASE，如果都不满足，则执行 ELSE 中的语句或值；
ELSE 可以省略，如果 ELSE省略了，并且所有 WHEN 条件都不满足，则返回 NULL
网友评论