多表查询

# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);

# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);

select * from emp;

-- 多表查询
SELECT * from emp , dept;

-- 消除无效数据
-- 查询emp 和dept 的数据, emp.dep_id = dept.did
select * FROM emp , dept WHERE emp.dep_id = dept.did;

 

 

*********

内连接查询 ：相当于查询AB交集数据

外连接查询

左外连接查询 ：相当于查询A表所有数据和交集部门数据

右外连接查询 ： 相当于查询B表所有数据和交集部分数据

子查询

 

-- 隐式内连接

SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

 

-- 隐式内连接
select * FROM emp , dept WHERE emp.dep_id = dept.did;

-- 查询 emp的name, 和gender, dept表的dname
select emp.name,emp.gender,dept.dname FROM emp , dept WHERE emp.dep_id = dept.did;

-- 给表起别名
select t1.name,t1.gender,t2.dname FROM emp as t1, dept as t2 WHERE t1.dep_id = t2.did;

-- 显示内连接
SELECT * FROM emp inner join dept on emp.dep_id = dept.did;
-- inner 可以省略

-- 左外连接
-- 查询emp表所有数据和对应的部门信息
SELECT * FROM emp LEFT join dept ON emp.dep_id = dept.did;

-- 右外连接
-- 查询dept表所有数据和对应的员工信息
SELECT * FROM emp RIGHT join dept ON emp.dep_id = dept.did;

SELECT * FROM dept LEFT join emp ON emp.dep_id = dept.did;

 

**********

子查询 : 

查询中嵌套查询，称嵌套查询为子查询。

入门:

-- 查询工资高于猪八戒的员工信息
select * FROM emp;
-- 1.查询猪八戒的工资
select salary FROM emp WHERE name ='猪八戒';
-- 2.查询工资高于猪八戒的员工信息
select * FROM emp WHERE salary > 3600;

select * FROM emp WHERE salary > (select salary FROM emp WHERE name ='猪八戒'); -- 子查询

 

子查询根据查询结果不同，作用不同

子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断

子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断

子查询语句结果是多行多列，子查询语句作为虚拟表

 

**********

多表查询

-- 查询工资高于猪八戒的员工信息
select * FROM emp;
-- 1.查询猪八戒的工资
select salary FROM emp WHERE name ='猪八戒';
-- 2.查询工资高于猪八戒的员工信息
select * FROM emp WHERE salary > 3600;

select * FROM emp WHERE salary > (select salary FROM emp WHERE name ='猪八戒'); -- 子查询

 

-- 查询 财务部 和 市场部 所有的员工信息
SELECT did FROM dept where dname = '财务部' or dname = '市场部';

SELECT * FROM emp WHERE dep_id in(SELECT did FROM dept where dname = '财务部' or dname = '市场部');

-- 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息
-- 查询'2011-11-11'之后入职的员工
SELECT * FROM emp,dept WHERE emp.join_date > '2011-11-11' AND emp.dep_id = dept.did;

-- 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息
-- 查询'2011-11-11'之后入职的员工
SELECT * FROM emp WHERE emp.join_date > '2011-11-11';

select * FROM emp , dept WHERE emp.dep_id = dept.did;

select * FROM (SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t1 , dept WHERE t1.dep_id = dept.did;