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