oracle学习之查询

desc emp; 显示数据库表结构

select empno,ename,sal*12 from emp;

     EMPNO ENAME          SAL*12
---------- ---------- ----------
      7369 SMITH            9600
      7499 ALLEN           19200
      7521 WARD            15000
      7566 JONES           35700
      7654 MARTIN          15000
      7698 BLAKE           34200
      7782 CLARK           29400
      7788 SCOTT           36000
      7839 KING            60000
      7844 TURNER          18000
      7876 ADAMS           13200
      7900 JAMES           11400
      7902 FORD            36000
      7934 MILLER          15600

日期类型是日期相加减
select empno,ename,hiredate,hiredate+10 from emp;

     EMPNO ENAME      HIREDATE   HIREDATE+1
---------- ---------- ---------- ----------
      7369 SMITH      17-12月-80 27-12月-80
      7499 ALLEN      20-2月 -81 02-3月 -81
      7521 WARD       22-2月 -81 04-3月 -81
      7566 JONES      02-4月 -81 12-4月 -81
      7654 MARTIN     28-9月 -81 08-10月-81
      7698 BLAKE      01-5月 -81 11-5月 -81
      7782 CLARK      09-6月 -81 19-6月 -81
      7788 SCOTT      19-4月 -87 29-4月 -87
      7839 KING       17-11月-81 27-11月-81
      7844 TURNER     08-9月 -81 18-9月 -81
      7876 ADAMS      23-5月 -87 02-6月 -87
      7900 JAMES      03-12月-81 13-12月-81
      7902 FORD       03-12月-81 13-12月-81
      7934 MILLER     23-1月 -82 02-2月 -82

连接字符串 ||
select ename || ' is a ' || job from emp;

ENAME||'ISA'||JOB
-------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
CLARK is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK

select empno,ename || ' is annual salary is ' || sal*12 from emp;

     EMPNO ENAME||'ISANNUALSALARYIS'||SAL*12
---------- -----------------------------------------------------------------------
      7369 SMITH is annual salary is 9600
      7499 ALLEN is annual salary is 19200
      7521 WARD is annual salary is 15000
      7566 JONES is annual salary is 35700
      7654 MARTIN is annual salary is 15000
      7698 BLAKE is annual salary is 34200
      7782 CLARK is annual salary is 29400
      7788 SCOTT is annual salary is 36000
      7839 KING is annual salary is 60000
      7844 TURNER is annual salary is 18000
      7876 ADAMS is annual salary is 13200
      7900 JAMES is annual salary is 11400
      7902 FORD is annual salary is 36000

字段别名
  员工编号 员工信息
---------- -----------------------------------------------------------------------
      7369 SMITH is annual salary is 9600
      7499 ALLEN is annual salary is 19200
      7521 WARD is annual salary is 15000
      7566 JONES is annual salary is 35700
      7654 MARTIN is annual salary is 15000
      7698 BLAKE is annual salary is 34200
      7782 CLARK is annual salary is 29400
      7788 SCOTT is annual salary is 36000
      7839 KING is annual salary is 60000
      7844 TURNER is annual salary is 18000
      7876 ADAMS is annual salary is 13200
      7900 JAMES is annual salary is 11400
      7902 FORD is annual salary is 36000
      7934 MILLER is annual salary is 15600

sql语言中的空值
空值是无效的，未指定的，未知的或不可预知的值
空值不等同于空格或者0

在表达式中使用空值
算术表达式中如果出现空值，则整个表达式结果为空
连接表达式中出现的空值被当作一个空的（长度为零的字符串处理）
select ename || comm as info1 , sal + comm as total from emp;

INFO1                                                   TOTAL
-------------------------------------------------- ----------
SMITH
ALLEN300                                                 1900
WARD500                                                  1750
JONES
MARTIN1400                                               2650
BLAKE
CLARK
SCOTT
KING
TURNER0                                                  1500
ADAMS
JAMES
FORD
MILLER
如果算术运算符中出现空值，则显示的结果也为空值。

去除重复行
distinct 关键字 如果是多个字段，则显示的是所有的字段的组合的集合不重复。
select distinct empno,job from emp;

查询结果排序
select empno,job from emp order by empno desc;
也可以按照别名排序
select empno,job,sal*12 as totalsal from emp order by totalsal desc;
部门编号正序，工资数倒序的内容的值
select deptno,empno,job,sal*12 as totalsal from emp order by deptno,totalsal desc;

to_date函数
select * from emp where hiredate > to_date('1982-03-10','yyyy-mm-dd')
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-4月 -87       3000                    20
      7876 ADAMS      CLERK           7788 23-5月 -87       1100                    20

比较运算符
>
<
=
>=
<=
<> 不等于

between and
介于两者之间，包括最大值和最小值
select * from emp where sal between 1900 and 2600
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-6月 -81       2450                    10

in 在集合的内容中查询，如果查不到，则不显示
select * from emp where ename in('WARD','ddd')

使用like进行模糊查询
使用%号进行查询，模糊多个
使用-表示查询一个字符
escape表示转义字符
select * from emp where ename like '%\_%' escape '\';
如果遇到\就代表转义字符，这个查询表示的是前边或者后边一个或者多个字母的情况，中间是_的时候，这个一般用在关键中，就是如果
查询的结果中包含关键字的情况。
一个单引号是转义字符，''这种情况表示一个单引号

判断空值 is null
查询数据库中所有是空值的列
select * from emp where comm is null
select * from emp where comm is not 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

逻辑运算符
and or not


这个查询的结果是所有工作是销售和工作是文员但是工资大于1280的那些人
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal > 1280
这个是所有销售和文员工资大于1280的
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal > 1280