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

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

2、n）,也叫作字段（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 format 缩写为 for/表示执行上一条s

3、ql语句。设置分页显示set pagesize 100 （pagesize可简写为 pages,设置为 0表示不分页）设置提交方式：set autocommit on/offSQL分类：SQL语句大小写不敏感，数据名大小写敏感。DDL（data definition language 数据定义语言）创建数据库： create database tarena ；创建表结构 create table emp_1212（empno number（10）,ename varchar（10）,）；复制表结构 create table emp_1313 as select*from emp_1212 wh

4、ere 11;复制表：create table emp_1313 as select*from emp_1212;利用distinct去除表中的重复数据creatc table emp_1313as select distinct empno,ename,salary hiredate,job,bouns,deptno,mgrfrom emp_1212;复制部分表： create table emp_1313 as select empno ,salary*12 year_salfrom emp_1212 where deptno=10;create table emp_count(did,e

5、mp_num) as select deptno,count(*)输出数据库当前系统时间select sysdata from dual ； from emp_1212 group by deptno;修改表结构:alter table删除歹1J alter table emp_1212 drop column salary;增加一列 alter table emp_1212 add(password char(4);修改表名：rename emp_1212 to emp_1313;修改歹 U 名： alter table emp_1212 rename column password to

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

7、再 delete from parent 再 drop parent table.更改表的约束结构：alter table 表名add constraint 约束名primary key( 列名)(约束类型)同一个用户下的不同表的约束名不能重复同一个用户下的不同表的列名可以重复删除约束：alter table child drop constraint child_c2_fk(约束名);设置约束失效：alter table emp_1212 disable constraint emp_hiloo_mgr_fk;批量加载数据alter table emp_1212 enable constra

8、int emp_hiloo_mgr_fk;主键约束(primary key):实现表中的记录不是null记录而且是唯一记录.一个表中只能有一个主键约束或联合主键约束更改表的主键约束：alter table parent add constraint parent_c1_pk primary key(c1);列级约束：歹1J级主键约束：create table test(c1 number constraint test_c1_pk primary key,c2 number);非空约束(not null):只有列级约束，没有表级约束.insert语句必须插入的列是非空列create table

9、 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约束；create table child (cl number constraint child_c1_pk primary key,c2 numb

10、er(2) constraint child_c2_fk references parent(c1);更改表的外键约束：alter table emp_hiloo add constraint emp_hiloo_deptno_fkforeign key(deptno)references dept_hiloo(deptno);级联约束删除：(删除父表连同子表的约束，一起删除)drop table parent cascade constraint purge;(alter table child drop constraint child_c2_fk;)级联删除：on delete casc

11、ade/ on delete set null(删除并且设为 null 彳K)；create table child1 (c1 number constraint child1_c1_pk primary key,c2 number(2) constraint child1_c2_fkreferences parent(c1)on delete cascade);列级检查约束check:create table test(c1 number,c2 number constraint test_c2_ck check (c2 100);唯一性约束unique:可以为null值且多个null值cr

12、eate 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 number ,c2 number ,constraint test_c1_pk primary key(c1);联合主键约束：create table test(c1 number,c2 numbe

13、r,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(id number(4),name varchar2(10) not null,email varchar2(30),age number(2),constraint student_ning2_id_pk primary key

14、(id),constraint student_ning2_email_uk unique(email);约束总结：alter table 表名add constraint 约束名primary key（ 列名）（约束类型）在表建立以后追加约束referencesprimary key没有null记录，没有重复记录解决多对一关系foreign key () references parent(c1);unique列的数据必须唯一，被引用列not null 非空check列里的数据符合条件表达式DML(data manipulation language 数据操作语言)表中的数据 data,ro

15、w(行)insert增加一行数据insert into emp_1212 values(彳K1,值 2,值 3)；指定字段插入，其他列补空值nullinsert into emp_1212(name) values(Tom) ；将源表中的所有数据插入到新表中(批量插入数据)insert into emp1313 select * from emp_1212;update 修改行中的某些列的值update emp_1212 set salary =3500,job=Programmer where empno=1012;update emp_1212 set salary = salary+10

16、00;delete 删除删除表中所有数据 delete from emp_1212delete from emp_1212 where deptno=10;利用rowid删除表中的重复数据delete from emp_1212 where rowid not in (select max(rowid)from emp_1212group by empno,ename,salary);把某个列的值置空 update删除一行deleteTCL（transaction control language 事务控制语言 交易）commit提交对DMLX乍rollback 回滚对DML作savepoin

17、t 设置回滚点savepoint Arollback to A （ 回滚到A,A之后保存的回滚点会自动取消）DQL. (data query language数据查询语言)select 查询语法顺序 select from where 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;列别名跟在列名后，用空格隔开 ()列别名本身包含空格，或者希望列

18、别名的大小写敏感，用双引号括起来.表达一个标识(列名，列别名)用并歹1J条件查询：select name ,salary from emp_1212 where empno =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

19、emp_1212 where salary between 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 deptno

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

21、d)where exists , where not existswhere,having 的比较共同点：过滤，执行在select之前区别：where过滤的是行(记录)，可以跟任意一个列名，单行函数，不可以跟组函数，执行在having之前having 过滤的是组，可以跟组标识，组函数，不能跟单行函数，以及除了组标识之外的列名，执行在 where 之后的总结select用于计算from源表数据源where 过滤记录group by 分组having 过滤组order by 对select 的计算结果排序子查询：可以跟在from where 后面(当子查询的返回结果是多条记录，系统会自动去重)se

22、lect ename from emp_1212where salary =(select min(salary) from emp_1212);在一条sql语句中嵌入一条 select 语句。select fromwhere (select.)create table . as selectinsert into . select关联子查询(子查询中调用了主表中的列)select o.ename,o.salary from emp_hiloo owhere o.salary ( select round(avg(salary)from emp_hiloo iwhere i.deptno =

23、o.deptno)多表查询表连接：from 表1 jion表2 oncross join( 交叉连接)数学：组合数据库：笛卡尔积 新结果集的记录数=表t1的记录数*表t2的记录数inner join( 内连接)joinnested loop嵌套循环驱动表匹配表内连接的结果集=表中的记录要出现在结果集中，在另一张表必须能找到匹配记录.outer join( 夕卜连接)left join , right jion形式from t1 left join t2 on tl.cl = t2.c2只能t1做驱动表,t1表中的所有记录都出现在结果集中外连接的结果集=内连接的结果集+t1表中匹配不上的记录和

24、t2的null记录的组合from t1 right join t2 on tl.cl = t2.c2只能t2做驱动表,t2表中的所有记录都出现在结果集中外连接的结果集=内连接的结果集+t2表中匹配不上的记录和 t1的null记录的组合full join(全连接)from t1 full join t2 on tl.cl = t2.c2外连接的结果集=内连接的结果集+t1表中匹配不上的记录和 t2的null记录的组合+t2表中匹配不上的记录和 t1的null记录的组合在外连接的情况下，如果对匹配表的过滤发生在连接之前，必须用关键字on(and);如果想通过匹配表的列对外连接的结果集过滤，发生在连

25、接之后，必须用关键字where.在外连接的情况下，如果对驱动表过滤，一定要用关键字 where.(自连接)查询的多个结果集来自同一张表的同一列集合运算union ( 自动去重)union all( 并集，不去重)intersect ( 交集，自动去重)minus( 差，自动去重)连接两条select语句，要求select语句是同构的，列的个数，列的数据类型必须一致 union,intersect, minus,结果集是经过去重的 .union all结果集是有重复DCL (data control language数据控制语言)grant 授权grant select on emp to js

26、d1212；hiloo 授予 jsd1212 查看 hiloo 的 emp表的权限(select )revoke 收回授权revoke select on emp from jsd1212；hiloo 用户将jsd1212 select hiloo 用 户emp表的权限收回。数据库数据类型：数字number(n) 表示数字最长n位；number(缺省彳I为38位有效数字)number(n,m)例：number(7,2) n 数字长度为7 , m小数点后保留2位number(3,-1) m为正数表示小数点后面保留的位数，0或不写表示个位，m为负数表示小数点前面的位数,1就是十位，依次类推numb

27、er(2,4) 0.9999- 0.0099字符：数据库中的字符用单引号表示char(n)表示定长字符串，最长放入n个字符，char(缺省是1)如果放入的数据不够n个字符则补空格，无论如何都占n个字符长度。varchar(n)变长字符串最长放入n个字符，放入的数据是几个长度就占几个长度。varchar2(n) Oracle自己定义的变长字符串。比较的列是字符类型，列的取值用单引号表达，大小写敏感.location char(20) beijing 能 空格不敏感不能空格敏感ename varchar2（20） zhangwuji select ename,hiredate from emp_h

28、iloowhere to_char(hiredate,fmMONTH) = MARCH;fm去掉空格，去掉前导0.select ename,hiredate from emp_hiloowhere to_char(hiredate,fmmm) = 3日期date (以天为单位)(一定不能跟宽度)date类型:7个字节 世纪年 月 日 时分秒(日期的系统缺 省格式DD-MON-RR)数据库中日期相减，得到天数 select(sysdate-hiredate) days from emp_1212;Oracle中的空值概念null;1)任何数据类型都可以取空值null;2)空值和任何数据做算术运算

29、，结果都为null;3)空值和字符串类型做连接操作，结果相当于空值null不存在。运算符：比较运算符null值是不可比较的in (=any)between andlike= != A= 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

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

31、*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代替。转换的数据类型必须一致；loalower()字符小写转换函数select*from emp_1212 where lower(job

32、)=analyst;upper()字符大写转换函数select*from emp_1212 where upper(job)=ANALYST;round()四舍五入initcap()转换为首字母大写length()取长度replace()字段替换mod()取模(求余数)select salary ,mod(salary,5000) from emp_1212trunc(m,n)截取数字n为正表示截取到 m小数点后的n位，n为负表示截取到 m小数点前的n位sysdate日期函数，输出系统当前时间months_between()日期函数 select months_between(sysdate,

33、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 hh24:mi:ss) from dual;时间转换函数to_date()把字符串转换为时间类型数据insert into emp_1212(h

34、iredate) 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 desc03=3字符型=number将字符转成 number.to_number(03) = 3rtrim():去掉前后空格select ename,hiredate from emp_hiloowhere trim(to_char(hiredate,MONTH) = MARCH;coalesce n

35、ull 值转换select ename,salary,bonus,coalesce(bonus,salary*0.5,100) new_bonus from emp_hiloo；条件函数 decode() (case when then when then.else end)如果没有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 ,

36、10,*) from emp_1212;将ename字段设置为10长度，如果不够，左边用*补齐rpad()右补丁 ： select rpad(ename ,10,*) from emp_1212;将ename字段设置为10长度，如果不够，右边用*补齐组函数():count();计算行数(count()函数不计算空值)avg();求平均值sum();求和max();求最大min();求最小事务transaction：事务的特点原子操作(DMLs)-上一个事务的结束就是下一个事务的开始，开始语句-结束语句commit/rollbackrollback segment(回滚段)database se

37、ssion transactionactive transaction活动事务(在session的某一时刻，只有一个未提交的事务)OLTP (on-line transaction processing)联机事务处理系统并发量大事务小rdbms数据一致并发度高事务的隔离级别read committed读已经提交了的数据本session正在修改的数据和已经提交了的数据create table drop table DDL 是自动提交的(oracle)DMLSdml操作不阻塞select操作，写不阻塞读修改同一条记录的时候，后一个session需要等待，修改不同记录并发操作不受影响DML操作锁表级

38、共享锁行级排他锁行级排他锁表级共享锁s1 ok ok s2 wait ok s3 ok ok占用回滚段资源，锁资源其他session被阻塞住事务结束时，占用的资源释放 当有 session对该表做 dml操作，另一个 session不能 drop.DDL操作，系统DDL排他锁（表级） database object数据库对象（存储在数据库中）table :数据库中存储的数据分为两大类emp,dept用户表（程序员，测试，支持，甲方技术人员）user_objects（所有的数据库对象）user_tables（table）系统表（dba）数据库字典*user_tables :用户所有的数据表sel

39、ect count（*） from user_tables;用户账户中表的个数；select count（*） from user_tables where table_name like %EMP%;（表名默认全部大写）user_objects（所有的用户数据库对象）：（表，视图，索引等）包含 object_name,object_type,status,reated 等 selectuser_constraint:用户所有的约束条件包含 constraint_name ,constraint_typeall_tables :用户能访问的数据表包括自己的和别的用户允许自己访问的all_con

40、straint:用户能访问的约束条件all_objects:用户能访问的对象（表，视图，索引等）view视图:view中没有数据存储，view是一条select语句（类似于windows里的快捷方式）, 本身没有表结构，是基表数据的投影，create or replace view test_v1as select * from testwhere cl = 1;1）view存放在user_views表中（系统表）select view_name,text from user_viewswhere view_name = TEST_V12）源表结构做修改后，view文件要重新编译alter v

41、iew test_v1 compile;alter procedure test_p1 compile;3）view的应用场景提供安全，限制对表的数据的查询（一部分）表的子集简化操作分区表大数据量，分表策略表的超集并集create table 海淀create table 昌平create or replace view beijing asselect from haidianunion allselect .4）view约束：限制对view的dml操作，通过view只能操作10部门的员工；create or replace view emp_v10 asselect * from emp_

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

43、；create index emp_empno_idx on emp_1212（empno）;index索引结构：叶子节点（叶子数据块）存index entry索引项，不计null值（key 彳!t,rowid）（zhangwuji,AAAMoAAAEAAAAmsAAA）被索引的列的取值（针对一条记录，这条记录的rowid叶子节点连成一个双向链表，（里面的数据是排序的）根节点和分支节点（根datablock分支datablock）,用于导航，找到叶子节点基于索引的扫描：（注意的是；where bonus is null有类似的查询要求，就算建立了索引，也做全表扫描）select ename f

44、rom emp_hiloo where ename = zhangwuji先扫索引，通过根节点，分支节点定位到叶子节点在叶子节点里找出对应的index entry zhangwuji,找到了 rowid.根据rowid的值，到表中读出相应的 data block.有效地降低了读取 data block的数量,index的记录了 rowid建立索引的原则：查询次数尽可能的少，查询结果尽可能的少1）primary key/unique,自动建唯一性索引create unique index test_c1_idx on test（c1）;索引分类: 唯一性索引解决唯一性问题 create uniq

45、ue index 普通索引解决 select语句效率问题.create index单歹1J索弓 I on tabname(coll)多列索引 on tabname(col1,col2)解决 is null, 函数索弓I ： create index emp_ename_idx on emp_hiloo(upper(ename);索引失效：索引失效有以下情况，做全表扫描selectwhere salary*12 = 60000where upper(ename) = ZHANWUJIwhere c1 = 10 c1 is varchar2where否定形式用不上索引not in ()where . is null异常(exception)oracle预定义异常oracle非预定义异常自定义异常