重学MYSQL之高级SQL特性

数据库 2020-09-12 930

1.约束

概念:管理如何插入或处理数据库数据的规则

1.1 主键

主键是一种特殊的约束,用来保证一列(或一组列)中的值是唯一的,而且永不改动。换句话说,表中的一列(或多列)的值唯一标识表中的每一行。

表中任意列只要满足以下条件,都可以用于主键。

  • 任意两行的主键值都不相同
  • 每行都具有一个主键值(即列中不允许NULL值)
  • 包含主键值的列从不修改或更新。(大部分数据库不允许这样做)
  • 主键值不能重用。如果从表中删除某一行,其主键值不会分配给新行。

创建主键:

CREATE TABLE `blog_blogtagmodel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag` varchar(50) NOT NULL,
  `add_time` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_blogtagmodel_tag_a1c65a4c_uniq` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

 通过上方的PRIMARY KEY (`id`)指定主键,或者在定义字段时创建主键,`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,。

还可以通过下列语句指定主键

ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);

说明:SQLite不允许使用ALTER来创建主键。

1.2 外键

外键是表中的一列,其值必须列在另一个表的主键中。

在Orders的顾客ID列上定义一个外键,因此该列只能接受Customers表的主键值,下面是定义外键的方法

CREATE TABLE Orders
(
 order_num  INTEGER NOT NULL PRIMARY KEY,
 order_date DATETIME NOT NULL,
 cust_id    CHAR(10) NOT NULL REFERENCES Customers(cust_id)
);

其中的表定义使用了REFERENCES关键字,它表示cust_id中的任何值都必须是Customers表的cust_id中的值。

也可以使用ALTER来创建

ALTER TABLE Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

1.3 唯一约束

唯一约束用来保证一列(或一组列)中的数据是唯一的,类似于主键,但是又与主键存在区别。区别如下:

  • 表可包含多个唯一约束,但每个表只允许一个主键
  • 唯一约束列可包含NULL值
  • 唯一约束可修改或更新
  • 唯一约束列的值可重复使用
  • 与主键不一样,唯一约束列不能用来定义主键

1.4 检查约束

检查约束用来保证一列中的数据满足一组指定的条件。检查约束的常见用途有以下几点:

  • 检查最小或最大值。例如,防止0个物品的订单
  • 指定范围。例如,保证发货日期大于等于今天的日期,但不超过今天起一年后的日期
  • 只允许特定的值,如,性别字段只允许M或F

下面例子对OrderItems表施加检查约束,它保证所有物品的数量大于0

CREATE TABLE OrderItems
(
 order_num INTEGER NOT NULL,
 order_item INTERGER NOT NULL
 quantity INTEGER NOT NULL CHECK (quantity > 0)
)

 检查名为gender的列只包含M或F

ALTER CONSTRAINT CHECK (gender LIKE '[MF]');

2.索引

索引用来排序数据以加快搜索和排序操作的速度。你可以把它想象成一本书。

索引的优势和问题

  • 索引改善了检索操作的性能,但降低了数据插入、修改和删除的性能,在执行这些操作时,数据库必须动态地更新索引
  • 索引数据可能要占用大量的存储空间
  • 并非所有的数据都适合做索引。取值不多的数据不如具有更多可能值的数据,能通过索引得到那么多的好处
  • 索引用于数据过滤和排序。如果你的操作大量涉及到这些,可能适合建立索引
  • 可以在索引中定义多个列(如州+城市)。这样的索引仅在以州加城市的顺序排序时有用。如果按城市排序,这无用。

创建索引

CREATE INDEX prod_name_id ON Products (prod_name);

 3.触发器

触发器是特殊的存储过程,它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT,UPDATE,DELETE操作(或组合)相关联。

与存储过程不一样(存储过程只是简单的存储SQL语句),触发器与单个表相关联。与Orders表上的INSERT操作相关联的触发器只在Orders表中插入行时执行。类似地,Customers表上地INSERT和UPDATE操作地触发器只在表上出现这些操作时执行。

触发器内地代码具有以下数据访问权:

  • INSERT操作中的所有新数据
  • UPDATE操作中的所有新数据
  • DELETE操作中删除的数据

触发器常见用途

  • 保证数据一致性。例如,在INSERT或UPDATE操作中将州名转为大写
  • 基于某个表的变动在其他表上执行活动。例如,每当更新或删除一行时将审计跟踪记录写入某个日志表
  • 进行额外的验证并根据需要回退数据。例如,保证某个顾客的可用资金不超限定,如果超额,则阻塞插入
  • 计算列的值或更新时间戳

下列例子是创建一个触发器,她对所有INSERT和UPDATE操作,将Customers表中的cust_state列转换为大写

这是本例子的SQL Server版本

CREATE TGIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;

 Oracle和PostgreSQL

CREATE TGIGGER customer_state
AFTER INSERT OR UPDATE
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id
END;

MYSQL中的触发器

在修改本表时,更新addTime字段的时间

CREATE TRIGGER update_time BEFORE UPDATE ON blog_blogtype FOR EACH ROW
BEGIN
	SET new.addTime = NOW();
END
UPDATE blog_blogtype SET category = 'JS' WHERE id = 1;

更详细的介绍触发器,参考https://blog.csdn.net/babycan5/article/details/82789099

标签:数据库

文章评论

评论列表

已有0条评论