MySql(二)联表查询、子查询、窗口函数

联表查询

UNION-纵向连接

 Union all 的话就是全保留，Distinct的话就是去重

 实例：将employee 和 employee_new 表拼接成一张大表

SELECT * FROM employee
UNION ALL
SELECT * FROM employee_new


SELECT * FROM employee WHERE sal > 10000
UNION ALL
SELECT * FROM employee_new WHERE deptno IN (30)

 union distinct 去重

只有两行数据完全一致才会去重。

SELECT * FROM employee
UNION DISTINCT
SELECT * FROM employee_new

横向链接 

inner join 两个表的交集 

 实例：将employee_new 和department 连表，只显示有相同deptno的数据

SELECT
* 
FROM
employee_new 
JOIN
department
on
employee_new.deptno = department.deptno


-inner可写可不写
SELECT
* 
FROM
employee_new 
INNER JOIN
department
on
employee_new.deptno = department.deptno

SELECT
* 
FROM
employee_new AS e 
JOIN
department AS f
on
e.deptno = f.deptno

 left join 以左表为准

 实例：将employee_new 和department 连表，以左表为准

SELECT