多表查询-6

最新推荐文章于 2023-08-01 01:00:00 发布
最新推荐文章于 2023-08-01 01:00:00 发布
阅读量111 收藏
点赞数
分类专栏： MySQL学习笔记 文章标签： 数据库 mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ssq_az/article/details/127441761
版权

这里写目录标题

多表查询

笛卡尔积

  • 笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。它的作用就是可以把任意表进行连接，即使这两张表不相关。
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
  • 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

多表查询分类

等值连接 vs 非等值连接

等值连接
  • 多个连接条件与 AND 操作符
SELECT e.employee_id, e.last_name, d.department_name, l.location_id
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
  • 区分重复的列名
    • 多个表中有相同列时，必须在列名之前加上表名前缀。
    • 在不同表中具有相同列名的列可以用 表名 加以区分。
  • 表的别名
    • 使用别名可以简化查询。
    • 列名前使用表名前缀可以提高查询效率。
非等值连接
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接 vs 非自连接

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

内连接 vs 外连接

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

实现多表查询

  • 使用JOIN…ON子句创建连接的语法结构：
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
  • 语法说明：
    • 可以使用 ON 子句指定额外的连接条件。
    • 这个连接条件是与其它条件分开的。
    • ON 子句使语句具有更高的易读性。
    • 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

内连接(INNER JOIN)

  • 语法：
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

外连接(OUTER JOIN)

左外连接(LEFT OUTER JOIN)
  • 语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
右外连接(RIGHT OUTER JOIN)
  • 语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
满外连接(FULL OUTER JOIN)
  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
  • ，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION的使用

  • 合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
  • UNION 操作符返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。（更推荐）
  • 语法：
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

# 查询部门编号>90或邮箱包含a的员工信息
#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

# 查询中国用户中男性的信息以及美国用户中年男性的用户信息
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

7种SQL JOINS的实现

在这里插入图片描述

#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#左下图：满外连接
# 左上图 nuion all 右中图
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

SQL99语法新特性

自然连接

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

练习

# 1.显示所有员工的姓名，部门号和部门名称。
select e.last_name, d.department_id, d.department_name
from employees e left join departments d
on e.department_id = d.department_id;

# 2.查询90号部门员工的job_id和90号部门的location_id
select e.job_id, d.location_id
from employees e join departments d
on e.department_id = d.department_id
where e.department_id = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
select last_name, department_name, d.location_id, city
from employees e left join departments d
on e.department_id = d.department_id
left join locations l
on d.location_id = l.location_id
where commission_pct is not null;

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
select last_name, job_id, e.department_id, department_name
from employees e left join departments d
on e.department_id = d.department_id
left join locations l
on l.location_id = d.location_id
where l.city = 'Toronto';

SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
select d.department_name, street_address, last_name, job_id, salary
from employees e join departments d
on e.department_id = d.department_id
left join locations l
on l.location_id = d.location_id
where d.department_name = 'Executive';

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
select e.last_name, e.employee_id "Emp#", p.last_name, p.employee_id  "Mgr#"
from employees e left join employees p
on e.manager_id = p.employee_id;

# 7.查询哪些部门没有员工
select d.department_id
from departments d left join employees e
on d.department_id = e.department_id
where e.department_id is null;

# 8. 查询哪个城市没有部门
select l.city
from locations l left join departments d
on d.location_id = l.location_id
where d.department_id is null;

# 9. 查询部门名为 Sales 或 IT 的员工信息
select last_name
from employees e left join departments d
on e.department_id = d.department_id
where d.department_name in ('Sales', 'IT');
天同化权
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL7.2.78破解版
12-21
MySQL客户端，完美破解，正常使用中，分享出来给大家使用。
sql多表关联查询--按照文本例子即可
08-10
sql多表关联查询按照文本例子即可
MySQL7
qq_47305937的博客
05-15 145
MySQL7学习笔记
mysql7
Markland_l的博客
06-11 234
mysql explain相关内容
MySQL 7
m0_64735681的博客
08-01 439
GTID
MyBatis-Plus多表联合查询并且分页(3表联合)
08-24
MyBatis-Plus 多表联合查询并且分页（3 表联合） MyBatis-Plus 是一个基于 MyBatis 的增强型 ORM 框架，提供了很多实用的功能，例如多表联合查询和分页等。下面我们将详细介绍如何使用 MyBatis-Plus 实现多表联合...
结合mybatis-plus实现简单不需要写sql的多表查询
08-25
在多表查询方面，MyBatis-Plus 提供了简单易用的方法，让开发者能够通过实体类之间的关联关系进行联查。 在 MyBatis-Plus 中，实现多表查询的关键在于实体类的关联和注解的使用。首先，你需要确保项目中已经集成了 ...
SQL 练习二 多表查询6-4.docx
最新发布
07-04
### SQL练习二：多表查询知识点详解 #### 一、创建表结构 在进行多表查询之前，首先需要创建好数据库中的两张表：商品表 `produ` 和订单表 `ords`。 1. **商品表 `produ`**： - 字段： - `id`: 自动增长主键，...
支持连表查询的mybatis-plus
04-21
1. **连表查询基础**：在SQL中，连表查询是将两个或多个表的数据关联在一起进行查询，常见的有内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）。这些连接方式可以根据不同...
mysql的安装
11-25
mysql yum install mysql mysql-server mysql-devel 完成后，用 /etc/init.d/mysqld start 启动mysql 启动mysql控制台： mysql mysql>; USE mysql; mysql>; UPDATE user SET Password=PASSWORD('newpassword') WHERE user='root'; mysql>; FLUSH PRIVILEGES; 允许远程登录 mysql -u root -p Enter Password: mysql>GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; 完成后就能远程管理mysql了。 mysql服务名字 service mysqld start GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
MySQL（06多表查询）
缘友一世的博客
04-03 1147
🐬🐬🐬🐬🐬🐬🐬🐬🐬🐬🐬 🐬小白最近学习了多表查询，对此做一些笔记的整理🐬 🎯🎯🎯🎯❤️❤️❤️❤️❤️❤️ 文章目录🐬第六章 多表查询🐬1.1 一个案例引发的多表连接1.2 笛卡尔积（或交叉连接）的理解1.3 案例分析与问题解决2 多表查询分类讲解分类1：等值连接 vs 非等值连拓展1分类2：自连接 vs 非自连接分类3：内连接 vs 外连接3 SQL99语法实现多表查询3.1 基本语法3.2 内连接(INNER JOIN)的实现3.3 外连接(OUTER JOIN)的实现3.3.1 左外连接(LEFT .
MySQL 7：MySQL触发器
Amzmks的博客
02-14 755
触发器和存储过程一样，是一个能够完成特定功能的SQL片段，存储在数据库服务器上，但是不需要调用触发器。在MySQL中，触发器的执行只能在执行插入、删除、更新操作时触发。假设触发器触发每次执行1s，insert table 500条数据，那么触发器需要触发500次。仅触发器的执行时间就需要500s，insert 500条数据加起来如果是1s的话，那么这个insert的效率就很低了。1. MySQL 中的触发器不能对该表进行插入、更新、删除操作，避免递归循环触发器。触发器定义在表上，附着在表上。
MySQL(7) MySQL存储引擎
逆轮回
04-19 108
它的目的是作为 MySQL 源代码中的一个例子，用来演示如何开始编写一个新存储引擎。Memory所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。Archive非常适合存储大量的独立的，作为历史记录的数据。InnoDB事务型数据库的首选引擎，支持ACID事务，支持行级锁定, MySQL 5.5 起成为默认数据库引擎。Federated将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。BlackHole：黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继。
【mysql数据库】MySQL7在Centos7的环境安装
weixin_63449996的博客
07-20 629
Mysql数据库——Mysql7在linux Centos7环境下的安装
通俗易懂的 mysql 7种join
qq_40662239的博客
03-21 1639
首先要建两张有外键关系的表 -- 学生表 CREATE TABLE IF NOT EXISTS `student`( `id` INT (4) NOT NULL AUTO_INCREMENT COMMENT'学号', `name` VARCHAR (10) COMMENT '姓名', `sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别', PRIMARY...
深入理解MySQL（7）：MySQL如何调优
m0_50275872的博客
04-13 1017
MySQL 九、MySQL调优 影响MySQL的性能因素： 系统各种配置及规则数据 超大文本数据 Schema设计对系统的性能影响 硬件环境对系统性能的影响 对于MySQL层优化一般遵从五个原则： 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO 返回更少的数据：只返回需要的字段和数据分页处理 减少磁盘io及网络io 减少交互次数：批量DML操作，函数存储等减少数据连接次数 减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用 利用更多资源：使用表分区，
mysql7 mysql8_MySQL 8即将到来
cumo7370的博客
06-24 122
mysql7 mysql8 MySQL 8即将到来，这将是一个巨大的变化。 MySQL 5.7已经问世了一年多，它的本机JSON数据类型，增强的安全性和更好的性能受到了好评。 但是关于5.7的某些事情需要现代化，这就是为什么MySQL 8即将问世的原因。 6＆7怎么了？ 多年前，在Sun Microsystems购买MySQL AB之前，有一个版本号为6MySQL版本。可悲的是，它有点雄...
MySQL - 7、用户权限和管理
学无止境
08-01 96
【代码】MySQL - 7、用户权限和管理。
mybatis-plus 多表查询
07-28
在使用MyBatis-Plus进行多表查询时，可以通过使用注解@Results和@Result来定义查询结果的映射关系。在引用\[2\]中的UserMapper接口中，可以看到一个示例，通过@Results和@Result注解来定义了查询用户及用户下所有...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值