傻瓜网站制作,新都区网站建设,一二三四视频社区在线,做百度推广的网络公司目录
准备工作
1.多表查询
2.INNER JOIN#xff08;内连接#xff09;
2.1.笛卡尔积
1.2.笛卡尔积的过滤
1.3.INNER JOIN#xff08;显式内连接#xff09; 1.4.SELF JOIN#xff08;自连接#xff09;
3. LEFT JOIN#xff08;左连接#xff09;
3.1.一个例子…目录
准备工作
1.多表查询
2.INNER JOIN内连接
2.1.笛卡尔积
1.2.笛卡尔积的过滤
1.3.INNER JOIN显式内连接 1.4.SELF JOIN自连接
3. LEFT JOIN左连接
3.1.一个例子
4.RIGHT JOIN右连接
4.1.使用示例 在MySQL中复合查询有时也称为组合查询或多查询通常涉及将多个查询组合在一起以便执行更复杂的检索、过滤和操作。这些查询可以包括多个表之间的连接JOIN、子查询SUBQUERY、联合查询UNION等。
今天我们先讲解多表查询
准备工作
首先我们去这个网站下载一个.sql文件阿里云盘分享 (alipan.com) 拿到该数据库文件以后我们可以先打开该文件进行查看其内容
vim scott_data.sql我们会发现其里面都是SQL记录对于MySQL我们备份其数据库时其实备份的全部都是一条条有效的SQL记录通过重新执行这些SQL我们便能够得到和原来一摸一样数据库。
接下来我们就可以在mysql中将这个数据库给创建出来了
source 该文件的绝对路径;例如我这里是
source /home/zs_113/scott_data.sql 然后我们查询我们的数据库发现数据库中多了一个scott的数据库
show databases;我们进去看看
use scott;
show tables; 这三张表是
EMP员工表DEPT部门表SALGRADE工资等级表
我们先分别查看一下表结构和表内容
EMP员工表
desc emp;
select * from emp; DEPT部门表
desc dept;select * from dept; SALGRADE工资等级表
desc salgrade;
select * from salgrade; 接下来我们的查询都会基于上面这一个数据库。
1.多表查询
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类
INNER JOIN内连接,或等值连接获取两个表中字段匹配关系的记录。LEFT JOIN左连接获取左表所有记录即使右表没有对应匹配的记录。RIGHT JOIN右连接 与 LEFT JOIN 相反用于获取右表所有记录即使左表没有对应匹配的记录。
2.INNER JOIN内连接
2.1.笛卡尔积
先来补充一下 笛卡尔积 笛卡尔积是数学中集合运算的一种概念也可以应用于数据库领域特别是在关系型数据库中。以下是对笛卡尔积的详细解释 一、定义 数学定义笛卡尔积是指两个集合A和B之间的所有可能的有序对的集合。具体来说如果A是一个集合B也是一个集合那么A和B的笛卡尔积A×B是一个新的集合该集合中的元素是A中的元素和B中的元素的所有可能的有序对。数据库定义在数据库领域尤其是关系型数据库中当两个或多个表进行连接查询时如果没有指定有效的连接条件或者连接条件错误每个表中的每条记录都会与另一个表中的每条记录进行配对形成大量的数据组合这种现象被称为笛卡尔积。 二、特点 记录组合笛卡尔积会生成两个或多个表中记录的所有可能组合。如果表A有m条记录表B有n条记录那么A和B的笛卡尔积将包含m×n条记录。无序性笛卡尔积中的记录是无序的即不同记录之间的顺序并不影响笛卡尔积的结果。冗余性笛卡尔积可能会生成大量无用的数据组合增加数据的冗余性。特别是在多表查询时如果没有有效的连接条件笛卡尔积会导致结果集急剧膨胀。 三、生成方式 隐式生成在MySQL中如果执行了多表查询而没有明确指定连接条件ON子句或者连接条件写错无法正确匹配表中的记录那么就会产生笛卡尔积。显式生成使用CROSS JOIN明确指示数据库进行笛卡尔积操作也会直接生成笛卡尔积。例如SELECT s.name, c.class_name FROM students s CROSS JOIN classes c; 这个查询语句会生成students表和classes表的笛卡尔积。 四、应用场景与注意事项 应用场景笛卡尔积在某些特定场景下可能是有用的比如需要生成所有可能的组合时。但在大多数情况下笛卡尔积并不是预期的查询结果因此需要避免其产生。注意事项在进行多表查询时应确保指定了有效的连接条件以避免生成笛卡尔积。如果确实需要生成笛卡尔积应明确使用CROSS JOIN并清楚其可能带来的性能问题和数据冗余性。 多表查询的语法
将多张表的表名依次放到from子句之后用逗号隔开即可这时MySQL将会对给定的这多张表取笛卡尔积作为多表查询的初始数据源。所谓的对多张表取笛卡尔积就是得到这多张表的记录的所有可能性全部组合出来。
....from table_1, table_2, table_4 ....例如我们对下面这两张小表做笛卡尔积 select * from dept, salgrade;可以看出对部门表和薪资等级表取笛卡尔积时会先从薪资等级表中选出一条记录与部门表中的所有记录进行组合然后再从薪资等级表中选出一条记录与部门表中的所有记录进行组合以此类推最终得到的就是这两张表的笛卡尔积。
1.2.笛卡尔积的过滤
需要注意的是对多张表取笛卡尔积后得到的数据并不都是有意义的。 我们举个例子
select * from emp; select * from dept; 比如对员工表和部门表取笛卡尔积时员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合而实际一个员工只有和自己所在的部门信息进行组合才是有意义的。
select * from emp,dept; 很多数据都是没有用的我们可以对此进行过滤。所以我们可以在where子句中指明emp.deptno dept.deptno即从笛卡尔积中筛选出员工的部门号和部门的编号相等记录。
select * from emp,dept where emp.deptno dept.deptno; 可以看到这种一个员工只和自己所在的部门信息进行组合的表格才是正真有意义的表格。 说明 进行笛卡尔积的多张表中可能会存在相同的列名这时在选中列名时需要通过表名.列名的方式进行指明 1.3.INNER JOIN显式内连接 事实上上面的select * from emp,dept where emp.deptno dept.deptno;其实是INNER JOIN的隐式写法尽管隐式内连接在语法上是正确的并且许多数据库系统包括 MySQL都支持它但显式内连接使用 INNER JOIN通常被认为是更好的做法因为它更易于阅读和维护特别是当查询变得复杂时。此外显式连接语法也是 SQL 标准的一部分因此在不同数据库系统之间的可移植性更好。
接下来我们来学习一下显示内连接
INNER JOIN 返回两个表中满足连接条件的匹配行以下是 INNER JOIN 语句的基本语法
SELECT column1, column2, ...FROM table1INNER JOIN table2 ON table1.column_name table2.column_name;
参数说明
column1, column2, ... 是你要选择的列的名称如果使用 * 表示选择所有列。table1 table2 是要连接的两个表的名称。table1.column_name table2.column_name 是连接条件指定了两个表中用于匹配的列。 我们可以将上面那个隐式内连接
select * from emp,dept where emp.deptno dept.deptno;
改成显式内连接
select * from emp inner join dept on emp.deptno dept.deptno; 这条语句使用了 INNER JOIN 关键字来明确指示进行内连接操作并通过 ON 子句指定了连接条件 emp.deptno dept.deptno。这是 SQL 中推荐的做法因为它使得查询的意图更加清晰也更易于理解和维护。
与隐式内连接相比显式内连接具有以下优点
可读性更高显式内连接的语法结构更加清晰能够一眼看出是在进行表连接操作。可维护性更强当查询变得复杂涉及多个表连接时显式内连接更容易进行修改和扩展。符合 SQL 标准显式内连接是 SQL 标准的一部分因此具有更好的跨数据库系统兼容性。避免潜在错误隐式内连接有时可能会因为缺少连接条件而意外地产生笛卡尔积而显式内连接则通过 ON 子句明确要求连接条件从而避免了这种错误。
因此在实际开发中建议优先使用显式内连接来替代隐式内连接。 可以看到结果和上面的一模一样。 注意以后再遇到类似的情况请用显式内连接写法 下面我们来进行一些练习
1. 显示部门号为10的部门名员工名
员工名(ename)和工资(sal)这两个字段在emp表中而部门名称在dept表中所以我们要使用多表查询from emp, dept。为了让两张表的笛卡儿积的结果是有效的我们可以在where条件中使用emp.deptno dept.deptno由于我们只要部门号为10的部门所以我们可以使用where条件进行筛选dept.deptno 10当然由于emp中也有deptno字段所以我们也可以使用emp.deptno 10。
隐式内连接的写法如下
select ename, dept.deptno, dname from emp, dept where (emp.deptnodept.deptno) and (dept.deptno 10);显式内连接的写法如下
select ename, dept.deptno, dname from emp inner join dept on (emp.deptnodept.deptno) and (dept.deptno 10); 2 显示各个员工的姓名工资及工资级别
员工名(ename)和工资(sal)这两个字段在emp表中而工资级别在salgrade表中所以我们要使用多表查询from emp, salgrade。为了让两张表的笛卡儿积的结果是有效的以及正确的显示工资级别我们要在where子句中添加限制条件: sal between losal and hisal 隐式内连接写法
select ename, sal, grade , losal, hisal from emp, salgrade where sal between losal and hisal;显式内连接写法
select ename, sal, grade , losal, hisal from emp inner join salgrade on sal between losal and hisal;1.4.SELF JOIN自连接
刚才我们使用多表查询是两张不同的表进行组合那么对于同一张表能不能进行组合呢
答案是可以的对同一张表连接查询我们称之为自连接 自连接与内连接的关系 特殊性自连接是一种特殊的内连接因为自连接本质上是在一个表内进行的而内连接通常涉及两个不同的表。但是通过为表取别名自连接在逻辑上将一个表视为两个表从而可以使用内连接的语法和逻辑。相同点自连接和内连接都使用JOIN子句尽管自连接可能不显式地使用JOIN关键字而是通过逗号分隔和WHERE子句来实现并指定连接条件来过滤结果集。不同点自连接只涉及一个物理表而内连接涉及两个或更多不同的物理表。此外自连接通常用于比较同一表中的不同行而内连接用于从多个表中获取相关联的数据。 在MySQL中自连接查询是指在同一张表中进行连接操作。通常情况下我们通过表与表之间的关系来进行连接操作但有时候我们需要在同一张表中进行数据的连接和比较这就需要使用到自连接查询。
自连接的语法如下 SELECT 列名 FROM 表名 表别名1, 表名 表别名2 WHERE 表别名1.列名 表别名2.列名;
SELECT 列名这里您应该指定要从连接结果中选择的列。如果两个表中有同名的列您需要使用表别名来区分它们例如表别名1.列名或表别名2.列名。如果您只选择了一个列名而没有指定别名那么SQL引擎通常会从它找到的第一个匹配的列中返回数据这取决于查询优化器和具体的SQL实现。但是为了避免混淆和错误最好总是使用表别名来明确指定要选择的列。FROM 表名 表别名1, 表名 表别名2这里您指定了要从哪个表中选择数据并且为同一个表指定了两个不同的别名表别名1和表别名2以便在查询中区分它们。这是自连接的关键部分。WHERE 表别名1.列名 表别名2.列名这是连接条件它指定了如何连接两个表的实例。在这个例子中您正在查找表别名1中某个列的值与表别名2中相同列的值相等的记录。
为了使查询更清晰和标准化您可以使用现代的JOIN子句风格来重写它如下所示 SELECT 表别名1.列名 AS 别名1列名, 表别名2.列名 AS 别名2列名 FROM 表名 表别名1 INNER JOIN 表名 表别名2 ON 表别名1.列名 表别名2.列名;
在这个重写后的查询中
我使用了INNER JOIN来明确指定这是一个内连接。我使用了ON子句来指定连接条件。我为选择的每个列都指定了一个别名别名1列名和别名2列名以避免混淆特别是当两个表中有同名的列时。
请注意如果您只选择一个列名而不指定别名并且两个表都有该列名那么您可能会得到一个错误或者SQL引擎可能会默认选择其中一个表的列这取决于具体的SQL实现和查询优化器的行为。因此最好总是使用表别名来明确指定要选择的列并为它们提供清晰的别名。
语法
我们直接像多表查询那样在from后面写上两次表名是不行的因为两张表同名的话我们后面对列进行各种操作时有歧义。
select * from dept,dept;所以我们需要对表进行重命名以保证我们后面对列进行各种操作时没有歧义
select * from dept as t1, dept as t2;下面我们来看一个例子帮我们更好的理解自连接
1.显示员工FORD的上级领导的编号和姓名mgr是员工领导的编号empno是雇员编号
首先理解题意员工的上级领导也是员工也有自己的员工编号。
此外员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来 上图说明FORD和FORD的领导都在emp表里我们要根据员工的mgr字段和其他人的empno进行比对得到FORO的领导是谁
方法一使用多条SQL
首先我们可以先拿到员工FORD其领导的编号
select mgr from emp where ename FORD;然后根据这个编号进行查找其上级领导
select * from emp where empno7566;方法二:使用自连接
我们发现两次查找都是在emp表中进行查找所以我们可以对emp表进行自连接然后对自连接的结果按照 :
e1表的雇员名称必须是FORD而且员工的领导编号等于领导的员工编号即(e1.enameFORD) and (e1.mgre2.empno)的方式进行过滤。
select e2.ename,e2.empno
from emp as e1, emp as e2
where (e1.enameFORD) and (e1.mgre2.empno);怎么理解这个语句呢 这里我们可以把e1和e2看作两张表然后我们知道select * from emp as e1,emp as e2会形成笛卡尔积 然后我们通过限定条件e1.enameFORD来缩小到那14行。
select *
from emp as e1, emp as e2
where (e1.enameFORD); 然后我们再通过限定条件 e1.mgre2.empno筛选出符合的那一行。其中e1是表的左边那半e2是右边那半。
select *
from emp as e1, emp as e2
where (e1.enameFORD) and (e1.mgre2.empno); 最后我们再筛选出那两列
select e2.ename,e2.empno
from emp as e1, emp as e2
where (e1.enameFORD) and (e1.mgre2.empno); 这个查询将返回所有直接汇报给名为FORD的员工的下属的姓名和员工编号。
3. LEFT JOIN左连接
LEFT JOIN 返回左表的所有行并包括右表中匹配的行如果右表中没有匹配的行将返回 NULL 值以下是 LEFT JOIN 语句的基本语法
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name table2.column_name;
这段SQL代码的功能是从两个表table1 和 table2中查询数据。 具体来说它执行了一个左连接LEFT JOIN操作这意味着查询结果将包含左表table1中的所有记录即使这些记录在右表table2中没有匹配的记录。如果在右表中有匹配的记录则这些匹配的记录信息也会被包含在结果中。如果没有匹配的记录则结果中对应的右表table2的列将包含NULL值。
详细解释如下
SELECT column1, column2, ...这部分指定了查询结果中需要包含的列。这里的column1, column2, ...应该替换为实际需要查询的列名可以来自table1或table2或者是通过计算得到的列比如使用函数或表达式。FROM table1这指定了查询的主表即左表。所有在table1中的记录都会出现在结果集中即使它们在table2中没有匹配的记录。LEFT JOIN table2 ON table1.column_name table2.column_name这部分执行了一个左连接操作将table1和table2连接起来。ON子句指定了连接条件即两个表中哪些列的值需要相等才能认为记录是匹配的。这里的table1.column_name table2.column_name应该替换为实际的列名这些列名在两个表中都存在并且用于确定哪些记录是相关的。
左连接的一个常见用途是确保结果集中包含左表的所有记录同时尽可能地从右表中获取相关的匹配记录。如果右表中没有匹配的记录结果集中的相应列将显示为NULL。
这种查询在处理需要保留主表所有记录的场景中非常有用比如在一个订单系统中你想列出所有的订单即使某些订单没有关联的客户信息比如新客户还未被添加到客户表中。 3.1.一个例子
给出一张学生表学生表中的name代表的是学生的姓名id代表的是学生的学号。如下
create table stu(
id int,
name varchar(10)
);
insert into stu values (1,jack),(2,tom),(3,kity),(4,nono);
select * from stu; 再给出一张成绩表其中的id代表的是考试学生的学号grade代表的是学生的成绩。如下
create table exam(
id int,
grade int
);
insert into exam values (1,56),(2,76),(11,80);
select * from exam; 注意观察这两张表
学生表中的3,4号学生在成绩表中是没有成绩的可能是缺考了成绩表中的11号学生在学生表中是不存在的可能学生表统计漏了
现在我们如果使用内连接进行连接显示学号很姓名的我们发现有一些信息会因为不存在而被筛选掉。
select * from stu inner join exam on stu.idexam.id;3号同学不见了。这样子可不行啊。
我们查询所有学生的成绩如果这个学生没有成绩也要将学生的个人信息显示出来
可以看出题目要求学生表的信息要被完全展示而成绩表中的信息如果实在不满足连接条件可以被忽略。
所以我们可以使用左外连接保证左边的表要被完全显示
select * from stu left join exam on stu.idexam.id;观察表格
学生表中的信息被完全被展示出来了就算在成绩表中找不到id与之匹配。同时因为找不到所以其对应的列全部以NULL进行填充而成绩表中的11号id因为无法匹配而被筛选掉了。
4.RIGHT JOIN右连接
RIGHT JOIN 返回右表的所有行并包括左表中匹配的行如果左表中没有匹配的行将返回 NULL 值以下是 RIGHT JOIN 语句的基本语法
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name table2.column_name;
右连接意味着查询结果将包含右表table2中的所有记录以及左表table1中与之匹配的记录。如果左表中没有匹配的记录则结果中对应的左表table1的列将包含NULL值。
详细解释如下
SELECT column1, column2, ...这部分指定了查询结果中需要包含的列。这里的column1, column2, ...应该替换为实际需要查询的列名这些列名可以来自table1或table2或者是通过计算得到的列比如使用函数或表达式。FROM table1这指定了查询中涉及的一个表即左表。尽管在RIGHT JOIN中左表不是结果集的主要来源主要来源是右表但它仍然是查询的一部分。RIGHT JOIN table2 ON table1.column_name table2.column_name这部分执行了一个右连接操作将table1和table2连接起来。ON子句指定了连接条件即两个表中哪些列的值需要相等才能认为记录是匹配的。这里的table1.column_name table2.column_name应该替换为实际的列名这些列名在两个表中都存在并且用于确定哪些记录是相关的。
右连接的一个常见用途是确保结果集中包含右表的所有记录同时尽可能地从左表中获取相关的匹配记录。如果左表中没有匹配的记录结果集中的相应列将显示为NULL。 然而需要注意的是在实际应用中RIGHT JOIN的使用相对较少因为大多数情况下可以通过调整查询的表顺序和使用LEFT JOIN来达到相同的目的。例如如果你有一个RIGHT JOIN查询你可以通过交换两个表的顺序即将table1和table2的位置互换并使用LEFT JOIN来达到相同的结果。 总的来说无论是LEFT JOIN还是RIGHT JOIN它们都是用于合并两个表的数据并根据指定的连接条件来选择哪些记录应该出现在结果集中。选择使用哪种连接类型取决于你想要保留哪个表的所有记录并尽可能地从另一个表中获取匹配的记录。
4.1.使用示例
我们继续沿用左连接的那个表
查询所有的成绩就算这个成绩没有学生与它对应也要将成绩信息显示出来
可以看出题目要求成绩表的信息要被完全展示而学生表中的信息如果实在不满足连接条件可以被忽略。
所以我们可以使用右连接保证右边的表要被完全显示(当然也可以调换表的顺序然后使用左外连接)
select * from stu right join exam on stu.idexam.id;观察表格
成绩表中的信息被完全被展示出来了就算在学生表中找不到id与之匹配。同时因为找不到所以其对应的列全部以NULL进行填充而学生表中的34号id因为无法匹配而被筛选掉了。
