MySQL之多表查询

        Select 
          列名列表
        From
          表名列表
        Where …

CREATE TABLE dept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUE('开发部'),('市场部'),('财务部');

CREATE TABLE tmp(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),--性别
  salary DOUBLE,--工资
  join_date DATE,--入职日期
  dept_id INT,
  FOREIGN KEY(dept_id) REFERENCES dept(id) --外键，关联部门表（部门表的主键） 
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

-- 第一种写法
SELECT emp.name,emp.gender,dept.NAME
FROM emp,dept
WHERE emp.dept_id=dept.id;

第二种写法
SELECT 
  t1.name,--员工表的名称
  t1.gender,--员工表的工资
  t2.name,--部门表的名称
FROM
  emp t1,
  dept t2
WHERE
  t1.deot_id = t2.id;

SELECT 
字段列表
FROM
表名
(inner) JOIN
表名2
ON
条件

SELECT * 
FROM
emp
INNER JOIN
dept
ON
emp.dept_id = dept.id;

SELECT *
FROM
emp
JOIN
dept
ON
emp.dept_id = dept.id;

SELECT
字段列表
FROM
表1
left [outer] join
表2
ON
条件;
-- 查询的是左表所有数据以及其交集部分

SELECT
字段列表
FROM
表1
RIGHT [OUTER] JOIN 
表2
ON 
条件；
-- 查询的是右表所有数据以及其交集部分

-- 查询工资最高的员工信息
SELECT MAX(salary) FROM emp;
-- 查询工资等于9000的员工信息
SELECT * FROM emp WHERE emp.salary = 9000;

--一条SQL语句就完成这个条件
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

--举例：查询员工工资小于平均工资的人
SELECT * 
FROM emp
WHERE emp.salary < (SELECT AVG(salary) FROM emp);

-- 举例：查询财务部和市场部所有的员工信息
--一般查询
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id=2;
--通过子查询
SELECT *
FROM
emp
WHERE
dept_id
IN
(SELECT id FROM dept WHERE NAME='财务部' OR NAME='市场部' );

--举例：查询员工入职日期是2011-11-11日之后的员工信息和部门信息
--通过子查询
SELECT * 
FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2,
WHERE
t1.id=t2.dept_id;
--通过内链接
SELECT *
FROM
emp t1,dept t2
WHERE
t1.dept_id = t2.id
AND
t1.join_date > '2011-11-11'

--查询员工姓名，工资，工资等级
SELECT
  t1.ename,
  t1.salary,
  t2.grade
FROM 
  emp t1, salarygrade t2
 WHERE 
   t1.salary BETWEEN t2.losalary AND t2.hisalary;

--查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级 
SELECT
  t1.ename,
  t1.salary,
  t2.jname,
  t2.description,
  t3.dname,
  t3.loc,
  t4.grade
FROM
  emp t1,job t2,dept t3,salarygrade t4
WHERE
  t1.job_id = t2.id
  AND
  t1.dept_id = t3.id
  AND
  t1.salary BETWEEN t4.losalary AND t4.hisalary;

--查询出部门编号，部门名称，部门位置，部门人数 
/*分析：
1.部门编号，部门名称，部门位置---dept表。部门人数---emp表
2.使用分组查询。按照emp.dept_id完成分组，查询COUNT（id）
3.使用子查询将第2步的查询结果和dept表进行关联查询*/
SELECT 
  t1.id,t1.dname,t1.loc,t2.total
FROM 
  dept t1,
  (SELECT 
    dept_id,COUNT(id) total
   FROM
    emp
   GROUP BY dept_id) t2
   WHERE t1.id = t2.dept_id;

--查询所有员工的姓名及其直接上级的姓名，没有领导的员工也需要查询
SELECT 
  t1.ename,
  t1.mgr,
  t2.id,
  t2.ename
FROM
  emp t1
LEFT JOIN 
emp t2
ON 
t1.mgr = t2.id;