mysql学习：进阶6：连接查询

# 进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
		按年代分类：
		sql92标准:仅仅支持内连接
		sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
		
		按功能分类：
				内连接：
						等值连接
						非等值连接
						自连接
				外连接：
						左外连接
						右外连接
						全外连接
				交叉连接
		
*/

select * from beauty;
select * from boys;

select name,boyName from boys,beauty;

select name,boyName from boys,beauty
where beauty.boyfriend_id=boys.id;

# 一、sql92标准

/*

1、多表连接的结果为多表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名
5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

*/
# 案例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 = departments.department_id;

# 2、为表起别名
/*
1、提高语句的简洁渡
2、区分多个重名字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
# 查询员工名、工种号、工种名
select last_name,e.job_id,j.job_title
from employees e,jobs j
where e.job_id = j.job_id;

# 3、两个表的顺序是否可以调换

# 查询员工名、工种号、工种名
select last_name,e.job_id,j.job_title
from jobs j,employees e
where e.job_id = j.job_id;

# 4、可以加筛选？

# 案例：查询有奖金的员工名、部门名
select last_name,department_name,commission_pct
from employees e,departments d
where  e.department_id=d.department_id
and e.commission_pct is not null;

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

# 5、可以加分组？

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

# 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select department_name,d.manager_id,min(salary)
from departments d,employees e
where d.department_id=e.department_id
and commission_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;

# 3、可以实现三表链接

# 案例：查询员工名、部门名和所在的城市
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;

# 2、非等值连接

# 案例1：查询员工的工资和工资级别

/*
创建新表语句
create table job_grades
(grade_level varchar(3),
lowest_sal int,
highest_sal int);

insert into job_grades values('A', 1000, 2999);
insert into job_grades values('B', 3000, 5999);
insert into job_grades values('C', 6000, 9999);
insert into job_grades values('D', 10000, 14999);
insert into job_grades values('E', 15000, 24999);
insert into job_grades values('F', 25000, 40000);
*/
select * from job_grades;

select salary,grade_level
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal
order by grade_level desc;

# 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;

# 测试

# 一、显示员工表的最大工资，工资平均值
select max(salary),avg(salary)
from employees;

# 二、查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
select employee_id,job_id,last_name
from employees
order by department_id desc,salary asc;

# 三、查询员工表的job_id中包含a和e的,并且a在e的前面
select job_id
from employees
where job_id like "%a%e%";

/* 
四、已知表student,里面有id（学号），name，gradeId（年级编号）
	已知表grade，里面有id（年级编号），name（年级名）
	已知表result,里面有id，score，studentNo（学号）
	要求查询姓名、年级名、成绩
*/

select s.name,g.name,r.score
from student s,grade g,result r
where s.gradeId=g.id
and s.id=r.studentNo;


# 五、显示当前日期，以及去前后空格，截取字符串的函数
select now();
select trim('  aa  ')
select substr(str,1,1)