oracle操作大全

1 oracle 安装
断开网络连接
在整个安装过程中，不要进行其他的任何操作。
2 进入到口令管理的界面
高级管理员：sys/change_on_install
普通官员: system/manager
普通用户: scott/tiger
大数据用户: sh/sh

3 OracleOraDb10g_home1TNSListener:监听服务，如通有远程的客户端需要连接，并且如果有其他的应用程序需要访问oracle，这个服务必须要启动
OracleServiceAMBOW：数据库的实例服务，保存数据库的信息服务的。


4 卸载
运行卸载程序
重新启动电脑，手动删除磁盘上对应的文件
还应该要去手动的删除oracle在注册表对应的选项
开始－＞运行，输入regedit ，打开注册表，删除oracle服务：
找到HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口；
HKEY_LOCAL_MACHINE\system\ControlSet001\services\所有oracle的服务，按del键删除掉
HKEY_LOCAL_MACHINE\system\ControlSet002\services\所有oracle的服务，按del键删除掉
HKEY_LOCAL_MACHINE\system\ControlSet003\services\所有oracle的服务，按del键删除掉
HKEY_LOCAL_MACHINE\system\ControlSet\services\所有oracle的服务，按del键删除掉


5 SQLPLUS
在DOS中使用SQLPLUS可以进入oracle。
SQLPLUSW.EXE,在键盘的方向键是无效的。ed sql.sql @sql.sql可以在文本编辑器中编辑SQL

oracle可以是用格式化操作，进行输出的格式化：
set pagesize 100;
set linesize 300;

如果当前要查询实例数据库中的表单：
select * from tab;

如果要查询当前的用户：
show user

切换用户sys:
conn sys/change_on_install as sysdba;

切换scott
conn scott/tiger ;

如果是使用sys用户登录，则访问：select * from emp;是无法执行的
select * from scott.emp;

如果需要访问操作系统中的命令，则可以是用:Host 最为前缀进行访问
Host copy d:\1.txt d:\2.txt;

关闭数据库实例服务：
shutdown immediate

启动数据库实例服务：
startup


6 SQL
DML 数据操作语句。 查询，修改
DDL 数据定义语句。
DCL 定义用户权限的。


7 简单查询：查询出一张表中所有符合条件的记录
emp:员工表
empno:雇员的编号
ename:雇员的姓名
job:工作岗位
mgr:对应领导的编号
hiredate:雇佣日期
sal:基本工资
comm:奖金
deptno:部门编号
dempt:部门
deptno:部门编号
dname:部门的名称
loc：部门所在位置
salgrade:工资等级表
orade 工资等级
losal 此等级的最低工资
hisal 此等级的最高工资
bonus工资表：
ename 雇员的姓名
job 工作岗位
sal 基本工资
comm 奖金


查询出所有的记录：
select * from emp;
单是意义在编程的时候应该把所有要查询的字段写出来：
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;


查询出所有的岗位：
select job from emp;
查询出的

结果是有重复的：
select distinct job from emp;
记录重复：如果查询出的记录对应每个字段的值都是一样，则可以认定两个记录是重复的。
select distinct ename,job from emp;

在简单查询中我可是使用数学中的四则运算：
求年薪：
select ename,job,sal*12 from emp;
select ename,job,sal*12 as nianxi from emp;
select ename,job,sal*12 nianxi from emp;
每个月补贴车补：100，补贴餐补：200，每年发13月工资求年薪
select ename,job,(sal+300)*12+sal nianxi from emp;

在简单查询中可以||表实现连接
select empno||ename from emp;
select empno|| ',' ||ename from emp;可以连接字符串常量


限定查询
where关系运算，逻辑运算
要求i：查询出所有雇员的，工资》1500
select * from emp
where
sal>1500;
查询出工作岗位是clerk所有雇员
select * from emp
where
job='CLERK';
查询出所有工作岗位不是Clerk:
select * from emp
where
job<>'CLERK';

select * from emp
where
job!='CLERK';

select * from emp
where
NOT(job='CLERK');

多条件的连接（and or）
查询雇员工资在1500-3000之间：
select * from emp
where
sal<=3000 and sal>=1500;

查询工作岗位是销售员或者是办事的员的：
select * from emp
where
job='CLERK' or job='SALESMAN';

查询工作岗位是销售员或者是办事的员的，并且工资大于1200
select * from emp
where
(job='CLERK' or job='SALESMAN') and sal>1200;

范围判断between...and...
查询雇员工资在1500-3000之间:
select * from emp
where
sal between 1500 and 3000;(包含1500,3000这个两个值)
还可以用在日期范围：
查询所有在1981年入职的所有雇员信息：
select * from emp
where
hiredate between '01-1月-81' and '31-12月-81';

判断是否为空is null ,is not null
查询出所有没有奖金的雇员信息
select * from emp
where
comm is null;

是否在指定范围的in运算：
select * from emp
where
empno=7369 or empno=7566 or empno=7839;
select * from emp
where
empno in(7369,7566,7839);


like模糊查询：
匹配单个字符：_
匹配多个字符：%
查询以字符A开头的所有雇员信息：
select * from emp
where
ename like 'A%';
查询所有第二个字符是A的所有雇员信息：
select * from emp
where
ename like '_A%';

还可以用在日期格式：
select * from emp where hiredate like '%1%' or sal like'%1%';


排序order by
select * from emp order by sal asc;默认采用升序
select * from emp order by sal desc;降序
select * from emp order by sal desc ,hiredate asc;


函数
字符函数
数值函数
日期函数
转换函数
通用函数

字符函数：
小写函数转化成为大写函数：（dual虚拟表）
select upper('smith') from dual;

select * from emp where ename=Upper('Smith');

大写转换成小写：
select lower('SMITH')

from dual;

第一个英文字母大写，其余的小写：
select initcap('china') from dual;

||连接，concat
select concat('张三','李四') from dual;


字符串的截取：substr()
字符串的长度：length()
内容替换：replace()

select substr('helloworld',2,3) from dual;
select substr('helloworld',2) from dual;

select length('helloworld') from dual;

select replace('helloworld','l','z') from dual;

显示出所有雇员的姓名及姓名后的三个字符
select ename,substr(ename,length(ename)-2,3) from emp;
select ename,substr(ename,-3,3) from emp;（负数代表反方向截取）

数值函数
四舍五入 round()
截断小数位 trunc()
取模 mod()

select round(123.5689) from dual;
select round(123.5689,2) from dual;(四舍五入，但保留了两个小数)
select round(123.5689,-2) from dual;
select trunc(123.5689) from dual;
select trunc(123.5689,2) from dual;
select trunc(123.5689,-2) from dual;


日期函数：
如果要显示当前的日期，则可以使用sysdate
select sysdate from dual;

使用日期可以进行如下的四则运算：
日期+数值=日期：表示若干天之后的那个日期
select sysdate+3 from dual;
日期-数值=日期：表示若干天之前的那个日期
select sysdate-3 from dual;
日期-日期=数值：表示两个日期之间相隔的天数
将雇员的雇佣日期到现在相隔多少天：
select sysdate-hiredate from emp;
select trunc(sysdate-hiredate) from emp;

日期函数：
last_day(日期):当前日期所在的月份的最后一天的日期
next_day(日期，星期几)当前所指定的日期的下一个星期几的日期
add_months(日期，数值)当前日期所在的月份+数值，所在的日期

select last_day(sysdate) from dual;
select next_day(sysdate,'星期一') from dual;
select add_months(sysdate,3) from dual;
求出每个雇员到现在的雇佣的月份数：
select trunc(months_between(sysdate,hiredate)) from emp;

转换函数：（重点内容）
进行日期类型，数值类型，字符串类型相互转化。
to_char():日期，数值转化成为字符串。
to_date():字符串转化成日期。
to_number():字符串转化成为数值。

to_char(日期，字符串)
将日期转化成为"年-月-日"显示
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy') year,to_char(sysdate,'mm') month,to_char(sysdate,'dd') day from dual;
在以上的显示中，会出现日期的前导0的问题，如果要解决问题：则可以使用fm去掉前导0.
select to_char(sysdate,'fmyyyy-mm-dd') from dual;
如果要求显示的是日期+时间:
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;（12小时）
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;（24小时）


to_char(数值，格式字符串)：
select to_char(112334343235,'999,999,999,999,999,999') from dual;
select to_char(112334343235,'L999,999,999,999,999,999') from dual

;(L,则默认的将字符串前加入当前系统的默认语言对应的货币符号)


to_date(字符串，格式字符串):
select to_date('1981-1-21','yyyy-mm-dd') from dual;

to_number()将字符串转化成数值,但是在oracle中可以不去使用这个函数。
select to_number('1')+to_number('2') from dual;
select '1'+'2' from dual;
在oracle中可以自行进行to_number()函数的调用


通用函数（重点）
nvl():处理null
decode:多值的判断if...else...

nvl:
求年薪（comm+sal）*12
select ename,(comm+sal)*12 from emp;
出现的问题是有空值。comm是有null
null：传播值，null进入任何表达式进行计算，则得到的结果一定null
select ename,(nvl(comm,0)+sal)*12 from emp;

decode():
decode(列名|数值，'数值1'，'替换值1'，'数值2'，'替换值2'，'数值3'，'替换值3'...)
select
decode(job,'CLERK','办事员','SALESMAN','销售员')
from emp;（如果不在替换值的范围内，则会用空值代替）

select
decode(job,'CLERK','办事员','SALESMAN','销售员','ANALYST','分析员','PRESIDENT','总裁','MANAGER','老大')
from emp;


多表查询：(重点，重点)
在查询中如果使用一张以上的表单，则叫做多表查询。
对于dept,emp多表查询：
select * from emp,dept;
如果只是采用上面的方式进行多表查询，得到结果一定是产生了笛卡尔积
如通要避开笛卡尔则需要使用关联操作。
select * from emp,dept where emp.deptno=dept.deptno;
注意：尽量要避开使用多表查询。

如果在使用多表查询过程，表的名字重复，或者表名太长，我可以别名的方式：
select * from emp e,dept d where e.deptno=d.deptno;

查询出雇员的编号，姓名，部门编号，部门的名称，位置
select e.empno,e.ename,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno；

查询出每个雇员的姓名，工作岗位，雇员的直接上级领导的名字。
需要将emp进行自身关联。
select e.ename,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno;
扩展问题：以上的要求里，再加上部门名称。
select e.ename,e.job,m.ename,d.dname
from emp e,emp m , dept d
where e.mgr=m.empno and e.deptno=d.deptno;

思考：要求查询出每个雇员的姓名/工资/部门名称/工资在在公司的等级/
领导的姓名/领导的工资所在公司的等级。
将问题进行分解：
1 每个雇员的姓名，工资，部门名称，工资所在公司的等级。
select e.ename,d.dname,s.grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and (e.sal between s.losal and s.hisal);
2 领导的姓名/领导的工资所在公司的等级。
select e.ename,d.dname,s.grade,m.ename, m.sal,ms.grade
from emp e,emp m,dept d,salgrade s,salgrade ms
where e.deptno=d.deptno and (e.sal between s.losal and s.hisal)
and e.mgr=m.empno and (m.sal between ms.losal and ms.hisal);
使用decode函数

进行值的替换
select e.ename,d.dname,decode(s.grade,1,'第五级',2,'第四级',3,'第三级',4,'第二级',5,'第一级')
,m.ename, m.sal,decode(ms.grade,1,'第五级',2,'第四级',3,'第三级',4,'第二级',5,'第一级')
from emp e,emp m,dept d,salgrade s,salgrade ms
where e.deptno=d.deptno and (e.sal between s.losal and s.hisal)
and e.mgr=m.empno and (m.sal between ms.losal and ms.hisal);


左右连接：（重点）
select * from emp,dept where emp.deptno=dept.deptno;
但是反馈回来的数据中，只有10,20,30部门,其中40部门是没有的。
（+）在=的左边，左连接
（+）在=的右边，右连接
查询出所有雇员的姓名，及领导的姓名
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno;

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


组函数统计（重点，重点）
组函数：
count():求出记录的个数
max();
min();
avg();
sum();

select count(empno) from emp;
select max(sal) from emp;
select min(sal) from emp;
select max(ename) from emp;
select avg(sal) from emp;
select sum(sal) from emp;

分组统计：group by
求出每个部门员工的数量：
select deptno,count(ename)
from emp
group by deptno;
求出每个部门的平均工资：

以下的两种情况会使用分组函数：
1 程序中使用group by，并定制了分组条件。
2 如果不使用group by，只能单独的去使用分组函数。

按照部门进行分组，并显示每个部门的名称，以及每个部门的员工数量。
select d.dname,count(e.empno)
from dept d,emp e
where d.deptno=e.deptno
group by d.dname;

显示出平均工资大于2000的部门编号和平均工资。
select deptno,avg(sal)
from emp e
where avg(sal) > 2000
group by deptno;
会出错，where语句中不能去使用统计函数.只能写在having.
select deptno,avg(sal)
from emp e
group by deptno
having avg(sal) > 2000;


显示非销售人员工作的名称以从事同一工作的雇员的月工资总和。并且要满足从事同一工作的员工的
工资合计大于5000，输出结果按月工资的合计升序排序。
1 非销售的工作人员
select * from emp where job != 'SALESMAN';
2 按照工作种类分组，求和
select job,sum(sal)
from emp
where job != 'SALESMAN'
group by job;
3 对分组的条件进行限制，工资的综合大于5000
select job,sum(sal)
from emp
where job != 'SALESMAN'
group by job
having sum(sal)>5000;
4 以升序的方式进行排序
select job,sum(sal) s
from emp
where job != 'SALESMAN'
group by job
having sum(sal)>5000
order by s;

select max(avg(sal)) from emp group by deptno;

子查询：
在一个查询中嵌入了一个查询，则当前这个查询叫子查询。
查询出比编号为7654工资要高的全部雇员信息。
select * from emp
where sal>(select sal from emp where empno=7654);
注意：在SQL中，所有的子

查询必须写在()里面.

子查询的操作可以认定一下三类：
单列子查询：返回的内容是一个一列的内容。
单行子查询：返回多个列，单是只有一条记录。
多行子查询：返回多条记录。

查询出工资比7654要高，同时与7788从事相同岗位的所有雇员的信息。
select *
from emp
where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);


查询出工资最低的雇员姓名，工作，工资。
select * from emp
where sal=(select min(sal) from emp);

查询出部门名称，部门的员工数，部门的平均工资，部门收入最低的雇员的姓名。
1 可以从emp表中解决的问题：
select deptno,avg(sal),count(empno)
from emp
group by deptno;
2 dept关联
select d.dname,a.avg,a.ce
from dept d,(select deptno,avg(sal) avg,count(empno) ce from emp group by deptno) a
where d.deptno=a.deptno;
3 收入最低的雇员：
select d.dname,a.avg,a.ce,e.ename
from dept d,(select deptno,avg(sal) avg,count(empno) ce,min(sal) m from emp group by deptno) a,emp e
where d.deptno=a.deptno and e.sal=a.m;


在子查询中嵌入查询的符号：
in
any
all

in指定的是一个查询的范围。
查询出每个部门的最低工资的雇员信息。
select * from emp
where sal=(select min(sal) from emp group by deptno);
出现了错误，因为单行子查询出现了多行。
select * from emp
where sal in(select min(sal) from emp group by deptno);


any与in的功能相似。
对于以上的SQL替换成ANY
=any功能等同于in
select * from emp
where sal=any(select min(sal) from emp group by deptno);
>any:比集合里最小的值还大。
select * from emp
where sal>any(select min(sal) from emp group by deptno);


ALL
>all：比最大的还大


数据库的更新操作。
inset,update,delete

如果要把emp建立副本。
create table myemp as select * from emp;

添加数据的操作：(格式)
insert into 表名(字段1，字段2，字段3...)values(值1,值2,值3...);
insert into 表名 values(值1,值2,值3...);
标准格式：
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7935,'小胖子','ceo',7369,'14-3月-2012',1000,100,10);（建议在程序中使用的一种方式）
简写的方式（当前插入记录的值包含了所有的字段）
insert into myemp
values(7926,'三胖子','ceo',7369,'14-3月-2012',1000,100,10);



修改数据：
update 两种情况的数据
1 修改全部的数据
2 修改部分数据(where)

将所有雇员的奖金全部修改为1000
update myemp set comm=1000;
将小胖子的工资改为1001
update myemp set sal=1001 where empno=7935;
注意问题：不要轻易的对数据库里的数据做全部的更新。要进行备份。
将7369,8899,7788,9632的领导的奖金取消。
update myemp set comm=nul

l
where empno in (select mgr from myemp where empno in (7369,7499));

删除数据：
delete：
删除全部
删除部分（where）
注意：删除的时候不要全部删除。
删除7899员工。
delete from myemp where empno=7839;
删除全部领取奖金的雇员：
delete from myemp where comm is not null;
删除所有的内容：
delete from myemp;


练习：
1 列出至少有一个员工的所有部门。
select deptno from dept where deptno in (select distinct deptno from emp);

2 列出薪水比"SMITH"要多的所有员工。
select ename,sal from emp where sal>(select sal from emp where ename='SMITH');

3 列出所有员工的姓名及其领导的姓名。（king要有）
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno(+);

4 列出受雇佣日期早于直接领导的所有员工的编号，姓名，部门名称
select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.hiredate<(select hiredate from emp where empno=e.mgr);

5 列出部门的名称和这些部门的员工信息，同时列出没有员工的部门编号
select * from emp e,dept d where e.deptno(+)=d.deptno;

6 列出所有'CLERK'的姓名及部门的名称，部门的人数。
select e.ename,d.dname,a.ce from emp e,dept d,(select deptno,count(ename) ce from emp group by deptno) a where e.deptno=d.deptno and e.deptno=a.deptno and e.job='CLERK';



oracle中常见的数据类型：
字符串类型：char,varchar,varchar2(节省空间，推荐使用)
数值类型:number
number(n):整数。数值的长度n，int代替
number(m,n):小数，n,标识的小数位的长度，整数的长度是m-n.
date：日期类型
clob: 超大的文本对象类型,4G
blob: 二进制类型，4G


表单的建立：create table
create table myemp as (select * from emp);
只复制结构，而 不要表单中的数据：
create table temp as (select * from emp where 1=2);

person:
pid varchar2(18) 编号
name varchar2(200) 姓名
age number(3) 年龄
birthday date 生日
sex varchar(2) 性别，默认的值男

create table person(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default '男'
);

删除表单：
drop table 表名称;

修改表结构：（了解）
增加字段：address（varchar2(20)）
alter table person add(address varchar2(20) default '南沙');

修改字段：
alter table person modify(name varchar2(15) default '没有名字');]


表的重命名：
rename person to tperson;
user：已经存在，存放用户的。


约束(重点，重点)
主键约束：唯一，非空,primary key
唯一约束：
非空约束：
外键约束:
检查约束：check
建立主键约束的 一：
create table person(
pid varchar2(18) primary key,
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default '男'
);
建立主键约束的 二：
create table p

erson(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default '男',
constraint person_pid_pk primary key(pid)
);
非空约束：not null;
create table person(
pid varchar2(18),
name varchar2(200),
age number(3) not null,
birthday date,
sex varchar2(2) default '男',
constraint person_pid_pk primary key(pid) );
唯一约束：unique
外键约束：foreign key
create table book(
id number(11) primary key not null,
name varchar2(30),
price number(5,2),
pid varchar2(18),
constraint person_book_pid_fk foreign key(pid) references person(pid));