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 ('行政部','开发部')

  • 1
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL中,连接操作主要有隐式内连接显式内连接左外连接外连接以及查询。它们的具体含义、异同点如下: 1. 隐式内连接 隐式内连接是指在SQL语句中使用"FROM"关键字连接两个或多个表,连接条件写在"WHERE"句中。只有在连接条件成立的情况下,才会将两个表中的数据连接起来,否则会被过滤掉。隐式内连接使用的关键字是"FROM"和"WHERE"。 示例语句: ``` SELECT * FROM table1, table2 WHERE table1.id = table2.id; ``` 2. 显式内连接 显式内连接是指在SQL语句中使用"JOIN"关键字连接两个或多个表,连接条件写在"ON"句中。只有在连接条件成立的情况下,才会将两个表中的数据连接起来,否则会被过滤掉。显式内连接使用的关键字是"JOIN"和"ON"。 示例语句: ``` SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; ``` 3. 左外连接 左外连接是指以左表为基础,将两个表中的数据连接起来,并且左表中的所有行都会被保留,表中没有匹配的行数据则会被设置为NULL。左外连接使用的关键字是"LEFT JOIN"和"ON"。 示例语句: ``` SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; ``` 4. 外连接 外连接是指以表为基础,将两个表中的数据连接起来,并且表中的所有行都会被保留,左表中没有匹配的行数据则会被设置为NULL。外连接使用的关键字是"RIGHT JOIN"和"ON"。 示例语句: ``` SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; ``` 5. 查询 查询是指在一个查询语句中嵌套另一个查询语句查询可以作为主查询的条件,也可以作为主查询的结果。查询使用的关键字是"SELECT"、"FROM"、"WHERE"等。 示例语句: ``` SELECT * FROM table1 WHERE table1.id IN (SELECT id FROM table2); ``` 上述几种连接方式和查询的异同点如下: 相同点: - 都是用来连接两个或多个表,获取需要的数据。 - 都可以使用条件过滤数据,获取符合条件的数据。 不同点: - 连接方式不同:隐式内连接使用"FROM"和"WHERE"关键字,显式内连接使用"JOIN"和"ON"关键字,左外连接使用"LEFT JOIN"和"ON"关键字,外连接使用"RIGHT JOIN"和"ON"关键字,查询使用"SELECT"、"FROM"、"WHERE"等关键字。 - 连接结果不同:内连接只返回匹配的数据,左外连接返回左表所有数据和表匹配的数据,外连接返回表所有数据和左表匹配的数据,查询可以返回符合条件的数据或作为主查询的条件。 - 执行效率不同:内连接显式连接效率相对较高,左外连接查询效率较低。 - 使用场景不同:内连接用于获取两个表中匹配的数据,左外连接用于获取两个表中的所有数据和匹配的数据,查询用于需要嵌套查询的情况。 总体来说,不同的连接方式和查询都有各自的使用场景和优缺点,在实际开发中需要根据具体需求进行选择。同时,在进行连接操作时需要注意条件的正确性和效率问题,以便获取准确的数据并提高查询效率。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值