修改字符集
使用用户system，通过sqlplus程序连接到Oracle数据库，输入以下命令，查看字符集：
SQL
>
select userenv('language') from
dual;
USERENV(
'LANGUAGE')
--
--------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL
>
我们可以看到查到的字符集为“ AMERICAN_AMERICA.AL32UTF8 ”，而国内数据库常用的字符集为“ ZHS16GBK ”。
例如：某字段在企业版中定义的为varchar2(4)， 保存数据为''田田''，则导入oraclexe时，该字段定义仍为varchar2(4)，但数据''田田''就需要占用6个字符长度，出现的问题如下所示：
IMP-00019: 由于 ORACLE 错误 12899 而拒绝行
IMP-00003: 遇到 ORACLE 错误 12899
ORA-12899: 列 "TEST"."TEST_TIANYC"."A" 的值太大 (实际值: 6, 最大值: 4)
此时就需要按照以下操作说明进行操作后即可解决问题。
操作说明：
开始菜单-->所有程序-->Oracle Database 11g Express Edition-->运行SQL命令行，启动sqlplus程序。
由于sqlplus程序不支持直接在程序窗口中右键，进行复制、粘贴等操作，所以博主采用的是在运行命令行中启动sqlplus程序。
开始菜单-->运行-->输入cmd，并回车-->打开cmd命令行。
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Candy>
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog
SQL*Plus: Release 11.2.0.2.0 Production on 星期三 11月 26 12:11:59 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL>
connect system as sysdba
输入口令:
已连接。
SQL>
shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>
startup mount
ORACLE 例程已经启动。
Total System Global Area 644468736 bytes
Fixed Size 1385488 bytes
Variable Size 192941040 bytes
Database Buffers 444596224 bytes
Redo Buffers 5545984 bytes
数据库装载完毕。
SQL>
alter system enable restricted session;
系统已更改。
SQL>
alter system set JOB_QUEUE_PROCESSES=0;
系统已更改。
SQL>
alter system set AQ_TM_PROCESSES=0;
系统已更改。
SQL>
alter database open;
数据库已更改。
SQL>
alter database character set internal_use ZHS16GBK;
数据库已更改。
SQL>
shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>
startup
ORACLE 例程已经启动。
Total System Global Area 644468736 bytes
Fixed Size 1385488 bytes
Variable Size 192941040 bytes
Database Buffers 444596224 bytes
Redo Buffers 5545984 bytes
数据库装载完毕。
数据库已经打开。
SQL>
select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SQL>
此时，再进行数据导入或者在字段类型为varchar2(4)的字段插入“田田”，就不会报错了。
修改字符集后可能导致navicat连不上Oracle数据库，请在菜单-->工具-->选项-->其他-->OCI中将OCI library(oci.dll)的值修改为
C:\oraclexe\app\oracle\product\11.2.0\server\bin\oci.dll，点击确定后，重启navicat即可。
Oracle自带的Scott用户下的Demo库的SQL
DROP
TABLE
EMP;
DROP
TABLE
DEPT;
DROP
TABLE
BONUS;
DROP
TABLE
SALGRADE;
CREATE
TABLE
DEPT (
DEPTNO
NUMBER
(
2
)
CONSTRAINT
PK_DEPT
PRIMARY
KEY
,
DNAME
VARCHAR2
(
14
),
LOC
VARCHAR2
(
13
)
);
CREATE
TABLE
EMP (
EMPNO
NUMBER
(
4
)
CONSTRAINT
PK_EMP
PRIMARY
KEY
,
ENAME
VARCHAR2
(
10
),
JOB
VARCHAR2
(
9
),
MGR
NUMBER
(
4
),
HIREDATE DATE,
SAL
NUMBER
(
7
,
2
),
COMM
NUMBER
(
7
,
2
),
DEPTNO
NUMBER
(
2
)
CONSTRAINT
FK_DEPTNO
REFERENCES
DEPT
);
INSERT
INTO
DEPT
VALUES
(
10
,
'
ACCOUNTING
'
,
'
NEW YORK
'
);
INSERT
INTO
DEPT
VALUES
(
20
,
'
RESEARCH
'
,
'
DALLAS
'
);
INSERT
INTO
DEPT
VALUES
(
30
,
'
SALES
'
,
'
CHICAGO
'
);
INSERT
INTO
DEPT
VALUES
(
40
,
'
OPERATIONS
'
,
'
BOSTON
'
);
INSERT
INTO
EMP
VALUES
(
7369
,
'
SMITH
'
,
'
CLERK
'
,
7902
, TO_DATE (
'
17-12-1980
'
,
'
dd-mm-yyyy
'
),
800
,
NULL
,
20
);
INSERT
INTO
EMP
VALUES
(
7499
,
'
ALLEN
'
,
'
SALESMAN
'
,
7698
, TO_DATE (
'
20-2-1981
'
,
'
dd-mm-yyyy
'
),
1600
,
300
,
30
);
INSERT
INTO
EMP
VALUES
(
7521
,
'
WARD
'
,
'
SALESMAN
'
,
7698
, TO_DATE (
'
22-2-1981
'
,
'
dd-mm-yyyy
'
),
1250
,
500
,
30
);
INSERT
INTO
EMP
VALUES
(
7566
,
'
JONES
'
,
'
MANAGER
'
,
7839
, TO_DATE (
'
2-4-1981
'
,
'
dd-mm-yyyy
'
),
2975
,
NULL
,
20
);
INSERT
INTO
EMP
VALUES
(
7654
,
'
MARTIN
'
,
'
SALESMAN
'
,
7698
, TO_DATE (
'
28-9-1981
'
,
'
dd-mm-yyyy
'
),
1250
,
1400
,
30
);
INSERT
INTO
EMP
VALUES
(
7698
,
'
BLAKE
'
,
'
MANAGER
'
,
7839
, TO_DATE (
'
1-5-1981
'
,
'
dd-mm-yyyy
'
),
2850
,
NULL
,
30
);
INSERT
INTO
EMP
VALUES
(
7782
,
'
CLARK
'
,
'
MANAGER
'
,
7839
, TO_DATE (
'
9-6-1981
'
,
'
dd-mm-yyyy
'
),
2450
,
NULL
,
10
);
INSERT
INTO
EMP
VALUES
(
7788
,
'
SCOTT
'
,
'
ANALYST
'
,
7566
, TO_DATE (
'
13-07-87
'
,
'
dd-mm-rr
'
)
-
85
,
3000
,
NULL
,
20
);
INSERT
INTO
EMP
VALUES
(
7839
,
'
KING
'
,
'
PRESIDENT
'
,
NULL
, TO_DATE (
'
17-11-1981
'
,
'
dd-mm-yyyy
'
),
5000
,
NULL
,
10
);
INSERT
INTO
EMP
VALUES
(
7844
,
'
TURNER
'
,
'
SALESMAN
'
,
7698
, TO_DATE (
'
8-9-1981
'
,
'
dd-mm-yyyy
'
),
1500
,
0
,
30
);
INSERT
INTO
EMP
VALUES
(
7876
,
'
ADAMS
'
,
'
CLERK
'
,
7788
, TO_DATE (
'
13-07-87
'
,
'
dd-mm-rr
'
)
-
51
,
1100
,
NULL
,
20
);
INSERT
INTO
EMP
VALUES
(
7900
,
'
JAMES
'
,
'
CLERK
'
,
7698
, TO_DATE (
'
3-12-1981
'
,
'
dd-mm-yyyy
'
),
950
,
NULL
,
30
);
INSERT
INTO
EMP
VALUES
(
7902
,
'
FORD
'
,
'
ANALYST
'
,
7566
, TO_DATE (
'
3-12-1981
'
,
'
dd-mm-yyyy
'
),
3000
,
NULL
,
20
);
INSERT
INTO
EMP
VALUES
(
7934
,
'
MILLER
'
,
'
CLERK
'
,
7782
, TO_DATE (
'
23-1-1982
'
,
'
dd-mm-yyyy
'
),
1300
,
NULL
,
10
);
CREATE
TABLE
BONUS (
ENAME
VARCHAR2
(
10
),
JOB
VARCHAR2
(
9
),
SAL
NUMBER
,
COMM
NUMBER
);
CREATE
TABLE
SALGRADE (
GRADE
NUMBER
,
LOSAL
NUMBER
,
HISAL
NUMBER
);
INSERT
INTO
SALGRADE
VALUES
(
1
,
700
,
1200
);
INSERT
INTO
SALGRADE
VALUES
(
2
,
1201
,
1400
);
INSERT
INTO
SALGRADE
VALUES
(
3
,
1401
,
2000
);
INSERT
INTO
SALGRADE
VALUES
(
4
,
2001
,
3000
);
INSERT
INTO
SALGRADE
VALUES
(
5
,
3001
,
9999
);
COMMIT
;
练习题目
根据Oracle数据库scott模式下的emp表和dept表，完成下列操作。
（
1
） 查询20号部门的所有员工信息。
select
*
from
emp
where
deptno
=
20
;
（
2
） 查询所有工种为CLERK的员工的工号、员工名和部门名。
select
empno,ename,deptno
from
emp
where
job
like
'
CLERK
'
;
（
3
） 查询奖金（COMM）高于工资（SAL）的员工信息。
select
*
from
emp
where
comm
>
sal;
（
4
） 查询奖金高于工资的20
%
的员工信息。
select
*
from
emp
where
comm
>
(sal
*
0.2
);
（
5
） 查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。
select
*
from
emp
where
(deptno
=
10
and
job
like
'
MANAGER
'
)
or
(deptno
=
20
and
job
like
'
CLERK
'
);
（
6
） 查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息。
select
*
from
emp
where
job
not
in
(
'
MANAGER
'
,
'
CLERK
'
)
and
sal
>=
2000
;
（
7
） 查询有奖金的员工的不同工种。
select
distinct
job
from
emp
where
comm
is
not
null
;
（
8
） 查询所有员工工资和奖金的和。
select
ename,(sal
+
nvl(comm,
0
)) salcomm
from
emp;
（
9
） 查询没有奖金或奖金低于100的员工信息。
select
*
from
emp
where
(comm
is
null
or
comm
<
100
) ;
（
10
） 查询各月倒数第2天入职的员工信息。
select
*
from
emp
where
hiredate
in
(
select
(last_day(hiredate)
-
1
)
from
emp);
（
11
） 查询员工工龄大于或等于10年的员工信息。
select
*
from
emp
where
(sysdate
-
hiredate)
/
365
>=
10
;
（
12
） 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
select
upper
(substr(ename,
1
,
1
))
||
lower
(substr(ename,
2
,length(ename)
-
1
))
from
emp;
（
13
） 查询员工名正好为6个字符的员工的信息。
select
*
from
emp
where
length（ename）
=
6
;
（
14
） 查询员工名字中不包含字母“S”员工。
select
*
from
emp
where
ename
not
in
(
select
ename
from
emp
where
ename
like
'
%S%
'
) ;
select
*
from
emp
where
ename
not
like
‘
%
S
%
’;
（
15
） 查询员工姓名的第2个字母为“M”的员工信息。
select
*
from
emp
where
ename
like
'
_M%
'
;
（
16
） 查询所有员工姓名的前3个字符。
select
substr(ename,
1
,
3
)
from
emp ;
（
17
） 查询所有员工的姓名，如果包含字母“s”，则用“S”替换。
select
replace
(ename,
'
s
'
,
'
S
'
)
from
emp ;
（
18
） 查询员工的姓名和入职日期，并按入职日期从先到后进行排列。
select
ename,hiredate
from
emp
order
by
hiredate
asc
;
（
19
） 显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列。
select
ename,job,sal,comm
from
emp
order
by
job
desc
,sal
asc
;
（
20
） 显示所有员工的姓名、入职的年份和月份，若入职日期所在的月份排序，若月份相同则按入职的年份排序。
select
ename,to_char(hiredate,
'
yyyy
'
)
||
'
-
'
||
to_char(hiredate,
'
mm
'
)
from
emp
order
by
to_char(hiredate,
'
mm
'
),to_char(hiredate,
'
yyyy
'
);
（
21
） 查询在2月份入职的所有员工信息。
select
*
from
emp
where
to_char(hiredate,
'
mm
'
)
=
2
;
（
22
） 查询所有员工入职以来的工作期限，用“
**
年
**
月
**
日”的形式表示。
select
ename,
floor
((sysdate
-
hiredate)
/
365
)
||
'
年
'
||
floor
(mod((sysdate
-
hiredate),
365
)
/
30
)
||
'
月
'
||
cell(mod(mod((sysdate
-
hiredate),
365
),
30
))
||
'
天
'
from
emp ;
（
23
） 查询至少有一个员工的部门信息。
select
*
from
dept
where
deptno
in
(
select
distinct
deptno
from
emp
where
mgr
is
not
null
) ;
（
24
） 查询工资比SMITH员工工资高的所有员工信息。
select
*
from
emp
where
sal
>
(
select
sal
from
emp
where
ename
like
'
SMITH
'
) ;
（
25
） 查询所有员工的姓名及其直接上级的姓名。
select
staname,ename supname
from
(
select
ename staname,mgr
from
emp) t
join
emp
on
t.mgr
=
emp.empno ;
（
26
） 查询入职日期早于其直接上级领导的所有员工信息。
select
*
from
emp
where
empno
in
(
select
staempno
from
(
select
empno staempno,hiredate stahiredate,mgr
from
emp) t
join
emp
on
t.mgr
=
emp.empno
and
stahiredate
<
hiredate) ;
（
27
） 查询所有部门及其员工信息，包括那些没有员工的部门。
select
*
from
dept
left
join
emp
on
emp.deptno
=
dept.deptno
order
by
dept.deptno ;
（
28
） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。
（
29
） 查询所有工种为CLERK的员工的姓名及其部门名称。
select
ename,dname
from
emp
join
dept
on
job
like
'
CLERK
'
and
emp.deptno
=
dept.deptno ;
（
30
） 查询最低工资大于2500的各种工作。
select
job
from
(
select
min
(sal) min_sal,job
from
emp
group
by
job)
where
min_sal
>
2500
;
（
31
） 查询最低工资低于2000的部门及其员工信息。
select
*
from
emp
where
deptno
in
(
select
deptno
from
(
select
min
(sal) min_sal,deptno
from
emp
group
by
deptno)
where
min_sal
<
'
2000
'
) ;
（
32
） 查询在SALES部门工作的员工的姓名信息。
select
ename
from
emp
where
deptno
=
(
select
deptno
from
dept
where
dname
like
'
SALES
'
);
（
33
） 查询工资高于公司平均工资的所有员工信息。
select
*
from
emp
where
sal
>
(
select
avg
(sal)
from
emp) ;
（
34
） 查询与SMITH员工从事相同工作的所有员工信息。
select
*
from
emp
where
job
in
(
select
job
from
emp
where
ename
like
'
SMITH
'
)
and
ename
not
like
'
SMITH
'
;
（
35
） 列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。
select
ename,sal
from
emp
where
sal
=any
(
select
sal
from
emp
where
deptno
=
30
) ;
（
36
） 查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资。
select
ename,sal
from
emp
where
sal
>all
(
select
sal
from
emp
where
deptno
=
30
) ;
（
37
） 查询每个部门中的员工数量、平均工资和平均工作年限。
select
dname,
count
,avg_sal,avg_date
from
dept
join
(
select
count
(
*
)
count
,
avg
(sal) avg_sal,
avg
((sysdate
-
hiredate)
/
365
) avg_date,deptno
from
emp
group
by
deptno) t
on
dept.deptno
=
t.deptno ;
（
38
） 查询从事同一种工作但不属于同一部门的员工信息。
select
distinct
t1.empno,t1.ename,t1.deptno
from
emp t1
join
emp t2
on
t1.job
like
t2.job
and
t1.deptno
<>
t2.deptno ;
（
39
） 查询各个部门的详细信息以及部门人数、部门平均工资。
Select
dept.
*
,person_num,avg_sal
from
dept,(
select
count
(
*
) person_num,
avg
(sal) avg_sal,deptno
from
emp
group
by
deptno) t
where
dept.deptno
=
t.deptno ;
（
40
） 查询各种工作的最低工资。
select
job,
min
(sal)
from
emp
group
by
job ;
（
41
） 查询各个部门中的不同工种的最高工资。
select
max
(sal),job,deptno
from
emp
group
by
deptno,job
order
by
deptno,job ；
（
42
） 查询10号部门员工以及领导的信息。
select
*
from
emp
where
empno
in
(
select
mgr
from
emp
where
deptno
=
10
)
or
deptno
=
10
；
（
43
） 查询各个部门的人数及平均工资。
select
deptno,
count
(
*
),
avg
(sal)
from
emp
group
by
deptno ；
（
44
） 查询工资为某个部门平均工资的员工信息。
select
*
from
emp
where
sal
in
(
select
avg
(sal) avg_sal
from
emp
group
by
deptno) ;
（
45
） 查询工资高于本部门平均工资的员工的信息。
select
emp.
*
from
emp
join
(
select
deptno,
avg
(sal) avg_sal
from
emp
group
by
deptno) t
on
emp.deptno
=
t.deptno
and
sal
>
avg_sal ;
（
46
） 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
select
emp.
*
,avg_sal
from
emp
join
(
select
deptno,
avg
(sal) avg_sal
from
emp
group
by
deptno) t
on
emp.deptno
=
t.deptno
and
sal
>
avg_sal ;
（
47
） 查询工资高于20号部门某个员工工资的员工的信息。
select
*
from
emp
where
sal
>any
(
select
sal
from
emp
where
deptno
=
20
);
（
48
） 统计各个工种的人数与平均工资。
select
job,
count
(
*
),
avg
(sal)
from
emp
group
by
job ;
（
49
） 统计每个部门中各个工种的人数与平均工资。
select
deptno,job,
count
(
*
),
avg
(sal)
from
emp
group
by
deptno,job
order
by
deptno,job;
（
50
） 查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。
select
emp.
*
from
emp
join
(
select
sal,comm
from
emp
where
deptno
=
10
) t
on
emp.sal
=
t.sal
and
nvl(emp.comm,
0
)
=
nvl(t.comm,
0
)
and
emp.deptno
!=
10
;
（
51
） 查询部门人数大于5的部门的员工的信息。
select
*
from
emp
where
deptno
in
(
select
deptno
from
emp
group
by
deptno
having
count
(
*
)
>
5
);
（
52
） 查询所有员工工资都大于1000的部门的信息。
select
*
from
dept
where
deptno
in
(
select
distinct
deptno
from
emp
where
deptno
not
in
(
select
distinct
deptno
from
emp
where
sal
<
1000
)) ;
（
53
） 查询所有员工工资都大于1000的部门的信息及其员工信息。
select
*
from
emp
join
dept
on
dept.deptno
in
(
select
distinct
deptno
from
emp
where
deptno
not
in
(
select
distinct
deptno
from
emp
where
sal
<
1000
))
and
dept.deptno
=
emp.deptno;
（
54
） 查询所有员工工资都在900
~
3000之间的部门的信息。
select
*
from
dept
where
deptno
in
(
select
distinct
deptno
from
emp
where
deptno
not
in
(
select
distinct
deptno
from
emp
where
sal
not
between
900
and
3000
)) ;
（
55
） 查询所有工资都在900
~
3000之间的员工所在部门的员工信息。
select
*
from
emp
where
deptno
in
(
select
distinct
deptno
from
emp
where
deptno
not
in
(
select
distinct
deptno
from
emp
where
sal
not
between
900
and
3000
)) ;
（
56
） 查询每个员工的领导所在部门的信息。
select
*
from
(
select
e1.empno,e1.ename,e1.mgr mno,e2.ename mname,e2.deptno
from
emp e1
join
emp e2
on
e1.mgr
=
e2.empno) t
join
dept
on
t.deptno
=
dept.deptno ;
（
57
） 查询人数最多的部门信息。
select
*
from
dept
where
deptno
in
(
select
deptno
from
(
select
count
(
*
)
count
,deptno
from
emp
group
by
deptno)
where
count
in
(
select
max
(
count
)
from
(
select
count
(
*
)
count
,deptno
from
emp
group
by
deptno)));
（
58
） 查询30号部门中工资排序前3名的员工信息。
select
*
from
emp
where
empno
in
(
select
empno
from
(
select
empno,sal
from
emp
where
deptno
=
30
order
by
sal
desc
)
where
rownum
<
4
) ;
（
59
） 查询所有员工中工资排在5
~
10名之间的员工信息。
select
*
from
emp
where
empno
in
(
select
empno
from
(
select
empno,rownum num
from
(
select
empno,sal
from
emp
order
by
sal
desc
))
where
num
between
5
and
10
) ;
select
empno
from
(
select
empno,sal
from
emp
order
by
sal
desc
)
where
rownum
<=
10
minus
select
empno
from
(
select
empno,sal
from
emp
order
by
sal
desc
)
where
rownum
<
5
;
（
60
） 查询SMITH员工及所有其直接、间接下属员工的信息。
（
61
） 查询SOCTT员工及其直接、间接上级员工的信息。
（
62
） 以树状结构查询所有员工与领导之间的层次关系。
（
63
） 向emp表中插入一条记录，员工号为1357，员工名字为oracle，工资为2050元，部门号为20，入职日期为2002年5月10日。
insertinto emp(empno,ename,sal,deptno,hiredate)
values
(
1357
,
'
oracle
'
,
2050
,
20
,to_date(
'
2002年5月10日
'
,
'
yyyy"年"mm"月"dd"日"
'
)) ;
（
64
） 向emp表中插入一条记录，员工名字为FAN，员工号为8000，其他信息与SMITH员工的信息相同。
（
65
） 将各部门员工的工资修改为该员工所在部门平均工资加1000。
update
emp t1
set
sal
=
(
select
new_sal
from
(
select
avg
(sal)
+
1000
new_sal,deptno
from
emp
group
by
deptno) t2 wher e t1.deptno
=
t2.deptno ) ;
解锁用户、设置密码
使用sqlplus程序登录
SQL*Plus: Release 11.2.0.2.0 Production on 星期四 11月 27 15:58:01 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> connect sys as sysdba
输入口令:
已连接。
SQL> alter user HR account unlock;
用户已更改。
SQL> alter user HR identified by 123456;
用户已更改。
SQL>
