oracle课堂笔记

oracle课堂笔记
星期⼀：Monday
星期⼆：Tuesday
星期三：Wednesday
星期四：Thursday
星期五：Friday
星期六：Saturday
星期天：Sunday
数据库⽬录
实例主⽬录：C:\oracle\product\10.1.0\admin
实例⽬录⽂件：bdump:⽤于保存进程跟踪⽂件 cdump:⽤于保存内核放弃⽂件 create:o数据库创建⽂件 pfile：初始化参数⽂件 udump:⽤户SQL跟踪⽂件C:\oracle\product\10.1.0\oradata\orcl 数据库⽂件 .ctl 数据库控制⽂件 .log 重做⽇志⽂件 .dbf 数据⽂件
Oracle注册表相关项
[HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\OracleCSService] Oracle服务相关项
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1] Oracle安装，运⾏环境，参数设置相关项
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle in OraDb10g_home1] 与ODBC驱动相关的⼦键
变量&
select employee_id,last_name,salary,department_id from employees where employee_id=&请输⼊员⼯编号;
select job_id, last_name, department_id, salary*12,salary from employees where job_id='&job' 中⽂字符要加单引号
select employee_id,last_name,job_id,&column_name from employees;
select employee_id,last_name,job_id,&column_name from employees order by &order_column;
操作符优先级：
（1）乘除的优先级⾼于加减。

（2）同⼀优先级运算符从左向右执⾏。

（3）括号内的运算先执⾏
select last_name,salary,(salary+300)*12,salary*12+300 from employees;
定义空值
包含空值的数学表达式的值都为空值
select last_name,job_id,commission_pct,12*salary*commission_pct from employees;
别名
as
select last_name,job_id,commission_pct,12*salary*commission_pct as "奖⾦" from employees
连接符
select last_name||job_id as employees from employees;
字符串
select last_name|| ' is a ' ||job_id as "Employees Details" from employees;
重复⾏
默认返回重复⾏
select department_id from employees;
删除重复⾏distinct
select distinct department_id from employees;
显⽰表结构
describe
过滤
where紧跟在from后⾯
select employee_id ,last_name,job_id,department_id from employees where department_id=80;
字符和⽇期要包含在单引号中。

字符串对⼤⼩写敏感。

select employee_id,last_name,job_id,department_id from employees where last_name='Grant';
⽐较运算
= 等于
> ⼤于
< ⼩于
=> ⼤于等于
<= ⼩于等于
!=,<> 不等于
select last_name,salary from employees where salary<=3000;
select last_name,salary from employees where salary!=3000;
其它⽐较运算
-------------------------------------------------------------------------------------------------------------------------------------
between...and... 在两个值之间（包含边界）
select last_name,salary from employees where salary between 2500 and 6000;
in(set) 等于值列表中的⼀个
select employee_id, last_name,salary,mana
ger_id from employees where manager_id in (100,101,102)
like 模糊查询（% 代表零个或多个字符 _ 代表⼀个字符。

）
select first_name from employees where first_name like 'S%';
select empno,ename from emp where ename like 'SMI\_%' escape '\'; 使⽤ESCAPE函数来代表字符本⾝，转义字符。

is null 空值
select ename,sal job from emp where comm is null; 空
select ename,sal job from emp where comm is not null; ⾮空
-------------------------------------------------------------------------------------------------------------------------------------
逻辑运算
(1)and 与运算 select employee_id ,last_name,job_id ,salary from employees where salary>=3000 and job_id like 'SA%';
(2)or 或运算 select employee_id,last_name,job_id,salary from employees where salary>=3000 or job_id like 'SA';
(3)not ⾮运算 select employee_id ,last_name,job_id salary from employees where job_id not like 'SA%';
select employee_id ,last_name,job_id salary from employees where job_id not in ('ST_CLERK','IT_PROG');
逻辑运算优先级
(1)算术运算符 (+ - * /)
(2)连接符 (||)
(3)⽐较符 (< > =)
(4)IS [NOT] NULL,LIKE,[NOT]IN
(5)[NOT]BETWEEN
(6)NOT
(7)AND
(8)OR
注：可以⽤括号改变优先级
select last_name,job_id,salary from employees where job_id='SA_REP' or job_id='ST_CLERK' and salary>=8000;
select last_name,job_id,salary from employees where (job_id='SA_REP' or job_id='ST_CLERK') and salary>=8000;
排序（order by）
asc:升序 desc：降序（默认为升序）
select last_name,job_id,salary,hire_date from employees order by hire_date asc;
select last_name,job_id,salary,hire_date from employees order by salary desc;
（可以按别名排序） select last_name,job_id,salary,salary*12 totalsalary from employees order by totalsalary ;
=====================================================================================================================================函数
⼤⼩写控制函数
(1)SQL> select lower('SQL SERVER') from dual; (将⼤写改为⼩写)
LOWER('SQLSERVER')
------------------
sql server
(2)SQL> select upper('sql server') from dual; (将⼩写改⼤写)
UPPER('SQLSERVER')
------------------
SQL SERVER
(3)SQL> select initcap('sql server') from dual; (将每个单词的⾸字每改为⼤写,其它改为⼩写)
INITCAP('SQLSERVER')
--------------------
Sql Server
===================================================================================================================================== SQL> select last_name, salary from employees where last_name=initcap('fay');
LAST_NAME SALARY
------------------------- ----------
Fay 6000.00
===================================================================================================================================== SQL> select last_name,
salary from employees where lower(last_name)='fay';
LAST_NAME SALARY
------------------------- ----------
Fay 6000.00
=====================================================================================================================================
字符控制函数
（1）concat合并字符 select concat('Sql','Server') from dual; 结果：SqlServer
（2）substr截取字符 select substr('SqlServer',4,6) from dual; 结果：Server 从第⼏个开始截取，共截取⼏个。

（3）length字符串长度 select length('SqlServer') from dual; 结果：9
（4）lengthb字符串所占字节数 select lengthb('李杰') from dual; 结果：4
（5）instr字符第⼀次出现的位置 select instr('SqlServer','e') from dual; 结果：5
（6）lpad在字符左边补字符 select lpad(salary,length(salary)+1,'$') from employees; length(salary)+1:字符总数 '$'要补的字符
（7）rpad在字符右边补字符 select rpad(salary,length(salary)+2,'元') from employees;
（8）trim去除字符串开头的空格或其它特殊字符 select trim('s' from 'sqlserver') from dual; 结果：qlserver 只能去字符串开头的空格或字符
=====================================================================================================================================
数字函数
（1）round四舍五⼊ select round(98.98773,3) from dual; 结果：98.988 3为保留的位数 0表⽰保留整数可以为负数，负数从个位往⼗位保留
（2）trunc截断 select trunc(98.98773,3) from dual; 结果：98.987 第⼆个参数可以为负数，可以为0
（3）mod求余 select mod(10,3) from dual; 结果：1
=====================================================================================================================================⽇期函数
sysdate 系统⽇期时间
（1）在⽇期上加上或减去⼀个数字结果仍为⽇期。

默认添加的单位为天
（2）两个⽇期相减返回⽇期之间相差的天数。

（3）可以⽤数字除24来向⽇期中加上或减去⼩时。

SQL> select to_char(sysdate,'yyyy-mm-dd,amhh:mi:ss'),to_char(sysdate+1/24,'yyyy-mm-dd,amhh:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD,AM TO_CHAR(SYSDATE+1/24,'YYYY-MM-
------------------------------ ------------------------------
2012-02-15,pm08:42:11 2012-02-15,pm09:42:11
(1)months_between 两个⽇期相差的⽉数 select months_between(sysdate,hire_date) from employees;
(2)add_months 向指定的⽇期中添加若⼲⽉数 select add_months(sysdate,10) from employees; 10为要添加的⽉数
(3)nest_day 指定⽇期的下⼀个⽇期 select next_day(sysdate,'Sunday')from dual; 第⼆个参数为星期⼏英⽂
(4)last_day 本⽉的最后⼀天 select last_day(sysdate) from dual;
(5)round ⽇期四舍五⼊ select round(sysdate,'month') from dual; 满16进1 select round(sysdate,'year') from dual; 满7进1
(6)trunc ⽇期截断 s
elect trunc(sysdate,'month') from dual; select trunc(sysdate,'year') from dual;
=====================================================================================================================================转换函数
to_char对⽇期的转换
select to_char(hire_date,'yyyy-mm-dd,amhh:mi:ss') from employees; 可以使⽤双引号向⽇期中添加字符
to_char对数字的转换
select to_char(salary,'$99,999.00') salary from employees;
to_number 将字符转为数字
to_date 将字符转为⽇期
to_char 将数值型或者⽇期型转化为字符型
=====================================================================================================================================嵌套函数
select last_name ,nvl(to_char(manager_id),'No Manager') from employees where manager_id is null;
nvl函数
nvl(expr1,expr2) 当expr1为空时，补exp2 ⽇期 nvl(hire_date,'01-jan-97')
nvl2函数
nvl2(expr1,expr2,expr3) 当expr1为空时，补expr2，当expr2为空时，补expr3
select last_name,salary,commission_pct,nvl2(commission_pct,'sal+comm','sal') income from employees where department_id in (50,80)
nullif函数
nullif(expr1,expr2) 若expr1不等于expr2 则返回expr1 若expr1=expr2 则返回null值
select last_name, length(last_name),first_name,length(first_name) ,nullif(length(last_name),length(first_name)) from employees;
coalesce函数如果第⼀个表达式⾮空,则返回这个表达式
select last_name,coalesce(commission_pct,salary,1000) from employees;
=====================================================================================================================================条件表达式（在sql语句中使⽤if...then...else逻辑）
CASE表达式
select last_name,job_id,salary,
case job_id
when 'IT_PROG' then 1.10*salary
when 'ST_CLERK' then 1.20*salary
when 'SA_REP' then 1.50*salary
else salary end "调整后的⼯资"
from employees;
DECODE函数
select last_name ,salary ,
decode(trunc(salary/2000,0),
0,0.00,
1,0.19,
2,0.20,
3,0.30,
4,0.40,
5,0.50,
0.60) TAX_RATE
from employees ;
语法：
select sid,serial#,username,
DECODE(command,
0,’None’,
2,’Insert’,
3,’Select’,
6,’Update’,
7,’Delete’,
8,’Drop’,
‘Other’) cmmand
from v$session where username is not null;
=====================================================================================================================================多表查询（使⽤表名前缀以提⾼执⾏效率）
select employee_id,last_name,departments.department_id,department_name from employees,departments
where employees.department_id=departments.department_id;
语法：
SELECT table1.column, table2.column FROM table1, table2
WHERE table1.column1 = table2.column2;
等值连接
select e.employee_id,/doc/974685d433d4b14e852468d1.html st_name,e.department_id,d.department_id,d.location_id
f
rom employees e,departments d
where e.department_id=d.department_id
连接多个表和使⽤AND操作符
select /doc/974685d433d4b14e852468d1.html st_name,e.department_id,d.location_id,l.city
from employees e,departments d ,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
⾮等值连接
select /doc/974685d433d4b14e852468d1.html st_name,e.salary,j.grade_level
from employees e ,job_grades j
where e.salary between j.lowest_sal and j.highest_sal;
外连接（使⽤外连接可以查询不满⾜连接条件的数据）
select /doc/974685d433d4b14e852468d1.html st_name, d.department_id ,d.department_name from employees e,departments d where
e.department_id(+)=d.department_id;
select /doc/974685d433d4b14e852468d1.html st_name, e.department_id ,d.department_name from employees e,departments d where
e.department_id=d.department_id(+);
⾃连接
select /doc/974685d433d4b14e852468d1.html st_name || 'work for '|| /doc/974685d433d4b14e852468d1.html st_name
from employees worker ,employees manager
where worker.manager_id=manager.employee_id
使⽤SQL: 1999 语法连接(使⽤cross join句⼦使连接的表产⽣叉集)
SELECT last_name, department_name FROM employees CROSS JOIN departments ;（笛卡尔集）
使⽤natural join⼦句，会以两个表中具有相同名字的列为条件创建等值连接。

select last_name,department_id,department_name from employees natural join departments;
在natural join⼦句创建等值连接时，可以使⽤using⼦句指定等值连接中需要⽤到的列,可以在有多个满⾜条件的列中进⾏选择，不要给选中的列前加上表名前缀或别名。

select /doc/974685d433d4b14e852468d1.html st_name,e.employee_id, d.location_id from employees e join departments d using (department_id);
使⽤on⼦句创建连接
⾃然连接中是以具有相同名字的列为连接条件的，可以使⽤on⼦句指定额外的连接条件。

select /doc/974685d433d4b14e852468d1.html st_name,e.employee_id,e.department_id,d.location_id,d.department_id from employees e join departments d on (e.department_id=d.department_id);
使⽤on⼦句连接多个表
select employee_id,city,department_name
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id ;
内连接与外连接
在SQL：1999中，内连接只返回满⾜条件的的数据。

两个表在连接过程中除了返回满⾜条件的数据以外还返回左（或右）表不满⾜条件的数据，这种连接称为左（或右）外联接。

同时返回不满⾜条件的⾏，这样的连接称为满外联接。

左外连接
select /doc/974685d433d4b14e852468d1.html st_name,e.department_id,d.department_name from employees e left outer join departments d on (d.department_id = e.department_id);
右外连接
select /doc/974685d433d4b14e852468d1.html st_name,e.department_id,d.department_name from employees e right outer join departments d on (d.department_id = e.department_id);
满外连接
select /doc/974685d433d4b14e852468d1.html st_name,e.department_id,d.department_name from employees e full outer join departments d on (d.department_id = e.department_id);
增加连接条件
select e.employee_id, /doc/974685d433d4b14e852468d1.html st_name,e.depa
rtment_id,e.manager_id,d.department_id,d.location_id
from employees e join departments d on (e.department_id = d.department_id) and e.manager_id=149;
=====================================================================================================================================分组函数
组函数类型
avg 平均数
count 计数函数
max 最⼤值
min 最⼩值
stddev 平⽅差
sum 求和
select avg(salary),min(salary),max(salary),sum(salary)/count(*) from employees;
可以对数值型数据使⽤AVG和SUM函数
可以对任意数据类型使⽤MAX和MIN函数
count(*)返回表中记录总数
select count(* ) from employees;
count(expr)返回expr不为空的记录总数
select count(commission_pct) from employees;
distinct关键字 count(distinct expr) 返回expr⾮空且不重复的记录总数
select count(distinct department_id) from employees;
组函数会忽略空值
select avg(commission_pct) from employees;
使组函数不忽略空值
select avg(nvl(commission_pct,0))from employees;
=====================================================================================================================================分组数据
可⽤group by ⼦句将表中的数据分成若⼲⼦句
select department_id,avg(salary) from employees group by department_id;
select avg(salary) from employees group by department_id;
在GROUP BY ⼦句中包含多个列
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
⾮法使⽤组函数
不能在where⼦句中使⽤组函数
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
可以在having⼦句中使⽤组函数(过滤分组，⾏已被分组，使⽤了分组函数，满⾜having条件的分组将被显⽰)
select department_id, AVG(salary) FROM employees having AVG(salary)>8000 GROUP BY department_id;
select department_id,max(salary) from employees group by department_id having max(salary)>10000;
select job_id,SUM(salary)from employees where job_id not like'%REP%' group by job_id having sum(salary) > 13000 ORDER BY SUM(salary)
嵌套组函数
查部门平均⼯资的最⼤值
select max(avg(salary)) from employees group by department_id;
=====================================================================================================================================
⼦查询（⼦查询在主查询之前⼀次执⾏完成，⼦查询的结果被主查询所使⽤。

）
⼦查询要包含在括号内，除了进⾏TOP-N分析，否则不要在⼦查询中使⽤ORDER BY ⼦句。

select last_name,salary from employees where salary >(select salary from employees where last_name = 'Abel');
单⾏⼦查询
select last_name,job_id,salary from employees
where
job_id=(select job_id from employees where employee_id=141)
and
salary>(select salary from employees where employee_id =143);
在⼦查询中使
⽤组函数
select last_name,job_id,salary from employees where salary =(select min(salary) from employees);
⼦查询中的having⼦句
select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id =50);
多⾏⼦查询
多⾏⽐较操作符
IN 等于列中的任何⼀个
ANY 和⼦值查询返回的任意⼀个值进⾏⽐较
ALL 和⼦查询返回的的所有值进⾏⽐较
在多⾏⼦查询中使⽤ANY操作符
select employee_id,last_name,job_id,salary from employees where salary < any (select salary from employees where job_id='IT_PROG' ) and job_id <> 'IT_PROG';
在多⾏⼦查询中使⽤ALL操作符
select employee_id,last_name,job_id,salary from employees where salary < all (select salary from employees where job_id='IT_PROG' ) and job_id <> 'IT_PROG';
===================================================================================================================================== DML = Data Manipulation Language，数据操纵语⾔
功能：
1.向表中插⼊数据
2.修改现存数据
3.删除现存数据
事务是由完成若⼲项⼯作的DML语句构成的。

插⼊数据(字符和⽇期型数据应包含在单引号中)
insert into values
向表中插⼊空值
隐式⽅式：
INSERT INTO departments (department_id,department_name) VALUES(30, 'Purchasing');
显⽰⽅式：
INSERT INTO departments VALUES(100, 'Finance', NULL, NULL);
可以插⼊SYSDATE为当前系统的时间和⽇期
在insert语句中加⼊⼦查询(不⽤写value字段，⼦查询中的值应与insert⼦句的列名对应)
insert into employees_history(id,name,salary,commission_pct) select employee_ id,last_name,salary,commission_pct from employees where job_id like '%REP%';
更新数据（update set）
update employees set department_id=70 where employee_id = 113;
在update语句中使⽤⼦查询
update employees set job_id=(select job_id from employees where employee_id=205),salary=(select salary from employees where employee_id=205) where employee_id=144;
更新144号员⼯的⼯资和⼯作使其与205号相同
在update语句中使⽤⼦查询使更新基于另⼀表中的数据
UPDATE employees_history SET department_id=(SELECT department_id FROM employees WHERE employee_id = 100)
WHERE job_id=(SELECT job_id FROM employees WHERE employee_id = 200);
删除数据
使⽤where⼦句删除指定的记录(如果省略where⼦句，则表中的数据将全被删除)
delete from departments where department_name = ‘Finance’
插⼊数据
263
更新：
update emp set ename='SMITH' where ename='SMI_TH';
忽略字符语句：
select ename,sal from emp where ename like '___\_%' escape '\';
%代表多个字符 _代表⼀个字符
排序：
select ename,sal,job,to_char(hiredate,'yyyy-mm-dd hh:mi:ss') from emp order by hiredate; 从⼩到⼤select ename,sal,job,to_char(hireda
te,'yyyy-mm-dd hh:mi:ss') from emp order by hiredate desc; 从⼤到⼩
别名：
select ename,sal,job,to_char(hiredate,'yyyy-mm-dd hh:mi:ss') as "⼊职时间" from emp order by hiredate; 系统时间：
(1)select sysdate from dual;
(2)select to_char(sysdate,'yyyy-mm-dd,hh:mi:ss') from dual;
(3)select to_char(sysdate,'yyyy-mm-dd,amhh"时":mi"分":ss"秒"') from dual; 格式化时间
别名排序：
select ename,sal*12 as totalsalary from emp order by totalsalary;
多字段排序：
select ename,sal from emp order by sal, ename ;
转换⼤⼩写：
select lower(ename),sal from emp; 转换成⼤写
select upper(ename),sal from emp; 转换成⼩写
⾸字母⼤写：
select initcap('i am super strongman') from dual;
合并：
select concat('i am', 'super strongman') from dual;
select 'i am'|| 'super strongman' from dual;
截取：
select substr('i am handsom man',1,3 )from dual; 1：从第⼏个开始截 3：⼀共截⼏个
字符个数：
select length('i am handsom man')from dual;
字节个数：
select lengthb('i am handsom man')from dual;
查找字符串中是否包含a：
select instr('i am handsom man','a')from dual; 返回结果为第⼀个a出现的位置
补字符：
select lpad('888888',8,'$')from dual; 8：宽度 $：要补的字符
select ename,lpad(sal,length(sal)+1,'$')from emp; ⾃动加⼀位补字符
补本地货币符号：
select ename,to_char(sal,'L99999') FROM EMP;
去空格：
select trim(' aaaaaaaaaaaaaaaa ')from dual; 只能去两边，不能去中间。

替换：
select replace( 'Im a richgirl','girl','man') from dual; girl替换为man
四舍五⼊：
select round(45.923,2),round(45.923,0),round(45.923,-1) from dual;
select trunc(45.923,2),trunc(45.923,0),trunc(45.923,-1) from dual; 全部丢弃。

求余剩：
select ename,sal,mod(sal,1000) from emp;
⽇期计算：
select ename,sal,(sysdate-hiredate) from emp order by hiredate; ⼊职到现在多长时间。

select ename,sal,(sysdate-hiredate) as "年" from emp order by hiredate;
居中：
column 年 justtify center;
⽇期函数：
MONTHS_BETWEEN 两个⽇期相差的⽉数
ADD_MONTHS 向指定⽇期中加上若⼲⽉数
NEXT_DAY 指定⽇期的下⼀个⽇期
LAST_DAY 本⽉的最后⼀天
1、从oracle10g开始删除数据库表的时候并不是真正删除，⽽是放到了recyclebin中，这个过程类似 windows⾥⾯删除的⽂件会被临时放到回收站中。

2、删除的表系统会⾃动给他重命名就是你看到的【BIN$】开头的名字
3、通过 show recyclebin 命令可以查看被删掉的表的详细信息，或者查询
select * from recyclebin;
4、收回表的命令：
flashback table 原表名 to before drop;
5、情况回收站的命令：
purge recyclebin;
6、如果不想删除的表经过回收站
drop table 表名 purge;
或者停⽤数据库的回收战功能
10.1版本中，修改
隐藏参数 _recyclebin
alter system set "_recyclebin" = false;
10.2版本中，
alter system set recyclebin = off;。