1. 更改用戶
alter user scott account unlock;
2. 表結構
desc emp;
名稱 是否為空? 類型
----------------------------------------- -------- -------------------
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)
3. dual表
SQL> select 2*3 from dual;
2*3
----------
6
SQL> select sysdate from dual;
SYSDATE
----------
17-8月 -08
4. 改變標題
select sal*12 "Anuual Sal" from emp；
Anuual Sal
----------
9600
19200
15000
35700
15000
5. 字段連接
select ename ||'character' from emp
ENAME||'CHARACTER'
------------------
SMITHcharacter
ALLENcharacter
WARDcharacter
JONEScharacter
MARTINcharacter
BLAKEcharacter
/* 若字符串中本來就有單引號，則用兩個單引號來替代一個單引號 */
select ename ||'charac''ter' from emp
ENAME||'CHARAC''TER'
-------------------
SMITHcharac'ter
ALLENcharac'ter
WARDcharac'ter
JONEScharac'ter
MARTINcharac'ter
BLAKEcharac'ter
6. distinct 去除重復行或重復組合
select distinct deptno from emp
DEPTNO
------
10
20
30
select distinct job from emp
JOB
-----------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
/*將deptno與job重復的組合去掉*/
select distinct deptno, job from emp
DEPTNO JOB
----------------------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
7. where 語句
=
>
<
<>
>=
<=
between and
in (a,b,c)
is (null)
is not (null)
like
not like
.
.
.
select ename from emp where ename like '_A%'
ENAME
------
WARD
MARTIN
JAMES
/* 如果字符串中本來就帶有%, 則加轉義字符 */
select ename from emp where ename like '%/%%'
ENAME
------
W%%D
MART%N
J%AMES
/* 可指定別的符號為轉移字符，以替代默認的反斜杠 */
select ename from emp where ename like '%$%%' escape '$'
8. 排序
select * from dept order by deptno desc
DEPTNO DNAME LOC
-------------------------------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
select ename , sal, deptno from emp order by deptno, ename desc
ENAME SAL DEPTNO
----------------------
MILLER 1300.00 10
KING 5000.00 10
CLARK 2450.00 10
SMITH 800.00 20
SCOTT 3000.00 20
JONES 2975.00 20
FORD 3000.00 20
ADAMS 1100.00 20
WARD 1250.00 30
TURNER 1500.00 30
MARTIN 1250.00 30
JAMES 950.00 30
BLAKE 2850.00 30
ALLEN 1600.00 30
總結如下：
select ename ,sal*12 "Annual Sal" from emp
where ename not like '_A%' and sal>800
order by sal desc
ENAME Annual Sal
------------------
KING 60000
SCOTT 36000
FORD 36000
JONES 35700
BLAKE 34200
CLARK 29400
ALLEN 19200
TURNER 18000
MILLER 15600
ADAMS 13200