create database if not exists mydb3;
use mydb3;
-- 創(chuàng)建部門表
create table if not exists dept3 (
    deptno varchar(20) primary key, -- 部門號
    name varchar(20) -- 部門名字
);
-- 創(chuàng)建員工表
create table if not exists emp3 (
    eid varchar(20) primary key, -- 員工編號
    ename varchar(20), -- 員工名字
    age int, -- 員工年齡
    dept_id varchar(20) -- 員工所屬部門
);
-- 給dept3表添加數(shù)據(jù)
insert into dept3 values('1001','研發(fā)部');
insert into dept3 values('1002','銷售部');
insert into dept3 values('1003','財務(wù)部');
insert into dept3 values('1004','人事部');
-- 給emp3表添加數(shù)據(jù)
insert into emp3 values('1','喬峰',20, '1001');
insert into emp3 values('2','段譽',21, '1001');
insert into emp3 values('3','虛竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','掃地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鳩摩智',50, '1002');
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

select * from 表1,表2,表3,...;

select * from emp3,dept3;

-- 隱式內(nèi)連接
select * from A,B where 條件;
-- 顯式內(nèi)連接
select * from A inner join B on 條件;

-- 查詢每個部門的所屬員工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
-- 或
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
-- 查詢研發(fā)部門的所屬員工
select
    dept3.name,emp3.ename
from
    dept3
inner join
    emp3 
on 
    dept3.deptno = emp3.dept_id 
where 
    dept3.name = '研發(fā)部';
-- 查詢研發(fā)部和銷售部的所屬員工
select 
    dept3.name,emp3.ename
from
    dept3
inner join
    emp3
on
    dept3.deptno = emp3.dept_id
where
    dept3.name = '研發(fā)部' || dept3.name = '銷售部';
-- 查詢每個部門的員工數(shù)，并升序排序
select
    dept3.name,count(emp3.ename)
from
    dept3
inner join
    emp3
on
    dept3.deptno = emp3.dept_id
group by 
    dept3.name
order by 
    count(ename) asc;
-- 查詢?nèi)藬?shù)大于等于3的部門，并按照人數(shù)降序排序
select 
    dept3.name,count(emp3.ename) as total_number
from
    dept3
inner join
    emp3
on
    dept3.deptno = emp3.dept_id
group by
    dept3.name
having
    total_number > 3
order by
    total_number desc;

-- 左外連接
select * from 表1 
    left outer join 表2 on 條件1
    left outer join 表3 on 條件2
    left outer join 表4 on 條件3;
-- 右外連接
select * from 表1 right outer join 表2 on 條件;
-- 滿外連接
select * from 表1 full outer join 表2 on 條件;

--外連接查詢
use mydb3;
-— 查詢哪些部門有員工，哪些部門沒有員工
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查詢哪些員工有對應(yīng)的部門，哪些沒有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union關(guān)鍵字實現(xiàn)左外連接和右外連接的并集
-- union 查詢結(jié)果上下拼接；去重
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- union all 查詢結(jié)果上下拼接；不去重
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union all
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

-- 查詢年齡最大的員工信息，顯示信息包含員工號、員工名字，員工年齡
SELECT
	* 
FROM
	emp3 
WHERE
	age = (SELECT max(age) FROM emp3);    -- 單行單列，輸出單個值
-- 查詢研發(fā)部和銷售部的員工信息，包含員工號、員工名字
SELECT -- 內(nèi)連接
	* 
FROM
	dept3
	INNER JOIN emp3 ON dept3.deptno = emp3.dept_id 
	AND NAME = '研發(fā)部' 
	OR NAME = '銷售部';
-- 或
SELECT
	* 
FROM
	emp3 
WHERE
	dept_id IN (
		SELECT  -- 多行單列，輸出了多個值
			deptno 
		FROM
			dept3 
		WHERE
	(NAME = '研發(fā)部' OR NAME = '銷售部'));
--查詢研發(fā)部20歲以下的員工信息，包括員工號、員工名字，部門名字
-- 方式一：關(guān)聯(lián)查詢
SELECT
	* 
FROM
	emp3
	INNER JOIN dept3 ON emp3.dept_id = dept3.deptno 
WHERE
	age < 20 
	AND NAME = '研發(fā)部';
-- 或
-- 方式二：子查詢
SELECT
* 
FROM
	emp3 
WHERE
	age < 20 
	AND dept_id IN (SELECT deptno FROM dept3 WHERE NAME = '研發(fā)部');    -- 單行多列 / 多行多列

-- all 語法格式
select 
    ...
from
    ...
where
    c > all(查詢語句)
-- 等價于
select 
    ...
from
    ...
where
    c > 結(jié)果1 and c > 結(jié)果2 and c > 結(jié)果3