四、sql进阶[约束详细]

一、NOT NULL (非空):

句意:默认的情况下，表的列接受 NULL 值,否则设置为NOT NULL

创表非空句例:
句例:CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
添加非空句例:
句例:ALTER TABLE Persons MODIFY Age int NOT NULL;

删除非空句例:
句例:ALTER TABLE Persons MODIFY Age int NULL;

二、UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录
mysql和其他主流这个关键字的使用方法是不一样的:

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 / 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四个主流的是一样的):MySQL / 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),
	CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)



当表已被创建时，如需在 "P_Id" 列单独创建 UNIQUE 约束，请使用下面的 SQL：
句例:ALTER TABLE Persons ADD UNIQUE (P_Id)

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
句例:ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)


删除UNIQUE 约束
MySQL：
句例:ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access：
句例:ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

三、PRIMARY KEY(主键)

句意:主键必须包含唯一的值、不能包含 NULL 值,每个表都应该有一个主键,且只能有一个主键

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 / MS Access：
句例: CREATE TABLE Persons
(
	P_Id int NOT NULL PRIMARY KEY,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Address varchar(255),
	City varchar(255)
)

当表已被创建时，如需在 "P_Id" 列创建 PRIMARY KEY 约束，请使用下面的 SQL(数据库通用)：
ALTER TABLE Persons ADD PRIMARY KEY (P_Id)

如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法(数据库通用)：
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 / MS Access：ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

四、FOREIGN KEY(外键):

语义:
FOREIGN KEY 约束用于预防破坏表之间连接的行为。
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 / MS Access(句例)：
CREATE TABLE Orders
(
	O_Id int NOT NULL PRIMARY KEY,
	OrderNo int NOT NULL,
	P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)


主动添加外键:

当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束,：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

定义多个列的 FOREIGN KEY 约束(通用):
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)
)


删除外键:
如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：
MySQL(句例)：
	ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access(句例)：
	ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

五、CHECK(检查约束)

语义: 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 / MS Access(句例)：
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 约束(通用):
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')
)

修改检查约束:

当表已被创建时，如需在 "P_Id" 列创建 CHECK 约束，请使用下面的 SQL：
	ALTER TABLE Persons ADD CHECK (P_Id>0)

如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法：
	ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')


撤销 CHECK 约束
	SQL Server / Oracle / MS Access(句例)：
ALTER TABLE Persons DROP CONSTRAINT chk_Person
MySQL(句例)：
	ALTER TABLE Persons DROP CHECK chk_Person

六、DEFAULT(默认):

句意:用于向列中插入默认值

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



添加默认值:
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'


撤销  DEFAULT 约束
MySQL：
	ALTER TABLE Persons
	ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access：
	ALTER TABLE Persons
	ALTER COLUMN City DROP DEFAULT

七、CREATE INDEX(索引)

CREATE INDEX 语句用于在表中创建索引(一般用于sql优化):
注意:更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引

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

八 、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)
)
设置起始值:
	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)
)
在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1
加入新递增列:INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen')


Oracle(实例):
特殊:您必须通过 sequence 对象（该对象生成数字序列）创建 auto-increment 字段
	CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10
创建一个名为 seq_person 的 sequence 对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能
为某列创建一个自增列:
	INSERT INTO Persons (ID,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen')