mysql 7天平均值_学习记录-第十三天-1（李玉婷MySQL基础 第7天）

#连接查询-1

---

/*
连接查询
含义：多表查询，当查询的字段来自多个表

笛卡尔乘积
发生原因：没有有效的连接条件
添加有效的连接条件的分类：
按年代分类：
SQL92标准 仅支持内连接
SQL99标准（推荐）支持内连接+外连接（左外+右外）+交叉连接

按功能分类：
内连接：
	等值连接
	非等值连接
	自连接
外连接：
	左外连接
	右外连接
交叉连接				
*/

SELECT * FROM beauty;
SELECT* FROM boys;

SELECT NAME,boyname FROM boys,beauty;
/*
12*4 笛卡尔集错误-笛卡尔乘积
发生原因：没有有效的连接条件，
select count(*) from beauty;
输出12行
select count(*) from boys;
输出4行

最终结果：48行

*/

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

#SQL92标准
#1/等值连接
#查询男神名对应的女神名

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

#查询员工名及对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#2/为表起别名-提高语句的简洁度，区分多个重名的字段

#查询员工名/工种号/工种名
SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.job_id=jobs.job_id;

SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j #顺序可变
WHERE e.job_id=j.job_id; #交集部分
#起了别名，不认识原始表名
#顺序
select                5
from                  1
where                 2
group by              3
having                4
order by              6

#3/查询的两个表顺序可变
#4/可做筛选
#有奖金的员工名/部门名
SELECT last_name,department_name
FROM employees AS e ,departments AS d
WHERE commission_pct IS NOT NULL
AND e.department_id=d.department_id;

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

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

#5/添加分组
#查询每个城市的部门个数
SELECT COUNT(department_id),city
FROM departments AS d,locations AS l
WHERE d.location_id=l.location_id
GROUP BY city;

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

#修改
SELECT department_name,e.manager_id,MIN(salary)
FROM departments AS d,employees AS e
WHERE commission_pct IS NOT NULL
AND d.manager_id=e.manager_id
GROUP BY department_name,e.manager_id;

#6/添加排序
#查询每个工种的工种名和员工的个数，并且按照员工个数排序
SELECT job_title,COUNT(*)
FROM jobs AS j,employees AS e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7/实现3表连接
#查询员工名/部门名/所在城市
SELECT last_name,department_name,city
FROM employees AS e,departments AS d,locations AS l
WHERE e.department_id=d.department_id AND d.location_id=l.location_id;

SELECT last_name,department_name,city
FROM employees AS e,departments AS d,locations AS l
WHERE e.department_id=d.department_id 
AND d.location_id=l.location_id
AND city LIKE '%a%';

/*
总结
等值连接
1/多表连接的结果为多表的交集
2/n表连接，至少需要N-1个连接条件
3/多表的顺序没有要求
4/一般需要为表格起别名
5/连接可以搭配之前所有的子句使用
*/