Mysql学习——多表查询

# 没有条件——笛卡儿积
SELECT NAME,boyName
FROM boys,beauty;

## 等值连接
# tips:
# 	一般需要起别名
#	顺序没有要求
#	等值连接获得的是两个表的交集部分


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

# 为表起别名
# tips: 为表起了别名以后就不能再使用原名字了
SELECT last_name,e.job_id,job_title
FROM employees AS e,
     jobs AS j
WHERE e.`job_id`=j.`job_id`;

# 可以分组嘛？查询每个城市的部门个数
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 commission_pct IS NOT NULL
GROUP BY department_name,d.`department_id`

# 查询每个工种的公众名和员工个数，并按工种个数排序
SELECT job_title,COUNT(*)
FROM employees e,jobs AS j
WHERE e.`job_id`= j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC

## 非等值连接
# 指若干个表之间，同通过不等关系连接在一起

## 自连接
# 指一个表自身若干个字段间相等进行的查询


/*
	Sql 99 语法
	
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名
	on 连接条件
	【where 语句】
	【group by 语句】
	【having 语句】
	【order by 语句】
	

连接类型：
	内链接——【inner】
	外联结
		左外——left
		右外——right
		


*/

# 等值连接
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.job_id
WHERE last_name LIKE "s%";

# 分组外加筛选
# 查询部门个数大于3的城市名及其部门个数
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

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

## 外连接：一般用于查询，一个表中有，另一个表中没有
# tips: 如果从表中有和它匹配的，则显示匹配的值
#	如果从表中没有和他匹配的，则显示null
#	外连接查询结果=内连接结果+主表中有而从表中没有的记录
# 	left join : 左边的是主表
#	right join：右边的是主表

# 查询没有男朋友的女神，左外连接
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

# 查询没有女票的男神，右外连接
SELECT b.name,bo.*
FROM beauty b
RIGHT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`

# 全外连接=内链接 并 左外连接 并 右外连接，mysql不支持，orical支持
SELECT b.*,bo.*
FROM beauty b
FULL JOIN boys bo
ON  b.boyfriend_id=bo.id;

# 交叉连接——笛卡儿积
SELECT b.`name`,bo.`boyName`
FROM boys bo
CROSS JOIN beauty b;


# 查询哪个城市没有部门
SELECT city
FROM locations l
LEFT OUTER JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL

# 查询部门名为SAL或IT的员工信息
SELECT d.`department_name`,d.`department_id`, e.*
FROM departments d
LEFT JOIN employees e
ON d.`department_id`= e.department_id
WHERE d.`department_name` IN("SAL","IT")