SQL语言(4)

(1)SQL CREATE INDEX 语句讲解

CREATE INDEX 语句用于在表中创建索引。

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

您可以在表中创建索引，以便更加快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

语法:

a.在表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_nameON table_name (column_name)

注释："column_name" 规定需要索引的列。

b.在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_nameON table_name (column_name)

实例:

a.本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列：

CREATE INDEX PersonIndexON Person (LastName) 

b.如果希望以降序索引某个列中的值，可以在列名称之后添加保留字DESC：

CREATE INDEX PersonIndexON Person (LastName DESC) 

c.假如希望索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndexON Person (LastName, FirstName)

(2)SQL 撤销索引、表以及数据库

通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

语法:

a.使用 DROP INDEX 命令删除表格中的索引。

用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:

DROP INDEX index_name ON table_name

用于 MS SQL Server 的语法:

DROP INDEX table_name.index_name

用于 IBM DB2 和 Oracle 语法:

DROP INDEX index_name

用于 MySQL 的语法:

ALTER TABLE table_name DROP INDEX index_name

b.DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：

DROP TABLE 表名称
SQL DROP DATABASE 语句
DROP DATABASE 语句用于删除数据库：
DROP DATABASE 数据库名称
SQL TRUNCATE TABLE 语句
如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？
请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：
TRUNCATE TABLE 表名称
(3)SQL ALTER TABLE 语句讲解
用于在已有的表中添加、修改或删除列。
语法:
a.如需在表中添加列，请使用下列语法:
ALTER TABLE table_nameADD column_name datatype
b.要删除表中的列，请使用下列语法：
ALTER TABLE table_name DROP COLUMN column_name
注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。
c.要改变表中列的数据类型，请使用下列语法：
ALTER TABLE table_nameALTER COLUMN column_name datatype
Persons 表:
IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing

实例:

a.在表 "Persons" 中添加一个名为 "Birthday" 的新列。

ALTER TABLE PersonsADD Birthday date

请注意，新列 "Birthday" 的类型是 date，可以存放日期。数据类型规定列中可以存放的数据的类型。

新的 "Persons" 表类似这样：

IdLastNameFirstNameAddressCityBirthday1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing

b.改变数据类型实例

现在改变 "Persons" 表中 "Birthday" 列的数据类型。

使用下列 SQL 语句：

ALTER TABLE PersonsALTER COLUMN Birthday year

请注意，"Birthday" 列的数据类型是 year，可以存放 2 位或 4 位格式的年份。

c.DROP COLUMN 实例

接下来，我们删除 "Person" 表中的 "Birthday" 列：

ALTER TABLE PersonDROP COLUMN Birthday

Persons 表会成为这样:

IdLastNameFirstNameAddressCity1AdamsJohnOxford StreetLondon2BushGeorgeFifth AvenueNew York3CarterThomasChangan StreetBeijing

(4)SQL AUTO INCREMENT 字段讲解

在每次插入新记录时，自动地创建主键字段的值。

a.用于 MySQL 的语法：

SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：

CREATE TABLE Persons(P_Id int NOT NULL AUTO_INCREMENT,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id))

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

要在 "Persons" 表中插入新记录，不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)VALUES ('Bill','Gates')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。

b.用于 SQL Server 的语法

把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：

CREATE TABLE Persons(P_Id int PRIMARY KEY IDENTITY,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

MS SQL 使用 IDENTITY 关键字来执行 auto-increment 任务。

默认地，IDENTITY 的开始值是 1，每条新记录递增 1。

要规定 "P_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)

要在 "Persons" 表中插入新记录，不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)VALUES ('Bill','Gates')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。

c.用于 Access 的语法

把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：

CREATE TABLE Persons(P_Id int PRIMARY KEY AUTOINCREMENT,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTOINCREMENT 的开始值是 1，每条新记录递增 1。

要规定 "P_Id" 列以 20 起始且递增 10，把 autoincrement 改为 AUTOINCREMENT(20,10)

要在 "Persons" 表中插入新记录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)VALUES ('Bill','Gates')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 会被赋予一个唯一的值。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。

d.用于 Oracle 的语法

在 Oracle 中，代码稍微复杂一点。

必须通过 sequence 对创建 auto-increment 字段（该对象生成数字序列）。

使用下面的 CREATE SEQUENCE 语法：

CREATE SEQUENCE seq_personMINVALUE 1START WITH 1INCREMENT BY 1CACHE 10

上面的代码创建名为 seq_person 的序列对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。CACHE 选项规定了为了提高访问速度要存储多少个序列值。

要在 "Persons" 表中插入新记录，必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）：

INSERT INTO Persons (P_Id,FirstName,LastName)VALUES (seq_person.nextval,'Lars','Monsen')

上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"P_Id" 的赋值是来自 seq_person 序列的下一个数字。"FirstName" 会被设置为 "Bill"，"LastName" 列会被设置为 "Gates"。

(5)SQL VIEW（视图）讲解

什么是视图？

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

SQL CREATE VIEW 语法

CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

实例:

a.本数据库 Northwind 拥有一些被默认安装的视图。视图 "Current Product List" 从 Products 表列出所有正在使用的产品。

CREATE VIEW [Current Product List]ASSELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=No

查询上面这个视图：

SELECT * FROM [Current Product List]

b.Northwind 样本数据库的另一个视图会选取 Products 表中所有单位价格高于平均单位价格的产品：

CREATE VIEW [Products Above Average Price] ASSELECT ProductName,UnitPriceFROM ProductsWHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) 

查询上面这个视图：

SELECT * FROM [Products Above Average Price]

c.另一个来自 Northwind 数据库的视图实例会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 "Product Sales for 1997" 的视图那里选取数据：

CREATE VIEW [Category Sales For 1997] ASSELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySalesFROM [Product Sales for 1997]GROUP BY CategoryName 

查询上面这个视图：

SELECT * FROM [Category Sales For 1997]

d.向查询添加条件。现在，查看 "Beverages" 类的全部销量：

SELECT * FROM [Category Sales For 1997]WHERE CategoryName='Beverages'SQL 更新视图

使用下面的语法来更新视图：

SQL CREATE OR REPLACE VIEW SyntaxCREATE OR REPLACE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

现在，向 "Current Product List" 视图添加 "Category" 列。我们将通过下列 SQL 更新视图：

CREATE VIEW [Current Product List] ASSELECT ProductID,ProductName,CategoryFROM ProductsWHERE Discontinued=No

SQL 撤销视图:通过 DROP VIEW 命令来删除视图。

SQL DROP VIEW SyntaxDROP VIEW view_name