oracle中select查询与空值

最新推荐文章于 2024-05-11 16:05:29 发布

cat媛

最新推荐文章于 2024-05-11 16:05:29 发布

阅读量6.7k

点赞数 1

分类专栏： oracle数据库

本文链接：https://blog.csdn.net/qq_42816766/article/details/92851887

版权

oracle数据库专栏收录该内容

27 篇文章 3 订阅

订阅专栏

1、select查询

1.1 设置行间距

SYS@OCPLHR1> set linesize 200;

1.2 查询所有列

SYS@OCPLHR1> select * from scott.emp;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10

14 rows selected.

1.3 查询某一列

SYS@OCPLHR1> select job from scott.emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK

14 rows selected.

1.4 给表加别名

SYS@OCPLHR1> select a.empno,a.ename,a.* from scott.emp a;

     EMPNO ENAME	   EMPNO ENAME	    JOB 	     MGR HIREDATE	  SAL	    COMM     DEPTNO
---------- ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH	    7369 SMITH	    CLERK	    7902 17-DEC-80	  800			 20
      7499 ALLEN	    7499 ALLEN	    SALESMAN	    7698 20-FEB-81	 1600	     300	 30
      7521 WARD 	    7521 WARD	    SALESMAN	    7698 22-FEB-81	 1250	     500	 30
      7566 JONES	    7566 JONES	    MANAGER	    7839 02-APR-81	 2975			 20
      7654 MARTIN	    7654 MARTIN     SALESMAN	    7698 28-SEP-81	 1250	    1400	 30
      7698 BLAKE	    7698 BLAKE	    MANAGER	    7839 01-MAY-81	 2850			 30
      7782 CLARK	    7782 CLARK	    MANAGER	    7839 09-JUN-81	 2450			 10
      7788 SCOTT	    7788 SCOTT	    ANALYST	    7566 19-APR-87	 3000			 20
      7839 KING 	    7839 KING	    PRESIDENT		 17-NOV-81	 5000			 10
      7844 TURNER	    7844 TURNER     SALESMAN	    7698 08-SEP-81	 1500	       0	 30
      7876 ADAMS	    7876 ADAMS	    CLERK	    7788 23-MAY-87	 1100			 20
      7900 JAMES	    7900 JAMES	    CLERK	    7698 03-DEC-81	  950			 30
      7902 FORD 	    7902 FORD	    ANALYST	    7566 03-DEC-81	 3000			 20
      7934 MILLER	    7934 MILLER     CLERK	    7782 23-JAN-82	 1300			 10
14 rows selected.

1.5 算术表达式（加减乘除）

给empno表中的数据加1000

SYS@OCPLHR1> select empno+1000 from scott.emp;

EMPNO+1000
----------
      8369
      8499
      8521
      8566
      8654
      8698
      8782
      8788
      8839
      8844
      8876
      8900
      8902
      8934

14 rows selected.

1.6 升序与降序

默认是升序

SYS@OCPLHR1> select * from scott.emp order by comm asc;		##升序排序，空值最小
SYS@OCPLHR1> select * from scott.emp order by comm desc;	##降序排序，空值最大

1.7 列别名

列别名不能使用在where子句中，若要使用，则需要使用子查询多嵌套一层
别名中包含空格或特殊字符（#、&）或者大小写敏感，将别名放在英文双引号中

SYS@OCPLHR1> select sal,sal+2000 "new sal" from scott.emp;
       SAL    new sal
---------- ----------
       800	 2800
      1600	 3600
      1250	 3250
      2975	 4975
      1250	 3250
      2850	 4850
      2450	 4450
      3000	 5000
      5000	 7000
      1500	 3500
      1100	 3100
       950	 2950
      3000	 5000
      1300	 3300
14 rows selected.

1.8 连字运算符

连接几个列：||

SYS@OCPLHR1> select ename || ' sal is ' || sal name_sal from scott.emp;

NAME_SAL
----------------------------------------------------------
SMITH sal is 800
ALLEN sal is 1600
WARD sal is 1250
JONES sal is 2975
MARTIN sal is 1250
BLAKE sal is 2850
CLARK sal is 2450
SCOTT sal is 3000
KING sal is 5000
TURNER sal is 1500
ADAMS sal is 1100
JAMES sal is 950
FORD sal is 3000
MILLER sal is 1300

14 rows selected.

1.9 去重（distinct）

注意：

1.oracle对内容是区分大小写的，a与A是不同的
2.当distinct修饰多列时，将多列的内容同时作为一个对象去提交，只要有一方不同。就看做一行不同的记录
3.默认下，查询会返回所有的行，包括重复行
4.在select子句中使用关键字distinct删除重复行

练习

SYS@OCPLHR1> select distinct deptno from scott.emp;

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

SYS@OCPLHR1> select distinct deptno,comm from scott.emp;

    DEPTNO	 COMM
---------- ----------
	30
	30	    0
	30	  500
	20
	30	 1400
	10
	30	  300

7 rows selected.

2、空值

2.1 定义空值

空值和数值型的列或者常量进行计算的结果为空，但是可以和字符串类型的列和常量进行连接，连接后的结果不变

2.1.1 空值和任何数字计算都是空值

SYS@OCPLHR1> select 1+null from dual;
    1+NULL
----------

2.1.2 空值也可以用 ’ ’ 表示

SYS@OCPLHR1> select 3*'' from dual;
      3*''
----------

2.1.3 空值与字符串的连接

SYS@OCPLHR1> select 'hello'||'' from dual;

'HELL
-----
hello

2.1.4 判断空值是否为空

SYS@OCPLHR1> select * from scott.emp where mgr is null;	##只能使用is null或is not null。不能使用mgr=''来判断空值

     EMPNO ENAME      JOB	       MGR		 HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT 	   		17-NOV-81	   5000 		   10

下面这种方法是错误的，不能查询空值
 SYS@OCPLHR1> slect * from scott.emp where mgr='';

2.2 空值不参与求平均值

SYS@OCPLHR1> select sum(comm) from scott.emp;	##求和
 SUM(COMM)
----------
      2200

SYS@OCPLHR1> select avg(comm) from scott.emp;	##求平均值
 AVG(COMM)
----------
       550

SYS@OCPLHR1> select 2200/13 from scott.emp;	
   2200/13
----------
169.230769

SYS@OCPLHR1> select count(comm) from scott.emp;	##计数
COUNT(COMM)
-----------
	  4

SYS@OCPLHR1> select count(*) from scott.emp;
  COUNT(*)
----------
	14

SYS@OCPLHR1> select 2200/4 from scott.emp;
    2200/4
----------
       550

2.3 空值在子查询中必须保证不为空，否则会报错

SYS@OCPLHR1> select * from scott.emp a
  2  where a.comm not in
  3  (select b.comm from scott.emp b
  4  where b.sal in (800,1600)
  5  and b.comm is not null);

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL      		 COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 		 0	   30
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30