《Oracle_SQL语句大全---尚学堂马士兵讲义6474.docx》由会员分享，可在线阅读，更多相关《Oracle_SQL语句大全---尚学堂马士兵讲义6474.docx（54页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 Oracle SQL语句大全2009-04-13 15:391. desc(描述述） empp 描描述emp这这张表2.ddesc deppt 部门门表3.deesc saalgradde 薪水等等级4.seelect *fromm tablle 查找表表中的元素55.duall 是系统中的的一张空表 （可以用于于计算各种数数学表达式）6.select *from dual7.select sysdate from dual 取出系统时间8.select ename,sal*12 annul_sal(取的别名） from emp; 查找用户姓名和用户的年薪9.任何含有空值的数学表达式的值都是
2、空值select ename,sal*12+comm from emp;10.select ename|sal from emp 其中的| (字符串连接符) 相当于将sal全部转化为字符串11.表示字符串的方法select ename |ajjf from emp;12.如果其中有一个单引号就用2个单引号来代替他select ename|sakj ldsfrom emp;13.select distinct deptno from emp (去除部门字段中重复的部分,关键字distinct)14.select distinct deptno,job from emp;(去除这2个字段中重复的组
3、合)15.select *from dept where deptno=10; 取出条件(取出部门编号为10的记录)16.select * from emp where ename=CLIRK; 取出部门中姓名为clirk的记录(注意取出过程中ename用单引号隔开)17.select ename,sal from emp where sal1500; 取出部门中薪水大于1500的人的姓名18.select ename,sal,deptno from emp where deptno 10 取出部门中的部门号不等于10的19.select ename,sal,deptno from emp w
4、here enameCBA 取出部门中员工名字大于CBA的员工(实际比较的是ACIIS码)20.select ename,sal from emp where sal between 800 and 1500 select ename,sal from emp where sal=800 and sal3-04月-81;宣传符合条件的日期24.select ename,sal,from emp where sal1000 or deptno=10; 找出工资薪水大于1000或者部门号等于10的员工25.select ename,sal from emp where sal not in(500
5、,1000); 查找薪水不在500到1000的员工姓名和月薪26.select ename,sal from emp where ename like %ALL%; 查找姓名中含有ALL的客户信息 %表示0个或多个字母 select ename,sal from emp where ename like _A%; 查找第二个字母是A的客户信息27. select enamee,sal from emp wwhere enamee likee _%$% eescapee $; 自己指定定转义字符 sselectt enamme,sall fromm emp wheree enamme likke
6、 _%; 查找中中间含有%相相匹配的客户户信息，运用用转易字符28.seleect * from dept orderr by ddeptnoo 对表表中元素按部部门号排序 sellect *from dept orderr by ddeptnoo descc 默默认为升序aasc，可以以用descc按降序299.seleect enname,ssal frrom emmp wheere saal 11000 oorder by saal dessc, enname ddesc 按照查询询条件来查询询，并排序（aasc升序排排列）30.selecct enaame,saal*12 from e
7、mp wwhere enamee not like _%A% andd sal800 oorder by saal dessc31.seleect loower(eename) fromm emp 将enamme都转化为为小写 loweer是函数能能将字母转化化为小写，upperr(enamme)32.selecct enaame frrom emmp wheere loower(eename) likee _%aa%; 找找出enamme 中所有有的含有a的的字符33.selecct subbstr(eename,2,3) form emp 从第2个字字符开始截取取3个字符334.selle
8、ct cchr(655) froom duaal; 将655转化为字符符35.seelect asciii(A) fromm duall 将将ACSIII码转化为字字符串36.selecct rouund(233.565)from dual 四四舍五入366.seleect roound(223,45665,2)ffrom ddual 四四舍五入到第第二位(第二二个参数默认认为0)377.seleect too_charr(sal,$99,999.99999) fromm emp 按指定格式式输出(9代代表一位数字字) sselectt to_cchar(ssal,LL99,9999.9999
9、) forrm empp L代表本本地字符(￥) 0代表表占位符388.seleect hiiredatte froom empp seelect to_chhar(hiiredatte,YYYYY-MMM-DD HHH:MI:SS) ffrom eemp; 时间间格式的显示示 seelect to_chhar(syysdatee,YYYYY-MM-DD HHH:MI:sss) frrom duual; 十二二小时制显示示系统时间 sellect tto_chaar(syssdate,YYYYY-MM-DDD HH224:MI:SS) ffrom ddual 二四小小时制显示系系统时间399.
10、seleect enname,hhiredaate frrom emmp wheere hiiredatte tto_datte(20005-2-3 12:32:233,YYYYY-MMM-DD HHH:MI:SS); 把特定定格式字符串串转换成日期期40 seelect sal ffrom eemp whhere ssaltoo_numbber($1,2500.00,$9,9999.999); 取取出比它大的的一切字符串串（把特定格格式的数字转转化成字符）41 select ename,sal+nvl(comm,0) from emp; 讲comm值为空的用0来替换，单行函数（以一条记录为条
11、件）一条对一条组函数42.sselectt Max(sal) from emp; sellect MMin(saal) frrom emmp; selecct avgg(sal) fromm emp; seelect sum(ssal) ffrom eemp; seleect coount(*) froom empp; 查看表表中一共有多多少条记录 sellect ccount(*) frrom emmp wheere deeptno=10; 查找找部门10一一共有多少人人；43.seleect avvg(sall),depptno ffrom eemp grroup bby depptno;
12、 按部门门号进行分组组 seelect deptnno,jobb,max(sal) from emp ggroup by joob,depptno; 按工作和部部门号进行分分组；44.selecct enaame frrom emmp wheere saal=(seelect max(ssal) ffrom eemp); 子查询，查找找部门中薪水水最高的员工工姓名45.groupp by 注注意：出现在在selecct列表中的的字段，如果果没有出现在在组函数中必必须出现在ggroup by子句中中where是过过滤单条语句句,haviing可以过过滤分组。46.seleect avvg(sal
13、l),depptno ffrom eemp grroup bby depptno hhavingg avg(sal)2000; 选选出部门中平平均薪水大于于2000的的部门，477.seleect * from emp whhere ssal1000 grooup byy depttno haaving .orderr by. 先先取数据-过滤数据-分组-对分组限限制-排序序48.seelect avg(ssal) ffrom eemp whhere ssal12200 grroup bby depptno hhavingg avg(sal)1500 orderr by aavg(saal)
14、deesc; 查找部门门中平均薪水水大于1200的员员工，并按部门号号进行排序，查查询分组后的的平均薪水必必须大于15500，查询询结果按平均均薪水从低到到高排列子查询（把它当当成一张表）49.seleect enname ffrom eemp whhere ssal(sselectt avg(sal) from emp); 查找找出员工中薪薪水位于部门门平均薪水之之上的所有员员工50.sselectt enamme,sall fromm emp join(selecct maxx(sal) max_sal ffrom eemp grroup bby depptno) t on(emp.ssa
15、l=t,max_ssal annd empp.depttno=t.deptnno); 查找找每个部门中中薪水最高的的51.seelect e1.enname,ee2.enaame frrom emmp e1,emp ee2 wheere e11.mgr=e2.emmpno; 表的自连接接(当成两张张表来用)1999 SQQL标准where语句句里不写“连接条件”，只写数据据过滤条件。join onn(表间连接接条件)，wwhere写写过滤条件，思思路清晰。52.seleect dnname,eename from emp ccross join dept 交叉连接接，笛卡尔SSQL99中中的新
16、语法553.sellect eename,dnamee fromm emp join ddept oon(empp.depttno=deept.deeptno); /oon(连接条条件)54.selecct enaame,dnname ffrom eemp jooin deept ussing(ddeptnoo); 不推推荐 查找eemp和deept表中ddeptnoo相同的部分分。55.sselectt enamme,dnaame,grrade ffrom eemp e join ddept dd on(e.ddeptnoo=d.deepno) join ssalgraade s on(e.
17、ssal beetweenn s.loosal aand s.hisall) wherre enaame noot likke _%A%; (三表表查找）566.seleect e11.enamme,e2.enamee fromm emp e1 jooin emmp e2 on(e11.mgr=e2.deeptno); 表的自自连接57.selecct e1.enamee,e2.eename from emp ee1 lefft joiin empp e2 oon(e1.mgr=ee2.emppno) 左左外表连接/ 可以将将左边表多余余的数据拿出出来，右外表表连接：riight (outer
18、r) joiin on sellect eename,dnamee fromm emp e rigght jooin deept d on(e.ddeptnoo=d.deeptno)右外连接 sellect eename,dnamee fromm emp e fulll joiin deppt d oon(e.ddeptnoo=d.deeptno)全连接588.求部门中中薪水最高的的 seelect enamee,sal from emp jjoin (selecct maxx(sal) max_sal, deptnno froom empp grouup by deptnno) t on (
19、emp.ssal=t.max_ssal annd empp.depttno=t.deptnno);-求部门平平均薪水的等等级select deptnno, avvg_sall, graade frrom (selectt depttno, aavg(saal) avvg_sall fromm emp groupp by ddeptnoo)t join saalgradde s oon (t.avg_ssal beetweenn s.loosal aand s.hisall);-求部门门中各成员的的平均薪水等等级select deptnno,enaame,grrade from emp join
20、 saalgradde s oon(empp.sal betweeen s.losall and s.hissal);59.求部门中中薪水等级的的平均值 seleect deeptno,avg(ggrade) fromm(seleect deeptno,enamee,gradde from emp jjoin ssalgraade s on(emmp.sall betwween ss.losaal andd s.hiisal)t grooup byy depttno;600.查找雇员员中哪些是经经理人 selecct enaame frrom emmp wheere emmpno iin(se
21、llect ddistinnct mggr froom empp);61.selecct disstinctt e1.ssal frrom emmp e1 join emp ee2 on(e1.saale2.sal); 自连接(不用组函数数求出最高薪薪水） selecct disstinctt sal from emp wwhere sal nnot inn (sellect eename, e1.ssal frrom emmp e1 join emp ee2 on(e1.saal( sellect mmax(saal) frrom emmp wheere emmpno nnot inn (s
22、elecct disstinctt mgr from emp wwhere mgr iis nott nulll);DML（数据操操作语言）语语句：更、删删、改、查创创建权限， conn sys/aadmin as syysdba grantt creaate taable,ccreatee vieww to sscott;首先在C:下面建个文文件夹备份文文件一.creaateNewwUser方方法1.-backuup scoott -备份sscott eexp -执执行exp.exe文件件/c:temppexp ,.DMMP文件。在在c:teemp下2.creatte useer(创建用用户
23、）用超级级管理员模式式进入 creatte useer 用户名名 idenntifieed by 密码 deffault tableespacee userrs quoota 100M on userss; ggrant creatte sesssion,creatte tabble,crreate view to 用户户名 -授予权限 dropp userr 用户名 -删除除用户3.iimportt the data(导入备份数数据） imp二.inserrtinseert innto deept vaalues (50,game,bj) 插入一一条记录innsert into dept22
24、 (depptno,ddname) valuues (778,gaames); 插入指指定的几条记记录inseert innto deept2 sselectt *froom deppt 插入指指定的表（表表结构要一样样）rollbacck; 回退（取取消上一步操操作）creeate ttable emp2 as seelect * froom empp; 备份empp这张表三. updaate emmp2 seet sall=sal*12 whhere ddeptnoo=10; uppdate的的用法四. deleete frrom deept2 wwhere deptnno25 ; 删除语
25、语句的用法rownum r 只能和和和或者=或者=transacction 事务:一个个事务起始于于一个DMLL语句DDL（数据定定义）语言1.creaate taable tt(a vaarcharr2(10); 创建表:2.dropp tablle t 删除表mmit 所有的(上上一条事务的的修改)提交交，所有修改改都结束了。对对于rolllback(rollbback会回回到原始状态态)无效，一一个事务开始始于第1条DDML语句碰到执行DDLL DCL语语句事务自动动提交(自动动执行commmit)对对于rolllback无无效，正常断开连接，自自动提交。 非正常提交交，自动回滚滚rol
26、lbbackDCL：数据控控制语句-如grrant 授授权建表语句建学生生信息表：ccreatee tablle stuu(id nnumberr(6),nname vvarchaar2(200) connstraiint sttu_namme_nn not nnull,sex nnumberr(1),aage nuumber(3),sddate ddate,ggrade numbeer(2) defauult 1,classs numbber(4),emaiil varrchar22(50) uniquue (唯一约束束);5个约束条件非空not nnull唯一uniquue（不能插插入重复
27、值），主键primaary keey（可以唯唯一标识整条条记录）： 非空，唯唯一，数字 ，可以使用用多个字段的的组合作为主主键外键 foreeign kkey(cllass) referrencess classs(id), refeerencees参考 claass中的iid，被参考考的字段必须须是主键。 被参考的记记录是无法删删除的：两张张表，在一张张表插入数据据的时候，不不允许插入另另一张表中一一个字段没有有的数据。默默认添加。check：检检查约束 create tablee stu(id numbber(6) primmary kkey,name varchhar2(220) coo
28、nstraaint sstu_naame_nnn not nnull, 约束名coonstraaintseex nummber(11),agee numbber(3),sdatte datte,graade nuumber(2) deefaultt 1,classs nummber(44),emaail vaarcharr2(50),consstrainnt stuu_namee_emaiil_uuii uniquue(emaail,naame) （表级约束束）组合性约约束，name和和emaill的组合不能能重复);主键约束方法二二creatte tabble sttu(id numbeer
29、(6),name varchhar2(220) coonstraaint sstu_naame_nnn not null,(非空约束束)sex numbeer(1),age nnumberr(3),ssdate date,gradee numbber(2) defaault 11,classs nummber(44) refferencces cllass(iid),(参参考classs 这张表表，参考字段段)emaiil varrchar22(50),consttraintt stu_id_pkk primmary kkey(idd),consttraintt stu_name_uui uu
30、niquee(emaiil,namme) 组合合性约束);外键约束creeate ttable classs(id numbber(4) primmary kkey, (id为为被参考字段段,被参考的的字段必须是是主键)naame vaarcharr2(20) not null)create tablee stu(id nuumber(6),naame vaarcharr2(20) consstrainnt stuu_namee_nn nnot nuull,(非非空约束)ssex nuumber(1),agge nummber(33),sdaate daate,grrade nnumberr(
31、2) ddefaullt 1,cclass numbeer(4)eemail varchhar2(550),coonstraaint sstu_cllass_ffk forreign key(cclass) refeerencees claass(idd), refeerencees参考consstrainnt stuu_id_ppk priimary key(iid),coonstraaint sstu_naame_uuui uniique(eemail,name) 组合性约约束);像外键中插入关关键字，1.inserrt intto claass vaalues(1000,c1);2.in
32、nsert into stu(iid,namme,claass,emmail) valuees(1,a,10000,aa);3.alterr tablle stuu add(adddr vaarcharr(20);添加表的的结构4.aalter tablee stu drop(aaddr); 删除表结结构5.allter ttable stu mmodifyy(addrr varcchar2(150);修改精度度6.altter taable sstu drrop coonstraaint sstu_cllass_ffk; 删除除约束条件77.alteer tabble sttu addd c
33、onsstrainnt stuu_classs_fk foreiign keey(claass) rrefereences classs(id),添加约束条条件查找当前用户下下有哪些表和和哪些视图及及哪些约束: 数据字典表user_taables：当前用户下下有多少张表表。user_viiews：当当前用户下有有多少个视图图。user_coonstraaints：当前用户下下有多少个约约束。user_inndexess：当前用户下下有多少个索索引。数据字典表的表表：dicttionarry8.selecct tabble_naame frrom usser_naames9.selecct vi
34、eew_namme froom vieew_nammes10.selecct connstraiint_naame,taable_nname ffrom uuser_cconstrraintss;descc dicttionarry数据字典典表descc userr_tablles当前用用户下面有多多少张表seelect tablee_namee fromm userr_tablles; 查查找当前用户户有多少张表表索引： 加主键键或者唯一约约束的时候ooraclee会自动为该该字段加索引引。创建索引引creatte inddex iddx_stuu_emaiil on stuu(emaiil
35、);drrop inndex iidx_sttu_emaail;查找找索引sellect iindex_name from user_indexxes;索引读的速度快快了，插入速速度变慢。经经常需要查询询的字段可以以建立索引。优化数据库表首首选。view 视图图（子查询，虚虚表）视图赠赠加了维护的的量create view v$_deept_avvg_sall_infoo as (selecct .)create view v$_sttu as selecct id, namee, agee fromm stu; -只给客户户看非关键的的信息非必要尽量不适适用，因为会会带来维护繁繁琐的问题。尽
36、量不要用视图图更新数据。因因为视图很可可能从多个表表拿数据。序列(sequuence)：序列对象 产生唯一的的，不间断的的序列,一般般用于主键ccreatee tablle arccticlee(id nnumberr,titlle varrchar22(10244),connt lonng);序列的创创建sequuence产产生独一无二二的序列，而而且是oraacle独有有的creaate seequencce seqq;selecct seqq.nexttval ffrom ddual; 查找序列号号inserrt intto arccticlee valuues(seeq.nexxtval,a,b);往往表中插入序序列dorp seequencce seqq; -删除序列列。数据库设计的33范式（姓范范的设定的规规则）：实际际当中可以打打破不要存在冗余数数据：一条数数据只存放在在一个表里，要要用这条数据据，通过该数数据的主键和和其他表联系