sql语句的编程手册(1)

一、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缓冲区中的命令写到操作系统下的文本文件， 
并调用操作系统提供的编辑器执行修改。 

------------- 
22 数据操纵 
数据的插入 
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 视图名