Orcale详细教程1

 1.

-----------------------------------------------------------------------------------------------------------------------------------------

select * from v$controlfile;
select * from v$datafile;
select * from v$logfile;
select * from v$bgprocess; 查看后台进程

archive log list;--显示当前redo日志信息
SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            d:\oracle\ora920\RDBMS
最早的概要日志序列     0
当前日志序列           1
alter system switch logfile ;--强制系统日志切换
--Oracle 关闭
shutdown normal|force|immediate

--Oracle启动
--Startup命令分为如下步骤：
Startup nomount;--启动输数据处于nomount状态，读取初始化参数文件
Alter database mount;--启动数据库处于mount状态，加载control files
Alter database open；--启动数据库处于open状态，进行数据文件与日志文件的验证并加载
--以上3个步骤等同于：startup

--创建用户
create user stephen
identified by stephen
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

--授权
grant connect to stephen
grant resource to stephen

 

 

--创建表空间
create tablespace Hahadata
datafile 'o:\haha.ora'  size 5M
extent management local;

--创建用户，使用hahadata表空间
create user haha
identified by a default tablespace hahadata
account unlock;

--分配权限
grant connect to haha;--连接权限
grant resource to haha;--存储权限
grant dba to haha;--dba权限

--删除用户
drop user haha cascade;

--删除表空间及文件
DROP TABLESPACE hahadata
INCLUDING CONTENTS AND DATAFILES;

 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2.

--create table aa
create table aa(stid int);
--insert data
declare
begin
insert into aa values(1);
insert into aa values(2);
insert into aa values(3);
insert into aa values(4);
end;
--select from aa
select * from aa;
--create table bb
create  table bb (stid int);
--insert data
declare
begin
insert into bb values(2);
insert into bb values(4);
insert into bb values(6);
insert into bb values(8);
end;
--select bb
select * from bb;
--commit
commit;
--use union
select stid from aa
union
select stid from bb;
--use union all
select stid from aa
union all
select stid from bb;
--use intersect
select stid from aa
intersect
select stid from bb;
--use minus
select stid from aa
minus
select stid from bb;
--use ||
select to_char(stid)||','||stname||','||sex as stinfo from st;

--use add_months
insert into bb value(2);
select ord_date,add_Months(ord_date,2) from sales;

--use months_between
select months_between(to_date('2006-06-06','yyyy-mm-dd'),
to_date('2006-06-06','yyyy-mm-dd')) from dual;

select months_between(to_date('2006-06-06','yyyy-mm-dd'),
to_date('2006-03-06','yyyy-mm-dd')) from dual;

select months_between(to_date('2006-06-06','yyyy-mm-dd'),
to_date('2006-09-06','yyyy-mm-dd')) from dual;

select months_between(to_date('2006-06-06','yyyy-mm-dd'),
to_date('2006-06-09','yyyy-mm-dd')) from dual;

--use last_day
select sysdate,last_day(sysdate) from dual;

--decode
select decode('aaa','aaa','ddd') from dual;
select decode('aaa','aa','ddd') from dual;

--nvl
select title_id,nvl(price,0) price  from titles

--row_number
select jobs.*,
row_number() over(order by max_lvl desc) as orders
from jobs;
--rank
select jobs.*,
rank() over  ( order by max_lvl desc) 排名
from jobs
--dense_rank
select jobs.*,
dense_rank() over  ( order by max_lvl desc) 排名
from jobs

 

--创建表空间
create tablespace myspace
datafile 'd:\aa.dbf' size 5M
extent management dictionary
default storage(
initial 100k
next 100k
pctincrease 10)
;
create tablespace myspace
datafile 'd:aa.dbf' size 5M
extent management local
uniform size 1M
;

-- 可以象查询表中的列一样查询ROWID伪列：

SELECT ROWID, ENAME FROM EMP;
SELECT ROWID, T.* FROM EMP T;

-- 下面的语句通过ROWID来定位行：
-- 定义一个变量来保存ROWID:
VARIABLE SROWID VARCHAR2(20)
-- 保存一个行的ROWID:
EXEC  SELECT ROWID INTO :SROWID FROM EMP WHERE EMPNO=7788;

-- 根据ROWID定位行：
SELECT * FROM EMP WHERE ROWID=:SROWID;

-- ROWNUM伪列返回行在结果集中的顺序号：
SELECT ROWNUM, T.* FROM DEPT T;

SELECT ROWNUM, ENAME FROM EMP WHERE JOB='MANAGER';

-- 修改会话的默认日期格式
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

-- 显示日期和时间部分
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

-- 测试效果
select * from order_master;

-- 临时改变一下会话的默认语言, 以识别类似 '12-MAY-05' 的日期格式
alter session set nls_date_language = 'AMERICAN';
-- 改回简体中文语言：
alter session set nls_date_language = 'SIMPLIFIED CHINESE';

-- Oralce 9i还支持使用DATE关键字后紧跟一个'YYYY-MM-DD'的字符串来表示日期值：
SELECT DATE'2005-10-25' FROM DUAL;

-- TOP-N 查询
SELECT * FROM
   (SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL DESC)
   WHERE ROWNUM<5;

-- 显示员工工资排名, 不对 :(
SELECT ENAME, SAL, ROWNUM AS SAL_ORDER
    FROM SCOTT.EMP
    ORDER BY SAL DESC;

-- 显示员工工资排名 :)
SELECT ENAME, SAL, ROWNUM AS SAL_ORDER FROM
       (SELECT ENAME, SAL FROM SCOTT.EMP ORDER BY SAL DESC);
--分页（作业）
select * from (select rownum rid,t.*
               from (select *
                     from emp
                     order by sal desc) t
               where rownum<10)
         where rid>5

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

3.

-- 可以通过以下试验来测试Oracle环境中的死锁：

-- 1. 创建两个表：
CREATE TABLE A AS SELECT 1 X FROM DUAL;
CREATE TABLE B AS SELECT 1 X FROM DUAL;

-- 2. 打开SQL*Plus，更新表A：
UPDATE A SET X = X+1;

-- 3. 打开另一个SQL*Plus，更新表B：
UPDATE B SET X = X+1;

-- 4. 现在在相同的SQL*Plus会话中，更新表A：
UPDATE A SET X = X+1;

-- 由于第一个会话已锁定此行，SQL提示符不能返回，但这不是死锁，只是阻塞。

-- 5. 现在回到第一个SQL*Plus会话中，更新表B：
UPDATE B SET X = X+1;

CREATE TABLE Sales_Details
(
  Prod_Id VARCHAR2 (5),
  Sale_Date DATE NOT NULL,
  Cost NUMBER
)
PARTITION BY RANGE (Sale_Date)
(
  PARTITION sd1 VALUES LESS THAN (TO_DATE('01/4月/2001', 'DD/MON/YYYY')) tablespace tp1,
  PARTITION sd2 VALUES LESS THAN (TO_DATE('01/7月/2001', 'DD/MON/YYYY')) tablespace tp2,
  PARTITION sd3 VALUES LESS THAN (TO_DATE('01/9月/2001', 'DD/MON/YYYY')) tablespace tp3,
  PARTITION sd4 VALUES LESS THAN (TO_DATE('01/1月/2002', 'DD/MON/YYYY')) tablespace tp4
);

CREATE TABLE EmpDetail
(
EmpID VARCHAR2 (5),
EmpName VARCHAR2 (15),
Department VARCHAR2 (10)
)
PARTITION BY HASH (EmpID)
(
PARTITION Eid1 tablespace tp1,
PARTITION Eid2 tablespace tp2,
PARTITION Eid3 tablespace tp3
);

CREATE TABLE DEPARTMENT
(
DepID varchar2 (5),
Dept_Name varchar2 (20)
)
PARTITION BY LIST (Dept_Name)
(
Partition D1 values ('会计部') tablespace tp1,
Partition D2 values ('管理层') tablespace tp2,
Partition D3 values ('人力资源部') tablespace tp3
);

CREATE TABLE BranchDetails1
(
Branch_ID varchar2 (5) not null,
Branch_name varchar2(10) not null,
Address varchar2(20)
)
PARTITION BY RANGE (Branch_Id)
SUBPARTITION BY HASH (Branch_name)
SUBPARTITIONS 6
(
PARTITION S1 values LESS THAN ('B005'),
PARTITION S2 VALUES LESS THAN ('B010'),
PARTITION S3 VALUES LESS THAN ('B015'),
PARTITION S4 VALUES LESS THAN ('B020')
);

CREATE TABLE BranchDetails2
(
Branch_ID varchar2 (5) not null,
Branch_name varchar2(10) not null,
Address varchar2(20)
)
PARTITION BY RANGE (Branch_Id)
SUBPARTITION BY HASH (Branch_name)
SUBPARTITIONS 2
(
PARTITION S1 values LESS THAN ('B005')
(SUBPARTITION sd1,SUBPARTITION sd2),
PARTITION S2 VALUES LESS THAN ('B010')
(SUBPARTITION sd3,SUBPARTITION sd4),,
PARTITION S3 VALUES LESS THAN ('B015')
(SUBPARTITION sd5,SUBPARTITION sd6),,
PARTITION S4 VALUES LESS THAN ('B020')
(SUBPARTITION sd7,SUBPARTITION sd8),
);

-----------------------------------------------------------------------------------------------------------------------------------------------

4.

-- 键保留表练习
CREATE OR REPLACE VIEW ven_ord_outj_view AS
SELECT vm.vencode, venname, orderno, odate, ostatus
FROM vendor_master vm, order_master om
WHERE vm.vencode = om.vencode(+);

-- 等价的 SQL 语句
SELECT vm.vencode, venname, orderno, odate, ostatus
FROM vendor_master vm LEFT OUTER JOIN order_master om
ON vm.vencode = om.vencode;

-- 建立主键
alter table ORDER_MASTER
  add constraint pk_ord_master primary key (ORDERNO);
 
alter table VENDOR_MASTER
  add constraint pk_ven_master primary key (VENCODE);

-- 重新编译视图
alter view VEN_ORD_VIEW compile;

-- 查看可更新的列
SELECT * FROM user_updatable_columns WHERE table_name ='VEN_ORD_VIEW';

UPDATE ven_ord_view SET odate = odate+1 WHERE vencode ='V003';

UPDATE ven_ord_view SET venname = 'Michael' WHERE vencode = 'V004';

 

create user tellixu
  identified by "23";
-- Grant/Revoke role privileges
revoke DBA from TELLIXU;
grant resource to tellixu;

conn scott/tiger

grant all on emp to tellixu
conn tellixu/123
select * from scott.emp;

create synonym myemp for scott.emp
select * from myemp;

----create sequence 序列
create sequence mysequence
increment by 2
start with 1
maxvalue 20
cycle
cache 2

select mysequence.nextval from dual
select mysequence.currval from dual

create table mytest
(
stuid number(3),
stuname varchar2(20)
)

insert into mytest(stuid,stuname) values (mysequence.nextval,'tellixu') ;

create view myemp as
select * from emp;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------