MySQL高级

MySQL高级

1、常见的函数

调用：select 函数名(实参列表) from 表名

1.1、单行函数

字符函数：

--length:获取参数值的字节个数
select  length('length')

--concat:拼接字符串
select CONCAT (last_name,'-',first_name) from employees

--upper、lower:将字符变成大写或者小写
select upper('join')
select lower('joiN')

--substr(从1开始)
--截取从指定索引处后面的所有字符
select substr('千呼万唤始出来',5)
--输出：始出来

--截取从指定索引处指定字符长度的字符
select substr('千呼万唤始出来',1,4)
--输出：千呼万唤

--instr:返回子串第一次出现的索引位置，找不到返回0
select instr('杨不悔爱上了殷六侠','殷六侠')
--输出：7

--trim:去空格
select trim('  张三丰  ')
--输出：张三丰

--replace:用指定字符替换指定字符
select replace('张无忌爱上了周芷若','周芷若','赵敏')
--输出：张无忌爱上了赵敏

数学函数：

--round:四舍五入
select round(1.1)
--输出：1
--保留指定位数
select round(1.123,2)
--输出：1.12

--ceil:向上取整
select ceil(1.1)

--floor向下取整
select floor(2.3)

--truncate:截断
select truncate(1.99999,1)
--输出：1.9

--mod:取余

select mod(10,3)
--输出：1

1.2、分组（聚合函数）函数

--常见的聚合函数:sum、avg、max、min、count
select sum(salary) from employees

select avg(salary) from employees

select max(salary) from employees

select min(salary) from employees

select count(salary) from employees

--聚合函数支持哪些类型？
--sum、avg只支持数值类型
--max、min、count可以处理任何类型

--是否忽略null
--sum、avg忽略null值
--max、min、count忽略null值
--结论：所有的聚合函授都忽略null值

--和distinct搭配使用
select sum(distinct salary) from employee
select count(distinct salary) from employee

--count函数的详细介绍
select count(*) from employees
--查询所有的列，一条数据中只要有一列数据不为不为null就算一条

select count(1) from employees
--在每一列的最前面加一列常量，常量可为任何值

--两者的效率问题：
--在MYISAM存储引擎下，count(*)的效率高一些。
--在InnoDB存储引擎下，count(*)和count(1)差别不大，但是都比count(salary)效率高（因为这样会进行null值判断）。

--和聚合函数一起查询的字段有限制

2、分组查询

2.1、语法

select 聚合函数，列（要求出现在group by后面）

from 表名

where 条件

group by分组的列表

order by 子句

2.2、查询案例

1、查询每个工种的最高工资

select max(salary) job_id from employees group by job_id

2、查询每个位置上的部门个数

select count(1) location_id from department group by location_id

3、查询邮箱中包含"a"字符的，每个部门的平均工资

select avg(salary) department_id from employees where email like 'a' group by department_id

4、查询有奖金的每个领导手下员工的最高工资

select max(salary) manager_id from employee where commission is not null group by manager_id

2.3、复杂的查询案例(添加分组后的筛选)

1、哪个部门的员工个数大于2

select count(*),department_id from employees group by department_id having count(*) > 2

2、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select max(salary),job_id from employees where commission is not null group by job_id having maxz(salary) > 12000

3、查询领导编号大于100的每个领导手下的最低工资大于4000的领导编号及其最低工资

select min(salary) manager_id from employees  where manager_id > 100 group by manager_id having min(salary) > 4000

总结：分组查询中的筛选分两类

​ 数据源 位置 关键字

分组前筛选 原始表 group by关键字之前 where

分组后筛选 分组后的结果集 group by关键字之后 having

2.4、按表达式或函数分组

1、按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的

select count(*) length(last_name) from employees group by length(last_name) having count(*) > 5

2.5、按照多个字段进行分组

1、查询每个工种每个部门的员工平均工资

select avg(salary),department_id,job_id from employees group by department_id,job_id