首页 > 其他分享 >34. 过滤条件、多表查询、子查询

34. 过滤条件、多表查询、子查询

时间：2024-10-28 23:11:08浏览次数：4  
标签：多表 name dep 34 emp 查询 id select

1. 过滤条件

1.1 过滤条件之having

[1]概念

HAVING 子句用于对分组后的结果进行过滤。它通常与 GROUP BY 子句一起使用，在 SELECT 语句的聚合函数（如 SUM(), AVG(), COUNT(), MAX(), MIN() 等）之后应用条件。

HAVING 子句与 WHERE 子句类似，但 HAVING 适用于分组后的数据，而 WHERE 适用于原始数据。

[2]应用

分组之后的数据再进行过滤，不能用where，只能用having

案例：员工按部门进行分组，筛选出30岁以上员工的工资，筛选出30岁以上员工平均工资大于10000的部门

先筛选出30岁以上的员工数据，再对数据按部门进行分组，分组后用聚合函数avg求平均值

select post,avg(salary) from emp where age>30 group by post;

在以上分组后的基础上再筛选出平均工资大于10000的部门

方法一：分组之后的数据用where再筛选（报错）

select post,avg(salary) from emp where age>30 group by post where avg(salary)>10000;

方法二：分组之后的数据用having再筛选 

select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;

1.2 过滤条件之distinct

去重，使用SELECT语句查询数据时，distinct可以从查询结果中删除重复的行，并仅返回唯一的记录。

语法：select distinct 列1,列2... from 表名

主键不重复，对主键去重无意义。

select distinct id,age from emp;

对post去重

select distinct post from emp;

1.3 过滤条件之order by

[1]概念

语法：select */列名 from 表名 order by 列名 asc/desc;

asc：升序（asc/desc不写时默认也为升序）

desc：降序

[2]代码应用

(1)查询所有数据，按工资的升序排列

select * from emp order by salary;

(2)查询所有数据，按工资的降序排列

select * from emp order by salary desc;

(3)先按部门编号降序排，同一个部门编号内的多条记录按年龄升序排

select * from emp order by office desc,age asc;

(4)混合排序，与where、group by、having配合使用

员工按部门进行分组，筛选出30岁以上员工的工资，筛选出30岁以上员工平均工资大于500的部门，对筛选出的列（部门、平均工资）按升序排列

select post,avg(salary) from emp where age>30 group by post having avg(salary)>500 order by avg(salary) asc;

1.4 过滤条件之limit

[1] 概念

limit子句用于限制查询结果的数量

使用方法一：

limit offset, count

offset：指定从哪一条记录开始返回数据（从0开始计数）。
count：指定返回记录的数量。

使用方法二：

limit count

只指定count时，offset默认为0，即从第一条记录开始返回数据。

[2]代码应用

(1)获取前10条记录

select * from emp limit 10;

(2)从第6条记录开始，向后获取5条记录

select * from emp limit 5,5;

1.5 过滤条件之正则表达式

[1]概念

列名 REGEXP 表达式：检查列中的值是否匹配正则表达式模式。

选项

说明

例子

匹配值示例

^

匹配文本的开始字符

‘^b’ 匹配以字母 b 开头的字符串

book、big、banana、bike

$

匹配文本的结束字符

‘st$’ 匹配以 st 结尾的字符串

test、resist、persist

.

匹配任何单个字符

‘b.t’ 匹配任何 b 和 t 之间有一个字符

bit、bat、but、bite

*

匹配前面的字符 0 次或多次

‘f*n’ 匹配字符 n 前面有任意个字符 f

fn、fan、faan、abcn

+

匹配前面的字符 1 次或多次

‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a

ba、bay、bare、battle

?

匹配前面的字符 0 次或1次

‘sa?’ 匹配0个或1个a字符

sa、s

字符串

匹配包含指定字符的文本

‘fa’ 匹配包含‘fa’的文本

fan、afa、faad

[字符集合]

匹配字符集合中的任何一个字符

‘[xz]’ 匹配 x 或者 z

dizzy、zebra、x-ray、extra

[^]

匹配不在括号中的任何字符

‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串

desk、fox、f8ke

字符串{n,}

匹配前面的字符串至少 n 次

‘b{2}’ 匹配 2 个或更多的 b

bbb、bbbb、bbbbbbb

字符串{n,m}

匹配前面的字符串至少 n 次， 至多 m 次

‘b{2,4}’ 匹配最少 2 个，最多 4 个 b

bbb、bbbb

[2]代码应用

(1)准备数据

CREATE TABLE `info`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(40) NULL DEFAULT NULL,
  `height` int(40) NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
);

INSERT INTO `info` VALUES ('Thomas ', 25, 168, '男');
INSERT INTO `info` VALUES ('Tom ', 20, 172, '男');
INSERT INTO `info` VALUES ('Dany', 29, 175, '男');
INSERT INTO `info` VALUES ('Jane', 27, 171, '男');
INSERT INTO `info` VALUES ('Susan', 24, 173, '女');
INSERT INTO `info` VALUES ('Green', 25, 168, '女');
INSERT INTO `info` VALUES ('Henry', 21, 160, '女');
INSERT INTO `info` VALUES ('Lily', 18, 190, '男');
INSERT INTO `info` VALUES ('LiMing', 19, 187, '男');

(2)查询name列以 j 开头的记录

select * from info where name regexp "^j";

(3)查询name列以 y 结尾的记录

select * from info where name regexp "y$";

(4)查询name列的值包含a 和 y，且两个字母之间只有一个字母的记录

select * from info where name regexp "a.y";

(5)查询name列的值包含T，且 T 后面出现字母 h 0次或多次的记录

select * from info where name regexp "Th*";

(6)查询name列的值包含T，且 T 后面至少出现h 一次的记录

select * from info where name regexp "Th+";

(7)查询name列的值包含S，且 S 后面出现 a  0次或一次的记录

select * from info where name regexp "Sa?";

2. 多表查询与子查询

2.1 概念

子查询：

子查询是一种嵌套在另一个查询中的查询。子查询可以出现在 SELECT, INSERT, UPDATE, 或 DELETE 语句中，并且必须被圆括号包围。

类比于解决问题的方式(一步一步解决)、将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件。

多表查询：

多表查询通常使用 JOIN 关键字来组合来自两个或多个表的数据。

将多张表拼接在一起，形成一张表，然后基于单表查询数据。

2.2 准备数据

create table dep(
    id int PRIMARY KEY AUTO_INCREMENT,
        name varchar(20)
);

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    gender ENUM("male","female") NOT NULL DEFAULT "male",
    age INT,
    dep_id INT
);

insert into dep values
("200","开发"),
("201","运营"),
("202","销售"),
("203","财务"),
("204","设计");

insert into emp(name,gender,age,dep_id) values
("ronaldo","male",18,200),
("avril","female",18,201),
("kylian","male",38,202),
("haaland","male",18,203),
("cristiano","male",28,204),
("lavigne","female",18,205);

2.3 子查询

(1)案例1：获取员工ronaldo所在的部门名称

方法一：分步查询

先在员工表中查询员工ronaldo部门id，再去部门表中根据部门id查询部门名称

select dep_id from emp where name="ronaldo";
select name from dep where id=200;

方法二：子查询

将方法一的两步合并为一步，将一个SQL语句的结果加上括号作为查询条件

select name from dep where id=(select dep_id from emp where name="ronaldo");

(2)案例2：查询开发或运营部门的员工信息

方法一：分步查询

先在部门表中查询对应部门id，再去员工表中根据部门id查询对应员工信息

select id from dep where name="开发" or name="运营";
select * from emp where dep_id=200 or dep_id=201;

方法二：子查询

将方法一的两步合并为一步

select * from emp where dep_id in (select id from dep where name="开发" or name="运营");

2.4 多表查询

[1]笛卡尔积

（1）笛卡尔积的概念
笛卡尔积是一个数学概念，它描述的是两个集合之间所有可能的元素组合的数量。
具体来说，如果集合A有n个元素，集合B有m个元素，则它们的笛卡尔积的大小为nm。
（2）笛卡尔积的理解
笛卡尔积是通过组合两个集合的所有元素来创建一个新的集合的过程。
在最简单的例子中，如果有一个集合包含三个元素a、b和c，另一个集合包含两个元素x和y，那么这两个集合的笛卡尔积将包含六个元素：ax、ay、bx、by、cx和cy。
（3）MySQL中的笛卡尔积
在SQL中，当使用JOIN操作将两个或更多的表连接在一起时，结果集中的行数是所有连接表的行数的乘积。这就是所谓的笛卡尔积。
例如，假设有两个表A和B，其中A有5行，B有3行。
如果使用INNER JOIN将这两个表连接起来，那么结果集中将会有5 x 3 = 15行。
这是因为对于每一行A，可以从B中选择任意一行进行匹配。
因此，总共有5种不同的方式来组合A表中的每一行和B表中的每一行，这导致了最终结果集的大小为5 x 3 = 15。
这个过程就是笛卡尔积，它是数学中的一种运算，用于计算两个集合的所有可能的元素组合的数量。
在这个情况下，每个元素都是一个表格中的行。
所以，当在MySQL中使用JOIN操作时，结果集的大小实际上是所有连接表的行数的乘积，这就是为什么称其为笛卡尔积的原因。

[2]拼虚拟表

拼虚拟表的结果即为笛卡尔积

select * from dep,emp;

[3]虚拟表去除无效数据

虽然部门表和员工表没有通过外键直接关联，但是设计的初衷是部门表的id即为员工表的dep_id，因此拼接虚拟表生成了大量的无效数据，需要去除

select * from dep,emp where dep.id=emp.dep_id;

部门表的id=员工表的dep_id  的记录才会被保留

[4]拼表关键字

(1)概念

inner join：内连接、交集
left join：左连接 、左表所有的数据都展示出来，右表中没有对应的项就用null表示
right join：右连接 、右表所有的数据都展示出来，左表中没有对应的项就用null表示
union：全连接 、合并两个或多个 SELECT 语句的结果集，每个 SELECT 语句中的列数必须相同，并且对应列的数据类型也必须兼容，默认去除重复的行

(2)内连接代码示例

inner join仍然为全部拼接，加上限制条件部门表的id=员工表的dep_id后，由于部门表的id没有205，因此求交集被舍去

select * from emp inner join dep;
select * from emp inner join dep on emp.dep_id=dep.id;

(3)左连接代码示例

select * from emp left join dep on emp.dep_id=dep.id;

(4)右连接代码示例

select * from dep right join emp on emp.dep_id=dep.id;

(5)全连接代码示例

基本语法：

SELECT column1, column2 FROM table1

UNION

SELECT column1, column2 FROM table2;

create table teacher(id int auto_increment primary key, name varchar(30));
insert into teacher (id, name) values (1, 'ronaldo'), (2, 'lavigne');

create table student(id int auto_increment primary key, name varchar(30));
insert into student (id, name) values (1, 'ronaldo'), (2, 'avril');

使用union：

去掉了重复的记录

select id,name from teacher union select id,name from student;

 

 

 

 

 

 

 

 

 

 

 

标签：多表,name,dep,34,emp,查询,id,select
From： https://www.cnblogs.com/hbutmeng/p/18469226

相关文章

  • 了解 SQL 查询执行顺序
    前言SQL是一种标准化的结构化查询语言，涉及结构化查询时，高效地检索数据至关重要。SQL在关系关系数据库交互至关重要，掌握其复杂性可以提高我们的数据查询技能。了解SQL查询子句的执行顺序，对于编写正确高效的SQL语句至关重要。本文一起探讨SQL查询子句的典型执行顺序及了......
  • 市面上很火的1234567转成视频数字人，数字人克隆一比一复刻
    功能介绍：数字人克隆一比一复刻，效果媲美真人水平我们是支持单视频不限制时长，我们的是导入2K还是4K的视频模型，输出的也是2K/4K的会员期间无限量不限制创作条数，不限量克隆形象，非积分模式无限合成并非市面上那些数字人按分钟技费的设备需求：安卓9.0以上......
  • 市面上很火的1234567转成视频数字人，数字人克隆一比一复刻
    功能介绍：数字人克隆一比一复刻，效果媲美真人水平我们是支持单视频不限制时长，我们的是导入2K还是4K的视频模型，输出的也是2K/4K的会员期间无限量不限制创作条数，不限量克隆形象，非积分模式无限合成并非市面上那些数字人按分钟技费的设备需求：安卓9.0以上......
  • 数据库之子查询
    一、子查询定义：一个查询中嵌套另一个查询二、子查询的分类（1）标量子查询（2）列子查询（3）行子查询（4）表子查询（运用多）三、子查询详解（1）标量子查询（返回一个值）标量子查询：把一个sql执行返回的一个值，作为另一个sql的条件，得到的结果是一行一列，一般出现在where之后，备注：标量子查询允许使用......
  • Excel-多表数据查找匹配(VLOOKUP)
    ......
  • 百万商品查询，性能提升了10倍
    前言最近在我的知识星球中，有个小伙伴问了这样一个问题：百万商品分页查询接口，如何保证接口的性能？这就需要对该分页查询接口做优化了。这篇文章从9个方面跟大家一起聊聊分页查询接口优化的一些小技巧，希望对你会有所帮助。1增加默认条件对于分页查询接口，如果没有特殊要求，我们可......
  • 第10课 数据库之多表运用
    一、多表查询1、什么是多表关联查询从2个表或者更多的表中查询我们需要的数据2、多表连接的关系？（1）内连接（2）左连接（3）右连接（4）左独有数据（5）右独有数据（6）全外连接比如：a表：1，2，3b表：1，2，4内连接：显示左边12和右边12关联12左连接：显示左边1，2，3，右边12关联......
  • 使用 EXPLAIN 分析结果优化 SQL 查询
    使用EXPLAIN分析结果优化SQL查询是数据库性能调优中的一项重要技能。EXPLAIN语句能够展示数据库查询优化器对SQL查询的处理计划，从而帮助开发者识别查询中的瓶颈和低效部分。本文将详细介绍如何使用EXPLAIN分析结果来优化SQL查询。一、什么是EXPLAINEXPLAIN语......
  • 【数据库】数据库管理（下）存储过程 触发器 慢查询日志 备份与恢复
    文章目录存储过程参数控制语句触发器慢日志备份&恢复备份方法恢复方法导入导出数据存储过程数据库存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集合，这些语句被预先编译并保存在数据库中。存储过程可以接受输入参数、执行复杂的业务逻辑，并返回结果......
  • 代码随想录算法训练营第十一天|leetcode150. 逆波兰表达式求值、leetcode239. 滑动窗
    1leetcode150.逆波兰表达式求值题目链接：150.逆波兰表达式求值-力扣（LeetCode）文章链接：代码随想录视频链接：栈的最后表演！|LeetCode：150.逆波兰表达式求值_哔哩哔哩_bilibili自己的思路：这是一道有思路，但是思路并不多的题目，就是我会觉得是先将数据进行添加，然后对于符号通过倒......

赞助商

阅读排行

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

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

Copyright © 2020-2023 IPS99 版权所有 IPS99