av资源在线,免费看日逼,亚欧视频在线,一本道无码字慕,乱伦激情,婷婷综合视频,翔田千里在线一区二区三区 ,国产足交内射

點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫(kù)開發(fā)”，

設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨

本篇文章是《SQL 必知必會(huì)》的讀書筆記，SQL必知必會(huì)的英文名叫做 Sams Teach Yourself in 10 Minutes。但是，我肯定是不能夠在10分鐘就能學(xué)會(huì)本書所有涉及到的sql，所以就起個(gè)名字叫30分鐘學(xué)會(huì)SQL語(yǔ)句。

目前手邊的數(shù)據(jù)庫(kù)是 mysql，所以以下示例均是由 mysql 演示。由于現(xiàn)在大部分工具都支持語(yǔ)法高亮，所以以下關(guān)鍵字都使用小寫。

準(zhǔn)備

工具

mycli，一個(gè)使用python編寫的終端工具，支持語(yǔ)法高亮，自動(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ù)語(yǔ)

Database

數(shù)據(jù)庫(kù)值一系列有關(guān)聯(lián)數(shù)據(jù)的集合，而操作和管理這些數(shù)據(jù)的是DBMS，包括MySQL，PostgreSQL，MongoDB，Oracle，SQLite等等。RDBMS 是基于關(guān)系模型的數(shù)據(jù)庫(kù)，使用?SQL?管理和操縱數(shù)據(jù)。另外也有一些?NoSQL?數(shù)據(jù)庫(kù)，比如 MongoDB。因?yàn)?/span>NoSQL為非關(guān)系型數(shù)據(jù)庫(kù)，一般不支持join操作，因此會(huì)有一些非正則化(denormalization)的數(shù)據(jù)，查詢也比較快。

Table

具有特定屬性的結(jié)構(gòu)化文件。比如學(xué)生表，學(xué)生屬性有學(xué)號(hào)，年齡，性別等。schema (模式) 用來(lái)描述這些信息。?NoSQL?不需要固定列，一般沒有 schema，同時(shí)也利于垂直擴(kuò)展。

Column

表中的特定屬性，如學(xué)生的學(xué)號(hào)，年齡。每一列都具有數(shù)據(jù)類型。

Data Type

每一列都具有數(shù)據(jù)類型，如 char, varchar，int，text，blob, datetime，timestamp。根據(jù)數(shù)據(jù)的粒度為列選擇合適的數(shù)據(jù)類型，避免無(wú)意義的空間浪費(fèi)。如下有一些類型對(duì)比

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ù)庫(kù)的時(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è)表?yè)碛泄沧侄伪憧梢詣?chuàng)建聯(lián)接，但是使用外鍵可以更好地保證數(shù)據(jù)完整性。比如當(dāng)對(duì)一個(gè)學(xué)生插入一條不存在的班級(jí)的時(shí)候，便會(huì)插入失敗。一般來(lái)說(shuō)，聯(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, '花無(wú)缺', 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 語(yǔ)句。存儲(chǔ)過程也可以看做對(duì)一系列數(shù)據(jù)庫(kù)操作的封裝，一定程度上可以提高數(shù)據(jù)庫(kù)的安全性。

# 創(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中，可以使用如下語(yǔ)法

update?
student, 
score_correct 
set?student.score = score_correct.score 
where?student.id = score_correct.uid;

3. 索引是如何工作的

簡(jiǎn)單來(lái)說(shuō)，索引分為 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ì)使用鏈表鏈接起來(lái)。因此適合范圍查找以及排序，不過只能搜索最左前綴，如只能索引以 a開頭的姓名，卻無(wú)法索引以 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語(yǔ)句中插入多行數(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注入

如有一條查詢語(yǔ)句為

"select?* from?(" + table + ");"

當(dāng)table取值 student);drop table student;-- 時(shí)，語(yǔ)句變?yōu)榱?，?huì)刪掉表，造成攻擊。

"select?* from?(student); drop?table?student; --);"

來(lái)源：https://segmentfault.com/p/1210000011760973/read

——End——
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
像Excel一樣使用SQL進(jìn)行數(shù)據(jù)分析
Python 與 Excel 終于在一起了
歷經(jīng)60年，數(shù)據(jù)庫(kù)的王者終于出現(xiàn)了......
崩潰，執(zhí)行DELETE沒加WHERE條件，該怎么辦？
我的常用數(shù)據(jù)軟件大公開

這是一個(gè)能學(xué)到技術(shù)的公眾號(hào)，歡迎關(guān)注
點(diǎn)擊「閱讀原文」了解SQL訓(xùn)練營(yíng)

《SQL必知必會(huì)》讀書筆記，30分鐘入門SQL！

準(zhǔn)備

工具

樣例表

術(shù)語(yǔ)

檢索數(shù)據(jù)

排序

數(shù)據(jù)過濾

計(jì)算字段

數(shù)據(jù)匯總

數(shù)據(jù)分組

子查詢

關(guān)聯(lián)聯(lián)接

插入數(shù)據(jù)

修改數(shù)據(jù)

創(chuàng)建表與更新表

視圖

約束

觸發(fā)器

存儲(chǔ)過程

SQL 練習(xí)

2. 寫一個(gè)函數(shù)，獲取第 N 高的分?jǐn)?shù)

3. 檢索每個(gè)班級(jí)分?jǐn)?shù)前兩名學(xué)生，并顯示排名

FAQ

1.?inner join?與?outer join?的區(qū)別是什么

3. 索引是如何工作的

4. 如何聯(lián)接多個(gè)行的字段

5. 如何在一個(gè)sql語(yǔ)句中插入多行數(shù)據(jù)

7. 如何找到重復(fù)項(xiàng)

8. 什么是SQL注入

《SQL必知必會(huì)》讀書筆記，30分鐘入門SQL！

2. 寫一個(gè)函數(shù)，獲取第 N 高的分?jǐn)?shù)

1.?`inner join`?與?`outer join`?的區(qū)別是什么