SQL SERVER

SQL SERVER
1 SQL INNER JOIN 关键字
1.1 SQL INNER JOIN 关键字
1.1.1 SQL INNER JOIN 语法
2 SQL LEFT JOIN 关键字
2.1 SQL LEFT JOIN 关键字
2.1.1 SQL LEFT JOIN 语法
3 SQL RIGHT JOIN 关键字
3.1 SQL RIGHT JOIN 关键字
3.1.1 SQL RIGHT JOIN 语法
4 SQL FULL OUTER JOIN 关键字
4.1 SQL FULL OUTER JOIN 关键字
4.1.1 SQL FULL OUTER JOIN 语法
5 SQL UNION 操作符
5.1 SQL UNION 操作符
5.1.1 SQL UNION 语法
5.1.2 SQL UNION ALL 语法
6 SQL FULL OUTER JOIN 关键字
6.1 SQL FULL OUTER JOIN 关键字
6.1.1 SQL FULL OUTER JOIN 语法
7 SQL UNION 操作符
7.1 SQL UNION 操作符
7.1.1 SQL UNION 语法
7.1.2 SQL UNION ALL 语法
8 SQL SELECT INTO 语句
8.1 SQL SELECT INTO 语句
8.1.1 SQL SELECT INTO 语法
8.2 SQL SELECT INTO 实例
9 SQL INSERT INTO SELECT 语句
9.1 SQL INSERT INTO SELECT 语句
9.1.1 SQL INSERT INTO SELECT 语法
10 SQL CREATE DATABASE 语句
10.1 SQL CREATE DATABASE 语句
10.1.1 SQL CREATE DATABASE 语法
10.2 SQL CREATE DATABASE 实例
11 SQL CREATE TABLE 语句
11.1 SQL CREATE TABLE 语句
11.1.1 SQL CREATE TABLE 语法
SQL 用于各种数据库的数据类型
11.2 Microsoft Access 数据类型
11.3 MySQL 数据类型
11.4 SQL Server 数据类型
12 SQL 约束（CONSTRAINTS）
12.1 SQL 约束（Constraints）
12.1.1 SQL CREATE TABLE + constraints 语法
12.2 SQL NOT NULL 约束
12.3 SQL UNIQUE 约束
12.3.1 Create table 时的 SQL Unique 约束
12.3.2 ALTER TABLE 时的 SQL UNIQUE 约束
12.3.3 撤销 UNIQUE 约束
12.4 SQL PRIMARY KEY 约束
12.4.1 Create table时的Primarykey约束
12.4.2 Alter table 时的 SQL Primary key 约束
12.4.3 撤销 Primary key约束
13 SQL FOREIGN KEY 约束
13.1 SQL FOREIGN KEY 约束
13.2 Create Table 时的 SQL Foreign Key 约束
13.3 Alter table 时的 SQL Foreign key 约束
13.4 撤销 Foreign Key 约束
14 SQL CHECK 约束
14.1 SQL CHECK 约束
14.2 CREATE TABLE 时的 SQL CHECK 约束
14.3 ALTER TABLE 时的 SQL CHECK 约束
14.4 撤销 CHECK 约束
15 SQL DEFAULT 约束
15.1 SQL DEFAULT 约束
15.2 CREATE TABLE 时的 SQL DEFAULT 约束
15.3 ALTER TABLE 时的 SQL DEFAULT 约束
15.4 撤销 DEFAULT 约束
16 SQL CREATE INDEX 语句
16.1 索引
16.1.1 SQL CREATE INDEX 语法
16.1.2 SQL CREATE UNIQUE INDEX 语法
16.2 CREATE INDEX 实例
17 SQL 撤销索引、撤销表以及撤销数据库
17.1 DROP INDEX 语句
17.1.1 用于 MS Access 的 DROP INDEX 语法：
17.1.2 用于 MS SQL Server 的 DROP INDEX 语法：
17.1.3 用于 DB2/Oracle 的 DROP INDEX 语法：
17.1.4 用于 MySQL 的 DROP INDEX 语法：
17.2 DROP TABLE 语句
17.3 DROP DATABASE 语句
17.4 TRUNCATE TABLE 语句
18 SQL ALTER TABLE 语句
18.1 ALTER TABLE 语句
18.1.1 SQL ALTER TABLE 语法
18.2 SQL ALTER TABLE 实例
18.3 改变数据类型实例
18.4 DROP COLUMN 实例
19 SQL AUTO INCREMENT 字段
19.1 AUTO INCREMENT 字段
19.2 用于 MySQL 的语法
19.3 用于 SQL Server 的语法
19.4 用于 Access 的语法
19.5 用于 Oracle 的语法
20 SQL 视图（VIEWS）
20.1 SQL CREATE VIEW 语句
20.1.1 SQL CREATE VIEW 语法
20.2 SQL CREATE VIEW 实例
20.3 SQL 更新视图
20.3.1 SQL Create Or Replace View 语法
20.3.2 SQL Server
20.4 SQL 撤销视图
20.4.1 SQL DROP VIEW 语法
21 SQL DATE 函数
21.1 SQL 日期（Dates）
21.2 MySQL Date 函数
21.2.1 Now(),Curdate(),Curtime()
21.2.2 Date()
21.2.3 EXTRACT()
21.2.4 DateAdd()
21.2.5 DateSub()
21.2.6 DateDiff()
21.2.7 DateFormat()
21.3 SQL Server Date 函数
21.3.1 GetDate()
21.3.2 DatePart()
21.3.3 DateAdd()
21.3.4 DateDiff()
21.3.5 Convert()
21.4 SQL Date 数据类型
21.5 SQL 日期处理
22 SQL NULL 函数
22.1 SQL ISNULL(),NVL(),IFNULL(),COALESCE()
23 SQL 用于各种数据库的数据类型
23.1 Microsoft Access 数据类型
23.2 MySQL 数据类型
23.3 SQL Server 数据类型
24 SQL 函数
24.1 SQL Aggregate 函数
24.2 SQL Scalar 函数
25 SQL GROUP BY 语句
25.1 GROUP BY 语句
25.1.1 SQL GROUP BY 语法
26 SQL HAVING 子句
26.1 HAVING 子句
26.1.1 SQL HAVING 语法
27 SQL UCASE() 函数
27.1 UCASE() 函数
27.1.1 SQL UCASE() 语法
27.1.2 用于 SQL Server 的语法
28 SQL MID() 函数
28.1 MID() 函数
28.1.1 SQL MID() 语法
29 SQL LEN() 函数
29.1 LEN() 函数
29.1.1 SQL LEN() 语法
30 SQL ROUND() 函数
30.1 ROUND() 函数
30.1.1 SQL ROUND() 语法
31





**

0

1 SQL INNER JOIN 关键字

1.1 SQL INNER JOIN 关键字

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

1.1.1 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;
注释：INNER JOIN 与 JOIN 是相同的

2 SQL LEFT JOIN 关键字

2.1 SQL LEFT JOIN 关键字

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

2.1.1 SQL LEFT 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;
注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

3 SQL RIGHT JOIN 关键字

3.1 SQL RIGHT JOIN 关键字

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

3.1.1 SQL RIGHT 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;
注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

4 SQL FULL OUTER JOIN 关键字

4.1 SQL FULL OUTER JOIN 关键字

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

4.1.1 SQL FULL OUTER JOIN 语法

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

5 SQL UNION 操作符

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

5.1 SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

5.1.1 SQL UNION 语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

5.1.2 SQL UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

6 SQL FULL OUTER JOIN 关键字

6.1 SQL FULL OUTER JOIN 关键字

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

6.1.1 SQL FULL OUTER JOIN 语法

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

7 SQL UNION 操作符

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

7.1 SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

7.1.1 SQL UNION 语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用** UNION ALL。**

7.1.2 SQL UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

8 SQL SELECT INTO 语句

通过 SQL，您可以从一个表复制信息到另一个表。（表不存在）
SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

8.1 SQL SELECT INTO 语句

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。
MySQL 数据库不支持 SELECT … INTO 语句，但支持 INSERT INTO … SELECT 。
当然你可以使用以下语句来拷贝表结构及数据：
CREATE TABLE 新表
AS
SELECT * FROM 旧表

8.1.1 SQL SELECT INTO 语法

我们可以复制所有的列插入到新表中：
SELECT *INTO newtable [IN externaldb] FROM table1;
或者只复制希望的列插入到新表中：
SELECT column_name(s) _INTO _newtable [IN externaldb]FROM table1;

	提示：新表将会使用 SELECT 语句中定义的列名称和类型进行创建。您可以使用 AS 子句来应用新名称。

8.2 SQL SELECT INTO 实例

创建 Websites 的备份复件：
SELECT INTO WebsitesBackup2016 FROM Websites;
只复制一些列插入到新表中：
SELECT name,url INTO WebsitesBackup2016 FROM Websites;
只复制中国的网站插入到新表中：
SELECT INTO WebsitesBackup2016
FROM Websites WHERE country=’CN’;
复制多个表中的数据插入到新表中：
SELECT Websites.name,access_log.count,access_log.date
INTO WebsitesBackup2016
FROM Websites LEFT JOIN access_log
ON Websites.id=access_log.site_id;
提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：
SELECT*INTO _newtable _FROM _table1 _WHERE 1=0;

9 SQL INSERT INTO SELECT 语句

通过 SQL，您可以从一个表复制信息到另一个表。(表已存在)
INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

9.1 SQL INSERT INTO SELECT 语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

9.1.1 SQL INSERT INTO SELECT 语法

我们可以从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO table2
SELECT * FROM table1;
或者我们可以只复制希望的列插入到另一个已存在的表中：
INSERT INTO table2(column_name(s))
SELECT column_name(s)_FROM _table1;

10 SQL CREATE DATABASE 语句

10.1 SQL CREATE DATABASE 语句

CREATE DATABASE 语句用于创建数据库。

10.1.1 SQL CREATE DATABASE 语法

CREATE DATABASE dbname;

10.2 SQL CREATE DATABASE 实例

下面的 SQL 语句创建一个名为 “my_db” 的数据库：
CREATE DATABASE my_db;
数据库表可以通过 CREATE TABLE 语句来添加。

11 SQL CREATE TABLE 语句

11.1 SQL CREATE TABLE 语句

CREATE TABLE 语句用于创建数据库中的表。
表由行和列组成，每个表都必须有个表名。

11.1.1 SQL CREATE TABLE 语法

CREATETABLE table_name
(column_name1 data_type(size),column_name2 data_type(size),
column_name3 data_type(size),….);
column_name 参数规定表中列的名称。
data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。
size 参数规定表中列的最大长度。
提示：如需了解 MS Access、MySQL 和 SQL Server 中可用的数据类型，请访问我们完整的 数据类型参考手册。

SQL 用于各种数据库的数据类型

Microsoft Access、MySQL 和 SQL Server 所使用的数据类型和范围。

11.2 Microsoft Access 数据类型

11.3 MySQL 数据类型

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。





Text 类型：

Number 类型：

注意：以上的 size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。
实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。
例如：
1、int的值为10 （指定zerofill）
int（9）显示结果为000000010
int（3）显示结果为010
就是显示的长度不一样而已 都是占用四个字节的空间
Date 类型：

*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

11.4 SQL Server 数据类型

String 类型：

Number 类型：

Date 类型：

其他数据类型：

12 SQL 约束（CONSTRAINTS）

12.1 SQL 约束（Constraints）

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

12.1.1 SQL CREATE TABLE + constraints 语法

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,….);
在 SQL 中，我们有如下约束：
· NOT NULL - 指示某列不能存储 NULL 值。
· UNIQUE - 保证某列的每行必须有唯一的值。
· PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
· FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
· CHECK - 保证列中的值符合指定的条件。
· DEFAULT - 规定没有给列赋值时的默认值。
在下面的章节，我们会详细讲解每一种约束。
在默认的情况下，表的列接受 NULL 值。

12.2 SQL NOT NULL 约束

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
下面的 SQL 强制 “P_Id” 列和 “LastName” 列不接受 NULL 值：
CREATE TABLE Persons
(P_Id int NOTNULL,
LastName varchar(255)NOTNULL,FirstName varchar(255),
Addressvarchar(255),Cityvarchar(255))

12.3 SQL UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

12.3.1 Create table 时的 SQL Unique 约束

下面的 SQL 在 “Persons” 表创建时在 “P_Id” 列上创建 UNIQUE 约束：
MySQL：
CREATE TABLE Persons
(P_Id int NOTNULL,LastName varchar(255) NOTNULL,
FirstName varchar(255),Address varchar(255),
City varchar(255),UNIQUE(P_Id))
SQL Server / Oracle / MS Access：
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 / MS Access：
CREATE TABLE Persons
(P_Id int NOTNULL,LastName varchar(255)NOTNULL,
FirstName varchar(255),Address varchar(255),
City varchar(255),CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

12.3.2 ALTER TABLE 时的 SQL UNIQUE 约束

当表已被创建时，如需在 “P_Id” 列创建 UNIQUE 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons ADD UNIQUE (P_Id)
如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

12.3.3 撤销 UNIQUE 约束

如需撤销 UNIQUE 约束，请使用下面的 SQL：
MySQL：
ALTER TABLE Persons DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access：
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

12.4 SQL PRIMARY KEY 约束

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

12.4.1 Create table时的Primarykey约束

下面的 SQL 在 “Persons” 表创建时在 “P_Id” 列上创建 PRIMARY KEY 约束：
MySQL：
CREATE TABLE Persons
(P_Id int NOTNULL,LastName varchar(255) NOTNULL,
FirstName varchar(255),Address varchar(255), City varchar(255),PRIMARY KEY (P_Id))
SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(P_Id int NOTNULL PRIMARY KEY,
LastName varchar(255) NOTNULL,
FirstName varchar(255),Address varchar(255),City varchar(255))
如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(P_Id int NOTNULL,LastName varchar(255) NOTNULL,
FirstName varchar(255),Address varchar(255),City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY(P_Id,LastName))
注释：在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的。

12.4.2 Alter table 时的 SQL Primary key 约束

当表已被创建时，如需在 “P_Id” 列创建 PRIMARY KEY 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons ADD PRIMARY KEY (P_Id)
如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

12.4.3 撤销 Primary key约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
MySQL：
ALTER TABLE Persons DROP Primary KEY
SQL Server / Oracle / MS Access：
ALTER TABLE Persons DROP constraint pk_PersonID

13 SQL FOREIGN KEY 约束

13.1 SQL FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。
让我们通过一个实例来解释外键。请看下面两个表：
“Persons” 表：

“Orders” 表：

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

13.2 Create Table 时的 SQL Foreign Key 约束

下面的 SQL 在 “Orders” 表创建时在 “P_Id” 列上创建 FOREIGN KEY 约束：
MySQL：
CREATE TABLE Orders
(O_Id int NOTNULL,OrderNo int NOTNULL,P_Id int,PRIMARY
KEY (O_Id),FOREIGN KEY (P_Id) REFERENCES Persons(P_Id))
SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(O_Id int NOT NULL PRIMARY KEY,OrderNo int NOTNULL,P_Id int FOREIGN KEY REFERENCES Persons(P_Id))
如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(O_Id int NOT NULL,OrderNo int NOTNULL,
P_Id int,PRIMARY KEY(O_Id),CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)REFERENCES Persons(P_Id))

13.3 Alter table 时的 SQL Foreign key 约束

当 “Orders” 表已被创建时，如需在 “P_Id” 列创建 FOREIGN KEY 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders ADD FOREIGN KEY(P_Id)
REFERENCES Persons(P_Id)
如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders
FOREIGN KEY(P_Id) REFERENCES Persons(P_Id)

13.4 撤销 Foreign Key 约束

如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：
MySQL：
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access：
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

14 SQL CHECK 约束

14.1 SQL CHECK 约束

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

14.2 CREATE TABLE 时的 SQL CHECK 约束

下面的 SQL 在 “Persons” 表创建时在 “P_Id” 列上创建 CHECK 约束。CHECK 约束规定 “P_Id” 列必须只包含大于 0 的整数。
MySQL：
CREATE TABLE Persons
(P_Id int NOTNULL,
LastName varchar(255) NOTNULL,
FirstName varchar(255),
Address varchar(255),City varchar(255),CHECK(P_Id>0))
SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(P_Id int NOTNULL CHECK(P_Id>0),
LastName varchar(255) NOTNULL,
FirstName varchar(255),Address varchar(255),Cityvarchar(255))
如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(P_Id int NOTNULL,
LastName varchar(255) NOTNULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK(P_Id>0ANDCity=’Sandnes’))

14.3 ALTER TABLE 时的 SQL CHECK 约束

当表已被创建时，如需在 “P_Id” 列创建 CHECK 约束，请使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CHECK (P_Id>0)
如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City=’Sandnes’)

14.4 撤销 CHECK 约束

如需撤销 CHECK 约束，请使用下面的 SQL：
SQL Server / Oracle / MS Access：
ALTER TABLE Persons DROP CONSTRAINT chk_Person
MySQL：
ALTER TABLE Persons DROP CHECK chk_Person

15 SQL DEFAULT 约束

15.1 SQL DEFAULT 约束

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

15.2 CREATE TABLE 时的 SQL DEFAULT 约束

下面的 SQL 在 “Persons” 表创建时在 “City” 列上创建 DEFAULT 约束：
My SQL / SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT ‘Sandnes’
)
通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

15.3 ALTER TABLE 时的 SQL DEFAULT 约束

当表已被创建时，如需在 “City” 列创建 DEFAULT 约束，请使用下面的 SQL：
MySQL：
ALTER TABLE Persons
ALTER City SET DEFAULT ‘SANDNES’
SQL Server / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT ‘SANDNES’ for City
Oracle：
ALTER TABLE Persons
MODIFY City DEFAULT ‘SANDNES’

15.4 撤销 DEFAULT 约束

如需撤销 DEFAULT 约束，请使用下面的 SQL：
MySQL：
ALTER TABLE Persons
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

16 SQL CREATE INDEX 语句

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

16.1 索引

您可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

16.1.1 SQL CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name ON table_name (column_name)

16.1.2 SQL CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_nameON table_name (column_name)
注释：用于创建索引的语法在不同的数据库中不一样。因此，检查您的数据库中创建索引的语法。

16.2 CREATE INDEX 实例

下面的 SQL 语句在 “Persons” 表的 “LastName” 列上创建一个名为 “PIndex” 的索引：
CREATE INDEX Pindex ON Persons (LastName)
如果您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX Pindex ON Persons (LastName, FirstName)

17 SQL 撤销索引、撤销表以及撤销数据库

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

17.1 DROP INDEX 语句

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

17.1.1 用于 MS Access 的 DROP INDEX 语法：

DROP INDEX index_name ON table_name

17.1.2 用于 MS SQL Server 的 DROP INDEX 语法：

DROP INDEX table_name.index_name

17.1.3 用于 DB2/Oracle 的 DROP INDEX 语法：

DROP INDEX index_name

17.1.4 用于 MySQL 的 DROP INDEX 语法：

ALTER TABLE table_name DROP INDEX index_name

17.2 DROP TABLE 语句

DROP TABLE 语句用于删除表。
DROP TABLE table_name

17.3 DROP DATABASE 语句

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

17.4 TRUNCATE TABLE 语句

如果我们仅仅需要删除表内的数据，但并不删除表本身，那么我们该如何做呢？
请使用 TRUNCATE TABLE 语句：
TRUNCATE TABLE table_name

18 SQL ALTER TABLE 语句

18.1 ALTER TABLE 语句

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

18.1.1 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
My SQL / Oracle：
ALTER TABLE table_name MODIFY COLUMN column_name datatype
Oracle 10G 之后版本:
ALTER TABLE table_name
MODIFY column_name datatype;

18.2 SQL ALTER TABLE 实例

请看 “Persons” 表：

现在，我们想在 “Persons” 表中添加一个名为 “DateOfBirth” 的列。
我们使用下面的 SQL 语句：
Alter table Persons add DateOfBirth date
请注意，新列 “DateOfBirth” 的类型是 date，可以存放日期。数据类型规定列中可以存放的数据的类型。

现在，”Persons” 表将如下所示：

18.3 改变数据类型实例

现在，我们想要改变 “Persons” 表中 “DateOfBirth” 列的数据类型。
我们使用下面的 SQL 语句：
ALTER TABLE Persons ALTER COLUMN DateOfBirth year
请注意，现在 “DateOfBirth” 列的类型是 year，可以存放 2 位或 4 位格式的年份。

18.4 DROP COLUMN 实例

接下来，我们想要删除 “Person” 表中的 “DateOfBirth” 列。
我们使用下面的 SQL 语句：
ALTER TABLE Persons DROP COLUMN DateOfBirth
现在，”Persons” 表将如下所示：

19 SQL AUTO INCREMENT 字段

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

19.1 AUTO INCREMENT 字段

我们通常希望在每次插入新记录时，自动地创建主键字段的值。我们可以在表中创建一个 auto-increment 字段。

19.2 用于 MySQL 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：
Create Table Persons
(ID int not null auto_increment,
LastName varchar(255) not null,
FirstName varchar(255) not null,
Address varchar(255) not null, City varchar(255),Primary key (ID))
MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。
默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。
要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：
ALTER TABLE Persons AUTO_INCREMENT=100
要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：
Insert into Persons(FirstName,LastName) values(‘Lars‘,‘Monsen‘)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。

19.3 用于 SQL Server 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：
Create table Persons
(ID int Identity(1,1) Primary key,
LastName varchar(255) not null,
FirstName varchar(255) not null,
Address varchar(255) not null, City varchar(255))
MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。
在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。
提示：要规定 “ID” 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。—(start,interval)
要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：
Insert into Persons(FirstName,LastName) values(‘Lasers‘,‘Monsen‘)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。

19.4 用于 Access 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：
Create table Persons
(ID integer primary key autoincrement,àAutoIncrement(1,1)
LastName varchar(255) not null,
FirstName varchar(255) not null,
Address varchar(255) not null, City varchar(255))
MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment 任务。
默认地，AUTOINCREMENT 的开始值是 1，每条新记录递增 1。
提示：要规定 “ID” 列以 10 起始且递增 5，请把 autoincrement 改为 AUTOINCREMENT(10,5)。
要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：
Insert into Persons(FirstName,LastName) values(‘Lasers‘,‘Monsen‘)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。

19.5 用于 Oracle 的语法

在 Oracle 中，代码稍微复杂一点。
您必须通过 sequence 对象（该对象生成数字序列）创建 auto-increment 字段。
请使用下面的 CREATE SEQUENCE 语法：
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 序列中取回下一个值）：
Insert into Persons
(ID,LastName,FirstName) Values(seq_person.nextval,‘Lasers‘,‘Monsen‘)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋值为来自 seq_person 序列的下一个数字。”FirstName”列 会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”

20 SQL 视图（VIEWS）

视图是可视化的表。本章讲解如何创建、更新和删除视图。

20.1 SQL CREATE VIEW 语句

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

20.1.1 SQL CREATE VIEW 语法

Create view view_name
as select column_name(s) from table_name where condition
注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

20.2 SQL CREATE VIEW 实例

样本数据库 Northwind 拥有一些被默认安装的视图。视图 “Current Product List” 会从 “Products” 表列出所有正在使用的产品（未停产的产品）。这个视图使用下面的 SQL 创建：
Create view [Current product List] as select ProductID,ProductName from Products where Discontinued=No
我们可以像这样查询上面这个视图：
SELECT FROM [Current Product List]
Northwind 样本数据库的另一个视图会选取 “Products” 表中所有单位价格高于平均单位价格的产品：
Create view [Products Above Averge Price]
AS select ProductName,UnitPrice
from Products where UnitPrice>( SELECT AVG(UnitPrice) FROM Products)
我们可以像这样查询上面这个视图：
SELECT FROM [Products Above Average Price]
Northwind 样本数据库的另一个视图会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 “Product Sales for 1997” 的视图那里选取数据：
Create view [Category Sales For 1997]
AS select Distinct CategoryName,Sum(ProductSales)
AS CategorySaes From [Product Sales For 1997] Group By CategoryName
我们可以像这样查询上面这个视图：
SELECT FROM [Category Sales For 1997]
我们也可以向查询添加条件。现在，我们仅仅需要查看 “Beverages” 类的销售总数：
Select from [Category Sales For 1997]
where CategoryName=‘Beverages‘

20.3 SQL 更新视图

您可以使用下面的语法来更新视图：

20.3.1 SQL Create Or Replace View 语法

Create or replace view view_name as select column_name(s)
from table_name where condition
现在，我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图：
Create view [current product list]
As select ProductID,ProductName,Category
from Products where Discontinued=No

20.3.2 SQL Server

Alter View [schema_name.] view_name[(column[,…n])]
[With [,…]]
AS select_statement
[With Check Option][;]
::=
{
[Encryption]
[Schemabinding]
[View_metadata]
}
· schema_name: 视图所属架构的名称。
· view_name: 要更改的视图。
· column: 将成为指定视图的一部分的一个或多个列的名称（以逗号分隔）。

20.4 SQL 撤销视图

您可以通过 DROP VIEW 命令来删除视图。

20.4.1 SQL DROP VIEW 语法

DROP VIEW view_name

21 SQL DATE 函数

21.1 SQL 日期（Dates）

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。
只要您的数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间部分，情况就有点复杂了。
在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。

21.2 MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

21.2.1Now(),Curdate(),Curtime()

NOW() 返回当前的日期和时间。
Curdate():返回当前的日期
Curtime():返回当前的时间
SELECT NOW(),CURDATE(),CURTIME()

21.2.2 Date()

DATE() 函数提取日期或日期/时间表达式的日期部分。
语法
DATE(date)
date 参数是合法的日期表达式。

21.2.3 EXTRACT()

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
语法
EXTRACT(unit FROM date)
date 参数是合法的日期表达式。unit 参数可以是下列的值：

Extract(year from OrderDate)

21.2.4 DateAdd()

DATE_ADD() 函数向日期添加指定的时间间隔。
语法：
DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：

21.2.5 DateSub()

DATE_SUB() 函数从日期减去指定的时间间隔。
语法：
DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：

21.2.6 DateDiff()

DATEDIFF() 函数返回两个日期之间的天数。
语法：
DATEDIFF(date1,date2)àdate1-date2
date1 和 date2 参数是合法的日期或日期/时间表达式。
注释：只有值的日期部分参与计算。

21.2.7 DateFormat()

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。








可以使用的格式有：

实例：
下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：
DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
DATE_FORMAT(NOW(),’%m-%d-%Y’)
DATE_FORMAT(NOW(),’%d %b %y’)
DATE_FORMAT(NOW(),’%d %b %Y %T:%f’)
结果如下所示：
Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243

21.3 SQL Server Date 函数

下面的表格列出了 SQL Server 中最重要的内建日期函数：

21.3.1 GetDate()

GETDATE() 函数从 SQL Server 返回当前的日期和时间。
GETDATE()
下面是 SELECT 语句：
SELECT GETDATE() AS CurrentDateTime
结果如下所示：

21.3.2 DatePart()

DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
DATEPART(datepart,date)
date 参数是合法的日期表达式。datepart 参数可以是下列的值：

假设我们有如下的 “Orders” 表：

下面是 SELECT 语句：
SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay,
FROM Orders
WHERE OrderId=1
结果如下所示：

21.3.3 DateAdd()

DATEADD() 函数在日期中添加或减去指定的时间间隔。
DATEADD(datepart,number,date)
date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。
datepart 参数可以是下列的值：

假设我们有如下的 “Orders” 表：

现在，我们想要向 “OrderDate” 添加 45 天，这样就可以找到付款日期。
我们使用下面的 SELECT 语句：
SELECT OrderId,DATEADD(day,45,OrderDate) AS OrderPayDate
FROM Orders
结果：

21.3.4 DateDiff()

DATEDIFF() 函数返回两个日期之间的天数。
DATEDIFF(datepart,startdate,enddate)

21.3.5 Convert()

CONVERT() 函数是把日期转换为新数据类型的通用函数。
CONVERT() 函数可以用不同的格式显示日期/时间数据。
CONVERT(data_type(length),expression,style)

下面的表格展示了 datetime 或 smalldatetime 转换为字符数据的 style 值：

实例：
下面的脚本使用 CONVERT() 函数来显示不同的格式。我们将使用 GETDATE() 函数来获得当前的日期/时间：
CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)
结果如下所示：
Nov 04 2011 11:45 PM
11-04-11
11-04-2011
04 Nov 11
04 Nov 2011
04 Nov 2011 11:45:34:243

21.4 SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
· DATE - 格式：YYYY-MM-DD
· DATETIME - 格式：YYYY-MM-DD HH:MM:SS
· TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
· YEAR - 格式：YYYY 或 YY
SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：
· DATE - 格式：YYYY-MM-DD
· DATETIME - 格式：YYYY-MM-DD HH:MM:SS
· SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
· TIMESTAMP - 格式：唯一的数字
注释：当您在数据库中创建一个新表时，需要为列选择数据类型！

21.5 SQL 日期处理

如果不涉及时间部分，那么我们可以轻松地比较两个日期！
假设我们有如下的 “Orders” 表：

现在，我们希望从上表中选取 OrderDate 为 “2008-11-11” 的记录。
我们使用下面的 SELECT 语句：
SELECT FROM Orders WHERE OrderDate=’2008-11-11’
结果集如下所示：

如果我们使用和上面一样的 SELECT 语句：
SELECT FROM Orders WHERE OrderDate=’2008-11-11’
或
SELECT FROM Orders WHERE OrderDate=’2008-11-11 00：00：00’
那么我们将得不到结果！因为表中没有”2008-11-11 00:00:00”日期。如果没有时间部分，默认时间为 00:00:00。
*提示：如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！

22 SQL NULL 函数

22.1 SQL ISNULL(),NVL(),IFNULL(),COALESCE()

请看下面的 “Products” 表：

假如 “UnitsOnOrder” 是可选的，而且可以包含 NULL 值。
我们使用下面的 SELECT 语句：
SELECT ProductName,UnitPrice(UnitsInStock+UnitsOnOrder)
FROM Products
在上面的实例中，如果有 “UnitsOnOrder” 值是 NULL，那么结果是 NULL。
微软的 ISNULL() 函数用于规定如何处理 NULL 值。
NVL()、IFNULL() 和 COALESCE() 函数也可以达到相同的结果。
在这里，我们希望 NULL 值为 0。
下面，如果 “UnitsOnOrder” 是 NULL，则不会影响计算，因为如果值是 NULL 则 ISNULL() 返回 0：

SQL Server / MS Access
SELECT ProductName,UnitPrice(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

Oracle
Oracle 没有 ISNULL() 函数。不过，我们可以使用 NVL() 函数达到相同的结果：
SELECT ProductName,UnitPrice(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products



MySQL
MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。
在 MySQL 中，我们可以使用 IFNULL() 函数，如下所示：
SELECT ProductName,UnitPrice(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
或者我们可以使用 COALESCE() 函数，如下所示：
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
—如果alexa列为null值，则赋予0，否则，取原值
select id,name,url,ifnull(alexa,0)from websites;
select id,name,url,COALESCE(alexa,0) from websites;

23 SQL 用于各种数据库的数据类型

Microsoft Access、MySQL 和 SQL Server 所使用的数据类型和范围。

23.1 Microsoft Access 数据类型

23.2 MySQL 数据类型

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。
Text 类型：

Number 类型：

注意：以上的 size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。
实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。
例如：
1、int的值为10 （指定zerofill）
int（9）显示结果为000000010
int（3）显示结果为010
就是显示的长度不一样而已 都是占用四个字节的空间
Date 类型：

*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

23.3 SQL Server 数据类型

String 类型：

Number 类型：

Date 类型：

其他数据类型：

24 SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。

24.1 SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。
有用的 Aggregate 函数：
· AVG() - 返回平均值
· COUNT() - 返回行数
· FIRST() - 返回第一个记录的值
· LAST() - 返回最后一个记录的值
· MAX() - 返回最大值
· MIN() - 返回最小值
· SUM() - 返回总和

24.2 SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。有用的 Scalar 函数：
· UCASE() - 将某个字段转换为大写
· LCASE() - 将某个字段转换为小写
· MID() - 从某个文本字段提取字符，MySql 中使用
· SubString(字段，1，end) - 从某个文本字段提取字符
· LEN() - 返回某个文本字段的长度
· ROUND() - 对某个数值字段进行指定小数位数的四舍五入
· NOW() - 返回当前的系统日期和时间
· FORMAT() - 格式化某个字段的显示方式

25 SQL GROUP BY 语句

GROUP BY 语句可结合一些聚合函数来使用

25.1 GROUP BY 语句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

25.1.1 SQL GROUP BY 语法

Select column_name,aggregate_function(column_name)
From table_name where column_name operator value Group by columnName

26 SQL HAVING 子句

26.1 HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。

26.1.1 SQL HAVING 语法

Select column_name,aggregate_function(column_name)
From table_name where column_name operator value
Group by column_name Having aggregate_function(column_name)operator value

27 SQL UCASE() 函数

27.1 UCASE() 函数

UCASE() 函数把字段的值转换为大写。

27.1.1 SQL UCASE() 语法

SELECT UCASE/LCASE(column_name) FROM table_name;

27.1.2 用于 SQL Server 的语法

SELECT UPPER/LOWER(column_name) FROM table_name;

28 SQL MID() 函数

28.1 MID() 函数

MID() 函数用于从文本字段中提取字符。

28.1.1 SQL MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name;

Oracle 中没有 MID 函数，有 substr 函数有类似功能:
select substr((“列名”，a,b) from ;
实例：
SELECT substr(name,1,4) AS ShortTitle
FROM Websites;

29 SQL LEN() 函数

29.1 LEN() 函数

LEN() 函数返回文本字段中值的长度。

29.1.1 SQL LEN() 语法

SELECT LEN(column_name) FROM table_name;
MySQL 中函数为 LENGTH():
SELECT LENGTH(column_name) FROM table_name;

30 SQL ROUND() 函数

30.1 ROUND() 函数

ROUND() 函数用于把数值字段舍入为指定的小数位数。

30.1.1 SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM table_name;

31 常用 SQL SERVER 规范集锦

31.1 分类编程技术

31.2常见的字段类型选择

1.字符类型建议采用varchar/nvarchar数据类型2.金额货币建议采用money数据类型3.科学计数建议采用numeric数据类型4.自增长标识建议采用bigint数据类型 (数据量一大，用int类型就装不下，那以后改造就麻烦了)
5.时间类型建议采用为datetime数据类型6.禁止使用text、ntext、image老的数据类型7.禁止使用xml数据类型、varchar(max)、nvarchar(max)

31.3约束与索引

每张表必须有主键
· 每张表必须有主键，用于强制实体完整性
· 单表只能有一个主键（不允许为空及重复数据）
· 尽量使用单字段主键
不允许使用外键
· 外键增加了表结构变更及数据迁移的复杂性
· 外键对插入，更新的性能有影响，需要检查主外键约束
· 数据完整性由程序控制
NULL属性
新加的表，所有字段禁止NULL
（新表为什么不允许**NULL？
允许NULL值，会增加应用程序的复杂性。你必须得增加特定的逻辑代码，以防止出现各种意外的bug
三值逻辑，所有等号（”=”）的查询都必须增加isnull的判断。Null=Null、Null!=Null、not(Null=Null)、not(Null!=Null)都为unknown，不为true）
举例来说明一下：
如果表里面的数据如图所示：

你想来找查找除了name等于aa的所有数据，然后你就不经意间用了SELECT FROM NULLTEST WHERE NAME<>’aa’
结果发现与预期不一样，事实上它只查出了name=bb而没有查找出name=NULL的数据记录
那我们如何查找除了name等于aa的所有数据，只能用ISNULL函数了
SELECT FROM NULLTEST WHERE ISNULL(NAME,1)<>’aa’
但是大家可能不知道ISNULL会引起很严重的性能瓶颈 ,所以很多时候最好是在应用层面限制用户的输入，确保用户输入有效的数据再进行查询。
旧表新加字段，需要允许为NULL（避免全表数据更新 ，长期持锁导致阻塞）**（这个主要是考虑之前表的改造问题）

31.4索引设计准则

· 应该对 WHERE 子句中经常使用的列创建索引
· 应该对经常用于连接表的列创建索引
· 应该对 ORDER BY 子句中经常使用的列创建索引
· 不应该对小型的表（仅使用几个页的表）创建索引，这是因为完全表扫描操作可能比使用索引执行的查询快
· 单表索引数不超过6个
· 不要给选择性低的字段建单列索引
· 充分利用唯一约束
· 索引包含的字段不超过5个（包括include列）

31.5不要给选择性低的字段创建单列索引

· SQL SERVER对索引字段的选择性有要求，如果选择性太低SQL SERVER会放弃使用
· 不适合创建索引的字段：性别、0/1、TRUE/FALSE
· 适合创建索引的字段：ORDERID、UID等

31.6充分利用唯一索引

唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息，当查询分析器通过唯一索引查找到一条记录则会立刻退出，不会继续查找索引
表索引数不超过6个

31.7表索引数不超过6个（这个规则只是携程DBA经过试验之后制定的。。。）

· 索引加快了查询速度，但是却会影响写入性能
· 一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并
· 组合索引的原则是，过滤性越好的字段越靠前
· 索引过多不仅会增加编译时间，也会影响数据库选择最佳执行计划

31.8SQL查询

· 禁止在数据库做复杂运算
· 禁止使用SELECT *
· 禁止在索引列上使用函数或计算
· 禁止使用游标
· 禁止使用触发器
· 禁止在查询里指定索引
· 变量/参数/关联字段类型必须与字段类型一致
· 参数化查询
· 限制JOIN个数
· 限制SQL语句长度及IN子句个数
· 尽量避免大事务操作
· 关闭影响的行计数信息返回
· 除非必要SELECT语句都必须加上NOLOCK
· 使用UNION ALL替换UNION
· 查询大量数据使用分页或TOP
· 递归查询层级限制
· NOT EXISTS替代NOT IN
· 临时表与表变量
· 使用本地变量选择中庸执行计划
· 尽量避免使用OR运算符
· 增加事务异常处理机制
· 输出列使用二段式命名格式

31.9禁止在数据库做复杂运算

· XML解析
· 字符串相似性比较
· 字符串搜索（Charindex）
· 复杂运算在程序端完成

31.10 禁止使用SELECT *

· 减少内存消耗和网络带宽
· 给查询优化器有机会从索引读取所需要的列
· 表结构变化时容易引起查询出错

31.11 禁止在索引列上使用函数或计算

在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描
假设在字段Col1上建有一个索引，则下列场景将无法使用到索引：
ABS[Col1]=1
[Col1]+1>9
再举例说明一下

像上面这样的查询，将无法用到O_OrderProcess表上的PrintTime索引，所以我们应用使用如下所示的查询SQL

31.12 禁止在索引列上使用函数或计算

假设在字段Col1上建有一个索引，则下列场景将可以使用到索引：
[Col1]=3.14
[Col1]>100
[Col1] BETWEEN 0 AND 99
[Col1] LIKE ‘abc%’
[Col1] IN(2,3,5,7)

31.13 LIKE查询的索引问题

1.[Col1] like “abc%” –index seek 这个就用到了索引查询2.[Col1] like “%abc%” –index scan 而这个就并未用到索引查询3.[Col1] like “%abc” –index scan 这个也并未用到索引查询
我想从上而三个例子中，大家应该明白，最好不要在LIKE条件前面用模糊匹配，否则就用不到索引查询。

31.14 禁止使用游标

关系数据库适合集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能。
游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。
（再加上游标真心比较复杂，挺不好用的，尽量少用吧）

31.15 禁止使用触发器

触发器对应用不透明（应用层面都不知道会什么时候触发触发器，发生也也不知道，感觉莫名……）

31.16 禁止在查询里指定索引

With(index=XXX)（ 在查询里我们指定索引一般都用With(index=XXX) ）
· 随着数据的变化查询语句指定的索引性能可能并不最佳
· 索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障
· 新建的索引无法被应用立即使用，必须通过发布代码才能生效

31.17 变量/参数/关联字段类型必须与字段类型一致（这是我之前不太关注的）

避免类型转换额外消耗的CPU，引起的大表scan尤为严重


看了上面这两个图，我想我不用解释说明，大家都应该已经清楚了吧。
如果数据库字段类型为VARCHAR，在应用里面最好类型指定为AnsiString并明确指定其长度
如果数据库字段类型为CHAR，在应用里面最好类型指定为AnsiStringFixedLength并明确指定其长度
如果数据库字段类型为NVARCHAR，在应用里面最好类型指定为String并明确指定其长度

31.18 参数化查询

以下方式可以对查询SQL进行参数化：sp_executesql
Prepared Queries
Stored procedures
用图来说明一下，哈哈。

31.19 限制JOIN个数

· 单个SQL语句的表JOIN个数不能超过5个
· 过多的JOIN个数会导致查询分析器走错执行计划
· 过多JOIN在编译执行计划时消耗很大

31.20 限制IN子句中条件个数

在 IN 子句中包括数量非常多的值（数以千计）可能会消耗资源并返回错误 8623 或 8632，要求IN子句中条件个数限制在100个以内

31.21 尽量避免大事务操作

· 只在数据需要更新时开始事务，减少资源锁持有时间
· 增加事务异常捕获预处理机制
· 禁止使用数据库上的分布式事务
用图来说明一下

也就是说我们不应该在1000行数据都更新完成之后再commit tran,你想想你在更新这一千行数据的时候是不是独占资源导致其它事务无法处理。

31.22 关闭影响的行计数信息返回

在SQL语句中显示设置Set Nocount On，取消影响的行计数信息返回，减少网络流量
除非必要SELECT语句都必须加上NOLOCK

31.23 除非必要，尽量让所有的select语句都必须加上NOLOCK

指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设 置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）
使用UNION ALL替换UNION

31.24 使用UNION ALL替换UNION

UNION会对SQL结果集去重排序，增加CPU、内存等消耗

31.25 查询大量数据使用分页或TOP

合理限制记录返回数，避免IO、网络带宽出现瓶颈

31.26 递归查询层次限制

使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环

31.27 临时表与表变量

31.28 使用本地变量选择中庸执行计划

在存储过程或查询中，访问了一张数据分布很不平均的表格，这样往往会让存储过程或查询使用了次优甚至于较差的执行计划上，造成High CPU及大量IO Read等问题，使用本地变量防止走错执行计划。
采用本地变量的方式，SQL在编译的时候是不知道这个本地变量的值，这时候SQL会根据表格里数据的一般分布，”猜测”一个返回值。不管用户在调用存储过程或语句的时候代入的变量值是多少，生成的计划都是一样的。这样的计划一般会比较中庸一些，不一定是最优的计划，但一般也不会是最差的计划
如果查询中本地变量使用了不等式运算符，查询分析器使用了一个简单的 30% 的算式来预估Estimated Rows =(Total Rows 30)/100
如果查询中本地变量使用了等式运算符，则查询分析器使用：精确度 表记录总数来预估Estimated Rows = Density * Total Rows

31.29 尽量避免使用OR运算符

对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集

31.30 增加事务异常处理机制

应用程序做好意外处理，及时做Rollback。
设置连接属性 “set xact_abort on”

31.31 输出列使用二段式命名格式

二段式命名格式：表名.字段名
有JOIN关系的TSQL，字段必须指明字段是属于哪个表的，否则未来表结构变更后，有可能发生Ambiguous column name的程序兼容错误

31.32 架构设计

· 读写分离
· schema解耦
· 数据生命周期

31.33 读写分离

· 设计之初就考虑读写分离，哪怕读写同一个库，有利于快速扩容
· 按照读特征把读分为实时读和可延迟读分别对应到写库和读库
· 读写分离应该考虑在读不可用情况下自动切换到写端

31.34 Schema解耦

禁止跨库JOIN

31.35 数据生命周期

根据数据的使用频繁度，对大表定期分库归档
主库/归档库物理分离

31.36 日志类型的表应分区或分表

对于大的表格要进行分区，分区操作将表和索引分在多个分区，通过分区切换能够快速实现新旧分区替换，加快数据清理速度，大幅减少IO资源消耗

31.37 频繁写入的表，需要分区或分表

自增长与Latch Lock
闩锁是sql Server自己内部申请和控制，用户没有办法来干预，用来保证内存里面数据结构的一致性，锁级别是页级锁
来源：http://www.codeceo.com/article/sql-server-tips.html

32 SQL 向一个表中批量插入或删除大量数据

插入：
1. 数据从另一个表中获取
（1）两表结构不一样insert into tb1 需要的列名 select 按照前面写上需要的列名 from tb2（2）两表结构一样insert into tb1 select from tb2
2. 数据直接输入
（1）
INSERT INTO MyTable(ID,NAME) VALUES(1,’123’);
INSERT INTO MyTable(ID,NAME) VALUES(2,’456’);
INSERT INTO MyTable(ID,NAME) VALUES(3,’789’);
（2）
INSERT INTO MyTable(ID,NAME) VALUES(7,’003’),(8,’004’),(9,’005’);
（3）UNION (ALL)
INSERT INTO MyTable(ID,NAME)
SELECT 4,’000’
UNION ALL
SELECT 5,’001’
UNION ALL
SELECT 6,’002’ ;
这种方法比（1）和（2）要快。

UNION && UNION ALL：
http://www.w3school.com.cn/sql/sql_union.asp
语法：
SELECT column_name(s) FROM table_name1
UNION (ALL)
SELECT column_name(s) FROM table_name2
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
删除：
DELETE FROM MyTable WHERE ID IN (1,2,4);
3. 从excel或access或TXT等文件导入。参考如下：
导出到excel
EXEC master..xp_cmdshell ‘bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S”GNETDATA/GNETDATA” -U”sa” -P””‘
/* 导入Excel
SELECT
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
‘Data Source=”c:\test.xls”;User ID=Admin;Password=;Extended properties=Excel 5.0’)…xactions
/动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = ‘c:\test.xls’
set @s =’’’Microsoft.Jet.OLEDB.4.0’’,
‘’Data Source=”‘+@fn+’”;User ID=Admin;Password=;Extended properties=Excel 5.0’’’
set @s = ‘SELECT FROM OpenDataSource (‘+@s+’)…sheet1$’
exec(@s)
/
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+’　‘ 转换后的别名
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
‘Data Source=”c:\test.xls”;User ID=Admin;Password=;Extended properties=Excel 5.0’)…xactions




/** EXCEL导到远程SQL
insert OPENDATASOURCE(
‘SQLOLEDB’,
‘Data Source=远程ip;User ID=sa;Password=密码’
).库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
‘Data Source=”c:\test.xls”;User ID=Admin;Password=;Extended properties=Excel 5.0’)…xactions
/ 导入文本文件
EXEC master..xp_cmdshell ‘bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword’
/ 导出文本文件
EXEC master..xp_cmdshell ‘bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword’
或
EXEC master..xp_cmdshell ‘bcp “Select from dbname..tablename” queryout c:\DT.txt -c -Sservername -Usa -Ppassword’
导出到TXT文本，用逗号分开
exec master..xp_cmdshell ‘bcp “库名..表名” out “d:\tt.txt” -c -t ,-U sa -P password’
BULK INSERT 库名..表名
FROM ‘c:\test.txt’
WITH (
FIELDTERMINATOR = ‘;’,
ROWTERMINATOR = ‘\n’
)
—/ dBase IV文件
select from
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0’
,’dBase IV;HDR=NO;IMEX=2;DATABASE=C:\’,’select from [客户资料4.dbf]’)
—/


—/ dBase III文件
select from
OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0’
,’dBase III;HDR=NO;IMEX=2;DATABASE=C:\’,’select from [客户资料3.dbf]’)
—/
—/ FoxPro 数据库
select from openrowset(‘MSDASQL’,
‘Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\’,
‘select from [aa.DBF]’)
—/
/**导入DBF文件**/
select from openrowset(‘MSDASQL’,
‘Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF’,
‘select from customer where country != “USA” order by country’)
go
/** 导出到DBF */
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insert into openrowset(‘MSDASQL’,
‘Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\’,
‘select from [aa.DBF]’)
select from 表
说明:
SourceDB=c:\ 指定foxpro表所在的文件夹
aa.DBF 指定foxpro表的文件名.
/*导出到Access**/
insert into openrowset(‘Microsoft.Jet.OLEDB.4.0’,
‘x:\A.mdb’;’admin’;’’,A表) select from 数据库名..B表
/**导入Access**/

insert into B表 selet from openrowset(‘Microsoft.Jet.OLEDB.4.0’,
‘x:\A.mdb’;’admin’;’’,A表)
文件名为参数
declare @fname varchar(20)
set @fname = ‘d:\test.mdb’
exec(‘SELECT a. FROM opendatasource(‘’Microsoft.Jet.OLEDB.4.0’’,
‘’’+@fname+’’’;’’admin’’;’’’’, topics) as a ‘)
SELECT
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
‘Data Source=”f:\northwind.mdb”;Jet OLEDB:Database Password=123;User ID=Admin;Password=;’)…产品
** 导入 xml　文件
DECLARE @idoc int
DECLARE @doc varchar(1000)
—sample XML document
SET @doc =’



Customer was very satisfied




Important
Happy Customer.




‘
— Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

— Execute a SELECT statement using OPENXML rowset provider.
SELECT
FROM OPENXML (@idoc, ‘/root/Customer/Order’, 1)
WITH (oid char(5),
amount float,
comment ntext ‘text()’)
EXEC sp_xml_removedocument @idoc
/**Excel导到Txt**/
想用
select into opendatasource(…) from opendatasource(…)
实现将一个Excel文件内容导入到一个文本文件
假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分，前8位和后8位分开。
邹健：
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.
insert into
opendatasource(‘MICROSOFT.JET.OLEDB.4.0’
,’Text;HDR=Yes;DATABASE=C:\’
)…[aa#txt]
—,aa#txt)
—/
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource(‘MICROSOFT.JET.OLEDB.4.0’
,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls’
—,Sheet1$)
)…[Sheet1$]



如果你想直接插入并生成文本文件,就要用bcp
declare @sql varchar(8000),@tbname varchar(50)
—首先将excel表内容导入到一个全局临时表
select @tbname=’[##temp’+cast(newid() as varchar(40))+’]’
,@sql=’select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into ‘+@tbname+’ from
opendatasource(‘’MICROSOFT.JET.OLEDB.4.0’’
,’’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls’’
)…[Sheet1$]’
exec(@sql)
—然后用bcp从全局临时表导出到文本文件
set @sql=’bcp “‘+@tbname+’” out “c:\aa.txt” /S”(local)” /P”” /c’
exec master..xp_cmdshell @sql
—删除临时表
exec(‘drop table ‘+@tbname)
/**导整个数据库**/
用bcp实现的存储过程
/
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
—导出调用示例
——导出单个表
exec file2table ‘zj’,’’,’’,’xzkh_sa..地区资料’,’c:\zj.txt’,1
——导出整个数据库
exec file2table ‘zj’,’’,’’,’xzkh_sa’,’C:\docman’,1
—导入调用示例
——导入单个表
exec file2table ‘zj’,’’,’’,’xzkh_sa..地区资料’,’c:\zj.txt’,0
——导入整个数据库
exec file2table ‘zj’,’’,’’,’xzkh_sa’,’C:\docman’,0
/
if exists(select 1 from sysobjects where name=’File2Table’ and objectproperty(id,’IsProcedure’)=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200) —服务器名
,@username varchar(200) —用户名,如果用NT验证方式,则为空’’
,@password varchar(200) —密码
,@tbname varchar(500) —数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000) —导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit —1为导出,0为导入
as
declare @sql varchar(8000)
if @tbname like ‘%.%.%’ —如果指定了表名,则直接导出单个表
begin
set @sql=’bcp ‘+@tbname
+case when @isout=1 then ‘ out ‘ else ‘ in ‘ end
+’ “‘+@filename+’” /w’
+’ /S ‘+@servername
+case when isnull(@username,’’)=’’ then ‘’ else ‘ /U ‘+@username end
+’ /P ‘+isnull(@password,’’)
exec master..xp_cmdshell @sql
end
else
begin —导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>’\’ set @filename=@filename+’\’

set @m_tbname=’declare #tb cursor for select name from ‘+@tbname+’..sysobjects where xtype=’’U’’’
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql=’bcp ‘+@tbname+’..’+@m_tbname
+case when @isout=1 then ‘ out ‘ else ‘ in ‘ end
+’ “‘+@filename+@m_tbname+’.txt “ /w’
+’ /S ‘+@servername
+case when isnull(@username,’’)=’’ then ‘’ else ‘ /U ‘+@username end
+’ /P ‘+isnull(@password,’’)
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go
/* Oracle **/
EXEC sp_addlinkedserver ‘OracleSvr’,
‘Oracle 7.3’,
‘MSDAORA’,
‘ORCLDB’
GO
delete from openquery(mailser,’select from yulin’)
select from openquery(mailser,’select from yulin’)
update openquery(mailser,’select from yulin where id=15’)set disorder=555,catago=888
insert into openquery(mailser,’select disorder,catago from yulin’)values(333,777)
补充：
对于用bcp导出,是没有字段名的.
用openrowset导出,需要事先建好表.
用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入

33 SQL AUTO INCREMENT 字段

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

33.1 AUTO INCREMENT 字段

我们通常希望在每次插入新记录时，自动地创建主键字段的值。
我们可以在表中创建一个 auto-increment 字段。

Set Identity_insert _Orbo_VRS_Data1 off
Set Identity_insert _Orbo_VRS_Data1 on

33.2 用于 MySQL 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：
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 语法：
ALTER TABLE Persons AUTO_INCREMENT=100
要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。

33.3 用于 SQL Server 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：
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)。
要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。

33.4 用于 Access 的语法

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：
CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment 任务。
默认地，AUTOINCREMENT 的开始值是 1，每条新记录递增 1。
提示：要规定 “ID” 列以 10 起始且递增 5，请把 autoincrement 改为 AUTOINCREMENT(10,5)。
要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）：
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。

33.5 用于 Oracle 的语法

在 Oracle 中，代码稍微复杂一点。
您必须通过 sequence 对象（该对象生成数字序列）创建 auto-increment 字段。
请使用下面的 CREATE SEQUENCE 语法：
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 序列中取回下一个值）：
INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,’Lars’,’Monsen’)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋值为来自 seq_person 序列的下一个数字。”FirstName”列 会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。

34