Oracle和SQL

这两天在公司培训了Oracle，所以有必要简单的总结一下.我所用的Oracle数据库是Oracle9i Enterprise Edition Release 9208

1、什么是Oracle？为什么被称之为"甲骨文"?

 Oracle是一个生产中间件和数据库的较大的生产商。Oracle原本的含义是:"神谕",指的是神说的话。中国商朝时代，人们把一些刻在龟壳上的文字当成是上天的指示，所以在中国将Oracle翻译为"甲骨文".

2、一些细节方面

2.1、Oracle中默认日期和字符是左对齐,数字是右对齐

3、SQL Struct Query Language结构化查询语言

DQL--数据查询语言,比如select语句.

DDL--Data Define Language数据定义语言,create drop alter...

DML--Data Manipulation Language数据操作语言,insert,update,delete..

DCL--Data Control Language数据控制语言,grant(授权),revoke(废除权限)

4、纵向投影操作--select

这个很简单不需要多说了，但这也是其他复杂查询语句的基础。需要提醒一下:distinct关键字去除重复以及操作符||的作用是将各列查询结果相连.  e.g. select '编号是:'||empno||'的雇员，姓名是:'||ename||'工作是:'||job from emp;

5、查询出基本工资不大于1500，同时不可以领取奖金的职工信息

 select * from emp where not(sal>1500 or comm is not null)

6、NVL()函数--如果原来的数值是null的话,由指定数值代替

e.g:select last_name,title,salary*NVL(comm,0)/100 from emp;

7、 查询出在1981年入职的全部雇员的信息

最才常规的思路就是查询出from1981-1-1~1981-12-31入职的人员

 select * from emp where hiredate between '1981-1-1'  and '1981-12-31';

8、在使用like子句的时候，我们用到了"%"--可以匹配任意长度的字符创;"_"--可以匹配一个字符

9、 对查询的结果排序我们使用的是order by关键字:asc升序(默认)、desc降序

这里需要注意的是：distinct也可以触发排序操作;select * from emp order by 1(按第一字段排序)

10、 单行函数 lower--转小写、upper--转大写、initcap--首字符大写、concat--连接字符串，等效于||

substr--截取字符串、length--求字符串长度、nvl--空值函数

11、查询出所有雇员的姓名以及姓名的后三个字符

select ename,sub(ename,-3,3) from emp;

从结果可以看出,当我们要从后向前取字串的时候，只要把第二个参数设置为负数就可以了.

12、 数值函数 round--四舍五入，trunc--忽略小数部分,mod--取模，相当于Java中的"%"

13、 日期函数 Oracle中提供了许多和日期操作相关的函数，主要包括加减，但对日期的操作需要遵循一些原则：

       日期-数字=日期

       日期+数字=日期

       日期-日期=数字  -- 表示两个日期之间间隔的天数

内置函数:add_months():

select sysdate 系统时间,add_months(sysdate,-5*12) 系统时间减去五年 from dual
select sysdate 系统时间,add_months(sysdate,5*12) 系统时间添加五年 from dual

              months_between()--返回两个给定日期之间相隔的月数

 eg--查询部门编号为10的雇员工作的月数:

 select round(months_between(sysdate,hiredate)) 相隔的月数
 rom emp where deptno='10';

              next_day():--返回指定日期下一个指定的星期几是哪一天

e.g: SELECT sysdate,next_day(sysdate,'星期二') FROM dual;

              last_day():--返回一个月的最后一天

e.g: SELECT last_day(to_date('2010-2-1','yyyy-mm-dd')) FROM dual;

14、查询上个月的最后一天:

select last_day(add_months(sysdate,-1)) 上个月的最后一天 from dual;

15、查询三月份入职的雇员

select * from emp where to_char(HIREDATE,'mm')='03';

to_char()--将数字或是日期转换为字符 select to_char(sysdate,' fmyyyy-mm-dd hh24:mi:ss') from dual

这里有一个小小的知识点--fm去除字符串中的0 select EMPNO,ENAME,to_char(SAL,'L9,999') from emp;--L表示本地货币 $美元

16、to_date()--字符转日期

17、to_number()--字符转数字

18、group by--分组查询 可以使用having子句对分组后的条件进行过滤

需要注意一点的是:where -- 单行函数

                         having -- 组函数

 这里有必要再谈谈where子句和having子句的区别:

WHERE 只能对单条记录限制（过滤），having是对分组进行过滤

19、 多表连接查询

 两张表在没有任何关联的情况下会产生笛卡尔积

19.1 等值连接  -- 表的两边有空值的话不会显示出来

19.2 自连接 --内连接

e.g 查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名

SELECT e.ename,e.job,s.ename FROM emp s,emp e WHERE s.empno=e.mgr;

19.3 左、右连接

--查询雇员的编号、姓名及其领导的编号、姓名
SELECT e.empno,e.ename,s.empno mgr_no,s.ename mgr_name
 FROM emp e,emp s
 WHERE s.empno=e.mgr;

==查询的结果只有13条记录,但以上结果缺少了KING,因为他是最高领导，所以他的领导编号为空.故没有办法查询出来.为了显示出来我们使用左/右连接来显示其信息

SELECT e.empno,e.ename,s.empno mgr_no,s.ename mgr_name
 FROM emp e,emp s
 WHERE s.empno(+)=e.mgr;

这里需要注意一下的是:(+)号放在哪边就表示在哪边补空,来和对方匹配,使得数据一个都不会漏掉.本例中的领导可能不存在，所以就将(+)放在了可能出现空值的一边

20、子查询

e.g--找出所有员工中工资最低的那个员工

select * from emp where SAL=(select min(SAL) from emp)

上边是一个简单的子查询的例子, 子查询运行的顺序是:先运行子查询在运行主查询,子查询一般出现在运算符的右边

e.g --查询出谁和Smith做同样的工作
--1、首先查询出Smith是做什么工作的？
select job from emp where ename='SMITH'
--2、再找出和smith工作相匹配的人
select * from emp where job in(
select job from emp where ename='SMITH'
) and ename<>'SMITH'

e.g-- 查询出哪些雇员的工资低于平均工资
--1、查询出平均工资
select round(nvl(avg(SAL),0) )平均工资 from emp
--2、找出雇员工资低于平局工资
select * from emp where SAL<(
select round(nvl(avg(SAL),0) )平均工资 from emp
)

21、前面提到分组group by 以后我们可以用having子句对分组后的条件进行过滤.但有一点是需要注意的:having子句后面接的是组函数,那么什么是组函数呢?

 SQL中常用的分组函数
 Counnt()   Max()   Min()    Avg()  Sum()--这些函数大家平时都比较常用，具体就不解释了. 
另外,我们在进行分组统计查询时有遵循这样一条规律:

出现在字段列表中的字段，如果没有出现在组函数中，就必定出现在GROUP BY 语句的后面--这很重要，必须牢记!!

e.g.--查询出每个部门的部门名称，及每个部门的雇员人数

SELECT d.deptno,d.dname, count(e.empno)
 FROM emp e, dept d
 WHERE e.deptno = d.deptno
 GROUP BY d.deptno,d.dname

22、利用RowNum伪列进行分页显示

首先看两条SQL语句:SELECT * FROM emp  where rownum < 5   ---显示4条语句(结果和我们预期的一样)
                            SELECT * FROM emp  where rownum > 5   ---显示0条数据(why???)

再来另外一条SQL语句: SELECT rownum, e.* FROM emp e WHERE rownum != 5 --同样显示前4条语句(结果和第一条SQL一样)

这样的SQL呢? SELECT rownum, e.* FROM emp e WHERE rownum > 0  ---查询出全部的记录(相当于select * from emp)

继续看 SELECT rownum, e.* FROM emp e WHERE rownum >= 1 --查询出来的结果和上面的一模一样

现在大家应该有点明白了吧？如果为想要查询前十条的记录该如何下手呢？

SELECT rownum, e.* FROM emp e WHERE rownum between 0 and 10;

SELECT rownum, e.* FROM emp e WHERE rownum between 1 and 10

---他们都可以得到前十条的记录

呵呵，那么为什么 SELECT rownum, e.* FROM emp e WHERE rownum between 2 and 10 记录就为0呢？

看明白的朋友应该可以很快的写出：

如何查询前1-5条的记录：SELECT * FROM emp  where rownum < 6

如何查询出6-10条的记录:

SELECT * FROM (SELECT rownum as rn, emp.* FROM emp WHERE rownum < 11) WHERE rn > 5

一般代码中对结果集进行分页也就是这么做的.

上面就是一天来培训的内容！哎~~写了很长时间了!!