devbox
不正经
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

oracle 基本sql语句合集_oracle sql语句

作者：不正经 | 2024-04-14 10:03:41

赞

踩

oracle sql语句

目录

1、常用查询语句

2.单行函数

2.1字符型函数

2.2数字型函数

2.3日期型函数

2.4转换函数

3.分组函数

         3.1 count

3.2 avg 求平均值函数

3.3 sum 求和

3.4 min

3.5 max

3.6 group by一般与分组函数一起使用

3.7 分组函数嵌套

4.多表查询

4.1 相等连接

4.2 自连接

4.3不等连接

4.4 外连接

5.子查询   一个查询依赖另一个查询.

5.1 单行子查询

5.2多行子查询

1).使用in操作符的多行子查询:

2).使用all操作符的多行子查询   

         3).使用any操作符的多行子查询

4).多列子查询

5.3 其他语句中使用子查询

1).建表语句中使用子查询

2).insert语句中使用子查询

3).删除语句中使用子查询  

4).update语句中使用子查询

6.dml(数据操作语言)

1）insert

2)update

3)delete

代码版（可以直接赋值粘贴到sqlplus下运行）

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;


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;
--查询 记录删除了
rollback;--无论怎么rollback记录都没有了。。。  还好提前有备份


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

代码版（可以直接赋值粘贴到sqlplus下运行）

  1. ​
  2. --1.查看emp表的所有信息
  3. select * from emp;
  4.  
  5. --2.查看有哪些部门，部门有哪些人，每个人的工作是什么
  6. select dname,ename,job from emp,dept where emp.deptno=dept.deptno;
  7.  
  8. --3.给每个员工多发600元，看每个员工要发多少钱   ★ 
  9. select sal+nvl(comm,0)+600 "月薪"  from emp;
  10.  
  11. --4.给老板打印工资表(仅sal基本工资，老板不认识英文，除了名字)
  12. select ename "姓名",job "职称",sal "工资" from emp;
  13.  
  14. --5.字符串连接
  15. select ename||' annual salary is '||sal "Employee' Salary" from emp;
  16.  
  17. --6.emp表中有几个部门
  18. select count(distinct deptno) 部门数  from emp;
  19.  
  20. --7.查询每个部门中的岗位有哪些  distinct dname,job作用于后面所有的列
  21. select distinct dname,job from emp,dept where emp.deptno=dept.deptno;
  22.  
  23. --8.查找smith的薪水，工作，所在部门
  24. select sal+nvl(comm,0),job,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';
  25.  
  26. --9.查找在82年1月23号入职的员工姓名、雇佣日期  --原题为1号 但1号无记录改成23号
  27. --方法1
  28. select ename,hiredate from emp where hiredate = '23-1月-82';
  29. --方法2:
  30. --session当前会话有效 scott有资格
  31. alter session set NLS_DATE_FORMAT='yyyy-mm-dd'; 
  32. select ename,hiredate from emp where hiredate = '1982-01-23';
  33. --方法3::
  34. select ename,hiredate from emp where hiredate=to_date('1982-01-01','yyyy-mm-dd');
  35.  
  36. --10.查找薪水在2000-3000的雇员姓名和薪水
  37. select ename,sal from emp where sal between 2000 and 3000;
  38. select ename,sal from emp where sal>=2000 and sal<=3000;
  39.  
  40. --11.显示首字母为S的员工的姓名、雇用日期  %
  41. /*
  42. %:可以匹配任意长度的内容
  43. "_":可以匹配一个长度的内容
  44. */
  45. select ename,hiredate from emp where ename like 'S%';
  46.  
  47. --12.显示第3个字符为O的所有员工姓名和工资
  48. select ename,sal from emp where ename like '__O%';
  49.  
  50. --13.查找没有绩效的员工姓名
  51. /*
  52. 广义绩效工资又称绩效加薪、奖励工资  也即绩效就是奖金comm
  53. */
  54. select ename from emp where comm is null;
  55.  
  56. --14.查找员工姓名、工资、绩效、年收入
  57. select ename,sal,comm,sal*12+nvl(comm,0) 年收入 from emp;
  58.  
  59. --15.查找员工岗位是SALESMAN,CLERK,MANAGER的员工信息
  60. select * from emp where job in('SALESMAN','CLERK','MANAGER');
  61.  
  62. --16.查找员工岗位不是SALESMAN,CLERK,MANAGER的员工信息
  63. select * from emp where job not in('SALESMAN','CLERK','MANAGER');
  64.  
  65. --17.按sal升序排列，如果有工资相同的，按姓名字符升序排列
  66. select * from emp order by sal,ename;
  67.  
  68. --18.选择在部门30中的员工的所有信息
  69. select * from emp where deptno=30;
  70.  
  71. --19.列出职位为(MANAGER)的员工编号、姓名
  72. select empno,ename from emp where job='MANAGER';
  73.  
  74. --20.找出奖金高于工资的员工
  75. select * from emp where comm>sal;
  76.  
  77. --21.找出每个员工奖金和工资的总和
  78. select ename,sal+nvl(comm,0) from emp;
  79.  
  80. --22.找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
  81. select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
  82. --或者
  83. select * from emp where deptno=20 and job='CLERK' union select * from emp where deptno=10 and job='MANAGER';
  84.  
  85. --23.找出部门10中既不是经理，也不是普通员工，而且工资大于等于2000的员工
  86. select * from emp where job not in('MANAGER','CLERK') and sal>=2000 and deptno=10;
  87.  
  88. --24.找出没有奖金的不同工作
  89. select distinct(job) from emp where comm is null;
  90.  
  91. --25.找出没有奖金或者奖金低于500的员工
  92. select * from emp where comm is null or comm<500;
  93. --更方便
  94. select * from emp where nvl(comm,0)<500;
  95.  
  96.  
  97.  
  98.  
  99. --2.单行函数
  100. --2.1字符型函数
  101. --1)upper 查询结果字符串全转大写
  102. select coll from test;
  103. select upper(coll) from test;
  104. --2)lower 查询结果字符串全转小写
  105. select ename from emp;
  106. select lower(ename) from emp;
  107. --3)initcap 每个单词的首字母大写
  108. select coll from test;
  109. select initcap(coll) from test;
  110. --4)concat 字符串拼接
  111. select concat('hello',' world') from dual;
  112. --输出:hello
  113.  
  114. --语句输出存字符的方法
  115. --隐藏列标题
  116. set heading off
  117. select '************当前用户下的表************' from dual;
  118. set heading on
  119. --5)substr 字符串截取
  120. --substr(s,i,[j])从字符串s第i位开始截取j个子字符串，省略j则一直截取到s末尾   (下标1开始 写0等价于写1)
  121. select substr('helloworld',6) from dual;
  122. --上行输出world
  123. select substr('helloworld',6,3) from dual;
  124. --上行输出wor
  125.  
  126. --6)length 字符串长度
  127. select length('hello') from dual;
  128. --输出5 
  129.  
  130. --7)replace(s1,s2,[s3]),用字符串s3替换在s1中出现的所有字符串s2.默认s3为空字符串，即起删除作用
  131. select replace('helloworld','low') from dual;
  132. --输出helorld
  133. select replace('helloworld','low','XXX') from dual;
  134. --输出helXXXorld
  135.  
  136. --8)instr instr(s1,s2[,i][,j]) s1,s2字符串 i,j整数 
  137. --返回字符串s2在字符串s1中第j次出现的位置，s1中搜索起点i,
  138. --i为负数则搜索从右向左进行，但返回的位置还是按从左到右计算
  139. select instr('XXXhelXXXhelXXXhel','hel',4,2) from dual;
  140. --输出10
  141. select instr('XXXhelXXXhelXXXhel','hel',5,2) from dual;
  142. --输出16
  143. select instr('XXXhelXXXhelXXXhel','hel',-5,2) from dual;
  144. --从右往左搜索  输出4
  145. select instr('XXXhelXXXhelXXXhel','hel',-5,3) from dual;
  146. --无 返回0
  147.  
  148.  
  149. --2.2数字型函数
  150. --9.round(num,n)  四舍五入数字num保留n位小数  (n默认值0 n为负数 小数点前n位的数字是精确的即可) 
  151. select round(3.14159) from dual;
  152. --输出:3
  153. select round(3.14159,4) from dual;
  154. --输出:3.1416
  155. select round(333.14159,-1) from dual;
  156. --输出:330
  157. select round(335.14159,-1) from dual;
  158. --输出:340
  159.  
  160. --10.trunc(num,n) 作用同round 不过不四舍五入，只是去尾
  161. select trunc(3.14159) from dual;
  162. --输出:3
  163. select trunc(3.14159,4) from dual;
  164. --输出:3.1415
  165. select trunc(333.14159,-1) from dual;
  166. --输出:330
  167. select trunc(339.14159,-1) from dual;
  168. --输出:330
  169.  
  170. --11.mod(m,n) 返回m%n  取余
  171. select mod(8,4) from dual;
  172. --输出:0
  173. select mod(8,3) from dual;
  174. --输出:2
  175.  
  176.  
  177. --2.3日期型函数
  178. --12.months_between(d1,d2) 返回d1,d2之间的月数    oracle里天转月时，除以基数为31
  179. --日期类型默认  '08-9月 -81'
  180. select months_between(to_date('2019-4-29','yyyy-mm-dd'),to_date('2019-4-30','yyyy-mm-dd')) from dual;
  181. -- -.03225806   解释:d1<d2 返回负  d1与d2相差不到一个月，就一天。。1/31月 1/31=03225806
  182. select months_between(to_date('2019-5-29','yyyy-mm-dd'),to_date('2019-4-29','yyyy-mm-dd')) from dual;
  183. --输出:1  正好相差一个月  d1>d2 输出正数
  184.  
  185. --13.add_months(d,i) 返回日期d加上i个月之后的结果
  186. select add_months(to_date('2019-5-29','yyyy-mm-dd'),5) from dual;
  187. --输出:29-10月-19
  188. select add_months(sysdate,5) from dual;
  189. --输出:29-9月 -19
  190.  
  191. --13.1补:sysdate 系统时间
  192. select sysdate from dual;
  193. --输出:29-4月 -19
  194.  
  195. --14.next_day(date,char) 返回日期date开始的下一个星期char的日期
  196. -- date参数为日期型， 
  197. -- char:为1~7或Monday/Mon~Sunday/   1星期天  2星期1 ... 7星期6
  198. --比如今天星期1
  199. select next_day(sysdate,2) from dual;
  200. --输出:06-5月 -19    而下个星期1也确实是5月6号
  201. --日期格式恶心死了，还是初始化时改下吧  
  202. select next_day('2019-04-29','MONDAY') from dual;
  203. --周中的日无效 数据库语言中文导致  改成下面写法即可
  204. select next_day('2019-04-29','星期一') from dual;
  205. -- 输出:2019-05-06
  206.  
  207. --15.last_day(d) 返回日期d所在月的最后一天
  208. --不写04也行 ^_^
  209. select last_day('2019-4-5') from dual;
  210. --输出:2019-04-30
  211.  
  212. -- 16.systime 
  213. -- select systime from dual;
  214. -- oracle 似乎并没有systime 获取时间可以这样
  215. select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
  216. --年
  217. select  to_char(sysdate, 'yyyy' )  from dual; 
  218. --月 
  219. select  to_char(sysdate, 'MM' )  from dual; 
  220. --日
  221. select  to_char(sysdate, 'dd' )  from dual;  
  222. --季
  223. select  to_char(sysdate, 'Q')  from dual;  
  224. --周--按日历上的那种，每年有52或者53周 
  225. select  to_char(sysdate, 'iw')  from dual; 
  226.  
  227.  
  228. --17 systimestamp  得到当前详细日期时间信息
  229. select systimestamp from dual;
  230. --输出:29-4月 -19 10.45.11.100000 上午 +08:00  
  231. select to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF9') from dual;
  232. --输出:2019-04-29 10:46:48.808000000
  233.  
  234. --18. current_timestamp
  235. select current_timestamp from dual;
  236. --输出:29-4月 -19 10.48.51.733000 上午 +08:00
  237. --设置完时区再执行
  238. alter session set time_zone='UTC';
  239. select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS') from dual;
  240. --输出:2019-04-29 02:51:32
  241. select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
  242. --输出:2019-04-29 10:52:14    说明修改时区不会影响  sysdate的执行
  243.  
  244.  
  245. --2.4转换函数
  246. -- 19.to_char(date,'format') 日期转换为指定的格式 
  247. select to_char(sysdate,'yyyy-mm-dd') from dual;
  248. --输出:2019-04-29
  249.  
  250. --20.to_char(数字,'format') 数字转换为字符串
  251. select to_char(25436.78,'$999,999.99') from dual;
  252. --输出:$25,436.78
  253.  
  254. --21.to_date(字符串,['fmt'])  字符串转日期
  255. select to_date('05-06---2019','mm-dd---yyyy') from dual;
  256. --输出:2019-05-06
  257. --前面第9题
  258. select ename,hiredate from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');
  259.  
  260.  
  261. -- 3.分组函数
  262. --3.1 count: select count(*) from emp;
  263. --22.公司中有多少员工
  264. select count(empno) from emp;
  265. --23.公司中有多少员工由经理管理
  266. select count(e1.empno) from emp e1,emp e2 where e1.mgr=e2.empno and e2.job='MANAGER';  
  267. --24.查看部门号为10的员工数
  268. select count(empno) from emp where deptno=10;
  269. --25.查看有多少个岗位
  270. select count(distinct job) from emp;
  271.  
  272. --3.2 avg 求平均值函数
  273. --26.查看公司的平均工资
  274. select avg(sal) from emp;
  275. --27.查看公司每个部门的平均工资
  276. select deptno,avg(sal) from emp group by deptno;
  277.  
  278.  
  279. --3.3 sum 求和
  280. --28.查看这个月工人的工资支出
  281. select sum(sal) from emp;
  282.  
  283. --3.4 min
  284. --29.查看公司的最少工资
  285. select min(sal) from emp;
  286.  
  287. --3.5 max
  288. --30.查看公司最高的工资
  289. select max(sal) from emp;
  290.  
  291. --31.查找雇佣第一员工和最迟雇佣员工
  292. select ename,hiredate from emp where hiredate in(select max(hiredate) from emp union select min(hiredate) from emp);
  293.  
  294.  
  295. --3.6 group by一般与分组函数一起使用
  296. --见前面例27
  297.  
  298. --3.7 分组函数嵌套
  299. --32.查找工作不是president的员工中按工作分类不同工作的最低平均工资和最高平均工资
  300. select max(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job;
  301. select min(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job;
  302.  
  303. select job 工作,avg(sal) 平均工资 from emp where job != 'PRESIDENT' group by job having avg(sal) in(
  304. 	select max(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job
  305. 	union
  306. 	select min(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job
  307. );
  308.  
  309. --方法二，不输出工作名称
  310. select max(avg(sal)) 最高平均工资,min(avg(sal)) 最低平均工资 from emp where job != 'PRESIDENT' group by job;
  311.  
  312. --33. 查出高于本部门员工平均工资的员工信息
  313. --各部门平均工资
  314. select deptno,avg(sal) from emp  group by deptno;
  315. --大于本部门平均工资
  316. select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno) order by e1.deptno;
  317.  
  318. --4.多表查询
  319. --4.1 相等连接
  320. --34.查询每个员工所属部门和所在的具体地点
  321. select ename 员工,dname 部门,loc 部门地址 from emp,dept where emp.deptno=dept.deptno;
  322.  
  323. --35.工资为1600元或者以上的员工所属的部门和地点
  324. select ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno and sal>=1600;
  325.  
  326. --4.2 自连接
  327. --36. 每个分析员及上司的姓名  (注意分析员只是众多职业其中之一 ANALYST)
  328. select e1.ename 分析员,e2.ename 上级 from emp e1,emp e2 where e1.mgr=e2.empno and e1.job='ANALYST';
  329.  
  330. --4.3不等连接
  331. --37.查询显示工资级别在3-5级之间的所有员工
  332. --最低
  333. select losal from salgrade where grade=3;
  334. --最高
  335. select hisal from salgrade where grade=5;
  336. --查询
  337. select * from emp where sal between (select losal from salgrade where grade=3) and (select hisal from salgrade where grade=5);
  338.  
  339. --38.查询员工姓名、工资、工资级别
  340. select ename,sal,grade from emp,salgrade where sal>=losal and sal<=hisal;
  341.  
  342. --4.4 外连接
  343. -- 内连接:★
  344. -- 	格式1:显式的内连接
  345. -- 		select a.*,b.* from a [inner] join b on ab的连接条件
  346. -- 	格式2:隐式的内连接
  347. -- 		select a.*,b.* from a,b where ab的连接条件
  348.  
  349. -- 外连接:★
  350. -- 	左外连接:★
  351. -- 		select a.*,b.* from a left [outer] join b on 连接条件;
  352. -- 		意思:
  353. -- 			先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以空白展示.
  354. -- 	右外连接:
  355. -- 		select a.*,b.* from b right [outer] join a on 连接条件;
  356. -- 		意思:
  357. -- 			先展示join右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以空白展示.
  358.  
  359. --on子句
  360. --40.查询员工编号、员工姓名、工资、部门号、部门地址   e.deptno为。。
  361. 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;
  362. --14行无空白
  363.  
  364. --左外连接
  365. -- 41.同上  左外连接dept为主
  366. select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno);
  367. --15行有空白
  368. --先展示dept表的所有信息 然后根据条件展示右边emp表的信息，没有的null,  反正join左边表的每条记录至少打印一行(都要展示)
  369. -- 而40号部门正好没人。所以有一空行
  370.  
  371. --右外连接
  372. --42.同上 同上  右外连接dept为主
  373. select emp.empno,emp.ename,dept.deptno,dept.loc from emp right outer join dept on (emp.deptno=dept.deptno);
  374. --15行有空白
  375. --先展示join右边表dept的所有记录  同样40号部门没人也要打印，对应的emp表内容为空白
  376.  
  377. -- 5.子查询   一个查询依赖另一个查询.
  378. --5.1 单行子查询
  379. --43. 查询与smith相同职位的人
  380. select * from emp where job =(select job from emp where ename='SMITH') and ename != 'SMITH';
  381.  
  382. --44.查询职位与'SMITH'相同，而工资不超过ADAMS的员工
  383. select * from emp where job =(select job from emp where ename='SMITH') and ename != 'SMITH' 
  384. and sal<=(select sal from emp where ename='ADAMS');
  385.  
  386. --45.查询最高工资的员工姓名、岗位、工资
  387. select ename,job,sal from emp where sal=(select max(sal) from emp);
  388.  
  389. --45_2 查询高于本部门员工平均工资的员工姓名、部门号、工资、部门平均工资
  390. --强大的临时表  select deptno,avg(sal) avgsal from emp group by deptno  查询结果作为临时表来与emp进行连接查询
  391. col avgsal format 9999.00;
  392. select ename,e1.deptno,sal,avgsal 
  393. 	from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2
  394. 	where e1.deptno=e2.deptno and sal>avgsal order by deptno;
  395.  
  396. --5.2多行子查询
  397. --1.使用in操作符的多行子查询:
  398. --eg:查询每个部门工资最高的员工姓名、工作、工资
  399. select ename,job,sal from emp where sal in (select max(sal) from emp group by job);
  400.  
  401. --46 查询与部门号10相同的工作
  402. --本意
  403. select distinct job from emp where job in (select job from emp where deptno=10);
  404. --其实
  405. select distinct job from emp where deptno=10;
  406.  
  407. -- 2.使用all操作符的多行子查询   
  408. --47.查询比所有10号部门员工工资低的员工的姓名、工作、工资(两种方法)  修改原题
  409. select ename,job,sal from emp where sal <(select min(sal) from emp where deptno=10);
  410. select ename,job,sal from emp where sal < all(select sal from emp where deptno=10);
  411.  
  412. --3.使用any操作符的多行子查询
  413. --48.查询比任意部门平均工资高的员工姓名、工作、薪水（两种方法）		 修改原题
  414. select ename,job,sal from emp where sal >(select min(avg(sal)) from emp group by deptno);
  415. select ename,job,sal from emp where sal > any(select avg(sal) from emp group by deptno);
  416.  
  417. --4.多列子查询
  418. --49.查询与smith工作、部门相同的员工
  419. select * from emp where ename!='SMITH' and job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');
  420.  
  421.  
  422. --50.如何显示高于自己部门平均工资的员工信息
  423. select * from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno);
  424.  
  425.  
  426. --5.3 其他语句中使用子查询
  427. -- 1.建表语句中使用子查询
  428. --复制查询结果到新表
  429. create table mytable(id,name,sal) as (select empno,ename,sal from emp);
  430. --table 不可省略
  431. drop table mytable;
  432. -- 完全复制   20号部门的信息全部复制过去
  433. create table mytable as	select * from emp where deptno=20;
  434.  
  435.  
  436. --2.insert语句中使用子查询
  437. --建立新表   --最后一行的,不能写 必须省略
  438. create table employee(
  439. 	name varchar2(10),
  440. 	sal number(7,2),
  441. 	job varchar2(10)
  442. );
  443. --插入数据  将查询结果批量插入
  444. insert into employee select ename,sal,job from emp where deptno=10;
  445.  
  446. --3.删除语句中使用子查询  
  447. --删除工资大于工作为'CLERK'(该工资唯一)的员工信息
  448. delete from employee where sal > (select sal from employee where job='CLERK');
  449.  
  450. --4.update语句中使用子查询
  451. update employee set sal=(select sal from employee where job ='CLERK');
  452.  
  453.  
  454. -- 6.dml(数据)
  455. 	-- DML:数据操作语言
  456. 	-- 	操作对象:记录
  457. --1）insert 完全记录插入   
  458. --指明要插入的具体字段值
  459. insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT','DETROIT');
  460.  
  461. --不指明具体字段，则按序每个字段都要插入一个值  用to_date函数指明日期格式，这样无论系统默认日期格式如何，都不会报错
  462. insert into emp values(2296,'AROMANO','SALESMAN',7782,to_date('03-2月 1997'，'DD-MON,YYYY'),1300,null,10);
  463.  
  464.  
  465. -- 2) 省略字段值
  466. -- dept表共三个字段 DEPTNO DNAME LOC  仅主键DEPTNO不允许为空
  467. --省略方法：列举列名，忽略有空值的列
  468. insert into dept(deptno,dname) values(70,'FINANCE');
  469. --省略列名 用null填充
  470. insert into dept values(60,'MIS',null);
  471.  
  472.  
  473. --update
  474. --改现有行
  475. --更改7698号雇员的工作和部门，与第7499号雇员改为相同
  476. --先备份
  477. --7698 BLAKE      MANAGER         7839 1981-05-01       2850                    30
  478. --多个列值 中间必须，不能是and
  479. --update emp set job='MANAGER',deptno=30 where empno=7698;   
  480.  
  481. --update set语句可以列对应赋值
  482. update emp set (job,deptno)=(select job,deptno from emp where empno=7499) where empno=7698;	
  483.  
  484. --delete
  485. --删除所有职位为salsman的员工信息，并练习rollback,commit
  486. --先备份
  487. create table salsEmp as select * from emp where job='SALESMAN';
  488. --后期还原
  489. -- insert into emp select * from salsEmp;
  490.  
  491. --开始删除
  492. delete emp where job='SALESMAN';
  493. --查询就剩10行了 SALESMAN job的全没了
  494. -- 此时 rollback
  495. rollback
  496. --再次查询job为SALESMAN的又恢复了
  497.  
  498. delete emp where job='SALESMAN';
  499. commit;
  500. --查询 记录删除了
  501. rollback;--无论怎么rollback记录都没有了。。。  还好提前有备份
  502.  
  503.  
  504. --还原
  505. insert into emp select * from salsEmp;
  506.  
  507. ​

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/421524
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号