oracle sql语句笔记

左外连接例子：

 

select employee_id,last_name,salary,department_id,department_name

 

from employees left join departments using(department_id)

 

(两个表使用department_id进行等值连接)

 

右外连接例子：

 

select employee_id,last_name,salary,department_id,department_name

 

from employees right join departments using(department_id)

 

(两个表使用department_id进行等值连接)

 

全外连接例子：

 

select employee_id,last_name,salary,department_id,department_name

 

from employees full outer join departments using(department_id)

 

(两个表使用department_id进行等值连接)

 

子查询（Sub Query）

 

问题引入：如何查得所有比“张三”工资高的员工信息

 

子查询

 

子查询在主查询前执行一次

 

主查询使用子查询的结果

 

语法格式：

 

select 字段列表

 

from table

 

where 表达式operator (select 字段列表from table);

 

使用子查询注意事项：

 

在查询是基于未知值时应考虑使用子查询;

 

子查询必须包含在括号内；

 

建议将子查询放在比较运算符的右侧，以增强可读性。

 

除非进行Top-N（即前几名）分析，否则不要在子查询中使用order by 子句；

 

对单行子查询使用单行运算符；

 

对多行子查询使用多行运算符；

 

单行子查询

 

单行子查询只返回一行记录；

 

对单行子查询可使用单行记录比较运算符；（=,>,>=,<,<=,<>不等于）

 

举例：select * from emp where sal>(select sal from emp where  empno=7566);

 

子查询空值/多值的问题

 

如果子查询未返回任何行，则主查询也不会返回任何结果；

 

Select * from emp where sal>(select sal from emp where empno=8888);//8888是不存在的

 

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比运算符；select * from emp where sal>(select sal from emp where empno=7566);

 

如果子查询返回多选择结果，则为多行子查询，此时不允许对其使用单行记录比较运算符。

 

select * from emp where sal>(select avg(sal) from emp group by deptno);//非法的，不可以这样做

 

Desc emploree//查询emploree表的结构

 

多行子查询

 

多行子查询返回多行记录，对多行子查询只能使用多行记录比较运算符（in,any,all）

 

in等于列表中的任何一个

 

Any和子查询返回的任意一个值比较；

 

All和子查询返回的所有值比较；

 

举例：

 

Select * from emp where sal>any(select avg(sal) from emp group by deptno);

 

Select * from emp where sal>all(select avg(sal) from emp group by deptno);

 

Select * from emp where job in(select job from emp ename='MARTIN'  or ename='SMITH');

 

TopN查询

 

在ORACLE中通常采用子查询的方式来实现Top N查询

 

举例：select * from (select * from emp order by sal desc) where rownum<=5;

 

Rownum是伪列，必须在已经查询中的结果中才可以使用rownum，直接使用无效。例如：

 

Select * from employee where rownum<=5//非法，rownum不存在

 

Select rownum,a.* from(select * from emp order by sal desc) a;//正确使用rownum的例子

 

Select * from

 

(select rownum myno,a.* from(select * emp order by sal desc) a)

 

Where myno >=5 and myno <=10;

 

第章DML与事务控制

1 数据操作语言DML  insert语句，update语句，delete语句，merge语句

 

2数据库事务控制

 

事务的概念，事务提交和回滚，自动提交，保存点和部分回滚。

 

Insert语句

 

1每次只能插入一条记录，缺省字段名列表时，应为新插入记录中的每个字段设定新值

 

也可以在insert语句中指定赋值字段列表，只为部分字段显示地设定值。其余字段为Null.

 

可以在insert语句中使用子查询，实现表间数据拷贝：

 

Insert into dept1(id,name) select deptno,dname from dept;

 

说明：此时不必再给出values子句。子查询中的值列表应与insert子句字段相对应。

 

Update语句

 

每次可更新多条记录，可使用where子句限定要更新的记录，如缺省的where子句，则更新表中的所有记录。

 

Update emp set sal=sal+99;

 

Update emp set sal=sal+99 where empno=7599;

 

Update student2

 

Set age=nvl(age,o)+1;

 

Delete语句

 

Delete语句每次可删除多条记录

 

可使用where子句，限定要删除的记录

 

Delete from table

 

Delete from table where empno=8888;

 

Merge语句

 

Merge语句用于进行数据合并，根据条件在表中执行数据的修改或插入操作，如果要插入的记录在目标表中已经存在，则执行更新操作，否则执行插入操作。

 

用法举例：

 

Create table test1 (eid number(10),name varchar);

 

Insert into test1 values(1001,'张三''20-5月-70',2300);

 

Insert into test1 values(1002,'李四''16-5月-70',2600);

 

Select * from test1;

 

Create table test2(eid number(10),name varchar2(20),birth date,salary number(8,2));

 

Select * from test2;

 

Merge into test2

 

Using test1

 

On (test1.eid=test2.eid)

 

When matched then

 

Update set name=test1.name,birth=test1.birth,salary=test1.salary

 

When not matched then

 

Insert(eid,name,birth,salary) values(test1.eid,test1.name,test1.birth,test1,salary);

 

Select * from test2;

 

事务控制

 

事务

 

组成单个逻辑工作单元的一系列操作被称为事务。

 

事务必须满足的ACID属性：

 

原子性（Atomicity）,一致性（Consistency）,隔离性（Isolation）,持久性（Durability）

 

执行插入，更新，删除操作时，一定要执行commit;操作。不然不会提交到数据库。

 

Sql Plus的自动提交

 

Sql Plus中执行SQL语句时可以设置是否自动提交，缺省设置为非自动提交。

 

查看设置show autocommit;

 

结果：

 

Autocommit OFF;

 

Autocommit IMMEDIATE

 

改变设置：

 

Set autocommit on;

 

Set autocommit off;

 

提交/回滚数据状态

 

当前事务中数据状态的改变是可以恢复的

 

当前事务中的DML操作结果只对当前用户（会话）可见，其它用户（会话）看不到当前事务中数据状态的改变，直到当前事务结束(即执行commit)。

 

当前事务中DML语句所涉及到的行被锁定，其他用户（会话）不能对其进行修改操作。

 

只可以查看select.

 

事务提交后数据状态

 

数据的修改永久生效，不可再撤消

 

数据以前的状态永久性丢失，无法恢复

 

所有用户（会话）都将看到操作后的结果

 

记录锁被释放，其它用户可对这些进行修改操作

 

事务的保存点（savepoints）被清除

 

保存点

 

通过保存点在当前的事务中创建标记，将来可回退到指定的标记（保存点）处，实现事务的部分回滚。

 

用法举例：

 

Insert into dept values(55,'Adv','Beijing');

 

Insert into dept values(56,'Seev','Beijing');

 

Savepoint p1;

 

Insert into dept values(57,'Acc','Tianjin');

 

……

 

Select * from dept;

 

Rollback to p1;

 

Select * from dept;

 

一旦执行了commit操作，那么savepoint自动消失。

 

数据库对象

 

常见数据库对象

 

表：存放数据的基本数据库对象，由行（记录）和列（字段）组成

 

约束条件：执行数据校验，保证数据完整性的系列规则

 

视图：表中数据的逻辑显示

 

索引：根据表中指定的字段建立起来的顺序，用于提高查询性能。

 

序列：一组有规律的整数值

 

同义词：对象的别名

 

命名规则

 

必须以字母开头

 

可以包含字母，数据，_，$，和#

 

同一用户下的对象不能重名

 

不能使用oracle保留字

 

创建表

 

创建表时必须指定表名，字段名，字段类型

 

Create table为DDL语句，一经执行不可撤消。

 

Create table scott.test(

 

Eid number(10),

 

Name varchar2(20),

 

Hiredate date default sysdate,

 

Salary number(8,2) default 0

 

);

 

数据字典：Select table_name from user_tables;

 

使用子查询创建表

 

在创建表的同时，可以将子查询的结果直接插入到其中：

 

新建表与子查询结果的字段列表必须匹配

 

新建表的字段列表可以缺省

 

Create table myemp(编号,姓名,年薪)

 

As select empno,ename,sal*12 from emp;

 

Create table myemp2

 

As select empno,ename,sal*12 annsal from emp;

 

修改表结构

 

使用alter table语句可以修改表的结构，包括：

 

添加字段，修改字段，删除字段

 

Alter 语句为DDL语句，一经执行不可撤消。

 

Alter table test1

 

Add(

 

Grade number(3);

 

Phone varchar2(20); default '无'

 

);

 

 

 

Desc test1;//查看test1表的结构

 

修改字段

 

Alter table test1

 

Modify(

 

Grade number(2),

 

Phone varchar2(15),default '12345667'

 

);

 

修改操作会受到当前表中已有数据的影响，当已有记录的相应字段只包含空值时，类型，大小都可以修改，否则修改可能失败。

 

修改的缺省值设置，只对此后新插入的记录有效。

 

清空表中数据

 

Truncate table语句用于清空表中数据

 

清除表中所有记录

 

释放表的存储空间

 

为DDL语句，一经执行不可撤消

 

Truncate table test1;

 

删除表

drop table test1;

 

重命名表

 

Rename old_name to new_name;

 

Rename test1 to test2;

 

用户定义的表

 

用户自己定义的表

 

用户自己创建并维护的一组表

 

包含了用户所需的信息

 

数据字典表

 

由oracle数据库自动创建并维护的一组表

 

包含数据库信息

 

什么是数据字典

 

数据字典是oracle数据库的核心，用于描述数据库及其所有对象。数据字典由一系列只读的表的视图组成，这些表和视图属sys用户拥有，由oracle server负责维护，用户可以通过select语句进行访问。

 

数据字典的内容

 

数据库的物理和逻辑结构，对象的定义和空间分配，完整性条件，用户，角色，权限，审计记录。

 

数据字典视图主要可分为三类

 

Dba 所有方案包含的对象信息

 

All 用户可以访问的对象信息

 

User 用户方案的对象信息

 

举例：//查看当前用户拥有的所有表的名字

 

Select table_name from user_tables;

 

--查看当前用户可以访问的所有表的名字

 

Select table_name from all_tables;

 

--查看当前用户拥有的所有对象的类型

 

Select distinct object_type from user_objects;

 

--查看所有用户拥有的所有对象类型

 

Select table_name from dba_tables;

 

约束constraint

 

Not null 非空unique key 唯一键primary key主键foreign key外键check 检查

 

相关说明

 

Oracle使用SYS_Cn格式命名约束，也可以由用户命名创建约束的时机。

 

在建表的同时创建，建表后单独添加

 

可以在表级或列级定义约束，可以通过数据字典视图查看约束。

 

唯一性约束特点：

 

唯一性约束用于确保所在的字段（或字段组合）不出现重复值。唯一性约束字段允许出现空值。Oracle会自动为唯一性约束创建对应的唯一性索引。唯一性约束既可以在字段级定义，也可以在表级定义。

 

Create table student(

 

Sid number(3) unique;

 

Name varchar2(20)

 

);

 

Create table student(

 

Sid number(3),

 

Name varchar2(20),

 

Constraint studnet_sid_un unique(sid)

 

);

 

Create table record(

 

Sid number(3),

 

Subject_name varchar2(20),

 

Record number(4),

 

Constraint record1_sid_subName_un unique(sid,subject_name)

 

);    --sid和subject_name组合起来两个字段不能同时重复，单个字段可以重复。

 

主键约束

 

联合主键

 

由多个字段组合而成的主键也称联合主键

 

联合主键中每一个字段都不能为空

 

联合主键字段组合的值不能出现重复

 

联合主键只能定义为表级约束

 

举例：

 

Create table record(

 

Student_id number(3),

 

Subject_id varchar2(20),

 

Record number(3),

 

Constraint record_stuId_subId_pk primary key(student_id,subject_id)

 

);

 

外键约束foreign key

 

外键约束特点：外键用于确保相关的两个字段之间的参照关系，以实现完整性约束；

 

外键约束通常构建于来自不同表的两个字段之间；

 

子表外键列的值必须在主表参照列值的范围内，或者为空：

 

外键参照的必须是主表的主键或者唯一键；

 

主表外键值被子表参照时，主表相应记录不允许被删除

 

检查约束check

 

定义每一行（字段）都必须满足的条件

 

以条件表达式的形式给出数据需要符合的条件

 

条件表达式中不允许出现如下内容

 

条件表达式中不允许出现如下内容

 

Currval,nextval,level,rownum待伪列

 

Sysdate,uid,user,userevn等函数

 

对其他字段值的引用

 

Create table test1(

 

Name varchar2(20),

 

Age number(3) check(age>=0 and age<=120)

 

);

 

第章DDL与数据库对象

查看约束

 

查询用户字典视图user_constrains 例如：select * from user_constraints;

 

可得到用户的所有约束

 

查询用户字典视图user_cons_columns

 

可获知约束建立在哪些字段上

 

建表后添加约束

 

Alter table tablename

 

Add constraint student_sid_pk primary  key(sid);

 

Alter table student

 

Modify(name not null);    --非空约束必须使用modify子句添加

 

Alter table student

 

Modify(

 

Sid not null;

 

Name default '0' not null

 

);

 

删除约束

 

Alter table table_name

 

Drop constraint constraint_name;

 

Alter table student drop constraint student_sid_pk;

 

删除主键约束的另一种方式：alter table table_name primary key;

 

例如：alter table student drop  primary key;

 

删除级连约束

 

在删除约束时，如果还存在与当前约束相关联的其它约束，则删除操作会失败，此时可使用cascade子句将其它关联约束一并删除。

 

Alter table table_name

 

Drop constraint constraint_name cascade;

 

例：create table empinfo(

 

Eid number(3) constraints empinfo_eid_pk primary key,

 

Ename varchar2(20)

 

);

 

Create table salary(

 

Eid number(3) references empinfo(eid)

 

);

 

Alter table empinfo drop constraints empinfo_eid_pk cascade;

 

在删除表中字段时，如果该字段处于多字段联合约束条件（联合主键，联合唯一键，存在参照当前字段的外键）中，则删除会失败，此时可使用cascade constraints 子句将与该字段相关的约束一并删除。

 

Create table record(

 

Student_id number(3),

 

Subject_id varchar2(20),

 

Record number(3),

 

Constraint record_stuId_subId_pk primary key(student_id,subject_id)

 

);

 

Alter table record drop (student_id) cascade constraints;

 

禁用约束

 

在alter table语句吕，还可使用disable constraint子句禁用已有约束。

 

也可使用cascade选项将相关联的约束也一并禁用。

 

Create table student(

 

Sid number(10),

 

Name varchar2(20),

 

Constraint student_sid_pk primary key(sid)

 

);

 

Alter table student disable constraint student_sid_pk;

 

视图

 

视图由一个或多个表中提取数据而成，是一种虚拟表，一经创建可以当作表来使用。

 

优点：简化复杂数据查询，提高运行效率，屏蔽数据库表结构，实现数据逻辑独立性，限制数据库访问，在相同数据上提供不同的视图，便于数据共享。

 

简单视图：只有一个基表，没有函数，没有分组，支持DML操作

 

复杂视图：一个或多个基表，使用函数，有分组，不一定支持DML操作。

 

创建视图：通过在create view语句中嵌入子查询的方式创建视图

 

Create or replace view myview1(编号，姓名，职位，工资) as select empno,ename,job,sal from emp where deptno=20;

 

查看视图结构Desc myview1; 删除视图：drop view myview1;

 

Create or replace    --如果不存在就创建，如果存在就替换

 

视图的原理：在视图中查询和在表中查询是一致的，因为在视图中查询的时候，每次都会先查询表中相应字段的的值，然后再在视图中查询。

 

如果在视图中出现表达式，那么应该给表达式一个别名

 

Create or replace view v1 as select empno,ename,sal*12 年薪,from emp where deptno=30;

 

Desc v1;    -- 查看视图结构

 

强制创建视图

 

可使用force选项强制创建视图，create or replace force view myview2 as select empno,ename,job,sal from emp2 where deptno=20;

 

创建复杂视图

 

Create or replace view v_sal(deptno,maxsal,minsal,avgsal) as select deptno,max(sal),min(sal),avg(sal) from emp group by deptno;

 

更新视图

 

在可更新视图上进行DML操作，可以修改基本表中数据

 

可更新视图的定义中不能使用分组函数，group by子句，distinct关键字，rownum伪列，字段的定义不能为表达式

 

由两个以上基表中导出的视图不可更新，基表中非空的列在视图定义中未包括，则不可在视图上进行insert操作。

 

在视图上进行DML操作，语法与在表上操作相同。Insert,update,delete

 

创建只读视图

 

在创建视图时，可使用with read only选项将之设置为只读。

 

Create or replace force view myview2

 

As select empno,ename,job,sal from emp2 where deptno=20 with read only;

 

临时视图

 

嵌入到SQL语句中的子查询是临时视图

 

临时视图不是数据库对象，其定义不会长久保存在数据库中，本次运行后即被清除。

 

Select rownum,a.* from (select * from emp order by sal) a where rownum<=5   --查询TOP 5

 

索引

 

一种用于提升查询效率的数据库对象；通过快速定位数据的方法，减少磁盘I/O操作；索引信息与表独立存放；oracle数据库自动使用和维护索引。

 

索引分类，唯一性索引，非唯一索引

 

创建索引的两种方式：

 

自动创建，在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引。

 

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

 

创建/删除索引

 

可使用create index语句手动创建索引

 

Create index myindex on emp(ename);

 

删除索引

 

使用drop index语句删除索引

 

操作者须是索引的所有者，或拥有者drop该index权限。删除表时相关的索引和约束将被自动删除，但视图和序列将保留drop index myindex;

 

Create index emp_idx1 on emp(ename);

 

Select * from emp where ename='KING';   -- 这个地方就会用到上面的索引

 

创建索引的原则

 

下述情况可以创建索引

 

字段取值分布范围很广，包含大量空值，经常出现在where子句或连接条件中，表经常被访问，数据量很大，且通常每次访问的数据量小于记录总量的2%-4%

 

下列情况不适合创建索引

 

表很小，字段不经常出现在where子句中，每次访问的数据量大于记录总数的2%-4%，表经常更新，被索引的字段作为表达式的一部分被引用。

 

基于函数的索引

 

基于表达式的索引被统称为基于函数的索引，索引表达式由表中的字段，常量，SQL函数和自定义函数构建而成。

 

创建函数索引

 

Create index myindex on emp(lower(ename));

 

使用函数索引select * from emp where lower(ename)='king';

 

序列

 

系统自动生成的，不重复的整数值，序列是一种数据库对象，可以被多个用户共享。典型用途是做为主键值，它对于每一行必须是唯一的。

 

序列可以代替应用程序编号，可以对序列值进行缓冲存储，以提高访问效率。

 

Create sequence sequence_name

 

Increment by n    --每次增加几

 

Start with n    --从多少开始

 

Maxvalue n | nomaxvalue    --可以设置最大值，也可以不设置

 

Minvalue n | nominvalue    --是否用最小值

 

Cycle | nocycle   --循环

 

Cache n | cache   --缓存

 

Order | noorder  --顺序

 

Create sequence mysequence1

 

Increment by 1

 

Start with 1

 

Nomaxvalue nocycle;

 

Create sequence mysequence2;

 

查询数据字典视图user_sequences可获得用户序列信息

 

使用序列

 

Nextval/currval伪列

 

Nextval伪列用于从指定的序列数值中取出下一个值

 

Currval伪列引用的是指定序列的当前值

 

Select mysequence1.currval from dual;

 

Select mysequence1.nextval from dual;

 

Insert into test1 values(mysequence1.nextval,'Tom');

 

说明：使用缓存（cache n）可提高访问效率。

 

序列在下列情况下可能出现不连续的情况：回滚，系统异常，多个表同时使用同一序列。

 

使用nocache和noorder设置会降低运行效率。

 

修改序列

 

Alter sequence sequence

 

Increment by n

 

Maxvalue n | nomaxvalue

 

Minvalue n | nomin value

 

Cycle | nocycle

 

Cache n | nocache

 

Order | noorder

 

注意事项：

 

操作者必须是序列的所有者，或者拥有alter该序列的权限；只有未来再生成的序列数受影响；序列的始值不可更改；更改中会进行一些验证，比如新的maxvalue如果小于当前的序列值就会报错。

 

删除序列

 

使用drop sequence语句删除序列，操作者须是所有者，或拥有drop的权限。

 

同义词（相当于别名）

 

同义词相当于对象的别名，使用同义词可以：方便访问其它用户的对象；缩短对象名字的长度；

 

创建同义词

 

Create synonym gt1 for emp;    --为emp表创建别名gt1

 

Select * from gt1;   --使用别名

 

Drop synonym gt1;   --删除别名

 

Create public synonym gt2 for scott.emp; //必须指定用户，当前用户为scott