2018-09-06 工作日志

Java 工作日志 SQL Oracle 数据库

自连接

SELECT f_id,s_id,f_name FROM fruits ORDER BY s_id;
-- 自连接
SELECT f1.f_id,f1.f_name FROM fruits f1,fruits f2 WHERE f1.s_id=f2.s_id AND f2.f_id='a1';

结果：

f_id f_name
a1 apple
b1 blackberry
c0 cherry

子查询

带ANY,SOME,ALL,EXISTS,IN关键字的子查询

SELECT * FROM tb11;
SELECT * FROM tb12;
-- 返回tb12表中的值，然后将tb11中的值与tb12中的值比较，只要大于tb12中的任何一个值即为符合查询条件
SELECT num1 FROM tb11 WHERE num1> ANY (SELECT num2 FROM tb12);
--带ANY,SOME,ALL,EXISTS,IN关键字的子查询
SELECT num1 FROM tb11 WHERE num1> SOME (SELECT num2 FROM tb12);
SELECT num1 FROM tb11 WHERE num1> ALL (SELECT num2 FROM tb12);

带比较运算符的子查询

SELECT * FROM suppliers;
SELECT*FROM fruits;
-- EXISTS返回true或false
SELECT*FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id=107);
SELECT*FROM fruits WHERE f_price>10 AND EXISTS (SELECT s_name FROM suppliers WHERE s_id=107);
SELECT*FROM fruits WHERE f_price<10 AND NOT EXISTS (SELECT s_name FROM suppliers WHERE s_id=108);
-- 根据orders号查询客户c_id
SELECT * FROM orderitems;
SELECT o_num,c_id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id='c0');
-- 根据供应商地区查询水果
SELECT*FROM fruits WHERE s_id IN(SELECT s_id FROM suppliers WHERE s_city='Tianjin');

合并查询结果

-- 选择的字段要一致,合并时，两个表对应的列数和数据类型必须相同
SELECT  * FROM  fruits WHERE    f_price < 9 UNION ALL
SELECT * FROM fruits WHERE s_id IN ( 101, 103 );
-- UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的
SELECT  * FROM  fruits WHERE    f_price < 9 UNION
SELECT  * FROM  fruits WHERE    s_id IN ( 101, 103 );

别名

SELECT * FROM orders o WHERE o.o_num;
SELECT o.o_num,c.c_id FROM orders o LEFT OUTER JOIN customers c ON o.c_id=c.c_id;
SELECT f1.f_name AS 名称,f1.f_price AS 价格 FROM fruits f1 WHERE f1.f_price<8;
SELECT CONCAT(s_name,'——',s_city) AS sdvsdv FROM suppliers;

下午

视图

简化复杂查询，限制数据访问

视图不是表，视图的数据来自于表，视图其实就是一条sql语句，但是我们在查询的时候，可以把视图当做表来看待，都可以写在from之后，当from视图时，系统会先找出视图所代表的结果集，然后再满足外界所需的查询。
在视图中用户可以使用SELECT语句查询数据，以及使用INSERT,UPDATE和DELETE修改记录。视图可以使用户操作方便，而且可以保障数据库系统的安全。
CREATE [OR REPLACE] VIEW view_name as SELECT 字段1，字段2......

当视图已经存在时，修改语句对视图进行修改；当视图不存在时，创建视图。
更新视图是指通过视图来插入，更新，删除表中的数据。因为视图是一个虚拟表，其中没有数据。通过视图更新时都是转到基本表上进行的，如果对视图增加或者删除记录，实际上是对基本表增加或者删除记录：

//基表的数据将被修改
UPDATE view_id_name SET F_ID=555;

限制视图的数据操作：对视图数据的增加或更新实际上是操作视图的源表。通过对视图的限制操作，可以提高数据操作安全性。如果想防止用户修改数据，可以将视图设置成只读属性(with read only)。
设置视图的检查属性:在修改视图数据时，可以指定一定的检查条件。此时需要使用with check option.

CREATE VIEW view_name_price AS SELECT F_NAME,F_PRICE FROM FRUITS;
CREATE VIEW view_id_name AS SELECT F_ID,F_NAME FROM FRUITS;
-- 两个表连在一起的视图
CREATE VIEW order_cname_time (单号,姓名,日期) AS
SELECT CUSTOMERS.C_ID,CUSTOMERS.C_NAME,ORDERS.O_DATE FROM CUSTOMERS,ORDERS WHERE CUSTOMERS.C_ID=ORDERS.C_ID;
-- 只读视图(with read only)
CREATE VIEW view_name_city AS SELECT C_NAME,C_CITY FROM CUSTOMERS with read only;
DROP VIEW view_name_city;

按视图查询

SELECT*FROM view_name_price;
SELECT*FROM view_id_name;

索引

用于加快数据的检索。

给表中的字段设置索引时，若给所有字段都设置，反而会使效率低下

经验：DISTINCT（去重）或WHERE、ORDER BY后的字段往往设置为索引字段。

创建语法:create index 索引名 on 表名(字段名1，字段名2....);

CREATE INDEX index_f ON FRUITS(S_ID);

比较适合建立索引的列的特点

经常需要搜索的列上
主键，一般建立唯一性索引，保持数据的唯一性
外键，提高表与表之间连接的速度
需要排序的列上
where子句后边经常出现的字段
经常需要根据范围进行搜索的列上，比如日期

不适合建立索引的列的特点

很少进行搜索的列上
列取值比较少的列上
blob类型的列上
修改频率比较高的列上

创建索引语法

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

修改索引

1）重命名索引

alter index index_sno rename to bitmap_index;

2) 合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index index_sno coalesce;

3)重建索引

　　方式一：删除原来的索引，重新建立索引

　　方式二：

alter index index_sno rebuild;

删除索引

drop index index_sno;

查看索引

select * from all_indexes;
-- eg:
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

总结

　　1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

　　2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

　　3. 小表不要简历索引

　　4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引

　　5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引

　　6. 经常进行连接查询的列应该创建索引

　　7. 使用create index时要将最常查询的列放在最前面

　　8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

　　9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

注意事项

1. 通配符在搜索词首出现时，oracle不能使用索引，例：

create index index_name on student('name');
--下面的方式oracle不适用name索引
select * from student where name like '%wish%';
--如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：
select * from student where name like 'wish%';

1. 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描）

select * from student where not (score=100);
select * from student where score <> 100;
--替换为
select * from student where score>100 or score <100

1. 索引上使用空值比较将停止使用索引， eg：

select * from student where score is not null;