oracle的自我总结

oracle

1.oracle或者关系型数据库中的对象
 表     由行和列组成的存储单位
 视图   在逻辑上是由一个或多个表的数据子集组成
 序列   用来自动生成主键值
 索引   用来提高一些查询的性能
 同义词  对象的替换名称
 程序单元   在PL/SQL中声明的 存储过程，函数，包

2.sql  sqlplus  pl/sql的区别

 sql 一种和数据库服务器进行通信的语言
 sqlplus  一种和oracle数据库进行通信的工具，它可以用来执行 sql和pl/sql 命令行模式
          DML,DDL,DCL语句都是sqlplus语句
 pl/sql  procedure language oracle特有的过程化语言 因为sql不具备过程控制功能

3.sql命令的分类：
查询：select
数据操作语言：DML:insert.update.delete       （表中数据层面操作）
              DDL:create.alter.drop.truncate （表层面操作）
事务控制：commit.rollback.savepoint
数据控制语言：DCL:grant.rovoke

4.关系型数据库的数据的完整性约束：（4个方面）
 （目的：关系型数据库中的数据有意义，使用更加方便）
 1）实体性约束
 主键：表中一行数据唯一标识符
 （非空且唯一）
 2）外键约束
 一个表中的外键指向另一个表中的唯一列
 外键：多张表之间的关系
 （可以为空）
 3）列的约束
 必须定义数据类型（数值类型，字符类型，（oracle）日期类型）
 4）用户自定义约束
 gender，phone（长度11位）

5.表
列：column 属性
行：tuple  元祖

6.where是筛选

***************************************************************8

查询：

1.desc +表名====查看表结构

2.
char()     定长字符串   char(10)
varchar()  变长字符串   varchar(10)
varchar2() oracle特有的数据类型

3.COL 列名 FOR 格式

4.select 语句排序 (order by)
order by：desc 降序
           asc 升序
 order by可以添加的子项:
 1)列名（不论在select子句中是否出现过都可以排）
 2)别名
 3)表达式
 4)select子项编号

5.select 条件限定 (where)
where:  and  or  否定

6.去重 distinct
放在select后面 select distinct

7.顺序:
select 2 列筛选
from 1 先把整张表格拿出来
where 3 行筛选
orser by 4

8.转义字符
where last_name like '\_%' escape '\';

9.Function函数
单行函数：调用函数时，输入数据进入一行，返回一行
多行函数：                      若干行

单行函数：字符操作：lower,upper,initcap,concat,substr(name,start,lengrh),length,nvl
   数值操作: round(,-1),trunc,mod
   日期操作: sysdate,systimestamp 无参数的单行函数：伪列
      rownum,rowid
      months_between,add_months,next_day,last_day
      round,trunc
   数据类型转化操作:to_char,to_number,to_date('YYYY,MM,D,DD,DDD,YEAR,MONTH,ddsp,DY,HH24:MI:SS AM')
 
多行函数：group 组函数

10.存储过程和函数的区别

11.连接查询
目的：多表查询（多个二维表）
 1）内连接，自然连接
 等值连接和不等值连接
 （=）       （不用=）

 2） 外连接 （左，右，全（并集））
 为避免笛卡尔乘积，建立联系条件个数-1 （最少）

 3） 自连接：自己的思路方法的多表连接

12.集合操作符
  union           union all
  全外连接        全集（取两次）
  minus           intersect 
  大-小 补集      重复值 交集

13.组函数
where中不可以使用组函数！！！
AVG，COUNT，MAX ，MIN ，STDDEV，SUM，VARIANCE

14.子查询 
子查询是一个sql语句中嵌套了另外一个select语句
子查询与链接查询的优劣 
 1） 子查询更好理解，更灵活 
 2） 链接查询效率更高

15.rownum原理 ：
 因为ROWNUM是对结果集加的一个伪列，即先筛选结果集之后再加上去的一个列
 (强调：先要有结果集)，并且rownum总是从1开始,故每次筛选剔除掉一行不符合要求的
 后整个结果集当做一个新的结果继续筛选，所以每次where结果需要先筛选出来结果，
 才能有rownum。

16.nvl函数
查询员工信息 员工每个月工资（考虑提成比例）
select last_name,salary*(1+nvl(commission_pct,0)/100)
from s_emp;

17.where 和 having 的区别
where筛选行记录         having分组记录
子句包含单行记录        只包含group by 后面的
执行在前                执行在后
相同：都不允许用行别名

*******************************************************************************************

DDL:

1.DDL:create.alter.drop.truncate （表层面操作）

2. 表与表的关联关系
 (转化成表之后 用外键来表示关系)
  外键的位置
 1:1
 任意一方即可
 所以注意 E-R图中的属性，少了外键 创建表记得加上
 1:n
 外键建立在多的一方
 n:n
 使用桥表进行连接
 联合外键，第三张表（桥表）
 构建一张桥表，桥表中两列外键分别指向两张表

3.范式
 降低耦合性

 第一范式 行和列交叉的地方只有一个值

 第二范式  表中的每一列只能通过主键列去唯一标识
 
 第三范式  表中的非主键列不能依赖于其他非主键列

create：

4.约束条件：
声明的时候约束，列级约束
创建完表中约束，表级约束

主键约束:primary key
既可以写成列级约束，也可以写成表级约束
  列级：
  creat table customer(
   id number(7) constraint cus_id_pk primary key,
   name varchar2(25) constraint cus_name_nn not null,
   phone varchar2(20) constraint cus_phone_uq unique,
   emp_id number(7) constraint cus_emp_id_fk refeerces s_emp(id)
  );
  表级:
  creat table customer(
   id number(7),
   name varchar2(25),
   phone varchar2(20),
   emp_id number(7),
   primary key(id)
  );

外键约束:foreign key ... references
 既可以写成列级约束，也可以写成表级约束
  creat table customer(
   id number(7),
   name varchar2(25),
   phone varchar2(20),
   emp_id number(7),
   primary key(id),
   foreign key(emp_id)
       references s_emp(id)
  );

非空约束:not null
只能写成列级约束

唯一约束:
既可以写成列级约束，也可以写成表级约束
  creat table customer(
   id number(7),
   name varchar2(25),
   phone varchar2(20),
   emp_id number(7),
   primary key(id),
   foreign key(emp_id)
       references s_emp(id),
   unique(phone)
  );

检查型约束:check
既可以写成列级约束，也可以写成表级约束
  create table student(
  gender varchar2(10) check(gender in('男','女'))
  )

  insert into student
  values('你好');//违反约束

  insert into student
  values('男');

5.联合主键
多个列组合起来成为主键
多个列的组合唯一，每个列都是非空
 create table t_s_tab(
  t_id number(7),
  s_id number(7),
  primary key(t_id,s_id)
 )

6.添加默认值default
 create table stu(
  id number(7),
  name varchar2(25) default '李狗蛋'
 );

7.通过使用子查询创建表
（原表中只有not null约束会复制到新表中）
 create table jd1710_emp
 as
 select id,last_name,salary
 from s_emp;

8.数据字典表
 1.通过模糊查询
 从需求中提取关键字
 从dictionary 找到 相关的若干张系统表

 select table_name
 from dictionary
 where upper(table_name) like 'USER%TABLE%';

 2.使用desc命令 查看这些表有哪些列

 desc user_tables

 3.选择合适的咧进行查询

 select table_name,num_rows
 from user_tables;

 查询briup用户创建的所有视图

 select table_name
 from dictionary
 where upper(table_name) like 'USER%VIEW%';

*******************************************************************************************

DML:insert.update.delete     （表中数据层面操作）
1.插入数据
  insert into jd1710_emp
  values(30,'yuyu',9999);

2.更新数据
  updete jd1710_emp
  set last_name='jack'
  where ...

3.删除数据
  delete from

4.delete 和 truncate 区别：
 truncate和delete作用一样,也是删除表中数据

 delete是DML语句,删除数据后需要自己手动的提交事务或者回滚事务

 truncate是DDL语句,删除数据后会自动的帮我们提交事务

*******************************************************************************************

事务控制：commit.rollback.savepoint

1.事务:是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行。
 一组sql命令的集合（DML,select,DDL,DCL）
 代表业务逻辑中的一个行为，这个行为涉及到的sql命令会有多条

2.
一个事务中可以包含多条：DML,select
一个事务中只能包含一条：DDL,DCL

3.事务的特性ACID：

 Atomicity：原子性
   不能分割成更小的单位，控制事务的最小单位是事务
 Consistency:一致性
   数据一致，事务开始之前，执行之后，数据没有改变
 Isolation:隔离性
   两个事务之间不应该发生信息的交换
   事务操作时，操作的数据不应被别人操作
 Durability:持久性
   事务完成之后事务中所操作的数据会被持久保存
   操作的数据上一次事务遗留下的数据

4.cummit
 只有事务提交之后，事务中操作的数据才能看到
 1.oracle 需要手动提交实务
 2.mysql  自动提交实务
 3.jdbc   自动提交事务

5.哪些行为能触发提交:
DDL,DCL,exit
DDL:create，alter,drop,

6.rollback 事务的回滚
  实务没提交之前，都可以用roolback回到事务开启之前的状态
  rollback;    会让实务结束（行为执行失败）
  rollback to p1;  不会让实务结束,可以继续回滚

7.savepoint  回滚点
  记录某些时候
  当事务结束的时候，该事物
savapoint p1;
rollback to p1;

（*******）
8.alter
添加新的列
alter table jd1710_emp
add(first_name varchar2(25) unique);

删除列 考虑外键
alter table jd1710_emp
drop colnum first_name;

修改列，
只能修改数据列的类型 和添加删除not null约束
如果原列中，有null值，不能添加not null 约束。

添加约束
alter table jd1710_emp
add constraint emp_salary_uq
unique(salary);

删除约束
select constraint_name,
 constraint_type,table_name
from user_constraints
order by table_name
;

alter table jd1710_emp
drop constraint SYS_C004196;

删除表：
drop table stu;

修改表名:
rename jd1710_emp to j_emp;

修改列名：
alter table j_emp
rename column salary  ‘’；

9.清空表空间：
truncate table j_emp;
1.DML
2.delete 不会立刻把数据删除
而是把要删除的数据添加一个标记，
select不会显示出带有标记的数据，从而得到删除数据的效果

*******************************************************************************************

序列（oracle特有）
使用方式类似于 Random
 new Rondom().
 nextInt(10);

1.创建序列
2.通过nextval关键字，取得序列中的下一个值
create sequence id_seq
increment by 2
start with 10;

currval
nextval

select id_seq.nextval
from dual;            每次取出的值都不一样

insert into j_emp
values(id_seq.nextval,'hello',1000);

*******************************************************************************************

视图
（目的：权限的划分，保证视图的安全性）

1.视图：是表中的一种映像
(里面的数据从别的表中取出来的虚拟表)

2.创建视图：
create view 视图名
as
子查询

3.分类：简单视图，复杂视图

简单视图：创建视图使用的子查询，数据只来自一张表，仅用了where和order by
  create view v_emp
  as
  select id,last_name,salary
  from s_emp
  where salary>1500;

 1）查           可以理解为 v_emp = select语句   视图解释成为一组sql语句
                 （数据库账号，查看表结构）
   （屏蔽数据，权限的划分，保证视图的安全性）
 2）增删改
    如果对视图进行操作，会影响原表          update v_emp                             
    如果对原表进行操作，会影响视图          set last_name='hello world'
                                                   where id=5;

复杂视图：创建视图使用的子查询，子查询使用了group by 或者有连接的查询
create view v_emp_dept
as
select e.id id,e.last_name name,e.salary salary,d.name dept_name
from s_emp e,s_dept d
where e.dept_id=d.id;

 1）查
 2）不允许删除，修改，增加

4.修改视图(直接替换视图就行)
create or replace view v_emp
as
select id,last_name,title
from s_emp;

5.设置视图为只读
 with read only
在简单视图后面加

create or replace view v_emp
as
select id,last_name,title
from s_emp
with read only;

6.检查不允许更改建立视图所用的条件列
with check option

create or replace view v_emp
as
select id,last_name,title
from s_emp
where last_name='jack'
with check option;

update v_emp
set last_name='tom'
where id=20;   错误

7.删除视图
drop view 视图名

*******************************************************************************************

索引

1.索引是提高在列上的一种数据库对象
目的是提高针对此列的查询效率
（平衡二叉树）

2.判断是否使用索引（oracle会自行判断）
  1）基于规则，超过标准值
  2）基于代价，考虑性价比，耗费资源多少

3.
自动添加索引:主键约束，唯一性约束
手动添加索引：数据大，非空大数值，大范围查询少量数据
 create index 索引名
 on 表名（列名）
      删除索引：drop index

*************************************************************************
其他:

1.round 和 trunc
select round(45.67) from dual;    46
select round(45.67,1) from dual;  45.7
select round(45.67,2) from dual;  45.67
select round(45.67,-1) from dual; 50
select round(45.67,-2) from dual; 0
select round(55.67,-2) from dual; 100
9)执行下面的sql语句，看看执行后结果，分析
trunc函数的含义？
select trunc(45.67) from dual;     45
select trunc(45.67,1) from dual;   45.6
select trunc(45.67,2) from dual;   45.67
select trunc(45.67,-1) from dual;  40
select trunc(45.67,-2) from dual;  0
select trunc(55.67,-2) from dual;  0