datagrip mysql连接用户特殊符号

4.连接查询

含义：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询。

select 字段名 from 表一，表二...where 表名.连接字段1=表名.连接字段2

分类：
	sql92标准：仅仅支持内连接。
	sql99标准：支持内连接+外连接（左外连接和右外连接）+交叉连接
	
	sql99支持的较多,实现连接条件和筛选条件的分离，可读性较高
	
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接：左表和右表都不做限制，所有的记录都显示，两表不足的地方用null填充
		交叉连接

笛卡尔积现象:表1有m行数据，表2有n行数据，结果是=m*n
	发生的原因：没有有效的连接条件
	如何避免：加上有效的连接条件

查询女神名对应的男神名
select name,boyname from beauty,boys where beauty.boyfriend_id=boys.id

1.等值连接
	1、可以搭配前面所学的所有子句使用的，比如排序，分组，筛选
	2、表表等值连接的结果为两个表的交集
	3、n表连接，至少需要n-1连接条件
	4、一般需要给表起别名
	5、多表的顺序没有要求
	
--起别名
查询员工名对应的部门名
select last_name,department_name from employees emp,departments dept where emp.department_id = dept.department_id

--两个标的顺序是否可以调换
查询员工名对应的部门名
select last_name,department_name from departments dept,employees emp where emp.department_id = dept.department_id

--是否可以添加筛选条件
查询有奖金的员工名、部门名
select last_name,department_name from employees emp,departments dept where emp.department_id=dept.department_id and commission_pct is not null

--是否可以添加分组呢
查询每个城市的部门个数
select count(*) 个数,city from departments dept,locations lo where dept.location_id=lo.location_id GROUP BY city


2.非等值连接
	查询员工的工资和工资级别
select salary,grade_level from employees emp,job_grades jo where salary between jo.lowest_sal and jo.highest_sal


3.自连接
	查询员工名和上级的名称
select emp1.last_name 员工名,emp2.last_name 上级领导 from employees emp1,employees emp2 where emp1.manager_id=emp2.employee_id;

sql99语法：
	select 查询列表 form 表一 别名 [连接类型] join 表2 别名 on 连接条件 [where][group by having][order by]

	分类：
			内连接：inner
				等值连接
				非等值连接
				自连接
			外连接：
				左外连接   left
				右外连接	 right
				全外连接	 full
			交叉连接：croos

等值连接
	查询员工名，部门名
select last_name,department_name from employees emp inner join departments dept on emp.department_id=dept.department_id

查询名字中包含e的员工名和工种名
select last_name,job_title from employees emp join jobs j on emp.job_id=j.job_id where last_name like '%e%'
非等值连接
	查询员工的工资级别
select salary,grade_level from employees emp join job_grades j on emp.salary between j.lowest_sal and j.highest_sal

二：外连接
		应用场景：用于查询一个表中有，另一表中没有的记录

查询男朋友，不在男神表中的女神名
select be.*,bo.* from beauty be left JOIN boys bo on be.boyfriend_id = bo.id
select be.*,bo.* from boys bo right JOIN beauty be on be.boyfriend_id = bo.id
select be.*,bo.* from beauty be,boys bo where be.boyfriend_id = bo.id

左外连(左面的表全部显示是主表)
select bo.*,b.* from  boys bo left join beauty b on b.boyfriend_id=bo.id

全外连接
 SELECT b.*,bo.*
 FROM beauty b
 FULL JOIN boys bo
 ON b.`boyfriend_id` = bo.id;

交叉连接
select b.*,bo.* from beauty b cross join boys bo;

左外连接 left JOIN ON

查询女神表和男神表的男女朋友关系，要求女神表中所有字段都显示出来
SELECT b.*,bo.* from beauty b LEFT JOIN boys bo on b.boyfriend_id = bo.id


重点：99标准外连接，左外联，右外联

左外链接：是把左边的表作为主表，右边的表和左边的表进行匹配
右外链接：是把右边的表作为主表，左边的表和右边的表进行匹配
1.外连接查询结果是主表中的所有数据，如果从表中有和他匹配的数据，则显示匹配值
2.左外和右外交换两个表的顺序可以实现同样效果
3.全外连接为两个表的并集

• 例题

sql99语法：
	select 查询列表 form 表一 别名 [连接类型] join 表2 别名 on 连接条件 [where][group by having][order by]
使用sql99语法做：
1.查询员工名、部门名
select last_name,department_name from employees emp left join departments dept on emp.department_id=dept.department_id;

2.查询名字中包含e的员工名和工种名（添加筛选）
select last_name,j.job_id from employees emp left join jobs j on emp.job_id=j.job_id where emp.last_name like '%e%';

3.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
select count(*) 部门个数,city from departments dept join locations l on dept.location_id=l.location_id GROUP BY city HAVING count(*)>3;

4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
select department_name,count(*) 员工个数 from employees emp join departments dept on emp.department_id=dept.department_id GROUP BY department_name HAVING count(*)>3 ORDER BY count(*) desc;

5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
select last_name,department_name,j.job_id from employees emp join departments dept on emp.department_id=dept.department_id join jobs j on emp.job_id=j.job_id ORDER BY department_name;

6.查询员工的工资级别
select last_name,salary,grade_level from employees emp join job_grades on emp.salary between lowest_sal and highest_sal;

7.查询工资级别的个数>20的个数，并且按工资级别降序
select grade_level,count(*) from employees emp join job_grades on emp.salary between lowest_sal and highest_sal GROUP BY grade_level HAVING count(*)>20 ORDER BY grade_level desc;

8.查询员工的名字、上级的名字
select emp1.last_name 员工姓名,emp2.last_name 上级姓名 from employees emp1 join employees emp2 on emp1.manager_id=emp2.employee_id;

9.查询姓名中包含字符k的员工的名字、上级的名字
select emp1.last_name 员工姓名,emp2.last_name 上级姓名 from employees emp1 join employees emp2 on emp1.manager_id=emp2.employee_id where emp1.last_name like '%k%';

10.查询哪个部门没有员工
select dept.department_name,count(employee_id) from departments dept left join employees emp on dept.department_id=emp.department_id GROUP BY dept.department_name HAVING count(employee_id)=0;

sql92做：
1.查询员工名、工种号、工种名
select last_name,jobs.job_id,job_title from employees,jobs where employees.job_id=jobs.job_id;

2.查询有奖金的员工名、部门名
select last_name,department_name from employees emp,departments dept where emp.department_id=dept.department_id and emp.commission_pct is not null;

3.查询城市名中第二个字符为o的部门名和城市名
select city,department_name from departments dept, locations l where dept.location_id=l.location_id and city like '_o%';

4.查询每个城市的部门个数
select count(*),city from departments dept, locations l where dept.location_id=l.location_id GROUP BY city ;

5.查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select dept.department_name,emp.manager_id,min(salary) from employees emp, departments dept where emp.department_id=dept.department_id and emp.commission_pct is not null GROUP BY dept.department_name;

6.查询每个工种的工种名和员工的个数，并且按员工个数降序
select jobs.job_id,count(*) from jobs,employees emp where jobs.job_id=emp.job_id GROUP BY jobs.job_id ORDER BY count(*) desc;

7.查询员工名、部门名和所在的城市
select emp.last_name,dept.department_name,l.city from employees emp, departments dept, locations l where emp.department_id=dept.department_id and dept.location_id=l.location_id;

8.查询员工的工资和工资级别
select salary,grade_level from employees,job_grades where salary BETWEEN lowest_sal and highest_sal;

9.查询 员工名和上级的名称
select emp1.last_name,emp2.last_name from employees emp1,employees emp2 where emp1.manager_id=emp2.employee_id;