oracle笔记

oracle笔记

1. 数据文件（dbf）
2. 控制文件（ctl）
3. 日志文件（log）
   验证是否安装成功
   sqlplus 用户名/密码
   oracle 监听目录

D:\app\xuefen.lv\product\11.2.0\client_1\network\admin\tnsnames.ora

3

用||可以把两列或多列查询的结果合并到一列中

select empno,ename||job from emp;

select * from emp for update;解锁

去除多列重复的数据

select distinct deptno，job from emp;

oracle中主要的数据类型

1. 字符型

   varchar（10）定长的字符型数据

   char（2）定长的字符型数据

   varchar2（20）变长的字符型数据

2. 数值型

   Number（4） 不带小数点的数值

   number（8,2）数据的总长度为8位，小数点后占两位

3. 日期型

   DATE

drop table users;
create table users(
id number(4) primary key,
>        name varchar(10),
>        password varchar2(10),
>        sex char(2),
>        birthday date,
>        sal number(8,2)
> );  
> select * from users;

4

select * from emp where sal (not) between 2000 and 3000;
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal<=2000 or sal>=3000;

select * from emp where job (not) in('MANAGER','ANALYST');
select * from emp where job='MANAGER'or job='CLERK';
select * from emp where job!='MANAGER' and job!='CLERK';

select * from select * from emp where ename like '%A%';
select * from emp where ename like 'A%';
select * from emp where ename like '_A%';
select * from emp where ename like '%\%%' escape '\';

select * from emp where comm is (not) null;

not->and ->or

select * from emp order by sal asc;
select * from emp order by sal desc;
select empno,ename,(sal*12) "nianxin" from emp order by "nianxin"; 

5 oracle 函数

字符函数

select ename from emp where lower(ename)='smith';
select ename from emp where upper(ename)='SMITH';
select ename,initcap(ename) from emp where initcap(ename)='Smith';

select empno||ename,concat(empno,ename) from emp; 
select ename,substr(ename,1,2) from emp;
select ename,instr(ename,'A') from emp;
select sal,lpad(sal,10,'#'),rpad(sal,10,'*') from emp;
select ename,replace(ename,'A','a') from emp;

数值函数

select round(46.223,2) from sys.dual;
select trunc(46.223,2) from sys.dual;
select ename,sal,mod(sal,300) from emp;

日期函数

select sysdate from sys.dual;

转换函数

select sal,to_char(sal,'$999,999,00')from emp;
select to_number('$88.00','$999,99.00')from sys.dual;
select * from emp where hiredate= to_date('1980-12-17','yyyy-mm-dd');

6 oracle组函数

其他函数

nvl 如果是null 做其他处理 nvl2 有则变量1 没有 变量2

select empno,ename,sal,comm,(sal*12+comm) "年收入1",(sal*12+nvl(comm,0)) "年收入2" from emp 
select ename,job, nvl2(job,job,'没工作') from emp;
--nullif 如果相同返回null 如果不同返回第一个表达式的值
select ename,job,nullif(length(ename),length(job)) from emp;
-- decode 和switch case 差不多
select job,sal,decode(job,
'CLERK',sal*1.1,
'SALESMAN',SAL*1.2
,'MANAGER',SAL*1.4) AS "修订工资" from emp;

组函数

avg sum max min count

-- avg，sum只针对数值型的数据
-- max min count可以针对不同类型的数据
select max(sal),min(sal),avg(sal),sum(sal),count(sal) from emp;
-- count(*) 查询数据总条数
-- count(字段) 忽略空值
select count(*) from emp;
select count(comm) from emp;
-- 所有的组函数都忽略空值

--计算平均奖金
select sum(comm)/count(*),avg(nvl(comm,0)) from emp;

--对数据进行分组后，使用组函数
-- 出现在查询列表中的字段要在组函数中或者group by子句中
select deptno,max(sal) from emp group by deptno;

--使用多字段分组
select deptno,job,max(sal) from emp group by deptno,job;

--having 对分组的数据进行筛选
select deptno,max(sal) from emp 
where deptno is not null 
group by deptno
having max(sal)>=3000
order by deptno; 

--组函数嵌套必须有group by子句 最多嵌套两层
select max(max(sal)) from emp group by deptno;

7 多表查询

-- 笛卡尔积
select e.*,d.* from emp e,dept d;
-- 等值查询 一一对应
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
-- 非等值查询 两个表之间没有父子关系 用between and 链接两个表
select e.* ,s,* from emp e,salgrade s where e.sal between s.losal and s.hisal;
-- 自查询 自连接不适合操作大表
-- 查询自己的老板名字
select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.empno=m.mgr;

-- 层次查询语句
select level,empno,ename,mgr from emp 
connect by prior empno=mgr
start with mgr is null
order by 1;

-- 外连接 在等值查询的基础上，可以查询不满足等值条件的数据


-- 左连接 加号在右边
select e.*,d.* from emp e,dept d where e.deptno=d.deptno(+);

-- 右连接 加号在左边
select e.*,d.* from emp e,dept d where e.deptno(+)=d.deptno;

-- 交叉连接 笛卡尔积 下面两个效果相同
select e.*,d.* from emp e cross join dept d;
select e.*,d.* from emp e,dept d;
-- 自然连接  在父子关系上，自动匹配两个表中列明完整相同的字段，在相同名称的字段上做等值操作
-- 参照列上不能使用前缀
-- 没有参照列的时候 产生笛卡尔积
-- 参照列的类型不同报错
select e.empno,e.ename,deptno,d.dname,d.loc from emp e natural join dept d;

-- join... using(相同的字段)
select e.empno,e.ename,deptno,d.dname,d.loc from emp e join dept d using(deptno);

--join on 等值连接 n个表的等值连接 需要n-1个join...on子句
select e.*，d.* from emp e join dept d on (e.deptno=d.deptno);

-- 外连接
-- 左外连接 可以把左边表中不满足等值条件的数据查询出来
select e.*,d.* from emp e left outer join dept d on (e.deptno=d.deptno);
-- 右外连接 右表中的数据全部显示出来
select e.*,d.* from emp e right outer join dept d on (e.deptno=d.deptno);
-- 全连接 左右两表中数据都显示出来
select e.*,d.* from emp e full outer join dept d on (e.deptno=d.deptno);


查询语句创建复制表
create table dept_bak as select * from dept;
-- union 把两个结果集合并到一个结果集
-- union 去除重复的数据
select * from dept_bak union
select * from dept;
-- union all 不去除重复数据
select * from dept_bak union all
select * from dept;

8 子查询

-- 子查询先执行 主查询使用子查询的查询条件
select * from emp where sal>(select sal from emp where empno=7566);
-- 子查询的分类 根据子查询的返回结果区分
-- 单行单列的子查询 使用单行比较操作符
select * from emp where sal<(select sal from emp where empno=7566);
-- 子查询结果是单行单列仍可使用单行比较操作符
select * from emp where sal<(select avg(sal) from emp);

-- 多行单列子查询 使用多行比较运算符 in，all，anay
--使用in
select * from emp e where e.job 
in(select job from emp where sal>3000);
--使用all 大于号表示大于最大值 小于号表示小于最小值
select * from emp e  
where e.sal>all(select sal from emp where deptno=20);
-- 多行多列子查询 可以使用in比较运算符
--成对的比较
select * from emp e
where (MGR,JOB) IN (SELECT MGR,JOB FROM EMP WHERE EMPNO=7566 OR EMPNO=7369);
-- 非成对的比较 拆分成多个多行单列的子查询，分别使用in运算符
select * from emp e
where MGR IN (SELECT MGR FROM EMP WHERE EMPNO=7566 OR EMPNO=7369)
and JOB IN (SELECT JOB FROM EMP WHERE EMPNO=7566 OR EMPNO=7369);
-- 子查询是出现在from后面的，提供数据源，子查询是一个虚拟表
select 部门编号,maxsal,avgsal
from(
    select deptno 部门编号,
           max(sal) maxsal,
           min(sal) minsal,
           avg(sal) avgsal,
           sum(sal) sumsal
           from emp
           where deptno is not null
           group by deptno)
           where 部门编号=20

9 DML 语句

增删改

DML语句引起事务

事务

把多个相关的操作捆绑成一个逻辑单元，要么全都成功，要么全都失败

对于事务 开始于第一个操作

结束于

1.要么提交结束，所有的操作都成功

2.要么回滚结束，所有的操作都失败，回滚到事务开始之前的状态

--在事务没结束之前，只有当前用户可以看到对数据库的修改操作，其他用户看不到
--事务可以回滚的方式结束，所有的操作被放弃，回滚到事务开始之前的状态
rollback;
--事务可以以提交的方式结束，对数据库的修改被永久的保存，其他用户可以看到被修改的内容
commit;

--增
insert into dept(deptno,dname,loc) values(50,'销售部',1000);
-- 一次性插入多条数据，复制表中的数据，把查询结果当做数据插入表中
insert into dept_bak select * from dept;

--修改
-- 使用update语句的时候，在事务没结束之前，该数据会被锁住，其他的用户无法修改这条数据
-- 事务结束之后，该条数据的锁被放开，其他用户可以操作这条数据
update emp set ename='张2',job='经理',sal=1500 where empno=8001;

--删除
delete from emp where deptno>40;


--表的备份 同步处理
--合并语句
merge into dept_back d
using dept s
on (d.deptno=s.deptno)
when matched then
  update set d.dname=s.dname,d.loc=s.loc
when not matched then 
  insert values(s.deptno,s.dname,s.loc);

10 事务

create table student(
       sid number(4) primary key,
       sname varchar2(10),
       grade varchar2(20),
       sex char(2) default '男',
       birthday date
);

insert into student values(1000,'张','Java就业班','女','12-8月-1987');
-- 在不影响数据的情况下 对表做出修改，对表的修改主要是对字段的修改
-- 主要操作 1.添加字段 2. 删除字段 3. 修改字段（修改字段类型，修改字段的长度）

-- 添加字段
alter table student
      add  tel varchar2(11);
--修改字段
-- 1.在该字段没有数据的时候，字段的类型，字段的长度都是可以修改的
alter table student
      modify tel number(11);
-- 2.对缺省值的修改，不会影响已存在的数据
alter table student
      modify sex char(2) default '女';

insert into student (sid,sname,grade,birthday) 
values(1003,'张3','java基础班',to_date('1995-08-24','yyyy-mm-dd'));
--3. 字段已存在数据 不能修改字段的类型 可以修改长度
-- 增大可以 减小数据长度不能小于已存在的数据长度
alter table student modify grade varchar2(20);
-- 删除字段
alter table student drop column addr;
-- 使用子查询建表
create table dept_back
as select deptno,dname from dept;
--别名即为创建表的表名
create table dept_back
as select deptno "部门编号",dname "部门名称" from dept;

create table dept_back
(部门编号,部门名称,位置编号)
as select * from dept;

--删除表的数据 truncate比delete删除速度快 慎用
-- truncate属于DDL语言 不能回滚
truncate table dept_back;
-- 表名重命名
rename student to students;

----数据库约束---
--约束是一个独立的数据库对象
--创建约束的操作可在建表时进行 也可以建好表通过alter table 添加


create table student(
       sid number(4) primary key,
       sname varchar(20) not null,
       grade varchar(20) not null
);

11 数据库对象

事务开始于第一个DML语句

事务的结束点：

​ 手动结束

​ 执行commit或rollback

​ 自动结束

​ 自动提交 DDL或DCL（数据库控制语句）

​ 自动回滚

-- 事务中使用标记点，标记点只在事务中有效
--事务结束后 所有的标记点都会失效
insert into dept values(70,'人力',1000);
--设置标记点
savepoint a;
delete from dept d where d.deptno>30;

savepoint b;
update dept d set d.dname='人力部' where d.deptno=10;
-- 回滚到b标记点
rollback to b;
commit;
--发生错误
rollback to a;

事务处理的ACID特性

1. 原子性 （事务是一个工作单元，所有的工作要么在数据库中保存，要么完全回滚，全部不保留）

2. 一致性（事务完成或撤销后，都应处于一致状态）

3. 隔离性 （多个事务同时进行，他们之间互不干扰，）

4. 永久性 （事务提交后，所做的工作被永久的保存下来）

   数据库对象

   表 视图 索引

12 约束 表的关联

唯一约束，保证该字段的数据不能重复，或字段组合不能重复，但可以为null

-- 约束关键字 unique
--唯一约束可以作用在单个字段上 为列级约束
create table student (
       sid number(4) primary key,
       sname varchar2(20) not null,
       email varchar2(20),
       constraints email_uni unique (email)
);
insert into student values(1000,'张1','zhangyi@163.com');
insert into student values(1001,'张1','zhanger@163.com');

--唯一约束也可以作用在多个字段上 为表级约束
create table student (
       sid number(4) primary key,
       first_name varchar2(20),
       last_name varchar2(20),
       constraints name_uni unique(first_name,last_name)
);

--主键不能用实体的业务数据做主键 用一个和实体无关的流水号当主键
--主键约束： 从功能上 相当于唯一并不为空
-- 主键约束作用在单个字段上 
create table student(
       sid number(4),
       sname varchar2(20),
       constraints pk_stu primary key(sid)
);

--联合主键 主键约束作用在多个字段
create table student(
       first_name varchar2(10),
       last_name varchar2(20),
       grade varchar2(20),
       constraints pk_stu primary key(first_name,last_name)
);

多对一

外键约束

外键约束可以重复 可以为null

外键约束关系到两个表的两个字段之间的关系

--多对一用主外键来实现
-- 在外键约束下 在建表的时候 先建父表 再建子表
--父表
create table dept1(
      deptno number(4) primary key,
      dname varchar2(10)
);
--子表
create table emp1(
       empno number(4) primary key,
       ename varchar2(10),
       job varchar2(10),
       dept_no number(4),
       constraints fk_emp foreign key (dept_no) references dept1 (deptno)
);

--在外键约束下，在添加数据的时候，先添加父表 再添加子表
insert into dept1 values(10,'销售部');
insert into dept1 values(20,'开发部');
insert into dept1 values(30,'事业部');

insert into emp1 values(1000,'张0','销售员'，10);
insert into emp1 values(1001,'张1','销售员'，10);
insert into emp1 values(1002,'张3','工程师'，20);
insert into emp1 values(1003,'张3','销售员'，null);

--删除数据时，先删除子表中相关联的数据，再删除父表数据
delete from emp1 where empno=1000;
delete from emp1 where empno=1001;
delete from dept1 where deptno=10;

父表中的主键或唯一键可以被其他表引为外键

--在建立好表后 通过alter table在给表添加约束
alter table emp2
add constraints fk_emp2 foreign key (dept_no) references dept1(deptno);

一对一

一对一用主外键来实现

create table card (
       cid number(4) primary key,
       cname varchar2(10)
);

create table person(
       pid number(4) primary key,
       pname varchar2(20),
       cid number(4),
       constraints fk_per_card foreign key (cid) references card(cid),
       constraint cid_uni unique (cid)
);

insert into card values(1000,'身份证');
insert into card values(1001,'身份证');

insert into person values(1,'张1',1000);
--报错
insert into person values(2,'张2',1000);

多对多

需要引入关系表

--1.关系表中可以引用两个表的主键当外键，同时把外键设置为联合主键 
create table student (
       sid number(4) primary key,
       sname varchar2(20)
);

create table course(
       CID number(4) primary key,
       cname varchar2(20)
);
create table stu_cour(
       sid number(4),
       cid number(4),
       constraint fk1 foreign key (sid) references student (sid),
       constraint fk2 foreign key (cid) references course (cid),
       constraint pk_stu_cour primary key(sid,cid)
);

--2. 关系表中用两个表的主键当外键，关系表有自己独立的主键
create table stu_cour1(
       scid number(4) primary key ,
       sid  number(4),
       cid  number(4),
       constraint fk3 foreign key (sid) references student (sid),
       constraint fk4 foreign key (cid) references course (cid)
);

insert into student values(1,'zhang1');
insert into student values(2,'zhang2');
insert into student values(3,'zhang3');
insert into student values(4,'zhang4');

insert into course values(1,'java');
insert into course values(2,'javaweb');
insert into course values(3,'javaee');
insert into course values(4,'java1');
insert into course values(5,'java2');

insert into stu_cour1 values(1,1,1);
insert into stu_cour1 values(2,2,1);
insert into stu_cour1 values(3,4,4);
insert into stu_cour1 values(4,3,2);


select * from student;
select * from course;
select * from stu_cour1;

13 索引 视图

索引即为目录

索引是数据库的对象之一，索引是为了加速对表中数据行的检索而创建的一种分散的一种存储结构。

索引是针对一个表而建立的，它是由数据页面以外的索引页面组成的

索引类型：

唯一索引：唯一索引是不允许其中任何两行具有相同索引值的索引。 当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

非唯一索引：
非唯一索引是相对唯一索引，允许其中任何两行具有相同索引值的索引。 当现有数据中存在重复的键值时，数据库是允许将新创建的索引与表一起保存。这时数据库不能防止添加将在表中创建重复键值的新数据。

主键索引：
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
聚集索引（也叫聚簇索引）：
在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。 如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

什么情况下使用索引：

1、较频繁地作为查询条件的字段

2、查询中排序的字段

3、查询中统计或分组统计的字段

4、表的字段唯一约束

不适用索引：

1、表记录太少

2、唯一性太差的字段不适合建立索引

3、更新太频繁地字段不适合创建索引

4、不会出现在where条件中的字段不该建立索引

数据重复且分布平均的表字段

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为 50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。比如说性别字段。

创建索引的两种方式
--1.自动创建：oracle会自动为主键和唯一键创建索引
--删除约束的时候，索引也自动删除
alter table emp
add constraints ename_uni unique (ename);

alter table emp
drop constraints ename_uni;
--2.手动创建约束 查询的时候经常为查询的字段应添加索引 
--添加索引后查询更快
--创建索引 删除索引
-- 索引关键字 index
create index ename_index on emp(ename);

drop index ename_index;

视图对象

视图不占物理存储空间，它只是一种逻辑对象。可将其看成一个”虚表”

什么情况下会用到视图？

比如说一个比较复杂的查询不想每次都写很多语句，就可以写个视图。下次查询的时候是需要使用select * from视图名就可以了。

或者给特定用户开放某些表的读取权限，但要加一些行和列的限制，也可以写个视图。

视图的好处，可以主要分为四点：

第一点： 使用视图，可以定制用户数据，聚焦特定的数据。

解释：

在实际过程中，公司有不同角色的工作人员。

以销售公司为例：， 采购人员，可以需要一些与其有关的数据，而与他无关的数据，对没有任何意义，我们可以根据这一实际情况，专门为采购人员创建一个视图，以后他在查询数据时，只select * from view_caigou 就可以了。

第二点：使用视图，可以简化数据操作。 *解释：*

在使用查询时，在很多时候要使用聚合函数，同时还要显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能会很长，如果这个动作频繁发生的话，则可以创建视图。以后要用的话，只需要select * from view1就可以了。

第三点：使用视图，基表中的数据就有了一定的安全性

因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，故可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图，不可以随意的更改 和删除，可以保证数据的安全性。 **

第四点：可以合并分离的数据，创建分区视图

随着社会的发展，公司业务量不断的扩大，一个大公司，下属都设有很多的分公司，为了管理方便，需要统一表的结构，定期查看各公司业务情况，而分别看各个公司的数据很不方便，没有很好的可比性，如果将这些数据合并为一个表格里，就方便多了，这时就可以使用union关键字， 将各分公司的数据合并为一个视图。

--视图是一个虚表 可以从这个表中查询数据
create view hr_view
as
select * from emp;
--权限
create view hr_mgr
as
select empno,ename,job,mgr,hiredate,deptno from emp;
--可在命令行desc 视图名 查看视图结构
--删除视图 不会影响原来的数据
drop view hr_mgr

--如果有这个视图 替换原来的视图
create or replace view view_emp
(员工编号,员工姓名,薪水,佣金)
as 
select empno,ename,sal,comm from emp;

select * from view_emp where 员工编号=7669;

--使用组函数创建视图
create or replace view_emp_sal
as
select deptno,max(sal),min(sal),sum(sal),avg(sal) from emp
where deptno is not null
group by deptno
order by deptno;

--可对视图插入数据 数据会插入到源表中
-- 视图主要是查询操作 设置为只读 不能做插入操作
create or replace view view_empinfo
as
select * from emp where sal>2000
with read only;

14 top-n分析法

rownum

--查询工资最高的三个人 top-n分析法
-- rownum 只适合于<=n的情况
--数据插入顺序决定rownum的顺序 rownum 都是从1开始
select rownum,e.* from (select * from emp order by sal desc)e where rownum<=3;

--可以得到数据
select rownum,sid,sname from student where rownum=1;
--查不到数据
select rownum,sid,sname from student where rownum=2;

--使用子查询解决rownum>2 的问题 rownum必须有别名
select * from (select rownum rn ,s.* from student s) where rn>2;

同义词

给数据库起别名Synonyms

-- Create the synonym 
create or replace synonym dual1
  for SYS.DUAL;


  select sysdate from sys.dual;
create or replace synonym dual1
  for SYS.DUAL;
select * from dual1;

序列

用于维护数据的主键数据sequence

-- Create sequence 
create sequence seq_stu
minvalue 1
maxvalue 99
start with 1
increment by 1;
create table student1 (
       sid number(4) primary key,
       sname varchar2(10)
);

insert into student1 values(seq_stu.nextval,'zhang5');

--查询当前的序列号
select * from student1;
select seq_stu.currval from sys.dual;

15 数据库设计三大范式

第一范式：表中不能包含重复的数据列

第二范式：所有的非主键字段必须完全依赖于 主键字段

第三范式：非主键字段不能依赖于其他的非主键字段，不能有传递依赖

其他

不学pl/sql 和没学过oracle一样

熟练使用pl/sql语句

快捷键

shift+HOME 选中一行sql语句

sql优化

1. 尽量使用列名代替*
2. where 条件从右往左执行 and 尽量把假的放右边
   or 尽量把真的放右边
3. 尽量使用where 不用having

参考：

https://zhidao.baidu.com/question/584823498.html