Oracle数据库使用基础和实例

数据库查询实例

数据查询语法格式

/*distinct:去除重复的查询结果*/
select [all|distinct] *|<目标列表达式>[别名],...
from <表名或视图名>[别名],...
where <条件表达式>
group by <列1>
having <条件表达式>
order by <列2>

exists

/*
exists:如果子句有返回结果,那么返回true.否则返回false
not exists:和上面相反
*/

1. 查询有员工的部门信息。

   select d.* from dept d
   where exists
   	(select empno from emp e
        where d.deptno = e.deptno);
   

模糊查询

/*
模糊查询:
_:匹配一个字符。
%:匹配0到多个字符。
*/

1. 查询身份证中出生年份‘1989’的学员信息。

   select stuname from studentinfo
   where stucard like '______1989%';
   

子查询

/*
子查询:在一个查询中嵌套另一个查询，嵌套的查询语句就是子查询，子查询的结果可以被本次查询语句使用。
单行子查询:子句只会查询出一个结果。
多行子查询:子句会查询出多个结果
*/

1. 查询工资比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;
   

2. 和工资大于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
*/

1. 查询每个部门的最高工资的员工的信息。

   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;
   

2. 查询男女学员的平均年龄。

   select stusex,avg(stuage) from studentinfo 
   group by stusex ;
   

排序

/*
order by:排序
		asc:从小到大（默认）
        desc:从大到小
如果一个数字跟一个空白(null)相加,那么结果为空白(null)
nvl(字段,值):当字段不为空的时候返回字段本来的值,否则返回参数2的值
*/

1. 列出所有员工的年工资,按年薪从高到低排序，年薪一样按工号从低到高。

   select ename,(sal+nvl(comm,0))*12 年薪 from emp
   order by (sal+nvl(comm,0))*12 desc,empno asc;
   

字符串拼接

/*
||:在查询过程中可以通过||拼接多个字段,做为一个字段
*/

1. 查询学员信息，要求一列显示。

   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):返回字节长度
一般使用程序完成，不在数据库内完成。
*/

1. 查询所有员工姓名，首字母大写。

   select initCap(ename) from emp;
   

2. 查询所有学员姓名的长度。

   select stuname,length(stuname) from studentinfo; 
   

3. 查询身份证中第9，10位为‘89’的学员信息。

   select stuname,stucard from studentinfo
   where substr(stucard,9,2) = '89';
   

4. 查询所有班主任的邮箱的用户名。

   select teachername,substr(teacheremail,1,length(teacheremail)-1-(length(teacheremail)-instr(teacheremail,'@'))) from teacherinfo;
   

5. 查询所有班主任的邮箱的所属网站。

   select teachername,substr(teacheremail,instr(teacheremail,'@')+1,instr(teacheremail,'.')-instr(teacheremail,'@')-1) from teacherinfo;
   

数字函数

/*
数字函数:
	ceil(num):向上取整
	floor(num):向下取整
	round(num,保留小位数):四舍五入
	trunc(num,保留小数位):截断
*/

1. 查询平均工资，并四舍五入。

   select round(avg(sal)) from emp;
   

2. 求小于-58.9的最大整数。

   select floor(-58.9) from dual;
   

3. 求大于78.8的最小整数。

   select ceil(78.8) from dual;
   

4. 求64除以7的余数。

   select mod(64,7) from dual;
   

5. 查询所有学员的平均年龄（要求保留两位小数）

   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:星期
*/

1. 查询‘2007-3-5’后入学的学员信息。

   select stuname from studentinfo
   where stujointime > to_date('2007-3-5','yyyy-mm-dd');
   

2. 查询入职超过30年的员工。

   /*两个日期加减以日为单位*/
   select ename,hiredate from emp
   where (sysdate-hiredate)/365 > 30;
   

3. 查询所有学员从入学到今天，一共度过了多少天

   select stuname,(sysdate-stujointime) from studentinfo;
   

4. 查询每月2号入学的学员信息。

   select stuname from studentinfo 
   where to_char(stujointime,'dd') = 2;
   

5. 查询所有学员的毕业日期，假定按每个学员入学时间1年半之后将毕业。

   select stuname,stujointime,add_months(stujointime,18) from studentinfo;
   

6. 查询星期四入学的学员姓名，性别，年龄，班级编号。

   select stuname,stusex,stuage,sclassid from studentinfo 
   where to_char(stujointime,'day') = '星期四';
   

7. 查询‘2007-3-10’之前入学的学员信息。

   select stuname,stujointime from studentinfo
   where stujointime < to_date('2007-3-10','yyyy/mm/dd');
   

8. 查询2007年入学的学员信息。

   select stuname,stujointime from studentinfo
   where to_char(stujointime,'yyyy') = '2007';
   

9. 查询当前时间，显示格式为2021年8月17日14:20:21

   select to_char(sysdate,'yyyy"年"mm"月"dd"日"hh24":"mi":"ss') from dual;
   

10. 查询所有学员入学时间，要求显示格式为‘2007年03月02日。

    select stuname,to_char(stujointime,'yyyy"年"mm"月"dd"日"') 入学时间 from studentinfo;
    

分析函数

/*
分析函数:可以在组内对数据进行排序，返回一个数字
partition by:在分析函数中用来替代group by
row_number:返回连续的排位，不论值相等
rank:具有相等值的排位相同，序数然后跳跃
dense_rank:具有相等值的排位相同，序号是连续的
*/

1. 通过分析函数获得每个部门的最高工资

   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;
   

2. 查询学员成绩，按成绩排序，并计算出名次

   --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查出来变成一个列再使用
*/

1. 查询年龄最小的三位学员。

   select * from 
   	(select * from studentinfo
        order by stuage)
   where rownum <= 3;
   

2. 查询 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(+)
*/

1. 查询李四的所有权限。

   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='李四';
   

2. 查询员工以及其领导的名字。

   /*
   emp表当员工表,又可以当领导表。
   */
   select e1.ename 员工,e2.ename 领导 from emp e1,emp e2
   where e1.mgr = e2.empno;
   

3. 按班主任姓名分组，查所带班级的总成绩分。

   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;