MySQL日记2

/*
连接查询
又称为：多表查询 ，当查询的字段来自于多个表时，需要使用


分类:
按年代分类:
sq192标准:仅仅文持内连接
sq199标准[推荐] :支持内连接+外连接( 左外和右外) +交叉连接

按功能分类:
	内连接:
		等值连接
		非等值连接
		自连接
	外连接: 
		左外连接
		右外连接
		全外连接
	交叉连接.
*/
SELECT * FROM beauty;
SELECT * FROM boys;

SELECT NAME,boyName FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;

#sql92标准

#等值连接

#案例1:查询女神名和对应的男神名
SELECT NAME,boyName FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;



#查询员工名和对应的部门名

SELECT * FROM departments ;

SELECT * FROM employees;

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id= departments.department_id;

/*
为表起别名
1.提高语句的简洁度

注意：
起别名之后就不能再用原始名字
*/
#查询员工名，工种号，工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id=j.job_id;



#可以加筛选

#案例 查询有奖金的员工名,部门名

SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id AND commission_pct IS NOT NULL;


#案例2.查询出城中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM locations l,departments d
WHERE city LIKE'_o%' AND l.location_id=d.location_id;



# 加分组

#查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM departments d , locations l
GROUP BY city

#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT `department_name`,e.manager_id,MIN(salary)
FROM departments d,employees e
WHERE commission_pct IS NOT NULL AND d.department_id=e.department_id
GROUP BY department_name;



#加排序
#案例:查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#实现三表连接

#案例:查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,locations l,departments d
WHERE d.department_id=e.department_id AND d.location_id=l.location_id;




/*
非等值连接
*/
SELECT * FROM job_grades;

#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal;

#自连接:一个表需要多次查询

#查询员工名和上级的名称

SELECT e.employee_id 员工ID,e.last_name 员工名,m.employee_id 领导ID,m.last_name 领导名
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

#查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.department_id=d.department_id AND e.department_id=90;

/*
sql99语法

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

连接类型

内连接: inner
	等值连接
	非等值连接
	自连接
外连接: 
	左外连接 left [outer]
	右外连接 rigth[outer]
	全外连接 full [outer]
交叉连接. cross
*/

#---->内连接
/*
特点:
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sq192语法中的等值连接效果是一样的，都是查询多表的交集

*/

# 等值连接

#查询员工名，部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;



#案例2.查询名字中包含e的员工名和工种名( 添加筛选)

SELECT last_name ,job_title 
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE last_name LIKE '%e%' AND job_title LIKE '%e%' ;

#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序(添加排序)
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY e.department_id
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;


#非等值连接

#查询员工的工资级别
SELECT salary,grade_level
FROM employees
INNER JOIN job_grades
ON salary BETWEEN lowest_sal AND highest_sal;


#查询工资级别的个数>20的个数，并按照工资级别降序
SELECT COUNT(*) ,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;


#自连接
#查询员工的名字、上级的名字.
SELECT e.last_name 员工,m.last_name 领导
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id;




/*
外连接

用于查询一个表中有，另一个表没有

特点:
1、外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示nul1
	外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
   右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
*/

*/
SELECT * FROM beauty;


#查询男朋友不在男表中的女名
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;