Mysql数据库常用语句——隐式内连接/显式外连接/左外连接/右外连接/子连接

Mysql数据库常用语句——创建数据库/表_yopky的博客-CSDN博客
Mysql数据库常用语句——插入/更改/删除数据_yopky的博客-CSDN博客
 

新增一张表

-- 新增一张工资表wage
CREATE TABLE wage(
			wid INT PRIMARY KEY AUTO_INCREMENT,
			wage_level VARCHAR(30),
			wage_money VARCHAR(30));
			
INSERT INTO wage (wage_level,wage_money) VALUES ('1级','9000-8000'),('2级','7000-6000'),('3级','5000-4000'),('4级','3000-2000');
SELECT * FROM wage

-- employee 新增一列wage_id
ALTER TABLE employee ADD wage_id INT;

-- 插入wage_id数据
UPDATE employee SET wage_id = 1 where eid in (2,6,8,13);
UPDATE employee SET wage_id = 2 where eid in (1,5,10,14);
UPDATE employee SET wage_id = 3 where eid in (3,6,8,12);
UPDATE employee SET wage_id = 4 where eid in (4,5,11,15);

内连接查询

内连接

通过指定条件匹配2张表的数据，匹配上就显示，匹配不上就不显示

隐式内连接

隐式内连接：FROM后面直接写多个表名，使用where指定连接条件的连接方式

查询所有的员工信息及所在部门

-- 查询所有的员工信息及所在部门
SELECT * FROM employee,department where dept_id = id;

-- 查询员工名字及所在部门
SELECT e.ename,d.dep_name FROM employee e,department d WHERE e.dept_id= d.id


-- 查询黄二属于哪个部门
SELECT e.eid,e.ename,d.dep_name FROM employee e,department d WHERE e.dept_id=d.id AND e.ename='黄二'

显式内连接

显式内连接：使用INNER JOIN..ON 这种方式

两表显式内连接查询

-- 查询所有员工信息及对应的部门信息

SELECT * FROM employee e INNER JOIN department d ON e.dept_id=d.id ORDER BY e.eid

查询年龄小于于40的员工信息，包括部门跟工资级别

三表显式内连接查询

-- 查询年龄小于于40的员工信息，包括部门跟工资级别
-- 三表显式内连接查询
SELECT e.ename,d.dep_name,w.wage_level FROM (employee e INNER JOIN department d ON e.dept_id=d.id) INNER JOIN wage w ON e.wage_id=w.wid WHERE e.age<40
SELECT * from employee WHERE age<40;

左外连接查询

左外连接：使用LEFT OUTER JOIN

1.已左表为基准，匹配右边表中的数据，如果匹配得上，就展示匹配到的数据

2.如果匹配不到，左表的数据正常展示，右边的展示为NULL

-- department添加一条数据
INSERT into department (dep_name,dep_location) VALUES ('运营部','上海')

-- 2表左外连接
SELECT * FROM employee e LEFT JOIN department d ON e.dept_id=d.id;

SELECT * FROM department d LEFT JOIN employee e ON e.dept_id=d.id;

 示例代码：

-- 3表左外连接
SELECT * FROM (employee e LEFT JOIN department d ON e.dept_id=d.id) LEFT JOIN wage w ON e.wage_id=w.wid ORDER BY e.eid

-- 查询每个部门有多少人
SELECT d.dep_name AS '部门',COUNT(e.ename) As '人数' FROM employee e LEFT JOIN department d ON e.dept_id=d.id GROUP BY e.dept_id

-- 查询每个部门有多少人不包括没部门的
SELECT d.dep_name AS '部门',COUNT(e.ename) As '人数' FROM employee e LEFT JOIN department d ON e.dept_id=d.id WHERE e.dept_id IS NOt NULL GROUP BY e.dept_id

右外连接查询

右外连接：使用RIGHT OUTER JOIN

1.已右表为基准，匹配左边表中的数据，如果匹配得上，就展示匹配到的数据

2.如果匹配不到，右表的数据正常展示，左边的展示为NULL

-- 右外连接
SELECT * FROM department d RIGHT JOIN employee e ON d.id=e.dept_id;

子查询

一条select查询语句的结果，作为另一条select语句的一部分

-- 子查询
-- 通过子查询，查询员工年龄最大的员工信息
SELECT * FROM employee WHERE age = (SELECT MAX(age) FROM employee)

-- 查询产品部的所有人的信息
-- 方法一：子连接
SELECT * FROM employee e WHERE e.dept_id = (SELECT d.id FROM department d WHERE d.dep_name='产品部')
-- 方法二：左连接
SELECT * FROM employee e LEFT JOIN department d ON e.dept_id=d.id WHERE d.dep_name='产品部' 

-- 查询小于平均年龄的员工信息
SELECT * FROM employee WHERE age<(SELECT AVG(age) FROM employee)

-- 查询小于平均年龄的员工信息，包括部门及工资级别信息
SELECT e.ename,d.dep_name,w.wage_level FROM (employee e LEFT JOIN department d ON e.dept_id=d.id) LEFT JOIN wage w ON e.wage_id=w.wid WHERE age < (SELECT AVG(age) FROM employee)

-- 查询年龄小于40的员工来自哪个个部门
-- 方法一：子查询使用IN获得单列多行数据
SELECT * FROM department d WHERE d.id IN (SELECT DISTINCT e.dept_id FROM employee e WHERE e.age <40);

-- 方法二：
SELECT DISTINCT d.id,d.dep_name FROM employee e LEFT JOIN department d ON e.dept_id = d.id WHERE e.age < 40

-- 查询‘行政部’与‘开发部’下面的全部员工信息
-- 方法一：
SELECT * FROM employee WHERE dept_id IN (SELECT id FROM department WHERE dep_name IN ('行政部','开发部'))

-- 方法二：
SELECT * FROM employee e LEFT JOIN department d ON e.dept_id=d.id WHERE d.dep_name IN ('行政部','开发部')