oracle数据库（精编版）

1、oracleadmin 和 mysql 数据库的password 相同sele 超级管理员 :sys/change on install普通管理员 :system/manager普通用户 :scott/tiger大数据用户 :sh/sh格式化语法：set linesize 300; 设置 oreacl 数据库的显示行大小set pagesize 30; 设置每页显示的条数col字段名for a15以上通常使用设置显示desc查看表结构scott表中的结构：1、 部门表： deptno名称类型描述1 deptnonumber(2)表示部门编号，由两位数字组成2 dnamvarchar2(14)部

2、门名称，最多由14 位字符组成3 locvarchar2(13)部门所在的位置2、雇员表： empno名称类型描述1 empnonumber(4)雇员的编号，由4 位数字组成2 enamevarchar2(10)雇员的，由10 位字符组成3 jobvarchar2(9)雇员的职位4 mgrnumber(4)雇员的领导编号5 hiredatedate雇员的雇佣日期6 salnumber(7,2)基本工资，其中有2 位小数， 5 位整数一共7 位7 commnumber(7,2)奖金佣金8 deptnonumber(2)雇员所在的部门编号3、工资等级表：salgradeno名称类型描述1 grad

3、enumber工资等级2 losalnumber此工资的最低等级3 hisalnumber此工资的最高等级4、工资表： bonusno名称类型描述1 enamevarchar2(10)雇员2 jobvarchar2(9)雇员职位3 salnumber雇员的工资4 commnumber雇员的奖金简单查询：sql 是数据库语言。sql 通常用于数据库之间的通讯，ansi 美国标准学会声称，sql是关系型数据库系统的标准语言。· dml data manipulation language ，数据库操作语言那用于检索或修改数据· ddl data definition langu

4、age ，数据定义语言用于定义数据的结构，如创建、修改或删除数据库对象。· dcl data control language ，数据控制语言用于定于时候据哭用户的权限select distinct * |字段别名, 字段 别名 from表名称 别名;select * from emp where ename=&str ;如果查询出来的数据有重复的数据，可以使用使用此种方式查询必须注意大小写distinct关键字消除重复如： select distinct job from emp在进行简单查询时也可以使用四则运算符select ename,job, sal*12 as sa

5、l from emp; |select ename,job, sal*12 sal from emp;“|”用于连接字符串；order by字段 asc | desc ;简单查询语法：限定查询：select distinct * |字段别名, 字段 别名 from表名称 别名 where条件 s ;查询排序：select distinct * |from表名称 别名 where条件 s 字段别名, 字段 别名 如果排序时有多个字段的时候，用“，”分隔开单行函数：单行函数主要分为五类：字符函数、数字函数、日期函数、转换函数、通用函数在 oracle 数据库中就算是使用函数进行简单的查询也必须写出

6、完整的sql 语句，但 oracle数据库为了查询方便专门提供了一个方便查询的虚拟表“dual ”表。字符函数：字符函数的功能主要是进行字符串数据的操作· upper字符串：将字符串变成大写返回· lower 字符串：将字符串变成小写返回· initcap 字符串| 列：开头首字母大写· length 字符串| 列：求出字符串的长度· replace 字符串| 列：进行替换需要三个参数：1、列名 2、替换前的字符3、替换后的字符。· substr 字符串| 列，开始点 结束点 ：字符串截取使用 substr 截取字符串时有两种语法：1

7、、 substr 字符串| 列，开始点 ：表示从开始一直截取到末尾2、 substr 字符串| 列，开始点，结束点 ：表示截取制定区域的字符串数字函数 :· round 数字| 列保留小数的位数 ：四舍五入的操作· trunc 数字| 列保留小数的位数：舍弃指定位置的内容· mod 数字 1，数字 2：取模，去余数日期函数：select sysdate from dual ;取得当前日期sysdate + n; 表示 n 天后的日期sysdate n;表示 n 天前的日期sysdate 日期 ;表示当前日期和指定日期之间的天数· last_date 日期

8、：求出指定日期的最后一天· next_date 日期，星期数 ：求出下一个指定星期x 的日期· add_months星期，数字 ：求出假设干月之后的日期· months_between日期 1，日期 2：求出两个日期之间所经历的月份转换函数：· to_char 字符串| 列，格式字符串 ：将日期或者是数字转换成字符串显示如果将日期转换成字符串显示的话，月份前面会添加一个“0”，如果想去掉这个“0”需要在格式化字符串里面并且是最前面加上fm 即可。如果需要显示出时间则需要在格式化字符串中添加时、分、秒缩写即可： 时： hh | 24hh；分： mi；秒：

9、ss；如果需要将数字格式化就在格式化字符串中使用一个“9”代表一个数字，如果需要表示不同的数字类型，如货币：就在格式化字符串前面添加缩写；货币：“ l”表示“ local ”含义，即当前电脑语言环境下的货币符号；· to_da te字符串，格式字符串：将字符串变为date 数据显示· to_number 字符串：将字符串变成数字显示通用函数 :通用函数主要有两个：nvl() 、decode() ，这两个函数是oracle 数据库独有的函数。1、 nvl() 函数，处理null ；设置函数需要两个参数第一个是列，第二个是如果为null 需要替换的数字。2、 decode()

10、函数：多条件，多数值判断。语法：decode( 数值| 列，判断值select empno,ename,job,1，显示值 1，判断值1，)decode(job,'clerk','办事员 ','salesman',' 销售员 ')from emp;多表查询：多表查询语法：select distinct * |字段别名, 字段 别名 .from表名称 别名， 表名称 别名,where条件 s order by字段 asc | desc ;·注意：一般在查询不知道表中的数据时，可以使用count() 函数查询出数据库表中的数

11、据条数，这样就可以防止由于数据库表数据量太大带来的损失。在 oracle 数据库中查询的时候需要使用左右连接查询就使用“+”符号： 1、“+ =”：如果将“+”放在等号左边就表示左连接查询；2、“ =+”：如果将“+”放在等号右边就表示右连接查询；统计函数及分组查询:·统计函数：· count() ：查询表中的数据记录；· avg() ：求出平均值；· sum() ：求和；· max() ：求出最大值；· min() ：求出最小值；·分组统计：select distinct * |分组字段1 别名 ，分组字段2 别名 ，.

12、| 统计函数from表名称 别名， 表名称 别名，. where条件 (s)group by分组字段1 ，分组字段2，.order by排序字段asc | desc ，排序字段asc | desc在进行分组查询时不管是单字段还是多字段，一定要有一个前提查询的数据存在重复数据在 where 子句中不能使用统计函数如果需要对分组后的数据再次进行过滤，则使用having 子句完成，并且having子句必须出现在 group by子句之后，因为是对分组后的数据再次过滤。· where和 having的区别：1、 where ：是在执行group by 操作之前进行的数据过滤，表示从全部时候据

13、之中筛选出部分的数据，在where 之中不能使用统计函数。2、 having ：是在 group by分组之后的再次过滤，可以在ha ving 子句中使用统计函数。子查询：子查询= 简单查询+ 限定查询+ 多表查询+ 统计查询； 子查询出现的规律：· where ：子查询一般只返回单行单列、多行单列、单行多列的数据；· from ：子查询发那会的一般是多行多列的数据，当做一张临时表出现；如果子查询返回的是多行单列数据的话，这个时候就需要使用三种判断：in 、any 、all ；1、 in 操作符：用于指定一个子查询的判断范围，与模糊查询中的in 是一样的，唯一不同的是，里面

14、的范围有子查询指定。2、 any 操作符：与每一个内容相匹配，有三种匹配形式。· =any ：功能与in 操作符时完全一样的· >any ：比子查询中返回记录最小还要大的数据· <any ：比子查询中返回记录最大还要小的数据3、 all 操作符：与每一个内容相匹配，有两种匹配形式：· >all ：比子查询中返回的记录还要大的数据· <all ：比子查询中返回的记录好要小的数据大部分情况下，如果最终的查询结构之中出现select子句，但是又不能使用统计函数的时候，就中子查询中统计信息，即：又复杂统计的地方大部分都需要使用子

15、查询。将多个表中查询的数据的结果保存一个临时表中使用的例如临时表查询：select * from( select bname from bookwherebidin( select bidfrom penalty) temp ;数据更新操作：数据库表复制语法：create table名称as select * from复制的表名称；数据增加：语法：insert into表名称( 字段 1，字段 2，.) v alues ( 值 1，值 2.)；增加 data 数据：· 第一种： 可以只按照已有的字符串的格式编写字符串，例如“12-12 月-80”；· 第二种： 利用 to_

16、date() 函数将字符串变为date 类型的数据；· 第三种： 如果设置的时间为当前系统时间，则使用sysdate ；数据修改：修改语法：update件(s)表名称set 更新字段1=更新值 1，更新字段2=更新值 2，.where更新条如果不写修改条件表示修改全部数据删除：删除语法：delete from表名称where删除条件 (s)如果不写删除条件表示删除全部如果删除的时候没有匹配的条件，则返回0；事物处理：所有的事物处理操作都是针对每一个session 进行的，中oracle 数据库之中，把每一个连接到数据库上的用户都称为一个session，每一个session 之间彼此独

17、立，彼此之间不会有任何的通讯，而每一个session 独享自己的事物控制，而事物控制之中主要使用两个命令：1、 事物的回滚： rollback更新操作回到原点2、 事物的提交： commit ，真正的发出更新的操作所有的数据更新一定都会受到事物的控制数据伪列：rownum ：表示为每一个显示的记录都会自动的随着查询生成行号，每次都是动态地重新生成；通过使用 rownum可以显示分页查询。语法：select * from (select rownumr,ename,comm,job,sal from emp where rownum<=10)tempwhere temp .r>5;&

18、#183;分页查询概念：将查询出来的数据保存到一个临时数据表中，在查询临时表中的的数据，即：使用 rownum查询出来的有序的数字保存到一个数据表中作为一个列，再查询这个临时表中的指定的条数。· rowid： 表示的是每一行数据保存的物理地址的编号；每一条记录的rowid都不会重复，即：就算表中的所有列的数据内容都重复了，rowid也是不会重复的，而且以一个rowid 为例，说明rowid 组成，例如： aaal+xaaeaaaaanaaa1、 数据对象号： aaal+x；2、 相对文件号： aae ；3、 数据块号： aaaaan；4、 数据行号： aaa ；ddl数据定义语言：对

19、于数据库而言实际上每一张表都表示的是一个数据库对象，而在数据库对象指的就是ddl定义的所有操作，例如：表、视图、索引、序列、约束等等，都属于对象的操作，所以表的建立就是对象的建立，而对象的操作主要分为以下三类语法：1、 创建对象： create对象名称.2、 删除对象： drop 对象名称.3、 修改对象： alter对象名称.常用的数据字段：每一张数据表实际上都是由假设干个字段组成，而每一个字段都会又其对应的数据类型，而在 oracle 之中，常用的数据类型有如下几种：no数据类型关键字描述1字符串varchar2(n)其中 n 表示的是字符串所能保存的最大长度，保存 200 个左右的内容基

20、本2整数number(n)表示最多为n 位的小数，有时候也可以使用代替int3小数number(n,m)其中 m 为小数位， n 为整数位， 有时候也可以使用float 代替4日期date存储日期时间5大文本clob可以存储海量文字4g，例如：电影、电视剧6大对象blob存放二进制数据，例如：电影、mp3 、图片、文字一般在开发中使用最多的：varchar2() 、number 、da te、clob ，而对于blob字段一般使用较少，首先 blob 可以存放4g 的二进制数据，但是存放进去之后，一是数据库过于庞大，二是读取不方便；数据库创建 :语法：create table表名称字段 1数据

21、类型(default默认值 ，字段 2数据类型default默认值 ，.字段 2数据类型default默认值 注意：最后一个不能添加“，”)；复制一张完整的表：create table复制表的名称as 字查询复制一张只包含20 部门的雇员信息的表：create table emp20 as select * from emp where deptno=20复制表结构：create table empnull as select * from emp where 1=2;即给定一个不成立的条件删除表：drop table表名称为表重命名：在 oracle 数据库之中，所有的数据都是通过数据字典保

22、存的！并且在oracle 数据库中提供了三种数据字典，最常用的是：dba_、user_，两个数据字典。例如： select * from user_tables； 就是一个“ user_”的数据字典；在 oracle 数据库之中所有的数据都是按文件保存的，等于所有的内容都会在字典中注册，那么修改表名称也就等于修改了一条数据而已，修改表名称语法：rename旧的表名称to 新的表名称； 截断表：如果使用delete删除表中的数据时会进行事物的回滚，如果此时希望彻底释放掉一张表所占用的全部资源表空间、 索引等就可以使用oracle 数据库特有的截断表的语法：truncate table表名称；表的

23、删除：表的删除操作就是指数据库对象的删除，使用drop 语法：drop table表名称；在 oracle 数据库之中删除一张表之后，并不会立即删除表，而是将删除的表全部放在了一个回收站中；查看回收站语法：show recyclebin；这个时候可以发现所有已经删除的表都在回收站之中保存，那么可以使用以下的语法恢复表：flashback table表名称to beforedrop ；也可以直接删除回收站中的一些数据表：purge table表名称；清空回收站语法：purge recyclebin；如果希望彻底删除数据表，即直接删除数据不放入回收站中，则使用：drop table表名称purge

24、 ；修改表的结构：如果一张建立好的数据表， 发现其初期的结构已经不能满足后期的使用要求， 则可以进行表的修改操作，而表的修改操作实际上就是数据库对象的修改操作，使用 alter 指令完成：增加表的字段：alter table表名称add( 列名称数据类型default默认值 ， 列名称数据类型 default默认值 ，.)；如果增加的数据列没有设置默认值，则所有已有的数据的列的内容都是null ，而如果增加的列指定的default默认值，则所有的已有列都是设置的默认值。如果 修改表的结构使用语法：alter table表名称modify( 列名称数据类型default默认值 ， 列名称数据类型

25、 default默认值 ，.)；软件项目开发的基本流程：1、 获取需求；软件销售2、 需求分析；根据客户的提示，作出需求分析3、 业务分析，数据库设计及接口设计；数据库表的设计4、 编码实现；业务流程5、 软件测试；理论上应该又专门的测试人员6、 软件培训以及项目的维护；约束：如果设置约束的名称的话，需要将约束的名称写在创建表的结构的最后一行，设置约束的名称一般使用约束的类型 _字段名称 作为约束名称。并使用constraint修饰。约束的只要功能是保证表中的数据的合法性，只按照约束的分类，主要分为五类约束：1、 非空约束， 2、唯一约束， 3、主键约束，4、检查约束， 5、外间约束；非空约束

26、 not null ： nk当数据表中的某个字段上的内容不希望设置为null 的话，则使用 not null进行指定。唯一约束 unique ： uk唯一约束指的是每一个列上的数据是不允许重复的，例如 email 地址每一个用户肯定不重复，那么就使用唯一约束完成。语法：在创建表结构的时候，在需要约束的字段后面添加“unique ”即可。如果在建立唯一约束的时候没有定义约束名称，那么如果插入相同的信息则错误提示不够完整，而是使用系统自动分配的代号提示，所以在建立唯一约束的时候最好定义约束的名称，定义唯一约束名称格式：“约束类型 _字段”，例如“ uk_email ”；指定约束名称使用constr

27、aint完成。例如： constraint uk_email unique(字段名称 )；主键约束 primary key： pk主键约束= 非空约束+唯一约束；主键一般作为数据的唯一的一个标记出现，主键不能插入null 值，并且主键不能重复；设置复合主键的时候在设置的字段名称后添加需要设置的多个的字段名称，并使用“，” 号分隔开；检查约束 check ： ckconstraint约束名称check( 约束条件 )；主外键约束： fk主外键约束是对与两张表中进行的，段的取值范围由父表决定。这两张表是存在父子关系的，即： 子表中的某个字例如： constraint fk_mid foreing

28、key(mid)references member(mid)；1、如果希望删除主表中数据删除之后，子表中对应的数据也可以删除的话，则可以在建立约束的时候指定一个级联删除的功能，即：在创建主外键关系的时候在后面添加：ondelete cascade指定级联删除语法：即可；constraint主外键名foreignkey( 本表中的字段) references外键表名 (外键表字段 ) on delete cascade为表中的数据增加一些过滤条件，constraint主外键名foreignkey( 本表中的字段) references外键表名 (外键表字段 )外键表即是父表使用这种方式删除主表的

29、数据的时候，对应的子表中的数据会一起删除掉；2、当主表中的数据删除之后，对应的子表中的数据相关项也希望将其设置为null ，而不是删除，在创建主外键关系的时候在后面添加：on delete set null即可；语法：constraint主外键名foreignkey( 本表中的字段) references外键表名 (外键表字段 ) on delete set null3、在 oracle 数据库中专门提供了一个强制性删除表的操作，即：不需要关心约束，在删除表的时候写上一句： “ cascade constraint”。drop table member cascade constraint p

30、urge；使用此种方式删除的时候，子表不会被删除。修改约束：约束本身也属于数据库对象，那么也肯定可以进行修改操作，而且只要修改都使用alter 指令，约束的修改只要指以下两种操作：· 为表增加约束：alter table表名称add constraint约束名称约束类型 (字段 );· 删除表的约束alter table表名称drop constraint约束名称 ;如果要维护约束，肯定需要一个正确的名字才可以，可是在这五种约束之中，非空约束是一个特殊的约束，无法操作。检查约束：在 oracle 数据库之中所有的对象都会在数据字典之中保存，而约束也是一样的，所以如果要想知道

31、有哪些约束，可以直接查询“user_constraints”数据字典：select * from user_constraints；使用此种方式查询出来的约束并不知道在哪个字段上的约束，所以此时可以查看另一张数据字典表“ user_cons_columns”查询集合操作：在数学的操作之中存在交、差、并、补的概念，而在数据库的查询中也存在此概念，有如下连接符号：1、 union ： 连接两个查询，相同的部分不显示；2 、 union all： 连接两个查询，相同的部分显示；3、 intersect： 返回两个查询中的相同部分；4、 minus ： 返回两个查询中的不同部分；1、 数据库的创建：&

32、#183; 主要的数据类型：varchar2、 number、date 、clob ；· 创建表的语法：create table表名称 ()；· 删除表的语法：drop table表名称；· 清空回收站：purge recyclebin；2、 约束：约束是保证表中的数据的完整性的一种手段，约束一定要在表建立的同时设置号， 而且在表真正使用之前一定要有约束；· 约束的分类：非空约束(notnull) 、唯一 (unique) 、主键 (primarykey) 、检查约束 check 、外键约束 (foreingkey) ；· 设置外键的时候有以下

33、的注意点：|- 默认情况下应该先删除子表之后再删除父表；|- 级联删除： on delete cascade；|- 级联设置null ： on delete set null；3、 序列： sequence ，可以形成自动增长列的操作；· 序列中有两个属性：nextval 、currval ；· 序列的操作一定要手工进行控制；视图：create or replace viewas 子查询 ;在创建视图的时候存在两个选项。· 选项一： on with check option在创建视图时使用此选项的话，就不能去更新试图的创建条件。· 选项二： with r

34、ead only虽然使用with check option可以保证视图的创建条件不被更新，视图名称但其他字段却允许更新。视图本身不是具体的真实数据，而是一些查询语句，所以在创建视图的时候一般将视图设置为只读视图。在一个项目中，视图的数量有可能超过表的数量，因为查询语句多。同义词：同义词在 oracle 数据库之中成为synonym，同义词创建语法：create public synonym同义词的名称for 用户名表名称 ;视图相当于sql 语句，创建视图的语句如下：在创建同义词的时候如果没有使用public ，则表示创建的不是公共同义词。索引：索引的主要功能就是用于提升数据库的操作性能。在

35、oracle 数据库之中创建索引有以下两种方式：· 主键约束：如果一张表中的列上存在了主键约束的话自动创建索引；· 手工创建：在某一个操作列上指定一个索引；create index索引名称on 表名称 (列名称 )；以上的索引就是oracle 数据库中的b 树索引 ：索引就是创建一颗树的数据结构保存数据，但是如果需要在这颗树上面频繁修改的话，代码的性能就会有所降低，所以性能的提升永远都是相对的。在 oracle 数据库中包含十几中索引，最简单的索引是b 树索引，还有位图索引、反向索引、函数索引等等。用户管理：sql语句分为三类：dml 、ddl 、dcl ；dcl主要表示的

36、是数据库的控制语句，控制语句就是操作权限，而在dcl 之中，主要又两个语法：grant 、revoke ；· grant ：授权；· revoke：回收； 创建用户：一、创建用户create user用户名identified by密码；此时虽然创建了用户，但是没有设置session 对象，则无法登陆到oracle 数据库之中二、设置session 权限grant create session to创建好的用户名；对于使用以上两步骤创建的用户虽然可以登陆但是不能创建表关于数据库表的保存问题：在 oracle 数据库之中所有的数据都是和保存到硬盘上面的，但是不是每一张数据表都

37、保存在硬盘上，而是表空间保存在硬盘上面，而数据表保存在表空间中。如果把硬盘 表示成一个图书馆的话，那么表空间就表示每一个书柜，每一张表就表示柜子上的一本书。三、给新建用户授权创建表的权限：grant create table to用户名；此时只是将数据库表的创建权限给了新建用户，但是并没有把表空间的操作权限给新建用户，所以用户任然无法创建表，因为没有地方可以保存表。·创建用户的简化操作：在 oracle 数据库中为用户提供了许多的角色，每一个会包含多个权限，而角色主要又两个： connect 、resource ；· 修改用户密码：alter user用户名identified by新密码；· 让用户密码失效：alter user· 锁定用户：用户名password expire ；alter user· 解锁用户：用户名account lock；alter user用户名account unlock；如果不同的用户需要访问其他用户的表则直接使用完整的“用户名.表名称”即可。权限有 4 种， select 、 insert 、 del