目录
UNIQUE/CHECK/PRIMARY KEY /FOREIGN KEY约束
已有表添加约束(UNIQUE/PRIMARY KEY/CHECK)
UNIQUE/PRIMARY KEY/FOREIGN KEY/CHECK
SQL索引
在数据库表的一列或多列上创建索引,并根据这些列的值进行排序。当执行查询时,数据库引擎可以使用索引来快速定位符合查询条件的数据行,而不必扫描整个表。这样可以大大减少查询所需的时间和资源消耗(用于加快对列筛选WHERE 语句的效率)。
常见的索引有B树索引、哈希索引、全文索引、空间索引。
CREATE INDEX idx_users_email ON users (email);
例如上面在users表中创建了名为idx_users_email的索引是关于email列的,此时下面对email的筛选效率会提升很多。过
SELECT * FROM users WHERE email = 'example@example.com';
注意:一般选择经常被查询的列作为索引列,索引的创建会增加存储空间和写操作的成本,需要权衡索引的数量和复杂度
创建索引
普通索引允许使用列中有重复的值
CREATE INDEX PIndex
ON Persons (LastName, FirstName)
唯一索引不允许列中有重复值
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
删除索引
MS Access
DROP INDEX index_name ON table_name
MS SQL Server
DROP INDEX table_name.index_name
DB2/Oracle
DROP INDEX index_name
MySQL
ALTER TABLE table_name DROP INDEX index_name
AUTO INCREMENT
在新记录插入表中时在设置列中生成一个递增的数字。
MySQL
id默认开始值为1,每次插入一条数据递增1。
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
)
设置起始值为100
ALTER TABLE Persons AUTO_INCREMENT=100
SQL Server
MS SQL Server 使用 IDENTITY 关键字来执行 AUTO_INCREMENT,下面表示id默认值为1,每次插入一条数据递增2。
CREATE TABLE Persons
(
ID int IDENTITY(1,2) PRIMARY KEY,
LastName varchar(255) NOT NULL,
)
MS Access
MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment
CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
)
设置起始值1和递增值2。
CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT(1,2),
LastName varchar(255) NOT NULL,
)
Oracle
需要通过 sequence 对象(该对象生成数字序列)创建 auto-increment 字段。再通过nextval 函数在插入数据时调用获取递增的值。
下面创建一个名为 seq_person 的 sequence 对象,它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值。
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
使用时借助nextval 函数(该函数从 seq_person 序列中取回下一个值)
INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
SQL约束
规定表中的数据规则,有not null、unique、primary key、foreign key、check、defalut约束。
Not null(非null)
不能命名约束直接指定多列,但可以通过check约束中is not null来实现。撤销时通过alter修改列数据类型。
创建表时添加约束
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
Age int
);
已有表添加约束
ALTER TABLE Persons MODIFY Age int NOT NULL;
DEFAULT(默认值)
向列中插入默认值,如果没有规定其他的值,那么会将默认值添加到所有的新记录。
创建表时添加约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
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'
撤销约束
MySQL
ALTER TABLE Persons
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
UNIQUE/CHECK/PRIMARY KEY /FOREIGN KEY约束
新建表添加约束(UNIQUE/PRIMARY KEY)
单列设置
MySQL
CREATE TABLE Persons
(
P_Id int NOT NULL,
UNIQUE/PRIMARY KEY (P_Id)
)
SQL Server / Oracle / MS Access
CREATE TABLE Persons
(P_Id int NOT NULL UNIQUE/PRIMARY KEY)
多列设置(命名约束)
只需删除该命名就去除了多列的约束设置。
MySQL/SQL Server / Oracle / MS Access
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT uc_PersonID UNIQUE/PRIMARY KEY (P_Id,LastName)
)
已有表添加约束(UNIQUE/PRIMARY KEY/CHECK)
ALTER TABLE Persons
ADD UNIQUE/PRIMARY KEY/CHECK (P_Id)
多列设置(多列都取不同)并命名约束(只需删除该命名就去除了多列的约束设置)
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE/PRIMARY KEY/CHECK (P_Id,LastName)
撤销命名约束
UNIQUE/FOREIGN KEY/CHECK
MySQL
ALTER TABLE Persons
DROP UNIQUE/FOREIGN KEY/CHECK chk_Person
UNIQUE/PRIMARY KEY/FOREIGN KEY/CHECK
SQL Server / Oracle / MS Access
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
Unique(去重)
保证改字段下的值无重复内容(null也只能有一个),注意命名约束多列相当于对每一列都设置了unique。
使用见上面。
Check
规定某一列或多列值的范围。
其它场景使用见上面。
创建表时添加约束
MySQL
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
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,
)
Primary KEY(主键)
主键,相当于id对应唯一的数据,非null且不能重复值。一个表中只能设置一个主键。
使用方法和Unique相同(用Primary KEY替换unique即可),除了撤销约束不同
其它场景使用见上面。
撤销约束/命名约束
MySQL
ALTER TABLE Persons
DROP PRIMARY KEY
FOREIGN KEY(外键)
建立设置外键表和主键表的联系,设置外键的字段对应另一个表中的主键。
外键可以为空值,若不为空值,则每一个外键的值必须等于另一个表中主键的某个值。
外键中列的数目必须和父表的主键中列的数目相同。
一个表中可以设置多个外键。
MySQL的外键约束(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)
)
命名 FOREIGN KEY 约束
定义多个列的 FOREIGN KEY 约束
MySQL / SQL Server / Oracle / MS Access
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)
)
已有表添加约束
MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
命名 FOREIGN KEY 约束
并定义多个列的 FOREIGN KEY 约束
MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
撤销命名约束(撤销多列约束)
MySQL
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders