oracle常用sql三连击——01

怼烎@ 2022-01-15 22:37 163阅读 0赞

***将admin\_sm\_org表中org\_spef\_name字段的值中包含的“郑州”字样全部修改为“天堂”。***

update admin\_sm\_org set org\_spef\_name=replace(org\_spef\_name,'郑州','天堂') where org\_spef\_name like '郑州%'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0d3htaW5n_size_16_color_FFFFFF_t_70][]

\-----------------------创建表时时间默认为当前系统时间-------------------  
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;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0d3htaW5n_size_16_color_FFFFFF_t_70]: /images/20220115/cd40ed022eee42a991c31b3ca403b3fc.png