Oracle学习日志（二）

结构化查询语言

SQL：Structured Query Language，是所有关系型数据库都支持的语言，SQL语言比较简介，SQL实际并不能满足数据库管理需求，所以所有数据库厂品在SQL语法的基础上做了扩展（扩展的语法只针对自己的数据）

Oracle对SQL的扩展：PL/SQL

SQL Server对SQL扩展：T-SQL

数据查询语言（DQL: Data Query Language） ---- SELECT

数据操作语言（DML：Data Manipulation Language） — INSERT，DELETE，UPDATE

事务控制语言（TCL：Tranasaction Control Language） — COMMIT,ROLLBACK

数据控制语言（DCL：Data Control Language) —GRANT ,REVOKE

数据定义语言 （DDL：Data Definition Language） – CREATE,DROP

1、数据查询

基本语法

从表中取数据，需要使select语句实现，select语句基本语法

SELECT * | { [DISTINCT] columnname|expression [alias],... } 
FROM tablename;

示例：查询EMP表所有数据
select * from emp;  * :表所有列（属性）
示例：查询emp,显示，EMPNO,ENAME,JOB,SAL,COMM属性的数据
select empno,ename,job,sal,comm from emp;
示例：查询EMP，显示雇员姓名，职业，及每月发放的工资
select ename,job,sal+comm from emp; 通过查看结果得到：与空值的属性做运算，得到的是空值
select ename,job,sal + nvl(comm,0) from emp;

expression:oracle数据库中的列支持简单运算
示例：查询EMP，显示雇员姓名，职业，及每月发放的工资，工资别名salary
select ename,job,sal + nvl(comm,0)  salary from emp;
select ename,job,sal + nvl(comm,0) AS  salary from emp;

包含空值的数学表达式的值都为空值

示例：查询显示员工表中的部门信息
select distinct deptno from emp;

小结：SQL大小写不敏感|列名称默认大写显示|复杂的SQL建议分行写

Oracle数据库常用数据类型：**number,varchar2,char,date,**clob,blob

条件过滤

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];

示例：查询emp表中部门编号为10的所有雇员 | 查询emp表中职务为办事员的所有雇员
select * from emp where deptno=10 |select * from emp where job='CLERK'

与 = 运算符同性质的运算符： >,>=,<,<=, <>,!=

示例：查询工资 大于1500的所有雇员 | 查询部门编号不是20的所有雇员
select * from emp where sal > 1500

示例：查询工资大于1500并且部门编号为30的所有雇员 | 查询部门编号是20的所有雇员和薪水高于3000的雇员
select * from emp where sal >1500 and deptno=30;
select * from emp where deptno=20 or sal > 3000;

between ...and 设置条件
示例：查询工资在2000到3000之间的所有雇员 [2000,3000]
select * from emp where sal>=2000 and sal <=3000;
select * from emp where sal between 2000 and 3000;

IN条件 in (值1，值2，...,值n)
示例：查询员工表中雇员编号为： 7369,7521,7788,7934
select * from emp where empno='7369' or empno='7521' or empno='7788' or empno='7934';
select * from emp where empno in(7369,7521,7788,7934,1122,3344,5566);

Like条件 -- 模糊查询 需要记住2个通配符：  _ :匹配任意单个字符  , % ：匹配任意0个或者多个字符
示例：查询姓名(ename)第三个字符为 A所有雇员 | 查询雇员表中员工姓名包含A的所有雇员 
select * from emp where ename like '__A%';| select * from emp where ename like '%A%'
select * from emp where ename like '张%'

null条件 ：空值查询
查询雇员表中佣金为空的所有雇员
select * from emp where comm is null; | select * from emp where comm is null;

备注：

如果属性是number类型，在设置过滤条件时，可以加单引号，也可以不加，如果是字符或者日期，需要用单 引号括起来

多个条件需要使用连接词：and,or

排序

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];

示例：按薪从高到低排序
select * from emp order by sal desc

2、单行函数

单行函数(function)：运行的结果是单个值；

oracle数据库函数：字符函数、数值函数、日期函数、转换函数、通用函数

**DUAL：**一张虚拟表，如果查询的对象不来自某一张表，可以使用DUAL代替(测试函数为主)

字符函数

lower,upper,initcap,concat,substr,length,instr,lpad,rpad,trim,replace

数值函数

round,trunc,mod

日期函数[难点]

elect sysdate from dual;

日期运算公式：

日期 + 数字 = 日期

日期 - 数字 = 日期

日期 - 日期 = 天数

MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY

示例：查询每月最后一天雇佣的雇员 | 下一个星期六日期
select * from emp where hiredate=last_day(hiredate);
select sysdate, next_day(sysdate,'星期六') from dual;
示例：查询员工雇佣的年数，月数,天数
select
ename,
to_char(hiredate,'yyyy-mm-dd') hiredate,
trunc(months_between(sysdate,hiredate)/12,0) year,
trunc(mod(months_between(sysdate,hiredate),12),0) month,
trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate)),0) day
from emp

转换函数

TO_CHAR,TO_NUMBER TO_DATE

yyyy:4位年份
mm:2位月份
dd:2位日期
hh24:小时
mm:分
ss:秒

通用函数

NVL，NVL2,COALESCE
示例：算年终奖，有佣金，年终奖为佣金的10%，没佣金，年终奖为基本工资
select ename,job,nvl2(comm,comm*0.1*12,1000) from emp;
示例：根据佣金判断，如果佣金不为空，显示其上司，如果佣金为空，显示老板
select ename,job,COALESCE(to_char(comm),to_char(mgr),'老板') from emp;

3、CASE表达式

根据部门编号给出备注，如果是10：人事部 20 研发部  30 市场部  

select 
ename,job,hiredate,deptno,case deptno
when 10 then '人事部'
when 20 then '研发部'
when 30 then '市场部'
else '综合部'
end
from emp;

4、Decode函数

select 
ename,job,hiredate,deptno,
DECODE(deptno,10,'人事部',20,'研发部',30,'市场部','综合部')
from emp;

5、多表查询
查询的数据来自多张表，就需要多表一起查; 多表查询会产生迪卡尔乘积 （多表数据组合）

T1(1000),T2(1000),T3(1000) -----> 1000 * 1000 * 1000 ,多表查询的效率是很低的

多表查询设置过滤条件：根据两表之间的关联列设条件（要么是直接的 —emp(deptno 外键,dept(deptno:主键))，要么是间接）

等值查询

示例1：显示雇员表所有信息及雇员的部门名称及地址
select 
e.*,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno

示例2：显示雇员的姓名，职务，薪水，雇员的部门名称，部门地址以及雇员的上司    ---- 多表查询可以自联连查找;
select 
e1.ename,e1.job,e1.sal,d.dname,d.loc,e2.ename 上司
from emp e1,dept d,emp e2
where e1.deptno=d.deptno and e1.mgr=e2.empno

示例3：显示雇员的姓名，职务，薪水，雇员的部门名称，部门地址、雇员的上司以及雇员的工资等级


select 
e1.ename,e1.job,e1.sal,d.dname,d.loc,e2.ename 上司,s.grade
from emp e1,dept d,emp e2,salgrade s
where 
	e1.deptno=d.deptno 
	and e1.mgr=e2.empno 
	and e1.sal between s.losal and s.hisal

连接查询

内连接、外连接

内连接(inner join) :显示满足条件的数据

示例1：显示雇员的姓名，职务，薪水及雇员的部门名称及地址
select
e.ename,e.job,e.sal,d.dname,d.loc
from emp e  inner join dept d 
on e.deptno=d.deptno
示例2：显示雇员的姓名，职务，薪水，雇员的部门名称，部门地址以及雇员的上司

select
e.ename,e.job,e.sal,d.dname,d.loc,e2.ename 上司
from emp e  
inner join dept d on e.deptno=d.deptno
inner join emp e2  on e.mgr=e2.empno

示例3：显示雇员的姓名，职务，薪水，雇员的部门名称，部门地址、雇员的上司以及雇员的工资等级

select
e.ename,e.job,e.sal,d.dname,d.loc,e2.ename 上司,s.grade
from emp e  
inner join dept d on e.deptno=d.deptno
inner join emp e2  on e.mgr=e2.empno
inner join salgrade s on e.sal between s.losal and s.hisal;

外连接(outer join) ：左向外连接（左连接），左路向外连接（右连接），完全外连接

左连接与右连接的本质是一样的，只是在编写SQL语句时， 表位置不一样

左连接：显示左侧表所有数据以及右侧表满足条件的数据,右连接与左连接相反

完全外连接:去迪卡尔乘积后，显示左右两张表所有数据

select 
d.deptno,d.dname,d.loc,e.ename,e.job,e.sal
from dept d  left join emp e on d.deptno=e.deptno;

select 
d.deptno,d.dname,d.loc,e.ename,e.job,e.sal
from  emp e right join dept d on d.deptno=e.deptno;

select 
d.deptno,d.dname,d.loc,e.ename,e.job,e.sal
from  emp e full join dept d on d.deptno=e.deptno;

6、自然连接

如果两张表关联的列名称一样(deptno),可以通过自然连接省去过滤编写条件

select
e.ename,e.job,e.sal,d.dname,d.loc
from emp e natural join dept d

7、USING子句

如果两张表关联的列名称一样(deptno),可以通过USING子句简化过滤编写条件

select
e.ename,e.job,e.sal,d.dname,d.loc
from emp e inner join dept d
USING(deptno)

五、子查询与分组查询

1、子查询

理解子查询：一个查询内部包含的查询称为子查询, 如果查询中包含子查询，子查询需要先执行（子查询的结果是外层查询的过滤条件）

示列：查询比SIMTH工资高的所有雇员 | 查询与SMITH同一个部门的员工
  select * from emp where sal > (select sal from emp where ename='SMITH')
  select * from emp where deptno=(select deptno from emp where ename='SMITH')
 
 子查询的结果为单值 ，过滤条件运算符： =, >,>= ,<,<= ,<>
 
 
 多值子查询, 多值子查询的比较需要用到几个谓词：ALL,ANY
 
 示例：查询比部门编号为30的所有员工工资都要高的员工 | 查询比部门编号为30的任意一个员工 工资高的雇员
 
  select * from emp where sal > all  ( select sal from emp where deptno=30)
   select * from emp where sal > any ( select sal from emp where deptno=30)

分页

oracle数据库中的分页：用到三层SQL语句,同时需要用到伪例(ROWNUM)

第1层：解决排序 第2层：生成连续的序号(ROWNUM)

第3层：获取当前面数据

public List<Emp> getPager(int pagesize,int pageindex){
	
}
select * from 
 (select t1.*,rownum rn from
    (select * from emp order by hiredate desc) t1) t2
where rn BETWEEN startindex and endindex

--pagesize 3,pageindex:3
--startindex :(pageindex-1) * pagesize + 1
--endindex: pagesize * pageindex

以上分页在应用效率低了点,上面的改进版本:

select * from 
 (select t1.*,rownum rn from
    (select * from emp order by hiredate desc) t1 where rownum<=endindex) t2
where  rn >= startindex

2、分组查询

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

分组：分组的目的是以组为单位来获取数据 ----- 一般都用来数据统计 ----- 组函数

sum | avg | max | min | count |.....
select sum(sal),avg(sal),max(sal),min(sal) ,count(*) from emp where deptno=10;

分组要找准分组的列(不是所有的列都适合分组的) ，一般按部门、专业、性别等来分; **分组能显示信息：**分组的列,组函数

基于部门来分组，统计每个部门每月发放的总工资，部门平均工资，部门最高工资，部门最低工资，部门总人数;

select 
deptno,sum(sal),avg(sal),max(sal),min(sal),count(*)
from emp group by deptno