黄刚创意工作室·IT技术部
一．
验证Oracle已经安装完成了，首先得确认程序里有这些个选项，有四个选项：Oracle Installation Products、集成管理工具、配置和移植工具（管理员用得比较多）、应用程序开发。Oracle也可以形成一种层次性的链接（Directory Manager），对于我们来说用得最多是sqlplus，而OLE 是一种面向对象的技术，利用这种技术可开发可重复使用的软件组件（COM），他是微软提出的，在用VB开发时可能要用，对于我们来说不须管。 当启动sqlplus时会提示输入用户名和口令，所谓的sqlplus是Oracle的一个客户端。Oracle大多数命令都用命令来实现，所以比较难使，就如同unix比windows难使。口令你输入tiger，建议就用它，因为以后去企业里Oracle 9i就是用的这个口令。当你看到SQL>命令符时说明已经进入到了Oracle的命令行了。 图形版的sqlplus，命令行的sqlplus，还有一个就是通过网页访问Oracle的服务，启动sqlplus，端口号是5560，URL是http://127.0.0.1:5560/isqlplus。isqlplus非常有用，比如说你的机器上没有装上Oracle，就可以通过键入别人的IP，然后登陆到他的机器上来做实验，当然也可以安装特定的客户端软件实现。 说实在的Oracle的这个客户端太难看了，并且要记好多好多的命令，所以专门有一类公司专门为Oracle开发客户端。有一个比较著名的客户端叫Toad，当然Oracle还有一个客户端PL/SQL Develplor，这个用的也非常多。数据库管理时常用Toad（英文蛤蟆的意思），如果有声卡的话，你启动一次它就叫一次。对我们来说sqlplus足够使了。以后有机会我使用一下PL/SQL Develplor，不过这个也不好使，因为你同时必须装上Oracle，还不如isqlplus好呢。 以超级管理员身份链接数据库的方法是再命令行下敲入命令：sqlplus sys/口令 as sysdba。Sys和system的区别是sys是数据库管理者，system是数据库所有者，sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户，system用户拥有dba，sysdba等角色或系统权限，system如果正常登录，它其实就是一个普通的dba用户，但是如果以as sysdba登录，其结果实际上它是作为sys用户登录的，这一点类似Linux里面的sudo的感觉，从登录信息里面我们可以看出来。sys用户必须以as sysdba或as sysoper形式登录。然后输入命令alter user scott account unlock;它的意思是将Scott用户解锁，以后关于dba的用到一个学一个，没必要从头到尾的去学它。 二． SQL语言是在数据库下进行操作的语言，它本身是一个标准语言，有国际标准。有两套一个是SQL1992,SQL1999。大多数数据库既支持旧的标准也支持新的，很多也是用的旧的SQL语言写程序的，对于我们来说新旧都要学，而且对大多数数据库系统来说都是通用的，只不过不同数据库有一个轻微的改变，这就是最郁闷的地方，没办法到时候查手册或google一下。SQL全称为Structured Query Language，即机构化查询语言，它被定义为第四代语言。为什么叫第四代语言，因为我们知道第三代语言也得有if else for 等这些流程控制语句，也就是你得告诉它干什么且怎么干。第四大只用告诉它我要干什么，不用去控制它怎么干，这样的语言学起来当然十分easy。 我们共有四类语句要学：数据操作语言语句[Data manipulation language，DML]，会话控制语句[session control statement]，数据定义语言语句[Data definition language，DDL]以及事务控制语句[transaction control statement]。 查询语句只有一句话就是select语句，这是最重要的一条语句。以后只要遇到相应的select语句一律背过，企业再面试时其他语句一般不会怎么考但这条语句是必考的。如果考你SQL语句肯定会考这条select语句不是别的。你跟别人交流的时候，别说我写过几条dml
1
黄刚创意工作室·IT技术部
语句，而因详细的说到底是什么语句，比如insert语句、update语句等。选择的意思是从表里把数据拿出来，展现出来。听起来似乎挺简单的，但你要从表里拿数据得首先知道有几张表，每张表里有些什么内容。 第一步要做的就是熟悉一下实验用的数据，这些数据Oracle都给自带了，直接用就行了。首先第一条语句desc emp，描述一下emp这张表，会分别列出所有字段（表头），empno 雇员编号，ename 雇员名字，job 雇员工种，mgr 经理人，hiredate 雇员入职日期，sal 薪水，comm 津贴，奖金，deptno 所属部门编号。表还有一部分叫类型，也就是相对应字段的类型,这些都是Oracle已经定义好的，我们直接用。现在先用别人建立好的表，然后再自己建表。NUMBER指数字，VARCHAR指字符型，Oracle7以前在用，现在常用的是VARCHAR2它支持各种可变字符串，可以更好的表示各国文字，DATE指日期，NUMBER(7,2)指七位数字有两位小数。 desc dept 部门表，它有三个字段DEPTNO 部门编号，DNAME 部门名称，LOC部门所在位置。Desc salgrade工资等级表，许多公司都实行的是工资等级制，分几级工资，GRADE工资等级，HISAL该等级最高工资,LOSAL该等级最低工资。 以上简要的介绍了三张表，如果想更详细的了解里面的数据，教大家第一个select语句： seclect * from 表名；。*表示把表里所有的数据都取出来，分号表示语句结束。尽量多熟悉各张表之间的关系，以及表内部各字段之间的关系，越熟悉越好。 如果要算纯数据，可以采用dual这张表，它就一条记录，一个字段。你如果用其他表计算的话会有好多条重复结果，记录。 字段是不能有空格的，如果非要空格可以将整个字段用双引号括起来，这样的话引号里面就可以加上各种各样的字符了。如果没加引号的话那样做是不对的。再没有加双引号的时候结果字段名会以大写字母显示出来，但加了双引号就保持住了原来的形式不变。没有就是null值，即便是0也与空值大不一样。0是有值但为0，null是没有值。任何含有空值的表达式最后的结果都是空值。 SQL里面有一个字符串链接符||，它的作用跟Java里的+一样，所有被链接的都会转换为字符串显示出来。如select ename||sal from emp;它的意思是将结果sal转换为字符串后链接到ename后面。在任何数据库的SQL语句里面都是用单引号括起来表示一个字符串序列。如果字符串里本来就有单引号，再解析时就会出错。可以用两个单引号来代替一个单引号，这样就可以正确显示了。 关键字distinct是指选择不重复的记录，如果修饰多个字段时表示选择该多个字段的组合不重复的显示出来。where叫过滤条件，它会过滤掉一些记录，等值判断用=，不等值判断注意是<>而不是Java语句里的不等号。字符串比较是比较字符的ASCII码，挨个比较。做比较时还可以用关键字between and，注意的是包括边界值的。and也可把两个过滤条件连接起来。 下面来讲空值的处理，你可以直接用where +字段+is null来取出是空值的，取出不是空值的多加一个not就行。这种处理方式非常像我们的说话习惯。in然后跟着某个字段的取值是括号括起来各值之间用逗号隔开。可以使用and、or，not连接起各个查询条件。 日期处理起来稍微麻烦一些，由于现在还没有学日期函数，所以采用另外一种方式—一个特殊的字符串。你就按它的日期写法来写如‘20-2月-07’，然后系统就按照字符串来比较即可。年份可以写全如‘20-2月-2007’，它能认出来。以后我们可以写成任何形式的日期格式都能处理。 模糊查询是这样的，比如找出名字里含有al的，有点像正则表达式。使用关键字like，可以通配符%，代表0个或多个字符，_代表一个字母。如果名字里就含有一个%，则使用转义字符\\%的形式，当然可以自己指定转义字符方法是select * from emp where ename like
2
黄刚创意工作室·IT技术部
'_A$%%' escape '$';。 排序用到的一个关键字是order by，默认的是按升序排列，如果想降序排列可以采用如下形式：select * from dept order by deptno desc;desc是降序的英文缩写。升序排列可不写也可加上asc。还可以加入where先过滤了再排序，按照几个字段进行排序时，用逗号分隔开，如下：select ename ,job,empno,sal ,deptno from emp order by deptno,sal desc;意思是先将表按照deptno升序排列，然后相同的deptno内部再按照sal降序排列。 三． SQL函数，函数就是Java里的方法，有参数有返回值。现在讲的是单行函数，第一个叫做lower，作用是转换为小写；第二个叫upper，作用是转为大写；第三个叫substring，取子串。这几个函数都是对字符串进行操作的，传递进去的参数就是字段名。如select lower(ename) from emp;还有一个函数叫chr，它是把一个数字转为字符：select chr(86) from dual;将86代表的字符显示出来，与之对应的是将一个字符转为对应的ASCII码，叫ascii，如：select ascii(‘A’) from dual;就是将A的码值显示出来。 round函数是四舍五入的作用，如select round(43.535435) from dual;结果就为44。还有一个重载的带两个参数的方法如select round(43.535435,4) from dual;四舍五入到小数点后4位，结果位44.5354，第二个参数可以是负数表示四舍五入到小数点前多少位。 其他函数可以不用记住，但要知道有这么个函数，然后取手册中可以查到，但下面讲的一个函数最好记住它。to_char是将字段内容显示为字符，可以对显示的形式进行格式控制，比如select to_char(sal, '$99,999,999.9999') from emp;就是以后面给定字符形式显示出来，小数点后的四位会强制显示，而前面的没有的位数就不显示，这就是9的含义。$换为L表示本地货币。0代表一位数字，如果没有该位数字也会强制显示为0，对于这种方式是为了进行格式控制。 实际上用to_char来控制数字是比较少用的，而用得最多的是对日期格式的控制，我们知道Oracle里的sysdate包含了插入记录的详细时间信息，我们可以对其进行显示的格式进行控制，比如：select to_char(hiredate,'yyyy-mm-dd') from emp;转换为年月日2006-3-23这种格式的。实际上对日期的格式控制是利用Java来实现的，到时会有一个关于日期处理的专题。 select ename,job,hiredate from emp where hiredate >to_date('1982-1-1 1:30:00', 'yyyy-mm-dd hh:mi:ss');意思是将特定的字符串转换为相应的日期格式。还有to_number是将特定的字符串解析为相应的数字格式如select ename ,job ,sal from emp where sal 3 黄刚创意工作室·IT技术部 select列表中的字段要么出现再group by 里要么出现在组函数里面，否则语法出错，主要由于可能不唯一匹配导致。 使用having对分组进行限制，这也是来自一个实际中的需求，比方说按部门编号进行分组，分组之后每个部门的平均薪水，要你把平均薪水大于2000的组给取出来。where语句是对单条记录进行过滤，这个地方无法用，实际在执行的时候首先执行where语句。select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;首先按deptno求出每个deptno的平均sal，然后对这些分组利用having 语句进行过滤。 到现在为止已经学完了所有的单条select语句，执行顺序按照select+字段或内容+from+表名+where…+group by…+having…+order by…这个顺序进行执行，首先取出记录，然后对单个记录进行过滤，过滤完了分组，分组好了对组过滤，剩下的分组再按顺序显示出来。单条select语句一定要非常熟悉，如果这都要查书的话对企业就没法交代了，哈哈！ 之前已经讲过一个子查询的例子：select ename,sal from emp where sal=(select max(sal) from emp);看谁的工资最高，之所以叫子查询因为再一个select语句里套了另外一个select语句。理解子查询的关键是把它当成一张表，可以对它做表连接最后就可以求出想要的结果来。单独的select语句相当简单，但是有了select子句和表连接之后语句就稍微复杂了点。select ename ,sal from emp join(select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal=t.max_sal and t.deptno=emp.deptno);这个语句的作用是求出每个部门薪水最高的员工记录。 select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;对于在同一张表中进行查询时需要进行自连接，为表取别名便于写出条件和比较。这条语句的意思是找出每个员工的经理人。为同一张表起不同的别名然后当成两张表来用。 在SQL99里面链接条件和过滤条件是分开的，即链接条件不能出现在where语句里面，利用Join+表名+on+链接条件进行。比如：select ename ,job,dname from emp join dept on (emp.deptno=dept.deptno);等值链接的时候有一个简便的写法using(deptno)代替on (emp.deptno=dept.deptno)但不推荐使用，因它的限制条件很多，可能报出来的错误很难找并且看起来也不是很清楚。 连接有各种各样的形式，下面我们讲外连接，其实用起来特别方便。它会把左边那张表不能和右边链接的数据也拿出来。select t1.ename , t2.ename mgr , t1.deptno from emp t1 left outer join emp t2 on (t1.mgr=t2.empno)；outer可以省略掉效果一样。如果想把右边多余的拿出来Join前加上right即可；如果两边多余的都想拿出来Join前加上full叫全外连接。 讲一道面试题，不准用组函数求出薪水的最高值。方法是用自连接，连接条件采用非等值连接<,这样表中的最大值就会连接不上，然后再从emp表中找出未连接上的最大值，如下：select sal from emp where sal not in (select t1.sal from emp t1 join emp t2 on (t1.sal join (select deptno,avg(sal) avg_sal from emp group by deptno) t on (t.deptno=dept.deptno) where avg_sal=(select max(avg(sal)) from emp group by deptno);这里还用到了组函数的嵌套使得程序简洁了，但要注意最多只能两层嵌套，第一层可能有多个输出（分组时），第二层一定只能有一个输出，接下来就不能再嵌套了，一个输入不能作为组函数的参数。 求平均薪水等级最低的部门的部门名称，编号，平均薪水： select dname,deptno ,grade ,avg_sal from salgrade 4 黄刚创意工作室·IT技术部 join ( select dname,t.deptno,avg_sal from dept join (select deptno,avg(sal) avg_sal from emp group by deptno) t on (t.deptno=dept.deptno) ) on (avg_sal between salgrade.losal and salgrade.hisal) where grade=(select min(grade) from (select grade from salgrade join (select deptno,avg(sal) avg_sal from emp group by deptno) on (avg_sal between salgrade.losal and salgrade.hisal) ) ) ; 如果想要scott用户具有创建视图或表的权限，需要以sys用户以sysdba的身份登录然后键入命令grant create table,create view to scott;然后以scott登录就可以了。创建视图时，名字要以v$开头，视图可以看做一个子查询或一张表，实际上是张虚表，取记录还是从表中取。创建视图的一个好处就是便于取数据，写起来方便。删除视图的方法是drop view+视图名。 求比普通员工最高薪水值还要高的经理人名称，主要是先求出普通员工的最高薪水，方法是员工的编号不在mgr字段，同时要主要去掉mgr为空值的： select ename,empno from emp where sal>(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)) and empno in (select distinct mgr from emp where mgr is not null ) ; 再讲一道面试题：比较这两个语句的执行效率 Select * from emp where deptno=10 and ename like ‘%A%’; Select * from emp where ename like ‘%A%’ and deptno=10; 理论上说应该是第一个，就如同&&和&的区别一样它会过滤掉很多条件，后面的字符串就不用再取去比较了，但实际中不好说因为有可能Oracle在执行的时候做了优化，自动将数字比较放前面了。 四． 现在已经讲完了select语句，接下来讲dml语句，常见的有三条，他们和select语句合称为数据库的四大语句（select/insert/update/delete）。增删改查！ 接下来简单介绍一下Oracle的逻辑结构。安装的时候创建了一个全局数据库，同时为这个数据库建立了一系列管理它的进程SID。一个大数据库里面又区分为不同的表空间，说白了就是装表的地儿。我们现在的表是放在不同的表空间里面，比方说users表空间，在里面放着某个用户所有的表，当该用户登录进去后其实是去访问自己的表空间里的相对应的表。可以将某个表空间里的内容全部copy出去创建一个新的user，然后该user就操作专属于自己的表空间里的表了，所以说Oracle是支持多用户的。 将某个表空间里的内容全部copy出去创建一个新的user做法是这样的：1.以超级管理员登录数据库conn sys/oracle as sysdba; 2.从命令行在起一个client，先建立一个目 5
(O管理)O自学自学笔记
