1. 前言

日常工作中会遇到更复杂的业务，单纯的SQL基础已经满足不了业务的实现，这时就需要祭出更高级的写法。

2. select top

用于规定要返回的记录的数目,对于拥有很多记录的大型表来说，是非常有用的。

注意不是所有数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

• SQL Server语法

SELECT TOP number|percent column_names FROM table_name;

• MySQL 语法

SELECT column_names FROM table_name LIMIT number;

• Oracle 语法

SELECT column_names FROM table_name WHERE ROWNUM <= number;

oracle 的需注意当rownum和order by一起使用，排序问题

3. like

用于在 WHERE 子句中搜索列中的指定模式。

SELECT column_names FROM table_name WHERE column_name LIKE pattern;

"%" 可用于定义通配符（模式中缺少的字母）。

如获取counter表中

// 以"/doc/file/"开头的数据
select id,url,num from counter where url like  '/doc/file/%'

// 以"html"结尾的数据
select id,url,num from counter where url like  '%html'

// 包含"file"的数据
select id,url,num from counter where url like  '%file%'

使用 NOT 关键字 可获取相反结构

如获取counter表不包含"file"的数据

select id,url,num from counter where url not like  '%file%'

4. 通配符

用于替代字符串中的任何其他字符。

在 SQL 中，通配符与 SQL LIKE 操作符一起使用，用于搜索表中的数据。

在 SQL 中，可使用以下通配符：

• % 替代 0 个或多个字符
• _ 替代一个字符
• [charlist] 字符列中的任何单一字符
• [!charlist]或^charlist 不在字符列中的任何单一字符

使用 _ 通配

如符获取counter表url中第一个字符后是doc的数据:

select * from counter where url like '_doc'

使用 [charlist]

a.如 通配符获取counter表url中第一个以是a、b、/开头的数据:

select * from counter where url like '[ab/]%'

上面语句在MySQL上是查不到数据，因为MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

上面列子需要这样才能在MySQL查询出

select * from counter where url REGEXP '^[ab/]';

b.选取 name 以 a 到 z 字母开头的页面名称

select * from counter where url REGEXP '^[a-z]';

b.选取 name 不以 a 到 z 字母开头的页面名称

select * from counter where url REGEXP '^[^a-z]';

5. in

IN 操作符允许在 WHERE 子句中包含多个值。

SELECT column_names FROM table_name WHERE column_name IN (value1,value2,...);

6. between

用于选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

SELECT column_names FROM table_name WHERE column_name BETWEEN value1 AND value2;

7. 别名

通过使用 SQL，可以为表名称或列名称指定别名。

// 列别名
SELECT column_name AS alias_name FROM table_name;

// 表别名
SELECT column_names FROM table_name AS alias_name;

在SQL语句中，我们还可把d多个列（url、num）结合在一起，并创建一个名为 "url_num" 的别名,如：

select id, name, concat(url,'&',num) as url_num from counter;

8. join

SQL join 用于把来自两个或多个表的行结合起来。

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

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

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

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

8.1. inner join

如果表中有至少一个匹配，则返回行,INNER JOIN 与 JOIN 是相同的。

SELECT column_names FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

SELECT column_names FROM table1 JOIN table2 ON table1.column_name=table2.column_name;

8.2. left join

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

SELECT column_names FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

SELECT column_names FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;

8.3. right join

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

SELECT column_names FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

SELECT column_names FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name;

8.4. full join

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SELECT column_names FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

9. union

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 内部的每个 SELECT 语句必须列的个数、数据类型和顺序必须相同。

SQL UNION 语法

SELECT column_names FROM table1 UNION SELECT column_names FROM table2;

如果允许获取重复的值，可使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_names FROM table1 UNION ALL SELECT column_names FROM table2;

UNION 结果集中的列名取 UNION 中第一个 SELECT 语句中的列名。

10. select into

用于创建表的备份复件。将一个表中获取的数据，插入另一个表中。

SQL SELECT INTO 语法

SELECT column_names INTO new_table FROM old_table;

注意： MySQL 数据库不支持 SELECT ... INTO 语句，可使用

CREATE TABLE new_table AS SELECT * FROM old_table

11. insert into select

用于创建表的备份复件。将一个表中获取的数据，插入另一个表中（MySQL支持此方式）。

INSERT INTO new_table SELECT * FROM old_table;

12. create database

用于创建数据库。

CREATE DATABASE dbname;

13. create table

用于创建数据库中的表。

CREATE TABLE table_name
(
column_name1 data_type(size),
....
);

column_name 表中列的名称,data_type 数据类型（例如 varchar、integer、decimal、date 等等）,size 列的最大长度。

14. 约束

SQL 约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

SQL CREATE TABLE + CONSTRAINT 语法

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
....
);

14.1. not null 约束

在默认的情况下，表的列接受 NULL 值，NOT NULL 约束强制列不接受 NULL 值。也就是如果不向字段添加值，就无法插入新记录或者更新记录。

如下面SQL强制 id,name不接受NULL值

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    price int
);

添加 NOT NULL 约束

在上面创建的fruits表里给price字段添加 NOT NULL 约束：

ALTER TABLE fruits MODIFY price int NOT NULL;

删除 NOT NULL 约束

ALTER TABLE fruits MODIFY price int NULL;

14.2. unique 约束

UNIQUE 约束唯一标识数据库表中的每条记录,为列或列集合提供了唯一性的保证,每个表可以有多个 UNIQUE 约束。

CREATE TABLE 时的 SQL UNIQUE 约束

下面的 SQL 在 "fruits" 表创建时在 "name" 列上创建 UNIQUE 约束：

MySQL：

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    price int,
    UNIQUE (name)
);

SQL Server / Oracle / MS Access：

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) NOT NULL UNIQUE,
    price int
);

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    price int,
    CONSTRAINT idx_id_name UNIQUE (id,name)
);

ALTER TABLE 时的 SQL UNIQUE 约束

当表已被创建时，如需在 "name" 列创建 UNIQUE 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE fruits ADD UNIQUE (name);

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE fruits ADD CONSTRAINT idx_id_name UNIQUE (id,name);

撤销 UNIQUE 约束

如需撤销 UNIQUE 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE fruits DROP INDEX idx_id_name

SQL Server / Oracle / MS Access：

ALTER TABLE fruits DROP CONSTRAINT idx_id_name

14.3. primary key 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录，主键必须包含唯一的值，主键列不能包含 NULL 值，每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE 时的 SQL PRIMARY KEY 约束

下面的 SQL 在 "fruits" 表创建时在 "id" 列上创建 PRIMARY KEY 约束：

MySQL：

CREATE TABLE fruits
(
    id int NOT NULL,
    name varchar(255) NOT NULL,
    price int,
    PRIMARY KEY (id)
)

SQL Server / Oracle / MS Access：

CREATE TABLE fruits
(
    id int NOT NULL PRIMARY KEY,
    name varchar(255) NOT NULL,
    price int
)

如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE fruits
(
    id int NOT NULL PRIMARY KEY,
    name varchar(255) NOT NULL,
    price int,
    CONSTRAINT idx_id_name PRIMARY KEY (id,name)
)

ALTER TABLE 时的 SQL PRIMARY KEY 约束

当表已被创建时，如需在 "id" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE fruits ADD PRIMARY KEY (id)

如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE fruits ADD CONSTRAINT idx_id_name PRIMARY KEY (id,name)

注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

撤销 PRIMARY KEY 约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE fruits DROP PRIMARY KEY

SQL Server / Oracle / MS Access：

ALTER TABLE fruits DROP CONSTRAINT idx_id_name

14.4. foreign key 约束

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

FOREIGN KEY 约束可用于预防破坏表之间连接的行为，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CREATE TABLE 时的 SQL FOREIGN KEY 约束

下面的 SQL 在 "stores" 表创建时在 "fruits_id" 列上创建 FOREIGN KEY 约束(fruits表id列是唯一键)：

MySQL：

CREATE TABLE stores
(
    id int NOT NULL,
    name int NOT NULL,
    fruits_id int,
    num int,
    PRIMARY KEY (id),
    FOREIGN KEY (fruits_id) REFERENCES fruits(id)
)

SQL Server / Oracle / MS Access：

CREATE TABLE stores
(
    id int NOT NULL PRIMARY KEY,
    name int NOT NULL,
    fruits_id int FOREIGN KEY REFERENCES fruits(id),
    num int
)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE stores
(
    id int NOT NULL PRIMARY KEY,
    name int NOT NULL,
    fruits_id int ,
    num int,
    PRIMARY KEY (O_Id),
    CONSTRAINT fk_fruits_stores FOREIGN KEY (fruits_id)
    REFERENCES fruits(id)
)

ALTER TABLE 时的 SQL FOREIGN KEY 约束

当 "stores" 表已被创建时，如需在 "fruits_id" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE stores
ADD FOREIGN KEY (fruits_id)
REFERENCES fruits(id)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE stores
ADD CONSTRAINT fk_fruits_stores
FOREIGN KEY (fruits_id)
REFERENCES fruits(id)

撤销 FOREIGN KEY 约束

如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE stores DROP FOREIGN KEY fk_fruits_stores

SQL Server / Oracle / MS Access：

ALTER TABLE stores DROP CONSTRAINT fk_fruits_stores

14.5. check 约束

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

CREATE TABLE 时的 SQL CHECK 约束

下面的 SQL 在 "fruits" 表创建时在 "price" 列上创建 CHECK 约束。CHECK 约束规定 "price" 列必须只包含大于 10 的整数。

MySQL：

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    price int,
    CHECK (price>10)
);

注意：MySQL只是可以使用check约束,但不会强制的遵循check约束!

如要真实约束,且设置CHECK约束的字段范围小，并且比较容易列举全部的值， 就可以考虑将该字段的类型设置为枚举类型 enum()。

如表中的sex字段:

CREATE TABLE student (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    age tinyint(4) NOT NULL DEFAULT '18',
    phone char(12) DEFAULT NULL,
    sex enum('男','女') NOT NULL DEFAULT '男',
    PRIMARY KEY (`id`)
)

SQL Server / Oracle / MS Access：

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    price int CHECK (price>10)
);

如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) NOT NULL,
    price int,
    CONSTRAINT chk_fruits CHECK (price>10 AND name='苹果')
);

ALTER TABLE 时的 SQL CHECK 约束

当表已被创建时，如需在 "price" 列创建 CHECK 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE fruits ADD CHECK (price>0)

如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE fruits ADD CONSTRAINT chk_fruits CHECK (price>0 AND name='苹果')

撤销 CHECK 约束

如需撤销 CHECK 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE fruits DROP CHECK chk_fruits

SQL Server / Oracle / MS Access：

ALTER TABLE fruits DROP CONSTRAINT chk_fruits

14.6. default 约束

DEFAULT 约束用于向列中插入默认值, 如果没有规定其他的值，那么会将默认值添加到所有的新记录。

下面的 SQL 在 "fruits" 表创建时在 "name" 列上创建 DEFAULT 约束：

My SQL / SQL Server / Oracle / MS Access：

CREATE TABLE fruits (
    id int NOT NULL,
    name varchar(255) DEFAULT 'apple',
    price int
);

ALTER TABLE 时的 SQL DEFAULT 约束

当表已被创建时，如需在 "name" 列创建 DEFAULT 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE fruits
ALTER name SET DEFAULT '苹果'

SQL Server / MS Access：

ALTER TABLE fruits
ADD CONSTRAINT apple DEFAULT '苹果' for name

Oracle：

ALTER TABLE fruits
MODIFY name DEFAULT '苹果'

撤销 DEFAULT 约束

如需撤销 DEFAULT 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE fruits
ALTER name DROP DEFAULT

SQL Server / Oracle / MS Access：

ALTER TABLE fruits
ALTER COLUMN name DROP DEFAULT

15. create index

用于在表中创建索引。

字段使用索引，在查询where这个字段的时候，可不读取整个表的情况下，更快地查找数据。

SQL CREATE INDEX 语法

创建简单索引，允许使用重复的值：

CREATE INDEX index_name ON table_name (column_name)

创建一个唯一的索引。不允许使用重复的值：

CREATE UNIQUE INDEX index_name ON table_name (column_name)

16. drop

使用 DROP 语句，可以轻松地删除索引、表和数据库。

DROP INDEX 语句

用于删除表中的索引。

MySQL上使用DROP INDEX 语句

ALTER TABLE table_name DROP INDEX index_name

DB2/Oracle 上使用 DROP INDEX 语法：

DROP INDEX index_name

SQL Server 上使用 DROP INDEX 语法：

DROP INDEX table_name.index_name

MS Access 上使用 DROP INDEX 语法：

DROP INDEX index_name ON table_name

DROP TABLE 语句

用于删除表。

DROP TABLE table_name

DROP DATABASE 语句

用于删除数据库。

DROP DATABASE database_name

TRUNCATE TABLE 语句

仅删除表内的数据

TRUNCATE TABLE table_name

17. alter table 语句

用于在已有的表中添加、删除或修改列。

SQL ALTER TABLE 语法

在表中添加列:

ALTER TABLE table_name ADD column_name datatype

删除表中的列:

注意:某些数据库系统不允许这种在数据库表中删除列的方式

ALTER TABLE table_name DROP COLUMN column_name

要改变表中列的数据类型，请使用下面的语法：

SQL Server / MS Access：

ALTER TABLE table_name ALTER COLUMN column_name datatype

MySQL / Oracle：

ALTER TABLE table_name MODIFY COLUMN column_name datatype

Oracle 10G 之后版本:

ALTER TABLE table_name MODIFY column_name datatype;

18. auto increment

会在新记录插入表中时生成一个唯一的数字。

在每次插入新记录时，会自动地创建主键字段的值。

用于 MySQL 的语法

CREATE TABLE fruits (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    price int,
    PRIMARY KEY (id)
);

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。 默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

ALTER TABLE fruits AUTO_INCREMENT=1000

用于 SQL Server 的语法

CREATE TABLE fruits (
    id int IDENTITY(1,1) PRIMARY KEY,
    name varchar(255) NOT NULL,
    price int
);

SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。IDENTITY(起始值,递增值)。

用于 Oracle 的语法

在 Oracle 中，会复杂一点，需要先通过 sequence 对象（该对象生成数字序列）创建 auto-increment 字段。

CREATE SEQUENCE 语法：

CREATE SEQUENCE seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

上面的代码创建一个名为 seq 的 sequence 对象，它以 1 起始且以 1 递增。 该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值。

在 "fruits" 表中插入新记录时，我们必须使用 nextval 函数（可从 seq 序列中取回下一个值）：

INSERT INTO fruits (id,name,price) VALUES (seq.nextval,'apple',10)

19. 视图

视图是可视化的表，是基于 SQL 语句的结果集的可视化的表，显示最新的数据。

SQL CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column_names
FROM table_name
WHERE condition

实列

CREATE VIEW view_name AS
SELECT id,name,price
FROM fruits

查询视图

select * from view_name

SQL 更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT column_names
FROM table_name
WHERE condition

SQL 撤销视图

DROP VIEW view_name

20. 日期

21. SQL 通用数据类型

下面表格是 SQL 中通用的数据类型：