Oracle之查询+内置函数

1.修改+删除语法：

update:

update 表名 set 字段名1=新值1,字段名2=新值2,... [where 条件];
delete:
delete [from] 表名 [where 条件];
truncate table;--->delete 表名;清空表的全部内容
2.查询：
简单查询：
select [distinct] *|column1 [as]别名1，column2 [as]别名2，...from 表名 [where 条件];//[]表示可选，distinct表示去重查询,只返回查询的第一列的值
字符串连接符：||
输入数据符：&
select '工号是:'||empno 工号,'姓名:'||ename 姓名 from scott.emp where empno=&i;//i为变量名,用于接收用户输入的empno作为查询条件
小练习：
查询出雇员表中雇员的工号，姓名，以及工作，入职日期，要求用下面格式显示

工号是:xxxx,姓名:xxxx,工作：xxxx,入职日期：xxxx;

答案：

select '工号：'||empno 工号,'姓名:'||ename 姓名,'工作：'||job 工作,'入职日期:'||hiredate 入职日期 from scott.emp;
补充运算符知识：
算术运算符：+,-,*,/
示例：select 5/2 from dual;
逻辑运算符：
and,or,not
比较运算符：
>,<,>=,<=,=,<>,!=,最后两个符号在oralce中没区别
运算符优先级:
（）>not>算术运算符>比较运算符>and>or
为空:is null
select * from emp where comm is null;
不为空：is not null;
集合：in(2,6,9)
区间：between...and
order by:升序
order by...desc:降序
3.单行函数：
1.字符函数：
upper():转成大写
lower()小写
initcap():单词首字母大写
select ename,job,sal from scott.emp where job in(upper('clerk'),upper('manager'));
select ename,job,sal from scott.emp where job in(upper('clerk'),upper('manager')) and sal not in(1000,3000,5000);
2.字符控制函数：
length():字符串长度，也就是字符个数
select length('汉字') from dual;
lengthb():字节数，
select lengthb('汉字') from dual;
substr(src,i,length):截取字符串
参数1：要截取的字符串
参数2：要从哪个位置开始截取
i>0:
i=0或i=1都表示从第一个字符截取
i>1时表示从第i个字符开始，包括i
i<0:表示截取的开始位置为字符串右端向左数第-i个字符开始
select substr('哇哈哈呵呵',-2,3) as value from dual;--呵呵
instr(src,dest):返回dest在src中出现的位置
select instr('哇哈哈abc呵呵','哇') from dual;---1
如果不存在，返回0
cancat(字符串1，字符串2):连接两个字符串,相当于||
trim(字符串):去除字符串前后空格
trim(字符 from 字符串):从字符串中去除首和尾字符
select trim('a' from 'abca')test from dual;--bc
lpad(s1,length,[pad_string]):左填充
s1:被填充的字符串
length填充后字符串长度
pad_string:要填充的字符串
rpad(s1,length,[pad_string]):右填充
3.数值函数
round(num,s):四舍五入，s表示要显示多少位小数
s=0:表示结果为整数，可不写(s<0一定为整数)
select round(3.14) from dual;
select round(3.1415,3) from dual;--3.142
s>0:表示小数位数
s<0:number(4,-2)类似
mod(n1,n2):相当于java中的%
4.日期函数：
to_date:
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual;
months_between:两个日期之间相差的月数
select months_between(sysdate,'2-8月-16') dd from dual;
select sysdate-hiredate from scott.emp;--相差天数
select (sysdate-hiredate)/7 from scott.emp;--周数
select trunc((sysdate-hiredate)/7) from scott.emp;取整数部分
add_months(date,n):在指定日期上增加n个月
next_day(date,weekday):给定日期的下一个星期几是什么日期
select next_day(sysdate,'星期一') from dual;
last_day(date):返回该月份的最后一天
to_char(date,字符串格式)：把日期转为指定格式的字符串
to_char(date,'yyyy):年
to_char(date,'mm'):月
练习：
查询出员工的姓名、该员工入职的年份，月份，并按年份进行升序排序，
如果年份相同再按月份降序排序
select ename,to_char(hiredate,'yyyy')year,to_char(hiredate,'mm')month from scott.emp
order by year,month desc;
to_char(num,'format'):对数字按指定格式显示
举例：to_char(sal,'$9,999.99')
格式：
9:1~9的任意一个数字
0：表示0，用于数字填充
，：千位符
.：表示小数点
L:本地货币符号
$:美元
查询出员工表中姓名，工资，要求工资显示成美元，再换成rmb（1=7rmb）;
select ename,to_char(sal,'$9,999.99')dollor from scott.emp;
select ename,to_char(sal,'$9,999.99')dollor,
to_char(sal*7,'L00999,999.99') rmb from scott.emp;
5.通用函数
nvl(n1,n2):如果第一个参数为空，那么显示第二个参数的值，如果第一个参数不空，显示第一个参数值
select ename,nvl(comm,0)from emp;
nvl2(p1,p2,p3):如果p1空，返回p3,如果p1不空，返回p2
decode(value,if1,then1,if2,then2,if3,then3,...[,else]);
如果value为if1则返回then1,如果为if2返回then2,....
如果没有相等的，返回else数据
select decode(2,1,'a', 2,'b','no') from dual;
6.case 表达式
格式一：
case express
when result1 then value1
when result2 then value2
.....
[else value]
end;
格式二：
case
 when 条件1 then value1
 when 条件2 then value2
 ....
 [else value]
end;
练习：查询出员工的姓名，工作，并把工作汉化
clerk:职员
salesman:销售
president:总裁
manager:经理
analyst:分析
decode使用
select ename,decode(job, 
'CLERK','职员',
'PRESIDENT','总裁',
'MANAGER','经理',
'SALESMEN','销售',
'ANALYST','分析'
) job(别名) from scott.emp;
case使用
select ename,case job 
   when 'CLERK' then '职员'
   when 'SALESMAN' then '销售'
   when 'PRESIDENT' THEN '总裁'
   else '扫地的'
   end
job from scott.emp;
查询员工的姓名，工资，税率
sal<1000 0
1000<=sal<2000 0.02
2000     30000.04
3000     40000.06
>40000.1
select ename,sal,
   case 
when sal<1000 then 0
       when sal>=1000 and sal<2000 then 0.02
when sal>=2000 and sal<3000 then 0.04
when sal>=3000 and sal<4000 then 0.06
else 0.1 
   end
tax from scott.emp;

select ename,sal,decode(trunc(sal/1000),
0,0,
1,0.02,
2,0.04,
3,0.06,
0.1)tax from scott.emp;
4.多表查询（***重点***）
如果一个查询语句需要显示多张表的数据，就必须用多张表查询，这种操作叫多表查询
select [distinct]*|字段[别名][,字段[别名],....]from 表名1 [别名]，[表名[别名],...]
[where 条件] [order by 字段 asc|desc][,排序字段 [asc|desc],...];
1.笛卡尔积（交叉连接）
select ename,dept.deptno from emp,dept;
2.内连接
1.等值连接
查询出员工的姓名，部门编号，部门名字
select e.ename,e.deptno from emp e;
select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno
ENAME          DEPTNO
---------- ----------
SMITH              20
ALLEN              30
WARD               30
JONES              20
MARTIN             30
BLAKE              30
CLARK              10
SCOTT              20
KING               10
TURNER             30
ADAMS              20
JAMES              30
FORD               20
MILLER             10

DEPTNO  DNAME
------- ----------
10ACCOUNTING
20RESEARCH
30SALES
40OPERATIONS
2.非等值连接
查询出员工姓名，工资，以及工资在公司的等级
select ename,sal,grade,losal,hisal from scott.emp,scott.salgrade s where sal between s.losal and s.hisal;
3.自连接
查询出员工的工号，姓名，工资，领导的姓名，领导工资
select empno,ename,mgr from emp;
select e.empno,e.ename,e.sal,m.ename,m.sal from emp e,emp m where e.mgr=m.empno;
练习：
查询出员工的姓名，工资，所在部门编号，部门名字，工资在公司的等级
emp,dept,salgrade
select e.ename,e.sal,e.deptno,d.dname,s.grade 
 from emp e,dept d,salgrade s 
 where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
练习：四表查询，查询出员工姓名，工资，部门编号，部门名，工资等级，
领导姓名和工资，领导工资等级
1.分析是几张表
2.查询哪些字段
3.落实查询条件
4.先实现最简单一部分功能，
select e.ename,e.sal,e.deptno,d.dname,s.grade,m.ename,m.sal,s.grade 
  from emp e,dept d,salgrade s,emp m,salgrade ms
  where e.deptno=d.deptno
  and e.sal between s.losal and s.hisal
  and e.mgr=m.empno and m.sal between ms.losal and ms.hisal; 
   3.外连接
左连接：
在多表查询时（+）写在‘=’号右边是左连接，左表是等号左边的表，右表是等号右边的表
特点：会显示左表中所有行，如果左边中某些行跟右表没有匹配项，则该行在显示时，对应右表部分会不显示
右连接：
+出现在等号左边，

特点：显示右表中所有行，如果右表中某些行跟右表无法匹配，则该行在显示时，对应左表部分不显示

查询出员工的姓名，工资，部门号，部门名（要显示所有部门，没有员工的部门也显示）
select ename,sal,d.deptno,dname from emp e,dept d where e.deptno=d.deptno; 
查询出员工的姓名，工资，领导的姓名，工资，要求没有领导的员工也显示
select e.ename,e.sal,m.ename,m.sal

from emp e,emp m where e.mgr=m.empno(+);

5.集合操作：

union:将两个查询结果合并成一个，并去除重复，要求两个结果集的表数和每列对应类型一致
select s_id,name from stu union select c_id,cours from class;
union all:跟上面一样，但不去重复
select s_id,name from stu union all select c_id,cours from class;
intersect:交集
select s_id,name from stu intersect select c_id,cours from class;
minus:差集
select s_id,name from stu minus select c_id,cours from class;
6.组函数（聚合函数）：
sum()：统计总和
max(): 最大值
min(): 最小值
avg(): 平均值
count():不为空的记录数，一般用count(1)
查询出员工表中员工人数，平均工资（小数点后保留2位），最高工资，最低工资
7.分组统计：group by
select 查询字段 from 查询的表名 where 查询过滤条件
group by 字段1[,字段2，...] order by
查询出每个部门人数
select deptno,count(1) from emp group by deptno;
*****注意：如果使用分组统计，查询结果只能是分组函数和分组条件
查询出每个部门的总人数，最高工资，和部门名
select count(1),max(e.sal),d.dname     // *** 组函数和字段同时作为查询字段时该字段必须作为分组条件
 from emp e,dept d where e.deptno=d.deptno
 group by d.dname;
查询出每个部门的总人数，最高工资，和部门名,只显示总人数超过4的信息
select count(1),max(e.sal),d.dname
from emp e,dept d where e.deptno=d.deptno
group by d.dname
having count(1)>4;
如果条件中要带组函数，要用having
select 查询字段 from 要查询的表名 where 查询条件 ---条件中不能使用分组函数
group by 分组字段
having---having可使用组函数
order by 排序字段 
总结：
where 跟having区别：
1.where放在group by 前面，先过滤再分组
2.having放在group by后面，先分组再过滤
3.where条件中不能出现分组函数，having中可以
=====================================================
8.子查询：在select语句中又嵌套select语句
1.where型子查询
1.单行子查询：子查询的返回结果只能是一行，这种查询叫单行子查询
查询出工资高于smith的员工信息
select * from emp where sal>
(select sal from emp where ename='SMITH');
查询出工资高于公司平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);
2.多行子查询：in,any,all
1.in:
查询每个部门最低工资的员工信息
每个部门的最低工资
select min(sal),deptno from emp group by deptno;
select * from emp where sal in(800,950,1300);

select * from emp where sal in
(select min(sal) from emp group by deptno);

select * from emp where (sal,deptno) in
(select min(sal),deptno from emp group by deptno)
2.any
=any:相当于in
select * from emp where sal =any
(select min(sal) from emp group by deptno);
>any:比子查询中最小的值大就成立
select * from emp where sal >any
(select min(sal) from emp group by deptno);
<any:比子查询中最大值小就成立
select * from emp where sal <any
(select min(sal) from emp group by deptno);
3.all:=all没什么用，如果子查询结果集多于1个，则什么也查不出
select * from emp where sal =all
(select min(sal) from emp group by deptno);
>all:比子查询中最大值大就成立
<all:比子查询中最小值要小就成立
2.from 型子查询：查询结果作为一张表存在，并为表取别名，使用该表字段进行过滤
练习：查出每个部门的编号、名称、位置、部门人数，部门平均工资
法一：多表查询，产生了笛尔积，一共有56条记录进行过滤
select d.deptno,d.dname,d.loc,count(1),avg(e.sal)
   from emp e,dept d
   where e.deptno(+)=d.deptno
   group by d.deptno,d.dname,d.loc;
法二：from型子查询，一共有14+12=26条记录进行过滤，效率高于法一
select d.deptno,d.dname,d.loc,tmp.count,tmp.avg
   from dept d,
   (select deptno,count(1) count,avg(sal) avg from emp group by deptno)tmp
   where d.deptno=tmp.deptno(+);
3.关联子查询：两个查询是相互关联的，子查询不能独立存在，依赖于外部查询的数据作为内部查询的条件使用
查询工资高于本部门平均工资的所有员工信息
from 型子查询
select deptno,avg(sal) from emp group by deptno;
select e.*
from emp e,
(select deptno,avg(sal)avg from emp group by deptno)tmp
where tmp.deptno = e.deptno and sal>tmp.avg;

select * from emp out where out.sal>

(select avg(sal) from emp inner where inner.deptno=out.deptno);

9.分页查询：
 rownum:伪列，每张表都存在这一列，永远是从1开始，也就是显示出来的结果中，第一个值一定是1
显示前4条：select rownum,e.* from emp e where rownum<5;
显示第五条之后的记录：（使用from型子查询查询表所有信息及rownum伪列作为子查询真实字段，调用该字段实现查询行数过滤）
select rownum,tmp.* from 
 (select rownum rn,e.* from emp e)tmp where tmp.rn>5;
显示6--10之间的记录：
select rownum,tmp.* from
 (select rownum rn,e.* from emp e)tmp
 where tmp.rn between 6 and 10;
 rowid:物理地址，是由18位64进制编码组成
哪64个？
A-Z(26个，0--25)
a-z(26个，26--51)
0-9(10个，52--61)
+,/(2个，62--63)
OOOOOO   FFF   BBBBBB   RRR
对象    文件    块     row   -->数据是储存在块中的某个文件中的某个对象中的某行
需求：删除一张表中重复的记录？
 delete from t1 where rowid not in
 (select min(rowid) from t1 group by id,name);
//以id分组，如果id不重复则该组只有一条记录(rowid唯一)则不删除，否则删除该组rowid不是最小的那些记录。