Oracle数据库知识点总结,复习必备资料-3

1、 66RDBMS（relationship database management system关系型（二维表）数据库管理系统） 是数据库软件中用来操纵和管理数据库的部分，用于建立，使用和维护数据库。 它对数据进行统一的管理和控制，以保证数据的安全性和完整性。 SQL (Structurde Query Language)数据结构查询语言 是用来在关系数据库上执行数据操作，检索及维护所使用的语言，是综合的，通用的关系数据库语言。 大多数数据库都使用相同或者相似的语言来操作和维护数据库。 Table (表)是数据库存储的基本单元，由行和列组成 横向为行（Row）,也叫记录（Record） 纵向为

2、列（column）,也叫作字段（Filed） rownum 伪列,从第一记录选 rowid 一条记录的物理位置. sqlplus 登录数据库的客户端软件 语句： 查看当前用户账户: show user 查询表结构 ： desc 表名 清除屏幕内容：clear scr 设置显示长度：set linesise 150 语句后面后面不用加分号 设置列的宽度： column name format a6 此语句中a6表示6个字符，此语句不能设置数字类型列宽 column empno format 9999 设置显示格式为9999 简写为 col empno for 999 column缩写为col f

3、ormat缩写为for "/"表示执行上一条sql语句。 设置分页显示 set pagesize 100 (pagesize可简写为pages,设置为0表示不分页) 设置提交方式： set autocommit on/off SQL分类：SQL语句大小写不敏感，数据名大小写敏感。 DDL(data definition language 数据定义语言) 创建数据库：create database tarena； 创建表结构 create table emp_1212(empno number(10),ename varchar(10),.); 复制表结构 create ta

4、ble emp_1313 as select*from emp_1212 where 1<>1; 复制表： create table emp_1313 as select*from emp_1212; 利用distinct去除表中的重复数据 creatc table emp_1313 as select distinct empno,ename,salary hiredate,job,bouns,deptno,mgr from emp_1212; 复制部分表：create table emp_1313 as select empno ,salary*12 year_sal from

5、 emp_1212 where deptno=10; create table emp_count(did,emp_num) as select deptno,count(*) 输出数据库当前系统时间 select sysdata from dual； from emp_1212 group by deptno; 修改表结构:alter table 删除一列 alter table emp_1212 drop column salary; 增加一列 alter table emp_1212 add(password char(4); 修改表名：rename emp_1212 to emp_13

6、13; 修改列名：alter table emp_1212 rename column password to pwd; 修改列的数据类型：alter table emp_1212 modify (ename varchar2(10); 删除表 ：drop table emp_1212; 清空表结构：truncate 约束：constraint 1 先create parent table(被引用列满足pk=uk + not null/uk(unique key), 再create child table(定义fk->parent(c1) 2 先insert into parent,再i

7、nsert into child 3 先delete from child,再delete from parent 4 先drop child table,再drop parent table. 更改表的约束结构：alter table 表名 add constraint 约束名 primary key(列名) (约束类型) 同一个用户下的不同表的约束名不能重复 同一个用户下的不同表的列名可以重复 删除约束：alter table child drop constraint child_c2_fk(约束名); 设置约束失效：alter table emp_1212 disable constr

8、aint emp_hiloo_mgr_fk; 批量加载数据 alter table emp_1212 enable constraint emp_hiloo_mgr_fk; 主键约束(primary key)：实现表中的记录不是null记录而且是唯一记录.一个表中只能有一个主键约束或联合主键约束 更改表的主键约束： alter table parent add constraint parent_c1_pk primary key(c1); 列级约束： 列级主键约束：create table test(c1 number constraint test_c1_pk primary key, c

9、2 number); 非空约束(not null):只有列级约束,没有表级约束.insert语句必须插入的列是非空列 create table test(c1 number not null); 非空约束命名：create table emp_1212(id number(4) primary key, name varchar2(10) constraint emp_1212_name_nn not null); 外键约束：用来实现两张表记录的多对一关系.(定义为外键约束的列数据必须被主键列包含，但可以为null,可以手动插入null值) 定义外键约束时，父表必须先存在并且定义了PK或者uk

10、约束； create table child ( c1 number constraint child_c1_pk primary key, c2 number(2) constraint child_c2_fk references parent(c1); 更改表的外键约束： alter table emp_hiloo add constraint emp_hiloo_deptno_fk foreign key(deptno) references dept_hiloo(deptno); 级联约束删除：(删除父表连同子表的约束，一起删除) drop table parent cascade

11、constraint purge;(alter table child drop constraint child_c2_fk;) 级联删除：on delete cascade/ on delete set null(删除并且设为null值)； create table child1 ( c1 number constraint child1_c1_pk primary key, c2 number(2) constraint child1_c2_fk references parent(c1) on delete cascade); 列级检查约束check: create table tes

12、t(c1 number, c2 number constraint test_c2_ck check (c2 > 100); 唯一性约束unique: 可以为null值且多个null值 create table test(c1 number(2) constraint test_c1_pk primary key, c2 number(3) constraint test_c2_uk unique not null,(相当于pk约束) c3 number(4) constraint test_c3_uk unique) 表级约束： 表级主键约束：create table test(c1

13、number , c2 number , constraint test_c1_pk primary key(c1); 联合主键约束：create table test(c1 number, c2 number, c3 number, constraint test_c1_c2_pk primary key(c1,c2); 表级检查约束check： create table test(c1 number, c2 number ,constraint test_c2_ck check (c2 > 100); 表级唯一约束unique: create table student_ning2(

14、 id number(4), name varchar2(10) not null, email varchar2(30), age number(2), constraint student_ning2_id_pk primary key(id), constraint student_ning2_email_uk unique(email) ); 约束总结： alter table 表名 add constraint 约束名 primary key(列名) (约束类型) 在表建立以后追加约束 references primary key 没有null记录,没有重复记录 foreign ke

15、y () references parent(c1);解决多对一关系 unique 列的数据必须唯一,被引用列 not null 非空 check 列里的数据符合条件表达式 DML(data manipulation language 数据 操作语言) 表中的数据data,row(行) insert 增加一行数据 insert into emp_1212 values(值1，值2，值3)； 指定字段插入，其他列补空值null insert into emp_1212(name) values('Tom')； 将源表中的所有数据插入到新表中 (批量插入数据) insert int

16、o emp1313 select * from emp_1212; update 修改行中的某些列的值 update emp_1212 set salary =3500,job='Programmer' where empno=1012; update emp_1212 set salary = salary+1000; delete 删除 删除表中所有数据 delete from emp_1212 delete from emp_1212 where deptno=10; 利用rowid删除表中的重复数据 delete from emp_1212 where rowid no

17、t in (select max(rowid)from emp_1212 group by empno,ename,salary); 把某个列的值置空 update 删除一行 delete TCL(transaction control language 事务控制语言 交易) commit 提交 对DML操作 rollback 回滚 对DML操作 savepoint 设置回滚点 savepoint A rollback to A (回滚到A,A之后保存的回滚点会自动取消) DQL（data query language 数据查询语言） select查询 语法顺序 select from whe

18、re group by having order by 执行顺序 from where group by having select order by 表全查询：select*from emp_1212; select name,round(avg(salary) "avg_sal" from emp_1212;列别名跟在列名后，用空格隔开() 列别名本身包含空格,或者希望列别名的大小写敏感,用双引号括起来. " 表达一个标识(列名,列别名)用" 并列条件查询：select name ,salary from emp_1212 where empno =

19、1001 and deptno =10; 或者条件查询：select*from emp_1212 where job='Manager' or job ='Analyst' select*from emp_1212 where job in('Manager','Analyst'); select*from emp_1212 where job not in('Manager','Analyst'); 区间查询：select salary from emp_1212 where salary bet

20、ween 1000 and 5000; select salary from emp_1212 where salary not between 1000 and 5000; 模糊匹配查询：select ename from emp_1212 where ename like '_Tom%'( '_'表示一个，'%'表示0个或多个) select后出现的列，凡是没有被组函数包围的列，必须出现在group by短语中 分组查询(group by)：select deptno ,avg(salary) from emp_1212 group by d

21、eptno; 查询结果排序 (order by desc/asc) ：select salary from emp_1212 order by desc;(缺省值为asc) null被看做最大来处理 having子句(对分组的结果集过滤)：select deptno,avg(nvl(salary,0) avg_sal from emp_1212 where deptno is not null group by deptno having avg(nvl(salary,0)>5000; 判断子句 ： in , not in (对于not in 来说，集合中一定不能包含null,否则，no

22、 rows selected) where exists , where not exists where,having的比较 共同点: 过滤,执行在select之前 区别 : where 过滤的是行(记录),可以跟任意一个列名,单行函数,不可以跟组函数,执行在having之前 having 过滤的是组,可以跟组标识,组函数,不能跟单行函数,以及除了组标识之外的列名，执行在where之后的 总结 select 用于计算 from 源表 数据源 where 过滤记录 group by 分组 having 过滤组 order by 对select的计算结果排序 子查询：可以跟在 from wher

23、e 后面 (当子查询的返回结果是多条记录，系统会自动去重) select ename from emp_1212 where salary =( select min(salary) from emp_1212 ); 在一条sql语句中嵌入一条select语句。 select from where（select.) create table . as select insert into . select 关联子查询（子查询中调用了主表中的列） select o.ename,o.salary from emp_hiloo o where o.salary > ( select round

24、(avg(salary) from emp_hiloo i where i.deptno = o.deptno) 多表查询 表连接：from 表1 jion 表2 on. cross join(交叉连接) 数学:组合 数据库:笛卡尔积 新结果集的记录数=表t1的记录数*表t2的记录数 inner join(内连接) join nested loop 嵌套循环 驱动表 匹配表 内连接的结果集=表中的记录要出现在结果集中,在另一张表必须能找到匹配记录. outer join(外连接) left join , right jion 形式 from t1 left join t2 on t1.c1 =

25、 t2.c2 只能t1做驱动表,t1表中的所有记录都出现在结果集中 外连接的结果集=内连接的结果集+t1表中匹配不上的记录和t2的null记录的组合 from t1 right join t2 on t1.c1 = t2.c2 只能t2做驱动表,t2表中的所有记录都出现在结果集中 外连接的结果集=内连接的结果集+t2表中匹配不上的记录和t1的null记录的组合 full join(全连接) from t1 full join t2 on t1.c1 = t2.c2 外连接的结果集=内连接的结果集+t1表中匹配不上的记录和t2的null记录的组合 +t2表中匹配不上的记录和t1的null记录的组

26、合 在外连接的情况下,如果对匹配表的过滤发生在连接之前,必须用关键字on(and); 如果想通过匹配表的列对外连接的结果集过滤,发生在连接之后,必须用关键字where. 在外连接的情况下,如果对驱动表过滤,一定要用关键字where. (自连接) 查询的多个结果集来自同一张表的同一列 集合运算 union (自动去重) union all(并集，不去重) intersect (交集，自动去重) minus(差，自动去重) 连接两条select语句,要求select语句是同构的,列的个数,列的数据类型必须一致 union,intersect, minus,结果集是经过去重的. union all

27、结果集是有重复 DCL（data control language 数据控制语言） grant 授权 grant select on emp to jsd1212； hiloo授予jsd1212查看hiloo的emp表的权限（select） revoke 收回授权 revoke select on emp from jsd1212； hiloo用户将jsd1212 select hiloo用 户emp表的权限收回。 数据库数据类型: 数字 number(n) 表示数字最长n位；number(缺省值为38位有效数字) number(n,m) 例：number(7,2) n数字长度为7 ，m小数点

28、后保留2位 number(3,-1) m为正数表示小数点后面保留的位数,0或不写表示个位, m为负数表示小数点前面的位数,1就是十位,依次类推 number(2,4) 0.9999-> 0.0099 字符: 数据库中的字符用单引号表示 char(n) 表示定长字符串，最长放入n个字符，char(缺省是1) 如果放入的数据不够n个字符则补空格，无论如何都占n个字符长度。 varchar(n) 变长字符串 最长放入n个字符，放入的数据是几个长度就占几个长度。 varchar2(n) Oracle自己定义的变长字符串。 比较的列是字符类型,列的取值用单引号表达,大小写敏感. location

29、char(20) 'beijing ' 能 空格不敏感 ename varchar2(20) 'zhangwuji ' 不能 空格敏感 select ename,hiredate from emp_hiloo where to_char(hiredate,'fmMONTH') = 'MARCH' fm去掉空格,去掉前导0. select ename,hiredate from emp_hiloo where to_char(hiredate,'fmmm') = '3' 日期 date（以天为单位）(

30、一定不能跟宽度)date类型:7个字节 世纪 年 月 日 时 分 秒 （日期的系统缺省格式 'DD-MON-RR'） 数据库中日期相减，得到天数 select(sysdate-hiredate) days from emp_1212; Oracle中的空值概念null; 1)任何数据类型都可以取空值null; 2)空值和任何数据做算术运算，结果都为null; 3)空值和字符串类型做连接操作，结果相当于空值null不存在。 运算符： 比较运算符 null值是不可比较的 in (=any) between and like = <> != = > < ( &

31、gt;any ,<any )( >all ,< all ) 语句总结： 查询数据库SID:select instance_name from V$instance; 串接列数据： select empno,ename|job d from emp_1212;(d为列别名) select empno,ename|' work as '|job d from emp_1212;(串接的2个字段间有常量字符) select ename|'''s job is '|job|'.' employee from emp_hi

32、loo; oracle中如何表达单引号本身,'''',第一个和第四个单引号表示定界符,第二个和第三个单引号联合起来表示单引号本身. 去重(distinct)：select distinct deptno,job from emp_1212;(只能跟在select后，作用域为from之前的所有列) 转义字符处理：select ename from emp_1212 where ename like 'S_%' escape''(查询名字以S_开头的名字) 判断空值is null(is not null) 空值不能跟在=后参加运算 s

33、elect*from emp_1212 where bonus is null; dual 虚表(单行单列的表,数据库自带的，存放常量数据X) select sysdate from dual; 更改会话时间格式：alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss'； 查询当前用户账户：select user from dual; 函数总结： 单行函数： nvl(bonus,0) 空值转换函数，bouns列中的null用0代替。转换的数据类型必须一致； loa lower() 字符小写转换函数 select*fr

34、om emp_1212 where lower(job)='analyst' upper() 字符大写转换函数 select*from emp_1212 where upper(job)='ANALYST' round()四舍五入 initcap() 转换为首字母大写 length() 取长度 replace() 字段替换 mod() 取模 (求余数) select salary ,mod(salary,5000) from emp_1212 trunc(m,n)截取数字 n为正表示截取到m小数点后的n位， n为负表示截取到m小数点前的n位 sysdate 日期

35、函数，输出系统当前时间 months_between() 日期函数 select months_between(sysdate,hiredate) months from emp_1212; add_months 日期函数 select add_months(sysdate,-12) from dual; last_day()计算本月最后一天 select last_day(sysdate) from dual; 字符转换函数to_char(),转换出的字符类型都为varchar2类型(Oracle数据库独有的函数) select to_char(sysdate,'yyyy-mm-dd

36、 hh24:mi:ss') from dual; 时间转换函数to_date() 把字符串转换为时间类型数据 insert into emp_1212(hiredate) values(to_date('2013-03-03','yyyy-mm-dd') 数字转换函数：to_number() select ename,hiredate from emp_1212 where to_char(hiredate,'mm') = 3 order by hiredate desc '03'=3 字符型=number 将字符转成nu

37、mber. to_number('03') = 3 rtrim():去掉前后空格 select ename,hiredate from emp_hiloo where trim(to_char(hiredate,'MONTH') = 'MARCH' coalesce null值转换 select ename,salary,bonus,coalesce(bonus,salary*0.5,100) new_bonus from emp_hiloo； 条件函数 decode() (case when then when then.else end)如果

38、没有else,当条件不匹配时,表达式的返回值为null. select ename,deptno,salary, decode(deptno, 10,salary*1.1, 20,salary*1.2, salary) new_sal from emp_hiloo; lpad()左补丁 ：select lpad(ename ,10,'*') from emp_1212; 将ename字段设置为10长度，如果不够，左边用'*'补齐 rpad()右补丁 ：select rpad(ename ,10,'*') from emp_1212; 将ename

39、字段设置为10长度，如果不够，右边用'*'补齐 组函数()： count();计算行数(count()函数不计算空值) avg();求平均值 sum();求和 max();求最大 min();求最小 事务 transaction：事务的特点 原子操作(DMLs) -上一个事务的结束就是下一个事务的开始,开始语句-结束语句 commit/rollback rollback segment(回滚段) database session transaction active transaction 活动事务(在session的某一时刻，只有一个未提交的事务） OLTP (on-line

40、 transaction processing) 联机事务处理系统 并发量大 事务小 rdbms 数据一致 并发度高 事务 的隔离级别 read committed 读已经提交了的数据 本session正在修改的数据和已经提交了的数据 create table drop table DDL是自动提交的(oracle) DMLS dml操作不阻塞select操作,写不阻塞读 修改同一条记录的时候,后一个session需要等待,修改不同记录并发操作不受影响. DML操作 锁 表级共享锁 行级排他锁 行级排他锁 表级共享锁 s1 ok ok s2 wait ok s3 ok ok 占用回滚段资源,锁

41、资源,其他session被阻塞住 事务结束时,占用的资源释放. 当有session对该表做dml操作,另一个session不能drop. DDL操作,系统DDL排他锁(表级) database object 数据库对象 (存储在数据库中) table :数据库中存储的数据分为两大类 emp,dept 用户表(程序员,测试,支持,甲方技术人员) user_objects(所有的数据库对象) user_tables(table) 系统表(dba) 数据库字典* user_tables : 用户所有的数据表 select count(*) from user_tables;用户账户中表的个数； se

42、lect count(*) from user_tables where table_name like '%EMP%'(表名默认全部大写) user_objects(所有的用户数据库对象)：(表，视图，索引等） 包含object_name,object_type,status,reated 等 select user_constraint: 用户所有的约束条件 包含constraint_name ,constraint_type all_tables :用户能访问的数据表 包括自己的和别的用户允许自己访问的 all_constraint:用户能访问的约束条件 all_obje

43、cts:用户能访问的对象(表，视图，索引等) view 视图:view中没有数据存储,view是一条select语句(类似于windows里的快捷方式), 本身没有表结构,是基表数据的投影， create or replace view test_v1 as select * from test where c1 = 1; 1)view存放在user_views表中(系统表) select view_name,text from user_views where view_name = 'TEST_V1' 2)源表结构做修改后，view文件要重新编译 alter view te

44、st_v1 compile; alter procedure test_p1 compile; 3)view的应用场景 提供安全,限制对表的数据的查询(一部分) 表的子集 简化操作 分区表 大数据量,分表策略 表的超集 并集 create table 海淀 create table 昌平 . create or replace view beijing as select from haidian union all select . 4)view约束：限制对view的dml操作,通过view只能操作10部门的员工； create or replace view emp_v10 as sele

45、ct * from emp_hiloo where deptno = 10 with check option; with read only; index 索引： 表的查询 1)heap table 堆表(数据都存放在堆表中，哪有空位往哪里插) 2)data block(数据块) 16k,最小的I/O单位(物理/逻辑),data block(数据块) 16k 3)HWM (high water mark 高水位线):曾经插入数据的最远的data block的位置 4)把HWM之下的所有data block读一遍 查询方式 FTS （full table scan） 全表扫描 创建索引：格式

46、create index 索引名 on 表名(列名)； create index emp_empno_idx on emp_1212(empno); index索引结构: 叶子节点(叶子数据块)存index entry索引项,不计null值 (key值,rowid)(zhangwuji,'AAAMoAAAEAAAAmsAAA') 被索引的列的取值(针对一条记录),这条记录的rowid 叶子节点连成一个双向链表,(里面的数据是排序的) 根节点和分支节点(根datablock 分支datablock),用于导航,找到叶子节点 基于索引的扫描:(注意的是;where bonus is

47、 null 有类似的查询要求，就算建立了索引，也做全表扫描) select ename from emp_hiloo where ename = 'zhangwuji' 先扫索引,通过根节点,分支节点定位到叶子节点 在叶子节点里找出对应的index entry zhangwuji,找到了rowid.根据rowid的值,到表中读出相应的data block. 有效地降低了读取data block的数量,index的记录了rowid 建立索引的原则：查询次数尽可能的少，查询结果尽可能的少 1)primary key/unique,自动建唯一性索引 create unique ind

48、ex test_c1_idx on test(c1); 索引分类： 唯一性索引解决唯一性问题 create unique index 普通索引解决select语句效率问题. create index 单列索引 on tabname(col1) 多列索引 on tabname(col1,col2) 解决is null, 函数索引：create index emp_ename_idx on emp_hiloo(upper(ename); 索引失效： 索引失效有以下情况，做全表扫描 select where salary*12 = 60000 where upper(ename) = 'ZHANWUJI' where c1 = 10 c1 is varchar2 where 否定形式用不上索引 not in () where . is null 异常（exception） oracle预定义异常 oracle非预定义异常 自定义异常 格式