MySQL-Task02

视图

视图：只能看一看的表，不是真实的数据。
个人理解视图存在的意义：数据库非常庞大，有固定想要查询的列和元素，所以直接查询视图会比重新写select语句方便。数据库更新的时候，视图里相应的数据也会更新。

create view <视图名称>(列名1,列名2,列名3......) as <select语句> 

视图中的select语句中，尽量不要用order by关键字，

怎么修改视图

ALTER VIEW <视图名> AS <SELECT语句>
视图在数据库的名字是唯一的，所以不能和其他的视图/表重名，如果需要修改视图的内容，可以
①删除DROP VIEW <视图名1> [ , <视图名2> …] 再重建
②使用ALTER VIEW <视图名> AS <SELECT语句>

怎么更改视图

更新视图的时候，如果包含以下结构的任意一种都是不可以被更新的：
• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• FROM 子句中包含多个表。
update <视图名> set <需要改变的数据> where <条件判断>
当更新视图的时候，原表也会跟着改变。

子查询

什么是子查询？

一个查询语句嵌套在另一个查询语句内部的查询。相当于本来视图需要用select定义一下，子查询直接把视图的定义用在from里，但是子查询是一次性的，不会像视图一样保存。
嵌套子查询会比较麻烦，执行效率也比较低，尽量避免使用。

什么是标量子查询？

返回单一的某个数据，就是指返回具体的某一行的某一列。
因为返回的是一个单一值，所以任何可以用为常数或者列名的地方都可以用标量子查询。

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 productions as p1
where sale_price >(select avg(sale_price)
from productions as p2 
where p1.product_type = p2.product_type
group by product_type
);

首先执行不带where的主查询->
根据主查询结果匹配子查询中的where，获取子查询结果->
将子查询结果与主查询结合执行

练习题-第一部分

4.1

create view ViewPractice5_1 as
select product_name,sale_price,regist_date
from productions
where sale_price >=1000 and regist_date > '2009-9-20';

select * from ViewPractice5_1;

4.2

0 28 18:35:17 insert into ViewPractice5_1 values(“刀”,300,‘2009-11-02’)
Error Code: 1423. Field of view ‘shop.viewpractice5_1’ underlying table doesn’t have a default value 0.000 sec

好像是因为原表格中的一些notnull的数据没有赋给值。

4.3

select product_id,product_name,product_type,sale_price,(select avg(sale_price) from productions)as sale_price_all
from productions;

这里要注意一下，如果最后一列直接写成

avg(sale_price) as sale_price_all

会导致只有一个记录被显示，因为为单一元素，所以要用到子查询。

4.4

select product_id,product_name,product_type,sale_price,
(select avg(sale_price)   
from productions  as p2
where p1.product_type = p2.product_type) as avg_sale_price
from productions as p1;

它这个是先取到主查询中的相关数据然后进行子查询最后做主查询where筛选的.也就是说先执行主查询，当主查询的product_type=“衣服”，把这个product_type传到子查询里面，匹配，当子查询的product_type为衣服的时候，进行avg计算，再返回给avg_sale_price
这样主查询和子查询就建立了联系

各种各样的函数

算术函数

不用记住所有的函数，己住常用的函数就行，其他的函数用的时候查询即可。
abs(数值) --绝对值函数
mod(被除数，除数)–求余数函数
round(对象数值，保留小数的位数) --四舍五入函数

select m,abs(m) as abs_col,n,p,mod(n,p) as mod_col, round(m,1) as round_col 
from samplemath;

要注意的是这里的函数和前面的聚合函数的区别：
这里的函数可以输出完成的列，但是前面的聚合函数计算完之后返回一个值

字符串函数

concat(str1,str2,str3) --拼接函数
length(str) – 字符串长度函数
lower(str)/upper(str)–小写/大写转换函数，只适用于字符串全部为英文的情况
replace(对象字符串，替换前的字符串，替换后的字符串)–字符串提换函数
substring(对象字符串 from 截取的起始位置 for 截取的字符串数)
起始位置从最左侧开始计算，索引值起始值为1
substring_index(原始字符串，分隔符，n)
获得第n个分隔符之前或者之后的字符串，支撑正向和反向索引
如果想要多次获取，只能试试多次分割

日期函数

current_date --当前日期；
current_time – 当前时间；
current_timetamp–当前日期和时间；
extract(日期元素 from 日期) 从日期中截取需要的日期元素，返回的是数值而不是日期类型

转换函数

cast(转换前的值 as 想要转换的数据类型) --可以将字符换类型转换成数值类型，也可以将字符串类型转换为日期类型
coalesce(数据1，数据2…)–返回第一个不为null的参数

谓词

谓词的返回值只有true/false/unknown
常用的谓词包括：like，between，is null/is not null，in，exists

like

①选取前方一致、中间一致、后方一致的字符串
前方一致：
SELECT * FROM samplelike WHERE strcol LIKE ’ddd%’;
中间一致：
SELECT * FROM samplelike WHERE strcol LIKE ’%ddd%’;
只要字符串中包含这些字符都行，不管出现在哪里
后方一致：
SELECT * FROM samplelike WHERE strcol LIKE ’%ddd’;
②可以用_匹配任何一个字符串
SELECT * FROM samplelike WHERE strcol LIKE ’ddd_’;

between

区间值为闭区间，如果不想用闭区间，只能在条件判断时候用>and <

is null/ is not null

为了选取某些值为null的列的数据，判断null 不能用=，只能用is null和is not null

in

多个查询条件取并集的时候经常用or，in语句简化了多个or语句
将多个并集变成
in(值1，值2，值3…)
或者 not in(值1，值2，值3…)

select product_name, sale_price from product
where product_id `in (select product_id from shopproduct where shop_id = ’000C’)`;

子查询从内到晚进行，先查询shopid为‘000c’的productid，再在主查询中筛选出produc_id符合的记录
not in 的用法是一样的

in (select product_id from shopproduct where shop_id = ’000C’)

in ( ’0003’ ,‘0004’.....)

区别就在于前者是自动更新（子查询的优点），后者需要手动维护

exist

exist的参数一般为一个子查询，而且是一个关联子查询
exist(select语句) 返回的时候，只关心子查询的那个记录存不存在

★ case表达式

表达条件分支的情况才用

case when <求值表达式> then <表达式>
	 when <求值表达式> then <表达式>
	 when <求值表达式> then <表达式>
else <表达式>
end

依次判断when表达式是不是为真值，如果是就执行then后面的语句，如果所有的when都为假，执行else后面的语句
最后只会返回一个值
应用场景①：根据不同分支得到不同列值
应用场景②：实现列方向上的聚合

select product_type,sum(sale_price) as sum_price
from productions group by product_type;

进行行列转换

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_fruits,
       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_snack,
       sum(case when product_type="电子产品"then sale_price else 0 end) as sum_price_tech
from productions ;

聚合函数+case when
就能实现转化 列方向上的聚合函数转化成行

应用场景③：实现行转列
聚合函数+case when + group by
总结：
• 当待转换列为数字时，可以使用 ‘SUM AVG MAX MIN‘等聚合函数；
• 当待转换列为文本时，可以使用 ‘MAX MIN‘等聚合函数

练习题-第二部分

4.5

是的！

4.6

not in null全部返回空（我也不知道为啥）

4.7

select count(case when sale_price < 1000 then sale_price  else null end) as low_price,
	   count(case when sale_price between 1000 and 9990 then sale_price  else null end) as mid_price,
       count(case when sale_price >=10000 then sale_price  else null end) as high_price
from productions;