oracle常用sql查询语句部分集合(图文)

网络 数据库

当前第2页 返回上一页

结果；

select * from (select rank() over(partition by 课程 order by 分数 desc) rk,分析函数_rank.* from 分析函数_rank) where rk<=3 ;

--dense_rank():有重复的数字不跳着排列

--row_number()

select deptno,sal,row_number() over(partition by deptno order by sal) rm from scott.emp ;

结果：

--lag()和lead()

select deptno,sal,lag(sal) over(partition by deptno order by sal) 上一个,lead(sal) over(partition by deptno order by sal) from scott.emp ;

结果：

--max(),min(),avg()

select deptno,sal,max(sal) over(partition by deptno order by sal)最大,min(sal) over(partition by deptno order by sal)最小,avg(sal) over(partition by deptno order by sal)平均 from scott.emp ;

结果：

--first_value(),last_value()

select deptno,sal,first_value(sal) over(partition by deptno)最前,last_value(sal) over(partition by deptno )最后 from scott.emp ;

结果：

--分组补充 group by grouping sets

select deptno ,sal,sum(sal) from scott.emp group by grouping sets(deptno,sal);

select null,sal,sum(sal) from scott.emp group by sal union all select deptno,null,sum(sal) from scott.emp group by deptno ;

结果：

--rollup

select deptno,job,avg(sal) from scott.emp group by rollup(deptno,job) ;

--理解rollup等价于

select deptno,job,avg(sal) from scott.emp group by deptno,job union select deptno ,null,avg(sal) from scott.emp group by deptno union select null,null,avg(sal) from scott.emp ;

结果：

select deptno,job,avg(sal) a from scott.emp group by cube(deptno,job) ;

--理解CUBE

select deptno,job,avg(sal) from scott.emp group by cube(deptno,job) ;

--等价于

select deptno,job,avg(sal) from scott.emp group by grouping sets((deptno,job),(deptno),(job),());

结果：

--查询工资不在1500至2850之间的所有雇员名及工资

select ename,sal from scott.emp where sal not in(select sal from scott.emp where sal between 1500 and 2850 );

--部门10和30中的工资超过1500的雇员名及工资

select deptno,ename,sal from scott.emp a where a.deptno in(10,30) and a.sal>1500 order by sal desc ;

结果：

--在1981年2月1日至1981年5月1日之间雇佣的雇员名，岗位及雇佣日期，并以雇佣日期先后顺序排序

select ename as 姓名,job as 岗位,hiredate as 雇佣日期 from scott.emp a where a.hiredate between to_date('1981-02-01','yyyy-mm-dd') and to_date('1981-05-01','yyyy-mm-dd') order by a.hiredate asc ;

结果：

select * from scott.emp where hiredate >to_date('1981-02-01','yyyy-MM-dd');

--查询获得补助的所有雇佣名，工资及补助额，并以工资和补助的降序排序

select ename,sal,comm from scott.emp a where a.comm > all(0) order by comm desc;

--工资低于1500的员工增加10%的工资，工资在1500及以上的增加5%的工资并按工资高低排序（降序）

select ename as 员工姓名,sal as 补发前的工资,case when sal<1500 then (sal+sal*0.1) else (sal+sal*0.05) end 补助后的工资 from scott.emp order by sal desc ;

结果：

--查询公司每天，每月，每季度，每年的资金支出数额

select sum(sal/30) as 每天发的工资,sum(sal) as 每月发的工资,sum(sal)*3 as 每季度发的工资,sum(sal)*12 as 每年发的工资 from scott.emp;

结果：

--查询所有员工的平均工资，总计工资，最高工资和最低工资

select avg(sal) as 平均工资,sum(sal) as 总计工资,max(sal) as 最高工资,min(sal) as 最低工资 from scott.emp;

结果：

--每种岗位的雇员总数和平均工资

select job as 岗位,count(job) as 岗位雇员总数,avg(sal) as 平均工资 from scott.emp group by job order by 平均工资 desc;

结果：

--雇员总数以及获得补助的雇员数

select count(*) as 公司雇员总数,count(comm) as 获得补助的雇员人数 from scott.emp ;

--管理者的总人数

--雇员工资的最大差额

select max(sal),min(sal),(max(sal) - min(sal)) as 员工工资最大差额 from scott.emp ;

--每个部门的平均工资

select deptno,avg(sal) from scott.emp a group by a.deptno;

结果：

--查询每个岗位人数超过2人的所有职员信息

select * from scott.emp a,(select c.job,count(c.job) as sl from scott.emp c group by c.job ) b where b.sl>2 and a.job=b.job;

结果：

select * from scott.emp a where a.empno in(select mgr from scott.emp ) and (select count(mgr) from scott.emp)>2 ;

结果：

--处理重复行数据信息（删除，查找，修改）

select * from a1 a where not exists (select b.rd from (select rowid rd,row_number() over(partition by LOAN, BRANCH order by BEGIN_DATE desc) rn from a1) b where b.rn = 1 and a.rowid = b.rd);

--查询emp表数据信息重复问题

select * from scott.emp a where exists(select b.rd from(select rowid rd,row_number() over(partition by ename,job,mgr,hiredate,sal,comm,deptno order by empno asc) rn from scott.emp) b where b.rn=1 and a.rowid=b.rd);

--initcap：返回字符串，字符串第一个字母大写

select initcap(ename) Upp from scott.emp ;

结果：

--ascii:返回与指定的字符对应的十进制数

select ascii(a.empno) as 编号,ascii(a.ename) as 姓名,ascii(a.job) as 岗位 from scott.emp a ;

结果：

--chr:给出整数，返回对应的字符

select chr(ascii(ename)) as 姓名 from scott.emp ;

结果：

--concat:连接字符串

select concat(a.ename,a.job)|| a.empno as 字符连接 from scott.emp a;

结果：

--instr:在一个字符串中搜索指定的字符,返回发现指定的字符的位置

select instr(a.empno,a.mgr,1,1) from scott.emp a ;

--length:返回字符串的长度

select ename,length(a.ename) as 长度,a.job,length(a.job) as 长度 from scott.emp a ;

--lower：返回字符串，并将所返回的字符小写

select a.ename as 大写,lower(a.ename) as 小写 from scott.emp a ;

结果：

--upper：返回字符串，并将返回字符串都大写

select lower(a.ename) as 小写名字,upper(a.ename) as 大写名字 from scott.emp a ;

结果：

--rpad:在列的右边粘贴字符，lpad: 在列的左边粘贴字符(不够字符则用*来填满)

select lpad(rpad(a.ename,10,'*'),16,'*') as 粘贴 from scott.emp a ;

结果：

--like不同角度的使用

select * from scott.emp where ename like '%XXR%';

select * from scott.emp where ename like '%S';

select * from scott.emp where ename like 'J%';

select * from scott.emp where ename like 'S';

select * from scott.emp where ename like '%S_';

--每个部门的工资总和

select a.ename,sum(sal) from scott.emp a group by ename;

--每个部门的平均工资

select a.deptno,avg(sal) from scott.emp a group by deptno ;

--每个部门的最大工资

select a.deptno,max(sal) from scott.emp a group by deptno ;

--每个部门的最小工资

select a.deptno,min(sal) from scott.emp a group by deptno ;

--查询原工资占部门工资的比率

select deptno ,sal,ratio_to_report(sal) over(partition by deptno) sal_ratio from scott.emp ;

--查询成绩不及格的所有学生信息（提示：没有对应的表，只是意思意思。不及格人数大于等于三才能查）

select * from scott.emp where empno in(select distinct empno from scott.emp where 3<(select count(sal) from scott.emp where sal<3000) and empno in(select empno from scott.emp where sal<3000));

结果：

--查询每个部门的平均工资

select distinct deptno,avg(sal) from scott.emp group by deptno  order by deptno desc;

--union组合查出的结果，但要求查出来的数据类型必须相同

select sal from scott.emp where sal >=all(select sal from scott.emp ) union select sal from scott.emp ;

select * from scott.emp a where a.empno between 7227 and 7369 ;--只能从小到大

---------创建表空间  要用拥有create tablespace权限的用户，比如sys

create tablespace tbs_dat datafile 'c:\oradata\tbs_dat.dbf' size 2000M;

---------添加数据文件

alter tablespace tbs_dat add datafile 'c:\oradata\tbs_dat2.dbf' size 100M;

---------改变数据文件大小

alter database datafile 'c:\oradata\tbs_dat.dbf' resize 250M;

---------数据文件自动扩展大小

alter database datafile 'c:\oradata\tbs_dat.dbf' autoextend on next 1m maxsize 20m;

---------修改表空间名称

alter tablespace tbs_dat rename to tbs_dat1;

---------删除表空间  and datafiles 表示同时删除物理文件

drop tablespace tbs_dat including contents and datafiles;

--substr(s1,s2,s3)：截取s1字符串,从s2开始,结束s3

select substr(job,3,length(job)) from scott.emp ;

--replace：替换字符串

select replace(ename,'LL','aa') from scott.emp;

select * from scott.test;

insert into scott.test(ename,job) values('weather','好');

insert into scott.test(ename,job) values('wether','差');

--soundex：返回一个与给定的字符串读音相同的字符串

select ename from scott.test where soundex(ename)=soundex('wether');

--floor：取整数

select sal,floor(sal) as 整数 from scott.emp ;

--log（n,s）：返回一个以n为低，s的对数

select empno,log(empno,2) as 对数 from scott.emp ;

--mod(n1,n2)：返回一个n1除以n2的余数

select empno,mod(empno,2) as 余数 from scott.emp ;

结果：

--power(n1,n2)：返回n1的n2次方根

select empno,power(empno,2) as 方根 from scott.emp ;

--round和trunc：按照指定的精度进行舍入

select round(41.5),round(-41.8),trunc(41.6),trunc(-41.9) from scott.emp ;

--sign：取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

select sign(45),sign(-21),sign(0) from scott.emp ;

结果：

select * from scott.emp;

oracle相关的数据库SQL查询语句：

1.  在职员表中查询出基本工资比平均基本工资高的职工编号。

2.  查询一个或者多个部门的所有员工信息，该部门的所有员工工资都高于公司的平均工资。

3.  现有张三的出生日期：1985-01-15 01:27:36，请各自新建表，将此日期时间插入表中，并计算出张三的年龄，显示张三的生日。

4.  生日的输出格式要求为MM-DD（未满两位的用0不全），张三的生日为01-15。

5.  算年龄要求用三个方式实现。

6.  生日要求用两个方式实现。

7.  在数据库表中有以下字符数据，如：

13-1,14-2,13-15，13-2，13-108，13-3，13-10,13-200,13-18,100-11,14-1

现在希望通过一条SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排序，输出要拍成如下所示：
13-1,13-2,13-3,13-10,13-15,13-18,13-108,13-200,14-1,14-2,100-11

数据库表名：SellRecord;字段ListNumber;

8.  显示所有雇员的姓名以及满10年服务年限后的日期。

9.  显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。

10显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日期所在月排序，将最早年份的职员排在最前面。

10.             显示假设一个月为30天的情况下所有雇员的日薪金。

11.             找出在（任何年份的）2月受聘的所有雇员（用两种方式实现）。

12.             对于每个雇员，显示其加入公司的天数。

13.             以年，月和日的方式显示所有雇员的服务年限（入职多少年/入职了多少月/入职了多少天）。

14.             找出各月最后一天受雇的所有雇员。

15.             找出早于25年之前受雇的雇员（用两种方式实现）。

16.             工资最低1500的职员增加10%，1500以上的增加5%的工资，用一条update语句实现（用两种方式实现）。

17.             按照部门统计每种岗位的平均工资，要求输出的格式如下图所示：

18.

19.

20.

21,。

22.

本人声明：以上内容出现任何错误与不足，皆与本人无关。


标签：

打赏

取消

感谢您的支持，我会继续努力的!

扫码支持
扫码打赏，您说多少就多少

打开支付宝扫一扫，即可进行扫码打赏哦

分享从这里开始，精彩与您同在

相关推荐

评论

管理员已关闭评论功能...