SQL语句 05进阶篇 索引、子查询、ALTER TABLE 命令、处理重复数据、使用视图

SQL 索引

索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。简单说来，索引就是指向表中数据的指针。

索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。

CREATE INDEX 命令：
CREATE INDEX命令的基本语法如下：

CREATE INDEX index_name ON table_name;

• 单列索引：

单列索引基于单一的字段创建，其基本语法如下所示：

CREATE INDEX index_name
ON table_name (column_name);

• 唯一索引：

唯一索引不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值。其基本语法如下所示：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

• 聚簇索引：

聚簇索引在表中两个或更多的列的基础上建立。其基本语法如下所示：

CREATE INDEX index_name
on table_name (column1, column2);

创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。

如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。

• 隐式索引：

隐式索引由数据库服务器在创建某些对象的时候自动生成。例如，对于主键约束和唯一约束，数据库服务器就会自动创建索引。
　
DROP INDEX 命令：

索引可以用 SQL DROP 命令删除。删除索引时应当特别小心，数据库的性能可能会因此而降低或者提高。

其基本语法如下：

DROP INDEX table_name.index_name;

什么时候应当避免使用索引？
　尽管创建索引的目的是提升数据库的性能，但是还是有一些情况应当避免使用索引。下面几条指导原则给出了何时应当重新考虑是否使用索引：

• 小的数据表不应当使用索引；
• 需要频繁进行大批量的更新或者插入操作的表；
• 如果列中包含大数或者 NULL 值，不宜创建索引；
• 频繁操作的列不宜创建索引。

SQL 子查询

子查询（Sub Query），也可以称作嵌套查询（Nested Query），是一种嵌套在其他 SQL 查询的 WHERE 子句中的查询。

子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。

子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。

使用子查询必须遵循以下几个规则：

• 子查询必须括在圆括号中。
• 子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较。
• 子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用。
• 返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。
• SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。
• 子查询不能直接用在集合函数中。
• BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中。

SELECT 语句中的子查询

通常情况下子查询都与 SELECT 语句一起使用，其基本语法如下所示：

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

SQL> SELECT * 
     FROM CUSTOMERS 
     WHERE ID IN (SELECT ID 
                  FROM CUSTOMERS 
                  WHERE SALARY > 4500) ;

INSERT 语句中的子查询：

子查询还可以用在 INSERT 语句中。INSERT 语句可以将子查询返回的数据插入到其他表中。子查询中选取的数据可以被任何字符、日期或者数值函数所修饰。

其基本语法如下所示：

INSERT INTO table_name [ (column1 [, column2 ]) ]
           SELECT [ *|column1 [, column2 ]
           FROM table1 [, table2 ]
           [ WHERE VALUE OPERATOR ]

UPDATE 语句中的子查询：

子查询可以用在 UPDATE 语句中。当子查询同 UPDATE 一起使用的时候，既可以更新单个列，也可更新多个列。

其基本语法如下：

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

DELETE 语句中的子查询：

如同前面提到的其他语句一样，子查询还可以同 DELETE 语句一起使用。

其基本语法如下所示：

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

SQL ALTER TABLE 命令

SQL ALTER TABLE 命令用于添加、删除或者更改现有数据表中的列。

你还可以用 ALTER TABLE 命令来添加或者删除现有数据表上的约束。

• 使用 ALTER TABLE 在现有的数据表中添加新列的基本语法如下：

ALTER TABLE table_name ADD column_name datatype;

• 使用 ALTER TABLE 在现有的数据表中删除列的基本语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

• 使用 ALTER TABLE 更改现有的数据表中列的数据类型的基本语法如下：

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

• 使用 ALTER TABLE 给某列添加 NOT NULL 约束 的基本语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

• 使用 ALTER TABLE 给数据表添加 唯一约束 的基本语法如下：

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

• 使用 ALTER TABLE 给数据表添加 CHECK 约束 的基本语法如下：

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

• 使用 ALTER TABLE 给数据表添加 主键约束 的基本语法如下：

ALTER TABLE table_name 
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

• 使用 ALTER TABLE 从数据表中 删除约束 的基本语法如下：

ALTER TABLE table_name 
DROP CONSTRAINT MyUniqueConstraint;

如果你在使用 MySQL，代码应当如下：

ALTER TABLE table_name 
DROP INDEX MyUniqueConstraint;

• 使用 ALTER TABLE 从数据表中 删除主键约束 的基本语法如下：

ALTER TABLE table_name 
DROP CONSTRAINT MyPrimaryKey;

如果你在使用 MySQL，代码应当如下：

ALTER TABLE table_name 
DROP PRIMARY KEY;

ALTER TABLE CUSTOMERS ADD SEX char(1);

ALTER TABLE CUSTOMERS DROP COLUMN SEX;

SQL 处理重复数据

有时候，数据表中会存在相同的记录。在获取表中记录时，相较于取得重复记录来说，取得唯一的记录显然更有意义。

我们之前讨论过的 SQL DISTINCT 关键字，与 SELECT 语句一起使用可以时，可以达到消除所有重复记录，只返回唯一记录的目的。

利用 DISTINCT 关键字来消除重复记录的基本语法如下所示：

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

SQL 使用视图

视图无非就是存储在数据库中并具有名字的 SQL 语句，或者说是以预定义的 SQL 查询的形式存在的数据表的成分。

视图可以包含表中的所有列，或者仅包含选定的列。视图可以创建自一个或者多个表，这取决于创建该视图的 SQL 语句的写法。

视图，一种虚拟的表，允许用户执行以下操作：

• 以用户或者某些类型的用户感觉自然或者直观的方式来组织数据；
• 限制对数据的访问，从而使得用户仅能够看到或者修改（某些情况下）他们需要的数据；
• 从多个表中汇总数据，以产生报表。

创建视图
　在 SQL 中，视图是基于 SQL 语句的结果集的可视化表。
　数据库视图由 CREATE VIEW 语句创建。视图可以创建自单个表、多个表或者其他视图。

视图中的字段是一个或多个数据库中真实表中的字段。
　在使用时视图可以被视为一个“虚拟表”。
　要创建视图的话，用户必须有适当的系统权限。具体需要何种权限随数据库系统实现的不同而不同。

CREATE VIEW 语句的基本语法如下所示：

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

和普通的 SQL SELECT 查询一样，你可以在上面的 SELECT 语句中包含多个数据表。

注释：视图总是显示最新数据！每当用户查询视图时，数据库引擎就使用视图的 SQL 语句重新构建数据。

WITH CHECK OPTION

WITH CHECK OPTION 是 CREATE VIEW 语句的一个可选项。

WITH CHECK OPTION 用于保证所有的 UPDATE 和 INSERT 语句都满足视图定义中的条件。

如果不能满足这些条件，UPDATE 或 INSERT 就会返回错误。

下面的例子创建的也是 CUSTOMERS_VIEW 视图，不过这次 WITH CHECK OPTION 是打开的：

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

这里 WITH CHECK OPTION 使得视图拒绝任何 AGE 字段为 NULL 的条目，因为视图的定义中，AGE 字段不能为空。

更新视图

在SQL视图上也可以使用修改数据的DML语句，如 INSERT、UPDATE和DELETE。

视图可以在特定的情况下更新：

• SELECT 子句不能包含 DISTINCT 关键字
• SELECT 子句不能包含任何汇总函数（summary functions）
• SELECT 子句不能包含任何集合函数（set functions）
• SELECT 子句不能包含任何集合运算符（set operators）
• SELECT 子句不能包含 ORDER BY 子句
• 视图不能包含连接操作符
• 视图不能包含伪列或表达式
• FROM 子句中不能有多个数据表
• WHERE 子句不能包含子查询（subquery）
• 查询语句中不能有 GROUP BY 或者 HAVING
• 计算得出的列不能更新
• 视图必须包含原始数据表中所有的 NOT NULL 列，从而使 INSERT 查询生效。

如果视图满足以上所有的条件，该视图就可以被更新。下面的例子中，Ramesh 的年龄被更新了：

SQL > UPDATE CUSTOMERS_VIEW
      SET AGE = 35
      WHERE name='Ramesh';

向视图中插入新行

可以向视图中插入新行，其规则同（使用 UPDATE 命令）更新视图所遵循的规则相同。

这里我们不能向 CUSTOMERS_VIEW 视图中添加新行，因为该视图没有包含原始数据表中所有 NOT NULL 的列。否则的话，你就可以像在数据表中插入新行一样，向视图中插入新行。

句法：

INSERT INTO view_name
VALUES (value1, value2, value3, ...);

删除视图中的行

视图中的数据行可以被删除。删除数据行与更新视图和向视图中插入新行遵循相同的规则。

下面的例子将删除 CUSTOMERS_VIEW 视图中 AGE=22 的数据行：

SQL > DELETE FROM CUSTOMERS_VIEW
      WHERE age = 22;

删除视图

很明显，当我们不再需要某个视图的时候，需要有一种方式可以让我们将其删除。删除视图的语法非常简单，如下所示：

DROP VIEW view_name;