完整版Oracle操作语句大全.docx
《完整版Oracle操作语句大全.docx》由会员分享，可在线阅读，更多相关《完整版Oracle操作语句大全.docx（24页珍藏版）》请在冰豆网上搜索。
完整版Oracle操作语句大全
Oracle操作语句大全
1。
desc（描述）emp描述emp这张表
2.descdept部门表
3.descsalgrade薪水等级
4。
select＊fromtable查找表中的元素
5.dual是系统中的一张空表
6。
select*fromdual
7.selectsysdatefromdual取出系统时间
8。
selectename，sal*12"annulsal”（取的别名）fromemp；查找用户姓名和用户的年薪
9。
任何含有空值的数学表达式的值都是空值
selectename,sal*12+commfromemp;
10.selectename｜|salfromemp其中的|｜相当于将sal全部转化为字符串
11.表示字符串的方法
selectename||’ajjf’fromemp；
12.如果其中有一个单引号就用2个单引号来代替他
selectename||'sakj’'lds'fromemp;
13。
selectdistinctdeptnofromemp（去除部门字段中重复的部分，关键字distinct）
14。
selectdistinctdeptno,jobfromemp;（去除这2个字段中重复的组合）
15.select＊fromdeptwheredeptno=10；取出条件（取出部门编号为10的记录）
16。
select*fromempwhereename='CLIRK';取出部门中姓名为clirk的记录（注意取出过程中ename用单引号隔开）
17。
selectename，salfromempwheresal>1500;取出部门中薪水大于1500的人的姓名
18。
selectename，sal,deptnofromempwheredeptno<〉10取出部门中的部门号不等于10的
19.selectename，sal，deptnofromempwhereename>’CBA'取出部门中员工名字大于CBA的员工（实际比较的是ACIIS码）
20。
selectename，salfromempwheresalbetween800and1500
selectename,salfromempwheresal〉=800andsal<=1500;（取出800和1500之间的数）
21.selectename,sal,commfromempwherecommisnull（选出其中的空值）
selectenmae，sal,commfromempwherecommisnotnull（选出其中的非空值）
22.selectename，sal,commfromempwheresalin（800，1500，2000）;取出这3者之中的
selectename,sal,commfromempwhereenamein（’simth'）；
23。
selectename，sal,hiredatefromempwherehiredata>'3-04月-81’；宣传符合条件的日期
24.selectename，sal,fromempwheresal〉1000ordeptno=10；找出工资薪水大于1000或者部门号等于10的员工
25。
selectename,salfromempwheresalnotin（500,1000）;查找薪水不在500到1000的员工姓名和月薪
26.selectename,salfromempwhereenamelike’％ALL%’；
selectename，salfromempwhereenamelike’_％A％';查找姓名中含有ALL的客户信息，一个横线代表一个通配符
27。
selectename，salfromempwhereenamelike'_%$％％’escape'＄’;自己指定转易字符
selectename,salfromempwhereenamelike’_%\％％'；查找中间含有％相匹配的客户信息，运用转易字符
28.select＊fromdeptorderbydeptno对表中元素按部门号排序
select*fromdeptorderbydeptnodesc默认为升序，可以用desc按降序
29.selectename，salfromempwheresal<〉1000orderbysaldesc按照查询条件来查询，并排序（asc升序排列）
30.selectename，sal*12fromempwhereenamenotlike’_％A%'andsal>800orderbysaldesc
31。
selectlower（ename）fromemp将ename都转化为小写lower是函数能将字母转化为小写
32。
selectenamefromempwherelower（ename）like’_％a％’;找出ename中所有的含有a的字符
33.selectsubstr（ename,2，3）formemp从第2个字符开始截取3个字符
34.selectchr（65）fromdual;将65转化为字符
35。
selectascii（'A’）fromdual将ACSII码转化为字符串
36。
selectround（23。
565）fromdual四舍五入
36.selectround（23,4565,2）fromdual四舍五入到第二位
37。
selectto_char（sal，'＄99。
999.9999’）fromemp按指定格式输出
selectto_char（sal,’L99,999,9999’）formempL代表本地字符
38。
selecthiredatefromemp
selectto_char（hiredate,'YYYY-MM-DDHH:
MI：
SS）fromemp;时间格式的显示
selectto_char（sysdate，’YYYY-MM—DDHH:
MI：
ss）fromdual;十二小时制显示系统时间
selectto_char（sysdate,’YYYY—MM-DDHH24：
MI:
SS）fromdual二四小时制显示系统时间
39。
selectename，hiredatefromempwherehiredate〉to_date（'2005-2—312：
32:
23','YYYY—MM-DDHH：
MI：
SS'）;
40selectsalfromempwheresal〉to_number（’$1，250.00’,’$9,999.99’）；取出比它大的一切字符串（把特定格式的数字转化成字符）
41selectename,sal+nvl（comm，0）fromemp;讲comm值为空的用0来替换，单行函数（以一条记录为条件）一条对一条
42。
selectMax（sal）fromemp；
selectMin（sal）fromemp；
selectavg（sal）fromemp;
selectsum（sal）fromemp;
selectcount（*）fromemp；查看表中一共有多少条记录
selectcount（*）fromempwheredeptno=10；查找部门10一共有多少人；
43。
selectavg（sal），deptnofromempgroupbydeptno；按部门号进行分组
selectdeptno，job，max（sal）fromempgroupbyjob,deptno;按工作和部门号进行分组；
44。
selectenamefromempwheresal=（selectmax（sal）fromemp）；子查询，查找部门中薪水最高的员工姓名
45.groupby注意：
出现在select列表中的字段,如果没有出现在组函数中必须出现在groupby子句中
46.selectavg（sal）,deptnofromempgroupbydeptnohavingavg（sal）>2000;选出部门中平均薪水大于2000的部门，
47.select*fromempwheresal〉100groupbydeptnohaving...。
。
.。
。
。
。
orderby。
。
.。
。
.。
.
先取数据--过滤数据———--—分组-———对分组限制-—----—排序
48。
selectavg（sal）fromempwheresal>2000groupbydeptnohavingavg（sal）>1500orderbyavg（sal）desc;
查找部门中平均薪水打印2000的员工并按部门号进行排序，查询分组后的平均薪水必须大于1500，查询结果按平均薪水从低到高排列
49.selectenamefromempwheresal〉（selectavg（sal）fromemp）；
查找出员工中薪水位于部门平均薪水之上的所有员工
50.selectename，salfromempjoin（selectmax（sal）max_salfromempgroupbydeptno）ton（emp.sal=t,max_salandemp.deptno=t.deptno）;
查找每个部门中薪水最高的
51。
selecte1。
ename,e2。
enamefromempe1,empe2wheree1。
mgr=e2.empno;表的自连接
52.selectdname，enamefromempcrossjoindept交叉连接，笛卡尔
SQL99中的新语法
53.selectename,dnamefromempjoindepton（emp.deptno=dept.deptno）;
54.selectename,dnamefromempjoindeptusing（deptno）;查找emp和dept表中deptno相同的部分。
55.selectename，dname,gradefromempejoindeptdon（e.deptno=d。
depno）
joinsalgrades（e。
salbetweens.losalands.hisal）（三表查找）
whereenamenotlike'_％A%'；
56。
selecte1。
ename，e2.enamefromempe1joinempe2on（e1。
mgr=e2。
deptno）;表的自连接
57。
selecte1。
ename,e2。
enamefromempe1leftjoinempe2on（e1。
mgr=e2.deptno）左外表连接
selectename,dnamefromemperightjoindeptdon（e.deptno=d。
deptno）右外连接
selectename，dnamefromempefulljoindeptdon（e.deptno=d.deptno）全连接
58。
求部门中薪水最高的
selectename,salfromempjoin（selectmax（sal）max_sal，deptnofromempgroupbydeptno）t
on（emp.sal=t。
max_salandemp。
deptno=t.deptno）；
59.求部门中薪水等级的平均值
selectdeptno，avg（grade）from（selectdeptno,ename，grade,fromempjoinsalgradeson（emp.salbetweens。
losalands。
hisal））tgroupbydeptno;
60。
查找雇员中哪些是经理人
selectenamefromempwhereempnoin（selectmgrfromemp）；
61。
selectdistincte1.salfromempe1joinempe2on（e1.sal〈e2。
sal）;自连接（不用组函数求出最高薪水）
selectdistinctsalfromempwherenotin（selectenamefrome1。
salfromempe1joinempe2on（e1。
sal〈e2。
sal））；
62.selectdeptnofrom（selectavg（sal）max_saldeptnofromempgroupbydeptno）wheremax_sal=（selectmax（avg_sal）from（select
avg（sal）avg_saldeptnofromempgroupbydeptno））；查找部门中部门薪水最大的部门号
63。
求平均薪水最大的部门的部门编号
selectdeptno，avg_salfrom（selectavg（sal）avg_sal,deptnofromempgroupby
deptno）whereavg_sal=（selectmax（avg（sal））fromempgroupbydeptno）;
DML语句：
更、删、改、查
创建权限，connsys/adminassysdba
grantcreatetable,createviewtoscott;
首先在C：
下面建个文件夹备份文件
1。
createNewUser方法
1.—-backupscott
exp
2.createuser（创建用户）用超级管理员模式进入
createuseryunidentifiedbykang1234defaulttablespaceusersquota10Monusers;
grantcreatesession,createtable,createviewtokafei（给kafei这个用户授予权限）
3.importthedata（导入备份数据）
imp
2。
insert
insertintodeptvalues（50，'game'，'bj’）插入一条记录
insertintodept2（deptno，dname）values（78，’games’）；插入指定的几条记录
insertintodept2select＊fromdept插入指定的表（表结构要一样）
rollback；回退
createtableemp2asselect*fromemp;创建数据库表2来备份emp这张表
3.updateemp2setsal=sal*12wheredeptno=10；update的用法
4.deletefromdept2wheredeptno〈25;删除语句的用法
DDL语言
1.创建表：
createtablet（avarchar2（10））;
2.droptablet删除表
3。
commit所有的提交，所有修改都结束了.对于rollback无效，一个事务开始于第1条DML语句
碰到执行DDLDCL语句事务自动提交对于rollback无效
建表语句
建学生信息表:
createtablestu
（idnumber（6），
namevarchar2（20）constraintstu_name_nnnotnull，
sexnumber
（1），
agenumber（3）,
sdatedate,
gradenumber
（2）default1，
classnumber（4）,
emailvarchar2（50）unique（唯一约束）
）;
非空唯一主键外键chick
createtablestu
（idnumber（6）primarykey，（主键约束）
namevarchar2（20）constraintstu_name_nnnotnull，（非空约束）
sexnumber
（1）,
agenumber（3），
sdatedate，
gradenumber
（2）default1，
classnumber（4）,
emailvarchar2（50），
constraintstu_name_uuiunique（email,name）组合性约束
）；
主键约束方法二
createtablestu
（idnumber（6）,
namevarchar2（20）constraintstu_name_nnnotnull,（非空约束）
sexnumber
（1），
agenumber（3）,
sdatedate，
gradenumber
（2）default1,
classnumber（4）referencesclass（id）,（参考class这张表,参考字段）
emailvarchar2（50）,
constraintstu_id_pkprimarykey（id），
constraintstu_name_uuiunique（email，name）组合性约束
）;
外键约束
createtableclass
（idnumber（4）primarykey,（id为被参考字段,被参考的字段必须是主键）
namevarchar2（20）notnull
）
createtablestu
（
idnumber（6）,
namevarchar2（20）constraintstu_name_nnnotnull，（非空约束）
sexnumber
（1）,
agenumber（3），
sdatedate,
gradenumber
（2）default1,
classnumber（4）
emailvarchar2（50），
constraintstu_class_fkforeignkey（class）referencesclass（id）,
constraintstu_id_pkprimarykey（id）,
constraintstu_name_uuiunique（email，name）组合性约束
）；
像外键中插入关键字，
1.insertintoclassvalues（1000，'c1'）；
2.insertintostu（id,name，class，email）values（1,'a',1000,’a’）；
3.altertablestuadd（addrvarchar（20））;添加表的结构
4.altertablestudrop（addr）；删除表结构
5。
altertablestumodify（addrvarchar2（150））;修改精度
6.altertablestudropconstraintstu_class_fk；删除约束条件
7.altertablestuaddconstraintstu_class_fkforengnkey（class）referencesclass（id），添加约束条件
查找当前用户下有哪些表和哪些视图及哪些约束
8.selecttable_namefromuser_names
9。
selectview_namefromview_names
10。
selectconstraint_name,table_namefromuser_constraints;
descdictionary数据字典表
descuser_tables当前用户下面有多少张表
selecttable_namefromuser_tables;查找当前用户有多少张表
索引：
创建索引
createindexidx_stu_emailonstu（email）;
dropindexidx_stu_email；
查找索引
selectindex_namefromuser_indexes；
索引读的速度快了，插入速度变慢
view视图
视图赠加了维护的量
序列：
createtablearcticle
（idnumber，
titlevarchar2（1024），
contlong
）；
序列的创建sequence产生独一无二的序列,而且是oracle独有的
createsequenceseq;
selectseq。
nextvalfromdual；查找序列号
insertintoarcticlevalues（seq。
nextval,'a’，'b'）;往表中插入序列
数据库设计的3范式
第一范式:
设计任何表都要有主键，列不可分
第二范式：
如果有2个主键的话,不能存在部分依赖
第三范式，不能存在传递依赖
PL—sql
例子1：
SQL〉setserveroutputon;
SQL>begin（必要的-—程序开始执行）
2dbms_output.put_line（'helloworld’）；
3end;（结束）
4/
例子2：
SQL>declare
2v_namevarchar2（20）；
3begin
4v_name：
=’myname’；
5dbms_output。
put_line（v_name）;
6end；
7/
myname
例子3：
SQL〉declare
2v_numnumber：
=0；
3begin
4v_num：
=2/v_num；
5dbms_output。
put_line（v_num）；
6end；
7/
declare
*
ERROR位于第1行:
ORA-01476：
除数为0
ORA—06512:
在line4
例子4：
declare
v_numnumber：
=0;
begin
v_num:
=2/v_num；
dbms_output。
put_line（v_num）；
exception
whenothersthen
dbms_output。
put_line（'error’）;
end；
/
变量声明的规则
1.变量名不能够使用保留字,
升级会员 