Oracl基础 - 羽林.Luouy

Oracl基础

oracle 基础学习

(1) 查询数据库名：

SQL> select name from v$database;

(2) 查询数据库实例名：

SQL> select instance_name from v$instance;

(3) 查询数据库服务名：

SQL> select value from v$parameter where name='service_names'; // (小写)

(4) 查询全局数据库名(sys用户)：

SQL> select value$ from props$ where name='GLOBAL_DB_NAME'; //　字符串区分大小写

监视用户会话：

SQL> select username, sid, serial#, machine from v$session;

删除用户会话：

SQL> alter system kill session 'sid,serail#';

// 使用下面的方法可以重复执行上一条SQL语句（在SQL*Plus中）

SQL> l //小写字母L，显示上一条SQL语句

1* select username, sid, serial#, machine from v$session

SQL> / //　正斜杠：重复执行上一条SQL语句

SQL> set linesize 1000 //将SQL*Plus中显示行宽设成1000个字符。

SQL> connect system/manager@orasjz // 在SQL*Plus中直接连接到另一台机器上的数据库，@字符后是另一台机器的tnsname

4. 手工配置Oracle网络连接:主要是配置"tnsnames.ora"文件。

A. 手工配置Oracle网络连接配置文件：tnsnames.ora

B. 手工配置Oracle监听进程配置文件：listener.ora

UNIX下启动进程命令：

$ lsnrctl start // 启动监听进程

$ lsnrctl status // 显示监听进程状态

$ lsnrctl stop // 停止监听进程

启动进程的命令与UNIX相同。

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORA54)(PORT = 1521))

//注意：上面一行的HOST必须是你的主机名，否则监听会出问题，也可以使用你的机器的IP地址

)

(DESCRIPTION =

(PROTOCOL_STACK =

(PRESENTATION = GIOP)

(SESSION = RAW)

)

(ADDRESS = (PROTOCOL = TCP)(HOST = ORA54)(PORT = 2481))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = ..........)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = ora54)

(ORACLE_HOME = ..........)

(SID_NAME = ora54) // 注意：ora54为数据库的SID名称，不能更改，否则监听出问题

)

5. 将主机字符串(Host String)写入注册表(简化SQL*Plus的登录)

regedit.exe -> HKLM -> Software -> Oracle -> home0

增加关键字: local(字符串)，键值：主机字符串名。

/* 将下面的内容复制到一个.reg文件中，在Windows 2000中双击执行，即可实现增加或者修改local键值的作用

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]

"local"="ora54"

[SVRMGRL: Server Manager的使用方法]

D:\>svrmgrl

SVRMGR> connect internal //使用svrmgrl，进入后使用的第一个命令必须是这个命令。

口令: // 如果要求输入口令，请输入oracle

连接成功。

SVRMGR> select name from v$database; // 检查当前使用的数据库名

NAME

---------

ORA44

已选择 1 行。

SVRMGR> shutdown immediate //关闭当前使用的数据库

已关闭数据库。

已卸下数据库。

已关闭 ORACLE 实例。

SVRMGR> startup //启动当前使用的数据库，如果无效，请使用startup force

已启动 ORACLE 实例。

系统全局区域合计有 24433932个字节

Fixed Size 70924个字节

Variable Size 7507968个字节

Database Buffers 16777216个字节

Redo Buffers 77824个字节

ORA-00205: ?????????????????????

SVRMGR>

[另一个启动oracle数据库的例子]

d:\> sqlplus internal/oracle

SQL> startup force //强行重新启动数据库。

[修改口令字]

SQL> grant connect to system identified by NewPassword; //如果用数字作口令，需要使用双引号括起来

SQL> grant connect to sys identified by NewPassword;

SQL> alter user system identified by NewPassword;

SQL> alter user sys identified by NewPassword;

SQL>password //需要输入原口令

// 注：以上修改口令的方法等价；sys与system用户可以互相修改口令；如果sys与system用户的口令都忘记了，使用如下方法：

D:\>svrmgrl

SVRMGR> connect internal/oracle

连接成功。

SVRMGR> grant connect to system identified by manager;

语句已处理。

SVRMGR> exit

服务器管理程序结束。

、Oracle 产品组成

查询数据库选件产品：

SQL> select * from v$option;

一般都是True, 如果是False, 可以双击激活。

/* cartridges (小产品的)插件，(大产品的)选件(options)*/

[SYS用户是Oracle数据库中权限最大的用户。]

[SQL*Plus登录方法]

1. c:\> sqlplus "/ as sysdba"

2. c:\> sqlplus internal

// 注意：以上两种方法可以类似的使用于SQL*Plus的图形登录界面中

// 用这种登录方法登录进去，所使用的用户均为SYS。

[之所以会出现这种登录方法，是因为在NT的用户组中存在一个ORA_DBA的本地组，凡在此组中的用户使用操作系统认证，即在此组中的用户登录数据库时不需要密码。]

[orapwd命令:修改internal用户的口令字]

/* orapwd的命令行参数

D:\>orapwd

Usage: orapwd file=<fname> password=<password> entries=<users>

where

file - name of password file (mand),

password - password for SYS and INTERNAL (mand),

entries - maximum number of distinct DBA and OPERs (opt),

There are no spaces around the equal-to (=) character. */

修改internal口令字(internal默认口令为oracle)认证方法(A B两个步骤)：

A. 修改Oracle登录认证方法：

修改文件SQLNET.ORA文件。

SQLNET.AUTHENTICATION_SERVICES=(NTS) //将这一行前面加上#号注释掉，即可将Oracle的认证方法由操作系统认证改为Oracle认证

SQLNET.ORA文件的位置：

UNIX:/u01/app/oracle/product/8.1.6/network/admin/sqlnet.ora

Windows NT/2000: d:\oracle\ora81\network\admin\sqlnet.ora

B. 修改Internal口令字：

Windows NT/2000:

C:\>ORAPWD file=d:\oracle\ora81\database\pwdora8i.ora

password=YourPassword // YourPassword为你要设置的密码

entries=30

/* ORAPWD file=d:\oracle\ora81\database\pwdora54.ora password=qev entries=30 */

UNIX:

$ orapwd file=$ORACLE_HOME/dbs/orapwSID

password=YourPassword

entries=30

然后重新启动Oracle服务(服务－＞OracleServiceHOSTNAME)。

/* 在执行上述命令之前，需要先将相应目录的pwdora8i.ora(或orapwSID)文件删除或者改名，因为口令字文件不能重名。*/

[tkprof 跟踪文件整理工具]

$ tkprof x.trc x.txt

Windows 2000/NT:

d:\oracle\admin\db_name\udump\*.trc

UNIX:/u01/app/oracle/amdin/db_name/udump/*.trc // */

Oracle数据分区技术：8.0以后开始使用

(一)LOB(Large Object)大对象类型数据：

1. BLOB:存储二进制数据，如图象、视频、声音等，用于代替Long raw类型(Oracle7.0以前的数据类型，今后不再支持)

2. CLOB:存储大字符，如：个人简历，用于代替long字段。

3. NCLOB:其它民族语言的支持

(1) 数据库字符集NLS:

Server端：NLS_CHARACTERSET: (如果为以下的字符集，Oracle的数据库可以用来存储汉字)

ZHS16GBK(Oraclei8, Oracle8)

ZHS16CGB231280(Oracle7.3, 8, 8i)

此参数位置在数据字典中，查询核心字符集(语言、日期、货币等)：

SQL>select * from nls_database_parameters;

Client端：NLS_LANG=Simplified Chinese_CHINA.ZHS16GBK (如果没有设置，默认是英文)

regedit.exe

UNIX Client中：

$ NLS_LANG="simplified chinese"_china.zhs16gbk

$ export NLS_LANG

//一般将上述环境变量放入.profile文件中。

/***********************************************插入内容********************************************************

(1) sys是一种用户，internal是一种方式，用来启动关闭数据库，9.0以后不再使用internal，全部是sys。

(2) SQL>set com v7 //将8i版本暂时退回到7版

(3) SQL> alter system suspend; // 使用internal用户执行，冻结数据

SQL> alter system resume; // 取消冻结，恢复正常

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

(2)Oracle数据库核心字符集修改方法：

[*] 修改数据字典(使用SYS用户)：

SQL>update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';

SQL>commit;

然后重新启动数据库。

4. BFILE:外部文件存储，将数据存储在服务器硬盘

5、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:校验，对数据进行校验。

6、SQL命令的输入：

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

7、SQL*Plus 关键字：以回车结束

1. @: 执行外部命令，格式：@路径\文件名

2. #: 注释

3. /: 执行上一条命令

4. Accept:接收键盘输入的命令

5. Append（简化为a): 在行尾增加字符串

SQL> a t

1* select * from dept

SQL> /

6. Break: 分组，　Syntax: break on 分组列 skip n　//n为每两组之间间隔的空行

/* 例：

SQL> break on job

SQL> select * from emp order by job; // 此处务必按分组列排序

SQL> break on job skip 1

SQL> /

7. Btitle, Ttitle:设置表尾、表头

SQL> Ttitle '表头'

SQL> Btitle '表尾'

SQL> Ttitle off //失效

8. Change: 写错了改正, Syntax: C/old/new

/* 例：

SQL> select * from detp;

select * from detp

SQL> c/tp/pt

1* select * from dept

SQL> /

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: 全部的数据字典

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;

SQL> rollback;

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

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'); // 在命令行中给字符或日期型参数加上单引号，则下面输入时不再需要输入单引号

输入 x1 的值: 53

输入 x2 的值: dd

输入 x3 的值: ee

原值 1: insert into dept values(&x1, '&x2', '&x3')

新值 1: insert into dept values(53, 'dd', 'ee')

// 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;

(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

select * from emp where job='MANAGER'; // select * from tab; 检查

SQL> select * from manager; // 查询视图如果查询表

// 第二次创建视图，可以使用or replace参数，不需要再删除而直接覆盖同名视图。

SQL> create or replace view manager

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 口令

using '主机字符串';

[*]数据库链路名必须与远程数据库的全局数据库名(数据库名.域名，若没有数据库名，就是数据库名)相同

[*]用户名及口令为远程数据库的用户名及口令

[*]主机字符串为本机tnsnames.ora中网络连接串。

SQL> Create database link ora31 connect to user30 identified by user30 using 'ora31';

2. 使用数据库链路：

SQL> select * from product@ora31;

SQL> insert into product@ora31 values(...);

SQL> Create table product as select * from product@ora31;

3. 删除数据库链路：

SQL> Drop database link ora31;

(一）创建数据库触发器实现两个数据库之间实时数据传输。

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

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

[*]在对方的数据库(DB2)上建立触发器，即数据发送方的数据库上建立触发器。

[*]在数据发送方建立指向DB1的数据库链路。

[操作步骤（假设db1为ora31, db2为ora8i)：]

(1) 在DB1上建立用来复制远程数据的表：

SQL> Create table product

as select * from product@ora31;

(2) 在DB2上建立到DB1的数据库链路：

SQL> Create database link ora31 connect to user30 identified by user30 using 'ora31';

(3) 在DB2上建立触发器：

SQL> Create or Replace trigger insert_product before insert on product

for each row

Begin

Insert into product@ora8i

values(:new.p_id, :new.p_name);

End insert_product;

// 上述创建数据库触发器的语句，请以.号结束，然后以/执行。

/* 查看SQL语句执行的错误信息：

SQL> show errors

(4) 在DB2上测试数据的自动复制是否成功：

SQL> Insert into product values(1005, '测试商品'); // 在db2的表中插入一条数据

SQL> select * from product; // 检查数据是否正确插入本地表

SQL> select * from product@ora31; // 检查数据是否复制到了db1的表中

查询数据库链路信息：

SQL> select username, password from user_db_links;

数据更新：

SQL> Create or Replace trigger update_product

before update on product

for each row

Begin

update product@ora8i

set p_id = :new.p_id, p_name = new.p_name where p_id = :old.p_id

End update_product;

数据删除：

SQL> Create or Replace trigger delete_product

before delete on product

for each row

Begin

delete from product@ora8i where p_id = :old.pid

End delete_product

（二）. 创建快照（实体化视图）实现两个数据库之间定时数据库传输：

快照：要求主副站点数据库的用户名相同

(1) 在主节点创建快照日志

语法：SQL> Create snapshot log on 主节点表名; //主节点基表必须含有主键

(2) 在副节点创建快照

语法：

SQL> Create snapshot 快照名

refresh 刷新方式

next 时间间隔

with primary key

for update

as select * from 主节点表名@数据库链路名;

[*]刷新方式：

Compelete:完全刷新

Force:强制刷新(建议使用，强制刷新自动先fast刷新，然后再force刷新)

Fast:快速刷新

[*] 时间间隔：以天为单位。

sysdate + 1/4 // 六个小时刷新一次

sysdate + 1/1440 // 一分钟刷新一次

SQL> show user

USER 为"STUD29"

SQL> create snapshot log on dept;

实体化视图日志已创建。

SQL> show user

USER 为"STUD29"

SQL> select * from dept;

SQL> insert into dept values(60, 'test snap', 'snapshot');

[2]副节点

SQL> connect system/ab

已连接。

SQL> create user stud29 identified by stud29;

用户已创建

SQL> grant connect, resource to stud29;

授权成功。

SQL> grant create snapshot to stud29;

授权成功。

SQL> connect stud29/stud29;

已连接。

SQL> connect system/ab

已连接。

SQL> grant create database link to stud29;

授权成功。

SQL> connect stud29/stud29

已连接。

SQL> create database link ora8i connect to stud29 identified by stud29 using 'tea';

数据库链接已创建。

SQL> create snapshot dept

实体化视图已创建。

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

创建索引(indexes):

语法：SQL> create index 索引名 on 表名(列名);

例：SQL> create index index_dept_dname on dept(dname);

索引数据字典：

SQL>select index_name, table_owner, table_name, from user_indexes;

五、创建序列(Sequences):

语法：SQL> Create sequence 序列名

start with 起始编码

increment by 步长

maxvalue 终止编码;

SQL> create sequence id_code

start with 2

increment by 2

maxvalue 999;

序列使用方法：

id_code.nextval // 下一个值

id_code.currval // 当前值

第一次要使用nextval，然后以后每次使用currval。

insert into student values(id_code.nextval, '姓名');

[*] ||(双竖线)在oracle中是连接符号，将两个字符串连成一个，如：'A' || 'B' = 'AB'

　数据库分区技术

一、什么是数据分区？

　　数据分区是指把一个表划分成若干小块。在创建表的结构时应考虑好分区方案，选择表中某一列或多列数据作为分区关键字，该关键字决定哪些数据分到哪些区。Oracle对分区进行管理，新插入数据自动存储到相应的分区。

二、创建分区表：

SQL> Create table employee (

id number(7),

name varchar2(20),

sal number(7,2))

Partition by range(sal)

(Partition p1 values less than(500) tablespace users,

Partition p2 values less than(800) tablespace tools,

partition p3 values less than(1000) tablespace system);

// p1, p2, p3是三个分区的名字，users, tools, system是三个表空间的名字。less than是小于（不包含）。

几点说明：

[.] 所插数据不得大于LESS THAN中的最大值

[.] 可以使用MAXVALUE（如上面的语句中，要求工资不能大于1000，如果出现这种情况，则应该改成下面的语句：

SQL> Create table employee (

id number(7),

name varchar2(20),

sal number(7,2))

Partition by range(sal)

(Partition p1 values less than(500) tablespace users,

Partition p2 values less than(800) tablespace tools,

partition p3 values less than(1000) tablespace system,

partition p4 values less then(maxvale) tablespace users);

[.] 不指定表空间时，则该区使用该用户的缺省表空间。

* 查询每个用户的用户缺省表空间：

SQL> select username, default_tablespace from dba_users; // 使用dba用户查询

[*]查询可以使用的表空间名字：

SQL> select tablespace_name, file_name from dba_data_files; // 使用dba（sys或system）来执行

SQL> select dba_users.username, dba_users.default_tablespace, dba_data_files.file_name

from dba_users, dba_data_files

where dba_users.default_tablespace = dba_data_files.tablespace_name;

[temp表空间不能用于数据分区。]

三、分区表的查询方法：

SQL> select * from employee; // 按没有分区的方法查询

SQL> select * from employee partition(p1); // 只查询p1分区的数据。

SQL> create table part3 as select * from employee partition(p3);

四、分区表的修改：

1、增加分区：

SQL> Alter table employee ADD

partition p4 values less than(1500) tablespace users;

[*]分区数据字典：

SQL> select partition_name, high_value, tablespace_name

from user_tab_partitions

where table_name = 'EMPLOYEE';

2、删除分区:

SQL> Alter table employee DROP partition p4; // 结构数据全部删除（相应分区及数据全部被删除)

SQL> Alter table employee TRUNCATE partition p4; // 保留结构(即区还存在)，数据删除

3、修改区名：

SQL> Alter table employee RENAME partition p4 to p5;

4、分区数据移动：将分区数据从一个表空间移动到另一个表空间

SQL>Alter table employee MOVE partition p4 tablespace system;

5、分区的拆分：

SQL> Alter table employee SPLIT

partition p3 at(900)

into(partition p31, partition p32);

/* 关于数据字典的几点说明：

v$打头的数据字典，后面不会以s结尾，例如：V$database, v$session;

user打头的，后面都会以s结尾(复数), 如：user_tab_partitions, user_tables

dba打头的，有的以s结尾，有的不。

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

6、分区的合并：

SQL> Alter table employee MERGE partitions p31, p32 into partition p3;

　SQL*Plus 报表功能

/********************** 插入内容：数据字典的一些说明 *****************************

(1) user_XXX：用户，例如：user_tables

(2) dba_XXX: DBA专用

(3) all_XXX: 本用户建的，或者其它用户创建本用户可以查询的（需要其它用户的授权）

(4) v$XXX: 动态数据字典，如：v$database, v$instance, v$session，这些数据字典在oracle不启动时也能查询

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

一、定义表头与表尾

SQL> ttitle '表头'

SQL> btitile '表尾'

失效：SQL> ttitle off

SQL> btittle off

二、定义列名

语法：SQL> column 列名 heading 别名 // 别名不区分大小写

三、定义列格式：

SQL> column 列名 Format 格式

常用列格式：An : A为字符，n为最大字符宽度。

$99.9999.99

9.99eeee

例：SQL> Column sal format $99.9999.99

SQL> Column comm like Sal

四、分组命令：

语法：SQL> break on 列名　skip n

例：SQL> break on deptno skip 2

SQL> select * from emp order by deptno;

五、统计计算：

语法：SQL> compute 函数 of 统计列 on skip n //可以使用的函数有：sum, max, min, avg,count, var(斜方差), std(标准差)

例：SQL> compute sum of sal on deptno

清除命令：SQL> clear compute

SQL> clear break

SQL> clear column

增加报表级统计：

SQL> break on deptno on REPORT

SQL> compute sum of sal on report

// 整个报表出一个结果，上面两行都要运行，那么整个报表会根据你的设置出一个sum of sal的总计结果。

　函数

一、日期格式转换函数：to_char(日期变量,'格式') // 格式要用单引号括起来

(1) 日期格式构成方法:

年月日时分秒

yy mm dd hh(12小时制） mi ss

yyyy mon dy(星期) hh24（24小时制）

month day

A. yy.mm.dd, yy/mm/dd, yy-mm-dd, yyyy.mm.dd, ... 加中文也可以，中文要用又引号括起来

SQL>select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; // dual是一个虚拟表，任何用户都可以使用。

二、聚组函数：从一组中返回汇总信息

聚组函数有：Sum, count, count distinct, max, min, avg, stddev(标准差)

例：SQL>select min(sal), max(sal), avg(sal), sum(sal) form emp;

SQL> select ename, job, sal from emp where sal=(slect max(sal) from emp);

SQL> select count(*) from emp;

　复杂查询

一、连接查询：

问：Smith在哪里工作？

答：SQL> select loc from dept, emp where ename = 'SMITH' and emp.deptno = dept.deptno;

二、集合查询[请参考PowerPiont教程：SQL讲稿.ppt P29,30,31]

集合操作是将多个基表的查询结果作UNION运算。

交操作: Intersect

差操作：MINUS

三、子查询(Subqueries):

子查询是在where子句中包含的查询语句，是由系列简单构成的复杂查询。

问：谁与smith在同一部门工作？

答：SQL> select deptno from emp where ename = 'SMITH';

SQL> select ename from emp where deptno = 20;

将两个语句合起来：Select ename from emp where deptno = ( select deptno from emp where ename = 'SMITH');

四、同义词(Synonym):

1、创建私有同义词：

语法：SQL> create synonym 同义词名 for 代替项;

user01:

SQL> grant select, upate on product to user02;

user02:

SQL> Create synonym product for user01.product;

SQL> select * from product; // 这里的product即user01.product。

同义词数据字典：

SQL> select synonym_name, owner, table_name from all_synonyms;

SQL> select synonym_name, table_name from user_synonyms;

2、DBA可以创建公共同义词(Public Synonym)：公共同义词全体用户可以存取

语法：SQL> create public synonym 公共同义词名 for 代替项;

SCOTT:

SQL> grant select on payment to public;

SYSTEM:

SQL> create public synonym payment for scott.payment;

3. 删除同义词：

User:SQL> drop synonym 私有同义词名;

DBA: SQL> drop public synonym 公共同义词名;

posted on 2010-03-20 13:31 羽林.Luouy 阅读(837) 评论(0) 编辑收藏举报