二、個人總結的菜鳥教程|sql教程的知識點（SQL高級教程）

二、SQL高級教程

1、SQL　SELECT TOP

SELECT TOP 子句用于規定要返回的記錄的數目。

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

注釋：并非所有的數據庫系統都支持 SELECT TOP 子句。

sql server|ms access語法

select top number|percent column_name from table_name;

mysql|oracle語法(與top等價)

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個或多個字符

_ 一個字符

[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 運算符 (或 RLIKE 和 NOT RLIKE) 來操作正則表達式。

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'

在某些數據庫中，BETWEEN 選取介于兩個值之間但不包括兩個測試值的字段。
在某些數據庫中，BETWEEN 選取介于兩個值之間且包括兩個測試值的字段。
在某些數據庫中，BETWEEN 選取介于兩個值之間且包括第一個測試值但不包括最后一個測試值的字段。(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 = '菜鳥教程';

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

• 在查詢中涉及超過一個表

• 在查詢中使用了函數

• 列名稱很長或者可讀性差

• 需要把兩個列或者多個列結合在一起

7、SQL連接（join）

SQL join 用于把來自兩個或多個表的行結合起來。

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

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

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

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

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

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

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

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

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 關鍵字在表中存在至少一個匹配時返回行。如果 "Websites" 表中的行在 "access_log" 中沒有匹配，則不會列出這些行。(多個表都要有）

9、SQL LEFT JOIN

LEFT JOIN 關鍵字從左表（table1）返回所有的行，即使右表（table2）中沒有匹配。如果右表中沒有匹配，則結果為 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

注釋：在某些數據庫中，LEFT JOIN 稱為 LEFT OUTER JOIN。

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

10、SQL Right JOIN

RIGHT JOIN 關鍵字從右表（table2）返回所有的行，即使左表（table1）中沒有匹配。如果左表中沒有匹配，則結果為 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;

在某些數據庫中left join 稱為 left outer join

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

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

11、SQL FULL JOIN

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

FULL OUTER JOIN 關鍵字結合了 LEFT JOIN 和 RIGHT JOIN 的結果。

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 關鍵字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中沒有匹配或者 "access_log" 表中的行在 "Websites" 表中沒有匹配，也會列出這些行。

12、SQL UNION

SQL UNION 操作符合并兩個或多個 SELECT 語句的結果。

請注意，UNION 內部的每個 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的數據類型。同時，每個 SELECT 語句中的列的順序必須相同。

select column_name(s) from table1

union

select column_name(s) from table2

默認地，UNION 操作符選取不同的值。如果允許重復的值，請使用 UNION ALL。

select column_name(s) from table1

union all

select column_name(s) from table2

UNION 結果集中的列名總是等于 UNION 中第一個 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，您可以從一個表復制信息到另一個表。

SELECT INTO 語句從一個表復制數據，然后把數據插入到另一個新表中。

MYSQL不支持select into支持insert into select

//復制整個表

select * into new_table[in externaldb] from table

//復制某幾列

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

新表將會使用 SELECT 語句中定義的列名稱和類型進行創建。您可以使用 AS 子句來應用新名稱。

select * into websites_backup from websites 

select name,url into websites_backup from webties;

只復制中國的網站插入到新表中：

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

復制多個表中的數據插入到新表中：

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 語句可用于通過另一種模式創建一個新的空表。只需要添加促使查詢沒有數據返回的 WHERE 子句即可：

select * into websites_backup from websites where 1=0;

14、SQL INSERT INTO SELECT

通過 SQL，您可以從一個表復制信息到另一個表。

INSERT INTO SELECT 語句從一個表復制數據，然后把數據插入到一個已存在的表中。

INSERT INTO SELECT 語句從一個表復制數據，然后把數據插入到一個已存在的表中。目標表中任何已存在的行都不會受影響。

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 指示某列不能存儲 NULL 值。

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

primary key  NOT NULL 和 UNIQUE 的結合。確保某列（或兩個列多個列的結合）有唯一標識，有助于更容易更快速地找到表中的一個特定的記錄。

foreign key 保證一個表中的數據匹配另一個表中的值的參照完整性。

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

default  規定沒有給列賦值時的默認值.

18、SQL NOT NULL約束

NOT NULL 約束強制列不接受 NULL 值。

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

19、SQL UNIQUE

UNIQUE 約束唯一標識數據庫表中的每條記錄。

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

PRIMARY KEY 約束擁有自動定義的 UNIQUE 約束。

請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。

如需命名 UNIQUE 約束，并定義多個列的 UNIQUE 約束，請使用下面的 SQL 語法：

MySQL刪除unique約束

SQL Server / Oracle / MS Access：

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

20、SQL PRIMARY KEY

PRIMARY KEY 約束唯一標識數據庫表中的每條記錄。

主鍵必須包含唯一的值。

主鍵列不能包含 NULL 值。

每個表都應該有一個主鍵，并且每個表只能有一個主鍵。

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 約束用于限制列中的值的范圍。

如果對單個列定義 CHECK 約束，那么該列只允許特定的值。

如果對一個表定義 CHECK 約束，那么此約束會基于行中其他列的值在特定的列中對值進行限制。

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 約束用于向列中插入默認值。

如果沒有規定其他的值，那么會將默認值添加到所有的新記錄。

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 語句用于在表中創建索引。

在不讀取整個表的情況下，索引使數據庫應用程序可以更快地查找數據。

您可以在表中創建索引，以便更加快速高效地查詢數據。

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

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

create index index_name on table_name(column_name)

create index index_name on persons(name)

在表上創建一個唯一的索引。不允許使用重復的值：唯一的索引意味著兩個行不能擁有相同的索引值。Creates a unique index 

create unique index index_name on table_name(column_name)

創建多列索引

create index index_name on table_name(column_name)

26、SQL DROP

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

drop index index_name on table_name

刪除表

drop table table_name

刪除數據庫

drop database database_name

如果我們僅僅需要刪除表內的數據，但并不刪除表本身，那么我們該如何做呢？

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 會在新記錄插入表中時生成一個唯一的數字。

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

要在 "Persons" 表中插入新記錄，我們不必為 "ID" 列規定值（會自動添加一個唯一的值）：

oracle的自增

create sequence seq_person

minvalue 1

start with 1

increment by 1

cache 10

29、SQL視圖

SQL視圖創建

SQL CREATE VIEW

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

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

視圖包含行和列，就像一個真實的表。視圖中的字段就是來自一個或多個數據庫中的真實的表中的字段。

您可以向視圖添加 SQL 函數、WHERE 以及 JOIN 語句，也可以呈現數據，就像這些數據來自于某個單一的表一樣。

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日期

當我們處理日期時，最難的任務恐怕是確保所插入的日期的格式，與數據庫中日期列的格式相匹配。

只要您的數據包含的只是日期部分，運行查詢就不會出問題。但是，如果涉及時間部分，情況就有點復雜了。

在討論日期查詢的復雜性之前，我們先來看看最重要的內建日期處理函數。

now() 返回當前的日期和時間

curdate() 返回當前日期

31、SQL NULL值

NULL 值代表遺漏的未知數據。

默認地，表的列可以存放 NULL 值。

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

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

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

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

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

無法使用比較運算符來測試 NULL 值，比如 =、< 或 <>。

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

SQL IS NULL

SQL IS NOT NULL

32、SQL NULL函數

33、SQL通用數據類型

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

varchar(n)或 character varying(n) 字符、字符串。可變長度。最大長度n。

binary(n) 二進制串。固定長度n。

boolean 存儲TRUE或FALSE值。

varbinary(n)或 binary varying(n) 二進制串。可變長度。最大長度n。

integer(p) 整數（沒有小數點）。精度p。

smallint 整數值（沒有小數點）。精度5。

integer 整數值（沒有小數點）。精度10。

bigint 整數值（沒有小數點）。精度19。

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

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

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

REAL 近似數值，尾數精度 7。

FLOAT 近似數值，尾數精度 16。

DOUBLE PRECISION 近似數值，尾數精度 16。

DATE 存儲年、月、日的值。

TIME 存儲小時、分、秒的值。

TIMESTAMP 存儲年、月、日、小時、分、秒的值。

INTERVAL 由一些整數字段組成，代表一段時間，取決于區間的類型。

ARRAY 元素的固定長度的有序集合

MULTISET 元素的可變長度的無序集合

XML 存儲 XML 數據

34、SQL DB數據類型

Microsoft Access 數據類型

MySQL 數據類型

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

SQL Server 數據類型

Number 類型：