SQL必知必会--中级篇（二）

接上一篇SQL必知必会--中级篇（一），继续对sql知识进行整理复习。

本篇包含知识点如图：

---

一、SQL 约束

用于规定表中的数据规则；

如果存在违反约束的数据行为，行为会被约束终止；

约束可以在创建表时规定(通过 create table 语句)，或者在表创建之后规定(通过 alter table 语句)；

1.not null

作用：强制列不接受 NULL 值；强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录

例子：下面的SQL 强制 "Id" 列和 "LastName" 列不接受 NULL 值：

create table Persons(
    Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
    ……
 )

2.unique

作用：唯一标识数据库表中的每条记录

unique和 primary key 约束均为列或列集合提供了唯一性的保证;

primary key约束拥有自动定义的 unique 约束;

PS：每个表可以有多个unique 约束，但是每个表只能有一个primary key约束

例子：

（1）create table时的unique 约束

下面的 SQL 在 "Persons" 表创建时在 "P_Id" 列上创建 unique 约束：

create table Person(
P_Id int not null,
LastName varchar(255) not null,
FirstName varchar(255) ,
Address varchar(255),
City varchar(255),
unique (P_Id) 
)

（2）alter table时的 unique 约束

当表已被创建时，如需在 "P_Id" 列创建 unique 约束，应使用下面的 SQL：

alter table Person add unique(P_Id);

命名 unique 约束，并定义多个列的 unique 约束的 SQL 语法：

alter table Persons add constraint uc_PersonID unique (P_Id,LastName);

（3）撤销 unique 约束

alter table Persons drop index uc_PersonID; //mysql

alter table Persons drop constraint uc_PersonID; //SQL Server / Oracle / MS Access

3.primary key

primary key 约束唯一标识数据库表中的每条记录；

主键必须包含唯一的值；

主键列不能包含 NULL 值；

每个表都应该有一个主键，并且每个表只能有一个主键；

（1）create table 时的primary key 约束

create table Persons(           //mysql
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
primary key (P_Id)
);  
                      
create table Persons(         //SQL Server / Oracle / MS Access
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

命名primary key 约束的语法：

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)
);

（2）alter table 时的primary key 约束

alter table Persons add primary key (P_Id);

命名 primary key 约束，并定义多个列的 primary key 约束的 SQL 语法：

alter table Personsadd constraint pk_PersonID primary key (P_Id,LastName);

（3）撤销primary key约束

alter table Persons drop primary key;  //mysql
alter table Persons drop constraint pk_PersonID; //SQL Server / Oracle / MS Access

4.foreign key

一个表中的 foreign key 指向另一个表中的unique(唯一约束的键)；

foreign key 约束用于预防破坏表之间连接的行为；

foreign key 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一；

（1）create table 时的foreign key 约束

create table Orders(                   // mysql
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
primary key (O_Id),
foreign key (P_Id) references Persons(P_Id)
);

create table Orders(                 //SQL Server / Oracle / MS Access
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int foreign key 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)
);

（2）alter table 时的foreign key 约束

alter table Orders add foreign key (P_Id) references Persons(P_Id);

命名 foreign key 约束，并定义多个列的 foreign key 约束的 SQL 语法：

alter table Orders add constraint fk_PerOrders
foreign key (P_Id) references Persons(P_Id);

（3）撤销 foreign key约束

alter table Orders drop foreign key fk_PerOrders; //mysql
alter table Orders drop constraint fk_PerOrders;  //SQL Server / Oracle / MS Access

5.check

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

如果对单个列定义 check 约束，那么该列只允许特定的值。

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

（1）create table 时的 check 约束

下面的语句，check 约束规定 "P_Id" 列必须只包含大于 0 的整数

create table Persons(            //mysql
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
check (P_Id>0)
);
create table Persons(          //SQL Server / Oracle / MS Access
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')
);

（2）alter table 时的 check 约束

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');

（3）撤销 check 约束

alter table Persons drop check chk_Person;      //mysql
alter table Persons drop constraint chk_Person; //SQL Server / Oracle / MS Access

6.default

default 约束用于向列中插入默认值；

如果没有规定其他的值，那么会将默认值添加到所有的新记录；

（1）create table 时的 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'
);

通过使用类似 gdedate() 这样的函数，dafault约束也可以用于插入系统值：

create table Orders(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date default GETDATE()
);

（2）alter table 时的 default 约束

alter table Persons alter City set default 'SANDNES'; // mysql
alyer table Persons add constraint ab_c default 'SANDNES' for City; //SQL Server / MS Acces
alter table Persons modify City default 'SANDNES; //Oracle

（3）撤销 default 约束

alter table Persons alter City drop default;        //mysql
alter table Persons alter column City drop default; //SQL Server / Oracle / MS Access

7.auto increment

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

通常希望在每次插入新记录，自动地创建主键字段的值时，就可以使用；

在此只介绍用于 mysql 的语法，其他的参见菜鸟教程

把 "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 序列以其他的值起始，写法如下：

 alter table Persons auto_increment = 100;