SQL学习二十、SQL高级特性

约束（constraint）

管理如何插入或处理数据库数据的规则。

DBMS 通过在数据库表上施加约束来实施引用完整性。
大多数约束是在 表定义中定义的，用 CREATE TABLE 或 ALTER TABLE 语句。

---

1、主键 （PRIMARY KEY）

主键是一种特殊的约束，用来保证一列（或 一组列）中的值是唯一的，而且永不改动。主键可以唯一标识表中的一行。且主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。
这方便了直接或交互地处理表中的行。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会 非常困难。

• 创建表的时候指定主键

CREATE TABLE `supplier_new` (
    `id`    INTEGER NOT NULL,
    `supplier`  TEXT,
    `supplierAddress`   TEXT,
    `supplierTel`   TEXT NOT NULL,
    `supplierEmail` TEXT,
    `supplierContact`   TEXT,
    PRIMARY KEY(`id`)
);

• 指定自增主键

CREATE TABLE `adjunctlist` (
    `id`    integer PRIMARY KEY AUTOINCREMENT,
    `add_time`  text,
    `ext0`  text,
    `ext1`  text,
    `file_name` text,
    `file_no`   integer,
    `file_path` text,
    `file_uri`  text,
    `flaw_id`   integer,
    `is_upload` integer,
    `local_flaw_id` integer,
    `user_id`   text,
    `task_id`   integer,
    `task_point_id` integer,
    `task_type` integer
);

• 某些DBMS运行在ALTER TABLE语句中指定主键

ALTER TABLE 表名  ADD CONSTRAINT PRIMARY KEY (列名);

比如这张表在创建的时候没有指定主键，我们可以通过ALTER TABLE语句为其指定主键

alter table test
add constraint primary key (test.no)

注意：SQLite不允许使用ALTER TABLE定义键，要求在初始的CREATE TABLE 语句中定义它们。

---

2、外键

外键是表中的一列，其值必须是另一表的唯一约束列，一般用另外一张表的主键。

当我们指定外键后，DBMS 不允许删除在另一个表中具有关联行的行，因此，可以利用外键防止意外删除。

比如，我们我们的订单表( oderlist_new )和供应商表( supplier_new )

• 创建时指定
  同样的我们在创建表的时候可以指定表的外键，使用REFERENCES 表名(列名)
  例如，我们在创建订单表（oderlist_new）时指定订单表（oderlist_new）的supplierId作为外键关联供应商表（supplier_new）的主键id

CREATE TABLE `oderlist_new` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `goodsName` TEXT,
    `quantity`  integer,
    `item_price`    real,
    `orderNo`   text NOT NULL,
    `userId`    INTEGER,
    `userName`  TEXT,
    `orderTime` TEXT,
    `supplierId`    INTEGER NOT NULL REFERENCES supplier_new (id)
);

• 利用ALTER TABLE指定
  利用ALTER TABLE语句时需要这样写

ALTER TABLE 表1
ADD CONSTRAINT FOREIGN KEY (表1.列) REFERENCES 表2(表2.列) 

• 防止意外删除
  比如我们删除一条供应商信息，这条供应商信息在订单表中有作为外键使用，删除的时候就会失败。

delete from supplier_new
where id = 1

---

3、唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主 键，但存在以下重要区别。
1、表可包含多个唯一约束，但每个表只允许一个主键。
2、唯一约束列可包含 NULL 值。
3、唯一约束列可修改或更新。
4、唯一约束列的值可重复使用。
5、与主键不一样，唯一约束不能用来定义外键。

例如我们的订单表，id是主键（唯一约束），订单编号orderNo也是唯一约束

唯一约束既可以用 UNIQUE 关 键字在表定义中定义，也可以用单独的 CONSTRAINT 定义。

创建订单表的时候，指定orderNo具有唯一约束orderNo text NOT NULL UNIQUE

CREATE TABLE `oderlist_new` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `goodsName` TEXT,
    `quantity`  integer,
    `item_price`    real,
    `orderNo`   text NOT NULL UNIQUE,
    `userId`    INTEGER,
    `userName`  TEXT,
    `orderTime` TEXT,
    `supplierId`    INTEGER NOT NULL,
    FOREIGN KEY(`supplierId`) REFERENCES `supplier_new`(`id`)
);

---

4、检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。

• 常见用途

1、检查最小或最大值。例如，防止 0个物品的订单（即使0是合法的数）。
2、指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天 起一年后的日期。
3、只允许特定的值。例如，在性别字段中只允许 M 或 F。

例如，我们限定订单表中商品单价和商品数量必须大于0，CHECK(quantity > 0) 和 CHECK(item_price > 0)

CREATE TABLE `oderlist_new` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `goodsName` TEXT,
    `quantity`  integer CHECK(quantity > 0),
    `item_price`    real CHECK(item_price > 0),
    `orderNo`   text NOT NULL UNIQUE,
    `userId`    INTEGER,
    `userName`  INTEGER,
    `orderTime` TEXT,
    `supplierId`    INTEGER NOT NULL,
    FOREIGN KEY(`supplierId`) REFERENCES `supplier_new`(`id`)
);

这样我们在插入不符合要求的数据时就会失败，比如我们插入如下数据

"菠萝"    
"0" 
"0.0"   
"20181023008"   
"30"    
"王舍"    
"2018-10-23 23:12:49"   
3

• 插入不符合约束的数据

insert into oderlist_new (goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)
VALUES ('菠萝',0,0.0,'20181023008',30,'王舍','2018-10-23 23:12:49',3)

执行结果

• 插入符合约束的数据，我们把单价修改为20.0，数量修改为10

insert into oderlist_new (goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)
VALUES ('菠萝',10,20.0,'20181023008',30,'王舍','2018-10-23 23:12:49',3)

执行结果 插入成功

---

索引

索引用来排序数据以加快搜索和排序操作的速度。

注意
1、索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。 在执行这些操作时，DBMS必须动态地更新索引。
2、索引数据可能要占用大量的存储空间。

所以，在这些情况下不应该使用索引

• 索引不应该使用在较小的表上。

• 索引不应该使用在有频繁的大批量的更新或插入操作的表上。

• 索引不应该使用在含有大量的 NULL 值的列上。

• 索引不应该使用在频繁操作的列上。

• 创建索引

索引用CREATE INDEX语句创建（不同DBMS创建索引的语句变化很大）。

CREATE INDEX 索引名
ON 
表名 (列名); 

例如，我们用订单编号作为订单表的索引

CREATE INDEX oderlist_new_ind
ON 
oderlist_new (orderNo); 

执行结果 创建成功

---

• 查询数据库中所有索引

SELECT * 
FROM sqlite_master 
WHERE type = 'index';

查询结果

• 查询对应表下的索引 使用 AND 子句连接表名

SELECT * 
FROM sqlite_master 
WHERE type = 'index' and tbl_name = 'oderlist_new';

---

• 删除索引 DROP INDEX 索引名

DROP INDEX 'oderlist_new_ind';

执行成功