SQL 面試題（二）

找出baseball表中，name屬性中包含“To”字符的元組

屬性表示列名稱，元組表示一條數(shù)據(jù)阎毅。本題可以用 like 操作符目代，以及 SQL 的通配符屈梁。

like 操作符用于在 WHERE 子句中搜索列中的指定模式。
like 一般搭配通配符使用
not like 可以選取與指定模式不同的其他值

SELECT * FROM baseball WHERE name LIKE 'TO%';
// % 是 SQL 的通配符榛了，用來(lái)指代1個(gè)或多個(gè)字符

常見的通配符有

例如在讶，要找出baseball表中，name屬性中以“T”或者“N”或者“J”字符開頭的所有元組

select * from baseball
where name like '[TNJ]%';

// 如果不支持字符列的表達(dá)法忽冻，可以使用or 或者 and關(guān)鍵詞
SELECT * FROM baseball
WHERE name LIKE 'N%' or name like 'T%' or name like 'J%';

//  [!charlist] 可以用not like來(lái)滿足

增加一個(gè)列

使用 alter 操作符

alter table test add column Email text;

alter table table_name add column column_name datatype;

更新表中的數(shù)據(jù)

為 test 表中真朗，Id=1的元組添加 Email值，值為“10177157@qq.com”
使用 update 操作符

UPDATE test SET Emali = '10177157@qq.com' WHERE Id =2;

什么是視圖

view 是從真實(shí)的基礎(chǔ)表 table 里面僧诚，根據(jù)用戶的需要遮婶，做成的虛表。

view有以下幾個(gè)特點(diǎn)：

1. view 中的數(shù)據(jù)可以來(lái)自于多個(gè)表湖笨；
2. view 的數(shù)據(jù)會(huì)隨著真實(shí)表格的數(shù)據(jù)變化而變化旗扑；
3. view 生成以后，只能使用select查詢慈省，不能用update臀防、delete、insert來(lái)修改虛擬表的內(nèi)容
4. view 不同的view可以針對(duì)不同需求的人，展示不同的數(shù)據(jù)袱衷，而不必向他們展示所有的數(shù)據(jù)捎废，可以保持?jǐn)?shù)據(jù)的保密性；
5. view 不能進(jìn)行修改致燥；
6. view 還可以用來(lái)簡(jiǎn)化查詢登疗。如果我們經(jīng)常使用某些數(shù)據(jù)，就可以把他們做成一個(gè)view嫌蚤，每次直接使用 select * from view_name 就可以了辐益，比每次重新寫SQL語(yǔ)句要簡(jiǎn)單的多；

語(yǔ)法

CREATE VIEW view_name AS
SELECT column_name(s) FROM table_name
WHERE condition; 
// 建立一個(gè)視圖 view脱吱，從真實(shí)表 table_name 中選出滿足 condition 條件的數(shù)據(jù)

DROP VIEW view_name;
// 刪除視圖 view_name

什么是索引

索引是一種特殊的查詢表智政，可以用來(lái)更快更搞笑的查詢數(shù)據(jù)。索引類似于圖書館的圖書目錄箱蝠，可以方便用戶更快的找到想要的數(shù)據(jù)续捂。

不過(guò)，由于索引表會(huì)跟著數(shù)據(jù)的更新而更新宦搬，所以疾忍，如果數(shù)據(jù)表經(jīng)常更新數(shù)據(jù)或插入、刪除數(shù)據(jù)床三，則不適合建立索引一罩，不然會(huì)導(dǎo)致更新表的數(shù)據(jù)變慢很多。

另外撇簿，索引不適合以下情況：

1. 小的數(shù)據(jù)表不適合使用索引聂渊；
2. 頻繁更改數(shù)據(jù)的數(shù)據(jù)表；
3. 取值單一的數(shù)據(jù)表中的列不適合作為索引四瘫；
4. 列中包含大數(shù)或者Null值汉嗽，不適合作為索引。

語(yǔ)法

CREATE INDEX index_name
ON table_name;
// 為table_name 建立索引

CREATE INDEX index_name
ON table_name(column_name);
// 為 table_name表中的 column_name列建立索引

CREATE INDEX index_name
ON table_name(column_name1,column_name2,……);
// 聚簇索引找蜜，即為多個(gè)列建立索引

CREATE UNIQUE INDEX index_name
on table_name (column_name);
// 唯一索引

DROP INDEX index_name;
// 刪除索引

SQL中的函數(shù)

SQL 擁有很多可用于計(jì)數(shù)和計(jì)算的內(nèi)建函數(shù)饼暑。

函數(shù)的語(yǔ)法

內(nèi)建 SQL 函數(shù)的語(yǔ)法是：

SELECT function(列) FROM 表

函數(shù)基本類型

主要分為兩類，

1. 合計(jì)函數(shù)（Aggregate function）洗做，面對(duì)一系列的值弓叛，返回一個(gè)單一的值
2. Scalar函數(shù)：面對(duì)一個(gè)輸入值，返回基于輸入值的單一值

合計(jì)函數(shù) Aggregate function

合計(jì)函數(shù)常與group by操作符一起使用诚纸，用來(lái)分組計(jì)數(shù)
也可以和 order by 一起使用撰筷，來(lái)對(duì)數(shù)據(jù)進(jìn)行排序

1. count(column) 求行數(shù)
2. avg 求平均
3. sum 求和
4. min 求最小值
5. max 求最大值
6. count(distinct column) 求不同值的個(gè)數(shù)

例如：選擇視圖base中的height列，并將數(shù)據(jù)按照height分組畦徘，計(jì)算每組的數(shù)量num毕籽，并將num按照倒序排列

SELECT height,count(*) as num
FROM base
GROUP BY height
ORDER BY num DESC;

Scalar 函數(shù)

1. round(column_name, decimals) 將數(shù)據(jù)舍入為指定小數(shù)抬闯；
2. Now()，返回當(dāng)前時(shí)間
3. len(column_name)关筒，返回當(dāng)前字符的長(zhǎng)度
4. 其他

Union 操作符

union 操作符可以合并兩個(gè)或多個(gè)select語(yǔ)句溶握，并取不同的值（即會(huì)默認(rèn)去重）。

union all 默認(rèn)不去重蒸播，返回所有的值奈虾。

使用union 或 union all時(shí)，select語(yǔ)句必須有相同數(shù)量的列和數(shù)據(jù)類型廉赔。

union以后，會(huì)選擇第一個(gè)select語(yǔ)句里面的列匾鸥，作為列名蜡塌。并將其他select語(yǔ)句里面的值對(duì)應(yīng)插入結(jié)果中

例如，我們要將 baseball 表的 name勿负、height 列和 test 表中的 Name馏艾、Age 列合并

select name,height from baseball
union
select Name,Age from test;

結(jié)果會(huì)用 name 和 height 作為列名。

join

引用兩個(gè)表時(shí)奴愉，我們可以使用join和on關(guān)鍵詞琅摩，也可以直接用where來(lái)進(jìn)行匹配

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 

與

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

是相同的作用

inner join和join的作用是一樣的，在表中至少存在一個(gè)匹配時(shí)锭硼，返回行房资。如果 "Persons" 中的行在 "Orders" 中沒有匹配，就不會(huì)列出這些行檀头。

left join

在某些數(shù)據(jù)庫(kù)中轰异，left join 也叫做 left out join.

LEFT JOIN 關(guān)鍵字會(huì)從左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中沒有匹配的行暑始。

tab1

tab2

我們使用left join來(lái)連接 tab1和tab2時(shí)搭独，（tab1作為左表），會(huì)默認(rèn)輸出tab1的所有行廊镜，如果對(duì)應(yīng)tab2中沒有匹配的值牙肝，則沒有值的部分用空值或者null表示。如果對(duì)應(yīng)到tab2中有多個(gè)匹配值嗤朴，則把每個(gè)值都打印出來(lái)配椭。

SELECT tab1.id, tab1.size, tab2.name
FROM tab1
LEFT JOIN tab2
ON tab1.size = tab2.size;

打印結(jié)果如下：

如果反過(guò)來(lái)，把tab2作為左表雹姊，則輸出結(jié)果為

right join

RIGHT JOIN 關(guān)鍵字會(huì)右表 (table_name2) 那里返回所有的行颂郎，即使在左表 (table_name1) 中沒有匹配的行。

與left join的原理類似容为，不過(guò)在sqlite3中乓序，不支持right join和full join寺酪，其他數(shù)據(jù)庫(kù)可能支持。

FULL JOIN

只要其中某個(gè)表存在匹配替劈，F(xiàn)ULL JOIN 關(guān)鍵字就會(huì)返回行寄雀。

語(yǔ)法

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注釋：在某些數(shù)據(jù)庫(kù)中， FULL JOIN 稱為 FULL OUTER JOIN陨献。

從上面的分析我們可以知道盒犹，join的作用主要是匹配，通過(guò)不同表的列之間的關(guān)系進(jìn)行匹配眨业，而union的作用主要是合并急膀，只需要合并的select語(yǔ)句有相同的列數(shù)和相似的數(shù)據(jù)類型就可以進(jìn)行合并。

也就是龄捡，如果我們需要卓嫂，查詢的時(shí)候，可以把name和Email合并到同一個(gè)列中聘殖。

where 和 on 晨雳、having的差別

首先，where 和 on 和 having 都是可以加條件的子句奸腺。

where 和 on 差別

如下表

where 和 having 的差別

在 SQL 中增加 HAVING 子句原因是讹蘑，WHERE 關(guān)鍵字無(wú)法與合計(jì)函數(shù)一起使用喜鼓。

1. 如果要對(duì) 合計(jì)函數(shù)進(jìn)行過(guò)濾，那就要使用 having衔肢。
2. 一般來(lái)說(shuō)庄岖，having要和合計(jì)函數(shù)（例如：count、sum角骤、avg隅忿、min、max）一起使用邦尊，group by 一起使用背桐。
3. 一般是on 最先生效，再是 where進(jìn)行條件過(guò)濾蝉揍，最后才是計(jì)算合計(jì)函數(shù)链峭，再用having對(duì)合計(jì)函數(shù)篩選。

舉例

用 left join 來(lái)檢驗(yàn) on 和 where 的差別又沾。
left join 會(huì)返回左表所有的行弊仪，不論右表能不能匹配條件熙卡，如果右表有多個(gè)匹配值。

用on的話励饵，是先把不符合條件的記錄過(guò)濾后才進(jìn)行統(tǒng)計(jì)驳癌；
用where的話，系統(tǒng)首先根據(jù)各個(gè)表之間的聯(lián)接條件役听，把多個(gè)表合成一個(gè)臨時(shí)表后颓鲜，再由where進(jìn)行過(guò)濾；

故典予，對(duì)于上述的 tab1 和 tab2甜滨，
如果使用on進(jìn)行過(guò)濾：

select *
from tab1
left join tab2
on tab1.size = tab2.size
and tab2.name = 'aaa';

這里會(huì)先按照 tab1.size = tab2.size 和 條件 tab2.name = 'aaa'進(jìn)行過(guò)濾，再將左右表進(jìn)行連接瘤袖，故打印結(jié)果如下：

如果使用where進(jìn)行過(guò)濾

select *
from tab1
left join tab2
on tab1.size = tab2.size
where tab2.name = 'aaa';

這里會(huì)先按照 tab1.size = tab2.size 將左右表進(jìn)行連接衣摩，再使用where進(jìn)行條件過(guò)濾，故打印結(jié)果如下：

總結(jié)

on孽椰、where、having 都可以進(jìn)行過(guò)濾凛篙，作用的順序不一樣黍匾。
on是先過(guò)濾再連接；where是先連接呛梆，在過(guò)濾
having可以和where一起使用锐涯，先用where進(jìn)行條件過(guò)濾，再用having進(jìn)行合計(jì)函數(shù)過(guò)濾填物。