SQL 简介

SQL 指结构化查询语言，全称是 Structured Query Language。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

SELECT

1 2	SELECT column1, column2, ... FROM table_name;

去重 DISTINCT

1 2	SELECT DISTINCT column1, column2, ... FROM table_name;

SQL SELECT TOP

SELECT TOP 子句用于规定要返回的记录的数目。

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

SQL Server语法

1 2	SELECT TOP number\|percent column_name(s) FROM table_name;

MySQL语法

1
2
3

SELECT column_name(s)
FROM table_name
LIMIT number;

Oracle 语法

1
2
3

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

SELECT INTO

通过 SQL，您可以从一个表复制信息到另一个表。

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

MySQL 数据库不支持 SELECT … INTO 语句，但支持 INSERT INTO … SELECT 。

当然你可以使用以下语句来拷贝表结构及数据：

1
2
3

CREATE TABLE 新表
AS
SELECT * FROM 旧表

INSERT INTO SELECT

通过 SQL，您可以从一个表复制信息到另一个表。

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

可以从一个表中复制所有的列插入到另一个已存在的表中

1 2	INSERT INTO table2 SELECT * FROM table1;

可以只复制指定的列插入到另一个已存在的表中

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

WHERE

1
2
3

SELECT column1, column2, ...
FROM table_name
WHERE condition;

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

ORDER BY

1
2
3

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

INSERT

1 2	INSERT INTO table_name VALUES (value1,value2,value3,...);

UPDATE

1
2
3

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE

1 2	DELETE FROM table_name WHERE condition;

可以在不删除表的情况下，删除表中所有的行。这意味着表结构、属性、索引将保持不变：

1	DELETE FROM table_name;

LIKE

1
2
3

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

通配符

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

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
^charlist 或 [!charlist]

IN

IN 操作符允许您在 WHERE 子句中规定多个值。

1
2
3

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

BETWEEN

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

1
2
3

SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

别名

列的 SQL 别名语法

1 2	SELECT column_name AS alias_name FROM table_name;

表的 SQL 别名语法

1 2	SELECT column_name(s) FROM table_name AS alias_name;

SQL 连接(JOIN)

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）

1
2
3

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;

字段	说明
INNER JOIN	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表返回所有的行
FULL JOIN	只要其中一个表中存在匹配，则返回行

INNER JOIN

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

INNER JOIN 与 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;

LEFT JOIN

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

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

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name

RIGHT JOIN

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

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

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

FULL OUTER JOIN

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

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。MySQL中不支持 FULL OUTER JOIN

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

UNION

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

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同

1
2
3

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

1
2
3

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

CREATE

CREATE DATABASE

1	CREATE DATABASE dbname;

CREATE TABLE

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

CREATE INDEX

CREATE INDEX 语句用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

SQL CREATE INDEX 语法

-- 在表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX 语法

-- 在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

DROP

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

DROP INDEX 语句

DROP INDEX 语句用于删除表中的索引。

-- SQL Server
DROP INDEX table_name.index_name

-- DB2/Oracle 
DROP INDEX index_name

-- MySQL
ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句

1 2	-- DROP TABLE 语句用于删除表。 DROP TABLE table_name

DROP DATABASE 语句

1 2	-- DROP DATABASE 语句用于删除数据库。 DROP DATABASE database_name

TRUNCATE TABLE 语句

如果我们仅仅需要删除表内的数据，但并不删除表本身，请使用 TRUNCATE TABLE 语句：

1	TRUNCATE TABLE table_name

ALTER

ALTER TABLE 语句

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

1
2
3

-- 在表中添加列
ALTER TABLE table_name
ADD column_name datatype

1
2
3

-- 删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name

-- 改变表中列的数据类型

-- SQL Server：
ALTER TABLE table_name
ALTER COLUMN column_name datatype

-- My SQL / Oracle：
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Constraints

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

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

约束	说明
NOT NULL	指示某列不能存储 NULL 值。
UNIQUE	保证某列的每行必须有唯一的值。
PRIMARY KEY	NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY	保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK	保证列中的值符合指定的条件。
DEFAULT	规定没有给列赋值时的默认值。

NOT NULL

NOT NULL 约束强制列不接受 NULL 值。

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

在一个已创建的表的 “Age” 字段中添加 NOT NULL 约束

1 2	ALTER TABLE Persons MODIFY Age int NOT NULL;

在一个已创建的表的 “Age” 字段中删除 NOT NULL 约束

1 2	ALTER TABLE Persons MODIFY Age int NULL;

UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

CREATE TABLE 时的 SQL UNIQUE 约束

-- MySQL 
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

-- SQL Server / Oracle：

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

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

-- MySQL / SQL Server / Oracle：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

ALTER TABLE 时的 SQL UNIQUE 约束

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

-- MySQL / SQL Server / Oracle：

ALTER TABLE Persons
ADD UNIQUE (P_Id)

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

-- MySQL / SQL Server / Oracle:

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

撤销 UNIQUE 约束

-- MySQL 

ALTER TABLE Persons
DROP INDEX uc_PersonID

-- SQL Server / Oracle：

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

PRIMARY KEY

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

CREATE TABLE 时的 SQL PRIMARY KEY 约束

-- MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

-- SQL Server / Oracle：

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

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

-- MySQL / SQL Server / Oracle:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

ALTER TABLE 时的 SQL PRIMARY KEY 约束

-- MySQL / SQL Server / Oracle：

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

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

-- MySQL / SQL Server / Oracle:

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

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

撤销 PRIMARY KEY 约束

-- MySQL：

ALTER TABLE Persons
DROP PRIMARY KEY

-- SQL Server / Oracle：

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

FOREIGN KEY

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

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

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

CREATE TABLE 时的 SQL FOREIGN KEY 约束

-- MySQL 

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

-- SQL Server / Oracle：

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

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

-- MySQL / SQL Server / Oracle: 

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

ALTER TABLE 时的 SQL FOREIGN KEY 约束

-- SQL / SQL Server / Oracle:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

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

-- MySQL / SQL Server / Oracle:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

撤销 FOREIGN KEY 约束

-- MySQL 

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

-- SQL Server / Oracle:

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

CHECK

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

CREATE TABLE 时的 SQL CHECK 约束

-- MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

-- SQL Server / Oracle:

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

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

-- MySQL / SQL Server / Oracle:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

ALTER TABLE 时的 SQL CHECK 约束

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

-- MySQL / SQL Server / Oracle:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

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

-- MySQL / SQL Server / Oracle:

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

撤销 CHECK 约束

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

-- SQL Server / Oracle:

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

-- MySQL：

ALTER TABLE Persons
DROP CHECK chk_Person

DEFAULT

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

CREATE TABLE 时的 SQL DEFAULT 约束

-- My SQL / SQL Server / Oracle:

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)

ALTER TABLE 时的 SQL DEFAULT 约束

-- MySQL：

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

-- SQL Server:

ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City

-- Oracle：

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

撤销 DEFAULT 约束

-- MySQL：

ALTER TABLE Persons
ALTER City DROP DEFAULT

-- SQL Server / Oracle:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

AUTO INCREMENT

Auto-increment 会在新记录插入表中时生成一个唯一的数字。通常希望在每次插入新记录时，自动地创建主键字段的值。

MySQL

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

1	ALTER TABLE Persons AUTO_INCREMENT=100

SQL Server

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。要规定 “ID” 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)

Oracle

必须通过 sequence 对象（该对象生成数字序列）创建 auto-increment 字段。

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

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

要在 “Persons” 表中插入新记录，我们必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）：

1 2	INSERT INTO Persons (ID,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen')

Views

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

CREATE VIEW

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

撤销视图

1	DROP VIEW view_name

日期

MySQL

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

SQL Server

函数	描述
GETDATE()	返回当前的日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间