Oracle基础问答 与 常用技巧

锁定设置口令:tiger
T7|ZTNfN
w0全局数据库名:orcl
;[Oy(S]A0系统标符:orcl
W9Fy+m5dJl-?0用户名/口令:scott/tiger51Testing软件测试网F @f'S!d
按F10可用IE执行sqlplus

Z(fP"wc+M0错误:ora-28000 the account is locked51Testing软件测试网 ~+Y"{Z2y] a"Pz
解决:新装完Oracle10g后，用scott/tiger测试，会出现以下错误提示：51Testing软件测试网RiAv(y!_
        oracle10g the account is locked
#Gd4GR:bYT!}uf,_0        oracle10g the password has expired51Testing软件测试网 ]y0~)Ma*jXfP#r%{
原因：默认Oracle10g的scott不能登陆。51Testing软件测试网pVP%aU1W;dd4Id
解决：
i:ku!b-I3i+dz \0（1）conn sys/sys as sysdba; //以DBA的身份登录51Testing软件测试网CoI'iO2}+b}3c I
（2）alter user scott account unlock;// 然后解锁51Testing软件测试网 Q!s+Bkyb:]y
（3）conn scott/tiger //弹出一个修改密码的对话框，修改一下密码就可以了51Testing软件测试网 O ~*[-a0Q

#w*cvT tW0在运行里面输入cmd在DOS模式下输入sqlplus，以system用户名登录，密码是刚装oracle时自己填写的密码orcl，登录进去以后。51Testing软件测试网$n]Q0QC~;P;h

1Kl7B0{&l
S/s"UvR0SQL> conn sys/sys as sysdba;       （分号是必须的但是我是以system登录的所在这不应该写conn sys/sys as sysdba应该写conn system/orcl as sysdba;）
8B)FbPtJX-v0         Connected.
g f-f5wv3\ V0SQL> alter user scott account unlock;51Testing软件测试网-H;Bmc&]$]%eiv*D0J
         User altered.51Testing软件测试网o;f,zZ?:K+pA3U.j*F7g
SQL> commit;51Testing软件测试网'ho:d-`0f0Z
         Commit complete.
v%kJcYb0SQL> conn scott/tiger//请输入新密码，并确认后OK
WW*J o6bD0Password changed
4H/c'kv#Mx#Kh0Connected.51Testing软件测试网7d3W|L7@#o`+C/a

.hL9\-C Cb"p\,Rf0授权:51Testing软件测试网H'xC?d2I6x
conn sys/scott as sysdba;51Testing软件测试网/NZQ[#z D\!Y8?
grant create table,create view to scott;

@c$Y ]#P#K(J#_0语句学习:
k:HxQ N5~ w%i0desc dept/salgrade/emp51Testing软件测试网R'cD/zt-F)Jf']A

[:s7i:f!E0o ["^0select * from dept;
9EK+e1uSX0select sal*12 from emp;
X T2j4{oE:]1i(O2Y0select 2*3 from dept;
8j6o!R0F&d+nQ0select ename||sal from emp;(串连接)
+U a'@PQD?0select sal sal_anme from emp;(取别名)51Testing软件测试网$|m-E*p-e2j4d"U[
select sal "sal dd" from emp;()51Testing软件测试网'k:B|5`C
select ename||'afasdf' from emp;(连接)51Testing软件测试网w9b,^|{W]Sa
select ename||'sdfs''sdf' from emp;(用双引号显示单引号)51Testing软件测试网l9mR0`A~#d

select deptno from emp;51Testing软件测试网6]UsA Ha*[7P
select distinct deptno from emp;(distinct去掉重复的)51Testing软件测试网p+NY/keH,m5R
select distinct deptno,job from emp;

%`o_0E
nD{+MM9u6CG@0select * from emp where deptno=10;51Testing软件测试网F1ty
HXSfMZp,Y
select * from emp where ename='CLARK';
N&n K.Lm5d)f0select sal,deptno from emp where deptno!=10;(!=与<>等同)
#U1E ivItQ0select ename,sal from emp where ename>'CBA';51Testing软件测试网@f XyIYX)_;f
select ename,sal from emp where sal between 800 and 1500;(其中包括800和1500)
F4@b Jn#Cy'fy}8\0select ename,sal form emp where sal >=800 and <= 1500;51Testing软件测试网2J2TLx4jQ

select ename,sal,comm from emp where comm is null;(正确的写法,not null)51Testing软件测试网"n]d1F!m^
select ename,sal,comm from emp where comm=null;(错误的写法)51Testing软件测试网'Ja*\L%e?/C{

select ename,sal,comm from emp where sal in (800,1500,2000)(取得等于800,或1500,或2000的值)
t]b0_2R&q.?0select ename,sal,comm from emp where ename in ('SMITH','KING','ABC')(取得满足条件的值)

select ename,sal from emp where deptno=10 and sal>1000; (and可以改成or)
Bb#O:i'g)e2H0select ename,sal from emp where sal not in (800,1500);(只要不是800,1500的)51Testing软件测试网o
Tl+`(nV]

%代表0到多个,_代表一个字符,\代表转义字符51Testing软件测试网5RR pu+Kx7R
select ename from emp where ename like '%ALL%';51Testing软件测试网8o e5`Dv n1a&DY3`
select ename from emp where ename like '_A%';(满足第二个字符为A的)
(te W Vz3x0select ename from emp where ename like '%\%%';(满足有%的)
6`o dM?ls*X3Iv(u0如果不想用\做转义字符.可以这么写:51Testing软件测试网*~djSEz,U$]
select ename from emp where ename like '%$%%' escape '$';(即以$为转义字符)

order by语句(排序)51Testing软件测试网 \f:w0U|!eZ9N
select * from dept;
e"z/J*Ec~]0select * from dept order by deptno desc;51Testing软件测试网k^@G'Hd
C.`
select empno,ename from emp where deptno<> 10 order by empno asc;(先筛选完再排序)
x']Q$M8_3^r{0select ename,sal,deptno from emp order by deptno asc,ename desc;(先按deptno升序后,再按ename降序)

函数的使用:51Testing软件测试网\ `0^W2o1Z7D
单行函数(给多条就产生多条输出):
v/~e#Q|#B[XPE0select lower(ename) from emp; 小写51Testing软件测试网.r\%Mg4TwN*X;e7]:}Z
select ename from emp where lower(ename) like '_a%';51Testing软件测试网T)m7W*k,Yv
w
select ename from emp where ename like '_a%' or ename like '_A%';

select substr(ename,1,3) from emp;(从第一个字符开始写,写三个字符)51Testing软件测试网x!N'l!R8NiQ|#?M f
select substr(ename,2,3) from emp;(从第二个字符开始写写三个字符)51Testing软件测试网.z&V-~7[2nrdW`
select chr(65) from dual;(把65转成ASCII字符)51Testing软件测试网6L2G b/z;nUp9w
select ascii('A') from dual;
k7]\SDf*a0select round(23.652) from dual;(四舍五入)
+^ q!l6Ol0select round(23.652,2) from dual;(四舍五入到小数点后两位)51Testing软件测试网dZG1U,g
select round(23.5443,-1) from dual;(结果20)51Testing软件测试网7Y:S)|I-_T
上面的函数有个概念即可,不记得查一下就好.下面的函数很重要,无论如何记住它们.51Testing软件测试网&y*g ~(W$A*U)q
1.to_char转换日期,数字
B
sy%mx&m[?0select to_char(sal,'$99,999.9999') from emp;(让工资以$99,999.9999的形式显示,其实9就是一位数字)(美元)51Testing软件测试网!B9T BUU7T
D4R#V
select to_char(sal,'L99,999.9999') from emp;(人民币)51Testing软件测试网*M6K a@9_ y-Y
select to_char(sal,'L00000.000') from emp;(统一格式,不足的以0补齐)51Testing软件测试网8f^a V$F
Y
select to_date(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;51Testing软件测试网4yRB$C r6W/F
select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;(把当前日期转成24小时制的.默认是12进制的)51Testing软件测试网XpN5st4@
select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS') ;
3@Z![k"e0select sal from emp where sal> to_number('$1,250.00','$9,999.99');51Testing软件测试网F9Y+ZM;W R`I
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from emp;
ctM#pv lv0select ename,sal*12+comm from emp;(这样只要表达式中有一个空值,结果就为空)
N h \3U},U'eR0select ename,sal*12+nvl(comm,0) from emp;51Testing软件测试网Pk0M]o4@/b1MD

#EO2x2Ll-G8ge0多行(组)函数(给出一条或者多条,只会有一行输出)51Testing软件测试网K4k*g{hf,W
select max(sal) from emp;51Testing软件测试网 oHt2TM
select min(sal) from emp;
3Yah t0}Nc0select avg(sal) from emp;
Ql P$D7D_-GMr0select to_char(avg(sal),'999999.99') from emp;51Testing软件测试网0J6L#RjW:M7|_B
select round(avg(sal),2) from emp;51Testing软件测试网8l.GnXvi&l7L2e
select sum(sal) from emp;统计总额
h%CN/fQ$h0select count(*) from emp;统计记录
8xp4z)sg,}9w0select count(*) from emp where deptno=10;求出部门号为10的人数为多少.
S/{ N3q7Hh0select count(comm) from emp;(只要不为空值就算一个)
u${c1nG.o0select count(distinct deptno) from emp;对非重复的条目进行统计51Testing软件测试网*Ts
H ?U:i4aD%N

p3M"Bbp7~0select deptno, avg(sal) from emp group by deptno;先按部门分组,再把各组数据平均值
+hxWB|p,K'j+s0select deptno,job, max(sal) from emp group by deptno,job;(按照两个字段来分组,同时满足)51Testing软件测试网fqPa/T2K%fAG'R

select ename from emp where sal=(select max(sal) from emp);取得工资最高的所有人

select deptno,max(sal) from emp group by deptno;(select中的字段,不在group内就要在组函数内)
(` Q4E&Fj8p-sj,m0select avg(sal),deptno from emp group by deptno;每个部门的平均薪水51Testing软件测试网Ej|_B:dR

where语句只对当前的记录进行过滤,不能在group by过后过滤,此时就要用到having51Testing软件测试网`C ^5H/t/vk7a
select avg(sal) ,deptno from emp group by deptno having avg(sal) >2000;51Testing软件测试网v2X m7l
P2q9hl1Z

记住一个重要的顺序,不能错:
#t
q7yKU-vne,}t0select * from emp
ro~j]8L9ez1S;S0where sal > 1000   条件限制
t-iBde,}#Zs0b0group by deptno    分组51Testing软件测试网Q(l@X$?U
having             限制
-I8zy8H6^0order by           排序51Testing软件测试网+Xo^^ao(e

t,x$I:w
z,y+T0例子:51Testing软件测试网[ MXR+H4p9xSNw&e:N
r y
select avg(sal)
,S-v:va+QYUk`&a0from emp51Testing软件测试网? m+| _l {
where sal>1200  51Testing软件测试网n^l'W*B%AJ7i^
U
group by deptno
5h'x|Gu"gR0having avg(sal) >1500
8RN]hxOA)T0order by avg(sal) desc;51Testing软件测试网0[3@:Zj+p7{c\

x6Tf7J_N{i8X0子查询(在select语句里套select语句):51Testing软件测试网rj-Dp Y$T0t*r
select ename,sal from emp where sal=(select max(sal) from emp);51Testing软件测试网;L in v v
select ename,sal from emp where sal>(select avg(sal) from emp);
m/A6}v-d t0select ename,sal ,deptno from emp where sal=(select max(sal) from emp group by deptno);(错误句,因为子查询的结果可能不只一个)

0b:yt5m[&l\"}*v0select ename,sal from emp
} WI*z'o7Et }8Z0join (select max(sal) max_sal,deptno from emp group by deptno) t 把select max(sal) max_sal,deptno from emp group by deptno查询的结果当成一张表t51Testing软件测试网e`;s)rU T mJ
on (emp.sal=t.max_sal and emp.deptno=t.deptno);                   进行表连接51Testing软件测试网tG:q.};~,YoN%E

自连接,连接相同的两表并取得对应的值51Testing软件测试网,Q"VIc?H,A#m.\
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

交叉连接(cross join)51Testing软件测试网X$w-s
]\['qO
select ename,dname from emp cross join dept;51Testing软件测试网%G]z t1P d
等值连接:
~q.X\)f0select ename,dname from emp,dept where emp.deptno=dept.deptno;    以前的写法51Testing软件测试网*Ep2MYaV#p3p
select ename,dname from emp join dept on (emp.deptno=dept.deptno);现在的写法
$UUDO3E,Y0select ename,dname from emp join dept using (deptno);另一种写法,但不推荐用
,Ma2N$UA$YaOc0不等值连接:51Testing软件测试网H:l^ g%?
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);51Testing软件测试网eD'y#i$o8{
]T)C

select ename,dname,grade from51Testing软件测试网'D\ FCV*D{%`:G
emp e join dept d on (e.deptno=d.deptno)51Testing软件测试网;MvUY"C] D6g
join salgrade s on (e.sal between s.losal and s.hisal)51Testing软件测试网Tr{SlK1z&\r
where ename not like '_A%';

4Y,h&C
t^$z!Z(R!x'zp0select e1.ename,e2.ename form emp e1 join emp e2 on (e1.mgr=e2.empno);(自连接的新语法)

显示空行的方式(左,右外连接)51Testing软件测试网6Hp
[9^7i:Z/cV7zI7e
select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);(左外连接,即便满足条件的右边没有数据也会显示出来)
)l@-L@t7E;Y0select ename,dname from emp e right outer join dept d on (e.deptno=d.deptno);(右外连接)51Testing软件测试网.z@#R.q9J3vh1OFUT
select ename,dname form emp e full join dept d on (e.deptno=d.deptno);(全外连接)

--求部门平均薪水等级:
7l&|b;B[:nZ0select deptno,avg_sal,grade from51Testing软件测试网1qhN8\,F9ILT
(select deptno,avg(sal) avg_sal from emp group by deptno) t
2SEFh c(dp0join salgrade s on (t.avg_sal between s.losal and s.hisal);
e;R3p\y
G
^0--求部门中哪些人的薪水最高
4c8ZoSD%{ W0select ename,sal from emp
nr(d S SB9ZZ0join (select max(sal) max_sal,deptno from emp group by deptno) t
_F!k6?f#B ZV0on (emp.sal=t.max_sal and emp.deptno=t.deptno);51Testing软件测试网n0^ g!A-ja
--求部门平均的薪水等级(平均所有的等级)
X1P6r!e)x"q7])@xa0select avg(grade) from51Testing软件测试网5}h+y$DD)EgA2s
(select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t
!{u,Jw3WVY0HA0group by deptno;51Testing软件测试网D)g|U8n.oo
--雇员中有哪些人是经理人51Testing软件测试网j4Z,{_0V@qo.{ x
select ename from emp where empno in (select distinct mgr from emp);
fEM2v a,~Mhf0--不准用组函数,求薪水的最高值(面试题)51Testing软件测试网
qE0V-i'cn
思路:左右两张同样的表,找左边的薪水比右边的薪水高,再找左右边对应不上的薪水值即为最高值.51Testing软件测试网O9U Bv)G(Ds*D
第一步:51Testing软件测试网X"{+XSc#l
select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal);51Testing软件测试网c5SC&N `%ymgz
第二步:51Testing软件测试网7w"H7J#SK,muV
select distinct sal from emp where sal not in51Testing软件测试网B{gOw(^
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));51Testing软件测试网+E;sB'V]`n
--求平均薪水最高的部门的部门编号
ujwx(z,P0第一步(每个部门的平均薪水):
Rc0LX{0select avg(sal),deptno from emp group by deptno;51Testing软件测试网 SC8D'o/s f
第二步,对上面求出的求最大值
Q v)[H'S'^&aM.K/b
?0select max(avg_sal) from51Testing软件测试网eT8pN,NAZ|
(select avg(sal) avg_sal,deptno from emp group by deptno);51Testing软件测试网U p)L+t|P?
第三步:
RZC+W/FtOw0select deptno,avg_sal from
W"e
l'u]#r|W9g0(select avg(sal) avg_sal,deptno from emp group by deptno)51Testing软件测试网l[/q:l9y@
where avg_sal=51Testing软件测试网h4i_F2F9q
(select max(avg_sal) from51Testing软件测试网zflu)S+d
(select avg(sal) avg_sal,deptno from emp group by deptno));51Testing软件测试网)Q!a#gC?rX$p;g4eD
修改:
E_BE;_0select deptno,avg_sal from
,hbUJC'W^0(select avg(sal) avg_sal,deptno from emp group by deptno)
6T`8p#Z)e'F UR0where avg_sal=51Testing软件测试网W0i3]3C?-X0H,b ~
(select max(avg(sal)) from emp group by deptno));51Testing软件测试网"|lsHQ!s9m
--求平均薪水最高的部门的部门名称51Testing软件测试网Gj@4CK
select dname from dept where deptno=
0]8c;pkT~}%l0(
L#B+O JM}5_
n/f*Ck#^5c0select deptno,avg_sal from51Testing软件测试网hwsW1j&L]
    (select avg(sal) avg_sal,deptno from emp group by deptno)
m5R|h!]}#h2JGx0where avg_sal=
K.zk3P1Ki7L5z r0    (select max(avg_sal) from51Testing软件测试网sl+?JQ
(select avg(sal) avg_sal,deptno from emp group by deptno))51Testing软件测试网|#l k3wt'i8ppd
);51Testing软件测试网]NojG3x
--求平均薪水的等级最低的部门的部门名称51Testing软件测试网9x"F/l(U!m2{+n$]
1.select deptno,avg(sal) avg_sal from emp group by deptno51Testing软件测试网*XR9_'jq
2.select deptno,grade,avg_sal from51Testing软件测试网aZr-YMw!f
(select deptno,avg(sal) avg_sal from emp group by deptno) t
#N;Vqzrn.|#MB q0join salgrade s on (t.avg_sal between s.losal and s.hisal)
p8g:w$^M03.select min(grade) from51Testing软件测试网*Vlo W)kQO.F
(select deptno,grade,avg_sal from51Testing软件测试网m'n S0W;Ra7NdP
(select deptno,avg(sal) avg_sal from emp group by deptno) t51Testing软件测试网J W x M A5c J(Mc:Xy[
join salgrade s on (t.avg_sal between s.losal and s.hisal));
Y6oTO0T%UB3PT04.51Testing软件测试网^5d\8B)AP
select danme,t1.deptno,grade,avg_sal from
7S,v3gGz'{};mu&Fy0(select deptno,grade,avg_sal from51Testing软件测试网4[.}9q9[O J4y ~N
(select deptno,avg(sal) avg_sal from emp group by deptno) t51Testing软件测试网kq2|.B'xB
join salgrade s on (t.avg_sal between s.losal and s.hisal)) t1
+xt0_3j(t
plo0join dept on (t1.deptno=dept.deptno)
N_8{a)X3L0where t1.grade=
pE/_(EO(H,V3W3f0(select min(grade) from
8H1_[,n1k3D0(select deptno,grade,avg_sal from
H%E%lZ/y6W^O0(select deptno,avg(sal) avg_sal from emp group by deptno) t51Testing软件测试网J"Z/d5na~9j*]
join salgrade s on (t.avg_sal between s.losal and s.hisal)));51Testing软件测试网:r-I@ X:qO
改进(利用视图View,简单理解视图就是一张表或是一个子查询):
I%_k5YNQf)zqB0create view v$_dept_avg_sal_info as
4_"db%],hm0select deptno,grade,avg_sal from
rX7[g"oc0(select deptno,avg(sal) avg_sal from emp group by deptno) t
9Q.|A)s%\4Z;\0join salgrade s on (t.avg_sal between s.losal and s.hisal)

select danme,t1.deptno,grade,avg_sal from
!G5F0N:qgUB*Yp)c'K(O0( v$_dept_avg_sal_info t1
*xf&Q1tAct1td(Q0join dept on (t1.deptno=dept.deptno)
z O_n];}0where t1.grade=51Testing软件测试网$EpDC+[
(select min(grade) from51Testing软件测试网b/S8g]p(j0Cr.g
v$_dept_avg_sal_info);51Testing软件测试网5K)}*Bx0[!o M%["Y