1、對(duì)視圖的理解
《sql基礎(chǔ)教程**第2版》用一句話非常凝練的概括了視圖與表的區(qū)別—“是否保存了實(shí)際的數(shù)據(jù)”。所以視圖并不是數(shù)據(jù)庫真實(shí)存儲(chǔ)的數(shù)據(jù)表，它可以看作是一個(gè)窗口毫目，通過這個(gè)窗口我們可以看到數(shù)據(jù)庫表中真實(shí)存在的數(shù)據(jù)。所以我們要區(qū)別視圖和數(shù)據(jù)表的本質(zhì)诲侮，即視圖是基于真實(shí)表的一張?zhí)摂M的表镀虐，其數(shù)據(jù)來源均建立在真實(shí)表的基礎(chǔ)上。
那既然已經(jīng)有數(shù)據(jù)表了沟绪，為什么還需要視圖呢刮便？主要有以下幾點(diǎn)原因：
通過定義視圖可以將頻繁使用的SELECT語句保存以提高效率。
通過定義視圖可以使用戶看到的數(shù)據(jù)更加清晰绽慈。
通過定義視圖可以不對(duì)外公開數(shù)據(jù)表全部字段恨旱，增強(qiáng)數(shù)據(jù)的保密性。
通過定義視圖可以降低數(shù)據(jù)的冗余坝疼。
視圖不僅可以基于真實(shí)表搜贤，我們也可以在視圖的基礎(chǔ)上繼續(xù)創(chuàng)建視圖。
雖然在視圖上繼續(xù)創(chuàng)建視圖的語法沒有錯(cuò)誤钝凶，但是我們還是應(yīng)該盡量避免這種操作仪芒。這是因?yàn)閷?duì)多數(shù) DBMS 來說， 多重視圖會(huì)降低 SQL 的性能。
2掂名、視圖的更改會(huì)直接改原始表的數(shù)據(jù)据沈，因?yàn)橐晥D本身是原始表的一個(gè)窗口而已。
3饺蔑、學(xué)會(huì)利用標(biāo)量子查詢：
標(biāo)量子查詢的意思是返回一個(gè)值锌介。例如
select avg(product_name) from product
標(biāo)量子查詢的使用場(chǎng)景：
查詢出銷售單價(jià)高于平均銷售單價(jià)的商品
select product_id, product_name, sale_price
from product
where sale_price > (select avg(sale_price) from product)
由于標(biāo)量子查詢的特性，導(dǎo)致標(biāo)量子查詢不僅僅局限于 WHERE 子句中猾警，通常任何可以使用單一值的位置都可以使用孔祸。也就是說， 能夠使用常數(shù)或者列名的地方发皿，無論是 SELECT 子句融击、GROUP BY 子句、HAVING 子句雳窟，還是 ORDER BY 子句，幾乎所有的地方都可以使用匣屡。
4封救、關(guān)于關(guān)聯(lián)子查詢，這個(gè)知識(shí)點(diǎn)必須要真的理解才知道怎么操作的捣作，這個(gè)博客寫的很好https://zhuanlan.zhihu.com/p/41844742，我自己的理解是券躁，它主要是類似一個(gè)FOR循環(huán)，先執(zhí)行的是主查詢也拜，之后看關(guān)聯(lián)語句，把關(guān)聯(lián)語句的第一個(gè)值傳入子查詢兰绣，對(duì)子查詢進(jìn)行執(zhí)行斋泄，并返回相應(yīng)的結(jié)果到主查詢；把關(guān)聯(lián)語句的第二個(gè)值傳入子查詢讶请，對(duì)子查詢進(jìn)行執(zhí)行论巍，并返回相應(yīng)的結(jié)果到主查詢状勤；以此類推，進(jìn)行for循環(huán)。
選取出各商品種類中高于該商品種類的平均銷售單價(jià)的商品匣距。
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);
但是，我以往的做法都是join兩個(gè)表，之后再一個(gè)嵌套select出來墩莫。也不是不可以逞敷，但是感覺沒有像上面那個(gè)語法簡(jiǎn)單贼穆。
練習(xí)：
NO1
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';
NO2:
看下執(zhí)行結(jié)果：
原因是：造成該問題的原因是： 當(dāng)向視圖中插入數(shù)據(jù)時(shí)，同時(shí)也會(huì)向原表插入數(shù)據(jù)插入數(shù)據(jù) 兰粉，而原表中存在多個(gè)字段不允許為空，所以無法插入顶瞳。
我們把NOT NULL的字段填上
create or replace View ViewPractice5_2(product_name, sale_price, regist_date, product_id, product_type)
as
select product_name, sale_price, regist_date,product_id, product_type
from product
where sale_price >= 1000 and regist_date = '2009-09-20';
insert into ViewPractice5_2 values (' 刀子 ', 300, '2009-11-02',100, '辦公用品');
select * from ViewPractice5_2;
結(jié)果是insert 成功了玖姑，但是視圖卻沒有改變，WHY?
答案是對(duì)視圖的操作其實(shí)是對(duì)原始表的操作Insert是到原始表了慨菱，但是由于我們視圖創(chuàng)建時(shí)有一個(gè)條件是where sale_price >= 1000 and regist_date = '2009-09-20';所以焰络，在view里是看不到新插入的數(shù)據(jù)的，在原始表里才能看到７取闪彼！
看看原始表的情況：
NICE! 和預(yù)計(jì)的一樣！
NO3
這里先有個(gè)小tips,由于我做了上一題协饲，插入了一行數(shù)據(jù)畏腕，我先要用delete from table where product_id = 100刪除這一行，結(jié)果出現(xiàn)了這個(gè)情況：
一番搜索之后發(fā)現(xiàn)原來是MySQL Workbench的安全設(shè)置茉稠。當(dāng)要執(zhí)行的SQL語句是進(jìn)行批量更新或者刪除的時(shí)候就會(huì)提示這個(gè)錯(cuò)誤描馅。
解決方法是：
改完之后需要重啟一下，才能生效而线。
好嘞铭污，下面開始做這個(gè)題：
考點(diǎn)是【對(duì)標(biāo)量子查詢的理解】
select product_id, product_name,
product_type, sale_price,
(select avg(sale_price) from product) as sale_price_all
from product
NO4:
這個(gè)考察的是關(guān)聯(lián)子查詢恋日，我的方法是，先寫子查詢的邏輯嘹狞，然后搞清楚它返回的是什么岂膳，返回的就是一個(gè)標(biāo)量，之后需要嵌入主查詢做FOR循環(huán)０跬谈截！
select product_id, product_name, product_type, sale_price , (SELECT AVG(sale_price)
FROM product as table2
where table2.product_type = table1.product_type
group by product_type) as avg_sale_price
from product as table1
運(yùn)行結(jié)果如下：
函數(shù)介紹
這里只挑了一些我不太常用的，一般忘記了就現(xiàn)用現(xiàn)查知市。
1傻盟、算術(shù)函數(shù)
ROUND( 對(duì)象數(shù)值，保留小數(shù)的位數(shù) ) ----四舍五入
2嫂丙、字符串函數(shù)
SUBSTRING（對(duì)象字符串 FROM 截取的起始位置 FOR 截取的字符數(shù)）---- 字符串的截取娘赴，sql中下標(biāo)從1開始
例子：從product_name第3個(gè)字符開始，往后取2個(gè)
select product_name,substring(product_name from 3 for 2) as sub_string
from product;
substring_index(原始字符串跟啤， 分隔符诽表，n) ：以分隔符split,取第n個(gè)分割之前的內(nèi)容，起始索引為1隅肥，倒序起始索引為-1
類似于python中的split()函數(shù)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
結(jié)果是www.mysql
獲取第N個(gè)元素竿奏，需要嵌套N次，取-1
例如：獲取 'www.mysql.com' 中的 mysql
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.' , -1);
3腥放、日期函數(shù)
---獲取當(dāng)前時(shí)間
select current_timestamp;
---獲取當(dāng)前日期
select current_date;
---獲取當(dāng)前時(shí)刻
select current_time;
截取日期元素 extract （這個(gè)很實(shí)用）
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;
4泛啸、轉(zhuǎn)換函數(shù)
COALESCE – 將NULL轉(zhuǎn)換為其他值
CAST（轉(zhuǎn)換前的值 AS 想要轉(zhuǎn)換的數(shù)據(jù)類型）
謂詞
謂詞：返回值為真值的函數(shù)
1、LIKE
---用‘%’正則秃症，前方一致候址、中間一致和后方一致
--前方：dddaa匹配
SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
--中間：abcdddcsv匹配
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
--后方：aaasvddd匹配
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
---用‘_’正則任意1個(gè)字符
aadddcsv匹配
SELECT *
FROM samplelike
WHERE strcol LIKE '__ddd___';
2、between :閉區(qū)間种柑，[],
where column between 100 and 1000
3岗仑、IN
where column IN (123,345,578,12346)
4、善于用“子查詢”代替 IN 和 NOT IN
使用子查詢即可保持 sql 語句不變聚请，極大提高了程序的可維護(hù)性荠雕，這是系統(tǒng)開發(fā)中需要重點(diǎn)考慮的內(nèi)容。實(shí)際生活中驶赏，某個(gè)門店的在售商品是不斷變化的炸卑，使用 in 謂詞就需要經(jīng)常更新 sql 語句，降低了效率煤傍，提高了維護(hù)成本矾兜；實(shí)際上，某個(gè)門店的在售商品可能有成百上千個(gè)患久，手工維護(hù)在售商品編號(hào)真是個(gè)大工程椅寺。
-- NOT IN 使用子查詢作為參數(shù)浑槽，取出未在大阪門店銷售的商品的銷售單價(jià)
SELECT product_name, sale_price
FROM product
WHERE product_id NOT IN (SELECT product_id
FROM shopproduct
WHERE shop_id = '000A');
5、exist & not exist: 是否存在符合條件的記錄返帕，這個(gè)應(yīng)該是個(gè)非常好用的謂詞Ｍ┎！！荆萤！因?yàn)槟憧梢园选鞠薅l件】全都放到【關(guān)聯(lián)子查詢】中镊靴，同時(shí)不必要想子查詢需要篩選出來什么，直接子查詢select * 或者select 1就好Ａ淳隆Ｆ埂！３ㄇ汀踊谋！
SELECT product_name, sale_price
FROM product AS p
WHERE EXISTS (SELECT 1 -- 這里可以書寫適當(dāng)?shù)某?shù)
FROM shopproduct AS sp
WHERE sp.shop_id = '000C'
AND sp.product_id = p.product_id);
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);
6.CASE
注意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;
7、聚合函數(shù)+CASE WHEN ... THEN... ELSE...END 在業(yè)務(wù)中是經(jīng)常用到的３良！睦疫！
核心思想是：先想你最終需要的表的列是哪幾個(gè)，然后再插入計(jì)算的東西鞭呕。（要什么拿什么）
第一步構(gòu)建框架：
select name,
......chinese,
......math,
......english
from
table1
group by name;
第二步蛤育，填充內(nèi)容：
select name,
sum(case when subject = '語文' then score else 0 end) as chinese,
sum(case when subject = '數(shù)學(xué)' then score else 0 end) math,
sum(case when subject = '英語' then score else 0 end) english
from
table1
group by name;
搞定~~~
哦~終于到了第二部分的練習(xí)了！葫松！LET'S DO IT!!
NO.1:
對(duì)
NO.2:
結(jié)果：
帶上了null以后瓦糕，所有的結(jié)果都沒有了，這是因?yàn)榻Γ^詞是對(duì)真值的判斷，Null沒有對(duì)應(yīng)的真值枷恕，它是unknown
NO.3:
SELECT
sum(case when sale_price <= 1000 then 1 else 0 end) as low_price,
sum(case when sale_price > 1001 and sale_price < 3001 then 1 else 0 end) as low_price,
sum(case when sale_price > 3001 then 1 else 0 end) as high_price
from product
NICE~
好嘞党晋，今天的打卡就到這里了~
完成和堅(jiān)持是第一步，所以今天又走了一步呢~我想你也和我一樣
