数据库学习笔记（3）——函数、分组查询、连接查询

调用：
select 函数名(实参列表) from 表名;
分类：
（1）单行函数
	如：concat、length、isfull等
（2）分组函数
	功能：做统计使用，又称为统计函数、聚合函数

1.常用函数

1.1 字符函数

1.length 获取参数值的字节个数
select length('join')

2.concat 拼接字符串
select concat(last_name,' ',first_name) 姓名 from employees;

3.upper、lower 大小写
select upper('join');
select lower('joIn');

4.substr、substring
注意:索引从1开始
#截取从指定索引出后面所有字符
select substr('李莫愁爱上了陆展元',6) out_put;
#截取从指定索引处指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) out_put;

5.instr 返回子串在原字符串中的位置，如果找不到返回0
select instr('杨步梅爱上了闫柳霞','闫柳霞') 

6.trim 删除字符串左右两边的指定字符
select length(trim('   z   n')) as out_put;
select trim('a' from 'aaaaaa章aaaaaa子a') as out_put;

7.lpad 用指定的字符实现左填充指定长度
select lpad('殷素素',10,'*') as out_put;

8.rpad 用指定的字符实现右填充指定长度

9.replace 替换
select replace('张无忌爱上了周芷若','周芷若','赵敏');

1.2 数学函数

1.round 四舍五入
select round(-1.4);

2.ceil 向上取整，返回大于等于该参数的最小整数
select ceil(1.00);

3.floor 向下取整
select floor(-9.99);

4.truncate 截断
select truncate(1.6999,1);

5.mod 取余
select mod(10,-3);
select 10%3;

1.3 日期函数

1.now 返回当前系统日期+时间
select now();

2.curdate 返回当前系统日期，不包含时间
select curdate();

3.curtime 返回当前时间，不包含日期
select curtime();

4.可以获取指定的部分，年月日时分秒
select year(now()) 年;

5.str_to_date 将日期格式的字符转换成指定格式的日期
select str_to_date('1998-3-2','%Y-%c-%d') as out_put;

6.date_format 将日期转换成字符
select data_format(now(),'%y年%m月%d日');

1.4 其他函数

select version();
select database();
select user();

2.流程控制函数

1.if函数
select if(10>5,'大','小');

2.case函数
使用1：switch case的效果
case 要判断的字段后者表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或者语句n;
end
案例：查询员工的工资
select salary 原始工资，department_id,
case department——id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

使用2：类似于多重if
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或者语句n
end

3.分组函数

功能：用作统计使用，又称为聚合函数或统计函数、组函数

分类：
sum求和、avg平均值、max最大值、min最小值、count计算个数

语法
select sum(salary) from employees;

特点：
1.参数支持哪些类型
（1）sum、avg一般处理数值型
（2）max、min、count可以处理任何类型，
2.是否可以忽略null
所有分组函数都忽略null值
3.可以和distinct搭配去重
select sum(distinct salary) from employees;
4.count函数详细介绍
select count(salary) from employees;
select count(*) from employees;
select count(1) from employees;
select count('今') from employees;
效率：
INNODB存储引擎下，count(*)和count(1)的效率差不多
5.和分组函数一同查询的字段有限制
一般是group by后的字段
select avg(salary),employee_id from employees;

4.分组查询

语法:
SELECT  分组函数，列（要求出现在group by的后面）
from 表
where 筛选条件
group by 分组的列表
Order by 子句

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：
1.分组查询中的筛选条件分为两类
					数据源			位置				关键字
		分组前筛选	原始表			group by子句前		where
		分组后筛选	分组后的结果集	group by子句后		having
		
		（1）分组函数做条件肯定是放在having子句中
		（2）能用分组前筛选的，优先考虑分组前筛选

引入：查询每个部门的平均工资
select AVG(salary) 
from employees;

案例：查询每个工种的最高工资
select max（salary），job_id
from employees
group by job_id;

案例：查询每个位置上的部门个数
select count(*),location_id
from department
group by location_id;

#添加筛选条件
案例：查询邮箱中包含a字符的，每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;

案例：查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commision_pct is not null
group by manager_id;

#复杂查询
案例1：查询哪个部门的员工个数大于2
1.查询每个部门的员工个数
select count(*),department_id
from employees
group by department_id;
2.根据上一步结果进行筛选，查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;

案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
1.查询每个工种有奖金的员工的最高工资
select max(salary),job_id
from employees
where commision_pct is not null
group by job_id;
2.根据1的结果继续筛选，最高工资>12000
select max(salary),job_id
from employees
where commision_pct is not null
group by job_id
having max(salary)>12000;

案例3：查询领导编号>102的每个领导手下的最低工资的>5000的领导编号是哪个，以及其最低工资
1.查询领导编号>102的每个领导手下的员工的最低工资
select min(salary),manager_id
from employees
group by manager_id
2.添加筛选条件
select min(salary),manager_id
from employees
where manager_id >102
group by manager_id
having min(salary)>5000;

#按表达式或函数分组
案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
1.查询每个长度的员工个数
select count(*) 
from employees
group by length(last_name);
2.添加筛选条件
select count(*) 
from employees
group by length(last_name);
having count(*)>5;

#按多个字段分组
案例：查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;

#添加排序
select avg(salary),department_id,job_id
from employees
group by department_id,job_id;
Order by avg(salary) desc;

5.连接查询

含义：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询
分类：
	按年代分类：
	sql92标准：仅仅支持内连接
	sql99标准【推荐】：支持内连接+外连接（全外不支持）+交叉连接
	按功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		右外连接
		全外连接
	交叉连接

5.1 sql92标准

5.1.1等值连接

#1.等值连接
案例1：查询女神名和对应的男神名
select name,boyName
from boys,beauty
where beauty.boyfriend_id = boys.id;

案例2：查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.`department_id` = department_id.`department_id`

#2.为表起别名（提高语句的简洁度）
注意：如果为表起了别名，则查询的字段不能用原来的表名修饰
#查询员工名、工种号、工种名
select e.last_name,e.job_id,j.job_title
from employees as e,jobs as j
where e.`job_id`=j.`job_id`;

#3.两个表的顺序可以调换

#4.添加筛选
案例：查询有奖金的员工名和部门名
select last_name,department_name,commision_pct
from employees e,departments d
where e.`department_id`=d.`department_id` 
and commision_pct is not null

案例:查询城市中第二个字符为0的部门名和城市名
select department_name,city
from departments d,locations l
where d.`location_id` = l.`location_id`
and city like '_o%';

#5.添加分组
案例：查询每个城市的部门个数
select count(*) 个数，city
from departments d,locations l
where d.`location_id`=l.`location_id`
group by city;

案例：查询出有奖金的每个部门的部门名和部门编号和该部门的最低工资
select department_name,d.manager_id,min(salary)
from departments d,employees e
where d.`department_id`=e.`department_id`
and commision_pct is not null
group by department_name,d.manager_id;

#6.添加排序
案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
select job_title,count(*)
from employees e,jobs j
where e.`job_id`=j.`job_id`
group by job_title
order by count(*) desc;

#7.三表连接
案列：查询员工名、部门名和所在城市
select last_name,department_name,city
from employees e,departments d,locations l
where e.`department_id`=d.`department_id`
and d.`location_id`=l.`location_id`
and city like 's%';

5.1.2 非等值连接

案例：查询员工的工资和工资级别 
select salary,grade_level
from employee e,job_grades g
where  salary between g.`lowest_sal` and g.`highest_sal`;

5.1.3 自连接

案例：查询员工名及其上级的领导名
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id = m.employee_id;

5.2 sql99标准

语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 筛选条件
Order by 排序列表  

分类：
内连接：inner
外连接
	左外连接：left（outer）
	右外连接：right（outer）
	全外连接:full（outer）
交叉连接：cross

5.2.1 内连接

语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；

分类：
等值连接
非等值连接
自连接

特点：
添加排序分组筛选
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
inner join连接和sq92中的等值连接最终实现的效果都是一样的

1.等值连接
案例一：查询员工名，部门名
select last_name，department_name
from employees e
inner join departments d
on e.department_id = d.department_id;

案例二：查询名字中包含e的员工名和工种名
select last_name，job_title
from employees e
inner join jobs
on e。job_id = j.job.id
where e.last_name like '%e%'

案例三：查询部门个数>3的城市名和部门个数
select city ,count(*) 个数
from departments d
inner join locations l
on d.location_id = l.location_id
group by city
having count(*)>3;

案例四：查询员工名、部门名、工种名，并按部门名降序
select last_name，department_id,job_id
from employees e
inner join departments d on e.department_id = d.department_id
inner join jobs j on j.job_id = e.job_id
order by department_name desc;

2.非等值连接
#查询员工的工资级别
select salary，grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;

3.自连接
#查询员工名字、上级的名字
select e.last_name,m.last_name
from employees e
join employees m
on e.manager_id = m.employeee_id;

5.2.2 外连接

应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
	1.外连接的查询结果为主表中的所有记录
		如果从表中有匹配的显示对应的匹配值
		如果从表中没有匹配的则显示null
		外连接查询结果=内连接结果+主表中有而从表中没有的记录
	2.左外连接：left join左边的是主表
	  右外连接：right join右边的是主表
	3.左外连接和右外连接交换两个表的顺序，可以实现同样的效果

引入：查询男朋友 不在男神表的女神名
select b.name,bo.*
from beauty b
left outer join boys bo
on b.`boyfriend_id` = bo.`id`;
where bo.`id` is null;

案例：查询哪个部门没有员工
#左外
select d.*,e.employee_id
from departments d
left outer join employees e
on d.`department_id` = e.`department_id`
where e.`employee_id` is null;

#右外
select d.*,e.employee_id
from employees e
right outer join departments d
on d.`department_id` = e.`department_id`
where e.`employee_id` is null;

5.2.3 全外连接

全外连接结果=内连接结果+表一中有但表二中没有的+表二中有但表一中没有的

5.2.4 交叉连接(sql99语法标准实现笛卡尔乘乘积)

select b.*,bo.*
from beauty b
cross join boys bo;