Oracle-1

来源：互联网发布：大学软件学院排名编辑：程序博客网时间：2024/06/09 22:40

SQL> --清屏

SQL> host cls

SQL> --当前用户

SQL> show user

USER 为 "SCOTT"

SQL> --当前用户下的表

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

SQL> --员工表的结构

SQL> 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)

SQL> --查询所有的员工信息

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- -------------- ---------- ----------

DEPTNO

----------

7369 SMITH CLERK 7902 17-12月-80 800

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300

7521 WARD SALESMAN 7698 22-2月 -81 1250 500

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- -------------- ---------- ----------

DEPTNO

----------

7566 JONES MANAGER 7839 02-4月 -81 2975

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400

7698 BLAKE MANAGER 7839 01-5月 -81 2850

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- -------------- ---------- ----------

DEPTNO

----------

7782 CLARK MANAGER 7839 09-6月 -81 2450

7788 SCOTT ANALYST 7566 19-4月 -87 3000

7839 KING PRESIDENT 17-11月-81 5000

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- -------------- ---------- ----------

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0

7876 ADAMS CLERK 7788 23-5月 -87 1100

7900 JAMES CLERK 7698 03-12月-81 950

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- -------------- ---------- ----------

DEPTNO

----------

7902 FORD ANALYST 7566 03-12月-81 3000

7934 MILLER CLERK 7782 23-1月 -82 1300

已选择 14 行。

SQL> --设置行宽

SQL> show linesize

linesize 80

SQL> set linesize 150

SQL> --设置列宽

SQL> col ename for a8

SQL> col sal for 9999

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno

2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> /*

SQL> SQL优化（数据库的优化）

SQL> 1. 尽量使用列名

SQL> */

SQL> --查询员工信息：员工号姓名月薪

SQL> select empno,ename,sal

2 form emp;

form emp

第 2 行出现错误:

ORA-00923: 未找到要求的 FROM 关键字

SQL> --c命令 change

SQL> 2

2* form emp

SQL> c /form/from

2* from emp

SQL> /

EMPNO ENAME SAL

---------- -------- -----

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7566 JONES 2975

7654 MARTIN 1250

7698 BLAKE 2850

7782 CLARK 2450

7788 SCOTT 3000

7839 KING 5000

7844 TURNER 1500

7876 ADAMS 1100

EMPNO ENAME SAL

---------- -------- -----

7900 JAMES 950

7902 FORD 3000

7934 MILLER 1300

已选择 14 行。

SQL> --查询员工信息：员工号姓名月薪年薪

SQL> select empno,ename,sal,sal*12

2 from emp;

EMPNO ENAME SAL SAL*12

---------- -------- ----- ----------

7369 SMITH 800 9600

7499 ALLEN 1600 19200

7521 WARD 1250 15000

7566 JONES 2975 35700

7654 MARTIN 1250 15000

7698 BLAKE 2850 34200

7782 CLARK 2450 29400

7788 SCOTT 3000 36000

7839 KING 5000 60000

7844 TURNER 1500 18000

7876 ADAMS 1100 13200

EMPNO ENAME SAL SAL*12

---------- -------- ----- ----------

7900 JAMES 950 11400

7902 FORD 3000 36000

7934 MILLER 1300 15600

已选择 14 行。

SQL> --查询员工信息：员工号姓名月薪年薪奖金年收入

SQL> select empno,ename,sal,sal*12,comm,sal*12+comm

2 from emp;

EMPNO ENAME SAL SAL*12 COMM SAL*12+COMM

---------- -------- ----- ---------- ---------- -----------

7369 SMITH 800 9600

7499 ALLEN 1600 19200 300 19500

7521 WARD 1250 15000 500 15500

7566 JONES 2975 35700

7654 MARTIN 1250 15000 1400 16400

7698 BLAKE 2850 34200

7782 CLARK 2450 29400

7788 SCOTT 3000 36000

7839 KING 5000 60000

7844 TURNER 1500 18000 0 18000

7876 ADAMS 1100 13200

EMPNO ENAME SAL SAL*12 COMM SAL*12+COMM

---------- -------- ----- ---------- ---------- -----------

7900 JAMES 950 11400

7902 FORD 3000 36000

7934 MILLER 1300 15600

已选择 14 行。

SQL> host cls

SQL> /*

SQL> SQL中的null值:

SQL> 1. 包含null的表达式都为null

SQL> 2. null永远!=null

SQL> */

SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

2 from emp;

EMPNO ENAME SAL SAL*12 COMM SAL*12+NVL(COMM,0)

---------- -------- ----- ---------- ---------- ------------------

7369 SMITH 800 9600 9600

7499 ALLEN 1600 19200 300 19500

7521 WARD 1250 15000 500 15500

7566 JONES 2975 35700 35700

7654 MARTIN 1250 15000 1400 16400

7698 BLAKE 2850 34200 34200

7782 CLARK 2450 29400 29400

7788 SCOTT 3000 36000 36000

7839 KING 5000 60000 60000

7844 TURNER 1500 18000 0 18000

7876 ADAMS 1100 13200 13200

EMPNO ENAME SAL SAL*12 COMM SAL*12+NVL(COMM,0)

---------- -------- ----- ---------- ---------- ------------------

7900 JAMES 950 11400 11400

7902 FORD 3000 36000 36000

7934 MILLER 1300 15600 15600

已选择 14 行。

SQL> --2. null永远!=null

SQL> --查询奖金为null的员工

SQL> select *

2 from emp

3 where comm=null;

未选定行

SQL> select *

2 from emp

3 where comm is null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 10 行。

SQL> host cls

SQL> --列的别名

SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0)

2 from emp;

EMPNO ENAME SAL SAL*12 COMM SAL*12+NVL(COMM,0)

---------- -------- ----- ---------- ---------- ------------------

7369 SMITH 800 9600 9600

7499 ALLEN 1600 19200 300 19500

7521 WARD 1250 15000 500 15500

7566 JONES 2975 35700 35700

7654 MARTIN 1250 15000 1400 16400

7698 BLAKE 2850 34200 34200

7782 CLARK 2450 29400 29400

7788 SCOTT 3000 36000 36000

7839 KING 5000 60000 60000

7844 TURNER 1500 18000 0 18000

7876 ADAMS 1100 13200 13200

EMPNO ENAME SAL SAL*12 COMM SAL*12+NVL(COMM,0)

---------- -------- ----- ---------- ---------- ------------------

7900 JAMES 950 11400 11400

7902 FORD 3000 36000 36000

7934 MILLER 1300 15600 15600

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1 select empno as "员工号",ename "姓名",sal 月薪,sal*12,comm,sal*12+nvl(comm,0)

2* from emp

SQL> /

select empno as "员工号",ename "姓名",sal 月薪,sal*12,comm,sal*12+nvl(comm,0)

第 1 行出现错误:

ORA-00923: 未找到要求的 FROM 关键字

SQL> ed

已写入 file afiedt.buf

1 select empno as "员工号",ename "姓名",sal "月薪",sal*12,comm,sal*12+nvl(comm,0)

2* from emp

SQL> /

员工号姓名月薪 SAL*12 COMM SAL*12+NVL(COMM,0)

---------- ---------- ---------- ---------- ---------- ------------------

7369 SMITH 800 9600 9600

7499 ALLEN 1600 19200 300 19500

7521 WARD 1250 15000 500 15500

7566 JONES 2975 35700 35700

7654 MARTIN 1250 15000 1400 16400

7698 BLAKE 2850 34200 34200

7782 CLARK 2450 29400 29400

7788 SCOTT 3000 36000 36000

7839 KING 5000 60000 60000

7844 TURNER 1500 18000 0 18000

7876 ADAMS 1100 13200 13200

员工号姓名月薪 SAL*12 COMM SAL*12+NVL(COMM,0)

---------- ---------- ---------- ---------- ---------- ------------------

7900 JAMES 950 11400 11400

7902 FORD 3000 36000 36000

7934 MILLER 1300 15600 15600

已选择 14 行。

SQL> host cls

SQL> --distinct去掉重复记录

SQL> select deptno from emp;

DEPTNO

----------

DEPTNO

----------

已选择 14 行。

SQL> select distinct deptno from emp;

DEPTNO

----------

SQL> select job from emp;

JOB

---------

CLERK

SALESMAN

MANAGER

SALESMAN

MANAGER

ANALYST

PRESIDENT

SALESMAN

CLERK

JOB

---------

CLERK

ANALYST

CLERK

已选择 14 行。

SQL> select distinct job from emp;

JOB

---------

CLERK

SALESMAN

PRESIDENT

MANAGER

ANALYST

SQL> select distinct deptno, job from emp;

DEPTNO JOB

---------- ---------

20 CLERK

30 SALESMAN

20 MANAGER

30 CLERK

10 PRESIDENT

30 MANAGER

10 CLERK

10 MANAGER

20 ANALYST

已选择 9 行。

SQL> --distinct作用于后面所有的列

SQL> host cls

SQL> --连接符

SQL> -- concat

SQL> select concat('Hello',' World');

select concat('Hello',' World')

第 1 行出现错误:

ORA-00923: 未找到要求的 FROM 关键字

SQL> select concat('Hello',' World') from emp;

CONCAT('HELL

------------

Hello World

CONCAT('HELL

------------

Hello World

已选择 14 行。

SQL> select concat('Hello',' World') from dual;

CONCAT('HELL

------------

Hello World

SQL> select 3+2 from dual;

3+2

----------

SQL> --dual 表：伪表

SQL> (伪列)

2 /

(伪列)

第 1 行出现错误:

ORA-00928: 缺失 SELECT 关键字

SQL> select 'Hello'||' World' 字符串 from dual;

字符串

------------

Hello World

SQL> --查询员工信息： ***的薪水是****

SQL> select ename||'的薪水是'||sal 信息 from emp;

信息

----------------------------------------------------------

SMITH的薪水是800

ALLEN的薪水是1600

WARD的薪水是1250

JONES的薪水是2975

MARTIN的薪水是1250

BLAKE的薪水是2850

CLARK的薪水是2450

SCOTT的薪水是3000

KING的薪水是5000

TURNER的薪水是1500

ADAMS的薪水是1100

信息

----------------------------------------------------------

JAMES的薪水是950

FORD的薪水是3000

MILLER的薪水是1300

已选择 14 行。

SQL> host cls

SQL> --字符串

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> spool off

SQL> --查询10号部门的员工

SQL> select * from emp

2 where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7839 KING PRESIDENT 17-11月-81 5000 10

7934 MILLER CLERK 7782 23-1月 -82 1300 10

SQL> --字符大小写敏感

SQL> --查询名叫KING的员工

SQL> select *

2 from emp

3 where ename='KING';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

SQL> rf

SP2-0042: 未知命令 "rf" - 其余行忽略。

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where ename='King'

SQL> /

未选定行

SQL> --日期格式敏感

SQL> --查询入职日期是17-11月-81的员工

SQL> select *

2 from emp

3 where hiredate='17-11月-81';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where hiredate='1981-11-17'

SQL> /

where hiredate='1981-11-17'

第 3 行出现错误:

ORA-01861: 文字与格式字符串不匹配

SQL> --修改日期格式

SQL> select * from v$nls_parameters;

PARAMETER VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

NLS_LANGUAGE SIMPLIFIED CHINESE

NLS_TERRITORY CHINA

NLS_CURRENCY ￥

NLS_ISO_CURRENCY CHINA

NLS_NUMERIC_CHARACTERS .,

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE SIMPLIFIED CHINESE

NLS_CHARACTERSET ZHS16GBK

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY ￥

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

已选择 19 行。

SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

会话已更改。

SQL> select *

2 from emp

3 where hiredate='1981-11-17';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- ---------- ----- ---------- ----------

7839 KING PRESIDENT 1981-11-17 5000 10

SQL> select *

2 from emp

3 where hiredate='17-11月-81';

where hiredate='17-11月-81'

第 3 行出现错误:

ORA-01861: 文字与格式字符串不匹配

SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';

会话已更改。

SQL> host cls

SQL> --between and

SQL> --查询薪水1000~2000之间的员工

SQL> select *

2 from emp

3 where sal between 1000 and 2000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 6 行。

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where sal between 2000 and 1000

SQL> /

未选定行

SQL> --between and: 1. 含有边界 2. 小值在前大值在后

SQL> host cls

SQL> --in 在集合中

SQL> --查询10和20号部门的员工

SQL> select *

2 from emp

3 where deptno in (10,20);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 8 行。

SQL> --查询不是10和20号部门的员工

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where deptno not in (10,20)

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7900 JAMES CLERK 7698 03-12月-81 950 30

已选择 6 行。

SQL> --null 值 3. 如果集合中含有null，不能使用not in; 但可以使用in

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where deptno not in (10,20,null)

SQL> /

未选定行

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where deptno in (10,20,null)

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 8 行。

SQL> --思考：上面的原因是什么?

SQL> host cls

SQL> --like 模糊查询

SQL> --查询名字以S打头的员工

SQL> select *

2 from emp

3 where ename like 'S%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

SQL> --查询名字是4个字的员工

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where ename like '____'

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7839 KING PRESIDENT 17-11月-81 5000 10

7902 FORD ANALYST 7566 03-12月-81 3000 20

SQL> insert into emp(empno,ename,sal,deptno) values(1001,'Tom_AB',3000,10);

已创建 1 行。

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

1001 Tom_AB 3000 10

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 15 行。

SQL> --查询名字中含有下划线的员工

SQL> select *

2 from emp

3 where ename like '%_%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

1001 Tom_AB 3000 10

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 15 行。

SQL> --转意字符

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* where ename like '%\_%' escape '\'

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

1001 Tom_AB 3000 10

SQL> rollback;

回退已完成。

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> --Oracle是自动开启事务

SQL> --SQL 优化 2. where解析顺序：右--》左

SQL> host cls

SQL> --排序

SQL> --查询员工信息按照月薪排序

SQL> select * from emp order by sal;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7934 MILLER CLERK 7782 23-1月 -82 1300 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7902 FORD ANALYST 7566 03-12月-81 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

已选择 14 行。

SQL> --order by后面 + 列，表达式，别名，序号

SQL> select empno,ename,sal,sal*12

2 from emp

3 order by sal*12 desc;

EMPNO ENAME SAL SAL*12

---------- -------- ----- ----------

7839 KING 5000 60000

7902 FORD 3000 36000

7788 SCOTT 3000 36000

7566 JONES 2975 35700

7698 BLAKE 2850 34200

7782 CLARK 2450 29400

7499 ALLEN 1600 19200

7844 TURNER 1500 18000

7934 MILLER 1300 15600

7521 WARD 1250 15000

7654 MARTIN 1250 15000

EMPNO ENAME SAL SAL*12

---------- -------- ----- ----------

7876 ADAMS 1100 13200

7900 JAMES 950 11400

7369 SMITH 800 9600

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1 select empno,ename,sal,sal*12 年薪

2 from emp

3* order by 年薪 desc

SQL> /

EMPNO ENAME SAL 年薪

---------- -------- ----- ----------

7839 KING 5000 60000

7902 FORD 3000 36000

7788 SCOTT 3000 36000

7566 JONES 2975 35700

7698 BLAKE 2850 34200

7782 CLARK 2450 29400

7499 ALLEN 1600 19200

7844 TURNER 1500 18000

7934 MILLER 1300 15600

7521 WARD 1250 15000

7654 MARTIN 1250 15000

EMPNO ENAME SAL 年薪

---------- -------- ----- ----------

7876 ADAMS 1100 13200

7900 JAMES 950 11400

7369 SMITH 800 9600

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1 select empno,ename,sal,sal*12 年薪

2 from emp

3* order by 4 desc

SQL> /

EMPNO ENAME SAL 年薪

---------- -------- ----- ----------

7839 KING 5000 60000

7902 FORD 3000 36000

7788 SCOTT 3000 36000

7566 JONES 2975 35700

7698 BLAKE 2850 34200

7782 CLARK 2450 29400

7499 ALLEN 1600 19200

7844 TURNER 1500 18000

7934 MILLER 1300 15600

7521 WARD 1250 15000

7654 MARTIN 1250 15000

EMPNO ENAME SAL 年薪

---------- -------- ----- ----------

7876 ADAMS 1100 13200

7900 JAMES 950 11400

7369 SMITH 800 9600

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1 select empno,ename,sal,sal*12 年薪

2 from emp

3* order by 5 desc

SQL> /

order by 5 desc

第 3 行出现错误:

ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目

SQL> host cls

SQL> --多个列排序

SQL> select *

2 from emp

3 order by deptno,sal;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7934 MILLER CLERK 7782 23-1月 -82 1300 10

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7839 KING PRESIDENT 17-11月-81 5000 10

7369 SMITH CLERK 7902 17-12月-80 800 20

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7902 FORD ANALYST 7566 03-12月-81 3000 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* order by deptno,sal desc

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7934 MILLER CLERK 7782 23-1月 -82 1300 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7902 FORD ANALYST 7566 03-12月-81 3000 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7369 SMITH CLERK 7902 17-12月-80 800 20

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7900 JAMES CLERK 7698 03-12月-81 950 30

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3* order by deptno desc,sal desc

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7369 SMITH CLERK 7902 17-12月-80 800 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> --order by 作用于后面所有的列；desc只作用于离他最近的列

SQL> host cls

SQL> --查询员工信息按照奖金排序

SQL> --null值 4. null的排序

SQL> select * from emp order by comm;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7369 SMITH CLERK 7902 17-12月-80 800 20

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

已选择 14 行。

SQL> set pagesize 20

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7369 SMITH CLERK 7902 17-12月-80 800 20

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1* select * from emp order by comm desc

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7902 FORD ANALYST 7566 03-12月-81 3000 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7934 MILLER CLERK 7782 23-1月 -82 1300 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

已选择 14 行。

SQL> ed

已写入 file afiedt.buf

1 select *

2 from emp

3 order by comm desc

4* nulls last

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- -------- --------- ---------- -------------- ----- ---------- ----------

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7369 SMITH CLERK 7902 17-12月-80 800 20

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

已选择 14 行。

SQL> --原因：null值最大

SQL> spool off

SQL> --字符函数

SQL> select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写

2 from dual;

转小写转大写首字母大写

----------- ----------- -----------

hello world HELLO WORLD Hello World

SQL> --substr(a,b) 从a中，第b位开始取

SQL> select substr('Hello World',3) 子串 from dual;

子串

---------

llo World

SQL> --substr(a,b,c) 从a中，第b位开始取,取c位

SQL> select substr('Hello World',3,4) 子串 from dual;

子串

----

llo

SQL> --instr(a,b)

SQL> select instr('Hello World','ll') 位置 from dual;

位置

----------

SQL> --length 字符数 lengthb 字节数

SQL> select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;

字符字节

---------- ----------

11 11

SQL> ed

已写入 file afiedt.buf

1* select length('北京') 字符,lengthb('北京') 字节 from dual

SQL> /

字符字节

---------- ----------

2 4

SQL> --lpad 左填充 rpad 右填充

SQL> -- abcd ---> 10 位

SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;

左右

---------- ----------

******abcd abcd******

SQL> --trim 去掉前后指定的字符

SQL> select trim('H' from 'Hello WorldH') from dual;

TRIM('H'FR

----------

ello World

SQL> --replace

SQL> select replace('Hello World','l','*') from dual;

REPLACE('HE

-----------

He**o Wor*d

SQL> host cls

SQL> --四舍五入

SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,

2 round(45.926,-1) 四,round(45.926,-2) 五

3 from dual;

一二三四五

---------- ---------- ---------- ---------- ----------

45.93 45.9 46 50 0

SQL> --截断

SQL> ed

已写入 file afiedt.buf

1 select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,

2 trunc(45.926,-1) 四,trunc(45.926,-2) 五

3* from dual

SQL> /

一二三四五

---------- ---------- ---------- ---------- ----------

45.92 45.9 45 40 0

SQL> --查询当前时间

SQL> select sysdate from dual;

SYSDATE

--------------

22-11月-15

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2015-11-22 15:05:22

SQL> --昨天今天明天

SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;

昨天今天明天

-------------- -------------- --------------

21-11月-15 22-11月-15 23-11月-15

SQL> --计算员工的工龄：天星期月年

SQL> select ename,hiredate, (sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,

2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年

3 from emp;

ENAME HIREDATE 天星期月年

---------- -------------- ---------- ---------- ---------- ----------

SMITH 17-12月-80 12758.6314 1822.66163 425.287713 34.9551545

ALLEN 20-2月 -81 12693.6314 1813.37591 423.121046 34.7770723

WARD 22-2月 -81 12691.6314 1813.0902 423.05438 34.7715928

JONES 02-4月 -81 12652.6314 1807.51877 421.75438 34.6647435

MARTIN 28-9月 -81 12473.6314 1781.94734 415.787713 34.1743326

BLAKE 01-5月 -81 12623.6314 1803.37591 420.787713 34.5852915

CLARK 09-6月 -81 12584.6314 1797.80448 419.487713 34.4784422

SCOTT 19-4月 -87 10444.6314 1492.0902 348.15438 28.6154285

KING 17-11月-81 12423.6314 1774.80448 414.121046 34.0373463

TURNER 08-9月 -81 12493.6314 1784.80448 416.45438 34.2291271

ADAMS 23-5月 -87 10410.6314 1487.23306 347.021046 28.5222778

ENAME HIREDATE 天星期月年

---------- -------------- ---------- ---------- ---------- ----------

JAMES 03-12月-81 12407.6314 1772.51877 413.587713 33.9935107

FORD 03-12月-81 12407.6314 1772.51877 413.587713 33.9935107

MILLER 23-1月 -82 12356.6314 1765.23306 411.887713 33.8537846

已选择 14 行。

SQL> select sysdate + hiredate from emp;

select sysdate + hiredate from emp

第 1 行出现错误:

ORA-00975: 不允许日期 + 日期

SQL> --months_between

SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二

2 from emp;

ENAME HIREDATE 一二

---------- -------------- ---------- ----------

SMITH 17-12月-80 425.287784 419.181726

ALLEN 20-2月 -81 423.121117 417.084952

WARD 22-2月 -81 423.054451 417

JONES 02-4月 -81 421.754451 415.665597

MARTIN 28-9月 -81 415.787784 409.826888

BLAKE 01-5月 -81 420.787784 414.697855

CLARK 09-6月 -81 419.487784 413.439791

SCOTT 19-4月 -87 348.154451 343.11721

KING 17-11月-81 414.121117 408.181726

TURNER 08-9月 -81 416.454451 410.472049

ADAMS 23-5月 -87 347.021117 341.988178

ENAME HIREDATE 一二

---------- -------------- ---------- ----------

JAMES 03-12月-81 413.587784 407.633339

FORD 03-12月-81 413.587784 407.633339

MILLER 23-1月 -82 411.887784 405.988178

已选择 14 行。

SQL> --56 个月后

SQL> select add_months(sysdate,56) from dual;

ADD_MONTHS(SYS

--------------

22-7月 -20

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA

--------------

30-11月-15

SQL> --next_day

SQL> --下一个星期日

SQL> select next_day(sysdate,'星期日') from dual;

NEXT_DAY(SYSDA

--------------

29-11月-15

SQL> --下一个星期一

SQL> select next_day(sysdate,'星期一') from dual;

NEXT_DAY(SYSDA

--------------

23-11月-15

SQL> /*

SQL> next_day的应用：每个星期一自动备份数据

SQL> 1. 分布式数据库

SQL> 2. 快照触发器

SQL> */

SQL> select round(sysdate,'month'),round(sysdate,'year') from dual;

ROUND(SYSDATE, ROUND(SYSDATE,

-------------- --------------

01-12月-15 01-1月 -16

SQL> --2015-11-22 15:32:12今天是星期日

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss今天是day') from dual;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss今天是day') from dual

第 1 行出现错误:

ORA-01821: 日期格式无法识别

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI

----------------------------------

2015-11-22 15:33:55今天是星期日

SQL> --查询员工薪水：两位小数千位符货币代码

SQL> select to_char(sal,'L9,999.99') from emp;

TO_CHAR(SAL,'L9,999

-------------------

￥800.00

￥1,600.00

￥1,250.00

￥2,975.00

￥1,250.00

￥2,850.00

￥2,450.00

￥3,000.00

￥5,000.00

￥1,500.00

￥1,100.00

TO_CHAR(SAL,'L9,999

-------------------

￥950.00

￥3,000.00

￥1,300.00

已选择 14 行。

SQL> host cls

SQL> --nvl2(a,b,c) 当a=null的时候，返回c；否则返回b

SQL> select sal*12+nvl2(comm,comm,0) from emp;

SAL*12+NVL2(COMM,COMM,0)

------------------------

9600

19500

15500

35700

16400

34200

29400

36000

60000

18000

13200

SAL*12+NVL2(COMM,COMM,0)

------------------------

11400

36000

15600

已选择 14 行。

SQL> --nullif(a,b) 当a=b的时候，返回null；否则返回a

SQL> select nullif('abc','abc') 值 from dual;

值

---

SQL> select nullif('abc','abcd') 值 from dual;

值

---

abc

SQL> --coalesce 从左到右找到第一个不为null的值

SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值"

2 from emp;

COMM SAL 第一个不为null的值

---------- ---------- ------------------

800 800

300 1600 300

500 1250 500

2975 2975

1400 1250 1400

2850 2850

2450 2450

3000 3000

5000 5000

0 1500 0

1100 1100

COMM SAL 第一个不为null的值

---------- ---------- ------------------

950 950

3000 3000

1300 1300

已选择 14 行。

SQL> host cls

SQL> --涨工资，总裁1000 经理 800 其他400

SQL> set linesize 200

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> select ename,job,sal 涨前,

2 case job when 'PRESIDENT' then sal+1000

3 when 'MANAGER' then sal+800

4 else sal+400

5 end 涨后

6 from emp;

ENAME JOB 涨前涨后

---------- --------- ---------- ----------

SMITH CLERK 800 1200

ALLEN SALESMAN 1600 2000

WARD SALESMAN 1250 1650

JONES MANAGER 2975 3775

MARTIN SALESMAN 1250 1650

BLAKE MANAGER 2850 3650

CLARK MANAGER 2450 3250

SCOTT ANALYST 3000 3400

KING PRESIDENT 5000 6000

TURNER SALESMAN 1500 1900

ADAMS CLERK 1100 1500

ENAME JOB 涨前涨后

---------- --------- ---------- ----------

JAMES CLERK 950 1350

FORD ANALYST 3000 3400

MILLER CLERK 1300 1700

已选择 14 行。

SQL> select ename,job,sal 涨前,

2 decode(job,'PRESIDENT',sal+1000,

3 'MANAGER',sal+800,

4 sal+400) 涨后

5 from emp;

ENAME JOB 涨前涨后

---------- --------- ---------- ----------

SMITH CLERK 800 1200

ALLEN SALESMAN 1600 2000

WARD SALESMAN 1250 1650

JONES MANAGER 2975 3775

MARTIN SALESMAN 1250 1650

BLAKE MANAGER 2850 3650

CLARK MANAGER 2450 3250

SCOTT ANALYST 3000 3400

KING PRESIDENT 5000 6000

TURNER SALESMAN 1500 1900

ADAMS CLERK 1100 1500

ENAME JOB 涨前涨后

---------- --------- ---------- ----------

JAMES CLERK 950 1350

FORD ANALYST 3000 3400

MILLER CLERK 1300 1700

已选择 14 行。

SQL> spool off

SQL> host cls

SQL> --工资总额

SQL> select sum(sal) from emp;

SUM(SAL)

----------

29025

SQL> --人数

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> --平均工资

SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;

一二

---------- ----------

2073.21429 2073.21429

SQL> --平均奖金

SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三

2 from emp;

一二三

---------- ---------- ----------

157.142857 550 550

SQL> select count(*),count(comm) from emp;

COUNT(*) COUNT(COMM)

---------- -----------

14 4

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> --null值 5.组函数（多行函数）自动滤空

SQL> select count(*),count(nvl(comm,0)) from emp;

COUNT(*) COUNT(NVL(COMM,0))

---------- ------------------

14 14

SQL> --null值 5.组函数（多行函数）自动滤空;可以嵌套滤空函数来屏蔽他的滤空功能

SQL> host cls

SQL> --求每个部门的平均工资

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno;

DEPTNO AVG(SAL)

---------- ----------

30 1566.66667

20 2175

10 2916.66667

SQL> --多个列的分组

SQL> select deptno,job,sum(sal)

2 from emp

3 group by deptno,job

4 order by 1;

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

已选择 9 行。

SQL> --求平均工资大于2000的部门

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno

4 having avg(sal) > 2000;

DEPTNO AVG(SAL)

---------- ----------

20 2175

10 2916.66667

SQL> --where和having的区别：where后面不能使用多行函数

SQL> --查询10号部门的平均工资

SQL> select deptno,avg(sal)

2 from emp

3 group by deptno

4 having deptno=10;

DEPTNO AVG(SAL)

---------- ----------

10 2916.66667

SQL> ed

已写入 file afiedt.buf

1 select deptno,avg(sal)

2 from emp

3 where deptno=10

4* group by deptno

SQL> /

DEPTNO AVG(SAL)

---------- ----------

10 2916.66667

SQL> --SQL 优化原则3. 尽量使用where

SQL> host cls

SQL> /*

SQL> group by的增强

SQL> select deptno,job,sum(sal) from emp group by deptno,job

SQL> +

SQL> select deptno,sum(sal) from emp group by deptno

SQL> +

SQL> select sum(sal) from emp

SQL>

SQL> ===

SQL>

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

SQL>

SQL> 抽象

SQL> group by rollup(a,b)

SQL> ==

SQL> group by a,b

SQL> +

SQL> group by a

SQL> +

SQL> group by null

SQL>

SQL> */

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025

已选择 13 行。

SQL> break on deptno skip 2

SQL> /

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

MANAGER 2450

PRESIDENT 5000

8750

20 CLERK 1900

ANALYST 6000

MANAGER 2975

10875

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 CLERK 950

MANAGER 2850

SALESMAN 5600

9400

29025

已选择 13 行。

SQL> break on null

SQL> /

DEPTNO JOB SUM(SAL)

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 CLERK 1900

20 ANALYST 6000

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

DEPTNO JOB SUM(SAL)

---------- --------- ----------

30 9400

29025

已选择 13 行。

SQL> spool off

SQL> host cls

SQL> --等值连接

SQL> --查询员工信息：员工号姓名月薪部门名称

SQL> set linesize 80

SQL> desc dept

名称是否为空? 类型

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> select e.empno,e.ename,e.sal,d.dname

2 from emp e,dept d

3 where e.deptno=d.deptno;

EMPNO ENAME SAL DNAME

---------- ---------- ---------- --------------

7369 SMITH 800 RESEARCH

7499 ALLEN 1600 SALES

7521 WARD 1250 SALES

7566 JONES 2975 RESEARCH

7654 MARTIN 1250 SALES

7698 BLAKE 2850 SALES

7782 CLARK 2450 ACCOUNTING

7788 SCOTT 3000 RESEARCH

7839 KING 5000 ACCOUNTING

7844 TURNER 1500 SALES

7876 ADAMS 1100 RESEARCH

EMPNO ENAME SAL DNAME

---------- ---------- ---------- --------------

7900 JAMES 950 SALES

7902 FORD 3000 RESEARCH

7934 MILLER 1300 ACCOUNTING

已选择 14 行。

SQL> --不等值连接

SQL> --查询员工信息：员工号姓名月薪工资级别

SQL> select * from salgrade;

GRADE LOSAL HISAL

---------- ---------- ----------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

SQL> select e.empno,e.ename,e.sal,s.grade

2 from emp e,salgrade s

3 where e.sal between s.losal and s.hisal;

EMPNO ENAME SAL GRADE

---------- ---------- ---------- ----------

7369 SMITH 800 1

7900 JAMES 950 1

7876 ADAMS 1100 1

7521 WARD 1250 2

7654 MARTIN 1250 2

7934 MILLER 1300 2

7844 TURNER 1500 3

7499 ALLEN 1600 3

7782 CLARK 2450 4

7698 BLAKE 2850 4

7566 JONES 2975 4

EMPNO ENAME SAL GRADE

---------- ---------- ---------- ----------

7788 SCOTT 3000 4

7902 FORD 3000 4

7839 KING 5000 5

已选择 14 行。

SQL> host cls

SQL> --外连接

SQL> --按部门统计员工人数：部门号部门名称人数

SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数

2 from emp e,dept d

3 where e.deptno=d.deptno

4 group by d.deptno,d.dname;

部门号部门名称人数

---------- -------------- ----------

10 ACCOUNTING 3

20 RESEARCH 5

30 SALES 6

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select * from emp where deptno=40;

未选定行

SQL> /*

SQL> 希望：对于某些不成立的记录，任然希望包含在最后的结果中

SQL> 左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含

SQL> 写法:where e.deptno=d.deptno(+)

SQL> 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含

SQL> 写法: where e.deptno(+)=d.deptno

SQL> */

SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数

2 from emp e,dept d

3 where e.deptno(+)=d.deptno

4 group by d.deptno,d.dname;

部门号部门名称人数

---------- -------------- ----------

10 ACCOUNTING 3

40 OPERATIONS 0

20 RESEARCH 5

30 SALES 6

SQL> host cls

SQL> --自连接

SQL> --查询员工信息：员工姓名老板姓名

SQL> set linesize 200

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12月-80 800 20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

7566 JONES MANAGER 7839 02-4月 -81 2975 20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

7698 BLAKE MANAGER 7839 01-5月 -81 2850 30

7782 CLARK MANAGER 7839 09-6月 -81 2450 10

7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

7839 KING PRESIDENT 17-11月-81 5000 10

7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30

7876 ADAMS CLERK 7788 23-5月 -87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7900 JAMES CLERK 7698 03-12月-81 950 30

7902 FORD ANALYST 7566 03-12月-81 3000 20

7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择 14 行。

SQL> --自连接：通过表的别名，将同一张表视为多张表

SQL> select e.ename 员工姓名,b.ename 老板姓名

2 from emp e,emp b

3 where e.mgr=b.empno;

员工姓名老板姓名

---------- ----------

FORD JONES

SCOTT JONES

JAMES BLAKE

TURNER BLAKE

MARTIN BLAKE

WARD BLAKE

ALLEN BLAKE

MILLER CLARK

ADAMS SCOTT

CLARK KING

BLAKE KING

员工姓名老板姓名

---------- ----------

JONES KING

SMITH FORD

已选择 13 行。

SQL> select count(*)

2 from emp e, emp b;

COUNT(*)

----------

196

SQL> --自连接不适合操作大表

SQL> --层次查询

SQL> select level,empno,ename,mgr

2 from emp

3 connect by prior empno=mgr

4 start with mgr is null

5 order by 1;

LEVEL EMPNO ENAME MGR

---------- ---------- ---------- ----------

1 7839 KING

2 7566 JONES 7839

2 7698 BLAKE 7839

2 7782 CLARK 7839

3 7902 FORD 7566

3 7521 WARD 7698

3 7900 JAMES 7698

3 7934 MILLER 7782

3 7499 ALLEN 7698

3 7788 SCOTT 7566

3 7654 MARTIN 7698

LEVEL EMPNO ENAME MGR

---------- ---------- ---------- ----------

3 7844 TURNER 7698

4 7876 ADAMS 7788

4 7369 SMITH 7902

已选择 14 行。

SQL> spool off

阅读全文

0 0