2020-12-23 SQL复习02

约束

NOT NULL 约束(不为空)

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

UNIQUE 约束(唯一)

创建表时建立：

CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,（SQL Server / Oracle / MS Access）
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)（MySQL）
)

如果需要命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

当表已被创建时，创建 UNIQUE 约束：

ALTER TABLE Persons
ADD UNIQUE (Id_P)

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

撤销 UNIQUE 约束：
ALTER TABLE Persons
DROP INDEX uc_PersonID （MySQL）
DROP CONSTRAINT uc_PersonID （SQL Server / Oracle / MS Access）

PRIMARY KEY约束（主键）

唯一，不为NULL
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,（SQL Server / Oracle / MS Access）
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P) （MySQL）
)

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

ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

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

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

ALTER TABLE Persons
DROP PRIMARY KEY

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

FOREIGN KEY约束（外键）

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
MySQL：
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
QL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

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

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

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

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

CHECK 约束（限制列中的值的范围）

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

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

CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),(SQL Server / Oracle / MS Access)
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0) （MySQL）
)
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes’)
)

ALTER TABLE Persons
ADD CHECK (Id_P>0)
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes’)

SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
MySQL:
ALTER TABLE Persons
DROP CHECK chk_Person

DEFAULT 约束（向列中插入默认值）

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

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

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

MySQL:
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES’
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES’

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 index_name ON table_name (column_name)
注释：“column_name” 规定需要索引的列，允许使用重复的值

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值：
CREATE UNIQUE INDEX index_name ON table_name (column_name)

字段

AUTO_INCREMENT(新记录插入表中时生成一个唯一的数字)

常用于主键自增

MySQL：
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
从特定值开始：
ALTER TABLE Persons AUTO_INCREMENT=100

SQL Server：
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
从20开始，每次自增10：IDENTITY→IDENTITY(20,10)

Oracle：
先通过 sequence 对创建 auto-increment 字段
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10 （该对象缓存 10 个值以提高性能）

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

INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,‘Lars’,‘Monsen’)
上面的 SQL 语句会在 “Persons” 表中插入一条新记录。“P_Id” 的赋值是来自 seq_person 序列的下一个数字。“FirstName” 会被设置为 “Bill”，“LastName” 列会被设置为 “Gates”。