MySQL第六章： 编写复杂查询

最新推荐文章于 2024-05-09 00:12:29 发布
最新推荐文章于 2024-05-09 00:12:29 发布
阅读量812 收藏 17
点赞数 13
分类专栏： 菜菜菊花酱数据分析课程 MySQL 文章标签： mysql 数据分析
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Alex_sdhjkb/article/details/136158406
版权

MySQL第六章： 编写复杂查询

**内容来自： **

1、b站mosh老师的SQL课程（第五章） 【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！_哔哩哔哩_bilibili www.bilibili.com/video/BV1UE41147KC/?p=17&spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=171e84ea90c06aa5a434d7fa2502e75c

2、https://zhuanlan.zhihu.com/p/222865842（非常感谢这位知乎大佬，笔记、课件写的很详细）

《SQL必知必会（第五版）》这部分内容这个月后期会统一更新

3、菊花酱数据分析课程

各数据库字段之间的关联

子查询

子查询： 任何一个充当另一个SQL语句的一部分（ SELECT…… 查询语句）都是子查询，子查询是一个很有用的技巧

在工作中，经常会遇见4种子查询，即:

  • 含有比较运算符（>、>=、<、<=、=、!=）
  • IN关键词
  • ANY/ALL关键词
  • EXISTS关键词的嵌套查询

子查询的层级用括号实现。

注意：

如果不确定执行顺序时最好加上括号确保万无一失

比较运算符

案例

题目：

在 products表中，筛选出所有比生菜（id = 3）价格高的记录

提示：

用子查询找到生菜价格

USE sql_store;

SELECT *
FROM products
WHERE unit_price > (
    SELECT unit_price
    FROM products
    WHERE product_id = 3
)

代码解析：

MySQL执行时会先执行括号内的子查询（内查询），将获得的生菜价格作为结果返回给外查询。

注意：

子查询不仅可用在 WHERE …… 中，也可用在 SELECT …… 或 FROM …… 等子句中。

练习1

题目：

在 sql_hr 库 employees表里，选择所有工资超过平均工资的记录。

提示：

由子查询得到平均工资

USE sql_hr;

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

为什么不能用WHERE salary > AVG(salary)?

WHERE 子句不能直接使用聚合函数来筛选每一行。 因为WHERE 子句用于过滤行，而这个过滤操作发生在行被分组（group by)和聚合之前。由于聚合函数作用于分组后的结果，所以它们不能用于决定哪些行会进入到分组中。

补充：

在 SQL 中，聚合函数可以在两种情况下使用：

  1. 不带 GROUP BY 子句时：聚合函数将作用于整个查询结果集(from table)。这意味着，当您不使用 GROUP BY 子句时，聚合函数（如 MAX(), MIN(), AVG(), SUM(), COUNT() 等,但select后面的内容不能有非聚合字段）会处理整个表或满足 WHERE 子句条件的所有行。在这种情况下，每个聚合函数都返回一个单一的值，代表整个数据集的相应聚合。
  2. 带有 GROUP BY 子句时：聚合函数将作用于每个分组。GROUP BY 子句会根据指定的列将数据分成多个组，然后聚合函数针对每个组分别计算结果。

练习2

前置准备（来源于菊花酱数据分析课程）

# 创建学员信息表
CREATE TABLE stu_info(
id INT AUTO_INCREMENT PRIMARY KEY,
iname VARCHAR(20),
gender CHAR(1),
department VARCHAR(10),
age TINYINT,
province VARCHAR(10),
email VARCHAR(50),
mobilephone CHAR(11)
);
# 向学员表中插入数据
INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone)
VALUES
('张勇','男','数学系',23,'河南','sfddf123dd@163.com','13323564321'),
('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'),
('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'),
('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'),
('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'),
('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'),
('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'),
('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'),
('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'),
('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','13827811311');
# 查看学员表
SELECT * FROM stu_info;
# 创建学员成绩表
CREATE TABLE stu_score(
id INT ,
Excel TINYINT,
Tableau TINYINT,
MySQL TINYINT
);
# 向成绩表中插入数据
INSERT INTO stu_score VALUES
(1,87,72,88),
(3,90,66,72),
(2,90,70,86),
(4,88,82,76),
(8,92,67,80),
(10,88,82,89),
(5,79,66,60),
(7,91,78,90),
(6,82,79,88),
(9,85,70,85);
# 查看成绩表
SELECT * FROM stu_score;

题目：查询年龄不低于所属系平均年龄的学员信息

SELECT * FROM stu_info AS s1
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2
WHERE s1.department = s2.department)

注意：

比较运算符后面的子查询只能返回一个结果

IN运算符

当查询条件涉及某些已知的可枚举离散值的时候，我们就可以选择IN关键词来完成数据的提取。IN关键词有两种用法：

  1. 将可枚举的离散值直接写在值列表中
  2. 当离散值是基于其他表的筛选结果时，就可以使用嵌套查询，即把另一个表的查询语句块写在IN关键词后面的括号里

案例

题目：

在 sql_store 库 products 表中找出那些从未被订购过的产品记录

思路：

  1. order_items 表里有所有产品被订购的记录，用 DISTINCT 去重，得到所有被订购过的产品列表
  2. 不在这列表里（NOT IN 的使用）的产品即为从未被订购过的产品
**USE sql_store;

SELECT *
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
)

练习1

题目：

在 sql_invoicing 库 clients 表中找到那些没有过发票记录的客户记录

思路：

在invoices里用DISTINCT找到所有有过发票记录的客户列表，再用NOT IN来筛选

USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
)

练习2

查询数学系和计算机系的学员信息 (数据库来源于菊花酱数据分析，见顶上比较运算符的练习2)

SELECT * FROM stu_info
WHERE department IN('数学系','计算机系');

练习3

查询与张勇、刘伟同一个系的学员信息 (数据库来源于菊花酱数据分析，见顶上比较运算符的练习2)

SELECT * FROM stu_info
WHERE department IN (SELECT department FROM stu_info WHERE iname IN('张勇','刘伟'));

练习4

查询MySQL成绩大于85分的学员信息 (数据库来源于菊花酱数据分析，见顶上比较运算符的练习2)

SELECT * FROM stu_info
WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);

注意：

在使用IN关键词的嵌套查询的时候，嵌套部分只能返回一个字段的信息（比如上面的department字段或者id字段），如果返回两个及以上字段信息则会出现语法错误

子查询vs连接

子查询（Subquery）是将一张表的查询结果作为另一张表的查询依据并层层嵌套，其实也可以先将这些表链接（Join）合并成一个包含所需全部信息的详情表再直接在详情表里筛选查询。两种方法一般是可互换的，具体用哪一种取决于 效率/性能（Performance） 和 可读性（readability）。

之后会学习执行计划，到时候就知道怎样编写并更快速地执行查询，现在主要考虑可读性

案例

题目：

在 sql_invoicing 库 clients 表中找到那些没有过发票记录的客户记录(用连接表实现)

USE sql_invoicing;

SELECT DISTINCT client_id, name
-- 不能SELECT DISTINCT *
FROM clients
LEFT JOIN invoices USING (client_id)
-- 注意不能用内链接，否则没有发票记录的顾客（我们的目标）直接就被筛掉了
WHERE invoice_id IS NULL

就上面这个案例而言，子查询可读性更好，但有时子查询会过于复杂（嵌套层数过多），用链接表更好。总之在选择方法时，可读性是很重要的考虑因素。

练习

题目：

在 sql_store 中，选出买过生菜（id = 3）的顾客的id、姓和名（分别用子查询法和链接表法实现并比较可读性）。

-- 法1 完全子查询
USE sql_store;

SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询2：从订单表中找出哪些顾客买过生菜
    SELECT customer_id
    FROM orders
    WHERE order_id IN (  
        -- 子查询1：从订单项目表中找出哪些订单包含生菜
        SELECT DISTINCT order_id
        FROM order_items
        WHERE product_id = 3
    )
)

-- 法2 混合：子查询 + 表连接
USE sql_store;

SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询：哪些顾客买过生菜
    SELECT customer_id
    FROM orders
    LEFT JOIN order_items USING (order_id)  
    -- 表连接：合并订单和订单项目表得到 订单详情表
    WHERE product_id = 3
)

-- 法3 完全表连接
USE sql_store;

SELECT DISTINCT customer_id, first_name, last_name
-- 注意 DISTINCT 关键字的运用
FROM customers
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items USING (order_id)
WHERE product_id = 3

ALL关键字

(MAX (……)) 和 > ALL(……) 等效可互换，即“比这里面最大的还大” = “比这里面的所有

对于含比较运算符的嵌套查询来说，嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值，就需要用到ALL关键词了。通常，ANY 关键词经常和比较运算符连用，下面是6种比较运算符与ALL 关键词的搭配结果：

组合含义
>ALL大于子查询结果中的所有值
>=ALL大于等于子查询中的所有值
<ALL小于子查询结果中的所有值
<=ALL小于等于子查询结果中的所有值
=ALL等于子查询结果中的所有值
!=ALL不等于子查询结果中的所有值

案例

题目：

sql_invoicing 库中，选出金额大于3号顾客所有发票金额（或3号顾客最大发票金额） 的发票记录（两种方法）

-- 法1. 用MAX关键字
USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

-- 法2. 用ALL关键字
USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)
-- 等同于where invoice_total > all(150, 130, 167, ...)
-- 若没有all， MySQL无法将一个发票金额与3号所有发票金额进行比较

注意：

上面代码中没有group by，相当于是处理整个表中满足 WHERE 子句条件的所有记录。

代码解析：

其实就是把内层括号的MAX拿到了外层括号变成ALL(两种方法是完全等效的)：

  • MAX法是用MAX()返回一个顾客3的最大订单金额，再判断哪些发票的金额比这个值大；
  • ALL法是先返回顾客3的所有订单金额，是一列值，再用ALL()判断比所有这些金额都大的发票有哪些。

练习

查询非管理系中比管理系所有学员年龄大的学员信息 (数据库来源于菊花酱数据分析，见顶上比较运算符的练习2)

SELECT * FROM stu_info
WHERE age > ALL (SELECT DISTINCT age FROM stu_info
	WHERE department = '管理系')
AND department != '管理系';

代码解析：

  1. 首先查询管理系中学生的年龄（去重），得到的结果是22和24；
  2. 查询出非管理系中年龄比22和24都大的学生信息（也就是年龄大于24的非管理系学生信息）

ANY关键字

  • ANY/SOME (……) 与 > (MIN (……)) 等效
  • ANY/SOME (……) 与 IN (……) 等效

对于含比较运算符的嵌套查询来说，嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值，就需要用到ANY或者ALL关键词了。通常，ANY / ALL 关键词经常和比较运算符连用，下面是6种比较运算符与ANY 关键词的搭配结果

组合含义
>ANY大于子查询结果中的某个值
>=ANY大于等于子查询中的某个值
<ANY小于子查询结果中的某个值
<=ANY小于等于子查询结果中的某个值
=ANY等于子查询结果中的某个值
!=ANY不等于子查询结果中的某个值

案例1

题目：

sql_invoicing 库中，选出金额大于3号顾客任何发票金额（或最小发票金额） 的发票记录(两种方法实现)

-- 法一
USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > ANY (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

-- 法二
USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > ( -- 连续值无法用in
    SELECT MIN(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

案例2

题目：

选出至少有两次发票记录的顾客（用两种方法实现）

-- 法一
USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id IN (  -- 或 = ANY ( 
    -- 子查询：有2次以上发票记录的顾客
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2 -- 筛选至少有两次发票记录的顾客
)

-- 法二
USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id = ANY( 
    -- 子查询：有2次以上发票记录的顾客
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

练习

查询非管理系中比管理系任意一个学员年龄小的学员信息 (数据库来源于菊花酱数据分析，见顶上比较运算符的练习2)

SELECT * FROM stu_info
WHERE age < ANY(SELECT DISTINCT age FROM stu_info
	WHERE department = '管理系')
AND department != '管理系';

代码解析：

  1. 首先查询管理系中学生的年龄（去重），得到的结果是22和24；
  2. 查查询出非管理系中年龄比22或24年龄小的学生信息（也就是年龄小于24的非管理系学生信息）

相关子查询

之前都是非关联主/子（外/内）查询，比如子查询先查出整体的某平均值或满足某些条件的一列id，作为主查询的筛选依据，这种子查询与主查询无关，会先一次性得出查询结果再返回给主查询供其使用。

相关子查询就是当你在子查询中使用了外查询中的表里的某一字段去执行子查询里的某个命令的时候，就叫相关子查询。此时外查询跟子查询因为某个条件被连接在了一起有了相关性。

注意：

这种关联查询是在主查询的每一行/每一条记录层面上依次进行的，这一点可以为我们写关联子查询提供线索（注意表别名的使用），另外也正因为这一点，相关子查询会比非关联查询执行起来慢一些。

案例

题目：

选出 sql_hr.employees 里那些工资超过他所在办公室平均工资（而不是整体平均工资）的员工(两种方法）)

思路：

子查询要查询的是某员工所在办公室的平均值，子查询是依赖主查询的。给主查询 employees表 设置别名 e，这样在子查询查询平均工资时加上 WHERE office_id = e.office_id 筛选条件即可相关联地查询到目前员工所在地办公室的平均工资

-- 法一
USE sql_hr;

SELECT *
FROM employees e  -- 关键 1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id  -- 关键 2（相同的id才能比较）
    
)

代码解析：

  1. 当被查询记录的office_id =e.office_id时，才执行计算平均工资和筛选出高于平均工资的员工；

  2. 首先把外部的employees表和子查询的employees表，分别命名为e1和e2两个独立的表格；

  3. 代码首先处理外部的where，此时DBMS会一次将e1（外部employees)的每一条记录的salary字段值一次与括号里的输出结果作对比：

    1. 此时由于外部employees表已经选择了一条记录，此时e.office_id也就确定的，此时只需要对e1表（内部employees)筛选出office_id = e.officd_id的记录，并计算字段salary的均值，与外部employees表已选的一条记录的salary字段值进行比较；

  4. 重复以上过程，知道外部employees表遍历完每一条记录

    总结：

    当内部表格被查询记录的office_id =e.office_id（对每位员工执行括号里的子查询）时，才执行计算平均工资和筛选出高于平均工资的员工

注意：

  • 子查询表字段不用加前缀，主查询表的字段要加前缀，以此区分；
-- 法二
USE sql_hr;

SELECT e.*
FROM employees e  -- 关键 1
inner join
	(select office_id, avg(salary) as avg_salary
	from employees
    group by office_id) as p
    using (office_id)
where salary > p.avg_salary

代码解析

  • 通过group by建立一个新表只包含office_id和avg_salary,然后和原表合并，再用where进行筛选(第一个括号内容就是生成一个表)
  • 本质：原表的基础上加了一列该员工办公室的平均工资

法二的代码来自于b站评论区的一位大神，膜拜大神

练习

题目：

在 sql_invoicing 库 invoices 表中，找出高于每位顾客平均发票金额的发票

-- 法一
USE sql_invoicing;

SELECT *
FROM invoices i
WHERE  invoice_total > (
    -- 子查询：目前客户的平均发票额
    SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
)

-- 法二
use sql_invoicing;

select i.* from invoices as i
left join (
		select client_id, avg(invoice_total) as invoice_average
        from invoices as i group by client_id) as p
using (client_id)
where invoice_total > invoice_average

/*通过group by建立一个新表只包含client_id和invoice_average,然后和原表合并，再用where将invoice_total与invoice_average筛选。*/

EXISTS运算符

  • EXISTS 关键词的作用和 IN关键词非常类似 ，即 IN + 子查询 等效于 EXIST + 相关子查询
  • 不同的是，通过EXISTS关键词的嵌套查询返回的不是具体的值集合，而是满足条件的逻辑值（也就是True / False）。也就是说，EXISTS的作用就是“判断是否存在满足某种条件的记录”，如果存在这样的记录就返回真（True），如果不存在这样的记录就返回假
    （False）

注意：

  • 如果前者（any、in）子查询的结果集过大，占用内存，用后者逐条验证更有效率；
  • EXIST() 本质上是根据是否为空返回 TRUE 和 FALSE，所以也可以加 NOT 取反；
  • 相比于IN(), EXIST() 并没有真的把结果（True/False）返回给外部查询，并且内查询只要有一条记录满足条件就返回结果TRUE给外部查询，效率会快很多。

案例

找出有过发票记录的客户记录（三种方法）。

-- 法1. 子查询
USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
)

-- 法2. 链接表
use sql_invoicing;

select distinct client_id, name from clients as c
left join invoices as i -- 内连接也可以，只留下有过发票记录的客户
using (client_id)
where invoice_id is not null

-- 法3. EXISTS运算符
SELECT *
FROM clients c
WHERE EXISTS (
    SELECT */client_id  
    /* 就这个子查询的目的来说，SELECT的选择不影响结果，
    因为EXISTS()函数只根据是否为空返回 TRUE 和 FALSE */
    FROM invoices
    WHERE client_id = c.client_id
)

代码解读：

这还是个相关子查询，因为在子查询中引用了主查询的 clients 表。这同样是按照主查询的记录一条条验证执行的。具体说来，对于 clients 表（设置别名为 c）里的每一个顾客，都会检查是否存在一条满足子查询条件的记录，其中子查询在 invoices 表查找这个人的发票记录（ 即 client_id = c.client_id 的发票记录），有就返回相关记录否则返回空，然后 EXISTS() 根据是否为空得到 TRUE 和 FALSE（表示此人有无发票记录），然后主查询凭此确定是否保留此条记录。

注意：

法1是用子查询返回一个有发票记录的顾客id列表，如（1，3，8 ……），然后用IN运算符来判断，如果子查询表太大，可能返回一个上百万千万甚至上亿的id列表，这个id列表就会很占内存非常影响性能，对于这种子查询会返回一个很大的结果集的情况，用这里的EXIST+相关子查询逐条筛选会更有效率

练习1

在sql_store中，筛选出从来没有被订购过的产品记录。(两种方法)

-- 法一
USE sql_store;

SELECT *
FROM products 
WHERE product_id NOT IN (
    SELECT product_id 
    -- 加不加DISTINCT对小数据集的影响不大
    FROM order_items
)

-- 法二
SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT *
    FROM order_items
    WHERE product_id = p.product_id
)

练习2

查询MySQL成绩大于85分的学员信息 (数据库来源于菊花酱数据分析，见顶上比较运算符的练习2)

SELECT * FROM stu_info
WHERE EXISTS(
    SELECT * FROM stu_score 
    WHERE stu_score.id = stu_info.id AND MySQL> 85
);

注意:

  • 使用EXISTS关键词的嵌套语句 WHERE与EXISTS关键词之间没有任何参数，这是因为EXISTS只需要一个参数，通常是在EXISTS右侧加一个子查询语句。
  • EXISTS后面的子查询中SELECT后面可以写表中任何一个字段或者星号或者一个常数，因为EXISTS后面的子查询只关心是否存在满足条件的记录

练习3

查询数学系和计算机系之外的学员信息

SELECT * FROM stu_info
WHERE NOT EXISTS(
    SELECT * FROM stu_score WHERE department IN('数学系','计算机系')
	and stu_score.id = stu_info.id
)

何时用in， 何时用exist?

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists,即IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况

SELECT子句的子查询

除此之外，子查询还可以放在select语句、from语句、having语句后面。

SELECT选择语句是用来确定查询结果选择包含哪些字段，每个字段都可以是一个表达式，而每个字段表达式里的元素除了可以是原始的列，具体的数值，也同样可以是其它各种花里胡哨的子查询的结果

注意：

  • 任何子查询都是简单查询的嵌套；
  • 以子查询方式实现在SELECT中使用同级列别名的方法。

案例

题目：

从invoices中筛选出invoice_id, invoice_total, avarege（总平均发票额）, difference（前两个值的差）字段。

USE sql_invoicing;

SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    /*不能直接用聚合函数，这里有其他非聚合字段，而
    用括号+子查询(SELECT AVG(invoice_total) FROM invoices) 
    将其作为一个数值结果 152.388235 加入主查询语句*/
    invoice_total - (SELECT invoice_average) AS difference
    /*SELECT表达式里要用原列名，不能直接用别名invoice_average
    要用列别名的话用子查询（SELECT 同级的列别名）即可
    说真的，感觉这个子查询有点难以理解，但记住会用就行*/
FROM invoices

注意：

  • (SELECT AVG(invoice_total) FROM invoices) AS invoice_average类似于前面的'silver' as points.

image-20231223230711192

  • 不能将(SELECT AVG(invoice_total) FROM invoices) AS invoice_average改为AVG(invoice_total) AS invoice_average,因为select后面有非聚合字段。

练习

从clients筛选出client_id, name, total_sales（各个客户的发票总额）, average（总平均发票额）, difference（前两个值的差）字段。

USE sql_invoicing;

SELECT 
    client_id,
    name,
    (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
    -- 要得到【相关】客户的发票总额，要用相关子查询 WHERE client_id = c.client_id
    (SELECT AVG(invoice_total) FROM invoices) AS average,
    (SELECT total_sales - average) AS difference   
    /* 如前所述，引用同级的列别名，要加括号和 SELECT，
    和前两行子查询的区别是，引用同级的列别名不需要说明来源，
    所以没有 FROM …… */
FROM clients c

注意：

  • 在select子句中引用同级的列别名不需要说明来源（from…),但要加括号和select；

  • 引用的列名如果是是聚合函数则需要说明来源（from…)，同时加括号和select。

    解释同级:

    都位于select子句的as后面的列别名中，中间用逗号分隔。

FROM子句的子查询

子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表，即将筛选查询结果作为来源再进行进一步的筛选查询。

注意:

只有在子查询不太复杂时进行这样的嵌套，否则最好用后面要学的的视图先把子查询结果储存储存为叫 sales_summury 的视图，然后再直接使用该视图作为来源表。

案例

题目：

将上一节练习里的查询结果当作来源表，查询其中 total_sales 非空的记录

USE sql_invoicing;

SELECT * 
FROM (
    SELECT 
        client_id,
        name,
        (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
        (SELECT AVG(invoice_total) FROM invoices) AS average,
        (SELECT total_sales - average) AS difference   
    FROM clients c
) AS sales_summury
WHERE total_sales IS NOT NULL

注意：

在FROM中使用子查询，即使用 “派生表” 时，必须给派生表取个别名（不管用不用），这是硬性要求，不写会报错：Error Code: 1248. Every derived table（派生表、导出表）must have its own alias.

总结：

查询数据常用套路：

  • 从表B中查询的信息与A表格的某个字段进行比较
    • 相关子查询
    • 非相关子查询
      FROM子句的子查询

子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表，即将筛选查询结果作为来源再进行进一步的筛选查询。

注意:

只有在子查询不太复杂时进行这样的嵌套，否则最好用后面要学的的视图先把子查询结果储存储存为叫 sales_summury 的视图，然后再直接使用该视图作为来源表。

案例

题目：

将上一节练习里的查询结果当作来源表，查询其中 total_sales 非空的记录

USE sql_invoicing;

SELECT * 
FROM (
    SELECT 
        client_id,
        name,
        (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
        (SELECT AVG(invoice_total) FROM invoices) AS average,
        (SELECT total_sales - average) AS difference   
    FROM clients c
) AS sales_summury
WHERE total_sales IS NOT NULL

注意：

在FROM中使用子查询，即使用 “派生表” 时，必须给派生表取个别名（不管用不用），这是硬性要求，不写会报错：Error Code: 1248. Every derived table（派生表、导出表）must have its own alias.

总结：

查询数据常用套路：

  • 从表B中查询的信息与A表格的某个字段进行比较
    • 相关子查询
    • 非相关子查询
  • 表B与表A进行inner join/left join
金鸡湖最后的张万森
关注
  • 13
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库 第5章：单表操作
惊鸿若梦一书生
04-04 2978
MySQL数据库 第5章：单表操作5.1 数据操作5.1.1 复制表结构和数据5.1.2 解决主键冲突5.1.3 清空数据5.1.4 去除重复记录5.2 排序与限量5.2.1 排序5.2.2 限量5.3 分组与聚合函数5.3.1 分组5.3.2 聚合函数5.4 运算符5.4.1 算术运算符5.4.2 比较运算符5.4.3 逻辑运算符5.4.4 赋值运算符5.4.5 位运算符5.4.6 运算符优先级5.5 案例实践：商品评论表的操作5.6 总结5.7 课后练习 5.1 数据操作 5.1.1 复制表结构和数据
MySQL数据库 第4章：数据库设计
惊鸿若梦一书生
03-27 3251
MySQL数据库 第4章：数据库设计4.1 数据库设计概述4.2 数据库设计范式4.3 数据建模工具4.4 数据库设计-电子商务网站4.4.1 需求分析4.4.2 准备工作4.4.3 商品分类4.4.4 商品表4.4.5 商品规格表4.4.6 商品属性表4.4.7 用户表4.4.8 评论表4.5 案例实践：商品购物流程设计4.6 总结4.7 课后练习 4.1 数据库设计概述 4.2 数据库设计范式 4.3 数据建模工具 4.4 数据库设计-电子商务网站 4.4.1 需求分析 4.4.2 准备工作 4.4.3
MySQL数据库 - 复杂查询（一）第3关：分数排名
2201_75609739的博客
10-14 739
补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。情况一：平分后的下一个名次是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。请仔细阅读右侧代码，根据方法内的提示，在。查询来实现二种排名方式的分数排名。如果两个分数相同，则两个分数排名（情况二：排名是非连续的。区域内进行代码补充。
高性能 MySQL - 第六章 查询性能优化
ylx1066863710的博客
11-19 643
最近阅读MySQL高性能，略有收获，好记忆不如烂笔头，记录一下。本期笔记主要是围绕高性能MySQL第六章查询性能优化。
MySQL数据库 - 复杂查询（一）第三关
qq_44745905的博客
08-21 5562
@R星校长 第3关：分数排名 本关任务：编写SQL查询来实现二种排名方式的分数排名。 score表结构信息如下： Id Score 1 3.52 2 3.65 3 4.23 4 3.85 5 4.23 6 3.65 如果两个分数相同，则两个分数排名（Rank）相同。 情况一：平分后的下一个名次是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。例：1、1、2、3、4、4。 情况二：排名是非连续的。例：1、1、1、4、4、6。 开始你的任务吧，祝你成功！ 答案.
MySQL数据库第六章总结：创建与使用存储过程
weixin_61961335的博客
05-25 865
SQL 语句没有流程控制，无法实现复杂的应用，PL/SQL 语言（Procedural Lan-guage/SQL）是将结构化查询与数据库过程控制结合为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有变量声明、赋值语句，而且有选择、循环等流程控制语句。 任务6.1、创建简单存储过程 PL/SQL的变量 变量是指程序运行过程中其值可以改变的量，包括用户变量、系统变量和局部变量。 （1）用户变量：用户可以在 PL/SQL 中使用自己定义的变量，这样的变量称为用户变量。可以先在用户变量中保存一个数
mysql 复杂 语句_Mysql复杂查询语句汇总（仅供学习）
weixin_31613423的博客
01-19 1870
1、mysql查询今天、昨天、上周今天select * from 表名 where to_days(时间字段名) = to_days(now());昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 17天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DA...
Mysql笔记：第04章_逻辑架构
IAMLSL的博客
03-16 1996
第04章_逻辑架构1. 逻辑架构剖析1.1 服务器处理客户端请求1.2 Connectors1.3 第1层：连接层1.4 第2层：服务层SQL Interface: SQL接口Parser: 解析器Optimizer: 查询优化器Caches & Buffers： 查询缓存组件1.5 第3层：引擎层1.6 存储层1.7 小结2. SQL执行流程2.1 MySQL 中的 SQL执行流程MySQL的查询流程：2.2 MySQL8中SQL执行原理2.3 MySQL5.7中SQL执行原理2.4 SQL语法顺
MySQL数据库 - 复杂查询（一）第四关
qq_44745905的博客
08-22 4386
@R星校长 第4关：体育馆的人流量 本关任务：某市建了一个新的体育馆，每日人流量信息被记录在gymnasium表中：序号 (id)、日期 (date)、 人流量 (visitors_flow)。 请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。 gymnasium表结构数据如下： id date visitors_flow 1 2019-01-01 58 2 2019-01-02 110 3 2019-01-03 123 4 2019-.
Mysql笔记：第01章_数据库概述
IAMLSL的博客
02-11 2885
第01章_数据库概述1. 为什么要使用数据库2. 数据库与数据库管理系统2.1 数据库的相关概念2.2 数据库与数据库管理系统的关系2.3 常见的数据库管理系统排名(DBMS)2.4 常见的数据库介绍3. MySQL介绍3.1 概述3.2 MySQL发展史重大事件3.3 关于MySQL 8.03.4 Oracle vs MySQL4. RDBMS 与 非RDBMS4.1 关系型数据库(RDBMS)4.2 非关系型数据库(非RDBMS)4.2.1 介绍4.2.2 有哪些非关系型数据库4.2.3 NoSQL的演
MYSQL中文手册
03-11
5.13. MySQL查询高速缓冲 5.13.1. 查询高速缓冲如何工作 5.13.2. 查询高速缓冲SELECT选项 5.13.3. 查询高速缓冲配置 5.13.4. 查询高速缓冲状态和维护 6. MySQL中的复制 6.1. 复制介绍 6.2. 复制实施概述 6.3...
MySQL 5.1官方简体中文参考手册
07-02
http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/（第 6／24 页）2006-11-02 19:12:13 MySQL 5.1 Reference Manual 6.11. 通报复制缺陷 6.12. 多服务器复制中的Auto-Increment 7. 优化 7.1. 优化概述 7.1.1....
MySQL 5.1中文手冊
12-11
5.13. MySQL查询高速缓冲 5.13.1. 查询高速缓冲如何工作 5.13.2. 查询高速缓冲SELECT选项 5.13.3. 查询高速缓冲配置 5.13.4. 查询高速缓冲状态和维护 6. MySQL中的复制 6.1. 复制介绍 6.2. 复制实施概述 6.3. 复制...
MySQL 5.1参考手册 （中文版）
07-20
5.13. MySQL查询高速缓冲 5.13.1. 查询高速缓冲如何工作 5.13.2. 查询高速缓冲SELECT选项 5.13.3. 查询高速缓冲配置 5.13.4. 查询高速缓冲状态和维护 6. MySQL中的复制 6.1. 复制介绍 6.2. 复制实施概述 6.3. 复制...
MySQL5.1参考手册官方简体中文版
05-10
5.13. MySQL查询高速缓冲 5.13.1. 查询高速缓冲如何工作 5.13.2. 查询高速缓冲SELECT选项 5.13.3. 查询高速缓冲配置 5.13.4. 查询高速缓冲状态和维护 6. MySQL中的复制 6.1. 复制介绍 6.2. 复制实施概述 6.3. 复制...
yum 卸载 MySQL
边博磊的博客
05-03 495
但是，请注意，这将永久删除所有 MySQL 数据库和配置，所以在执行此操作之前，请确保你已经备份了所有重要数据。如果你只想卸载 MySQL 服务器，但保留客户端和库，你需要特别指定要卸载的包。因此，在删除之前，请确保你知道你的 MySQL 安装的确切位置。但是，请注意，删除用户和组可能会影响到系统上其他依赖于这些用户和组的服务或应用程序，所以请确保在删除之前你已经考虑了这一点。卸载 MySQL 时，你需要确保你卸载的是整个 MySQL 服务器和相关组件，而不仅仅是客户端或库。，而数据目录通常位于。
mysql中varchar与bigint直接比较会导致精度丢失以至于匹配到多行数据
最新发布
卖炫迈的小男孩的博客
05-09 276
如果联表的两个字段类型不一样，数据库会对两个不同类型的字段进行隐式的类型转换，导致索引失效。varchar与bigint进行比较的时候，mysql先转换为双精度浮点数double再进行比较的，由于bigint的位数超过了16位所以会导致丢失一部分精度，所以会匹配上多行数据。而在mysql中double的精度的精度为16位有效数据,因此如果要比较varchar和bigint类型的话，如果允许的话就改为统一的类型进行比较。也可以将bigint转换为varchar进行比较，或者两者都使用更高精度的Decimal类
MySQL为什么默认引擎是InnoDB?
weixin_50076754的博客
05-05 270
为什么Mysql的默认引擎是InnoDB?
MySQL mysqldump备份恢复
zj88189748的专栏
05-06 893
通过以上的日志分析，可以总结下mysqldump备份的主要流程：一开始设置SQL_MODE为空;设置TIME_ZONE为+00:00;查看是否开启了GTID；执行FLUSH TABLES关闭实例中所有的表；执行语句FLUSH TABLES WITH READ LOCK获取全局表的读锁，保证表一致性；设置会话级别事务的隔离级别为REPEATABLE READ，保证事务期间数据的一致性；执行语句START TRANSACTION /*!
酒店管理系统mysql
05-31
您的问题不够明确，可能需要提供更多细节才能回答。但是，如果您是在寻求有关如何使用MySQL...以上是一些基本的步骤，但是酒店管理系统的开发需要更多的细节和复杂性，因此您可能需要深入了解MySQL和数据库设计的知识。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值