【数据库03】复杂一点的查询（视图、子查询、CASE表达式）

最新推荐文章于 2024-06-17 08:45:58 发布
最新推荐文章于 2024-06-17 08:45:58 发布
阅读量466 收藏 2
点赞数 1
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a8689756/article/details/119840597
版权

目录

1. 视图

1.1 视图概述

1.什么是视图
视图是一个虚拟的表，不同于直接操作数据表，视图是依据 SELECT 语句来创建的，操作视图时会根据创建视图的 SELECT 语句生成一张虚拟表，然后在这张虚拟表上做 SQL操作。
2. 视图和表的区别
视图和表的区别：是否保存了实际的数据
【注意】视图不是表，视图是虚表，视图依赖于表
在这里插入图片描述
3.视图的作用
为什么有了表还需要视图呢？

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

1.2 创建视图

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图，但是应尽量减少这种做法，多重视图会造成SQL性能降低
在这里插入图片描述
【注意】
在一般的DBMS 中定义视图时不能使用 ORDER BY 语句，是因为视图和表一样，数据行都是没有顺序的。
在 MySQL 中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使
用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

-- 错误示范
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
ORDER BY product_type;
  • 基于单表的视图
    在product表的基础上创建一个视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type ;

结果如下

在这里插入图片描述

  • 基于多表的视图
-- 创建一张新表
CREATE TABLE shop_product
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));

--向新表中插入数据
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000A’, ’东京’, ’0001’, 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000A’, ’东京’, ’0002’, 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000A’, ’东京’, ’0003’, 15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000B’, ’名古屋’, ’0002’, 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000B’, ’名古屋’, ’0003’, 120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000B’, ’名古屋’, ’0004’, 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000B’, ’名古屋’, ’0006’, 10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000B’, ’名古屋’, ’0007’, 40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000C’, ’大阪’, ’0003’, 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000C’, ’大阪’, ’0004’, 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000C’, ’大阪’, ’0006’, 90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000C’, ’大阪’, ’0007’, 70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES (’000D’, ’福冈’, ’0001’, 100);

--在product表和shop_product表的基础上创建视图
CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
FROM product,
shop_product
 WHERE product.product_id = shop_product.product_id;

最终得到如下视图
在这里插入图片描述
正在上面的视图的基础上执行查询操作，找到product_type = ’衣服’的信息

SELECT sale_price, shop_name
FROM view_shop_product
WHERE product_type = ’衣服’;

在这里插入图片描述

1.3 修改视图结构

ALTER VIEW <视图名> AS <SELECT语句>

视图名在数据库中需要是唯一的，不能与其他视图和表重名，当然也可以通过将当前视图删除然后重新创建的方式达到修改的效果

ALTER VIEW productSum
AS
SELECT product_type, sale_price
FROM Product
WHERE regist_date > ’2009-09-11’

在这里插入图片描述

1.4 更新视图的内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改，否则将会破坏视图和表的一致性。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

  • 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
  • DISTINCT 关键字。
  • GROUP BY 子句。
  • HAVING 子句。
  • UNION 或 UNION ALL 运算符。
  • FROM 子句中包含多个表。
UPDATE productsum
SET sale_price = ’5000’
WHERE product_type = ’办公用品’;`

可以发现此时视图更新了
在这里插入图片描述
原表中的对应数据被更新了，但是注意并非原表中的所有办公用品销售价格都被更新了，而是只更新了视图中对应的那一条
在这里插入图片描述

1.5 删除视图

DROP VIEW <视图名1> [ , <视图名2> …]

【注意】需要有相应的权限才能成功删除。

2. 子查询

2.1 子查询概述

1.什么是子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，在SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表
2.子查询和视图的关系
子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了
3.嵌套子查询
与在视图上再定义视图类似，子查询也没有具体的限制

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;

其中最内层的子查询我们将其命名为 productSum，这条语句根据 product_type 分组并查询个数，第二层查询中将个数为 4 的商品查询出来，最外层查询 product_type 和 cnt_product 两列。
【注意】应尽量避免使用子查询，子查询嵌套的层数的叠加，SQL执行效率会变低

2.2 标量子查询

标量就是单一的意思，标量子查询就是要求我们执行的 SQL 语句只能返回一个值，也就是要返回表中具体的某一行的某一列

通过标量子查询语句查询出销售单价高于平均销售单价的商品。

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 子句

2.3 关联子查询

关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的
在子查询中像标量子查询，嵌套子查询或者关联子查询可以看作是子查询的一种操作方式即可
选取出各商品种类中高于该商品种类的平均销售单价的商品
该语句为关联子查询，将外面的 product表标记为 p1，将内部的 product 设置为 p2，而且通过 WHERE 语句连接了两个查询

其过程可以概括为

  1. 首先执行不带 WHERE 的主查询
  2. 根据主查询讯结果匹配 product_type，获取子查询结果
  3. 将子查询结果再与主查询结合执行完整的 SQL 语句
SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
					FROM product ASp2
 					WHERE p1.product_type =p2.product_type
GROUP BY product_type);

3. 各种各样的函数

sql 自带了各种各样的函数，极大提高了 sql 语言的便利性。
所谓函数，类似一个黑盒子，你给它一个输入值，它便按照预设的程序定义给出返回值，输入值称为参数 。
函数大致分为如下几类：

  • 算术函数（用来进行数值计算的函数）
  • 字符串函数（用来进行字符串操作的函数）
  • 日期函数（用来进行日期操作的函数）
  • 转换函数（用来转换数据类型和值的函数）
  • 聚合函数（用来进行数据聚合的函数）

2.1 算法函数

除了+ - * / 四则运算还有

  • ABS ： 绝对值函数，ABS( 数值)，当 ABS 函数的参数为 ‘NULL‘时，返回值也是 ‘NULL‘。
  • MOD ：求余数‘MOD( 被除数，除数)小数没有余数的概念，只能对整数列求余数
  • ROUND：四舍五入ROUND( 对象数值，保留小数的位数)，当参数 保留小数的位数为变量时，可能会遇到错误，请谨慎使用变量

有如下表samplemath

mnp
500.0000NULL
-180.0000NULL
30NULLNULL
NULL73
NULL52
NULL4NULL
8.000NULL3
352.2701
5.5552NULL
NULL1NULL
8.760NULLNULL
SELECT m,ABS(m) AS abs_col ,n, p,MOD(n, p) AS mod_col, ROUND(m,1) AS round_colS
FROM samplemath;

执行上面语句得到新表

mabs_colnpmod_colround_col
10500.000500.0000NULLNULL
-180.000180.0000NULLNULL-180.0
NULLNULLNULLNULLNULLNULL
NULLNULL731NULL
NULLNULL521NULL
15NULLNULL4NULLNULL
8.0008.000NULL3NULL8.0
2.2702.2701NULLNULL2.3
5.5555.5552NULLNULL5.6
NULLNULL1NULLNULLNULL
208.7608.760NULLNULLNULL

3.2 字符串函数

  • CONCAT：拼接CONCAT(str1, str2, str3)
  • LENGTH：字符串长度LENGTH( 字符串)
  • LOWER：小写转换
  • UPPER ：大写转换
  • REPLACE：字符串替换REPLACE( 对象字符串，替换前的字符串，替换后的字符串)
  • SUBSTRING：字符串截取SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）索引值起始为 1
  • SUBSTRING_INDEX： 字符串按索引SUBSTRING_INDEX (原始字符串，分隔符，n)，该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
str1str2str3
opxrtNULL
abcdefNULL
30太阳月亮
aaaNULLNULL
NULLxyzNULL
@!#$%NULLNULL
ABCNULLNULL
35aBCNULL
abc哈哈abcABC
abcdefabcabcABC
micmiciI

下面进行字符串操作在这里插入图片描述
注意下字符串替换
REPLACE：字符串替换REPLACE( 对象字符串，替换前的字符串，替换后的字符串)
把字符串2在字符串1中出现的内容替换成了字符串3
在这里插入图片描述
SUBSTRING_INDEX

SELECT SUBSTRING_INDEX(’www.mysql.com’, ’.’, 2);

得到www.mysql

SELECT SUBSTRING_INDEX(’www.mysql.com’, ’.’, -2);

得到mysql.com

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(’www.mysql.com’, ’.’, 2), ’.’, -1);

得到mysql

3.3 日期函数

  • CURRENT_DATE： 获取当前日期SELECT CURRENT_DATE；就会返回当前日期
  • CURRENT_TIME： 当前时间SELECT CURRENT_TIME；
  • CURRENT_TIMESTAMP： 当前日期和时间SELECT CURRENT_TIMESTAMP;
  • EXTRACT：截取日期元素EXTRACT(日期元素 FROM 日期)使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

得到

nowyearmonthdayhourMINutesecond
2020-08-08 17:34:38202088173438

3.4 转换函数

“转换”这个词在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换

  • CAST：类型转换CAST（转换前的值 AS 想要转换的数据类型）
-- 将字符串类型转换为数值类型
SELECT CAST(’0001’ AS SIGNED INTEGER) AS int_col;

-- 将字符串类型转换为日期类型
SELECT CAST(’2009-12-14’ AS DATE) AS date_col;
  • OALESCE：将 NULL 转换为其他值COALESCE(数据 1，数据 2，数据 3……)
    COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1 个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, ’hello world’, NULL) AS col_2,
COALESCE(NULL, NULL, ’2020-11-01’) AS col_3;

4.谓词

4.1 谓词概述

谓词就是返回值为真值的函数。包括 ‘TRUE / FALSE / UNKNOWN‘。
谓词主要有以下几个：

  • LIKE
  • BETWEEN
  • IS NULL、IS NOT NULL
  • IN
  • EXISTS

4.1 LIKE谓词

LIKE 谓词 ： 用于字符串的部分一致查询，部分一致大体可以分为前方一致、中间一致和后方一致三种类型
通配符有

  • %
  • _ 下划线匹配任意 1 个字使用 _（下划线）来代替

有表

strcol
abcdd
abcddd
abddc
abdddc
ddabc
dddabc

（1）前方一致
作为查询条件的字符串（这里是“ddd”）与查询对象字符串起始部分相同

SELECT *
FROM samplelike
WHERE strcol LIKE ’ddd%’;

结果为 dddabc
（2）中间一致：即查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字符串的最后还是中间都没有关系。

SELECT *
FROM samplelike
WHERE strcol LIKE ’%ddd%’;

结果为abcddd、 abdddc、 dddabc
（3）后方一致

SELECT *
FROM samplelike
WHERE strcol LIKE ’%ddd’;

得到 abcddd
（4）_ 下划线匹配任意 1 个字符，使用 _（下划线）来代替

SELECT *
FROM samplelike
WHERE strcol LIKE ’abc__’;

得到abcdd

4.3 BETWEEN 谓词 – 用于范围查询

-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

【注意】BETWEEN查询得到的是【闭区间】，如果不想包含100和1000的话用＞、＜实现

SELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;

4.4 IS NULL、IS NOT NULL – 用于判断是否为 NULL

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词 IS NULL。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

4.5 IN 谓词 – OR 的简便用法

多个查询条件取并集时可以选择使用 or语句。

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

--上面的式子等价于
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);

--如果想找不等于这几个值的使用NOT IN
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);

4.6 使用子查询作为 IN 谓词的参数

  • IN 和子查询/NOT IN和子查询
    IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数（也可以将视图作为IN的参数）
shop_idshop_nameproduct_idquantity
000A东京000130
000A东京000250
000A东京000315
000B名古屋000230
000B名古屋0003120
000B名古屋000420
000B名古屋000610
000B名古屋000740
000C大阪000320
000C大阪000450
000C大阪000690
000C大阪000770
000D福冈0001100

想要取出大阪在售商品的销售单价，该如何实现呢？
第一步，取出大阪门店的在售商品 ‘product_id ;
第二步，取出大阪门店在售商品的销售单价 ‘sale_price

--step1：取出大阪门店的在售商品 product_id
SELECT product_id
FROM shopproduct
WHERE shop_id = ’000C’;

-- step2：取出大阪门店在售商品的销售单价 sale_price
SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
					 FROM shopproduct
					WHERE shop_id = '000C');

-- 子查询展开后的结果
SELECT product_name, sale_price
FROM product
WHERE product_id IN (’0003’, ’0004’, ’0006’, ’0007’);

既然 in 谓词也能实现，那为什么还要使用子查询呢？

  • 实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
  • 实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性

4.7 EXIST 谓词

谓词的作用就是 **“判断是否存在满足某种条件的记录”**如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是“记录”，EXIST 的左侧并没有任何参数，EXIST 只需要在右侧书写 1 个参数，一般情况下EXIST可以使用 IN（或者 NOT IN）来代替

4.6中的例子使用EXIST来实现

SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT *
			FROM shopproduct AS sp
			WHERE sp.shop_id = ’000C’
			AND sp.product_id = p.product_id);

/*由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。EXIST 只会判断是否存在满足子查
询中 WHERE 子句指定的条件“商店编号（shop_id）为’000C’，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真
（TRUE）。因此，使用下面的查询语句，查询结果也不会发生变化。
*/
SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
			FROM shopproduct AS sp
			WHERE sp.shop_id = ’000C’
			AND sp.product_id = p.product_id);

5. CASE表达式

5.1 CASE概述

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

CASE WHEN <求值表达式> THEN <表达式>
	WHEN <求值表达式> THEN <表达式>
	WHEN <求值表达式> THEN <表达式>
ELSE <表达式>
END

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when
表达式均为假，则执行 ELSE 后的语句。无论多么庞大的 CASE 表达式，最后也只会返回一个值

5.2 CASE表达式使用方法

如果想要实现

A ：衣服
B ：办公用品
C ：厨房用具

可以采用

SELECT product_name,
CASE WHEN product_type = ’衣服’ THEN CONCAT(’A ： ’,product_type)
	 WHEN product_type = ’办公用品’ THEN CONCAT(’B ： ’,product_type)
	 WHEN product_type = ’厨房用具’ THEN CONCAT(’C ： ’,product_type)
ELSE NULL
END AS abc_product_type
FROM product;

得到

product_nameabc_product_type
T恤A ： 衣服
打孔器B ： 办公用品
运动T恤A ： 衣服
菜刀C ： 厨房用具
高压锅C ： 厨房用具
叉子C ： 厨房用具
擦菜板C ： 厨房用具
圆珠笔B ： 办公用品

【注意】ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。此外，CASE 表达式最后的“END”是不能省略的，忘记书写 END 会发生语法错误

应用场景2 ：实现列方向上的聚合
通过

SELECT product_type,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type;

得到了

product_typesum_price
衣服5000
办公用品600
厨房用具11180

如果想把上面的表变成在列方向上的聚合值，可以通过聚合函数 + CASE WHEN 表达式即可实现该效果

sum_price_clothessum_price_kitchensum_price_office
500011180600
-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = ’衣服’ THEN sale_price ELSE 0 END) AS sum_price_clothes,
	   SUM(CASE WHEN product_type = ’厨房用具’ THEN sale_price ELSE 0 END) AS sum_price_kitchen,
	   SUM(CASE WHEN product_type = ’办公用品’ THEN sale_price ELSE 0 END) AS sum_price_office FROM product;

应用场景3：实现行转列
在这里插入图片描述
在这里插入图片描述

-- CASE WHEN 实现数字列 score 行转列
SELECT name,
		SUM(CASE WHEN subject = ’语文’ THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = ’数学’ THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = ’外语’ THEN score ELSE null END) as english
 FROM score
GROUP BY name;

上述代码实现了数字列 score 的行转列，也可以实现文本列 subject 的行转列

-- CASE WHEN 实现文本列 subject 行转列
SELECT name,
	MAX(CASE WHEN subject = ’语文’ THEN subject ELSE null END) as chinese,
	MAX(CASE WHEN subject = ’数学’ THEN subject ELSE null END) as math,
	MIN(CASE WHEN subject = ’外语’ THEN subject ELSE null END) as english
FROM score
GROUP BY name;

得到

namechinesemathenglish
张三语文数学外语
李四语文数学外语

【总结】

  • 当待转换列为数字时，可以使用 ‘SUM AVG MAX MIN‘等聚合函数；
  • 当待转换列为文本时，可以使用 ‘MAX MIN‘等聚合函数

6. 练习题

1. 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照
表，假设表中包含初始状态的 8 行数据。
• 条件 1：销售单价大于等于 1000 日元。
• 条件 2：登记日期是 2009 年 9 月 20 日。
• 条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;
product_namesale_priceregist_date
T恤衫10002009-09-20
菜刀30002009-09-20

【习题一】

-- 创建视图
CREAT 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_date='2009-09-20';
-- 查询视图
SELECT *
FROM ViewPractice5_1;

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

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

会发生错误，对于视图执行插入其实质还是在原表上进行更新，上述语句相当于在原表中执行INSERT INTO Product VALUES (NULL, '刀子', NULL, 300, NULL, '2009-11-02');
第1、3、4列由于没有赋值，也没有默认值，会自动设为null。
而由于第1、3列都有not null约束，因此会发生错误。
3. 请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

product_idproduct_nameproduct_typesale_pricesale_price_all
0001T恤衫衣服10002097.5000000000000000
0002打孔器办公用品5002097.5000000000000000
5 0003运动T恤衣服40002097.5000000000000000
0004菜刀厨房用具30002097.5000000000000000
0005高压锅厨房用具68002097.5000000000000000
0006叉子厨房用具5002097.5000000000000000
0007擦菜板厨房用具8802097.5000000000000000
10 0008圆珠笔办公用品1002097.5000000000000000
SELECT product_id,product_name, product_type, sale_price,(SELECT AVG(sale_price)
			FROM product)AS sale_price_all
FROM product 
ORDER BY product_id ;

4.请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为 AvgPriceByType）。
提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

product_idproduct_nameproduct_typesale_priceavg_sale_price
0001T恤衫衣服10002500.0000000000000000
0002打孔器办公用品500300.0000000000000000
0003运动T恤衣服40002500.0000000000000000
0004菜刀厨房用具30002795.0000000000000000
0005高压锅厨房用具68002795.0000000000000000
0006叉子厨房用具5002795.0000000000000000
0007擦菜板厨房用具8802795.0000000000000000
0008圆珠笔办公用品100300.0000000000000000
CREATE VIEW AvgPriceType (product_id, product_name, product_type, sale_price, avg_sale_price)
AS
SELECT product_id, 
       product_name, 
       product_type, 
       sale_price,
       (SELECT AVG(sale_price)
          FROM Product AS P2
         WHERE P1.product_type = P2.product_type) AS avg_sale_price
FROM Product AS P1;

SELECT *
FROM AvgPriceByType;

5.运算或者函数中含有 NULL 时，结果全都会变为 NULL ？（判断题）
错误，比如字符串函数中就不一定
6.对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);

查找售价不等于500, 2800, 5000的商品名称和售价

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

无法取出任何记录，not in的参数中不能包含null，否则结果为空
【注意】不仅如此，使用子查询作为not in的参数时，子查询的返回值也不能为null
7.按照销售单价（sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。
• 低档商品：销售单价在 1000 日元以下（T 恤衫、办公用品、叉子、擦菜板、圆珠笔）
• 中档商品：销售单价在 1001 日元以上 3000 日元以下（菜刀）
• 高档商品：销售单价在 3001 日元以上（运动 T 恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。执行结果

low_pricemid_pricehigh_price
512
/*
   读取Product的每一条记录，然后先看第一列的内容，即低档商品的数量
   使用case，对每一条记录做判断，若属于低档，则返回1，否则返回0
   然后用sum函数统计每条记录case表达式返回值的和
   由于低档商品的记录返回1，其他返回0，故得到的和就是低档商品的数量
   第二第三列同理
*/
编辑于 20
SELECT SUM(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
       SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) AS mid_price,
       SUM(CASE WHEN sale_price >= 3001 THEN 1  ELSE 0 END) AS high_price
FROM Product;

`

一一张xi
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql 复杂查询，一些经典案例
07-12
sql 复杂查询，一些经典查询，希望对朋友有帮助！一些经典案例
数据库--复杂查询
qq_56324572的博客
01-13 1143
给定一个字段/表达式作为分组的依据，通过 group by 来体现， 根据这个字段中每一行的值，得到最终的分组。PS：聚合查询中，只能出现聚合函数，不能出现记录的原字段。-- sum/avg/max/min 遇到 null（包含其他非数字字段），聚合的时候就不考虑这些行了。聚合查询的 select 后边，可以出现两种东西：聚合函数、分组聚合时分组的依据字段。：将一次查询的的查询结果看作一张“表”，在这个结果继续查询，可以一直嵌套。：先通过where做记录的筛选，然后针对满足条件的记录做聚合。
MySQL入门学习-子查询.EXISTS
最新发布
weixin_69763181的博客
06-17 524
例如，可以使用子查询 EXISTS 检查子查询结果集中是否存在特定的值，或者使用子查询 EXISTS 与其他操作符结合使用，以实现更复杂的条件判断。与其他子查询类型相比，子查询 EXISTS 在某些情况下更高效，特别是在子查询结果集较大且只关心结果集是否为空的情况下。子查询 EXISTS 是 MySQL 中的一种子查询类型，用于检查子查询结果集是否为空。否则，返回 FALSE。子查询 EXISTS 的使用方法是在查询语句中嵌套一个子查询，并使用 EXISTS 关键字指定子查询结果集是否为空。
复杂的sql数据库查询
u010890857的专栏
01-20 722
遇到复杂的数据库查询，可使用中间表的方式连接查询查出正确的数据： SELECT d.id, c.* FROM diz_reply AS d INNER JOIN ( SELECT target_id, count(id) num, a.to_userid, a.model FROM diz_user_message AS a WHERE a.isRead = '0'
数据库查询之复杂查询
天暮雨的博客
11-10 670
分组函数 COUNT(列名/*): 查询出满足条件的记录行数 MAX(列名): 查询指定列上的最大值 MIN(列名): 查询指定列上的最小值 SUM(列名): 查询指定列上的所有数据的总和   AVG(列名): 查询指定列上的评价值(Average) 示例：查询所有员工的数量   SELECT  COUNT(eid)  FROM emp;   SELECT  COUNT(*)  F
数据库中常用的复杂查询，希望对你有所帮助
不可以庸俗堕落的博客
06-02 1051
在数据库操作中，sql语句结束时一定要带上 ; 号 否则不会执行。 1.一般特性 1.查看当前用户——show user; 2.查看当前用户有哪些表——select table_name from user_tables; 3.查看emp表的结构——desc emp; 4.列出emp表的所有列——select * from emp; 5.查看指定列——select ename from emp; ...
MySQL初阶【3】 —— 数据库约束及复杂查询SQL
LU_ZE_XX的博客
03-08 927
数据库的若干约束，包含：primary key，foreign key的详细用法。更高级的查询语句，聚合查询、联合查询！读此一文足矣！
复杂查询 指什么，包括哪些
软件工程小施同学 的专栏
06-08 334
复杂查询主要包括多维查询、最近邻查询(KNN)、Top-K 查询、Join 以及 KNN-Join 等《云数据管理索引技术研究》
数据库系统——第七讲 SQL语言之复杂查询与视图
lingran234的博客
03-11 615
数据库系统——第七讲 SQL语言之复杂查询与视图SQL语言之子查询运用1.IN子查询2.ThetaSome/Thetaall子查询3.Exists子查询SQL语言之结果计算与聚集函数SQL语言之分组查询与分组过滤1.分组查询2.分组过滤利用SQL语言实现关系代数操作SQL语言之视图及其应用总结 SQL语言之子查询运用 重点与难点：SQL-SELECT：IN | NOT IN, Exists | NOT Exists 1.IN子查询 子查询：出现在Where子句中的Select语句被称为子查询(subque
数据库 7---SQL语言复杂查询与视图
hala22的博客
03-22 356
导读1.IN子查询 2.Theta some 子查询 3.Exists 子查询 4.结果计算与聚集计算 5.分组聚集计算与分组过滤 6.用SQL语句表达关系代数运算 7.SQL视图
数据库3：复杂查询方法-视图，函数等-天池龙珠计划SQL训练营
m0_69120168的博客
04-06 2092
一、视图是什么 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操 二、视图存在的原因 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。 通过定义视图可以使用户看到的数据更加清晰。 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。 通过定义视图可以降低数据的冗余。 三、创建视图 创建视图的语法：CREATE VIEW <视图名称>(<列名1&
Codesys case实例源码，带可视化视图
09-16
Codesys case实例源码，带可视化视图，供大家下载学习！ 积分已修改适合新人使用
SQL学习——复杂查询方法
luschka的博客
02-27 609
复杂查询方法 视图 定义 视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作 视图与表的区别—“是否保存了实际的数据”。视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。 优点 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。 通过定义视
【SQL优化】子查询展开
Qiuht的技术博客
10-09 914
下面是很早之前的优化例子，一直保存在本地笔记中，今天分享出来： 交谈中请勿轻信汇款、中奖信息、陌生电话，勿使用外挂软件。 xxx网友  19:06:29 哪位大哥大姐帮我优化一下SQl 我数据记录太多 加载不了啊 我  19:07:43 可以贴上来研究哈 xxx网友  19:07:57 select (select org_name from o_org where org_
sql视图使用case，when
吉晨的博客
12-14 3623
视图用 case when 需要 用如下格式，[需要的列名]= case when...,而表里面的case 不用这样 [isNormal]= CASE WHEN dbo.c_bdm_head.I_E_FLAG = '1' OR dbo.c_bdm_head.AISSTATUS = '0'   THEN '未删除' ELSE '已删除' END
oracle数据库对象---视图、case表达式
睡不醒的每天
10-25 1156
语法 1Slect 列名,（Case 条件表达式 when 值1 then 处理 when 值1 then 处理...else 其他处理 end） from 表名。
深入解析Oracle数据库中的CASE表达式原理及优化技巧
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-31 1665
另外，当CASE出现在GROUP BY、ORDER BY或者JOIN条件中时，需要格外注意其对查询性能的影响，并考虑是否有更优的索引或数据模型设计可以替代。表达式广泛应用于数据转换、条件计算、逻辑判断等多种场景，包括在SELECT列表中格式化输出、在WHERE子句中进行动态过滤以及在UPDATE或INSERT语句中设置条件性的值。表达式是一种条件控制结构，类似于其他编程语言中的if-then-else语句，它可以在SQL查询中根据不同的条件返回不同的值。优化Oracle数据库中的。Oracle数据库中的。
MySQL子查询(关键字any,all,exist,in,以及算术运算符的使用)
m0_56245143的博客
02-19 779
先引入子查询的基本概念，它实际上是一个完整的条件查询语句，只是这个语句恰好又是外面语句的一个查询的条件，即查询的嵌套问题。在子查询中通常可以使用比较和运算符这些，以及in,any,all,exists等的关键字。下面我们进入学习关键字。
sql子查询之exist后面的子查询
youhebuke225的博客
09-02 1501
执行步骤IN 上面的 语句我们也可以使用来代替，等效于sql语句如下
Oracle数据库基础教程：从SQL到查询操作
包括数据库概念、表的创建、SQL分类、数据查询、SQLPlus命令、条件查询、排序、数据处理函数、聚合函数、分组查询以及连接查询和子查询等多个方面，旨在提供全面的Oracle数据库操作知识。" 在Oracle数据库管理中，...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值