【MySQL】视图&子查询

最新推荐文章于 2024-01-06 19:55:51 发布

网站不知名写手柠乐同学

最新推荐文章于 2024-01-06 19:55:51 发布

阅读量429

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.csdn.net/kyra1997/article/details/111461273

版权

笔记专栏收录该内容

69 篇文章 5 订阅

订阅专栏

视图

SELECT stu_name FROM view_students_info;

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

视图存在定义

那既然已经有数据表了，为什么还需要视图呢？主要有以下几点原因：

通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

创建视图

基本语法
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
---------------------------------------------------------
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
 这里不能用这是因为视图和表一样，数据行都是没有顺序的。
 ORDER BY product_type;//

单

多

视图查询

视图修改

视图更新

视图删除

子查询

https://www.cnblogs.com/fzxey/p/10896244.html (参考链接)

嵌套子查询

SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                     COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;

标量子查询

标量就是单一的意思，那么标量子查询也就是单一的子查询，那什么叫做单一的子查询呢？

所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。例如我们有下面这样一张表

product_id | product_name | sale_price 
------------+-------------+----------
0003       | 运动T恤       | 4000 
0004       | 菜刀          | 3000 
0005       | 高压锅        | 6800

那么我们执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果。
我们现在已经知道标量子查询可以返回一个值了，那么它有什么作用呢？

直接这样想可能会有些困难，让我们看几个具体的需求：

查询出销售单价高于平均销售单价的商品
查询出注册日期最晚的那个商品
你有思路了吗？

让我们看如何通过标量子查询语句查询出销售单价高于平均销售单价的商品。

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

上面的这条语句首先后半部分查询出product表中的平均售价，前面的sql语句在根据WHERE条件挑选出合适的商品。
由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

列出歐州每國家的人均GDP，當中人均GDP要高於英國’United Kingdom’的數值。

select name from world
where gdp/population > 
(    select gdp/population from world
     where name = 'United Kingdom'
)and continent = 'Europe'

在阿根廷Argentina 及澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

易错不是一个词的时候写in

select name,continent from world 
where continent in (
    select continent from world
     where name in ('Argentina','Australia')
)
order by name

哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。

SQL BETWEEN 语法 SELECT column_name(s) FROM table_name WHERE column_name
BETWEEN value1 AND value2 表示左闭右闭介于

select name,population from world
where population > (
	select population from world 
	where name = 'Canada'
	) 
 and population < (
 	select population from world 
	where name = 'Poland'
	)

Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。

顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

concat,round

select name,concat
(
	round
	(100 * population/
		(
		select population from world where name = 'Germany'
		)
	),'%'
) 
from world
where continent = 'Europe'

all函数

sql all 函数
ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询

all 函数
    ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询

在这里插入图片描述

哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

select name from world
where gdp > all
(
	select gdp from world where continent = 'Europe'and gdp>0
	)

在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area FROM world x -- 在库后面添加x，设world为x库
  WHERE area >= ALL
    (SELECT area FROM world y -- 在库后面添加y，设world为y库
        WHERE y.continent=x.continent
          AND area>0)

列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

我們可以在子查詢，參閱外部查詢的數值。我們為表格再命名，便可以分別內外兩個不同的表格。

select continent,name from world x
where x.name = (select y.name from world y where x.continent = y.continent order by name limit 1)

子应用

执行逻辑：

第1步、取主product表中的product_type列第一个值；
第2步、替换p1.product_type，得到子查询结果；
第3步、判断父查询的WHERE子句条件
第4步、回到整个语句的第一个结果。
循环操作，取完后，最终得出所有的查询结果。

练习

1创建视图

Q:创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

product_name | sale_price | regist_date
--------------+------------+------------
T恤衫         |   1000    | 2009-09-20
菜刀          |    3000    | 2009-09-20

A：

CREATE VIEW ViewPractice5_1(product_name, sale_price, regist_date)
AS
SELECT product_name, sale_price, regist_date FROM product
WHERE sale_price >= 1000 AND regist_data = '2009-09-20';

2

Q：向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

A：更新

3

请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。
A:

CREATE VIEW productsum(product_id,product_name, product_type,sale_price,sale_price_all)
AS
SELECT product_id,product_name, product_type,sale_price, SUM(sale_price)
FROM product;

4

在这里插入图片描述

5

找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

select name,continent,population from world x
where 25000000 >= all(select y.population from world y
where x.continent = y.continent and  population > 0 )

6

有些國家的人口是同洲份的所有其他國的3倍或以上。列出國家名字name 和洲份 continent。

select name,continent from world x
where x.population >= all(
select y.population * 3 from world y 
where y.continent = x.continent and population >0 and x.name!= y.name)