注: 这是个人看Oracle视频时写下的笔记, 多有错误, 望各位切勿吝惜赐教.
1. Dos下登陆超级管理员: sqlplus sys/密码 as sysdba
2.更改管理员:alter user scott account unlock;
3.数据的备份.
A导出:
Cmd下: exp (调用Oracle的程序) 导出
B create user
create user JJ (用户名) identified by Jack (密码) default tablespace users quota 10M on users
授权:
grant create session, create table, create view to JJ
3. 起别名:select ename, sal*12 anuual_sal from emp; --为sal*12起别名:anuual_sal
4.任何含有空值的表达式结果都为空值;
对于空值的处理: select ename, sal ,comm. From emp where comm. is null;(选空值)
select ename, sal ,comm. From emp where comm. is not null;(选非空值)
让空值在运算中等于0:
select ename, sal*12 + nvl(comm., 0) from emp; --若comm为空值,则comm=0
5. 转换为字符串:elect ename||sal from emp; --将ename与sal转换为字符串而其并在一起(结果如:SMITH800)
6.单引号表示字符串 'adkfjkda', 连续的两个单引号能表示为一个字符单引号;如('abc''def'
其结果为:abc'def
7.去掉重复值: select distinct deptno from emp;
去点组合后重复值: select distinct deptno, job from emp;
8. ”不等于” 不是”!=” , 是”<>”
9.in 的用法select ename, sal, comm.,from emp where sal in (800, 1500, 2000) --sal等于800,1500,2000,字符亦可
select ename, sal, comm.,from emp where sal not in (800, 1500, 2000) --sal不等于800,1500,2000,字符亦可
10.模糊查询:
Select ename, sal, comm., from emp where ename like ‘%ALL%’; --在ALL的左边或右边有多个字符的。
Select ename, sal, comm., from emp where ename like ‘-A%’; --横线代表一个字母。
11.转义字符:
Select ename, sal, comm., from emp where ename like ‘/%’; -- ‘/’转义字符
Select ename, sal, comm., from emp where ename like ‘$%’ escape ‘$’; -- ‘escape指定 转义字符
12.排序
Select ename, sal, deptno from emp order by deptno asc; --默认为正排序(亦可用asc)
Select ename, sal, deptno from emp order by deptno desc; --反排序
13.截字符:
Select substr(ename , 2, 4) from emp --从第2个开始, 截4个字符
14.ASCII编码的转换
Select chr(65) from dual; --65对应的字符
Select ascii(‘A’) from dual; --A对应的ASCII
15.四舍五入:
Select round(23.56) from dual; --等于24
Select round(23.56, 2) from dual; --精确到小数点后两位
Select round(23.56, -1) from dual; --等于20
16.字符格式转换:
select to_char(sal, '$99,999.9999') from emp; -- 9代表一个数字 $800.0000
select to_char(sal, '$0000,0000') from emp; -- 9代表一个数字 $ 0800.0000
select to_char(sal, 'L99,999.9999') from emp; -- 9代表一个数字 ¥800.0000
还有to_number , to_date等;
日期:
Select to_char(hiredate, ‘YYYY-MM-DD HH:MI:SS’) from emp;
Select to_char(sysdate, ‘YYYY-MM-DD HH:MI:SS’) from emp; --2009-09-21 01:09:18
Select to_char(sysdate, ‘YYYY-MM-DD HH:MI:SS’) from emp; ----2009-09-21 13:09:18
17. group_by:(分组输入只有
Select deptno, max(sal) from emp group by deptno; --按deptno分组, 然后取sal的最大值,
Select deptno, avg(sal) from emp group by deptno; --按deptno分组, 然后取sal的平均值,
Select deptno, avg(sal) from emp group by deptno,job; --按deptno, job组合分组, 然后取sal的平均值,
Select deptno, avg(sal) from emp group by deptno,job; --按deptno,job组合分组, 然后取sal的平均值
在group by 中, select字段若没有出现在主函数中,就必须出现在group by 中,否则出错:
Select ename, max(sal) from emp group by deptno; (错) --ename不是唯一的。
18.使用having对分组进行限制。
Select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;--按deptno分组后, --取sal平均值大于--2000的
19.过滤的顺序, 其执行的顺序也如下:
Select * from emp
where sal > 1000
group by deptno
having
order by
20.关于子连接的,要把生成的结果看做一张表:
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);-- 求每一组中最多的sal,并显示enamel
21. 关于在文本编译命令: ed;
22. SQL表连接99标准
交叉连接: select ename, dname from emp cross join dept;
( == select ename, dname, grade from emp e, dept d, salgrade e
Where e.deptno = d. deptno and e.sal between a.losal and s.hisal and
Job <> ‘CLERK’;
等值连接: select ename, dname from emp join dept on (emp.deptno = dept.deptno);
== select ename, dname from emp join dept using(deptno); --不推荐
(== select ename, dname from emp, dept where emp.deptno = dept.deptno;)
左外连接: 将左边的表多余的数据拿出来 left (outer) join
右外连接: 将右边的表多余的数据拿出来right (outer) join
全外连接: 将左. 右边的表多余的数据拿出来 full join;
23. 若存在空值, 系统会认为所有的number都在这里面了
24. 数据的备份.
A导出:
Cmd下: exp (调用Oracle的程序) 导出
B create user
create user JJ (用户名) identified by Jack (密码) default tablespace users quota 10M on users
授权:
grant create session, create table, create view to JJ
Oracle中自动配好的表:
emp
Name Null? Type
----------------------------------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
dept
Name Null? Type
----------------------------------------- -------- --------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
salgrade
Name Null? Type
----------------------------------------- -------- ----------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
题目:
一.求部门中哪些人的薪水最高
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)
二.求部门平均薪水的等级
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
三求部门平均的薪水等级
Select deptno, avg(grade) from
(select deptno, ename, grade from emp join salgrade s on (emp.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 deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal
(select max(avg(sal)) 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, t1.deptno, grade, avg_sal from
(
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
)t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
)
)
八. 求部门经理人中平均薪水最低的部门名称
select avg_sal from
(
avg(m2) avg_sal (
(select distinct empno,ename, mgr from emp) m
join emp e on ( m.empno = e.empno)
) m2
)group by deptno
九. 求比普通员工的最高薪水还要高的经理人名称
select ename from emp where empno in
(select distinct mgr from emp where mgr is not null)
and sal >
(
select max(sal) from emp where
empno not in
(select distinct mgr from emp where mgr is not null)
)
十. 求薪水最高的前5名雇员
Oracle对象:
create table stu
(
id number(6),
--将非空 列为关键字 stu_name_nn
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) ,
--唯一约束(字段约束)
email varchar2(50) unique,
--定义外键约束,外键约束必须是主键references
constraint stu_class_fk foreign key(class) references class(id),
constraint stu_id_pk primary key (id),
-- email, name组合唯一(表集约束)
constraint stu_name_class_uni unique(name, class)
);
create table stu
(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) ,
email varchar2(50) unique,
constraint stu_class_fk foreign key(class) references class(id),
constraint stu_id_pk primary key (id),
constraint stu_name_class_uni unique(name, class)
);
create table class
(
id number(4) primary key, --定义主键
name varchar2(20) not null
)
create table class
(
id number(4) primary key,
name varchar2(20) not null
)
alter table stu add(addr varchar2(100)); --修改字段
alter table stu modify(addr varchar2(50)); --修改字段属性
alter table stu drop constraint stu_class_fk; --修改约束条件
alter table stu add constraint stu_class_fk foreign key(class) references class (id);--增加约束条件
数据字典表
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
select index_name from user_indexs;
create index idx_stu_email on stu(email); --亦可用两个字段的组合建立索引
drop index idx_stu_email;
数据字典表的表:dictionary
create view v$_stu as select id, name, age from stu; --视图均以v$开头, 虚表,
视图: 好处 :可授权给别人查看部分数据
坏处: 增加维护开销,
故除非你确定视图有用处, 不然勿建.
视图可更新数据, 但少用.
create sequence seq; --创建序列
insert into article values( seq.nextval, 'a', 'b');
在提高效率的方法中, 建立索引是第一优先考虑的方法, 然后再想表结构的问题.
三范式:
第一范式: 每张表要有主键, 列不可分.
第二范式: 在多对多的关系中: 一个表里面有多个字段作为主键, 非主键不能部分依赖主键.要分割成为3张表.建立多张"关系表"
第三范式:不存在传递依赖.
PL_SQL语言
分四部分:
第一 declare
第二 begin
第三exception
set serveroutput on; --使输出流到SQL(dos)窗口中
begin
dbms_output.put_line(‘HelloWrod!’);
end;
/
DECLARE
变量声明
BEGIN
执行代码块
EXCEPTION
异常执行代码块
END
set serveroutput on;
begin
dbms_output.put_line('HelloWrod!');
end;
/
例一
declare
v_name varchar2(20);
begin
v_name := 'myname';
dbms_output.put_line(v_name);
end;
/
例二:
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
/
常用变量类型
1.binary_integer: 整数, 主要用来计数而不是用来表示字段类型
2.number: 数字类型
3.char: 定长字符串
4.varchar2: 变长字符串
5.date: 日期
6. long: 长字符串,最长2GB
7. boolean:布尔类型,可以取值为true、flase 和null 值 //不可打印,一定要赋初值
declare
v_temp number(1);
v_count binary_integer := 0;
v_sal number(7,2) := 4000.00;
v_date date := sysdate;
--constant == finally
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'MyName';
begin
--字符串连接符: ||
dbms_output.put_line('v_temp value:' || v_temp);
end;
set serveroutput on;
--变量声明, 使用%type属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
--Table变量类型, 相当于数组
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) := 7954;
v_empnos(2) := 7932;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
--Record变量类型, 相当于类
set serveroutput on;
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'djf';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
--使用%rowtype声明record变量
set serveroutput on;
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'djf';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
在PL-SQL, select 中必须 有且只有返回一条语句
set serveroutput on;
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;
dbms_output.put_line(v_ename || ' '|| v_sal);
end;
/
declare
v_deptno emp2.depno%type = 50;
v_count number;
begin
--update emp2 set sal = sal/2 where deptno = v_deptno;
--select deptno into v_deptno from emp2 where empno = 7369; --1条记录受影响
--select count(*) into v_count from emp2; --1条记录受影响
dbms_output.put_line(sql%rowcount || '条记录受影响');
commit;
end;
/
DML语句:
例: update, insert, delete
DDL语句:
例: 数据定义语言
DCL语句:
例: grant
begin
execute immediate 'create table T (nnn varchar2(20))';
end;
--if语句
--取出7369的薪水, 若 <1200,则输出'low' 若 <2000,则输出 middle 若 > 2000, 则输出 high
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');
elsif(v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
--循环
--1. do_while()
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when (i >= 11);
end loop;
end;
结果:
1
2
3
4
5
6
7
8
9
10
--2. while()
declare
j binary_integer :=1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j+1;
end loop;
end;
结果:
1
2
3
4
5
6
7
8
9
10
--3.增强for循环
/*
用 for/in 对数组进行循环就是小菜一碟
public void testArrayLooping(PrintStream out) throws IOException {
int[] primes = new int[] { 2, 3, 5, 7, 11, 13, 17, 19, 23, 29 };
// Print the primes out using a for/in loop
for (int n : primes) {
out.println(n);
}
}
*/
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
结果:
1
2
3
4
5
6
7
8
9
10
10
9
8
7
6
5
4
3
2
1
--错误处理
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录');
when others then
dbms_output.put_line('error');
end;
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
--错误日志的记录
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
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;
--关键字SQLCODE, 错误的代码行 (都是负数)
v_errmsg := SQLERRM;
--关键字SQLERRM, 错误信息
insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg,
sysdate);
commit;
end;
--获取错误时间
select to_char(errdate, 'YYYY--MM--DD HH24:MI:SS') from errorlog;
--游标(注意,当游标找不到所指结果集时,它仍然指向上一次所得结果集)
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;
--与循环结合
--1.与do while()
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;
--2.与while()
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
--3.与for循环
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
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);
end loop;
end;
--可更新的游标
declare
cursor c
is
select * from emp2 for update;
--v_temp c%rowtype;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif(v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
--存储过程
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;
elsif(v_emp.deptno = 20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
--执行 存储过程
--1.
exec p;
--2.
begin
p;
end;
--带参数的存储过程procedure
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if( v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;
--调用
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
--函数 function
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end;
--触发器 trigger
create or replace trigger trig
after insert or delete or update on emp2 for each row
begin
if inserting then
insert into emp2_log values(USER, 'insert', sysdate);
elsif updating then
insert into emp2_log values(USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values(USER, 'delete', sysdate);
end if;
end;
--副作用(少用)
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno =: NEW.deptno where deptno =: OLD:deptno;
end;
--树状结构
create table article
(
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1),
alevel number(2)
);
insert into article values (1, '蚂蚁大战大象', 0, 0, 0);
insert into article values (2, '大象被打趴下了', 1,0,1);
insert into article values (3, '蚂蚁也不好过', 2,1,2);
insert into article values (4, '瞎说', 2,0,2);
insert into article values (5, '没有瞎说', 4,1,3);
insert into article values (6, '怎么可能', 1,0,1);
insert into article values (7, '怎么没有可能', 6,1,2);
insert into article values (8, '可能性是很大的', 6,1,2);
insert into article values (9, '大象进医院了', 2,0,2);
insert into article values (10, '护士是蚂蚁', 9,1,3);
create or replace procedure p(v_pid article.pid%type, v_level binary_integer) is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) := '';
begin
for i in 0..v_level loop
v_preStr := v_preStr || '****';
end loop;
for v_article in c loop
dbms_output.put_line(v_preStr || v_article.cont);
if(v_article.isleaf = 0) then
p(v_article.id, v_level + 1);
end if;
end loop;
end;
end loop;