oracle数据库总结（一）

oracle数据库

SQL 语句的分类
1.1.SELECT 语句 DQL 数据查询语句

1.2.DML语句（data manipulation language） 数据操作语句

INSERT     增加DELETE     删除UPDATE     修改

1.3.DDL语句（data define language）数据定义语句

CAREATE    TABLE     建立表DROP       TABLE     删除表ALTER      TABLE     修改表

1.4.TCL语句（Transaction control language）事务控制语句

commit     提交rollback   回滚  savepoint  保存

1.5.DCL(data control language) 数据控制语句

grant      授权revoke     回收权限

增加数据(insert)

全字段插入(每个字段按照顺序都给一个值)

  Insert into 表名values(字段值1,字段值,字段值3);

选择部分字段插入数据

  Insert into 表名(字段名,字段名3)values(值1,值3);

删除数据(delete)

Delete from 表名 where 条件;Delete from s_emp where id=1001;

修改数据(update)

Update 表名 set 字段名1=值1，字段名2=字段值2 where 条件;Update student set name=’mengge’,salary=111 where id=1;

修改表字段

 alter table 表名 add 字段名  字段类型        /*增加列*/                 alter table 表名 drop column 列名               /*删除列*/ alter table 表名 rename column 列名 to 新列名     /*重命名列名*/ alter table 表名 rename to 新表名 to新列名   /*重命名表名*/

前加级联删除

create table(id number,dept_id number constraint emp_dept_id_fk foreign key(dept_id) references dept(id) on delete casecode);

后加级联删除

alter table add emp  constraint emp_dept_id_fk foreign key(dept_id) references dept(id) on delete casecode

TCL(事务控制语句 transaction control language)
什么是事务？

在数据库会把多条逻辑上相关语句，组织在一起，逻辑上我们称为一个事务

事务特性

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

必须满足的ACID（原子性、一致性、隔离性和持久性）属性。

原子性：事务的语句是一个整体，不可分割
一致性：事务在完成时，必须使所有的数据都保持一致状态。
隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。
持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(读未提交)、Read committed（读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

脏读：一个事务读到另一个事务未提交的数据结果。
不可重复读：一个事务对同一行数据读取两次，获得不同的结果。
幻读：事务在操作的过程中，第二次查询与第一次查询到的结果包含了第一次未查询到的或缺少了第一次查询的数据，两次查询过程中有另外一个事务插入数据造成的。

读未提交

读未提交：一个事务开始写数据，则另一个事务不允许同时进行写操作，允许读取数据。避免更新的丢失。可以通过“排他写锁”实现。可能出现脏读。

读提交

读提交：可以读取提交过的数据，但未提交的写事务将会禁止访问。避免了脏读，但可能出现不可重复读。

可重复读

可重复读：读取数据的事务将禁止写事务，写事务则禁止任何其他事务。可能出现幻读。这可以通过“共享读锁”和“排他写锁”实现。

序列化

序列化：提供了严格的事务隔离，要求事务序列化执行，事务只能一个接一个的执行，不能并发执行。不仅可以避免脏读、不可重复读，还避免了幻像读。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

大多数数据库的默认级别就是读提交，比如Sql Server , Oracle。
Mysql的默认隔离级别就是读未提交。

组函数

  Count  统计数量  Max   最大值  Min   最小值  Avg   求平均值  Sum   求和

From表名where条件 group by 分组标准 having 组数据过滤条件 Order by 排序标准;

Select dept_id,count(id) from s_emp where 1=1 group by dept_id having count(id)>2;

子查询

把一个sql查询结果，作为另外一个sql查询操作的基础

   Select id,first_name,salary from s_emp where salary>( Select salary from s_emp where id = 10);

rownum 分页

最内层:负责排序
中间层:负责编号 和 起别名 小于的过滤
最外层:负责使用别名 进行大于的过滤

select r sal from (select rownum r sal from (select sal from order by sal)where r<2*11+1) where r>(2-1)*11

约束

对数据库进行验证，满足条件的才能进入，不满足条件的被拦截数据库之外。

约束的种类

主键约束 字段值不能为空，不能重复，整个表只能有一个主键。
唯一性约束：字段值不能重复。
非空约束：字段值不能为空。
检查约束：字段值必须满足检查的条件。
外键约束：

primary key-------主键     unique     -------唯一性约束not null   -------非空约束check    ---------检查约束foreign-----------外键约束

列级约束：在定义表的某一列时，在这一列后添加的约束。
表集约束：在定义表的所有列完成后，添加的某些列的约束。

字段名 类型 constraint 约束名 约束关键字
约束名的构造：表名字段名约束关键字（简写）

id number  constraint emp_id_pk primmary key

序列对主键值的控制

create sequence emp_id_seq;emp_id_seq.nextval;创建序列

索引 index

消耗大量的内存和时间来达到快速查询的目的

 create  index  索引名  on  表名(字段名);   set timing  on;

不要在 主键 和 唯一性上 加索引 系统已经自动建立索引
create index s_emp_first_name_ind on s_emp(first_name);

1.3删除索引

   drop  index  索引名;

oracle视图基础详解与实例

一：概念

视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视图本身不包含任何数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询语句。视图的数据都来自于某些表，这些表被称为基表。数据库中只在数据字典中存储对视图的定义。

二：优点

1、为用户集中数据，简化用户的数据查询和处理。2、屏蔽数据库的复杂性，用户不必了解数据库的复杂性。3、简化用户权限的管理，只授予用户使用视图的权限。4、可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访问表的全部行和列。5、便于数据共享，多个用户不必都定义所需的数据。

三：视图类别
1、简单视图

指基于单个表并且不包含函数或表达式的视图，在该视图上可以执行DML语句（即可执行增、删、改操作）。

2、复杂视图

指基于单个或者多个表或者包含函数、表达式或者分组数据的视图，在该视图上执行DML语句时必须要符合特定条件。注意：在定义复杂视图时必须为函数或表达式定义别名

3、连接视图

指基于多个表建立的视图，一般来说不会在该视图上执行INSERT、UPDATE、DELETE操作。

4、只读视图

指只允许进行SELECT操作的视图，在该视图时指定WITH READ
ONLY选项。该视图上不能执行INSERT、UPDATE、DELETE操作。

5、check约束视图

WITH CHECK OPTION用于在视图上定义CHECK约束,即在该视图上执行INSERT或UPDATE操作时,数据必须符合查询结果.

四：创建视图
1、基本语法

CREATE [OR REPLACE] VIEW  view_name  [(column_name1[,column_name2… AS select_statement [WITH CHECK OPTION] [WITH READ ONLY]

2、参数说明

CREATE OR Repalce：用于创建和修改视图WITH CHECK OPTION ：用于创建限制数据访问的视图WITH READ ONLY ：用于创建只读视图

3、DML操作遵循的原则

a）简单视图可以执行DML操作。

b）在视图出现下列情况时不可以通过视图修改基表数据或插入数据：

i、集合运算符(union,intersect,minus)ii、DISTINCT关键字iii、GROUP BY，ORDER BY，CONNECT BY或START WITH子句v、子查询vi、分组函数vii、需要更新的列不是由“列表达式”定义的vx、基表中所有NOT NULL列均属于该视图

五：如何查询视图和表的更新权限

select table_name,column_name,updatable,insertable,deletable from user_updatable_columns; 

说明

updatable 表示当前字段是否可以执行修改操作

insertable 表示当前字段是否可以执行添加操作

deletable 表示当前字段是否可以执行删除操作

六：示例

–对简单视图的操作

drop table emp1;create table emp1 as select * from emp;

–简单视图

create or replace view v_emp1asselect * from emp1 ;

–查询

select * from v_emp1;

–更新

update v_emp1 set v_emp1.ENAME='andy' where v_emp1.JOB='CLERK';

–增加

insert into v_emp1 values (7777, 'chy', 'MANAGER', 8888, sysdate, 10000,1111.11,20);

–删除

delete from v_emp1 where v_emp1.EMPNO=7777;

–复杂视图、仅两基表相连、不包含各种分组函数、group by、distinct命令等。

create or replace view v_complexasselect emp1.ename, emp1.job, dept.dname from emp1, dept where emp1.deptno=dept.deptnowith check option ;

–查询

select * from v_complex;

–修改

update v_complex set v_complex.ename='andy' where v_complex.job='MANAGER';

–增加
–报错：ORA-01776：无法通过连接视图修改多个基表

insert into v_complex (v_complex.ename, v_complex.job, v_complex.dname) values ('chy', 'MANAGER', 'SALES');

–删除

delete from v_complex where v_complex.ename='chy';

–复杂视图、包含不能进行DML的元素、一般仅用与查询、可以加上

with read only;create or replace view v_complex_readonlyas

–对使用聚合函数的列必须使用别名！

select max(emp1.sal) max_sal from emp1, dept where emp1.deptno=dept.deptno group by dept.deptnowith read only;

–查询

select * from v_complex_readonly;

–删除视图

create or replace view v_for_deleteasselect * from empwith read only;drop view v_for_delete;

常见的访问数据库的技术

JDBC java访问数据库的技术PL/SQL 在数据库内部访问数据库的技术proc/c++ c/c++访问数据库的技术ODBC 微软访问数据库的技术OCI oracle底层连接的接口