SQL基础-理解高级SQL特性

最新推荐文章于 2024-05-31 15:50:21 发布

木刀的博客

最新推荐文章于 2024-05-31 15:50:21 发布

阅读量170

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/EmperorWu/article/details/103616049

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

在本节课中，介绍几种SQL中的高级数据操作特性:约束、索引和触发器

1 理解约束

关系数据库将数据存储为多个表，每个表存储相关数据。键用于创建从一个表到另一个表的引用(因此称为引用完整性)。
要使关系数据库设计正常工作，需要确保只有有效的数据被插入到表中。
尽管可以在插入新行之前执行检查(在另一个表上执行SELECT以确保值是有效的和存在的)，但出于以下原因，最好避免这种做法：

如果数据库完整性规则在客户端级别强制执行，那么每个客户端都必须强制执行这些规则，而不可避免的是，有些客户端不会强制执行这些规则。
必须对更新和删除操作执行这些规则。
执行客户端检查是一个耗时的过程。让DBMS（数据库管理系统）为您进行检查要有效得多。

约束：数据库输入如何被插入或修改的规则。
dbms通过对数据库表施加约束来实现引用完整性。大多数约束是在表定义中定义的。

1.1 主键

主键是一种特殊的约束，用于确保列(或一组列)中的值是惟一的且永不更改，换句话说，表中的一个列(或多个列)的值惟一地标识表中的每一行。这有助于直接操作各个行并与之交互。如果没有主键，就很难安全地使用更新或删除特定的行而不影响其他行。
表中的任何列都可以作为主键，只要它满足以下条件：

没有两行具有相同的主键值。
每一行必须有一个主键值。(列不能为空值)
包含主键值的列永远不能修改或更新。(大多数dbms不支持这样做，但是如果您的dbms支持这样做，那么就不要这样做!)
不能重用主键值。如果从表中删除了一行，则不能将其主键分配给任何新行。
定义主键的一种方法是创建它们，如下所示:

CREATE TABLE Vendors
(
	vend_id CHAR(10) NOT NULL PRIMARY KEY,
	vend_name CHAR(50) NOT NULL,
	vend_address CHAR(50) NULL,
	vend_city CHAR(50) NULL,
	vend_state CHAR(5) NULL,
	vend_zip CHAR(10) NULL
	vend_country CHAR(50) NULL
);

在上面的例子中，关键字主键被添加到表定义中，这样vend_id就成为了主键。

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

在这里，相同的列被定义为主键，但是使用了CONSTRAINT语法。此语法可用于CREATE TABLE和ALTER TABLE语句。

1.2 外键

外键是表中的列，其值必须在另一个表的主键中列出。外键是确保引用完整性的一个极其重要的部分。为了理解外键，我们来看一个示例。
Orders表包含系统中输入的每个订单的单行。客户信息存储在客户表中。订单中的订单通过customer ID绑定到Customers表中的特定行。customer ID是customers表中的主键;每个customer都有唯一的ID，订单号是Orders表中的主键;每个订单都有唯一的编号。
Orders表中的customer ID列中的值不一定是惟一的。如果一个客户有多个订单，那么就会有多个具有相同客户ID的行(尽管每个订单号不同)。同时，在客户ID列中唯一有效的值是客户表中客户的ID。
这就是外键的作用。在我们的示例中，在订单中的客户ID列上定义了一个外键，因此该列只能接受客户表主键中的值。

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 TABLE语句中使用CONSTRAINT语法也可以完成相同的工作:

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

外键可以帮助防止意外删除。定义了外键之后，DBMS不允许删除其他表中具有相关行的行。例如，不允许删除具有关联订单的客户。删除该客户的唯一方法是首先删除相关订单(这意味着删除相关订单项)。因为外键需要这样有条理的删除，所以可以帮助防止意外删除数据。
然而，一些dbms支持一个称为级联删除的特性。如果启用，当从表中删除一行时，此功能将删除所有相关数据。例如，如果启用了级联删除，并且从客户表中删除了客户，则会自动删除任何相关的订单行。

CASCADE:删除或更新父表中的行，并自动删除或更新子表中匹配的行。支持删除级联和更新级联。在两个表之间，不要定义几个ON UPDATE CASCADE子句，它们作用于父表或子表中的同一列。
如果在一个外键关系中的两个表上定义了一个外键子句，使两个表都成为父级和子级，则为其中一个定义了一个on UPDATE CASCADE或on DELETE CASCADE子句,必须为另一个定义外键子句，以便级联操作成功。
SET NULL:删除或更新父表中的行，并将子表中的一个或多个外键列设置为NULL。同时支持删除集空和更新集空子句。
如果指定了SET NULL操作，请确保没有将子表中的列声明为not NULL。
RESTRICT:拒绝父表的删除或更新操作。指定RESTRICT(或NO ACTION)与省略ON DELETE或ON UPDATE子句相同。
NO ACTION:来自标准SQL的关键字。在MySQL中，相当于 RESTRICT。
SET DEFAULT:该操作可以被MySQL解析器识别，但是InnoDB和NDB都拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。
对于未指定的ON DELETE或ON UPDATE，默认操作总是NO ACTION。

1.3 唯一约束

惟一约束用于确保一个列(或一组列)中的所有数据都是惟一的。它们与主键相似，但有一些重要的区别:

一个表可以包含多个惟一约束，但每个表只允许一个主键。
唯一约束列可以包含空值。
可以修改或更新唯一约束列。
唯一约束列值可以重用。
与主键不同，惟一约束不能用于定义外键。

惟一约束的语法类似于其他约束的语法。要么UNIQUE关键字在表定义中定义，要么使用CONSTRAINT定义。

1.4 检查约束

检查约束用于确保列(或一组列)中的数据满足您指定的一组条件。

检查最小值或最大值—例如，防止订单为0(zero)项(即使0是一个有效的数字)
指定范围——例如，确保发货日期大于或等于今天的日期，且不大于一年后的日期
只允许特定的值—例如，在性别字段中只允许M或F
换句话说，datatypes限制了可以存储在列中的数据类型。检查约束在该数据类型中设置了更多的限制，这些限制对于确保插入到数据库中的数据正是您想要的数据非常有用。DBMS本身会拒绝任何无效的东西，而不是依靠客户机应用程序或用户来处理它。
下面的示例将检查约束应用于OrderItems表，以确保所有项的数量都大于0

CREATE TABLE OrderItems
(
	order_num INTEGER NOT NULL,
	order_item INTEGER NOT NULL,
	prod_id CHAR(10) NOT NULL,
	quantity INTEGER NOT NULL CHECK (quantity > 0),
	item_price MONEY NOT NULL
);

有了这个约束，插入(或更新)的任何行都将被检查，以确保数量大于0。
要检查名为gender的列是否只包含M或F，可以在ALTER TABLE语句中执行以下操作:

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

有些dbms允许您定义自己的数据类型。这些本质上是简单的数据类型，定义了检查约束(或其他约束)。例如，您可以定义自己的数据类型，称为gender，它是一个单字符文本数据类型，带有一个检查约束，将其值限制为M或F(也可能为NULL)。然后可以在表定义中使用这种数据类型。自定义数据类型的优点是约束只需要应用一次(在数据类型定义中)，并且每次使用数据类型时都会自动应用约束。检查你的DBM

2 理解索引

索引用于对数据进行逻辑排序，以提高搜索和排序操作的速度。
假设您希望查找本书中出现的所有单词datatype。最简单的方法是翻到第1页，扫描每一页的每一行，寻找匹配的内容。虽然这是可行的，但显然不是一个可行的解决方案。扫描几页文本可能是可行的，但以这种方式扫描整本书就不可行了。随着要搜索的文本数量的增加，查明所需数据所需的时间也会增加。

这就是书籍有索引的原因。索引是一个按字母顺序排列的单词列表，其中的单词在书中有相应的位置。要搜索数据类型，可以在索引中找到该单词，以确定它出现在什么页面上。然后，你翻到那些特定的页面去寻找你的匹配项。

什么使索引起作用?简单地说，它的排序是正确的。在一本书中找到单词的困难不在于必须搜索的内容的数量;更确切地说，是内容没有按单词排序。如果内容像字典一样排序，就不需要索引(这就是字典没有索引的原因)。

数据库索引的工作方式大致相同。主键数据总是排序的;这就是DBMS为你做的事情。因此，通过主键检索特定的行始终是一种快速而有效的操作。

但是，在其他列中搜索值通常效率不高。例如，如果您想检索居住在某个国家下的所有客户，该怎么办?由于表不是按状态排序的，DBMS必须读取表中的每一行(从第一行开始)查找匹配项，就像在不使用索引的情况下在书中查找单词一样。
解决方案是使用索引。您可以在一个或多个列上定义一个索引，以便DBMS保留一个排序的内容列表供自己使用。在定义了索引之后，DBMS使用索引的方式与使用图书索引的方式非常相似。它搜索已排序的索引以查找任何匹配项的位置，然后检索那些特定的行。

但是在匆忙创建几十个索引之前，请记住以下几点：

索引提高了检索操作的性能，但是降低了数据插入、修改和删除的性能。执行这些操作时，DBMS必须动态更新索引。
索引数据会占用大量的存储空间
并不是所有的数据都适合索引。某些数据不够唯一，可取的值比较有限（比如州名），不适合建索引。
索引用于数据过滤和数据排序。如果您经常按照特定的顺序对数据进行排序，那么这些数据可以作为索引。
可以在一个索引中定义多个列(例如，State + City)。这样的索引只有在数据按州和城市的顺序排序时才有用。(如果你想按城市排序，这个索引没有任何用处。)

对于应该索引什么以及什么时候索引，没有硬性规定。大多数dbms提供可以用来确定索引有效性的实用程序，您应该经常使用它们。
索引是用CREATE INDEX语句创建的(不同DBMS之间的差异很大)。

CREATE INDEX prod_name_ind ON PRODUCTS (prod_name);

每个索引必须唯一命名。在这里，名称prod_name_ind是在关键字CREATE INDEX之后定义的。ON用于指定被索引的表，索引中包含的列(本例中只有一个列)在表名后面的括号中指定。
索引有效性随着表数据的添加或更改而变化。许多数据库管理员发现，经过几个月的数据操作之后，曾经理想的索引集可能不再那么理想了。定期重新访问索引并根据需要对它们进行调优始终是一个好主意。

3 理解触发器

触发器是在特定数据库活动发生时自动执行的特殊存储过程。触发器可能与插入、更新和删除特定表上的操作(或其任何组合)关联。
与存储过程(仅存储SQL语句)不同，触发器绑定到单个表。只有在将行插入Orders表时，才会执行与Orders表上的插入操作相关的触发器。类似地，只有在客户表上执行插入和更新操作时，才会执行这些特定操作。
在触发器中，您的代码可以访问以下内容：

插入操作中的所有新数据
更新操作中的所有新数据和旧数据
删除操作中已删除的数据

根据使用的DBMS，可以在执行指定操作之前或之后执行触发器。
以下是触发器的一些常见用法：

确保数据一致性——例如，在插入或更新操作期间将所有州名转换为大写
根据对表的更改对其他表执行操作——例如，在每次更新或删除一行时向日志表写入审计跟踪记录
执行额外的验证并在需要时回滚数据——例如，确保客户的可用信用额度没有被超出，如果超出则阻塞插入

4 数据库安全

对于一个组织来说，没有什么比它的数据更有价值的了，而且数据应该始终受到保护，以免被小偷或偶然的浏览器窃取。当然，同时需要访问数据的用户必须能够访问数据，因此大多数dbms为管理员提供了授予或限制访问数据的机制。

任何安全系统的基础都是用户授权和身份验证。这是验证用户的过程，以确保他是他所说的那个人，并允许他执行他试图执行的操作。有些dbms为此与操作系统安全性集成，有些则维护自己的用户和密码列表，还有一些与外部目录服务服务器集成。

一些操作通常是安全的

访问数据库管理特性(创建表、修改或删除现有表，等等)
访问特定的数据库或表
访问的类型(只读、对特定列的访问，等等)
仅通过视图或存储过程访问表
创建多层次的安全，从而允许不同程度的访问和控制基于登录
限制管理用户帐户的能力
安全性是通过SQL GRANT和REVOKE语句来管理的，尽管大多数语句都是这样
dbms提供在内部使用GRANT和REVOKE语句的交互式管理实用程序。

摘录自书籍《Sams Teach Yourself SQL in 10 Minutes, Fourth Edition》