SQL基础学习笔记--4--复杂查询

4.1 视图

 

创建视图

CREATE VIEW shitu (stype, sname) AS SELECT type, NAME FROM pp2;

使用视图(当成普通的表来查询使用)

SELECT * FROM shitu

注意:多重视图会降低SQL性能

删除视图

DROP VIEW shitu;

 

4.2 子查询

 

使用子查询

SELECT type1, name1 FROM ( SELECT product_type AS type1, product_name AS name1 FROM product ) AS xin

关键字AS可省略

 

标量子查询

标量子查询绝对不能返回多行结果

SELECT * FROM product WHERE sale_price < ( SELECT AVG(sale_price) FROM product )

 

4.3 关联子查询

 

创建关联子查询

SELECT * FROM product AS p1 WHERE sale_price > ( SELECT AVG(sale_price) FROM product AS p2 WHERE p1.product_type = p2.product_type GROUP BY product_type )