首页 > 数据库 > 数据库其它

进阶Ⅱ-各种各样的嵌套也太难了

admin 数据库其它 2021-04-25 18:48:22 sql  
后台-系统设置-扩展变量-手机广告位-内容正文底部

这一半的内容花了我一天的时间 T T 还是还好都被我磨出来了

今日继续

 

3.7关联子查询

先看product表,我们想选取出各商品种类中高于该商品种类的平均销售单价的商品

首先我们先查询不同的品类和商品的售价

接下来我们要把售价大于均值的品类商品提取出来

SELECT product_type,product_name,sale_price
 FROM sql_store.product
 where sale_price>

我们暂且把这段码剪切出来放一边,提取高于本品类的均值

我们需要把其中相同品类的售价加起来,先把两端码拼在一起

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);

得到结果

在group by前面用where能将两个表连接起来,这里是内连接是和full join类似,所以这段语句也可以用join来写,但是mysql不能用fulljoin 你说气人不气人。其实left 和right join相当于是匹配 而where 和full join相当于提取就是左右表有的全部提取出来

这里有一篇join和where当作连接语句的讲解https://blog.csdn.net/xyr05288/article/details/51852321

我们用join写写试试 是可以和上面输出同样结果的 不过不是在mysql中使用。其实用where更方便一些,记住where的用法就好了

select p1.product_type,p1.product_name,p1.sale_price from product AS p1
full join
(SELECT 
  product_type, 
  product_name, 
  sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price)from product))p2
 on p1.product_type=p2.product_type
 group by p1.product_type
 

练习题答案:

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-9-20'

2、

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

插入时报错视图插入数据时,原表也会插入数据,而原表数据插入时不满足约束条件,所以会报错。(因为 ViewPractice5_1 的原表有三个带有 NOT NULL 约束的字段)

3、SELECT product_id,product_name,product_type,sale_price,
(select avg(sale_price)from product) as sale_price_all
 FROM product

 

4、

3.7各种各样的函数

算数函数:+ - * / ;ABS – 绝对值 语法:ABS( 数值 );MOD – 求余数 语法:MOD( 被除数,除数 );

ROUND – 四舍五入 语法 ROUND( 对象数值,保留小数的位数 )

拼接-CONCAT(str1, str2, str3);LENGTH( 字符串 )字符串长度 LOWER – 小写转换

REPLACE – 字符串的替换 REPLACE( 对象字符串,替换前的字符串,替换后的字符串 )

SUBSTRING_INDEX (原始字符串, 分隔符,n)-字符串按索引截取

SELECT CURRENT_DATE; – 获取当前日期

SELECT CURRENT_TIME;– 当前时间

SELECT CURRENT_TIMESTAMP;– 当前日期和时间

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.8转换函数

-- 将字符串类型转换为数值类型 SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

-- 将字符串类型转换为日期类型 SELECT CAST('2009-12-14' AS DATE) AS date_col;

-- 将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.9谓词

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

4.0 CASE 表达式 重要 重要 重要

语法:依次判断 when 表达式是否为真值,是则执行 THEN 后的语句,如果所有的 when 表达式均为假,则执行 ELSE 后的语句。

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

举个例子,现在要把product表变成这样

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;

第二种写法:聚合+转列

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

练习题答案:

1、正确

2、放入执行就好

3、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;

文章来源:https://blog.csdn.net/feiluyi/article/details/116091756

后台-系统设置-扩展变量-手机广告位-内容正文底部
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:https://jcdi.cn/shujuku/33ada81b96ff27344df39d0a96e8297a.html

留言与评论(共有 0 条评论)
   
验证码:
后台-系统设置-扩展变量-手机广告位-评论底部广告位

教程弟

https://www.jcdi.cn/

统计代码 | 京ICP1234567-2号

Powered By 教程弟 教程弟

使用手机软件扫描微信二维码