1.了解什么是笛卡尔积（或交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

笛卡尔积的错误会在下面条件下产生：

• 省略多个表的连接条件（或关联条件）
• 连接条件（或关联条件）无效
• 所有表中的所有行互相连接

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
加入连接条件后，查询语法：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

在 WHERE子句中写入连接条件。
正确写法：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在表中有相同列时，在列名之前加上表名前缀。

2.多表连接分类

分类1：等值连接 vs 非等值连接
等值连接

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

拓展2：区分重复的列名

• 多个表中有相同列时，必须在列名之前加上表名前缀。
• 在不同表中具有相同列名的列可以用 表名 加以区分。

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

拓展3：表的别名

• 使用别名可以简化查询。
• 列名前使用表名前缀可以提高查询效率。

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

阿里开发规范 ：
【 强制 】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
说明 ：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
正例 ：select t1.name from table_first as t1 , table_second as t2 where
t1.id=t2.id;
反例 ：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052 异常：Column ‘name’ in field list is ambiguous。

总结：连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

3.非等值连接

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

4.UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并
时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION
ALL关键字分隔。
语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符
UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

真实案例UNION ALL使用
目的：行转列想到case when 列转行首先想到UNION ALL 。

SELECT DISTINCT
	it.STANDARD_ID,
	-- it.STANDARD_REQUIRE_FH 空值处理
	CONCAT(IF(it.STANDARD_REQUIRE_FH IS NULL,'',it.STANDARD_REQUIRE_FH),it.STANDARD_REQUIRE) AS STANDARD_REQUIREMENT,-- 标准要求
	it.ASSAY_GIST,-- 检验依据
	it.MAT_ITEMID,
	it.ITEM_ORDER,
	ba.FID_ID,
	-- it.UNIT为空值处理
	CONCAT(ba.ASS_ITEMU,'(',IF(it.UNIT IS NULL,'',it.UNIT),')') AS ASS_ITEM_UNIT,-- 检验项目 
	ba.ID,
	dat.itemname,-- 实测项目
	dat.Itemvalue,-- 实测值
	dat.PRO_CODE
FROM
	qims_cem_standard_item it JOIN qims_assay_item_base ba ON it.MAT_ITEMID = ba.ID
	JOIN qims_cem_standard st ON it.STANDARD_ID = st.ID 
	JOIN qims_cem_data da ON st.ID=da.STANDARD_ID
	JOIN (
SELECT datacem.*  FROM

(SELECT 
PRO_CODE,
'adx' as itemname,-- 安定性(沸煮法)
 adx as itemvalue 
FROM (SELECT PRO_CODE,adx FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'adx_jsf' as itemname,-- 安定性(浸水法)
 adx_jsf as itemvalue 
FROM (SELECT PRO_CODE,adx_jsf FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'adx_sbf' as itemname,-- 安定性(试饼法)
 adx_sbf as itemvalue 
FROM (SELECT PRO_CODE,adx_sbf FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'adx_yzf' as itemname,-- 安定性(压蒸法)
 adx_yzf as itemvalue 
FROM (SELECT PRO_CODE,adx_yzf FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'bbmj' as itemname,-- 比表面积
 bbmj as itemvalue 
FROM (SELECT PRO_CODE,bbmj FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'bsl' as itemname,-- 保水率
 bsl as itemvalue 
FROM (SELECT PRO_CODE,bsl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'ky28' as itemname,-- 28天抗压强度
 ky28  as itemvalue 
FROM (SELECT PRO_CODE,CONCAT(ky28_1,',',ky28_1,',',ky28_2,',',ky28_3,',',ky28_4,',',ky28_5,',',ky28_6,' 平均值:',ky28) ky28 
FROM qims_cem_data )qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'ky3' as itemname,-- 3天抗压强度
 ky3  as itemvalue 
FROM (SELECT PRO_CODE,CONCAT(ky3_1,',',ky3_1,',',ky3_2,',',ky3_3,',',ky3_4,',',ky3_5,',',ky3_6,' 平均值:',ky3) ky3 
FROM qims_cem_data )qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'ky7' as itemname,-- 7天抗压强度
 ky7  as itemvalue 
FROM (SELECT PRO_CODE,CONCAT(ky7_1,',',ky7_1,',',ky7_2,',',ky7_3,',',ky7_4,',',ky7_5,',',ky7_6,' 平均值:',ky7) ky7 
FROM qims_cem_data )qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'kz28' as itemname,-- 28天抗折强度
kz28 as itemvalue 
FROM (SELECT PRO_CODE,CONCAT(kz28_1,', ',kz28_2,', ',kz28_3,' 平均值:',kz28) kz28 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'kz3' as itemname,-- 3天抗折强度
kz3 as itemvalue 
FROM (SELECT PRO_CODE,CONCAT(kz3_1,', ',kz3_2,', ',kz3_3,' 平均值:',kz3) kz3 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'kz7' as itemname,-- 7天抗折强度
kz7 as itemvalue 
FROM (SELECT PRO_CODE,CONCAT(kz7_1,', ',kz7_2,', ',kz7_3,' 平均值:',kz7) kz7 FROM qims_cem_data )
 qims_cem_databack

UNION ALL
SELECT 
PRO_CODE,
'bzcd' as itemname,-- 标准稠度
 bzcd as itemvalue 
FROM (SELECT PRO_CODE,bzcd FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'c2s' as itemname,-- 硅酸二钙(C2S)
 c2s as itemvalue 
FROM (SELECT PRO_CODE,c2s FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'c3a' as itemname,-- 铝酸三钙(C3A)
 c3a as itemvalue 
FROM (SELECT PRO_CODE,c3a FROM qims_cem_data )
 qims_cem_databack
UNION ALL
-- SELECT 
-- PRO_CODE,
-- 'c3a_c2s' as itemname,-- C₃S+C₂S
--  c3a_c2s as itemvalue 
-- FROM (SELECT PRO_CODE,c3a_c2s FROM qims_cem_data )
--  qims_cem_databack
-- UNION ALL
SELECT 
PRO_CODE,
'c4af' as itemname,-- 铁铝酸四钙(C₄AF)
 c4af as itemvalue 
FROM (SELECT PRO_CODE,c4af FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cao' as itemname,-- 氧化钙
 cao as itemvalue 
FROM (SELECT PRO_CODE,cao FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cao_sio2' as itemname,-- CaO/SiO₂
 cao_sio2 as itemvalue 
FROM (SELECT PRO_CODE,cao_sio2 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cl_' as itemname,-- 氯离子
 cl_ as itemvalue 
FROM (SELECT PRO_CODE,cl_ FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cnsjb' as itemname,-- 初凝时间比
 cnsjb as itemvalue 
FROM (SELECT PRO_CODE,cnsjb FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cnys' as itemname,-- 初凝时间
 cnys as itemvalue 
FROM (SELECT PRO_CODE,cnys FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cr6_' as itemname,-- 水溶性铬(VI)
 cr6_ as itemvalue 
FROM (SELECT PRO_CODE,cr6_ FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cyky_38' as itemname,-- 38℃8小时常压抗压强度
 cyky_38 as itemvalue 
FROM (SELECT PRO_CODE,cyky_38 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'cyky_60' as itemname,-- 60℃8小时常压抗压强度
 cyky_60 as itemvalue 
FROM (SELECT PRO_CODE,cyky_60 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'fmhcjl' as itemname,-- 粉煤灰掺加量
 fmhcjl as itemvalue 
FROM (SELECT PRO_CODE,fmhcjl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'f_cao' as itemname,-- 游离氧化钙
 f_cao as itemvalue 
FROM (SELECT PRO_CODE,f_cao FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'gsl28' as itemname,-- 28d干缩率
 gsl28 as itemvalue 
FROM (SELECT PRO_CODE,gsl28 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'hhccl' as itemname,-- 混合材掺量
 hhccl as itemvalue 
FROM (SELECT PRO_CODE,hhccl FROM qims_cem_data )
 qims_cem_databack
 UNION ALL
SELECT 
PRO_CODE,
'hhcpzjcl' as itemname,-- 混合材品种及掺量
 hhcpzjcl as itemvalue 
FROM (SELECT PRO_CODE,hhcpzjcl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'hsl' as itemname,-- 含水量
 hsl as itemvalue 
FROM (SELECT PRO_CODE,hsl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'hx_28' as itemname,-- 28天活性指数
 hx_28 as itemvalue 
FROM (SELECT PRO_CODE,hx_28 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'hx_7' as itemname,-- 7天活性指数
 hx_7 as itemvalue 
FROM (SELECT PRO_CODE,hx_7 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'ir' as itemname,-- 不溶物
 ir as itemvalue 
FROM (SELECT PRO_CODE,ir FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'jbqcd_15_30' as itemname,-- 15-30min搅拌期稠度
 jbqcd_15_30 as itemvalue 
FROM (SELECT PRO_CODE,jbqcd_15_30 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'jjkzd_60' as itemname,-- 净浆流动度(60min)
 jjkzd_60 as itemvalue 
FROM (SELECT PRO_CODE,jjkzd_60 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'jjkzd_CS' as itemname,-- 净浆流动度(初始)
 jjkzd_CS as itemvalue 
FROM (SELECT PRO_CODE,jjkzd_CS FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'k2o' as itemname,-- 氧化钾
 k2o as itemvalue 
FROM (SELECT PRO_CODE,k2o FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'kaidu' as itemname,-- 安定性(雷氏夹法)
 kaidu as itemvalue 
FROM (SELECT PRO_CODE,kaidu FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'kzcjl' as itemname,-- 矿渣掺加量
 kzcjl as itemvalue 
FROM (SELECT PRO_CODE,kzcjl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'lddb' as itemname,-- 流动度比
 lddb as itemvalue 
FROM (SELECT PRO_CODE,lddb FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'loss' as itemname,-- 烧失量
 loss as itemvalue 
FROM (SELECT PRO_CODE,loss FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'md' as itemname,-- 密度
 md as itemvalue 
FROM (SELECT PRO_CODE,md FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'mgo' as itemname,-- 氧化镁
 mgo as itemvalue 
FROM (SELECT PRO_CODE,mgo FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'mhl28' as itemname,-- 28d磨耗量
 mhl28 as itemvalue 
FROM (SELECT PRO_CODE,mhl28 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'nao2' as itemname,-- 氧化钠
 nao2 as itemvalue 
FROM (SELECT PRO_CODE,nao2 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'pzl_14' as itemname,-- 14d线膨胀率
 pzl_14 as itemvalue 
FROM (SELECT PRO_CODE,pzl_14 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'pzl_28' as itemname,-- 28d线膨胀率
 pzl_28 as itemvalue 
FROM (SELECT PRO_CODE,pzl_28 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'pzl_7' as itemname,-- 7d线膨胀率
 pzl_7 as itemvalue 
FROM (SELECT PRO_CODE,pzl_7 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'r2o' as itemname,-- 碱含量
 r2o as itemvalue 
FROM (SELECT PRO_CODE,r2o FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'sgpzjcl' as itemname,-- 石膏品种及掺量
 sgpzjcl as itemvalue 
FROM (SELECT PRO_CODE,sgpzjcl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'shb' as itemname,-- 水灰比
 shb as itemvalue 
FROM (SELECT PRO_CODE,shb FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'shr_3' as itemname,-- 3天水化热
 shr_3 as itemvalue 
FROM (SELECT PRO_CODE,shr_3 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'shr_7' as itemname,-- 7天水化热
 shr_7 as itemvalue 
FROM (SELECT PRO_CODE,shr_7 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'shscjl' as itemname,-- 石灰石掺加量
 shscjl as itemvalue 
FROM (SELECT PRO_CODE,shscjl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'sl_cao' as itemname,-- 熟料中游离氧化钙
 sl_cao as itemvalue 
FROM (SELECT PRO_CODE,sl_cao FROM qims_cem_data )
 qims_cem_databack
UNION ALL 
SELECT 
PRO_CODE,
'snjsldd' as itemname,-- 胶砂流动度
 snjsldd as itemvalue 
FROM (SELECT PRO_CODE,snjsldd FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'so3' as itemname,-- 三氧化硫
 so3 as itemvalue 
FROM (SELECT PRO_CODE,so3 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'so3_xm' as itemname,-- 水泥中三氧化硫
 so3_xm as itemvalue 
FROM (SELECT PRO_CODE,so3_xm FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'water' as itemname,-- 水分
 water as itemvalue 
FROM (SELECT PRO_CODE,water FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'wlpz_06' as itemname,-- 物料配比06
 wlpz_06 as itemvalue 
FROM (SELECT PRO_CODE,wlpz_06 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'wlpz_07' as itemname,-- 物料配比07
 wlpz_07 as itemvalue 
FROM (SELECT PRO_CODE,wlpz_07 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'xd45' as itemname,-- 45μm筛余
 xd45 as itemvalue 
FROM (SELECT PRO_CODE,xd45 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'xd80' as itemname,-- 80μm筛余
 xd80 as itemvalue 
FROM (SELECT PRO_CODE,xd80 FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'yly' as itemname,-- 游离液
 yly as itemvalue 
FROM (SELECT PRO_CODE,yly FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'zmjpzjcl' as itemname,-- 助磨剂品种及掺量
 zmjpzjcl as itemvalue 
FROM (SELECT PRO_CODE,zmjpzjcl FROM qims_cem_data )
 qims_cem_databack
UNION ALL
SELECT 
PRO_CODE,
'znys' as itemname,-- 终凝时间
 znys as itemvalue 
FROM (SELECT PRO_CODE,znys FROM qims_cem_data )
 qims_cem_databack) datacem

) dat ON dat.PRO_CODE=da.PRO_CODE
	
	WHERE 
  ba.FID_ID=dat.itemname
	ORDER BY
	it.ITEM_ORDER,
	ass_item_unit,
  it.STANDARD_ID

7.MySQL的七种join图解

描述：连个表共有的记录

select * from tb1_emp a inner join tb1_dept b on a.deptId = b.id;

二、左连接
描述：求A表所有记录以及AB表共有的记录(B表没有的字段补null)

select * from tb1_emp a left join tb1_dept b on a.deptId = b.id;

三、右连接
描述： 求B表所有记录以及AB表共有的记录(A表没有的字段补null)

select * from tb1_emp a right join tb1_dept b on a.deptId = b.id;

四、左外连接
描述：从AB表中求A表独有的记录

select * from tb1_emp a left join tb1_dept b on a.deptId = b.id where b.id is null;

五、右外连接
描述：从AB表中求B表中独有的记录

select * from tb1_emp a right join tb1_dept b on a.deptId = b.id where a.deptId is null;

1
六、全连接
描述：查询AB表中所有的记录

select * from tb1_emp a left join tb1_dept b on a.deptId = b.id
union
select * from tb1_emp a right join tb1_dept b on a.deptId = b.id

七、全外连接
描述：查询A表独有的记录和B表独有的记录

select * from tb1_emp a left join tb1_dept b on a.deptId = b.id where b.id is null
union
select * from tb1_emp a right join tb1_dept b on a.deptId = b.id where a.deptId is null;

8.课后习题

题目：

# 1.显示所有员工的姓名，部门号和部门名称。
# 2.查询90号部门员工的job_id和90号部门的location_id
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
# 7.查询哪些部门没有员工
# 8. 查询哪个城市没有部门
# 9. 查询部门名为 Sales 或 IT 的员工信息

1.显示所有员工的姓名，部门号和部门名称

SELECT last_name, e.department_id, department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

2.查询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;

或者

SELECT job_id, location_id
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;

3.选择所有有奖金的员工的 last_name , department_name , location_id ,
city

SELECT last_name , department_name , d.location_id , city
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;

4.选择city在Toronto工作的员工的 last_name , job_id , department_id ,
department_name

SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';

或

SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';

5．查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所
在部门的部门名称为’Executive’

SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'

6. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp# manager Mgr#
kochhar 101 king 100

SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

7. 查询哪些部门没有员工

 #方式1：
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON e.department_id = d.`department_id`
WHERE e.department_id IS NULL
#方式2：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.`department_id` = d.`department_id`
)

8. 查询哪个城市没有部门

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL

9. 查询哪个城市没有部门

SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');

多表查询-2

储备：建表操作：
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1. 所有有门派的人员信息
（ A、B两表共有）
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);


【题目】
#1.所有有门派的人员信息
（ A、B两表共有）
#2.列出所有用户，并显示其机构信息
（A的全集）
#3.列出所有门派
（B的全集）
#4.所有不入门派的人员
（A的独有）
#5.所有没人入的门派
（B的独有）
#6.列出所有人员和机构的对照关系
(AB全有)
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
#7.列出所有没入派的人员和没人入的门派
（A的独有+B的独有）

1. 所有有门派的人员信息
   （ A、B两表共有）

select *
from t_emp a inner join t_dept b
on a.deptId = b.id;

2. 列出所有用户，并显示其机构信息
   （A的全集）

select *
from t_emp a left join t_dept b
on a.deptId = b.id;

3. 列出所有门派
   （B的全集）

select *
from t_dept b;

4. 所有不入门派的人员
   （A的独有）

select *
from t_emp a left join t_dept b
on a.deptId = b.id
where b.id is null;

5. 所有没人入的门派
   （B的独有）
   select *
   from t_dept b left join t_emp a
   on a.deptId = b.id
   where a.deptId is null;
6. 列出所有人员和机构的对照关系
   (AB全有)

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT *
FROM t_emp A LEFT JOIN t_dept B
ON A.deptId = B.id
UNION
SELECT *
FROM t_emp A RIGHT JOIN t_dept B
ON A.deptId = B.id

7. 列出所有没入派的人员和没人入的门派
   （A的独有+B的独有）

SELECT *
FROM t_emp A LEFT JOIN t_dept B
ON A.deptId = B.id
WHERE B.`id` IS NULL
UNION
SELECT *
FROM t_emp A RIGHT JOIN t_dept B
ON A.deptId = B.id
WHERE A.`deptId` IS NULL;