修改字符集
使用用戶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>
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
