《ORACLE数据库教程-SQL基础知识篇.ppt》由会员分享，可在线阅读，更多相关《ORACLE数据库教程-SQL基础知识篇.ppt（39页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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)DDL:DDL:数据定义语言数据定义
3、语言 （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 constraint(s)set constraint(s)、se
4、t transactionset transaction）审计控制：审计控制：auditaudit、noauditnoaudit系统控制：系统控制：alter system alter system 会话控制：会话控制：alter session alter session 其他语句：其他语句：commentcomment（添加注释）、（添加注释）、explain planexplain plan、analyzeanalyze、validatevalidate、call call DDL数据定义语言数据定义语言CREATE(CREATE(建立建立)ALTER(ALTER(修改修改)DROP(D
5、ROP(删除删除)语言描述约定语言描述约定 ：表示可选项：表示可选项|：表示选择项：表示选择项CREATE：粗体表示关键字或必输项：粗体表示关键字或必输项Table_name：斜体表示用户输入项：斜体表示用户输入项创建创建 ：蓝色表示说明：蓝色表示说明DDL数据定义语言数据定义语言CREATE-CREATE-创建用户创建用户CREATE USER user_name IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE tablespace_name;创建创建user_nameuser_na
6、me用户，密码为用户，密码为passwordpassword，使用，使用tablespace_nametablespace_name表空间，如果没有指定表空间，默认表表空间，如果没有指定表空间，默认表空间为空间为systemsystem表空间。表空间。Oracle10gOracle10g有用户默认表空间设置。有用户默认表空间设置。建议在创建用户时一定要指名表空间。建议在创建用户时一定要指名表空间。DDL数据定义语言数据定义语言CREATE-CREATE-创建数据表创建数据表CREATE TABLE table_name(Column1 datatype defaultnot null prim
7、ary key,constraint key_name primary key(column_list)using index tablespace tablespace_name)tablespace tablespace_name;Datatype Datatype 是数据类型是数据类型:varchar2(x),number(x,x),date,integer:varchar2(x),number(x,x),date,integer等。等。Not nullNot null非空限制，如果不写表示可为空。非空限制，如果不写表示可为空。Primary keyPrimary key主键，可以紧跟在
8、字段后，或在最后使用主键，可以紧跟在字段后，或在最后使用constraintconstraint。未指名表空间，数据表建立在用户默认的表空间中。未指名表空间，数据表建立在用户默认的表空间中。DDL数据定义语言数据定义语言CREATE-CREATE-创建索引创建索引CREATE UNIQUE INDEX index_name ON table_name(column_list)tablespace tablespace_name;在在table_nametable_name表上按照表上按照column_listcolumn_list建立索引，索引名为建立索引，索引名为index_nameinde
9、x_name，索引保存在，索引保存在tablespace_nametablespace_name表空间中。表空间中。UNIQUE:UNIQUE:唯一性索引。唯一性索引。DDL数据定义语言数据定义语言ALTER-ALTER-修改表修改表ALTER TABLE table_name REMAME TO new_table_name;将将table_nametable_name表名修改为表名修改为new_table_namenew_table_name。ALTER TABLE table_name ADD(column1 datatype default not null,column2 datat
10、ype 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表字段属性，长度或精度不能小于原长度或精度。表字段属性，长度或精度不能小于原长度或精度。ALTER T
11、ABLE 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 CONSTRAINT pk_name PRIMARY KEY(column_list);建立建立
12、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;删除删除table_nametable_name表。表。DROP INDEX index_name;删
13、除删除index_nameindex_name索引。索引。删除主键（强制限制的一种），使用删除主键（强制限制的一种），使用ALTER TABLE table_name DROP CONSTRAINT pk_name;DML小结小结从从ALTER和和CREATE及及DROP语法数量再一次证明需语法数量再一次证明需求是易变的。求是易变的。如果增加表字段，并要求字段的排列位置，可以使用如果增加表字段，并要求字段的排列位置，可以使用:1.CREATE TABLE temp_table_name AS SELECT*FROM table_name;2.DROP TABLE table_name;3.CR
14、EATE 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 DATABASE的语法，如果大家想的语法，如果大家想要尝试删除数据库的话，最好在专家的指导下进行！要尝试删除数据
15、库的话，最好在专家的指导下进行！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 condition GROUP BY expression,.HAVING condition,.UNION|INTERSECT|EXCEPT
16、 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表介绍表介绍DEPTDEPT部门定义表，部门定义表，DEPTNODEPTNO是主键是主键EMPEMP职工表职工表,EMPNO,EMPNO主键，主键，DEPTNODEPTNO外键关联外键关联DE
17、PTDEPT其他表没有数据或没有关系，不在这里介绍其他表没有数据或没有关系，不在这里介绍DDL数据操作语言数据操作语言SELECT-单表查询单表查询查询表中所有字段和所有记录，查询表中所有字段和所有记录，select后跟后跟*表示所有字段表示所有字段SELECT*FROM DEPT;查询指定字段，在查询指定字段，在select后跟查询的字段名列表，字段间用后跟查询的字段名列表，字段间用,隔开隔开SELECT DEPTNO,DNAME FROM DEPT;条件查询，条件查询，FROM后面使用后面使用WHERE，在，在WHERE中可以使中可以使用用=,=,10GROUP BY DEPTNO,DNA
18、MEHAVING COUNT(*)0ORDER BY DEPTNO DESC;-ORDER BY 是对返回的结果进行排序，所以必须放在最后一句。是对返回的结果进行排序，所以必须放在最后一句。SELECT DEPTNO,DNAMEFROM SCOTT.DEPTWHERE DEPTNO 10GROUP BY DEPTNO,DNAMEHAVING COUNT(*)0ORDER BY 1 DESC;-结果同第一个查询，字段位置从结果同第一个查询，字段位置从1开始。开始。DDL数据操作语言数据操作语言SELECT-FOR UPDATE锁等待查询。当查询结果中有被锁定记录时等待解锁，当记录锁等待查询。当查
19、询结果中有被锁定记录时等待解锁，当记录被解锁后返回结果集，并锁定返回的记录。如果被解锁后返回结果集，并锁定返回的记录。如果FOR UPDATE后跟后跟NOWAIT，遇到锁后不等待，返回错误。，遇到锁后不等待，返回错误。ORACLE使用记录级锁定，当事物被提交或回滚后锁定被释放。使用记录级锁定，当事物被提交或回滚后锁定被释放。死锁问题死锁问题当两个事物以不同的顺序同时更新多个表时就会发生死锁（对当两个事物以不同的顺序同时更新多个表时就会发生死锁（对牛），解决死锁的途径是手工杀掉死锁的进程或者重新启动数牛），解决死锁的途径是手工杀掉死锁的进程或者重新启动数据库。因此，在使用事物更新多表数据时一定要
20、小心，使用面据库。因此，在使用事物更新多表数据时一定要小心，使用面向对象的方法封装数据操作可以在很大程度上解决死锁问题。向对象的方法封装数据操作可以在很大程度上解决死锁问题。DDL数据操作语言数据操作语言SELECT-子查询子查询查询语句可以嵌套，任何产生数值的地方都可以使用子查询。查询语句可以嵌套，任何产生数值的地方都可以使用子查询。在查询条件中使用子查询时，当子查询返回多个结果时只能使用在查询条件中使用子查询时，当子查询返回多个结果时只能使用IN。查询顺序是先执行被依赖的底层查询，然后一层层向上查。查询顺序是先执行被依赖的底层查询，然后一层层向上查。子查询只被执行一次。子查询只被执行一次。
21、查询平均工资最低的部门情况查询平均工资最低的部门情况SELECT DEPTNO,AVG(SAL)FROM SCOTT.EMPGROUP BY DEPTNOHAVING AVG(SAL)=(SELECT MIN(AVG(SAL)FROM SCOTT.EMPGROUP BY DEPTNO);-子查询先查询出最低的部门平均工资，然后查询部门最低平均工资等子查询先查询出最低的部门平均工资，然后查询部门最低平均工资等于子查询工资的部门于子查询工资的部门DDL数据操作语言数据操作语言SELECT-子查询子查询在在FROM中使用子查询，子查询在这里相对与中使用子查询，子查询在这里相对与VIEWSELECT
22、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.DEPTNO;在在WHERE中使用子查询，例子不好，应该直接写表关联，这里只是为中使用子查询，例子不好，应该直接写表关联，这里只是为了说明语法。了说明语法。SELECT*FROM SCOTT.EMP WHERE DEPTNO IN(SELECT DE
23、PTNO FROM SCOTT.DEPT WHERE DEPTNO=20);DDL数据操作语言数据操作语言SELECT-子查询子查询使用使用EXISTS(NOT EXISTS)替换替换IN(NOT IN)IN(NOT IN)在执行数据库操作时性能非常低下，应该使用在执行数据库操作时性能非常低下，应该使用EXISTS(NOT EXISTS)替换，特别是替换，特别是NOT IN子句将执行一个内部的排子句将执行一个内部的排序和合并序和合并;EXISTS子查询使用主表的字段限制查询数据子查询使用主表的字段限制查询数据SELECT*FROM SCOTT.EMP E WHERE EXISTS(SELECT
24、*FROM SCOTT.DEPT WHERE DEPT.DEPTNO=E.DEPTNO AND DEPTNO=20);-因为因为EXISTS可以看到外表，所以，如果表名重复，使用表别名区分，可以看到外表，所以，如果表名重复，使用表别名区分，在子查询中一定写清楚和外表的关联关系，另外，子查询写在子查询中一定写清楚和外表的关联关系，另外，子查询写SELECT*是对的，不用写字段名。是对的，不用写字段名。DDL数据操作语言数据操作语言UPDATE修改修改用来更新数据表中的数据用来更新数据表中的数据 UPDATE table_nameSET column_name=new_value,WHERE co
25、ndition;更新更新DEPT表，将表，将20号部门名称修改为号部门名称修改为销售部销售部，地址修改为，地址修改为北京北京UPDATE DEPT SET DNAME=销售部销售部,LOC=北京北京WHERE DEPTNO=20;子查询也可在子查询也可在UPDATE中使用中使用,将职工领导是将职工领导是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=KI
26、NG);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 AND G.ENAME=KING);DDL数据操作语言数据操作语言INSERT-插入插入语法语法:INSERT INTO table_name(column,.)VALUES(expression,)|SELECT query当表字段和插入值相同时，可以省略字段列表当表字段和插入值相同时，可以省略字段列表INSERT INTO SCOTT.BO
27、NUS VALUES(TURNER,SALESMAN,200,40);当只插入部分字段时，必须列举字段，未赋值字段使用默认值或为空当只插入部分字段时，必须列举字段，未赋值字段使用默认值或为空INSERT INTO SCOTT.BONUS(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-删除删除
28、语法语法:DELETE FROM table_nameWHERE condition;删除删除BONUS中中ENAME为为CLARK的记录的记录DELETE FROM SCOTT.BONUS WHERE ENAME=CLARK删除重复记录，删除重复记录，ROWID是记录的物理位置，一经确定永不改变是记录的物理位置，一经确定永不改变DELETE FROM SCOTT.BONUS B WHERE ROWID 1);-删除删除ENAME重复记录重复记录序列号序列号创建创建CREATE SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n|NOMAX
29、VALUE MINVALUE n|NOMINVALUE CYCLE|NOCYCLE CACHE n|NOCACHE;说明：说明：INCREMENT BY n 一次增长一次增长n 个数字个数字START WITH n 初始值初始值NOMAXVALUE 缺省值缺省值10E+27NOMINVALUE 缺省值缺省值1NOCYCLE 不循环不循环,常用于唯一关键字常用于唯一关键字CACHE n 在内存里缓存在内存里缓存n个序列个序列,出错回退时会丢失出错回退时会丢失创建从创建从1开始，不缓存的开始，不缓存的EMP_EMPNO序列。序列。CREATE SEQUENCE EMP_EMPNO START WI
30、TH 1 NOCACHE;修改修改ALTER SEQUENCE name INCREMENT BY n MAXVALUE n|NOMAXVALUE MINVALUE n|NOMINVALUE CYCLE|NOCYCLE CACHE n|NOCACHE;-起始值不能改变，如果要改变序列的起始值起始值不能改变，如果要改变序列的起始值,先删除先删除,再新建。再新建。删除删除DROP SEQUENCE name;DROP SEQUENCE EMP_EMPNO;序列号序列号使用使用NEXTVAL下一个序列号值下一个序列号值CURRVAL当前序列号值当前序列号值如果如果EMP_EMPNO是一个序列号，可以
31、用下面是一个序列号，可以用下面SQL取序列值取序列值-去序列的当前值去序列的当前值SELECT EMP_EMPNO.CURRVAL FROM DUAL;-去序列的下一个值去序列的下一个值SELECT EMP_EMPNO.NEXTVAL FROM DUAL;-使用序列插入数据使用序列插入数据INSERT INTO EMP(EMPNO,ENAME,)VALUES(EMP_EMPNO.NEXTVAL,JONE,);不能用序列号的不能用序列号的nextval和和currval的地方的地方视图查询、视图查询、distinct查询、有查询、有group by,having,order by的查询、的查询、
32、有子查询的查询、表里的缺省值有子查询的查询、表里的缺省值注释注释/*注释内容注释内容 */-注释内容注释内容-SELECT*DELETEFROM EMPWHERE DEPTNO=20;提示：删除数据前最好先查询一下。可以先写提示：删除数据前最好先查询一下。可以先写SELECT*，然后把，然后把SELECT*注释掉，增加注释掉，增加DELETE。常用函数常用函数数字函数数字函数ABS 取绝对值取绝对值 POWER 乘方乘方 LN 10为底数取为底数取幂幂SQRT 平方根平方根 EXP e的的n次乘方次乘方 LOG(m,n)m为底数为底数n取取幂幂数学运算函数数学运算函数:ACOS ATAN AT
33、AN2 COS COSH SIGN SIN SINH TAN TANH CEIL 大于或等于取整数大于或等于取整数 FLOOR 小于或等于取整数小于或等于取整数MOD 取余数取余数 ROUND(n,m)按按m的位数取四舍五入值如果的位数取四舍五入值如果round(日期日期):中午中午12以后以后将是明天的日期将是明天的日期.round(sysdate,Y)是年的第一天是年的第一天TRUNC(n,m)按按m的位数取前面的数值如果的位数取前面的数值如果trunc(日期日期),确省的是去掉时确省的是去掉时间间常用函数常用函数字符函数字符函数CHR 按数据库的字符集由数字返回字符按数据库的字符集由数字
34、返回字符 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 字符
35、全部小写字符全部小写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字字符串替换符串替换右右边的空位边的空位提示：去掉字符串中间的空格可以
36、使用提示：去掉字符串中间的空格可以使用replace常用函数常用函数日期函数日期函数ADD_MONTHS(d,n)日期值加日期值加n月月LAST_DAY(d)返回当月的最后一天的日期返回当月的最后一天的日期MONTHS_BETWEEN(d1,d2)两个日期值间的月份两个日期值间的月份NEXT_DAY(d)返回日期值下一天的日期返回日期值下一天的日期SYSDATE 当前的系统时间当前的系统时间DUAL是是SYS用户下一个空表，它只有一个字段用户下一个空表，它只有一个字段dummy提示：提示：dual可以理解为虚表，多在查询系统函数返回值时使用，可以理解为虚表，多在查询系统函数返回值时使用，以满足
37、以满足SELECT语法规则，如语法规则，如select sysdate from dual;select 3+4 from dual;常用函数常用函数转换函数转换函数TO_CHAR(date,日期显示格式日期显示格式)TO_CHAR(number)用于显示或报表的格式对齐用于显示或报表的格式对齐TO_DATE(char,日期显示格式日期显示格式)TO_LOB 把把long字段转换成字段转换成lob字段字段TO_NUMBER(char)用于计算或者比较大小用于计算或者比较大小 日期显示格式日期显示格式年年:YYYY,YEAR,YY季季:Q月月:MM,MONTH,MON日日:DD,DAY,DY时时
38、:HH24,HH12,HH(12小时小时)分分:MI秒秒:SS常用函数常用函数逻辑比较函数逻辑比较函数NVL(EXPR1,EXPR2)当当EXPR1为空用为空用EXPR2替代替代DECODE(EXPRV1R1V2R2.)当当EXPR=V1时返回时返回R1当当EXPR=V2是放回是放回V2EXPR后条件和返回值成对出现，最后一个单值是不在条件中的返回值，后条件和返回值成对出现，最后一个单值是不在条件中的返回值，如果没有单值，不满足条件的返回如果没有单值，不满足条件的返回NULL。SELECT ENAME,DECODE(JOB,MANAGER,经理经理,PRESIDENT,总裁总裁,SALESMAN,销售销售,不详不详)FROM SCOTT.EMP;CASE WHEN condition THEN express1 ELSE express2 END当当condition成立返回成立返回express1否则返回否则返回express2SELECT ENAME,HIREDATE,(CASE WHEN TO_NUMBER(TO_CHAR(HIREDATE,MM)6 THEN 下半年下半年 ELSE 上半年上半年 END)AS 入职时间入职时间 FROM SCOTT.EMP;-TO_CHAR(HIREDATE,MM)取时间月份取时间月份