oracle学习.txt

关于oracle的详细资料!! 一定对你学习oracle有用的

SQL*Plus支持的SQL语言：
1. SQL语言（标准的SQL语言）
2. SQL*Plus语言（非标准）：报表或接口
3. PL/SQL 语言（非标准）：程序开发（例如存储过程）

二、SQL语言特点：
1. 非过程化语言
2. 功能强
3. 提供视图功能
4. 两种使用方式
(1)交互式
(2)程序式: SQL> @filename.sql

5. 提供数据控制

三、SQL语言分类：
1. 数据查询语言(QL): Query Language
2. 数据操纵语言(DML): 包括数据的插入、更新和删除，Data Manipulation Language
3. 数据定义语言(DDL Data Definition Language): 建表、建视图、建存储过程等
4. 数据控制语言(DCL Data Control Language): 事务控制、包括权限等

四、SQL关键字（命令）
SQL关键字（以下命令为SQL命令，以分号结束）
1. Alter：修改表结构
2. Audit: 审计，　NoAudit(取消审计)
3. Commit:数据提交，相反的命令：Rollback(回退)
4. Comment:将oracle的注释写入数据字典
5. Create: 建表、数组、索引、视图等，　相反的命令：Drop
6. delete: 删除表中的数据，　Drop是把表中的数据包括结构全部删除
7. Grant:授权，　Revoke:收权(权限回收)
8. Insert:在表中插入新行
9. Lock: 将表强行锁住
10. Rename:修改表名
11. Select: 数据查询
12. Update:数据更新，修改某一个列
13. Validate:校验，对数据进行校验。

五、SQL命令的输入：
在SQL>提示符后输入命令，可以输入多行，以分号结束

六、SQL*Plus 关键字：以回车结束
1. @: 执行外部命令，格式：@路径\文件名
2. #: 注释
3. /: 执行上一条命令
4. Accept:接收键盘输入的命令
5. Append（简化为a): 在行尾增加字符串
/* 例
SQL> select * from dep;
select * from dep
              *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在


SQL> a t
  1* select * from dept
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING
*/

6. Break: 分组，　Syntax: break on 分组列 skip n　//n为每两组之间间隔的空行
/* 例：
SQL> break on job
SQL> select * from emp order by job;  // 此处务必按分组列排序

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 13-6月 -87       3000                    20
      7902 FORD                       7566 03-12月-81       3000                    20
      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7876 ADAMS                      7788 13-6月 -87       1100                    20
      7934 MILLER                     7782 23-1月 -82       1300                    10
      7900 JAMES                      7698 03-12月-81        950                    30
      7566 JONES      MANAGER         7839 02-4月 -81       2975                    20
      7782 CLARK                      7839 09-6月 -81       2450                    10
      7698 BLAKE                      7839 01-5月 -81       2850                    30
      7839 KING       PRESIDENT            17-11月-81       5000                    10
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-9月 -81       1250       1400         30
      7844 TURNER                     7698 08-9月 -81       1500          0         30
      7521 WARD                       7698 22-2月 -81       1250        500         30

已选择14行。

SQL> break on job skip 1
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 13-6月 -87       3000                    20
      7902 FORD                       7566 03-12月-81       3000                    20

      7369 SMITH      CLERK           7902 17-12月-80        800                    20
      7876 ADAMS                      7788 13-6月 -87       1100                    20
      7934 MILLER                     7782 23-1月 -82       1300                    10
      7900 JAMES                      7698 03-12月-81        950                    30

      7566 JONES      MANAGER         7839 02-4月 -81       2975                    20
      7782 CLARK                      7839 09-6月 -81       2450                    10
      7698 BLAKE                      7839 01-5月 -81       2850                    30

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

      7839 KING       PRESIDENT            17-11月-81       5000                    10

      7499 ALLEN      SALESMAN        7698 20-2月 -81       1600        300         30
      7654 MARTIN                     7698 28-9月 -81       1250       1400         30
      7844 TURNER                     7698 08-9月 -81       1500          0         30
      7521 WARD                       7698 22-2月 -81       1250        500         30


已选择14行。
*/
7. Btitle, Ttitle:设置表尾、表头
SQL> Ttitle '表头'
SQL> Btitle '表尾'
SQL> Ttitle off //失效

8. Change: 写错了改正, Syntax: C/old/new
/* 例：
SQL> select * from detp;
select * from detp
              *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在


SQL> c/tp/pt
  1* select * from dept
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING
*/

9. Clear: 清除
SQL> clear break 		// 清除前面的break设置
SQL> clear buff		// 清除SQL*Plus缓冲区中的SQL语句

10. Column: 列定义，用于定义列的显示格式
语法：SQL> column 列名 format 格式
例：SQL> Column sal format $99.999999

11. computer: 统计计算

12. connect: 从一个用户退出，直接进入另一个用户

13. disconnect: 中断当前用户的连接

14. copy: 远程复制（用的较少，一般数据链路)

15. define:定义

16. undefine:取消定义

17. del: 删除当前行

18. describe（简写desc): 显示表结构
例：SQL> desc emp

19. document: 文档注释

20. edit(简化ed):调入外部编辑器编辑缓冲区中的SQL语句，保存后，可以直接通过“/”运行编辑过的SQL语句

21. get: 将外部文件调入不执行，只供显示
例：SQL> get c:\sqlplus.ora

22. host: 执行操作系统命令
例：SQL> host dir

23. input(简化为I): 行插入，在当前行的后面插入。

24. List(简化为L): 列示

25. Pause: 设置屏幕暂停
SQL> set pause on			//暂停
SQL> set pause off			//不暂停
SQL> set  'more' pause on	[???]

26. Quit: 退出

27. Remark: 注释

28. Run(R): 类似“/”

29. Save: 将已经运行过的SQL命令保存到磁盘
例：SQL> save c:\run.sql 	replace		//覆盖保存
						append		//追加

30. set: 设置

31. show: 显示环境变量的值

32. Spool: 显示跟踪
例：SQL> spool c:\文件名
SQL> spool off		//停止记录

33. Start: 类似@，执行外部的SQL文件

33. Timing: 服务器端某一命令的执行时间跟踪

七、数据字典：描述系统信息的表、视图、同义词等，由系统自动维护。
例：SQL>select * from tabs;

1. 显示当前所登录的用户：select * from all_users;

2. user_xxx:描述用户创建的对象，如：user_tables，user_views，user_indexes

3. dba_XXX:数据库管理员(sys, system)专用数据字典

4. all_XXX: 描述用户创建的对象，其他用户授权可以存取的对象。
all_objects: 全部的数据字典


[第二章 数据库查询语言(QL)]

一、查询语句基本语法：
1. 查询全表的数据：
SQL> select * from emp;

2. 查询某（几）个列：SQL> select ename, sal from emp;

3. DISTINCT 标识：只显示不相同的列
例：
SQL> select job from emp;		// 显示结果中有相同的记录
SQL> select distinct job from emp;	// 只显示不相同的职业

4. 使用order by将显示结果排序：ASC升序（默认），DESC降序

5. 使用where指出查询条件：
SQL> select * from emp where sal > 2000;

[字符型或日期型用单引号括起来，并区分大小写]


6. 设置日期显示格式：
(1) 确定日历格式：设置参数nls_calendar。

SQL>alter session set nls_calendar='Japanese Imperial';	//设置为日本日历
SQL>alter session set nls_calendar='ROC official';		//设置为台湾日历
SQL>alter session set nls_calendar='Gregorian';			//设置为格林尼治日历，这是我们要设置成的日历。

SQL> select sysdate from dual;		//查询系统时间

(2) 确定日期格式：参数nls_date_format
常用的日期格式：
yyyy.mm.dd  
yyyy-mm-dd 
yyyy/mm/dd  
yyyy"年"mm"月"dd"日" 		//如果要显示汉字，请用双引号括起来
yyyy"年"mm"月"dd"日"dy		// dy表示星期

命令格式：
SQL>alter session set nls_date_format='格式';
[此命令只修改前端的显示，退出后即失效，要想永久有效，要修改注册表，在注册表中HKLM->Software->Oracle->Home0增加字符串关键字nls_date_format，键值为日期格式，此处的格式不需要用单引号括起来。]

例：SQL>alter session set nls_date_format='yyyy"年"mm"月"dd"日"dy';

7. Oracle登录自动执行文件glogin.sql:每次用户登录时自动执行该文件，可以将一些环境变量（如linesize）的设置命令或者其它命令放入该文件中。

glogin.sql文件路径：

Windows下：d:\oracle\ora81\sqlplus\admin\glogin.sql

UNIX: /u01/app/oracle/product/8.1.6/sqlplus/admin/glogin.sql


二、运算符与谓词
1、算术运算符：+, -, *, /

SQL> select sal, sal*12 from emp;			//计算工资及年薪。
SQL> select sal, sal+comm from emp; 		//错误语句，因为comm中存在空值，会使运算结果也会出现空值。
SQL> select sal, sal + NVL(comm,0) from emp; // 正确语句，NVL为空值运算函数，当comm为空值时，该函数返回第二个值0。

2、逻辑运算符：NOT, AND, OR，三个运算符的优先级别依次降低。

3、比较运算符：>,  <,  =,  >=,  <=,  !=

4、谓词：
(1) IN(或NOT IN):等于（不等于）列表中的任意值。

SQL> select * from emp where job in ('MANAGER', 'CLERK')

(2) (NOT) BETWEEN AND : 表示从小到大的一个范围。[必须是从小到大]
SQL> select * from emp where sal not between 2000 and 3000;

(3) LIKE:模式匹配
SQL> select * from emp where ename like 'S%';   //寻找ename为S打头的记录
%: 表示任意字符串
_(下划线): 表示一个任意字符

SQL> select * from emp where ename like '李%';   //可以使用中文
SQL> select * from emp where ename like 'S_I%';   //寻找ename为S打头，第三个字母为I的记录
SQL> select object_name from all_objects where object_name like 'DBA%';	//搜索数据字典中与dba有关系的对象

(4) <IS> (NOT) NULL(空值): 

SQL> select * from emp where comm is NULL;		//查询哪些人没有资金。

5. 伪列：

(1) rowid: 唯一行标识，行被删除之后，rowid不变。
(2) rownum: 行号，一行被删除之后，后面的行号会随之改变

SQL> select empno, ename, rowid, rownum from emp;


[查询SQL语句的相关数据字典]

（1）SQL> select sql_text from v$sqlarea;		//查询以前使用过的SQL语句

（2）查询当前连接用户的SQL语句：
SQL> select user_name,sql_text from v$open_cursor;	//需要用sys或system用户执行	

三、列名别名
SQL> select ename, sal as Salary from emp;
SQL> select ename  职工姓名, sal 工资 from emp;

* # / \ select 都不允许做别名，如果一定要用，用双引号括起来。


[第三章　数据操纵语言(DML)]

一、数据插入：

1. 对于表中全部列插入
语法：SQL> insert into　表名 values(值表达式);

例：SQL> Insert into dept values(51, '软件开发部', '北京');

[*] 用desc显示表结构、数据类型、顺序   SQL> desc dept    //注意，不要将linesize设置的太大，否则看不到表结构，设置成100即可。
[*] 所插数据必须与目标列一致。
[*] 字符与日期数据使用单引号



2. 对于表中部分列插入
语法：SQL> insert into　表名（列名1，列名2...)  values(值表达式);
[*]对于表中的非空列必须插入数据

SQL> insert into emp(empno, ename, job, hiredate,deptno) values(1234, '李大力', '工程师', sysdate - 30, 10);

/* sys 或 system 用户访问其它用户的表的方法

SQL> connect system/ab@ora44
已连接。
SQL> select * from user01.emp						*/

/******************** commit　及 Rollback用法:下例是Rollback的一个例子。
SQL> delete dept where deptno = 51;

已删除 1 行。

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING

SQL> rollback;

重算已完成。

SQL> select * from dept;

    DEPTNO DNAME          LOC