Oracle PL/SQL 编程手册(SQL大全)

最新推荐文章于 2021-04-08 00:00:31 发布

谷白

最新推荐文章于 2021-04-08 00:00:31 发布

阅读量1.5k

点赞数 1

分类专栏：数据库文章标签： sql 数据库 plsql oracle 数据

本文链接：https://blog.csdn.net/qq_23101033/article/details/52868744

版权

一、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视图名

------------
5SQL*PLUS报表功能
SQL*PLUS的一些基本格式命令
columndeptnoheadingdepartment

columnenameheadingname

columnsalheadingsalary

columnsalformat$99,999.00

ttitlesamplereportfor｜hitechcorp

btitlestrictlyconfidential

breakondeptno

computesumofsalondeptno

run

表头和表尾
ttitlesamplereportfor｜hitechcorp
btitlerightstrictlyconfidential