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

我学oracle时的随手笔记（数据库面试必备知识）都是些基础的，还有提高的。拿出来给大家分享吧！*关于oracle自带的表*8emp：empno：员工编号； ename：员工名字； job：员工工种； mgr: 上司； hiredate：入职时间；sal： 基本工资； comm： 补贴； deptno：所属部门编号；dept：deptno：部门编号； dname：部门名称； loc：地理位置；salgrade：grade： 工资等级； losal：最低限额； hisal：最高限额；dual：系统自带的一张空表； 可用于计算数据：select 2*3 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) 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 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将字符转化为数字格式，以作比较。select 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; 精确到小数点后面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*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; 这样可行。*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 语句里面套另外一个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 = e2.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 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) from (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 join 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 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 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) )* *求平均薪水的等级最低的部门的部门名称*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, create 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 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：变长字符串date：日期long：长字符串，最长2Gboolean：布尔类型，可取true，false和null值。-Table变量类型declare type_table_emp_empno is table of emp.empno%type index by binary_integer;begin v_empnos(0) :=7369; v_empnos(2) :=7839; v_empnos(-1) :=9999; dbms_output.put_line(v_empnos(-1);end;-Record变量类型declare v_temp dept%rowtype;begin v_temp.deptno := 50; v_temp.dname := aaaa; v_temp.loc := bj; dbms_output.put_line(V_temp.deptno | | v_temp,dname);end;-使用%rowtype申明record变量declare v_temp dept%rowtype;begin v_temp.deptno := 50; v_temp.dname := aaaa; v_temp.loc := bj; dbms_output.put_line(v_temp.deptno | | v_temp.dname);end;-declare v_ename emp.ename%type; v_sal emp.sal%type;begin select ename, sal into v_ename,v_sal from emp where empno = 7369; ebms_output.put_line(v_ename | | v_sal);end;/(显示的结果为 SMITH 800)-declare v_deptno emp2.deptno%type :=10; v_count number;begin -update emp2 set sal = sal/2 where deptno = v_deptno; select count(*) into v_count from emp2; dbms_output.put_line(sql%rowcount | 条记录被影响); commit;end;-ddl语句-begin execute immediate create table T (nnn varchar2(20) default aaa);end;/-declare v_sal emp.sal%type;begin select sal into v_sal from emp where empno = 7369; if (v_sal 1200) then dbms_output.put_line(low); elseif (v_sal 2000) then dbms_output.put_line(middle); else dbms_output.put_line(hign);end if;end;/-declare v_sal emp.sal%type;begin select sal into v_sal from emp where empno =7839; if(v_sal 2500) then update emp set sal = sal*2 where empno = 7839; dbms_output.put_line(sal); if(v_sal = 2500) then dbms_output.put_line(sal); then update emp set sal = sal/2 where empno = 7839; dbms_output.put_line(sal); end if;end;-错误处理-create sequence seq_errorlog_id start with 1 increment by 1;declare v_deptno dept.deptno%type :=10; v_errcode number; v_errmsg varchar2(1024);begin delete from dept where deptno = v_deptno; commit;exception when others then rollback; v_errcode := SQLCODE; v_errsmg :=SQLRRM; insert into errorlog values (seq_errorlog_id.nextval, v_errmsg, sysdate); commit;end;-游标declare cursor c is select * from emp; v_emp c%rowtype;begin open c; loop fetch c into v_emp; exit when (c%notfound); dbms_output.put_line(v_emp.ename); end loop; close c;end;/-使用for循环的游标declare cursor c is select * from emp;begin for v_emp in c loop dbms_output.put_line(v_emp.ename) (v_emp在前面已经申明) end loop;end;-带参数的游标declare cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is select ename, sal from emp where deptno = v_deptno and job = v_job; -v_temp c%rowtype;begin for v_temp in c(30, CLERK) loop dbms_output.put_line(v_temp.ename);-存储过程当有编译的错误时 ，用命令show error 可显示出错的地方。create or replace procedure pis cursor c is select * from emp2 for update;begin for v_emp in c loop if (v_emp.deptno = 10) then update emp2 set sal = sal +10 where current of c; elseif (v_emp.deptno = 20) then update emp2 set sal = sal +20 where current of c; else update emp2 set sal = sal +20 where current of c; end if; end loop; commit;end;select * from emp2;begin