oracle学习.txt

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

---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING
        51 软件开发部     北京

已选择6行。
*************************************************/

3. 使用参数(变量)，临时输入值

SQL> insert into 表名(列名1,列名2, ...) values(&x1, &x2, ...);

SQL> insert dept(deptno, dname, loc) values(&x1, '&x2', '&x3')

/* 例：
SQL> insert into dept values(&x1, &x2, &x3)			//&x2, &x3没有用单引号括起来
输入 x1 的值:  52
输入 x2 的值:  'bb'								// 字符型数据输入时需要输入单引号
输入 x3 的值:  'cc'								// 同上
原值    1: insert into dept values(&x1, &x2, &x3)
新值    1: insert into dept values(52, 'bb', 'cc')

已创建 1 行。

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 COMPUTER       BEIJING
        51 软件开发部     北京
        52 bb             cc

已选择7行。

SQL> insert into dept values(&x1, '&x2', '&x3');		// 在命令行中给字符或日期型参数加上单引号，则下面输入时不再需要输入单引号

输入 x1 的值:  53
输入 x2 的值:  dd
输入 x3 的值:  ee
原值    1: insert into dept values(&x1, '&x2', '&x3')
新值    1: insert into dept values(53, 'dd', 'ee')

已创建 1 行。
********************************************************************/

// define x = 7788       
// 也可以这样用：select &x1, &x2 from emp;

4. 使用子查询从另一个表中复制数据
语法：Insert into 表名 select 子句;

SQL> insert to emp(empno, hiredate, deptno) 
		select deptno+7300, sysdate, deptno from dept;

SQL> insert to emp(empno, hiredate, deptno) 
		select deptno+7300, sysdate, deptno from dept@数据库链路名;    //表示dept这个表在网络上的其它服务器中，注意：@之后不是连接串。

SQL> create table pay as select ename, sal from emp;	// 用复制的方法创建表
SQL> insert into pay select * from pay;				//自己复制自己，可以使一个表飞速扩大。


二、数据更新
语法：SQL> Update 表名 set 列名=值表达式  where 条件;

例：
SQL> update emp set sal = sal + 100 where sal < 2000;
SQL>update emp set sal = 5000, job='MANAGER' where ename = 'SMITH';
SQL> update emp set comm = 100 where comm is null;

三、数据删除
语法：	SQL> Delete from 表名 where 条件;      	//删除数据保留结构，可以回退
		SQL>Truncate table 表名; 			//删除数据保留结构，不可以回退，效率高
		SQL>Drop table 表名; 				//删除数据及结构，不可以回退
		
[第四章　数据控制语言(DCL)]

一、事务提交：对于数据的插入、更新、删除，只有提交后，数据才真正改变，在提交之前，只有修改了数据的用户才可以看到数据的改变，而其他用户看不到数据的改变。

1. 显式数据提交语法：SQL> commit;

/* 某一个用户对表进行更新等写操作之后，如果没有commit，那么其它用户不能对表进行更新操作，否则就会死掉。
查锁方法：
SQL> select username, sid, serial# from v$session;

解锁方法：
SQL> alter system kill session 'sid, serial#';

*****************************************************************************************************/

2. 隐式数据提交：
下列命令是隐式提交命令：
Create, Alter, Drop, Connect, Disconnect, Grant, Revoke, Rename, Exit, Quit, Audit, NoAudit

3. 自动数据提交：
SQL> set autocommit on			//打开自动数据提交开关
SQL> set autocommit off			//关闭自动数据提交开关（默认）

二、事务回退
语法：SQL>Rollback;		//使数据库回退到最近一次提交后状态，如果一次也没有提交过，回到最原始状态
SQL>Rollbac;
SQL>Rollba;
SQL>Rollb;
SQL>Roll;
//以上的用法都是正确的。


三、设置保存点：
SQL> SavePoint a;

回退到保存点：
SQL> Rollback to a; 		//此处的Rollback不能再简写。


[第五章　数据定义语言(DDL)]

包括：创建基表、视图、同义词、索引、数据库链路、序列等

一、创建基表
语法：
SQL> Create Table 表名(
	列名1　数据类型,
	列名2　数据类型,
	...);				//最多可以到1000个字段

例：SQL> Create Table product(
		p_name, varchar2(20),
		p_id_number number(7),
		p_date date);
	

1. 数据类型
(1)字符型
char(n): n<=2000，固定长度，如果实际长度不够，前面用空格补齐。
varchar2(n): n<=4000，可变长度，不用空格补齐。
(2) 数字型　number(n):整数，　number(n,d):小数
(3) 日期型
(4) 二进制raw(8i以前的，现在不支持了）
(5) 大字符long：建议不要使用，现在不支持了
(6) blob:存储二进制
(7) CLOB:存储大字符

SQL> Create table employee(
	name varchar2(30),
	salary number(7,2),
	b_date date,
	phote blob,
	resume clob);
	
[*]修改数据库兼容性参数（如果在执行上面的SQL语句时，提示clob字段“默认字符集具有不同的宽度”，则需要修改数据库兼容性。）:
D:\oracle\amdin\db_name\pfile\init.ora
compatible=8.0.5 =改为=> 8.1.0或8.1.5
改完后重新启动数据库使修改生效。
/**********************************************
修改前：
SQL> Create table employee(
  2   name varchar2(30),
  3   salary number(7,2),
  4   b_date date,
  5   phote blob,
  6   resume clob);
 resume clob)
 *
ERROR 位于第 6 行:
ORA-22866: 默认字符集具有不同的宽度

修改后：
SQL> Create table employee(
  2   name varchar2(30),
  3   salary number(7,2),
  4   b_date date,
  5   phote blob,
  6   resume clob);

表已创建。

************************************************/

2. 约束条件：数据完整性约束条件(Data Integrity Constaints)

Oracle 常用约束条件：
(1) 非空约束：NOT NULL
(2) 唯一性约束：UNIQUE
(3) 主键：PRIMARY KEY，主键同时具有上面两个约束条件，一个表中只允许有一个主键。
(4) 外键：FOREIGN KEY，这一列值从其它表中取出，允许重复，但不允许修改。
(5) 检查：CHECK  (e.g. check (sal > 200)
(6) 引用（参考）：REFERENCES，只能用其它表或者本表的某一列列值，不能随便修改。
(7) 缺省值：DEFAULT，如果没有输入，自动使用DEFAULT值。

3. 约束条件的定义方法：
可以定义为列的一部分，也可以定义为表的一部分。
(1)定义为列的一部分：
SQL> Create Table product(
	p_name varchar2(20) unique,
	p_id number(7) primary key,
	p_date date not null);

[*] Check、Default约束条件：
SQL> Create table employee(
	name varchar2(20),
	id number(7) primary key,
	sal number(11) check(sal > 200 and sal < 2000),
	h_date date default sysdate);

一个列具有多个约束条件的写法：
SQL> Create Table product(
	p_name varchar2(20) unique,
	p_id number(7) primary key,
		check(p_id >= 111 and p_id <= 999),
	p_date date not null);

例：
SQL> insert into employee(name, id, sal) values('Smith', 2, 201)；
SQL> select * from employee;

NAME                         ID        SAL H_DATE
-------------------- ---------- ---------- --------------
ChenZheng                     1        500 2002年04月14日
Smith                         2        201 2002年04月19日


(2)定义为表的一部分(不能用在Default 和 Not NUll的定义上)：
SQL> Create Table  Product(
	p_name varchar2(20),
	p_id number(7),
	p_date date not null,
	constraint p_id_pk primary key(p_id),
	constraint p_name_uk unique(p_name));
	
[*] 查询约束条件：
SQL> select * from user_constraints where table_name = 'PRODUCT';

(3) 另一种约束定义方法：
SQL> Create Table  Product(
	p_name varchar2(20),
	p_id number(7) constraint pk_p_id primary key,
	p_date date constraint fk_p_date not null);
	
4. 使用外键创建主从基表
(1) 创建主表(定义主表):
SQL> Create Table product(
	p_name varchar2(20),
	p_id number(7) primary key,
	p_date date not null);
	
(2)创建子表，定义外键
SQL> create table sales_list(
	sales_name varchar2(20),
	sales_id number(7) primary key, 
	p_id number(7), 
	constraint p_id_fk foreign key (p_id) references product(p_id));
	
5. 数据完整性约束条件的修改
(1) 删除约束条件：
A. 删除主键约束：
SQL> Alter table product drop primary key;
SQL> Alter table product drop constraint p_id_pk;

// 以上两种方法等价

B. 删除唯一性约束：
SQL> Alter table product drop unique(p_name);
SQL> Alter table product drop constraint p_id_uk;

C. 删除非空约束
SQL> Alter table product modify(p_date NULL);

D. 删除缺省值：
SQL> Alter table product modify(p_date default null);

(2) 增加约束条件
A. 增加主键约束：
SQL> Alter table product add primary key(p_id);
SQL> Alter table product add constraint p_id_pk primary key (p_id);

B. 增加非空约束
SQL> alter table product modify ( p_date not null);

C. 增加缺省值
SQL> Alter table product modify (p_date default sysdate-1);

[*]查询缺省值：
SQL> select table_name, column_name, data_default from user_tab_columns;

二、修改表结构
1. 在表中增加新列：
SQL> Alter table product add (p_list number(7), p_loc varchar2(20));

2. 删除一个列(只适用于Oracle8i以后的版本）：

SQL> Alter table product drop(p_list, p_loc);		//删除多个列
SQL> Alter table product drop column p_list;			//删除一个列

3. 修改列宽：
SQL> Alter table product modify (p_name varchar2(40));
//增加列宽没有约束，但减小列宽要求列中数据为空。使用此命令也可以修改列的数据类型。

三、视图(View):视图是虚表。
[*]视图不存储数据
[*]数据来源于基表
[*]不是数据的复制
[*]在同一个表上可以创建多个视图

1. 创建视图的语法：
SQL> Create or Replace view 视图名
	as select 语句;
	
例：SQL> create view manager 
		as
		select * from emp where job='MANAGER';		// select * from tab; 检查
		
	SQL> select * from manager;		// 查询视图如果查询表
	
// 第二次创建视图，可以使用or replace参数，不需要再删除而直接覆盖同名视图。	
	SQL> create or replace view manager
  		as
  		select * from emp where job='MANAGER';
  		
[几点说明:]
(1) 在创建视图时，不得使用order by排序。
(2) 在视图中插入数据，则数据被插入到基表中，所以，如果要向视图插入数据，则创建视图时，必须包含表中全部非空列。
(3) 用户视图数据字典：
SQL> select view_name,text from user_views;

2. 视图列别名：

错误语句：
SQL> create view payment as
	select sal, sal*12, nul(comm,0)/sal from emp;  // 错误原因：视图可以视同为表，所以列名也要符合规定，而sal*12则是不符合规矩的列名。

正确语句：
SQL> create view payment(c1, c2, c3) as		// c1, c2, c3即为视图列别名
	select sal, sal*12, nvl(comm,0)/sal from emp;

3. 创建视图时增加约束条件：WITH CHECK OPTION

SQL> Create or replace view deptno20 as 
	select empno, ename, deptno from emp where deptno=20;

SQL> Insert into deptno20 values(1236, '李力', 30);

SQL> select * from deptno20;

// 上面的语句会出现能够通过视图入基表中插入数据，但却不能通过视图看到插入的数据的问题，解决办法：
SQL> Create or replace view deptno20 as 
	select empno, ename, deptno from emp where deptno=20
	WITH CHECK OPTION;

4. 创建Oracle8i的实体化视图(Materialized View):视图不依赖于基表，基表被删除后，视图仍然正常。一般用于两个远程数据库之间的访问，通过数据链路来实现。

(1) 以DBA用户登录，为用户授予创建实体化视图的权限：
SQL> Grant Create Materialized View to 用户名;

(2)以获权用户登录，创建实体化视图：
SQL> Create Materialized View manager as
	select * from emp where job = 'MANAGER';
	
(3) 删除实体化视图：
SQL> Drop Materialized View manager;

删除基表：
SQL>drop table 表名;

删除视图：
SQL> drop view 视图名;


三、创建数据库链路(Database link):
数据库链路：用于数据库之间的远程数据复制。

DB1(UNIX) <------- DB2(NT)

若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

创建数据库链路的步骤：
(1) 创建好连接串。
(2) 创建数据库链路。

1. 创建数据库链路的语法：
SQL> Create database link 数据库链路名
	connect to 用户名 identified by 口令