Oracle面试题

连接用户：超级用户：conn sys 用户名/密码as sysdba普通用户：conn system 用户名/密码创建表空间：CREATE TABLESPACE epet_tablespaceDATAFILE 'E:\app\Administrator\oradata\orcl\'SIZE 100Mautoextend on next 32mmaxsize 2048m删除表空间、对象及数据文件：(drop tablespace study including contents and datafiles;为表空间创建用户CREATE USER userIDENTIFIED BY password[DEFAULT TABLESPACE tablespace]CONNECT：临时用户RESOURCE：更为可靠和正式的用户DBA：数据库管理员角色，拥有管理数据库的最高权限#分配权限或角色GRANT privileges or role TO user;~#撤销权限或角色REVOKE privileges or role FROM user;CONNECT角色：--是授予最终用户的典型权利，最基本的CREATE SESSION --建立会话RESOURCE 角色：--是授予开发人员的CREATE CLUSTER --建立聚簇CREATE PROCEDURE --建立过程CREATE SEQUENCE --建立序列CREATE TABLE --建表CREATE TRIGGER --建立触发器《CREATE TYPE --建立类型数据查询语言（DQL:Data Query Language）用于检索数据库表中存储的行。

可以使用SQL的SELECT语句编写查询语句。

数据操作语言（DML：Data Manipulation Language）用于修改表的内容。

DML语句有三种，分别为Insert,Update,Delete。

事务控制语言（TCL：Transaction Control Language）用于将对行所作的修改永久性的存储到表中，或者取消这些修改操作。

TC语句共有3种：Commit 永久性的保存对行所作的修改。

Rollback 取消对行所作的修改。

SavePoint 设置一个“保存点”，可以将对行所作的修改回滚到此处。

数据定义语言（DDL：Data Definition Language）用于定义构成数据库的数据结构，例如表。

DDL语句有5种基本类型：分别为Create 创建数据库结构。

Alter 修改数据库结构。

Drop 删除数据库结构。

￥数据控制语言（DCL：Data Control Language）用于修改数据库结构的操作权限。

DCL语句有两种：Grant 授予其他用户对数据库结构（例如表）的访问权限。

REVOKE 防治其他用户访问数据库结构dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

我们可以用它来做很多事情，如下：1、查看当前用户，可以在SQL Plus中执行下面语句select user from dual;2、用来调用系统函数select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名select from dual;--获得一个随机数3、得到序列的下一个值或当前值，用下面语句《select from dual;--获得序列your_sequence的下一个值select from dual;--获得序列your_sequence的当前值4、可以用做计算器select 7*9 from dual;多表联查SELECTAS 姓名, AS 课程, AS 成绩FROM Students AS SINNER JOIN Score AS C ON =INNER JOIN Course AS CS ON =【优先级1 算术运算符\2 连接符\3 比较符\4 IS [NOT] NULL, LIKE, [NOT] IN5 [NOT] BETWEEN\6 NOT \7 AND\8 OR伪列分页：SELECT * FROM ( SELECT A.*, ROWNUM RNFROM student A WHERE ROWNUM <= 5 ) WHERE RN > 0创建索引：create unique/bitmap index 索引名字on 表名(字段)创建同义词：CREATE [PUBLIC] SYNONYM synonym FOR object;#WITH 子句使用WITH 子句, 可以避免在SELECT 语句中重复书写相同的语句块WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中使用WITH 子句可以提高查询效率null 非空【如果在列上定义了not null，那么插入数据时必须为该列提供数据，否则插不进去。

】唯一键【当定义了唯一约束以后，该列值是不能重复的，但是可以为null】key 主键【用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而且不能为null说明：一张表最多只能有一个主键，但是可以有多个unique约束。

联合主键：多列联合起来作为主键。

】key 外键【用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须有主键约束或unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null】|检查【用于强制行数据必须满足的条件，假定在score列上定义了check约束，并要求score列值在0-100之间，如果不在此区间内就提示错误。

】DROP CONSTRAINT删除约束DISABLE CONSTRAINT无效化约束ENABLE CONSTRAINT 激活约束查询约束SELECT constraint_name, constraint_type,search_conditionFROM user_constraintsWHERE table_name = 'EMPLOYEES';UNION 操作符：合并数据INTERSECT 操作符：交集)MINUS 操作符：补集回滚：使用SAVEPOINT 语句在当前事务中创建保存点。

使用ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点视图：CREATE VIEW stud_viewAS SELECT studno, studname, subnoFROM Stud_details;1. 查询员工表所有数据, 并说明使用*的缺点答案：%select * from emp;使用*的缺点有：查询出了不必要的列；效率上不如直接指定列名。

2. 查询职位(JOB)为'PRESIDENT'的员工的工资答案：select * from emp where job = 'PRESIDENT';3. 查询佣金(COMM)为0或为NULL的员工信息答案：select * from emp where comm = 0 or comm is null;4. 查询入职日期在1981-5-1 到1981-12-31之间的所有员工信息答案：'select * from emp where hiredatebetween to_date('1981-5-1','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd ');5. 查询所有名字长度为4 的员工的员工编号,姓名答案：select * from emp where length(ename) = 4;6. 显示10 号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息答案：select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 2 0 and job='CLERK';7. 显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息答案：$select * from emp where ename not like '%L%' or ename like '%SM%';8. 显示各个部门经理('MANAGER')的工资答案：select sal from emp where job = 'MANAGER';9. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息答案：select * from emp where comm > sal;10. 把hiredate列看做是员工的生日,求本月过生日的员工答案：select * from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm'); *11. 把hiredate列看做是员工的生日,求下月过生日的员工答案：select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysda te,1) ,'mm');12. 求1982年入职的员工答案：select * from emp where to_char(hiredate,'yyyy') = '1982';13. 求1981年下半年入职的员工答案：select * from emp where hiredatebetween to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd') - 1;)14. 求1981年各个月入职的的员工个数答案：select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm')from emp where to_char(hiredate,'yyyy')='1981'group by trunc(hiredate,'month')order by trunc(hiredate,'month');15. 查询各个部门的平均工资答案：select deptno,avg(sal) from emp group by deptno;16. 显示各种职位的最低工资"答案：select job,min(sal) from emp group by job;17. 按照入职日期由新到旧排列员工信息答案：select * from emp order by hiredate desc;18. 查询员工的基本信息，附加其上级的姓名答案：select e.*, from emp e, emp e2 where = ;19. 显示工资比'ALLEN'高的所有员工的姓名和工资答案：<select * from emp where sal > (select sal from emp where ename='ALLEN');20. 显示与'SCOTT'从事相同工作的员工的详细信息答案：select * from emp where job = (select * from emp where ename='SCOTT');21. 显示销售部('SALES')员工的姓名答案：select ename from emp e, dept d where = and ='SALES';22. 显示与30号部门'MARTIN'员工工资相同的员工的姓名和工资答案：select ename, sal from emp|where sal = (select sal from emp where deptno=30 and ename='MARTIN');23. 查询所有工资高于平均工资(平均工资包括所有员工)的销售人员('SALESMA N')答案：select * from emp where job='SALESMAN' and sal > (select avg(sal) from em p);24. 显示所有职员的姓名及其所在部门的名称和工资答案：select ename, job, dname from emp e, dept d where = ;25. 查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地答案：select empno,ename,dname,loc from emp e, dept d》where = and danme='RESEARCH';26. 查询各个部门的名称和员工人数答案：select * from (select count(*) c, deptno from emp group by deptno) einner join dept d on = ;27. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位答案：select job, count(*) from emp where sal > (select avg(sal) from emp) group by job;28. 查询工资相同的员工的工资和姓名答案：>select * from emp e where (select count(*) from emp where sal = group b y sal)> 1;29. 查询工资最高的3名员工信息答案：select * from (select * from emp order by sal desc) where rownum <= 3; 30. 按工资进行排名,排名从1开始,工资相同排名相同(如果两人并列第1则没有第2名,从第三名继续排)答案：select e.*, (select count(*) from emp where sal > +1 rank from emp e orde r byrank;31. 求入职日期相同的(年月日相同)的员工答案：select * from emp e where (select count(*) from emp where =hiredate)>1;…32. 查询每个部门的最高工资答案：select deptno, max(sal) maxsal from emp group by deptno order by deptno;33. 查询每个部门,每种职位的最高工资答案：select deptno, job, max(sal) from emp group by deptno, job order by deptno, job;34. 查询每个员工的信息及工资级别答案：select e.*, from emp e, salgrade sg where sal between losal and hisal; 35. 查询工资最高的第6-10名员工~答案：select * from (select e.*,rownum rn from(select * from emp order by sal desc) ewhere rownum <=10)where rn > 5;36. 查询各部门工资最高的员工信息答案：select * from emp e where = (select max(sal) from emp where (deptno =);37. 查询每个部门工资最高的前2名员工'答案：select * from emp e where(select count(*) from emp where sal > and = deptno) < 2order by deptno, sal desc;38. 查询出有3个以上下属的员工信息答案：select * from emp e where(select count(*) from emp where = mgr) > 2;39. 查询所有大于本部门平均工资的员工信息答案：>select * from emp e where sal >(select avg(sal) from emp where (deptno = )order by deptno;40. 查询平均工资最高的部门信息答案：select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp grou p bydeptno) sewhere avgsal = (select max(avg(sal)) from emp group by deptno) and =; 41. 查询大于各部门总工资的平均值的部门信息答案：select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp gro up bydeptno) se。