我学oracle时的随手笔记数据库面试必备知识.docx

1、我学oracle时的随手笔记数据库面试必备知识我学oracle时的随手笔记（数据库面试必备知识）都是些基础的，还有提高的。拿出来给大家分享吧！*关于oracle自带的表*8emp：empno：员工编号； ename：员工名字； job：员工工种； mgr: 上司； hiredate：入职时间；sal： 基本工资； comm： 补贴； deptno：所属部门编号；dept：deptno：部门编号； dname：部门名称； loc：地理位置；salgrade：grade： 工资等级； losal：最低限额； hisal：最高限额；dual：系统自带的一张空表； 可用于计算数据：select 2*3

2、 from dual; *sql_function1*select lower(ename) from emp; 取出的名字全部变成小写。select ename from emp where lower(ename) like _a%;取出的名字变成小写后 不含字母aselect substr(ename, 2, 3) from emp;从第二个字符截，截取三个字符。select cha(65) from dual; 将数字转化为字符（显示为a）。select ascii(A) from dual; 将字符转化为数字。select round(23.652) from dual; (显示24

3、) select round(23.652, 2) from dual; （显示23.65）select round(23.652, -1) from dual; （显示20）select to_char(sal, $99,999.9999)from emp;强制转化为指定的格式。select to_char(sal, L0000.0000)from emp;同上。select to_char(hiredate, YYYY-MM-DD HH:MI:SS) from emp; 对时间格式显示处 理。select to_char(sysdate, YYYY-MM-DD HH:MI:SS) from

4、 emp; 12进制。select to_char(sysdate, YYYY-MM-DD HH24:MI:SS) from emp; 24进制。*sql_function2*select ename, hiredate from emp where hiredate to_date(1981-2-20 12:34:52, YYYY-MM-DD HH24:MI:SS); 函数to_date 将字符转化为时间格式。select sal from emp where sal to_number($1,250.00, $9,999.99); 函数to_number将字符转化为数字格式，以作比较。se

5、lect ename sal*12 + nvl(comm 0) from emp; 函数nvl作用为当comm为null的时候当作处理，避免了comm为null给结果带来的不便。*group_function*select max(sal) from emp;输出薪水值最高的。select min(sal) from emp;输出薪水值最低的。select avg(sal) from emp;输出平均薪水值。select to_char(avg(sal),99999999.99) from emp;按照指定格式输出平均薪水 值。select round(avg(sal),2) from emp

6、; 精确到小数点后面2位。select sum(sal) from emp; 输出薪水值的总和。select count(*) from emp;求出一共有多少条记录。select count(*) from emp where deptno = 10; 求部门为10号的记录条数。select count(ename) from emp; 求一共有几个名字。select count(comm) from emp; 求非空comm的记录条数。select count(deptno) from emp;select count(distinct deptno) from emp;*group_by

7、*select deptno, avg(sal) from emp group by deptno; 将部门薪水平均分组。select deptno, job, max(sal) from emp group by deptno; 按组合分组。select ename, max(sal) from emp where sal = (select max(sal) from emp);select ename max(sal) from emp group by deptno; 这样是错误的。select deptno max(sal) from emp group by deptno; 这样可

8、行。*having*select avg(sal), deptno from emp group by deptno;select avg(sal), deptno from emp group by deptno having avg(sal) 2000; having是对分组进行限制。1 select avg(sal) 选择 2 from emp 表原3 where sal 1200 条件过滤4 group by deptno 分组5 having avg(sal) 1500 对结果进行限制6 order by avg(sal) desc 对产生的结果进行排序 *子查询*select 语句

9、里面套另外一个select语句。select ename, sal from emp where sal (select avg(sal) from emp);select ename,sal from emp join(select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);*self_table*自连接：select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e

10、2.empno; 把一个表当成两个来使用。*sql1999_table_connections*1999年标准：select ename, dname from emp cross join dept;旧：新：select ename, dname from emp join dept on (emp.deptno = deptno);select ename, dname from emp join dept using(deptno);(了解即可，不推荐使 用)select ename, grade from emp e join salgrade s on (e.sal between

11、s.losal and s.hisal);左外连接：select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno);右外连接：select ename, dname from emp e right outer join dept d (e.deptno = d.deptno);全外连接：select ename, dname from emp e full join dept d (e.deptno = d.deptno);*求部门平均薪水等级*select deptno, avg(grade) fro

12、m (select deptno, ename, grade from emp join salgrade s on (t.avg_sal between s.losal and s.hisal) t group by deptno;*部门中那些人是经理人*select ename from emp where empno in (select distinct mgr from emp);*不用组函数求薪水的最高值（面试题）*select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 jo

13、in emp e2 on (e1.sal e2.sal);* *平均薪水最高的部门的编号*select deptno, avg_sal from (select avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal , deptno from emp group by deptno)*求平均薪水最高的部门的部门名称select dname from dept where deptno = ( select dep

14、tno, avg_sal from (select avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal , deptno from emp group by deptno) )方法二：select dname from dept where deptno = ( select deptno, avg_sal from (select avg(sal) avg_sal , deptno from emp grou

15、p by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal, deptno from emp group by deptno) )* *求平均薪水的等级最低的部门的部门名称*creat new user and insert*1-backup scott2-create user create user wp identified by wp default tablespace users quota 10M on users；(创建新用户) grant create session, cre

16、ate table , create view to wangpeng(赋予新用 户权限)3-import the data insert into dept values (50, game bj); 插入数据。insert into dept (deptno, dname) values (60,game); 同上。insert into dept2 select * from dept; 数据又挨着插了一遍。rollback；回退命令。create table dept2 as select * from dept; 备份数据。*rownum*select emp, ename from

17、 emp where rownum =5; 取前四行。rownum只能和 和=使用，大于号和等于号不支持。select ename , sal from (select ename, sal from emp order by sal desc) where rownum =6 and r set serveroutput on;SQL begin dbms_output.put_line(HelloWorld!); end; /HelloWorld! （显示的结果）运行中dcomcnfg命令是查看系统组件服务变量申明的规则：变量名不能使用保留字，如from，select等第一个字符必须是字母。变量名最多包含30个字符不要与数据库的表或者列同名每一行只能申明一个变量常用变量的类型：binary_integer: 整数，主要用来计数而不是用来表示字段类型number：数字类型char：定长字符串varchar2：变长字符