MySQL基础__内连接查询

连接查询

内连接 等值查询

# 连接查询 多表查询
# 查询的字段来源于多个表 就会用到连接查询
# 多表查询 查询来源于多个表的时候 就会用到连接查询
/**
笛卡尔乘积现象：表1 有m行 表2有n行 结果=m*n行
原因:没有有效的连接条件
如何避免:添加有效的连接条件 
		按年代分类 
				sql92标准: 仅仅支持内连接
				sql99标准 [推荐] : 支持内连接+外连接(左外+右外)+交叉连接
				
				按功能分类
						内连接：
									等值连接
									非等值连接
									自连接
						外连接:
									左外连接
									右外连接
									全外连接
						交叉连接				
*/
# 笛卡尔集错误情况 
SELECT name,boyName FROM beauty,boys;

SELECT count(*) FROM beauty;# 12 

SELECT count(*) FROM boys;# 4 
# 12*4 =48行

# 解决
SELECT name,boyName FROM beauty s ,boys b 
WHERE s.boyfriend_id=b.id;

#一,sql92标准
#1,等值连接
/*
① 多表等值连接的结果为多表的交集部分
② n表连接 至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 起别名
⑤ 可以搭配分组 筛选 排序
*/
# 查询女神对应男神的名字
SELECT NAME,
	boyName 
FROM
	beauty s,
	boys b 
WHERE
	s.boyfriend_id = b.id;

#案例2 查询员工名和对应的部门名
SELECT
	last_name,
	department_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id

# 查询员工名 工种号 工种名 确定job_id在那个具体的表
SELECT
	last_name,
	e.job_id,
	job_title 
FROM
	employees e,
	jobs b 
WHERE # 等值连接 相当于两个表得交集 
	e.job_id = b.job_id

# 4，可以加筛选
# 查询有奖金的员工名，部门名
SELECT
	e.last_name,
	d.department_name,
	e.commission_pct 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id 
	AND e.commission_pct IS NOT NULL;
	
#查询城市名中第二个字符为o的部门名和城市名
SELECT
	d.department_name,
	l.city 
FROM
	departments d,
	locations l 
WHERE
	d.location_id = l.location_id 
	AND l.city LIKE '_o%'#匹配第二个字符为o

#5.可以加分组
#案例1 查询每个城市的部门个数
SELECT
	count( * ),
	city 
FROM
	departments d,
	locations l 
WHERE
	l.location_id = d.location_id 
GROUP BY
	city

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

# 可以加排序 
#查询每个工种的工种名和员工个数 并且按照员工个数降序

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

# 实现三表连接
# 案例：查询员工名 部门名和所在的城市
SELECT
	e.last_name,
	d.department_name,
	l.city 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.department_id = d.department_id 
	AND d.location_id = l.location_id
	and city LIKE 's%'; # 以s开头

非等值连接

# 非等值连接 可以加排序 筛选

# 查询员工工资和工资级别
# 用salary 去比较 
SELECT
	e.salary,
	g.grade_level
FROM
	employees e,
	job_grades g 
WHERE
	e.salary BETWEEN g.lowest_sal 
	AND g.highest_sal 
	AND g.grade_level = 'A'

自连接

# 自连接 自己连接本身
#查询员工名和上级的名城
# e2 作为一个管理表 e1 作为一个员工表
SELECT
	e1.last_name AS 员工名, e1.employee_id as 员工编号, 
	e2.last_name AS 上级名 ,e2.employee_id as 领导编号
FROM
	employees e1,
	employees e2 
WHERE
	e1.manager_id = e2.employee_id


# 练习
# 1.显示所有员工的姓名，部门号和部门名称。
SELECT
	e.last_name,
	d.department_id,
	d.department_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id

# 查询90号部门员工的job_id 和90 号部门的location_id  内连接 取交集
SELECT
	e.job_id,
	l.location_id,
	e.last_name,
	e.department_id 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.department_id = d.department_id 
	AND d.location_id = l.location_id 
	AND e.department_id = 90

# 3. 选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT
	last_name,
	department_name,
	l.location_id,
	city 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.department_id = d.department_id 
	AND d.location_id = l.location_id 
	AND commission_pct IS NOT NULL
	
# 4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT
	last_name,
	job_id,
	d.department_id,
	d.department_name,
	city 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.department_id = d.department_id 
	AND d.location_id = l.location_id 
	AND city = 'Toronto'
	
# 5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT
	job_title,
	department_name,
	MIN( salary ) min 
FROM
	employees e,
	departments d,
	jobs b 
WHERE
	e.department_id = d.department_id 
	AND e.job_id = b.job_id
	GROUP BY job_title,department_name

# 查询每个国家下的部门个数大于2的国家编号 
SELECT
	COUNT( * ) num,
	country_id 
FROM
	departments d,
	locations l 
WHERE
	d.location_id = l.location_id 
GROUP BY
	country_id 
HAVING
	num >2

# 自连接 
/**
7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp#  manager Mgr#
kochhar   101   king    100
**/
SELECT
	emp.last_name AS em,
	emp.employee_id AS Emp,
	mgr.last_name AS manager,
	mgr.employee_id AS Mgr 
	
FROM
	employees emp,
	employees mgr 
WHERE
	emp.manager_id = mgr.employee_id 
	AND emp.last_name = 'kochhar'
	
# 一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT
	`name`,
	boyName
FROM
	beauty g
	LEFT JOIN boys b ON g.boyfriend_id = b.id 
WHERE
	g.id >3

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

# 3.查询部门名为SAL或IT的员工信息
SELECT
	department_name,
	last_name 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id 
	AND department_name IN ( 'SAL', 'IT' )

SELECT RAND()

交叉连接

# 交叉连接 使用99乘法 达到笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b 
CROSS JOIN boys bo