mysql基础入门总结 复杂查询
2021/12/22 2:24:59
本文主要是介绍mysql基础入门总结 复杂查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
子查询语句(SubQuery)
## 薪水大于平均薪水的雇员 select * from employees where salary > (select avg(salary) from employees);
IN 操作符 in 子查询
## 没有发票的顾客 select * from clients where client_id not in(select distinct client_id from invoices)
子查询 与 join
select * from clients left join invoices using(client_id) where invoice_id is null ## 也是查询没有发票的顾客 ## 和使用IN操作符+子查询语句表达的意思是一样的 ## 但对于表达同样意思的不同于据来说,既要考虑语句的性能,也要考虑语句的可读性
ALL 关键字 in 子查询
## 比client id为3的所有发票都大的发票 select * from invoices where invoice_total > ( select max(invoice_total) from invoices where client_id = 3 ) select * from invoices where invoice_total > all ( select invoice_total from invoices where client_id = 3 ) ## 每一条invoice记录都会和所有的子句返回的值进行比较,返回大于所有值的结果
ANY / SOME 关键字 in 子查询
## 至少有两个发票的顾客 select * from clients where client_id in ( select client_id from invoices group by client_id having count(*) >= 2 ) ## = any 相当于 in select * from clients where client_id = any ( select client_id from invoices group by client_id having count(*) >= 2 )
相关子查询(Correlated SubQuery)
-- 对于 employees 中的每个记录e -- 计算employees中满足office_id=e.office_id条件的avg salary -- 如果e.salary > avg salary 返回记录 -- 在这中情况下 子查询会在每次主查询的每条记录执行一次 所以性能会有所降低 select * from employees e where salary > ( select avg(salary) from employees where office_id = e.office_id -- 在这个子查询中,有个条件是依赖外部查询的 )
EXISTS 操作符
## 有发票的顾客 select * from clients c where exists ( -- 表达的意思和in操作符一样,只不过当in()中的结果集很大时,会有负面的性能开销;使用exists相关子查询更好 select client_id from invoices where client_id = c.client_id -- 相关子查询 )
SELECT子句中的子查询
select invoice_id, invoice_total, (select avg(invoice_total) from invoices) as invoice_average, invoice_total - (select invoice_average) as difference -- 不能写成 invoice_total - invoice_average,因为不能在表达式中使用别名 from invoices 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;
FROM子句中的子查询
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_summary -- FROM子句中子查询的结果必须要有别名 where total_sales is not null
这篇关于mysql基础入门总结 复杂查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-21MySQL集群部署资料:新手入门教程
- 2024-09-21MySQL集群资料:初学者入门指南
- 2024-09-21部署MySQL集群资料:新手入门教程
- 2024-09-20MySQL集群部署教程:新手入门指南
- 2024-09-20MySQL集群教程:初学者必备指南
- 2024-09-20部署MySQL集群项目实战:新手入门教程
- 2024-09-20如何部署MySQL集群:简单教程
- 2024-09-20MySQL集群部署:新手入门指南
- 2024-09-20部署MySQL集群学习:入门指南
- 2024-09-20部署MySQL集群入门教程