首页 > 代码库 > 数据库编程1 Oracle 过滤 函数 分组 外连接 自连接

数据库编程1 Oracle 过滤 函数 分组 外连接 自连接

本文谢绝转载原文来自http://990487026.blog.51cto.com

<大纲>

数据库编程1 Oracle 过滤 函数 分组 外连接 自连接
	本文实验基于的数据表:
	winsows安装好Oracle11g之后,开始实验
	SQLplus 登陆 ORacle
	sqlplus 退出的方式
	查看用户之下有什么表
	查看表的所有记录,不区分大小写
	设置SQLplus行宽,页宽,列宽:
	清屏命令
	select as 语法
		1,as别名的使用
		2,没有引号带有空格的别名,无法识别:
		3,带有空格的别名解决办法:
		年收入抛出问题,年收入显示为空
		解决办法,nvl(a,b)函数
	1,NULL空值 任何数与NULL运算都是NULL
	2,空值不是空 null != null	
	ed修改上一条命令:保存退出,/开始执行	
	where 过滤,查询奖金为空的员工:	
	||连接符,与虚表	
	dual虚表	
	DISTINCT 删除重复行	
	DISTINCT修饰多个字段时,当两个数据完全一样才认为是重复的	
	SQLplus工具的 help	
	edit的帮助:	
	Oracle系统常用的参数	
	默认的时间显示	
	临时修改系统时间的显示方式:	
	where过滤字句 > 大于	
	where过滤字句between and	
	where过滤字句between A  and B,A 必须小于等于 B	
	where过滤字句 in	
	where过滤字句 not in	
	where过滤字句 not in 与null在一起,受影响	
	where like模糊查询,查询员工姓名S开头的信息:	
	where like模糊查询,查询员工姓名是4个字母	
	where like模糊查询,转义字符	
	where order by 工资排序,默认升序	
	where order by 别名	
		order by 遇到null
		1,任何表达式与null运算都为空
		2,null!=null
	nulls last 把null放在最后	
	order修饰多个列,desc的作用范围只是最近的哪一个!	
	count函数
	
	字符函数
		大小写转换函数	LOWER	UPPER	INITCAP
	字符串连接:	
		concat
		substr(a,b) 从a中第b位开始取字符串	
		length 字符数
		lengthb 字节数
		instr求子串的位置
		lpad,rpad左填充右填充:
		trim去除前后指定字符:
		replace 字符串替换
		
	数字函数
		round 四舍五入
		tuec截断函数
		mod求余函数
		to_char格式化输出
		两个日期相减,返回日期之间相差的天数	
		精确显示员工入职月数,时间运算months_between	
		add_months()向指定的日期中加若干的月数	
		3,1隐式转换,字符串转日期	
		3,1显示转换,字符串转日期	
		字符串与字符串比较	
		to_char()对数字转字符	
		to_number()字符转数字	
	通用函数	
		nvl2(a,b,c)当a为null时返回c,否则返回b
		nullif(a,b)当a,b相等时返回NULL,否则返回a
		
		
	案例:总裁涨1000,经理涨800,其他涨500	
		方法1,SQL规范方法:
		方法2:Oracle专有方法
		
		
	组函数:	avg sum max min count 
		组函数与null在一起
		组函数会自动过滤空值:
			修正:
		
	分组数据:	
		求出各个部门的平均工资
		
	分组最难的地方:	
	having 分组过滤:	
	求10号部门的平均工资的两种方法	
		1,先分组再过滤
		2,先过滤再分组
	SQL优化问题:	
	多表查询:	
	[等值连接],显示员工信息,员工号,姓名,月薪	
	[不等值连接]显示员工信息,员工号,姓名,月薪,薪水级别	
	多表查询4,求每个部门的人数:(需要分组)	
	[少了一个部门,这个方法有漏洞,因为40号部门没有员工] 	
	[外连接]技术引出:	
	右外连接,+在等号的左边
	左外连接,+在等号的右边
	
	[难点]:[自连接]
	
	显示老板的信息:
	[优化显示]
	[再优化]
	[难点]:查询每个部门的人数	
	
	
	
	


[说明]本文实验基于以下数据表

dept表;
emp表;
salgrade表;
______________________________________________________________________________
SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
______________________________________________________________________________

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
      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
         1 tom_abc                                                                 8000                    10

已选择15行。
______________________________________________________________________________

SQL> select * from salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999
______________________________________________________________________________


winsows安装好Oracle11g之后,

实验基于[OracleServiceORCL]后台服务进程的开启:


以超级用户方式登录:

C:\Users\VMware>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 24 22:03:26 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect /as sysdba
已连接。 
SQL>

1,SQLplus 登陆 ORacle:

winsows安装好Oracle11g之后,修改scott的密码

C:\Users\VMware>sqlplus

请输入用户名:  scott
输入口令:tiger
ERROR:
ORA-28001: the password has expired
更改 scott 的口令
新口令:
重新键入新口令:
口令已更改

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

输入账户和密码,直接登录

C:\Users\VMware>sqlplus scott/11

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 24 22:00:50 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

sqlplus 退出的方式:

C:\Users\VMware>sqlplus scott/11
SQL> quit
SQL> exit


查看用户之下有什么表:

tab是关键字
C:\Users\VMware>sqlplus scott/11
SQL> select * from tab;

TNAME                                                        TABTYPE
------------------------------------------------------------ --------------
 CLUSTERID
----------
BONUS                                                        TABLE


DEPT                                                         TABLE


EMP                                                          TABLE



TNAME                                                        TABTYPE
------------------------------------------------------------ --------------
 CLUSTERID
----------
SALGRADE                                                     TABLE



SQL>



查看表的所有记录,不区分大小写

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

SQL>


查看表结构:

SQL> desc dept;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL>


设置SQLplus行宽,页宽,列宽:

设置行宽前:
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
设置行宽后:
SQL> set linesize 999;
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> set pagesize 999;
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
      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行。


设置数字的列宽:
oracle 9代表占位:
SQL> set linesize 199;
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
      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> col empno for 9999999999999999999
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
                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行。

设置字符的列宽:60个字符的宽度
SQL> col ename for a60;
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
                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> col ename for a20;
SQL> col empno for 999999
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
   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> host cls


select 语法:

select 参与运算语法:

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
   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
SQL> select ename, empno, job, sal, sal*12 from emp;

ENAME                  EMPNO JOB                       SAL     SAL*12
-------------------- ------- ------------------ ---------- ----------
SMITH                   7369 CLERK                     800       9600
ALLEN                   7499 SALESMAN                 1600      19200
WARD                    7521 SALESMAN                 1250      15000
JONES                   7566 MANAGER                  2975      35700
MARTIN                  7654 SALESMAN                 1250      15000
BLAKE                   7698 MANAGER                  2850      34200
CLARK                   7782 MANAGER                  2450      29400
SCOTT                   7788 ANALYST                  3000      36000
KING                    7839 PRESIDENT                5000      60000
TURNER                  7844 SALESMAN                 1500      18000
ADAMS                   7876 CLERK                    1100      13200
JAMES                   7900 CLERK                     950      11400
FORD                    7902 ANALYST                  3000      36000
MILLER                  7934 CLERK                    1300      15600


select as 语法

as 可以省略
冒号可以省略
别名之间不能有空格

1,as别名的使用
SQL> select ename as "员工编号", empno "编号", job 工作 , sal 月薪 , sal*12 年薪  from emp;

员工编号                   编号 工作                     月薪       年薪
-------------------- ---------- ------------------ ---------- ----------
SMITH                      7369 CLERK                     800       9600
ALLEN                      7499 SALESMAN                 1600      19200
WARD                       7521 SALESMAN                 1250      15000
JONES                      7566 MANAGER                  2975      35700
MARTIN                     7654 SALESMAN                 1250      15000
BLAKE                      7698 MANAGER                  2850      34200
CLARK                      7782 MANAGER                  2450      29400
SCOTT                      7788 ANALYST                  3000      36000
KING                       7839 PRESIDENT                5000      60000
TURNER                     7844 SALESMAN                 1500      18000
ADAMS                      7876 CLERK                    1100      13200
JAMES                      7900 CLERK                     950      11400
FORD                       7902 ANALYST                  3000      36000
MILLER                     7934 CLERK                    1300      15600

已选择14行。


2,没有引号带有空格的别名,无法识别:

SQL> select ename as "员工编号", empno "编号", job 工作 , sal 月 薪 , sal*12 年薪  from emp;
select ename as "员工编号", empno "编号", job 工作 , sal 月 薪 , sal*12 年薪  from emp
                                                                       *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字


3,带有空格的别名解决办法:

SQL> select ename as "员工编号", empno "编号", job 工作 , sal "月  薪" , sal*12 年薪  from emp;

员工编号                   编号 工作                   月  薪       年薪
-------------------- ---------- ------------------ ---------- ----------
SMITH                      7369 CLERK                     800       9600
ALLEN                      7499 SALESMAN                 1600      19200
WARD                       7521 SALESMAN                 1250      15000
JONES                      7566 MANAGER                  2975      35700
MARTIN                     7654 SALESMAN                 1250      15000
BLAKE                      7698 MANAGER                  2850      34200
CLARK                      7782 MANAGER                  2450      29400
SCOTT                      7788 ANALYST                  3000      36000
KING                       7839 PRESIDENT                5000      60000
TURNER                     7844 SALESMAN                 1500      18000
ADAMS                      7876 CLERK                    1100      13200
JAMES                      7900 CLERK                     950      11400
FORD                       7902 ANALYST                  3000      36000
MILLER                     7934 CLERK                    1300      15600

已选择14行。


年收入抛出问题,年收入显示为空

SQL> select ename as "员工编号", empno "编号", job 工作 , sal "月  薪" , sal*12+comm 年收入  from emp;

员工编号                   编号 工作                   月  薪     年收入
-------------------- ---------- ------------------ ---------- ----------
SMITH                      7369 CLERK                     800
ALLEN                      7499 SALESMAN                 1600      19500
WARD                       7521 SALESMAN                 1250      15500
JONES                      7566 MANAGER                  2975
MARTIN                     7654 SALESMAN                 1250      16400
BLAKE                      7698 MANAGER                  2850
CLARK                      7782 MANAGER                  2450
SCOTT                      7788 ANALYST                  3000
KING                       7839 PRESIDENT                5000
TURNER                     7844 SALESMAN                 1500      18000
ADAMS                      7876 CLERK                    1100
JAMES                      7900 CLERK                     950
FORD                       7902 ANALYST                  3000
MILLER                     7934 CLERK                    1300





1,NULL空值 任何数与NULL运算都是NULL

2,空值不是空 null != null



解决办法,nvl(a,b)函数,当a是null是返回b,b可以是数字/字符串

SQL> select ename,empno, job, sal, nvl(sal*12+comm,0) from emp;

ENAME                     EMPNO JOB                       SAL NVL(SAL*12+COMM,0)
-------------------- ---------- ------------------ ---------- ------------------
SMITH                      7369 CLERK                     800                  0
ALLEN                      7499 SALESMAN                 1600              19500
WARD                       7521 SALESMAN                 1250              15500
JONES                      7566 MANAGER                  2975                  0
MARTIN                     7654 SALESMAN                 1250              16400
BLAKE                      7698 MANAGER                  2850                  0
CLARK                      7782 MANAGER                  2450                  0
SCOTT                      7788 ANALYST                  3000                  0
KING                       7839 PRESIDENT                5000                  0
TURNER                     7844 SALESMAN                 1500              18000
ADAMS                      7876 CLERK                    1100                  0
JAMES                      7900 CLERK                     950                  0
FORD                       7902 ANALYST                  3000                  0
MILLER                     7934 CLERK                    1300                  0

已选择14行。


SQL> select ename,empno, job, sal, nvl(sal*12+comm,0) "年收入" from emp;

ENAME                     EMPNO JOB                       SAL     年收入
-------------------- ---------- ------------------ ---------- ----------
SMITH                      7369 CLERK                     800          0
ALLEN                      7499 SALESMAN                 1600      19500
WARD                       7521 SALESMAN                 1250      15500
JONES                      7566 MANAGER                  2975          0
MARTIN                     7654 SALESMAN                 1250      16400
BLAKE                      7698 MANAGER                  2850          0
CLARK                      7782 MANAGER                  2450          0
SCOTT                      7788 ANALYST                  3000          0
KING                       7839 PRESIDENT                5000          0
TURNER                     7844 SALESMAN                 1500      18000
ADAMS                      7876 CLERK                    1100          0
JAMES                      7900 CLERK                     950          0
FORD                       7902 ANALYST                  3000          0
MILLER                     7934 CLERK                    1300          0

已选择14行。

SQL> select ename,empno, job, sal, nvl(sal*12+comm,0)as  "年收入" from emp;

ENAME                     EMPNO JOB                       SAL     年收入
-------------------- ---------- ------------------ ---------- ----------
SMITH                      7369 CLERK                     800          0
ALLEN                      7499 SALESMAN                 1600      19500
WARD                       7521 SALESMAN                 1250      15500
JONES                      7566 MANAGER                  2975          0
MARTIN                     7654 SALESMAN                 1250      16400
BLAKE                      7698 MANAGER                  2850          0
CLARK                      7782 MANAGER                  2450          0
SCOTT                      7788 ANALYST                  3000          0
KING                       7839 PRESIDENT                5000          0
TURNER                     7844 SALESMAN                 1500      18000
ADAMS                      7876 CLERK                    1100          0
JAMES                      7900 CLERK                     950          0
FORD                       7902 ANALYST                  3000          0
MILLER                     7934 CLERK                    1300          0

已选择14行。


ed修改上一条命令:保存退出,/开始执行

SQL> ed
已写入 file afiedt.buf

  1* select ename,empno, job, sal, nvl(sal*12+comm,0)as  "年 入" from emp
SQL> /

ENAME                     EMPNO JOB                       SAL      年 入
-------------------- ---------- ------------------ ---------- ----------
SMITH                      7369 CLERK                     800          0
ALLEN                      7499 SALESMAN                 1600      19500
WARD                       7521 SALESMAN                 1250      15500
JONES                      7566 MANAGER                  2975          0
MARTIN                     7654 SALESMAN                 1250      16400
BLAKE                      7698 MANAGER                  2850          0
CLARK                      7782 MANAGER                  2450          0
SCOTT                      7788 ANALYST                  3000          0
KING                       7839 PRESIDENT                5000          0
TURNER                     7844 SALESMAN                 1500      18000
ADAMS                      7876 CLERK                    1100          0
JAMES                      7900 CLERK                     950          0
FORD                       7902 ANALYST                  3000          0
MILLER                     7934 CLERK                    1300          0

已选择14行。


where 过滤,查询奖金为空的员工:

SQL> select * from emp 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> select ‘Hello ‘ || ‘World‘ from emp;

‘HELLO‘||‘WORLD‘
----------------------
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World

已选择14行。


dual虚表:

SQL> select ‘Hello ‘ || ‘World‘ from dual;

‘HELLO‘||‘WORLD‘
----------------------
Hello World


利用伪表,显示系统时间

SQL> select sysdate from dual;

SYSDATE
--------------
25-8月 -16


DISTINCT 删除重复行,显示部门个数:

SQL> select DISTINCT deptno from emp;

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



DISTINCT修饰多个字段时,当两个数据完全一样才认为是重复的

SQL> select deptno,job from emp;

    DEPTNO JOB
---------- ------------------
        20 CLERK
        30 SALESMAN
        30 SALESMAN
        20 MANAGER
        30 SALESMAN
        30 MANAGER
        10 MANAGER
        20 ANALYST
        10 PRESIDENT
        30 SALESMAN
        20 CLERK
        30 CLERK
        20 ANALYST
        10 CLERK

已选择14行。

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行。


SQLplus工具的 help

SQL> help topic

 Help is available on the following topics:

/
@
@@
ACCEPT
APPEND
ARCHIVE LOG
ATTRIBUTE
BREAK
BTITLE
CHANGE
CLEAR
COLUMN
COMPUTE
CONNECT
COPY
DEFINE
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
GET
HELP
HOST
INDEX
INPUT
LIST
MENU
PASSWORD
PAUSE
PRINT
PROMPT
QUIT
RECOVER
REMARK
REPFOOTER
REPHEADER
RESERVED WORDS (PL/SQL)
RESERVED WORDS (SQL)
RUN
SAVE
SET
SHOW
SHUTDOWN
SPOOL
SQLPLUS
START
STARTUP
STORE
TIMING
TOPICS
TTITLE
UNDEFINE
VARIABLE
WHENEVER OSERROR
WHENEVER SQLERROR
XQUERY

SQL>



edit的帮助:

SQL> ? edit

 EDIT
 ----

 Invokes an operating system text editor on the contents of the
 specified file or on the contents of the SQL buffer. The buffer
 has no command history list and does not record SQL*Plus commands.

 ED[IT] [file_name[.ext]]


SQL>


Oracle系统常用的参数

SQL> set pagesize 299
SQL> set linesize 299
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                  AL32UTF8
NLS_SORT                          BINARY
NLS_TIME_FORMAT                   HH.MI.SSXFF AM
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> select sysdate from dual;

SYSDATE
--------------
25-8月 -16



临时修改系统时间的显示方式:

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

会话已更改。

SQL> select sysdate from dual;

SYSDATE
----------
2016-08-25

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17        800                    20
      7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         30
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
      7654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 1981-05-01       2850                    30
      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17       5000                    10
      7844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         30
      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
      7900 JAMES                CLERK                    7698 1981-12-03        950                    30
      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20
      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已选择14行。




where过滤字句 > 大于:

查询入职比80年1月1号晚的员工:日期格式是敏感的

SQL> select * from emp where hiredate > ‘1980-01-01‘;

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17        800                    20
      7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         30
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
      7654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 1981-05-01       2850                    30
      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17       5000                    10
      7844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         30
      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
      7900 JAMES                CLERK                    7698 1981-12-03        950                    30
      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20
      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已选择14行。


where过滤字句between and,查询工资在1000 ~ 2000之间的

SQL> select * from emp where sal between 1000 and 2000;

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         30
      7654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         30
      7844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         30
      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已选择6行。


where过滤字句between A  and B,A 必须小于等于 B

SQL> select * from emp where sal between 9000 and 200;

未选定行

SQL>


where过滤字句 and

SQL> select * from emp where sal <=9000 and sal > 2000;

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
      7698 BLAKE                MANAGER                  7839 1981-05-01       2850                    30
      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17       5000                    10
      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20

已选择6行。



where过滤字句 in

SQL> select * from emp where deptno in (10,20);

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17        800                    20
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17       5000                    10
      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20
      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已选择8行。


where过滤字句 not in

SQL> select * from emp where deptno not in (10,20);

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         30
      7654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 1981-05-01       2850                    30
      7844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         30
      7900 JAMES                CLERK                    7698 1981-12-03        950                    30

已选择6行。


where过滤字句 not in 与null在一起,受影响

SQL> select * from emp where deptno in (10,20);

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17        800                    20
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17       5000                    10
      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20
      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已选择8行。

SQL> select * from emp where deptno in (10,20,null);

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17        800                    20
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17       5000                    10
      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20
      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已选择8行。

SQL> select * from emp where deptno not in (10,20,null);

未选定行


where like模糊查询,查询员工姓名S开头的信息:

SQL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17        800                    20
      7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         30
      7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
      7654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 1981-05-01       2850                    30
      7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
      7839 KING                 PRESIDENT                     1981-11-17       5000                    10
      7844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         30
      7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
      7900 JAMES                CLERK                    7698 1981-12-03        950                    30
      7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20
      7934 MILLER               CLERK                    7782 1982-01-23       1300                    10

已选择14行。

SQL> select * from emp where ename like ‘S%‘;

     EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 1980-12-17        800                    20
      7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20






本文出自 “魂斗罗” 博客,谢绝转载!

数据库编程1 Oracle 过滤 函数 分组 外连接 自连接