TASK03|SQLcoding复杂一点的查询

最新推荐文章于 2024-07-12 07:13:33 发布

speoki

最新推荐文章于 2024-07-12 07:13:33 发布

阅读量604

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/m0_52024881/article/details/123574899

版权

文章目录

复杂一点的查询

上期错误勘正：

select * 
from product
where purchase_price = null
空数据
select * 
from product
where purchase_price <> null
空数据
select * 
from product
where purchase_price > null
空数据

null属于第三种逻辑，无法比较

复杂一点的查询

3.1 视图

视图的查询

select product_name form view_product;

3.1.1 什么是视图

视图是一个虚拟的表

视图是依据SELECT语句来创建的

3.1.2 视图和表的区别

视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

在这里插入图片描述

3.1.3为什么会存在视图

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

3.1.4如何创建视图

create view ** (column1,columm) as <select ...>

select 语句写在as 关键字之后，
视图名在数据库中需要是唯一的，不能与其他视图和表重名
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。
虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。

Attention：
在一般的DBMS中定义视图时不能使用ORDER BY语句。下面这样定义视图是错误的。

不能在创建视图的时候使用order by语句

数据行都是没有顺序的

在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

在这里插入图片描述

基于单表的视图
在product表的基础上创建一个视图

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

在这里插入图片描述
为了学习多表视图创建一张表shop_product

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;

在这里插入图片描述
联合两个表建立视图，在视图的基础上进行查询,这样的效率比较高

select sale_price,shop_name
from view_shop_product
where product_type = '衣服'

在这里插入图片描述

3.1.5如何修改视图结构 alter

alter view ** as <select..>

3.1.6如何更新视图内容update

视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作

不能被更新的视图(数据一致性)

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

聚合函数SUM(),MIN(),MAX(),COUNT()
DISTINCT 关键字
GROUP BY子句
HAVING子句
UNION或UNION ALL运算符
PROM 子句中包含多个表

如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

UPDATE productsum
set sale_price = '5000'
where product_type='办公用品'

在这里插入图片描述
视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。
只能修改视图里的数据

最好不要用视图修改表

3.1.7如何删除视图(权限)

drop view **1 [, **2..]

需要有相应的权限才能删除

drop view productSum;

如果继续操作这个视图的话就会提示当前操作的内容不存在

3.2 子查询

select ** from(select** from ** group by .. ) as ,,

子查询指一个查询语句嵌套在另一个查询语句内部的查询
子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

3.2.1 子查询和视图的关系

由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了。

3.2.2嵌套子查询

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语句不仅会难以理解而且执行效率也会很差，尽量避免这样的使用

3.2.3标量子查询

标量子查询也就是单一的子查询
我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列
在这里插入图片描述
由于标量子查询的特性，导致标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

在这里插入图片描述

关联子查询

关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的


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

#################

SELECT product_type, product_name, sale_price
  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);

在第二条SQL语句也就是关联子查询中我们将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。

首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句

exercise Part one

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

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。

drop view ViewPractice5_1;
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_date = '2009-09-20' 
	)
	
	SELECT * FROM ViewPractice5_1;

在这里插入图片描述

3.2

插入不进去，如果修改了视图但是原表没有更新。这样数据无法保持一致性

3.3

SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	( SELECT AVG(sale_price) FROM product) AS sale_price_avg 
FROM
	product

3.4

create view AvgPriceByType (product_id,product_name,product_type,sale_price,sale_price_avg_type)
as(
select product_id,product_name,product_type,sale_price,(
select AVG(sale_price) from product  as p2 where p2.product_type = p1.product_type 
)as sale_price_avg_type 
from product as p1

)

3.3 各种各样的函数

函数大致分为如下几类：

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

3.3.1 算术函数


-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m NUMERIC(10,3),
 n INT,
 p INT);

-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;

ABS – 绝对值
当 ABS 函数的参数为NULL时，返回值也是NULL。
MOD – 求余数

SELECT m,
    ABS(m)ASabs_col ,
    n, p,
    MOD(n, p) AS mod_col,
    ROUND(m,1) AS round_col
FROM samplemath;

3.3.2字符串函数

-- DDL ：创建表
USE  shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML：插入数据
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',	'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',	'月亮', '火星');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',	NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- 确认表中的内容
SELECT * FROM samplestr;

concat 拼接 CONCAT(str1, str2, str3)
length字符串长度
LOWER – 小写转换
REPLACE – 字符串的替换REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING – 字符串的截取（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
SUBSTRING_INDEX --字符串按索引截取(原始字符串，分隔符，n)第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1
REPEAT – 字符串按需重复多次

3.3.3日期函数

CURRENT_DATE – 获取当前日期
CURRENT_TIME – 当前时间
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素- EXTRACT(日期元素 FROM 日期)

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;

在这里插入图片描述

3.3.4转换函数

数据类型的转换，简称为类型转换，在英语中称为cast
值的转换。
- CAST – 类型转换
  CAST（转换前的值 AS 想要转换的数据类型）

CAST（转换前的值 AS 想要转换的数据类型）

在这里插入图片描述

COALESCE --将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;

3.4谓词

3.4.1什么是谓词

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

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

3.4.2LIKE谓词-用于字符串的部分一致查询

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

%模糊查询

--前方一致

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
##############
--- 中间一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
---后方一致
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
---

在这里插入图片描述

_ 任意1个字符

_下划线匹配任意 1 个字符

使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。
在这里插入图片描述

在这里插入图片描述

3.4.3BETWEEN谓词 --用于范围查询、闭

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。
在这里插入图片描述

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

为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
在这里插入图片描述

3.4.5IN谓词–OR的简便用法

多个查询条件取并集时可以选择使用or语句。
在这里插入图片描述
SQL优化一下，用in谓词

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

反之想要取出不是320、500、5000的商品的时候可以用NOT IN

在这里插入图片描述
IN和NOT IN 都无法取出NULL数据的, IN可以使用子查询作为其参数

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

IN和子查询
创建一个shopproduct表

假设我们需要取出大阪在售商品的销售单价，该如何实现呢？

第一步，取出大阪门店的在售商品 product_id ;

第二步，取出大阪门店在售商品的销售单价 sale_price
在这里插入图片描述

子查询是从最内层开始执行的（由内而外），因此，上述语句的子查询执行之后，sql 展开成下面的语句

SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');

为什么不用in谓词代替子查询

in谓词维护成本高
子查询可保持语句不变,提高了程序的可维护性

3.4.7EXIST谓词

EXIST 基本上也都可以使用 IN（或者 NOT IN）来代替

谓词的作用就是 “判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是“记录”。

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

在这里插入图片描述

EXISTS的参数

EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
EXIST 通常会使用关联子查询作为参数。
子查询中的SELECT *
商品（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);

在这里插入图片描述
使用NOT EXIST替换NOT IN

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

3.5CASE表达式

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

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种

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

使用方法

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;

ELSE 子句默认为 ELSE NULL。最好显式地写出 ELSE 子句。
此外， CASE 表达式最后的“END”是不能省略的
在这里插入图片描述

应用场景：实现列方向上的聚合、
通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）

在列的方向上展示不同种类额聚合值
聚合函数 + CASE WHEN 表达式即可实现该效果
在这里插入图片描述
（扩展内容）应用场景3：实现行转列

#数字列，文本列
select name,
sum(case when subject='语文' then score else null end)as chinese,
sum(case when subject='英语' then score else null end)as english,
sum(case when subject='数学' then score else null end)as math.
from score
group by name;

练习题part two

3.5

不一定, SUM()， COALESCE() 函数中含有 NULL 时结果不会变为 NULL

3.6

查询进货价不是500，2800，5000的商品
查不出结果，in语句不能包括null

3.7

select count(case when sale_price<=1000 then 1 else null end)as low_price,
count(case when sale_price>1000 And sale_price<=3000 then 1 else null end)as mid_price,
count(case when sale_price>3000 then 1 else null end)as high_price
from product

出处

【课程地址】
https://github.com/datawhalechina/wonderful-sql
【备用地址】
https://gitee.com/datawhalechina/wonderful-sql

speoki

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
TASK03|SQLcoding复杂一点的查询

文章目录复杂一点的查询3.1 视图3.1.1 什么是视图3.1.2 视图和表的区别3.1.3为什么会存在视图3.1.4如何创建视图不能在创建视图的时候使用order by语句3.1.5如何修改视图结构 alter3.1.6如何更新视图内容update不能被更新的视图(数据一致性)3.1.7如何删除视图(权限)3.2 子查询3.2.1 子查询和视图的关系3.2.2嵌套子查询3.2.3标量子查询关联子查询exercise Part one3.23.33.43.3 各种各样的函数3.3.1 算术函数上期错误勘正
复制链接

扫一扫