【Java EE 学习 27】【oracle学习第一天】

一、oracle 11g安装的注意事项

　　1.超级管理员密码设置要符合要求（特别是不能以数字打头），否则在创建数据库的时候会产生ora-00922错误以及ora-28000错误。

　　　　解决方法：http://kuangdaoyizhimei.blog.163.com/blog/static/22055721120157994441330/

　　2.oracle 11g能够兼容win7，但是不兼容xp;oracle 10g不兼容win7，兼容xp;xp既支持oracle 11g，也支持oracle 10g。

　　　　oracle 11g下载地址：http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

　　　　下载该软件需要有oracle账号。

　　3.怎样彻底卸载oracle 11g

　　　　http://jingyan.baidu.com/article/922554468d4e6b851648f4e3.html

　　4.命令行登陆sqlplus

　　　　（1）普通用户登陆

　　　　　　  可以直接使用命令sqlplus，然后根据提示输入用户名和密码；或者使用sqlplus 用户名/密码的格式登陆数据库。

　　　　（2）管理员用户登陆不能使用（1）中的方法登陆，需要使用as sysdba进行标识。

　　　　　　 sqlplus / as sysdba；默认使用sys账户进行登陆，不需要输入密码。

　　　　　　sqlplus 用户名/密码 as sysdba；使用一个指定的管理员账户和密码登陆数据库。

　　5.解锁用户和修改密码

　　　　（1）解锁用户（需要先使用管理员账号登陆数据库）

　　　　　　　alter user scott account unlock;

　　　　（2）修改密码

　　　　　　　alter user scott identified by 新密码

　　6.oracle 10g和oracle 11g官方文档

　　　　oracle 10g：http://www.oracle.com/pls/db102/homepage

　　　　oracle 11g：http://docs.oracle.com/cd/E11882_01/

二、常用命令行命令。

　　set linesize number;　　　　　　　　设置sqlplus行宽的最大值。

　　set pagesize ;　　　　　　　　　　　  设置sqlplus页面的最大行数。

　　spool 文件路径名 ;　　　　　　　　　   设置屏幕输出保存路径。

　　spool off　　　　　　　　　　　　　　 关闭屏幕输出保存路径，该命令将文字保存到文件。

　　edit　　　　　　　　　　　　　　　　   在文本文件中编辑上一条命令，关闭文件之后使用/执行该条命令。

　　host 　　　　　　　　　　　　　　　　 返回到当前操作系统命令行界面。

　　host cls　　　　　　　　　　　　　　  使用windows命令行清屏命令，Linux命令使用host clear。

　　save 文件路径名　　　　　　　　　　    将最近一次的查询命令保存到文件中。

　　start或者@ +文件路径名　　　　　　　 执行指定sql文件中的命令。

　　column 列名 format 格式字符串　　   设置指定列的列宽。

　　　　　　　　　　　　a20　　　　　　    设置字符串属性的列宽为20个字符。

　　　　　　　　　　　　9999　　　　　     设置数值属性的列宽为4个字符。

　　column 列名 heading 显示名称　　　 设置该列显示的列名。

三、常用查询语句

　　1.show user　　　　　　　　　　　　查看当前用户

　　2.select * from tab　　　　　　　　 查看当前用户中的所有表

　　3.desc 表名　　　　　　　　　　　　  查看某张表的所有字段极其属性

四、null值注意事项

　　1.包含null值的表达式都是null

　　　　举例：表达式sal*12+comm是年收入，但是如果comm为null的话，该表达式就成为了null。

　　　　　　　select empno,ename,sal*12 年薪,comm 奖金,sal*12+comm 年收入  from emp;

　　　　　　　运行结果：

　　　　　　　

　　　　　这里出现的问题就是如果当奖金为Null的时候，最终求出来的年收入就为null，显然不合适，因为就算没有年终奖金，平时工资还是有的。

　　　　　怎样解决该null值带来的问题？使用nvl函数。

　　　　　select empno,ename,sal*12 年薪,comm 奖金,sal*12+nvl(comm,0) 年收入 from emp;

　　　　　运行结果：

　　　　　

　　　　　nvl函数在这里的作用就是当comm为Null的时候，使用0代替之。

　　2.判断一个字段值是否为空的方法是使用is null，不能使用=null的方法来进行判断。

五、去除重复行和字符串连接函数

　　1.去除重复行的方法：使用distinct关键字，例：

　　　　

　　2.字符串连接函数：concat

　　　　

　　　　使用||符号能够达到相同的效果。

　　　　

 六、单行函数

　　文档查询位置：http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions002.htm#SQLRF51178

　　以下列举几个比较常用的单行函数。

　　1.字符串函数

　　（1）转大写函数upper，转小写函数lower，首字母转大写函数initcap

select upper('apple'),lower('APPLE'),initcap('apple') from dual

　　　　运行结果：

　　　　

　　（2）字符串截取函数substr（字符串下标从1开始），该函数有许多变型，可以查看oracle 11g文档查看详细用法、示例等。

　　　　oracle 11g官方文档中记载的例子：

　　　　

　　　　举例：

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL

　　　　运行结果：

　　　　

　　（3）求字符串长度的函数length与求字节数长度的函数lengthb，返回值是数值

select length('你好'),lengthb('你好') from dual

　　　　运行结果：

　　　　

　　（4）查找字符串函数instr，返回值为字符串位置，下标从1开始计算

select instr('hello','ll') from dual

　　运行结果：

　　

　　（5）字符串填充函数lpad与rpad

select lpad('你好',10,'#'),rpad('你好',10,'#')from dual

　　运行结果：

　　

　　返回结果是数值型的结果，代表被填充的字符长度。

　　其它变型见oracle 11g api。

　　（6）去掉前后指定的字符trim

　　该函数变型较多，果然还得看官方文档才行，汗~

　　

　　举例：

select trim('a' from 'abcdea') from dual

　　运行结果：

　　

　　可以使用LEADING关键字去除前面的字符；可以使用TRAILING去除后面的字符；也可以使用BOTH去除两端指定的字符。默认去除两端指定的字符。

　　限制：只能去除第一个字符，即截取集只能有一个字符。

　　（7）字符串替换函数replace

select replace('apple','p','*') from dual

　　运行结果：　

　　

　　2.数值函数

　　　　（1）四舍五入ROUND和截断TRUNC

select round(11.1),round(11.5),round(11.9),trunc(11.1),trunc(11.5),trunc(11.9) from dual

　　　　运行结果：

　　　　

　　3.日期函数

　　　　（1）查询当前系统日期函数：sysdate

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL

　　　　　运行结果：

　　　　　

　　　　　这里使用了to_char函数对日期进行了处理，使用select sysdate from dual是最简单的一种查询日期的方法。

　　　　（2）时间戳函数SYSTIMESTAMP

select systimestamp from dual

　　　　　运行结果：

　　　　　

　　　　　格式化日期方法：

select to_char(systimestamp,'DD-MM-YYYY  hh24:mi:ssxff') from dual

　　　　　运行结果：

　　　　　

　　（3）时间戳函数的使用：昨天、今天和明天

select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual

　　运行结果：

　　

　　也就是说允许日期和数字进行计算，如果是加上一个整数表示后X天，反之就是前X天。但是不允许日期和日期之间进行计算。

　　（4）lastday函数：计算某日期所在月份的最后一天。

select sysdate,last_day(sysdate) from dual

　　运行结果：

　　

　　（5）add_months：计算一个日期之后的几个月之后的日期

　　　计算十二个月之后的日期：

select add_months(sysdate,12) from dual

　　　运行结果：

　　　

　　（6）从今天开始算起，下个星期几的日期。

select next_day(sysdate,'星期一') from dual

　　运行结果：

　　

　　（7）日期对象和字符串之间的显隐式转换。

　　使用to_char将日期对象转换为格式化的字符串。

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL

select to_char(systimestamp,'DD-MM-YYYY  hh24:mi:ssxff') from dual

　　也可以将一个数值转化为字符串。

select to_char(sal,'L9,999.99') from emp

　　运行结果：

　　

4.通用函数

　　（1）COALESEC函数：得到第一非空列的值。

select comm,sal,COALESCE(comm,sal) from emp

　　运行结果：

　　

　　（2）NVL函数和NVL2函数：使用指定的字符串替换掉NULL的字段值。

select ename "姓名",nvl(to_char(comm),'无奖金') "奖金" from emp

　　　运行结果：

　　　　

　　NVL2函数是NVL的增强版，它不仅仅能够当字段值为NULL的时候返回特定的字符串，还能够控制当字段值不为NULL的时候的返回值。

　　NVL2(exp1,exp2,exp3),当exp1不为NULL，返回exp2；当exp1为NULL，返回exp3。

　　（3）NULLIF函数：判断两个对象是否相同，如果相同返回NULL，如果不相同返回第一个数。

　　用法：NULLIF(exp1,exp2)

　　5.case......when......then.....end与decode：可以相互替换使用的两种语法

　　　　（1）case......when.....：SQL99标准语法

　　　　　　用于分支结构的判断。

　　　　举例：针对不同级别的职务进行涨薪。普通职员clerk涨薪200元，管理员manage涨薪800元。

select ename,job,sal "涨前薪水" ,case job when 'CLERK' then sal+200
                        when 'MANAGER' then sal+800
                    end "涨后薪水" from emp where job in('CLERK','MANAGER')

　　　　运行效果：

　　

　　　　（2）decode：oracle定义的语法

　　decode的用法相对于（1）来说更加简单灵活，在oracle中推荐使用这种方式进行书写。

select ename,job,sal "涨前薪水" ,decode(job, 'CLERK' ,sal+200,
                 'MANAGER' ,sal+800)
                     "涨后薪水" from emp where job in('CLERK','MANAGER')

　　运行结果是完全相同的：

　　

七、组函数

　　1.sum函数：求和函数

　　2.count函数：求数量的函数，会自动虑空。

select count(*) "总人数" ,count(sal) "发工资的人数",count(comm) "有奖金的人数" from emp

　　运行结果：

　　

　　说明了count函数会自动过滤NULL值的字段，同时也说明了所有人都有工资，但是并不是所有人都有奖金。

　　如何关闭自动虑空？使用NVL函数即可，但是在这里并不需要这样做。

　　3.AVG函数：求平均数的函数

　　　　求平均奖金：

select sum(comm)/count(*) "1" ,avg(comm) "2" from emp

　　　　运行结果：

　　　　

　　　　也就是说AVG函数也会自动虑空，在这里AVG函数不应当有自动虑空的功能，怎样屏蔽自动虑空功能？

　　　　在AVG函数中嵌套滤空函数NVL：

select sum(comm)/count(*) "1" ,avg(nvl(comm,0)) "2" from emp

　　　　运行结果：

　　　　

八、查询

　　1.分组查询：Oracle中所有的分组查询中涉及到的查询列必须在group by字句中出现，否则会报错，如：

　　　　

　　2.SQL优化案例：

　　　　求10号部门的平均工资：

　　　　（1）使用having进行分组过滤。

select avg(sal) from emp group by deptno having deptno=10

　　　　　　运行结果：

　　　　　　

　　　　（2）使用where进行分组过滤。

select avg(sal) from emp where deptno=10 group by deptno

　　　　　　运行结果：

　　　　　　

　　　　（3）SQL优化的原则：尽量使用where，尽量少用having;

　　　　（4）如果条件中含有分组函数，则必须使用having，where语句中不允许出现分组函数。

九、sqlplus的报表功能

　　1.rollup函数

select deptno,job,sum(sal) from emp group by rollup(deptno,job)

　　运行结果：

　　

　　

select deptno,job,sum(sal) from emp group by rollup(deptno,job)

等价于

select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,to_char(null),sum(sal) from emp group by deptno
union
select to_number(null),to_char(null),sum(sal) from emp;

　　2.格式化方法：使用break on skip 语句。

break on deptno skip 2

　　运行结果：无，但是影响了1中执行的查询结果。

　　再次运行1中的查询语句。

　　结果：

　　

　　3.停止格式化的方法：

break on null

十、多表查询

　　1.等值连接查询

　　　　查询所有用户的姓名及其所在部门的部门名称：

　　　　使用SQL99标准：

select ename,dname from emp,dept where emp.deptno=dept.deptno

　　　　使用SQL01标准：

select ename,dname from emp inner join dept on emp.deptno=dept.deptno 

　　　　执行结果相同：

　　　　

　　但是推荐01标准的写法，这种写法效率更高。

　　2.不等值连接查询

　　查询所有员工的工资级别。

select ename,sal,grade from emp,salgrade where sal between losal and hisal

　　运行结果：

　　

　　3.外连接

　　　　（1）案例：查询每个部门的部门号、部门名称、每个部门的人数

　　　　写法1:

select dept.deptno,dept.dname,count(emp.empno) from emp,dept where emp.deptno=dept.deptno group by (dept.deptno,dept.dname)

　　　　运行结果：

　　　　

　　　　这样写真的没有问题吗？

　　　　疑问：

　　　　

　　　　也就是说没有40号的员工，所以不将40号的员工信息显示出来，但是这是不对的。应当显示出来并且显示数量为0.

　　　　写法2：

select dept.deptno,dept.dname,count(emp.empno) from emp,dept where dept.deptno=emp.deptno(+) group by (dept.deptno,dept.dname) order by dept.deptno

　　　　运行结果：

　　　　

　　　　（2）外连接解决的问题：当条件不成立时，任然希望在结果中包含不成立的记录

　　　　左外连接: where d.deptno=e.deptno 当不成立时，等号左边代表的表的信息任然被包含，写法: where d.deptno=e.deptno(+)

　　　　右外连接: where d.deptno=e.deptno 当不成立时，等号右边代表的表的信息任然被包含，写法：where d.deptno(+)=e.deptno

　　　　可以看得出来外连接的符号写法和表示的意思相反，符号（+）放在那里表示另一侧需要被包含。

　　4.自连接查询

　　　　（1）原理：利用表的别名，将同一张表视为多张表。

　　　　（2）自连接不适合大表操作。

　　　　（3）查询每一个员工表中的成员老板的名字。

　　　　　　方法1：普通自连接查询

select emp1.ename||' 的老板是 '||emp2.ename from emp emp1,emp emp2 where emp1.mgr=emp2.empno

　　　　　　运行结果：

　　　　　　

　　　　　　出现的问题：如果使用select * from emp;SQL语句查询所有员工信息，则可以发现有一个员工没有老板信息，但是使用上述SQL语句并没有对该现象加以描述。

　　　　　　方法二：使用自连接+外连接的方式

select emp1.ename||' 的老板是 '||emp2.ename from emp emp1,emp emp2 where emp1.mgr=emp2.empno(+)

　　　　　　运行结果：　　

　　　　　　

　　　　　　使用该种方法解决了方法1中的问题，但是没有办法避开使用自连接的固有缺点：不适合操作大表。

　　　　　　方法三：使用层次查询。

　　5.层次查询

　　　　1.使用层次查询的目的：解决自连接不适合操作大表的固有缺陷。

　　　　2.层次查询的原理：对同一张表的前后两次操作并进行连接。

　　　　3.使用条件：当一张表满足可以形成一个树状结构的时候，就能够使用层次查询解决自连接的缺陷问题。

　　　　4.特殊之处：拥有伪列level，这是使用层次查询自动加上去的一列，代表树的深度。

　　　　5.使用层次查询的关键语法：

select level,empno,mgr from emp connect by prior empno=mgr start with mgr is null

　　　　运行结果：

　　　　

　　语法解析：

　　　　connect by prior empno=mgr：前一个节点的员工号empno等于后一个节点的mgr，按照此规律进行连接形成树。

　　　　start with mgr is null：mgr是一个表达式，表示从按照满足该表达式的节点开始形成树结构，这里mgr is null表示没有前一个节点的emp元素，也就是根元素，指的是“总老板”，也可以从任意一个元素开始形成，比如empno=7782

select level,empno,mgr from emp connect by prior empno=mgr start with empno=7782

　　　　运行结果：

　　　　

　　　　6.使用层次查询替代之前的自连接。

select empno,ename||'的老板是'||mgr newcolumn from emp connect by prior empno=mgr 

start with mgr is null

　　　　运行结果：

　　　　

十一、过滤和排序

　　1.怎样将空值放到最后面：使用nulls last命令

　　　　比较ASC和DESC的排序结果：

　　　　（1）ASC

select * from emp order by comm asc

　　　　结果：

　　　　

　　（2）使用DESC命令

select * from emp order by comm desc

　　　　结果：

　　　　

　　（3）使用DESC的时候怎样将空值放在后面：使用NULLS LAST命令。

select * from emp order by comm desc NULLS LAST

　　　　结果：

　　　　

　　2.SQL优化注意事项

　　　　（1）SQL语句解析的方向是从右到左

　　　　（2）使用where语句的时候，where condition1 and condition2 和where condition2 and condition1两条语句并不等价，这里应当将为为假的可能性最大的语句放到and的右侧；反之，如果是or语句，应当将为真的可能性最大的语句放到or的右侧。

　　　　

转载于:https://www.cnblogs.com/kuangdaoyizhimei/p/4721562.html