TIPTOP系统Oracle 常用命令及权限表

进入ISQL  sqlplus       1.常规登陆   sqlplus         SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 9月 17 09:57:02 2003        Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

     2.快捷登陆    sqlplus username/passwd       username 登陆用户名       passwd     登陆用户密码     SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 9月 17 09:59:18 2003         Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.         Connected to:         Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production         With the Partitioning, OLAP and Oracle Data Mining options         JServer Release 9.2.0.1.0 - Production 建立/修改Table 建立/更改Table 1.写好create table 的sql再执行

        vi  dpe_file.sch         /*        =========================================================         档案代号:dpe_file         档案名称:体检项目代号资料档        ==========.===================.===========================         */         create table dpe_file            (               dpe01        varchar2(6),            /*体检项目代号            */               dpe02        varchar2(30),          /*说明                            */               dpe03        varchar2(01),          /*No use                         */               dpeacti      varchar2(01),          /*资料有效码                 */               dpeuser     varchar2(10),          /*资料所有者                 */               dpegrup    varchar2(06),          /*资料所有部门              */               dpemodu  varchar2(10),          /*资料修改者                  */               dpedate     date                         /*最近修改日                  */           );           create unique index dpe_01 on dpe_file (dpe01);             建立/更改Table 2.在 unix 环境下 sqlplus ds/ds  < dpe_file.sch  即可create table 了

 </u1/topo/apy/sql> sqlplus ds/ds < dpe_file.sch SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 9月 17 10:37:23 2003 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Connected to:                                                                   Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production                      With the Partitioning, OLAP and Oracle Data Mining options                      JServer Release 9.2.0.1.0 - Production DOC>============================================================    DOC>档案代号:dpe_file                                                          DOC>档案名称:体检项目代号资料档                                                 DOC>==========.===================.==============================    DOC>*/                                                                          Table created.                                                                                 Index created. SQL>

建立/更改Table 建立/更改Table SQL> desc dpe_file;                                                              Name                                              Null?    Type                         ----------------------------------------- -------- ----------------------------  DPE01                                                        VARCHAR2(6)                  DPE02                                                        VARCHAR2(30)                 DPE03                                                        VARCHAR2(1)                  DPEACTI                                                   VARCHAR2(1)                  DPEUSER                                                  VARCHAR2(10)                 DPEGRUP                                                  VARCHAR2(6)                  DPEMODU                                                VARCHAR2(10)                 DPEDATE                                                  DATE                                                                                                        SQL> 建立/修改Table 修改table       1.写成sql 更改            SQL> ed alter_dpe.sch           alter table dpe_file modify (dpe01 varchar2(10)); /*修改dpe01*/                   alter table dpe_file add (dpe00 varchar2(10));      /*增加dep00*/           alter table dpe_file drop (dpe03);                          /*删除dpe03*/            drop index dpe_01;                                                /*删除索引*/           SQL> @alter_dpe.sch            Table altered.            Table altered.                                                                                            Table altered.            Index dropped.     建立/修改Table 2.进入sqlplus 更改                                                                                 SQL> alter table dpe_file modify (dpe01 varchar2(10));                                                                                                          Table altered.                                                                                                                                                  SQL> alter table dpe_file add (dpe00 varchar2(10)); Table altered. SQL> alter table dpe_file drop (dpe03); Table altered. SQL> drop index dpe_01; Index dropped.                                                                                 SQL> 建立/修改Table SQL> desc dpe_file;                                                             Name                                                Null?   Type                         ----------------------------------------- -------- ----------------------------  DPE01                                                         VARCHAR2(10)                 DPE02                                                         VARCHAR2(30)                 DPEACTI                                                    VARCHAR2(1)                  DPEUSER                                                   VARCHAR2(10)                 DPEGRUP                                                   VARCHAR2(6)                  DPEMODU                                                 VARCHAR2(10)                 DPEDATE                                                   DATE                         DPE00                                                         VARCHAR2(10)    

INSERT 指令介绍 INSERT INTO table_name[(column-list)]          VALUES(value-list) 范例: 1. INSERT INTO dpe_file VALUES('A00003','test','','Y','carrier','1400','','03/09/17‘) 2. INSERT INTO cus_file(cus01,cus02)     VALUES(‘C00002’, ‘ test01’);

UPDATE 指令介绍 Syntax     UPDATE table_name          SET col=expr,col=expr         [WHERE clause ]  范列說明:   1.UPDATE dpe_file SET dpe02=‘test02’            WHERE dpe01 LIKE ‘A%1’   2. UPDATE dpe_file SET dpe02='test02',                                        dpeacti='N‘           WHERE dpe01 like 'A%1'    3. UPDATE dpe_file SET dpeuser='michael‘,                                       dpegrup='2100‘          WHERE (dpe01 = 'A00002' or dpe01 like 'C_000_');

DELETE 指令介绍 Syntax:     DELETE FROM table_name            [WHERE clause] 范例说明:  1.DELETE FROM dpe_file     [注意]:没有where 条件会将所有资料删除                 且无法将资料还原 2.DELETE FROM dpe_file            WHERE dpe01 LIKE ‘C%’ SELECT指令介绍 Syntax: SELECT   column,group_function(column)          FROM    table_name          [WHERE condition]          [GROUP BY group_by_expression]          [HAVING group_condition]          [ORDER BY column]              范例说明:   1.SELECT  *   FROM cus_file order by cus01 ASC   2.SELECT cus01,cus02 FROM cus_file order by cus01 DESC,cus02   3.SELECT last_name,salary,12*salary+100 FROM employee   4.SELECT last_name As name,commission comm FROM employee   5.SELECT last_name||job_id as “Employee Info”  FROM employee   6.SELECT last_name || ‘ is a ’||job_id as “Employee Detail” FROM employee   7.SELECT distinct dpe02  FROM dpe_file   8. SELECT last_name,age FROM employee WHERE age<=45            SELECT指令介绍 范例说明:    9.SELECT  * FROM  employee WHERE age between 20 and 65       SELECT *  FROM dpe_file        WHERE dpe02 is null and (dpeacti=‘Y’ or dpeuser like ‘carr%’)  10.SELECT employee_id,UPPER(last_name)  FROM employee        WHERE INITCAP(last_name) = ‘Higgins’       SELECT employee_id,CONCAT(first_name,last_name)  NAME ,                       job_id,LENGTH(last_name), INSTR(last-name,’a’)  “Contain ‘a’?”             FROM employee WHERE SUBSTR(job_id,4)=“REP”       SELECT last_name,salary,MOD(salary,5000) FROM employee        WHERE job_id=‘SA_REP’       Join table 範例:   11.SELECT oea01,oeb02,oeb04,oeb12  FROM oea_file,oeb_file          WHERE oea01=oeb01   12.SELECT e.employee_id,e.last_name,e.department_id,d.department_id,                       d.location_id             FROM employee e ,department d         WHERE e.department_id=d.department_id          SELECT指令介紹 范例說明:   13. SELECT  e.last_name,e.department_id,d.department_name                 FROM employee e ,department d               WHERE e.department_id(+)=d.department_id              SELECT  e.last_name,e.department_id,d.department_name                 FROM employee e ,department d               WHERE e.department_id=d.department_id(+)         14.INSERT INTO cus1_file  SELECT *   FROM cus_file         15.INSERT INTO cua_file(cua01,cua02)                   SELECT cus01,cus02 FROM cus_file                    WHERE cus01 MATCHES ‘C*’         16.SELECT COUNT(*) FROM oea_file               WHERE oea02 BETWEEN ’01/01/01’ AND ’01/12/31’         17.SELECT COUNT(DISTINCT oea03) FROM oea_file               WHERE oea02 BETWEEN ’01/01/01’ AND ’01/12/31’         18.SELECT MAX(oea02),MIN(oea02) FROM oea_file               WHERE oea02 BETWEEN ’01/01/01’ AND ’01/12/31’   

SELECT指令介紹 范例說明:       19. SELECT AVG(oeb12)  FROM  oea_file,oeb_file             WHERE  oea02 BETWEEN  '010101' AND '011231'                   AND oea01=oeb01 AND  oeb04='11-03-SDD16‘ and oeb12<>0 

     20.SELECT  * FROM oea_file            WHERE oea02 = (SELECT MAX(oea02) FROM oea_file                                           WHERE oea02 > = ’01/01/01’)                                              21.SELECT department_id,AVG(salary) FROM employee                GROUP BY department_id

      22.SELECT department_id,MAX(salary) FROM employee                GROUP BY department_id                HAVING MAX(salary)>10000

           SELECT指令介紹----where 比较符号     ‘=’ ‘ >’  ‘ >= ’  ‘< ’  ‘<=’   ‘<>’ 其它比较符号    BETWEEN .. AND ..      WHERE age between 20 and 30    IN (..)     WHERE age in (20,21,22,23,24,25,26,27,28,29,30)    LIKE      WHERE dpe01 like ‘A_0%’    IS NULL   WHERE age is null SELECT指令介紹----where 逻辑符号     ‘AND’ ‘OR’ ‘NOT’     where dpe01=‘A00001’ AND dpe02=‘test01’       where dpe01=‘A00001’ OR dpe01=‘C00001’       where age not in (20,21,22,23,24,25,26,27,28,29,30)

Group Function AVG COUNT MAX MIN STDDEV SUM

Oracle Function---1 FUNCTION---2 Number Function    ROUND             round(45.926,2)   45.93    TRUNC             trunc(45.926,2)    45.92     MOD             mod(1600,300)     100 LOAD/UNLOAD指令介绍 因为oracle没有提供类似INFORMIX的load,unload语句，所以在tiptop环境下有一些工具 1.使用load shell,load shell会去呼叫$TOP/ora/load.42m 程序 使用方式 Usage: load database tablename txtfile Ex : load ds ze_file ze_file.txt 2.使用loaddb shell,loaddb shell会去呼叫external tools SQL*Loader 使用方式 Usage: /u1/topo/ora/bin/loaddb dbname [tablename] [txtfile] Ex1 : /u1/topo/ora/bin/loaddb ds1 Ex2 : /u1/topo/ora/bin/loaddb ds1 ima_file [ima_file.txt] Ex3 : /u1/topo/ora/bin/loaddb ds1 i%_file 3.直接使用Oracle提供的SQL*Loader,但必须自己编辑control file, 用法 sqlldr ds/ds control=azb_file.ctl log=azb_file.log LOAD/UNLOAD指令介绍

control file格式如下 load data infile '/u1/tiptopo/azb_file.txt ' append into table azb_file fields terminated by '^A' ( azb01, azb02, azb06, azbacti, azbuser, azbgrup, azbmodu, azbdate ) 4.另外可以在BDL程序中使用channel的方式来产生SQL*Loader 所需用的control file, 再透过RUN command的作法来激活SQL*Loader,channel的写法可参考BDL文件

Sqlplus 一些命令介绍 Help index  查询相关命令 @  运行.sql的文件 Ed  类似于vi Get 将一句sql语句放入sql 缓冲器 R –run / 重复上一个在memory中的command Desc 显示表结构 Spool 记录执行过程 Start  get+r Save 将memory中的命令保存在一个文件中 Set 设置一些oracle设定

Sqlplus 一些命令介绍 示范 help accept   找寻accept的帮助信息 @c_dpe.sql   执行c_dpe.sql中的内容 ed c_dpe.sql  编辑一个文件c_dpe.sql get c_dpe.sql 将该文件的内容放入memoery 中 r                    执行memory中的命令 /                    同r desc dpe_file  查看dpe_file的表结构 spool file.lst   将在这期间执行的命令及结果放入file.lst 文件             sqlplus 命令             spool off Start c_dpe.sql  执行start后面文件中的内容 save t1.sql       set wrap on     设置可以折行显示 Set heading off