SQL零基础学习笔记(五)
2021/8/25 2:06:09
本文主要是介绍SQL零基础学习笔记(五),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
多表查询
一、标量子查询
SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name='奶茶'; SELECT * FROM milk_tea AS m1 WHERE m1.sale_price> ( SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name='奶茶'); SELECT m1.*, (SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name='奶茶') FROM milk_tea AS m1; SELECT p.class,AVG(p.sale_price) FROM prod_info AS p GROUP BY class HAVING AVG(p.sale_price) > 15; SELECT p.class,AVG(p.sale_price) FROM prod_info AS p GROUP BY class HAVING AVG(p.sale_price) > (SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name='奶茶') ; SELECT AVG(p.sale_price) FROM prod_info AS p WHERE p.class='日用品'; ---34.96 SELECT * FROM prod_info AS P1 WHERE p1.class='日用品' AND p1.sale_price >( SELECT AVG(p.sale_price) FROM prod_info AS p WHERE p.class='日用品');
二、关联子查询
SELECT * FROM prod_info AS P1 WHERE p1.sale_price >(#关联子查询 SELECT AVG(p.sale_price) FROM prod_info AS p WHERE p.class=p1.class);
三、普通子查询
SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price= 15; SELECT * FROM milk_tea AS m WHERE m.prod_name IN('奶茶','薯片','薯条'); SELECT * FROM milk_tea AS m WHERE m.prod_name IN( SELECT m1.prod_name FROM milk_tea AS m1 WHERE m1.sale_price= 15);
SELECT p.prod_name, p.type, p.sale_price FROM prod_info AS p WHERE p.prod_name='抽纸'; SELECT b.type FROM ( #将选出的三列作为新的目标表 SELECT p.prod_name, p.type, p.sale_price FROM prod_info AS p WHERE p.prod_name='抽纸') AS b WHERE b.sale_price > 26;
四、表联结
关联多个表,在一行中输出。
(一)内部联结
SELECT *FROM prod_info AS p ; SELECT * FROM supplier_info AS s; SELECT p.*, s.* FROM prod_info AS p, supplier_info AS s WHERE p.supplier_id= s.supplier_id;
(二)自联结
SELECT p.* FROM prod_info AS p, order_list AS l WHERE p.prod_id=l.prod_id AND l.order_id='20190403001'; SELECT p.* FROM prod_info AS p INNER JOIN order_list AS l ON p.prod_id=l.prod_id AND l.order_id='20190403001';
(三)外部联结
SELECT * FROM cust_info AS c; SELECT * FROM order_list AS l WHERE l.order_id LIKE '20190407%'; SELECT c.*,l.* FROM cust_info AS c LEFT JOIN order_list AS l ON c.cust_id=l.cust_id AND l.order_id LIKE '20190407%';
INNER JOIN 相当于并集
OUTER JOIN 相当于交集
五、组合查询 UNION
这篇关于SQL零基础学习笔记(五)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-07-08如何在敏捷项目中实现高效测试?
- 2024-07-08用户故事一定要有 “So that...” 吗?
- 2024-07-04TiDB 资源管控的对撞测试以及最佳实践架构
- 2024-07-03万字长文聊聊Web3的组成架构
- 2024-07-02springboot项目无法注册到nacos-icode9专业技术文章分享
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt