Oracle 数据库

Oracle 数据库
语法顺序
select from on where group by having order by
执行顺序
from on where group by having select order by


一、关键字语法介绍
1、from on from后面接的是需要查询的表格
on后面接的是表的连接条件和过滤条件
单表查询直接跟上源表的名字
多表查询跟上的是源表的名字和两表之间的连接种类

2、where where语句实现的是对查询表的结果集的筛选
where语句后面跟的是条件表达式(可以是列名、常量，比较运算符，文字值)
between ..and.. 语句表示一个范围，是两边的闭区间范围
in 运算符是表示范围是一个由离散值组成的集合
like运算符配合通配符进行相关字符性的信息查找
%表示0或多个字符 _表示任意单个字符
is null表示值是空值null的行信息


3、group by 根据group by子句指定的表达式将要处理的数据分组


4、having 根据统计结果添加条件对分组后的组进行过滤
只有符合having条件的组被保留
{where和having的区别}
where过滤的是行，having过滤的是分组
where可以跟任意列名、单行函数，having只能包含group by的表达式和组函数
where执行在前，having执行在后
where和having都不允许用列别名

5、select select语句把需要显示的列的名称或者是表达式进行设定(查询的核心步骤)
通过select语句得到需要的信息进行显示
distinct(去重)跟在select后面，用于对显示的记录进行去重操作


6、order by 用于对select语句的结果集进行排序的语句
后面跟上需要依赖进行排序的列名以及asc(升序，默认)或者desc(降序)
7、insert into… 用于把记录添加到表格当中
insert into tabname(col1,col2/*不写括号默认添加行的所有列，书写可以写进去相应列的记录，其他保持空值*/) values (val1,val2);
8、drop 用于删除表格
drop table tabname cascade constraints purge;
/*cascade constraints用在删除表格前先中断与其他表格的外键约束关系*/
9、alter table 修改表格中的列的信息
alter table tabname modify (colname null);/*not null列改为null列*/
alter table tabname modify (colname default 1 not null);
/* null列改为not null列*/
alter table tabname add(建表时列定义方法);/*添加列*/
alter table tabname drop(colname);/*删除列*/
10、synonym 同义词
create synonym account for tarena.account;
/*设置account与tarena.account一致*/


二、数据类型
1、number类型 number(int a,int b) 表示该数值有a位有效数字，b位小数位
ex: number(6);第二个参数不写默认为0，表示从-999999~999999的整数
number(4,3);4位有效数字，3位小数位，表示从-9.999~9.999的三位小数数字
number(3,-3);3位有效数字，-3位小数位，表示（+-）1~(+-)999*103的数字
2、字符类型
a、char类型 按定义的字符长度存
可以不定义长度，缺省为1字节，最大长度2000字节
b、varchar2类型 按字符串的实际长度存
必须定义长度，最大值为4000字节
/*列的取值是定长，定义为char类型；列的取值长度不固定，定义为varchar2类型*/
3、日期函数
缺省日期格式为DD-MON-RR
alter session set nls_date_format = “yyyy-mm-dd hh24:mi:ss”;
/*调整系统date函数缺省格式函数*/


三、比较和逻辑运算符 
比较运算符 : = , > , >= , < , <=
SQL比较运算符 : between and , in , like , is null
逻辑运算符 : and , or , not
否定形式 : 比较运算符 : <> , != , ^=
SQL比较运算符 : not between and , not in , not like , is not null


四、函数
1、字符函数
lower 把字符中的字母降为小写
upper 把字符中的字母升为大写
initcap 把字符中的首字符转为大写
concat 拼接函数的内容
substr 求子串的函数
length 字符串的长度
lpad 右对齐函数，位数不够的，左边补指定字符   
lpad('a',5,'b') 在a前方补4个b到5位
rpad 左对齐函数，位数不够的，右边补指定字符
trim 去掉字符串两边的空格和制表符
2、数值函数
round(num,x);对传进的num数值进行四舍五入，保留x位小数
trunc(num,x);对传进的num数值进行去尾操作，保留x位小数
3、日期函数
add months(Date date,number a) 一个日期加a各月
months between 两个日期之间相差几个月
next day 下一天
last day 前一天
4、转换函数
to date(str);把字符串转为时间函数
to char(Date date,String reg);根据reg传入的格式把date转换为字符串
to number(str);把数字的字符串转为number数据类型
5、一般函数
nvl(col1,String str);遍历col1列所有元素，有空值的会置换为str字符串


五、SQL语句中的分支
1、 case ...  then... 对where语句进行优先级筛选，从上到下，有一个满足便马上退出case
select  base_duration,base_cost,
case when  base_duration = 20 then unit_cost + 0.05
when  base_duration = 40 then unit_cost + 0.03
else 
unit_cost
end 
new_unit_cost from cost;
当base_duration找到符合的值便马上套用值退出；
如果不添加else语句，不符合case中的base_duration的值此时会输出null


2、decode 用法与case...when接近
SELECT a2.real_name cunstomer,
  DECODE(a1.real_name,a2.real_name,'No recommender', a1.real_name) recommender
FROM account a1 JOIN account a2
ON a1.id = NVL(a2.recommender_id,a2.id);
后面的参数分别是: 需要检查的列名，条件1，执行语句1，条件2，执行语句2，... 不符合所有条件时返回的执行语句
语句执行的方法是: 找到当前表格的列，检查列中的每一个值是否符合各项的条件， 一有符合 的条件，马上跳转到执行语句上并执行后退出decode; 若列中的值没有符合条件，则会执行” 不符 合所有条件时返回的执行语句”后结束decode。


六、子查询
子查询的概念是在一个查询里面包含另外一个select语句(一般是where条件语句中)
1、非关联子查询
非关联子查询，先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。
子查询只执行一遍，子查询结果若返回多值，会自动去重。
2、关联子查询
关联子查询，采用循环的方式，查询先从外部查询，获得一条记录后，并将其传入内部查询，内部查询从其结果中把值传回外部查询，若符合条件，就放入结果集，否则放弃。重复执行以上步骤。
3、in函数和exist函数
in函数
多用在非关联子查询中，后面跟值或者是表达式
exist函数 多用在关联子查询中
采用的是循环(loop)方式，判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可
in和extst的比较
exist使用循环方式，由outer表的记录决定循环的次数，所以外表的记录数要少
in先执行子查询，返回结果去重后再执行主查询，子查询结果越少越适合用in函数


/*哪些客户申请远程登录服务*/
/*关联子查询*/ SELECT real_name,id FROM account o
WHERE EXISTS
  (SELECT 1 FROM service i WHERE i.account_id = o.id );
/*非关联子查询*/ SELECT real_name FROM account
WHERE id IN (SELECT account_id FROM service );


七、多表连接和多表查询
1、交叉连接 tabname1 cross join tabname2
交叉连接得到的是表1和表2的笛卡尔积(两个表里的元素一一对应)
2、内连接 tabname1 join tabname2 on tabname.c1 = tabname2.c2 and
内连接所达到的效果是两表内相关的信息保留，不会显示没有交叉的信息
多个条件的情况下，可以加上and继续添加
执行顺序 : 先根据on后面的过滤条件进行过滤，筛选出符合条件的行，
再根据on的连接条件将两个表进行连接。
{自连接}指的是驱动表和匹配表都是同一张表，方法同内连接一样，给表起不同表别名
/*哪些os帐号的开通时间比同一台机器上os帐号的平均开通时间长*/
/*关联子查询方法*/ SELECT unix_host,os_username,
  ROUND(sysdate-create_date) days
FROM service o
WHERE ROUND(sysdate-create_date) >
(SELECT ROUND(AVG(sysdate-create_date))
  FROM service i
WHERE i.unix_host = o.unix_host);
/*多表查询方法*/ SELECT s.unix_host,s.os_username,
ROUND(sysdate-create_date) day,d.days
FROM service s
JOIN (SELECT unix_host,ROUND(AVG(sysdate-create_date)) days
  FROM service
  GROUP BY unix_host) d
ON s.unix_host = d.unix_host
AND ROUND(sysdate-s.create_date) > d.days;
3、外连接
(1)left join 驱动表是左边的表，匹配表是右边的表
(2)right join 驱动表是右边的表，匹配表是左边的表
(3)full join
执行顺序 : 先根据on和and条件对要连接的表进行过滤，将过滤后的结果集进行外连接操作(join on)，再对外连接的结果集用where子句进行过滤，最后用select语句生成最终结果集。
/*列出客户姓名以及他的推荐人*/
select t2.real_name cunstomer,nvl(t1.real_name,'No recommender') recommender
from account t1 right join account t2
on t1.id = t2.recommender_id;
4、内连接和外连接的区别
简单来说，内连接的结果集只显示两张关联表中关联值的交集记录，驱动表和匹配表交 换对结果集影响不大；外连接的结果集与驱动表和匹配表的设置有密切关系，驱动表会显示其所有的记录，没有跟匹配表匹配的也会显示出来。
{表连接，子查询 共同解决问题}
1.匹配问题 (结果集出自一张表)in exist、表连接(inner join)
匹配问题 (结果集出自多张表) 表连接(inner join)
2.不匹配问题 (结果集出自一张表) not in  not exist 表连接(outer join + where ...is null)
3.匹配+不匹配 表连接(outer join)


八、集合
数据库中的集合的概念与数学上集合的概念基本一致，同样有union/union all(并运算)， intersert(交运算)，minus(减运算)。
1、union/union all 
union all 实现的是集合(结果集)的并集，简单做并集，不去重
select name,base_duration,unit_cost+0.05 new_unit_cost from cost
where base_duration = 20
union all
select name,base_duration,unit_cost+0.03  from cost
where base_duration = 40
union all
select name,base_duration,unit_cost from cost 
where base_duration not in(20,40)
or base_duration is null
/*这里要注意，要加上空值的保留，否则会忽略了计时和包月两种套餐*/;
union 实现的是结果的并集，得到的结果集会去掉重复的记录
2、intersect 实现的是对结果集取交集的运算
/*sun280和sun-server上的远程登录业务使用了哪些相同的资费标准*/
  select name,id from cost        
  where id in 
( /*加上select from cost where id in ()语句用以获取相应套餐的名字*/       
      select cost_id from service s join host h
      on s.unix_host = h.id
      and h.name = 'sun280'/*利用on的过滤条件把相应的名字筛选出来*/
      intersect
      select cost_id from service s join host h
      on s.unix_host = h.id
      and h.name = 'sun-server');
3、minus 实现的是集合A减去A和B的交集，结果集是A中与B不一样的记录
select name,id,location from host 
  minus
  select h.name,s.unix_host,h.location from service s join host h
  on s.unix_host = h.id;


九、排名分页
rownum 是一个伪列，对查询返回的行编号即行好，由1开始一次递增
=>oracle的rownum数值是在获取每行之后才赋予的，rownum是不能作为表的一列 存在， 无法通过rownum = 2得到第二行的数据
要利用小于等于伪列某个值之后再利用where确定范围
/*最晚开通netCTOSS系统的第四到第六名客户*/
  select rn,real_name,create_date
from(
    select rownum rn,real_name,create_date
    from(
          select real_name,create_date
          from account 
        order by create_date desc)
    where rownum <=6)
  where rn between 4 and 6;
  /*经典例题，这里的做法是先通过对account里的记录按照开通时间的早晚降序排列，
    再引入rownum进行编号(如果在排序前引入，则会依照读取account记录的顺序并添上rownum，再做降序，此时的结果并不能达到预想的效果)，并通过where rownum <= a抽出前a个记录后在嵌套多一个select语句把处在靠后位置的第四到第六的记录显示*/


十、约束
1、约束的类型
a、根据约束的性质分类
主键约束(primary key,pk) 特性是非空且唯一
  唯一约束(unique key,uk) 特性是唯一
  检查约束(check key,ck) 特性是填入的记录的范围
  非空约束(not null,nn) 特性是非空
  外键约束(foreign key,fk) 特性是表与表之间的联系列
b、根据约束的范围分类
列级约束: 约束直接在列中声明，只约束一列
表级约束: 约束在列以外表中声明，可以只约束一列，也可以多列同时约束
2、建立数据库表格时约束的写法
create table testmt(
c1 number(3) constraint testmt_c1_pk primary key,/*主键约束，列级约束*/
c2 varchar2(10) constraint testmt_c2_uk unique,/*唯一性约束，列级约束*/
c3 char not null,/*非空约束，列级约束*/
c4 number(1) constraint testmt_c4_ck check(c4 in(0,1,2,3)),/*检查约束，列级约束*/
c5 number(4) constraint testmt_c5_fk references tarena.account(id),
/*外键约束，列级约束*/
constraint testmt_c2_c3_uk unique(c2,c3)/*唯一性约束，表级约束*/ );
3、外键约束
a、表格的一对多关系的实现
表和表之间的关系: 外键约束 1:n
      child table(fk) -> parent table(pk/uk)
      account 1[id pk]
      service m[id(服务)，account_id(客户)
                account_id fk-->account(id)]
create table testchild (/*列级约束 外键约束写法*/
c1 number(2) constraint testchild_c1_pk primary key,
c2 number(3) constraint testchild_c2_fk references wzqparent(c1));
  
create table testchild (/*表级约束 外键约束写法*/
c1 number(2) constraint testchild_c1_pk primary key,
c2 number(3) ,
constraint testchild_c2_fk foreign key(c2) references testparent(c1));
b、表格的多对多关系的实现
表格之间的多对多关系
m : n  通过中间表((t1_pk列名)-->t1.pk列，(t2_pk列名)-->t2.pk列)
表达两张表(t1,t2)的关系
c、表格的一对一关系的实现
表格之间的一对一关系
把一对一信息的两列信息各定义成一张表，每张表的id列定义成pk列，其中一张 表的pk列同时定义成fk列，实现一一对应关系
d、合表问题
=>合表问题的三个范式
第一范式 有pk，每一列不可再分。
  第二范式 每个非主属性必须完全依赖pk列(多对多关系的合表)
  第三范式 每个非主属性不能依赖于另一个非主属性(非主属性之间不能有依赖关系)
=>表格的一对多关系进行合表--数据冗余
一对多的两张表要合表会导致数据冗余，违反了第三范式
    占用空间，insert每一条服务信息，同时写account信息。
    同一用户信息出现多条记录，很可能数据不一致。
=>表格的多对多关系进行合表—违反第二范式
e、外键约束定义时的两种形式
=>on delete cascade--级联删除，删除父表的记录前，先删除子表里的相关记录
create table testchild1 (
  c1 number(2) constraint testchild1_c1_pk primary key,
  c2 number(3) constraint testchild1_c2_fk references testparent(c1)
                on delete cascade);
/*在级联删除的情况下，删除父表的值，子表的同一值下的行也会被自动删除*/

=>on delete set null-- 删除父表的记录前，先将子表中外键列的相关值置空
create table testchild1 (
  c1 number(2) constraint testchild1_c1_pk primary key,
  c2 number(3) constraint testchild1_c2_fk references testparent(c1)
                on delete set null 
/*update wzqchild2 set c2 = null where c1 = 1;*/
);
4、检查约束
检查约束也可以在列级和表级之间实现。
/*列级*/create table testmt(
c1 number(3) constraint testmt_c1_pk primary key,
c2 number(3) constraint testmt_c2_ck check(c2 > 100)
);
/*表级*/create table testmt(
c1 number(3) constraint testmt_c1_pk primary key,
c2 number(2),
c3 number(2),
constraint testmt_c2_c3_ck check((c2+c3) > 100)
/*check的表达式可以是in，可以是= ，也可以是算术表达式*/
);
5、约束的暂停和恢复
在为表格增加记录的时候，有些记录在表格所有记录完全写入之前受制于表格创建时所 设下的约束条件，导致无法向表格中填入记录。在这种情况下，除了在创建表格时候不 添加相应的约束，等到记录全部填入后再写，或者可以选择暂时暂停约束的条件，待记 录录入完成后恢复。具体的操作如下: 
alter table testmt disable constraint  testmt_c5_fk;
/*关键字disable可以暂时暂停命名为testmt_c5_fk的constraint(约束) */
alter table testmt enable constraint  testmt_c5_fk;
/*关键字enable可以恢复命名为testmt_c5_fk的constraint(约束) */
注意: 约束的存在不仅仅是为了保持表格于表格之间的联系(foreign key)，同时还有其他例如唯一性(unique key)和主键(primary key)等用于限定表格中的记录以符合业务逻辑的。因此，在暂停约束的时候不能够随便暂停，一旦在暂停过程中输入违反约束条件的记录就不能正常恢复相应的约束，导致出现工作的延误和不合理。所以，要根据记录的输入和现实中的业务逻辑考虑使用约束的暂停。
6、约束的查询
select c.constraint_name,c.constraint_type,cc.column_name，position
from user_constraints c join user_cons_columns cc
on c.table_name = cc.table_name
and c.table_name = “ACCOUNT”;
constraint_name:约束名
table_name:表名
column_name:被约束的列
position:单列约束值为1，联合约束时表示该列在约束定义中的位置
7、约束的操作
alter table tabname drop constraint_name;/*根据约束名删除约束*/
alter table tabname drop primary key;/*删除主键约束*/
alter table tabname add 表级约束语法;/*添加约束*/


十一、update和delete语法
1、update的语法
--用于更新表中已经存在的记录，即修改记录的某列/某些列的值
update tabname set colname1 = value1[,colname2 = value2]
where condition;
2、delete的语法
--删除已经存在的记录
delete from tabname where condition;
如果要删除表格里面的所有记录，可以简写成delete tabname;
{delete和truncate的区别}
delete from tabname 时间长(是否写rollback/undo segment)
    truncate table tabname 把表所有数据删除(删除的速度比delete from tabname块)    
    delete 不释放表占用的空间
    truncate 释放表占用的空间
    全部删除大表中的数据用truncate table


十二、事务
事物是由一组DML语句和commit/rollback组成，是改变数据库数据的最小逻辑单元。如果是commit，表示数据入库；如果是rollback，表示取消所有的DML操作。
1、事物的特性
a、原子性: 一个事务或者完全发生，或者完全不发生
b、一致性: 事务把数据库从一个一致状态转变到另一个一致状态
c、隔离性: 隔离级别(read committed只可读取已经修改的和正在修改的信息)，事务提交之前，其他事务觉察不到事务的影响
d、持久性: 一旦事务提交，它是永久的
2、数据库开发的关键挑战
=>使并行的访问量达到最大化
=>保证每一个用户(会话)可以以一致的方式读取并修改数据
锁机制:用来管理对一个共享资源的并行访问


十三、视图
1、带子查询的create table
=>根据子查询语句创建表并插入数据
=>表结构由子查询的select语句决定，create table指定列的数量要跟select语句指定的列的数量一致。
/*create table 定义列只能定义命名，缺省值，完整性约束。*/
/*20机器上的业务信息*/
create table service_a
as 
select * from tarena.service
where unix_host = '192.168.0.20';
2、带子查询的insert
=>根据子查询语句向表中插入数据
=>insert指定的裂地数量要跟select语句指定的列的数量一致。
=>一次可以插入多条记录，不能用values子句
  /*account_90表中含有所有的90后用户*/
  create table account_w90
  as
  select * from tarena.account
  where 1 = 2;/*用于复制表格的列，但不会复制表格里的数据(where 1 = 2;)*/
  insert into account_w90
  select * from tarena.account
  where to_char(tarena.account.birthdate,'yyyy')
        between '1990' and '1999';
3、view视图的概念
=>类似于快捷方式，利用select语句实现，只在系统表中存储对视图的定义
=>视图在数据库中不存储数据值，即不占空间
select test from user_views where view_name = 'test_v11';
select 'c1','c2'from test where c1 = 11;
      
      系统表 user_tables user_views user_objects(系统insert)
      用户表 account service cost(用户DDL)
      
      select object_name,object_type,status
      from user_objects
      where object_name = 'test_v11';
        [如果在where语句后面加上with check option，就可以只允许符合要求的数据
         例如该题目，加上with check option，就只有当c1 = 11情况下才能插入]
        [如果在select语句后加上with read only，表明该视图是只读属性]
      drop一张表系统自动将所有依赖该表的数据库对象
(view procedure function)的状态变为invalid(无效的)
      当一个对象是无效时，系统会执行
                   alter view test_v11 compile; 重新编译该视图
4、视图的应用场景
a、简化操作，屏蔽了复杂的sql语句，直接对视图操作
b、控制权限，只允许查询一张表中的部分数据。解决办法：对其创建视图，授予用户读视图的权限，而非读表的权限。
c、通过视图将多张表union all成一张逻辑表，作为单独一个数据库对象，实现表的超集
5、视图的维护
视图是一个依赖表的数据库对象，查询视图最终都要通过查询源表实现。如果源表的结构发生变化，对视图的操作就有可能出问题。查看视图的状态是帮助我们发现视图是否可用的方法。


十四、索引
创建方式: create index index_name on table(column);
1、扫描表的方式
a、全表扫描FTS
将扫描高水位线一下的所有数据块
/*高水位线:(high water mark)曾经插入数据的最远块*/
b、通过rowid来扫描数据
/*rowid是一个伪列，rowid包含如下信息：
data_object_id 该记录是属于哪张表的(哪个数据库对象)
    file_id        该记录在哪个数据文件里
    block_id       该记录在数据文件的第几个数据块里
    row_id         该记录在数据块里是第几条记录
*/
在oracle看来，没有重复记录，每条记录都有rowid。
delete from test o
where rowid<
          (select max(rowid) from test i
            where o.c1=i.c1
            and o.c2=i.c2) 删除表中重复数据的方法
对于所有的重复记录，留rowid最大的那条记录。
delete from test o
where rowid<
          (select min(rowid) from test i
           where o.c1=i.c1
             and o.c2=i.c2) 删除表中重复数据的方法
对于所有的重复记录，留rowid最小的那条记录。
2、索引的结构
B*tree索引由 根块（root block）、属于date_block
            分支块（branch block）、
          叶子块（leaf block）组成
*根块下面是分支块，拥于导航结构，包含了索引列范围和另一非根块(可以是分支块或叶子块)的地址
*最底层为叶子块,包含索引项（index entry），索引项有key值（被索引列的值）和该列所在行的rowid组成
*叶子块实际上是双向链表。一旦找到叶子块的“开始”点（一旦找到第一个值），对值进行顺序扫描（索引范围扫描）是很容易的。不必要做结构导航，只要通过叶子块转发就行
id列没建索引，用FTS
id列建索引，找索引，从root块通过几步找到叶子块，找index entry，获得rowid，select变成where rowid = ''
    
select real_name,rowid
    from tarena.account
    索引的顺序就是按照order by的顺序排列，不然就按照rowid的顺序排列
3、索引的类型
唯一性索引（unique）   等价于唯一性约束
非唯一性索引  用于提高查询效率
单列索引  索引建在一列上
联合索引  索引建在多列上
4、哪些列适合建索引
经常出现在WHERE子句的列
经常用于表连接的列
该列是高基数数据列（高基数数据列是指有很多不同的值）
该列包含许多null值
表很大，查询的结果集小
主键（PK）列、唯一键（UK）列
外键（FK）列
经常需要排序（ORDER BY）和分组（GROUP BY）的列,DISTINCT也适合
=>索引不是万能的,没有索引是万万不能的
5、索引用不了的写法
函数导致索引用不了－－WHERE UPPER（COLNAME）＝‘CARMEN’
  表达式导致索引用不了－－WHERE COLNAME＊12＝12000
  部分隐式数据类型导致索引用不了－－WHERE COLNAME1＝2（C1为varchar2类型）
  LIKE和SUBSTR－－WHERE COLNAME LIKE ‘CA％’ 
               －－WHERE SUBSTR（COLNAME，1，2）＝‘CA’
  查询所有的null值－－WHERE COLNAME IS NULL
  否定形式－－NOT IN 、 < >


十五、序列号
1、序列号的概念
=>为了解决主键值和唯一键值的唯一性
按照预定义的模式自动生成整数的一种机制，保证数字的自动增长
2、序列号的创建
create sequence sequence1
      start with 1/*起始值*/
      increment by 1/*步长，下一步所增加的数值*/
      maxvalue 999/*最大值，序列号的最大值*/
cycle /*是否循环，默认值为nocycle*/
cache 20/*循环值，表示需要是从1-20的循环*/;
3、实际案例的应用
create table test(c1 number constraint test_c1_pk primary key);
create sequence s_test_c1 start with 1302001;


insert into test values (s_test_c1.nextval);
insert into test values (s_test_c1.nextval); commit;
insert into test values (s_test_c1.nextval); rollback;
insert into test values (s_test_c1.nextval);
select * from test;
        C1
----------
    1302001
    1302002
  1302004