Oracle学习笔记（chstone原创）

声明：最近再学习Oracle，《Oracle学习笔记》由chstone根据自己的学习情况整理，如需转载，请注明原作者chstone（西兰卡普）以及原始出处http://www.hack286.com/。谢谢大家的支持！如果发现文中错误或者有疑问，请和作者chstone交流，欢迎指教，共同学习。联系方式QQ：19122116   e_mail:[email protected]学习笔记之一
2007年1月9日1.1 Oracle公司发展历史
SDL（Software Development Laboratory，软件开发实验室）是Oracle公司曾经使用过的一个名字。
用户名Scott是开发者Bruce Scott的名字，口令Tiger则是他的猫。
1977年，Larry Ellison、Bob Miner和Ed Oates建立了SDL。
Oracle的含义是“神喻”，预言或者作出这种预言的人。
1978年，Oracle 1.0使用汇编语言，这个版本从未发行。这一年，公司的名字由Software Development Laboratory改成Relational Software Inc.（RSI）。
1980年，Relational Software公司正是改名为Oracle Systems Corporation（后来变为Oracle Corporation）。
Oracle 3采用C语言编写。
Read Consistency读一致性。
1985年，Oracle进入金融应用行业。
CASE（Computer-assisted software engineering计算机辅助软件设计）。
Oracle 8i采用Java编写。1.2 目前提供的产品
Oracle Corporation提供信息管理用的软件。其中包括：数据库管理、应用开发、商务智能以及基于Internet的商务应用。
作为一套产品，Oracle可以分为5个领域：
1、Oracle9i数据库。
2、Oracle9i Application Server。
3、Internet Development Suite。
4、Date Warehousing和Business Development。
5、Oracle E-Business Suite。1.3 术语
DBMS代表数据库管理系统。可以把它当成数据库文件的文件管理器。现在最常用的数据库管理系统是RDBMS。RDBMS代表关系数据库管理系统。
TAR（Technical Assistance Request）技术援助需求。1.4 在Oracle服务器端配置监听器
手动配置服务器端监听器：监听器配置包括监听协议、地址以及其他相关信息的参数。监听器配置存储在一个名称为listener.ora的配置文件中，该文件位于ORACLE_HOME\network\admin目录下。（ORACLE_HOME为Oracle软件的安装目录，后同。）
Listener.ora配置文件中的内容如下（可以使用EditPlus等文档编辑工具打开）：
# LISTENER.ORA Network Configuration File: e:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.1)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLXXXtProc)
      (ORACLE_HOME = e:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = chstone)
      (ORACLE_HOME = e:\oracle\ora92)
      (SID_NAME = chstone)
    )
  )
我们主要修改的是“LISTENER”中的“ADDRESS”这一项，其中“PROTOCOL”是协议类型，一般都为TCP。“HOST”是主机地址，如果本机配置，可以使用127.0.0.1，但如果在局域网中或者远程终端连结，就需要配置本机的IP地址了，如上就配置了两个IP，本机的127.0.0.1和局域网中的192.168.5.1。“PORT”是使用端口号，默认为1521。

解决方案 »

  1.   

    1.5 客户端服务名配置
    客户端服务名与远程或本地的监听器建立连接，客户用它向服务器端发出连接请求。
    安装Oracle时，用户必须指定一个全局数据库名称，即一个SID名称。Oracle用指定的SID名称在服务器端自动创建一个服务名。在客户端创建服务名时，需要指定网络协议、与协议相关的信息和数据库的SID名称。对于最常用的TCP/IP协议来说，需要指定服务器主机名或IP地址、监听器端口和数据库SID。这些配置信息都存储在tnsnames.ora文件中，该文件同listener.ora保存位置相同，都位于ORACLE_HOME\network\admin目录下。另外也可以使用开始—程序—Oracle-OraHome92—Configuration and Migration Tools—Net Configuration Assistant的向导界面进行配置。这里主要看手动配置过程。
    Tnsname.ora文件中主要需要配置的文件如下：
    TESTSERVICE =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.1)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = chstone)
        )
      )
    我们一项项来看，首先是“TESTSERVICE”，这就是在客户端配置的服务名，名字可以任意取，但是一定要记住，前面不可以有空格。“(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.1)(PORT = 1521))”这一段中，“PROTOCOL”代表协议类型，此处为TCP；“HOST”是服务器端的IP地址或者主机名；“PROT”是端口号。下面的“SERVICE_NAME”代表服务器端的服务名。因为我安装的时候指定的SID名称为“chstone”，所以在这里Oracle用指定的SID名称在服务器端自动创建一个默认服务名“chstone”。当在使用SQL*Plus登陆连接服务器端时，主机字符串中就填刚才配置好的客户端Net服务名“TESTSERVICE”，如果是将本机作为服务器，则主机字符串可以不填。1.6 使用SQL*Plus查询工具连接Oracle数据库
    在连接数据库服务器之前，先要在Windows的控制面板――管理工具――服务中开启两个和Oracle有关的服务，即：OracleOraHome92TNSListener（监听器服务）和OracleServiceCHSTONE（实例服务），如果本机作服务器，并且不接受远程客户端登陆的话，可以只开后面一个实例服务就行了。
    建立连接：开始—程序—Oracle-OraHome92—Application Development—SQL Plus，出现登录对话框，输入用户名和口令，主机字符串就是前面配置的Net服务名，如果为空，表示连接本机上默认数据库。
    使用开始—运行建立连接的方法：
    直接输入“sqlplus”进入状态下的SQL*Plus，输入“sqlplusw”进入Windos状态下的SQL*Plus；可直接在后面输入用户名密码登陆进去，例如“sqlplusw  scott/tiger”；如果要登陆远程服务器，则在后面加上主机字符串，主机字符串前面加@与密码分隔，例如：要登陆远程服务器，用我们上面配置的Net服务名，就是：“sqlplusw scott/tiger@testservice”。
    $符号用来在SQL*Plus下执行dos命令，比如启动和关闭服务：
    $ net start OracleServiceCHSTONE（启动服务）
    $ net stop OracleServiceCHSTONE（关闭服务）1.7 SQL*Plus中一些常用的命令及使用技巧
    SQL> show user;显示当前用户（在SQL*Plus工具中专用的，更好的方式是使用SQL> select user from dual;语句进行查看当前用户，其中dual表是每个SQL*Plus用户都可以使用的Oracle表，这里真正重要的是要知道它只含有一行数据。）；
    SQL> set pagelize 20;设置每页显示行数为20；
    SQL> set linesize 100;设置每行显示的字符数为100；（如果嫌每次设置麻烦，可以将这两条语句保存在C:\oracle\ora92\sqlplus\admin\glogin.sql中，每次启动是Oracle会自动运行glogin.sql文件，相当于初始化）
    SQL> show all;查看当前的环境设置；
    SQL> spool file_name;将显示的内容输出到指定文件，例如spool c:\spool.txt;
    SQL> spool off;关闭spool输出，只有关闭spool输出，才会在输出文件中看到输出的内容；
    SQL> /;重新运行上一次命令；
    SQL> set timing on/off;显示、关闭每个sql语句花费的执行时间；
    SQL> select * from tab;查看当前用户下所有表；
    SQL> start file_name;或SQL> @ file_name;执行一个SQL脚本文件，我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理；
    SQL> desc table_name;显示表的结构；
    SQL*Plus中的快速复制和粘贴技巧(摘自www.cnoug.org)：
    1) 鼠标移至想要复制内容的开始 
    2) 用右手食指按下鼠标左键 
    3) 向想要复制内容的另一角拖动鼠标，与Word中选取内容的方法一样 
    4) 内容选取完毕后（所选内容全部反显），鼠标左键按住不动，用右手中指按鼠标右键
    5) 这时，所选内容会自动复制到SQL*Plus环境的最后一行1.8超级用户密码忘记的处理方式
    1、在开始运行中输入sqlplusw /nolog登陆，即不启动服务登陆。
    2、利用$ net start OracleServiceCHSTONE和$ net start OracleOraHome92TNSListener启动两个服务。
    3、以超级用户身份连接数据库 SQL> connect / as sysdba;
    4、修改system的密码：SQL> alter user system identified by “password”。
      

  2.   

    Oracle学习笔记之二
    2007年1月12日2.1 创建新用户并授权
    SQL>Create user username
         Identified by “password”
         Default tablespace users
    Temporary tablespace temp
         Account lock;
    以上语句的意思是创建一个名为“username”的用户，密码为“password”，默认表空间为“users”，临时表空间为“temp”，“Account lock”意思在创建用户时锁定用户。并且当用户被锁定时不能重复锁定。
    Grant命令用于为用户分配权限或角色：
    SQL>Grant connect to username; 允许用户连接数据库并在数据库中创建表或其他对象；
    SQL>Grant resource to username;允许用户使用数据库中的空间；
    SQL>Grant create sequence to username;允许用户创建序列。
    SQL>Grant select on emp to username;用户scott允许用户username查看emp表中的记录；
    SQL>Grant update on emp to username;用户scott允许用户username更新emp表中的记录；
    SQL>Grant all on emp to username;用户scott授予用户username对emp表的各种权限。
    SQL>Revoke select,update on emp from username;收回用户username在emp表中的查看、更新权限；
    SQL>Alter user username identified by “password”;修改用户口令；
    SQL>Alter user username account unlock;为用户解锁；
    SQL>Drop user username cascade;删除用户，当用户拥有模式对象时必须使用cascade选项删除模式对象。2.2 SQL常用的命令分类及例子
    数据定义语言：create（创建）、alter（更改）和drop（删除）命令。
    数据操纵语言：insert（插入）、select（选择）、delete（删除）和update（更新）命令。
    事务控制语言：commit（提交）、savepoint（保存点）和rollback（回滚）命令。
    数据控制语言：grant（授予）和revoke（回收）。
    数据定义语言举例：
    SQL> create tablespace mySpa datafile ‘c:\mySpa.dbf’ size 100M;创建大小为100M、名为mySpa的表空间，文件位于C盘根目录下；
    SQL> create table myTab(no number(4),name varchar2(20));创建一个名为myTab的表，包含两列no和name；
    SQL> alter table myTab modify (name varchar2(25));修改myTab中的name列，使此列能容纳25个字符；
    SQL> alter table myTab add (tel_no varchar2(20));给表myTab增加一列tel_no；
    SQL> alter table myTab drop column tel_no;删除表myTab的tel_no列;
    SQL> drop table myTab;删除表myTab；
    SQL> truncate table myTab;删除表myTab中的所有行（截断表）。不可以回滚。
    数据操纵语言举例：
    SQL> insert into myTab values(‘001’,’John’);向表myTab中插入一行数据；
    SQL> select distinct sal “薪水” from emp where sal>1500 order by sal desc;选择表中sal大于1500的数据，以别名“薪水”显示并按照sal的降序进行排列输出；
    SQL> create table empa as select empno,ename,job,sal from emp;从emp表中选择“empno,ename,job,sal”四列的数据建立新表empa;
    SQL> create table empa as select * from emp where 1=2;使用一个假条件根据现有表emp创建一个只包含结构的空表empa；
    SQL> delete from empa where sal<1500;删除表empa中sal小于1500的行；
    SQL> update empa set sal=1500 where sal<1500;更新，将表empa中sal小于1500的行的sal值全部改为1500。
    事务控制语言举例：
    SQL> commit;用于提交并结束事务处理；
    SQL> savepoint 1;保存点类似于标记，用来标记事务中可以应用回滚的点；
    SQL> rollback to savepoint 1;回滚到保存点1。2.2 Oracle中的伪列
    伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值。常用的伪列：rowid和rownum。
    Rowid：数据库中的每一行都有一个行地址，Rowid伪列返回该行地址。可以使用Rowid值来定位表中的一行。通常情况下，Rowid值可以唯一地标识数据库中的一行。
    Rowid伪列有以下重要用途：
    1）能以最快的方式访问表中的一行；
    2）能显示表的行是如何存储的。
    3）可以作为表中行的唯一标识。
    如：SQL> select rowid,ename from emp;
    Rownum：对于一个查询返回的每一行，Rownum伪列返回一个数值代表的次序。返回的第一行的Rownum值为1，第二行的Rownum值为2，依此类推。通过使用Rownum伪列，用户可以限制查询返回的行数。
    如：SQL>select * from emp where rownum<11; 从EMP表中提取10条记录。
      

  3.   

    Oracle学习笔记之三
    2007年1月15日3.1 SQL*Plus中的数学函数
    数学函数          示例       结果 说明
    Abs(value) Abs(-70)       70          返回value的绝对值，结果恒为正
    Ceil(value) Ceil(34.09)    35          返回大于或等于value的最接近的整数
    Cos(value) Cos(180)       0.5984601 返回value的余弦值
    Floor(value) Floor(34.09)   34          返回等于或小于value的最大的整数
    Mod(value,divisor) Mod(100,6)     4          返回value除divisor的余数。如果divisor等于0，则返回value
    Power(value,exponent)Power(5,2)   25          返回vaule的exponent次幂
    Round(value,precision)Round(4.789,2) 4.79 结果近似到小数点右侧的precision位
    Sign(value) Sign(-309.1) -1 返回一个数值，指出value是正还是负。>0返回1，<0返回－1，＝0返回0
    Sqrt(value) Sqrt(25) 5 返回value的平方根
    Trunc(value,precision) Trunk(4.789,2)     4.78      返回舍入到指定的precision位的value值。如果precision为正，就截取到小数点右侧的这个数值处。如果指定的precision为负，就截取到小数点左侧的该数值处。如果没有指定precision，就假定为0，截取到小数点处                           Trunk(456.789,-1)   450
                               Trunk(456.789,-2)   400
    3.2 SQL*Plus中的字符串函数
    字符串函数 示例                   显示结果 返回值
    Length(value) Length(‘massachusetts’) 13 Length函数返回value的长度。Value可以是字符串、数字或者表达式
    Lower(string) Lower(‘BOSTON’)   boston Lower函数把给定的字符串string中的字符变成小写
    Upper(string) Upper(‘boston’)   BOSTON Upper函数将string的字符改为大写
    Lpad(string,
    Length
    [,padding]) Lpad(‘Total:’,10,’*’) ****Total: Lpad函数在string左侧填充padding指定的字符串，直到string达到了给定的length长度。如果未指定padding，相应的空间使用默认值
    Rpad(string,
    length[,padding]) Rpad(‘Total:’,10,’*’) Total:**** 作用和Lpad相同，只是在右侧填充
    Ltrim(string
    [,trimming_value]) Ltrim(‘mississippi’,’mis’) ppi Ltrim函数从左侧修剪string。它从字符串左侧删除trimming_value中出现的任何字符，直到出现trimming_value中没有的字符为止。如果没有指定trimming_value，就使用默认空间
    Rtrim(string
    [,trimming_value]) Rtrim(‘mississippi’,’ip’) mississ 和ltrim功能相同，只是从右侧修剪。这对于通过删除不想要的空间以清理数据时很有用
    Initcap(string) Initcap(‘mr Corey’) Mr Corey Initcap   函数将每个字符串的首字母大写
    Instr(string,value
    [,start[,occurrence]]) Instr(‘mississippi’,’is’,1,2) 5 Instr函数返回value在string中的位置。如果指定start位置，instr就从那开始。如果指定了occurance，instr返回value出现occurance次的位置。注意：日期和数字可以代替字符串
    Replace(string,if,then) Replace(‘dogcatdogcat’,’dog’,’cat’)  catcatcatcat Replace函数在string中查if，并用then替换
    Soundex(string) Shoudex(‘mike’) M200 该函数语法与其他的字符串函数不同，它用来查找与string发音相似的单词。返回结果的首字母要与string的首字母相同
    Substr(string,start[,count]) Substr(‘candlestick’,7) stick Substr函数从string中提取从start指定的位置开始的count个字符。如果未指定count，从start开始的所有字符都要被提取
      

  4.   

    3.3 SQL*Plus中的日期函数
    Add_months(date,number)  add_months函数返回给指定的日期加上指定的月数后的日期值。例：select sysdate as today,add_months(sysdate,1) as next_nonth,add_month
    (sysdate,-1) as last_month from dual;
    Last_day(date)  last_day函数返回指定日期所在月的最后一天。例：select last_day(sysdate) as last_day from dual;
    Months_between(date1,date2)  month_between函数返回date1减去date2得到的月数。通常，date1是将来的日期。所得结果通常是个小数。例：select months_between
    (’01-5月-2007’,sysdate) from dual;
    New_time(current_date,current_zone,future_zone)  next_time函数根据current_date和current_zone，返回在future_zone中的日期。current_zone和future_zone是代表时区的三个字母简写。其中一些常用的值如下：
    AST/ADT  Atlantic标准/白昼时间
    BST/BDT  Bering标准/白昼时间
    CST/CDT  Central标准/白昼时间
    EST/EDT   Eastern标准/白昼时间
    GMT      格林威治标准时间
    MST/MDT  Mountain标准/白昼时间
    NST       Newfoundland标准时间
    PST/PDT    Pacific标准/白昼时间
    YST/YDT   Yukon标准/白昼时间
    HST/HDT   Alaska-Hawaii标准/白昼时间
    Next_day(date,’day’)  next_day函数给出date后的day所在的日期，day是全拼出来的星期名称。例：select next_day(sysdate,’星期二’) from dual;
    Round(date,’format’)  round函数把date四舍五入到由format指定的格式。例：select round(sysdate,’month’) from dual;
    To_char(date,’format’)  to_char函数接收date，并把它以给定的format形式作为字符类型的日期返回。多数情况下，用于把日期和字符段连接起来。例：select to_char
    (sysdate,’YYYY”年”fmMM”月”fmDD”日”’) from dual;
    To_date(string,’format’)  to_day函数将char或varchar2数据类型转换为日期数据类型，格式模型format指定字符的形式。例：select to_date(‘2005-12-06’,’yyyy-mm-dd’) from dual;
    Trunk(date,’format’)  trunc函数将指定日期截断为格式模型format指定的单位的日期，与round函数不同的是它只舍不入。例：select trunc(sysdate,’month’) from dual;
      

  5.   

    3.4 SQL*Plus中的decode语句
    SQL*Plus中有一个decode语句，它可以用来在SQL*Plus中实现if-then-else逻辑。其语法为：decode(colum_name,comparison1,action1,comparison2,action2,…else action);
    Decode语句把colum_name的内容与每一个comparison进行比较，如果结果相等，decode执行action动作。如果没有任何一项与comparison匹配，程序就执行else action动作。我们可以看一些例子：Select ename,job,decode(job,’ANALYST’,’分析员’,’CLERK’,’办事员’,’MANAGER’,’经理’,’PRESIDENT’,’主管’,’销售员’) as “工作” from emp;
    我们可以利用decode语句来进行表中的行列互换或者统计，例如在emp表中统计各部门的工作人员工作类别，可以按照下面的步骤来做（为了便于理解，我们将步骤进行分解）：
    1、select deptno,ename,job from emp;选择表中部门、姓名、工作三项；
    2、select deptno,ename,job,decode(job,’ANALYST’,1) as ANALYST from emp; 选择表中部门、姓名、工作三项，同时将ANALYST用数字1标识；
    3、select deptno,ename,job,decode(job,’ANALYST’,1) as ANALYST,
                           decode(job,’CLERK’,1) as CLERK,
    decode(job,’MANAGER’,1) as MANAGER,
    decode(job,’PRESIDENT’,1) as PRESIDENT,
    decode(job,’SALESMAN’,1) as SALESMAN 
                                               from emp order by deptno;重复步骤2的工作，将各种职业用数值1标识出来，并按照部门号进行排序。
    4、select deptno, sum(decode(job,’ANALYST’,1,0)) as ANALYST,
                   sum(decode(job,’CLERK’,1,0)) as CLERK,
    sum(decode(job,’MANAGER’,1,0)) as MANAGER,
    sum(decode(job,’PRESIDENT’,1,0)) as PRESIDENT,
    sum(decode(job,’SALESMAN’,1,0)) as SALESMAN 
                                      from emp group by deptno;
    最后一步，按照部门进行分组，并求每一组中相同工作种类的和，这样就统计出了每个部门各个不同工种的人员各有多少了。
    下面再看一个关于学生成绩统计的例子。
    我们先建一个表，并插入数据：
    Create table stuInfo(
      stuno number(8),
      curno number(4),
      score number(5,2));  表名stuInfo，包含3列：学生编号stuno，课程编号curno，分数score。然后我们随机插入一些数据。
    insert into stuInfo values (9801,1001,84);
    insert into stuInfo values (9801,1002,97);
    insert into stuInfo values (9801,1003,72);
    insert into stuInfo values (9802,1001,65);
    insert into stuInfo values (9802,1002,87);
    insert into stuInfo values (9802,1003,64);
    insert into stuInfo values (9803,1001,98);
    insert into stuInfo values (9803,1003,83);
    insert into stuInfo values (9804,1002,72);
    insert into stuInfo values (9804,1003,65);
    insert into stuInfo values (9805,1001,88);
    insert into stuInfo values (9805,1003,90);
    insert into stuInfo values (9806,1002,70);
    insert into stuInfo values (9807,1003,62);
    下面进行替换：
    select stuno,curno,
      decode(curno,1001,score) as 语文,
      decode(curno,1002,score) as 数学,
      decode(curno,1003,score) as 英语
    from stuInfo;
    然后分组统计：
    select stuno,
      sum(decode(curno,1001,score)) as 语文,
      sum(decode(curno,1002,score)) as 数学,
      sum(decode(curno,1003,score)) as 英语
    from stuInfo group by stuno;
      

  6.   

    我已经保存了。多谢chstone(吃素)