Oracle Class1. Oracle 入门(rdbms结构)
------------------------2013-5-4------------------------
create user "tempuser2" profile "DEFAULT"
identified by "tempuser2" default tablespace "USERS"
account unlock;
--profile "DEFAULT" 是区分大小写的。
default tablespace "USERS" 是区分大小写的。
create user "TEMPUSER" profile "DEFAULT"
identified by "TEMPUSER" default tablespace "USERS"
account unlock;
--全部大写，否则创建的用户名是："tempuser2" !!!
grant create any table to "tempuser2" with admin option;
grant "CONNECT" to "tempuser2" with admin option; "CONNECT"大写
grant create any table to "TEMPUSER" with admin option;
grant "CONNECT" to "TEMPUSER" with admin option;
begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name =>'TEMPUSER',
consumer_group =>'DEFAULT_CONSUMER_GROUP',
grant_option=>true
);
end;
begin
dbms_resource_manager.set_initial_consumer_group(
user=>'TEMPUSER',
consumer_group=>'DEFAULT_CONSUMER_GROUP'
);
end;
alter user "TEMPUSER" account lock
alter user "TEMPUSER" identified by "TEMP"
drop user tempuser cascade;
CREATE ROLE "TEMPROLE"
IDENTIFIED BY "temprole"
--角色--
GRANT "CONNECT" TO "TEMPROLE" WITH ADMIN OPTION;
GRANT "DBA" TO "TEMPROLE" WITH ADMIN OPTION;
--系统权限--
GRANT ALTER ANY
INDEX TO "TEMPROLE" WITH ADMIN OPTION
GRANT SELECT ANY TABLE TO "TEMPROLE" WITH ADMIN OPTION
--grant--
BEGIN
dbms_resource_manager_privs.revoke_switch_consumer_group(
revokee_name => 'TEMPROLE',
consumer_group => 'DEFAULT_CONSUMER_GROUP'
);
END;
------------------------2013-5-5------------------------
##角色##
create role "TEMPROLE2" identified by "TEMPROLE2";
grant alter any index to "TEMPROLE2" with admin option;
grant select any table to "TEMPROLE2" with admin option;
grant "CONNECT" to "TEMPROLE2" with admin option;
grant "DBA" to "TEMPROLE2" with admin option;
begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'TEMPROLE2',
consumer_group => 'DEFAULT_CONSUMER_GROUP',
grant_option => false
);
end;
alter role "TEMPROLE2" identified externally;
revoke "DBA" from "TEMPROLE2"; #revoke关键字#
drop role TEMPROLE2;
##profile概要文件##
CREATE PROFILE "TEMPPROFILE"
LIMIT CPU_PER_SESSION 1000 CPU_PER_CALL 1000 CONNECT_TIME 30
IDLE_TIME DEFAULT SESSIONS_PER_USER 10
LOGICAL_READS_PER_SESSION 1000 LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 16K COMPOSITE_LIMIT 10000000
FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 5
PASSWORD_GRACE_TIME 60 PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 30
PASSWORD_VERIFY_FUNCTION DEFAULT
为用户分配概要文件
alter user tempuser profile tempprofile;
概要文件的删除
drop profile tempprofile;
##表空间##
CREATE TABLESPACE "TEMPTABLESPACE"
LOGGING
DATAFILE 'D:\ORACLE\ORADATA\ORA1128\TEMPTABLESPACE.ora' SIZE
5M EXTENT MANAGEMENT LOCAL
创建表空间的语法是：
create tablespace tablespacename
datafile 'filename' [size integer [K|M]]
[autoextend [off|on]];
##数据文件##
ALTER TABLESPACE "CWMLITE"
ADD
DATAFILE 'D:\ORACLE\ORADATA\ORA1128\TEMPDATAFILE.ora' SIZE
5M AUTOEXTEND
ON NEXT 100K MAXSIZE UNLIMITED
##回退段##
回退段是一种特殊类型的数据段，记录着数据库被某个事务操作后的资料的原值，因此回退段里的资料可以用来对数据库进行恢复。
CREATE ROLLBACK SEGMENT "TEMPROLLBACKSEGMENT"
TABLESPACE "TEMPTABLESPACE"
STORAGE ( INITIAL 10K NEXT 10K)
ALTER ROLLBACK SEGMENT "TEMPROLLBACKSEGMENT" ONLINE
D:\oracle\admin\ora1128\pfile\init.ora
###########################################
# 系统管理的撤销和回退段
###########################################
undo_management=AUTO -->manual
undo_retention=10800
undo_tablespace=UNDOTBS
报错：ORA-30019:自动撤消模式中的回退段操作非法。
##重做日志组##
ALTER DATABASE
ADD LOGFILE GROUP 4 ('D:\ORACLE\ORADATA\ORA1128\log4.ora')
SIZE 1024K
SQL
数据查询语言DQL: 查询数据
数据定义语言DDL: 建立，删除和修改数据对象，create alter drop truncate
数据操纵语言DML: 完成数据操纵的命令，包括查询。 insert select delete update
数据控制语言DCL: 控制对数据库的访问，服务器的关闭，启动等。grant revoke
事务处理语言TCL commit savepoint rollback
select * from emp;
select * from dept;
desc dept 查看表结构。
select empno,ename,job from scott.emp;
select distinct job from scott.emp; <===> select all job from scott.emp;
select empno,ename,job from scott.emp where job='MANAGER';
select empno,ename,sal from scott.emp where sal <=2500;
不等于运算符
select empno,ename,job from scott.emp where job ^= 'MANAGER';
select empno,ename,job from scott.emp where job != 'MANAGER';
select empno,ename,job from scott.emp where job <> 'MANAGER';
in(列表以逗号隔开)
not in(列表以逗号隔开)
between and 介于之间
字符型字段也可以比较大小
select empno,ename,job from scott.emp where job > 'MANAGER';
select empno,ename,job from scott.emp where job not in ('MANAGER','CLERK');
select empno,ename,job from scott.emp where job between 'CLERK' and 'MANAGER'
like模式匹配
select empno,ename,job from scott.emp where job like 'M%'
select empno,ename,job from scott.emp where job like 'M__' x
select empno,ename,job from scott.emp where job like 'M______' 代表M开头的长度为7的字符串，并且区分大小写。
is null是否为空
select empno,ename,job from scott.emp where job is null
以上为单条件查询，以下为组合条件的查询。
select empno,ename,job from scott.emp where job > 'CLERK' and sal <= 2000
not job='CLERK' 等价于 job<>'CLERK'
排序
select empno,ename,job,sal from scott.emp where job <= 'CLERK' order by job asc,sal desc
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7369 SMITH CLERK 800
分组查询
select empno,ename,job,sal from scott.emp group by job,empno,ename,sal having sal <= 2000
select job,sum(sal) from scott.emp group by job,sal having sal < 2000;
select job,sum(sal) as sal2 from scott.emp group by job having sal > 5000
*
ERROR 位于第 1 行:
ORA-00979: 不是 GROUP BY 表达式
应该写成分组函数的方式。
select job,sum(sal) from scott.emp group by job; 按job分组，分组统计sal求和。
select job,sum(sal) as sal2 from scott.emp group by job having sum(sal) > 5000; 在以上条件基础上筛选总和大于5000的记录。
对空表记录的查询，同样适合于oracle。
select count(*) from newlifeyhj.t; 为0
select sum(A) from newlifeyhj.t; 为空
顺序求子句的值：
1.where子句，建立候选行。
2.group by子句中指定的组。
3.having子句进一步约束不满足having子句中分组标准结果分组。
avg 平均值
count 计数
max 最大值
min 最小值
stddev 标准差
sum 合计
variance 方差
所有的oracle忽略空值。
min和max用于任何数据类型，avg,sum,variance,stddev函数只能被用于数字。
NVL函数强制组函数包含空值。
分组数据: GROUP BY 子句语法
可以使用GROUP BY 子句将表中的数据分成若干组
在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
SQL> select employee_id,avg(salary) from employees group by employee_id;
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中，
否则会报错：第 1 行出现错误: ORA-00979: 不是 GROUP BY 表达式 注意：
不能在 WHERE 子句中使用组函数（注意）。
可以在 HAVING 子句中使用组函数。
过滤分组：
使用 HAVING 过滤分组:
1.行已经被分组。
2.使用了组函数。
3.满足HAVING 子句中条件的分组将被显示
select department_id,max(salary) from employees group by department_id having max(salary)>2000
##带where条件##
--查询全部的sal条件 8行
select job,sal as sal2 from scott.emp where sal <= 2000;
--查询满足job,sal记录分组，7行， SALESMAN 1250 两条重复的记录。
select job,sal as sal2 from scott.emp where sal <= 2000 group by job,sal;
--查询满足job,sal记录分组，7行， sum(SALESMAN 1250) 两条重复的记录。 ==>求和，SALESMAN 2500
select job,sum(sal) as sal2 from scott.emp where sal <= 2000 group by job,sal;
--注释,根据单一job分组求和sal
select job,sum(sal) as sal2 from scott.emp where sal <= 2000 group by job;
字段运算查询+ - * /
select empno,ename,sal,mgr,sal+mgr,sal+100 from scott.emp;
select empno 编号,ename 姓名,job 工作,sal 薪水,mgr,sal+mgr,sal+100 from scott.emp;
##多表查询##
#无条件多表查询#
select emp.empno,emp.ename,emp.deptno from scott.emp; 14行
select dept.dname,dept.loc from scott.dept; 5行
select emp.empno,emp.ename,emp.deptno,dept.dname,dept.loc from scott.emp,scott.dept; 14*5=70行
无条件多表查询是将各表的记录以"笛卡尔"积的方式组合起来。
#等值多表查询#
select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.dname,dept.loc from scott.emp,scott.dept
where scott.emp.deptno=scott.dept.deptno
#非等值多表查询#
select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.dname,dept.loc from scott.emp,scott.dept
where scott.emp.deptno!=scott.dept.deptno and scott.emp.deptno=10
##SQL嵌套查询##
内嵌的select语句称为子查询，子查询形成的结果又成为父查询的条件。
select sal from scott.emp where ename='WARD'; ==> sal=1250
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >= (select sal from scott.emp where ename='WARD')
#关键字in not in#
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal in (select sal from scott.emp where ename='WARD')
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal not in (select sal from scott.emp where ename='WARD')
select sal from scott.emp where job = 'MANAGER';
#any关键字# 等价于where sal > 2975 or sal > 2850 or sal > 2450
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal > any(select sal from scott.emp where job = 'MANAGER')
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal > 2975
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal > 2850
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal > 2450
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal > 2975 or sal > 2850 or sal > 2450
#some关键字# 等价于where sal = 2975 or sal = 2850 or sal = 2450
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal = some(select sal from scott.emp where job = 'MANAGER');
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal = 2975 or sal = 2850 or sal = 2450;
#all关键字#
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal > all(select sal from scott.emp where job = 'MANAGER');
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal > 2975 and sal > 2850 and sal > 2450;
#exists关键字#
select emp.empno,emp.ename,emp.job,emp.sal from scott.emp,scott.dept
where exists
(select * from scott.emp where scott.emp.deptno = scott.dept.deptno);
#union关键字#并操作
select deptno from scott.emp
union
select deptno from scott.dept;
#intersect关键字#交操作
select deptno from scott.emp
intersect
select deptno from scott.dept;
#minus关键字#差操作,属于集合A且不属于集合B的元素总合就是差集。
select deptno from scott.emp
minus
select deptno from scott.dept;
要求属性具有相同的定义，包括类型和取值范围。
##SQL函数查询##
#ceil函数#取大于等于数值n的最小整数
select mgr,mgr/100,ceil(mgr/100) from scott.emp;
#floor函数#取小于等于数值n的最大整数
select mgr,mgr/100,floor(mgr/100) from scott.emp;
#mod函数# mod(m,n), 取m整除n后的余数。
select mgr,mod(mgr,1000),mod(mgr,100),mod(mgr,10) from scott.emp;
==>p21
------------------------2013-5-6------------------------
#power函数#[数]幂,power(m,n),取m的n次方。
select mgr,power(mgr,2),power(mgr,3) from scott.emp;
#round函数# round(m,n)，四舍五入，保留n位。
select mgr,round(mgr/100,2),round(mgr/1000,2) from scott.emp;
#sign函数# sign(n),n>0,取1; n=0,取0; n<0,取-1
select mgr,mgr-7800,sign(mgr-7800) from scott.emp;
#avg函数# 求平均值，要求字段为数值型。
select avg(mgr) 平均薪水 from scott.emp;
#count函数# count(字段名)或count(*),统计总数。
select count(*) 记录总数 from scott.emp;
select job,count(distinct job) 工作类别总数 from scott.emp group by job;
select count(distinct job) 工作类别总数 from scott.emp;
#min函数# min(字段名),计算数值型字段最小值。
select min(sal) 最少薪水 from scott.emp;
#max函数# max(字段名),计算数值型字段最大值。
select max(sal) 最高薪水 from scott.emp;
#sum函数# 计算数值型字段总和。
select sum(sal) 薪水求和 from scott.emp;
##SQL录入数据##
#单行记录录入#
7900 JAMES 03-12月-81
insert into scott.emp(empno,ename,hiredate)values(7999,'JONE','25-11月-2002');
select empno,ename,hiredate from scott.emp;
#多行记录的录入#
select empno+100,ename,hiredate from scott.emp where empno >= 6999
insert into scott.emp(empno,ename,hiredate)
(select empno+100,ename,hiredate from scott.emp where empno >= 6999)
#表间数据复制#
select distinct empno,ename,hiredate
from scott.emp
where empno >= 7000
create table scott.test --利用现有的表创建表。
as(
select distinct empno,ename,hiredate
from scott.emp
where empno >= 7000
); //分三步执行的，首先查询符合要求的数据，其次是建立3个字段的名为test数据库空表，最后将查询的数据插入到test数据表中。
select * from scott.test;
desc scott.test;
create table test2
as
select * from scott.test where 1 <> 2; --条件不成立，仅仅复制的是表结构，而不是表数据。
create table scott.test2 --注意表名前面的前缀。
as
select * from scott.test where 1 <> 2;
日期数据类型默认格式为 DD-MON-RR
使用日期的默认格式
使用TO_DATE函数转换 TO_DATE('2005-10-18','YYYY-MM-DD')
select * from scott.test;
##SQL删除数据##
delete from scott.test where empno >= 7500 and empno <= 8000;
delete from scott.test
truncate table scott.test; --表已截掉。
truncate table命令将快速删除数据表中的所有记录，但保留数据表结构。这种快速删除与delete from数据表的删除全部数据表记录不一样，
delete命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复。
而truncate命令删除的数据是不可以恢复的。
--insert into scott.test(empno,ename,hiredate)values(7999,'JONE','25-11月-2002');
--delete from scott.test;
--rollback;
select * from scott.test;
事务例子sql
select * from scott.test2 where 1 = 1;
--insert into scott.test2(empno,ename,hiredate)values(8881,'STEVE',TO_DATE('2013-5-6','YYYY-MM-DD'));
update scott.test2 set ename='STEVE2' where empno = 8881;
savepoint mark1;
delete from scott.test2 where empno = 8881;
--delete from scott.test2 where empno = 8881;
--truncate table scott.test2; --测试truncate是否支持回滚。
savepoint mark2;
rollback to savepoint mark1;
commit;
select * from scott.test2 where 1 = 1;
##SQL更新数据##
#直接赋值更新#
select empno,ename,hiredate from scott.emp where empno=7999;
update scott.emp set empno=8888,ename='TOM',hiredate='03-9月-2002' where empno=7999;
select empno,ename,hiredate from scott.emp where empno=8888;
#嵌套更新#
update scott.emp set sal = 600 where empno=8888;
select empno,sal from scott.emp where empno=8888;
select sal+300 from scott.emp where empno=8888;
update scott.emp
set sal = (select sal+300 from scott.emp where empno=8888)
where empno = 8888;
安装9i
ping 10.0.6.57 -t 网络
数据库服务器的体系结构
进程结构
1.用户进程，客户机内存上运行的程序，如SQL Plus，企业管理器。
2.服务器进程，Oracle 9i的主要后台支持进程。
系统监控进程(smon) x
进程监控进程(pmon) x
数据库写入进程(dbwr) x
日志写入进程(lgwr) x
归档进程(arch) x
检查点进程(ckpt) x
恢复进程(reco)
锁进程(LCKn)
快照进程(SNPn)
调度进程(Dnnn) --服务进程和用户进程--
内存结构
1.系统全局区SGA
2.程序全局区PGA
PGA是数据库服务器内存中为单个用户进程分配的专用的内存区域，是用户进程私有的，不能共享。
数据库的逻辑结构
1.数据块 Data Block
2.数据区间 Data Extent
3.数据段 Data Segment 数据段 索引段 临时段 回滚段
4.逻辑对象 Logic Object 表 索引 视图
5.表空间 Tablespace
6.数据库 Database
模式
模式是对用户所创建的数据库对象的总称。
模式对象包括表、视图、索引、同义词、序列、过程和程序包等。
oracle 9i安装完毕后自动建立9个默认的表空间。
cwmlite 用于联机分析处理olap
drsys 用于存放与工作空间设置有关的信息
example 实例表空间，存放实例信息。
index 索引表空间，存放数据库索引信息。
system 系统表空间，存放表空间名称、所含数据文件等管理信息。
temp 临时表空间，存储临时表。
tools 工具表空间，存放数据库工具软件所需的数据库对象。
undotbs 回滚表空间，存放数据库恢复信息。
users 用户表空间，存放用户私有信息。
1.物理块
2.物理文件
数据文件：用于存放所有的数据，以DBF为扩展名。 存储数据库数据，如表，索引数据等。 （数据库文件或数据文件）
日志文件：记录了对数据库进行的所有操作，以LOG为扩展名。 对数据库所有修改信息，用于故障恢复。（恢复日志文件）
控制文件：记录了数据库所有文件的控制信息，以CTL为扩展名。 记录数据库物理结构的二进制文件。 （控制文件）
D:\oracle\oradata\ora1128
数据库以下面两种模式运行
NOARCHIVELOG MODE
ARCHIVELOG MODE
to_date('17-12-1980','dd-mm-yyyy')
show sga;
show parameter sga;
show parameter db;
show parameter pga;
show parameter log_buffer;
select * from v$bgprocess where paddr <> '00';
select * from v$controlfile;
select * from v$datafile;
select * from v$logfile;
interval函数用法：
--日期加上1天
select trunc(sysdate) + interval '1' day from dual;
--月份加上1月
select trunc(sysdate) + interval '1' month from dual;
--年份加上1年
select trunc(sysdate) + interval '1' year from dual;
--查询系统时间
select sysdate from dual;
--trunc函数
select trunc(sysdate) from dual;
select trunc(sysdate) + interval '99' day from dual;
select trunc(sysdate) + interval '101' day from dual;
--ERROR 位于第 1 行:
ORA-01873: 间隔的前导精度太小
select TO_CHAR(SYSDATE + INTERVAL '100' DAY(3), 'YYYYMMDD') from dual;
select
TO_CHAR(SYSDATE - INTERVAL '3' YEAR, 'YYYYMMDDHH24') ToYEAR,
--=>從系統日期算起往前 3 年
TO_CHAR(SYSDATE - INTERVAL '3' month, 'YYYYMMDDHH24') ToMONTH,
--=>從系統日期算起往前 3 個月
TO_CHAR(SYSDATE - INTERVAL '30' DAY, 'YYYYMMDDHH24') ToDay,
--=>從系統日期算起往前 30 天
TO_CHAR(SYSDATE - INTERVAL '30' HOUR, 'YYYYMMDDHH24') ToHOUR,
--=>從系統日期算起往前 30 小時
TO_CHAR(SYSDATE - INTERVAL '30' minute, 'YYYYMMDD HH24:MI:SS') ToMinute,
--=>從系統日期算起往前 30 分鍾
TO_CHAR(SYSDATE - INTERVAL '50' second,'YYYYMMDD HH24:MI:SS') ToSecond,
--=>從系統日期算起往前 50 秒
SYSDATE
--系統日期
from dual;
--http://pramaire.pixnet.net/blog/post/7619355-oracle-%E6%97%A5%E6%9C%9F%E9%81%8B%E7%AE%97-%5B%2B--interval%5D
Oracle数据库的主要特点:
1. 支持多用户,大事务量的事务处理.
2. 数据安全性和完整性控制.
3. 支持分布式数据处理.
4. 可移植性.
授予用户MARTIN操作TEST表对象的权限
grant select on test to martin; --允许用户查询test表的记录
grant update on test to martin; --允许用户更新test表的记录
grant all on test to martin; --允许用户插入,删除,更新和查询test表中的记录
grant select,update on test to martin with grant option; --表的多个权限的赋予,管理权限。
grant update(qty_hand,re_level) on test to martin; --控制力度在修改表中的字段。
revoke select,update on test from martin; --移除权限。
RAW
(raw image format)原始图象数据存储格式。
##Oracle数据类型##
字符数据类型，char(1-2000) varchar2(1-4000) long(可变长度字符数据，最多能存储2GB)
数值数据类型的声明语法：可以存储整数，浮点数和实数。最高精度为38位。
number(p[,s]),p表示精度，s表示小数点的位数。
日期类型：date,timestamp
raw: 存储二进制数据，最多能存储2000字节，
long raw: 存储可变长度的二进制数据，最多能存储2GB
lob:大对象数据类型，存储达4GB的非结构化信息，如声音剪辑和视频文件。允许高效，随机，分段的访问。
clob: character lob 字符lob 能够存储大量字符数据。
blob: binary lob 二进制lob,可以存储较大的二进制对象，如图形，视频剪辑和声音文件。
bfile: binary file 二进制文件，用于将二进制数据存储在数据库外部的操作系统文件中。
oracle伪列：rowid(表中行的存储地址),rownum(查询返回的结果集中行的序号，可以用它来限制查询返回的行数。)
Oracle服务器由Oracle数据库（逻辑单元）和Oracle实例（管理数据库的后台进程和内存结构）组成。
SGA内存结构（共享池，数据缓冲区，日志缓冲区）
使用表空间：为什么使用？
在大型商场中收款机众多，同时访问进程很多，经常达到50-100个进程同时访问，这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里。
（多个用户表空间放在不同的物理磁盘上），减少了用户之间的I/O竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物理磁盘上）
