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

我学oracle时的随手笔记（数据库面试必备知识）
都是些基础的，还有提高的。拿出来给大家分享吧！！！
**关于oracle自带的表***********************************8
emp：
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%';取出的名字变成小写后 不含字母a
select 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 scott
2--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 <=5;

select ename, sal from
(
select ename,sal, rownum r from
(select ename, sal from emp order by sal desc)
) where r >=6 and r<=10; 求薪水最高的第6到第10名雇员。
************************************************************************

**update*******************************************************
update emp2 set sal = sal*2, ename=ename||'-' where deptno = 10; 更改。
delete from emp2; 删除。
****************************************************************

**创建新表与约束****************************************************
create table stu
(
id number(6) primary key, //主键约束，主键非空且唯一，也可以写在后面 constraint stu_id_pk primary key(id),
name varchar2(20) constraint stu_name_nn not null, //not null指定 必须为非空
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50),
constraint stu_name_email_uni unique(email, name) //email与name组合 唯一
)

外键参考的值段必须是主键，加上constraint stu_class_fk foreign (class) reference class(id),
*************************************************************************

**alter修改表结构***************************************************
alter table stu add(addr varchar2(100)); //添加字段
alter table stu drop(asddr);
alter table stu modify(asddr varchar2(50));
alter table stu drop constraint stu_class_fk; //删除约束条件
alter table stu add constraint stu_class_fk foreign key (class) reference class (id); //重新添加约束条件
****************************************************************

**查询系统的表**********************************************
select table_name from user_tables; //当前用户下所有的表
select view_name from user_views; //当前用户下的视图
select constraint_name，table_name from user_constraints;
desc dictionary //数据字典表
******************************************************************

**索引********************************************************
create index idx_stu_email on stu(email); //创建索引
drop index idx_stu_email; //删除索引
select index idx_name from user_indexes;
select view_name from user_views;
desc (视图的名称)
create view v$_stu as select id, name, age from stu; 只给予查看id，name，age 的权力，保护私有数据。
*********************************************************************

**sequence序列*****************************************************
create sequence seq; //创建序列
select seq.nextval from dual;
insert into article values (seq.nextval, 'a', 'b'); //添加数据*********************************************************************

**三范式**********************************************************
第一范式：要有主键且列不可分。
第二范式：不能存在部分依赖：非主键的字段不能依赖于组合主键的一部分。
第三范式：不能存在传递依赖。
*******************************************************************

**PL_SQL语句*************************************************************
简单小程序：
SQL> set serveroutput on;
SQL> begin
dbms_output.put_line('HelloWorld!');
end;
/
HelloWorld! （显示的结果）
运行中dcomcnfg命令是查看系统组件服务

变量申明的规则：
变量名不能使用保留字，如from，select等
第一个字符必须是字母。
变量名最多包含30个字符
不要与数据库的表或者列同名
每一行只能申明一个变量

常用变量的类型：
binary_integer: 整数，主要用来计数而不是用来表示字段类型
number：数字类型
char：定长字符串
varchar2：变长字符串
date：日期
long：长字符串，最长2G
boolean：布尔类型，可取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 p
is
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
p;
end;
--带参数的存储过程
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
.
.
.
*********************************************************************

**trigger************************************************************
创建触发器：
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno = :NEW.deptno where deprno = :OLD.deptno;
end;
/
然后就可以这样子更改了： update emp set deptno = 99 where deptno = 10;
***