18 Java面试之 Oracle 和 Mysql 数据库

一、Oracle 中，查看某个表的结构使用哪个语句?
答:
1）、select table_name from user_tables 获取当前用户的表（结构）
2）、select table_name from all_tables 获取所有用户的表(结构)
3）、select table_name from dba_tables 获取所有表包括系统表的表（结构）

二、Oracle 中，select * from scott.emp;表示什么含义?
答:
查询表 Emp所有数据行

三、【编程题】查询每个员工的所有信息
答:
select * from scott.emp;

四、【编程题】查询每个员工的姓名
答:
select ename from scott.emp;

五、【编程题】每个人的部门编号，姓名，薪水
答:
select deptno,ename,sal from scott.emp

六、【编程题】每个人的年薪
答:
select ename,sal*12 from scott.emp;

七、【编程题】计算 23 的值(利用虚拟表：dual)
答:
select 23 from dual;

八、【编程题】得到当前时间
答:
select sysdate from dual;

九、【编程题】求每个人的年薪，列的别名：“年薪”;
答:
注意:年薪”可以加或不加双引号，但单引号不行
select ename，sal*12 as 年薪 from scott.emp；

十、【编程题】计算每个人的全年的收入包括月薪和年终津贴?
答:
select ename, sal*12+nvl(comm,0)as 年薪 from scott.emp;

一十一、【编程题】求姓名和薪水和津贴，格式为 smith-sal-123(利用拼接字符串)?
答:
注意:拼接字符串不同于 java 中用“+”,这里用”||“。这里拼接的字符串是作为数据出现在字段内容中的。
select ename||’-’||sal||’-’||nvl(comm,0) 收入信息 from scott.emp;

一十二、Oracle 中，双引号一般用于什么地方?单引号用于什么地方?
答:

1. 双引号：在给字段起别名时，双引号可以加在别名上（也可以不加）；
2. 单引号：字段数据中需要拼接字符串时，字符串常量要加单引号。
   3）varchar类型的字段数据值来查询记录时，这个用来查询的字段值要用单引号

一十三、【编程题】求 10 这个部门的所有员工
答:
select * from scott.emp where deptno=10;

一十四、【编程题】求名字是 KING 的这个人的信息
答:
select * from scott.emp where ename=‘KING’

一十五、【编程题】求薪水大于 2000 的员工信息
答:
elect * from scott.emp where sal>2000

一十六、【编程题】求部门不是 10 的员工信息
答:
select * from scott.emp where deptno<>10;
select * from scott.emp where deptno!=10;

一十七、【编程题】求薪水在 800 和 1500 之间的员工信息(包含 800 和 1500)
答:
select * from scott.emp where sal between 800 and 1500;
select * from scott.emp where sal >=800 and sal <=1500;

一十八、【编程题】利用 in 操作符，列出部门 10 和 20 的人员
答:
select * from scott.emp where deptno in (10,20);

一十九、【编程题】列出 deptno 为 10 或者 30，并且工资>2000 的所有人
答:
select * from scott.emp where deptno in(10,30) and sal>2000;

二十、【编程题】利用 like 操作符，查询名字中含有"H"的人员
答:
select * from scott.emp where ename like ‘%H%’;

二十一、【编程题】分别利用 like操作符，查询名字中含有"H"或者"S"的人员
答:
select * from scott.emp where ename like ‘%H%’ or ename like’%S%’;

二十二、下面两句话是否结果相同?
select * from Emp where not ( deptno in(10,20));
select * from Emp where deptno not in(10,20);
答:
相同

二十三、数据库查询时，哪些内容区分大小写?哪些不区分?
答:
1、sql 语句关键字，如：select，from 等不区分大小写；
2、表名和字段名：不区分大小写；
3、字段中的内容：区分大小写

二十四、【编程题】把所有姓名变成小写
答:
select lower(ename) from scott.emp;

二十五、【编程题】把所有姓名变成大写
答:
select upper(ename) from scott.emp;

二十六、【编程题】求所有人名中包含’a’的员工信息不区分大小写
答:
select ename from scott.emp where lower(ename) like ‘%a%’;

二十七、【编程题】截取子字符串，比如求 Hello 的一部分
答:
select substr(‘Hello’, 2) from dual;

二十八、【编程题】截取Hello 的一部分，并指明长度
答:
select substr(‘Hello’, 2, 3) from dual;

二十九、【编程题】求 ascii 码对应的字符
答:
select chr(65) from dual;

三十、【编程题】自定义数字使用四舍五入
答:
select round(23.652) from dual;

三十一、【编程题】自定义数字使用四舍五入小数点后面多少位
答:
select round(23.652, 1) from dual;

三十二、【编程题】自定义数字使用四舍五入小数点前面多少位
答:
select round(23.652, -1) from dual;

三十三、【编程题】将当前日期转换成 2010-06-06 12:00:00 这种形式的字符串
答:
select to_char(sysdate, ‘YYYY-MM-DD HH24:MI:SS’) from dual;

三十四、【编程题】将 2010-06-06 12:00:00字符串转换成日期
答:
select to_date(‘1981-03-12 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) from
dual;

三十五、【编程题】将每个人的薪水转换成固定格式的字符串
答:
select to_char(sal, ‘L00,000.9999’) from scott.emp;

三十六、【编程题】将固定格式的字符串转换成数值
答:
select to_number(’$2,310.00’, ‘$9,999.99’) from dual;

三十七、【编程题】计算 scott.emp 表中的所有人员的平均薪水
答:
select avg(sal) as 平均薪水 from scott.emp

三十八、【编程题】计算 scott.emp 表中最高薪水
答:
select max(sal) from scott.emp;

三十九、【编程题】计算 scott.emp 表中最低薪水
答:
select min(sal) from scott.emp;

四十、【编程题】计算 scott.emp 表中薪水大于 1000 的人员的个数
答:
select count(*) from scott.emp where sal>1000;

四十一、【编程题】计算 scott.emp 表中薪水的总和
答:
select sum(sal) as 薪水总和 from scott.emp;

四十二、【编程题】计算 scott.emp 表中薪水和津贴的总和
答:
select sum(sal+nvl(comm,0)) from scott.emp;
select sum(sal)+sum(nvl(comm,0)) from scott.emp;

四十三、【编程题】求各部门最高薪水
答:
select deptno,max(sal) from scott.emp group by deptno;

select de.dname,em.deptno,em.maxsal from scott.dept de right join (select deptno,max(sal)maxsal from scott.emp group by deptno) em on de.deptno=em.deptno
；

四十四、【编程题】按照部门和职位分组，分别求最高薪水，该组人员个数
答:
部门分组
不待部门名称
select deptno,max(sal),count( * ) from scott.emp group by deptno;
待部门名称
select de.dname,em.deptno,em.maxsal,em.countnumber from scott.dept de right join (select deptno,max(sal) maxsal ,count( * ) countnumber from scott.emp group by deptno ) em on de.deptno=em.deptno

职位分组
select job,max(sal),count(*) from scott.emp group by job;

四十五、【编程题】求薪水最高的员工姓名
答:
select ename from scott.emp where sal=(select max(sal) from scott.emp);

四十六、【编程题】求平均薪水是 2000 以上的部门
答:
select deptno,avg(sal) from scott.emp group by deptno having avg(sal)>2000;

select de.dname,em.deptno,avgsal from scott.dept de right join (select deptno,avg(sal) avgsal from scott.emp group by deptno having avg(sal)>2000 ) em on de.deptno=em.deptno

四十七、【编程题】求每个部门的平均薪水，并按照薪水降序排列
答:
select deptno,avg(sal) 平均薪水 from scott.emp group by deptno order by
平均薪水 desc ;

select em.deptno,de.dname,em.部门平均薪水 from scott.dept de right join (
select deptno,avg(sal) 部门平均薪水 from scott.emp group by deptno order by 部门平均薪水 desc
) em on de.deptno=em.deptno

四十八、【编程题】求每个部门薪水在1200以上的雇员的平均薪水、最高薪水，并且分组结果中只包含平均薪水大于 1500 的部门，排序按照部门平均薪水倒序排列
答:
select deptno,avg(sal),max(sal) from scott.emp where sal>1200 group
bydeptno having avg(sal)>2500 order by avg(sal) desc;

select de.dname,em.deptno,em.avgsal,em.maxsal from scott.dept de right join (select deptno,avg(sal) avgsal ,max(sal) maxsal from scott.emp where sal>1200 group by deptno having avg(sal)>2500 order by avg(sal) desc) em on de.deptno=em.deptno

四十九、【编程题】把雇员按部门分组，求最高薪水，部门，过滤掉员工名字中第二个字母是’A’的， 要求分组后的平均薪水>1500， 按照部门编号倒序排列
答:
select deptno,avg(sal),max(sal),count(*) from scott.emp where ename not
like '_A%'group by deptno having avg(sal)>2000 order by deptno
desc;

select de.dname,em.deptno,em.avgsal,em.maxsal,em.countall from scott.dept de right join (
select deptno,avg(sal) avgsal ,max(sal) maxsal,count(*) countall from scott.emp where ename not
like '_A%'group by deptno having avg(sal)>2000 order by deptno
desc ) em on de.deptno=em.deptno

五十、where、groupby、having、order by 的执行顺序
答:
执行顺序：where>group by>having>order by。

五十一、【编程题】求平均薪水最高的部门的部门编号,名称与平均薪水
答:
select max(平均薪水) from (select deptno,avg(sal) 平均薪水 from scott.emp
group by deptno);

select * from (
select de.dname,em.deptno,平均薪水 from scott.dept de right join (
select deptno,avg(sal) 平均薪水 from scott.emp group by deptno
) em on de.deptno=em.deptno )
where 平均薪水=(select max(平均薪水) from (select deptno,avg(sal) 平均薪水 from scott.emp
group by deptno))

五十二、【编程题】求出 scott.emp 表中哪些人是经理，打印出名字和编号
答:
select ename, empno from scott.emp where job=‘MANAGER’;

五十三、【编程题】求薪水高于普通员工中最高薪水的经理名称
答:
select ename, sal from scott.emp where job in (‘MANAGER’,‘PRESIDENT’)
and sal> (select max(sal) from scott.emp where job not
in(‘MANAGER’,‘PRESIDENT’));

五十四、【编程题】每个部门平均薪水的等级
答:
select deptno,部门平均薪水,grade from (select deptno,avg(sal) 部门平均薪水 from scott.emp group by deptno),scott.salgrade where 部门平均薪水
between losal and hisal;

select de.dname,em.deptno,部门平均薪水,grade from scott.dept de right join (
select deptno,部门平均薪水,grade from (select deptno,avg(sal) 部门平均薪水 from scott.emp group by deptno),scott.salgrade where 部门平均薪水
between losal and hisal
) em on de.deptno=em.deptno

五十五、Oracle 中的分页处理依赖于哪个字段? mysql 中分页处理使用什么来实现?
答:
Oracle : rownum;
SELECT * FROM scott.emp WHERE ROWNUM <= 6
Mysql : limit;
select * from scott.emp limit 0,5

五十六、【编程题】查询薪水最高的前 5 个人
答:

select * from (select ename,sal from scott.emp order by sal desc) where rownum<=5;

五十七、【编程题】查询薪水排名(从高到低)在 5-10 之间的员工
答:

select * from (select t.*,rownum as rn from (select ename,sal from scott.emp order by sal desc) t) ab where rn>5 and rn<10;

五十八、【编程题】求平均薪水的等级最低的部门名称
答:

select * from (
select es.deptno,de.dname,es.部门平均薪水,es.grade from scott.dept de right join (
select deptno,部门平均薪水,grade from (select deptno,avg(sal)部门平均薪水 from scott.emp group by deptno),scott.salgrade where 部门平均薪水 between losal and hisal
) es on de.deptno=es.deptno
)ee where ee.grade=(
select min(grade) from (
select deptno,部门平均薪水,grade from (select deptno,avg(sal)部门平均薪水 from scott.emp group by deptno),scott.salgrade where 部门平均薪水 between losal and hisal
))

五十九、【编程题】求部门经理人中平均薪水最低的部门名称
答:

select * from (
select es.deptno,de.dname,经理人的平均薪水 from scott.dept de right join (
select deptno,avg(sal)经理人的平均薪水 from scott.emp where job ='MANAGER' group by deptno
)es on de.deptno=es.deptno
)where 经理人的平均薪水=(
select min(经理人的平均薪水) from (
select deptno,avg(sal)经理人的平均薪水 from scott.emp where job ='MANAGER' group by deptno
)
)

六十、【编程题】求薪水最高的前 5 名雇员
答:

select d.*,rownum from (
select ename,sal from scott.emp order by sal desc
) d where rownum<=5

六十一、【编程题】求薪水最高的第 6 名到第 10 名雇员
答:

select * from (
select e . *,rownum ro from (
select ename,sal from scott.emp order by sal desc
)e
)where ro >5 and ro<=10

六十二、Oracle 中， union 和 minus 的作用
答:
Union: 是将两个或者两个以上的结果集合并在一起;
Minus: 是从一个结果集中减去一部分结果集。

六十三、【编程题】Oracle 中，用 minus 的办法求薪水最高的第 6 到第 10 名雇员。
答:

select * from (
select * from (
select e . *,rownum from (
select ename,sal from scott.emp order by sal desc
) e where rownum <=10
) f minus (
select e . *,rownum from (
select ename,sal from scott.emp order by sal desc
) e where rownum <=5
) ) sf ord

er by sf.sal desc

六十四、【编程题】为表增加记录？
答:

insert into scott.emp values (8888,'xiaozhang','andmin',7782,to_date('1988/04/08','YYYY/MM/DD'),3000,1500,10)

六十五、【编程题】将新表中小张的薪水翻番。
答:
update scott.emp set sal=sal*2 where ename=‘xiaozhang’;

六十六、【编程题】将表中小张的信息删除。
答:
delete scott.emp where ename=‘xiaozhang’;

六十七、truncate 和 delete 有什么区别?
答:
二者均删除表中的全部行:
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少
TRUNCATE TABLE：删除内容不删除定义,释放空间 。
DELETE TABLE:删除内容不删除定义，不释放空间。

六十八、什么是事务?为什么需要事务?
答:
1、事务
是指作为单个逻辑工作单元执行的一组相关操作，这些操作要求全部完成或者全部不完成。事务使使数
据库从一种状态变换成为另一种状态，是数据库所特有的。
2、使用事务的原因：
保证数据的安全有效。

六十九、说出事务的特点?
答:
事务的特性有四个：简称 ACID 即
1、原子性（Atomic）
事务中所有数据的修改，要么全部执行，要么全部不执行。
2、一致性（Consistence）
事务完成时，要使所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
3、隔离性（Isolation）
事务应该在另一个事务对数据的修改前或者修改后进行访问。
4、持久性（Durability）
保证事务对数据库的修改是持久有效的，即使发生系统故障， 也不应该丢失。

七十、索引有什么作用?
答:
索引是一种供服务器在表中快速查找一个行的数据库结构。合理使用索引能够大大提高数据库的运行效率
1）、快速存取数据。
2）、既可以改善数据库性能，又可以保证列值的唯一性。
3）、实现表与表之间的参照完整性
4）、在使用 orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。

七十一、什么字段适合做索引?什么字段不适合做索引?
答:
1、应该建索引：
1）、经常需要搜索的列，提高搜索的速度；
2）、作为主键的列，强制该列的唯一性和组织表中数据的排列结构；
3）、经常用在连接的列，这些主要是一些外键，便于加快连接的速度；
4）、频繁根据范围进行搜索的列需要创建索引，因为索引已经排序，其指定的范围是连续的；
5）、经常排序的列创建索引，索引已经排序，这样查询可以利用索引的排序，加快查询时间；
6）、经常使用WHERE子句中的列创建索引，加快条件的判断速度。
2、不应该建索引
1)、 在查询中很少使用的列。降低了系统的维护速度和增大了空间需求。
2)、 只有很少数据值的列。增加索引，并不能明显加快检索速度。
3)、 改性能远远大于检索性能时。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

七十二、【编程题】针对 scott.emp 表的 ename 创建索引。然后删除？
答:
创建：create index index_on_emp on scott.emp(ename);
删除：drop index index_on_emp;