数据库易错题

Oracle 1

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

2.Oracle中，select * from emp;表示什么含义?
查询表emp的所有行。

3.求每个人的年薪，列的别名：“年薪”;
分析：怎么给列起别名呢？“as”关键字；
select ename，sal*12 as 年薪 from emp；–“年薪”可以加或不加双引号，但单引号不行。

4.求姓名和薪水和津贴，格式为smith-sal-123(利用拼接字符串)
分析：拼接字符串不同于java中用“+”,这里用”||“。这里拼接的字符串是作为数据出现在字段内容中的。
select ename||’-‘||sal||’-‘||nvl(comm,0) 收入信息 from emp;

5、–求ascii码对应的字符
select chr(65) from dual;
–求字符对应的ascii码
select ascii(‘中’) from dual;

6、–将当前日期转换成1981-03-12 12:00:00这种形式的字符串
select to_char(sysdate, ‘YYYY-MM-DD HH24:MI:SS’) from dual;
–将1981-03-12 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 emp;
–将固定格式的字符串转换成数值
select to_number(‘$1,250.00', '$9,999.99’) from dual;
–null当null参与计算时候，需要要nvl这个函数
select ename, sal*12+comm from emp;
select ename, sal*12+ nvl(comm, 0) from emp;

Oracle 2

分组函数的使用：
1、计算emp表中的所有人员的平均薪水
select avg(sal) from as 平均薪水 emp;

2、计算emp表中薪水的总和
select sum(sal) as 薪水总和 from emp;

3、计算emp表中薪水和津贴的总和
(1)select sum(sal+nvl(comm,0)) from emp;
(2)select sum(sal)+sum(nvl(comm,0)) from emp;

Group by having语句

1.求各部门最高薪水
分析：“各部门“，就是按部门分组；
select max(sal) from emp group by deptno;
select deptno,max(sal) from emp group by deptno;

2.求薪水最高的员工姓名
select ename from emp where sal=(select max(sal) from emp);

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

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

5.下面的sql语句为什么不行?说出你的理由。
select ename,deptno,avg(sal) from emp group by deptno;
要查询的字段必须是单组分组函数和被分组的字段。此题中的”ename”既不是单组分组函数，也不是被分组的字段，所以报错。

6.说出：where、groupby、having、order by的执行顺序
执行顺序：where>group by>having>order by。
7. truncate和delete有什么区别?
Truncate 不需要事务处理，直接删除，没有回滚操作 效率高

8.为什么需要表连接? 常用的表连接查询有哪几种？分别适用于哪种情况？
数据库是由多张表组成的存储结构，并通过多张表之间的关系建立起完整有效的数据存储形式。为了获得多张表之间的完整有效的数据联系，数据库查询语言SQL提供了强大的表连接查询功能。表连接就是将多个表中的数据连接到一起的查询，即连接操作可以在一个select语句中完成从多个表中查询和处理数据的功能。

9.为什么需要笛卡尔积?笛卡尔积的作用是?
所谓笛卡尔积,通俗点说就是指两个集合中任意取出两个元素所构成的组合的集合。假设R中有元组M个,S中有元组N个,则R和S的笛卡尔积中包含的元组数量就是M*N.这个规则可以向多个关系扩展。
所有表连接方式都会先生成临时笛卡尔积表，表示两个表中的每一行数据任意组合。在实际应用中，笛卡尔积本身大多没有实际用处，只有在两个表连接时加上限制条件，才会有实际意义。

10.Oracle中的分页处理依赖于哪个字段? mysql中分页处理使用什么来实现?
Oracle : rownum;
Mysql : limit;

子查询：

1.求比普通员工的最高薪水还要高的经理人名称
select ename, sal from emp where job in (‘MANAGER’,’PRESIDENT’) and sal> (select max(sal) from emp where job not in(‘MANAGER’,’PRESIDENT’));

2.求平均薪水最高的部门的部门编号
select max(平均薪水) from (select deptno,avg(sal) 平均薪水 from emp group by deptno);

3.每个部门平均薪水的等级(需要用到表的连接)
select deptno,部门平均薪水,grade from (select deptno,avg(sal) 部门平均薪水 from emp group by deptno),salgrade where 部门平均薪水 between losal and hisal ;

表连接和子查询：

92 标准版 99的话在两表连接处上加join语句 ,on 语句 代替where
1.求部门平均薪水的等级92版
select deptno,部门平均薪水,grade from (select deptno,avg(sal) 部门平均薪水 from emp group by deptno),salgradewhere 部门平均薪水 between losal and hisal;
99版
select e.deptno,e.avg_sal,sg.grade
from (select deptno,avg(sal) avg_sal from emp where deptno is not null group by deptno )e
join salgrade sg
on (e.avg_sal between sg.losal and sg.hisal)

2.求平均薪水的等级最低的部门名称92版
select d.dname from dept d ,（select * from (select deptno,部门平均薪水,grade from (select deptno,avg(sal) 部门平均薪水 from emp group by deptno),salgrade
where 部门平均薪水 between losal and hisal) t where grade=(select min(grade) from (select deptno,部门平均薪水,grade from (select deptno,avg(sal) 部门平均薪水 from emp group by deptno),salgrade
where 部门平均薪水 between losal and hisal) )）m where m.deptno=d.deptno
99版
select d.dname
from dept d
join(select m.deptno from
(select e.deptno,e.avg_sal,sg.grade
from (select deptno,avg(sal) avg_sal from emp where deptno is not null group by deptno )e
join salgrade sg
on (e.avg_sal between sg.losal and sg.hisal)) m where m.grade=(select min(grade) from (select e.deptno,e.avg_sal,sg.grade
from (select deptno,avg(sal) avg_sal from emp where deptno is not null group by deptno )e
join salgrade sg
on (e.avg_sal between sg.losal and sg.hisal))))n
on(d.deptno=n.deptno)

3.求部门经理人中平均薪水最低的部门名称92版
select dname from dept d,
（select t.* from (select t.*,grade from (select deptno,avg(sal) 经理人平均薪水 from emp where job=’MANAGER’ group by deptno) t,salgrade where 经理人平均薪水 between losal and hisal) t where grade=(select min(grade) from (select deptno,avg(sal) 经理人平均薪水 from emp where job=’MANAGER’ group by deptno))）m where m.deptno=d.deptno
99版

select d.dname
from dept d
join(select m.deptno from
(select e.deptno,e.avg_sal,sg.grade
from (select deptno,avg(sal) avg_sal from emp where job=’MANAGER’ group by deptno )e
join salgrade sg
on (e.avg_sal between sg.losal and sg.hisal)) m where m.grade=(select min(grade) from (select e.deptno,e.avg_sal,sg.grade
from (select deptno,avg(sal) avg_sal from emp where job=’MANAGER’ group by deptno )e
join salgrade sg
on (e.avg_sal between sg.losal and sg.hisal))))n
on(d.deptno=n.deptno)
4.求薪水最高的前5名雇员
select t.*,rownum from (select rownum, ename,sal from emp order by sal desc) t where rownum<=5;
5.求薪水最高的第6到第10名雇员
select ename,sal,rn,rownum from (select t.*,rownum as rn from (select rownum, ename,sal from emp order by sal desc) t) ab where rn>5 and rn<=10;