ORACLE数据库最基础教程

ORACLE数据库教程2007年5月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、callDDL数据定义语言•CREATE(建立)•ALTER(修改)•DROP(删除)语言描述约定•[]：表示可选项•{|}：表示选择项•CREATE：粗体表示关键字或必输项•Table_name：斜体表示用户输入项•创建：蓝色表示说明DDL数据定义语言CREATE--创建用户•CREATEUSERuser_nameIDENTIFIEDBYpassword[DEFAULTTABLESPACEtablespace_name][TEMPORARYTABLESPACEtablespace_name];创建user_name用户，密码为password，使用tablespace_name表空间，如果没有指定表空间，默认表空间为system表空间。Oracle10g有用户默认表空间设置。建议在创建用户时一定要指名表空间。DDL数据定义语言CREATE--创建数据表•CREATETABLEtable_name(Column1datatype[default][notnull][primarykey],[………,][constraintkey_nameprimarykey(column_list)[usingindextablespacetablespace_name]])[tablespacetablespace_name];Datatype是数据类型:varchar2(x),number(x,x),date,integer等。Notnull非空限制，如果不写表示可为空。Primarykey主键，可以紧跟在字段后，或在最后使用constraint。未指名表空间，数据表建立在用户默认的表空间中。DDL数据定义语言CREATE--创建索引•CREATE[UNIQUE]INDEXindex_nameONtable_name(column_list)[tablespacetablespace_name];在table_name表上按照column_list建立索引，索引名为index_name，索引保存在tablespace_name表空间中。UNIQUE:唯一性索引。DDL数据定义语言ALTER--修改表•ALTERTABLEtable_nameRENAMETOnew_table_name;将table_name表名修改为new_table_name。•ALTERTABLEtable_nameADD(column1datatype[default][notnull],column2datatype[default][notnull],…..);在table_name表中增加column1,column2..字段，追加到字段最后。•ALTERTABLEtable_nameMODIFY(column1datatype[default][{notnull|nullable}],column2datatype[default][{notnull|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语法数量再一次证明需求是易变的。•如果增加表字段，并要求字段的排列位置，可以使用:1.CREATETABLEtemp_table_nameASSELECT*FROMtable_name;2.DROPTABLEtable_name;3.CREATETABLEtable_name(column…);4.INSERTINTOtable_nameVALUES(column_list)(SELECTcolumn_list1FROMtemp_table_name);5.DROPTABLEtable_name;需要注意的是，删除表后，表中的索引也被删除，所以，在执行上述操作前要保留好表的索引脚本。•这里没有写DROPDATABASE的语法，如果大家想要尝试删除数据库的话，最好在专家的指导下进行！DDL数据操作语言•SELECT(查询)•INSERT(插入)•UPDATE(更新)•DELETE(删除)DDL数据操作语言SELECT--查询SELECT[ALL|DISTINCT[ON(expression[,...])]]*|expression[ASoutput_name][,...][INTO[TEMPORARY|TEMP][TABLE]new_table][FROMfrom_item[,...]][WHEREcondition][GROUPBYexpression[,...]][HAVINGcondition[,...]][{UNION|INTERSECT|EXCEPT[ALL]}select][ORDERBYexpression[ASC|DESC|USINGoperator][,...]][FORUPDATE[OFclass_name[,...]]][LIMIT{count|ALL}[{OFFSET|,}start]]•后续查询范例均以oracle中的SCOTT/TIGER用户表为例DDL数据操作语言SCOTT/TIGER表介绍•DEPT部门定义表，DEPTNO是主键•EMP职工表,EMPNO主键，DEPTNO外键关联DEPT•其他表没有数据或没有关系，不在这里介绍DDL数据操作语言SELECT--单表查询•查询表中所有字段和所有记录，select后跟*表示所有字段SELECT*FROMDEPT;•查询指定字段，在select后跟查询的字段名列表，字段间用’,’隔开SELECTDEPTNO,DNAMEFROMDEPT;•条件查询，FROM后面使用WHERE，在WHERE中可以使用=,,,,=,=,BETWEEN,LIKE,IN，返回WHERE条件为true的数据SELECT*FROMDEPTWHEREDEPTNO=20;--返回部门编码为20的部门数据SELECT*FROMDEPTWHEREDEPTNOBETWEEN20AND30;--返回部门编码在20到30之间的部门数据（包括20和30）DDL数据操作语言SELECT--单表查询SELECT*FROMDEPTWHEREDNAMELIKE‘ACCOUNT%’;--返回部门名称以ACCOUNT开始的部门数据SELECT*FROMDEPTWHEREDNAMELIKE‘%UNT%’;--返回部门名称含UNT的部门数据，%全匹配符，？单匹配符SELECT*FROMDEPTWHEREDEPTNOIN(20,30)AND(DNAMELIKE‘%UNT%’ORDNAME=‘SALES’);--返回部门编码等于20和30并且部门名称含UNT或等于SALES的部门数据，AND与，OR或，IN和（等同OR）DDL数据操作语言SELECT--多表查询•在FROM中列举表名，WHERE中写表关联SELECTEMP.EMPNO,EMP.ENAME,EMP.JOB,DEPT.DEPTNO,DEPT.DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNOANDDEPT.DEPTNO=20;--字段名前使用表名.指定表字段，在WHERE中先写表关联关系，从FROM的内侧（即左侧）向外侧写（即右侧），表关联写完后再写查询条件，不要把表管理和查询条件混在一起写，这虽然不是必须的，但却是规范推荐的。SELECTE.EMPNOAS职工编码,E.ENAME姓名,E.JOB,D.DEPTNO,D.DNAMEFROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNOANDD.DEPTNO=20;--字段和表名可以使用别名，方法为AS别名，或直接写别名，表如果指定别名，原表名在该查询中不能再被使用。DDL数据操作语言SELECT--Joins（连接）•语法{T1}[NATURAL][INNER|{LEFT|RIGHT|FULL}[OUTER]]JOIN{T2}{ONsearchcondition|USING(joincolumnlist)}•一个条件JOIN必须通过提供一个(并且只能有一个)NATURAL，ON，或者USING这样的关键字来声明它的连接条件．ON子句接受一个searchcondition，它与一个WHERE子句相同．USING子句接受一个用逗号分隔的字段名列表，连接表中必须都有这些字段，并且用那些字段连接这些表。•NATURAL是USING子句的缩写，它列出两个表中所有公共的字段名字。•对于所有JOIN而言，INNER和OUTER都是可选的．INNER是缺省．LEFT，RIGHT和FULL只用于OUTERJOIN。DDL数据操作语言SELECT—INNERJOIN内连接•对于T1的每行R1，连接成的表在T2里都有一行满足与R1的连接条件。SELECTEMP.EMPNO,EMP.ENAME,EMP.JOB,DEPT.DEPTNO,DEPT.DNAMEFROMEMPJOINDEPTUSING(DEPTNO);结果同：SELECTEMP.EMPNO,EMP.ENAME,EMP.JOB,DEPT.DEPTNO,DEPT.DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO•INNERJOIN完全可以写成WHERE子句DDL数据操作语言SELECT—OUTERJOIN外连接•LEFT[OUTER]JOIN首先执行一次INNERJOIN，然后，如果T1里有一行对任何T2的行都