oracle 学习笔记

===数据库====
1.什么是数据库?
数据库就是按照一定的方式来组织,管理和存储数据的仓库.
2.数据库的基本概念:
1)数据库:DataBase(DB)
2)数据库管理系统:DataBase Management System(DBMS)
3)数据库管理员:DataBase Administrator(DBA)
3.常用数据库:
Oracle
DB2,Sybase
Sql/server,mysql
DM
....
4.oracle安装
注意在安装的时候不要改动字符集和尽量关闭防火墙.


5.SQL 介绍
SQL:结构化查询语言(Structured Query  Language),专门用于数据库中的标准数据查询的语言.
6.SQL的分类:
1)DQL:数据库查询语言
用于查询数据库中的数据
2)DML:数据库操作语言
用于增加,修改,删除数据
3)DDL:数据库定义语言
用于操作表.比如表的创建,删除,修改
4)DCL:数据控制语言
用于控制用户权限
5)TCL:事务控制语言
用于控制事务
7.介绍SQLPLUS
1)sqlplus是Oracle提供的一个命令行执行工具
2)进入sqlplus方式:
a.直接通过cmd里面输入sqlplus,然后进行用户名以及密码的校验.
b.直接通过开始栏里面的Oracle里面的命令行工具进入,然后进行用户名以及密码的校验.
3)简单几个命令:
a.conn+用户名/密码 : 连接数据库,使用相关用户 scott/tiger
b.disconn:断开当前用户连接
c.exit: 跳出sqlplus工具
d.show user:展示当前登录用户的用户名
e.select * from tab;展示当前登录用户下的所有表对象
8. oracle中的数据类型:
1)字符型:char,varchar,varchar2
varchar不推荐使用
char:固定长度,如果定义char(10),那么不管存小于或等于10的任意大小数据,在数据库中,都占10个字符长度空间.
varchar2:可变长度,如果定义varchar(10),那么不管存小于或等于10的任意大小数据,在数据库中,占的空间和数据字符长度相等.
2)日期型
Date
3)大字段
blob:主要用来存放非字符的文件.如音频,视频等.
clob:用来存放文本文件.最大能存放4G.
4)数字类型
a.number:能够表示的任意数字.
b.number(p):表示的整数.长度是p.
c.number(p,s):p表示长度,s表示小数点后的位数
1234.567 --->number(6,2) --->1234.57
1234.567 ---->number(5)-->1235
12345 --->number(5,-2)--->12300
12365--->number(5,-2)-->12400

desc +表名:查看表结构

9.DQL:数据库查询语言
1)查询语句
select + 列名,列名 + from + 表名;
2)别名
列名 [as] + 自定义名称
3)数字运算
包括: +-*/
注意:
a.空值跟任意数据做运算结果都为空
b.数字运算只能作用在数字列,不能使用在非数字类型的字段.
4)nvl函数:
格式:nvl(p1,p2):如果p1为空,则显示p2,否则,显示p1
例:查找emp表中所有人的姓名以及月收入
select ename,sal+nvl(comm,0) from emp;
5)列名拼接 : ||
select ename||','||job from emp;
6)去重复:distinct
使用在想要去重复的字段前
查找emp表中存在的部门编号
select distinct deptno from emp;
7)带条件的查询: where
查询一些附带某些条件的语句
select ename,deptno from emp where deptno=10;
--条件语句
a.比较操作符: <,>,>=,<=,!=,=,<>
在Oracle中,字符串使用单引号引起来,并且所有的数据时区分大小写,字段名,表名不分.

b.逻辑运算符: and,between.. and .. ,or,in(),not in(),is null,is not null,like
例:查找emp表中工资在1000~3000之间的所有人员信息
select ename,sal from emp where sal>=1000 and sal<=3000;
select ename,sal from emp where 
sal between 1000 and 3000;
例:查找emp表中工资是1100或1600的所有人员信息
select ename,sal from emp where sal=1100 or sal=1600;
select ename,sal from emp where sal in(1100,1600);
例:查找emp表中没有奖金的所有人
select ename,comm from emp where comm is null;
c.like 模糊查询
通配符: 匹配一个字符 : _
匹配多个字符: %
例:查找emp表中姓名第二个字母为L的所有人员信息
select * from emp where ename like '_L%';
8)排序 order by + 需要排序的列名 + asc(升序)|desc(降序)
例:查找emp表中sal大于1000,并且按照工资收入降序,如果工资一样,在按奖金降序
select ename,sal,comm from emp where sal>1000 order by sal desc,comm desc;
--伪表 dual
oracle自带的表,这个表中只有较多的dummy字符字段并且没有任何数据,不允许插入或删除操作.
作用:
测函数,测序列值
9)单行函数:
--字符函数
1)nvl(p1,p2)
2)Upper/Lower:大写/小写
select upper('asd') from dual;
select lower('AA') from dual;
select empno,lower(ename),lower(job) from emp;
3)initcap:名字初始化(首字母大写,其他小写)
select initcap('tom adson') ename from dual; 结果:Tom Adson
4)concat(p1,p2):字符串连接,类型||
select concat('11','22') from dual;
5)length:长度
select ename,length(ename) from emp;
6)substr:截取一段长度
语法:substr(string,start[,count])
返回"string"中截取的一部分.截取的部分从"start"位置开始,取"count"个字符.如果不指定"count",则取完.
注意:
下标值从1开始.
select substr('adbcefg',2) from dual;-->dbcefg
select substr('adbcefg',2,3) from dual;-->dbc
select substr('adbcefg',-2) from dual;-->fg
select substr('adbcefg',-3,2) from dual;-->ef
7)replace:替换
语法:replace(string,if,then)
select replace('abcefg','b','h') from dual; -->ahcefg
8)填充Lpad/Rpad
语法:Lpad(string,number,str)
表示number减string长度的空白使用str来填充.Lpad左填充,Rpad右填充
select ename,lpad(sal,length(sal)+1,'$') from emp;
9)trim:去掉收尾的空白
select length(trim(' tom ')) from dual;
10)查找 instr
语法:instr(string,char[,number]):查找char这个字符在stirng中第一次出现的位置.
如果number为正,string从左边数number的位置来时查,number为负,则从右边,反向查找.查不到返回0.
select instr('abcdeabc','c',4) from dual;-->8
 select instr('abcdeabc','c',-4) from dual;-->3
 select instr('abcdeabc','c') from dual;-->3
--数字函数
1)round 四舍五入
round(number1[,number2])
select round(1314.5288) from dual;-->1215
select round(1314.5288,2) from dual;-->1314.53
select round(1314.5288,-2) from dual;-->1300
2) mod 取余
mod(m,n):返回m被n除后的余数.然而如果n为0,则返回m.
3)数字截取:trunc
此函数跟round语法一样,只截取不会四舍五入.
4)绝对值:abs
--日期函数
1)last_day(date):求某个日期所在月份的最后一天
例:获得当前日期
select sysdate from dual;
select last_day(sysdate) from dual;
2)next_day(date,char):date之后的星期几
例:当前日期之后的一个星期日
select next_day(sysdate,1) from dual;
3)months_between(date1,date2):拿到两个日期之间的月数.
例:emp表中员工工作了多少个月
select ename,months_between(sysdate,hiredate) from emp;
4)add_months(date,number):
在指定日期date上面做月份的增减操作.
select sysdate,add_months(sysdate,1) from dual;
--转换函数
1)日期转换字符串
to_char(date,mode):
将date以指定格式(mode)转化成字符串
select to_char(sysdate,'yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyy"年"MM"月"dd"日"') from dual;
select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;
2)字符串转换成日期
to_date(string,mode):
将以指定格式(mode)string转化成日期
select to_date('2014-05-01','yyyy-MM-dd') from dual;
select last_day(to_date('2014-05-01 14:11:22','yyyy-MM-dd hh24:mi:ss')) from dual;
3)字符串转数字
to_nubmer(string)
select to_number('123')+123 from dual;
--通用函数
1) case 字段名 [when 值 then 值1]* else .. end 
select empno,ename,deptno,case deptno 
when 10 then '会计部'
when 20 then '研发部'
when 30 then '销售部'
else '其他部门' end as 部门 from emp;

2)decode :作用类似case
decode(列名,[值,值1]*,其他情况)
select empno,ename,deptno,decode(deptno,
10,'会计部',20,'研发部',30,'销售部','其他部门') 
from emp;
10)分组函数
1)count:数量统计
例:员工数量
select count(*) from emp;
有部门的员工数量
select count(deptno) from emp;
有几个部门是有员工的
select count(distinct deptno) from emp;
2)max,min,avg,sum:最大值,最小值,平均值,求和
注意:在使用分组函数来进行操作,会自动忽略掉空值.
select avg(nvl(comm,0)) from emp;
11)group by:分组查询
按照想要的分组规则来对数据进行相同数据为一组并且进行分组操作.
例:查询出不同部门里面的员工数量
select deptno,count(*) from emp group by deptno;
查询出不同部门里面不同职位的员工数量
select deptno,job,count(*) from emp group by deptno,job order by deptno;
注意:
出现在select后的列名和group by 之后的列名保持一致.
查询出不同部门里面不同职位的工资大于1000的员工数量
select deptno,job,count(*) from emp where sal>1000 group by deptno,job order by deptno;
where和having:
where + 单行函数的判断 + group by
grounp by + having + 组函数的判断
例:员工数量大于2的部门
select deptno,count(*) from emp group by deptno having count(*)>2;
各部门1985年之前入职的员工数量
select deptno,count(*) from emp where hiredate<to_date('1985-01-01','yyyy-MM-dd')
group by deptno;
员工的平均工资大于1000 的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>1000; 
12)组函数的嵌套
多个组函数嵌套放在查询中.
各部门中最小平均工资是多少
select deptno,avg(nvl(sal,0)) from emp group by deptno having avg(nvl(sal,0))=(
select min(avg(nvl(sal,0))) from emp group by deptno);
查询包含1982年1月1号之后入职的员工并且平均工资大于2000的部门.
对:
select deptno,avg(sal) from emp where deptno in(select deptno from emp where hiredate>=to_date('1982-01-01','yyyy-MM-dd'))
group by deptno having avg(sal)>2000;
错:
select deptno,avg(sal) from emp where hiredate>=to_date('1982-01-01','yyyy-MM-dd')
group by deptno having avg(sal)>2000;
where-->group by --> having
13)多表查询
对多张表进行查询
a.内连接(表1 inner join 表2 on 对等条件)
--等值连接:根据几张表中的关联条件来进行连接.
例:显示员工的姓名,以及所在部门的名称
select e.ename,d.dname
from emp e inner join dept d on e.deptno = d.deptno; 
--不等值连接
select e.ename,d.dname
from emp e inner join dept d on e.deptno <> d.deptno; 

--自然连接(natural join)
把两张表中有关联关系的数据进行自然连接
select * from emp natural join dept;
例:查询出每个雇员的姓名,工资,部门名称,工资所在等级以及领导的姓名及领导的工资和领导工资所在等级.

select e.ename,e.sal,d.dname,s.grade,ee.ename,ee.sal,ss.grade 
from emp e inner join dept d on e.deptno=d.deptno 
inner join salgrade s on e.sal<=s.hisal and e.sal>=s.losal 
inner join emp ee on e.mgr = ee.empno 
inner join salgrade ss on ee.sal<=ss.hisal and ee.sal>=ss.losal;

b.外连接
--左外连接/右外连接 left|right outer join .. on ...
驱动表                  匹配表
select e.ename,d.dname from emp e left outer join dept d on e.deptno = d.deptno;
匹配表                  驱动表
select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;
注意：驱动表中记录去匹配匹配表中的数据，匹配成功则拼成完整记录返回；匹配不成功则补空值返回．匹配表中没有匹配上的则不显示．
--全外连接:full outer join
把连接的表中没有匹配上的数据补空值返回
select e.ename,d.dname from emp e full outer join dept d on e.deptno = d.deptno;
14)子查询
在查询内部还可以包括另外的查询.
例:查询出比编号7934的雇员工资高的员工信息
select * from emp where sal>(select sal from emp where empno=7934);
求出每个部门的最低工资的员工信息
错:
select * from emp where sal in(select min(sal) from emp e inner join dept d on e.deptno=d.deptno group by dname)
对:
select * from emp e inner join (select deptno,min(sal) min_sal from emp e group by deptno) t on e.deptno = t.deptno and e.sal = t.min_sal;

子查询出现位置:
from,where,join,having
例:查出部门名称,部门的员工数,部门的平均工资和部门的最低收入员工姓名.
select e.deptno,d.dname,t.c,t.a,t.m,e.ename
from emp e
inner join dept d on e.deptno = d.deptno 
inner join (select deptno,count(*) c,avg(sal) a,min(sal+nvl(comm,0)) m from emp group by deptno) t 
on e.deptno = t.deptno and e.sal = t.m;
15)伪列:rownum
行号,数据查询出来的每条记录都有一个行号.
select e.*,rownum from emp e; 
例:工资最低的三个员工的信息
错:
select * from emp where rownum<4 order by sal;
对:
select * from (select * from emp order by sal) where rownum<4;


10.DML语言(数据操作语言)
insert into,update,delete
--DDL
创建表:
1).创建一张表:
create table myuser(
id number,
name varchar2(32),
sex char(3),
age number(3));
2).复制一张表:
create table 表名 as 子查询
--复制表结构和数据
create table emp_bak as(select * from emp);
--复制表结构和不要数据
create table emp_bak as(select * from emp where 1=0);
--复制部分表结构
create table emp_bak as(select ename,empno from emp);
3)insert插入
a.insert into 表名(字段名+) values (值+)
insert into emp_bak(empno,ename,hiredate) values(1100,'POLO',to_date('1990-09-09','yyyy-MM-dd'));
b.insert into 表名 values(...)
--批量插入数据
insert into 表名 + 子查询
insert into emp_bak (select * from emp);
insert into emp_bak(empno,ename) (select empno,ename from emp);
4)update(修改)
update 表名 set 字段名=新值,字段名=新值 where ...
update emp_bak set sal=2000,comm=4000 where empno=1002;
5)delete(删除)
delete [from] 表名 where ...
delete from emp where sal is null or sal=0;
11.事务处理
1)事务:就是为了保证数据的完整和一致性,由一组相关的DML语句组成,该组DML语句,要么全部成功,要么全部失败.
2)事务的起止:起于第一条DML语句,结束与commit或者rollback或者DDL语句,或者正常关闭窗口.
注意:事务提交之前,使用DDL语句(隐式提交)会提交事务.

3)事务处理:
a.commit:提交
表示将之前的事务进行提交.提交之后,之前的DML语句才算真正执行.
在commit之前所操作DML语句能查看到结果(Oracle提供的一个试图),并不是真正的操作了执行后结果.
b.rollback:回滚
表示将rollback之前的事务进行回退.取消之前DML语句的操作.
4)事务锁机制:
如果一个用户在进行对某张表的某个数据进行DML操作的时候,其他用户一旦操作这个表,就会被挂起,知道原来的用户把事务结束掉后面挂起的用户的操作才会继续.

5)自定义事务保存点:
savepoint +变量名;
回滚到自定义保存点的位置:
rollback to 想要回退的那个位置的变量名.
savepoint a;
insert into emp_bak 
(select * from emp where sal>3000);
savepoint b;
delete from emp_bak;
rollback to b;
commit;

12.DDL语句
1)创建表,见上面:
default:当创建表时,可以在列的类型后面加default关键字+ 默认值.
create table myuser(id number,name varchar2(20),sex char(3) default '男');

insert into myuser(id,name) values(1,'tom');
2)删除表:
drop table + 表名;
3)表的修改:
a.列的追加
语法:alter table 表名 add(字段名 字段类型,字段名 字段类型,....)
alter table myuser add(age number(3) default 18,sal number(7,2) default 0);
b.列的类型修改
语法:alter table 表名 modify(要修改的列名 类型,...)
alter table myuser modify(sex varchar2(3) default '女');
注意:修改列的类型如果有数据的话,不能违背原数据类型以及长度原则.
比如:一个varchar2类型的字段,不能改成number类型
一个varchar2类型的字段长度为20,不能修改成20以下.
c.修改列的名称
语法:alter table 表名 rename column 原字段名 to 新字段名;
alter table myuser rename column sal to salary;
d.列的删除
语法:alter table 表名 drop column 列名;
alter table myuser drop column age;
注意:列的删除能够删除哪些带有数据的列,数据一起删除.
f.表名修改
rename 原表名 to 新表名
rename myuser to myusers;
4)DDL的数据清空操作:
truncate table 表名;
与DML操作中的delete的区别:
a.操作SQL类型不同,一个是DDL,一个是DML.
b.一个是显示提交事务,一个是隐式提交事务
c.允许回滚的删除时,占用系统资源,比不回滚的效率要慢.
13. 约束
1)主键约束(primary key):
用于唯一标识一行记录.
添加方式:
a.直接在想要的字段类型后加主键约束.
create table myuser(
id number primary key,
name varchar2(20),
age number(3)
);
insert into myuser values(1,'tom',18);
insert into myuser values(2,'jack',18);
b.建表语句之后,加上:
constraint + 错误信息名 + primary key(列名)
create table myuser(
id number,
name varchar2(20),
age number(3),
constraint myuser_id_pk primary key(id)
);
错误信息:可以是中文,格式:表名_列名_约束的简写
2)唯一约束(unique):
表示字段的值唯一.
添加方式:
a.直接在想要的字段类型后加唯一约束.
create table myuser(
id number primary key,
name varchar2(20) unique,
age number(3)
);
b.建表语句之后,加上:
constraint + 错误信息名 + unique(列名)
create table myuser(
id number,
name varchar2(20),
age number(3),
constraint myuser_id_pk primary key(id),
constraint myuser_name_uk unique(name)
);
3)非空约束(not null)
表示列中数据不能为空值.
create table myuser(
id number primary key,
name varchar2(20) unique,
age number(3) not null
);
4)检查约束(check)
限定字段中的数据应符合自定义条件
create table myuser(
id number primary key,
name varchar2(20) unique,
age number(3) not null,
constraint myuser_sex_ck check(sex in('男','女')),
constraint myuser_age_ck check(age between 0 and 200)
);
5)外键约束(foreign key)
表示字段中的数据应符合关联表的主键值或唯一值.
例:设计表:person表,book表
一个人不一定有书,也可能有多本书,一本书必须有拥有者,而且一本书只能有一个拥有者.
person:pid(pk),pname,sex(ck),age(ck)
book:bid(pk),bname(uk),bprice,pid(fk)
create table person(
pid number primary key,
pname varchar2(60) not null,
sex char(3),
age number(3),
constraint person_sex_ck check(sex in('男','女')),
constraint person_age_ck check(age between 0 and 200)
);
create table book(
bid number primary key,
bname varchar2(60) not null,
bprice number(7,2),
pid number not null,
constraint book_bname_uk unique(bname),
constraint book_pid_fk foreign key(pid) references person(pid)
)
--理解子表和主表:
把带有外键并且这个外键的列跟另外一张表的主键相关联的表称为子表,而相关联的这张表为主表.
--子表和主表的删除:
当主表和子表有关联数据,那么不能直接删除主表的有关联的数据.可以先删除子表的相应数据再删除主表的数据.
强制级联删除:
drop table person cascade constraint;
子表中:
constraint book_pid_fk foreign key(pid) references person(pid) on delete [cascade|set null]
cascade:子表中记录可随主表中关联的记录一起删除
set null:主表中关联的记录删除时,子表的外键设为空.

6)约束的修改
a)约束的追加
语法:alter table 表名 add constraint 约束名 约束条件(字段)
alter table myuser add constraint myuser_id_pk primary key(id);
b)约束的查看
select * from user_constraints where table_name='MYUSER'(表名);
注意表名大写.
c)约束的删除
语法:alter table 表名 drop constraint 约束名
alter table myuser drop constraint myuser_id_pk;
d)约束重命名
语法:alter table 表名 rename constraint 旧约束名 to 新约束名;
alter table person rename constraint person_sex_ck to person_sex_nk;
e)禁止约束
alter table 表名 disable constraint 约束名;
alter table myuser disable constraint myuser_id_pk;
f)启动约束
alter table 表名 enable constraint 约束名;
alter table myuser enable constraint myuser_id_pk;
14.集合
并:
union:忽略重复的并
union all:不忽略重复的并

差:
minus 第一个结果集减去第二个有重复数据的结果集
注意:结果集的顺序会影响差的结果

交:
intersect:两个结果集的交集.
注意:
在进行集合的并差交操作之前,需要保证这两个结果集的列类型一致.

15.视图(view)
视图就是一个复杂的查询语句进行封装,为了方便日后的查看和节省查询效率.
创建视图:
create view 视图名 as 子查询
create view myview as select e.*,d.dname from emp e inner join dept d on e.deptno = d.deptno; 
修改:
create or replace 视图名 as 子查询
删除:
drop view 视图名.
视图约束:
create or replace view myview 
as select e.* from scott.emp e where e.deptno=20;
--正对视图的创建条件进行修改
update myview set deptno=20 where empno=7369;
a.不允许修改视图的创建条件:
create view myview as select e.* from emp e 
with check option; 
b.设置视图为只读:
with read only;
16.用户管理
有关用户的操作都要在DBA权限下进行:
--用户的创建
create user xiaoxiao identified by 123;
--用户连接的授权:
grant create session to xiaoxiao;
--用户的角色授权:
grant connect,resource to xiaoxiao;
--修改用户密码
alter user xiaoxiao identified by 321;
--用户的锁定
alter user xiaoxiao account lock;
--用户解锁
alter user xiaoxiao account unlock;
--用户的删除
drop user xiaoxiao cascade;
--授予其他用户表的权限
grant all on scott.emp to xiaoxiao;
--收回权限:
revoke all on scott.emp from xiaoxiao;
--查看权限
select * from user_sys_privs;
17.序列(sequence)
用户产生一系列数字的数据库对象.
序列创建:
create sequence 序列名
[increment by n] 步进,如果为正,则递增,为负,则递减
[start with n] 开始的值
[maxvalue] 最大值
[minvalue] 最小值
使用:
获取序列的下一个值:nextval
获取序列的当前值:currval
select myseq.nextval from dual;
新增数据时,用序列值作为主键值:
insert into myemp(empno,ename) values(myseq.nextval,'xxx');
序列修改:
alter sequence 序列名 需要修改的参数
  序列删除:
drop sequence 序列名;
18.索引
索引就是用于提升查询效率的数据库对象.使用索引可以快速的定位数据,减少硬盘的IO操作次数,从而提高数据的访问效率.
索引的分类:
唯一性索引和非唯一性索引
创建索引的方式:
1)自动创建:在定义主键或者唯一约束时,oracle自动在相应德 字段创建一个唯一性索引.
2)手动创建:手动在某张表以及某些字段上进行非唯一性索引创建
如何手动创建索引:
create index 索引名 on 表名(列名,列名,....)
例:
A.create index emp_ename on emp(ename)--单一索引
B.create index emp_ename_empno on emp(empno,ename)--复合索引
C.create index emp_ename_empno1 on emp(ename,empno)
B和C的区别?
创建复合索引的字段顺序不同,查询排在前面的字段的效率要比后面的高.

索引的删除:
drop index 索引名;

优点:
1)加快了数据的检索效率,有效的避免全表扫描.
2)加快了表与表之间的连接速度
缺点:
1)索引为占用空间,大约消耗的空间大小为对应表的1.2陪.
2)当对表中的数据进行DML操作时,数据的变动会让Oracle去对索引进行更新,这样DML操作的效率降低了.