一、SQL PLUS  
1 引言  

SQL命令  
以下17个是作为语句开头的关键字：  
alter  drop  revoke  
audit  grant  rollback*  
commit*  insert  select  
comment  lock  update  
create  noaudit  validate  
delete  rename  
这些命令必须以“;”结尾  
带*命令句尾不必加分号，并且不存入SQL缓存区。  

SQL中没有的SQL*PLUS命令  
这些命令不存入SQL缓存区  
@  define  pause  
#  del  quit  
$  describe  remark  
/  disconnect  run  
accept  document  save  
append  edit  set  
break  exit  show  
btitle  get  spool  
change  help  sqlplus  
clear  host  start  
column  input  timing  
compute  list  ttitle  
connect  newpage  undefine  
copy  

---------  
2 数据库查询  

数据字典  
TAB 用户创建的所有基表、视图和同义词清单  

DTAB 构成数据字典的所有表  

COL 用户创建的基表的所有列定义的清单  

CATALOG 用户可存取的所有基表清单  

select * from tab;  

describe命令 描述基表的结构信息  
describe dept  

select *  
from emp;  

select empno,ename,job  
from emp;  

select * from dept  
order by deptno desc;  

逻辑运算符  
= !=或<> > >= < <=  
in  
between value1 and value2  
like  
%  
_  
in null  
not  
no in,is not null  

谓词in和not in  
有哪些职员和分析员  
select ename,job  
from emp  
where job in ('clerk','analyst');  

select ename,job  
from emp  
where job not in ('clerk','analyst');  

谓词between和not between  
哪些雇员的工资在2000和3000之间  
select ename,job,sal from emp  
where sal between 2000 and 3000;  

select ename,job,sal from emp  
where sal not between 2000 and 3000;  

谓词like,not like  
select ename,deptno from emp  
where ename like 'S%';  
(以字母S开头)  
select ename,deptno from emp  
where ename like '%K';  
(以K结尾)  
select ename,deptno from emp  
where ename like 'W___';  
(以W开头，后面仅有三个字母)  
select ename,job from emp  
where job not like 'sales%';  
(哪些雇员的工种名不以sales开头)  

谓词is null,is not null  
没有奖金的雇员（即commision为null）  
select ename,job from emp  
where comm is null;  

select ename,job from emp  
where comm is not null;  

多条件查询  
select ename,job  
from emp  
where deptno=20  
and job!='clerk';  

表达式  
+ - * /  

算术表达式  
选择奖金高于其工资的5%的雇员  
select ename,sal,comm,comm/sal from emp  
where comm>.05*sal  
  order by comm/sal desc;  

日期型数据的运算  
add two days to 6-Mar-87  
6-Mar-87 + 2 = 8-Mar-87  
add two hours to 6-Mar-87  
6-Mar-87 + 2/24 = 6-Mar-87 and 2hrs  
add 15 seconds to 6-Mar-87  
6-Mar-87 + 15/(24*60*60) = 6-Mar-87 and 15 secs  

列名的别名  
select ename employee from emp  
  where deptno=10;  
（别名：employee）  
select ename,sal,comm,comm/sal "C/S RATIO" from emp  
  where comm>.05*sal  
  order by comm/sal desc;  

SQL命令的编辑  
list or l 显示缓冲区的内容  
list 4 显示当前SQL命令的第4行，并把第4行作为当前行，在该行号后面有个*。  
change or c 用新的内容替换原来在一行中第一次出现内容  
SQL>c/(...)/('analyst')/  
input or i 增加一行或多行  
append or a 在一行后追加内容  
del  删除当前行 删除SQL缓冲区中的当前行  
run  显示并运行SQL缓冲区中的命令  
/  运行SQL缓冲区中的命令  
edit  把SQL缓冲区中的命令写到操作系统下的文本文件，  
并调用操作系统提供的编辑器执行修改。  

-------------  
3 数据操纵  
数据的插入  
insert into dept  
  values (10,'accounting','new york');  

insert into dept (dname,deptno)  
  values ('accounting',10);  

从其它表中选择插入数据  
insert into emp (empno,ename,deptno)  
select id,name,department  
from old_emp  
where department in(10,20,30,40);  

使用参数  
insert into dept  
  values(&deptno,&dname,&loc);  
执行时，SQL/PLUS对每个参数将有提示用户输入  

参数对应日期型或字符型数据时，可在参数上加引号，输入时就可不用引号  
insert into dept  
  values(&deptno,'&dname','&loc');  

插入空值（NULL）  
insert into dept  
  values(50,'education',null);  

插入日期型数据  
日期型数据缺省格式：DD-MON-YY  
insert into emp  
(empno,ename,hiredate)  
values(7963,'stone','07-APR-87');  

系统时间：SYSDATE  
insert into emp  
(empno,ename,hiredate)  
values(7600,'kohn',SYSDATE);  

数据更新  
update emp  
set job='manager'  
where ename='martin';  

update emp  
set job='market rep'  
where ename='salesman';  

update emp  
set deptno=40,job='market rep'  
where job='salesman';  

数据删除  
delete emp  
where empno=765;  

更新的提交  
commit  

自动提交方式  
set autocommit on  
如果状态设为开，则使用inesrt,update,delete会立即提交。  

更新取消  
rollback  

两次连续成功的commit之间的操作，称为一个事务  

---------------  
4  创建基表、视图  
创建基表  
create table dept  
(deptno number(2),  
dname char(14),  
loc char(13));  

数据字典会自动更新。  
一个基表最多254列。  

表名列名命名规则：  
限制  
第一个字符必须是字母，后面可任意（包括 $ # _ 但不能是逗号）。  
名字不得超过30个字符。  

唯一  
某一用户的基表名必须唯一，不能是ORACLE的保留字，同一基表的列名互不相同。  

使用双引号  
如果表名用双引号括起来，则可不满足上述规则；  
只有使用双引号，才能区别大、小写；  
命名时使用了双引号，在以后的操作也必须使用双引号。  

数据类型：  
char(n)    （不得超过240字符）  
number(n,d)  
date  
long    （最多65536字符）  
raw    （二进制原始数据）  

空值处理  
有时要求列值不能为空  
create table dept  
(deptno number(2) not null,  
dname char(14),  
loc char(13));  

在基表中增加一列  
alter table dept  
add (headcnt number(3));  

修改已有列属性  
alter table dept  
modify dname char(20);  
注：只有当某列所有值都为空时，才能减小其列值宽度。  
只有当某列所有值都为空时，才能改变其列值类型。  
只有当某列所有值都为不空时，才能定义该列为not null。  
例：  
alter table dept modify (loc char(12));  
alter table dept modify loc char(12);  
alter table dept modify (dname char(13),loc char(12));  

创建视图  
create view managers as  
select ename,job,sal  
from emp  
where job='manager';  
为视图列名取别名  
create view mydept  
(person,title,salary)  
as select ename,job,sal  
from emp  
where deptno=10;  

with check option选项  
使用with check option，保证当对视图插入或更新数据时，  
该数据必须满足视图定义中select命令所指定的条件。  
create view dept20 as  
select ename,job,sal,deptno  
from emp  
where deptno=20  
with check option;  
在做下述操作时，会发生错误  
update dept20  
set deptno=30  
where ename='ward';  

基表、视图的拷贝  
create table emp2  
as select * from emp;  

基表、视图的删除  
drop table 表名  
drop view 视图名