数据库oracle 目录结构 Oracle数据库入门——sql语句和函数详解

Oracle_Home主目录位于D:\dev\oracle\product\10.2.0(oracle安装路径)下，它包含Oracle软件运行有关的子目录和网络文件以及选定的组件等；若在主机上第一次且只安装了Oracle数据库,没有其他Oracle产品，则使用默认的主目录\db_1；如果在同一台主机的同一个根目录下安装多个产品或安装了第2次，则Oracle_Home主目录会以db_n的形式出现,即db_2、db_3等。这也是为什么Oracle_Base目录可以有多个Oracle_Home目录的缘故。

Oracle_Home(D:\dev\oracle\product\10.2.0\db_1)目录中包括的主要子目录有:

\BIN——主要包含用于数据库管理的各种命令等。

\css——与Oracle Cluster Synchronization服务有关的文件。

\dbs——存放数据库服务器端的参数文件Spfile。

\demo——存放数据库实例模式的脚本等。

\install——用于存储ORACLE安装后的端口号,iSQL*Plus以及Enterprise Manager Database Control启动并登录的方式等。

\network\admin——有关监听器listener.ora和sqlnet.ora以及tnsnames.ora等。

\sysman\config——用于与Oracle Enterprise Management有关的端口管理等。

   

二、Admin目录

数据库管理文件均存储在D:\dev\oracle\product\10.2.0\admin\orcl目录下。各个子目录的主要含义如下:

\bdump——后台进程跟踪文件。

\cdump——信息转储文件（core dump）。

\create——数据库创建文件。

\exp——数据库导出文件。

\pfile——初始化参数文件。

\udump——用户SQL追踪文件。

   

三、Oradata目录

数据库文件存储在D:\dev\oracle\product\10.2.0\oradata\orcl目录下,该目录主要存储数据库的控制文件、数据文件、重做日志文件。其中*.dbf文件对应数据库中每个表空间；.ctl文件为控制文件；.log文件对应重做日志文件组及其成员。

  

四、flash_recovery_area目录

flash_recovery_area目录存储并管理与备份和恢复有关的文件。它包含系统中每个数据库的子目录。该目录可用于存储与恢复有关的文件,如控制文件、联机重做日志副本、归档日志、闪回日志以及Oracle数据库恢复管理器(RMAN)备份等

 

（1）oradata：打开这个文件夹，里面是一个以数据库名（而不是实例名）命名的文件夹，我们喜欢将其命名为“orcl”。把orcl打开，看到的文件就是Oracle数据库的大部分内容了，也是DBA需要管理的东西。这些文件可以分成三类：

• *.ctl：控制文件 （至少一个，一般是三个）
• *.dbf：数据文件（分为系统数据文件和用户数据文件）
• *.log：在线重做日志文件（至少两个，默认安装是三个，Oracle9i 下为 100M 大小）

我们在后继课程中会详细讲述这些文件的特性和用处。
—————————————————————————————————-
NOTE:如果想使用 dbca 安装第二个数据库 orcl2，那么我们应该在 oradata 下建立文件夹“orcl2”，然后将orcl2 的数据文件、控制文件、在线重做日志文件都放在文件夹“orcl2”下。这不是必须的，但这样去做，你以后会发现管理起来特别方便。
—————————————————————————————————-

（2）admin：打开这个文件夹，我们同样会看到一个以数据库名命名的文件夹，也许就是orcl。我们再把它打开，会出现如下五个文件夹：

• bdump：后台进程（background process）dump文件。包括dbwr、lgwr、arch、pmon、qmn、smon等后台进程dump文件，以及警告日志文件：alter_.log 。我们要记住的，就是警告日志文件！当数据库发生错误的时候，我们第一步要检查的就是这个文件。该文件记录了自数据库安装以来的所有结构变动记录，错误记录，以及日志切换和归档记录。
• cdump：核心（core）dump文件。当数据库出现内部错误的时候，会将部分错误信息记录在这个文件中。
• create：不详。
• pfile：不详（不过这个pfile不是数据库启动时调用的PFILE）。
• udump：用户（user）dump文件。比如，我使用命令： alter system dump datafile 11 block 257; 将11号文件的257号数据块dump出来了，那么dump文件就自动放在了该目录下。

注意：我们必须记住的，是 bdump 下的警告日志文件，以及 udump 文件夹！

 

—————————————————————————————————–
NOTE: 对于 udump，可以再多了解一些：这些路径，是由参数控制的 ：

 

SQL> show parameter dump NAME                                 TYPE        VALUE ———————————— ———– ————————- background_core_dump                 string      partial background_dump_dest                 string      D:\Oracle\admin\orcl\bdump core_dump_dest                       string      D:\Oracle\admin\orcl\cdump max_dump_file_size                   string      UNLIMITED shadow_core_dump                     string      partial user_dump_dest                       string      D:\Oracle\admin\orcl\udump

而我们手工 dump 出来的文件，可以通过如下 sql找到文件的位置：

 

select p.value||’\’||i.instance_name||’_ora_’||p.spid||’.trc’ trace_path from v$process p, v$instance i, v$parameter p where p.addr = ( select paddr from v$session where sid = ( select sid from v$mystat where rownum = 1) ) and p.name=’user_dump_dest’;

—————————————————————————————————–

（3）ora92：只剩下这个文件夹了。可是我们打开后，会发现里面有很多的文件夹。而随便打开一个，里面会有更多的文件夹。很多内容我也不懂，所以放在最后介绍了，呵呵。

这里面有很多东西是属于 Oracle 软件的，我们只讲述与数据库有关的内容。

• admin：和（2）中的“admin”文件夹格局类似，是一个模板，用于手工创建数据库。
• bin：管理Oracle的命令。我们常用的 dbca、exp、imp、sqlplus、sqlplusw、tnsping、tnslsnr 等命令就放这里。这也是为什么我们必须在环境变量的“path”中，指定这个路径的原因。
• database：这里有三个重要的文件：密码文件、PFILE、SPFILE。密码文件的命名格式为“pwd.ora”，SPFILE的命名格式为“spfile.ora”，PFILE 的命名格式为“init.ora”。在 5.2 小节中我们会介绍一点 pfile 和spfile 的用处。至于密码文件，我们可以通过 Oracle 提供的操作系统级的命令“orapwd”来查看其用法。
• rdbms：存放了Oracle提供的数据库管理的很多脚本。可被 Oracle 某些功能自动调用，或由我们手工执行。
• network：网络配置文件。我们看里面的“admin”文件夹，有两个文件我们应该比较熟悉：tnsname.ora、listener.ora。不熟悉？那好，我们看看下面这个菜单：
  

我们安装上数据库后，会在这里配置服务名，来连接到服务器上开发程序。所配置的服务名信息，就保存到 tnsname.ora 文件中。我们也可以不使用上述菜单，而直接在 tnsname.ora 文件中配置服务名。

至于 listener.ora，是监听配置文件。在这个菜单所提供的界面中，也能看到。比如我的机器是 C，现在我要连接到服务器 S 上，那么我在 C 的 tnsname.ora 文件中配置连接到 S 的服务名“cs”；而 S 上的 listener.ora 文件中配置了相应的监听，并已经启动。那么当 C 用“cs”试图连接 S 时，S 的监听就捕捉到了该信息，并转发给相应的 Oracle 服务进程。

 

 

 

 

一、oracle常用数据类型

 

  

一、  数据定义语言(ddl)

数据定义语言ddl(data definition language)用于改变数据库结构，包括创建、更改和删除数据库对象。

用于操纵表结构的数据定义语言命令有：

create table

alter table

truncate table

drop table

eg、

—创建tb_stu表数据结构

create table tb_stu(

   id number,

   name varchar2(20)

);

 

—修改tb_stu表数据结构，新增一列

alter table tb_stu add pwd varchar2(6);

—修改字段

alter table tb_stu modify pwd varchar2(20);

–重命名表: rename table_name1 to table_name2;

RENAME student TO tb_student;

–重命名列：alter table table_name rename column col_oldname to colnewname;

ALTER TABLE student RENAME COLUMN pwd TO pwd1;

—删除字段

alter table tb_stu drop column pwd;

 

 

 

—删除tb_stu表数据

truncate table tb_stu;

 

—删除tb_stu表

drop table tb_stu;

二、  数据操作语言(dml)

数据操纵语言dml(data manipulation language)用于检索、插入和修改数据，dml利用insert、select、update 及 delete 等语句来操作数据库对象所包含的数据。

(1)、利用现有的表创建表

语法：create table <new_table_name> as

         select column_names from <old_table_name>;

eg、

1)、create table tb_dept as select * from dept;

2)、create table tb_dept as select a.deptno, a.dname from dept a;

3)、create table tb_dept as select * from dept a where a.deptno=10;

 

(2)、选择无重复的行，使用distinct关键字

eg、select distinct a.dname from tb_dept a;

 

(3)、使用列别名

select a.deptno 部门编号, a.dname “部门 名称” from tb_dept a;

—中间有空格，用””

 

(4)、插入来自其他表中的记录

语法：insert into <table_name> [(cloumn_list)]

         select column_names from <other_table_name>;

eg、insert into student2 select * from student;

 

三、  事务控制语言(tcl)

放到“事务”讲解

四、  数据控制语言(dcl)

放到“用户和权限”讲解

五、  sql操作符

略

六、  oracle函数

1、字符函数

initcap(char)函数：首字母大写

eg、select initcap(‘hello’) from dual;

输出结果：Hello

 

lower(char)函数：字母全部转换为小写

eg、select lower(‘heLLo’) from dual;

输出结果：hello

 

upper(char)函数：字母全部转换为大写

eg、select upper(‘hello’) from dual;

输出结果：HELLO

 

ltrim(char,set)函数：去掉左边指定的字符

eg、select ltrim(‘xyzadmin’,’xyz’) from dual;

输出结果：admin

 

rtrim(char,set)函数：去掉右边指定的字符

eg、select rtrim(‘xyzadmin’,’admin’) from dual;

输出结果：xyz

 

translate(char, from, to)函数：返回将from中的每个字符替换为to中相应字符以后的string

eg、1)、select translate(‘jack’,’j’,’b’) from dual; –将j替换为b

输出结果：back

2)、select translate(‘123abc’,’2dc’,’4e’) from dual;

输出结果：143ab

解析：2替换为4，

d因为字符串里没有，所以不作替换，

c由于没有对应的替换字符，所以字符串里的c会被删除

 

replace(char, searchstring, [rep string]) 函数：替换

eg、select replace(‘jack and jue’ ,’j’, ‘bl’) from dual;

输出结果：black and blue

 

instr(char, m, n)函数：返回截取的字符串在源字符串中的位置，没有返回0

eg、select instr (‘worldwide’, ‘d’) from dual;

输出结果：5

instr(C1,C2,I,J) –>判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1

 

substr(char, m, n)函数：截取字符串

eg、select substr(‘abcdefg’, 3, 2) from dual;

输出结果：cd

 

concat(expr1, expr2)函数：连接字符串

eg、select concat(‘Hello’,’ world’) from dual;

输出结果：Hello world

 

chr(number)函数：给出整数,返回对应的字符;

eg、select chr(54740), chr(65) from dual;

输出结果：赵 A

 

lpad和rpad函数：粘贴字符

lpad在列的左边粘贴字符

rpad在列的右边粘贴字符

eg、SELECT lpad(‘林计钦’, 10, ‘*’) FROM dual;

输出结果：****林计钦

注意：不够字符则用*来填满

 

trim函数：去除字符串左右两边的空字符串；如果不指定,默认为空格符。

 

length(char)函数：返回字符串的长度

eg、select length(‘abcdef’) from dual;

输出结果：6

2、日期时间函数

add_months函数：增加或减去月份

eg、

select to_char(add_months(to_date(‘2011-11-30′,’yyyy-mm-dd’),2),’yyyy-mm-dd’) from dual;–前进

输出结果：2012-01-31

select to_char(add_months(to_date(‘2011-11-30′,’yyyy-mm-dd’),-2),’yyyy-mm-dd’) from dual;–后退

输出结果：2011-9-30

 

months_between(date2,date1)函数：给出date2-date1的月份

eg、select months_between(’19-12月-1999′,’19-3月-1999′) from dual;

输出结果：9

 

last_day函数：返回日期的最后一天

eg、select to_char(last_day(sysdate), ‘yyyy-mm-dd hh24:mi:ss’) from dual;

输出结果：2011-11-30 23:27:20

 

round和trunc函数：按照指定的精度进行四舍五入

eg、select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

输出结果： 56  -55  55  -55

 

next_day(date,’day’)函数：给出日期date和星期几计算下一个星期的日期

eg、select next_day(’30-11月-2011′,’星期三’) next_day from dual;

输出结果：07-12月-11

 

extract(datetime)函数：获取时间函数

eg、

输出结果：

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; –获取年

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; –获取月

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; –获取日

select to_char(sysdate,’hh24′) from dual; –获取小时

select to_char(sysdate, ‘mi’) from dual;–获取分钟

select to_char(sysdate, ‘ss’) from dual;–获取秒

 

select extract(year from systimestamp) year 

      ,extract(month from systimestamp) month 

      ,extract(day from systimestamp) day 

      ,extract(minute from systimestamp) minute 

      ,extract(second from systimestamp) second 

      ,extract(timezone_hour from systimestamp) th 

      ,extract(timezone_minute from systimestamp) tm 

      ,extract(timezone_region from systimestamp) tr 

      ,extract(timezone_abbr from systimestamp) ta 

from dual;

3、数字函数

abs函数：绝对值

eg、select abs(-15) from dual;

输出结果：15

round(m, n)四舍五入函数：

eg、select round(45.926, 2) from dual;

输出结果：45.93

select round(45.926, -1) from dual;

输出结果：50

select round(245.926, -2) from dual;

输出结果：200

select round(275.926, -2) from dual;

输出结果：300

 

trunc(m, n)截取函数

一、日期

trunc函数为指定元素而截取的日期值。

其具体的语法格式如下：

TRUNC（date[,fmt]）

其中：date 一个日期值

      fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去

如果当日日期是：2011-3-18

select trunc(sysdate) from dual –2011-3-18 今天的日期为2011-3-18

select trunc(sysdate, ‘mm’) from dual –2011-3-1 返回当月第一天.

select trunc(sysdate,’yy’) from dual –2011-1-1 返回当年第一天

select trunc(sysdate,’dd’) from dual –2011-3-18 返回当前年月日

select trunc(sysdate,’yyyy’) from dual –2011-1-1 返回当年第一天

select trunc(sysdate,’d’) from dual –2011-3-13 (星期天)返回当前星期的第一天

select trunc(sysdate, ‘hh’) from dual –2011-3-18 14:00:00 当前时间为14:41

select trunc(sysdate, ‘mi’) from dual –2011-3-18 14:41:00 TRUNC()函数没有秒的精确

 

二、数字

trunc（number,num_digits）

number 需要截尾取整的数字。

num_digits 用于指定取整精度的数字。num_digits 的默认值为 0。

trunc()函数截取时不进行四舍五入

 

select trunc(123.458) from dual –123

select trunc(123.458,0) from dual –123

select trunc(123.458,1) from dual –123.4

select trunc(123.458,-1) from dual –120

select trunc(123.458,-4) from dual –0

select trunc(123.458,4) from dual –123.458

select trunc(123) from dual –123

select trunc(123,1) from dual –123

select trunc(123,-1) from dual –120

 

mod(m,n)求余函数

eg、select mod(5,2) from dual;

输出结果：1

 

ceil(n)函数：取大于等于数值n的最小整数

eg、select ceil(44.778) from dual;

输出结果：45

 

floor(n)函数：取小于等于数值n的最大整数

eg、select ceil(44.778) from dual;

输出结果：44     

4、转换函数

select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) from dual;

select to_date(‘2011-12-01 10:33:56’, ‘yyyy-mm-dd hh24:mi:ss’) from dual;

select to_number(‘1000’) from dual;

5、混合函数

nvl(string1, replace_with)功能：如果string1为null，则nvl函数返回replace_with的值，否则返回string1的值。 　　

注意事项：string1和replace_with必须为同一数据类型，除非显示的使用to_char函数。

 

nvl2(e1, e2, e3)的功能为：如果e1为null，则函数返回e3，否则返回e2。

 

nullif(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(null)，否则返回第一个值。

eg、select nullif(‘a’, ‘a’) from dual;

输出结果：返回空，无值

select nullif(‘a’, ‘1’) from dual;

输出结果：a

 

6、分组函数

avg(n)、min(n)、max(n)、sun(n)、count(n)

八、oracle多表查询

 

多表联合查询

通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。

                

两个表连接有四种连接方式：

* 相等连接

* 不等连接(看作单表查询)

* 外连接(左连接、右连接；左连接即左表全部显示，右表只显示匹配的信息，右连接反之。)

* 自连接(自关联)

        

1．相等连接

通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中    

显示雇员名称和所在部门的编号和名称。

执行以下查询：

SELECT a.ename, b.id, b.dname

FROM employee a,dept b  

WHERE a.id=b.id

说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，用“.”分隔，表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。

以上训练中，不在两个表中同时出现的列，前面的表名前缀可以省略。所以以上例子可以简化为如下的表示：

SELECT *

FROM emp e, dept d

WHERE e.deptno=d.deptno;

   

2．外连接

在以上的例子中，相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。

为了解决这个问题可以用外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同，这里用如下的例子予以说明。

     

使用外连显示不满足相等条件的记录。

显示雇员名称和所在部门的编号和名称。

执行以下查询：

左连接方法一(推荐使用，简洁)：

–+号(附表)对面的是主表，可以理解为主表全部显示，+号这边如果没有匹配就显示空值

—不管dept是否存在，emp都会显示(emp当主表)

SELECT * FROM emp e, dept d WHERE d.deptno(+)=e.deptno; —左连接

SELECT * FROM emp e, dept d WHERE e.deptno=d.deptno(+); —右连接

—不管emp是否存在，dept都会显示(dept当主表)

SELECT * FROM emp e, dept d WHERE e.deptno(+)=d.deptno; —左连接

SELECT * FROM emp e, dept d WHERE d.deptno=e.deptno(+); —右连接

 

左连接方法二：

–不管dept是否存在，employee都会显示

SELECT * FROM emp e LEFT JOIN dept d  ON d.deptno=e.deptno;

SELECT * FROM dept d RIGHT JOIN emp e ON d.deptno=e.deptno;

   

3、自连接(一般用在树形权限结构中)

自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。

SELECT worker.ename||’ 的经理是 ‘||manager.ename AS 雇员经理  

FROM employee worker, employee manager 

WHERE worker.mgr = manager.empno;

       

————

执行结果为：

1.SMITH 的经理是 FORD 

2.ALLEN 的经理是 BLAKE 

3.WARD 的经理是 BLAKE 

 

  

注：在操作多表联合查询时，若出现以下情况，将形成笛卡尔积

– 联接条件被省略

– 联接条件无效

– 第一个表中的所有行被联接到第二个表中的所有行上

为了避免笛卡尔积，请始终包括有效的联接条件

    

何为笛卡尔积？

笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。

 

九、集合连接

十、扩展

1、cmd控制台登录oracle数据库：sqlplus scott/oracle@orcl

 

2、获取当前时间

–> select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’) from dual;

输出结果值：2011-11-30 19:40:55

 

获取当前时间(小数点精确到后面6位,6是可变的)

–>select to_char(systimestamp, ‘yyyy-mm-dd hh24:mi:ssxff6’) from dual;

输出结果值：2011-11-30 19:45:35.791000

 

3、查看表结构：desc dept;

 

4、修改日期格式：alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;(注意：只在当前会话中生效)

 

5、两种插入空值方式

方法一：insert into tb_stu values(1, null, to_date(‘20111130’, ‘yyyymmdd’));

方法二：insert into tb_stu(id, birthday) values(1, to_date(‘20111130’, ‘yyyymmdd’));

 

6、清屏：clear screen;

 

7、oracle中两个单引号表示一个单引号

eg、update tb_stu set pwd=’1”2′ where id=1;

 

8、oracle匹配

eg、select * from tb_stu where pwd like ‘A%’ –匹配多个

select * from tb_stu where pwd like ‘A_’ –匹配一个

 

9、drop table tb_dept purge; –永久删除

   drop table tb_dept; –把表放入回收站，并没有彻底的删除表

  

10、select to_char(33, ‘L99.99’) from dual; –获取本地货币符号，输出结果为：￥33.00

    select to_char(33, ‘$99.99’) from dual; –获取美元

  

11、oracle中为空的两种表达方式

select ” from dual;

select null from dual;

  

12、查询当前数据库中的所有表名

select * from tab;