ORACLE数据库基础知识.ppt

ORACLE数据库教程,你可以不知道，但是绝对不能不懂,-毁人不倦语录,2007年5月,SQL基础知识篇,SQL-001,简介,SQL（StructuredQueryLanguage）结构化查询语言是IBM公司SanJose实验室为SystemR而设计的查询语言，后被国际标准化组织（ISO）批准作为关系数据库语言的国际标准。SQL目前遵循的是1992年标准，即SQL-92。各数据库厂家对SQL-92标准均有扩充，扩充部分不能完全保证数据库之间的兼容性。,我的热情会灼伤一些人，但我是无心的,-毁人不倦语录,简介,SQL可以分为:（基础篇只介绍前两种）DML:数据操作语言（DataManipulationLanguage）:select,insert,update,delete,(其他：truncate)DDL:数据定义语言（DataDefinitionLanguages）:create,drop,alter,(其他：rename)DCL数据控制语言：grant、revoke、setrole事务控制：commit、rollback、savepoint（其他：locktable、setconstraint(s)、settransaction）审计控制：audit、noaudit系统控制：altersystem会话控制：altersession其他语句：comment（添加注释）、explainplan、analyze、validate、call,如果我的话没有错，一定是你听错了,-毁人不倦语录,DDL数据定义语言,CREATE(建立)ALTER(修改)DROP(删除),无知者无畏，无畏者不一定无知,-毁人不倦语录,语言描述约定,：表示可选项|：表示选择项CREATE：粗体表示关键字或必输项Table_name：斜体表示用户输入项创建：蓝色表示说明,如果1+12，我相信那说的是团队的力量,-毁人不倦语录,DDL数据定义语言,CREATE-创建用户CREATEUSERuser_nameIDENTIFIEDBYpasswordDEFAULTTABLESPACEtablespace_nameTEMPORARYTABLESPACEtablespace_name;创建user_name用户，密码为password，使用tablespace_name表空间，如果没有指定表空间，默认表空间为system表空间。Oracle10g有用户默认表空间设置。建议在创建用户时一定要指名表空间。,先有鸡，还是先有蛋，这么重要的问题还是留给鸡去考证吧,-毁人不倦语录,DDL数据定义语言,CREATE-创建数据表CREATETABLEtable_name(Column1datatypedefaultnotnullprimarykey,constraintkey_nameprimarykey(column_list)usingindextablespacetablespace_name)tablespacetablespace_name;Datatype是数据类型:varchar2(x),number(x,x),date,integer等。Notnull非空限制，如果不写表示可为空。Primarykey主键，可以紧跟在字段后，或在最后使用constraint。未指名表空间，数据表建立在用户默认的表空间中。,笔只留下了痕迹，人用笔留下了文化,-毁人不倦语录,DDL数据定义语言,CREATE-创建索引CREATEUNIQUEINDEXindex_nameONtable_name(column_list)tablespacetablespace_name;在table_name表上按照column_list建立索引，索引名为index_name，索引保存在tablespace_name表空间中。UNIQUE:唯一性索引。,-毁人不倦语录,你可以不听，但是我一定要说,DDL数据定义语言,ALTER-修改表ALTERTABLEtable_nameREMAMETOnew_table_name;将table_name表名修改为new_table_name。ALTERTABLEtable_nameADD(column1datatypedefaultnotnull,column2datatypedefaultnotnull,.);在table_name表中增加column1,column2.字段，追加到字段最后。ALTERTABLEtable_nameMODIFY(column1datatypedefaultnotnull|nullable,column2datatypedefaultnotnull|nullable,.修改table_name表字段属性，长度或精度不能小于原长度或精度。ALTERTABLEtable_nameDROPCOLUMNcolumn_name;删除table_name表中的column_name字段。ALTERTABLEtable_nameRENAMECOLUMNold_nameTOnew_name;修改table_name表的old_name字段名为new_name。ALTERTABLEtable_nameADDCONSTRAINTpk_namePRIMARYKEY(column_list);建立table_name表的主键，主键名pk_name，主键字段column_list。ALTERTABLEtable_nameDROPCONSTRAINTpk_name;删除table_name表的pk_name主键。一个表只能有一个主键,-毁人不倦语录,不说了，再说就该吐了。补充一点：站着进，扶着出也许是侃爷的最高境界吧,DDL数据定义语言,DROP-删除DROPTABLEtable_name;删除table_name表。DROPINDEXindex_name;删除index_name索引。删除主键（强制限制的一种），使用ALTERTABLEtable_nameDROPCONSTRAINTpk_name;,-毁人不倦猜想,数据库和水库之间不可能没有区别,DML小结,从ALTER和CREATE及DROP语法数量再一次证明需求是易变的。如果增加表字段，并要求字段的排列位置，可以使用:CREATETABLEtemp_table_nameASSELECT*FROMtable_name;DROPTABLEtable_name;CREATETABLEtable_name(column);INSERTINTOtable_nameVALUES(column_list)(SELECTcolumn_list1FROMtemp_table_name);DROPTABLEtable_name;需要注意的是，删除表后，表中的索引也被删除，所以，在执行上述操作前要保留好表的索引脚本。这里没有写DROPDATABASE的语法，如果大家想要尝试删除数据库的话，最好在专家的指导下进行！,人间本无对错，因为计算机才有了对与错,-毁人不倦语录,DDL数据操作语言,SELECT(查询)INSERT(插入)UPDATE(更新)DELETE(删除),如果老天能再给我一次机会，我一定做甲方,-毁人不倦语录,DDL数据操作语言,SELECT-查询SELECTALL|DISTINCTON(expression,.)*|expressionASoutput_name,.INTOTEMPORARY|TEMPTABLEnew_tableFROMfrom_item,.WHEREconditionGROUPBYexpression,.HAVINGcondition,.UNION|INTERSECT|EXCEPTALLselectORDERBYexpressionASC|DESC|USINGoperator,.FORUPDATEOFclass_name,.LIMITcount|ALLOFFSET|,start后续查询范例均以oracle中的SCOTT/TIGER用户表为例,如果失眠了，就看看上面的语法吧,-毁人不倦语录,DDL数据操作语言,SCOTT/TIGER表介绍DEPT部门定义表，DEPTNO是主键EMP职工表,EMPNO主键，DEPTNO外键关联DEPT其他表没有数据或没有关系，不在这里介绍,将复杂问题简单化是修养，将简单问题复杂化是艺术，综合起来叫艺术修养,-毁人不倦语录,DDL数据操作语言,SELECT-单表查询查询表中所有字段和所有记录，select后跟*表示所有字段SELECT*FROMDEPT;查询指定字段，在select后跟查询的字段名列表，字段间用,隔开SELECTDEPTNO,DNAMEFROMDEPT;条件查询，FROM后面使用WHERE，在WHERE中可以使用=,=,10GROUPBYDEPTNO,DNAMEHAVINGCOUNT(*)0ORDERBYDEPTNODESC;-ORDERBY是对返回的结果进行排序，所以必须放在最后一句。SELECTDEPTNO,DNAMEFROMSCOTT.DEPTWHEREDEPTNO10GROUPBYDEPTNO,DNAMEHAVINGCOUNT(*)0ORDERBY1DESC;-结果同第一个查询，字段位置从1开始。,我是来做项目的，不是来创造奇迹的,-毁人不倦语录,DDL数据操作语言,SELECT-FORUPDATE锁等待查询。当查询结果中有被锁定记录时等待解锁，当记录被解锁后返回结果集，并锁定返回的记录。如果FORUPDATE后跟NOWAIT，遇到锁后不等待，返回错误。ORACLE使用记录级锁定，当事物被提交或回滚后锁定被释放。死锁问题当两个事物以不同的顺序同时更新多个表时就会发生死锁（对牛），解决死锁的途径是手工杀掉死锁的进程或者重新启动数据库。因此，在使用事物更新多表数据时一定要小心，使用面向对象的方法封装数据操作可以在很大程度上解决死锁问题。,没有经过严格测试的软件开发进度报告不看也罢,-毁人不倦语录,DDL数据操作语言,SELECT-子查询查询语句可以嵌套，任何产生数值的地方都可以使用子查询。在查询条件中使用子查询时，当子查询返回多个结果时只能使用IN。查询顺序是先执行被依赖的底层查询，然后一层层向上查。子查询只被执行一次。查询平均工资最低的部门情况SELECTDEPTNO,AVG(SAL)FROMSCOTT.EMPGROUPBYDEPTNOHAVINGAVG(SAL)=(SELECTMIN(AVG(SAL)FROMSCOTT.EMPGROUPBYDEPTNO);-子查询先查询出最低的部门平均工资，然后查询部门最低平均工资等于子查询工资的部门,就是喜欢编程也不能为了编程而放弃和用户的功能确认，如果你把用户对程序的任意修改提升为对你自身价值的否定，你还不先确认吗？,-毁人不倦语录,DDL数据操作语言,SELECT-子查询在FROM中使用子查询，子查询在这里相对与VIEWSELECTD.DEPTNO,D.DNAME,S.AVG_SAL,S.SUM_SAL,S.NUMFROMSCOTT.DEPTD,(SELECTDEPTNO,AVG(SAL)ASAVG_SAL,SUM(SAL)ASSUM_SAL,COUNT(*)ASNUMFROMSCOTT.EMPGROUPBYDEPTNO)SWHERED.DEPTNO=S.DEPTNO;在WHERE中使用子查询，例子不好，应该直接写表关联，这里只是为了说明语法。SELECT*FROMSCOTT.EMPWHEREDEPTNOIN(SELECTDEPTNOFROMSCOTT.DEPTWHEREDEPTNO=20);,驴怎么了？人家采用的可是软件工程中的螺旋法则,-毁人不倦语录,DDL数据操作语言,SELECT-子查询使用EXISTS(NOTEXISTS)替换IN(NOTIN)IN(NOTIN)在执行数据库操作时性能非常低下，应该使用EXISTS(NOTEXISTS)替换，特别是NOTIN子句将执行一个内部的排序和合并;EXISTS子查询使用主表的字段限制查询数据SELECT*FROMSCOTT.EMPEWHEREEXISTS(SELECT*FROMSCOTT.DEPTWHEREDEPT.DEPTNO=E.DEPTNOANDDEPTNO=20);-因为EXISTS可以看到外表，所以，如果表名重复，使用表别名区分，在子查询中一定写清楚和外表的关联关系，另外，子查询写SELECT*是对的，不用写字段名。,多做一点，早知道一点,-毁人不倦语录,DDL数据操作语言,UPDATE修改用来更新数据表中的数据UPDATEtable_nameSETcolumn_name=new_value,WHEREcondition;更新DEPT表，将20号部门名称修改为销售部，地址修改为北京UPDATEDEPTSETDNAME=销售部,LOC=北京WHEREDEPTNO=20;子查询也可在UPDATE中使用,将职工领导是KING的人员薪资增加100元UPDATESCOTT.EMPSETSAL=SAL+100WHEREEMPNOIN(SELECTE.EMPNOFROMSCOTT.EMPE,SCOTT.EMPGWHEREE.MGR=G.EMPNOANDG.ENAME=KING);UPDATESCOTT.EMPSETSAL=SAL+100WHEREEXISTS(SELECT*FROMSCOTT.EMPE,SCOTT.EMPGWHEREE.MGR=G.EMPNOANDE.EMPNO=EMP.EMPNOANDG.ENAME=KING);,金保工程是“阳光工程”，所以我才阳光,-毁人不倦语录,DDL数据操作语言,INSERT-插入语法:INSERTINTOtable_name(column,.)VALUES(expression,)|SELECTquery当表字段和插入值相同时，可以省略字段列表INSERTINTOSCOTT.BONUSVALUES(TURNER,SALESMAN,200,40);当只插入部分字段时，必须列举字段，未赋值字段使用默认值或为空INSERTINTOSCOTT.BONUS(ENAME,JOB,SAL)VALUES(CLARK,MANAGER,100);也可以使用查询给表插入数据INSERTINTOSCOTT.BONUS(ENAME,JOB,SAL)SELECTENAME,JOB,SALFROMSCOTT.EMPWHEREMGR=7698;,理论上都是问题，做起来没有问题,-毁人不倦语录,DDL数据操作语言,DELETE-删除语法:DELETEFROMtable_nameWHEREcondition;删除BONUS中ENAME为CLARK的记录DELETEFROMSCOTT.BONUSWHEREENAME=CLARK删除重复记录，ROWID是记录的物理位置，一经确定永不改变DELETEFROMSCOTT.BONUSBWHEREROWID1);-删除ENAME重复记录,自己先擦块玻璃再交代给别人擦，安排工作同理,-毁人不倦语录,序列号,创建CREATESEQUENCEnameINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE;说明：INCREMENTBYn一次增长n个数字STARTWITHn初始值NOMAXVALUE缺省值10E+27NOMINVALUE缺省值1NOCYCLE不循环,常用于唯一关键字CACHEn在内存里缓存n个序列,出错回退时会丢失创建从1开始，不缓存的EMP_EMPNO序列。CREATESEQUENCEEMP_EMPNOSTARTWITH1NOCACHE;修改ALTERSEQUENCEnameINCREMENTBYnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE;-起始值不能改变，如果要改变序列的起始值,先删除,再新建。删除DROPSEQUENCEname;DROPSEQUENCEEMP_EMPNO;,序列号,使用NEXTVAL下一个序列号值CURRVAL当前序列号值如果EMP_EMPNO是一个序列号，可以用下面SQL取序列值-去序列的当前值SELECTEMP_EMPNO.CURRVALFROMDUAL;-去序列的下一个值SELECTEMP_EMPNO.NEXTVALFROMDUAL;-使用序列插入数据INSERTINTOEMP(EMPNO,ENAME,)VALUES(EMP_EMPNO.NEXTVAL,JONE,);不能用序列号的nextval和currval的地方视图查询、distinct查询、有groupby,having,orderby的查询、有子查询的查询、表里的缺省值,注释,/*注释内容*/-注释内容-SELECT*DELETEFROMEMPWHEREDEPTNO=20;提示：删除数据前最好先查询一下。可以先写SELECT*，然后把SELECT*注释掉，增加DELETE。,如果再想不出来就去上厕所,-毁人不倦语录,常用函数,数字函数ABS取绝对值POWER乘方LN10为底数取幂SQRT平方根EXPe的n次乘方LOG(m,n)m为底数n取幂数学运算函数:ACOSATANATAN2COSCOSHSIGNSINSINHTANTANHCEIL大于或等于取整数FLOOR小于或等于取整数MOD取余数ROUND(n,m)按m的位数取四舍五入值如果round(日期):中午12以后将是明天的日期.round(sysdate,Y)是年的第一天TRUNC(n,m)按m的位数取前面的数值如果trunc(日期),确省的是去掉时间,冲锋号响起后执行的是命令而不是计划,-毁人不倦语录,常用函数,字符函数CHR按数据库的字符集由数字返回字符CONCAT(c1,c2)把两个字符c1,c2组合成一个字符,和|相同REPLACE(c,s,r)把字符c里出现s的字符替换成r,返回新字符SUBSTR(c,m,n)m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,m小与0,字符c从后面m处开始取n位字符TRANSLATE(c,f1,t1)字符c按f1到t1的规则转换成新的字符串INITCAP字符首字母大写,其它字符小写LOWER字符全部小写UPPER字符全部大写LTRIM(c1,c2)去掉字符c1左边出现的字符c2RTRIM(c1,c2)去掉字符c1右边出现的字符c2TRIM(c1,c2)去掉字符c1左右两边的字符c2LPAD(c1,n,c2)字符c1按指定的位数n显示，不足的位数用c2字符串替换左边的空位RPAD(c1,n,c2)字符c1按指定的位数n显示，不足的位数用c2字符串替换右边的空位提示：去掉字符串中间的空格可以使用replace,如果有三条路供你选择，不要选中间的，剩下两条可以扔硬币决定,-毁人不倦语录,常用函数,日期函数ADD_MONTHS(d,n)日期值加n月L