Oracle数据库知识点总结

1.关系型数据库(RDBMS)：基于关系模型来组织数据的数据库，属于第二代数据库。
  关系模型：用一个二维表,行(记录)和列(字段)的形式来保存数据。关系模型里面的关系 主要反映到以后学习的主外键.
2.三个名词
sql:结构化的查询语句，操作oracle数据库的语言  增删改查  
sqlplus:oracle软件自带的可以输入sql，且将sql执行结果显示的终端的一个工具。注意区分sql语句和sqlplus语句。
pl/sql:程序化的sql语句，在sql语句的基础上加入一定的逻辑操作，如if for...,使之成为一个sql块，完成一定的功能
3.四种对象
table：表格，由行和列组成，列又称字段，每一行内容为表格的一条完整的数据。
view:  视图，一张表或者多张表的部分或者完整的映射，好比表格照镜子，镜子里面的虚像就是view
除去常见的table和view两种对象以外，oracle数据库还支持如下四种对象
  sequence：序列
  index：索引，提高数据的访问效率
  synonym：同义，方便对象的操作
  program unit：程序单元，pl/sql操作的对象
5.sql的五大分类:
1.Data retrieval：数据查询  
select
2.DML：数据操纵语言(行级操作语言)：操作的是表格当中一条一条的数据
insert update delete
3.DDL：数据定义语言(表级操作语言)：操作的内容为表格(对象)
create alter drop truncate rename
4.transaction control：事务控制
commit rollback savepoint
5.DCL：数据控制语言
grant revoke
6.delete，truncate区别：
delete：  删除表中的一条或者多条记录，该操作需要提交事务
truncate：清空表中数据,该操作不需要提交事务
7.*  /  +  -
select语句永远不对原始数据进行修改；
乘除的优先级高于加减；
优先级相同时，按照从左到右运算；
可以使用括号改变优先级。
8.对null值得替换运算
nvl()函数
nvl(a,b)函数作用为： 如果a为空返回b,否则返回a;
9.关键词
distinct关键词，可以将显示中重复的记录(行)只显示一条,只能放在select关键词后面
     语法：select distinct col_name,col_name...
           from tb_name;
sysdate关键字
显示时间:当前时间
select sysdate from dual;
10.注意点：日期和字符只能在单引号中出现；
11.命令
sqlplus 输入用户名  再输入密码
sqlplus 用户名  直接输入密码即可
sqlplus 用户名/密码 直接登录
sqlplus "/as sysdba" 超级管理员登录(很危险  操作系统对应的用户才可以登录,在linux里面只有oracle用户才可以登录)
password 用户名  给用户改密码
show user 查看当前用户
$cls 清屏
$其他cmd命令

list   查看缓存中的sql语句
append   在[定位]的那一行后面追加新的内容
in      在[定位]的那一行下面插入新的一行
change   替换[定位]的那一行中的某些字符串 
c/老的字符串/新的字符串
del 删除[定位]的那一行内容
n   后面加内容可以重写这一行
!   后面接终端命令 !clear:清屏 
/   执行缓存sql命令
clear buffer:清空当前缓存的命令

COLUMN last_name FORMAT a15;
可以简写为:
col last_name for a15;


COLUMN salary JUSTIFY LEFT FORMAT $99,999.00
. salary JUSTIFY LEFT : 仅仅改变列名显示为左齐
. FORMAT $99,999.00: 控制显示格式为前面加 $ 符, “，”为分隔符, 0或9代表数字(通配符)，0表示替换对齐数值,位数不足会补足,可以混合使用。


12. select col_name,... 【列名】
from tb_name   【表名】
where 。。。       【筛选语句】
group by。。。 【分组】
having。。。 【组函数筛选】
order by col_name [asc|desc],...【排序asc升序】
13.lower 把字符转为小写
 例如:把'HELLO'转换为小写
 select lower('HELLO')
 from dual;
   upper 把字符转换为大写
 例如:把'world'转换为大写
 select upper('world')
 from dual;
   initcap 把字符串首字母转换为大写
 例如:把'hELLO'转换为首字母大写,其余字母小写
 select initcap('hELLO')
 from dual;
   to_char 把日期转换为字符
把当前日期按照指定格式转换为字符串
select to_char(sysdate,'yyyy')
from dual;
   like:模糊查询
   通配符，即可以代替任何内容的符号
% ：通配0到多个字符      
_ : 当且仅当通配一个字符 
select id,last_name,salary
from s_emp
where last_name like 'C%';
14.多表查询
连接查询：1.等值连接
 2.不等值连接
 3.外连接
左外连接select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+);
或者 俩者是等价的
select last_name,dept_id,name 
from s_emp left outer join s_dept
on s_emp.dept_id=s_dept.id;
注意:outer可以省去不写
右外连接
全连接select last_name,dept_id,name 
     from s_emp full outer join s_dept 
             on s_emp.dept_id=s_dept.id;
 4.自连接
15.where和having区别


a.where和having都是做条件筛选的
b.where执行的时间比having要早
c.where后面不能出现组函数(组函数执行次序比where晚)
d.having后面可以出现组函数
e.where语句要紧跟from后面
f.having语句要紧跟group by后面


group by和having的关系:
  1.group by可以单独存在,后面可以不出现having语句
  2.having不能单独存在,有需要的话,必须出现在group by后面


order by语句
  1.如果sql语句中需要排序,那么就一定要写在sql语句的最后面
  2.order by后也可以出现组函数


16.实体-关系图：  一对一关系 (1 ∶ 1)
 一对多关系 (1 ∶ N)
 多对多关系 (M ∶ N)


17.主键和外键
   主键:
1).能做主键的列必要满足【非空唯一】的特点
2).只要满足非空唯一的任何列都可以做主键(并不是说只要非空唯一，就是主键)
3).可以让表中一个【有意义的列做主键】,比如说学号,它既表示学生学号又作为表中的主键,因为这个列满足非空唯一的条件
4).也可以找一个【没有意义的列做主键】,就是用来唯一标识一行记录的
5).我们可以让【多个列联合】在一起做表中的主键,那么它就是【联合主键】,要求这几个列的值联合在一起是非空唯一的
    外键:
一对一关系: 
随便弄
一对多关系: 
必须通过主外键(多:外键；一:主键)
s_dept(主表) s_emp(外表)
id(主键)  name   id  dept_id(外键)
1).表中的某一个列声明为【外键列】,一般这个外键列的值都会【引用于另外一张表的主键列的值】(有唯一约束的列就可以,不一定非要引用主键列)
2).另外一张表的主键列中出现过的值都可以在外键列中使用,【没有出现过的值,绝对不能使用】
3).【外键列值可以为空】,前提是这个外键列在表中不做主键。
我们也可以把表中的外键列当做主键来使用(只有满足非空唯一的要求就可以)
4).如果把B表中的【联合主键】的值引用到A表中做外键,那么A表引用过来的时候也要把俩个列的值都引用过来,那么它们在A表中就会作为一个【联合外键】出现
18.建表
create table 表名(
列名1 数据类型 列级约束,
列名2 数据类型 列级约束,
列名3 数据类型 列级约束,
列名4 数据类型 列级约束
);
create table 表名(
列名1 数据类型 列级约束,
列名2 数据类型 列级约束,
列名3 数据类型 列级约束,
列名4 数据类型 列级约束,
表级约束1,
表级约束2
);
1.)常见的数据类型
a.char
b.varchar
c.varchar2  主要字符类型（长度可以变化  可以存null）
d.number
e.date
   特殊建表
***     只拿来s_dept的表结构,没有数据
create table test2
as
select * from s_dept
where 1=2;
将s_dept的表结构和表中的数据全部复制过来
  as
select * from s_dept;
只复制表中某几个列以及数据
create table test3
as
select id,last_name,salary 
from s_emp;
19.列的约束
列的约束就是对这个列中的值的要求(可有可无)
1.主键约束  PRIMARY KEY  primary key
2.外键约束  FOREIGN KEY  foreign key
3.唯一约束  UNIQUE       unique
4.非空约束  NOT NULL     not null
5.check约束 CHECK     check(类似枚举)
6*外键约束 references 主表(主键);
列级约束/行级约束:在列的后面直接加上的约束
create table 表名(
列名1 数据类型 列级约束,
列名2 数据类型 列级约束,
列名3 数据类型 列级约束,
列名4 数据类型 列级约束
);
     表级约束:在所有列声明以后加的约束
create table 表名(
列名1 数据类型 列级约束,
列名2 数据类型 列级约束,
列名3 数据类型 列级约束,
列名4 数据类型 列级约束,
表级约束1,
表级约束2
);
   desc student; 查看表结构
   drop table student;删除表
   注意：先建立主键表，再建立外键表，有【次序之分】。
20.表级约束和列级约束对比
1).表级约束和列级约束所写的【位置不一样】
2).【not null】约束不能用表级约束来声明
3).表级约束和列级约束声明【语法稍有所不同】
4).如果要声明的约束为联合主键、联合外键、联合唯一的时候,就一定要用表级约束.
21.insert语句
insert into 表名[(列名1,列名2,...)] values(对应值1，对应值2,...);中括号里的可以省略但必须再添加时按次序全部添加。
   update语句
update 表名 set 列名=值[,列名=值,...][where ...]
   delete语句
delete from 表名 [where ...];
如果外键列中用了这个值则不能成功删除
22.数据库事务
1.）只有Dml语句执行时才会产生事务。
2.）commit（提交）、rollback（事务回滚）、DDL语句都会结束事务。
回滚点/保存点 savepoint
例如:
DML语句1
savepoint A
DML语句2
savepoint B
DML语句3
rollback to A或B  回滚到B还可以回滚到A，回滚到A则不能回滚到B。
3.）事务特征ACID(了解)
原子性：Atomicity
同时成功或者同时失败
一致性：Consistency 
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
隔离性：Isolation 
事务操作应该相互独立
持久性：Durability 
事务所做的影响 ，在事务结束之后应该能够是持久的。
4.)脏读  主要针对update操作。 一个事务A读到另一个事务B中修改过但是还没有提交的数据


  不可重复读  主要针对update操作。 一个事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改并提交了,所以就出现了事务A里面读一个数据俩次,但是读到的结果是不同的。


  幻读  主要针对的是insert/delete操作。事务A第一次用where条件筛选出了10条数据,事务A第二次用通样的where条件筛选出的却是11条数据,因为事务B在事务A的第一次和第二次查询直接进行了插入操作,并且插入的这个数据满足事务A的where筛选条件.

********解决方法：read-uncommitted  不提交也能读
 read-committed    提交之后才能读 解决了脏读（oracle中用这个Set Transaction Isolation Level Read Committed）
 repeatable-read   解决了脏读和不可重复读
 serializable      三个问题都解决了
23.修改表和约束（alter语句）
1.）alter可以修改表的结构，具体格式为:
   alter table 表名 add|drop|modify|disable|enable ...;
   给表中的【列添加约束】
这个约束相当于之前的表级约束
alter table t_user
add constraint user_name_un
unique(name);
24.DDL操作
******* 1.）truncate截断  与  DML 中delete效果相同但是后者需要commit；
2.）rename重命名 rename 表名 to 新表名
25.添加注释（给表）comment on table 表名 is ‘内容’；
查看注释：select * from user_tab_comments 
 where table_name=upper('students')；
26.创建序列
create sequence 序列名;
[INCREMENT BY n]  每次拿出值加多少
[START WITH n]    初始值从几开始
[{MAXVALUE n | NOMAXVALUE}]  最大值
[{MINVALUE n | NOMINVALUE}]  最小值
[{CYCLE | NOCYCLE}]  到了最大值后是否循环(如果【循环会从1开始】,不循环出错)
[{CACHE n | NOCACHE}] 每次在缓存里面放多少个值.
a.获得序列中的下一个值 【序列名.nextval】
  select seq_test.nextval
from dual;
b.查询序列中当前的值是多少【序列名.currval】
  select seq_test.currval
from dual;
27.视图
1.）创建视图(【由select语句获得视图】)
create or replace view 视图名字
as
sql语句
[with read only;]（只能读）
[with check option;]（通过视图修改的信息，必须可以通过这个视图能够显示出来,否则就操作失败）
2.）删除视图
drop view 视图名字;
3.）查看视图内容
select *
from v_test;
28.用户权限控制
1.）删除用户 drop user zhangsan cascade;
2.）回收权限 revoke operator on object from user;