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