oracle常用sql三连击——01

将admin_sm_org表中org_spef_name字段的值中包含的“郑州”字样全部修改为“天堂”。

update admin_sm_org set org_spef_name=replace(org_spef_name,'郑州','天堂') where org_spef_name like '郑州%'

-----------------------创建表时时间默认为当前系统时间-------------------
create table RMFT_BATCH_RECORD{
    CREATE_TIME DATE default sysdate,
    STATUS NUMBER default 0
}

Oracle数据库中Date类型时间转字符串： to_char(sysdate ,'mm-MM-dd hh24:mi:mm')。与Java代码不一样。

---------------------------------system用户登录------------------------------

/*
oracle体系结构
1、数据库    database 
oracle 只有一个database  orcl

2、实例
一系列的后台进程和内存结构   orcl

3、数据文件
扩展名：dbf
4、表空间
是数据文件的一个逻辑映射文件

5、用户
scott  system   hr

scott用户是oracle提供给学习者练习的
*/

--scott介绍
--员工
select * from scott.emp;//scott用户的表emp。如果用Scott用户登录就可以直接emp表了。
--empno 员工编号
--ename 名称
--job 工作 职位
--mgr  上级领导
--hiredate 入职时间
--sal   工资
--deptno 部门编号
--部门表
select * from scott.dept;
--deptno 部门编号
--dname  部门名称
--loc    部门位置
--工资等级
select * from scott.salgrade;
--grade 等级
--losal 最低工资
--hisal 最高工资

--解锁scott用户（如果在安装的时候忘记“口令管理”解锁scott用户时使用）Scott是oracle第四个员工，前三个都是老板。他当时养了只猫叫tiger，所以密码也起了tiger。但密码过期。
alter user account scott unlock;

--重置scott用户的密码（用scott初次登录时需要重置密码，一定要执行一下语句）（这句sql意思是把scott用户的密码重置成tiger。）
alter  user  scott identified by tiger;

-------------------- 是以scott用户登录-------------------
---基本查询
select * from emp;
--效率高
select  empno , ename, job , mgr , hiredate, sal,comm,deptno from emp;

--别名的使用 4种方式（这三天中只有别名使用到双引号，其他都是单引号。）

select  empno as "员工编号" , ename  "名称", job 职位 , mgr as 上级领导 , hiredate, sal,comm,deptno from emp;

/*
 中文乱码问题解决
1.查看服务器端编码
select userenv('language') from dual;
我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK
2.执行语句 select * from V$NLS_PARAMETERS 
查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。
如果不是，需要设置环境变量.变量名必须是NLS_LANG，变量值就是查询出来的结果AMERICAN_AMERICA.ZHS16GBK
否则PLSQL客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码.
3.设置环境变量
计算机->属性->高级系统设置->环境变量->新建
设置变量名:NLS_LANG,变量值:第1步查到的值， 我的是    AMERICAN_AMERICA.ZHS16GBK
4.重新启动PLSQL后,插入数据正常

*/

--查询所有员工的职位 去重  distinct 关键字

select distinct job  from emp;

--连接符   ||   --是oracle独有的

--显示 员工编号是XXX，姓名是XXX，职位是XXX

select  '员工编号是'||empno||'，姓名是'||ename||'，职位是'||job  info from emp 

--四则运算   + - * /
select ename, sal, sal*12 from emp;

--------------带条件的查询和排序
select 列名 from  表名 where 条件   order by

--奖金为空的员工

select * from emp where comm is null

--奖金不为空的员工  not

select * from emp where comm is not null;

--奖金为空的员工并且工资大于1500

select * from emp where comm is null and sal>1500
--奖金为空的员工并且工资不大于1500

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

--奖金为空的员工或者工资大于1500

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

-- or  and  not  或与非

--查询工资大于1500并且小于3000的员工

select  * from emp where sal>=1500 and sal<=3000;

--包括临界值1500和3000
select  * from emp where sal between 1500 and 3000;

--以下是错误的写法（大小顺序不能写反了，反了没有结果。）
select  * from emp where sal between  3000 and  1500;
select  * from emp where sal>=3000 and sal<=1500;

--in  查询字段要和括号中的数据类型保持一致（只要这样，任何类型都行。）
--查询员工编号是 7369 7788 7654的员工
select  * from emp where empno in (7369 ,7788 ,7654);

select  * from emp where ename in ('SMITH' ,'MARTIN' ,'SCOTT');

-- 不等于
select  * from emp where empno <> 7369;
select  * from emp where empno != 7369;

--模糊查询  like  通配符 _   %
--查询姓名中第二个字母是M的员工

select * from emp where ename like '_M%';

select * from emp where ename like '%M%';（这个有结果）

select * from emp where ename like '%m%';（这个没结果，因为大写小写敏感。）

--注意：oracle查询中 大小写敏感

--姓名中带写划线的员工（那个Q是随便加的，&字符不行，别的都行。）
select  * from emp where ename like '%Q_%' escape 'Q';

---排序
order by
desc --倒序
asc --升序（默认）
--多字段排序用 逗号分隔（哪个条件在前，哪个优先。）（多字段做条件！！！排序，其中时间的顺序是按数值的大小，比方1987是大于1981的。）
select * from emp order by sal desc , hiredate desc

--按照奖金排序 倒序（加上nulls last，使奖金为空的放到结果后面。）
select * from emp order by comm  desc  nulls last;

select * from emp order by comm  asc  nulls first;（空的放到前面）

=========================分阶段：下面是单行函数========================================

---单行函数

--字符串函数
upper--转大写（MySQL中能直接用select  UPPER('asasass')，oracle不行。因为查询必须有from是99年数据库厂商开会制定的规范，但当时MySQL是行业好大，没遵从规范。目前oracle是老大。）

dual --oracle内置的  伪表
select  UPPER('asasass') from dual;（如果写成真实存在的表，则表中有多少条数据，结果便会有多少条。但我们只要一条。oracle提供了伪表dual配合查询用。）
select * from dual
lower--转小写
select  lower('AASAASAA') from dual;
select  ename, lower(ename) from emp;
initcap--首字母大写

select initcap('assaasas') from dual

concat  -- 相当于||  字符串连接。但concat只能连两个,mysql也能用。

select concat('aaaaa','bbbbb') ab from dual

length--长度
select length('aasd') from dual 

substr--截取函数 起始位置是0和1结果一样  --重点
select substr('qwerty',0,3) from dual;  qwe
select substr('qwerty',2,3) from dual;  wer

replace--替换函数                   --重点
select replace ('qqqwertyu','qqq','rrrrr') from dual

--数值函数
round  --四舍五入   --重点
select round(12.467) from dual  --12
select round(12.467,2) from dual  --12.47
select round(12.467,-1) from dual --10
select round(16.467,-1) from dual --20

trunc--截断函数
select trunc(12.467) from dual  --12
select trunc(12.469,2) from dual  --12.46
select trunc(12.467,-1) from dual --10
select trunc(16.467,-1) from dual --10

mod--取余
select mod(10,3) from dual; --1
select mod(10,2) from dual; --0

--------日期函数
日期-日期=数字  单位：天
日期+数字=日期
--当前时间  sysdate（MySQL是select now()就是取当前时间。）
select sysdate from dual;

--查询emp表中所有员工入职多少天了
select ename, hiredate, round((sysdate-hiredate)) days     from emp;

--查询emp表中所有员工入职多少周

select ename, hiredate, round((sysdate-hiredate)/7) weeks     from emp;

--查询emp表中所有员工入职多少月
--months_between--两个日期的月份差!!!!!!!!!
select ename, hiredate, months_between(sysdate,hiredate)     from emp;

-- add_months 时间 加月份（只加月，日不变。）
select  add_months(sysdate,2) from dual
select  add_months(sysdate,-1) from dual

--------------转换函数
to_char --转成字符串，数据库里所有字符串类型通用。  
--数字转字符串
select 12,to_char(12) from dual （结果一个数字12：靠右；一个字符串12：靠左。）
--日期转字符串   --重点
select  to_char(sysdate ,'yyyy-MM-dd') from dual 
select  to_char(sysdate ,'yyyy-MM-dd hh24:mi:ss') from dual （不加24就是12小时制）
非正常实验：select  to_char(sysdate ,'mm-MM-dd hh24:mi:mm') from dual 结果是05-05-07 01:14:05。得：①oracle不区分大小写。②固定的字符代表固定的意思，像mm代表几月，放哪个位置都可以出来月。
select  to_char(sysdate ,'yyyy') years,
to_char(sysdate ,'mm') monthss,
to_char(sysdate ,'dd') days,
to_char(sysdate ,'day') weeks --结果是英文的星期几。
 from dual

select to_char(8712267,'9,999,999') from dual --结果就是按千分符来分的：8,712,267。但9的个数必须与数字的长度匹配，9个数大于长度，结果前面会有多个空格。等于也有一个空格。小于结果就都是#了。
select to_char(8712267,'$9,999,999') from dual --表示美元。999是固定的，写成888无效。

select to_char(8712267,'L9,999,999') from dual --L是本地货币符号，但我们的环境是英文的，所以结果还是$.

select '￥'||to_char(8712267,'9,999,999') from dual --结果为人民币货币符号的写法：拼接。￥ 8,712,267

--to_number    转数字
select '13' ,to_number('13') from  dual
--to_date     --重点
select * from emp where hiredate
   between to_date('1981-09-09','yyyy-mm-dd') 
    and to_date('1989-09-09','yyyy-mm-dd')
（select * from emp where hiredate
   between '1981-09-09'
    and '1989-09-09'这种写法只是MySQL能用。还是1999年sql会议问题。）

-------------------通用函数
--nvl   --重点
--查询所有员工的年薪
--如果null参与运算结果恒为null
--nvl(不为空时取本值，为空时取的值）
select ename,sal,comm, sal*12+ nvl(comm,0) yearsal from emp

--  nvl2(表达式,表达式不为空时的值，为空时的值)
select nvl2(1,2,3) from dual  --2

select nvl2(null,2,3) from dual  --3

-- decode   --oracle独有   decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
--把emp中的职位job显示成中文
select empno, ename, job,
decode(job,
'CLERK','店员',
'SALESMAN','销售员',
'MANAGER','经理',
'ANALYST','分析师',
'PRESIDENT','总裁',
'其他'    --剩余值全显示成了其他。
)
 from emp

---------------条件表达式
case  字段  when ... then ... end  --重点
  
select empno,ename,
case job when 'CLERK' then '店员'
when 'SALESMAN' then '销售员'
else   '其他'
 end  job   from emp 

------ --多行函数（oracle叫法）    MySQL叫：聚合函数  组函数--------------
当行函数作用域是单行，结果也是单行。多行函数作用域是多行，结果是单行。
sum--加和
select sum(sal) from emp
count--总数
select count(*) from emp
select count(ename) from emp --这个比上面的*效率高。
select count(comm) from emp --这个不行，因为这列有null值，会自动跳过null值，null值不被计入。
select count(0) from emp --0只是个占位符，别的也行。但count(0)效率更高，比count(ename)效率也高。他自动去找索引列。索引列就是主键列。

max--最大值
select max(sal) from emp

min--最小值
select min(sal) from emp

select max(sal)- min(sal) from emp

avg--平均值

select round(avg(sal)) from emp --round给四舍五入了。

--分组查询 group by
--每个部门的平均工资
--按分类统计各类数量：select rule_id,count(rule_id) from manifest_hit group by rule_id;

select deptno, avg(sal) from emp group by deptno

select deptno, max(sal) from emp group by deptno

--------------------多表查询--------------
--查询部门表和员工表   --笛卡尔积
select  * from dept d , emp e --56    dept 4   emp 14
select  * from dept d ;
select  * from emp e;

select  * from dept d , emp e  where d.deptno=e.deptno

----- select 后面出现的物理列(实际表中存在的，不是算出来的。)，group by后面必须要出现。(sql规范，MySQL不需要这样.)所有条件完了才写group by.
select  d.deptno,d.dname,count(*) from dept d , emp e  where d.deptno=e.deptno
group by d.deptno,d.dname

select  d.deptno,d.dname,count(*) from dept d , emp e  where d.deptno=e.deptno
group by d.deptno,d.dname  having count(*)>5
having  和 where 区别
having只在分组时使用 having后面接的是组函数（having只是对前面sql的结果进行条件筛选，比方上面例子是对分组后的结果筛选count(*)大于5的。
where只是用来做物理列查询

（以下开始到外连接上，都少了一条数据，因为有一条没上级。这些sql把没上级的去掉了，而不是显示null或空。不符合要求，）
--范例：查询出每个员工的上级领导(员工编号，员工姓名，员工工资，领导编号，领导姓名，领导工资)
--自关联（数据在一张表里，我们可以当两张表来用。通过起别名。）
select e1.empno,e1.ename ,e1.sal,e2.empno,e2.ename,e2.sal from emp e1, --员工表
emp e2 --领导表
where e1.mgr=e2.empno

--范例: 在上一个例子的基础上查询该员工的部门名称
(员工编号，员工姓名，员工工资，部门名称，领导编号，领导姓名，领导工资)

select e1.empno,e1.ename ,e1.sal,d.dname,
e2.empno,e2.ename,e2.sal from
emp e1, --员工表
emp e2, --领导表
dept d
where e1.mgr=e2.empno
and e1.deptno=d.deptno

--范例：查询出每个员工编号，姓名，部门名称，
--工资等级和他的上级领导的姓名，工资等级
(员工编号，员工姓名，员工工资，工资等级，部门名称，领导编号，领导姓名，领导工资，工资等级)
select * from salgrade

select e1.empno,e1.ename ,e1.sal,s.grade,d.dname,
e2.empno,e2.ename,e2.sal  ,s1.grade from
emp e1, --员工表
emp e2, --领导表
dept d,
salgrade s, 
salgrade s1 
where e1.mgr=e2.empno
and e1.deptno=d.deptno
and e1.sal between s.losal and s.hisal
and e2.sal between s1.losal and s1.hisal

--------------------- 外连接-------------
--范例：查询出所有员工的上级领导

select * from emp e1 left join  emp e2 on e1.mgr=e2.empno
select e1.ename,e2.ename from emp e1 left join  emp e2 on e1.mgr=e2.empno
--(+)Oracle 独有的
e1全量表   e2非全量表（全量表在哪边就是什么连接，在左边就是左连接。）（全量表就是每条数据都有的，非全量表就是有n条数据是空的。）
select * from emp e1,emp e2 where  e1.mgr=e2.empno(+)-- 左连接（不写这个括号和加号，没有mgr上级的那条数据就不显示。）
select * from emp e2,emp e1 where  e1.mgr=e2.empno(+)-- 右连接（加号写在非全量表上。因为e1中有一条查询条件e1.mgr为空导致了结果中e2的整条都为空，e2为非全量。）

--范例：查询出所有的部门下的员工，要求把没有员工的部门也展示出来

select * from emp e , dept d where e.deptno(+)=d.deptno; （部门表d中有个部门下没有员工，结果中该部门对应的e表字段全为空。e为非全量。）

--作业：查询出所有的部门下的员工，要求把没有员工的部门也展示出来 left或者right join 写出来

--一定要掌握 left  join  或者  right join

--查询出7566号员工的所有下属（下属的下属也是下属。）
--递归（oracle独有的）
语法：
select * from 表名
start with 条件1
connect by prior 条件2;

select * from emp
start with empno=7566
connect by prior empno=mgr
--查询出7566号员工的所有领导
select * from emp
start with empno=7566
connect by prior mgr=empno;