二、个人总结的菜鸟教程|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 类型：