oracle第一天

---------1.过滤和排序-------------------------------------------------

SQL> --查询10号部门的员工信息
SQL> select *
2 from emp
3 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> 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> 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> select sysdate from dual;

SYSDATE
--------------
19-6月 -13

SQL> select *
2 from v$nls_parameters;

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE

NLS_TERRITORY
CHINA

NLS_CURRENCY
￥

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA

NLS_NUMERIC_CHARACTERS
.,

NLS_CALENDAR
GREGORIAN

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE

NLS_CHARACTERSET
ZHS16GBK

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

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

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

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

NLS_DUAL_CURRENCY
￥

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE

已选择19行。

SQL> col PARAMETER for a30
SQL> /

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> host cls

SQL> select *
2 from v$nls_parameters;

已选择19行。

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

会话已更改。

SQL> select * from emp where hiredate='1981-11-17';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- ---------- ----- ---------- ----------
7839 KING PRESIDENT 1981-11-17 5000 10

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 13-7月 -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> --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 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择8行。

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> --思考题:
SQL> --结论:空值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> /

已选择8行。

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 13-7月 -87 3000 20

SQL> --查询名字是4个字的员工
SQL> select *
2 from emp
3 where ename like '____';

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)
2 values(1001,'Tom_ABCD',5000,10);

已创建 1 行。

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 13-7月 -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 13-7月 -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
1001 Tom_ABCD 5000 10

已选择15行。

SQL> --查询名字中含有下划线的员工
SQL> select *
2 from emp
3 where ename like '%_%';

已选择15行。

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_ABCD 5000 10

SQL> rollback;

回退已完成。

SQL> select * from emp;

已选择14行。

SQL> host cls

SQL> --优化2: where 从右-->左
SQL> host cls

SQL> --排序
SQL> select *
2 from emp
3 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 13-7月 -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 13-7月 -87 3000 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10

已选择14行。

SQL> --a命令 append
SQL> a desc
3* order by sal desc
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 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
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- -------------- ----- ---------- ----------
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7369 SMITH CLERK 7902 17-12月-80 800 20

已选择14行。

SQL> --order by后面+列名表达式别名序号
SQL> --查询员工信息按照年薪排序
SQL> select ename,sal,sal*12
2 from emp
3 order by sal*12;

ENAME SAL SAL*12
-------- ----- ----------
SMITH 800 9600
JAMES 950 11400
ADAMS 1100 13200
WARD 1250 15000
MARTIN 1250 15000
MILLER 1300 15600
TURNER 1500 18000
ALLEN 1600 19200
CLARK 2450 29400
BLAKE 2850 34200
JONES 2975 35700

ENAME SAL SAL*12
-------- ----- ----------
SCOTT 3000 36000
FORD 3000 36000
KING 5000 60000

已选择14行。

SQL> ed
已写入 file afiedt.buf

1 select ename,sal,sal*12 年薪
2 from emp
3* order by 年薪
SQL> /

ENAME SAL 年薪
-------- ----- ----------
SMITH 800 9600
JAMES 950 11400
ADAMS 1100 13200
WARD 1250 15000
MARTIN 1250 15000
MILLER 1300 15600
TURNER 1500 18000
ALLEN 1600 19200
CLARK 2450 29400
BLAKE 2850 34200
JONES 2975 35700

ENAME SAL 年薪
-------- ----- ----------
SCOTT 3000 36000
FORD 3000 36000
KING 5000 60000

已选择14行。

SQL> ed
已写入 file afiedt.buf

1 select ename,sal,sal*12 年薪
2 from emp
3* order by 3
SQL> /

ENAME SAL 年薪
-------- ----- ----------
SCOTT 3000 36000
FORD 3000 36000
KING 5000 60000

已选择14行。

SQL> ed
已写入 file afiedt.buf

1 select ename,sal,sal*12 年薪
2 from emp
3* order by 4
SQL> /
order by 4
*
第 3 行出现错误:
ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目

SQL> host cls

SQL> --order by 后面+多列
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 13-7月 -87 1100 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 13-7月 -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

已选择14行。

SQL> --含义：先按照第一列排序，如果相同，再按照第二列排序，以此类推
SQL> --order by作用于后面所有的列
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 13-7月 -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 13-7月 -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

已选择14行。

SQL> --desc只作用于离他最近的一列
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 13-7月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7369 SMITH CLERK 7902 17-12月-80 800 20

已选择14行。

SQL> host cls

SQL> --查询员工信息，按照奖金排序
SQL> --空值4
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 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -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> select * from emp order by comm;

已选择14行。

SQL> select * from emp order by comm desc;

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 13-7月 -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 13-7月 -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 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -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> spool off

------------------2基本查询------------------------------------------------------

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> --tab: 数据字典(表)
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
20

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

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
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

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10

7788 SCOTT ANALYST 7566 13-7月 -87 3000
20

7839 KING PRESIDENT 17-11月-81 5000
10

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30

7876 ADAMS CLERK 7788 13-7月 -87 1100
20

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20

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

已选择14行。

SQL> --设置行宽
SQL> set linesize 120
SQL> --设置列宽
SQL> col ename for a8
SQL> col sal for 9999
SQL> /

已选择14行。

SQL> --通过列名
SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno
2 from emp;

已选择14行。

SQL> /*
SQL> SQL优化:
SQL> 1. 尽量使用列名(Orale9i之后，*和列名是一样的 )
SQL> */
SQL> host cls

SQL> --查询员工信息:员工号姓名月薪
SQL> select empno,ename,sal
2 from emp;

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> /*
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> --查询奖金为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 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -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> select *
2 form emp;
form emp
*
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

SQL> --c 命令
SQL> 2
2* form emp
SQL> c /form/from
2* from emp
SQL> /

已选择14行。

SQL> select empno,ename,sal,job
2 form emp;
form emp
*
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

SQL> ed
已写入 file afiedt.buf

1 select empno,ename,sal,job
2* from emp
SQL> /

EMPNO ENAME SAL JOB
---------- -------- ----- ---------
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK

EMPNO ENAME SAL JOB
---------- -------- ----- ---------
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK

已选择14行。

SQL> ed
已写入 file afiedt.buf

1 select empno as "员工号",ename "姓名",sal 月薪,job 职位
2* from emp
SQL> /

员工号姓名月薪职位
---------- ---------- ---------- ---------
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK

员工号姓名月薪职位
---------- ---------- ---------- ---------
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK

已选择14行。

SQL> ed
已写入 file afiedt.buf

1 select empno as "员工号",ename "姓名",sal 月薪,job 职位
2* from emp
SQL> /
select empno as "员工号",ename "姓名",sal 月薪,job 职位
*
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

SQL> ed
已写入 file afiedt.buf

1 select empno as "员工号",ename "姓名",sal "月薪",job 职位
2* from emp
SQL> /

员工号姓名月薪职位
---------- ---------- ---------- ---------
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK

已选择14行。

SQL> host cls

SQL> --DISTINCT 去掉重复记录
SQL> select deptno from emp;

DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20

DEPTNO
----------
30
20
10

已选择14行。

SQL> select DISTINCT deptno from emp;

DEPTNO
----------
30
20
10

SQL> select job from emp;

JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
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> select concat('Hello',' World') from emp;

CONCAT('HELL
------------
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World

CONCAT('HELL
------------
Hello World
Hello World
Hello World

已选择14行。

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

CONCAT('HELL
------------
Hello World

SQL> --dual:伪表
SQL> select 3+2 from dual;

3+2
----------
5

SQL> select 'Hello'||' World' 一列 from dual;

一列
------------
Hello World

SQL> --查询员工信息：***的薪水是****
SQL> select ename||'的薪水是'||sal 一列
2 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> select * from emp;

已选择14行。

SQL> save c:\a.sql
已创建 file c:\a.sql
SQL> @c:\a.sql

已选择14行。

SQL> spool off

----------------3单行函数------------------------------------------------

SQL> --字符函数
SQL> select lower('Hello WORLd') 转小写,upper('Hello WORLd') 转大写,
2 initcap('hello world') 首字母大写
3 from dual;

转小写转大写首字母大写
----------- ----------- -----------
hello world HELLO WORLD Hello World

SQL> --substr(a,b) 从a中，第b位开始取，取右边所有的字符
SQL> select substr('hello world',3) from dual;

SUBSTR('H
---------
llo world

SQL> --substr(a,b,c) 从a中，第b位开始取，取c位
SQL> select substr('hello world',3,4) from dual;

SUBS
----
llo

SQL> host cls

SQL> --length 字符数 lengthb 字节数
SQL> select length('hello world') 字符数,lengthb('hello world') 字节数
2 from dual;

字符数字节数
---------- ----------
11 11

SQL> ed
已写入 file afiedt.buf

1 select length('中国') 字符数,lengthb('中国') 字节数
2* from dual
SQL> /

字符数字节数
---------- ----------
2 4

SQL> host cls

SQL> --instr(a,b) 从a中查找b，找到返回下标，否则返回0
SQL> select instr('hello world','ll') from dual;

INSTR('HELLOWORLD','LL')
------------------------
3

SQL> --提示:第二天的课后作业
SQL> host cls

SQL> --lpad 左填充 rpad右填充
SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右
2 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',
2 '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> 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> host cls

SQL> --日期函数
SQL> select sysdate from dual;

SYSDATE
--------------
19-6月 -13

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

TO_CHAR(SYSDATE,'YY
-------------------
2013-06-19 14:22:23

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

TO_CHAR(SYSTIMESTAMP,'YYYY-MM
-----------------------------
2013-06-19 14:23:22:000000

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

TO_CHAR(SYSTIMESTAMP,'YYYY-MM
-----------------------------
2013-06-19 14:23:24:843000

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

昨天今天明天
-------------- -------------- --------------
18-6月 -13 19-6月 -13 20-6月 -13

SQL> host cls

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 11872.6018 1696.08597 395.753392 32.5276761
ALLEN 20-2月 -81 11807.6018 1686.80025 393.586725 32.3495939
WARD 22-2月 -81 11805.6018 1686.51454 393.520059 32.3441144
JONES 02-4月 -81 11766.6018 1680.94311 392.220059 32.2372651
MARTIN 28-9月 -81 11587.6018 1655.37168 386.253392 31.7468541
BLAKE 01-5月 -81 11737.6018 1676.80025 391.253392 32.157813
CLARK 09-6月 -81 11698.6018 1671.22882 389.953392 32.0509637
SCOTT 13-7月 -87 703435.602 100490.8 23447.8534 1927.22083
KING 17-11月-81 11537.6018 1648.22882 384.586725 31.6098678
TURNER 08-9月 -81 11607.6018 1658.22882 386.920059 31.8016487
ADAMS 13-7月 -87 703435.602 100490.8 23447.8534 1927.22083

ENAME HIREDATE 天星期月年
---------- -------------- ---------- ---------- ---------- ----------
JAMES 03-12月-81 11521.6018 1645.94311 384.053392 31.5660322
FORD 03-12月-81 11521.6018 1645.94311 384.053392 31.5660322
MILLER 23-1月 -82 11470.6018 1638.65739 382.353392 31.4263062

已选择14行。

SQL> select sysdate+hiredate from emp;
select sysdate+hiredate from emp
*
第 1 行出现错误:
ORA-00975: 不允许日期 + 日期

SQL> host cls

SQL> --last_day 日期所在月份的最后一天
SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
30-6月 -13

SQL> --MONTHS_BETWEEN 返回两个日期相差的月数
SQL> select ename,hiredate,(sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二
2 from emp;

ENAME HIREDATE 一二
---------- -------------- ---------- ----------
SMITH 17-12月-80 395.753475 390.084008
ALLEN 20-2月 -81 393.586809 387.987234
WARD 22-2月 -81 393.520142 387.922718
JONES 02-4月 -81 392.220142 386.567879
MARTIN 28-9月 -81 386.253475 380.72917
BLAKE 01-5月 -81 391.253475 385.600137
CLARK 09-6月 -81 389.953475 384.342073
SCOTT 13-7月 -87 23447.8535 23111.213
KING 17-11月-81 384.586809 379.084008
TURNER 08-9月 -81 386.920142 381.374331
ADAMS 13-7月 -87 23447.8535 23111.213

ENAME HIREDATE 一二
---------- -------------- ---------- ----------
JAMES 03-12月-81 384.053475 378.535621
FORD 03-12月-81 384.053475 378.535621
MILLER 23-1月 -82 382.353475 376.89046

已选择14行。

SQL> --ADD_MONTHS: 加上若干个月
SQL> select ADD_MONTHS(sysdate,100) from dual;

ADD_MONTHS(SYS
--------------
19-10月-21

SQL> host cls

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

NEXT_DAY(SYSDA
--------------
26-6月 -13

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

NEXT_DAY(SYSDA
--------------
20-6月 -13

SQL> --提示: next_day应用：每个星期一做数据备份
SQL> --第四天：分布式数据库
SQL> select next_day(sysdate,'礼拜四') from dual;
select next_day(sysdate,'礼拜四') from dual
*
第 1 行出现错误:
ORA-01846: 周中的日无效

SQL> host cls

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

ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-7月 -13 01-1月 -13

SQL> --隐式转换的前提：被转换对象是可以转换的
SQL> --显式转换
SQL> --显示当前时间: 2013-06-19 14:45:23今天是星期三
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是" day') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:
-----------------------------------
2013-06-19 14:46:59今天是星期三

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> --通用函数
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> host cls

SQL> --NULLIF(a,b) 当a=b时, 返回null，否则返回a
SQL> select nullif('abc','abc') from dual
2 ;

NUL
---

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

NUL
---
abc

SQL> --COALESCE :从左往右找到第一个不为null的值
SQL> select comm,sal,COALESCE(comm,sal) from emp;

COMM SAL COALESCE(COMM,SAL)
---------- ---------- ------------------
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 COALESCE(COMM,SAL)
---------- ---------- ------------------
950 950
3000 3000
1300 1300

已选择14行。

SQL> host cls

SQL> --条件表达式
SQL> --涨工资，总裁1000 经理800 其他400
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 涨前薪水涨后薪水
---------- --------- ---------- ----------
JAMES CLERK 950 1350
FORD ANALYST 3000 3400
MILLER CLERK 1300 1700

已选择14行。

SQL> spool off

--------------4.多表查询-------------------------------------------------

SQL> host cls

SQL> --等值连接
SQL> --查询员工信息：员工号姓名月薪部门名称
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;

DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6

SQL> ed
已写入 file afiedt.buf

1 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
SQL> /

部门号部门名称人数
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6

SQL> select count(*) from emp;

COUNT(*)
----------
14

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> 外连接:
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;

DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
40 OPERATIONS 0
20 RESEARCH 5
30 SALES 6

SQL> ed
已写入 file afiedt.buf

1 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
5* order by 1
SQL> /

DEPTNO DNAME COUNT(E.EMPNO)
---------- -------------- --------------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0

SQL> host cls

SQL> --自连接
SQL> --查询员工信息：***的老板是***
SQL>
SQL> select * from emp;

已选择14行。

SQL> set linesize 120
SQL> col sal for 999
SQL> col sal for 9999
SQL> host cls

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 13-7月 -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 13-7月 -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;

E.ENAME||'的老板是'||B.ENAME
----------------------------
FORD的老板是JONES
SCOTT的老板是JONES
JAMES的老板是BLAKE
TURNER的老板是BLAKE
MARTIN的老板是BLAKE
WARD的老板是BLAKE
ALLEN的老板是BLAKE
MILLER的老板是CLARK
ADAMS的老板是SCOTT
CLARK的老板是KING
BLAKE的老板是KING

E.ENAME||'的老板是'||B.ENAME
----------------------------
JONES的老板是KING
SMITH的老板是FORD

已选择13行。

SQL> select count(*)
2 from emp e,emp b;

COUNT(*)
----------
196

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> select level,empno,ename,sal,mgr
2 from emp
3 connect by prior empno=mgr
4 start with mgr is null
5 order by 1;

LEVEL EMPNO ENAME SAL MGR
---------- ---------- ---------- ----- ----------
1 7839 KING 5000
2 7566 JONES 2975 7839
2 7698 BLAKE 2850 7839
2 7782 CLARK 2450 7839
3 7902 FORD 3000 7566
3 7521 WARD 1250 7698
3 7900 JAMES 950 7698
3 7934 MILLER 1300 7782
3 7499 ALLEN 1600 7698
3 7788 SCOTT 3000 7566
3 7654 MARTIN 1250 7698

LEVEL EMPNO ENAME SAL MGR
---------- ---------- ---------- ----- ----------
3 7844 TURNER 1500 7698
4 7876 ADAMS 1100 7788
4 7369 SMITH 800 7902

已选择14行。

SQL> /*
SQL> 执行的过程:
SQL> 1. KING: start with mgr is null ---> empno=7839
SQL> 2. where mgr = 7839; ---> 7566 7698 7782
SQL> 3. where mgr in (7566 7698 7782)*/
SQL> spool off

---------5多行函数--------------------------------------

SQL> --工资总额
SQL> select sum(sal) from emp;

SUM(SAL)
----------
29025

SQL> --人数
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> --平均工资
SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;

一二
---------- ----------
2073.21429 2073.21429

SQL> --平均奖金: 空值 5
SQL> select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三
2 from emp;

一二三
---------- ---------- ----------
157.142857 550 550

SQL> --空值 5: 组函数自动滤空
SQL> select count(*), count(comm) from emp;

COUNT(*) COUNT(COMM)
---------- -----------
14 4

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

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

SQL> host cls

SQL> --分组数据
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> select detpno,job,sum(sal)
2 from emp
3 group by deptno,job;
select detpno,job,sum(sal)
*
第 1 行出现错误:
ORA-00904: "DETPNO": 标识符无效

SQL> ed
已写入 file afiedt.buf

1 select deptno,job,sum(sal)
2 from emp
3* group by deptno,job
SQL> /

DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000

已选择9行。

SQL> ed
已写入 file afiedt.buf

1 select deptno,job,sum(sal)
2 from emp
3 group by deptno,job
4* order by 1
SQL> /

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> 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> --求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
5 /

DEPTNO AVG(SAL)
---------- ----------
10 2916.66667

SQL> --优化4: 尽量使用where
SQL> host cls

SQL> /*
SQL> group by的增强
SQL> group by deptno,job
SQL> +
SQL> group by deptno
SQL> +
SQL> group by null
SQL> =
SQL> group by rollup(deptno,job)
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> select deptno,job,sum(sal)
2 from emp
3 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> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job);

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)
---------- --------- ----------
30 9400
29025

已选择13行。

SQL> spool off

posted on 2016-06-20 21:44 zhaoshuzhan 阅读(341) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

zhaoshuzhan

oracle第一天

导航

公告