oracle命令

Oracle8i

desc 察看表结构

插入时日期型用单引号即可？

系统日期:SYSDATE, 可加减如:SYSDATE+1表明天

to_date('2005-06-11', 'yyyy-mm-dd');

insert into emp(empno, hirdate, deptno)

DML
select deptno+7901, sysdate, deptno from dept;

insert into emp(empno, hirdate, deptno)
select deptno+7901, sysdate, deptno from dept@数据库链路(表另外的一个数据库)

insert into dept values(&x1, &x2, &x3);
x1:80
x2:'a'
x3:'b'

或
insert into dept values(&x1, '&x2', '&x3');
x1:80
x2:a
x3:b

delete from table_name where condition

delete from table_name;

truncate table table_name;(保留表结构,数据全部删除,此时rollback不可恢复)

drop table

create table emp1 as select * from emp;(复制表)

 

DCl
对于数据库的插入、删除、和更新，只有提交后数据库才真正改变。提交前只有修改数据的用户才能看到。

行锁
同一用户,比如:soctt
update一行,但是没有提交,这时用同一用户再打开一个session，然后也更新同一行，这时后来用户的更新语句将不会马上被执行，他将一直等到第一个用户commit后才会执行后返回，在第一个用户提交前，后一个用户处于锁定状态，无法进行任何操作，这是因为第一用户update时，已经将update的行加上了行锁，其他用户必须等到第一个用户commit后，行锁解除后，才能继续工作。(死锁?)

隐式提交命令:
create, drop, grant, revoke, connect, disconnect, audit, noaudit, rename, exit, quit, alter.
执行任何一个隐式提交命令,以前的非隐式提交命令命令也会全部提交。
如果没有提交前发生异常，如：网络中断，此时所有未提交的命令全部回退。

一个事物一个回退段。比如：一次删除10行在一个事物中。

SQL>set autocommit on(缺省为off)

server服务：
listener.ora(监听客户端的连接,缺省态是机器名)
tnsnames.ora(用于与别的服务器连接)

Tansaction
Savepoint
SQL>insert into ...
SQL>savepoint b;
SQL>insert into ...
SQL>rollback to b;(b以前的语句回退)

SQL>update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';(重启数据库)

create table 最多可以有1000个列名

(1)字符型:
char(n): n<=2000 (如果长度<n，那么前面加空格补齐，如：/20/20abc)
varchar2(n): n<=4000
(2)数字型:
number(n, d) (浮点型)(n是数字的位数, d是小数位数)
number(n) (整型)
(n<38)
(3)日期型:DATE
(4)RAW:二进制数据
(5)LONG:长字符(一个表中只可有一个)
   long raw:用于存储图像或声音
(6)LOB(一个表中可有多个)
   BLOB
   CLOB
　 NCLOB
   BFILE(使用比较复杂)

/oracle/admin/db_name/pfile/init.ora
compatible=8.1.5
重启数据库(才可用clob)

SQL>shutdown immediate
SQL>startup

product_date date constraints f_nn not null;
约束名将写入数据字典
user_constraints(约束的数据字典)
select * from user_constrains where table_name='PRODUCT';(表名大写)

如果用not null,oracle自动给出约束名称SYS_Cxxxx(四个数字)
SYS_Cxxxx(系统缺省)
f_nn      (用户定义)

添加删除列
alert table table_name add (...);
alert table table_name drop (...);
alert table table_name modify(colum varchar2(100))(加宽列，只有列为空时才可变窄，也可修改字段类型)
alert table table_name modify(colum null)(取消非空)

删除数据后水印还在:high-water
回收未使用空间
alter table table_name DEALLOCATE unused;--回收全部未使用空间
alter table table_name DEALLOCATE unused KEEP 40k;--保留未使用空间为40k(必须小于未使用空间)

数据块在ora8i为8k

view
主要用于简化数据
视图数据字典
select text from user_views where view_name='MANAGER';
1. 创建视图时不得使用order by
2. 向视图中插入数据，则数据被插入表中，所以如果要通过视图插入数据，那么创建视图时必须把表中的非空列都加入。
3. 在创建视图时增加选项，with check option
防止通过视图插入、修改的数据视图看不见。
SQL>create or replace view deptno20
    as
    select empno, ename, job, deptno
    where deptno=20;
SQL>insert into deptno20
    value (1221, 'jack', 'engineer', 30)
SQL>select * from deptno20;--这时通过视图修改数据后，视图却发现不到
SQL>create or replace view deptno20
    as
    select empno, ename, job, deptno
    where deptno=20
    with check option;--必须满足条件的数据才能插入、修改
4. 视图列别名
SQL>create or replace view salary
    as
    select sal, sal*12, nvl(comm, 0)/sal --非法列名
    from emp;
SQL>create or replace view salary(a1, a2, a3)--别名
    as
    select sal, sal*12, nvl(comm, 0)/sal
    from emp;
5. 创建实体化视图(materialized  view)(oracle 8i)
A. 以system登陆数据库,为用户授权:
SQL>grant create materialized view to user_name;
B. 以被授权的用户登录,创建实体化视图:
SQL>create materialized view manager
    as
    select * from emp
    where job='MANAGER';
C. 删除实体化视图:
SQL>drop materialized view view_name;
相当于快照,必须有主键,不允许插入、修改，但是允许更新。原表删除对他没有影响。

6. 表和视图的拷贝
(1)拷贝表结构和数据
SQL>create table employee(id, name, job)--别名
    as
    select empno, ename, job from emp;
(2)拷贝表结构,拷贝时加一个永不成立的条件
SQL>create table employee
    as
    select * from emp
    where 1=2;
(3)修改表名:
SQL>rename old_name to new_name;

7.表和视图的删除
SQL>drop table table_name;--视图数据丢失
SQL>drop view view_name;--表不受影响

创建索引
索引可提高查询速度70%
不加索引会全表逐条匹配，加索引会根据索引直接根据rowid定位
SQL>create index table_name on col_name(列名，查询关键字)
索引的数据字典
SQL>select index_name, table_name, table_owner from user_indexes;

创建序列(sequence)
产生连续自然数
SQL>create sequence sequence_name start with 起始号 increment by 步长 maxvalue 终止号码;
SQL>create sequence id_code
    start with 1000
    increment by 3
    maxvalue 9999;
使用方法:
    id_code.currval--当前值
    id_code.nextval--当前值+步长(使用一次就会增加,包括查询)
SQL>create sequence id_code
    start with 1000
    increment by 3
    maxvalue 9999
    CYCLE--循环,下一次从1开始。默认NOCYCLE
    NOCACHE;--不使用内存。默认CACHE.影响效率
删除序列
SQL>drop sequence sequence_name;
序列数据字典：
SQL>select sequence_name, min_value, max_value, increment_by from user_sequences;

创建数据库链路(database link)
用于oralce分布式环境下，在数据库之间进行数据传输、复制等。
SQL>create database link 链路名--必须与对方的数据库名相同
    connect to 用户名--对方的用户名口令
    identified by 口令
    using 连接串名--tnsnames中的网络连接串
17讲
SQL>create datebase link ora32
    connect to user30
    identified by user30
    using 'ora32';
使用数据库链路
SQL>create table sales
    as
    select * from sales@ora32;--注意:是数据库链路名。另外一个库查询
不允许对远程数据库用ddl语句。
a数据库名如果相同不能建链路。
b链路名必须与远程数据库名相同。

使用触发器自动复制数据：(实时传输，在对方数据库创建触发器)
SQL>create or replace trigger InsertData
    before insert on sales
    for each row--行级
    begin
       insert into sales@ora8i--本地数据库
       values(:new.s1);--新插的一行new
    end;

数据库链路数据字典：user_db_links
触发器数据字典：    user_triggers

 

2005-06-13

第9讲
sql*plus关键字
SQL>@文件名路径(如/uo1/oracle/file_name.txt, C:/file_name.txt)
@执行外部命令
#注释
/再一次运行上一运行的sql命令
accept 接收键盘输入
append 简写为a, 如: SQL>a XXX在行尾增加字符串
break 分组显示
btitle 增加表尾 sql>btitle '文本'
ttitle 增加表头 sql>ttitle '文本'
               sql>ttitle off--(取消)
               sql>btitle off
change 简写为c，用于变量替换 sql>c/old/new(用new替换old)
clear 清除缓冲区命令 sql>clear buff
                   sql>clear break
column 列定义，sql>column 列名 format 格式($99,999.99, 99999.99, 9.99eeee)
compute 统计计算
connect 从一个用户退出进入另外一个用户

sql>connect scott/tigger
sql>connect stud30/stud30@ora30
sql>disconnect
sql>show user
copy 数据远程复制
define undefine 变量定义/取消定义
del 删除当前行
describle 简写desc，显示表结构

sql>set linesize 100
sql>desc table_name
document 文档注释
edit 调用外部编辑器，在unix中调用vi
get 将外部文件调入oracle中但是不执行 sql>get c:/file_name
help 现在已经放在光盘中
host 执行操作系统命令 sql>host dir /p
input 简写为i，在当前行后插入一行
list 简写为l，显示已经运行过的sql命令
newpage 另起新页
pause 设置屏幕暂停 sql>set pause on
                 sql>set pause off
quit 同exit退出
remark 注释同document
run 同/运行上一次运行的sql命令，简写r
save 将sql命令保存到外部磁盘 sql>save c:/file_name
                          sql>save c:/file_name replace --简写rep，代替同名文件
     sql>save c:/file_name append --简写app，追加在同名文件尾部
set 设置 sql>set linesize 1000
        sql>set pagesize 100
 sql>set pause
show 显示当前sql*plus的环境变量名 sql>show all
                               sql>show user
spool 设置屏幕跟踪,将屏幕显示结果保存到一个文件中. sql>spool c:/file_name (*lst)
                                             sql>spool off
                                             sql>spool out --直接在打印机上打印输出
start 同@,执行外部文件
timing 设置某一命令在服务器端的执行时间跟踪. sql>set timing on
常用命令@, /, c, l 必须记住。

 

2005-06-16

数据字典
包括表、视图、同义词
可以查看不能修改由数据库维护
SQL>select * from all_users;
SQL>select * from tab;
数据字典类型：
(1)DBA_xxx 必须以DBA权限登陆,system，如：dba_users;dba_tablespaces;
(2)USER_xxx 记录用户自己创建的实体，如：user_tables;user_indexes;user_views;
(3)ALL_xxx 记录用户自己创建的和其他用户创建授权可以访问的实体，如：all_users;all_tables;
(4)V$xxx 动态数据字典，不同时间可能是不同的，需要以system登陆，如：v$database;v$instance;v$version;v$license;v$sga(ora占用内

存大小)
all_objects 所有数据字典的总目录
SQL>select object_name from all_objects;

日期格式转换方法：
(1)修改会话:
   只是改动显示格式,数据库存储格式无改动
   SQL>alter session set nls_data_format 'yyyy.mm.dd';
   可以使用的日期格式如下：
   'yyyy.mm.dd'
   'yyyy-mm-dd'
   'yyyy/mm/dd'
   'yy.mm.dd'
   'yyyy"年"mm"月"dd"日"'
(2)修改注册表:
   增加关键字:NLS_DATE_FORMAT
   键值:      yyyy"年"mm"月"dd"日"

oralce 登陆自动运行的文件 glogin.sql,可以设置一些环境,如:set linesize;
unix: $ORACLE_HOME/sqlplus/admin/glogin.sql
windows: oralce/ora81/sqlplus/admin/glogin.sql

NVL(c1,c2) 不为空取c1，否则取c2

between ... and ...
not between ... and ...
SQL>select * from emp where sal between 2000 and 3000; --包括2000和3000

ROWID 唯一行标识
一行在数据库中存储的物理位置
ROWNUM 行号，在显示时增加的不重复的数字，不能做条件，是显示时临时加上的

 

2005-06-19

数据完整性(data integerity)是指数据的正确性、有效性。
(1)not null
(2)unique 不能重复，允许空
(3)primary key 主键 a. not null + unique
                            b. 表中只有一个

                            c.自动创建索引
(4)foreign key
(5)check 如：sal > 3000
(6)references (可以参考别的表也可以参考本表的列)
(7)default 如: sal = 0;

create table product(
    product_name char(20) unique,
    product_id number(7) primary key,
    check(product_id > 1001 and product_id < 3001),
     product_date date not null 
);
create table employee(
    name varchar2(20),
    identification number(18) constraint pk_id primary key, --pk_id 是约束名
    hiredate date default sysdate,
    sal number(7, 2) check(sal > 200)
);
create table product(
    name char(20),
    id number(7),
    p_date date,
    constraint uk_name unique(name),
    constraint pk_id primary key(id, p_date) --联合主键
);
删除约束:
alert table product drop primary key;
alert table product drop unique(product_name);
alert table product drop constraint pk_id;
alert table product modify(product_date null);
增加约束
alert table product add primary key(product_id, product_date);
alert table product add constraint pk_id primary key(product_id, product_date);
alert table product add unique(product_name);
alert table product modify(product_date default sysdate + 1)
使约束条件失效
alert table product disable primary key;
alert table product disable unique(product_name);
使约束生效
alert table product enable primary key;
alert table product enable unique(product_name);

主
create table product(
    product_name varchar2(20),
    product_id number(7) primary key,
    product_date date not null
);
从
create table sales_order(
    sales_name varchar2(20),
    sales_loc char(20),
    product_id number(7),
    constraint fk_product_id foreign key(product_id) references product(product_id)

);

 

19讲

oracle数据分区及对象数据
数据分区是把表划分为若干个块,在创建表结构时考虑分区方案，选取表中一个或多个列作为分区关键字，该关键字决定那些数据存入那个分区，插入数据时数据被存入相应分区。
最多16个列，64 k个分区。 
优点：
(1)硬件故障，只影响部分数据。
(2)可以对某一个分区操作，提高查询速度。
(3)平衡磁盘I/O操作
create table 表名(
    列名1 数据类型,
    列名2 数据类型,
    ...
)
partition by rang(列名1, 列名2...)(

    partition 分区名1 values less than(值1,值2) tablespace 表空间名,
    partition 分区名2 values less than(值1,值2) tablespace 表空间名
)

 

2005-11-20
20讲
（1）数据自动存储于指定的表空间
（2）maxvalue
（3）未指定表空间使用默认表空间
查询用户缺省表空间
select username,default_tablespace from dba_users;

分区表的查询
select * from personal;
select * from personal partition(p1);--指定查询某一区，其他区不查询
select * from personal partition(p2);

create table part3 as select * from personal partition(p3);--将第三个分区复制为一个表

分区数据字典表：
select partition_name,high_value,tablespace_name from user_tab_partitions where table_name='personal';

分区表的修改
1 增加分区
在最后一个分区后增加分区（如果有maxvalue不能增加分区）
alter table personal add partition p4 values less than(1500) tablespace system;
2 删除分区
alter table personal truncate partition p4;--删除数据结构保留
alter table personal drop partition p4;--数据结构都删除
3 分区拆分（split）
alert table personal split partition p3 at(1000) into(partition p31, partition p32);
4 分区合并（merge）
alert table personal merge partitions p31,p32 into partition p3;
5 分区数据移动（move）：将分区数据从一个表空间移动到另外一个表空间
alert table personal move partition p4 tablespace student;
6 修改分区名称（rename）
alert table personal rename partition p4 to p44;
7 将分区数据与表交换（分区结构与表结构完全相同）（exchange）
alert table personal exchange partition p4 with table part4;--part4是一个表名
select * from personal;
select * from personal partition(p1);
create table part1 as select * from personal partition(p1) where 1=2;--创建一个空表
alert table personal exchange partition p1 with table part1;--将分区1的数据导入表part1中

对象类型数据使用方法（object type）
1 创建对象类型（object type）
create or replace type emp_info as object(
 emp_id number(7),
 emp_name char(20),
 emp_sal number(7,2)
);
2 在创建表时使用对象类型
create table employee(
 job char(20),
 b_date date,
 other emp_info
)
insert into employee values("工程师",sysdate,emp_info(123,'James',800));
select * from employee;
select job, p1.other.emp_name from employee p1;--必须用别名
3 对象类型数据更新
update employee p1 set p1.other.emp_sal=1200 where p1.other.emp_id=123;
4 对象类型数据删除
delete from employee p1 where p1.other.emp_name='James';

必须启动agent服务才能被管理
Oralce Management Server:
管理员用户名：SYSMAN
口令：OEM_TEMP

表空间名：逻辑存储单位。
以system登陆：查询表空间
select * from dba_tablespaces;
create table personal(
    name char(20),
    id number(7),
    sal number(7,2)
)
partition by range(sal)(
    partition p1 values less than(500) tablespace users,
    partition p2 values less than(800) tablespace  student,
    partition p3 values less than(1200) tablespace  student,
    partition p4 values less than(maxvalue) tablespace  student
);