《SQL必知必會(huì)》讀書筆記
點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，
設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨
SQL專欄
目前手邊的數(shù)據(jù)庫是 mysql，所以以下示例均是由 mysql 演示。由于現(xiàn)在大部分工具都支持語法高亮，所以以下關(guān)鍵字都使用小寫。
準(zhǔn)備
工具
mycli，一個(gè)使用python編寫的終端工具，支持語法高亮，自動(dòng)補(bǔ)全，多行模式，并且如果你熟悉vi的話，可以使用vi-mode快速移動(dòng)，編輯?？傊?，vi + mycli 簡(jiǎn)直是神器！
同樣， postgreSQL 可以使用pgcli。
pip install -U mycli # 默認(rèn)你已經(jīng)安裝了pip
樣例表
示例中有兩個(gè)表，分為 student 學(xué)生表與 class 班級(jí)表。student 表中有 class_id 關(guān)聯(lián) class 表。以下是兩個(gè)表數(shù)據(jù)的 sql。另外，最后有三道小練習(xí)題會(huì)用到樣例表。
create table class
( id int(11) not null auto_increment,
name varchar(50) not null,
primary key (id)
);
create table student
( id int(11) not null auto_increment,
name varchar(50) not null,
age smallint default 20,
sex enum('male', 'famale'),
score tinyint comment '入學(xué)成績(jī)',
class_id int(11),
createTime timestamp default current_timestamp,
primary key (id),
foreign key (class_id) references class (id)
);
insert into class (name)
values ('軟件工程'), ('市場(chǎng)營(yíng)銷');
insert into student (name, age, sex, score, class_id)
values ('張三', 21, 'male', 100, 1);
insert into student (name, age, sex, score, class_id)
values ('李四', 22, 'male', 98, 1);
insert into student (name, age, sex, score, class_id)
values ('王五', 22, 'male', 99, 1);
insert into student (name, age, sex, score, class_id)
values ('燕七', 21, 'famale', 34, 2);
insert into student (name, age, sex, score, class_id)
values ('林仙兒', 23, 'famale', 78, 2);（提示：可以左右滑動(dòng)代碼）
SQL 基礎(chǔ)
術(shù)語
SQL 管理和操縱數(shù)據(jù)。另外也有一些 NoSQL 數(shù)據(jù)庫，比如 MongoDB。因?yàn)?/span>NoSQL為非關(guān)系型數(shù)據(jù)庫，一般不支持join操作，因此會(huì)有一些非正則化(denormalization)的數(shù)據(jù)，查詢也比較快。NoSQL 不需要固定列，一般沒有 schema，同時(shí)也利于垂直擴(kuò)展。char, varchar 需要存儲(chǔ)數(shù)據(jù)的長(zhǎng)度方差小的時(shí)候適合存儲(chǔ) char，否則varchar。varchar會(huì)使用額外長(zhǎng)度存儲(chǔ)字符串長(zhǎng)度，占用存儲(chǔ)空間較大。兩者對(duì)字符串末尾的空格處理的策略不同，不同的DBMS又有不同的策略，設(shè)計(jì)數(shù)據(jù)庫的時(shí)候應(yīng)當(dāng)注意到這個(gè)區(qū)別。datetime, timestamp datetime存儲(chǔ)時(shí)間范圍從1001年到9999年。timestamp保存了自1970年1月1日的秒數(shù)，因?yàn)榇鎯?chǔ)范圍比較小，自然存儲(chǔ)空間占用也比較小。日期類型可以設(shè)置更新行時(shí)自動(dòng)更新日期，建議日期時(shí)間類型根據(jù)精度存儲(chǔ)為這兩個(gè)類型。如今 DBMS 能夠存儲(chǔ)微秒級(jí)別的精度，比如mysql默認(rèn)存儲(chǔ)精度為秒，但可以指定到微秒級(jí)別，即小數(shù)點(diǎn)后六位小數(shù)enum 對(duì)于一些固定，不易變動(dòng)的狀態(tài)碼建議存儲(chǔ)為 enum類型，具有更好的可讀性，更小的存儲(chǔ)空間，并且可以保證數(shù)據(jù)有效性。
小問題：如何存儲(chǔ)IP地址？
Row
數(shù)據(jù)表的每一行記錄。如學(xué)生張三。
檢索數(shù)據(jù)
# 檢索單列
select name from student;
# 檢索多列
select name, age, class from student;
# 檢索所有列
select * from student;
# 對(duì)某列去重
select distinct class from student;
# 檢索列-選擇區(qū)間
# offset 基數(shù)為0，所以 `offset 1` 代表從第2行開始
select * from student limit 1, 10;
select * from student limit 10 offset 1;排序
默認(rèn)排序是 ASC，所以一般升序的時(shí)候不需指定，降序的關(guān)鍵字是 DESC。使用 B-Tree 索引可以提高排序性能，但只限最左匹配。關(guān)于索引可以查看以下 FAQ。
# 檢索單列
select name from student;
# 檢索多列
select name, age, class from student;
# 檢索所有列
select * from student;
# 對(duì)某列去重
select distinct class from student;
# 檢索列-選擇區(qū)間
# offset 基數(shù)為0，所以 `offset 1` 代表從第2行開始
select * from student limit 1, 10;
select * from student limit 10 offset 1;數(shù)據(jù)過濾
# 找到學(xué)號(hào)為1的學(xué)生
select * from student where number = 1;
# 找到學(xué)號(hào)為在 [1, 10] 的學(xué)生(閉區(qū)間)
select * from student where number between 1 and 10;
# 找到未設(shè)置電子郵箱的學(xué)生
# 注意不能使用 =
select * from student where email is null;
# 找到一班中大于23歲的學(xué)生
select * from student where class_id = 1 and age > 23;
# 找到一班或者大于23歲的學(xué)生
select * from student where class_id = 1 or age > 22;
# 找到一班與二班的學(xué)生
select * from student where class_id in (1, 2);
# 找到不是一班二班的學(xué)生
select * from student where class_id not in (1, 2);計(jì)算字段
CONCAT
select concat(name, '(', age, ')') as nameWithAge from student;
select concat('hello', 'world') as helloworld;Math
select age - 18 as relativeAge from student;
select 3 * 4 as n;更多函數(shù)可以查看 API 手冊(cè)，同時(shí)也可以自定義函數(shù)(User Define Function)。
可以直接使用 select 調(diào)用函數(shù)
select now();
select concat('hello', 'world');數(shù)據(jù)匯總
聚集函數(shù)，一些對(duì)數(shù)據(jù)進(jìn)行匯總的函數(shù)，常見有 COUNT， MIN， MAX， AVG， SUM 五種。
# 統(tǒng)計(jì)1班人數(shù)
select count(*) from student where class_id = 1;數(shù)據(jù)分組
使用 group by 進(jìn)行數(shù)據(jù)分組，可以使用聚合函數(shù)對(duì)分組數(shù)據(jù)進(jìn)行匯總，使用 having 對(duì)分組數(shù)據(jù)進(jìn)行篩選。
# 按照班級(jí)進(jìn)行分組并統(tǒng)計(jì)各班人數(shù)
select class_id, count(*) from student group by class_id;
# 列出大于三個(gè)學(xué)生的班級(jí)
select class_id, count(*) as cnt from student
group by class_id having cnt > 3;子查詢
# 列出軟件工程班級(jí)中的學(xué)生
select * from student where class_id in (
select id from class where class_id = '軟件工程'
);關(guān)聯(lián)聯(lián)接
雖然兩個(gè)表擁有公共字段便可以創(chuàng)建聯(lián)接，但是使用外鍵可以更好地保證數(shù)據(jù)完整性。比如當(dāng)對(duì)一個(gè)學(xué)生插入一條不存在的班級(jí)的時(shí)候，便會(huì)插入失敗。一般來說，聯(lián)接比子查詢擁有更好的性能。
# 列出軟件工程班級(jí)中的學(xué)生
select * from student, class
where student.class_id = class.id and class.name = '軟件工程';內(nèi)聯(lián)接
內(nèi)聯(lián)接又叫等值聯(lián)接。
# 列出軟件工程班級(jí)中的學(xué)生
select * from student
inner join class on student.class_id = class.id
where class.name = '軟件工程';自聯(lián)接
# 列出與張三同一班級(jí)的學(xué)生
select * from student s1
inner join student s2 on s1.class_id = s2.class_id
where s1.name = '張三';外聯(lián)接
--列出每個(gè)學(xué)生的班級(jí)，弱沒有班級(jí)則為null
select name, class.name from student
left join class on student.class_id = class.id;插入數(shù)據(jù)
可以采用以下方法插入一條數(shù)據(jù)，不過嚴(yán)重依賴表中列的順序關(guān)系，推薦指定列名插入數(shù)據(jù)，并且可以插入部分列。
# 插入一條數(shù)據(jù)
insert into student values(8, '陸小鳳', 24, 1, 3);
insert into student(name, age, sex, class_id)
values(9, '花無缺', 25, 1, 3);修改數(shù)據(jù)
更新
# 修改張三的班級(jí)
update student set class_id = 2 where name = '張三';刪除
# 刪除張三的數(shù)據(jù)
delete from student where name = '張三';
# 刪除表中所有數(shù)據(jù)
delete from student;
# 更快地刪除表中所有數(shù)據(jù)
truncate table student;創(chuàng)建表與更新表
# 創(chuàng)建學(xué)生表
create table student (
id int(11) not null auto_increment,
name varchar(50) not null,
age smallint default 20,
sex enum('male', 'famale'),
score tinyint comment '入學(xué)成績(jī)',
class_id int(11),
createTime timestamp default current_timestamp,
primary key (id),
foreign key (class_id) references class (id)
);
# 根據(jù)舊表創(chuàng)建新表
create table student_copy as select * from student;
# 刪除 age 列
alter table student drop column age;
# 添加 age 列
alter table student add column age smallint;
# 刪除學(xué)生表
drop table student;視圖
視圖是一種虛擬的表，便于更好地在多個(gè)表中檢索數(shù)據(jù)，視圖也可以作寫操作，不過最好作為只讀。在需要多個(gè)表聯(lián)接的時(shí)候可以使用視圖。
create view v_student_with_classname as
select student.name name, class.name class_name
from student left join class
where student.class_id = class.id;
select * from v_student_with_classname;約束
primiry key
任意兩行絕對(duì)沒有相同的主鍵，且任一行不會(huì)有兩個(gè)主鍵且主鍵絕不為空。使用主鍵可以加快索引。
alter table student add constraint primary key (id);foreign key
外鍵可以保證數(shù)據(jù)的完整性。有以下兩種情況。
插入張三豐5班到student表中會(huì)失敗，因?yàn)?班在class表中不存在。
class表刪除3班會(huì)失敗，因?yàn)殛懶▲P和楚留香還在3班。
alter table student add constraint
foreign key (class_id) references class (id);unique key
唯一索引保證該列值是唯一的，但可以允許有null。
alter table student add constraint unique key (name);check
檢查約束可以使列滿足特定的條件，如果學(xué)生表中所有的人的年齡都應(yīng)該大于0。
不過很可惜mysql不支持，可以使用觸發(fā)器代替
alter table student add constraint check (age > 0);index
索引可以更快地檢索數(shù)據(jù)，但是降低了更新操作的性能。
create index index_on_student_name on student (name);
alter table student add constraint key(name );觸發(fā)器
可以在插入，更新，刪除行的時(shí)候觸發(fā)事件。
# 創(chuàng)建觸發(fā)器
# 比如mysql中沒有check約束，可以使用創(chuàng)建觸發(fā)器，當(dāng)插入數(shù)據(jù)小于0時(shí)，置為0。
create trigger reset_age before insert on student for each row
begin
if NEW.age < 0 then
set NEW.age = 0;
end if;
end;
# 打印觸發(fā)器列表
show triggers;存儲(chǔ)過程
存儲(chǔ)過程可以視為一個(gè)函數(shù)，根據(jù)輸入執(zhí)行一系列的 sql 語句。存儲(chǔ)過程也可以看做對(duì)一系列數(shù)據(jù)庫操作的封裝，一定程度上可以提高數(shù)據(jù)庫的安全性。
# 創(chuàng)建存儲(chǔ)過程
create procedure create_student(name varchar(50))
begin
insert into students(name) values (name);
end;
# 調(diào)用存儲(chǔ)過程
call create_student('shanyue');SQL 練習(xí)
1. 根據(jù)班級(jí)學(xué)生的分?jǐn)?shù)進(jìn)行排名，如果分?jǐn)?shù)相等則為同一名次
select id, name, score, (
select count(distinct score) from student s2
where s2.score >= s1.score
) as rank
from student s1
order by s1.score desc;在where以及排序中經(jīng)常用到的字段需要添加Btree索引，因此 score 上可以添加索引。
Result:
| id | name | score | rank |
|---|---|---|---|
| 1 | 張三 | 100 | 1 |
| 3 | 王五 | 99 | 2 |
| 2 | 李四 | 98 | 3 |
| 5 | 林仙兒 | 78 | 4 |
| 4 | 燕七 | 34 | 5 |
2. 寫一個(gè)函數(shù)，獲取第 N 高的分?jǐn)?shù)
create function getNthHighestScore(N int) return int
begin
declare M int default N-1;
return (
select distinct score from student
order by score desc limit M, 1;
)
end;
select getNthHighestScore(2);Result:
| getNthHighestScore(2) | |
|---|---|
| 99 |
3. 檢索每個(gè)班級(jí)分?jǐn)?shù)前兩名學(xué)生，并顯示排名
select
class.id class_id,
class.name class_name,
s.name student_name,
score,
rank
from (
select *,
(
select count(distinct score) from student s2
where s2.score >= s1.score
and s2.class_id = s1.class_id
) as rank
from student s1
) as s
left join class on s.class_id = class.id
where rank <= 2;
--如果不想在from中包含select子句，也可以像如下檢索，不過不顯示排名
select
class.id class_id,
class.name class_name,
s1.name name,
score
from student s1
left join class on s1.class_id = class.id
where (
select count(*) from student s2
where s2.class_id = s1.class_id
and s1.score <= s2.score) <= 2
order by s1.class_id, score desc;Result:
| class_name | student_name | score | rank |
|---|---|---|---|
| 軟件工程 | 張三 | 100 | 1 |
| 軟件工程 | 王五 | 99 | 2 |
| 市場(chǎng)營(yíng)銷 | 燕七 | 34 | 2 |
| 市場(chǎng)營(yíng)銷 | 林仙兒 | 78 | 1 |
FAQ
1. inner join 與 outer join 的區(qū)別是什么
2. 如何根據(jù)一個(gè)表的數(shù)據(jù)更新另一個(gè)表
比如以上 student 表保存著成績(jī)，另有一表 score_correct 內(nèi)存因失誤而需修改的學(xué)生成績(jī)。
在mysql中，可以使用如下語法
update
student,
score_correct
set student.score = score_correct.score
where student.id = score_correct.uid;3. 索引是如何工作的
簡(jiǎn)單來說，索引分為 hash 和 B-Tree 兩種。hash 查找的時(shí)間復(fù)雜度為O(1)。B-Tree 其實(shí)是 B+Tree，一種自平衡多叉搜索數(shù)，自平衡代表每次插入和刪除數(shù)據(jù)都會(huì)需要?jiǎng)討B(tài)調(diào)整樹高，以降低平衡因子。B+Tree 只有葉子節(jié)點(diǎn)會(huì)存儲(chǔ)信息，并且會(huì)使用鏈表鏈接起來。因此適合范圍查找以及排序，不過只能搜索最左前綴，如只能索引以 a開頭的姓名，卻無法索引以 a結(jié)尾的姓名。另外，Everything is trade off。B+Tree的自平衡特性保證能夠快速查找的同時(shí)也降低了更新的性能，需要權(quán)衡利弊。
4. 如何聯(lián)接多個(gè)行的字段
在mysql中，可以使用 group_concat
select group_concat(name) from student;5. 如何在一個(gè)sql語句中插入多行數(shù)據(jù)
values 使用逗號(hào)相隔，可以插入多行數(shù)據(jù)
insert into student(id, name) values (), (), ()6. 如何在 select中使用條件表達(dá)式
示例，在student表中，查詢所有人成績(jī)，小于60則顯示為0
select id, name, if(score < 60, 0, score) score from student;7. 如何找到重復(fù)項(xiàng)
select name, sex, count(*) times from student
group by name, sex
having times > 1;8. 什么是SQL注入
如有一條查詢語句為
"select * from (" + table + ");"當(dāng)table取值 student);drop table student;-- 時(shí)，語句變?yōu)榱?，?huì)刪掉表，造成攻擊。
"select * from (student); drop table student; --);"來源：https://segmentfault.com/p/1210000011760973/read
推薦閱讀
社會(huì)內(nèi)卷的真正原因：華為內(nèi)部論壇的這篇雄文火了 50 款數(shù)據(jù)可視化分析工具大集合，總有一款適合你 悟空無姓無名的時(shí)候，閻王生死簿是怎么寫的呢？ 5號(hào)發(fā)工資和25號(hào)發(fā)工資，還能看出公司是否靠譜？?? 如果把14億中國(guó)人都拉到一個(gè)微信群。。。 假如面試中大家都說實(shí)話....
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群
