oracle10g学习笔记2 5怎么样可以取得演示数据表

1、纲 2、内 、SQL概 、 、SQL的分 、SQLPlus常用命 、setlinesize 、 、run/ 、 、 、 、如何直接执行sql 、between…and…操作 、is 、 、 、 、 、 、 、 、 、 、 、 、 、 、 、case…when…then 、 、 、聚合函 、 、 、 、 、 、group 、 、select语句总 、SQL92语 、SQL99语 、子查 、在where语句中使用子查询，也就是在where语句中加入select语 、在from语句中使用子查询，可以将该子查询看做一张 、union和 、rownum的使 、rownum隐含字 、取得前5条数 、取得薪水最高的前5 、采用rownum进行分 、 、 、 、 、t_student和t_classes完整示 、索 、视 、过 、游 、常用的DBA命 、给用户、修改用户 、分配表空间usres给用 SQLOracleSQLplusSQLplusunionrownum表Oracle体系结构、概念及常用DBA、SQLSQLsequel，SQLStructuredQueryLanguage)，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准。（DBMSSQLServer、MySql、Sybase、informix、DB2、interbase、PostgreSql 张 李 以由一个字段或多个成的主键称为单一主

表里可以有0条或多条

、SQL（DQL（DML，（DDLselectselect*from或selecttable_namefromdescdesc员工注：DEPTNO字段是外键，DEPTNO的值来源于dept表的主键，起到了约束的作用selectenameselectenamefromselectempno,selectempno,enamefromselect*select*fromselect语句的后面列出员工的编号，和年selectselectempno,ename,sal*12fromselectselectempnoas员工编号,enameas员 ,sal*12as年薪from可以采用asasselectselectempno员工编号,ename员 ,sal*12年薪from、SQLPlus、setlinesizesetlinesize80setlinesize300,300、、run、、getsql语句放到缓存区中，采用/或rrun、sql条件查询需要用到where语句，where必须放到from语句表的后面=<>或<>between…andis为not可以取非，主要用在is或inselectempno,ename,selectempno,ename,salfromempwhereSELECTSELECTempno,enameFROMempwhereselectempno,enamefromempselectempno,enamefromempwhereselectselectempno,enamefromempwhereselectselectempno,enamefromempwhereselectselectempno,enamefromempwhereselectempno,ename,selectempno,ename,salfromempwheresal<>selectselectempno,ename,salfromempwheresal!=selectselectempno,ename,salfromempwheresal<>selectselectempno,enamefromempwherejob<>、betweenandselectempno,ename,salfromempwheresal>=1600selectempno,ename,salfromempwheresal>=1600andsal<selectselect*fromempwhereselectempno,ename,salfromempwheresalbetween1600and查询薪水为selectempno,ename,salfromempwheresalbetween1600andbetweenand、isNullnullnull的字段，采isnullselect*select*fromempwherenull，而不是空串，null和空串是不一样selectselect*fromempwherecommis、select*fromempselect*fromempwherejob='MANAGER'andsal>、select*fromempwherejob='MANAGER'orselect*fromempwherejob='MANAGER'orselect*fromempwheresal>select*fromempwheresal>1800anddeptno=20ordeptno=1800的数据也被查询上来了，原因是表达式的优先级导sal1800anddeptno20deptno30员工合并过来，所以是不select*fromempwheresal>select*fromempwheresal>1800and(deptno=20ordeptno=、inorinjobmanagerjobsalesman16003000selectselect*fromempwheresalin(1600,、select*fromempwheresalselect*fromempwheresal<>1600andsal<>selectselect*fromempwherenot(sal=1600orsal=select*fromempwhereselect*fromempwheresalnotin(1600,select*fromselect*fromempwherecommis 、Like可以实现模糊查询，like支持%以Nselect*以Nselect*fromempwhereenamelike查 select*fromempwhereenamelikeselectselect*fromempwhereenamelikeselect*fromempwhereenameselect*fromempwhereenamelikeLike中%select*select*fromempwhereenamelikeorderby子句，orderby后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，orderby默认采用升序，如果存在whereorderby必须放到where语句的后select*fromselect*fromemporderbyselectselect*fromempwherejob='MANAGER'orderby如果包含whereorderbywhere后面，如果没有whereorderby放到表的selectselect*fromemporderbysalwhereselect*select*fromemporderbysalselectselect*fromemporderbysalselect*fromselect*fromemporderbyjobdesc,salselectselect*fromemporderbynull、selectlower(ename)fromselectlower(ename)from、select*fromempselect*fromempwhere、select*fromempwhereselect*fromempwheresubstr(ename,1,、selectlength(ename),enamefromselectlength(ename),enamefromempwhere、select*fromselect*fromempwhere 、select*fromempwhereselect*fromempwhereHIREDATE='20-2-selectselect*fromempwherehiredate=to_date('1981-02-20','YYYY-MM-select*fromempwherehiredate=to_date('1981-02-2000:00:00','YYYY-MM-DD1224、selectempno,ename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss')asselectempno,ename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss')ashiredatefromselectselectempno,ename,to_char(sal,'$999,999')fromselectselectempno,ename,to_char(sal,'$999,999.99')from90$符L.,、selectselect*fromempwheresal>to_number('1,500',、selectempno,selectempno,ename,sal, mfrom以上结果不正确，主要原因是津贴（comm）null，所以无法计算，所以正确的做法0Oraclenvl，该函数的语法格式为：nvl(表selectempno,ename,sal,selectempno,ename,sal,comm,sal+nvl(comm,0)from、casewhenthenselectempno,ename,job,sal,casejobwhen'MANAGER'selectempno,ename,job,sal,casejobwhen'MANAGER'thensal*1.1when'SALESMAN'sal*1.5endasnewsalfrom、casewhenthenselectempno,ename,job,sal,decode(job,'MANAGER',sal*1.1,'SALESMAN',selectempno,ename,job,sal,decode(job,'MANAGER',sal*1.1,'SALESMAN',sal*1.5)newsalfrom、selectselect .1234,2)from、selectcount(*)selectcount(*)fromselectcount(comm)fromselectcount(comm)fromselectcount(distinct(job))selectcount(distinct(job))from、Sum可以取得某一个列的和，如果是nullselectselectsum(sal)fromselectselectsum(comm)fromselectselectsum(sal+nvl(comm,0))fromnullselect m)fromselect m)from从以上结果来看，不正确，原因在于commnull值，所以无法计算，sum会忽略掉，comm0、selectavg(sal)selectavg(sal)from、selectselectmax(sal)fromselectmax(to_char(hiredate,selectmax(to_char(hiredate,'yyyy-mm-dd'))from、selectselectmin(sal)fromselectselectmin(to_char(hiredate,'yyyy/mm/dd'))from分组查询主要涉及到两个子句，分别是：groupby和、groupselectselectjob,sum(sal)fromempgroupbygroupby，聚合函数前面的所有字段必须参与分组orderby，orderbygroupby后面SQL>SQL>select*from MGR 147369790217-12月-769820-2-769822-2-7566783902-4-7654769828-9-7698783901-5-7782783909-6-7788756619-4-783917-11月- 769808-9-778823-5-7900769803-12月-7902756603-12月-7934778223-1-selectjob,deptno,sum(sal)selectjob,deptno,sum(sal)fromempgroupby、selectjob,avg(sal)fromselectjob,avg(sal)fromempgroupbyjobhavingavg(sal)2、selectselect字段fromselect字段from表名where…….groupbyhaving…….orderby……..首先执行wheregroupbyselectorderby、SQL92selectename,dnamefromemp,dept;SQL>selectename,dnamefromemp,dept; ACCOUNTING ACCOUNTING ACCOUNTING ACCOUNTING ACCOUNTING RESEARCH RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES OPERATIONS OPERATIONS OPERATIONS OPERATIONS56selectemp.ename,dept.dnamefromselectemp.ename,dept.dnamefromemp,deptwhereselecte.ename,d.dnamefromempe,deptdwhereselecte.ename,m.enamefromempe,empmwhereSQLselect*fromemp;（普通员工EMPNO MGR7369 790217-12月-7499 769820-2-7521 769822-2-7566 783902-4-7654 769828-9-7698 783901-5月-7782 783909-6-7788 756619-4-7839 17-11月-7844 769808-9- 778823-5-7900 769803-12月-7902 756603-12月-7934 778223-1-14SQLselect*fromemp;（管理者EMPNO MGR7369 790217-12月-7499 769820-2-7521 769822-2-7566 783902-4-7654 769828-9-7698 783901-5月-7782 783909-6-7788 756619-4-7839 17-11月-7844 769808-9- 778823-5-7900 769803-12月-7902 756603-12月-7934 778223-1-SQL>selecteename,menamefromempe,empmwhereemgr=m 、SQL99SQL92selectSQL92selecte.ename,e.sal,d.dnamefromempe,deptdwheree.deptno=d.deptno e.sal>SQL99selecte.ename,e.sal,d.dnamefromempejoindeptdone.deptno=d.deptnowhere或selecte.ename,e.sal,d.dnamefromempeinnerjoindeptdone.deptno=d.deptnowhereinnerSql92sql99语法的区别：99语法可以做到表的连接和查询条件分离，特别是多个表sql92更清晰selectselecte.ename,e.sal,d.dnamefromemperightjoindeptdonselecte.ename,e.sal,d.dnamefromdeptdleftjoinempeonselecte.ename,e.sal,selecte.ename,e.sal,d.dnamefromemperightouterjoindeptdonselecte.ename,e.sal,d.dnamefromdeptdleftouterjoinempeonwherewhere语句select语句查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工selectdistinctselectdistinctmgrfromempwheremgrisnotselectselectempno,enamefromempwhereempnoin(selectmgrfromempwheremgrisnotselectavg(sal)selectavg(sal)fromselectselectempno,ename,salfromempwheresal>(selectavg(sal)from92selecte.empno,e.enamefromemp92selecte.empno,e.enamefromempe,(selectdistinctmgrfromempwheremgrisnotnull)mwheree.empno=m.mgr;99selecte.empno,e.enamefromempejoin(selectdistinctmgrfromempwheremgrisnotnull)mone.empno=m.mgr;selectdistinctmgrfromempwheremgrisnotselectdeptno,selectdeptno,avg(sal)avg_salfromempgroupbyselectselecte.ename,selecte.ename,d.dnamefromempe,deptdwhereselecte.ename,(selectd.dnamefromselecte.ename,(selectd.dnamefromdeptdwheree.deptno=d.deptno)asdnamefromemp、union、union可以合并集合（相加select*fromempwherejobselect*fromempwherejobin('MANAGER',select*fromempselect*fromempwherejob='MANAGER'select*fromempwhere、minus可以移出集合（相减select*fromempwheredeptnoin(10,20)and查询部门编号为select*fromempwheredeptnoin(10,20)and10202000的（selectselect*fromempwheredeptnoin(10,select*fromempwhere、rownumOracle提供了rownum，rownum是一个隐含的字段，rownum1、rownumselectselectrownum,e.*fromemp5selectselectrownum,e.*fromempewhererownumselectselectrownum,a.*from(select*fromemporderbysaldesc)awhere5selectselect*fromempwhererownum>以上语句，oracle不支持，oraclerownum5selectselectrownum,e.*fromempewhererownum<=5orderbye.sal已经形成，正确使用的方式，将排序好的数据作为一来使用，这样这个表的rownum是rownumrownumselectselectselect*(selectrownumr,t.*(select*from)twhererownum)wherer>、InsertintoInsertinto表名(字段。)values(值 insertinsertintoempvalues(9999,'zhangsan','MANAGER',null,null,3000,500,第SQL第SQLinsertintoemp(empnoename,jobmgrhiredatesalcomm,deptnovalues(9997,'zhangsan','MANAGER',null06-12月-0,500,1SQL>select*fromMGR7369790217-12月-769820-2-769822-2-7566783902-4-7654769828-9-7698783901-5-7782783909-6-7788756619-4-783917-11月- 769808-9-778823-5-7900769803-12月-7902756603-12月-7934778223-1-999999989997zhangsan9997zhangsan06-12月-SQL>insertintoemp(empno,SQL>insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(9996,'zhangsan','MANAGER',null,to_date('2010-01-01','yyyy-mm-dd'),3000,500,10);1SQL>select*fromMGRMGR9996zhangsan01-1-187369790217-12月-769820-2-769822-2-7566783902-4-7654769828-9-7698783901-5-7782783909-6-7788756619-4-783917-11月- 769808-9-778823-5-7900769803-12月-7902756603-12月-7934778223-1-99999998999706-12月-SQL>insertSQL>insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(9995,'zhangsan','MANAGER',null,sysdate,3000,500,1SQL>select*fromMGR7369790217-12月-769820-2-MGR19表769822-2-7566783902-4-7654769828-9-7698783901-5-7782783909-6-7788756619-4-783917-11月- 769808-9-778823-5-7900769803-12月-7902756603-12月-7934778223-1-99999998999706-12月-9996zhangsan01-1-9995zhangsan28-1-createcreatetableemp_0128asselect*frominsertinsertintoemp_0128select*fromempwhere、updateupdateset1=1,2=2wherejobmanagerupdateupdateempsetsal=sal+sal*0.1where、DeleteDeletefromwhere500deletedeletefromempwheredeletefromempdeletefromempwherecommis向证向证数据库转账8000数据库减少数据库增加8000sql要么全成功，要么全失败。事务具有四个特征性（ansction开启事务（Begin提交事务（commit seet

createcreatetable表名（列名称类型,）Oracle定长字符串，空间大小固定，适合作createtablet_student( birthdaycreatetablet_student( birthday )t_studentt_studentinsertintot_student(student_id,男to_date('1988-01-01','yyyy-mm-dd',',,classes_id)values(1001,createtablet_student(createtablet_student( char(2)default'男',birthdaydatedefault )非空约束，not唯一约束，unique主键约束，primary外键约束，foreign非空约束，not createtablet_student( createtablet_student( varchar2(20)notnull,birthday )createtablet_student(createtablet_student( varchar2(20)constraintstudent_name_not_nullnotnull,birthday )selectconstraint_namefromselectconstraint_namefrom唯一约束，uniquecreatetablecreatetable birthdaydate,varchar2(30)unique, )createtablet_student( birthdayvarchar2(30)createtablet_student( birthdayvarchar2(30)_unique )关于约束名称可以到user_constraintscreatetablet_student(createtablet_student( birthday _unique_1unique())主键约束，primarycreatetablecreatetable number(10)primary birthday )createtable number(10)constraintpk_student_idprimarykey, birthdaydate, )createtablet_student( birthday student_id_pkprimary)createtablet_student(createtablet_student( birthdayconstraintpk_student_id_nameprimarykey(student_id,)外键约束，foreigndeptno字段值。createtablet_classes(classes_idnumber(3),classes_namecreatetablet_classes(classes_idnumber(3),classes_nameconstraintpk_classes_idprimary)t_studentcreatetablet_student( birthday number(3)referencest_classes(classes_id), student_id_pkprimarykey(student_id))t_studentinsertintot_student(student_id,student_name, ,birthday, ,classes_id)values(1001,'','男',to_date('1988-01-01','yyyy-mm-dd'),' ',10)createtablet_student(createtablet_student( birthday student_id_pkprimarykey(student_id),constraintfk_classes_idforeignkey(classes_id)referencest_classes(classes_id))nullinsertinsertintot_student(student_id,男to_date('1988-01-01','yyyy-mm-,,classes_id)values(1001, ',createtablet_student( createtablet_student( birthday number(3)notnull, student_id_pkprimarykey(student_id),constraintfk_classes_idforeignkey(classes_id)referencest_classes(classes_id))再次插入班级编号为null因为子表（t_student）classes_id，它参照了父表（t_classes）中的主键，所以droptabledropdrop父表createtablet_student( birthdaycreatetablet_student( birthdaystudent_id_pkprimaryin('男女)、t_studentt_classescreatetablet_student(createtablet_student( varchar2(50)notchar(2)char(2)notnull,birthdaydatenotnull,varchar2(30)unique, number(3)notnull,constraintpk_student_idprimaryconstraintfk_classes_idforeignkey(classes_id)references)createtablet_classes( classes_namevarchar2(30)notnull,constraintpk_classes_idprimarykey(classes_id))altertablet_student 如：需求发生改变，需要向t_student中加系 字段，字段名称为：altertablet_student altertablet_studentmodify(student_namealtertablet_studentmodify(student_namevarchar2(100))altertablet_studentaltertablet_student altertablet_studentdropaltertablet_studentdropconstraintaltertablet_studentaddaltertablet_studentaddconstraintfk_classes_id_1foreignkey(classes_id)alteraltertablet_studentmodify(student_namevarchar(30)dropdroptablecreateviewcreateviewv_dept_avg_sal_gradeasselecta.deptno,a.avg_sal,from(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)a,salgradebwherea.avg_salbetweenb.losalandb.hisal;索引的目的是提高查询数据的速度，索引一本书 一样，索引的建立原则，比较少deletecreateindexidx_t_student_birthdayoncreateindexidx_t_student_birthdayondropdropindexselectselecta.deptno,a.avg_sal,from(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)a,salgradebwherea.avg_salbetweenb.losalandb.hisal;会给带来成本，视图可以解决这个问题scott用户能够创建事务的权限connconnselect*fromshowshowconnscott/tigeras如何对scottconnscott/tigeras切换到切换到system系统connsystem/bjpowernode为scott用户grantcreateviewtoconnselect*fromcreateviewv_dept_avg_sal_gradecreateviewv_dept_avg_sal_gradeasselecta.deptno,a.avg_sal,from(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)a,salgradebwherea.avg_salbetweenb.losalandb.hisal;dropdropview、序列序列是Oracle特有的，它可以一个自增的数字序列，通常从1开增长，但可以设置，t_studentOracle的序列的方式来createcreatesequenceseq_student_idstartwith1incrementbydropdropsequence、过程、触发器和游、过forvarretexecvarretexecproc_test(20,:ret)print:retcreateorre ceprocedureproc_test(in_varnumber,out_varoutsys_refcursor)openout_varforselect*fromempwhere、过程例createtable(IDNUMBER(22),--CODEVARCHAR2(10),--随机数)需求说明：批量生成随机数存放到code字段，字段id11亿，要求code11亿之间的随机数，且不能重复，code8实现思路：每次插入一条数据，ID值和CODE值相同，同时计数器加1，再从已经插入的数据中取出一个随机D，查出这个D对应的CODE值，将 的记录的CODE值与随机取得的记录的CODE值互换。//创 过程insert_random_couponCode,maxNum是参数，in表示是输入参createorreceprocedureinsert_random_couponCode(maxNuminnumber)currentIdnumber(9);randomIdnumber(9);randomCodevarchar2(9);currentCodevarchar2(9);querySqlvarchar2(2000);typec_mycurisrefcursor定义游标c_curc_mycur;//从begin开始就 过程selectmax(id)intocurrentIdfromcoupon_code;//查出最大id的值赋值给变量currentIdif(currentIdisnull)theninsertinsertintocommit;//提交insertcurrentId:=1;这里冒号加等号表示赋值endif;//每一个if都有一个对应的endif结束openc_curforselectt.id,tcodefromcoupon_codet';//打开游标loop开始循环querySql:='selectabs(mod(dbms_random.random,'||currentId||'))fromdual';//executeimmediatequerySqlintorandomId;//执行拼成的querySqlifrandomId=0thenendif;querySql:='selecttcodefromcoupon_codetwheretid='||randomId;executeimmediatequerySqlintorandomCode;updateSql:='begininsertintocoupon_code(id,code)values('||currentId||','''||randomCode||''')'||';updatecoupon_codesetcode='''||currentCode||'''whereid='||randomId||';end;';executeimmediateupdateSql;exitwhencurrentId=maxNum;//currentId与maxNumendloop;//每一个loop都有一个对应的endloopclosec_cur;dbms_outputput_line('finished!');//打印输出finished!exception这里是异常的处理whenothersthenend 学习过了过程，函数就是小菜了，这里就只给出一个实际项目中的小例子作为参考createorrecefunctionF_getpkfiel