oracle 数据库

数据库二种人:
DBA: 数据库管理员[安装oracle . ]  tiger 中国人  中国移动..业务数据库..   23省;
开发: 查询，视图，存储过程..
oracle
oracle  oracle[甲骨文]
ms      sql server
im      informax
ibm     db2
开源    mysql

SQL [标准化查询语言]
所有数据库，大部分，支持标准SQL ORACLE MYSQL SQL SERVER
数据库软件，功能存储大量数据[可以，字符，数据，图片，视频..]

1:数据库，优点，存储非常大；
2:最查询
114 10000000
oracle 数据库管理软件
可以创建多个数据库;;
默认，创建一个库  
7 8 9 9i 10g XE  10g

数据库[关系型数据库]
关系---表;;
查询当前时间[数据库]
select 查询
sysdate 当前时间
from  关键 后表名
dual  表;;
进入oracle
sqlplus system/root
sqlplus 主机:端口/库名
或者
sqlplus /nolog
conn 用户/密码@主机:端口/库名
查询当前时间
select sysdate from dual;
退出
exit
清屏
host cls;

数据库表;;
几个表;
用户叫 system/root

查询当前用户表名;;;
select table_name from user_tables;
刚 ORACLE  三个
system
sys
hr  默认情况，锁定..
解锁;system
alter user hr account unlock;
sqlplus hr/hr 
hr
新口令hr
确认hr

DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

EMP      雇员表
Dept     部门表
salgrade 工资等级别表
bonus    奖金表

由于黑框，小，窗口，宽...内容小,,,
oracle
设置行长..
set linesize 200;
//* 所有列
select * from emp;
empno   雇员编号
ename   名子
job     职务
[clerk 文员;salesman 销售员;manager经理;president主管;analyst分析员]
mgr     管理者id
hiredate 入职时间
sal     工资
comm    奖金
deptno  部门编号

基本SQL SELECT语句

查询几个列..
select empno,ename from emp;
可以查询加一些 + - * / "针对数字和日期;;
select empno,ename, sal * 12 from emp;
日期加减
select ename,hiredate - 365 from emp;
原因comm 空 null  任何值与null  结果  null
select ename,sal*12,job ,(sal*12)+(comm*12) from emp;
列的别名
select ename  AS  "员工姓名"   ,job AS "职务",(sal*12)+(comm*12)  AS "年薪" from emp;
select ename  "员工姓名"   ,job "职务",(sal*12)+(comm*12)  "年薪" from emp;
|| 连接
select  ename||job from emp;
出重复行
select distinct deptno from emp;
显示表结构;
desc emp;

下面的语句是否可以执行成功
select ename , job , sal as salary from emp;
下面的语句是否可以执行成功
select  *  from emp;
找出下面语句中的错误
select empno , ename
     sal * 12  ANNUAL  SALARY
from emp;
显示表dept的结构，并查询其中的全部数据
显示出表emp中的不重复的岗位job
select distinct job from emp;
连接表emp的全部列，各个列之间用逗号连接，列头显示成OUT_PUT(提示:使用连接符||)
select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno as OUT_PUT from emp;

过滤
使用WHERE 子句，将不满足条件的行过滤掉。
WHERE 子句紧随 FROM 子句。
> < = <= >= != <>

select * from
emp
where sal > 1000;
SELECT * FROM EMP
where deptno = 20;
select * from emp
where deptno != 30
select * from emp
where deptno <> 30

字符和日期
1:字符和日期要包含在单引号中。
2:字符大小写敏感，日期格式敏感。
3:默认的日期格式是 DD-MON-RR。

错
select * from emp
where ename = 'king';
未选定行
正确
select * from emp
where ename = 'KING'

select * from emp
where hiredate < '11-10月-90'

其它比较运算
BETWEEN  AND  在两值之间 (包含)
IN(set)       匹配列出的值
LIKE          匹配一个字符模式
IS NULL       空值 null

select ename,sal * 1.1
from emp
where sal between 2000 and  3000;

select ename,sal * 0.9
from emp
where sal between  800 and 1000;

涨工资  1.3
10 20
select ename,sal * 1.3 ,deptno
from emp
where deptno = 10 or deptno = 20;

select ename,sal * 1.3,deptno
from emp
where deptno IN (10,20);
查K字母开头员工
select * from emp
where ename like 'K%';
select * from emp
where ename like '____';

select * from emp
order by  sal desc;
过滤和排序数据
查询工资大于1600的员工姓名和工资
select ename,sal from emp
where sal>1600;

查询员工号为7369的员工的姓名和部门号
select ename,deptno
from emp
where empno = 7369;
选择工资不在4000到5000的员工的姓名和工资
select ename,sal from emp
where sal<4000 or sal>5000;
select ename,sal from emp where sal not between 4000 and 5000;
选择雇用时间在1981-2-19到1981-12-3之间的员工姓名，JOB和雇用时间HIREDATE
select ename"员工姓名",job "职务",hiredate "雇用时间"
from emp
where hiredate >'19-2月-81' AND hiredate <'3-12月-81';
select ename,job,hiredate from emp where hiredate between '19-2月-81' and '3-12月-81';
选择在20和30号部门工作的员工姓名和部门号
select * from emp
where deptno IN(20,30);
选择在1987年雇用的员工的姓名和雇用时间
select ename,hiredate from emp where hiredate like '%81';
选择公司中没有管理者的员工姓名及JOB
select ename,job from
emp
where MGR is null;
选择公司中有奖金的员工姓名，工资和奖金级别
select ename,sal,comm from emp where comm is not null;

选择员工姓名的第三个字母是A的员工姓名
select ename from emp
where ename like '__A%';
选择姓名中有字母A和E的员工姓名
select ename from emp where ename like '%A%' and ename like '%E%';
select ename from emp where ename like '%A%E%' or ename like '%E%A%';
单行函数;;
函数 ,,SQL 语言  max() min() count() avg()
oracle 专用... [其它数据库不行]

单行函数:
操作数据对象
接受函数返回一个结果----------
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
Character 字符
General   通用
Conversion 转换
Date       日期
Number      数字

character
LOWER
UPPER
INITCAP
LOWER('SQL Course')
UPPER('SQL Course')
INITCAP('SQL Course')
select * from emp
where lower(ename) = 'james';
CONCAT
SUBSTR
LENGTH
INSTR
LPAD
RPAD
TRIM
REPLACE
CONCAT('Hello', 'World')
SUBSTR('HelloWorld',1,5)
LENGTH('HelloWorld')
INSTR('HelloWorld', 'W')
LPAD(salary,10,'*')
RPAD(salary, 10, '*')
TRIM(‘H’ FROM ‘HelloWorld’)(L,R)
数字

ROUND: 四舍五入
ROUND(45.926, 2)      45.93
TRUNC:截断(保留小数点)
TRUNC(45.926, 2)       45.92
MOD: 求余
MOD(1600, 300)       100

日期
SYSDATE
select sysdate from dual;
//dual 虚表...一行一列..测试
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
select ename,trunc((sysdate-hiredate)/365,0) as "几年" from emp;
LAST_DAY('01-FEB-95')
NEXT_DAY ('01-SEP-95','FRIDAY')
ADD_MONTHS ('11-JAN-94',6)
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
select ADD_MONTHS('6-8月-09',6) from dual;
TO_CHAR 函数对日期的转换
日期-字符
select ename,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp;
数字-字符;
select ename,to_char(sal,'L9999.00') from emp;
select to_number('100.00','999,999.999999')   from emp
SELECT TO_DATE( ‘2006-08-15 星期二','yyyy-mm-dd day') FROM DUAL
单行函数
1:显示系统时间
  select sysdate from dual;
2:查询员工表emp中员工号empno，姓名ename，工资sal，以及工资提高百分之20%后的结果

3:将员工的姓名按首字母排序，并写出姓名的长度（length）;
select ename ,length(ename)  from emp order by ename;

4:查询各员工的姓名ename，并显示出各员工在公司工作的月份数(即:与当前日期比较,该员工已经工作了几个月)。
select ename,round((sysdate-hiredate)/30,0) from emp

5:查询员工的姓名和工资，按下面的形式显示
Name Salary
SMITH $$$$$$$$$$24000
??select ename ,to_char(sal,'$99999') sal from emp;
select ename Name,LPAD(sal,15,'$' )as Salary
from emp;
6:查询员工的姓名ename和工资数sal,条件限定为工资数必须大于1200，并对查询结果按月份数降序方式进行排列
做一个查询，产生下面的结果
select  ename "姓名",sal "工资",to_char(hiredate,'YYYY-MM-DD') "工作时间" from emp
where sal >1200 order by to_char(hiredate,'MM')desc；
select ename,trunc((sysdate - hiredate)/30,0),sal from emp where sal > 1200
order by trunc((sysdate - hiredate)/30,0) desc

<enamename> earns <sal> monthly but wants <sal*3>

Dream Salary
King earns $24000 monthly but wants $72000
select (initcap(ename) || ' earns ' || trim(to_char(sal,'$99999'))
|| ' monthly but wants ' || trim(to_char(sal*3,'$99999')))
from emp;
7:
做一个查询，产生类似下面的结果
Ename HireDate reiew
SMITH 1980-12-17 1980年12月17日
select ename,hiredate,to_char(hiredate,'yyyy')||'年'||to_char(hiredate,'month')||'月' from emp;
select ename,hiredate,to_char(hiredate,'yyyy')||'年'||to_char(hiredate,'month')||to_char(hiredate,'fmdd')||'日' from emp;
select ename Ename,hiredate HireDate,to_char(hiredate,'yyyy"年"mm"月"dd"日"')  reiew from emp;

使用decode函数，按照下面的条件：
job                  grade
PRESIDENT             A
MANAGER               B
ANALYST               C
SALESMAN              D
CLERK                 E
产生类似下面形式的结果
ENAME Job Grade
SMITH CLERK E

将第9题的查询用case函数重新实现。
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
nvl2()
如果表达式1的值不为null,返回表达式2,否则返回表达式3

SELECT first_name, LENGTH(first_name) "expr1",
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;

如果表达式1的值不为空,返回表达式1的值,否则返回表达式2的值,如果表达式2的值为空,返回表达式3的值
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式非空,则返回这个表达式，对其他的参数进行COALESCE 。
如:
     SELECT coalesce(MGR,COMM,DEPTNO)
     FROM EMP
SELECT ename, job, sal,
       CASE job    WHEN 'CLERK'  THEN  1.10*sal
                   WHEN 'MANAGER' THEN  1.15*sal
                   WHEN 'SALESMAN'   THEN  1.20*sal
       ELSE      sal END     "REVISED_SALARY"
FROM   emp;

SELECT ename, job, sal,
       DECODE(job, 'SALESMAN',  1.10*saL,
                   'CLERK', 1.15*sal,
                   'MANAGER',   1.20*sal,
              sal)
       REVISED_SALARY
FROM   emp;
SELECT ename, sal,
       DECODE (TRUNC(sal/2000, 0),
                         0, 0.00,
                         1, 0.09,
                         2, 0.20,
                         3, 0.30,
                         4, 0.40,
                         5, 0.42,
                         6, 0.44,
                            0.45) TAX_RATE
FROM   emp
select ENAME,JOB,
case JOB when 'PERSIDENT' then 'A'
          when 'MANAGER' then 'B'
          when 'ANALYST' then 'C'
          when 'SALESMAN' then 'D'
          when 'CLERK' then 'E'
else 'A' end from emp

SELECT ENAME,JOB,
       DECODE(JOB,
                         'PERSIDENT', 'A',
                         'MANAGER', 'B',
                         'ANALYST', 'C',
                         'SALESMAN', 'D',
                         'CLERK','E',
                            'F') TAX_RATE
FROM   emp
select ename,sal,decode(job,'PRESIDENT','A',
'MANAGER','B',
'ANALYST','C',
'SALESMAN','D',
'CLERK','E'
) grade
from emp;

select ENAME,JOB,
case JOB when 'PERSIDENT' then 'A'
         when 'MANAGER' then 'B'
         when 'ANALYST' then 'C'
         when 'SALESMAN' then 'D'
         when 'CLERK' then 'E'
else 'A' end from emp

多表查询:
如果你查二张或多张表....  查询前一定找，表之有什么关系，只要加上关系，保证正确;;
编号 姓名  部门名称
emp  emp   dept

select ename,empno,dname
from emp,dept
where emp.deptno = dept.deptno;

pl/sql  oracle 自己，java c [sql server 存储过程，触发器,]
等值连接
select empno,ename,sal,grade
from emp,salgrade
where sal between losal and hisal


empno,ename,loc,grade
emp
dept
salgrade

select empno,ename,loc,grade
  from emp,dept,salgrade
  where sal between losal and hisal and dept.deptno=emp.deptno;
select empno,ename,loc,grade
from emp e,
     dept d,
    salgrade s
where e.deptno = d.deptno  and e.sal between s.losal and s.hisal

使用外连接可以查询不满足连接条件的数据。
外连接的符号是 (+)
没有(+)的一侧将显示全部记录,
有(+)的一侧显示匹配的记录
select empno,ename,job,sal,dname,loc
from emp e,dept d
where e.deptno(+) = d.deptno
自连接;;
empno,ename,sal,主管理姓名;

select e.ename,e.empno,m.ename
from emp e, emp m
where e.mgr = m.empno;


多表查询
显示所有员工的姓名ename，部门号deptno和部门名称dname。
select e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno

查询20号部门员工的job和20号部门的loc
select job,loc
from emp,dept
where dept.deptno = 20;

选择所有有奖金comm的员工的
ename , dname , loc

select  emp.ename,emp.job,dept.deptno,dept.dname from
emp,dept
where comm is not null and emp.deptno=dept.deptno
选择在DALLAS工作的员工的
ename , job , deptno, dname
select e.ename,e.job,e.deptno,d.dname
from dept d,emp e
where d.deptno=e.deptno and d.loc like 'DALLAS';
选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名ename和员工号deptno，结果类似于下面的格式
employees Emp# manager Mgr#
SMITH 7369 FORD 7902

select e.ename as "employees",e.deptno as "Emp#",m.ename as "manager",m.deptno as "Mgr#" from emp e,emp m where e.mgr=m.empno;
6. 查询各部门员工姓名和他们所在位置，结果类似于下面的格式
Deptno Ename Loc
20 SMITH DALLAS
select  e.deptno as "Deptno",ename as "Ename" , loc as "Loc"
from emp e,dept d
where e.deptno=d.deptno
sql 1999

使用CROSS JOIN 子句使连接的表产生叉集。
叉集和笛卡尔集是相同的。
SELECT e.empno, e.ename, d.dname
FROM   emp e JOIN dept d
USING (deptno)
92
SELECT e.empno, e.ename, d.dname
FROM   emp e , dept d
where e.deptno = d.deptno;

99
SELECT e.empno, e.ename, d.dname
FROM   emp e JOIN dept d
ON     (e.deptno = d.deptno);
SELECT e.empno, e.ename, s.grade
FROM   emp e JOIN salgrade s
ON     (e.sal between s.losal and s.hisal)

SELECT empno,ename,loc,grade
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
SELECT e.ename, e.empno, d.dname
FROM   emp e
LEFT OUTER JOIN dept d
ON   (e.deptno = d.deptno)

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

SELECT e.ename, e.empno, d.dname
FROM   emp e
FULL OUTER JOIN dept d
ON   (e.deptno = d.deptno) ;
AVG
COUNT
MAX
MIN
SUM

所用包含于SELECT 列表中，而未包含于组函数中的列都
必须包含于 GROUP BY 子句中。

使用 HAVING 过滤分组:
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。

SELECT   job, MAX(sal)
FROM     emp
GROUP BY job
HAVING   MAX(sal)>2000

分组函数
查询公司员工工资的最大值，最小值，平均值，总和
select MAX(sal),MIN(sal),AVG(sal),SUM(sal) from emp;
查询各job的员工工资的最大值，最小值，平均值，总和
select job,max(sal),min(sal),avg(sal),sum(sal)
from emp
group by job;

选择具有各个job的员工人数(提示:对job进行分组)
select MAX(sal) maxsal,MIN(sal) minsal,AVG(sal) avg ,SUM(sal) sunsal
from emp
group by job

select job,count(deptno) from emp group by job ;
查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(sal) a,min(sal) i,max(sal)-min(sal) c from emp;

查询各个管理者手下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内
select mgr 管理者编号,min(sal) 最低工资
from emp
group by mgr
having  min(sal) >= 800 and mgr is not null
查询所有部门的名字dname，所在位置loc，员工数量和工资平均值
select dname as 部门名字,loc as 所在位置,count(dname) as 部门人数,avg(sal) as 本部门工资平均值
from emp,dept
where emp.deptno = dept.deptno
group by dname,loc;

查询公司的人数，以及在1980-1987年之间，每年雇用的人数，结果类似下面的格式
total 1980 1981 1982 1987
30 3 4 6 7
参考答案:
SELECT COUNT(*) "total",SUM(DECODE(
TO_CHAR(HIREDATE,'YYYY'),'1980',1,0))
AS "1980",SUM(DECODE( TO_CHAR(HIREDATE,'YYYY'),'1981',1,0))
AS "1981",SUM(DECODE( TO_CHAR(HIREDATE,'YYYY'),'1982',1,0))
AS "1982",SUM(DECODE( TO_CHAR(HIREDATE,'YYYY'),'1987',1,0))
AS "1987"FROM EMP

orcle 查询约束关系

select pk.table_name,fk.table_name,fk.constraint_name  from 
(select * from user_constraints  where constraint_type='R') fk,
(select * from user_constraints  where constraint_type='P') pk
where fk.r_constraint_name=pk.constraint_name and   
fk.constraint_name = upper('&fk_cons_name')

查看回收站对象
select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;
还原删除的表
flashback table tab_name to before drop
还原删除重建的表
flashback table tab_name to before drop   rename to order_old_version;
回滚指定时间段
alter table inp_alarm_sumbyday enable row movement 
FLASHBACK TABLE order TO TIMESTAMP    TO_TIMESTAMP('2010-09-12 01:15:25 PM','YYYY-MM-DD HH:MI:SS AM')

删除重复表数据
完全相同的数据，可以创建临时表
create tb_tem as select distinct * from tb_name
drop table tb_name
rename table tb_tem to tb_name
不完全重复的数据，可以拆成两张表
delete from tb_name where rowid in(select a.rowid from tb_name a,tb_name b where a.rowid>b.rowid and a.col=b.col ...)