首页 > 数据库 >MySQL-6.表的高级查询（多表查询、子查询、表复制、合并查询、表外连接）

MySQL-6.表的高级查询（多表查询、子查询、表复制、合并查询、表外连接）

时间：2024-04-10 12:30:50浏览次数：11  
标签：多表 sal 表外 查询 emp deptno WHERE SELECT

6.1 多表查询

  • 基于两个或以上表的查询，默认从表1取出一行，与表2的每一行组合，返回的记录数为表1×表2，默认返回的结果为笛卡尔集，需写出正确的WHERE条件进行筛选。
  • 多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集。
  • 指定显示某个表的列：表.列
# 显示雇员名,雇员工资及所在部门的名字
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno;
# 显示部门号为 10 的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno AND emp.deptno = 10
# 显示各个员工的姓名，工资，及其工资的级别
# 思路: 姓名，工资来自emp,工资级别salgrade
# 先写一个简单，然后加入过滤条件... 
select ename, sal, grade
    from emp , salgrade
    where sal between losal and hisal;
  • 自连接：在同一张表的连续查询，即将该表看做两张表；
                        需要给表取别名：表名 表别名；
                        列名不明确可以指定列的别名。
# 显示公司员工名字和他的上级的名字
# 员工名字在 emp, 上级的名字在 emp
# 员工和上级是通过 emp 表的 mgr 列关联
SELECT worker.ename AS '职员名' , boss.ename AS '上级名' 
    FROM emp worker, emp boss
    WHERE worker.mgr = boss.empno;

6.2 表子查询（嵌套查询）

1. 子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询。
2. 单行子查询是指只返回一行数据的子查询语句。
3. 多行子查询指返回多行数据的子查询，使用关键字 in。
# 显示与 SMITH 同一部门的所有员工
# 先查询 SMITH 的部门号
SELECT deptno FROM emp WHERE ename = 'SMITH';
# 把上面的 select 语句当做一个子查询来使用
SELECT * FROM emp
    WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
# 查询和部门10的工作相同的雇员名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员
# 先查询部门10有哪些工作
SELECT DISTINCT job FROM emp WHERE depno = 10;
# 把上面查询的结果当做子查询使用
SELECT ename, job, sal, depno
    FROM emp
    WHERE job in (SELECT DISTINCT job FROM emp WHERE depno = 10)
        AND depno <> 10;
4. 可以将子查询当做临时表使用：在FROM中使用
# 查询 ecshop 中各个类别中，价格最高的商品
# 查询商品表
# 先得到各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
SELECT cat_id , MAX(shop_price) as max_price
    FROM ecs_goods
    GROUP BY cat_id
# 把子查询当做一张临时表可以解决很多复杂的查询，再给这个临时表去个别名temp
SELECT *
    FROM(
        SELECT cat_id , MAX(shop_price) as max_price
            FROM ecs_goods
            GROUP BY cat_id
        )temp, ecs_goods
    where temp.cat_id = ecs_goods.cat_id
        and temp.max_price = ecs_goods.shop_price;
# 查找每个部门工资高于本部门平均工资的人的资料
# 先得到每个部门的部门号和对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
    FROM emp 
    GROUP BY deptno;
# 把上面的结果当做子查询,临时表,和 emp 进行多表查询
SELECT ename, sal, temp.avg_sal, emp.deptno
    FROM emp, (
        SELECT deptno, AVG(sal) AS avg_sal
            FROM emp
            GROUP BY deptno
        ) temp
    WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal;
5. 在多行子查询中使用 all 操作符：所有

all和any的区别：all强调所有，any则是其中之一即可。

# 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
# 先查询部门30员工的工资
SELECT sal FROM emp WHERE deptno = 30;
# 比部门30的所有员工工资高——all关键字
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

# 也可以使用MAX()
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
# 显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > any(SELECT sal FROM emp WHERE deptno = 30);

# 也可以使用MIN
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30);
6. 多列子查询：查询返回多个列的子查询

# 查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
# 得到 allen 的部门和岗位
SELECT depno, job FROM emp WHERE `name` = "allen";

SELECT * 
    FROM emp
    WHERE (depno, job) = (
        SELECT depno, job FROM emp
            WHERE `name` = "allen") AND name != "allen";
# 查询和宋江数学，英语，语文成绩完全相同的学生
SELECT `name`
    FROM student
    WHERE (math, english, chinese) = (
        SELECT math, english, chinese
            FROM student
            WHERE `name` = '宋江' );

6.3 表复制和去重

也称自我复制数据(蠕虫复制)，有时需对sql语句测试效率需要大量数据，用此进行自我复制。

# 先把 emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
    (id, `name`, sal, job,deptno)
    SELECT empno, ename, sal, job, deptno FROM emp;

# 自我复制
INSERT INTO my_tab01
    SELECT * FROM my_tab01;
# 删除my_tab02表的重复记录
# 思路
# (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
# (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
# (3) 清除掉 my_tab02 记录
# (4) 把 my_tmp 表的记录复制到 my_tab02
# (5) drop 掉 临时表 my_tmp

create table my_tmp like my_tab02;
insert into my_tmp select distinct * from my_tab02;
delete from my_tab02;
insert into my_tab02 select * from my_tmp;
drop table my_tmp;

6.4 合并查询

        合并多个SELECT语句的查询结果，可以使用集合操作符号：union all、union，直接加在两句之间。
  • union all：对两个结果集取并集，不会去掉重复行
  • union：取并集，去掉重复行。
SELECT ename,sal,job FROM emp WHERE sal>2500;
SELECT ename,sal,job FROM emp WHERE job='MANAGER';

SELECT ename,sal,job FROM emp WHERE sal>2500
    UNION ALL
        SELECT ename,sal,job FROM emp WHERE job='MANAGER';

SELECT ename,sal,job FROM emp WHERE sal>2500
    UNION
        SELECT ename,sal,job FROM emp WHERE job='MANAGER';

6.5 表外连接

外连接可以弥补多表查询无法做到的操作。
多表查询利用 where 子句对两张表或者多张表，形成的笛卡尔集进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示（即弥补这一部分）。
# 列出部门名称和这些部门的员工名称和工作，同时显示出没有员工的部门
# 多表查询：无法同时显示出没有员工的部门
SELECT dname, ename, job
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
    ORDER BY dname;

# 右连接：显示部门的全部信息，把部门做右表
SELECT dname, ename, job
    FROM emp RIGHT JOIN dept
    ON dept.deptno = emp.deptno
# 左连接：把部门做左表
SELECT dname, ename, job
    FROM dept LEFT JOIN emp
    ON dept.deptno = emp.deptno
  • 左外连接：如果左侧的表完全显示我们就说是左外连接
        select ... from 表1 left join 表2 on 条件;               (表1为左表）
  • 右外连接：如果右侧的表完全显示我们就说是右外连接
        select ... from 表1 right join 表2 on 条件;
# 外连接
# 学生表stu和成绩表exam
# 左连接：显示所有人的成绩，如果没有成绩，也要显示该人的姓名和 id 号,成绩显示为空
# 如果使用多表查询，只能显示有成绩的学生，没有成绩的无法显示
SELECT `name`, stu.id, grade
    FROM stu, exam
    WHERE stu.id = exam.id;

# 左连接：
SELECT `name`, stu.id, grade
    FROM stu LEFT JOIN exam
    ON stu.id = exam.id;

# 右连接：显示所有成绩，如果没有名字匹配，显示空
SELECT `name`, stu.id, grade
    FROM stu RIGHT JOIN exam
    ON stu.id = exam.id;
 参考：韩顺平mysql

标签：多表,sal,表外,查询,emp,deptno,WHERE,SELECT
From： https://blog.csdn.net/weixin_46356448/article/details/137482278

相关文章

  • 20240410sql查询学习_1
    需求：查看合同信息表里面有多少合同编号是重复的SELECT合同编号,COUNT(*)FROM合同信息表GROUPBY合同编号HAVINGCOUNT(*)>1;结果：数量太多需求：列数太多，怎样看具体有多少个SELECTCOUNT(*)AStotalFROM(SELECT合同编号,COUNT(*)ASduplicate_count......
  • mysql中将where条件中过滤掉的group by分组后查询无数据的行进行补0
    背景mysql经常会用到groupBy来进行分组查询，但也经常会遇到一个问题，就是当有where条件时，被where条件过滤的数据不显示了。例如我有一组数据：我想查询创建时间大于某一范围的spu的分组下的sku的数量正常的sql查出的话，假如不存在相关记录SELECTproduct_id,count(*)countF......
  • MongoDB的一次奇妙查询
    这段时间遇到了一个业务需求，我有一些关于书籍的文档数据存储在MongoDB数据中，然后在修复数据之后，需要用一个查询去验证更新是否成功。书籍数据大概长这个样子：{"books":[{"name":"UbuntuMeta","sku_id":"101","price":30.5},{&q......
  • 查询题目，日期函数
    老王是荆职百获食堂的采购员，每天买白菜50斤，土豆30斤，茄子30斤，豆角50斤，采购完都会记录到下面的Vegetable表中，请你用学到的Oracle知识帮帮老王算算帐。编号白菜单价土豆单价茄子单价豆角单价日期0010.350.50.51.22017/11/100020.4......
  • 多表查询
    一语句查询1， 在SCOTT模式下，检索emp表的指定列（empno,ename,job）2， 检索emp表的sal列，把其值调整为原来的1.53， 在emp表中，使用like关键字匹配以字母s开头的员工名称4， 查询emp表中没有奖金的员工信息5， 在emp表中，查询既不是最高工资，也不是最低工资的员工信息6， ......
  • 查询
    用户名：scott密码：tiger三种方法可以打开sqlplus1win+R打开窗口输入sqlplus2快捷方式3在oracle主菜单下打开select*fromemp;select*fromdept;select1+1fromdual;select99*99fromdual;select99*99fromemp;selectename姓名......
  • 查询下属
    win+R键sqlplus1用户名：scott密码：tigerselect*fromemp;select*fromdept;selectename,sal,commfromemp;selectename,sal+nvl(comm,0)fromemp;selectename,12*(sal+nvl(comm,0))年薪fromemp;1selecte......
  • 查询表
    --1切换到master数据库中usemastergo--2判断cpms数据库是否存在，若存在则删除ifexists(select*fromsys.sysdatabaseswherename='cpms')dropdatabasecpmsgo--3创建cpms数据库createdatabasecpmson(name=cpms_data,filename='c:\cpms\cpms_data.mdf')log......
  • 关于查询优化的一些总结
    一、程序优化热点数据使用缓存数据库读写分离二、数据库方面的优化1、数据库设计优化如果单表数据量过大，可以根据业务来做分表数据库表可以做一些字段冗余，可以减少连表查询，提升查询效率2、Sql语句优化2.1.首先定位慢查询开启慢查询日志mysqlslow_query_log:是否开启慢查询sl......
  • 【数据结构 | 并查集】维护元素分组信息，支持高效合并集合、查询元素所在集合
    文章目录并查集概述引入并查集的实现存储方式Union-Find抽象基类两种实现思路基本实现基于QuickFind思路基于QuickUnion思路优化基于size的优化基于rank的优化find优化路径压缩路径分裂路径减半总结并查集概述并查集（DisjointSetUnion，简称并查集），也叫......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99