ORACLE数据库教程

作可仅不衛位，俚g佑対不縛不傅一JRA不传语录SQL-001y jJTTts a简介 SQL fStructured Query Language J 结构化查询 语言是IBM公司San Jose实佥宝为System R而设 计的香询语言，后彼国际标准化组织($O)拉 准作为关糸数据岸语扌的囲际标准。 SQL目甫運循的1992年标准，即SQL-92o各数据库厂家对SQL-92标准沟有护丸，护充部 分不能完全保证数据库之间的兼彖性。、位玄旻无令鸽SQL可以分为：1基础篇只介绍裔两种丿 DML:数据操作语言 fData Manipulation Languagej : select, insert, update, delete,(其他：truncate) DDL：數据走义fData Definition Languagej : create, drop, alter,其他：rename) DCLJt据挫制语 ir： grant revoke. set role 寧务雄制：commit, rollback, savepoint (其他：lock table, set con$traint(s)、set transactionJ申计控制：audit、noaudit 糸统控制:alter system 会话挫制:alter session 其他语句：comment C海加注释丿、explainplan、 analyze. validate, callMA不”录DDL数据定义语言 CREATE （建立） ALTER（修型DROP（| 除）死鼻4平走死知 -A不*语录離警像梆111|:表示可选项 |:表示选择项 CREATE：粗体表示关健孕或必输项 Table_name 斜体表示用户输入项 创建蓝色表示说明i厶崇1+12,启由宿歼盘的吳团阻的力*一A不传语录DDL数据定义语言CREATE-创 用户- CREATE USER user_name IDENTIFIED BY passwordDEFAULT TABLESPACE tablespace_nameTEMPORARY TABLESPACE tablespace_name;创建user_name用户，密码为password,使用 tablespace_name表空间，如果没有指走叙变间，默认农 空间孙y$tem裘空间。OradelOg有用户默认表变间HL置。建仪农创建用户对一定要指老表金间。还足乩疳姜，这幺金要的阳痺还是给埠专证乜一軌不催语录0 e e 0 gov a cwDDL数据定义语言CREATE“创电数据表40 lCREATE TABLE table_name(Column 1 datatype defaultnot null primary key,constraint key_name primary key(columnjis using index tablespace tablespace_nameJ)tablespace tablespace_name;Datatype 是数据奏憂:varchar2(x), number(x,x), date, integer。Notnull非交限术|,如系不舷示可为交。s”丁 2, g叮碍PrimarykeyiM,可以紧跟应孝段后，或应最后使用constraint。 未指名裹交间，數槪表建竝在用户默认的表丈间中。DDL数据定义语言CREATE-创建奈引 CREATE UNIQUE INDEX index_name ONtable_name(coIumnist)tablespace tablespace_nameAtable_name表上按JKcolumnist建立崇引,楽計老夠 index_name,崇引锋存在tablespacename住间 中。 UNIQUE: it 二 弘W莒湖布弟W丹请TBt汨许辩抒DDL数据定义语言ALTER“修欧表ALTER TABLE table_name RENAME TO new_table_name;将 tablename 表名 修改为 new_table_name。ALTER TABLE table_name ADD(column 1 datatype default not nu9column2datatype default not null, .M.);4 table name表中增加columnl,column2.学段,追那到学技景后。ALTER TABLE table_name MODIFY (column 1 datatype default not null|nullable,co/t/n7n2 datatype default not null|nullable9 修改tab】e_name裹丰段属性,衣友成祷友不能小于点长度戎聘度。ALTER TABLE table_name DROP COLUMN column_name;病除table_name表 中的column_name学段。ALTER TABLE table_name RENAME COLUMN old_name TO new_name 修改 table_name 裹 的 old_name 学段名为 new_name。ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARYKEY (co/UimiJisD；-电丈table name表的主虢,圭键老pk name,宝 键学段columr)i$t。 ALTER TABLE table_name DROP CONSTRAINT pk_name;刪除table_name表的pk_name殳健。一个表只能有一个主健DDL数据定义语言DROP “刪除 DROP TABLE table_name;剧徐 table_ nameL。DROP INDEX index_name;刪際index_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. CREATE TABLE table_name(column.);4. INSERT INTO table_name VALUES(columnJist) (SELECT columnist1 FROM temp_table_name);5. DROP TABLE 诚需要注盘的是，期除表后，表中的崇引也枚期除，所以，农规行上述 操作前要榇留好表的条引冲本。这里没有2|DROP DATABASE的语鮎 如果大家想 要套试剜除数据库的话，最好应专家的拒导下进行！人羯*死对“，苑卄算机令右丁对MA不0 e e ci 00wvc c*uDDL数据操作语言SELECT（查询）INSERT（插入）UPDATE（j）DELETE* 除）.L J人，- ftl. z rJ.(L-uL cofiff dTt?V cl 制O 064 il J 孑DDL数据操作语言SELECT查询SELECT ALL I DISTINCT ON ( expression ,.) * I expression AS outputname ，INTO TEMPORARY I TEMP TABLE newjable FROM from_item ,.WHERE conditionGROUP BY expression ,HAVING coftditicm , ”J UNION I INTERSECT I EXCEPT ALL select ORDER BY expression ASC I DESC I USING operator ,.FOR UPDATE OF class_name ,.LIMIT count I ALL OFFSET I, start 后變圭询临钢均以oradw中的SCOTT/TIGER用户菽另倒DDL数据操作语言SCOTT/TIGER 表介绍DEPT却门走义 DEPCIO是殳健Columns of DEFT4 1 NameTypeNullableDefault | CommentsDEPTNO NUMBERI21部门编码丫DNAME VARCHAR2(14)LOC，匸部门若称二VARCHAR2(13) YSiEMP示工&EMPNO主健.DEPTNO外健关朕DEPTCoLuans of EIFNameI Type| NullableDefault I CommentsEMPNO NUMBER(4) EN.AMEJOBMGRHIREDATESALCOMMDEPTNOVARCHAR2(W)VARCHAR2NUMBER(4)DATENUMBER(7Z2)MUMBER(7Z2)NUMBER(2)工名位管用水注门 职姓职主聘薪备部真他4汶吉就捱氐汶寺关余,不衣这里介何JR人不DDL数据燥作语言_:j 1 SELECT* 衰圭 查询表中所有学段和所疽记录，select后跟*表示所有学段 SELECT * FROM DEPT; 一 圭询摘走学段，A select后跟查询的学段名刊表，学段间 用隔开 SELECT DEPTNO, DNAME FROM DEPT; 条件圭询FROM后面使用WHERE,虚WHERE中可以使 用=,=,_MINUSIAM 作 INTERSECT交績繰作DDL数据操作语言U SELECTORDER ORDER BY排序,琅數孕C代表学段住置）航学段名，ASC升序DESC阵序默认升序。 ELECT DEPTNO, DNAME -1FROM SCOTT.DEPT V HERE DEPTNO 10GROUP BY DEPTNO, DNAME鲁HAVING COUNTS）刃* L P彗只 BY DEPTNO D1SC;-ORDER BY 对逅回的结系进行排序，所以党须放淮最后一旬。SELECT DEPTNO, DNAMEFROM SCOTT.DEPTWHERE DEPTNO 10GROUP BY DEPTNO, DNAMEHAVING COUNTS） 0ORDER BY 1 DESC;建系同第一个圭询，学段位置从1开始。一ftA不存语录DDL数据操作语言1r SELECT-FOR UPDATE J祯等待查询。当查询结崇中有痕祯走祀录对等待解碳，当记录 痕解碳后返凹结系為，并碳走逅回的祀录。如泉FOR UPDATE 后跟NOWAIT,遇列碳后不孑待，逅回锵谖。 ORACLE使用记录“定，当李畅菠提夾蜕回液后锁定故释放。4.死磺问题三当两个丰肠以不耐的顺序阿对更祈多个表对就会发生死祯（对 牛丿,解决死碳的罐技是手工杀掉死礙的进程或者董新左动数 据库。因此，蛊後用孝畅更新多表教擁对一定要小心，使用面 .向对余的方法封MJU操作可以庭很大程度上解决死碳问题。SELECT子查询去询诗勺可以戦養,任何产生嵐值的地方却可以使用孑妥询。于子圭询工资的部门HAVING AVG(SAL) = (SELECT MIN(AVG(SAL) FROM SCOTT.EMP GROUP BY DEPTNO)；子圭询丸圭询岀最低的部门平均工资，然后圭询部门最低平均工资著DDL数据操作语言农圭询条件中使用子圭询对，由子去询返回多个结系对只能後用IN。 姿询顺序是丸林行秋俵赖的底层圭询，然后一屋农向上妥。子圭询只痕执行一次。妥询平均工资最低的部口楼况SELECT DEPTNO, AVG(SAL) =FROM SCOTT.EMPGROUP BY DEPTNO-DDL数据操作语厂- SELECT子香询AFROM中使用子圭询，子查询农这里柏对与VIEWSELECT D.DEPTNO, D.DNAME, S.AVG_SAL, S.SUM_SAL, S.NUMI ROM SCOTT.DEPT D,(SELECT DEPTNO, AVG(SAL) AS AVG_SAL, SUM(SAL) AS SUM_SAL, COUNT(*) AS NUM气FROM SCOTT.EMP嚣 13ROUP BY DEPTNO) SW 二 帀fi離 D.DEPTNO = S.DEPlOi庭WHERE中後用子查询，钢子不好，应该直接鸳表关联，这更只是为 了说朗语出。一JRA不传馭SELECT * FROM SCOTT.EMP WHERE DEPTNO IN (SELECT DEPTNO FROM SCOTT.DEPT WHERE DEPTNODDL数据操作语言SELECT子查询 使用 EXISTS(NOT EXISTS)售據IN(NOT IN)IN(NOTIN)虚桃行数据库操作对性能非嗥低下，应该使用EXISTS(NOT EXISTS)格據，特别是NOT IN孑金将轨行一个内部的排 考和合幷；EXISTS孑圭询使用圭表的学段限制圭询救据SELECT *FROM SCOTT.EMP EWHERE EXISTS (SELECT * FROM SCOTT.DEPT WHERE DEPT.DEPTNO = E.DEPTNO AND DEPTNO = 20);因为EXISTS可以看到外哀，所以，如糸表名童复，後用袅別名区分, 在孑圭询中一走筠请楚和外套的关联关糸，另外，子圭询頁SELECT* 是对的，不用筠孕段名。X-A,平知st-扳MA 不*DDL数据操作语言UPDATE修咬用来更新数煽表中的散据UPDATE table_nameSET columr)_name = new_value,.WHERE condition;更新DEPT 将20号部口名穂修欧为地址修改莎北駅UPDATE DEPT7ZZZ SET DNAME =部；LOC = 北京._ WHERE DEPTNO = 20；孑音询也可A UPDATE中後用，将职工领导是KING的人贡薪资增加100无UPDATE SCOTT.EMPSET SAL = SAL +100_WHERE EMPNO IN (SELECT E.EMPNO FROM SCOTT.EMP E, SCOTT.EMP G WHERE E.MGR = G.EMPNO AND G.ENAME = KING*)；UPDATE SCOTT.EMPSET SAL = SAL + 100WHERE EXISTS (SELECT * FROM SCOTT.EMP E, SCOTT.EMP GWHERE E.MGR = G.EMPNOAND E.EMPNO = EMP.EMPNOZ工僵旻丸工値,舜以為才NilAND G.ENAME = KING)；TR人柚体TITSDDL数据操作语言INSERT插入INSERT INTO SCOTT.BONUS(ENAME3 JOB, SAL)SELECT ENAME, JOB, SAL FROM SCOTT.EMP WHERE MGR = 7698;鼻论上样是闫4LINSERT INTO table_name (column,.) VALUES (expression , J) SELECT a“6ry杏表孕段和插入值相岡对，可以布咚孕段列表INSERT INTO SCOTT.BONUS VALUESCTURNER： SALESMAN； 200, 40); 吉只插入秤分学段对，強须列举学段，未赋值学段使用默认值或交 INSERT INTO SCOTT.BONUS(ENAME, JOB, SAL) VALUESCCLARK1, MANAGER*, 100);也可以使用圭询给裹插入散据占刀刁 dJNdd) i)DDL数据操作语言l DELETE| 卧竇跆_二 DELETE FROM table nameVBEBE condition; | 除 BONUS 中 ENAME 为CLARK的超录DELETE FROM SCOTT.BONUS WHERE ENAME = 9LARK病徐芟复杞录，ROWID是北录的畅理住置，一经确走永不友支DELETE FROM SCOTT.BONUS BWHERE ROWID 1)； 7徐ENAME重复杞豪令巳先嘉舄克代弘剑心，会樽工件鼻鼻。古01 d cl o 0 cr ftl由/训Add占占J#Ji/d J / tf a v序列号; 创之_CREATE SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE;说朗：INCREMENT BY n -次增个散丰-1START WITHn 杓 jfejt_1 NOMAxVALUE 缺才他 10E*27JitNOMINVALUE 缺不值 1_NOCYCLE不循环，帝用于*-关健字 CACHE n息内存里皱存n个序列，出馆叫退财会丢失 创建从1开始，不缓存的EMP_EMPN0序列aCREATE SEQUENCE EMP_EMPNO START WITH 1 NOCACHE;修欧二ALTER SEQUENCE name INCREMENT BY n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUECYCLE | NOCYCLE CACHE n | NOCACHE;起始值不能欧变，如泉要欧变序列的起始值，丸则除,再新建。 刪除幕DROFBEQ嘲CE加阿DROP SEQUENCE EMP EMPNO;序列号使用N EXTV AL下一个序列号值CURRVAL省首序列号值如系EMP_EMPNO是一个序列号，可以用下面SQL取序列值 去序列的去if值SELECT EMP_EMPNO.CURRVAL FROM DUAL;“去序列的下一个值SELECT EMP_EMPNO.NEXTVAL FROM DUAL;使用序列桶入數itINSERT INTO EMP(EMPNO, ENAME,.) VALUES(EMP_EMPNO.NEXTVAL, JONE,.);- 不能用序列号的nextval和currval的地方视图圭询.distinct圭询、有group by,having,order by的圭询、 有子圭询的姿询、裹里的缺击值注释/*注释内家*/注年内彖-SELECT *冷：DELETE-jFROM EMPZZZZWHERE DEPTNO = 20；提示：刪除救据前素好丸圭询一下 注释掉，增加DELETE。可以丸頁SELECTS然后把SELECT4豪鼻不岀瞬一ILVHHW常用函数w v dtil数学因数ABS取绝对值 POWER泵方 LN10取罩 SQRT平方根 EXPe的n次采方 LOG(m,n)m为底放n取罩 救学运算西數:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TANTANH_-_厂CEIL大于或警于取整散 FLOOR d、于或等于取整數 MOD取余裁ROUND(n,m)挨m飾住救取b舍五入值如系round(可朗)：中年12以后 将憂明夭的a期.round(sysdate,Y)是年的第一夭 TRUNC(n,m)按m的住数取甫面的数值如系trunc(可期)，确不的是去掉对 间常用函数 1P季符因数 一 、 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“j、与0冷符c从后面m处开始取n住学存 TRANSLATED,们， 字符C按f 1列口的规则转换成新的学符串 INITCAP学符督孕步大筠,其它孕符小写二霽* LOWER 学符全部小筠丄二 _一 UPPER孝符全部大写 LTRIM(c1 ,c2) #掉学符c1左边岀现的学符C2 RTRIM(c1,c2)去莽字符c1右边出现的学符c2L TRIM(c1,c2)去掉字符c1左右两边的睾符c21 LPAD(c15n,c2)孕符c1按指走的住Itn星示，不足的住数用c2|学符丰椁换左边的空住 RPAD(c1,n,c2)学符c1挨指走的伐数n显示，不足的住數用c2*学符串眷换右边的空住. 提示:占瘵李需遽主前J&空格可以及用replace厶不44加，1一A不常用函数岩！2就-一一ADD_MONTHS(d,n)可期值加n 月LAST_DAY(d) 区回当月的最后一夭的可期 M0NTHS_BETWEEN(d1 ,d2)两个目期值间的月份 NEXT_DAY(d) 追回目期值下一夭的可期 SYSDATE 当甫的糸统肘间DUAL是SYS用户下一个空表它只有一个学段dummy 提示：dual可以理解为遂表，多农查询糸统由數逅回值时使用， 以满足SELECT语法规则，如select sysdate from dual; select 3 + 4 from dual; 一一A不常用函数转换因数 TO_CHAR(date；可期显示格或/) TO_CHAR(number)用于显示蜕掖表的格貞对齐 TO_DATE(char；目期显示格対) TO_LOB把long学段轉换成lob学段 TO_NUMBER(char)用于计莽或*咚轶大小目朗星示格式年：YYYY, YEAR, YYt：Q:月：MM, MONTH, MONq : DD5 DAY, DY对：HH24, HH12, HH（12小时） 分：Ml一*A不OR常用函数逻辑比较函数 NVL(EXPR1, EXPR2)*EXPR1殆交用EXPR2卷代 DECODE(EXPR , V1 , R1 , V2 , R2) 6 THEN 下半年 ELSE 上半年END) AS 巒，FROM SCOTT.EMP；-TO_CHAR(HIREDATE, MM)取财间月份HRA 不