Oracle学习笔记（1）

说明：没有PL/SQL内容。

Oracle中一个实例，就是一个数据库，所以没有对应的show databases语句。

Oracle数据库的基本单元：

表空间（Tablespace）

数据文件（DataFiles）表空间物理上由一个或多个数据文件组成。

段（Segment)        多个数据块组成的区间，多个区间组成了段。

区间（Extents）  区间由多个数据块组成，一个Extent总是属于一个表空间的一个数据文件。

数据块（Blocks） 数据块是Oracle的最小存储单位，一般为8K，实际的数据是存储在数据块中。

表空间是逻辑概念，各种对象都存放在表空间上。

根据表用途分成几种：

Permenent Tablespace存放数据

Undo Tablespace存放回滚数据

Temporary Tablespace存放临时数据

著名的表空间：SYSTEM、SYSAUX、TEMP、Users、Example

在Oracle里，只有表空间，表空间里有表。

//创建临时表空间

create temporary tablespace user_temp

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'

size 50m

autoextend on

next 20m maxsize 500m

extent management local;

//创建数据表空间

create tablespace test_data

logging

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

//创建用户并指定表空间

create user username identified by password

default tablespace user_data

temporary tablespace user_temp;

//给用户授予权限（角色）

grant connect,resource to username;

//转换用户

conn username/password

//创建表（类似与sql，数据类型有些变化）

create table user(

       id number primary key,

       …

)

//插入数据（和sql一样）

insert into table values (…)

角色、权限和用户

比如：具有create session权限才能连接到数据库上，只有具有create table权限再能建表。

角色是指多个权限的集合，因为在给用户设置权限时，一个一个分配权限，太过于麻烦，可以通过将多个权限分配给角色，然后将角色直接分配给用户。

权限：系统权限：用户对数据库的相关权限。

  对象权限：用户对其他用户的数据对象（如：表，视图，存储过程…）操作的权限。

角色：预定义角色、自定义角色。

sys和system是用户，sysdba和sysoper是权限。

===============================================================

基本命令

conn[ect] 用户名/@网络服务名 [as sysdba/sysoper]

disc[onnect]

passw[ord]

show user

start和@

edit

spool

exit

Oracle基本命令

set linesize 300;    设置行高

set pagesize 50;     设置每页显示的数目

conn system/manager as sysdba;   以system登陆

show user;           显示当前的用户

select * from tab;   显示当前数据库所有的表

desc 表名;            显示表的结构

clear scr;           清除屏幕

/                    上次查询

====================

用户管理

创建用户

新用户的创建命令create user，具有dba权限才能使用。

create user 用户名 identified by 密码

实例：create user ruan identified by ruan012；

修改密码

给自己修改直接用：

password [用户名]

具有dba权限，或拥有alter user的系统权限，可以修改别人密码：

实例：create user ruan identified by ruan012；

删除用户：

drop user 用户名 [cascade]

删除用户时，如果该用户创建了表，删除时就得带上参数cascade。

创建的新用户是没有任何权限的，需要为其指定相应的权限。

给一个用户赋权限使用命令grant，回收权限用命令revoke。

能授权的用户下：grant connect to ruan;

grant select on emp to ruan;

其中对象权限有：select、update、delete、alert、all……

ruan用户下：select * from scott.emp;

方案：是该用户下所有对象的集合。默认情况下方案名就是用户名。如上面句子的：scott。

授权的用户收回权限：revoke select on emp from ruan

对权限的维护

权限的传递：

——如果是对象权限，就在后面加with grant option

如：grant select on emp to ruan with grant option;

——如果是系统权限

如：grant connect to ruan with admin option;

使用profile管理用户口令

概述：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile。当建立用户没有指定profile选项，那oracle就会将default分配给用户。

（1）账户锁定

指定用户登录时最多可以输入密码的次数，也可以指定用户锁定的时间（天），一般用dba的省份去执行该命令。

实例：create profile aaaa limit failed_login_attempts 3 password_lock_time 2;

赋给哪个用户：alter user ruan profile aaaa;

（2）给用户解锁

alter user ruan account unlock;

 (3)终止口令

使用终止口令可以让用户定期修改密码。

实例：每10天要修改密码，宽限为2天。

create profile aaaa limit password_life_time 10 password_grace_time 2;

alter user ruan profile aaaa;

另：password_reuse_time 指定口令可重用时间

删除profile

drop profile aaaa [cascade]

====================

表的管理

表名和列的命名规则：

必须以字母开头

长度不超过30字符

不能使用oracle的保留字

只能使用如下字符a-z、A-Z、0-9、￥、#

支持的数据类型：

字符型：

char      定长，最大2000字符

varchar2  变长，最大4000字符

clob      字符型大对象，最大4G

数字型：

number    范围-10的38次方到10的38次方

如 number（5,2）表示一个小数有5位有效数，2位小数

如 number（5）表示一个五位整数

日期类型：

date 包含年月日和时分秒

timestamp 这是oracle9i对date数据类型的扩展

图片：

blob  二进制数据，可以存放图片、声音 4G （一般不用，除非考虑安全性）

注意分号别忘了！

创建表

create table student( --表名

xh number(4), --学号

xm varchar2(20), --姓名

sex char(2), --性别

birthday date, --出生日期

sal number(5,2) --奖学金

);

查看表结构

desc student；

添加一个字段

alter table student add (classid number(2));

（以下实际很少用）

修改字段的类型或是名字（不能有数据，）

alter table student modify (xm char(30))

删除一个字段

alter table student drop column sal;

修改表的名字

rename student to stu；

所有字段都插入

insert into student values (001,'小明','男','1990-12-14',100.00);

注意：

Oracle默认日期格式：

'DD-MON-YY'，如：14-12月-90

改日期默认格式（也只是暂时改，除非改注册表）：

alter session set nls_date_format = 'yyyy-mm-dd';

操作数据库插入带有日期值的时候可以用to_data()函数。

插入部分字段

insert into student(xh,xm,sex) values (002,'Amy','女');

插入空值

insert into student (xh,xm,sex,birthday) values(003,'aa','男',null)

查询：select * from student where birthday is null;

select * from student where birthday is not null;

改一（多）个字段

update student set sex='女' where xh=001;

update student set sex='女'，birthday='1980-12-14' where xh=001;

修改含有null值的数据

参考插入空值，用is null

删除数据

delete from student;

删除所有记录，表结构还在，写日志，可以恢复的，速度慢。

删除数据前，设置保存点：savepoint aa;

恢复数据：rollback to aa;  必须保证在这之前没有提交（commit）；

drop table student;

删除表的结构和数据

delete from student where xh=002;

删除一条记录

truncate table student;

删除表中左右记录，表结构还在，不写日志，无法找回删除的记录，速度快。

====================

表的查询

scott表中的emp表和dept表。

消除重复行用distinct关键字。

如：select discinct sex from student;

注意查询表中数据时，数据内容区分大小写字母。例如：

select * from scott.emp where job='clerk';

会出现找不到内容，使用下面可以找到：

select * from scott.emp where job='CLERK';

为字段取别名（as可以不要）：

如：select ename "名字",job as "职位" from scott.emp;

如何处理null值

使用nvl函数来处理

select ename "名字",job as "职位",sal*12+comm*12 as "年工资" from scott.emp;

这样得到的年工资有的就是空，因为表达式sal*12+comm*12中comm为空值，整个表达式也就是空值。通过nvl函数处理如下：

select ename "名字",job as "职位",sal*12+nvl(comm,0)*12 as "年工资" from scott.emp;

like操作符

%：表示任意0到多个字符

_：表示任意单个字符

例子：select * from scott.emp where ename like 'S*';

in操作符

例子：select * from scott.emp where ename in ('SCOTT','SMITH');

order by（默认升序asc）和desc（降序）

例子：select * from scott.emp order by deptno,sal desc;

另外：通过别名排序：

select ename,job,sal*12 "年工资（不包括奖金）",deptno from scott.emp order by deptno,"年工资（不包括奖金）" desc;

函数：count，max，min，avg，sum

1、分组函数只能出现在选择列表、having、order by子句中。

2、在select语句中同时拥有以下的的应按照group by、having、order by的顺序

3、在选择列如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则出错。

子查询：

select * from scott.emp where sal=(select max(sal) from scott.emp);

select * from scott.emp where sal>=(select avg(sal) from scott.emp);

group by和having

select avg(sal),deptno,job from scott.emp group by deptno,job;

用having进一步进行条件显示：

select avg(sal),deptno from scott.emp group by deptno having avg(sal)>2000;

多表查询

select * from scott.emp,scott.dept

where scott.emp.deptno=scott.dept.deptno;

all、any操作符

单行子查询、多行子查询、多列子查询

Select ename,sal,deptno from emp where sal>

All (select sal from emp where deptno=30);

Select ename,sal,deptno from emp where sal>

Any (select sal from emp where deptno=30);

Select * from emp Where (deptno,job)=(select deptno,job from emp where ename='SMITH');

Oracle分页查询  三种方式

rownum分页
1.rownum分页 
(select * from emp) 
2.显示rownum[oracle分配的] 
select a1.*,rownum rn from (select * from emp )a1; 
3.查询1到10行记录 
select a1.*,rownum rn from (select * from emp )a1 where rownum<=10;  OR 
select * from (select a1.*,rownum rn from (select * from emp )a1 where rownum<=10) ； 
查询6到10行记录 
select * from (select aa.*,rownum rm from(select * from emp order by sal)aa where rownum<=10) where rm>5; 

格式：

SELECT x.* from (   
     SELECT z.*,rownum numbers from(   
          select * from emp order by sal)z   
     where rownum<10) x    
where x.numbers>5 

另外：子查询的效率是非常慢的， 能不用子查询就尽量不要用。 

select * from (select e.*,rownum rn from scott.emp e) a1 where a1.rn between 6 and 10;
4.几个查询变化 
A。指定查询列，只需修改最里层的子查询； 
B。排序，也只需修改最里层的子查询； 
select * from (select a1.*,rownum rn from (select ename,sal from emp )a1 where rownum<=10) where rn=>6; 
select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc)a1 where rownum<=10) where rn=>6; 

分页三种方式：
1 要把ROWID来分 
 select * from t_xiaoxi where rowid in(select rid from select rownum rn,rid from (select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
2 按分析函数来分 
 select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t )where rk<100000 and rk>9980; 
3 按rownum来分。 
 select * from (select t.* rownum rn from (select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980; 
效率1》3》2 （1比较麻烦，2效率很低）

其他：当使用values子句时，一次只能插入一条数据。当使用子查询插入语句是，一条insert可以插入大量数据。当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。   还有update也可以。