前言
单值函数
单值函数的定义
首先什么是单值函数,单值单值就是单个输入,操作单个变量或数值的函数。
具体的特性如下:
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以嵌套
参数可以是一列或一个值
单值函数的分类
数值函数
字符串函数
注意在MySQL中,字符串的位置是从1开始的。
日期与时间函数
加密与解密函数
MySQL信息函数
VERSION()、CONNECTION_ID()、DATABASE()、SCHEMA()、USER()、
使用LPAD,RPAD实现左对齐与右对齐的效果
SELECT employee_id,last_name,LPAD(salary,10,' ')
FROM employees;
4.查询员工id,last_name,salary,并作为一个列输出,别名为OUT_PUT
SELECT CONCAT(employee_id,',',last_name,',',salary) OUT_PUT
FROM employees;
MySQL语句自带循环控制结构
我们在客户端登录信息,然后发送信息给服务器验证的过程中,客户端就已经实现了加密,信息在传输的过程中都是加密的
MD5与SHA加密都是不可逆的加密。
聚合函数
数值型的聚合函数
聚合函数类型:
AVG():限制数值类型
SUM():限制数值类型
MAX():任意数据类型
MIN():任意数据类型
COUNT():返回表中某种记录的总数
COUNT(*):会统计值为 NULL 的行
COUNT(expr):返回expr不为空的记录总数
Mysql中的聚合函数是不能够嵌套的(ORACLE支持),单行函数可以嵌套,因为聚合函数的输出也是单个值,无法作为参数传递给多值函数
#需求:查询公司中平均奖金率
#错误的!
SELECT AVG(commission_pct)
FROM employees;
#正确的:
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;
--在mysql中会报错
MIN(AVG(salary))
GROUP BY
单分组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
使用GROUP BY子句将表中的数据分成若干组,在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中,包含在 GROUP BY 子句中的列不必包含在SELECT 列表中。
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
多分组
使用多个列分组
#需求:查询各个department_id,job_id的平均工资
#方式1:
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
#方式2:
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
#错误的!
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;
#结论1:SELECT中出现的非组函数的字段必须声明在GROUP BY 中。注意非组函数的字段这个词的意思
# 反之,GROUP BY中声明的字段可以不出现在SELECT中。
#结论2:GROUP BY 声明在FROM后面、WHERE后面,ORDER BY 前面、LIMIT前面
WITH ROLLUP
使用 WITH ROLLUP 关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量
当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的
#错误的:
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;
HAVING
过滤分组:HAVING子句
- 行已经被分组。
- 使用了聚合函数。
- 满足HAVING 子句中条件的分组将被显示。
- HAVING 不能单独使用,必须要跟 GROUP BY 一起使用
#要求1:如果过滤条件中使用了聚合函数,则必须使用HAVING来替换WHERE。否则,报错。
#要求2:HAVING 必须声明在 GROUP BY 的后面,注意与WHERE的不同
#要求3:开发中,我们使用HAVING的前提是SQL中使用了GROUP BY。
#练习:查询各个部门中最高工资比10000高的部门信息
#错误的写法:
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;
#正确的写法:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
#结论:当过滤条件中有聚合函数时,则此过滤条件必须声明在HAVING中。
# 当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE中或HAVING中都可以。但是,建议大家声明在WHERE中。
#练习:查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#方式1:推荐,执行效率高于方式2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
#方式2:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);
注意事项
非法使用聚合函数 : 不能在 WHERE 子句中使用聚合函数。
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
WHERE与HAVING的对比
优点 | 缺点 | |
---|---|---|
WHERE | 先筛选数据再关联,执行效率高 | 不能使用分组中的计算函数进行筛选 |
HAVING | 可以使用分组中的计算函数 | 在最后的结果集中进行筛选,执行效率较低 |
WHERE 和 HAVING 也不是互相排斥的,我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用HAVING,普通条件用 WHERE。这样,我们就既利用了 WHERE 条件的高效快速,又发
挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候,运行效率会有很大的差别。
SQL语句执行过程
#(1)from:从哪些表中筛选
#(2)on:关联多表查询时,去除笛卡尔积
#(3)where:从表中筛选的条件
#(4)group by:分组依据
#(5)having:在统计结果中再次筛选
#(6)order by:排序
#(7)limit:分页
- 关键字的顺序是不能颠倒的:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
- SELECT 语句的执行顺序(在 MySQL 和 Oracle 中,SELECT 执行顺序基本相同):
在 SELECT 语句执行这些步骤的时候,每个步骤都会产生一个 虚拟表 ,然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是,这些步骤隐含在 SQL 的执行过程中,对于我们来说是不可见的。
SQL语句执行原理
对虚拟表有了解,具体还是比较复杂