Oracle（二）

三、SQL语言介绍

分类	命令
DDL ，Data Definition Language数据定义语言	create:创建；drop:删除；alter:修改；rename:重命名；truncate:截断
DML，Data Management Language数据管理语言	insert:插入；delete:删除；update:更新；select:查询
DCL，Data Control Language数据库控制语言	grant:授权；revoke:回收权力；commit:提交事务；rollback:回滚事务

四、SQL语句

1.select结构
select * | colname [,…] from table [alias]
2.去重、别名和排序
（1）去重结构
select distinct colName from tableName
（2）列别名
select colName [as] nameA …
（3）排序
select . . . order by colName asc | desc
3.伪列和表达式
伪列示例：
– 查询员工姓名、月工资、年薪（月薪12）
select ename，sal，sal12 from emp；
（1）null处理
nvl()
示例：查询员工 月收入
说明： – nvl(comm，res) ，comm为查询列，此列若为空，则使用res代替.
select ename，sal，comm，sal+nvl（comm，0） 月收入 from emp；
（2）nulls first 和 nulls last排序
示例：查询员工信息，并且按照提成升序排序（有些员工提成可能为null）
说明：若排序字段出现null时，考虑null的位置在前还是后
select * from emp order by comm desc nulls first；
（3）字符串拼接
||
示例：在员工名字后拼接一个“a”字母
select ename，ename || “a” 别名 from emp；
示例：拼接员工名字和提成，若提成为null则会作为空字符串进行拼接
select ename，comm，ename || comm test from emp；
4.虚表
dual是一个虚表，虚拟表。用来构建称select的语法规则，oracle保证dual里面永远只有一条记录。该表只有一列，和其他表一样可以执行插入、更新、删除操作，还可以执行drop操作。但是不要执行drop表的操作，否则会使系统不能用，起不了数据库。
dual只要用来选择系统变量或是求一个表达式的值。如果我们不需要从具体的表来获得表中数据，而是单纯地为了得到一些我们想要的信息，并要通过select完成，就要借助一个对象，这个对象就是dual。
示例：计算999666
select 999666 from dual
5.select语句的执行顺序
– 简单查询 所有的行记录 某一行（指定、全部）字段
– 去重
– 别名
– 伪列
– 排序
– null 当null进行算数运算时，结果为null
– || 字符串拼接
– nulls first 和 nulls last 升序降序null的位置
select distinct 查询字段1 别名，查询字段2 as 别名，表达式 别名 from 表 order by 排序字段 asc[desc]

– 语法
select . . . from . . . order by
–解析顺序
–1）from
–1）select
–1）order by
6.条件查询
语法：
select 查询内容 from 数据来源 where 行记录条件
（1）条件运算
结构：select 查询字段 from 数据来源 where 行记录条件
= 、>、<、>=、<=、<>、!=、between …and…、in
between …and…:两边的区间都包含
in：相当于or
（2）连接条件运算
and、or、not
当查询条件有多个时，可以需要同时满足，或者只满足其中一个，或者不满足某个条件，则需要用到 或。且。非
（3）null运算nvl（）
null比较特殊需要单独吹
is null 为空
is not null 不为空
not . . . is null 不为空
示例：select * from emp not comm is null
7.模糊查询
% 、 _
（1）% ：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
示例： SELECT * FROM [user] WHERE u_name LIKE ‘%三%’
将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。
另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’

若使用 SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’
虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”

（2）_ ： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：
（3）[ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’
将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE ‘老[1-9]’
将找出“老1”、“老2”、……、“老9”；

（4）[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

比如 SELECT * FROM [user] WHERE u_name LIKE ‘[^张李王]三’
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

SELECT * FROM [user] WHERE u_name LIKE ‘老[^1-4]’;
将排除“老1”到“老4”，寻找“老5”、“老6”、……
8.where子句查询
要求：查询 工资等级在2的员工信息
– 数据 员工信息
– 来源 员工表
– 条件 工资等级 为2
select * from emp where sal between （select losal from salgrade where grade=2） and （select hisal from salgrade where grade=2）

losal	hisal
700	1200
1201	1400
1401	2000
2001	3000
3001	9999

9.函数

Oracle
SQL提供了用于执行特定操作的专用函数，这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并且返回一个输出结果。这种由Oracle提供给我们的函数成为内置函数，除了内置函数外还可以自定义函数。

根据函数的返回结果，我们将函数分为单行函数和多行函数
单行函数：对应在标记录时，一条记录返回一个结果。例如lower（x），将参数转换为小写。
多行函数：也称 组函数 或 聚合函数(重点)：此类函数可同时对多条记录进行操作，并返回一个结果。例如max（x）求最大值

常用单行函数：
（1）字符函数：
concat（column1,column2）：将column1和column2进行字符串形式拼接。concat：合并多个数组，合并多个字符串
instr（x，str，start，n）：在x中查找str，可以指定start开始，也可以指定从第n次开始.
示例：select instr("helloword","e") from dual; //返回2，若不存在返回0
length（x）：返回x的长度
lower（x）：x转换为小写
upper（x）：x转换为大写
ltrim（x，trim_str）：把x左边截去trim_str字符串，缺省截去空格
rtrim（x，trim_str）：把x右边截去trim_str字符串，缺省截去空格
replace（x，old，new）：在x中查找old，并替代为new
substr（x，start，length）：返回x的字符串，从start处开始，截取length个字符，缺省length，默认到结尾
（2）数学函数
abs（x）：x的绝对值
ceil（x）：向上取整
floor（x）：向下取整
mod（x，y）：对x求y的余数
（3）日期函数
sysdate：当前系统日期
sysdate+day：当前系统日期+day天
current_date：返回当前系统日期
add_months(d1,n1)：返回在日期d1基础上再n1个月后新的日期
last_day(d1)：返回日期d1所在月份最后一天的日期
months_between(d1,d2)：返回日期d1到日期d2之间的月数
next_day(d1[,c1])：返回日期d1在下周，星期几（参数c1）的日期
（4）转换函数
to_char(x,c)：将日期或数据x按照c的格式转换为char数据类型
to_date(x,c)：将字符串x按照c的格式转换为日期
to_number(x)：将字符串x转化为数字型
（5）常用组函数
avg()：平均值
sum()：求和
min()：最小值
max()：最大值
count()：统计
注意：null不参与运算

10.group by分组
“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
结构：select … from … where … gruop by …

原始表

示例1
select 类别, sum(数量) as 数量之和
from A
group by 类别
返回结果如下表，实际上就是分类汇总。

11、Group By中Select指定的字段限制
示例3
select 类别, sum(数量) as 数量之和, 摘要
from A
group by 类别
order by 类别 desc
示例3执行后会提示下错误，如下图。这就是需要注意的一点，在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

12.Group By与聚合函数
在示例3中提到group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中，常见的聚合函数如下表：

示例5：求各组平均值
select 类别, avg(数量) AS 平均值 from A group by 类别;
示例6：求各组记录数目
select 类别, count(*) AS 记录数 from A group by 类别;
示例7：求各组记录数目
13.having过滤组信息
要获取的组信息也许要满足一定条件时，我们通过having来过滤组的条件。
结构：
select . . . from . . . where . . . group by . . . having . . .
14. Having与Where的区别

(1)where
子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
(2)having
子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组。

示例8
select 类别, sum(数量) as 数量之和 from A
group by 类别
having sum(数量) > 18
示例9：Having和Where的联合使用方法
select 类别, SUM(数量)from A
where 数量 gt;8
group by 类别
having SUM(数量) gt; 10
结构：select . . . from . . . where . . . group by . . . having . . .
执行顺序：
from
where
group by
having
select

15.分页
Oracle中的rownum一般是在实现分页查询时用到，虽然在我做的项目中只实现了分页显示而没有实现真正的分页，但是出于学习的目的研究了一下oracle的rownum。在使用查询语句时，我们经常要求返回表中的前n条记录或者是中间的几条记录，比如在一个大表（假设有10W条数据）要求查询从第1000到1005条的记录。面对这种查询，我们怎么办呢？mysql和oracle都有自己的解决办法。
在mysql中，我们可以使用limit语句来实现：
1）查询从第1000到1005条的记录（注意mysql中的记录是从0开始编号的，所以第1000条记录编号为999）
select * from table_name limit 999,5;
Oracle中的实现
Oracle使用rownum的关键字来实现这种查询：
首先我们假设有一个地域信息表area，其表结构如下图所示：

表中的数据如下图所示（select * from area语句得到的结果）：

1）查询表中的前8条记录
select * from area where rownum <= 8
查询结果如下：

2）查询第2到第8条记录
对于这种形式的查询，oracle不像mysql那么方便，它必须使用子查询或者是集合操作来实现。我们可以使用以下3种方式可以实现：

A: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num between 2 and 8;

首先根据select id,province,city,district,rownum as num from area得到一个临时表，这个临时表中有一个rownum列（一个伪列，类似与rowid，但又不同于rowid，因为rowid是物理存在的一个列，也就是说Oracle数据库中任何一个表都有一个rowid列，而rownum不是物理存在的），然后在临时表中来查询。

B: select * from area where rownum <= 8 minus select * from area where rownum < 2;

使用集合减运算符minus，该操作返回在第一个select中出现而不在第二个select中出现的记录。

C: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num >=2

intersect

select * from area where rownum <= 8;

使用集合交运算符intersect，这里绕了一个弯（不过这个弯实现了rownum大于某个数的查询），它是首先利用A的方式查询得到所有rownum大于2的记录，然后再与rownum小于等于8的记录集合做交运算。三种操作得到的结果一样，如下图所示：

发现没有，它只对area表中的前8条记录进行排序。那么，如果我要取表中的前8条记录并且要求是全表有序，那怎么办呢？还是老办法，使用子查询。我们可以使用以下语句得到：

select * from (select * from area order by district)

where rownum <= 8;

查询的结果如下图所示：

Oracle中的rownum与mysql的limit实现的功能相同，但没有mysql来的容易，它一般通过一个子查询来实现。mysql的易用性也是它能够纵横开源数据库的原因，它不像postgresql那样的学院派，它的那种简单易用性或许在大型软件项目的开发中值得借鉴。最近听说sql server 2008也实现了limit的查询，不过还没去试过，Oracle在这方面也要加油啊，用户容易使用才是王道。
16.去重
rowid介绍
ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID它是一个伪列，并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。
有时繁琐的数据检索时，普通检索条件不能达到要求，可以利用rowid来精确检索的结果。
oracle中如果要查询某张表中多个字段，又只对某个字段去重的时候用distinct或者group by都不行。diatinct和group by会对要查询的字段一起进行去重，也就是当查询的所有字段都相同，oracle才认为是重复的。这时用rowid是个不错的选择。

ROWID的使用——快速删除重复的记录
ROWID是数据的详细地址，通过rowid，oracle可以快速的定位某行具体的数据的位置。
ROWID可以分为物理rowid和逻辑rowid两种。普通的表中的rowid是物理rowid，索引组织表(IOT)的rowid是逻辑rowid。
当表中有大量重复数据时，可以使用ROWID快速删除重复的记录。

删除重复记录方法很多，列出两种。

⑴ 通过创建临时表

可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：

SQL>create table stu_tmp as select distinct* from stu;

SQL>truncate table sut; //清空表记录

SQL>insert into stu select * from stu_tmp; //将临时表中的数据添加回原表

这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。

⑵ 利用rowid结合max或min函数

使用rowid快速唯一确定重复行结合max或min函数来实现删除重复行。

SQL>delete from stu a where rowid not in (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex =b.sex); //这里max使用min也可以

或者用下面的语句

SQL>delete from stu a where rowid < (select max(b.rowid) from stu b where a.no=b.no and a.name = b.name and a.sex = b.sex);

//这里如果把max换成min的话，前面的where子句中需要把"<“改为”>"

跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。

SQL>delete from stu where rowid not in (select max(rowid) from stu t group by t.no, t.name, t.sex );

思考：若在stu表中唯一确定任意一行数据（1, ‘ab’,’男’），把sex字段更新为”女”，怎么做？

   SQL>update stu set sex=’女’ where rowid=(select min(rowid) from stu where no=1 and name=’ab’ and sex=’男’);

我的理解：当想要得到唯一值的时候就可以考虑使用rowid来进行处理。

17.表连接
表连接介绍
简单来说，我们将数据存在不同的表中，而不同的表有着他们自身的表结构，不同表之间可以是有关联的，大部分实际使用中，不会仅仅只需要一张表的信息。
连接查询：
1）即查询的时候同时需要多张表（特别是存在外键关系的），此时需要多张表之间的值进行连接；
2）目前SQL标准提出过两种连接后才想你，第一种是较早的SQL92标准，第二种是目前使用广泛的较新的SQL99标准；
3）92形式简单，但编写较为容易，99不仅在底层得到优化，并且形式看上去更加一目了然，逻辑性更强，一般建议使用99标准；
(1)92语法
多张表需要全部放在from之后，所有的连接条件都放在where当中，因此SQL92中的等值连接、非等值连接、外连接等其实只是where条件的筛选。
结构：select . . . from table1,table2,table3 . . . where . . .
很多时候需要为表取别名（1、简化表名 2、可能存在自连接的情况）
连接的原理：按照from后面表的出现顺序，前面的表作为内存的for循环，后出现的表作为外层的for循环。
(1.1)92语法笛卡尔积
通过线性代数的人都知道，笛卡尔积通俗的说，就是两个集合中的每个成员，都与对方集合中的任意一个成员有关联。

--笛卡尔积
--使用的字段,是多个表中存在的,指定字段的来源
select empno,emp.deptno from emp,dept;

(1.2)92语法等值连接
在笛卡尔积的基础上取条件列相同的值，例如a表中id等于b表中的a_id。

--等值连接  可以是相同名字的字段,也可以是非相同名字的字段,但是要保证两个字段的类型相同
select * from emp,dept where emp.deptno=dept.deptno;
select * from emp,dept where emp.ename=dept.dname; --没有满足条件的数据

(1.3)92语法非等值连接
!=、>、<、<>、between and
示例：
– 查询每一个员工的姓名，工资金额，入职时间，对应工资等级
– 查询数据：ename，sal，hiredate，grade
– 数据来源：emp，salgrade
– 连接条件：sal between losal and hisal
有两张表，一张emp，一张salgrade
select ename，sal，hiredate，grade from emp e，salgrade s where e.sal between losal and hisal;//此时的 e.sal between losal and hisal可以带出对应的grade，这种是非等值连接查询

--非等值连接
--薪资2500的等级信息
select * from salgrade where 2500 between losal and hisal;
--查询所有员工的信息及工资等级信息
select * from emp e,salgrade s where e.sal between s.losal and s.hisal;

(1.4)92语法内连接
内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。
1、内连接
内连接，即最常见的等值连接，例:
SELECT * FROM TESTA,TESTBWHERE TESTA.A=TESTB.A
(1.5)92语法外连接
92语法内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。内连接使用比较运算都根据每个表共有的列的值匹配两个表中的行。

92语法外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接
外连接分为左外连接，右外连接和全外连接。

92语法左外连接 left outer join 或者 left join
在左连接和右连接时都会以一张A表为基础表，该表的内容会全部显示，然后加上A表和B表匹配的内容。 如果A表的数据在B表中没有记录。 那么在相关联的结果集行中列显示为空值（NULL）

左外连接就是在等值连接的基础上加上主表中的未匹配数据，例:

SELECT *FROM TESTA LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A
结果:

92语法全外连接 full outer join 或者 full join
全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上。
SELECT * FROM TESTA FULL OUTER JOIN TESTBON TESTA.A=TESTB.A
结果:

对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：

（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
（+）操作符只适用于列，而不能用在表达式上。
（+）操作符不能与OR和IN操作符一起使用。
（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
例如：用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。
Select * from t_A a,t_B b where a.id=b.id(+);

select * from emp e2,emp e1 where e1.mgr=e2.empno(+); --右连接
select * from emp e1,emp e2 where e1.mgr=e2.empno(+); --左连接

(1.6)92语法自连接
自连接的表是同一张表，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
要求：显示各员工的姓名和他的上级领导姓名
思路：把emp表看做两张表（worker，boss）

select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;

select * from emp e1,emp e2 where e1.mgr=e2.empno; --自连接

(2)99语法
（2.1）99语法cross join ：交叉连接，实现笛卡尔积

--92写法
select * from emp ,dept;
--99写法   交叉连接 cross join
select * from emp cross join dept;

（2.2）99语法natural join ：自然连接，做等值连接（需要有同名列或主外键）

-- 查看所有员工名称，员工编号，所属的部门编号，部门名称
-- 查询的数据：员工名称，员工编号，所属的部门编号，部门名称
-- 数据来源：emp，dept
-- 92连接条件：emp.deptno = dept.deptno
-- 99连接条件：可以使用自然连接实现（等值）
select ename，empno，deptno，dname from emp natural join dept；

上述SQL语句中， natural join 左右两张表中需要有 同名列 或 主外键的列才可以。
（2.3）99语法table1 join table2 using（同名列） ：using连接，等值连接
查询所有员工姓名及所在部门的名称。

-- 使用using连接实现（等值）
select ename，empno，deptno，dname from emp join dept using(deptno);

99中natural join和join using的使用
要求：
– 查询10部门的员工名称，部门编号，部门名称
– 只能是10部门的

-- natural join自然连接实现
select ename，deptno，dname from emp natural join dept where deptno=10；
-- join using连接实现
select ename，deptno，dname from emp join  dept using(deptno) where deptno=10;

（2.4）99语法 join on：可做等值连接、非等值连接、自连接、可以解决一切连接，关系列必须要区分。
使用方式：table1 join table2 on 条件
等值连接：

-- 查看所有员工的名字、编号、以及所属的部门编号，部门名称
-- 查询的数据：员工的名字、编号、以及所属的部门编号，部门名称
-- 数据来源：emp dept
-- 连接条件：emp.deptno=dept.deptno
select ename,empno,e.deptno,dname
from emp e
join dept d
on e.deptno=d.deptno;

以上在SQL语句中同名列前是否需要加限定名称的字段？如果使用的 自然连接 或 using连接 则不需要加。如果使用 join on 则需要添加指定表中的字段。
非等值连接：

-- 查看每一个员工的姓名，工资，所属的部门编号，工资等级
-- 查询的数据：员工的姓名，工资，所属的部门编号，工资等级
-- 数据来源：emp salgrade
-- 连接条件：sal between lasal and hisal
-- on实现
select ename，sal，deptno，grade
from emp e
join salgrade s
on sal between lasal and hisal；

等值和非等值连接，三表连接

-- 查询30部门员工姓名，工资，部门编号，工资等级，部门名称
-- 查询的数据：员工姓名，工资，部门编号，工资等级，部门名称
-- 数据来源：emp e，salgrade s，dept d
-- 连接条件：sal between losal and hisal    e.deptno=d.deptno
select ename,sal,e.deptno,grade,dname
from emp e
join dept d on e.deptno=d.deptno
join salgrade on e.sal between losal and hisal
where e.deptno=30;

（2.5）99语法 外连接outer join
外连接，有主表和从表一说
left [outer] join on
left [outer] join using
right [outer] join on
right [outer] join using

-- 查看每一个员工的编号，员工名称，上级编号，上级名称
-- 查询数据：员工的编号，员工名称，上级编号，上级名称
-- 数据来源：emp e，emp m
-- 连接条件：e.mgr = m.empno
-- on 连接实现  外连接
select e.empno 员工编号，
		e.ename 员工名称，
		e.mgr 上级编号，
		m.ename 上级名称
from emmp e 
left outer join emp m 
on e.mgr=m.empno;

（2.6）99语法 全连接full join
– 全连接

select ... from table1 full join table2 on 连接条件 where ...

（3）集合操作
Union：并集（去重）对两个结果集进行并集操作，不包括重复同时进行默认规则的排序；
Union All：全集（不去重）对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect：交集（找出重复）对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus：差集（减去重复）对两个结果进行差操作，不包含重复行，同时进行默认规则的排序；