Oracle数据库使⽤基础和实例
数据库查询实例
数据查询语法格式
/*distinct：去除重复的查询结果*/
select [all|distinct] *|<⽬标列表达式>[别名],...
from <；表名或视图名>[别名],...
where <；条件表达式>
group by <；列1>
having <；条件表达式>
order by <；列2>
exists
/
*
exists：如果⼦句有返回结果，那么返回true.否则返回false
not exists：和上⾯相反
*/
查询有员⼯的部门信息。
select d.* from dept d
where exists
(select empno from emp e
where d.deptno = e.deptno);
模糊查询
/*
模糊查询:
_：匹配⼀个字符。
%：匹配0到多个字符。
*/
查询⾝份证中出⽣年份‘1989’的学员信息。
select stuname from studentinfo
where stucard like '______1989%';
⼦查询
/*
⼦查询：在⼀个查询中嵌套另⼀个查询，嵌套的查询语句就是⼦查询，⼦查询的结果可以被本次查询语句使⽤。
单⾏⼦查询:⼦句只会查询出⼀个结果。
多⾏⼦查询:⼦句会查询出多个结果
*/
查询⼯资⽐JONES⾼的所有员⼯。oracle四舍五入
select ename from emp
where sal >
(select sal from emp
where ename = 'JONES');
/*关联查询⽅法*/
ame,e2.sal from emp e1,emp e2
ame = 'JONES' and e2.sal>e1.sal;
和⼯资⼤于2000的⼈在同⼀个部门的⼈。
select ename,deptno from emp
where deptno in
(select distinct deptno from emp
where sal>2000);
/*关联查询⽅法*/
select ame,e2.deptno,e2.sal from emp e1,emp e2
where e1.sal>2000 and e2.deptno = e1.deptno;
分组查询
/*
分组查询:⼀般情况下分组查询基本都会使⽤到聚合函数。
group by：分组只能查询按照分组的字段的信息，以及相关的聚合数据
avg()：平均数
min()：最⼩值
max()：最⼤值
sum()：求和
count():⾏数，如果根据求⾏数的字段为null那么就不计算⾏数
where后⾯不能跟聚合函数，如果想对组进⾏筛选使⽤having
*/
查询每个部门的最⾼⼯资的员⼯的信息。
select e1.* from emp e1,
(select max(sal) s,deptno from emp
group by deptno) e2
where e1.sal = e2.s and e1.deptno = e2.deptno;
查询男⼥学员的平均年龄。
select stusex,avg(stuage) from studentinfo
group by stusex ;
排序
/*
order by：排序
asc：从⼩到⼤（默认）
desc：从⼤到⼩
如果⼀个数字跟⼀个空⽩(null)相加，那么结果为空⽩(null)
nvl(字段，值)：当字段不为空的时候返回字段本来的值，否则返回参数2的值
*/
列出所有员⼯的年⼯资，按年薪从⾼到低排序，年薪⼀样按⼯号从低到⾼。
select ename,(sal+nvl(comm,0))*12 年薪 from emp
order by (sal+nvl(comm,0))*12 desc,empno asc;
字符串拼接
/*
||：在查询过程中可以通过||拼接多个字段，做为⼀个字段
*/
查询学员信息，要求⼀列显⽰。
select '姓名' || stuname || '，年龄' || stuage || '，家住' || stuaddress 学员信息 from studentinfo;
字符函数
/
*
字符函数:
initCap(str)：将字符串⾸字母转换为⼤写，其他字母转换为⼩写
lower(str)：将字符串转换为⼩写
upper(str)：将字符串转换为⼤写
concat(str1,str2)：将字符串1和字符串2拼接起来
substr(str，开始位置，数量)：截取字符串，包括开始位置，下标从1开始
lpad(str，位数，指定字符)：从左侧补齐字符串
rpad(str，位数，指定字符)：从右侧补齐字符串
instr(str，字符)：查询字符在字符串中的第⼀个出现位置
replace(str,oldstr,newstr)：新字符串替换旧字符串
length(str)：返回字符串长度
lengthb(str)：返回字节长度
⼀般使⽤程序完成，不在数据库内完成。
*/
查询所有员⼯姓名，⾸字母⼤写。
select initCap(ename) from emp;
查询所有学员姓名的长度。
select stuname,length(stuname) from studentinfo;
查询⾝份证中第9，10位为‘89’的学员信息。
select stuname,stucard from studentinfo
where substr(stucard,9,2) = '89';
查询所有班主任的邮箱的⽤户名。
select teachername,substr(teacheremail,1,length(teacheremail)-1-(length(teacheremail)-instr(teacheremail,'@'))) from teacherinfo；查询所有班主任的邮箱的所属⽹站。
select teachername,substr(teacheremail,instr(teacheremail,'@')+1,instr(teacheremail,'.')-instr(teacheremail,'@')-1) from teacherinfo；数字函数
/*
数字函数:
ceil(num)：向上取整
floor(num)：向下取整
round(num，保留⼩位数)：四舍五⼊
trunc(num，保留⼩数位)：截断
trunc(num，保留⼩数位)：截断
*/
查询平均⼯资，并四舍五⼊。
select round(avg(sal)) from emp;
求⼩于-58.9的最⼤整数。
select floor(-58.9) from dual;
求⼤于78.8的最⼩整数。
select ceil(78.8) from dual;
求64除以7的余数。
select mod(64,7) from dual;
查询所有学员的平均年龄（要求保留两位⼩数）
select trunc(avg(stuage),2) from studentinfo;
⽇期函数
/*
⽇期函数:
add_months(⽇期，要增加⽉份)：返回增加⽉份后的⽇期
next_day(指定的⽇期，星期⼏)：返回指定⽇期的下⼀个的星期⼏
trunc(指定的⽇期)：截断时分秒，返回年⽉⽇
to_date(⽇期的字符串，转换格式)：把字符串类型⽇期转换成date
to_char(指定的⽇期，字母格式)：返回指定格式的时间信息
yyyy：年
mm:⽉
dd:⽇
hh24:24⼩时制
mi：分
ss：秒
ff：毫秒，但是date类型⽆法保存到毫秒，如果想保存毫秒需要使⽤timestamp类型
day：星期
*/
查询‘2007-3-5’后⼊学的学员信息。
select stuname from studentinfo
where stujointime > to_date('2007-3-5','yyyy-mm-dd');
查询⼊职超过30年的员⼯。
/*两个⽇期加减以⽇为单位*/
select ename,hiredate from emp
where (sysdate-hiredate)/365 > 30;
查询所有学员从⼊学到今天，⼀共度过了多少天
select stuname,(sysdate-stujointime) from studentinfo;
查询每⽉2号⼊学的学员信息。
select stuname from studentinfo
where to_char(stujointime,'dd') = 2;
查询所有学员的毕业⽇期，假定按每个学员⼊学时间1年半之后将毕业。select stuname,stujointime,add_months(stujointime,18) from studentinfo;
查询星期四⼊学的学员姓名，性别，年龄，班级编号。
select stuname,stusex,stuage,sclassid from studentinfo
where to_char(stujointime,'day') = '星期四';
查询‘2007-3-10’之前⼊学的学员信息。
select stuname,stujointime from studentinfo
where stujointime < to_date('2007-3-10','yyyy/mm/dd');
查询2007年⼊学的学员信息。
select stuname,stujointime from studentinfo
where to_char(stujointime,'yyyy') = '2007';
查询当前时间，显⽰格式为2021年8⽉17⽇14:20:21
select to_char(sysdate,'yyyy"年"mm"⽉"dd"⽇"hh24":"mi":"ss') from dual;
查询所有学员⼊学时间，要求显⽰格式为‘2007年03⽉02⽇。
select stuname,to_char(stujointime,'yyyy"年"mm"⽉"dd"⽇"') ⼊学时间 from studentinfo;
分析函数
/
*
分析函数：可以在组内对数据进⾏排序，返回⼀个数字
partition by：在分析函数中⽤来替代group by
row_number：返回连续的排位，不论值相等
rank：具有相等值的排位相同，序数然后跳跃
dense_rank：具有相等值的排位相同，序号是连续的
*/
通过分析函数获得每个部门的最⾼⼯资
select e.*,
row_number()over(partition by deptno orderby sal) rn,
rank()over(partition by deptno order by sal) r,
dense_rank()over(partition by deptno order by sal) dr
from emp e;
查询学员成绩，按成绩排序，并计算出名次
--1.要求不论成绩是否相同，名次是连续的序号
select estuid,examresult,examsubject,
row_number()over(partition by examsubject order by examresult)
from studentexam;
--2.要求成绩相等的排位相同，名次随后跳跃
select estuid,examresult,examsubject,
rank()over(partition by examsubject order by examresult)
from studentexam;
-
-3.要求成绩相等的排位相同，名次是连续的
select estuid,examresult,examsubject,
dense_rank()over(partition by examsubject order by examresult)
from studentexam;
伪列
/*
伪列：在插⼊数据时数据库⾃动给改⾏数据⽣成的唯⼀的rowid
rowid：在插⼊数据时数据库⾃动给改⾏数据⽣成的唯⼀rowid，查数据最快
rownum：查询返回结果集中⾏的序号，可以⽤来限制查询返回的⾏数
rownum在排序之前就已经存在，排序会打乱rownum的排序
伪列只能直接查等于1和<，若要查⼤于1和⼀个区间，需要把rownum查出来变成⼀个列再使⽤
*/
查询年龄最⼩的三位学员。
select * from
(select * from studentinfo
order by stuage)
where rownum <= 3;
查询 Java 考试成绩第⼆名的学员信息。
select * from
(select exam.*,rownum r from(
select * from studentexam exam
where examsubject = 'Java'
order by examresult desc))
where r = 2;
多表连接
/*
多表连接查询:
全连接:
等值连接:⼀般情况下通过两张表的主外键进⾏连接两张表
左(右)外连接：以左边的表为主，左边表的所有数据都查询出来，与右边表⽆法建⽴关联的数据空着
select e.*,d.* from emp e left join dept d
on e.deptno=d.deptno
select e.*,d.* from emp e ,dept d
where e.deptno=d.deptno(+)
*/
查询李四的所有权限。
select tp.*
select tp.*
from t_user tu,t_role tr,t_power tp,t_role_power trp
le_id=tr.id le_id=tr.id and trp.power_id=tp.id and tu.name='李四';
查询员⼯以及其领导的名字。
/*
emp表当员⼯表,⼜可以当领导表。
*/
ame 员⼯,e2.ename 领导 from emp e1,emp e2
= e2.empno;
按班主任姓名分组，查所带班级的总成绩分。
select teachername,sum(examresult) from teacherinfo t,studentexam e,studentinfo s,classinfo c where s.stuid = e.estuid acherid = t.teacherid and c.classid = s.sclassid
group by teachername;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。