Mysql连接查询

Mysql连接查询

数据库数据准备

-- 创建部门表
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);

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 * FROM emp,dept WHERE emp.id = dept.id;

显式内连接

#显式内连接
SELECT * FROM emp INNER JOIN dept ON emp.id = dept.id;

小结

• 内连接查询的是公共部分,满足连接条件(主外键关系)的部分
• 用左边表的记录去匹配右边表的记录如果符合条件的则显示

外连接

左外连接

#左外连接
SELECT * FROM emp LEFT JOIN dept ON emp.id = dept.id;

右外连接

#右外连接
SELECT * FROM emp RIGHT JOIN dept ON emp.id = dept.id;

小结

• 左外连接在内连接的基础上保证左边表的数据全部显示
• 右外连接在内连接的基础上保证右边表的数据全部显示

子查询

什么是子查询
一个查询里面至少包含2个select

子查询结果的三种情况

子查询结果是值的情况

问题1查询工资最高的员工是谁
分解:查询最高工资

SELECT MAX(salary) FROM emp;

查询工资是9000的员工即查询工资最高的员工

SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);

子查询结果是单列多行的时候

问题1查询工资小于平均工资的员工有哪些
分解:查询平均工资

 SELECT AVG(salary) FROM emp;

查询工资小于平均工资的员工

SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

子查询的结果是多行多列

问题1查询工资大于5000的员工来自于哪些部门的名字

分解查询工资大于5000的员工

  SELECT dept_id FROM emp WHERE salary > 5000;

根据子查询结果查询部门名字

SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000);