oracle总结笔记

1.数据的集中语言:

　　1)DML:Data Manipulation Language(数据操纵语言) insert,update,update

　　2)DQL:Data Query Language(数据查询语句) select

　　3)DDL:Data Definition Language(数据定义语言) create,alter,drop,rename,truncate

　　4)DCL:Data control language(数据控制语言) grant(分配权限),revoke(收回权限)

2.事务控制三种方式

　　(Transation control) commit(事务提交),rollback(事务回滚),savepoint(设置事务回滚点)

5.select语法 select [distinct(去重用)] 列名 from 表名 补充:列中数据为数值类型的可以进行算术运算,优先级按普通算术运算来

6.列的别名:别名中可以有空格,特殊字符,大小写敏感,需要双引号,如:select salary*12+200 "total salary" from s_emp;

7.nvl方法将数据库中的空值转换为特定的值,nvl(列名,0):将特定列里的空值转换为0,两个参数的数据类型必须匹配.假如为数值都得为数值,为字符串时,则必须为字符串.

8.describe 表名:用于显示指定表名的表结构(列名,类型),describe index for 表名:用于列出指定表的所有索引

9.数据库中的数据类型:
　　1.数值类型:number(p,s) p:有效位数 s:小数点后的取值位数
　　2.字符串类型:
　　　　char(s):定长字符串
　　　　varchar(s):变长字符串,所有关系数据库通用
　　　　varchar2(s):变长字符串,oracle公司定义
　　3.日期类型:
　　　　date

10.关于varchar和varchar2的区别:
　　1)varchar2把空串等同于null处理,varchar还是空串
　　2)varchar2是内部数据类型,varchar是外部数据类型
　　3)建议使用varchar2,对oracle具有很好的兼容性,
　　4)Oracle以特定格式表示数据，称为内部数据类型。

11.save:将缓冲区内容写入文件(save D:\oracle\my.sql) start:执行sql脚本,start my.sql或@ my.sql exit:退出sqlplus工具

12.order by子句:排序规则 asc:升序,默认取值 desc 降序
　　1)order by是select命令的最后一个子句
　　2)中的null值处理:升序放最后,降序放最前
　　3)order by后可跟内容,可跟多个列
　　select last_name,dept_id,salary
　　from s_emp
　　order by dept_id(默认升序),salary desc;

13.where子句:限制取出记录数
　　1)where比较中的字符串常量严格区分大小写
　　2)where中的操作符:
　　　　1)between and:闭区间,先小值在大值
　　　　　　where salary between 900 and 1000;
　　　　2)in:从给定的列表范围内匹配到值
　　　　　　where salary in(41,43,45);
　　　　3)like:模糊查询
　　　　　　通配符:
　　　　　　　　1)%:代表0或多个字符
　　　　　　　　2)_:代表单一字符
　　　　3)escape:自定义转义字符,让通配符转变成普通字符.
　　　　　　查询last_name以_a开始的?
　　　　　　where last_name like '\_a%' escape '\';
　　　　4)is null:空值判断
　　　　　　where salary is not null;
　　　　5)多条件查询:
　　　　　　1)and:多个条件必须同时满足
　　　　　　2)or:多个条件选一个
　　　　　　3)and的优先级比or的高

14.单行函数:需要处理的行数和返回结果的行数相等
1)字符串处理函数:
　　1)lower:字符串转小写
　　2)upper:字符串转大写
　　3)initcap:字符串首字母大写,其他字符小写
　　4)concat:字符串拼接
　　　　concat(first_name,last_name) 等价于 first_name||last_name
　　5)substr:字符串截取(原字符串,开始位置1开始,取得字符数)
　　　　获取last_name的倒数两位字符
　　　　substr(last_name,length(last_name)-1,2)
　　6)length:字符串长度
2)数值处理函数:
　　1)round(数值,想保留的小数精度)会四舍五入
　　　　如果小数精度为负数时,代表将小数点虚拟往左移,且将虚拟小数点后全部清零.
　　　　round(59,-1) => 60
　　　　round(59,-2) => 100
　　　　round(49,-2) => 0
　　2)trunc(数值,想保留的小数精度),不会进位
　　　　trunc(149,-2) => 100
　　　　trunc(49,-2)=> 0
　　3)mod:取余
3)日期处理函数:
　　伪列函数:没有参数的函数
　　1)查看系统时间:
　　　　select sysdate from dual;
　　2)查看系统时间戳:
　　　　select systimestamp from dual;
　　3)行号
　　　　select rownum from s_emp;
　　　　rownum注意:
　　　　　　1)<,<=无限制
　　　　　　2)=,>=只对1有意义
　　　　　　3)>无意义,可以使用between and.
　　　　　　4)rowid:行地址
　　　　　　select rowid from e_customer;

日期普通函数:
　　1)months_between:两个日期相差多少个月(日期格式要一致)
　　2)add_months(‘15-MAY-95’,6)：15-MAY-95往后推6个月的日期作为返回值
　　3)next_day(‘15-MAY-95’,’FRIDAY’)：15-MAY-95后面离得最近的星期五的日期作为返回值
　　4)last_day(‘15-MAY-95’)：15-MAY-95这个月的最后一天的日期作为返回值
　　5)round(to_date('16-MAY-95','DD-MON-YY'),'month')
　　　　1)第二个参数为month,天向月进位,逢16进1
　　　　2)第二个参数为year,月向年进位,逢7进1
　　6)trunc:对日期进行舍位

15.类型转换函数:(也算是单行函数)
　　1)to_char(date/number,'fmt'):
　　　　将数值和日期类型转换为字符串类型,第二个参数用单引号,格式区分大小写.
　　　　1)日期格式:
　　　　　　YYYY:4位数字的年
　　　　　　YY:2位数字的年
　　　　　　RR:2位数字的年
　　　　　　MM:2位数字的月
　　　　　　DD:2位数字的天
　　　　　　YEAR:年的英文全拼
　　　　　　HH24:小时的24制
　　　　　　MI:2位数字的分钟
　　　　　　SS:2位数字的秒
　　千年虫问题:就是yy格式的日期,00不知道是1900还是2000
　　oracle9i新增了RR格式的,分四种情况,通过分为00~49和50~99两区间来判别
　　　　2)数字格式:
　　　　　　9:代表一位数字占位符
　　　　　　0:表示1位数字占位符，位数不够前面强制补0
　　　　　　fm:去掉空白和占位符0;
2)to_number:将字符串类型转换成数值类型
　　select to_number('100')
　　from dual;
3)to_date:将字符串转换为日期类型,第一个参数为字符串,第二参数为日期格式.
　　select to_date(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;
　　单行函数注意可以嵌套使用,从内向外进行计算.

16.多表查询:在多个表中查询数据时,需要在表和表间建立连接,一般使用主外键建立连接
　　1)"笛卡尔乘积":多个表中数据记录的全组合
　　　　为了避免产生笛卡尔积:n个表得有n-1个有效的连接条件
　　2)连接种类:
　　　　1)等连接:连接条件使用等号
　　　　2)非等连接:连接条件使用等号以外的其他符号
　　　　3)内连接:根据指定的连接条件进行连接查询,满足连接条件的数据才会出现在结果集
　　　　4)外连接:在内连接的基础上,将其中连接表不符合连接条件的记录加入结果集
　　　　5)自连接:自身表中的列关联自身表中的其他列
　　3)比如:
　　　　1)内连接
　　　　　　select e.last_name,d.name
　　　　　　from s_emp e inner join s_dept d
　　　　　　on e.dept_id=d.id;
　　　　2)左外连接:A left outer join B on 连接条件,在内连接的基础上,将A表中不符合连接条件的数据也加入,因为A表为基准
　　　　　　Oracle特有写法：
　　　　　　select e.last_name,d.name
　　　　　　from s_emp e,s_dept d
　　　　　　where e.dept_id=d.id(+); s_emp表为基表
　　　　3)右外连接:A right outer join B on 连接条件,同上,只是把b表作为基准
　　　　　　Oracle特有写法：
　　　　　　select e.last_name,d.name
　　　　　　from s_emp e,s_dept d
　　　　　　where e.dept_id(+)=d.id;
　　　　　　oracle扩展连接方式：连接条件哪边表的列用（+），则它的记录用null值填充（也可把null当作占位符，没有东西、空占着位置）
　　　　4)全外连接:查询出所有列,和不符合连接条件的以其他边为null补充
　　　　　　select e.last_name,d.name
　　　　　　from s_emp e full outer join s_dept d
　　　　　　on e.dept_id=d.id;
　　　　　　oracle没有特有写法
　　　　5)自连接:查询自身表中关联自身表中的其他列
　　　　　　select worker.last_name,manager.last_name
　　　　　　from s_emp worker,s_emp manager
　　　　　　where worker.manager_id=manager.id(+);
6)集合操作符:
　　1）union:两个集合的并集,去掉重复内容
　　　　select e.last_name,d.name
　　　　from s_emp e,s_dept d
　　　　where e.dept_id=d.id(+)
　　　　union
　　　　select e.last_name,d.name
　　　　from s_emp e,s_dept d
　　　　where e.dept_id(+)=d.id;
2)union all：两个集合的并集,重复内容取两次,操作方法同上
3)minus：第一个集合减去和第二个集合相交的内容
4)intersect:两个集合的交集

17.组函数:(多条数据求出一个结果)
　　1)组函数:
　　　　1)avg:求平均值.
　　　　2)sum:求和.
　　　　3)min:求最小值
　　　　4)max:求最大值,以上可以操作任意数据类型.
　　　　5)count:统计记录条数
　　fx:统计非空记录条数?
　　　　select count(commission_pct) from dual;
2)组函数的注意点:
　　1).组函数和group by配合使用,进行分组,不然默认为算总表的数据为一组
　　2)where中不能使用组函数,对分组条件进行限制应该使用having子句
　　　　例如:查询部门平均工资大于1500的部门id和平均工资？
　　　　select dept_id,avg(salary)
　　　　from s_emp
　　　　group by dept_id
　　　　having avg(salary)>1500;

18.子查询:(将select语句嵌套在其他sql语句中,先执行select子句,将结果传给外围SQL继续处理)
例如:查询部门平均工资大于32号部门平均工资的部门？
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > (select avg(salary) from s_emp where dept_id=32);

取出表中第11到20条数据？
select *,rownum
from s_emp
where rownum<=20
minus
select *,rownum
from s_emp
where rownum<=10;
或者
select *,rownum
from{ //这里只是为了了解from里面也能写select语句
select rownum r,*
from s_emp
}
where r between 11 and 20;
注意:子查询出现在from中时,可以使用order by.

19.数据库设计
1)数据模型开发步聚:
　　需求分析->E-R图->表的实例图->数据库对象
　　1)E-R图:实体-关系图
　　2)实体:对应数据库中的表
　　3)属性:对应数据库中的列
4)关系:对应数据库中的关联约束
2)范式：
　　1)第一范式:所有行数据的属性必须单值(行列交叉处一个值)
　　2)第二范式:所有属性依赖主属性
　　3)第三范式:除了主属性,其他属性间没有依赖关系
3)完整性约束:
1.主键
　　1)唯一标识表中的记录
　　2)取值必须非空和唯一
　　3)一个表上,主键只能有一个
　　4)主键可以分为多列,称为联合主键
　　5)主键分为自然主键(如用户名,有商业意义)或者逻辑主键(如自动生成的id,没有商业意义,低耦合)
2.外键
　　1)建立表和表的关联关系
　　2)所关联PK或UK中的值,或者NULL
　　3)一个表上,外键可以有多个
　　4)外键可以为多列,称为联合外键
　　5)外键可以关联其他表,也可以关联自身表

20.建表
1)简单的建表语句
create table 表名(
　　属性名 属性类型 修饰符(not null:不能为空), //varchar2必须给范围 :varchar2(20)
);
2)设置表属性默认值:default
　　name varchar2(10) default user
3)表名命名规则:
　　字母开始,后可跟字母,数字,_,$,#,1~30个字符长度,不同重名,和关键字冲突
4)数据类型:
　　number,char,varchar,varchar2,date,CLOB(字符型大对象用来存一部小说),BLOB(二进制大对象,如存一部电影)
5)约束默认为SYS_Cn(有列级约束:约束一个列或表级约束:约束多个列)
　　1)not null:不能为空,列级别约束
　　2)unique:限制取值唯一,列级别约束
　　3)primary key:主键约束,必须唯一且不能为空,可以为列级别也能为表级别
列级别:
id number constraint student_id_pk primary key
表级别
constraint student_id_pk primary key(id)
4)foreign key:外键约束,可以加在表上,也可以加在列上.
　　id number(7) constraint emp_id_pk primary key,
　　dept_id number(7) constraint emp_dept_id_fk references dept(id)
5)check:用于检查列中属性是否符合,可以是列级的,也可以是表级的
　　gender varchar2(10) constraint student_gender_c check(gender in('Male','Female'))
6)对表的特殊用法:

1)带约束的建表语句：
create table S_DEPT(
　　id number(7) constraint s_detp_id_pk primary key,
　　name varchar2(25) constraint s_dept_name_nn not null,
　　region_id number(7) constraint s_dept_region_id_fk references s_region(id),
　　constraint s_dept_name_region_id_u2 unique(name,region_id)
);
2)基于已有表建新表:
基于已有表创建新表,只拷贝表结构,不拷贝数据:
create table emp_null
as
select id,last_name,salary,dept_id
from s_emp
where 1=2;(如果想拷贝啥数据,可以改where条件)

21.数据库中的数据操作:
1)DML:数据操作语言:执行DML操作时,需要注意下数据的主外键关联
　　1)insert
　　2)update
　　3)delete
2)TCL:事务:一组相关联的操作,这组操作不可分隔,要么同时成功,要么同时失败
事务的特点:ACID
　　1)原子性(Automicity):原子是不可分割的,同一事务的一组操作也是不可分割.
　　2)一致性(Consistency):事务开始之前数据库处于一致的状态,事务结束以后数据库也能处于一致的状态
　　3)隔离性(Lsolation):一个事务的执行不会影响到其他的事务,oracle的隔离级别为read commited
　　　　1)脏读:事务1读取了事务2未提交的数据
　　　　2)不可重复读:同一查询在同一事务中多次进行,
　　　　3)虚读:同一查询在同一事务中多次进行,这个想了解,自行百度
　　　　4)持久性(Durability):一旦事务被提交,它对数据库的影响是持久的
事务开始:执行sql操作会自动启动事务
事务结束:commit,rollback,DDL/DCL(自动提交),exit
事务未提交前数据的特点:
　　1)数据被写到数据库缓冲区
　　2)DML操作只能在当前会话可见
　　3)其它会话看不到DML操作的结果
　　4)多个事务都操作的数据行被锁定
3)事务的操作
　　1)commit:提交事务,事务操作持久保存
　　2)rollback:回滚事务,事务操作撤销
　　rollback to p6;
3)savepoint:设置回滚点
　　savepoint p6;

22.修改表:
1.修改列操作
1).添加列
　　alter table student
　　add(
　　　　age number(3),
　　　　start_date date default sysdate
　　);
2).删除列
　　alter table student
　　drop column age;
3).修改列
alter table student
modify(
name varchar2(50) default user not null
);
2.修改约束:
1)添加约束,和添加列差不多
2)删除约束
3)石约束失效
alter table student
disable constraint student_id_pk cascade;
4)约束生效
alter table student
enable constraint student_id_pk;

3)对表的其他命令:
1.删除表: cascade constraints:删除父表时,级联地将子表中关联的外键约束一起删除
drop table student cascade constraints;
2.重命名表
rename student to stu;
3.重命名列
alter table stu rename name to last_name;
4.清空表
truncate table emp_42;和delete * from emp_42相似都是起删除表中所有数据的作用
区别:
　　1)truncate是DDL(数据定义语言),是自动提交的,不能回滚,delete是DML(数据操纵语言),可以回滚
　　2)truncate删除表中所有数据,delete删除表中的数据(可根据条件判断)
　　3)truncate操作后表空间会恢复到默认初始化大小,delete则不会释放表空间
5.给表加注释
comment on table stu
is '注释内容'
23.数据字典
1.了解:
1)是oracle存放有关数据库有关信息的地方
2)数据字典的表不能直接访问,但是可以访问数据字典的视图
2.分类
1)user_*:该视图存储了当前用户所拥有的对象信息.
1.USER_TABLES，找到数据库表的信息
select table_name from user_tables;
2.user_constraints 关于用户表的约束信息
select constraint_name,constraint_type from user_constraints where lower(table_name)='student';
3.user_tablespaces 关于表空间的信息
4. user_users 关于用户的信息
5.USER_SEQUENCES 关于用户的序列
6.USER_VIEWS 关于用户的视图
7.USER_INDEXES 关于用户的索引
2)all_*:该视图存储了当前用户能够访问的对象信息,并不需要拥有,只要能访问就行
例子同上,
3)dba_*:该视图存储了数据库中的所有对象的信息(前提是当前用户拥有此权限)

24.序列:
1.产生唯一数值,常作主键
2.创建
create sequence my_seq
increment by 2
start with 100
maxvalue 500
minvalue 100
nocycle
nocache;
3.使用:(第一次要先插入nextval,currval才会有值)
select my_seq.nextval
from dual;
select my_seq.currval
from dual;
4.修改序列,使用alter
注意:最小值必须小于等于当前值,start with不能改
5.删除
drop sequence my_seq;

25.视图
1.数据库中的映象
2.创建、使用
grant create view to briup;
create view emp41_view
as
select last_name,salary,dept_id
from s_emp
where dept_id=41;
desc emp41_view;
3.修改视图：
//将视图进行DML操作进行修改
update emp41_view
set salary=1000
where lower(last_name)='smith';
select last_name,salary
from s_emp
where lower(last_name)='smith';

//将视图设置为只读,则修改视图只能下面这么修改了
create or replace view emp41_view
as
select last_name,salary,dept_id,start_date
from s_emp
where dept_id=41
with read only; //设置只读
4.分类:
简单视图：涉及一张表、没有组函数、没有分组操作，可以进行DML
复杂视图：涉及多张表、有组函数、有分组操作，满足任何一点，不可以进行DML
5.限制:
with check option：用视图所做的修改，通过该视图要能够查看到修改后的结果
6.删除:
drop view my_view;

26.索引:
1.概念:数据库中的对象,用来加快查询,比对速度,采用BTree树,减少比较次数,提高效率
2.索引的分类:
1)自动创建的索引:primary key,unique
2)手动创建的索引:自己写sql
3.索引的创建:
create index my_index
on emp_42(id,last_name);
4.什么时候建立索引?
1）表中数据量大
2）索引列经常作为查询条件
3）查询出的数据量小
4）修改操作少
5.删除
drop index my_index;

27.事务的特性:
1.原子性:
事务要么全部成功,要么全部回滚
2.一致性：
事务的完整性约束没有被破坏,如果遇到违反约束的情况会回滚
3.隔离性
每个事务不会受其他事务的影响
4.持久性:
事务一旦提交就会永久生效

28.事务的隔离级别:
1.read uncommitted:可以读取尚未提交的数据,哪个问题都不能解决
2.read committed:只能读取已经提交的数据;可以解决脏读 --oracle默认的
3.repeatable read:可重复读:可以解决脏读和不可重复读和幻读 --mysql默认的
4.serializable:串行化:可以解决脏读 不可重复读和虚读 --相当于锁表

29.事务的容易出现的错误:
1.脏读:事务T1修改了数据,事务t2在事务t1提交前读到了这行数据
2.幻读:事务t1读取了满足条件的一个数据集,事务t2插入了一行或者多行数据满足了T1的选择条件，导致事务T1再次使用同样的选择条件读取的时候，得到了比第一次读取更多的数据集。
3.不可重复读:事务T1读取了一行数据，事务T2接着修改或者删除了该行数据，当T1再次读取同一行数据的时候，读到的数据时修改之后的或者发现已经被删除