oracle 9i 基础

1、SELECT *, column alias,.FROMtable; DEPTNO DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL SELECT * 2 FROM dept; DEPTNO LOC- - 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTONSQL SELECT deptno, loc 2 FROM dept;运算运算+-* / 描述描述加法加法减法减法乘法乘法除法除法 SQL SELECT ename

2、, sal, sal+300 2 FROMemp;ENAME SAL SAL+300- - -KING 5000 5300BLAKE 2850 3150CLARK 2450 2750JONES 2975 3275MARTIN 1250 1550ALLEN 1600 1900.14 rows selected. SQL SELECT ename, sal, 12*sal+100 2 FROM emp;ENAME SAL 12*SAL+100- - -KING 5000 60100BLAKE 2850 34300CLARK 2450 29500JONES 2975 35800MARTIN 1250

3、 15100ALLEN 1600 19300.14 rows selected. SQL SELECT ename, sal, 12*(sal+100) 2 FROM emp;ENAME SAL 12*(SAL+100)- - -KING 5000 61200BLAKE 2850 35400CLARK 2450 30600JONES 2975 36900MARTIN 1250 16200.14 rows selected. SQL SELECT ename, job, comm 2 FROMemp;ENAME JOB COMM- - -KING PRESIDENTBLAKE MANAGER.T

4、URNER SALESMAN 0.14 rows selected. SQL select ename NAME, 12*sal+comm 2 from emp 3 WHERE ename=KING;NAME 12*SAL+COMM - -KINGSQL SELECTename|job AS Employees 2 FROM emp;Employees-KINGPRESIDENTBLAKEMANAGERCLARKMANAGERJONESMANAGERMARTINSALESMANALLENSALESMAN.14 rows selected.Employee Details-KING is a P

5、RESIDENTBLAKE is a MANAGERCLARK is a MANAGERJONES is a MANAGERMARTIN is a SALESMAN.14 rows selected.SQL SELECT ename | |is a| |job 2 AS Employee Details 3 FROM emp; SQL SELECT deptno 2 FROM emp; DEPTNO- 10 30 10 20.14 rows selected. SQL SELECT DISTINCT deptno 2 FROM emp; DEPTNO- 10 20 30 注：DISTINCT操

6、作会引起排序，通过排序去掉重操作会引起排序，通过排序去掉重复记录！复记录！DESCRIBE tablenameSQL DESCRIBE deptName Null? Type- - -DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13) EMPNO ENAME JOB . DEPTNO 7839 KINGPRESIDENT 10 7698 BLAKEMANAGER 30 7782 CLARKMANAGER 10 7566 JONESMANAGER 20 . EMPNO ENAME JOB . DEPTNO 7839 KINGPR

7、ESIDENT 10 7782 CLARKMANAGER 10 7934 MILLER CLERK 10SELECT * FROM emp WHERE deptno= 10;SELECTDISTINCT *, column alias, .FROM tableWHEREcondition(s);SQL SELECT ename, job, deptno 2 FROM emp 3 WHERE job=CLERK;ENAME JOB DEPTNO- - -JAMES CLERK 30SMITH CLERK 20ADAMS CLERK 20MILLER CLERK 10运算符运算符= = =含义含义

8、等于等于大于大于 大于等于大于等于 小于小于 小于等于小于等于不等于不等于SQL SELECT ename, sal, comm 2 FROM emp 3 WHERE sal SELECTename, sal 2 FROM emp 3 WHEREsal BETWEEN 1000 AND 1500;LowerlimitHigherlimitSQL SELECTempno, ename, sal, mgr 2 FROM emp 3 WHEREmgr IN (7902, 7566, 7788); EMPNO ENAME SAL MGR- - - - 7902 FORD 3000 7566 7369

9、SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788 使用使用LIKE运算符执行通配查询运算符执行通配查询 查询条件可包含文字字符或数字查询条件可包含文字字符或数字(%) 可表示零或多个字符可表示零或多个字符( _ ) 可表示一个字符可表示一个字符SQL SELECTename 2 FROM emp 3 WHEREename LIKE S%;SQL SELECTename 2 FROMemp 3 WHEREename LIKE _A%;ENAME- JAMES WARD运算符运算符ANDORNOT含义含义如果组合的条件都是如果组合的条

10、件都是TRUE,返回返回TRUE如果组合的条件如果组合的条件 之一是之一是TRUE,返回返回TRUE如果下面的条件是如果下面的条件是FALSE,返回返回TRUESQL SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal=1100 4 AND job=CLERK; EMPNO ENAME JOB SAL- - - - 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300SQL SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal=1100 4 OR job

11、=CLERK; EMPNO ENAME JOB SAL- - - - 7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250.14 rows selected.SQL SELECT ename, job 2 FROM emp 3 WHERE job NOT IN (CLERK,MANAGER,ANALYST);ENAME JOB- -KING PRESIDENTMARTIN SALESMANALLEN SA

12、LESMANTURNER SALESMANWARD SALESMAN次序次序 运算符运算符 1所有的比较运算所有的比较运算2NOT3AND4ORENAME JOB SAL- - -KING PRESIDENT 5000MARTIN SALESMAN 1250ALLEN SALESMAN 1600TURNER SALESMAN 1500WARD SALESMAN 1250SQL SELECT ename, job, sal 2 FROM emp 3 WHERE job=SALESMAN 4 OR job=PRESIDENT 5 AND sal1500;ENAME JOB SAL- - -KING

13、 PRESIDENT 5000ALLEN SALESMAN 1600SQL SELECT ename, job, sal 2 FROM emp 3 WHERE (job=SALESMAN 4 OR job=PRESIDENT) 5 AND sal1500;SQL SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate;ENAME JOB DEPTNO HIREDATE- - - -SMITH CLERK 20 17-DEC-80ALLEN SALESMAN 30 20-FEB-81.14 rows selected.

14、SQL SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate DESC;ENAME JOB DEPTNO HIREDATE- - - -ADAMS CLERK 20 12-JAN-83SCOTT ANALYST 20 09-DEC-82MILLER CLERK 10 23-JAN-82JAMES CLERK 30 03-DEC-81FORD ANALYST 20 03-DEC-81KING PRESIDENT 10 17-NOV-81MARTIN SALESMAN 30 28-SEP-81.14 rows sele

15、cted. 你可使用不在你可使用不在SELECT序列上的列来排序序列上的列来排序SQL SELECTename, deptno, sal 2 FROM emp 3 ORDER BY deptno, sal DESC;ENAME DEPTNO SAL- - -KING 10 5000CLARK 10 2450MILLER 10 1300FORD 20 3000.14 rows selected.- - -DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON DE

16、PTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON 50DEVELOPMENTDETROITINSERT INTOtable (column , column.)VALUES(value , value.);SQL INSERT INTOdept (deptno, dname, loc) 2 VALUES(50, DEVELOPMENT, DETROIT);UPDATEtableSETcolumn = value , column = valueWH

17、ERE condition;SQL UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782;SQL UPDATE employee 2 SET deptno = 20;DELETE FROM tableWHERE condition;SQL DELETE FROMdepartment 2 WHERE dname = DEVELOPMENT; SQL DELETE FROMdepartment;SQL UPDATEemp 2 SET deptno = 10 3 WHEREempno = 7782; 提交数据提交数据.SQL COMMIT;SQL DEL

18、ETE FROMemployee;SQL ROLLBACK;DatabaseInstanceSGARedo logbufferData buffercacheShared poolData dict.cacheLibrarycache PMON DBW0SMON LGWRCKPTOthersUserprocessServerprocessPGAControl filesData files Redo logfilesArchived log filesParameterfilePasswordfile后台进程结构后台进程结构内存结构内存结构InstanceSGARedo LogBufferSh

19、ared PoolData DictionaryCacheLibraryCacheDBWRSMONPMONCKPTLGWROthersDatabaseBuffer CacheJava PoolLarge PoolPasswordfileParameterfileArchived Log filesControl filesDatafiles Redo Log filesOracle Database 日志组一日志组一 日志组二日志组二 LGWR 假定数据库只包含两个日志组假定数据库只包含两个日志组 LGWR 1.新内容覆盖旧内容新内容覆盖旧内容2.只能在关闭数据库时进行备份只能在关闭数据库时进

20、行备份3.只能恢复到完全备份点只能恢复到完全备份点4.恢复时必须转储所有数据文件、控制文件和联机重做日志恢复时必须转储所有数据文件、控制文件和联机重做日志 101 ARCH ARCH ARCH LGWR 日志组一日志组一 LGWR LGWR LGWR 日志组二日志组二 日志组一日志组一 日志组二日志组二 102 101 103 102 101 1.只有在归档后新内容才能覆盖旧内容只有在归档后新内容才能覆盖旧内容2.允许在数据库打开时备份允许在数据库打开时备份3.可以完全恢复数据库可以完全恢复数据库SQL CONNECT / AS SYSDBASQL STARTUP实例实例SGA共享池共享池库缓

21、存库缓存重做日志重做日志缓冲区缓冲区PMONDBWRSMONLGWRCKPTARCH数据库数据库缓存缓存数据字典数据字典缓存缓存System表空间表空间Data 表空间表空间TableIndexIndexTableIndexTableIndexTableTableClusterTableIndexIndexIndexTableTableTableIndexTableIndexTableIndexTableIndexTableIndexIndexTableSystem表空间表空间Data 表空间表空间TableIndexIndexTableIndexTableIndexTableTableClu

22、sterTableIndexIndexIndexTableTableTableIndexTableIndexTableIndexTableIndexTableIndexIndexTableData FileData FileData FileDatabaseTablespaceData filesCREATE TABLESPACE userdataDATAFILE /u01/oradata/userdata01.dbf SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;CREATE TABLESPACE userdataDATAFILE /

23、u01/oradata/userdata01.dbf SIZE 500M EXTENT MANAGEMENT DICTIONARYDEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);CREATE UNDO TABLESPACE undo1DATAFILE /u01/oradata/undo01.dbf SIZE 40M;CREATE TEMPORARY TABLESPACE tempTEMPFILE /u01/oradata/temp01.dbf SIZE 20MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;1

24、listener.oraListenersqlplus user/pwDB1User234ServerObjectDescriptionTableBasic unit of storage; composed of rows and columnsView Logically represents subsets of data from one or more tablesSequence Generates primary key valuesIndexImproves the performance of some queriesSynonym Gives alternative nam

25、es to objectsObjectDescriptionTableBasic unit of storage; composed of rows and columnsView Logically represents subsets of data from one or more tablesSequence Generates primary key valuesIndexImproves the performance of some queriesSynonym Gives alternative names to objectsCREATE TABLE schema.table

26、 (column datatype DEFAULT expr;.hiredate DATE DEFAULT SYSDATE, 合法的值是字符合法的值是字符,表达式表达式.或或SQL函数函数 非法值为其他的列名非法值为其他的列名. 缺省的数据类型必须匹配列的数据类型缺省的数据类型必须匹配列的数据类型. SQL CREATE TABLE dept 2(deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13); 确认创建的表确认创建的表 SQL DESCRIBE dept Name Null? Type - - - DEPTNO NOT N

27、ULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) 查询此用户拥有哪些类型的对象查询此用户拥有哪些类型的对象 查询此用户拥有的表查询此用户拥有的表,视图视图,同义词同义词,序列号序列号SQL SELECT* 2FROMuser_tables;SQL SELECTDISTINCT object_type 2FROM user_objects;SQL SELECT* 2FROMuser_catalog;数据类型数据类型说明说明VARCHAR2(size)可变长字符串可变长字符串CHAR(size) 定长字符串定长字符串NUMBER(p,s) 数字值数

28、字值DATE 日期值日期值LONG 变长度的字符串，最大字节数可达变长度的字符串，最大字节数可达2GB CLOB单位字符串数据最大字节数单位字符串数据最大字节数4GRAW and LONG RAW 存储二进制数据的变长度字符串存储二进制数据的变长度字符串BLOB二进制数据，最大字节数二进制数据，最大字节数4GBFILE二进制数据外部存储，最大字节数二进制数据外部存储，最大字节数4GALTER TABLE tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datat

29、ype DEFAULT expr , column datatype.);SQL DROP TABLE dept30;SQL RENAME dept TO department;SQL TRUNCATE TABLE department;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7839 KING PRESIDENT 17-NOV-81 5000 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10

30、 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7902 FORD ANALYST 7566 03-DEC-

31、81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7839 KING PRESIDENT 17-NOV-81 5000 107782 CLARK MANAGER 7839 09-JUN-8

32、1 1500 300 107934 MILLER CLERK 7782 23-JAN-82 1300 107566 JONES MANAGER 7839 02-APR-81 2975 207788 SCOTT ANALYST 7566 09-DEC-82 3000 207876 ADAMS CLERK 7788 12-JAN-83 1100 207369 SMITH CLERK 7902 17-DEC-80 800 207902 FORD ANALYST 7566 03-DEC-81 3000 207698 BLAKE MANAGER 7839 01-MAY-81 2850 307654 MA

33、RTIN SALESMAN 7698 28-SEP-81 1250 1400 307499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307900 JAMES CLERK 7698 03-DEC-81 950 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB - - - 7839 KING PRESIDENT 7782 CLARK MANAGER 7934 MILLER CLERKCREATE

34、 OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.)AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY 使用使用 SQL*Plus DESCRIBE 命令描述视图结命令描述视图结构构.SQL DESCRIBE empvu10SQL CREATE VIEW empvu10 2 AS SELECTempno, ename, job 4 FROMemp 5 WHEREdeptno = 10;SQL CREATE VIEW salvu30 2 AS SELECTempno E

35、MPLOYEE_NUMBER, ename NAME, 3sal SALARY 4 FROMemp 5 WHEREdeptno = 30;EMPLOYEE_NUMBER NAME SALARY- - - 7698 BLAKE 2850 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950 7521 WARD 12506 rows selected.SQL SELECT * 2 FROMsalvu30; SELECTempno, ename, jobFROMempWHEREdeptno = 10; SELECT *FRO

36、M empvu10;EMP7839KINGPRESIDENT7782CLARK MANAGER7934MILLER CLERKSQL CREATE OR REPLACE VIEW empvu10 2 (employee_number, employee_name, job_title) 3 AS SELECT empno, ename, job 4 FROMemp 5 WHEREdeptno = 10;Index entry headerKey column lengthKey column valueROWIDRootBranchLeafIndex entryKEY ROWIDID (BLO

37、CK# ROW# FILE#)- -1257 0000000F.0002.00012877 0000000F.0006.00014567 0000000F.0004.00016657 0000000F.0003.00018967 0000000F.0005.00019637 0000000F.0001.00019947 0000000F.0000.0001.ID FIRST_NAME JOB- - -7499 ALLEN SALESMAN7369 SMITH CLERK7521 WARD SALESMAN7566 JONES MANAGER7654 MARTIN SALESMAN7698 BL

38、AKE MANAGER7782 CLARK MANAGER.startROWIDendROWIDbitmapTableIndexBlock 10Block 11Block 12File 3UserprocessServerprocessPGADatabasePasswordfileArchivedlog filesParameterfileData file 3Redo logfile 2Data file 2Control filesRedo logfile 1Data file 1InstanceSGARedo logbufferData buffercacheLarge poolLock

39、sShared poolData dict.cacheShared SQLand PL/SQL PMON DBWnSMON LGWRCKPTARCn运行模式 备份方法 恢复方法 可以恢复的状态 备注 关库，cp所有文件 关库，直接cp回来 备份点 非归档模式 exp imp 备份点 全库备份允许有增量、累计和完全备份3种选择. 日志没用 运行模 式 备 份 方 法 恢 复 方 法 可 以 恢 复 的状 态 备 注 e x p i m p 备 份 点 同 上 关 库 ，c p所 有 文 件 关 库 ， 直 接c p回来；再执行r e c o v e r 故 障 点 R m a n R m a n 故 障 点 归档模 式 A l t e r t