30分鐘SQL指南

shanyue發表於2019-03-22

本篇文章是 SQL 必知必會 的讀書筆記，SQL必知必會的英文名叫做 Sams Teach Yourself in 10 Minutes 。但是，我肯定是不能夠在10分鐘就能學會本書所有涉及到的sql，所以就起個名字叫30分鐘學會SQL語句（其實半個小時也沒有學會...）。

目前手邊的資料庫是 mysql，所以以下示例均是由 mysql 演示。由於現在大部分工具都支援語法高亮，所以以下關鍵字都使用小寫。

原文連結見 shanyue.tech/post/sql-gu…

準備

工具

mycli，一個使用python編寫的終端工具，支援語法高亮，自動補全，多行模式，並且如果你熟悉vi的話，可以使用vi-mode快速移動，編輯。總之，vi + mycli 簡直是神器！

同樣，postgreSQL 可以使用pgcli。

pip install -U mycli    # 預設你已經安裝了pip
複製程式碼

樣例表

示例中有兩個表，分為 student 學生表與 class 班級表。student 表中有 class_id 關聯 class 表。以下是兩個表資料的 sql。另外，最後有三道小練習題會用到樣例表。

create table class (
  id int(11) not null auto_increment comment '班級id',
  name varchar(50) not null comment '班級名',
  primary key (id)
) comment '班級表';

create table student (
  id int(11) not null auto_increment comment '學生id',
  name varchar(50) not null comment '學生姓名',
  age tinyint unsigned default 20 comment '學生年齡',
  sex enum('male', 'famale') comment '性別',
  score tinyint comment '入學成績',
  class_id int(11) comment '班級',
  createTime timestamp default current_timestamp comment '建立時間',
  primary key (id),
  foreign key (class_id) references class (id)
) comment '學生表';

insert into class (name) values ('軟體工程'), ('市場營銷');

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);
複製程式碼

SQL 基礎

術語

Database

資料庫值一系列有關聯資料的集合，而操作和管理這些資料的是DBMS，包括MySQL，PostgreSQL，MongoDB，Oracle，SQLite等等。 RDBMS 是基於關係模型的資料庫，使用 SQL 管理和操縱資料。另外也有一些 NoSQL 資料庫，比如 MongoDB。因為NoSQL為非關係型資料庫，一般不支援join操作，因此會有一些非正則化(denormalization)的資料，查詢也比較快。
Table

具有特定屬性的結構化檔案。比如學生表，學生屬性有學號，年齡，性別等。schema (模式) 用來描述這些資訊。 NoSQL 不需要固定列，一般沒有 schema，同時也利於垂直擴充套件。
Column

表中的特定屬性，如學生的學號，年齡。每一列都具有資料型別。
Data Type

每一列都具有資料型別，如 char, varchar，int，text，blob, datetime，timestamp。根據資料的粒度為列選擇合適的資料型別，避免無意義的空間浪費。如下有一些型別對比
- char, varchar 需要儲存資料的長度方差小的時候適合儲存char，否則varchar。 varchar 會使用額外長度儲存字串長度，佔用儲存空間較大。兩者對字串末尾的空格處理的策略不同，不同的DBMS又有不同的策略，設計資料庫的時候應當注意到這個區別。
- datetime, timestamp datetime 儲存時間範圍從1001年到9999年。 timestamp 儲存了自1970年1月1日的秒數，因為儲存範圍比較小，自然儲存空間佔用也比較小。日期型別可以設定更新行時自動更新日期，建議日期時間型別根據精度儲存為這兩個型別。如今 DBMS 能夠儲存微秒級別的精度，比如 mysql 預設儲存精度為秒，但可以指定到微秒級別，即小數點後六位小數
- enum 對於一些固定，不易變動的狀態碼建議儲存為 enum 型別，具有更好的可讀性，更小的儲存空間，並且可以保證資料有效性。
插一個小問題：如何儲存IP地址
Row

資料表的每一行記錄。如學生張三。

檢索資料

-- 檢索單列
select name from student;

-- 檢索多列
select name, age, class from student;

-- 檢索所有列
select * from student;

-- 對某列去重
select distinct class from student;

-- 檢索列-選擇區間
-- offset 基數為0，所以 `offset 1` 代表從第2行開始
select * from student limit 1, 10;
select * from student limit 10 offset 1;
複製程式碼

排序

預設排序是 ASC，所以一般升序的時候不需指定，降序的關鍵字是 DESC。使用 B-Tree 索引可以提高排序效能，但只限最左匹配。關於索引可以檢視以下 FAQ。

-- 根據學號降序排列
select * from student order by number desc;

-- 新增索引 (score, name) 可以提高排序效能
-- 但是索引 (name, score) 對效能毫無幫助，此謂最左匹配，可以根據 B+Tree 進行理解
select * from student order by score desc, name;
複製程式碼

資料過濾

資料篩選，或者資料過濾在 sql 中使用頻率最高

-- 找到學號為1的學生
select * from student where number = 1;

-- 找到學號為在 [1, 10] 的學生(閉區間)
select * from student where number between 1 and 10;

-- 找到未設定電子郵箱的學生
-- 注意不能使用 =
select * from student where email is null;

-- 找到一班中大於23歲的學生
select * from student where class_id = 1 and age > 23;

-- 找到一班或者大於23歲的學生
select * from student where class_id = 1 or age > 22;

-- 找到一班與二班的學生
select * from student where class_id in (1, 2);

-- 找到不是一班二班的學生
select * from student where class_id not in (1, 2);
複製程式碼

計算欄位

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;
複製程式碼

更多函式可以檢視 API 手冊，同時也可以自定義函式(User Define Function)。

可以直接使用 select 呼叫函式

select now();
select concat('hello', 'world');
複製程式碼

資料聚合 (aggregation)

聚合函式，一些對資料進行彙總的函式，常見有 COUNT，MIN，MAX，AVG，SUM 五種。

-- 統計1班人數
select count(*) from student where class_id = 1;
複製程式碼

資料分組

使用 group by 進行資料分組，可以使用聚合函式對分組資料進行彙總，使用 having 對分組資料進行篩選。

-- 按照班級進行分組並統計各班人數
select class_id, count(*) from student group by class_id;

-- 列出大於三個學生的班級
select class_id, count(*) as cnt from student group by class_id having cnt > 3;
複製程式碼

子查詢

-- 列出軟體工程班級中的學生
select * from student where class_id in (
  select id from class where name = '軟體工程'
);
複製程式碼

聯接

雖然兩個表擁有公共欄位便可以建立聯接，但是使用外來鍵可以更好地保證資料完整性。比如當對一個學生插入一條不存在的班級的時候，便會插入失敗。一般來說，聯接比子查詢擁有更好的效能。

-- 列出軟體工程班級中的學生
select * from student, class
where student.class_id = class.id and class.name = '軟體工程';
複製程式碼

內聯接

內聯接又叫等值聯接。

-- 列出軟體工程班級中的學生
select * from student
inner join class on student.class_id = class.id
where class.name = '軟體工程';
複製程式碼

自聯接

自連線就是相同的表進行聯接

-- 列出與張三同一班級的學生
select * from student s1
inner join student s2 on s1.class_id = s2.class_id
where s1.name = '張三';
複製程式碼

外聯接

外聯接分為 left join 與 right join，left join 指左側永不會為 null，right join 指右側永不會為 null。

-- 列出每個學生的班級，若沒有班級則為null
select name, class.name from student
left join class on student.class_id = class.id;
複製程式碼

插入資料

使用 insert into 向表中插入資料，也可以插入多行。

插入時可以不指定列名，不過嚴重依賴表中列的順序關係，推薦指定列名插入資料，並且可以插入部分列。

-- 插入一條資料
insert into student values(8, '陸小鳳', 24, 1, 3);

insert into student(name, age, sex, class_id) values(9, '花無缺', 25, 1, 3);
複製程式碼

修改資料

在修改重要資料時，務必先 select 確認是否需要運算元據，然後 begin 方便及時 rollback

更新

-- 修改張三的班級
update student set class_id = 2 where name = '張三';
複製程式碼

刪除

-- 刪除張三的資料
delete from student where name = '張三';

-- 刪除表中所有資料
delete from student;

-- 更快地刪除表中所有資料
truncate table student;
複製程式碼

建立表與更新表

-- 建立學生表，注意新增必要的註釋
create table student (
  id int(11) not null auto_increment comment '學生id',
  name varchar(50) not null comment '學生姓名',
  age tinyint unsigned default 20 comment '學生年齡',
  sex enum('male', 'famale') comment '性別',
  score tinyint comment '入學成績',
  class_id int(11) comment '班級',
  createTime timestamp default current_timestamp comment '建立時間',
  primary key (id),
  foreign key (class_id) references class (id)
) comment '學生表';

-- 根據舊錶建立新表
create table student_copy as select * from student;

-- 刪除 age 列
alter table student drop column age;

-- 新增 age 列
alter table student add column age smallint;

-- 刪除學生表
drop table student;
複製程式碼

檢視

檢視是一種虛擬的表，便於更好地在多個表中檢索資料，檢視也可以作寫操作，不過最好作為只讀。在需要多個表聯接的時候可以使用檢視。

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

任意兩行絕對沒有相同的主鍵，且任一行不會有兩個主鍵且主鍵絕不為空。使用主鍵可以加快索引。
```
alter table student add constraint primary key (id);
複製程式碼
```
foreign key

外來鍵可以保證資料的完整性。有以下兩種情況。
- 插入張三丰5班到student表中會失敗，因為5班在class表中不存在。
- class表刪除3班會失敗，因為陸小鳳和楚留香還在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

檢查約束可以使列滿足特定的條件，如果學生表中所有的人的年齡都應該大於0。

不過很可惜mysql不支援，可以使用觸發器代替
```
alter table student add constraint check (age > 0);
複製程式碼
```

index

索引可以更快地檢索資料，但是降低了更新操作的效能。

create index index_on_student_name on student (name);

alter table student add constraint key(name);
複製程式碼

觸發器

開發過程中從來沒有使用過，有可能是我經驗少

可以在插入，更新，刪除行的時候觸發事件。

場景:

資料約束，比如學生的年齡必須大於0
hook，提供資料庫級別的 hook

-- 建立觸發器
-- 比如mysql中沒有check約束，可以使用建立觸發器，當插入資料小於0時，置為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;

-- 列印觸發器列表
show triggers;
複製程式碼

儲存過程

開發過程中從來沒有使用過，有可能是我經驗少

儲存過程可以視為一個函式，根據輸入執行一系列的 sql 語句。儲存過程也可以看做對一系列資料庫操作的封裝，一定程度上可以提高資料庫的安全性。

-- 建立儲存過程
create procedure create_student(name varchar(50))
begin
  insert into students(name) values (name);
end;

-- 呼叫儲存過程
call create_student('shanyue');
複製程式碼

SQL 實踐

更多練習可以檢視 leetcode

1. 根據班級學生的分數進行排名，如果分數相等則為同一名次

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以及排序中經常用到的欄位需要新增Btree索引，因此 score 上可以新增索引。

Result:

id	name	score	rank
1	張三	100	1
3	王五	99	2
2	李四	98	3
5	林仙兒	78	4
4	燕七	34	5

參考 leetcode: rank-scores

2. 寫一個函式，獲取第 N 高的分數

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

參考 leetcode: nth highset salary

3. 檢索每個班級分數前兩名學生，並顯示排名

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
市場營銷	燕七	34	2
市場營銷	林仙兒	78	1

FAQ

大多根據 stackoverflow 中瀏覽最多的問題整理而成。

`inner join` 與 `outer join` 的區別是什麼

參考 StackOverflow: what is the difference between inner join and outer join

如何根據一個表的資料更新另一個表

比如以上 student 表儲存著成績，另有一表 score_correct 記憶體因失誤而需修改的學生成績。

在mysql中，可以使用如下語法

update student, score_correct set student.score = score_correct.score where student.id = score_correct.uid;
複製程式碼

索引是如何工作的

簡單來說，索引分為 hash 和 B-Tree 兩種。 hash 查詢的時間複雜度為O(1)。 B-Tree 其實是 B+Tree，一種自平衡多叉搜尋數，自平衡代表每次插入和刪除資料都會需要動態調整樹高，以降低平衡因子。B+Tree 只有葉子節點會儲存資訊，並且會使用連結串列連結起來。因此適合範圍查詢以及排序，不過只能搜尋最左字首，如只能索引以a開頭的姓名，卻無法索引以a結尾的姓名。另外，Everything is trade off。B+Tree的自平衡特性保證能夠快速查詢的同時也降低了更新的效能，需要權衡利弊。

參考 StackOverflow: how dow database indexing work

如何聯接多個行的欄位

在mysql中，可以使用group_concat

select group_concat(name) from student;
複製程式碼

參考 StackOverflow: Concatenate many rows into a single text string

如何在一個sql語句中插入多行資料

values 使用逗號相隔，可以插入多行資料

insert into student(id, name) values (), (), ()
複製程式碼

參考 StackOverflow: Inserting multiple rows in a single SQL query

如何在`select`中使用條件表示式

示例，在student表中，查詢所有人成績，小於60則顯示為0

select id, name, if(score < 60, 0, score) score from student;
複製程式碼

如何找到重複項

姓名與班級唯一，找到姓名與班級的重複項，檢索重複次數與id

select name, class_id, group_concat(id), count(*) times from student
group by name, class_id
having times > 1;
複製程式碼

1:1 Relation 設計的必要性在哪裡

參考 stackoverflow.com/questions/5…

如何刪除重複項並只保留首項

姓名與班級唯一，刪除重複項，只保留首項

# mysql 就簡單很多
delete s1 from student s1, student s2
where s1.name = s2.name and s1.sex = s2.sex and s1.id > s2.id;
複製程式碼

參考 StackOverflow: how can i remove duplicate rows

什麼是SQL隱碼攻擊

如有一條查詢語句為

"select * from (" + table + ");"
複製程式碼

當table取值 student); drop table student; -- 時，語句變為了，會刪掉表，造成攻擊。

"select * from (student); drop table student; --);"
複製程式碼

mysql中單引號，雙引號，反引號有什麼區別

反引號(`) 表示table，column 識別符號。主要用在當表名或者列名為保留字的時候。在其它一些DBMS中，也用[]表示表名和列名。

單引號(') 表示字串。

雙引號(") 預設表示字串，但是當sql_mode為ANSI_QUOTES時，雙引號表示表名或者列名。

參考 StackOverflow: when to use single quotes, double quotes and backticks in mysql

Git兩分鐘指南
2014-10-27
Git
30分鐘入門MyBatis
2019-03-03
MyBatis
30分鐘精通React Hooks
2018-11-08
ReactHook
30分鐘泛型教程
2013-05-03
泛型
30分鐘快速瞭解webpack
2019-03-02
Web
【譯】30 分鐘入門 Typescript
2019-01-25
TypeScript
30分鐘理解GraphQL核心概念
2018-04-02
30 分鐘 Qunit 入門教程
2016-11-29
5分鐘 Pipenv 上手指南
2018-12-18
30 分鐘快速入門 Docker 教程
2019-04-09
Docker
30 分鐘理解 CORB 是什麼
2018-08-23
ORB
30 分鐘學會 Flex 佈局
2018-01-16
Flex
30 分鐘 Java Lambda 入門教程
2016-08-10
Java
Tomcat叢集5分鐘指南
2010-08-24
Tomcat
調優: 一個SQL現在30分鐘,而原來只要2秒
2008-01-17
SQL
30分鐘徹底弄懂flex佈局
2018-10-25
Flex
[譯] 30 分鐘 Python 爬蟲教程
2018-05-15
Python爬蟲
30分鐘讓網站支援HTTPS
2016-05-10
網站HTTP
30 分鐘內瞭解 IEC 61850
2024-08-27
3分鐘看完SQL常用語法
2017-04-24
SQL
快速SQL TUNING——1分鐘搞定超慢SQL
2016-06-10
SQL
30分鐘讓你掌握Git的黑魔法
2019-05-27
Git
30分鐘講清楚深度神經網路
2021-09-09
神經網路
30分鐘搭建你的靜態網站
2021-04-02
網站
前置機器學習（三）：30分鐘掌握常用NumPy用法
2020-12-06
機器學習
1 分鐘學會 30 種程式語言
2021-11-15
30分鐘，讓你徹底明白Promise原理
2017-05-27
Promise
30分鐘Git命令入門到放棄
2016-06-27
Git
30分鐘編寫一個Flask應用
2015-02-20
Flask
正規表示式30分鐘入門教程
2012-12-18
30分鐘實現小程式語音識別
2018-11-24
前置機器學習（五）：30分鐘掌握常用Matplotlib用法
2020-12-25
機器學習
numpy 基礎入門 - 30分鐘學會numpy
2018-04-03
30分鐘手把手教你學webpack實戰
2015-09-08
Web
Shell指令碼程式設計30分鐘入門
2016-02-29
指令碼程式設計
30分鐘帶你搞定Dokcer部署Kafka叢集
2024-05-16
Kafka
寶付：30分鐘理解Spark的基本原理
2019-09-27
Spark
CentOS 30分鐘部署 .net core 線上客服系統
2021-06-16
CentOS

30分鐘SQL指南

準備

工具

樣例表

SQL 基礎

術語

檢索資料

排序

資料過濾

計算欄位

資料聚合 (aggregation)

資料分組

子查詢

聯接

插入資料

修改資料

建立表與更新表

檢視

約束

觸發器

儲存過程

SQL 實踐

1. 根據班級學生的分數進行排名，如果分數相等則為同一名次

2. 寫一個函式，獲取第 N 高的分數

3. 檢索每個班級分數前兩名學生，並顯示排名

FAQ

inner join 與 outer join 的區別是什麼

如何根據一個表的資料更新另一個表

索引是如何工作的

如何聯接多個行的欄位

如何在一個sql語句中插入多行資料

如何在select中使用條件表示式

如何找到重複項

1:1 Relation 設計的必要性在哪裡

如何刪除重複項並只保留首項

什麼是SQL隱碼攻擊

mysql中單引號，雙引號，反引號有什麼區別

相關文章

`inner join` 與 `outer join` 的區別是什麼

如何在`select`中使用條件表示式