一.概述

首先這是之前一版極其瑣碎的blog的鏈接.然后這篇博客不會涉及存儲過程觸發(fā)器等內(nèi)容(后續(xù)會補(bǔ)充),僅僅按照增刪改查這四個點(diǎn)來做基本的介紹以及記錄,畢竟工作中很大一部分需要的也是這些內(nèi)容~

二.素材準(zhǔn)備

為這次的記錄建好database以及user.(表結(jié)構(gòu)素材來自mysql必知必會)

create database notereview;

create user review@localhost identified by 'xxxxx';

grant all on notereview.* to review@localhost;

flush privileges;

三.查詢

基礎(chǔ)語句

查詢子語句有一個基本的模板:

    FROM xxx WHERE xxx GROUP BY xxx HAVING xxxx ORDER BY xxx LIMIT xxxxx

WHERE和條件連接

這里的WHERE就是查詢條件,簡單的條件過濾比如 =,<>,!=,<,>,<=,>=,BETWEEN,IN,NOT等.

還有就是對于NULL的檢查：IS NULL,IS NOT NULL

上面是使用單條子語句所可能用到的關(guān)鍵詞,如果是多條子語句可以通過AND 或者 OR 連接.其中 AND 的優(yōu)先級是高于 OR的.也就是說:

SELECT a,b,c FROM tableName WHERE conditionA OR conditionB AND conditionC;

相當(dāng)于是查詢同時(shí)滿足條件B和C,或者滿足條件A的數(shù)據(jù).舉個例子:

create table if not exists products(
    prod_id varcahr(10) not null auto_increment,
    vend_id int not null,
    prod_name varchar(255) not null,
    prod_price decimal(10,2) not null,
    prod_desc text not null,
    primary key(prod_id)
)engine = InnoDb default charset=utf8mb4 comment = '產(chǎn)品表';

SELECT prod_name,vend_id,prod_price FROM products  WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

其中Fuses的價(jià)格為3.42不滿足大于等于10的要求但是滿足了vend_id為1002的要求.

LIMIT 和 ORDER BY

然后是LIMIT以及ORDER BY,基礎(chǔ)模板為:

    xxx ORDER BY xxxx [DESC][,xxxx [desc]] LIMIT index[,offset];

也就是說按照一定規(guī)則排序后,從index行開始(包含index),返回最多offset行數(shù)據(jù),這里需要注意的是index是從0開始計(jì)算的,也就是說LIMIT 1,1其實(shí)返回的是第二行.

ORDER BY 默認(rèn)是升序,如果是DESC則是降序,其中可以按照多個字段排序,實(shí)際結(jié)果是按照排序字段的出現(xiàn)的優(yōu)先級來確定的,舉個例子:

SELECT prod_name,vend_id,prod_price FROM products where prod_price >10 ORDER BY prod_price DESC,vend_id ASC;

這邊的結(jié)果可以看出雖然 JetPack 1000 的vend_id 也是1005但是排序的第一條件是按照prod_price 降序,所以他不能處在第一位,又因?yàn)榈诙€條件是按照 vend_id 升序,所以他最終處在第三位.

SELECT prod_name,vend_id,prod_price FROM products where prod_price >10 ORDER BY prod_price DESC,vend_id ASC limit 2,10;

這里的結(jié)果對比上面的可以看出LIMIT 2,10 確實(shí)是從第三條開始返回的,如果數(shù)據(jù)不夠則會返回他所能返回的最多的數(shù)據(jù).

GROUP BY 和 HAVING

按照上面的順序,接下來是分組查詢GROUP BY 和 HAVING.這兩者一般配合使用.HAVING 可以當(dāng)成是在使用GROUP BY時(shí)的WHERE,用來過濾分組后的數(shù)據(jù).其中SELECT 后接的表字段(聚合函數(shù)除外)必須出現(xiàn)在GROUP BY后.舉個例子,查詢products表中能夠提供多種產(chǎn)品的商戶(即產(chǎn)品多余一種)，列出商戶id以及所能提供的產(chǎn)品數(shù)量并按照產(chǎn)品種類數(shù)降序排序列出.

SELECT vend_id,count(*) AS prods from products GROUP BY vend_id HAVING prods > 1 ORDER BY prods DESC;

結(jié)果如下:

這里列出幾個常用的聚合函數(shù):

like 和正則

這邊 like和正則簡單提下,因?yàn)樾蕟栴}推薦放在搜索條件的最后面

WHERE xxxx  like 'xxx'

SELECT  prod_id FROM products WHERE prod_id LIKE 'JP%';

%表示這里可以出現(xiàn)0,1或多個字符.這條語句相當(dāng)于查詢JP開頭的數(shù)據(jù)(注意NULL不會匹配)

同樣也可以通過正則來達(dá)到一樣的效果:

SELECT  prod_id FROM products WHERE prod_id REGEXP 'JP*';

# 這個則不行，因?yàn)閎inary相當(dāng)于是大小寫敏感匹配
SELECT  prod_id FROM products WHERE prod_id REGEXP BINARY 'jp*';

聯(lián)結(jié)查詢

等值聯(lián)結(jié)/內(nèi)部聯(lián)結(jié)

這個其實(shí)就是我們常用的 INNER JOIN或者多表?xiàng)l件查詢

#等值聯(lián)結(jié) 
SELECT a.x,b.x FROM a,b WHERE a.xxx = b.xxx;
#內(nèi)聯(lián)結(jié)
SELECT a.x,b.x FROM a INNER JOIN b ON a.xxx = b.xxx;

這里相當(dāng)于是那表a的每一行和表b的每一行去做匹配并留下符合條件的數(shù)據(jù)

這里有個特殊的比如自聯(lián)結(jié):

SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

這里使用了別名是因?yàn)槭潜韕roducts和自己聯(lián)結(jié)防止指代不情.這句sql的結(jié)果是挑選DTNTR的商戶所能生產(chǎn)的所有商品

結(jié)果如下:

另外的LEFT JOIN,RIGHT JOIN就是所謂的左聯(lián)結(jié)右聯(lián)結(jié).什么聯(lián)結(jié)就是以那邊的數(shù)據(jù)為準(zhǔn),那邊的數(shù)據(jù)最全,如果右邊沒有匹配的數(shù)據(jù)則以NULL的形式展示.

組合查詢

UNION:其實(shí)是相當(dāng)于將多條SELECT的結(jié)果組合。默認(rèn)是去除重復(fù)行的,UNION ALL 則是不去重噠

# 結(jié)果見表1
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5;

#結(jié)果見表2
SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN(1001,1002);

#結(jié)果見表3
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN(1001,1002);

#結(jié)果見表4
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 UNION  ALL SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN(1001,1002);

表1:

表2:

表3:

表4:

這里因?yàn)槭菍⒔Y(jié)果合并,所以查詢字段必須一致~

四.插入

這個則是insert語句

 INSERT INTO targetName(columnName[,columnName]…) values(columnValue[,columnValue])[,(…)];

這里再提下遷移數(shù)據(jù)到新表:

INSERT INTO newTable SELECT * FROM oldTable;

#如果結(jié)構(gòu)不一致的話

INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 舊表;

五.刪除

DELETE FROM tableName WHERE xxx = xxx;

這里記得注意WHERE條件~
然后 TRUNCATE TABLE tableName 不過這個需要注意的是他不會激活觸發(fā)器,并且不會涉及事務(wù),所以效率很高但是注意備份數(shù)據(jù),畢竟不能回滾.

六.更新

UPDATE targetName SET columnName = xxx [,columnName2 = xxxx] WHERE xxx = xxxx;
UPDATE products SET prod_price = 35.00 WHERE prod_id ='SAFE';

同樣不要忘記WHERE 條件哈~

七.幾個練習(xí)(from leetcode-cn)

交換工資(?性別)

給定一個 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交換所有的 f 和 m 值（例如，將所有 f 值更改為 m，反之亦然）。要求只使用一個更新（Update）語句，并且沒有中間的臨時(shí)表。

注意，您必只能寫一個 Update 語句，請不要編寫任何 Select 語句。

# 這里使用了 CASE WHEN
UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;


# if的話
UPDATE salary
SET 
 sex = IF(sex='m','f','m');

# 這里就不涉及代碼思路解決了比如字符串變換

超過經(jīng)理收入的員工

Employee 表包含所有員工，他們的經(jīng)理也屬于員工。每個員工都有一個 Id，此外還有一列對應(yīng)員工的經(jīng)理的 Id。

SELECT  a.Name AS Employee   FROM Employee AS a,Employee AS b where a.ManagerId = b.Id AND a.Salary > b.Salary;

不訂購任何東西的客戶

某網(wǎng)站包含兩個表，Customers 表和 Orders 表。編寫一個 SQL 查詢，找出所有從不訂購任何東西的客戶。

Customers 表：

Orders 表：

SELECT Name AS Customers from Customers  where Id NOT IN (select CustomerId from Orders);