oracle学习笔记

oracle安装

    在win7及其以上安装oracle的时候，有时候需要用兼容性来安装（右键-->属性-->兼容性：选择windows XP(Service Pack 2)）。

    平常所说的 Oracle 或 Oracle 数据库指的是 Oracle数据库管理系统。Oracle 数据库管理系统是管理数据库访问的计算机软件(Oracle database manager system)。它由 Oracle 数据库和 Oracle 实例(instance)构成。

    Oracle 数据库: 一个相关的操作系统文件(即存储在计算机硬盘上的文件)集合，这些文件组织在一起,，成为一个逻辑整体， 即为 Oracle 数据库。

   Oracle 用它来存储和管理相关的信息。Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。

    Oracle 实例: 位于物理内存里的数据结构，它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被所有进程访问。

   Oracle 用它们来管理数据库访问.用户如果要存取数据库(也就是硬盘上的文件) 里的数据，必须通过Oracle实例才能实现,，不能直接读取硬盘上的文件。

实际上，Oracle 实例就是平常所说的数据库服务(service) 。

    区别：实例可以操作数据库；在任何时刻一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问（RAC）

    对于oracle安装创建的5项服务，我们一般只需要用上其中的两项就可以（OracleOraDb10g_home1TNSListener和OracleServiceORCL），其他的服务一般禁用就可以了，这两项推荐设置成手动启动，因为自动随着电脑的启动而启动会耗费大量的系统资源。

显示一个表的结构：

desc table

oracle中SQL语句的注释：

--select * from table;

日期格式的处理：

如果查询的格式是日期格式的：

两种处理方法：

第一种： where date = '24-4月-1998'，必须这样写。

第二种：where to_char(date,'yyyy-mm-dd') = '1998-04-24'

这两种写法也都可以参与有大于号和小于号的区间查找。

模糊查询：

where lastName like '_R%'：查询第二个字母是“R”的。

使用escape说明是转义字符

where lastName like '%\_%' escape '\'：查找中间有“_”的。

单行函数：

LOWER()

UPPER()

INITCAP() 首字母大写转换函数，将所有单词的首字母大写。

字符控制函数：

CONCAT('Hellow','World') 结果：HellowWorld:合并两个字符串，合并的个数最多为两个，如果要合并多个就多次使用这个函数。

SUBSTR('HellowWorld ',1,5) 结果：Hellow :从一个字符串的第一个字符开始，取上五个，注意，SQL的下标是用1开始，而不是从0开始。

LENGTH('HellowWorld') 结果：返回字符串的字符个数。

INSTR('HellowWorld',W) 结果：返回W首次出现的位置。

LPAD(salary,10,*) 结果：*****24000。

RPAD(salary,10,*) 结果：24000*****。

TRIM('H' FROM 'HellowWorld') 结果：ellowWorld：去除字符串两边的“H”。

REPLACE('abcd','b','m') 结果：amcd ：将字符串中所有的b替换成m。

数字函数：

ROUND() 四舍五入函数

ROUND(45.926,2) --> 45.93

TRUNC() 截断函数

TRUNC(45.926,2) --> 45.92

MOD() 求余函数

MOD(1600,300) --> 100

日期的数学运算：

在日期上加上或是减去一个数字结果仍为日期

两个日期相减返回日期之间相差的天数，但是注意相加没有意义。

日期函数：

MONTHS_BETWEEN:两个日期之间相差的月数。

ADD_MONTHS:向指定日期中加上若干月数。

NEXT_DAY:指定日期的下一个星期*对应的日期。

LAST_DAY:本月的最后一天。

ROUND:日期四舍五入。

TRUNC:日期截断。

显示数据类型转换：

TO_CHAR():

TO_NUMBER():

TO_DATE():

select to_char(1234567.89,'999,999,999.99')from dual;

通用函数：

NVL(expr1,expr2):当expr1为空的时候显示expr2，当expr1不为空的时候显示expr1。

NVL2(expr1,expr2,expr3):当expr1不为空的时候显示expr2，当expr1为空的时候显示expr3。

NULLIF(expr1,expr2):相等的时候返回NULL，不相等返回expr1。

COALESCE(expr1,expr2,...,exprn)：如果第一个表达式为空，则返回第二个表达式，如果第二个表达式为空，则返回第三个表达式，一次类推。

 

事务控制：

条件表达式：

CASE表达式，DECODE表达式

【实例】

查询部门号为10,20,30的员工信息，若部门号为10，则打印其工资的1.1倍，

若部门号为20，则打印其工资的1.2倍，若部门号为30，则打印其工资的1.3倍，

selectemployee_id,last_name,department_id,case department_id when 10 then salary *1.1

                                                                       when 20 then salary * 1.2

                                                                       else salary * 1.3 end new_sal

from emploryees

where employee_id in (10,20,30)

 

selectemployee_id,last_name,department_id,decode(department_id,10,salary * 1.1

                                                                        20,salary* 1.2

                                                                           salary)new_sal

from emploryees

where employee_id in (10,20,30)

重点练习：

1、打印出“2009年10月1日 9:25:40”格式的当前系统日期和时间。

select to_char(sysdate,'YYYY"年"MM"月"DD"日"HH:mi:SS')

from dual

注意：使用双引号向日期中添加字符

2、格式化数字：1234567.89为1,234,567.89

select to_char(1234567.89,'999,999,999.99')

from dual

3、字符串转换为数字时

  (1)若字符串中没有特殊字符，可以进行隐式转换：

    select '1234567.89'+100

    from dual

  (2)若字符串中有特殊字符，例如'1,234,567.89',则无法进行隐式转换，需要使用to_number()来完成

    select to_number('1,234,567.89','999,999,999.99')+100

    from dual

4、对于日期作为查询条件的条件查询，一般使用to_date()把一个字符串转为日期，这样可以不必关注日期的格式

   select last_name,hrie_date

   from employees

   where hrie_date = to_date('1998-5-23','yyyy-mm-dd')

或  wherero_char(hire_date,'yyyy-mm-dd') = '1998-5-23'

5、查询每个月倒数第二天入职的员工信息。

   select last_name,hire_date

   from employees

   where hire_date = last_day(hire_date) - 1

6、计算公司员工的年薪

   --错误写法：因为空值计算的结果仍然是空值

   select last_name,salary*12*(1+commission_pct) year_sal

   from employees

   --正确写法

   select last_name,salary*12*(1+nvl(commission_pct,0)) year_sal

   from employees

7、显示所有员工的姓名，部门号和部门名称。（左右查询）

   select last_name,e.department_id,department_name

   from employees e,departments d

   where e.department_id = d.department_id(+)

 

truncate table:清空表，这个是不可回滚的清空。对于增（insert）删（delete）改（update）对表的操作是可以回滚的。

在SQL语句中使用&变量指定列值。

&变量放在values字句中。

insert into departments

           (department_id,department_name,location_id)

values     (&department_id,&department_name,&location_id)

约束关键词：

NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

【实例】

create table t_test(

--列级约束

id number(10),

name varchar2(20) constraint test_name_nnnot null,

email varchar2(20),

salary number(10,2) check(salary > 1500and salary < 30000)

department_id number(10),

--表级约束

constraint test_email_uk unique(email),

constraint test_id_uk primary key(id)

)

视图：

创建视图：

视图的数据和基表中的数据是同步的，这也是视图存在的目的。

创建视图：

select view viewTest

as

select...

在创建语句后面加上 with read only表示创建的视图是不可更改的。

简单视图是允许修改的，但是复杂视图是不可修改的。

Top-N分析：

Top-N分析查询一个列中最大或最小的n个值，最大和最小的值得集合是Top-N分析所关心的。

ROWNUM是整个伪列，对ROWNUM只能使用<或<=，而用=，>，>=都将不能返回任何数据。

ROWNUM的数值是根据

select * from table

出来的，后面加上where语句后它的数值就变化了。

具体的分页查询参照cadn日志。

序列：

可供多个用户用来产生唯一数值的数据库对象。

自动提供唯一的数值。

共享对象

主要用于提供主键值。

将序列值装入内存可以提高访问效率。

语句：

create sequence seq

[increment by n] -- 每次增长的数值

[start with n] -- 从哪个值开始

[{maxvalue n | nomaxvalue}]

[{minvalue n | nominvalue}]

[{cycle n | nocycle}] -- 是否需要循环

[{cache n | nocache}] -- 是否缓存登录

序列在使用的时候就像使用对象一样：

seq.nextcal 和 seq.currval

【实例】

insert into emp01

values(emseq.nextval,'cc',3500)

 

alert sequence seq

increment by 1

nocycle

注意：

将序列装入内存中可以提高访问效率

序列在下列情况下出现裂缝：

回滚

系统异常

多个表同时使用同一序列

 

索引

一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中。

索引被删除或是损坏，不会对表产生影响，其影响的只是查询的速度。

索引一旦建立，oracle管理系统会对其进行自动维护，而且由oralce管理系统决定何时使用索引。用户不用再查询语句中指定使用哪个索引。

再删除一个表时，所有基于该表的索引会自动删除。

通过指针加速oracle服务器的查询速度。

通过快速定位数据的方法，减少磁盘I/O。

 

创建索引：

自动创建：在定义primary key或unique约束后系统自动在相应的列上创建唯一性索引。

手动创建：用户可以在其他列上创建非唯一性的索引，以加快查询。

 

create index inx

on table(colum[,colum]...)

什么时候创建索引

列中数据值分布范围很广

列经常where子句或链接条件中出现

表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%。

 

同义词-synonym（了解）

使用同义词访问相同的对象：

方便访问其他用户的对象

缩短对象名字的长度

create [public] synonym syn

for object;

 

SET操作符：

将多个查询结果用set操作符链接组成一个新的查询。

union/union all

intersect

minus

 

在select列表中的列名和表达式在数量和数据类型上要相对应。

【实例】

select employ_idemp_id,department_id,to_char(null)

from employees01

union

select to_number(null),department_id,department_name

from departments