第一章 數(shù)據(jù)庫(kù)和SQL
1.2數(shù)據(jù)庫(kù)的結(jié)構(gòu)
·關(guān)系型數(shù)據(jù)庫(kù):行代表記錄屁擅,列代表字段仲义,以行為單位進(jìn)行讀寫(xiě)
1.3SQL概要
·SQL包括DDL、DML杠巡、DCL量窘。SQL不區(qū)分大小寫(xiě),以分號(hào)為結(jié)尾氢拥,可逐句執(zhí)行
·特殊常量:字符串用單引號(hào)標(biāo)識(shí)蚌铜,如‘db’。日期‘年-月-日’
1.4創(chuàng)建表
·創(chuàng)建數(shù)據(jù)庫(kù):CREATE DATABASE <數(shù)據(jù)庫(kù)名>??
·創(chuàng)建表:CREATE TABLE <表名>
(<列名><數(shù)據(jù)類(lèi)型><列約束>,
?<表約束>,<表約束>)
·數(shù)據(jù)類(lèi)型:數(shù)字型嫩海、字符型(CHAR,VARCHAR)冬殃、日期型(DATE)。注意叁怪,CHAR在存儲(chǔ)字符串未達(dá)到最大程度時(shí)审葬,自動(dòng)空格補(bǔ)足,如‘ABC? ? ? ’骂束;而VARCHAR不會(huì)自動(dòng)補(bǔ)空格
·約束
列約束:NOT NULL。當(dāng)某記錄該字段為空時(shí)成箫,該記錄寫(xiě)入失敗
表約束:PRIMARY KEY (列名)展箱。每個(gè)記錄主鍵不重復(fù),否則寫(xiě)入失敗
1.5表的刪除和更新
·刪除表:DROP TABLE <表名>?
·更改表:ALTER TABLE<表名> ADD COLUMN<列定義>
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? DROP COLUMN <列定義>
第二章 查詢基礎(chǔ)
2.1SELECT蹬昌、FROM混驰、WHERE
·SELECT <列名,列名> FROM <表名> WHERE <條件>
執(zhí)行順序:FROM選定操作的表皂贩, WHERE過(guò)濾滿足條件的記錄(行)栖榨, SELECT選出指定的列
·列名可以為*,表示所有列
·SELECT <列名> AS <自定義列名>
·SELECT DISTINCT <列名1明刷,列名2>婴栽。刪除重復(fù)行,NULL看作一類(lèi)數(shù)據(jù)辈末。DISTINCT只能放在第一個(gè)列名之前
2.2算術(shù)運(yùn)算符和比較運(yùn)算符
·算術(shù)運(yùn)算:加減乘除
·包含NULL字段的算術(shù)運(yùn)算愚争,結(jié)果都為NULL
·比較運(yùn)算:=,<>挤聘,<轰枝,>,<=组去,>=
·字符型CHAR,VARCHAR比較大小是按照字典序比較鞍陨,而不是數(shù)字大小。DATE可以直接比較大小
·對(duì)NULL值字段進(jìn)行比較運(yùn)算从隆,結(jié)果都為NULL诚撵。判斷是否為NULL不能用<字段名>=NULL缭裆,這樣什么結(jié)果都得不到,應(yīng)該用IS NULL, IS NOT NULL
2.3邏輯運(yùn)算符
·邏輯運(yùn)算:AND, OR, NOT砾脑。結(jié)合括號(hào)匹配優(yōu)先級(jí)
·對(duì)NULL做邏輯運(yùn)算(三值邏輯):TRUE AND NULL = NULL; FALSE AND NULL = FALSE;TRUE OR NULL = TRUE; FALSE OR NULL = NULL幼驶。例,WHERE 28 = NULL AND TRUE 值為NULL韧衣,所以WHERE過(guò)濾后得不到任何記錄
·因此盅藻,列約束NOT NULL的重要性得以體現(xiàn)
第三章 聚合和排序
3.1聚合查詢
·所謂聚合,就是將多行數(shù)據(jù)匯總為一行畅铭。一條記錄為一行氏淑,將記錄按某些列的不同值分組為一組
·聚合函數(shù):SUM,COUNT硕噩,MAX等假残。例,
SELECT COUNT(<列名>) FROM <表名>?
·COUNT(*)得到包含NULL的行數(shù)炉擅。 COUNT(<列名>)得到該列去除NULL后的行數(shù)
·除COUNT(*)外辉懒,所有聚合函數(shù)都將NULL排除在外計(jì)算結(jié)果,如SUM谍失,AVG眶俩,MAX等。注意快鱼,不是把NULL值當(dāng)做0計(jì)算颠印,而是不參加計(jì)算
·聚合函數(shù)和DISTINCT配合使用。如去重后計(jì)數(shù):
SELECT COUNT(DISTINCT <>) FROM <>
注意DISTINCT的位置抹竹,如果寫(xiě)成SELECT DISTINCT COUNT(<>)則達(dá)不到效果
3.2對(duì)表分組GROUP BY
·書(shū)寫(xiě)順序:SELECT <列名> FROM <表名> WHERE 條件 GROUP BY <列名>
聚合鍵值為NULL的行會(huì)被聚合為一類(lèi)线罕。GROUP BY常常和SELECT、HAVING中的聚合函數(shù)配合使用
·執(zhí)行順序:FROM WHERE GROUP BY SELECT
·使用聚合函數(shù)和GROUP BY常見(jiàn)錯(cuò)誤:
1窃判、SELECT中出現(xiàn)聚合鍵之外的其他列名钞楼。GROUP BY和SELECT配合使用時(shí),SELECT只能包含聚合鍵袄琳、聚合函數(shù)和常數(shù)窿凤。因?yàn)槠渌袩o(wú)法和聚合鍵一一對(duì)應(yīng)。
如表A有如下列:c1,c2,c3,c4跨蟹。GROUP BY c1后雳殊,得到的臨時(shí)表為:c1,聚合函數(shù)(c2,c3,c4)
錯(cuò)誤例子:SELECT C1, C2, AVG(C1) FROM T1
2窗轩、GROUP BY中寫(xiě)了列的別名夯秃。注意執(zhí)行順序,SELECT中定義的別名在GROUP BY中還未生效
3、GROUP BY得到的結(jié)果是隨機(jī)無(wú)序的
4仓洼、在WHERE中使用聚合函數(shù)介陶。聚合函數(shù)只有在SELECT, HAVING, ORDER BY中使用
錯(cuò)誤例,選出有兩行記錄的那一類(lèi):SELECT <> FROM <> WHERE COUNT(*) = 2 GROUP BY<>色建。應(yīng)該用HAVING實(shí)現(xiàn)
3.3聚合結(jié)果條件語(yǔ)句HAVING
·WHERE=某一行的過(guò)濾條件哺呜,HAVING=聚合后以組為單位的過(guò)濾條件(如,數(shù)據(jù)行數(shù)為2的組箕戳,平均值為20的組)
·書(shū)寫(xiě)順序:SELECT FROM WHERE GROUP BY HAVING某残。例,選擇有兩條單價(jià)大于100的記錄的商品種類(lèi):SELECT type FROM product WHERE sale > 100 GROUP BY type HAVING BY COUNT(*) = 2
·HAVING字句只能包含:常數(shù)陵吸、聚合函數(shù)玻墅、GROUP BY中的聚合鍵。不能出現(xiàn)其他列的原因同上壮虫,GROUP BY后其他列無(wú)法和聚合鍵一一對(duì)應(yīng)
·聚合鍵對(duì)應(yīng)的條件澳厢,既可以寫(xiě)在WHERE中,也可以寫(xiě)在HAVING中囚似,結(jié)果相同剩拢。不過(guò),推薦寫(xiě)在WHERE中饶唤,原因如下:
1徐伐、語(yǔ)意更明確
2、WHERE中的列可以利用索引搬素,提高處理速度
3呵晨、HAVING中常使用聚合函數(shù)魏保,而使用聚合函數(shù)時(shí)會(huì)涉及對(duì)數(shù)據(jù)的排序熬尺。應(yīng)該優(yōu)先在WHERE中過(guò)濾掉部分?jǐn)?shù)據(jù),減小排序的數(shù)據(jù)量谓罗,而不是把過(guò)濾條件放在執(zhí)行考后的HAVING中
3.4排序ORDER BY
·書(shū)寫(xiě)順序:SELECT FROM WHERE GROUP BY HAVING ORDER BY粱哼。ORDER BY<列名1>,<列名2>,成為排序鍵檩咱。默認(rèn)升序排列揭措,DESC為降序
·選為排序鍵的列若含有NULL,會(huì)排在最前或最后刻蚯,這取決于具體數(shù)據(jù)庫(kù)
·執(zhí)行順序:FROM WHERE GROUP BY HAVING SELECT ORDER BY绊含。所以select中的別名,order by中可以識(shí)別
·ORDER BY中可以使用聚合函數(shù)和SELECT中未出現(xiàn)的列
第四章 數(shù)據(jù)更新
4.1插入
·INSERT
4.2刪除
·DELETE
4.3更新
·UPDATE
4.4事務(wù)性
·ACID特性
·TRANSACTION炊汹、COMMIT躬充、ROLLBACK
第五章 復(fù)雜查詢
5.1視圖
·創(chuàng)建視圖
CREATE VIEW 視圖名稱(視圖列名1,列名2) AS <SELECT語(yǔ)句>
例如,CREATE VIEW productSum (type, count)
AS SELECT type, COUNT(*) FROM product GROUP BY type
·使用視圖
SELECT 視圖列名 FROM 視圖名充甚,例如以政,SELECT type FROM productSum
·視圖的用處:保存高頻使用的SELECT語(yǔ)句
·視圖就是臨時(shí)表。視圖和表的區(qū)別:是否保存了實(shí)際數(shù)據(jù)
·多重視圖:在視圖的基礎(chǔ)上再建新視圖
CREATE VIEW 視圖名稱(視圖列名1伴找,列名2) AS <SELECT 列名 FROM 已存在的視圖>
避免使用多重視圖盈蛮,嚴(yán)重影響數(shù)據(jù)庫(kù)引擎效率
·視圖不能使用ORDER BY
·最好不要對(duì)視圖進(jìn)行寫(xiě)操作(INSERT, DELETE, UPDATE)。只有能和原表一一對(duì)應(yīng)的視圖才可以進(jìn)行寫(xiě)操作:
1技矮、SELECT中沒(méi)有使用DISTINCT
2抖誉、FROM中只有一個(gè)表,沒(méi)有使用連接查詢
3穆役、沒(méi)有使用GROUP BY和HAVING
·刪除視圖 DROP? VIEW
5.2子查詢
·子查詢:將用來(lái)定義視圖的SELECT語(yǔ)句直接作為外層的FROM語(yǔ)句中
SELECT a FROM (SELECT a FROM A) AS 子查詢名稱
執(zhí)行順序顯然由內(nèi)向外寸五。最好用AS設(shè)定子查詢名稱
·理論子查詢可以無(wú)限嵌套,但是子查詢會(huì)嚴(yán)重影響性能
·標(biāo)量子查詢:返回單一值的子查詢(一行一列)耿币。常常在WHERE中使用梳杏,可以避免WHERE不能使用聚合函數(shù)的缺陷。也可以使用在SELECT淹接、GROUP BY語(yǔ)句中
例十性,SELECT ID FROM PRODUCT WHERE SALE > (
SELECT AVG(SALE) FROM PRODUCT)
·注意,標(biāo)量子查詢必須返回一行一列塑悼,否則在SELECT中可能出錯(cuò)劲适,因?yàn)樾袛?shù)無(wú)法對(duì)應(yīng)
5.3關(guān)聯(lián)子查詢
·背景:查詢每個(gè)種類(lèi)中高于該種類(lèi)均價(jià)的商品
·錯(cuò)誤寫(xiě)法:
SELECT NAME FROM PRODUCT
WHERE SALE > (
SELECT AVG(SALE)? FROM PRODUCT?
GROUP BY TYPE )
因?yàn)镾ELECT AVG(SALE)? FROM PRODUCT?GROUP BY TYPE得到的數(shù)據(jù)是多行,不是標(biāo)量子查詢?
·正確寫(xiě)法:
SELECT NAME FROM PRODUCT AS P1
WHERE SALE > (
SELECT AVG(SALE)? FROM PRODUCT AS P2?
WHERE P1.TYPE = P2.TYPE GROUP BY TYPE )
·在GROUP BY分組后的細(xì)分組內(nèi)比較時(shí)厢蒜,需要關(guān)聯(lián)子查詢
·關(guān)聯(lián)條件必須放在子查詢的WHERE中霞势,因?yàn)锳S別名的作用域是外部看不見(jiàn)內(nèi)部,內(nèi)部看得見(jiàn)外部
第六章 函數(shù)斑鸦、謂詞愕贡、case
6.1函數(shù)
·常見(jiàn)函數(shù)包括:聚合函數(shù)、算書(shū)函數(shù)巷屿、日期函數(shù)固以、字符串函數(shù)
具體使用是查閱API
6.2謂詞
·LIKE:字符串模糊查詢。%表示任意長(zhǎng)度字符串嘱巾,_表示一個(gè)字符憨琳。=是精確查詢
SELECT <列名> FROM <表名> WHERE type LIKE ‘%dd_’
·BETWEEN a AND b等價(jià)于 >=a && <= b。想不包含邊界值旬昭,必須用<,>
·IS NULL 篙螟,IS NOT NULL
·IN的作用相當(dāng)于OR。
WHERE price = 100 OR price = 200 等價(jià)于WHERE price IN(100问拘,200)
但是IN無(wú)法選取出NULL數(shù)據(jù)遍略,NULL必須用IS NULL判斷慢味!NOT IN的參數(shù)中若包括NULL,則無(wú)法篩選出任何數(shù)據(jù)墅冷,如WHERE price NOT IN (100纯路, NULL)
·使用子查詢,也就是視圖寞忿,作為IN的參數(shù)驰唬。
SELECT <列名> FROM <表1> WHERE id IN (
SELECT id FROM <表2> WHERE<條件>)
·EXIST只有一個(gè)參數(shù),在右側(cè)腔彰,通常是一個(gè)關(guān)聯(lián)子查詢
SELECT name FROM product WHERE EXIST (
SELECT * FROM shop WHERE product.pid = shop.pid AND shop.sid = 1)
等價(jià)于
SELECT name FROM product WHERE pid IN (
SELECT * FROM shop WHERE shop.sid = 1)
EXIST中的子查詢SELECT后為什么列并不影響結(jié)果叫编,EXIST只關(guān)心有沒(méi)有存在的行,寫(xiě)成*只是一種習(xí)慣
·IN霹抛,NOT IN可以和EXIST搓逾,NO EXIST互相替換
6.3CASE表達(dá)式
·語(yǔ)法:CASE WHEN <求值表達(dá)式> THEN <表達(dá)式>
CASE就像一個(gè)表達(dá)式,可以寫(xiě)在任意位置杯拐,但通常和SELECT配合使用霞篡,處理查詢結(jié)果
第七章 集合運(yùn)算
7.1表的加減法
·并集UNION。會(huì)自動(dòng)去重端逼,想要不去重朗兵,使用UNION ALL字段
注意事項(xiàng):運(yùn)算的兩個(gè)對(duì)象記錄必須列數(shù)相同且類(lèi)型相同;ORDER BY必須在最后使用
SELECT A, B FROM T1 UNION SELECT A, B FROM T2
·INTERSECT交集顶滩,EXCEPT差集
7.2聯(lián)結(jié)
·JOIN余掖,就是將其他表的列添加過(guò)來(lái)。a有三列礁鲁, JOIN b三列盐欺,會(huì)得到一個(gè)六列的臨時(shí)表
·INNER JOIN。以表a中的列為橋梁仅醇,將b中滿足相同條件的列匯集到一個(gè)臨時(shí)表中冗美。
FROM t1 INNER JOIN t2 ON t1.id = t2.id
三要素:用在FROM子句中;ON設(shè)置聯(lián)結(jié)條件着憨,一般用=墩衙,也可以用<等條件务嫡;SELECT子句中列名注意區(qū)分在不同表
·OUTER JOIN甲抖。語(yǔ)法同內(nèi)鏈接相同。不同之處在于心铃,表a中聯(lián)結(jié)條件存在的數(shù)據(jù)若表b中不存在准谚,在最后的臨時(shí)表中仍會(huì)有記錄,以NULL的形式存在
·LEFT去扣,RIGHT JOIN柱衔。OUTER JOIN是左右兩個(gè)表都為主表樊破,可以選擇單個(gè)主表
·可以聯(lián)結(jié)多個(gè)表
·交叉聯(lián)結(jié)(笛卡爾積)CROSS JOIN。完全交叉唆铐,新表的行數(shù)為len(a) * len(b)哲戚。