1、数据库操作与管理语言Oracle SQL 数据库对象,本章目标,创建序列 创建索引 创建视图,序列Sequence,序列是一个数据库对象，用于生成一系列的整数。 学习内容包括 创建序列 查询序列的信息 使用序列 修改序列 删除序列,创建序列-1,可以使用CREATE SEQUENCE语句来创建序列，语法如下： CREATE SEQUENCE sequence_name START WITH start_num INCREMENT BY increment_num MAXVALUE maximum_num | NOMAXVALUE MINVALUE minimum_num | NOMINVALU
2、E CYCLE | NOCYCLE CACHE cache_num | NOCACHE ORDER | NOORDER ; 说明 sequence_name：序列的名称 start_num：序列开始的第一个整数，默认从1开始 increment_num：每次增长的数值 maximum_num：序列能够增长到的最大值 NOMAXVALUE：对于序列最大值，不指定最大值，对于升序最大值是1027，对于降序是-1,创建序列-2,可以使用CREATE SEQUENCE语句来创建序列，语法如下： CREATE SEQUENCE sequence_name START WITH start_num INC
3、REMENT BY increment_num MAXVALUE maximum_num | NOMAXVALUE MINVALUE minimum_num | NOMINVALUE CYCLE | NOCYCLE CACHE cache_num | NOCACHE ORDER | NOORDER ; 说明 minimum_num：序列中的最小值，该值必须比start_num小，比maximum_num也要小 NOMINVALUE：对于序列最小值，不指定最小值，对于升序最小值是1，对于降序最小值是-1026 CYCLE：对于序列的值达到最大值或最小值时的处理方法。如果是升序达到最大值了，那么下
4、一个生成的值将是最小值；如果是降序达到最小值时，那么下一个值将是最大值。 NOCYCLE：当序列的值达到最大或最小值时，序列将不再产生任何数值。默认是NOCYCLE。,创建序列-3,可以使用CREATE SEQUENCE语句来创建序列，语法如下： CREATE SEQUENCE sequence_name START WITH start_num INCREMENT BY increment_num MAXVALUE maximum_num | NOMAXVALUE MINVALUE minimum_num | NOMINVALUE CYCLE | NOCYCLE CACHE cache_nu
5、m | NOCACHE ORDER | NOORDER ; 说明 cache_num：保存在内存中缓存整数的数量，默认缓存的数量是20，最小的缓存数是2，最大的缓存数是CEIL(maximum_num -minimum_num)/ABS(increment_num) NOCACHE：表示没有缓存数字 ORDER：保证整数的请求顺序是按照生成顺序得到的。 NOORDER：不保证整数的请求顺序是按照生成顺序得到的。默认值。,案例,使用默认选项创建序列 CREATE SEQUENCE s_test; 说明 创建序列忽略了其他选项，那么将会使用默认值。 start_num和increment_num都
6、是1 指定相关选项创建序列 CREATE SEQUENCE s_test2 START WITH 10 INCREMENT BY 5 MINVALUE 10 MAXVALUE 20 CYCLE CACHE 2 ORDER; 创建降序的序列 CREATE SEQUENCE s_test3 START WITH 10 INCREMENT BY -1 MINVALUE 1 MAXVALUE 10 CYCLE CACHE 5;,查看序列的信息,可以通过查看user_sequences视图了解序列的信息 查看序列信息 SELECT * FROM user_sequences ORDER BY seque
7、nce_name; 查询结果如下图所示：,使用序列-1,创建序列之后，序列可以产生一系列的数值。可以通过currval和nextval来访问它们来获取当前值和下一个值。 例如 SELECT s_test.nextval FROM dual;在s_test序列中的第一个值是1，一旦序列被初始化之后，就可以从序列中获取当前值 SELECT s_test.currval FROM dual;,使用序列-2,s_test2序列的初始值是10，每次增加5，最大值为20 执行3次 SELECT s_test2.nextval FROM dual; 说明 由于设置了循环，当达到最大值20时， 又会重新从10
8、开始进行循环,使用序列填充主键-1,序列经常用于填充整型的主键列 例如，创建order_status2表 CREATE TABLE order_status2 (id INTEGER CONSTRAINT order_status2_pk PRIMARY KEY,status VARCHAR2(10),last_modified DATE DEFAULT SYSDATE ); 创建序列 CREATE SEQUENCE s_order_status2 NOCACHE; 插入数据 INSERT INTO order_status2(id, status, last_modified) VALUES
9、 (s_order_status2.nextval, PLACED, 01-1月-2006); INSERT INTO order_status2 (id, status, last_modified) VALUES (s_order_status2.nextval, PENDING, 01-2月-2006);,使用序列填充主键-2,查询插入结果 SELECT * FROM order_status2; 查询结果如图所示注意 当使用序列填充主键列时，通常应使用NOCACHE避免序列产生的数值发生遗漏（数值产生遗漏是因为缓存数值时，数据库被关闭）。然而，使用NOCACHE虽然会降低性能。但是如果
10、不介意主键值产生遗漏，可以再考虑使用CACHE。,修改序列,修改序列可以使用ALTER SEQUENCE语句进行修改。修改有一些限制如下： 初始值不能修改 序列的最小值不能大于当前序列的值 序列的最大值不能小于当前序列的值 例如 修改s_test序列的增长值为2 ALTER SEQUENCE s_test INCREMENT BY 2; 查看序列情况 SELECT s_test.currval FROM dual; SELECT s_test.nextval FROM dual;,删除序列,删除序列使用DROP SEQUENCE语句 例如，删除s_test3序列 DROP SEQUENCE s
11、_test3; 执行效果如下图所示：,索引介绍,索引是与表关联的可选结构。可以创建索引以加快对表执行SQL语句的速度。就像书的索引可以帮助我们更快速的查找信息一样，Oracle中的索引也提供了一种更快地访问表数据的途径。应该建索引列的特点： 1）在经常需要搜索的列上，可以加快搜索的速度； 2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排
12、序查询时间； 6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。不应该建索引列的特点： 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。 第二，对于那些只有很少数据值的列也不应该增加索引。 第三，对于那些定义为blob数据类型的列不应该增加索引。 第四，当修改性能远远大于检索性能时，不应该创建索引。,索引的特点：,优点： 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检
13、索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 缺点： 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。,索引,索引学习部分内容包括 创建B-Tree索引（默认类型） 创建函数索引 查看索引信息 修改索引 删除索引 创建位图索引,创建B-Tree索引,可以使用CREATE IN
14、DEX创建B-Tree索引，语法如下 CREATE UNIQUE INDEX index_name ON table_name(column_name, column_name .) TABLESPACE tab_space; 说明 UNIQUE：意味着索引列中的值必须是唯一的 index_name：索引的名称 table_name：需要加索引的表名 column_name：需要加索引的列。可以在多列上加索引(这样的索引称为复合索引) tab_space：存储索引的表空间。如果不指定的话，那么索引将存储在用户默认的表空间下 出于性能原因，可以将索引存储的表空间与实际表所存储的表空间分开。管理员
15、应该为表和索引创建不同的表空间。,案例-1,现有customer表 CREATE TABLE customers (customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,first_name VARCHAR2(10) NOT NULL,last_name VARCHAR2(10) NOT NULL,dob DATE,phone VARCHAR2(12) ); 插入测试数据 INSERT INTO customers VALUES (1, John, Brown, 01-1月-1965, 800-555-1211); INSERT INT
16、O customers VALUES (2, Cynthia, Green, 05-2月-1968, 800-555-1212); INSERT INTO customers VALUES (3, Steve, White, 16-3月-1971, 800-555-1213); INSERT INTO customers VALUES (4, Gail, Black, NULL, 800-555-1214); INSERT INTO customers VALUES (5, Doreen, Blue, 20-5月-1970, NULL);,案例-2,假设customers表中有很多数据，现有如
17、下查询语句 SELECT customer_id, first_name, last_name FROM customers WHERE last_name = Brown; 如果last_name列的基数很大，符合要求的数据又少于总行数的10%，那么可以使用CREATE INDEX来给last_name列创建索引 CREATE INDEX i_customers_last_name ON customers(last_name); 创建完名为i_customers_last_name的索引之后，之前查询速度会更快一些 可以使用唯一索引强制列值的唯一。例如，使phone列的值唯一 CREATE
18、 UNIQUE INDEX i_customers_phone ON customers(phone); 唯一索引不能包含相同的值,函数索引,刚才创建了i_customers_last_name索引，现在查询如下语句 SELECT first_name, last_name FROM customers WHERE last_name = UPPER(BROWN); 由于查询使用了UPPER()函数，这样的i_customers_last_name索引就不起作用。如果你的索引是基于函数处理的结果的话，那么可以使用函数索引，例如 CREATE INDEX i_func_customers_las
19、t_name ON customers(UPPER(last_name); 说明 另外，数据库管理员需要设置初始化参数QUERY_REWRITE_ENABLED为true(默认为false)，从而有利于函数索引。 例如 ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;,查看索引信息-1,可以通过查看user_indexes视图，了解索引的信息 例如 SELECT index_name, table_name, uniqueness, status FROM user_indexes WHERE table_name IN (CUSTOMERS, EMPL
20、OYEES) ORDER BY index_name; 查询效果如下图所示：,查看索引信息-2,说明 以上语句查询出customers表和employees表的索引信息 customers_pk索引是在创建表时设置customer_id列为主键时数据库自动创建的 相关列的说明 INDEX_NAME：索引名 TABLE_NAME：索引所属表的名称 UNIQUENES：是否是唯一索引，UNIQUE或NONUNIQUE STATUS：当前索引是否有效，VALID或INVALID,查看某列的索引信息,可以通过user_ind_columns视图查看某列的索引信息 例如 SELECT index_nam
21、e, table_name, column_name FROM user_ind_columns WHERE table_name IN (CUSTOMERS, EMPLOYEES) ORDER BY index_name; 查询效果如下图所示：,修改索引,可以使用ALTER INDEX语句修改索引 例如， 修改i_customers_phone索引名为i_customers_phone_number索引 ALTER INDEX i_customers_phone RENAME TO i_customers_phone_number; 修改效果如下图所示：,删除索引,可以使用DROP INDE
22、X删除索引 例如，删除i_customers_phone_number索引 DROP INDEX i_customers_phone_number; 执行效果如下图所示：,位图索引,位图索引通常用于数据仓库 数据仓库里的数据一般是用来进行大量的查询，而一般不是用来被大量的并发事务进行修改的。数据仓库通常被用于商业组织的智能分析，像监控销售趋势，等等 可以创建位图索引的列，一般会被用来进行大量的查询，但是列的基数缺不大。比如： 1、2、3、4、5、6 N、S、E、W “Order placed”, “Order shipped” 位图索引比较有代表性的应用是表中含有大量数据，而且不经常修改。如果
23、表中列的不同值的行数数目小于1，或如果列中的值是重复的100倍以上，则该列是一个位图索引的候选键。 例如，一张表中有100万行数据，某列的不同值达到10000甚至更少，那么该列可以用来创建位图索引。同样，该列应该尽可能的少修改，而且该列应该常用于WHERE子句的查询。,创建位图索引,在order_status表的status列上创建位图索引 修改表名 RENAME order_status2 TO order_status; 创建位图索引 CREATE BITMAP INDEX i_order_status ON order_status(status); 执行效果图如下所示：,什么情况下要创
24、建索引,在以下情况，应该考虑创建索引： 列包含较大范围的值 列包含大量空值 在WHERE子句或连接条件中频繁使用一个或多个列 表相当大，但是预计的多数查询检索的行占总行数比例较小，如百分之十以下,什么情况下不创建索引,在以下情况，通常不值得创建索引： 表比较小 在查询中不经常使用列作为条件 预计多数查询检索的行要超过表中总数行数的一定比例，如百分之十 表更新比较频繁 被索引的列将作为表达式的一部分进行引用,视图内容,视图学习内容包括以下部分： 创建和使用视图 从数据字典中获取视图的信息 修改视图 删除视图,视图,视图是基于已知的一张或多张表的查询 查询视图与查询普通表的查询方式是一样的 一些视
25、图是可以对其进行DML操作，从而影响其基表的操作 视图实际并不存储数据，数据存储实际是存储在表中 之前已经使用过很多视图查看信息。 比如：user_tables，user_sequences 以及user_indexes都是视图,为什么要使用视图,将复杂的查询用视图来实现，然后给用户授权访问 这样可以隐藏复杂的业务逻辑 可以禁止用户直接访问基表，授予用户访问视图的权限 可以允许视图来访问基表中的某些行 可以对最终用户隐藏某些行,数据准备,产品类型表 CREATE TABLE product_types (product_type_id INTEGER CONSTRAINT product_ty
26、pes_pk PRIMARY KEY,name VARCHAR2(10) NOT NULL ); 产品信息表 CREATE TABLE products (product_id INTEGER CONSTRAINT products_pk PRIMARY KEY,product_type_id INTEGERCONSTRAINT products_fk_product_typesREFERENCES product_types(product_type_id),name VARCHAR2(30) NOT NULL,description VARCHAR2(50),price NUMBER(5,
27、 2) );,创建和使用视图,可以使用CREATE VIEW语句创建视图，语法如下 CREATE OR REPLACE FORCE | NOFORCE VIEW view_name (alias_name, alias_name .) AS subquery WITH CHECK OPTION | READ ONLY CONSTRAINT constraint_name; 说明 OR REPLACE：表示替换已存在的同名视图 FORCE：强制创建视图，即使基表不存在也创建 NOFORCE：基表如果不存在，则不创建视图。默认值 view_name：视图名称 alias_name：子查询表达式的别
28、名 subquery：检索基表数据的子查询 WITH CHECK OPTION：表示只有被查询搜索到的数据行才可以进行插入、更新、删除操作。默认是不使用该项 constraint_name：是WITH CHECK OPTION或WITH READ ONLY选项的约束名 WITH READ ONLY：只能读取基表的数据行,视图的分类,简单视图 查询语句只涉及到一张基表 复杂视图 一般查询涉及到一张或多张表 使用GROUP BY子句或DISTINCT关键字 包含函数的使用,创建视图的准备,要创建视图，必须给用户授予创建视图CREATE VIEW的权利。可以使用SYS给用户store授予该权利。 授
29、权语句如下 GRANT CREATE VIEW TO store; 执行效果如下图所示：,创建简单视图,使用store用户创建名为cheap_products_view视图，用以查找价格低于15元的商品 CREATE VIEW cheap_products_view AS SELECT * FROM products WHERE price 15; 创建名为employees_view的视图，用以查找employees表中除salary列的信息 CREATE VIEW employees_view AS SELECT employee_id, manager_id, first_name, l
30、ast_name, title FROM employees;,使用简单视图,查询cheap_products_view视图的数据 SELECT product_id, name, price FROM cheap_products_view; 查询employees_view视图的数据 SELECT * FROM employees_view;,使用视图插入数据,可以使用DML语句对cheap_products_view视图进行操作(注意只可用于简单视图) 例如，对视图进行插入操作 INSERT INTO cheap_products_view (product_id, product_ty
31、pe_id, name, price) VALUES (13, 1, Western Front, 13.50); 查询视图 SELECT product_id, name, price FROM cheap_products_view WHERE product_id = 13;,注意事项-1,由于创建cheap_products_view视图未使用CHECK OPTION选项，即使检索不出数据的数据行，也可以进行DML操作 例如，插入价格超过15元的商品 INSERT INTO cheap_products_view (product_id, product_type_id, name,
32、price) VALUES (14, 1, Eastern Front, 16.50); 查询该商品 SELECT * FROM cheap_products_view WHERE product_id = 14;,注意事项-2,由于employees_view视图中不包含salary列，所以在往该视图中插入数据的时候，salary列值将被设置为null 例如，先插入员工数据，再查询 插入测试 INSERT INTO employees_view (employee_id, manager_id, first_name, last_name, title) VALUES (5, 1, Jeff
33、, Jones, CTO); 查询测试 SELECT employee_id, first_name, last_name, salary FROM employees WHERE employee_id = 5; 查询效果如下图所示：,创建视图使用CHECK OPTION-1,如果想DML语句在视图上进行安全的操作，那么可以在查询上设置CHECK OPTION。例如， CREATE VIEW cheap_products_view2 AS SELECT * FROM products WHERE price 15 WITH CHECK OPTION CONSTRAINT cheap_prod
34、ucts_view2_price; 说明 WHERE子句的限定跟cheap_products_view视图一样，只不过加了CHECK OPTION限制。,创建视图使用CHECK OPTION-2,通过视图插入一条记录 INSERT INTO cheap_products_view2 ( product_id, product_type_id, name, price) VALUES (15, 1, Southern Front, 19.50); 执行结果如下图所示：说明 由于商品价格是19.50，通过视图是无法将该商品查出的，视图里面筛选的价格是小于15的，所以插入数据时数据库返回一个错误。,
35、使用READ ONLY创建视图,创建视图的时候使用READ ONLY，那么该将为只读视图 例如，创建cheap_products_view3视图使用READ ONLY CREATE VIEW cheap_products_view3 AS SELECT * FROM products WHERE price 15 WITH READ ONLY CONSTRAINT cheap_products_view3_read_only; 插入测试 INSERT INTO cheap_products_view3 (product_id, product_type_id, name, price) VAL
36、UES (16, 1, Northern Front, 19.50); 执行效果如下图所示：,查看视图定义-1,可以使用DESCRIBE命令查看视图定义 例如，查看cheap_products_view3视图定义 DESCRIBE cheap_products_view3 执行效果如下图所示,查看视图定义-2,同样，可以通过user_views视图查看视图定义信息 例如 SELECT view_name,text_length,text FROM user_views; 说明，视图的相关列 view_name：视图名 text_length：创建视图的字符长度 text：创建视图的文本代码 r
37、ead_only：是否为只读视图,创建复杂视图-1,创建名为products_and_types_view的视图，使用完全外连接 CREATE VIEW products_and_types_view AS SELECT p.product_id, p.name product_name, pt.name product_type_name, p.price FROM products p FULL OUTER JOIN product_types pt USING (product_type_id) ORDER BY p.product_id; 查询视图 SELECT * FROM prod
38、ucts_and_types_view; 查询效果如下图所示：,创建复杂视图-2,创建视图employee_salary_grades_view，使用内连接 CREATE VIEW employee_salary_grades_view AS SELECT e.first_name, e.last_name, e.title, e.salary, sg.salary_grade_id FROM employees e INNER JOIN salary_grades sg ON e.salary BETWEEN sg.low_salary AND sg.high_salary ORDER BY
39、 sg.salary_grade_id; 查询视图 SELECT * FROM employee_salary_grades_view; 效果如下图所示,创建复杂视图-3,创建视图product_average_view，使用WHERE子句、GROUP BY、HAVING CREATE VIEW product_average_view AS SELECT product_type_id, AVG(price) average_price FROM products WHERE price 13 ORDER BY product_type_id; 查询视图 SELECT * FROM prod
40、uct_average_view; 查询效果如下图所示,修改视图,可以使用CREATE OR REPLACE VIEW修改或替换视图 替换product_average_view视图 CREATE OR REPLACE VIEW product_average_view AS SELECT product_type_id, AVG(price) average_price FROM products WHERE price 11 ORDER BY product_type_id; 可以使用ALTER VIEW去除视图相关约束 例如，去除cheap_products_view2_price视图的
41、约束 ALTER VIEW cheap_products_view2 DROP CONSTRAINT cheap_products_view2_price;,删除视图,可以使用DROP VIEW语句删除视图 例如，删除视图cheap_products_view2 DROP VIEW cheap_products_view2; 执行效果如下图所示：,同义词,使用同义词访问相同的对象: 方便访问其它用户的对象 缩短对象名字的长度,CREATE PUBLIC SYNONYM synonym FOR object;,创建和删除同义词,为视图DEPT_SUM_VU 创建同义词删除同义词说明：创建同义词必须要拥有 create synonym | create public synonym的权限。 类似别名，多用于分布式的数据库中。,CREATE SYNONYM d_sum FOR dept_sum_vu; Synonym Created.,DROP SYNONYM d_sum; Synonym dropped.,同义词示例：,本章总结,序列 索引 视图 同义词,
