ORACLE数据库教程

1、ORACLE数据库教程数据库教程20072007年年5 5月月SQL-001SQL-001简介简介SQLSQL（Structured Query LanguageStructured Query Language）结构化查询）结构化查询语言是语言是IBMIBM公司公司San JoseSan Jose实验室为实验室为System RSystem R而设而设计的查询语言，后被国际标准化组织（计的查询语言，后被国际标准化组织（ISOISO）批）批准作为关系数据库语言的国际标准。准作为关系数据库语言的国际标准。SQLSQL目前遵循的是目前遵循的是19921992年标准，即年标准，即SQL-92SQL-

2、92。各数据库厂家对各数据库厂家对SQL-92SQL-92标准均有扩充，扩充部标准均有扩充，扩充部分不能完全保证数据库之间的兼容性。分不能完全保证数据库之间的兼容性。简介简介SQLSQL可以分为可以分为: : （基础篇只介绍前两种）（基础篇只介绍前两种）DML:DML:数据操作语言（数据操作语言（Data Manipulation LanguageData Manipulation Language）: : select, insert, update, delete, (select, insert, update, delete, (其他：其他：truncate) truncate) DD

3、L:DDL:数据定义语言数据定义语言 （Data Definition LanguagesData Definition Languages）: : create, drop , alter,(create, drop , alter,(其他：其他：rename)rename)DCLDCL数据控制语言：数据控制语言：grantgrant、revokerevoke、set role set role 事务控制：事务控制：commitcommit、rollbackrollback、savepointsavepoint（其他：（其他：lock lock tabletable、set set cons

4、traint(sconstraint(s) )、set transactionset transaction）审计控制：审计控制：auditaudit、noauditnoaudit系统控制：系统控制：alter system alter system 会话控制：会话控制：alter session alter session 其他语句：其他语句：commentcomment（添加注释）、（添加注释）、explain planexplain plan、analyzeanalyze、validatevalidate、call call DDL数据定义语言数据定义语言CREATE(CREATE(建立

5、建立) )ALTER(ALTER(修改修改) )DROP(DROP(删除删除) )语言描述约定语言描述约定 ：表示可选项：表示可选项 | ：表示选择项：表示选择项CREATE：粗体表示关键字或必输项：粗体表示关键字或必输项Table_name：斜体表示用户输入项：斜体表示用户输入项创建创建 ：蓝色表示说明：蓝色表示说明DDL数据定义语言数据定义语言CREATE-CREATE-创建用户创建用户CREATE USER user_name IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE ta

6、blespace_name;创建创建user_nameuser_name用户，密码为用户，密码为passwordpassword，使用，使用tablespace_nametablespace_name表空间，如果没有指定表空间，默认表表空间，如果没有指定表空间，默认表空间为空间为systemsystem表空间。表空间。Oracle10gOracle10g有用户默认表空间设置。有用户默认表空间设置。建议在创建用户时一定要指名表空间。建议在创建用户时一定要指名表空间。DDL数据定义语言数据定义语言CREATE-CREATE-创建数据表创建数据表CREATE TABLE table_name(Col

7、umn1 datatype defaultnot null primary key,constraint key_name primary key(column_list) using index tablespace tablespace_name)tablespace tablespace_name;DatatypeDatatype 是数据类型是数据类型:varchar2(x), :varchar2(x), number(x,xnumber(x,x), date, integer), date, integer等。等。Not nullNot null非空限制，如果不写表示可为空。非空限制，

8、如果不写表示可为空。Primary keyPrimary key主键，可以紧跟在字段后，或在最后使用主键，可以紧跟在字段后，或在最后使用constraintconstraint。未指名表空间，数据表建立在用户默认的表空间中。未指名表空间，数据表建立在用户默认的表空间中。DDL数据定义语言数据定义语言CREATE-CREATE-创建索引创建索引CREATE UNIQUE INDEX index_name ON table_name(column_list)tablespace tablespace_name;在在table_nametable_name表上按照表上按照column_listcol

9、umn_list建立索引，索引名为建立索引，索引名为index_nameindex_name，索引保存在，索引保存在tablespace_nametablespace_name表空间中。表空间中。UNIQUE:UNIQUE:唯一性索引。唯一性索引。DDL数据定义语言数据定义语言ALTER-ALTER-修改表修改表ALTER TABLE table_name RENAME TO new_table_name;将将table_nametable_name表名修改为表名修改为new_table_namenew_table_name。ALTER TABLE table_name ADD(column1

10、 datatype default not null,column2 datatype default not null, . );在在table_nametable_name表中增加表中增加column1,column2.column1,column2.字段，追加到字段最后。字段，追加到字段最后。ALTER TABLE table_name MODIFY(column1 datatype default not null|nullable,column2 datatype default not null|nullable, . 修改修改table_nametable_name表字段属性，长

11、度或精度不能小于原长度或精度。表字段属性，长度或精度不能小于原长度或精度。ALTER TABLE table_name DROP COLUMN column_name;删除删除table_nametable_name表中的表中的column_namecolumn_name字段。字段。ALTER TABLE table_name RENAME COLUMN old_name TO new_name;修改修改table_nametable_name表的表的old_nameold_name字段名为字段名为new_namenew_name。ALTER TABLE table_name ADD CONS

12、TRAINT pk_name PRIMARY KEY(column_list);建立建立table_nametable_name表的主键，主键名表的主键，主键名pk_namepk_name，主键字段，主键字段column_listcolumn_list。ALTER TABLE table_name DROP CONSTRAINT pk_name;删除删除table_nametable_name表的表的pk_namepk_name主键。主键。一个表只能有一个主键一个表只能有一个主键DDL数据定义语言数据定义语言DROP -DROP -删除删除DROP TABLE table_name;删除删除t

13、able_nametable_name表。表。DROP INDEX index_name;删除删除index_nameindex_name索引。索引。删除主键（强制限制的一种），使用删除主键（强制限制的一种），使用ALTER TABLE table_name DROP CONSTRAINT pk_name;DML小结小结从从ALTER和和CREATE及及DROP语法数量再一次证明语法数量再一次证明需求是易变的。需求是易变的。如果增加表字段，并要求字段的排列位置，可以使用如果增加表字段，并要求字段的排列位置，可以使用:1.CREATE TABLE temp_table_name AS SELEC

14、T * FROM table_name;2.DROP TABLE table_name;3.CREATE TABLE table_name(column);4.INSERT INTO table_name VALUES(column_list) (SELECT column_list1 FROM temp_table_name);5.DROP TABLE table_name;需要注意的是，删除表后，表中的索引也被删除，所以，在执行上需要注意的是，删除表后，表中的索引也被删除，所以，在执行上述操作前要保留好表的索引脚本。述操作前要保留好表的索引脚本。这里没有写这里没有写DROP DATABAS

15、E的语法，如果大家想的语法，如果大家想要尝试删除数据库的话，最好在专家的指导下进行！要尝试删除数据库的话，最好在专家的指导下进行！DDL数据操作语言数据操作语言SELECT(查询查询)INSERT(插入插入)UPDATE(更新更新)DELETE(删除删除)DDL数据操作语言数据操作语言SELECT-查询查询SELECT ALL | DISTINCT ON ( expression , . ) * | expression AS output_name , . INTO TEMPORARY | TEMP TABLE new_table FROM from_item , . WHERE condi

16、tion GROUP BY expression , . HAVING condition , . UNION | INTERSECT | EXCEPT ALL select ORDER BY expression ASC | DESC | USING operator , . FOR UPDATE OF class_name , . LIMIT count | ALL OFFSET | , start 后续查询范例均以后续查询范例均以oracleoracle中的中的SCOTT/TIGERSCOTT/TIGER用户表为例用户表为例DDL数据操作语言数据操作语言SCOTT/TIGER表介绍表介绍

17、DEPTDEPT部门定义表，部门定义表，DEPTNODEPTNO是主键是主键EMPEMP职工表职工表,EMPNO,EMPNO主键，主键，DEPTNODEPTNO外键关联外键关联DEPTDEPT其他表没有数据或没有关系，不在这里介绍其他表没有数据或没有关系，不在这里介绍DDL数据操作语言数据操作语言SELECT-单表查询单表查询查询表中所有字段和所有记录，查询表中所有字段和所有记录，select后跟后跟* 表示所有字段表示所有字段SELECT * FROM DEPT;查询指定字段，在查询指定字段，在select后跟查询的字段名列表，字段间后跟查询的字段名列表，字段间用用,隔开隔开SELECT D

18、EPTNO, DNAME FROM DEPT;条件查询，条件查询，FROM后面使用后面使用WHERE，在，在WHERE中可以使中可以使用用=, , ,=, 10GROUP BY DEPTNO, DNAMEHAVING COUNT(*) 0ORDER BY DEPTNO DESC; -ORDER BY 是对返回的结果进行排序，所以必须放在最后一句。是对返回的结果进行排序，所以必须放在最后一句。SELECT DEPTNO, DNAMEFROM SCOTT.DEPTWHERE DEPTNO 10GROUP BY DEPTNO, DNAMEHAVING COUNT(*) 0ORDER BY 1 DES

19、C; -结果同第一个查询，字段位置从结果同第一个查询，字段位置从1开始。开始。DDL数据操作语言数据操作语言SELECT-FOR UPDATE锁等待查询。当查询结果中有被锁定记录时等待解锁，当记录锁等待查询。当查询结果中有被锁定记录时等待解锁，当记录被解锁后返回结果集，并锁定返回的记录。如果被解锁后返回结果集，并锁定返回的记录。如果FOR UPDATE后跟后跟NOWAIT，遇到锁后不等待，返回错误。，遇到锁后不等待，返回错误。ORACLE使用记录级锁定，当事物被提交或回滚后锁定被释放。使用记录级锁定，当事物被提交或回滚后锁定被释放。死锁问题死锁问题当两个事物以不同的顺序同时更新多个表时就会发生

20、死锁当两个事物以不同的顺序同时更新多个表时就会发生死锁（对牛），解决死锁的途径是手工杀掉死锁的进程或者重新（对牛），解决死锁的途径是手工杀掉死锁的进程或者重新启动数据库。因此，在使用事物更新多表数据时一定要小心，启动数据库。因此，在使用事物更新多表数据时一定要小心，使用面向对象的方法封装数据操作可以在很大程度上解决死使用面向对象的方法封装数据操作可以在很大程度上解决死锁问题。锁问题。DDL数据操作语言数据操作语言SELECT-子查询子查询查询语句可以嵌套，任何产生数值的地方都可以使用子查询。查询语句可以嵌套，任何产生数值的地方都可以使用子查询。在查询条件中使用子查询时，当子查询返回多个结果时只

21、能使用在查询条件中使用子查询时，当子查询返回多个结果时只能使用IN。查询顺序是先执行被依赖的底层查询，然后一层层向上查。查询顺序是先执行被依赖的底层查询，然后一层层向上查。子查询只被执行一次。子查询只被执行一次。查询平均工资最低的部门情况查询平均工资最低的部门情况SELECT DEPTNO, AVG(SAL)FROM SCOTT.EMPGROUP BY DEPTNOHAVING AVG(SAL) = (SELECT MIN(AVG(SAL) FROM SCOTT.EMPGROUP BY DEPTNO);-子查询先查询出最低的部门平均工资，然后查询部门最低平均工资等子查询先查询出最低的部门平均工

22、资，然后查询部门最低平均工资等于子查询工资的部门于子查询工资的部门DDL数据操作语言数据操作语言SELECT-子查询子查询在在FROM中使用子查询，子查询在这里相对与中使用子查询，子查询在这里相对与VIEWSELECT D.DEPTNO, D.DNAME, S.AVG_SAL, S.SUM_SAL, S.NUMFROM SCOTT.DEPT D, (SELECT DEPTNO, AVG(SAL) AS AVG_SAL, SUM(SAL) AS SUM_SAL, COUNT(*) AS NUMFROM SCOTT.EMPGROUP BY DEPTNO) SWHERE D.DEPTNO = S.D

23、EPTNO;在在WHERE中使用子查询，例子不好，应该直接写表关联，这里只是为中使用子查询，例子不好，应该直接写表关联，这里只是为了说明语法。了说明语法。SELECT * FROM SCOTT.EMP WHERE DEPTNO IN (SELECT DEPTNO FROM SCOTT.DEPT WHERE DEPTNO = 20);DDL数据操作语言数据操作语言SELECT-子查询子查询使用使用EXISTS(NOT EXISTS)替换替换IN(NOT IN)IN(NOT IN)在执行数据库操作时性能非常低下，应该使用在执行数据库操作时性能非常低下，应该使用EXISTS(NOT EXISTS)替

24、换，特别是替换，特别是NOT IN子句将执行一个内部的排子句将执行一个内部的排序和合并序和合并;EXISTS子查询使用主表的字段限制查询数据子查询使用主表的字段限制查询数据SELECT * FROM SCOTT.EMP E WHERE EXISTS (SELECT * FROM SCOTT.DEPT WHERE DEPT.DEPTNO = E.DEPTNO AND DEPTNO = 20);-因为因为EXISTS可以看到外表，所以，如果表名重复，使用表别名区分，可以看到外表，所以，如果表名重复，使用表别名区分，在子查询中一定写清楚和外表的关联关系，另外，子查询写在子查询中一定写清楚和外表的关联

25、关系，另外，子查询写SELECT * 是对的，不用写字段名。是对的，不用写字段名。DDL数据操作语言数据操作语言UPDATE修改修改用来更新数据表中的数据用来更新数据表中的数据 UPDATE table_nameSET column_name = new_value, WHERE condition;更新更新DEPT表，将表，将20号部门名称修改为号部门名称修改为销售部销售部，地址修改为，地址修改为北京北京UPDATE DEPT SET DNAME = 销售部销售部, LOC = 北京北京WHERE DEPTNO = 20;子查询也可在子查询也可在UPDATE中使用中使用,将职工领导是将职工领

26、导是KING的人员薪资增加的人员薪资增加100元元UPDATE SCOTT.EMP SET SAL = SAL + 100WHERE EMPNO IN (SELECT E.EMPNO FROM SCOTT.EMP E, SCOTT.EMP G WHERE E.MGR = G.EMPNO AND G.ENAME = KING); UPDATE SCOTT.EMP SET SAL = SAL + 100WHERE EXISTS (SELECT * FROM SCOTT.EMP E, SCOTT.EMP G WHERE E.MGR = G.EMPNO AND E.EMPNO = EMP.EMPNO

27、AND G.ENAME = KING); DDL数据操作语言数据操作语言INSERT-插入插入语法语法:INSERT INTO table_name (column,.) VALUES (expression ,) | SELECT query当表字段和插入值相同时，可以省略字段列表当表字段和插入值相同时，可以省略字段列表INSERT INTO SCOTT.BONUS VALUES(TURNER, SALESMAN, 200, 40);当只插入部分字段时，必须列举字段，未赋值字段使用默认值或为空当只插入部分字段时，必须列举字段，未赋值字段使用默认值或为空INSERT INTO SCOTT.BO

28、NUS(ENAME, JOB, SAL) VALUES(CLARK, MANAGER, 100);也可以使用查询给表插入数据也可以使用查询给表插入数据INSERT INTO SCOTT.BONUS(ENAME, JOB, SAL) SELECT ENAME, JOB, SAL FROM SCOTT.EMP WHERE MGR = 7698;DDL数据操作语言数据操作语言DELETE-删除删除语法语法:DELETE FROM table_nameWHERE condition;删除删除BONUS中中ENAME为为CLARK的记录的记录DELETE FROM SCOTT.BONUS WHERE E

29、NAME = CLARK删除重复记录，删除重复记录，ROWID是记录的物理位置，一经确定永不改变是记录的物理位置，一经确定永不改变DELETE FROM SCOTT.BONUS B WHERE ROWID 1);-删除删除ENAME重复记录重复记录序列号序列号创建创建CREATE SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE;说明：说明：INCREMENT BY n 一次增长一次增长n 个数字个

30、数字START WITH n 初始值初始值NOMAXVALUE 缺省值缺省值10E+27NOMINVALUE 缺省值缺省值1NOCYCLE 不循环不循环, 常用于唯一关键字常用于唯一关键字CACHE n 在内存里缓存在内存里缓存n个序列个序列,出错回退时会丢失出错回退时会丢失创建从创建从1开始，不缓存的开始，不缓存的EMP_EMPNO序列。序列。CREATE SEQUENCE EMP_EMPNO START WITH 1 NOCACHE;修改修改ALTER SEQUENCE name INCREMENT BY n MAXVALUE n | NOMAXVALUE MINVALUE n | NOM

31、INVALUE CYCLE | NOCYCLE CACHE n | NOCACHE;-起始值不能改变，如果要改变序列的起始值起始值不能改变，如果要改变序列的起始值, 先删除先删除, 再新建。再新建。删除删除DROP SEQUENCE name;DROP SEQUENCE EMP_EMPNO;序列号序列号使用使用NEXTVAL下一个序列号值下一个序列号值CURRVAL当前序列号值当前序列号值如果如果EMP_EMPNO是一个序列号，可以用下面是一个序列号，可以用下面SQL取序列值取序列值-去序列的当前值去序列的当前值SELECT EMP_EMPNO.CURRVAL FROM DUAL;-去序列的下

32、一个值去序列的下一个值SELECT EMP_EMPNO.NEXTVAL FROM DUAL;-使用序列插入数据使用序列插入数据INSERT INTO EMP(EMPNO, ENAME,) VALUES(EMP_EMPNO.NEXTVAL, JONE,);不能用序列号的不能用序列号的nextval和和currval的地方的地方视图查询、视图查询、distinct查询、有查询、有group by,having,order by的查询、的查询、有子查询的查询、表里的缺省值有子查询的查询、表里的缺省值注释注释/* 注释内容注释内容 */-注释内容注释内容-SELECT * DELETEFROM EMP

33、WHERE DEPTNO = 20;提示：删除数据前最好先查询一下。可以先写提示：删除数据前最好先查询一下。可以先写SELECT *，然后把，然后把SELECT *注释掉，增加注释掉，增加DELETE。常用函数常用函数数字函数数字函数ABS 取绝对值取绝对值 POWER 乘方乘方 LN 10为底数取为底数取幂幂SQRT 平方根平方根 EXP e的的n次乘方次乘方 LOG(m,n) m为底数为底数n取取幂幂数学运算函数数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH CEIL 大于或等于取整数大于或等于取整数 FLOOR 小于或等于取整

34、数小于或等于取整数MOD 取余数取余数 ROUND(n,m) 按按m的位数取四舍五入值如果的位数取四舍五入值如果round(日期日期): 中午中午12以后以后将是明天的日期将是明天的日期. round(sysdate,Y)是年的第一天是年的第一天TRUNC(n,m) 按按m的位数取前面的数值如果的位数取前面的数值如果trunc(日期日期), 确省的是去掉确省的是去掉时间时间常用函数常用函数字符函数字符函数CHR 按数据库的字符集由数字返回字符按数据库的字符集由数字返回字符 CONCAT(c1,c2) 把两个字符把两个字符c1,c2组合成一个字符组合成一个字符, 和和 | 相同相同REPLACE

35、(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字符串替换字符串替换右右边的空位边的空位提示：去掉字符串中间的空格可以使用提示：去掉字符串中间的空格可