oracle数据库.doc

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名称类型描述1DEPTNONUMBER(2)表示部门编号，由两位数字组成2DNAMVARCHAR2(14)部门名称，最多由14位字符组成3LOCVARCHAR2(13)部门所在的位置2、雇员表：empNo名称类型描述1EMPNONUMBER(4)雇员的编号，由4位数字组成2ENAMEVARCHAR2(10)雇员的姓名，由10位字符组成3JOBVARCHAR2(9)雇员的职位4MGRNUMBER(4)雇员的领导编号5HIREDATEDATE雇员的雇佣日期6SALNUMBER(7,2)基本工资，其中有2位小数，5位整数一共7位7COMMNUMBER(7,2)奖金（佣金）8DEPTNONUMBER(2)雇员所在的部门编号3、工资等级表：salgradeNo名称类型描述1GRADENUMBER工资等级2LOSALNUMBER此工资的最低等级3HISALNUMBER此工资的最高等级4、工资表：bonusNo名称类型描述1ENAMEVARCHAR2(10)雇员姓名2JOBVARCHAR2(9)雇员职位3SALNUMBER雇员的工资4COMMNUMBER雇员的奖金简单查询：SQL是数据库语言。SQL通常用于数据库之间的通讯，ANSI（美国标准学会）声称，SQL是关系型数据库系统的标准语言。DML（Data Manipulation Language，数据库操作语言那）用于检索或修改数据DDL（Data Definition Language，数据定义语言）用于定义数据的结构，如创建、修改或删除数据库对象。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 SAL FROM emp;|SELECT ename,job,Sal*12 SAL FROM emp;“|”用于连接字符串限定查询：SELECT DISTINCT * | 字段 别名,字段别名FROM 表名称 别名WHERE 条件（S）;查询排序：SELECT DISTINCT * | 字段 别名,字段别名FROM 表名称 别名WHERE 条件（S）ORDER BY 字段ASC | DESC ;如果排序时有多个字段的时候，用“，”分隔开单行函数：单行函数主要分为五类：字符函数、数字函数、日期函数、转换函数、通用函数在Oracle数据库中就算是使用函数进行简单的查询也必须写出完整的SQL语句，但Oracle数据库为了查询方便专门提供了一个方便查询的虚拟表“dual”表。字符函数：字符函数的功能主要是进行字符串数据的操作UPPER（字符串）：将字符串变成大写返回LOWER（字符串）：将字符串变成小写返回INITCAP（字符串 | 列）：开头首字母大写LENGTH（字符串 | 列）：求出字符串的长度REPLACE（字符串 | 列）：进行替换需要三个参数：1、列名2、替换前的字符3、替换后的字符。SUBSTR（字符串 | 列，开始点结束点）：字符串截取使用SUBSTR截取字符串时有两种语法：1、 SUBSTR（字符串 | 列，开始点）：表示从开始一直截取到末尾2、 SUBSTR（字符串 | 列，开始点，结束点）：表示截取制定区域的字符串数字函数:ROUND（数字 | 列保留小数的位数）：四舍五入的操作TRUNC（数字 | 列保留小数的位数）：舍弃指定位置的内容MOD（数字1，数字2）：取模，去余数日期函数：SELECT SYSDATE FROM dual ; 取得当前日期SYSDATE + n;表示n天后的日期SYSDATE n;表示n天前的日期SYSDATE 日期;表示当前日期和指定日期之间的天数LAST_DATE（日期）：求出指定日期的最后一天NEXT_DATE（日期，星期数）：求出下一个指定星期X的日期ADD_MONTHS（星期，数字）：求出若干月之后的日期MONTHS_BETWEEN（日期1，日期2）：求出两个日期之间所经历的月份转换函数：TO_CHAR（字符串 | 列，格式字符串）：将日期或者是数字转换成字符串显示如果将日期转换成字符串显示的话，月份前面会添加一个“0”，如果想去掉这个“0”需要在格式化字符串里面并且是最前面加上fm即可。如果需要显示出时间则需要在格式化字符串中添加时、分、秒缩写即可：时：hh | 24hh；分：mi；秒：ss；如果需要将数字格式化就在格式化字符串中使用一个“9”代表一个数字，如果需要表示不同的数字类型，如货币：就在格式化字符串前面添加缩写；货币：“L”表示“Local”含义，即当前计算机语言环境下的货币符号；TO_DATE（字符串，格式字符串）：将字符串变为DATE数据显示TO_NUMBER（字符串）：将字符串变成数字显示通用函数:通用函数主要有两个：NVL()、DECODE()，这两个函数是Oracle数据库独有的函数。1、 NVL()函数，处理null；设置函数需要两个参数第一个是列，第二个是如果为null需要替换的数字。2、 DECODE()函数：多条件，多数值判断。语法：DECODE(数值 | 列，判断值1，显示值1，判断值1，)SELECT empno,ename,job,DECODE(job,CLERK,办事员,SALESMAN,销售员) FROM emp;多表查询：多表查询语法：SELECT DISTINCT * | 字段 别名,字段别名.FROM 表名称 别名，表名称 别名,WHERE 条件（S）ORDER BY 字段ASC | DESC ;注意：一般在查询不知道表中的数据时，可以使用COUNT()函数查询出数据库表中的数据条数，这样就可以避免由于数据库表数据量太大带来的损失。在Oracle数据库中查询的时候需要使用左右连接查询就使用“（+）”符号：1、“（+）=”：如果将“（+）”放在等号左边就表示左连接查询；2、“=（+）”：如果将“（+）”放在等号右边就表示右连接查询；统计函数及分组查询:统计函数： COUNT()：查询表中的数据记录； AVG()：求出平均值； SUM()：求和； MAX()：求出最大值； MIN()：求出最小值；分组统计：SELECT DISTINCT * | 分组字段1 别名 ，分组字段2 别名，. | 统计函数FROM 表名称 别名，表名称 别名，.WHERE 条件(s)GROUP BY 分组字段1 ，分组字段2，.ORDER BY 排序字段 ASC | DESC ，排序字段 ASC | DESC在进行分组查询时不管是单字段还是多字段，一定要有一个前提查询的数据存在重复数据在WHERE子句中不能使用统计函数如果需要对分组后的数据再次进行过滤，则使用HAVING子句完成，并且HAVING子句必须出现在GROUP BY 子句之后，因为是对分组后的数据再次过滤。WHERE和HAVING的区别：1、 WHERE：是在执行GROUP BY操作之前进行的数据过滤，表示从全部时候据之中筛选出部分的数据，在WHERE之中不能使用统计函数。2、 HAVING：是在GROUP BY分组之后的再次过滤，可以在HAVING子句中使用统计函数。子查询：子查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询；子查询出现的规律：WHERE：子查询一般只返回单行单列、多行单列、单行多列的数据；FROM：子查询发那会的一般是多行多列的数据，当做一张临时表出现；如果子查询返回的是多行单列数据的话，这个时候就需要使用三种判断：IN、ANY、ALL；1、 IN操作符：用于指定一个子查询的判断范围，与模糊查询中的IN是一样的，唯一不同的是，里面的范围有子查询指定。2、 ANY操作符：与每一个内容相匹配，有三种匹配形式。 =ANY：功能与IN操作符时完全一样的 ANY：比子查询中返回记录最小还要大的数据 ALL：比子查询中返回的记录还要大的数据 ALL：比子查询中返回的记录好要小的数据大部分情况下，如果最终的查询结构之中出现SELECT子句，但是又不能使用统计函数的时候，就中子查询中统计信息，即：又复杂统计的地方大部分都需要使用子查询。将多个表中查询的数据的结果保存一个临时表中使用的示例（临时表查询）：SELECT * FROM(SELECT BName FROM Book WHERE BID IN(SELECT BID FROM Penalty)temp;数据更新操作：数据库表复制语法：CREATE TABLE 名称 AS SELECT * FROM 复制的表名称；数据增加：语法：INSERT INTO 表名称 (字段1，字段2，.) VALUES (值1，值2.)；增加DATA数据：第一种：可以只按照已有的字符串的格式编写字符串，例如“12-12月-80”；第二种：利用TO_DATE()函数将字符串变为DATE类型的数据；第三种：如果设置的时间为当前系统时间，则使用SYSDATE；数据修改：修改语法：UPDATE 表名称 SET 更新字段1=更新值1，更新字段2=更新值2，.WHERE 更新条件(S)如果不写修改条件表示修改全部数据删除：删除语法：DELETE FROM 表名称 WHERE 删除条件(S)如果不写删除条件表示删除全部如果删除的时候没有匹配的条件，则返回0；事物处理：所有的事物处理操作都是针对每一个session进行的，中oracle数据库之中，把每一个连接到数据库上的用户都称为一个session，每一个session之间彼此独立，彼此之间不会有任何的通讯，而每一个session独享自己的事物控制，而事物控制之中主要使用两个命令：1、 事物的回滚：ROLLBACK更新操作回到原点2、 事物的提交：COMMIT，真正的发出更新的操作所有的数据更新一定都会受到事物的控制数据伪列：ROWNUM：表示为每一个显示的记录都会自动的随着查询生成行号，每次都是动态地重新生成；通过使用ROWNUM可以显示分页查询。语法：SELECT * FROM (SELECT ROWNUM r,ename,comm,job,sal FROM emp WHERE ROWNUM5;分页查询概念：将查询出来的数据保存到一个临时数据表中，在查询临时表中的的数据，即：使用ROWNUM查询出来的有序的数字保存到一个数据表中作为一个列，再查询这个临时表中的指定的条数。ROWID：表示的是每一行数据保存的物理地址的编号；每一条记录的ROWID都不会重复，即：就算表中的所有列的数据内容都重复了，ROWID也是不会重复的，而且以一个ROWID为例，说明ROWID组成，例如：AAAL+XAAEAAAAANAAA1、 数据对象号：AAAL+X；2、 相对文件号：AAE；3、 数据块号：AAAAAN；4、 数据行号：AAA；DDL数据定义语言：对于数据库而言实际上每一张表都表示的是一个数据库对象，而在数据库对象指的就是DDL定义的所有操作，例如：表、视图、索引、序列、约束等等，都属于对象的操作，所以表的建立就是对象的建立，而对象的操作主要分为以下三类语法：1、 创建对象：CREATE 对象名称.2、 删除对象：DROP 对象名称.3、 修改对象：ALTER 对象名称.常用的数据字段：每一张数据表实际上都是由若干个字段组成，而每一个字段都会又其对应的数据类型，而在Oracle之中，常用的数据类型有如下几种：No数据类型关键字描述1字符串VARCHAR2(n)其中n表示的是字符串所能保存的最大长度，基本保存200个左右的内容2整数NUMBER(n)表示最多为n位的小数，有时候也可以使用INT代替3小数NUMBER(n,m)其中m为小数位，n为整数位，有时候也可以使用FLOAT代替4日期DATE存储日期时间5大文本CLOB可以存储海量文字（4G），例如：电影、电视剧6大对象BLOB存放二进制数据，例如：电影、MP3、图片、文字一般在开发中使用最多的：VARCHAR2()、NUMBER、DATE、CLOB，而对于BLOB字段一般使用较少，首先BLOB可以存放4G的二进制数据，但是存放进去之后，一是数据库过于庞大，二是读取不方便；数据库创建:语法：CREATE TABLE 表名称(字段1数据类型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数据库之中，所有的数据都是通过数据字典保存的！并且在Oracle数据库中提供了三种数据字典，最常用的是：dba_、user_，两个数据字典。例如：select * from user_tables；就是一个“user_”的数据字典；在Oracle数据库之中所有的数据都是按文件保存的，等于所有的内容都会在字典中注册，那么修改表名称也就等于修改了一条数据而已，修改表名称语法：RENAME 旧的表名称 TO 新的表名称；截断表：如果使用DELETE删除表中的数据时会进行事物的回滚，如果此时希望彻底释放掉一张表所占用的全部资源（表空间、索引等）就可以使用Oracle数据库特有的截断表的语法：TRUNCATE TABLE 表名称；表的删除：表的删除操作就是指数据库对象的删除，使用DROP语法：DROP TABLE 表名称；在Oracle数据库之中删除一张表之后，并不会立即删除表，而是将删除的表全部放在了一个回收站中；查看回收站语法：SHOW RECYCLEBIN；这个时候可以发现所有已经删除的表都在回收站之中保存，那么可以使用以下的语法恢复表：FLASHBACK TABLE 表名称 TO BEFOREDROP；也可以直接删除回收站中的一些数据表：PURGE TABLE 表名称；清空回收站语法：PURGE RECYCLEBIN；如果希望彻底删除数据表，即直接删除数据不放入回收站中，则使用：DROP TABLE 表名称 PURGE；修改表的结构：如果一张建立好的数据表，发现其初期的结构已经不能满足后期的使用要求，则可以进行表的修改操作，而表的修改操作实际上就是数据库对象的修改操作，使用ALTER指令完成：增加表的字段：ALTER TABLE 表名称 ADD(列名称 数据类型 DEFAULT 默认值，列名称 数据类型DEFAULT 默认值，.)；如果增加的数据列没有设置默认值，则所有已有的数据的列的内容都是null，而如果增加的列指定的DEFAULT默认值，则所有的已有列都是设置的默认值。如果修改表的结构使用语法：ALTER TABLE 表名称 MODIFY(列名称 数据类型 DEFAULT 默认值，列名称 数据类型DEFAULT 默认值，.)；软件项目开发的基本流程：1、 获取需求； 软件销售2、 需求分析； 根据客户的提示，作出需求分析3、 业务分析，数据库设计及接口设计； 数据库表的设计4、 编码实现； 业务流程5、 软件测试； 理论上应该又专门的测试人员6、 软件培训以及项目的维护；约束：如果设置约束的名称的话，需要将约束的名称写在创建表的结构的最后一行，设置约束的名称一般使用约束的类型_字段名称作为约束名称。并使用CONSTRAINT修饰。约束的只要功能是保证表中的数据的合法性，只按照约束的分类，主要分为五类约束：1、 非空约束，2、唯一约束，3、主键约束，4、检查约束，5、外间约束；非空约束（NOT NULL）：NK当数据表中的某个字段上的内容不希望设置为null的话，则使用NOT NULL进行指定。唯一约束（UNIQUE）：UK唯一约束指的是每一个列上的数据是不允许重复的，例如email地址每一个用户肯定不重复，那么就使用唯一约束完成。语法：在创建表结构的时候，在需要约束的字段后面添加“UNIQUE”即可。如果在建立唯一约束的时候没有定义约束名称，那么如果插入相同的信息则错误提示不够完整，而是使用系统自动分配的代号提示，所以在建立唯一约束的时候最好定义约束的名称，定义唯一约束名称格式：“约束类型_字段”，例如“UK_email”；指定约束名称使用CONSTRAINT完成。例如：CONSTRAINT UK_email UNIQUE(字段名称)；主键约束（Primary Key）：PK主键约束 = 非空约束 + 唯一约束；主键一般作为数据的唯一的一个标记出现，主键不能插入null值，并且主键不能重复；设置复合主键的时候在设置的字段名称后添加需要设置的多个的字段名称，并使用“，”号分隔开；检查约束（Check）：CK为表中的数据增加一些过滤条件，CONSTRAINT 约束名称 CHECK(约束条件)；主外键约束：FK主外键约束是对与两张表中进行的，这两张表是存在父子关系的，即：子表中的某个字段的取值范围由父表决定。CONSTRAINT 主外键名 FOREIGN KEY(本表中的字段) REFERENCES 外键表名(外键表字段) 外键表即是父表例如：CONSTRAINT fk_mid FOREING KEY(mid)REFERENCES member(mid)；1、如果希望删除主表中数据删除之后，子表中对应的数据也可以删除的话，则可以在建立约束的时候指定一个级联删除的功能，即：在创建主外键关系的时候在后面添加：ON DELETE CASCADE即可；指定级联删除语法：CONSTRAINT 主外键名 FOREIGN KEY(本表中的字段) REFERENCES 外键表名(外键表字段) ON DELETE CASCADE使用这种方式删除主表的数据的时候，对应的子表中的数据会一起删除掉；2、当主表中的数据删除之后，对应的子表中的数据相关项也希望将其设置为null，而不是删除，在创建主外键关系的时候在后面添加：ON DELETE SET NULL即可；语法：CONSTRAINT 主外键名 FOREIGN KEY(本表中的字段) REFERENCES 外键表名(外键表字段) ON DELETE SET NULL3、在Oracle数据库中专门提供了一个强制性删除表的操作，即：不需要关心约束，在删除表的时候写上一句：“CASCADE CONSTRAINT”。DROP TABLE member CASCADE CONSTRAINT PURGE；使用此种方式删除的时候，子表不会被删除。修改约束：约束本身也属于数据库对象，那么也肯定可以进行修改操作，而且只要修改都使用ALTER指令，约束的修改只要指以下两种操作： 为表增加约束：ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段); 删除表的约束ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;如果要维护约束，肯定需要一个正确的名字才可以，可是在这五种约束之中，非空约束是一个特殊的约束，无法操作。检查约束：在Oracle数据库之中所有的对象都会在数据字典之中保存，而约束也是一样的，所以如果要想知道有哪些约束，可以直接查询“user_constraints”数据字典：SELECT * FROM user_constraints；使用此种方式查询出来的约束并不知道在哪个字段上的约束，所以此时可以查看另一张数据字典表“user_cons_columns”查询集合操作：在数学的操作之中存在交、差、并、补的概念，而在数据库的查询中也存在此概念，有如下连接符号：1、 UNION：连接两个查询，相同的部分不显示；2、 UNION ALL：连接两个查询，相同的部分显示；3、 INTERSECT：返回两个查询中的相同部分；4、 MINUS：返回两个查询中的不同部分；1、 数据库的创建： 主要的数据类型：VARCHAR2、NUMBER、DATE、CLOB； 创建表的语法：CREATE TABLE 表名称()； 删除表的语法：DROP TABLE 表名称； 清空回收站：PURGE RECYCLEBIN；2、 约束：约束是保证表中的数据的完整性的一种手段，约束一定要在表建立的同时设置号，而且在表真正使用之前一定要有约束； 约束的分类：非空约束(NOT NULL)、唯一(UNIQUE)、主键(PRIMARY KEY)、检查约束（CHECK）、外键约束(FOREING KEY)； 设置外键的时候有以下的注意点：|- 默认情况下应该先删除子表之后再删除父表；|- 级联删除：ON DELETE CASCADE；|- 级联设置NULL：ON DELETE SET NULL；3、 序列：SEQUENCE，可以形成自动增长列的操作； 序列中有两个属性：nextval、currval； 序列的操作一定要手工进行控制；视图：视图相当于SQL语句，创建视图的语句如下：CREATE OR REPLACE VIEW 视图名称AS 子查询;在创建视图的时候存在两个选项。 选项一：ON WITH CHECK OPTION在创建视图时使用此选项的话，就不能去更新试图的创建条件。 选项二：WITH READ ONLY虽然使用WITH CHECK OPTION 可以保证视图的创建条件不被更新，但其他字段却允许更新。视图本身不是具体的真实数据，而是一些查询语句，所以在创建视图的时候一般将视图设置为只读视图。 在一个项目中，视图的数量有可能超过表的数量，因为查询语句多。同义词：同义词在Oracle数据库之中成为SYNONYM，同义词创建语法：CREATE PUBLIC SYNONYM 同义词的名称 FOR 用户名 表名称;在创建同义词的时候如果没有使用PUBLIC，则表示创建的不是公共同义词。索引：索引的主要功能就是用于提升数据库的操作性能。在Oracle数据库之中创建索引有以下两种方式： 主键约束：如果一张表中的列上存在了主键约束的话自动创建索引； 手工创建：在某一个操作列上指定一个索引；CREATE INDEX 索引名称 ON 表名称(列名称)；以上的索引就是Oracle数据库中的B树索引：索引就是创建一颗树的数据结构保存数据，但是如果需要在这颗树上面频繁修改的话，代码的性能就会有所降低，所以性能的提升永远都是相对的。在Oracle数据库中包含十几中索引，最简单的索引是B树索引，还有位图索引、反向索引、函数索引等等。用户管理：SQL语句分为三类：DML、DDL、DCL；DCL主要表示的是数据库的控制语句，控制语句就是操作权限，而在DCL之中，主要又两个语法：GRANT、REVOKE； GRANT：授权； REVOKE：回收；创建用户：一、创建用户CREATE USER 用户名 IDENTIFIED BY 密码；此时虽然创建了用户，但是没有设置session对象，则无法登陆到Oracle数据库之中二、设置session权限GRANT CREATE SESSION TO 创建好的用户名；对于使用以上两步骤创建的用户虽然可以登陆但是不能创建表关于数据库表的保存问题：在Oracle数据库之中所有的数据都是和保存到硬盘上面的，但是不是每一张数据表都保存在硬盘上，而是表空间保存在硬盘上面，而数据表保存在表空间中。如果把硬盘表示成一个图书馆的话，那么表空间就表示每一个书柜，每一张表就表示柜子上的一本书。三、给新建用户授权创建表的权限：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、DELETE、UPD