Oracle.doc

1、Oracle的安装安装完成后需要进行数据库的配置，之后进入到“口令管理”的界面上。如果要使用oracle数据库，主要使用以下四个用户：l 超级管理员：sys/change_on_installl 普通管理员：system/managerl 普通用户：soctt（scott）/tigerl 大数据用户：sh/sh 完成安装后自动在windows中注册若干服务，但是这些服务许多是默认启动的，则有可能造成速度的缓慢所以建议将其都修改为手动启动方式在这些服务中有两个服务最为重要：（1）监听服务：OracleOraDb10g_home1NSListener，以后如果程序要操作数据库或是一些远程客户端要连接数据库则必须启动此服务（2）数据库的实例服务：OracleServiceROOT保存数据库具体信息的服务，每个数据库有每个数据库的实例（服务）；命名规则：OracleService-,其中的-是配置的数据库的名称（严格来讲是SID）；2、Oracle的卸载。1、 直接进行卸载程序。2、 删除磁盘上的残留文件，如果删除不掉则进入安全模式下删除。3、 同时删除注册表中所有与Oracle相关的配置项。 如果没有正常的安装成功，除操作系统的问题外，直接采用以上第二，第三两个步骤就可以完成卸载。 3、 监听问题。监听服务是Oracle中最主要使用的一个服务，但是这个服务经常会出现错误，下面有两种解决方案：错误一：注册表使用了相关工具被删除了相关项。（每一个系统服务都会在注册表中有保存）；监听服务的注册项：HKEY_LOCAL_MACHINESYSTEMservices OracleOraDb10g_home1NSListener.里面有一个ImagePath选项，这个选项有可能会在使用优化软件的时候被自动删除，如果被删除的话，则应该按照上面的方式建立一个新的项，并且写上相应的内容。错误二：网络环境发生改变。 在Oracle 10g里面，如果网络环境出现了改变之后，有可能原始配置的网络就无法正常的工作，此时必须手工的进行网络配置的修改。 网络环境的更改最多的就是电脑名称的改变。 【我的电脑】【属性】【计算机名】【更改主机名称】。将新的名称修改为huang，这样一来实际上计算机的网络名称已经发生改变了，在Oracle 11g 中这个问题可以帮助用户自动的进行配置。但在11g以前就需要手工解决。 修改完成后会提示用户现在需要重新启动电脑。重启电脑后监听服务已经无法正常启动了，因为网络环境已经发生了改变，而这种问题是在学习及开发中出现最多的问题之一，如果想解决这个问题，必须修改配置网络配置文件： 网络配置文件路径：D:oracledb_1networkADMIN 下的listener.ora和tnsnames.ora文件。 listener.ora文件： # listener.ora Network Configuration File: d:oracledb_1networkadminlistener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:oracledb_1) (PROGRAM = extproc) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) (ADDRESS = (PROTOCOL = TCP)(HOST = huangwei-PC)(PORT = 1521) ) ) tnsnames.ora文件： # tnsnames.ora Network Configuration File: d:oracledb_1networkadmintnsnames.ora# Generated by Oracle configuration tools.ROOT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = huangwei-PC)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = root) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) 之后再重新启动监听器，但是如果以后程序连接Oracle 10g的话，则以上配置有可能还是不能够正常访问数据库，此时需要进入第二部操作,进行数据库名称的注册： 选择Net Manager进入到网络配置界面。 在Oracle中也提供了一个监听状态的检查命令：LSNRCTL.EXE。4、 sqlplus. 对于Oracle数据库主要使用的是命令的方式，而所有的命令都是使用sqlplus完成，可对于sqlplus的形式：l 一种是dos风格的sqlplus:sqlplus.exel 另一种是windows风格的sqlplus：sqlplusw.exe 在Oracle 10g中主要使用的是sqlplusw命令，因为其可以进行一些简单的数据调整（这些数据调整对开发没有任何的意义，只是看着舒服）。此时会找到默认的可用的数据库实例 范例：输入查询指令 select * from customer; 此时的数据已经正常显示，但是会出现折行问题，这个是默认的每行的数据大小，现在可以通过两个密令进行调整： l 设置每行显示的记录长度 SET LINESIZE 300;l 设置每页显示的记录长度 SET PAGESIZE 30; 这两个命令统一被称为格式化命令，但这些格式化命令在sqlplus下设置不是很明显。 但是sqlplus虽然显示上方便，可是对于命令的编辑不是很方便，如果正常的操作，命令些错了，则肯定使用方向键移到错误的地方进行修改，但是在sqlplus中方向键只能控制屏幕的移动，所以这个这个命令根本就不适合编辑，所以sqlplus中提供了ed和指令，可以进行记事本的调用和执行；l 使用ed调用记事本程序：ed ddd; 此时会提示用户说，现在找不到“ddd.sql”文件，询问是否创建，选择“是”，但是使用ed打开记事本之后，sqlplus会进入阻塞状态，无法使用。l 使用执行程序：ddd; 但是在windows中，由于提供了很多编辑软件，所以一般在外部执行程序，例如：在d:盘新建一个demo.txt的文件，里面保存了程序代码。 这个时候这个时候要读取硬盘上的文件，则必须写上完整的路径D:demo.txt,而且如果这个文件的后缀是“.sql”的话，则不用输入文件后缀（demo.sql）,输入d得到:demo，或者更省事直接写ddd:demo 在一个数据库中会有许多的用户，每个用户下都会有多张用户自己的表要查看所有的表，则使用下面的密令：Select * from tab; 而且用户很多，如果想要知道当前登录的用户则可以使用： show user； 指令完成。 而且在sqlplusw中也可以进行用户的切换，如使用以下语法完成：conn 用户名/密码 as sysdba; 使用sys用户登录：conn sys/change_on_install as sysdba; 但是一旦使用了sys用户登录后则无法查询customer表数据。 对于每一张表都有其属于的用户，所以一张表的完整名称是“用户名.表名”或者是“模式名.表名”，所以不同的用户要想访问其他用户的表，则应该加上用户名，即：现在访问名称：Select * from scott.emp; 可是一旦使用超级管理员登录以后，可以通过命令手工的控制数据库实例的打开和关闭。l 关闭数据库实例： shutdown immediate； 一旦关闭之后用户将无法直接连接sqlplusw命令，此时可以先使用“/nolog”登录，之后再用管理员登录。l 启动数据库实例： startup； 另外，sqlplusw命令窗口也可以调用本机的操作系统命令，使用“host”作为前缀。 Host copy d:demo.sql d:hello.txt5、 所需要的表。 学习Oracle所需要的表都是scott用户下的表，所以必须了解几张表的结构，和作用。雇员表 No字段 类型描述1Empno6、 SQL简介。 SQL(structured Query Language,结构化查询语言) 是一个功能强大的数据库语言，sql通常用于数据库的通讯。ANSI（美国国家标准学会）声称，SQL是关系数据库管理系统的标准语言。 SQL功能强大，概括起来，可分为三部分：l DML（Date Manipulation Language,数据库操作语言）用于检索或者修改数据。l DDL（Date Definition Language，数据定义语言） 用于定义数据库的结构，如创建、修改、或删除数据库对象。l DCL（Date Control Language,数据控制语言）用于定义数据库用户的权限。1、 简单的查询语言。 简单的查询语句的语法是: Select distinct *|具体的列 别名From 表名称where 条件(s)ORDER BY 排序字段1，排序字段2 asc|desc 别名的使用：Select empno 编号，ename 姓名，job 工作 from emp; 要求查出雇员的编号，姓名，工作，但是显示的格式：l 编号是7369的雇员，姓名是：SMITH，工作是：CLREK； 要想实现这个功能，则可以使用Oracle中的链接字符串操作，使用“|”表示。如果要加入一些显示的信息的话需要用“”括起来。 select 编号是:|EMPNO|的雇员，姓名是：|ENAME|,工作是:|JOB|. from emp; 在查询中也可以使用四则运算的功能，例如：要求，求每个雇员的姓名和年薪 Select ENAME,SAL*12 income form emp; 这里的sal*12意义很不明确，所以最好起个别名，但是在起别名时一定要回避中文。程序中支持+、-、*、/运算，所有的语句要有优先顺序。 限定查询：l 只要字段中存在内容，则表示此内容不为NULL，如果存在内容，则显示具体的值。l 不为空的字段表示：字段 IS NOT NULL;l 为空的字段表示：字段 IS NULL;Select * from emp where comm is null;l 同时满足多个条件，则要使用AND操作符进行条件的连接。l 如果需要表现出或者的概念，则要使用OR进行连接。表示多个条件中有一个满足。l NOT关键字可以取反，把真的变为假，假的变为真。l 通过括号表示一组条件。l 在sql语句中专门提供了指定范围查询的提供语句：BETWEEN.AND. 语法格式：字段 between 最小值 and 最大值； 实际上between.and等价：sal=1500 and sal1500 and sal 、=、=、=等计算的符；Select * from emp where ename like _M%;l 在SQL中如果想使用不等于符号，则有两种形式”、”!=”;Select * from emp where ename7499;Select * from emp where ename!=7499; l 排序时默认是升序。l 排序时放在整个sql语句的最后。select * from emp order by sal desc,hiredate asc;2、单行函数。 数据库系统中，每个数据库之间唯一不同的最大区别点在函数的支持上，使用函数可以完成一系列的操作。 单行函数的语法： Function_name(column|expression,org1,org，.) 单行函数分类：l 字符函数：接受字符输入并返回字符或数值。 将字符转换为小写UPPER(); select upper(simith) from dual; 将字符转换为大写LOWER(); SELECT lower(SIMITH) FROM dual; 使用initcap()函数将每个单词的第一个首字母大写； Select initcap(ename) from emp; 字符串除了可以用|连接之外，还可以使用concat()函数进行连接操作。 Select concat(hello ,world) from dual; 字符函数还可以进行字符串的截取、求出字符串的函数、进行指定内容的替换。 字符串截取：substring(hello,1,3); Substring()函数的截取点是从0还是从1开始，从0和从1效果是一样的，因为Oracle比较智能。 Substring()提供了从后截取的机制，也就是在参数中输入负数，substring(hello,-3,3); 字符串的长度：length(hello); 字符串的替换：replace(hello,l,x);l 数值函数：接受数值输入并返回数值。 四舍五入：ROUND(); Select round(554.6566) from dual;Selct round(4545,-2) from dual; 截取小数点：TRUNC(); Select trunc(555.555,2) from dual; 取余（取摸）：mod(); Select mod(10,3) from dual;l 日期函数：对日期数据类型进行操作。在Oracle中提供了很多与日期相关的函数，包括加减日期等等，但是在日期加减的时候有一些规律： 日期数字=日期； 日期+数字=日期； 日期-日期=数字(天数); Months_between():求出给定日期范围的月数。Select ename,months_between(sysdate,hiredate) from emp; Add_months():在指定日期上加上指定的月数，求出之后的日期。Select add_months(sysdate,4) from dual;Next_day():下一个的今天是哪一个日期。Select next_day(sysdate,星期一) from dual; Last_day():求出给定日期的最后一天日期。Select last_day(sysdate) from dual;l 转换函数：从一种数据类型转换到另一种数据类型。 To_char():转换为字符串 还可以对日期显示的转换 从运行结果中可以看到如果是5月的话，就用05表示，那么这个0称为前导0，可以用fm去掉这个前导0； 如果数字太长的话可以用to_char()对分割，一般中国用，（9表示一位数字）。 此外希望数字可以明确表示区域可以使用：（$美元符号）、（L：表示Local的缩写）select ename,to_char(hiredate,yyyy) 年,to_char(hiredate,mm) 月,to_char(hiredate,dd) 日 from emp;select to_char(hiredate,yyyy-mm-dd) from emp;select to_char(hiredate,fmyyyy-mm-dd) from emp;select to_char(sal,99,999) from emp;select to_char(sal,$99,999) from emp; To_number:转换为数字 Select to_number(111)+to_number(1111) from emp; To_date():转换为日期Select to_date(2012-01-11,yyyy-mm-dd) from dual;l 通用函数：NVL函数。 Nvl()函数可以将null值变为指定的内容。select (nvl(comm,0)+sal)*12 from emp; DECODE函数，类似于ifelseif.else语句。 Decode(column/expression,seach1,result1,seach2,result2,default)； Column/expression:列名或表达式 Seach1、seach2.seachi:用户比较的条件 Result1、result2.resulti:返回值 如果column/expression和seachi比较，如果相同的话，则返回resulti，如果没有与col/exprission匹配的，就返回default。select decode(5,1,返回值为1,2,返回值2,3,返回值3,4,返回值为4,没有匹配的值)from dual;2、 多表查询。 Select distinct *|具体的列 别名From 表名称1 别名1，表名称2 别名2where 条件(s)ORDER BY 排序字段1，排序字段2 asc|desc select * from emp e,dept d where e.deptno=d.deptno; 左右查询：默认使用右查询 （加号在左边=右连接，加号在右边=左连接） 左查询：select * from emp e,dept d where e.deptno(+)=d.deptno; 右查询：select * from emp e,dept d where e.deptno=d.deptno(+); SQL:1999语法对sql的支持： SELECT table1.column,table2.columnFROM table1 CROSS JOIN table2|NATURAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2ON(table1.column_name=table2.column_name)|LEFT|RIGHT|FULL OUTER JOIN table2ON(table1.column_name=table2.column_name);l 交叉查询（cross join）:产生笛卡尔积 Select * from emp cross join dept;l 自然查询（natural join）:自然查询Select * from emp natural join dept;l Using()子句：直接关联查询Select * from emp join dept using(deptno) where deptno=30;l On子句：用户自己编写连接的条件Select * from emp e join dept p on(e.deptno=p.deptno);l 左连接（左外连接）、右连接（右外连接）Select * from emp e left outer join dept d on(e.deptno=d.deptno) ;Select * from emp e right outer join dept d on(e.deptno=d.deptno); 3、组函数和分组统计： Sql中常用的数组函数有以下几个：l Count():求出全部的记录l Max():求出一组中最大值l Min():求出最小值l Avg():求出平均值l Sum():求和 分组统计： Select distinct *|具体的列 别名From 表名称1 别名1，表名称2 别名2group by 分组条件 having 分组条件where 条件(s)ORDER BY 排序字段1，排序字段2 asc|desc l 查询出每个部门的平均工资：select deptno,avg(sal) from emp group by deptno;l 在程序中使用分组函数的时候，只有两种情况下可以使用： (1).程序中存在group by，并指定了分组条件，这样可以将分组条件一起查出来。 (2).如果不使用分组的话，则只能单独使用分组函数。 (3).在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。l 分组函数只能在分组中使用，不允许where语句中出现，如果要指定分组条件，则只能通过第二种条件的指令：having Select deptno,avg(sal) from emp group by deptno having avg(sal)2000;l 分组函数可以嵌套使用，但是在使用分组函数嵌套时不能再出现分组条件的查询语句。 例：求出平均工资最高的部门工资 select max(avg(sal) from emp group by deptno; 4.子查询。 在一个查询中还包括另一个查询，则此查询称为子查询，语法：Select distinct *|具体的列 别名From 表名称1 别名1，表名称2 别名2，（ Select distinct *|具体的列 别名 From 表名称1 别名1，表名称2 别名2 group by 分组条件 having 分组条件 where 条件(s) ORDER BY 排序字段1，排序字段2 asc|desc ）group by 分组条件 having 分组条件where 条件(s) Select distinct *|具体的列 别名 From 表名称1 别名1，表名称2 别名2 group by 分组条件 having 分组条件 where 条件(s) ORDER BY 排序字段1，排序字段2 asc|descORDER BY 排序字段1，排序字段2 asc|desc 范例：要求查出比7654工资要高的雇员的信息 select * from emp where sal(select sal from emp where empno=7654); 所有的子查询必须在“()”中编写。 子查询在操作中分为三类：l 单列子查询，返回的结果是一列的子查询，出现几率最高。l 单行子查询，返回的是多列。l 多行子查询，返回多条记录。 范例：要求查询出比7654工资高，同时与7788从事相同工作的全部雇员的信息。 select * from emp where sal(select sal from emp where empno=7654) and job=(select job from emp where empno=7788); 范例：部门的名称、部门的员工数，部门的平均工资，部门的最低收入雇员的姓名。 select d.dname,c,a,m from dept d,emp e,(select deptno,count(job) c,avg(sal) a,min(sal) m from emp group by deptno) ed where d.deptno=ed.deptno and e.sal=ed.m; 在子查询中存在以下三中操作符：l In,指定一个查询的范围求出每个部门的最低工资的雇员信息Select * from emp where sal in(select min(sal) from emp group by deptno);l Any(1)、=any:与in操作符完全一样；(2)、any:比里面最小的值要大；(3)、all:比里面最大的值要大；(2)、all:比里面最小的值要小； 6、高级查询。 6.1、集合操作符l Union all(返回各个查询检索出的所有行，包括重复)l Union(返回各个查询检索出的所有行，不包括重复行)l Intersect(返回两个查询的共有行)l Minus(返回将第二个查询检索的行从第一个查询检索出的行中减去之后剩余的记录)select product_id,product_type_id,name from products union all select prd_id,pro_type_id,name from more_products; select product_id,product_type_id,name from products union select prd_id,pro_type_id,name from more_products; select product_id,product_type_id,name from products intersect select prd_id,pro_type_id,name from more_products; select product_id,product_type_id,name from products minus select prd_id,pro_type_id,name from more_products; 6.2、使用translate()函数。 Translate(x,from_string,to_string)函数在x中查找from_string中的字符，并将其替换为to_string()中对应的字符。select translate(huangwei,ha,ah) from dual; 6.3、使用case表达式。Case search_expressionWhen expression1 then result1When expression2 then result2When expressionn then resultElse default_resultEnd其中：l Search_expression是待求值的表达式。l Expression1、expression2、是要与serch_expression进行比较的表达式。l Result1,result2、每一个可能出现的表达式所对应的返回值。l Default_result无法找到匹配的表达式时的默认值 6.4、层次化查询。 5、数据库更新操作。 数据库的更新操作：l insert增加：insert into 表名称字段名称1，字段名称2，、 values(值1，值2，、)；（对与数字不加,字符串必须加）l update修改：update 表名称 set 要修改的字段=新值，要修改的字段新值，. where 修改条件l delete删除：delete from 表名称 where 删除条件7、 事务处理. 事务处理：所谓的事务处理就是保证数据库操作的完整性，所有的操作要么成功要么失败。 对Oracle中连接到的每一个事务的窗口（sqlplus、sqlplusw）连接后实际上与数据库建立一个session连接，即：每一个连接到数据库上的用户都连接了一个session； 一个session对数据库所做的修改，不会立即反应到数据库的真实数据之上，是允许回滚的，当一个session提交所有的操作之后，数据库才真正的修改。 在数据库中提供了两个主要的命令完成事物的处理：l 提交事务：commit;l 回滚事务：rollback; (在数据库提交之前回滚) 在oracle关于事务的处理上也有一种死锁的概念： 一个session如果更新了数据库中的数据，其他的session无法立即完成更新，要等待对方提交之后才能更新。8、创建和管理表。 8.1、常用数据类型。 NO数据类型描述1Varcharlength、varchar2length表示是一个字符串有长度限制，为2552NumberNumbern:表示是一个整数，数字的长度是n，可以使用intNumber(m,n):表示一个小数，数字长度为n，整数长度为m-n，可以使用float3Date日期要按照标准的日期格式进行存放4Clob大对象，表示文本，一般可以存放4g。5Blob大对象，表示二进制数据，最大可以是4g， 8.2、表的建立。 Create table 表名称( 字段名称1 字段类型 default 默认值, 字段名称1 字段类型 default 默认值,. 字段名称n 字段类型 default 默认值,) 复制表：Create table 表名称 as (子查询);l 如果写的是：select * from emp,表示表数据和表结构一起复制。l 如果写的是：select * from emp where 1=2;,加入了一个永远不可能成立的条件，则表示是只复制表结构，不复制表内容。 删除表：Drop tabel 表名称； 修改表：alert指令的语法如下：Alert table 表名称 add(列名称 列的类型 default 默认值,.); 修改表结构：Alert table 表名称 modify(列的名称 列的类型 default 默认值); 为表重命名：(此语句只能在oracle中使用) Rename 旧的表名称 to 新的表名称; 截断表:(在person表中的一条数据使用了delete进行删除，但是可以用rollback进行回滚，假设要清空一张表的数据，但同时又不能回滚，可以立即释放资源就需要使用截断表) Truncate table 表名; 8.3 、约束。 在实际开发中约束是必不可少的支持，使用约束可以更好的保证数据库中数据的完整性，在实际中约束主要分为五种。l 主键约束(primary key)。主键可以表示唯一的标识，本身不能为空。l 唯一约束。在一个表中只允许建立一个主键约束，但其他的不希望重复的就需要建立唯一约束。l 非空约束。不能为空。l 外键约束。在两张表中进行约束操作。l 检查约束。检查一个列的内容是否合法。 约束名称系统可以自动分配，也可以使用constraint指定约束名称. Drop table person;Create table person(pid int primary key, Name varchar2(200) unique, Age number(20) check(age between 10 and 20), Birthday date not null, Sex varchar2(2) default 男 check(sex in (男,女);Drop table person;Create table person( Pid int, Name varchar2(200), Age number(2), Birthday date, Sex varchar2(2) default 男, Constraint person_pid_pk primary key(pid), Constraint person_cage check(age between 10 and 20), Constraint person_csex check(sex in (男,女), Constraint person_name_uk unique(name) );create table book( bid number primary key, bname varchar(200) not null, pid int, constraint person_book_pid foreign key(pid) references person(pid) on delete cascade);On delete cascade 是级联删除。删除约束：alert table 表名称 drop constraint 约束名称，添加约束：alert table 表名称 add constraint 约束名称 约束类型(字段名) 8.9、ROWNUM. ROWNUM:表示行号，实际上是一个列，只不过是个伪列，此列可以再每张表中出现。 Select rownnum,empno from emp; 截取中间的：SELECT * FROM (SELECT ROWNUM M,EMPNO FROM EMP WHERE ROWNUM10