oracle基础学习

1.desc table_name 可以查询表的结构

2.怎么获取有哪些用户在使用数据库 

select username from v$session;

3.如何在Oracle服务器上通过SQLPLUS查看本机IP地址 ? 

select sys_context('userenv','ip_address') from dual; 

如果是登陆本机数据库，只能返回127.0.0.1

4.如何给表、列加注释？

SQL>comment on table 表 is '表注释';

注释已创建

SQL>comment on column 表.列 is '列注释';

注释已创建。 

SQL> select * from user_tab_comments where comments is not null; 

5.如何在ORACLE中取毫秒?

select systimestamp from dual; 

6.如何在字符串里加回车？ 

添加一个||chr(10)

select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;

7.怎样修改oracel数据库的默认日期?

alter session set nls_date_format='yyyymmddhh24miss';

8.怎么可以看到数据库有多少个tablespace? 

select * from dba_tablespaces;

9.如何显示当前连接用户? 

SHOW USER

10.如何测试SQL语句执行所用的时间? 

SQL>set timing on ;

11.怎么把select出来的结果导到一个文本文件中？

SQL>SPOOL F:\ABCD.TXT; 

SQL>select * from table;

SQL >spool off;

12.如何在sqlplus下改变字段大小?

alter table table_name modify (field_name varchar2(100));

改大行，改小不行（除非都是空的）

13.如果修改表名?

alter table old_table_name rename to new_table_name;

14.如何搜索出前N条记录？ (desc降序)

SELECT * FROM Tablename WHERE ROWNUM < n 

ORDER BY column;

15. 如何在给现有的日期加上2年？

select add_months(sysdate,24) from dual;

16.Connect string是指什么? 

应该是tnsnames.ora中的服务名后面的内容

17.返回大于等于N的最小整数值?

SELECT CEIL(-10.102) FROM DUAL;

18.返回小于等于N的最大整数值? 

SELECT FLOOR(2.3) FROM DUAL;

19.返回行的物理地址 

SELECT ROWID, ename FROM tablename WHERE deptno = 20 ;

20.将N秒转换为时分秒格式？ 

set serverout on 

declare 

N number := 1000000; 

ret varchar2(100); 

begin 

ret := trunc(n/3600) || '小时' || to_char(to_date(mod(n,3600),'sssss'),'fmmi"分"ss"秒"'); 

dbms_output.put_line(ret); 

end;

21.如何监控当前数据库谁在运行什么SQL语句？ 

SELECT osuser, username, sql_text from v$session a, v$sqltext b 

where a.sql_address =b.address order by address, piece;

22.如何知道当前用户的ID号? 

SQL>SHOW USER; 

OR 

SQL>select user from dual;

23.如何知道使用CPU多的用户session? 

11是cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value 

from v$session a,v$process b,v$sesstat c 

where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;

24.Oracle建立表空间和用户

建立表空间和用户的步骤：

用户

建立：create user 用户名 identified by "密码";

授权：grant create session to 用户名;

            grant create table to 用户名;

            grant create tablespace to 用户名;

            grant create view to 用户名;

表空间

建立表空间(一般建N个存数据的表空间和一个索引空间)：

create tablespace 表空间名

datafile ' 路径(要先建好路径)\***.dbf ' size *M

tempfile ' 路径\***.dbf ' size *M

autoextend on --自动增长

--还有一些定义大小的命令，看需要

default storage(

initial 100K,

next 100k,

);

用户权限

授予用户使用表空间的权限：

alter user 用户名 quota unlimited on 表空间;

或 alter user 用户名 quota *M on 表空间;

create tablespace zq datafile 'D:\zq\zw.dbf' SIZE 1000M AUTOALLOCATE;

修改用户的默认表空间 

alter user username default tablespace tablespacename;

25.在sqlplus 中清屏命令：clear src   clear   screen; cl   scr;

怎样用语句查询表空间里面表的内容?

select table_name from all_tables where tablespace_name='zq';

select   table_name   from   user_tables   where   tablespace_name='xx'    

26.如何查询表在哪个表空间中？(单引号里面的要大写)

SELECT tablespace_name FROM USER_TABLES WHERE table_name = 'YOUR_TABLENAME'

查一下，这个表是哪个用户下的，如果是本用户则可以用上面的sql

如果是别的用户的表你就用

SELECT tablespace_name FROM DBA_TABLES WHERE table_name = 'YOUR_TABLENAME'   and owner='表的OWNER'

还有你要确定你查的确实是一个表而不是 view 或 SYNONYM

而且在引号里面的表名和owner都要用大写字母

27.表的创建

create table aa 

(a varchar2(10),

b number(8,2),

c date

) tablespace users;

如果在创建用户时没有指定默认表空间，系统默认表空间为System，在创建表时必须指定tablespace;

28.如何查询一个表空间下的所有表(单引号里面的要大写)

select table_name from user_tables where tablespace_name='表空间名';

29.更改计算机名后会出现Oracle　ORA-12541:TNS:no listener错误解决方法

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora

修改为现在的计算机名，再次启动OracleOraHome90TNSListener服务成功

30.创建表时默认表空间是SYSAUX

31.oracle10g em Database Control的启动问题修复 

打开http://localhost:1158/em/ 显示数据库状态没有启动，提示用户登录错误ORA-28000: the account is locked，使用PL/SQL或SQL*plus

连接是正常的。到网上搜索一番，没有找到确切的原因。其中一个可能的原因是用户DBSNMP的密码和sys用户的密码不一致，导致

OracleDBConsoleSID服务错误，网上很多朋友说使用emca（EM Configuration Assistant）工具进行修复。

在命令行里键入下面两个命令就可以修复数据库的em

emca -repos recreate

emca -config dbcontrol db

        另外，网上很多朋友给出“emca -r”这样的命令，我试了之后发现10g版本的命令格式已经有所变化，具体的命令格式可以通过命令

的帮助获得。在命令行中键入

emca help=y

可以查看详细的命令格式。

常用的命令语法：

emca -repos create创建一个EM资料库

emca -repos recreate重建一个EM资料库

emca -repos drop删除一个EM资料库

emca -config dbcontrol db配置数据库的 Database Control

emca -deconfig dbcontrol db删除数据库的 Database Control配置

emca -reconfig ports 重新配置db control的端口，默认端口在1158

emctl start console启动EM console服务，使用前需要先设置ORACLE_SID环境变量

emctl stop console停止EM console服务，使用前需要先设置ORACLE_SID环境变量

32.解决启动Oracle9i的OEM或OMS的常见问题(VTK-1000)

能否正常启动OEM或OMS关键有以下两点：

    第一．Oracle的系统服务是否开启；

    第二．登录时用的用户名和口令是否正确。

    那么先针对第一点谈谈Oracle的系统服务。在完全安装的情况下，Oracle的系统服务共有11项：

    1．Oracle OLAP 9.0.1.0.1

    2．Oracle OLAP Agent

    3．OracleOraHome90Agent

    4．OracleOraHome90ClientCache

    5．OracleOraHome90HTTPServer

    6．OracleOraHome90ManagementServer(0.5M)

    7．OracleOraHome90PagingServer

    8．OracleOraHome90SNMPPeerEncapsulator

    9．OracleOraHome90SNMPPeerMasterAgent

    10．OracleOraHome90TNSListener(5.2M)

    11．OracleServiceORACLE(70M)

        (注：OraHome90是可以在安装时改变的Oracle的主目录名称，是安装时的默认值)

    其中最重要的服务有3个，分别是OracleOraHome90ManagementServer、OracleOraHome90TNSListener与

OracleServiceORACLE。下面就来看一下有哪些启动错误与它们有关。

    1．Oracle系统提示：Ora-12541:TNS:没有监听器；

    2．操作系统提示：在本地计算机无法启动OMS服务

                     错误：1053：服务并未及时响应来控制请求附带；

    以上两种错误提示大都是由OracleOraHome90TNSListener监听服务引起的。

解决方法：控制面版->管理工具->服务->右键单击“OracleOraHome90TNSListener”，再单击“启动”。

    3．Oracle系统提示：Ora-12500:TNS:监听程序无法启动专用服务器进程；

    该错误是由OracleServiceORACLE专用服务器进程引起的。

解决方法：控制面版->管理工具->服务->右键单击“OracleServiceORACLE”，再单击“启动”。

    4．Oracle系统提示：VTK-1000：无法连接到Management Server。

                       请验证您已输入Oracle Management Server的正确主机名和状态。

    该错误引起的原因有两种，一是OracleOraHome90ManagementServer还没启动；二是没有输入主机名。

解决方法：控制面版->管理工具->服务->右键单击“OracleOraHome90ManagementServer”，

再单击“启动”，

或是输入您这台计算机的完整名称。

   

    接着针对第二点谈谈登录时用的用户名和口令。

    在安装结束后，系统提供了两个默认的数据库系统管理员，其用户名和口令分别是SYS/change_on_install和SYSTEM/manager，同时系统还

提供了登录OMS的用户名和口令：sysman/oem_temp。这里容易出现错误的是在登录OMS是用SYS或SYSTEM作为用户名进行登录，那么Oracle系统

就回有“登录身份证明不正确”的提示。

   小结：这三个服务的启动或关闭还有先后的顺序。一般来讲，启动时必须先启动OracleOraHome90TNSListener再启动

OracleOraHome90ManagementServer或OracleServiceORACLE，在启动OracleOraHome90ManagementServer时，同时也启动了

OracleServiceORACLE。而关闭时必须先关闭OracleOraHome90ManagementServer再关闭OracleOraHome90TNSListener或OracleServiceORACLE，

关闭OracleOraHome90ManagementServer时，若有提示输入用户名和口令，请输入sysman的用户名和口令，以确保成功的执行。有些其他提示如

：资源已被占用，I/O重复，端口已被使用等等之类的话，那最好与系统管理员联系，再寻求解决办法。

 

 

 

 

 

 

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 

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

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 公共同义词名;