oracle学习笔记分析和总结.docx

《oracle学习笔记分析和总结.docx》由会员分享，可在线阅读，更多相关《oracle学习笔记分析和总结.docx（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第一章SQL更新数据2第二章序列4第三章SQL查询6第四章ORACLE数据表对象15Select manager_name name, statusFrom t_managersWhere status = ACTUnion allSelect employee_name, statusFrom t_employeesWhere status =,ACTIntersect 查询Intersect查询用于获得两个结果集的交集。例 select employee_name, statusFrom t_employeesWhere status = ACTIntersectselect manage

2、r_name, statusFrom t_managersWhere status = ACTMinus查询Minus查询可以看做集合间的减法运算，该操作的第一个集 合看做被减数，而第二个集合看作减数，那么minus操作将 返回第一个结果集中存在，而第二个结果集中不存在的记录 例 selectemployee_name,statusFrom t_employeesWhere status = ACTMinusSelect manager_name, statusFrom t_managersWhere status = ACTIV .联接联接用于多数据源（表、视图）之间如何组合，以形成最终 的

3、数据源。联接对于查询语句有着不可或缺的作用。如果未 显示指定联接，那么将获得多个数据源的笛卡尔积。Oracle中主要包括以下几种联接关系。自然联接内联接外联接：左联接外联接：右联接外联接：完全联接自然联接自然联接将两个数据源中具有相同名称的列进行联接。用户不 必明确指定执行联接的列。自然联接应该使用natural join 关键字。例 select *From t_employees natural join t_managers内联接内联接像自然联接不同，需要在from子句中使用联接条件。 但是，用户可以自行制定所要联接的各数据源的列。这克服 T自然联接要求联接必须同名的限制。例 selec

4、t *From t_employees e inner join t_managers mOne. employee_name = m. manager_name内连接可以用where子句来代替外联接一左联接外联接与内联接不同的是，内联接中的两个数据源是并列关系, 二者具有平等的地位，而外联接将其中一个数据源指定为基 表（或者说为主表）另一个数据源可以看做附表。在最终的数 据源中，一定含有基表中的数据，而附表中的数据是 否出现, 那么依具体的联接条件而定。左联接例子 select e.employeejd, e.employee_name, s.salary From t_employees

5、eLeft outer join t_salarysOn e.employeejd = s.employee_idOrder by e. employee_id（当左侧数据表中的数据在右侧表中没有记录与之对应，将利 用null来填补空缺的查询结果）对于左联接，oracle还提供了另外一种简写的方式一一在where子句条件中添加(+ )来指定附表Select e.employeejd, e.employee_name, salaryFrom t_employees e, t_salary sWhere e. employ ee_id = s.employee_id(+)Order by e.em

6、ployee_id外联接一一右联接右联接与左联接的执行过程非常相似，二者的区别在于基表 的选择。右联接应该选择使用right (outer) join关键字，而 基表即处于该关键字右侧的数据表select e.employeejd, e. employee_name, s. salaryFrom t_employees eright outer join t_salarysOn e.employeeJd = s.employee_idOrder by e.employee_id外联接一一完全联接完全联接实际是一个左联接和右联接的组合，即首先执行一 个左联接，然后执行一个右联接，最后将两个结果集

7、执行 union操作，从而获得最终的数据源。例 select e.employeejd, e.employee_name, s.salaryFrom t_employees e full join t_salary sOn e.employeeJd = s.employee_idOrder by e.employee_id缺点：完全联接开销很大V.层次化查询利用connect by进行层次化查询例 select * from marketStart with market_id = 14Connect by prior parent_market_id = market_idConnect指定

8、的递归条件，可以像where子句中的条件一样， 使用and或者or等运算符来指定多个条件例 select * from marketStart with marketjd = 14Connect by prior parent_market_id = marketjd and instr (market_name,洲)=0演示用connect by获得所有中国市场的客户信息。(具体看书)Select * from market_customerWhere marketjd in (Select marketjd from marketStart with market_name =中国，Con

9、nect by prior marketjd = parent_market_id)Sys_connect_by_path()函数的使用(具体看书316页)第四章ORACLE数据表对象与其他数据库不同，oracle数据库的下一层逻辑结构并非数 据表，而是表空间；每个数据表都属于唯一的表空间。I .oracle表空间表空间是oracle的开创性理念，他是一个逻辑对象。使用表 空间有以下好处：1 .防止磁盘空间突然耗竭的风险.规划数据更灵活（类似于操作系统的文件夹功能）2 .提高数据库性能（访问频繁的数据表放入单独的表空间）.提高数据库的安全性II.创立oracle表空间.Oracle表空间是一个

10、逻辑概念，创立是需要指定物理文件， 即实际数据分配磁盘空间。表空间的物理文件被称为数据文 件（datafile）；与此同时，应同时指定数据文件的初始大小。创立一个名为USER1的表空间，其SQL语句如下：Create tablespace userl datafilef:databaseoracleuser 1 _data.dbf size 20 M1 指定数据文件的可扩展性表空间设计理念灵活性的一个方面在于数据文件的可扩展 性。当存储在某个数据文件中的数据量超过了其初始大小 时，数据文件可以进行自动扩展。要像实现该功能，在创立表空间时应该使用autoextend选项Create tables

11、pace user2 datafilef:databaseoracleuser2_data.dbf size 20M autoextend on要关闭自动扩展，可以将autoextend选项的值设为“off”2 指定数据文件的增长幅度数据文件默认每次增长64K,当频繁增长时，可以使用next 选项设定数据文件自动增长的幅度例create tablespace user3 datafile f:databaseoracleuser3_data.dbf size 20MAutoextend on next5M数据文件的大小和自动增长的幅度单位只有KB和MB两种。3 .指定数据文件的最大尺寸数据文件

12、可以自动增长，但是无限制的增长往往带来风险。很 多情况下，某台服务器上可能同时运行着多个系统，如邮件 服务器和数据库服务器可能为同一台机器。，因此除非特殊 需要，应为每个表空间的数据文件设定最大尺寸。应使用 maxsize 选项Create tablespace user4 datafile f:datafileoracleuser4_data.dbf size 20M Autoextend on next 5m maxsize unlimited 这里unlimited作用是不限制最大尺寸。4 .查看表空间是否创立成功表空间成功创立后，会在数据库系统表中添加相应的记录，并 且创立相应的物理文

13、件。可以通过查看视图dba_data_files 中的记录和实际数据文件存在性，来判断表空间是否创立成 功Select file_name, tablespace_name from dba_data_files order by file_name,为一个表空间创立多个数据文件5 .一个表空间可以有多个数据文件，为一个表空间创立多个数据文件需要指定多个数据文件的完整路径和详细的选项 参数。各数据文件之间使用分隔。例 select tablespace multiple_data_file datafilef:databasetmpdata_ 1.dbf size IM ,f: databas

14、e tmp data_2. dbf size 5MIII.查看表空间Dba_tablespaces可以用来查看所有表空间的基本信息；而 dba_data_files可以用来查看相关数据文件的信息。查看表空间信息，可以利用如下SQL语句：Select tablespace_name, status, allocation_type from dba_tablespacesW ,修改数据库默认表空间查询每个用户的默认表空间，可以使用如下SQL语句：Selectuser_id, username, default_tablespace from dba_users 修改数据库默认的表空间的SQL语句

15、如下：Alter database default tablespace userl.修改表空间名称修改表空间名称使用rename命令Alter tablespace user2 rename to user20表空间重命名并不对数据文件产生影响，需要注意的是，不 能对数据的系统表空间进行重命名，例如SYSTEM, SYSAUX等无法进行重命名。V .删除表空间删除表空间的命令为drop tablespaceo删除表空间有两种方 式，一种是仅仅删除其在数据库中的记录，另一种是将记录 和数据文件一起删除。仅删除其在数据库中的记录的SQL语句如下：Drop tablespace user20删除表

16、空间及其数据文件的SQL语句如下：Drop tablespace user20 including contents and datafiles.创立oracle数据表利用命令创立数据表例 create table t_user (user_id number not null, user_namevarchar2(20) not null,user_email varchar2(30)利用命令查看表结构Select table_name, tablespace_name from user_tables where table_name=,t_user视图user_tables可以用来查看所

17、有用户表的基本信息。 可以在SQL Plus 或 PL/SQL Developer 的Command Window 执行如下SQL语句来查看数据表信息Describe t user;.修改oracle数据表结构利用命令修改数据表结构修改数据表结构的SQL命令为alter table可以利用RENAME选项，对列名进行修改例 alter table t_user rename column user_emailto email 列更名时，不会影响数据库中的记录；但注意不能将列名更 新为与其他已存在列名相同。可以利用modify选项对列的属性进行修改，以修改 USER_NAME的长度为例，相应的S

18、QL语句如下： Alter tablet_user modify (user_name varchar2 (15) Oracle允许一次修改多个列属性。例 alter table t_user modify (user_name varchar2 (15), email varchar2 (30)使用alter table命令同样可以为表添加一列例 alter table t_user add (remarks varchar2 (50)删除表中的操作使用drop选项第一章SQL更新数据I .插入数据一.Insert语句向表中插入数据1 .为所有列显示赋值Insert into people

19、values(people_seq.nextval,1, ACT);2 .同时指定要插入的列名列表和列值列表Insert into table_name(column 1 ,column2,.) values (value 1 ,value2二.利用子查询批量插入数据此时的SQL语句除了包含insert into命令之外，还应包含一个查询语句，其语法规那么如下：Insert into table_name select.例：insert into people select employeejd , employee_name,status from t_employees where emp

20、loyee_id=3;n.修改数据Update修改数据一般有以下几种情况：直接修改单列的值，直接修 改多列的值、利用where子句限制修改范围和利用视图修改数据。L修改单列的值update table_name set colum =value例 update people set status =CXL;例 alter table t_user drop column remarkAvertable命令除了对列进行修改外，还可以对表本身的属 性进行修改。例如，利用rename选项修改表名 Altertablet_userrenametot_users （注意，不要轻易修改表名）.删除数据表删

21、除数据表的命令为drop table例 drop table t_dropDrop table和delete命令的区别：drop table用于删除数据表， 一旦删除，该数据表在数据库中将不再存在，而delete那么用 于删除数据表的记录，无论记录有无，数据表仍然是存在的。有 时，某些数据表的列被其他表引用，如外键引用，直接使用 drop table将无法删除该表。此时，需要使用cascade constraints 选项。例 drop table t_drop cascade constraints cascade constraints表示删除表时，将一起作用于约束。4 ,备份/恢复数据表

22、数据表的备份和恢复是最常用的数据库操作，数据表的备份 主要用于以下场合修改数据表结构之前 修改数据表的数据之前 删除某个数据表之前利用命令备份/恢复数据表对于oracle数据表的备份/恢复操作，最常用的命令为exp和 impo Exp命令实现导出操作，imp实现导入操作。使用exp导出表T_USERS的步骤如下Exp system/abc 123test tables=(T_USERS)file=D:tempcommandT_USERS.dmp使用imp导入数据表的步骤如下所示：Imp system/abc 123testfile=D: temp command T_USERS.dmp tab

23、les=T_USERS注意，tabi6s参数是必需的，而且要与导出时的表名保持一 致.临时表Oracle使用create table命令创立的数据表称为永久表或普通 表。在oracle中还有另外一种特殊的数据表临时表。临时表指的是所存储的数据的临时性，临时表虽然一直存 在，但是其中的数据会在某种条件下被oracle数据库自动清 空。临时表数据清空的条件有两种，一种是事物提交或回滚；二 是会话结束。创立临时表应该使用create global temporary table命令 创立名为tmp_users_session的会话级临时表。Createglobal temporary table t

24、mp_users_session (userjd int,user_namevarchar2(20), user_email varchar2 (30) on commitpreserve rows创立事务级临时表例 create global temporary table tmp_users_transaction (user_id int, user_name varchar2 (20), user_email varchar2 (30) on commit delete rowsVII.特殊的表dual第五章约束.主键约束主键约束可以保存数据完整性，即防止数据表中的两条记录完 全相同，

25、通过将主键纳入查询条件，可以到达查询结果最多 返回一条记录的目的。I .创立主键约束Create table student (studentjd number primary key, student_name varchar2 (20), student_birthday date, student_address varchar2 (50), student_phone vhar2 (20) 查看约束Select table_name, constraint_name, constraint_type, status from user_constraints where table_n

26、ame = STUDENT 演示获得主键的作用列Select constraint name, table name, column name from user_cons_columns where constraint_name = SYS_C00512r 创立表时显示命名主键Create table student (studentjd number constraint pk_student primary key, student_name varchar2 (20), student_brithday date, student_address varchar2 (50), stu

27、dent_phone varchar2 (20) 创立主键的另一种写法Create table student (studentjd number, student_name varchar2(20), student_birthday date, student_address varchar2 (50), student_phone varchar2(20) constraint pk_student primary key (student_id)在 student_name,student_birthday,student_address 三列上创立 主键约束。Create table

28、 student (student_id number, student_name varchar2(20),student_birthday date, student_addressvarchar2(50),student_phonevarchar2 (20), constraintpk_student primary key (student_name, student_birthday, student_address).修改表的主键约束 为已存在的表创立主键Alter table student modify (student Jd number primary key) 当要添加的

29、主键作用于多个列时，可以通过添加主键命令， 而不是通过修改列的属性来添加主键。例 alter table student add constraint pk_student primary key (student_name, student_birthday, student_address)删除主键Alter table student drop primary key 利用删除约束的语法删除主键Alter table student drop constraint pk_student 列status标识了约束的可用性演示禁用主键约束Alter table student disable

30、 primary key启用主键约束的SQL语句如下：Alter table student enable primary key重命名主键Alter table student rename constraint SYS_C005145 toPK_STUDENT2 .利用update命令同时修改多列的值。Update table_name set colum 1 = value 1, colum2 = value2update people set id = (20000+id), status =ACT;.利用where 了,句限制修改范围例 update people set statu

31、s = CXL where id 20010;m.删除数据Delete命令删除表中的某些记录，truncate命令的作用是删除整个数 据表。语法规那么 delete from table_name where例 delete from people p where exists(select 1 from t_employees e wheree. employee_name = p.name)Truncate命令的语法规那么Truncate table table_name例 truncate table peopleDDL不能回滚，DML可以回滚回滚命令:rollback提交命令:comm

32、it第二章序列I .创立序歹|J使用create sequence命令例 create sequence employee_seq序列有两个重要属性cuirval和nextvaL Cuirval用于获得序列的当前值，nextval用于获得序列的下一个值。每次调 用nextval,都会使序列的当前值增加单位步长（默认步长为 l）o序列的调用方法为seq.currval和seq.nextval。但是，需 要注意的是，在序列创立之后，应该首先使用seq.nextval,然 后才能使用seq.currvalo使用方法：Select employee_seq. nextval from dualSele

33、ct employee_seq.currval from dual 向表中插入新的员工信息：Insert into t_employees values （employee_seq.nextval；陆逊 OACT）;删除序列：Drop sequence employee_seq创立序列，并指定start with选项：Create sequence employee_seq start with 12n.修改序列属性修改 minvalue 和 maxvalue 的值Alter sequence employee_seq minvalue 20;Alter sequence employee_s

34、eq maxvalue 99999;设置maxvalue值为无限Alter sequence employee_seq nomaxvalue；利用alter命令修改步长为5：Alter sequence test_seq increment by 5；III.修改 cycleCycle选项用于指定序列在获得最大值的下一个值时，从头开始获取，这 里的头即为minvahie指定的值。修改序列test_seq为可循环Alter sequence test_seq cycle；关闭可循环Alter sequence test_seqnocycle；Cache是序列缓存，其实际意义为，每次利用nextv

35、al,并非直接操作 序列，而是一次性获取多个值的列表到缓存。使用nextval获得的值， 实际是从缓存抓取。抓取的值依赖于序列的cuiral和步长increment by。缓存的值（在这里，即容量20）必须小于一次循环所能获得的 数目。Cache的存在会对其他修改其他选项产生影响。第三章SQL查询I .基本查询例 select employee_name from t_employees;当from子句中含有多个数据源时，这些数据源将实现笛卡尔积。Where子句Where子句用于过滤from子句所获得的数据源。例 selecte.employee_id, e.employ_name, s.sa

36、laryfrom t_employees e, t_salary sWhere e.employee_id =s.employee_id（当from子句中含有多个数据表或其他数据源时，为各数据表指定别 名是一个良好的习惯，即使select语句所要捕获的列不会引起列名的混 淆。）利用distinct获得唯一性记录，被distinct限制的既可以是单个列，也 可以是多个列的组合。演示利用distinct关键字获得工资表中的所有月份Select distinct month from t_salary可以获得员工的工资的唯一性记录，以表现员工的工资变化：Select distinct e.emplo

37、yee_name, s.salaryFrom t_employee e, t_salary sWhere e.employeejd =s.employee_id此语句捕获唯一的employee_name和salary的组合。Order by 子句Order by子句可以将查询结果按照一定的顺序进行排序。Select distinct e.employee_name, s.salaryFrom t_employee e, t_salarysWhere e.employee_id = s.employeeJdOrder by s.salary默认进行升序排列。升asc降desc例 order by

38、 s.salary descGroup by 子句Group by子句用于对记录集合进行分组，一旦使用了分组之后，select 语句的真实操作目标即为各个分组数据，每次循环处理的也是各个分 组，而不再是单条记录。Select e. employee_name, sum(s.salary)From t_employee e, t_salary sWhere e.employeejd = s.employee_idGroup by e.employeejd, e.employee_name(order by子句应该置于group by子句之后，并且order by子句的排 序标准不能出现group

39、 by定义之外的列，这里的列包括了聚合函数) Select e.employee_name,sum(s.salary)From t_employee e, t_salarysWhere e.employee_id = s.employee_idGroup by e.employee_id, e.employee_nameOrder by sum(s.salary);Having子句having就是筛选组的筛选器和where一样，只不过where 是筛选记录的Select e. employee_name, sum(s.salary)From t_employee e, t_salary sWh

40、ere e.employeejd = s.employee_idGroup by e.employeejd, e.employee_nameHaving sum(s.salary) 15000Order by sum(s.salary);n.子查询子查询是指嵌套在查询语句中的查询语句。子查询出现的位置 一般为条件语句，如where条件。Oracle会首先执行子查询, 然后执行父查询。Select * from t_employeesWhere employeejd in (select employeejd from t_salary)Select * fromt_employeesWhere

41、 exists (select employeejd from t_salary where employeejd = e.employeejd)m .联合语句联合语句是指两个或多个select语句是并列关系，并且对这 些select语句所捕获的记录集进行集合操作，以获得最终的 结果集。这些联合语句包括以下几利I union查询、union all 查询、intersect查询和 minus查询。Union查询是指两个查询结果集进行并集操作，并将重复记 录剔除，即相当于并集操作之后，再进行一次distinct操作 Select 0 value,请选择display_valueFrom dualUnionSelect employeejd value, employee_name display_valueFrom t_employeesWhere status = ACTUnion操作的各结果集，只要保证列数相同，并且各列数相同, 并且各列的数据类型一致即可，并不要求其具有统一的列名。 并集操作的最终结果的列名将统一使用第一个查询语句所获 得的列名。Union all查询与union同为并集操作，但union all查询并 不删除最终结果集中的重复记录，因此imionall的操作要 快于uniono