oracle数据库核心笔记

xinjiang

　　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、wherewhere语句实现的是对查询表的结果集的筛选
　　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、selectselect语句把需要显示的列的名称或者是表达式进行设定(查询的核心步骤)
　　通过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 修改表格中的列的信息

　　>
　　>　　/* 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

　　>　　/*调整系统date函数缺省格式函数*/
　　三、比较和逻辑运算符
　　比较运算符 : = , > , >= , < , 　　七、多表连接和多表查询
　　1、交叉连接tabname1 cross join tabname2
　　交叉连接得到的是表1和表2的笛卡尔积(两个表里的元素一一对应)
　　2、内连接tabname1 join tabname2on tabname.c1 = tabname2.c2and
　　内连接所达到的效果是两表内相关的信息保留，不会显示没有交叉的信息
　　多个条件的情况下，可以加上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>
　　( /*加上select from cost where>　　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 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、约束的暂停和恢复
　　在为表格增加记录的时候，有些记录在表格所有记录完全写入之前受制于表格创建时所设下的约束条件，导致无法向表格中填入记录。在这种情况下，除了在创建表格时候不添加相应的约束，等到记录全部填入后再写，或者可以选择暂时暂停约束的条件，待记录录入完成后恢复。具体的操作如下:

　　>　　/*关键字disable可以暂时暂停命名为testmt_c5_fk的constraint(约束) */

　　>　　/*关键字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、约束的操作

　　>
　　>
　　>　　十一、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(无效的)
　　当一个对象是无效时，系统会执行

　　>　　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列建索引，找索引，从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