热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

DQL(数据查询语言)之多表查询

1、 多表查询(连接查询) 之前的一些查询都是在单表上进行的,使用起来比较简单,但是在实际情况中,大多数查询都是几张表联合起来查询
1、 多表查询(连接查询)

之前的一些查询都是在单表上进行的,使用起来比较简单,但是在实际情况中,大多数查询都是几张表联合起来查询的。把所有数据放在同一张表不好吗?当然不好,当把所有的数据都放到同一张表中,那么可想而知,那张表中的数据量得多大,而且其中有些字段的值也相同,都放在一张表会造成数据冗余,浪费存储空间,更重要的是不安全,万一这个表被删除了,那么数据就彻底没有了,会造成很大的损失,所以一般都会把数据放在不同的表中,这个时候可能就得使用外键了。

2、笛卡尔积现象

​ 在进行多表连接时会产生笛卡尔积现象,所谓笛卡尔积现象是指两个集合XY的笛卡尔积(Cartesian product),又称直积,表示为X×Y,即第一张表中的每个字段都分别与第二张表中的每个字段相组合 。
在这里插入图片描述
这样的结果明显是错误的。

案例:查找EMP表中的员工名字和DEPT表中的部门名称。


mysql> select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| WARD | ACCOUNTING |
| WARD | RESEARCH |
| WARD | SALES |
| WARD | OPERATIONS |
| JONES | ACCOUNTING |
| JONES | RESEARCH |
| JONES | SALES |
| JONES | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH |
| MARTIN | SALES |
| MARTIN | OPERATIONS |
| BLAKE | ACCOUNTING |
| BLAKE | RESEARCH |
| BLAKE | SALES |
| BLAKE | OPERATIONS |
| CLARK | ACCOUNTING |
| CLARK | RESEARCH |
| CLARK | SALES |
| CLARK | OPERATIONS |
| SCOTT | ACCOUNTING |
| SCOTT | RESEARCH |
| SCOTT | SALES |
| SCOTT | OPERATIONS |
| KING | ACCOUNTING |
| KING | RESEARCH |
| KING | SALES |
| KING | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH |
| TURNER | SALES |
| TURNER | OPERATIONS |
| ADAMS | ACCOUNTING |
| ADAMS | RESEARCH |
| ADAMS | SALES |
| ADAMS | OPERATIONS |
| JAMES | ACCOUNTING |
| JAMES | RESEARCH |
| JAMES | SALES |
| JAMES | OPERATIONS |
| FORD | ACCOUNTING |
| FORD | RESEARCH |
| FORD | SALES |
| FORD | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set

这样就产生了笛卡尔积现象,因为没有指明连接的条件,此时可以使用where限制条件来消除笛卡尔积现象。但是比较的次数不会减少,只是最终显示的结果会减少,索引查询效率没有得到提高,如果想提升可以使用索引等。

mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set此时就要用到外键的约束了。

3、内连接查询

​ 内连接主要分为两大类等值连接和非等值连接,在这两种连接中又分为隐式连接(SQL92语法)和显示连接(SQL99语法),显示连接一般要是用关键字 inner join on来表明,而隐式连接不用。内连接表与表之间是平等关系,没有需要主次之分,其中等值连接的条件是等量关系,而非等值连接相当于between … and…
在这里插入图片描述
具体语法:
1.隐式连接(SQL92语法)

SELECT table_name1.column_name,table_name2.column_nameFROM table_name1,table_name2WHERE table_name1.column_name = table_name2.column_nameGROUP BY column_name;HVING column_name;ORDER BY column_name ASC/DESC

2.显式连接(SQL99语法)

SELECT table_name1.column_name,table_name2.column_nameFROM table_name1inner join table_name2on table_name1.column_name = table_name2.column_name WHERE 表与表的连接条件GROUP BY column_name;HVING column_name;ORDER BY column_name ASC/DESC

3.1 等值连接

等值连接: 连接的条件是等量关系。

案例1:查找EMP表中薪水在2500到3000的员工信息,并显示所属的部门名称。


隐式连接即SQL92语法
mysql> select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and e.sal between 2500 and 3000;
+-------+------+----------+
| ename | sal | dname |
+-------+------+----------+
| JONES | 2975 | RESEARCH |
| BLAKE | 2850 | SALES |
| SCOTT | 3000 | RESEARCH |
| FORD | 3000 | RESEARCH |
+-------+------+----------+
4 rows in set显式连接即SQL99语法,把条件分开,结构更加清晰, inner可以省略,但是带着可读性更好,查询效率更高
mysql> select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal between 2500 and 3000;
+-------+------+----------+
| ename | sal | dname |
+-------+------+----------+
| JONES | 2975 | RESEARCH |
| BLAKE | 2850 | SALES |
| SCOTT | 3000 | RESEARCH |
| FORD | 3000 | RESEARCH |
+-------+------+----------+
4 rows in set

4、外连接查询

​ 外连接表与表之间不平等,有一张主表,一张副表,副表的查询不能影响主表的查询,如果副表没有匹配的数据自动变成NULL

​ 主要分为左外连接、右外连接、全连接

4.1 左外连接(left outer join)

1.左外连接(left outer join): (任何一个左外连接都有一个右外连接) ,此时主表是左边的表即在from语句后的表,副表为join后面的表,前面的表起决定性作用。
在这里插入图片描述
具体语法:

SELECT table_name1.column_name,table_name2.column_nameFROM table_name1left outer join table_name2on table_name1.column_name = table_name2.column_name WHERE 条件通俗语法表示:select 查询字段 from1 leftouterjoin2 on 连接条件 where 条件

案例1:显示员工信息,并显示所属的部门名称。


mysql> select e.ename, e.sal, d.dname from dept d left outer join emp e on e.deptno=d.deptno;
+--------+------+------------+
| ename | sal | dname |
+--------+------+------------+
| SMITH | 800 | RESEARCH |
| ALLEN | 1600 | SALES |
| WARD | 1250 | SALES |
| JONES | 2975 | RESEARCH |
| MARTIN | 1250 | SALES |
| BLAKE | 2850 | SALES |
| CLARK | 2450 | ACCOUNTING |
| SCOTT | 3000 | RESEARCH |
| KING | 5000 | ACCOUNTING |
| TURNER | 1500 | SALES |
| ADAMS | 1100 | RESEARCH |
| JAMES | 950 | SALES |
| FORD | 3000 | RESEARCH |
| MILLER | 1300 | ACCOUNTING |
| NULL | NULL | OPERATIONS |
+--------+------+------------+
15 rows in set

案例2:显示员工信息,并显示所属的部门名称。


mysql> select e.ename, e.sal, d.dname from emp e right outer join dept d on e.deptno=d.deptno;
+--------+------+------------+
| ename | sal | dname |
+--------+------+------------+
| SMITH | 800 | RESEARCH |
| ALLEN | 1600 | SALES |
| WARD | 1250 | SALES |
| JONES | 2975 | RESEARCH |
| MARTIN | 1250 | SALES |
| BLAKE | 2850 | SALES |
| CLARK | 2450 | ACCOUNTING |
| SCOTT | 3000 | RESEARCH |
| KING | 5000 | ACCOUNTING |
| TURNER | 1500 | SALES |
| ADAMS | 1100 | RESEARCH |
| JAMES | 950 | SALES |
| FORD | 3000 | RESEARCH |
| MILLER | 1300 | ACCOUNTING |
| NULL | NULL | OPERATIONS |
+--------+------+------------+
15 rows in set

4.2 右外连接(right outer join)

2.右外连接(right out join):(任何一个右外连接都有一个左外连接),此时主表是有边的表即在join语句后的表,副表为from后面的表,join后的表起决定性作用。
在这里插入图片描述
具体语法:

SELECT table_name1.column_name,table_name2.column_nameFROM table_name1right outer join table_name2on table_name1.column_name = table_name2.column_name WHERE 条件通俗语法表示:select 查询字段 from1 rightouterjoin2 on 连接条件 where 条件

注: 区分左外连接还是右外连接主要看的是起主要作用的那张表在那边,也就是在左边还是右边


4.3 全连接(full outer join)

3.全连接(full outer join): 即有左表又有右表,左边不能影响右表的查询,右边不能影响左表的查询,一般不会使用。

SELECT table_name1.column_name,table_name2.column_nameFROM table_name1full outer join table_name2on table_name1.column_name = table_name2.column_name WHERE 条件通俗语法表示:select 查询字段 from1 rightouterjoin2 on 连接条件 where 条件

5 、自然连接

自连接: 一张表看成两张表,相当于等值连接。

mysql> select e1.ename '第一张表',e2.ename '第二张表' from emp e1 ,emp e2 where e1.ename = e2.ename;
+----------+----------+
| 第一张表 | 第二张表 |
+----------+----------+
| SMITH | SMITH |
| ALLEN | ALLEN |
| WARD | WARD |
| JONES | JONES |
| MARTIN | MARTIN |
| BLAKE | BLAKE |
| CLARK | CLARK |
| SCOTT | SCOTT |
| KING | KING |
| TURNER | TURNER |
| ADAMS | ADAMS |
| JAMES | JAMES |
| FORD | FORD |
| MILLER | MILLER |
+----------+----------+
14 rows in set

6、Union合并查询

​ union操作符用于连接两个以上的 DQL语句的查询结果组合到一个结果集合中。多个 selcet语句会删除重复的数据。
集体语法:

SELECT column_name
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT] DISTINCT 可选 删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据.ALL: 可选,返回所有结果集,包含重复数据。
SELECT column_name
FROM tables
[WHERE conditions];

案例1:在EMP表中查询job为MANAGER和包含SALESMAN的员工,返回员工的名字和薪资。


原始sql语句:
mysql> select ename,sal from emp where job in('MANAGER','SALESMAN');
+--------+------+
| ename | sal |
+--------+------+
| ALLEN | 1600 |
| WARD | 1250 |
| JONES | 2975 |
| MARTIN | 1250 |
| BLAKE | 2850 |
| CLARK | 2450 |
| TURNER | 1500 |
+--------+------+
7 rows in set使用union
mysql> select ename,sal from emp where job='MANAGER'
union all
select ename,sal from emp where job='SALESMAN';+--------+------+
| ename | sal |
+--------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| ALLEN | 1600 |
| WARD | 1250 |
| MARTIN | 1250 |
| TURNER | 1500 |
+--------+------+
7 rows in set

在使用union连接两个表时,两个表中的字段必须一样,否则会报错

mysql> select * from emp union select * from dept;
1222 - The used SELECT statements have a different number of columns

------------------------------------------------------------------------------END-----------------------------------------------------------------------------
------------------------------------------------------------>>>>>>>>>>加载完成<<<<<<<<<------------------------------------------------------


推荐阅读
  • 本文详细介绍了MySQL数据库的基础语法与核心操作,涵盖从基础概念到具体应用的多个方面。首先,文章从基础知识入手,逐步深入到创建和修改数据表的操作。接着,详细讲解了如何进行数据的插入、更新与删除。在查询部分,不仅介绍了DISTINCT和LIMIT的使用方法,还探讨了排序、过滤和通配符的应用。此外,文章还涵盖了计算字段以及多种函数的使用,包括文本处理、日期和时间处理及数值处理等。通过这些内容,读者可以全面掌握MySQL数据库的核心操作技巧。 ... [详细]
  • SQL 连接详解与应用
    本文详细介绍了 SQL 连接的概念、分类及实际应用,包括内连接、外连接、自连接等,并提供了丰富的示例代码。 ... [详细]
  • 本文介绍了如何使用Flume从Linux文件系统收集日志并存储到HDFS,然后通过MapReduce清洗数据,使用Hive进行数据分析,并最终通过Sqoop将结果导出到MySQL数据库。 ... [详细]
  • DAO(Data Access Object)模式是一种用于抽象和封装所有对数据库或其他持久化机制访问的方法,它通过提供一个统一的接口来隐藏底层数据访问的复杂性。 ... [详细]
  • 本文总结了在SQL Server数据库中编写和优化存储过程的经验和技巧,旨在帮助数据库开发人员提升存储过程的性能和可维护性。 ... [详细]
  • php更新数据库字段的函数是,php更新数据库字段的函数是 ... [详细]
  • 通过使用Sqoop导入工具,可以精确控制并高效地将表数据的特定子集导入到HDFS中。具体而言,可以通过在导入命令中添加WHERE子句来指定所需的数据范围,从而在数据库服务器上执行相应的SQL查询,并将查询结果高效地存储到HDFS中。这种方法不仅提高了数据导入的灵活性,还确保了数据的准确性和完整性。 ... [详细]
  • MySQL Decimal 类型的最大值解析及其在数据处理中的应用艺术
    在关系型数据库中,表的设计与SQL语句的编写对性能的影响至关重要,甚至可占到90%以上。本文将重点探讨MySQL中Decimal类型的最大值及其在数据处理中的应用技巧,通过实例分析和优化建议,帮助读者深入理解并掌握这一重要知识点。 ... [详细]
  • 您的数据库配置是否安全?DBSAT工具助您一臂之力!
    本文探讨了Oracle提供的免费工具DBSAT,该工具能够有效协助用户检测和优化数据库配置的安全性。通过全面的分析和报告,DBSAT帮助用户识别潜在的安全漏洞,并提供针对性的改进建议,确保数据库系统的稳定性和安全性。 ... [详细]
  • 本文介绍了在 MySQL 中如何使用正则表达式来提高查询效率,通过具体示例展示了如何筛选包含中文字符的记录,并详细解释了正则表达式的各种特殊字符和结构。 ... [详细]
  • 本文介绍如何在将数据库从服务器复制到本地时,处理因外键约束导致的数据插入失败问题。 ... [详细]
  • 本文介绍了在 Spring Boot 中使用 JPA 进行数据删除操作时遇到的 SQL 错误及其解决方法。错误表现为:删除操作失败,原因是无法打开 JPA EntityManager 以进行事务处理。 ... [详细]
  • importpymysql#一、直接连接mysql数据库'''coonpymysql.connect(host'192.168.*.*',u ... [详细]
  • 本文讨论了在进行 MySQL 数据迁移过程中遇到的所有 .frm 文件报错的问题,并提供了详细的解决方案和建议。 ... [详细]
  • 本文是Java并发编程系列的开篇之作,将详细解析Java 1.5及以上版本中提供的并发工具。文章假设读者已经具备同步和易失性关键字的基本知识,重点介绍信号量机制的内部工作原理及其在实际开发中的应用。 ... [详细]
author-avatar
游戏fans2012
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有