笔记整理-Oracle数据库学习笔记（下）

1、组函数：组函数作用于一组数据，并对一组数据返回一个值。

AVG 平均值

COUNT 计数

MAX 最大值

MIN 最小值

SUM 求和

可以对数值型数据使用AVG 和 SUM 函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

不能在WHERE子句中使用组函数。

可以在HAVING子句中使用组函数。

嵌套组函数

SELECT MAX (AVG(salary))

FROM employees

GROUP BY department_id;

2、可以使用 GROUP BY 子句将表中的数据分成若干组

SELECT department_id,AVG(salary)

FROM employees GROUP BY department_id ;

使用 HAVING 过滤分组:

行已经被分组。

使用了组函数。

满足HAVING子句中条件的分组将被显示。

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id

HAVING MAX(salary)>10000 ;

3、SQL支持下列类别的命令：

数据定义语言（DDL）

CREATE  ALTER  DROP

数据操纵语言（DML）

INSERT  SELECT  DELETE UPDATE

事务控制语言（TCL）

COMMIT  SAVEPOINT  ROLLBACK

数据控制语言（DCL）

GRANT REVOKE

4、事务的ACID特性

原子性：任何一个事务都是一个整体，不可以再分割，要么一起执行要么一起回滚。

一致性：必须从一个正确的状态到另外一个正确的状态。

隔离性：一个会话没有提交的数据，其他的会话是不能看到。

持久性：提交事务以后，事务的操作会被持久化到数据库当中，不能回退，别的会话可以看到事务操作的结果

5、使用 SAVEPOINT 语句在当前事务中创建保存点。

使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。

自动提交在以下情况中执行:

DDL语句。

DCL语句。

不使用COMMIT或ROLLBACK语句提交或回滚，正常结束会话。

会话异常结束或系统异常会导致自动回滚。

6、读一致性为数据提供一个一致的视图

一个用户的对数据的改变不会影响其他用户的改变

对于相同的数据读一致性保证:

查询不等待修改。

修改不等待查询。

7、Oracle 数据库中，锁是 :

并行事务中避免资源竞争。

避免用户动作。

自动使用最低级别的限制。

在事务结束结束前存在。

两种类型:显式和隐式。

两种模式:

独占锁:屏蔽其他用户。

共享锁:允许其他用户操作。

8、只读事务：在事务中看不到其他会话的修改，在当前会话只允许查询

顺序事务：同上，但是可以在当前会话中做DML操作

9、多表查询 ：自连接、内连接、外链接、子查询

自连接就是把某一张表中的行同该表中另外一些行连接起来。自连接主要用于查询比较相同的信息，所比较的列必须有相同的或兼容的数据类型。

为了连接同一个表，需要为该表指定两个不同的别名，只有这样才能把该表逻辑上作为两个不同的表使用。

select a.ename, a.sal, b.ename from empa,emp b 

where a.deptno=b.deptno;

内连接把两个表连接成一个表（称为第三个表），在这个表中仅包含那些满足连接条件的记录行。

内连接保证了两个表中所有的行都满足连接条件，但却丢失了一些不满足连接条件的数据。

select * from goods, buy where goods.goods_no=buy.goods_no;

外连接如果想只限定一个表的条件而不限定另外一个表的条件，就需要使用外连接。

外连接只能用于两或两张以上的连接。

包括左外连接和右外连接，用(+)表示可以 填充NULL值。

SELECT 别名1.colmun, 别名2.colmun

FROM table1 别名1 LEFT (OUTER) JOIN table2 别名2

ON 别名1.colmun1=别名2.colmun2;

左外连接查询

SELECT别名1.colmun,别名2.colmun

FROM table1 别名1,table2 别名2 

WHERE 别名1.colmun1=别名2.colmun2(+);

右外连接查询

SELECT 别名1.colmun, 别名2.colmun

FROM table1 别名1, table2别名2

WHERE 别名1.colmun1(+)= 别名2.colmun2;

子查询在主查询之前一次执行完成

SELECT last_name FROM employees

WHERE salary >

(SELECT salary FROM employees WHERE last_name= 'Abel');

子查询要包含在括号内。

将子查询放在比较条件的右侧。

除非进行Top-N分析，否则不要在子查询中使用ORDER BY子句。

单行操作符对应单行子查询，多行操作符对应多行子查询。

多行子查询：返回多行，使用多行比较操作符

多行比较操作符
10、EXISTS 操作符检查在子查询中是否存在满足条件的行

如果在子查询中存在满足条件的行:

不在子查询中继续查找

条件返回TRUE

如果在子查询中不存在满足条件的行:

条件返回FALSE

继续在子查询中查找

11、Oracle 数据对象：视图、序列、索引、同义词

为什么使用视图？

控制数据访问

简化查询

数据独立性

避免重复访问相同的数据

简单视图和复杂视图
12、创建视图

CREATE VIEW empvu80

AS SELECT employee_id,last_name,salary

FROM employees 

WHERE department_id= 80;

13、修改视图

使用CREATE OR REPLACE VIEW子句修改视图

CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)

AS  SELECT employee_id, first_name || ' ' || last_name,

salary,department_id FROM employees

WHERE department_id = 80;

14、当视图定义中包含以下元素之一时不能使用delete:

组函数

GROUP BY 子句

DISTINCT关键字

ROWNUM 伪列

当视图定义中包含以下元素之一时不能使用update

组函数

GROUP BY子句

DISTINCT 关键字

ROWNUM 伪列

列的定义为表达式

当视图定义中包含以下元素之一时不能使用insert

组函数

GROUP BY子句

DISTINCT关键字

ROWNUM伪列

列的定义为表达式

表中非空的列在视图定义中未包括

15、n使用WITH CHECK OPTION子句确保DML只能在特定的范围内执行

CREATE  OR REPLACE VIEW empvu20

ASSELECT * FROM employees

WHERE department_id= 20

WITH CHECK OPTION CONSTRAINT empvu20_ck ;

任何违反WITH CHECK OPTION约束的请求都会失败

删除视图只是删除视图的定义，并不会删除基表的数据

DROP VIEW view;

16、什么是序列？

自动提供唯一的数值

共享对象

主要用于提供主键值

代替应用代码

将序列值装入内存可以提高访问效率

创建序列DEPT_DEPTID_SEQ 为表 DEPARTMENTS 提供主键

不使用CYCLE选项

CREATE SEQUENCE dept_deptid_seq

INCREMENT BY 10

START WITH 120

MAXVALUE 9999

NOCACHE 

NOCYCLE ;

查询数据字典视图USER_SEQUENCES获取序列定义信息

SELECT sequence_name, min_value, max_value,

increment_by , last_number

FROM  user_sequences;

17、NEXTVAL 和 CURRVAL 伪列

NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用

CURRVAL 中存放序列的当前值

NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效

18、修改序列

修改序列的增量,最大值,最小值,循环选项,或是否装入内存

ALTER SEQUENCE dept_deptid_seq

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

注意事项：

必须是序列的拥有者或对序列有ALTER权限

只有将来的序列值会被改变

改变序列的初始值只能通过删除序列之后重建序列的方法实现

其它的一些限制

使用DROP SEQUENCE语句删除序列

删除之后，序列不能再次被引用

19、索引

一种数据库对象

通过指针加速Oracle服务器的查询速度

通过快速定位数据的方法，减少磁盘I/O

索引与表相互独立

Oracle服务器自动使用和维护索引

创建索引

自动创建:在定义PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引

手动创建:用户可以在其它列上创建非唯一的索引，以加速查询

在表EMPLOYEES的列LAST_NAME上创建索引

CREATE INDEX emp_last_name_idx

ON employees(last_name);

适合创建B-TREE索引的时机：

列值基数比较高（重复率比较低）

不以空值做条件

列经常出现在条件子句中

表很大

经常做查询操作，而不是DML操作

满足条件的数据不超过总记录数的15%

模糊查询不走索引，如果模糊查询想使用索引，必须用全文检索技术

可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息

SELECT ic.index_name,ic.column_name,ic.column_positioncol_pos,ix.uniqueness

FROM user_indexesix,user_ind_columnsic

WHERE ic.index_name=ix.index_name

AND ic.table_name= 'EMPLOYEES';

基于函数的索引是一个基于表达式的索引

索引表达式由列,常量,SQL函数和用户自定义的函数

CREATE INDEX upper_dept_name_idx

ON departments (UPPER(department_name));

Index created.

SELECT * FROM departments

WHERE UPPER(department_name) ='SALES';

位图索引是在基数较小，即重复率很大的列值中创建比较适用

create bitmap index emp_jobid_bmidx

on empcopy (job_id);

使用DROP INDEX命令删除索引

DROP INDEX index；

只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引

20、使用同义词访问相同的对象:

方便访问其它用户的对象

缩短对象名字的长度

为视图DEPT_SUM_VU创建同义词

CREATE SYNONYM  d_sum

FOR dept_sum_vu;