连接查询、自关联、子查询

于 2024-09-04 22:58:19 发布
阅读量932 收藏 20
点赞数 7
分类专栏： 数据库 文章标签： 性能优化 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44038847/article/details/141906420
版权

连接查询

连接查询概述

  • 内连接：连接两个表时，取的是两个表中都存在的数据（取交集）
  • 左连接：连接两个表时，取的是左表中特有的数据，对于右表中不存在的数据，用null来填充。
  • 右连接：连接两个表时，取的时右表中特有的数据，对于左表中不存在的数据，用null来填充。

image-20240903090147845

image-20240903090308138

image-20240903090351846

image-20240903090447615

  • 数据准备
drop table if exists courses;
create table courses (
courseNo int(10) unsigned primary key auto_increment,
name varchar(10)
);
insert into courses values ('1', '数据库'), ('2', 'qtp'), ('3', 'linux'),
('4', '系统测试'), ('5', '单元测试'), ('6', '测试过程');
drop table if exists scores;
create table scores (
id int(10) unsigned primary key auto_increment,
courseNo int(10),
studentno varchar(10),
score tinyint(4)
);
insert into scores values ('1', '1', '001', '90'), ('2', '1', '002', '75'),
('3', '2', '002', '98'),('4', '3', '001', '86'),('5', '3', '003', '80'),
('6', '4', '004', '79'),('7', '5', '005', '96'),('8', '6', '006', '80');

image-20240903090855359

内连接

  • 语法格式

    select * from 表名1 inner join 表名2 on 表1.列=表2.列;
    • 查询的时两个表的交集的数据
    • 表1的列与表2的列一定是存在关联关系
    • 内连接连接时可以连接多个表

  • 案例

    -- 例1：查询学生信息及学生的成绩
select * from students inner join scores on students.studentNo=scores.studentNo;
-- students 起别名为stu,scores起别名为sc
select * from students as stu inner join scores as  sc on stu.studentNo=sc.studentNo;
-- 起别名可以不写as
select * from students  stu inner join scores   sc on stu.studentNo=sc.studentNo;
select * from students stu , scores sc where   stu.studentNo=sc.studentNo;

-- 例2：查询课程信息及课程的成绩
select * from courses cs  inner join scores  sc on cs.courseNo=sc.courseNo

-- 例3、查询王昭君的成绩，要求显示姓名，课程号，成绩
select stu.name,sc.courseNo,sc.score from students stu inner join scores sc on stu.studentNo=sc.studentNo where stu.name='王昭君'

    -- 1、查询学生信息及学生的课程对应的成绩
select * from students inner join scores on students.studentNo=scores.studentno
inner join courses on scores.courseNo=courses.courseNo
-- 2、查询所有学生的数据库成绩，要求显示姓名，课程名，成绩
select students.name,courses.name,scores.score from students 
inner join scores on students.studentNo=scores.studentno
inner join courses on scores.courseNo=courses.courseNo
-- 3、查询王昭君的数据库成绩，要求显示姓名，课程名，成绩
select students.name,courses.name,scores.score from students 
inner join scores on students.studentNo=scores.studentno
inner join courses on scores.courseNo=courses.courseNo where students.name='王昭君'

-- 4、 查询男生中最高成绩，要求显示姓名、课程名、成绩
select students.name,courses.name,scores.score from students 
inner join scores on students.studentNo=scores.studentno
inner join courses on scores.courseNo=courses.courseNo where students.sex='男' order by scores.score desc LIMIT 0,1 -- 等价于limit 1

左连接

  • 语法格式

    select * from 表1 left join 表2 on 表1.列=表2.列
    • 左连接查询的是左表特有的数据，对于右表中不存在的数据用null来填充

  • 案例

    -- 例1、查询所有学生的成绩，包括没有成绩的学生
select * from  students stu left join scores sc on stu.studentNo=sc.studentno
-- 例2、查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select * from  students stu left join scores sc on stu.studentNo=sc.studentno
left join courses cs on sc.courseNo=cs.courseNo

    image-20240903221732290

右连接

  • 语法格式

    select * from 表1 right join 表2 on 表1.列=表2.列
    • 右连接查询的是右表特有的数据，对于左表中不存在的数据用null来填充

  • 案例

    -- 例1、查询所有学生的成绩，包括没有成绩的学生
select * from scores sc  right join students stu on sc.studentno=stu.studentNo
-- 例2、查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select * from   scores sc right join courses cs on sc.courseNo=cs.courseNo
right join students stu on stu.studentNo=sc.studentno

    image-20240903222349435

自关联

自关联介绍

  • 自连接的应用场景
    • 省、市、区的信息，一般不会分开放在不同的表里面进行存储，而是放在同一个表当中。

image-20240903222739970

  • 数据准备
drop table if exists areas;
create table areas(aid int primary key, atitle varchar(20),pid int);
insert into areas values ('130000', '河北省', NULL), ('130100', '石家庄市','130000'),
('130400','邯郸市','130000'), ('130600', '保定市', '130000'),('130700','张家口市', '130000'),
('130800', '承德市', '130000'),('410000', '河南省', NULL), ('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'),('410500', '安阳市', '410000'),('410700', '新乡市', '410000'),
('410800', '焦作市', '410000'),('410101', '中原区', '410100'),('410102', '二七区', '410100'),
('410301', '洛龙区', '410300');

自关联实现

  • 要通过自关联进行查询时，当前自关联的表当中一定会存在两个相关联的字段。

  • 自关联要用别名

  • 语法格式

    select * from 表名 as 别名1 inner join 表名 as 别名2 on 别名1.列=别名2.列

  • 案例

    -- 查询出河南省的所有的市
select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='河南省';
-- 查询出郑州市所有的区
select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='郑州市';
-- 查询出河南省所有的市区信息
select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid 
left join areas as a3 on a2.pid=a3.pid where a1.atitle='河南省'

-- 1、查询河北省所有市的信息
select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='河北省'
-- 2、查询洛阳市所有区的信息
select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='洛阳市'

    image-20240904084046676

image-20240904084108578

子查询

  • 将一条SQL查询的语句嵌入在其他的SQL语句中，被嵌入的SQL语句称之为子查询，其他的SQL称之为主查询
    • 子查询select语句，要么是充当条件，要么充当数据源
    • 子查询语句是一条完整的select语句，且可以单独执行

子查询充当条件

-- 例1、查询王昭君的成绩，要求显示成绩(标量子查询)
select studentNo from students where name='王昭君';
select * from scores where studentNO=(select studentNo from students where name='王昭君');

-- 例2、查询18岁的学生的成绩，要求显示成绩（列子查询）
select studentNO from students where age=18;
select score from scores where studentNo in (select studentNO from students where age=18);

-- 例3、查询和王昭君同班、同龄的学生信息（行子查询）
select class,age from students where name='王昭君';
select * from students where (class,age)=(select class,age from students where name='王昭君')

子查询充当数据源

-- 例1、查询数据库和系统测试的课程成绩
select * from scores as sc inner join (select * from courses where name in ('数据库','系统测试'))
as co on sc.courseNo=co.courseNo
  • 子查询-特定关键字
    在这里插入图片描述
-- 例1、查询18岁的学生的成绩，要求显示成绩（列子查询）
select studentNo from students where age=18;
select score from scores where studentNo in (select studentNo from students where age=18);
--  =any 和in等价
select score from scores where studentNo =any(select studentNo from students where age=18);
-- some:是any的别称，很少用
select score from scores where studentNo =some(select studentNo from students where age=18);
-- !=all 和not in 等价
select score from scores where studentNo not in (select studentNo from students where age=18);
select score from scores where studentNo !=all (select studentNo from students where age=18);
  • 练习
-- 1、查询大于平均年龄的学生
-- 先查平均年龄
select avg(age) from students ;
select *  from students  where age>(select avg(age) from students);
-- 2、查询年龄在18-20之间的学生的成绩
-- 先查18-20之间的学生的学号
select studentNo from students where age between 18 and  20;
-- 子查询充当条件
select * from scores where studentNo in (select studentNo from students where age between 18 and  20);
-- 子查询充当数据源
select * from scores as sc inner join(select *  from students where age between 18 and  20) as stu
on sc.studentNo=stu.studentNo;

查询演练

  • 准备数据

    /*
**创建部门表
*/
drop table if exists departments;
create table departments (
deptid int(10) primary key,
deptname varchar(20) not null -- 部门名称
);
insert into departments values ('1001', '市场部');
insert into departments values ('1002', '测试部');
insert into departments values ('1003', '开发部');


/*
**创建员工表
*/
drop table if exists employees;
create table employees (
empid int(10) primary key,
empname varchar(20) not null, -- 姓名
sex varchar(4) default null, -- 性别
deptid int(20) default null, -- 部门编号
jobs varchar(20) default null, -- 岗位
politicalstatus varchar(20) default null, -- 政治面貌
leader int(10) default null
);
insert into employees values ('1', '王昭君', '女', '1003', '开发', '群众', '9');
insert into employees values ('2', '诸葛亮', '男', '1003', '开发经理', '群众', null);
insert into employees values ('3', '张飞', '男', '1002', '测试', '团员', '4');
insert into employees values ('4', '白起', '男', '1002', '测试经理', '党员', null);
insert into employees values ('5', '大乔', '女', '1002', '测试', '党员', '4');
insert into employees values ('6', '孙尚香', '女', '1001', '市场', '党员', '12');
insert into employees values ('7', '百里玄策', '男', '1001', '市场', '团员', '12');
insert into employees values ('8', '小乔', '女', '1002', '测试', '群众', '4');
insert into employees values ('9', '百里守约', '男', '1003', '开发', '党员', '9');
insert into employees values ('10', '妲己', '女', '1003', '开发', '团员', '9');
insert into employees values ('11', '李白', '男', '1002', '测试', '团员', '4');
insert into employees values ('12', '孙膑', '男', '1001', '市场经理', '党员', null);

/*
**创建工资表
*/
drop table if exists salary;
create table salary (
sid int(10) primary key,
empid int(10) not null,
salary int(10) not null -- 工资
);
insert into salary values ('1', '7', '2100');
insert into salary values ('2', '6', '2000');
insert into salary values ('3', '12', '5000');
insert into salary values ('4', '9', '1999');
insert into salary values ('5', '10', '1900');
insert into salary values ('6', '1', '3000');
insert into salary values ('7', '2', '5500');
insert into salary values ('8', '5', '2000');
insert into salary values ('9', '3', '1500');
insert into salary values ('10', '8', '4000');
insert into salary values ('11', '11', '2600');
insert into salary values ('12', '4', '5300');

在这里插入图片描述

  • 练习

    -- 1、列出总人数大于4的部门号和总人数（要统计所有部门的人数，需要使用分组，同时也要使用聚合函数）
select deptid,count(*) from employees GROUP BY deptid having count(*)>4

-- 2、列出开发部和测试部的职工号、姓名
select deptid from departments where deptname in ('开发部','测试部')
select empid,empname from employees where deptid in (
select deptid from departments where deptname in ('开发部','测试部'))

-- 3、求出各部门党员的人数，要求显示部门名称
select * from employees  as emp inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员';

select dep.deptname from employees as emp 
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员'

select dep.deptname from employees as emp 
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus='党员' GROUP BY emp.deptid;

-- 4、列出市场部的所有女职工的姓名和政治面貌
select * from employees as emp 
inner join departments as dep on emp.deptid=dep.deptid
where emp.sex='女' and dep.deptname='市场部'

select emp.empname, emp.politicalstatus from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.sex='女' and dep.deptname='市场部';
select emp.empname, dep.deptname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid;

-- 6、显示各部门名和该部门的职工平均工资。
select dep.deptname,avg(sa.salary) from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid;

-- 7、显示工资最高的前3名职工的职工号和姓名。
select emp.empname, dep.deptname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
order by sa.salary desc limit 3 -- limit 3 等价于 limit 0, 3

-- 8、列出工资在1000－2000之间的所有职工姓名。
select emp.empname, dep.deptname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
where sa.salary between 1000 and 2000;

-- 9、列出工资比王昭君高的员工。(首先查询王昭君的工资)
-- 查询王昭君的工资
select sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where emp.empname='王昭君'
select emp.empname, sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where sa.salary > (
select sa.salary from employees as empinner join salary as sa on sa.empid=emp.empid
where emp.empname='王昭君'
);

-- 10、列出每个部门中工资小于本部门平均工资的员工信息。(首先查询出每个部门的平均工资)
-- 查询出每个部门的平均工资
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
select emp.deptid, emp.empname, sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
inner join (
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
) as c on emp.deptid=c.deptid
where sa.salary < c.avg_salary;
粥小铭同学
关注
专栏目录
mysql中的自连接查询
m0_60745607的博客
01-07 5877
一、什么是自连接 自连接查询，顾名思义，就是自己和自己比较。 例如：分数表中，科目1有学生考试不及格，不及格的学生补考了，这个的话，同一个学生，同一个科目出现了2笔成绩，如果查询出成绩高的那一笔记录的话，需要分数表自己和自己比较。 二、数据准备 create database stuMS default charset=’utf8′; use stuMS; — 表1：学生信息表 Students create table Students( Scode int not null PRIM..
MySQL查询之自连接查询
qq_40662239的博客
03-22 1157
自连接查询属于联表查询中特殊的一种 联表查询是两张表采用7中join方式 依据两张表不同字段相同数据进行数据查询 那么一张表如何进行联表查询呢？ 就是采用自连接的查询方式 自连接说白了就是把一张表分成两张 然后又采用联表查询的方式进行操作 话不多说，直接看表 - - 建表 `obj` CREATE TABLE IF NOT EXISTS `obj`( `id` INT (2) NOT NULL ...
多表查询，自连接，子查询
weixin_52571040的博客
08-24 409
mysql5 复合查询
MySQL 自关联基础操作
热门推荐
hubingzhong的博客
07-29 1万+
自关联 设计省信息的表结构provinces id ptitle 设计市信息的表结构citys id ctitle proid citys表的proid表示城市所属的省，对应着provinces表的id值 问题：能不能将两个表合成一张表呢？ 思考：观察两张表发现，citys表比provinces表多一个列proid，其它列的类型都是一样的 意义：存储的都是地区信...
数据库的自连接查询
玄昌盛不会编程
11-20 914
之前介绍了 数据库基本常识的简介及mysql安装 结构化查询语言sql 数据库表介绍 数据库的数据类型 数据操作语言DML——数据的增删改查 数据库对象操作语言DDL——修改、删除数据库表等对象 数据库的完整性约束 数据库查询语言——DQL 数据库中的函数 数据库的group by分组和having筛选 select语句总结（DQL） 数据库的多表查询 接下来说一下自连接查询，自连接就是自己连接自己。 举个简单的例子，查询员工的编号、姓名、上级编号、上级姓名，其中这些信息都在一个表中。 select
连接查询-自连接
缘 源 园
03-28 1608
1. 自连接查询 左表和右表是同一个表，根据连接查询条件查询两个表中的数据。 区域表效果图 例1：查询省的名称为“山西省”的所有城市 创建areas表: create table areas( id varchar(30) not null primary key, title varchar(30), pid varchar(30) ); 执行sql文件给areas表导入数据: source areas.sql; 说明: sourc..
《自连接与子查询》
weixin_44540479的博客
04-08 686
自连接与子查询 开发工具与关键技术：PL SQL 、Oracle SQL 作者：郑凯丰 撰写时间：2019年3月26日 自连接：自连接就是自己和自己连接来查询数据！ 自连接代码解析： 其实这道题关键在于自连接这里，用自己与自己链接然后再排除自己的那一条数据 （自己！=自己）；然后再用（工资=工资）就可以查询出工资相同的人！ 结果： 子查询： 代码： 1. 最普通的子查询：但是子查询，它只能查询...
mysql实现多表关联统计(子查询统计)示例
09-09
在这个示例中，我们将探讨如何利用子查询来处理多表关联统计的问题，以及解决数据重复的问题。 首先，我们有四个表：`book`（书籍表），`orders`（订单表），`reward_log`（打赏表）和`consume_log`（消费表）。`...
mysql关联子查询的一种优化方法分析
09-10
在数据库查询优化中，关联子查询是一种常见的操作，但它的性能往往不如预期。当涉及到大型数据表时，关联子查询可能导致显著的延迟。本文将深入探讨MySQL关联子查询的优化方法，通过实例来展示如何改进查询效率。 ...
Mysql自连接查询实例详解
09-09
MySQL自连接查询是一种特殊的连接查询方式，它将同一个表视为两个不同的实体进行连接，从而实现对自身数据的关联。在数据库设计中，特别是在处理层级结构数据时，自连接查询非常有用。例如，上述实例中提到的产品...
四、MySQL分页查询 + 子查询复习 学习笔记 （复习连接查询相关内容 详解）
12-14
本篇学习笔记主要复习了分页查询及其与子查询的结合应用，同时也涵盖了连接查询的相关内容。 分页查询的应用场景是当数据量过大，无法一次性显示在页面上时，通过分页的方式逐页加载数据。其基本语法结构如下： ``...
详解MySQL数据库--多表查询--内连接，外连接，子查询，相关子查询
09-09
本文将深入探讨MySQL数据库中的多表查询，包括内连接、外连接和子查询，这些都是数据库操作中的重要概念。 首先，多表查询允许我们从多个相关联的表格中获取信息。例如，假设我们有两个表格，一个是`customers`，...
表自关联查询
tan_1208815066的专栏
07-08 886
select a.* from t_sales_orga a start with a.id  =1203 connect by prior a.id =a.parent_id
mysql 自连接 子查询_自连接和子查询
weixin_39673471的博客
01-19 656
SQL SELECT(复杂查询)之 自连接 & 子查询 解析一、自连接概念：把一张表 当做多个表使用语法：select ...from 表 a(别名)join 表 b(别名)on a.字段=b.字段注意：自连接也可以是 内连接也可以是外连接自己与一张与自己完全一样的从表建立关系 进行连接查询以下举例所用表employees表:字段如下employee_id, first_name, las...
十四、连接查询——自连接查询
最新发布
qq_62899128的博客
02-01 412
左表和右表是同一个表，根据连接查询条件查询俩个表中的数据。
自关联
weixin_30240349的博客
03-07 116
设计省信息的表结构provinces id ptitle 设计市信息的表结构citys id ctitle proid citys表的proid表示城市所属的省，对应着provinces表的id值 问题： 能不能将两个表合成一张表呢？ 思考： 观察两张表发现，citys表比provinces表多一个列proid，其它列的...
mysql自连接查询_Mysql自连接查询实例详解
weixin_33277412的博客
01-18 1355
本文实例讲述了Mysql自连接查询。分享给大家供大家参考，具体如下：自连接查询假想以下场景：某一电商网站想要对站内产品做层级分类，一个类别下面有若干子类，子类下面也会有别的子类。例如数码产品这个类别下面有笔记本，台式机，智能手机等；笔记本，台式机，智能手机又可以按照品牌分类；品牌又可以按照价格分类，等等。也许这些分类会达到一个很深的层次，呈现一种树状的结构。那么这些数据要怎么在数据库中表示呢？我们...
自连接查询
Joash_Wu的博客
09-21 242
自连接查询
Oracle关联子查询详解与示例
优化关联子查询通常是数据库性能调优的重要部分，可能涉及重写查询、创建索引或使用连接操作代替。 总结来说，理解和熟练运用子查询，特别是关联子查询，对于提升ORACLE SQL查询的效率和灵活性至关重要。通过深入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值