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高的所有员工。
select ename from emp where sal > (select sal from emp where ename = 'JONES'); /*关联查询方法*/ select e2.ename,e2.sal from emp e1,emp e2 where e1.ename = 'JONES' and e2.sal>e1.sal; -
和工资大于2000的人在同一个部门的人。
select ename,deptno from emp where deptno in (select distinct deptno from emp where sal>2000); /*关联查询方法*/ select distinct e2.ename,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,保留小数位):截断
*/
-
查询平均工资，并四舍五入。
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.* from t_user tu,t_role tr,t_power tp,t_role_power trp where tu.role_id=tr.id and trp.role_id=tr.id and trp.power_id=tp.id and tu.name='李四'; -
查询员工以及其领导的名字。
/* emp表当员工表,又可以当领导表。 */ select e1.ename 员工,e2.ename 领导 from emp e1,emp e2 where e1.mgr = e2.empno; -
按班主任姓名分组，查所带班级的总成绩分。
select teachername,sum(examresult) from teacherinfo t,studentexam e,studentinfo s,classinfo c where s.stuid = e.estuid and c.cteacherid = t.teacherid and c.classid = s.sclassid group by teachername;
