oracle 常用查询语句

1、常用查询语句
--1.查看emp表的所有信息
select * from emp;

--2.查看有哪些部门，部门有哪些人，每个人的工作是什么
select dname,ename,job from emp,dept where emp.deptno=dept.deptno;
select dname,ename,job from emp,dept where emp.deptno=dept.deptno order by dname;

--3.给每个员工多发600元，看每个员工要发多少钱 ★
select sal+nvl(comm,0)+600 "月薪" from emp;

--4.给老板打印工资表(仅sal基本工资，老板不认识英文，除了名字)
select ename "姓名",job "职称",sal "工资" from emp;

--5.字符串连接
select ename||' annual salary is '||sal "Employee' Salary" from emp;

--6.emp表中有几个部门
select count(distinct deptno) 部门数 from emp;

--7.查询每个部门中的岗位有哪些 distinct dname,job作用于后面所有的列
select distinct dname,job from emp,dept where emp.deptno=dept.deptno;

--8.查找smith的薪水，工作，所在部门
select sal+nvl(comm,0),job,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';

--9.查找在82年1月23号入职的员工姓名、雇佣日期 --原题为1号但1号无记录改成23号
--方法1
select ename,hiredate from emp where hiredate = '23-1月-82';
--方法2:
--session当前会话有效 scott有资格
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
select ename,hiredate from emp where hiredate = '1982-01-23';
--方法3::
select ename,hiredate from emp where hiredate=to_date('1982-01-01','yyyy-mm-dd');

--10.查找薪水在2000-3000的雇员姓名和薪水
select ename,sal from emp where sal between 2000 and 3000;
select ename,sal from emp where sal>=2000 and sal<=3000;

--11.显示首字母为S的员工的姓名、雇用日期 %
/*
%:可以匹配任意长度的内容
"_":可以匹配一个长度的内容
*/
select ename,hiredate from emp where ename like 'S%';

--12.显示第3个字符为O的所有员工姓名和工资
select ename,sal from emp where ename like '__O%';

--13.查找没有绩效的员工姓名
/*
广义绩效工资又称绩效加薪、奖励工资也即绩效就是奖金comm
*/
select ename from emp where comm is null;

--14.查找员工姓名、工资、绩效、年收入
select ename,sal,comm,sal*12+nvl(comm,0) 年收入 from emp;

--15.查找员工岗位是SALESMAN,CLERK,MANAGER的员工信息
select * from emp where job in('SALESMAN','CLERK','MANAGER');

--16.查找员工岗位不是SALESMAN,CLERK,MANAGER的员工信息
select * from emp where job not in('SALESMAN','CLERK','MANAGER');

--看到此 2019年6月5日01:39:33

--17.按sal升序排列，如果有工资相同的，按姓名字符升序排列
select * from emp order by sal,ename;

--18.选择在部门30中的员工的所有信息
select * from emp where deptno=30;

--19.列出职位为(MANAGER)的员工编号、姓名
select empno,ename from emp where job='MANAGER';

--20.找出奖金高于工资的员工
select * from emp where comm>sal;

--21.找出每个员工奖金和工资的总和
select ename,sal+nvl(comm,0) from emp;

--22.找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
--或者
select * from emp where deptno=20 and job='CLERK' union select * from emp where deptno=10 and job='MANAGER';

--23.找出部门10中既不是经理，也不是普通员工，而且工资大于等于2000的员工
select * from emp where job not in('MANAGER','CLERK') and sal>=2000 and deptno=10;

--24.找出没有奖金的不同工作
select distinct(job) from emp where comm is null;

--25.找出没有奖金或者奖金低于500的员工
select * from emp where comm is null or comm<500;
--更方便
select * from emp where nvl(comm,0)<500;

--26.获得当前系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

2.单行函数
2.1字符型函数
1)upper 查询结果字符串全转大写
select coll from test;
select upper(coll) from test;

2)lower 查询结果字符串全转小写
select ename from emp;
select lower(ename) from emp;

3)initcap 每个单词的首字母大写
select coll from test;
select initcap(coll) from test;

4)concat 字符串拼接
select concat('hello',' world') from dual;
--输出:hello

--语句输出存字符的方法
--隐藏列标题
set heading off
select '************当前用户下的表************' from dual;
set heading on

5)substr 字符串截取
--substr(s,i,[j])从字符串s第i位开始截取j个子字符串，省略j则一直截取到s末尾 (下标1开始写0等价于写1)
select substr('helloworld',6) from dual;
--上行输出world
select substr('helloworld',6,3) from dual;
--上行输出wor

6)length 字符串长度
select length('hello') from dual;
--输出5

7)replace(s1,s2,[s3]),用字符串s3替换在s1中出现的所有字符串s2.默认s3为空字符串，即起删除作用
select replace('helloworld','low') from dual;
--输出helorld
select replace('helloworld','low','XXX') from dual;
--输出helXXXorld

8)instr instr(s1,s2[,i][,j]) s1,s2字符串 i,j整数
--返回字符串s2在字符串s1中第j次出现的位置，s1中搜索起点i,
--i为负数则搜索从右向左进行，但返回的位置还是按从左到右计算
select instr('XXXhelXXXhelXXXhel','hel',4,2) from dual;
--输出10
select instr('XXXhelXXXhelXXXhel','hel',5,2) from dual;
--输出16
select instr('XXXhelXXXhelXXXhel','hel',-5,2) from dual;
--从右往左搜索输出4
select instr('XXXhelXXXhelXXXhel','hel',-5,3) from dual;
--无返回0

2.2数字型函数
1).round(num,n) 四舍五入数字num保留n位小数 (n默认值0 n为负数小数点前n位的数字是精确的即可)
select round(3.14159) from dual;
--输出:3
select round(3.14159,4) from dual;
--输出:3.1416
select round(333.14159,-1) from dual;
--输出:330
select round(335.14159,-1) from dual;
--输出:340

2).trunc(num,n) 作用同round 不过不四舍五入，只是去尾
select trunc(3.14159) from dual;
--输出:3
select trunc(3.14159,4) from dual;
--输出:3.1415
select trunc(333.14159,-1) from dual;
--输出:330
select trunc(339.14159,-1) from dual;
--输出:330

3).mod(m,n) 返回m%n 取余
select mod(8,4) from dual;
--输出:0
select mod(8,3) from dual;
--输出:2

2.3日期型函数
1).months_between(d1,d2) 返回d1,d2之间的月数 oracle里天转月时，除以基数为31
--日期类型默认 '08-9月 -81'
select months_between(to_date('2019-4-29','yyyy-mm-dd'),to_date('2019-4-30','yyyy-mm-dd')) from dual;
-- -.03225806 解释:d1<d2 返回负 d1与d2相差不到一个月，就一天。。1/31月 1/31=03225806
select months_between(to_date('2019-5-29','yyyy-mm-dd'),to_date('2019-4-29','yyyy-mm-dd')) from dual;
--输出:1 正好相差一个月 d1>d2 输出正数

2).add_months(d,i) 返回日期d加上i个月之后的结果
select add_months(to_date('2019-5-29','yyyy-mm-dd'),5) from dual;
--输出:29-10月-19
select add_months(sysdate,5) from dual;
--输出:29-9月 -19

补:sysdate 系统时间
select sysdate from dual;
--输出:29-4月 -19

3).next_day(date,char) 返回日期date开始的下一个星期char的日期
-- date参数为日期型，
-- char:为1~7或Monday/Mon~Sunday/ 1星期天 2星期1 ... 7星期6
--比如今天星期1
select next_day(sysdate,2) from dual;
--输出:06-5月 -19 而下个星期1也确实是5月6号
--日期格式恶心死了，还是初始化时改下吧
select next_day('2019-04-29','MONDAY') from dual;
--周中的日无效数据库语言中文导致改成下面写法即可
select next_day('2019-04-29','星期一') from dual;
-- 输出:2019-05-06

4).last_day(d) 返回日期d所在月的最后一天
--不写04也行 ^_^
select last_day('2019-4-5') from dual;
--输出:2019-04-30

5).systime
-- select systime from dual;
-- oracle 似乎并没有systime 获取时间可以这样
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--年
select to_char(sysdate, 'yyyy' ) from dual;
--月
select to_char(sysdate, 'MM' ) from dual;
--日
select to_char(sysdate, 'dd' ) from dual;
--季
select to_char(sysdate, 'Q') from dual;
--周--按日历上的那种，每年有52或者53周
select to_char(sysdate, 'iw') from dual;

6) systimestamp 得到当前详细日期时间信息
select systimestamp from dual;
--输出:29-4月 -19 10.45.11.100000 上午 +08:00
select to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF9') from dual;
--输出:2019-04-29 10:46:48.808000000

7). current_timestamp
select current_timestamp from dual;
--输出:29-4月 -19 10.48.51.733000 上午 +08:00
--设置完时区再执行
alter session set time_zone='UTC';
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS') from dual;
--输出:2019-04-29 02:51:32
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
--输出:2019-04-29 10:52:14 说明修改时区不会影响 sysdate的执行

2.4转换函数
1).to_char(date,'format') 日期转换为指定的格式
select to_char(sysdate,'yyyy-mm-dd') from dual;
--输出:2019-04-29

2).to_char(数字,'format') 数字转换为字符串
select to_char(25436.78,'$999,999.99') from dual;
--输出:$25,436.78

3).to_date(字符串,['fmt']) 字符串转日期
select to_date('05-06---2019','mm-dd---yyyy') from dual;
--输出:2019-05-06
--前面第9题
select ename,hiredate from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');

3.分组函数
3.1 count 计数
--1.公司中有多少员工
select count(empno) from emp;
--2.公司中有多少员工由经理管理
select count(e1.empno) from emp e1,emp e2 where e1.mgr=e2.empno and e2.job='MANAGER';
-3.查看部门号为10的员工数
select count(empno) from emp where deptno=10;
--4.查看有多少个岗位
select count(distinct job) from emp;

3.2 avg 求平均值函数
--1.查看公司的平均工资
select avg(sal) from emp;
--2.查看公司每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

3.3 sum 求和
--查看这个月工人的工资支出
select sum(sal) from emp;

3.4 min
--查看公司的最少工资
select min(sal) from emp;

3.5 max
--1.查看公司最高的工资
select max(sal) from emp;

--2.查找雇佣第一员工和最迟雇佣员工
select ename,hiredate from emp where hiredate in(select max(hiredate) from emp union select min(hiredate) from emp);

3.6 group by一般与分组函数一起使用
--查看公司每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

3.7 分组函数嵌套
--1.查找工作不是president的员工中按工作分类不同工作的最低平均工资和最高平均工资
select max(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job;
select min(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job;

select job 工作,avg(sal) 平均工资 from emp where job != 'PRESIDENT' group by job having avg(sal) in(
   select max(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job
   union
   select min(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job
);

--方法二，不输出工作名称
select max(avg(sal)) 最高平均工资,min(avg(sal)) 最低平均工资 from emp where job != 'PRESIDENT' group by job;

--2. 查出高于本部门员工平均工资的员工信息
--各部门平均工资
select deptno,avg(sal) from emp group by deptno;
--大于本部门平均工资
select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno) order by e1.deptno;

4.多表查询
4.1 相等连接
--1.查询每个员工所属部门和所在的具体地点
select ename 员工,dname 部门,loc 部门地址 from emp,dept where emp.deptno=dept.deptno;

--2.工资为1600元或者以上的员工所属的部门和地点
select ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno and sal>=1600;

4.2 自连接
--1. 每个分析员及上司的姓名 (注意分析员只是众多职业其中之一 ANALYST)
select e1.ename 分析员,e2.ename 上级 from emp e1,emp e2 where e1.mgr=e2.empno and e1.job='ANALYST';

4.3不等连接
--1.查询显示工资级别在3-5级之间的所有员工
--最低
select losal from salgrade where grade=3;
--最高
select hisal from salgrade where grade=5;
--查询
select * from emp where sal between (select losal from salgrade where grade=3) and (select hisal from salgrade where grade=5);

--2.查询员工姓名、工资、工资级别
select ename,sal,grade from emp,salgrade where sal>=losal and sal<=hisal;

4.4 外连接
内连接:★
格式1:显式的内连接
select a.*,b.* from a [inner] join b on ab的连接条件
格式2:隐式的内连接
select a.*,b.* from a,b where ab的连接条件

外连接:★
左外连接:★
select a.*,b.* from a left [outer] join b on 连接条件;
意思:
先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以空白展示.
右外连接:
select a.*,b.* from b right [outer] join a on 连接条件;
意思:
先展示join右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以空白展示.

--on子句
--1.查询员工编号、员工姓名、工资、部门号、部门地址 e.deptno为。。
select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e join dept d on (e.deptno=d.deptno) order by loc;
--14行无空白

--左外连接
-- 2.同上左外连接dept为主
select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno);
--15行有空白
--先展示dept表的所有信息然后根据条件展示右边emp表的信息，没有的null, 反正join左边表的每条记录至少打印一行(都要展示)
-- 而40号部门正好没人。所以有一空行

--右外连接
--3.同上同上右外连接dept为主
select emp.empno,emp.ename,dept.deptno,dept.loc from emp right outer join dept on (emp.deptno=dept.deptno);
--15行有空白
--先展示join右边表dept的所有记录同样40号部门没人也要打印，对应的emp表内容为空白

5.子查询一个查询依赖另一个查询.
5.1 单行子查询
--1. 查询与smith相同职位的人
select * from emp where job =(select job from emp where ename='SMITH') and ename != 'SMITH';

--2.查询职位与'SMITH'相同，而工资不超过ADAMS的员工
select * from emp where job =(select job from emp where ename='SMITH') and ename != 'SMITH'
and sal<=(select sal from emp where ename='ADAMS');

--3.查询最高工资的员工姓名、岗位、工资
select ename,job,sal from emp where sal=(select max(sal) from emp);

--4.查询高于本部门员工平均工资的员工姓名、部门号、工资、部门平均工资 ★★
--强大的临时表 select deptno,avg(sal) avgsal from emp group by deptno 查询结果作为临时表来与emp进行连接查询
col avgsal format 9999.00;
select ename,e1.deptno,sal,avgsal
from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2
where e1.deptno=e2.deptno and sal>avgsal order by deptno;

5.2多行子查询

1).使用in操作符的多行子查询:
--eg:查询每个部门工资最高的员工姓名、工作、工资(部门最高工资不重复时逻辑才对，否则就是工资等于某个部门最高工资的员工信息)
select ename,job,sal from emp where sal in (select max(sal) from emp group by job);

--查询与部门号10相同的工作
--本意
select distinct job from emp where job in (select job from emp where deptno=10);
--其实
select distinct job from emp where deptno=10;

2).使用all操作符的多行子查询
--查询比所有10号部门员工工资低的员工的姓名、工作、工资(两种方法) 修改原题
select ename,job,sal from emp where sal <(select min(sal) from emp where deptno=10);
select ename,job,sal from emp where sal < all(select sal from emp where deptno=10);

3).使用any操作符的多行子查询
--查询比任意部门平均工资高的员工姓名、工作、薪水（两种方法）修改原题
select ename,job,sal from emp where sal >(select min(avg(sal)) from emp group by deptno);
select ename,job,sal from emp where sal > any(select avg(sal) from emp group by deptno);

4).多列子查询
--1.查询与smith工作、部门相同的员工
select * from emp where ename!='SMITH' and job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');

--2.如何显示高于自己部门平均工资的员工信息
select * from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno);

5.3 其他语句中使用子查询
1).建表语句中使用子查询
--复制查询结果到新表
create table mytable(id,name,sal) as (select empno,ename,sal from emp);
--table 不可省略
drop table mytable;
-- 完全复制 20号部门的信息全部复制过去
create table mytable as select * from emp where deptno=20;

--可以用此来备份表或者新建一个备份表

--只是复制表结构 where后条件为假即可
create table myemp as select * from emp where 1=2;

2).insert语句中使用子查询
--建立新表 --最后一行的,不能写必须省略
create table employee(
   name varchar2(10),
   sal number(7,2),
   job varchar2(10)
);
--插入数据将查询结果批量插入没有 as 也不能有as DDL语句才有as
insert into employee select ename,sal,job from emp where deptno=10;

3).删除语句中使用子查询
--删除工资大于工作为'CLERK'(该工资唯一)的员工信息
delete from employee where sal > (select sal from employee where job='CLERK');

4).update语句中使用子查询
update employee set sal=(select sal from employee where job ='CLERK');

6.dml(数据操作语言)
DML:数据操作语言
操作对象:记录

1）insert
--1)完全插入指明要插入的具体字段值
insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT','DETROIT');

--不指明具体字段，则按序每个字段都要插入一个值用to_date函数指明日期格式，这样无论系统默认日期格式如何，都不会报错
insert into emp values(2296,'AROMANO','SALESMAN',7782,to_date('03-2月 1997'，'DD-MON,YYYY'),1300,null,10);

-- 2) 省略字段值
-- dept表共三个字段 DEPTNO DNAME LOC 仅主键DEPTNO不允许为空
--省略方法：列举列名，忽略有空值的列
insert into dept(deptno,dname) values(70,'FINANCE');
--省略列名用null填充
insert into dept values(60,'MIS',null);

2)update
--改现有行
--更改7698号雇员的工作和部门，与第7499号雇员改为相同
--先备份
--7698 BLAKE MANAGER 7839 1981-05-01 2850 30
--多个列值中间必须，不能是and
--update emp set job='MANAGER',deptno=30 where empno=7698;

--update set语句可以列对应赋值
update emp set (job,deptno)=(select job,deptno from emp where empno=7499) where empno=7698;

3)delete
--删除所有职位为salsman的员工信息，并练习rollback,commit
--先备份
create table salsEmp as select * from emp where job='SALESMAN';
--后期还原
-- insert into emp select * from salsEmp;

--开始删除
delete emp where job='SALESMAN';
--查询就剩10行了 SALESMAN job的全没了
-- 此时 rollback
rollback
--再次查询job为SALESMAN的又恢复了

delete emp where job='SALESMAN';
commit;

--还原
insert into emp select * from salsEmp;

--日期格式查询
select t.rowid,t.* from TI_SP_RUNSHEET_JIT t where t.updatedate between to_date('2021-09-23,00:00:00','yyyy-mm-dd,hh24:mi:ss') and to_date('2021-09-23,23:00:00','yyyy-mm-dd,hh24:mi:ss')

--查看问题-case when
select
distinct '0' extendFields,
t.tuid as tuid,
t.issueNo,
t.issueDescription,
t.issueTypeId,
t.creator,
to_char(t.createdate,'yyyy-MM-dd HH24:mi:ss') createdate,
(case when t.issuestatus !=-1 then w.nodename
else '关闭' end) as nodename,
to_char(t.issueStatus) issueStatus,
u.realname,
(case when t.issuestatus !=-1 then '0'
else null end) as approval,
null labels,
null ext
FROM
tb_rt_issue t
left join tb_user u on t.creator = u.useraccount
left join tb_workflow_node w ON w.tuid = t.currentworkflownodeid
where t.issuetypeid='20220408-103839-9116-73227'
and ((t.issuestatus=0 and t.creator='eis_autotest') or t.issuestatus != 0)
and t.issuedescription like '%AutoTest%'
order by t.tuid desc；

posted on 2023-04-03 17:56 波音666 阅读(370) 评论(0) 编辑收藏举报

刷新页面返回顶部

oracle 常用查询语句

导航

公告