Oracle学习笔记


单行函数的使用:(oracle 第二天) 一、 函数分为多行函数(组函数)和单行函数(字符函数,数值函数,日期函数,转换 函数) 函数:字段的操作 1 、 lower('SQL Course') 大写转小写! 2、 upper('SQL course') 小写转大写 3、Initcap(‘SQL course’) 首字母大写 4、CONCAT('Good','String')--->GoodString 字符串拼接 5、SUBSTR ‘( String’,1,3)---->Str 求子串,1 代表其实位置,它可以是负数,代表从右往左, 3 代表取的长度 6、LENGTH('String')----->6 求字符串长度 7、Lpad ‘( String’,10,'*' ) 右对齐字符串 String 长度为 10,不足的补*; 8、Rpad(‘String’,10,'*') 左对齐字符串 String 长度为 10,不足的补*; 9、ROUND --->四舍五入 Round(45.923,2)=45.93 ;第二个参数是保留的位数 10、TRUNC----> 截掉 Trunc(45.923,-1)=40 11、to_number('12') 字符一定是数组字符,超出范围汇报错 to_number('ab','xx') 将 a 和 b 分别转换成 16 进制,然后运算 隐式数据类型转换,倾向于 number 型:select first_name,salary from s_emp where salary='1550'; ‘将 1550’转换成 1550;部分隐式数据类型转换会导致索引无法起作用 13、to_char(number,'fmt')数值转字符型,第二个参数是查询结果的输出格式。 经典:select first_name,NVL(to_char(Manager_id),'boss') from s_emp;将空置为 Boss select first_name,coalesce(to_char(Manager_id),'boss') from s_emp; 二、連接: 1、cross join,交叉连接产生的结果为笛卡尔积 2、inner join,内连接 select first_name,name 3、等值连接:两张表有描述共同属性的列,常见形式为父表的主键和子表的外键相等 4 、不等值连接:可以用 between and 这样的非等值运算符将两种表中的列写成一个表达式, 所谓表之间的关系,实际指表中的行(记录)之间的关系,该关系通过将表中的列写成表达式来实现 5、自连接: 同一张表的列之间有关系实际反应的是同一张表的行(记录)之间有关系 通过给表起别名将同一张表的列之间的关系转换成不同表的列之间的关系 eg:select e.first_name,m.first_name 自连接中别名的作用是将表一两用 多列子查询:(oracle 第二天) 一、 操作在一组行(记录)上,每组返回一个结果,组函数可以出现在 select 子句和 Having 子 句中 1、group by 对数据分组 组函数处理的是所有的非空值 不同组函数处理的数据类型不一样! AVG 和 SUM 针对的是 NUMBER 型 COUNT 和 MIN、MAX 可以针对任何类型 若没有 group by 子句 select 后面有一个是组函数,其他都必须是组函数 若有 group by 子句 select 后面可跟 group by 后面跟的表达式以及组函数,其他会报错; group by 子句中跟的字段越多,分组越细,那么记录可能越多越 2、有 having 子句的 select 语句的执行过程 行被分组 将 having 子句的条件应用到每个分组上 只有符合 having 条件的组被保留,再引用 select 后面的组函数对每组的数据进行处理 3、where 和 having 的区别 where 子句过滤的是行(记录) having 子句过滤的是分组以后的聚合结果 where 子句只能跟单行函数 Having 子句只能包含 group by 后面的表达式和组函数 where 子句执行再前 Having 子句执行在后 where 子句和 having 子句都不允许用列名 二、子查询: 先执行子查询,子查询的泛或结果作为煮茶寻的条件,再执行主查询, 子查询只执行一次 若子查询的返回结果为多个值,oracle 会去掉重复值之后再将结果返回给主查询; 根据子查询的结果选择运算符的使用; in 等价于=any 若子查询的返回结果中包含空值,并且运算符为 not in 那末整个查询不会返回任何行,not in 等价于 <>all ,任何值跟 null(包括 null 本身)比结果都不为 true 否定、时用外连接! 三、多列子查询:例 select first_name salary from s_emp where (dept_id,salary)in(select dept_id,avg(salary) from s_emp group by dept_id) 四、关联子查询: 首先执行主查询 inner join 、exists 、 in outer join 、not exists 、 not in(不匹配时问题) 1、in 和 exists 的比较: exists 是用循环(loop)的方式,由 outer 表的记录数决定循环的次数,对于 exists 影响最大,所以, 外表的记录数要少 in 先执行子查询,子查询的返回结果去重之后,再执行 oracle(第四天) 一、E-R 图的详解: 基于数据库的开发,是一个紫丁香下的系统开发方法,将业务需求转换成可操作的数据库,包含五个阶段, 涉及到一定阶段,可以用 sql 语句来实现 实体:有共同属性的一类对象的集合 属性:通过属性描述实体,区分实体; 关系:描述实体和实体的关系 五个阶段:需求分析,设计,开发测试,实施、上线,产生系统 二、标识 # 表示唯一标识 * 表示强制属性(非空) o 表示可选的,及属性可位空 (# ) 唯一的字段有多个 #* 唯一标识非空 三、实体关系 语法:实体和实体的关系从实例之间的输了关系的角度可分为一对一、一对多、多对多 实体和实体之间的关系从紧密程度上分为必须和可以 四、递归关系: ‘’同一个实体里的实例之间有关系,图例表示为 猪耳朵 五、表上的约束 1、保证数据的完整性 2、保证数据的一致性 3、通过数据库的特性或应用程序完成 4、数据库约束:primary key (主键) unique key (唯一键) foreign key(外键) 5、实体完整性:主键值的任意部分不能为空并且主键值必须唯一 6、引用完整性:外键的取值必须是已经存在的主键值或为空(references) 7、列值:列的取值必须满足数据类型的定义 8、用户自定义:取值必须遵守业务规则 六、主键: 要求唯一且非空 1 、 联合主键:多列联合唯一,任意一个 2、外键: 外键(FK)是定义在子表上一列,它的取值要引用父表上的主键列(PK)或唯一键列(UK) 外键(FK)的定义是基于数据值的是纯逻辑概念;外键的取值必须匹配主键值或唯一键值,还可以是空值, 若外键是主键的一部分,它的取值不能为空 七、数据库的设计: 1、将 E-R 图转换成表 2、将属性映射成列 3、将唯一表示映射成主键 将唯一表和是定义成主键,主键意为非空约束加上唯一约束,一张表只能有一个主键,其他唯一表示定义 成非空约束 4、将关系映射成外键 八、规范化: 作用:最小化数据冗余,减少完整性问题,标识丢失的实体、关系、表 三个范式 第一范式:表中不会有重复的记录即有主属性,每个属性值不可再分 第二范式:每个非主属性必须完全依赖于主属性 第三范式:每个非主属性不能依赖于另一个非主属性 九、命名规则: 首字符必须是字母 名字为 1-30-个字符长 只能包含 A-Z,a—z,0-9,$、_ 十、字符类型 varchar2 和 char: varchar2 必须定义长度,最大长度 4000 个字节,char 可以不定义长度,缺省为 1,最大长度 2000 个 字节 varchar1 按字符串的实际长度存,char 按定义长度存, 列的取值是定长,定义成 char 类型,列的取值长度不固定,定义成 varchar2 再字符串的比较中 varchar2 按实际字符串比,对空格是敏感的,char 会将短子符串补起后,再与长字 符串比,对空格不敏感 varchar 类型是 ansi 定义的,varchar2 是 oracle 定义的。目前是等价的,但如果 ansi 对 varchar 类型定义有变化,oracle varchar2 类型不变 十一、number: number(p,s) p 是精度,数值中所有数字位的个数,最大精度 38, s 是可读范围,s 为正数,表示小数点右边的数字为的个数,为负数,表示小数点开始向左进行计算数字 位的个数,可读范围从-84 到 127 oracle 用 7 个直接来存储日期和时间信息,世纪,年,月,日,时,分,秒 缺省日期格式为 DD-MON-RR alter session set nls_date_format='yyyy mm dd hh12 :mi:ss'; 十二、日期格式: yyyy:用数字表达的四位年 mm:用数字表达的二位月 dd:用数字表达的二位日 hh24 用数字表达的 24 进制的小时 hh12 用数字表达的 12 进制的小时 mi:用数字表达的分钟 ss:用数字表达的小时 D:用数字表达的星期几 day:用全拼表达的星期几 month:用全拼表达的月 三月份录进的员工: select first_name,to_char(start_date,'YYYY dd mm hh24 :mi :ss') from s_emp where to_char(start_date,'mm')=3; rtrim()函数是将空格去掉; to_char(start_date,'fmMONTH')=‘march’ (orcale 第五天) 一、日期函数: months_between('01-sep-95','11-jan-94'),前后日期相隔多少月 add_months('11-jan-94',6) 加六个月 next_day('01-sep-95','friday') 从第一个日期开始出现的第一个星期五 last_day('01-sep-95') 本月最后一天 sysdate='25-may-95' round(sysdate,'MONTH') round(sysdate,'YEAR') trunc(sysdate,'MONTH') trunc(sysdate,'YEAR') 截掉时分秒信息 二、约束语法: 列级约束: 表级约束:联合主键 给约束起名(constraint test52_c1_c2_pk primary key(c1,c2)):系统能找到对应的表 三、主键约束: 每张表都应该创建主键而且只能有一个主键 强制列值的唯一性 主键任意部分的取值不能为空 有列级约束和表级约束两种形式 若主键列上没建索引,系统会自动创建唯一性索引 通过外键可以与同一张表的主键或唯一键建立关系,也可以与不同表的主键或唯一键建立关系 非空约束: 不可以将该列的值置为空 ,只有列级约束的形式; 四、外键约束(FK)的关键字: Foreign key 用表级约束的形式在子表上定义外键,需要用到该关键字 references 表示引用父表的哪一列 on delete cascade 级联删除 删除父表的记录前,先删除子表相关记录 on delete set null 删除父表前,先将子表中外键列的相关值置空 1、删除约束与添加约束: alter table *** add + 约束 增加约束 alter table *** drop + 约束 删除约束 2、使约束失效:用 alter table 跃居中的 disable 子句可以使用约束失效 强制使 s_emp 表上主键的约束失效,enable 生效 UK:允许为空,且允许多个空值;(空值与空值不一样) 唯一性约束 唯一的键的列 检查约束:check 3、建表脚本: 创建一个脚本文件,内容是 create table 语句 定义列名,数据类型,宽度 用列级约束的形式定义非空(not null)约束 定义主键约束、外键约束、唯一键约束、检查约束 保存文件,执行该脚本文件名绝对路径或者当前路径下已有的文件 五、带子查询的 create Table 语句:语法 create table s_emp_new52 as select id,first_name, salary*12 "ann_sal" from s_emp create table s_emp_com52 as select id, commission_pct from s_emp where commission_pct is not null ; 六、 数据操作语句 DML:(数据类型、约束、语法) insert 在表中增加记录 update 修改表中已存在的记录 delete 将表中的记录删除 1、create 操作为 DDL 语句,它自动提交 带子查询的 insert 语句 根据子查询语句向表中插入数据 insert 指定的列的数量要跟 select 语句指定的列的数量一致 一次可以插入多条记录,不能用 values 子句 2、update 语句:语法 用 update 语句更新表中已经存在的记录 子查询:例子 update s_emp_new52 s set "ann_sal"=(select "ann_sal" *(1+commission_pct/100) from s_emp_com52 e where s.id=e.id) where exists(select 1 from s_emp_com52 i where s.id=i.id); 七、缺省值的定义: 在 create table 中定义缺省值 缺省值的数据类型必须匹配列的数据列型 有效的缺省值为 八、事务的特性: 原子性:一个事务要末完全发生,要么完全不发生 一致性:事务把数据库从一个一致状态转变成另一个状态 隔离性:再事务提交之前,其他事物觉察不到事务的影响 数据库引用程序中最常用的隔离级别 read committed 一个事务只可以读取在事务开始之前提交的数据和本事物正在修改的数据 持久性:一旦事务提交,它是永久的 数据库开发的关键挑战 在开发躲用户、数据库驱动的引用程序中,关键性的挑战之一是并发执行 九、锁的基本机制: 1、排它锁: 如果一个对象上加了 X 锁,再这个锁被采用 commit 或 rollback 释放之前,该对象不能施加任何其他类型 的锁 2、共享锁: 如果一个对象被加上了 s 锁,该对象上可以加其他类型的 s 锁,但是,再该锁释放之前,该对象不能被加其他 类型的 X 锁 oracle 的锁机制: 为确保并发用户能正确使用与管理共享资源,如表中的记录,oracle 引进锁机制 DML 锁:用于保护数据完整性 TM: TX: DDL 锁: 十、事务不提交的后果: 其他事务看不见它的操作结果 表和行上加的所不释放,会阻塞其他事务的操作 它所操作的数据可以恢复到之前的状态 占用的回滚段资源不释放 补充:事务的回滚: truncate table(截取一张表) 可以删除表中的所有记录 释放该表占用的存储空间 是 DDL 命令 该命令不能回滚 索引(index)(oracle 第六天) OLTP on-line transaction processing 创建索引: create index test52_c1_ind on test52(c2) 一、表扫描的方式: 1、全表扫描 FTS 高水位线:曾经包含数据的最右边的块 将扫描高水位线以下的所有数据块 通过 rowid(伪列)来扫描数据 rowid:表示一条记录的物理位置 rownum:表示一条记录的记录号; 2、包含如下信息: 该记录属于哪张表(哪个数据库对象) object_id 该记录在哪个数据文件里 file_id 该记录在数据文件的第几个数据块里 block_id 该记录在数据库里是第几条记录 row_id 二、索引的结构: 索引使用的是 B*tree 结构 B*tree 索引由根节点、分支块、叶子节点组成 根快下面是分支块,用于导航结构,包含了索引列范围和另一索引块(可以是分支或叶子节点)的地址 最低层为叶子节点,包含索引项,索引项有 key 值(被索引列的值)和相应行所对应的 rowid 组成 索引上也直接点叶子节点实际上是双向链接的表,一旦找到叶子节点的开始点,对值进行顺序少面是很 容易的,不必在做结构导航,只要通过叶子节点转发就行 三、为什么要用索引: oracle server 通过 rowid 能够快速定位要找的行 通过 rowid 定位数据能有效降低读取数据块的数量(跟全盘扫描比较) 索引的使用和维护是自动的,一般情况不需要用户干预 四、索引的操作语句: 1、alter index:快,用空间换时间 2、drop index 和 create index:慢,占用的空间少,用时间换空间 五、哪些列适合建索引: 经常出现在 where 子句的列 经常用于表连接的列 该列是高基数数据列(高基数数据列是指有很多不同的值) 该列包含许多 null 值 表很大,查询的结果很小 主键列(PK)、唯一键列(UK):保证数据的唯一性,实现 uk 约束 外键列(FK) 将常需要排序(order by)和分组(group by)的列 索引不是万能的! 六、哪些列不适合建索引: 小表 列很少出现在 where 子句 查询的结果集大 该列被反复更新 七、索引的类型: 唯一性索引 等价于唯一约束 非唯一性索引:用于提高查询效率 单列索引: 索引键在一列上 联合索引: 索引键在多列上 create index test52_c1_c2_ind on test52(c2,c3) 八、哪些写法会导致索引用不了 函数导致索引用不了 表达式导致索引用不了 部分隐式数据类型导致索引用不了 like 和 substr 九、基于函数的索引: create index last_name_idx on s_emp(upper(last_name)) select last_name,dept_id,salary where upper(last_name)='SMITH' from 后面跟子查询: 例子:谁的工资和本部门的平均工资相等 select e.first_name,e.salary from (select dept_id,avg(salary) avgsal from s_emp group by dept_id) a join s_emp e on e.dept_id=a.dept_id and e.salary=a.avgsal 十、集合运算: union/union all intersect 两个集合做交集,结果不包含重复记录 minus A 中除去 B 中有的部分(解决不匹配问题)例: select deptno from dept minus select deptno from emp 十一、 case when 和 decode 在 select 语句中可以实现 if-then-else 的逻辑 用两种方式 1 、 select first_name ,salary, case when dept_id =31 then salary *1.1 when dept_id =32 then salary *1.2 when dept_id =33 then salary *1.3 else salary end new_salary from s_emp 2、转账: update test52 set c2= case when c1=1 then c2+800 when c1=2 then c2-800 else c2 end 3、decode: select first_name,salary, decode(dept_id,31,salary *1.1, 例题: select max(s.name),max(decode(cid,'MUST200901',grade)) 数据, max(decode(cid,'MUST200902',grade)) 操作, max(decode(cid,'MUST200903',grade)) 设计, max(decode(cid,'MUST200904',grade)) corejava from s_c52 sc join student52 s on sc.sid=s.id group by sc.sid 结果:
还剩7页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

lxqguoke

贡献于2011-12-22

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf