Oracle基础问答 与 常用技巧

锁定设置口令:tiger51Testing软件测试网iA r"Zm&C_
全局数据库名:orcl
#E
m#w9Hq"y8sZ\k0系统标符:orcl51Testing软件测试网Z d!M.N$s3@,p9Qir;i
用户名/口令:scott/tiger51Testing软件测试网m*[Of
Uh.l
按F10可用IE执行sqlplus51Testing软件测试网"?O*Yb-I

错误:ora-28000 the account is locked
ly$V4M]s#{Kq0解决:新装完Oracle10g后，用scott/tiger测试，会出现以下错误提示：
6JD:y!u\@ La W0        oracle10g the account is locked
Q({5T#\9K4E0K5wQ0        oracle10g the password has expired51Testing软件测试网IxpHm@5S\
原因：默认Oracle10g的scott不能登陆。
*^2Y!q fPj}/l0解决：51Testing软件测试网NY!\!U+y%svE9W
（1）conn sys/sys as sysdba; //以DBA的身份登录51Testing软件测试网{^#^/HN fyV
（2）alter user scott account unlock;// 然后解锁
3|+w7u J"Jb0（3）conn scott/tiger //弹出一个修改密码的对话框，修改一下密码就可以了51Testing软件测试网fB(mT,z,j ~I

在运行里面输入cmd在DOS模式下输入sqlplus，以system用户名登录，密码是刚装oracle时自己填写的密码orcl，登录进去以后。

B8E;yd|Nd
|0SQL> conn sys/sys as sysdba;       （分号是必须的但是我是以system登录的所在这不应该写conn sys/sys as sysdba应该写conn system/orcl as sysdba;）51Testing软件测试网6v
soo)I#N/PZ/Ow#k
         Connected.51Testing软件测试网AS9n1n/I&a
SQL> alter user scott account unlock;51Testing软件测试网 d$Lr}/G1|g0mV\2tL
         User altered.
J(Pmxo+rH;r$W0SQL> commit;
A$@O
pr:yD(ywyK+W0         Commit complete.51Testing软件测试网,W,WFO4g@
SQL> conn scott/tiger//请输入新密码，并确认后OK51Testing软件测试网f l5y9@Sp:X#b/x\S,p
Password changed
Jd(A4Y3WT%RB0Connected.51Testing软件测试网\zqj%sM1@'vvK

n n6Tae pi0授权:51Testing软件测试网'`c3xY:A,Mo
conn sys/scott as sysdba;51Testing软件测试网!T&J%o D$}9@m(M
grant create table,create view to scott;

语句学习:51Testing软件测试网7DIgh"\6|"W
desc dept/salgrade/emp51Testing软件测试网J2T)^M?_8]HH

select * from dept;51Testing软件测试网U J;o
On#DB+l
select sal*12 from emp;
{rCws!ky um(vW0select 2*3 from dept;51Testing软件测试网_ VlNz-{Y$k
select ename||sal from emp;(串连接)
.\x2Xb,z{ds5H0select sal sal_anme from emp;(取别名)
8T*vz*y!Dd}q2|0select sal "sal dd" from emp;()
:JE~'q$^D0select ename||'afasdf' from emp;(连接)
cd%Q1KMwA0select ename||'sdfs''sdf' from emp;(用双引号显示单引号)

|rkvK]0select deptno from emp;51Testing软件测试网&JmS5]UC_:y
select distinct deptno from emp;(distinct去掉重复的)
Z
`([[1`C;\#FK0select distinct deptno,job from emp;

]&W&a^k ~up0select * from emp where deptno=10;
bO,T rP:g&K2q+~5f6S\0select * from emp where ename='CLARK';51Testing软件测试网G
S QPk%X;P.d7h
select sal,deptno from emp where deptno!=10;(!=与<>等同)
#s} N@unD^0select ename,sal from emp where ename>'CBA';51Testing软件测试网:CRWOB{&L&h m9[2`
select ename,sal from emp where sal between 800 and 1500;(其中包括800和1500)51Testing软件测试网lY6vlO!?(^ R
select ename,sal form emp where sal >=800 and <= 1500;

select ename,sal,comm from emp where comm is null;(正确的写法,not null)51Testing软件测试网-ZGsD
u'~o1B4r
select ename,sal,comm from emp where comm=null;(错误的写法)51Testing软件测试网3P5h1c
A D;VJo&V8G

H"\*WHh0select ename,sal,comm from emp where sal in (800,1500,2000)(取得等于800,或1500,或2000的值)51Testing软件测试网6p#^-Y sL _q_
select ename,sal,comm from emp where ename in ('SMITH','KING','ABC')(取得满足条件的值)

eq3Bf6t/Y0select ename,sal from emp where deptno=10 and sal>1000; (and可以改成or)
3K_h%iY;Y6M0select ename,sal from emp where sal not in (800,1500);(只要不是800,1500的)51Testing软件测试网S+cDO*?GS

lp
e xo0%代表0到多个,_代表一个字符,\代表转义字符51Testing软件测试网1CF{/x*R%\
select ename from emp where ename like '%ALL%';51Testing软件测试网(DRx#jd,@F
select ename from emp where ename like '_A%';(满足第二个字符为A的)
*Bp4m0k1H ^T0select ename from emp where ename like '%\%%';(满足有%的)
!VDa%bA$H!M'e0如果不想用\做转义字符.可以这么写:
&q;Su{EHy0select ename from emp where ename like '%$%%' escape '$';(即以$为转义字符)

%wiS`[8|Yk-X~0order by语句(排序)51Testing软件测试网CPX1i%kog1^L
select * from dept;51Testing软件测试网 L&mn(Kd B2Y |
select * from dept order by deptno desc;
+Je(K(@-s0select empno,ename from emp where deptno<> 10 order by empno asc;(先筛选完再排序)51Testing软件测试网v2d4^2c2DE
select ename,sal,deptno from emp order by deptno asc,ename desc;(先按deptno升序后,再按ename降序)

X.z0l@\1QJo0函数的使用:51Testing软件测试网$}tDb jK kr f.O
单行函数(给多条就产生多条输出):
8H
eo0z[-}:? y0select lower(ename) from emp; 小写
Q.q5@uY-_Mk0select ename from emp where lower(ename) like '_a%';
8l2`1dfR3S6r3ob(P0select ename from emp where ename like '_a%' or ename like '_A%';

select substr(ename,1,3) from emp;(从第一个字符开始写,写三个字符)
dDe0U"o9p0select substr(ename,2,3) from emp;(从第二个字符开始写写三个字符)
/iehqq3U&d7V0select chr(65) from dual;(把65转成ASCII字符)
A!M4m;igC5E0select ascii('A') from dual;51Testing软件测试网Q@G&_ j.w*ys
select round(23.652) from dual;(四舍五入)
9WO[X.T$O7ql5p3v:C0select round(23.652,2) from dual;(四舍五入到小数点后两位)51Testing软件测试网/Z/s4U!X)zB
select round(23.5443,-1) from dual;(结果20)
G_/z(U Rk,cnY'f0上面的函数有个概念即可,不记得查一下就好.下面的函数很重要,无论如何记住它们.51Testing软件测试网2DPiB@0Q
1.to_char转换日期,数字
Yo3m,q0na0select to_char(sal,'$99,999.9999') from emp;(让工资以$99,999.9999的形式显示,其实9就是一位数字)(美元)51Testing软件测试网.Ku6Oq"\
select to_char(sal,'L99,999.9999') from emp;(人民币)51Testing软件测试网Zv9r-i/W_
select to_char(sal,'L00000.000') from emp;(统一格式,不足的以0补齐)51Testing软件测试网8d.YwWH XaZ `#a
select to_date(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;51Testing软件测试网|#z5FT#O| F;P
select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;(把当前日期转成24小时制的.默认是12进制的)
mf`KBohv
xm0select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS') ;51Testing软件测试网)Mubs2~
select sal from emp where sal> to_number('$1,250.00','$9,999.99');
"Bu0KP"|7\0select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;51Testing软件测试网we l!M{ Y.E
select ename,sal*12+comm from emp;(这样只要表达式中有一个空值,结果就为空)
Dn&t"`3G&XM
sg0select ename,sal*12+nvl(comm,0) from emp;

多行(组)函数(给出一条或者多条,只会有一行输出)51Testing软件测试网PwiRv1p E
select max(sal) from emp;51Testing软件测试网:W ImON5{-c
select min(sal) from emp;
&X%q!}e
Q0select avg(sal) from emp;51Testing软件测试网:i5}*HW8mq4mZ.D
select to_char(avg(sal),'999999.99') from emp;
5Pc+Vb_1B:o0select round(avg(sal),2) from emp;51Testing软件测试网/e:l
`uZ-q
select sum(sal) from emp;统计总额51Testing软件测试网nU ?q!Rh\VBC
select count(*) from emp;统计记录
Z W
r&nL H:?\)dCx:\0select count(*) from emp where deptno=10;求出部门号为10的人数为多少.
H*?#h8jC
a(eND0select count(comm) from emp;(只要不为空值就算一个)51Testing软件测试网 RnT!nW PGj
select count(distinct deptno) from emp;对非重复的条目进行统计

O?"l3I[Q0F0select deptno, avg(sal) from emp group by deptno;先按部门分组,再把各组数据平均值
j{B9l!Q(X0select deptno,job, max(sal) from emp group by deptno,job;(按照两个字段来分组,同时满足)51Testing软件测试网\f6I%H6M6gR*de

select ename from emp where sal=(select max(sal) from emp);取得工资最高的所有人51Testing软件测试网A4U,UH;}}?:A*s

;g&]#E4jr&I}0select deptno,max(sal) from emp group by deptno;(select中的字段,不在group内就要在组函数内)
)N%i4@ng}0select avg(sal),deptno from emp group by deptno;每个部门的平均薪水51Testing软件测试网7jC!g nb`

tD(]
]ja0where语句只对当前的记录进行过滤,不能在group by过后过滤,此时就要用到having51Testing软件测试网!] ~/B8R
C8CJr
select avg(sal) ,deptno from emp group by deptno having avg(sal) >2000;51Testing软件测试网 g3c-u#h/z Y-kc

记住一个重要的顺序,不能错:
/ZNL$n Y"G2^)r,W0select * from emp
p?&{|:ap0where sal > 1000   条件限制
JKy;De4_0group by deptno    分组51Testing软件测试网{8w3V3Hu
having             限制
e5B&dzYL)U0order by           排序

m}!@ f;i9yO0例子:
|
@%[t]5FsyxZ(h0select avg(sal)51Testing软件测试网5y JKNQda%y4l
from emp51Testing软件测试网{3A~;?r+P6Oj{+V1\8q
where sal>1200  51Testing软件测试网^-|7m5hf
Etnv(u
group by deptno51Testing软件测试网]6S5GBYW@
having avg(sal) >1500
HPPNs&F2?lxZ0order by avg(sal) desc;

子查询(在select语句里套select语句):
,RN"MW-P!v0select ename,sal from emp where sal=(select max(sal) from emp);
.d&B v(S$M
Vy{0select ename,sal from emp where sal>(select avg(sal) from emp);51Testing软件测试网"I2z,`v]9tZe
select ename,sal ,deptno from emp where sal=(select max(sal) from emp group by deptno);(错误句,因为子查询的结果可能不只一个)51Testing软件测试网Z\*[xa`_

select ename,sal from emp51Testing软件测试网f'HkU.?.e1r
join (select max(sal) max_sal,deptno from emp group by deptno) t 把select max(sal) max_sal,deptno from emp group by deptno查询的结果当成一张表t51Testing软件测试网9Fc8z4p#d;g-u
on (emp.sal=t.max_sal and emp.deptno=t.deptno);                   进行表连接

自连接,连接相同的两表并取得对应的值51Testing软件测试网t,?.sH9qV+sWe:|KA
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

交叉连接(cross join)
-I+R5ZS8f&O c/Va0select ename,dname from emp cross join dept;
:Z&roA"F#b&z:?
t*Y(f0等值连接:51Testing软件测试网s&CB"ZU
select ename,dname from emp,dept where emp.deptno=dept.deptno;    以前的写法51Testing软件测试网Qg*t6s9pQ3?
select ename,dname from emp join dept on (emp.deptno=dept.deptno);现在的写法
.~%O-z1vg1w0select ename,dname from emp join dept using (deptno);另一种写法,但不推荐用51Testing软件测试网"^ K WAf
不等值连接:51Testing软件测试网l`!aDh\s
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);51Testing软件测试网9J
X_qU

select ename,dname,grade from
r0dxNs\0emp e join dept d on (e.deptno=d.deptno)51Testing软件测试网b^eAFk+k0~
join salgrade s on (e.sal between s.losal and s.hisal)51Testing软件测试网 ] N KS7In1R~t
where ename not like '_A%';

select e1.ename,e2.ename form emp e1 join emp e2 on (e1.mgr=e2.empno);(自连接的新语法)

?$K1rc5OVh4VAg7_0显示空行的方式(左,右外连接)
Yi,Mh} F0W0select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);(左外连接,即便满足条件的右边没有数据也会显示出来)
2p'Y_+l%x~0select ename,dname from emp e right outer join dept d on (e.deptno=d.deptno);(右外连接)
3N$g|O@-@0select ename,dname form emp e full join dept d on (e.deptno=d.deptno);(全外连接)51Testing软件测试网){5k~:QX.P+l

'gZ O\
D6u;yn[0--求部门平均薪水等级:51Testing软件测试网u8F;OI8c&e5{8u
select deptno,avg_sal,grade from51Testing软件测试网hB6Q'T'E] s tU
(select deptno,avg(sal) avg_sal from emp group by deptno) t
V Ku f_U0join salgrade s on (t.avg_sal between s.losal and s.hisal);
R#d0~zJ4@2e0--求部门中哪些人的薪水最高51Testing软件测试网\;gli6r
select ename,sal from emp51Testing软件测试网d1AH u5^
join (select max(sal) max_sal,deptno from emp group by deptno) t51Testing软件测试网#vXu]hd!u
on (emp.sal=t.max_sal and emp.deptno=t.deptno);
q AX&v8XJ;qx0--求部门平均的薪水等级(平均所有的等级)
I/Si#X s5gi5f0d0select avg(grade) from
VL^cx.rz'N M1IMX0(select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t51Testing软件测试网MHH(d+s:}_c
group by deptno;51Testing软件测试网bGL\
{$C$s B
--雇员中有哪些人是经理人51Testing软件测试网?i+V5Gz?(]j]
select ename from emp where empno in (select distinct mgr from emp);
o'Ta`k t0--不准用组函数,求薪水的最高值(面试题)51Testing软件测试网f7k+Jv0|r/B/{t
思路:左右两张同样的表,找左边的薪水比右边的薪水高,再找左右边对应不上的薪水值即为最高值.51Testing软件测试网#[+l(w&e7rV,iXJ]3w*h
第一步:
%F\d7pB0select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal);51Testing软件测试网C+]O@M&c(e
第二步:
-W9L%hf-k*KHh y0select distinct sal from emp where sal not in

zbddb[)k,_0(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
2t`*B
]lC0--求平均薪水最高的部门的部门编号
n
H V5x4@~0第一步(每个部门的平均薪水):51Testing软件测试网#?
~.R%~P]4op
select avg(sal),deptno from emp group by deptno;
|'Yn ^A-cd[0第二步,对上面求出的求最大值51Testing软件测试网 dQ^^2Z}7H+[/g
select max(avg_sal) from
/UJ(OVWRoc O.?0(select avg(sal) avg_sal,deptno from emp group by deptno);
-^WZ,G0m/Z0第三步:51Testing软件测试网+e^1^.Y'KJ
select deptno,avg_sal from
(b4S#`Za2J2y$K0(select avg(sal) avg_sal,deptno from emp group by deptno)
P(j6f \6OW0Sm0where avg_sal=51Testing软件测试网kZYVO+c [
(select max(avg_sal) from
/o"L{H5k#R;v$?'f)Lc0(select avg(sal) avg_sal,deptno from emp group by deptno));51Testing软件测试网X+[&DO5|nl2U
修改:
RX"aYt{$|o0select deptno,avg_sal from51Testing软件测试网n&F ONz3L+r4v
(select avg(sal) avg_sal,deptno from emp group by deptno)51Testing软件测试网+l7PX9hp;ZT
where avg_sal=
\6Lx2}$z-n#|:CM0(select max(avg(sal)) from emp group by deptno));51Testing软件测试网Fmv.I[+Rl)x
--求平均薪水最高的部门的部门名称51Testing软件测试网0KVCjXO5Ou,Hr(r H
select dname from dept where deptno=
0s|*fzS([?n0(
0\9j`J#b6N'p}0select deptno,avg_sal from51Testing软件测试网-I7[k5k?n#yR
    (select avg(sal) avg_sal,deptno from emp group by deptno)
e2n a-mP*zfL0where avg_sal=
3Ty@.ATC(T*I_0    (select max(avg_sal) from
Dq d9_jgF0(select avg(sal) avg_sal,deptno from emp group by deptno))51Testing软件测试网 ITVn^wwW
);51Testing软件测试网 o6^4b!\#HF F
--求平均薪水的等级最低的部门的部门名称51Testing软件测试网;a]i!lV%S8ug
1.select deptno,avg(sal) avg_sal from emp group by deptno51Testing软件测试网e(c-`l$]V+x
2.select deptno,grade,avg_sal from51Testing软件测试网$e*h@+It v
(select deptno,avg(sal) avg_sal from emp group by deptno) t51Testing软件测试网d hF*F^ cO
join salgrade s on (t.avg_sal between s.losal and s.hisal)51Testing软件测试网K }X1}-].D8]
u{)z
3.select min(grade) from51Testing软件测试网 Hcboe)s
(select deptno,grade,avg_sal from
)eO1s*kou&cb v1Js0(select deptno,avg(sal) avg_sal from emp group by deptno) t51Testing软件测试网S)H9N{uZ~@
join salgrade s on (t.avg_sal between s.losal and s.hisal));
;})L8u7gA*A,H\04.
mU},z,{(z5C[H+J0select danme,t1.deptno,grade,avg_sal from51Testing软件测试网/C!X5O*A&J;G`
(select deptno,grade,avg_sal from
/s7~sS}^dP0(select deptno,avg(sal) avg_sal from emp group by deptno) t51Testing软件测试网3@$h[%Tb-SD}
join salgrade s on (t.avg_sal between s.losal and s.hisal)) t151Testing软件测试网X5[m Qc;N
join dept on (t1.deptno=dept.deptno)51Testing软件测试网'[Nj?-P9a pW
where t1.grade=51Testing软件测试网7K0N!MM6nw
(select min(grade) from
.Xe#M#U2T^;sC~|%U Z0(select deptno,grade,avg_sal from51Testing软件测试网1TBC2BA3[
(select deptno,avg(sal) avg_sal from emp group by deptno) t
0^_Io'xEV2_o0join salgrade s on (t.avg_sal between s.losal and s.hisal)));51Testing软件测试网!^-U,v3E^_`o
改进(利用视图View,简单理解视图就是一张表或是一个子查询):
5j,Zn0D%Y+H'A#\8[6]P]0create view v$_dept_avg_sal_info as
;^ZG_*y'{v1h5l+d0select deptno,grade,avg_sal from51Testing软件测试网J5d)Ee9?m3`
(select deptno,avg(sal) avg_sal from emp group by deptno) t51Testing软件测试网2T7n5|,~:h![&gC6b*oo
join salgrade s on (t.avg_sal between s.losal and s.hisal)51Testing软件测试网VL*vn'zT T

select danme,t1.deptno,grade,avg_sal from
+I\\XV+pP0( v$_dept_avg_sal_info t1
4D)k%Si'Tn$h0join dept on (t1.deptno=dept.deptno)
d1RDp!\{A,MOk7N0where t1.grade=51Testing软件测试网1o5}+BDrUU
(select min(grade) from
TT0GmD~ t9JQ0v$_dept_avg_sal_info);51Testing软件测试网&n^3g_Q&d