sql——创建视图和索引

一、视图

1、使用视图

创建视图：给BILLS表创建视图。创建的视图实际上是一个虚表。

CREATE VIEW DEBTS AS 
SELECT * FROM BILLS;

可以选择特定的行进行新视图的创建。

CREATE VIEW CREDITCARD_DEBTS AS
SELECT * FROM DEBTS
WHERE ACCOUNT_ID = 4;

SELECT * FROM CREDITCARD_DEBTS;

可以从表中挑选特定的列创建视图。

---

2、列的重命名

视图继承了已有列的名字，此外视图还可以有自己的名字。

例如：把 COMPANY 中的 ADDRESS CITY 和 STATE 字段组合起来并打印到信封。

CREATE VIEW ENVELOPE(COMPANY, MAILING_ADDRESS) AS
SELECT NAME, ADDRESS + " " + CITY + ", " + STATE
FROM COMPANY;

select * from ENVELOPE；

注：当在视图中使用 SQL 的计算功能时，SQL 会要求你给出一个虚字段的名字， 像 COUNT(*)或 AVG(PAYMENT)是不能作为名字的。

3、sql对视图的处理过程

视图应用于经常运行一个查询。

例如：你需要例行公事地将 BILLS 表与 BANK_ACCOUNT 表进行归并以得到支付信息

create table BANK_ACCOUNTS (
ACCOUNT_ID NUMBER NOT NULL,
TYPE CHAR(30),
BALANCE NUMBER,
BANK CHAR(30));

--使用sql语句进行
SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE
BLANCE， BANK_ACCOUNTS.BANK BANK FROM BILLS, BANK_ACCOUNTS
WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;

--使用视图进行
CREATE VIEW BILLS_DUE (NAME, AMOUNT, ACCT_BALANCE, BANK) AS
SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE
BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS
WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID；

如果对BILLS_DUE视图执行查询操作。

SELECT * FROM BILLS_DUE
WHERE ACCT_BALANCE > 500；

--因为BILLS_DUE是一个视图，不是真实的表。找不到可能会从系统表中发现BILLS_DUE是一个视图，会对视图进行诠释，语句为：
SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE,
BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS
WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID
AND BANK_ACCOUNTS.BALANCE > 500;

4、在select语句中使用约束

4.1  在视图中修改数据

更新 BILLS 表中的10%的费用。

create view LATE_PAYMENT as
select * from BILLS;

update LATE_PAYMENT
set AMOUNT=NMOUNT*1.10;

select *from LATE_PAYMENT;

从视图中删除一行：

delete from LATE_PAYMENT
where ACCOUNT_ID=4;

update函数

例如：为BILLS表中所有的NEW_TOTAL中对于100的增加10。

update LATE_PAYMENT
set NEW_TOTAL=new_TOTAL+10;
where NEW_TOTAL>100;

select * from LATE_PAYMENNT;

4.2  在视图中修改数据的几个问题

4.2.1  视图与安全性

数据库系统的用户通常会根据使用的数据库进行分组：

数据库管理员（database adminstrators)

数据库开发人员（database developers）

数据录入人员（data entry personnel)

大众用户

不同的组在使用数据库时有着不同的权限，系统管理员有系统的完全控制权限，包括更新、插入、删除、修改。大众则只有数据库使用select语句的权限。

例如：希望用户访问BILLS表中的字段，需要创建一个名字叫BILLS_NAME的视图。

CREATE VIEW BILLS_NAME AS SELECT NAME FROM BILLS；

4.2.2  在单位换算中使用视图

视图在你提供给用户的数据与数据库中的真实数据不同时也相当有用 。

例如：如果 AMOUNT 字段实际上存储于美国，加拿大的用户不想频繁地进行美元与加拿大元之间的转换工作。那么你可以创建一个叫 CANADA_BILLS 的视图。

CREATE VIEW CANADIAN_BILLS (NAME, CAN_AMOUNT) AS
SELECT NAME, AMOUNT / 1.10
FROM BILLS；

select * from CANADIAN_BILLS；

4.2.3 在视图中使用简单的结构化复合查询

视图在你需要按次序运行一系列查询以后得到某个结果的情况下也很有用。

例如：找出所有发给德克萨斯州的账单金额少于 50 美元的银行的名字。

可以将问题分解成两个问题：

1、得到所有发给得克萨斯州的账单。

2、找出账单中金额小于50美元的记录。

两个子问题的解决语句为：

CREATE TABLE BILLS1 AS
SELECT * FROM BILLS
WHERE AMOUNT < 50;

CREATE TABLE BILLS2 (NAME, AMOUNT, ACCOUNT_ID) AS
SELECT BILLS * FROM BILLS, COMPANY
WHERE BILLS.NAME = COMPANY.NAME AND COMPANY.STATE = "TX";

使用sql中的in子句来找出所有在 BILLS1中发往德州的账单，这个信息来创建一个名字叫 BILLS3 的视图：

CREATE VIEW BILLS3 AS
SELECT * FROM BILLS2 WHERE NAME IN
(SELECT * FROM BILLS1);

将上述的BANK_ACCOUNT表进行合并得到最初想要的结果：

CREATE VIEW BANKS_IN_TEXAS (BANK) AS
SELECT BANK_ACCOUNTS.BANK
FROM BANK_ACCOUNTS, BILLS3
WHERE BILLS3.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;

select * from BANK_IN_TEXAS;

4.2.4  删除视图语句

drop view view_name;

drop view命令会将所有与drop视图相关联的视图不能正常运行，一些数据库系统甚至会将所有与要drop的视图相关联的视图也要删除掉。

例如：删除BILLS视图,查询相关的视图BANKS_IN_TEXAS也会返回错误，

drop view BILLS;

select *from BANKS_IN_TEXAS;

删除视图不会对真实的表有任何影响，视图是一个虚的表。

二、索引

1、使用索引

SQL使用索引的原因是因为以下几种：

（1）在使用UNIQUE关键字时强制性地保证数据的完整性。

（2）可以容易地用索引字段或其他字段进行排序。

（3）提高查询的执行速度

2、什么是索引?

        索引是方便查询的一种手段，SQL采用树形结构存储和返回索引数据，用以指示的数据存储在树的最末端，每个节点中有一个指向其他节点的指针，节点左边的值只是它的双亲节点，节点右边的值则是孩子节点或叶子。

基本的索引语法：

2.1  创建索引

create index index_name;

例如：要对BILLS表中的ACCOUNTID字段创建索引，其CREATE INDEX语句如下：

select * from BILLS；
create INDEX ID_INDEX on BILLS（ACCOUNT_ID);
select * from BILLS;

`直至索引被drop index语句删除前，BILLS表是按照ACCOUNT_ID的顺序进行排序的。

2.2  删除索引

drop index index_name;

例如：删除刚刚创建的ACCOUNTID索引。

drop index ID_INDEX;

--查看BILLS存储结构
select * from BILLS;

现在BILLS表就是它原本的形态，使用索引不会对表中的物理存储造成影响。

注：使用select * from BILLS order by ACCOUNT_ID;的结果和和创建索引的结果相同，不同在于当使用order by子句时，每次运行它都要重新进行排序，当使用索引的时候，数据库会建立一个物理索引对象，在你每次运行查询时，都访问同一个索引。

注：当表被删除时，所有与表相关的索引都将会被删除。

2.3  使用索引的技巧

（1）对于小表来说，使用索引对于性能不会有任何提高。

（2）当你的索引列中有极多的不同的数据和空值时索引会使性能有极大的提高。

（3）当查询要返回的数据很少时索引可以优化你的查询（比较好的情况是少于全部数据的 25%），如果你要返回的数据很多时索引会加大系统开销。

（4）索引可以提高查询的速度，但会使得数据的更新操作变慢。进行大量的更新操作时，可以删除索引，在更新完成后进行索引的恢复。

（5）索引会占据数据库的空间，设计数据库可用空间时需要考虑索引占用的空间。

（6）对字段的索引已经对两个表进行了归并操作，可以提高归并的速度。

（7）对视图创建索引，在 SELECT 语句中对视图的数据进行排序。

（8）不要创建对经常需要更新或修改的字段创建索引，更新索引的开销会降低你所期望获得的性能。

（9）不要将索引与表存储在同一个驱动器上，分开存储会去掉访问的冲突从而使结果返回的更快。

2.4   复合索引

复合索引：对多个字段进行索引。虽然进行的是多个字段的索引，但是索引在物理结构上还是只有一个。

例如：对BILLS表的ACCOUNTID和AMOUNT字段创建索引。

create INDEX ID_CMPD_INDIX on BILLS(ACCOUNT_ID,AMOUNT);
select * from BILLS;

选择唯一值越多的列建立索引，可以达到更好的性能。

如果你经常在BILLS表中查询某一位人员，例如：SELECT * FROM BILLS WHERE NAME = "Cable TV Company";这时为了更好的查询性能，可以对name列创建一个索引。

create INDEX NAME_INDEX on BILLS(NAME);
//或者使用复合索引
create INDEX NAME_INDEX on BILLS(NAME,AMOUNT);

        如果你在查询条件中经常指定某一特定的列那个你可以创建这个列的索引。而当你的查询需要复合条件时你可以创建复合索引。

2.5  使用unique关键字创建索引

复合索引通常使用unique关键字防止有相同数据的多个记录多次出现。

ORACLE不支持UNIQUE 语法，它是用UNIQUE 完整性约束来达到内容唯一这一特性的。

使用unique关键字创建索引以后，如果想要添加一列相同的记录则将会报错。

使用DESC 操作创建的索引，它将告诉SQL 将索引降序排列。

例如：在 BILLS 表中创建一个索引以对 AMOUNT 字段进行降序排列。（一般默认为升序）

create INDEX DESC_AMOUNT on BILLS(AMOUNT DESC);

2.6  索引与归并

对 BILS 表与 BANK_ACCOUNT 表根据 ACCOUNT_ID 字段创建索引：

CREATE INDEX BILLS_INDEX ON BILLS(ACCOUNT_ID);

CREATE INDEX BILLS_INDEX2 ON BANK_ACCOUNTS(ACCOUNT_ID);

SELECT BILLS.NAME NAME, BILLS.AMOUNT AMOUNT,
BANK_ACCOUNTS.BALANCE ACCOUNT_BALANCE
FROM BILLS, BANK_ACCOUNTS
WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;

在每一个表中均对 ACCOUNT_ID 字段创建了索引以后，归并就可以更快地访问特定行的数据。

2.7  群集（簇）的使用

        当使用群集索引时，数据在表中的物理排列方式将会被修改，使用群集索引通常比传统的不使用群集的索引速度要快。但是许多数据库管理系统（如 Sybase 的 SQL Server），只允许一个表有一个群集索引，用于创建群集索引的字段常常是主关键字。

例如：对 BANK_ACCOUNT 的 ACCOUNT_ID 字段创建一个群集的，不重复的索引。

create unique clustered index id_index on BANK_ACCOUNTS(ACCOUNT_ID)
go

ORACLE 中群集的概念与此不同，当使用 ORACLE 关系数据库系统时，群集就是一 个像数据或表一样的对象，群集一般是存储了表的共有字段以提高对表的访问速度。

例如：ORACLE7 中创建群集。

CREATE CLUSTER [schema.]cluster (column datatype [,column datatype] ... )
[PCTUSED integer] [PCTFREE integer] [SIZE integer [K|M] ]
[INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace]
[STORAGE storage_clause] [!!under!!INDEX | [HASH IS column] HASHKEYS integer]

创建集群以后 ，之后创建的该表的群集的索引会被加入到集群中，然后把表也加在群集中。