二、個(gè)人總結(jié)的菜鳥教程|sql教程的知識(shí)點(diǎn)（SQL高級(jí)教程）

二、SQL高級(jí)教程

1、SQL　SELECT TOP

SELECT TOP 子句用于規(guī)定要返回的記錄的數(shù)目。

SELECT TOP 子句對(duì)于擁有數(shù)千條記錄的大型表來說，是非常有用的。

注釋：并非所有的數(shù)據(jù)庫系統(tǒng)都支持 SELECT TOP 子句。

sql server|ms access語法

select top number|percent column_name from table_name;

mysql|oracle語法(與top等價(jià))

select Column_name from table_name limit number;

mysql語法

select * from table_name limit number

select * from websites limit 2

oracle語法

select * from table_name where rownum <= number

select * from BASICINFO t where rownum <= 2

sql server語法

select top 50 percent * from websites

2、SQL LIKE

select column_name from table_name where column_name like pattern;

select * from websites where name like '%G%'

select * from websites where name like '%K%'

select * from websites where name like '%oo%'

select * from websites where name not like '%oo%'

3、SQL通配符

% 0個(gè)或多個(gè)字符

_ 一個(gè)字符

[childList] 列表中任意單一字符

[`childList]或[!chldList] 不在列表中的任意單一字符

select * from websites where url like'%https%'

select * from websites where url like '%oo%'

select * from websites where name like '_oogl%'

select * from websites where name like '_oo_le'

MySQL 中使用 REGEXP 或 NOT REGEXP 運(yùn)算符 (或 RLIKE 和 NOT RLIKE) 來操作正則表達(dá)式。

select * from websites where name regexp '^[GFs]'

select * from websites where name rlike '^[GFs]'

select * from websites where name regexp '^[a-h]'

select * from websites where name regexp '^[^a-h]'

4、SQL IN

select column_name from table_name where colnum_name in (v1,v2,v3...);

select * from websites where name in('淘寶','菜鳥教程');

5、SQL BETWEEN

select column_name from table_name where column_name between v1 and v2;

select * from websites where alexa between 1 and 20

select * from websites where alexa not between 1 and 20

select * from websites where (alexa between 1 and 20) and country not in('usa','ind')

select * from websites where name between 'a' and 'h'

select * from websites where name not between 'a' and 'h'

select * from access_log where date between '2016-5-10' and '2016-5-13'

在某些數(shù)據(jù)庫中，BETWEEN 選取介于兩個(gè)值之間但不包括兩個(gè)測(cè)試值的字段。
在某些數(shù)據(jù)庫中，BETWEEN 選取介于兩個(gè)值之間且包括兩個(gè)測(cè)試值的字段。
在某些數(shù)據(jù)庫中，BETWEEN 選取介于兩個(gè)值之間且包括第一個(gè)測(cè)試值但不包括最后一個(gè)測(cè)試值的字段。(mysql和oracle都包括)

6、SQL別名

select column_name as new_column_name from table_name;

select column_name from table_name as new_table_name;

select name as n,url as u from websites;

select name,concat(name,',',url,',',alexa,',',country) as site_info from websites;

select w.name,w.url,a.count,a.date from websites as w,access_log as a where w.id = a.aid;

select w.name,w.url,a.count,a.date from websites as w,access_log as a where w.id = a.aid and w.name = '菜鳥教程';

在下面的情況下，使用別名很有用：

• 在查詢中涉及超過一個(gè)表

• 在查詢中使用了函數(shù)

• 列名稱很長(zhǎng)或者可讀性差

• 需要把兩個(gè)列或者多個(gè)列結(jié)合在一起

7、SQL連接（join）

SQL join 用于把來自兩個(gè)或多個(gè)表的行結(jié)合起來。

SQL JOIN 子句用于把來自兩個(gè)或多個(gè)表的行結(jié)合起來，基于這些表之間的共同字段。

最常見的 JOIN 類型：SQL INNER JOIN（簡(jiǎn)單的 JOIN）。 SQL INNER JOIN 從多個(gè)表中返回滿足 JOIN 條件的所有行。

select * from websites as w,access_log as a等價(jià)于select * from websites w inner join access_log a  (笛卡爾積）

select * from websites as w,access_log as a where w.id = a.site_id等價(jià)于select * from websites w inner join access_log a on w.id = a.site_id 

• INNER JOIN：如果表中有至少一個(gè)匹配，則返回行

• LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行

• RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行

• FULL JOIN：只要其中一個(gè)表中存在匹配，則返回行

8、SQL INNER JOIN

select column_name(s) from table1 inner join table2 on table1.column_name = table2.column_name

或

select column_name(s) from table1 join table2 on table1.column_name = table2.column_name

select * from websites w inner join access_log a on w.id = a.site_id order by a.count;

INNER JOIN 關(guān)鍵字在表中存在至少一個(gè)匹配時(shí)返回行。如果 "Websites" 表中的行在 "access_log" 中沒有匹配，則不會(huì)列出這些行。(多個(gè)表都要有）

9、SQL LEFT JOIN

LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行，即使右表（table2）中沒有匹配。如果右表中沒有匹配，則結(jié)果為 NULL。

select * from table1 left join table2 on table1.column_name = table2.column_name

或

select * from table1 left outer join table2 on table1.column_name = table2.column_name

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

注釋：LEFT JOIN 關(guān)鍵字從左表（Websites）返回所有的行，即使右表（access_log）中沒有匹配。

10、SQL Right JOIN

RIGHT JOIN 關(guān)鍵字從右表（table2）返回所有的行，即使左表（table1）中沒有匹配。如果左表中沒有匹配，則結(jié)果為 NULL。

select * from table1 right join table2 on table1.column_name = table2.column_name

或

select * from table1 right outer join table2 on table1.column_name = table2.column_name;

在某些數(shù)據(jù)庫中l(wèi)eft join 稱為 left outer join

select * from access_log a right join websites w on a.site_id = w.id

RIGHT JOIN 關(guān)鍵字從右表（Websites）返回所有的行，即使左表（access_log）中沒有匹配。

11、SQL FULL JOIN

FULL OUTER JOIN 關(guān)鍵字只要左表（table1）和右表（table2）其中一個(gè)表中存在匹配，則返回行.

FULL OUTER JOIN 關(guān)鍵字結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果。

select * from table1 full outer join table2 on table1.column_name = table2.column_name;

Mysql不支持full outer join

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

FULL OUTER JOIN 關(guān)鍵字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中沒有匹配或者 "access_log" 表中的行在 "Websites" 表中沒有匹配，也會(huì)列出這些行。

12、SQL UNION

SQL UNION 操作符合并兩個(gè)或多個(gè) SELECT 語句的結(jié)果。

請(qǐng)注意，UNION 內(nèi)部的每個(gè) SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每個(gè) SELECT 語句中的列的順序必須相同。

select column_name(s) from table1

union

select column_name(s) from table2

默認(rèn)地，UNION 操作符選取不同的值。如果允許重復(fù)的值，請(qǐng)使用 UNION ALL。

select column_name(s) from table1

union all

select column_name(s) from table2

UNION 結(jié)果集中的列名總是等于 UNION 中第一個(gè) SELECT 語句中的列名。

select country from websites

union

select country from apps

select country from websites

union all

select country from apps

select name,country from websites

union ALL

select app_name,country from apps

13、SQL SELECT INTO

通過 SQL，您可以從一個(gè)表復(fù)制信息到另一個(gè)表。

SELECT INTO 語句從一個(gè)表復(fù)制數(shù)據(jù)，然后把數(shù)據(jù)插入到另一個(gè)新表中。

MYSQL不支持select into支持insert into select

//復(fù)制整個(gè)表

select * into new_table[in externaldb] from table

//復(fù)制某幾列

select column_name(s) into new_table[in externaldb] from table

新表將會(huì)使用 SELECT 語句中定義的列名稱和類型進(jìn)行創(chuàng)建。您可以使用 AS 子句來應(yīng)用新名稱。

select * into websites_backup from websites 

select name,url into websites_backup from webties;

只復(fù)制中國(guó)的網(wǎng)站插入到新表中：

select name,url into websites_backup from webties wherecountry = 'cn';

復(fù)制多個(gè)表中的數(shù)據(jù)插入到新表中：

select w.name,w.url,a.site_id into websites_backup from websites w lleft join access_log a on w.id = a.site_id;

提示：SELECT INTO 語句可用于通過另一種模式創(chuàng)建一個(gè)新的空表。只需要添加促使查詢沒有數(shù)據(jù)返回的 WHERE 子句即可：

select * into websites_backup from websites where 1=0;

14、SQL INSERT INTO SELECT

通過 SQL，您可以從一個(gè)表復(fù)制信息到另一個(gè)表。

INSERT INTO SELECT 語句從一個(gè)表復(fù)制數(shù)據(jù)，然后把數(shù)據(jù)插入到一個(gè)已存在的表中。

INSERT INTO SELECT 語句從一個(gè)表復(fù)制數(shù)據(jù)，然后把數(shù)據(jù)插入到一個(gè)已存在的表中。目標(biāo)表中任何已存在的行都不會(huì)受影響。

insert into table2 select * from table1

或

insert into table2(column_name(s)) select colnum_name(s) from table1;

insert into apps(app_name,url) select name,url from websites;

15、SQL CREATE DB

create database DBName;

16、SQL CREATE TABLE

create table table_name(

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size)

.....

);

create table persons(

personid int primary key not NULL,

lastname VARCHAR(10),

firstName VARCHAR(10),

address varchar(255),

city varchar(50)

)

17、SQL 約束

create table table_name(

column_name1 datatype(size) constraint_name,

column_name2 datatype(size) constraint_name,

column_name3 datatype(size) constraint_name,

column_name4 datatype(size) constraint_name

);

NOT NULL 指示某列不能存儲(chǔ) NULL 值。

UNIQUE 保證某列的每行必須有唯一的值。

primary key  NOT NULL 和 UNIQUE 的結(jié)合。確保某列（或兩個(gè)列多個(gè)列的結(jié)合）有唯一標(biāo)識(shí)，有助于更容易更快速地找到表中的一個(gè)特定的記錄。

foreign key 保證一個(gè)表中的數(shù)據(jù)匹配另一個(gè)表中的值的參照完整性。

check 保證列中的值符合指定的條件。

default  規(guī)定沒有給列賦值時(shí)的默認(rèn)值.

18、SQL NOT NULL約束

NOT NULL 約束強(qiáng)制列不接受 NULL 值。

NOT NULL 約束強(qiáng)制字段始終包含值。這意味著，如果不向字段添加值，就無法插入新記錄或者更新記錄。

19、SQL UNIQUE

UNIQUE 約束唯一標(biāo)識(shí)數(shù)據(jù)庫表中的每條記錄。

UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。

PRIMARY KEY 約束擁有自動(dòng)定義的 UNIQUE 約束。

請(qǐng)注意，每個(gè)表可以有多個(gè) UNIQUE 約束，但是每個(gè)表只能有一個(gè) PRIMARY KEY 約束。

如需命名 UNIQUE 約束，并定義多個(gè)列的 UNIQUE 約束，請(qǐng)使用下面的 SQL 語法：

MySQL刪除unique約束

SQL Server / Oracle / MS Access：

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

20、SQL PRIMARY KEY

PRIMARY KEY 約束唯一標(biāo)識(shí)數(shù)據(jù)庫表中的每條記錄。

主鍵必須包含唯一的值。

主鍵列不能包含 NULL 值。

每個(gè)表都應(yīng)該有一個(gè)主鍵，并且每個(gè)表只能有一個(gè)主鍵。

create table person1(

id int primary key,

name varchar(20) not null,

age int

)

create table person2(

id int not null unique,

name varchar(20) not null,

age int,

primary key(id)

)

create table person3(

id int not null,

name varchar(20) not null,

age int,

constraint person_id primary key (id)

)

alter table person1 drop PRIMARY KEY

alter table person1 add PRIMARY key (id)

21、SQL FOREIGN KEY

create table table_name(

column_name1 datatype(size) primary key,

column_name2 datatype(size) unique not null,

column_name3 datatype(size)

...

foreign key references other_table(id)

)

create table table_name(

column_name1 datatype(size),

......

constraint p_id primary key(id),

constraint fk_id foreign key(id) references person(id)

)

create table orders(

id int primary key,

name varchar(20) not null,

p_id int not null,

foreign key(p_id) references person1(id)

)

create table orders2(

id int,

p_id int,

constraint order_id primary key (id),

CONSTRAINT fk_id foreign key (p_id) references person2(id)

)

alter table orders drop foreign key orders_ibfk_1

alter table orders add foreign key (p_id) references person1(id)

22、SQL CHECK

CHECK 約束用于限制列中的值的范圍。

如果對(duì)單個(gè)列定義 CHECK 約束，那么該列只允許特定的值。

如果對(duì)一個(gè)表定義 CHECK 約束，那么此約束會(huì)基于行中其他列的值在特定的列中對(duì)值進(jìn)行限制。

create table persons(

id primary key check(id>10)

)

create table person4(

id int primary key check(id>10)

)

alter table person4 add check(id>10)

alter table person4 drop check(...)

24、SQL DEFAULT

DEFAULT 約束用于向列中插入默認(rèn)值。

如果沒有規(guī)定其他的值，那么會(huì)將默認(rèn)值添加到所有的新記錄。

create table Persons(

id int primary key,

name varchar(20) default "zy"

)

create table orders(

id int primary key,

p_id int not null,

orderdate date default getdate(),

foreign key (p_id) references persons(id)

)

create table persons(

id int primary key,

name varchar(20) default 'zy'

)

alter table persons alter name set default 'zz'

alter table persons alter name drop default

25、SQL CREATE INDEX

CREATE INDEX 語句用于在表中創(chuàng)建索引。

在不讀取整個(gè)表的情況下，索引使數(shù)據(jù)庫應(yīng)用程序可以更快地查找數(shù)據(jù)。

您可以在表中創(chuàng)建索引，以便更加快速高效地查詢數(shù)據(jù)。

用戶無法看到索引，它們只能被用來加速搜索/查詢。

注釋：更新一個(gè)包含索引的表需要比更新一個(gè)沒有索引的表花費(fèi)更多的時(shí)間，這是由于索引本身也需要更新。因此，理想的做法是僅僅在常常被搜索的列（以及表）上面創(chuàng)建索引。

create index index_name on table_name(column_name)

create index index_name on persons(name)

在表上創(chuàng)建一個(gè)唯一的索引。不允許使用重復(fù)的值：唯一的索引意味著兩個(gè)行不能擁有相同的索引值。Creates a unique index 

create unique index index_name on table_name(column_name)

創(chuàng)建多列索引

create index index_name on table_name(column_name)

26、SQL DROP

通過使用 DROP 語句，可以輕松地刪除索引、表和數(shù)據(jù)庫。

drop index index_name on table_name

刪除表

drop table table_name

刪除數(shù)據(jù)庫

drop database database_name

如果我們僅僅需要?jiǎng)h除表內(nèi)的數(shù)據(jù)，但并不刪除表本身，那么我們?cè)撊绾巫瞿兀?/span>

truncate table table_name

27、SQL ALTER

ALTER TABLE 語句用于在已有的表中添加、刪除或修改列。

增加列

alter table_name add column_name datattype

修改列

alter table table_name modify column column_name datatype

刪除列

alter table table_name drop column column_name

28、SQL AUTO INCREMENT

Auto-increment 會(huì)在新記錄插入表中時(shí)生成一個(gè)唯一的數(shù)字。

要讓 AUTO_INCREMENT 序列以其他的值起始，請(qǐng)使用下面的 SQL 語法：

要在 "Persons" 表中插入新記錄，我們不必為 "ID" 列規(guī)定值（會(huì)自動(dòng)添加一個(gè)唯一的值）：

oracle的自增

create sequence seq_person

minvalue 1

start with 1

increment by 1

cache 10

29、SQL視圖

SQL視圖創(chuàng)建

SQL CREATE VIEW

create view view_name as select column_name(s) from table_name where ...

在 SQL 中，視圖是基于 SQL 語句的結(jié)果集的可視化的表。

視圖包含行和列，就像一個(gè)真實(shí)的表。視圖中的字段就是來自一個(gè)或多個(gè)數(shù)據(jù)庫中的真實(shí)的表中的字段。

您可以向視圖添加 SQL 函數(shù)、WHERE 以及 JOIN 語句，也可以呈現(xiàn)數(shù)據(jù)，就像這些數(shù)據(jù)來自于某個(gè)單一的表一樣。

SQL視圖更新

SQL CREATE OR REPLACE VIEW

create or replace view view_name as select column_names from table_name where....

SQL視圖刪除

drop view view_name

30、SQL日期

當(dāng)我們處理日期時(shí)，最難的任務(wù)恐怕是確保所插入的日期的格式，與數(shù)據(jù)庫中日期列的格式相匹配。

只要您的數(shù)據(jù)包含的只是日期部分，運(yùn)行查詢就不會(huì)出問題。但是，如果涉及時(shí)間部分，情況就有點(diǎn)復(fù)雜了。

在討論日期查詢的復(fù)雜性之前，我們先來看看最重要的內(nèi)建日期處理函數(shù)。

now() 返回當(dāng)前的日期和時(shí)間

curdate() 返回當(dāng)前日期

31、SQL NULL值

NULL 值代表遺漏的未知數(shù)據(jù)。

默認(rèn)地，表的列可以存放 NULL 值。

本章講解 IS NULL 和 IS NOT NULL 操作符。

如果表中的某個(gè)列是可選的，那么我們可以在不向該列添加值的情況下插入新記錄或更新已有的記錄。這意味著該字段將以 NULL 值保存。

NULL 值的處理方式與其他值不同。

NULL 用作未知的或不適用的值的占位符。

注釋：無法比較 NULL 和 0；它們是不等價(jià)的。

無法使用比較運(yùn)算符來測(cè)試 NULL 值，比如 =、< 或 <>。

我們必須使用 IS NULL 和 IS NOT NULL 操作符。

SQL IS NULL

SQL IS NOT NULL

32、SQL NULL函數(shù)

33、SQL通用數(shù)據(jù)類型

character(n) 字符/字符串.固定長(zhǎng)度n.

varchar(n)或 character varying(n) 字符、字符串?？勺冮L(zhǎng)度。最大長(zhǎng)度n。

binary(n) 二進(jìn)制串。固定長(zhǎng)度n。

boolean 存儲(chǔ)TRUE或FALSE值。

varbinary(n)或 binary varying(n) 二進(jìn)制串?？勺冮L(zhǎng)度。最大長(zhǎng)度n。

integer(p) 整數(shù)（沒有小數(shù)點(diǎn)）。精度p。

smallint 整數(shù)值（沒有小數(shù)點(diǎn)）。精度5。

integer 整數(shù)值（沒有小數(shù)點(diǎn)）。精度10。

bigint 整數(shù)值（沒有小數(shù)點(diǎn)）。精度19。

decimal(p,s) 精確數(shù)值，精度p,小數(shù)點(diǎn)后位數(shù)s。列如：decimal（5,2）是小數(shù)點(diǎn)前有3位，小數(shù)點(diǎn)后有2位數(shù)的數(shù)字。

numeric(p,s)精確值，精度p，小數(shù)點(diǎn)后位數(shù)s。(與decimal相同）

float(p) 近似數(shù)值，尾數(shù)精度p。一個(gè)采用以10為基數(shù)的指數(shù)計(jì)數(shù)法的浮點(diǎn)數(shù)。該類型的size參數(shù)由一個(gè)指定最小精度的單一數(shù)字組成。

REAL 近似數(shù)值，尾數(shù)精度 7。

FLOAT 近似數(shù)值，尾數(shù)精度 16。

DOUBLE PRECISION 近似數(shù)值，尾數(shù)精度 16。

DATE 存儲(chǔ)年、月、日的值。

TIME 存儲(chǔ)小時(shí)、分、秒的值。

TIMESTAMP 存儲(chǔ)年、月、日、小時(shí)、分、秒的值。

INTERVAL 由一些整數(shù)字段組成，代表一段時(shí)間，取決于區(qū)間的類型。

ARRAY 元素的固定長(zhǎng)度的有序集合

MULTISET 元素的可變長(zhǎng)度的無序集合

XML 存儲(chǔ) XML 數(shù)據(jù)

34、SQL DB數(shù)據(jù)類型

Microsoft Access 數(shù)據(jù)類型

MySQL 數(shù)據(jù)類型

在 MySQL 中，有三種主要的類型：Text（文本）、Number（數(shù)字）和 Date/Time（日期/時(shí)間）類型。

SQL Server 數(shù)據(jù)類型

Number 類型：