ORACLE数据库系列教程-SQL基础知识篇剖析

ORACLE系列教程2016年5月张超公安信息化产品中心研发五部简介•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(删除)DDL数据定义语言CREATE--创建用户•CREATEUSERuser_nameIDENTIFIEDBYpassword[DEFAULTTABLESPACEtablespace_name][TEMPORARYTABLESPACEtablespace_name];创建user_name用户，密码为password，使用tablespace_name表空间，如果没有指定表空间，默认表空间为USERS表空间。例：createuserzc1identifiedbyzc1;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。未指名表空间，数据表建立在用户默认的表空间中。•例：---创建部门表createtableDEPT(DEPTNONUMBER(2)NOTNULLprimarykey,DNAMEVARCHAR2(14),LOCVARCHAR2(13))DDL数据定义语言CREATE--创建索引•CREATE[UNIQUE]INDEXindex_nameONtable_name(column_list)[tablespacetablespace_name];在table_name表上按照column_list建立索引，索引名为index_name，索引保存在tablespace_name表空间中。UNIQUE:唯一性索引。例：创建：createindexpk_DEPTonDEPT(DEPTNO);--(创建主键索引)createindexpk_EMPonEMP(ENAME);--(创建非主键索引)DDL数据定义语言ALTER--修改表•ALTERTABLEtable_nameREMAMETOnew_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);需要注意的是，删除表后，表中的索引也被删除，所以，在执行上述操作前要保留好表的索引脚本。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[,...]]]WHERE和HAVING的区别：WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数DDL数据操作语言DEPT/EMP表介绍•DEPT部门定义表，DEPTNO是主键•EMP职工表,EMPNO主键，DEPTNO外键关联DEPTDDL数据操作语言SELECT--单表查询•查询表中所有字段和所有记录，select后跟*表示所有字段SELECT*FROMDEPT;•查询指定字段，在select后跟查询的字段名列表，字段间用’,’隔开SELECTDEPTNO,DNAMEFROMDEPT;•条件查询，FROM后面使用WHERE，在WHERE中可以使用=,,,,=,=,BETWEEN,LIKE,IN，返回WHERE条件为true的数据SELECT*FROMDEPTWHEREDEPTNO=2;--返回部门编码为2的部门数据SELECT*FROMDEPTWHEREDEPTNOBETWEEN2AND3;--返回部门编码在2到3之间的部门数据（包括2和3）DDL数据操作语言SELECT--单表查询SELECT*FROMDEPTWHEREDNAMELIKE‘化%’;--返回部门名称以化开始的部门数据SELECT*FROMDEPTWHEREDNAMELIKE‘%馆%’;--返回部门名称含馆的部门数据，%全匹配符SELECT*FROMDEPTWHEREDEPTNOIN(2,3)--返回部门编码等于2和3DDL数据操作语言SELECT--多表查询•在FROM中列举表名，WHERE中写表关联SELECTEMP.EMPNO,EMP.ENAME,EMP.JOB,DEPT.DEPTNO,DEPT.DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNOANDDEPT.DEPTNO=2;--字段名前使用表名.指定表字段，在WHERE中先写表关联关系，从FROM的内侧（即左侧）向外侧写（即右侧），表关联写完后再写查询条件，不要把表管理和查询条件混在一起写，这虽然不是必须的，但却是规范推荐的。SELECTE.EMPNOAS职工编码,E.ENAME姓名,E.JOB,D.DEPTNO,D.DNAMEFROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNOANDD.DEPTNO=2;--字段和表名可以使用别名，方法为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的行都不满足连接条件，那么返回一个连接行，该行的T2的字段为null。左表数据全部返回，右表无对应数据返回NULL。Oracle也可以写成：WHEREEM