数据库 查询语句

条件查询
select *|字段1,字段2… from 表名 where 行过滤条件;
求20部门所有员工信息
select * from emp where deptno = 20;
执行顺序 from—where—select

– = 、 >、 <、 >=、 <=、 !=、 <>、
所有员工中薪资>800

• select * from emp where sal>800;

SMITH查询斯密斯的信息

• select * from emp where ename=‘SMITH’;

求除了SMITH以外的所有员工信息

• select * from emp where ename != ‘SMITH’;
• select * from emp where ename <> ‘SMITH’;
• select * from emp where not ename = ‘SMITH’;

and 、or、 not , between and
求薪资在1500~2500之间的员工信息

• select * from emp where sal >=1500 and sal<=2500;

• select * from emp where sal between 1500 and 2500;

求薪资>1500并且是30部门的员工名称,部门标号,薪资

• select ename,deptno,sal from emp where sal>=1500 and deptno=30;

求薪资>1500或者30部门的员工名称,部门标号,薪资

• select ename,deptno,sal from emp where sal>=1500 or deptno=30;

求除了薪资>1500和30部门的员工名称,部门标号,薪资

• select ename,deptno,sal from emp where not sal>=1500 and not
  deptno=30;
• select ename,deptno,sal from emp where not(sal>=1500 and deptno=30);
• select ename,deptno,sal from emp where not(sal>=1500 or deptno=30);

查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资

• select ename,deptno,sal from emp where job=‘CLERK’ and deptno=20;

求奖金为null的人

• select * from emp where comm is null;

求有奖金的人

• select * from emp where comm is not null;
• select * from emp where not comm is null;

–集合操作

• 求薪资>1500或者30部门的员工名称,部门标号,薪资
  select * from emp where sal>=1500
  Union
  select * from emp where deptno=30;

• Union All，全集(不去重)
  select * from emp where sal>=1500
  Union All
  select * from emp where deptno=30;

• 求没有员工在的部门标号
  select deptno from dept
  Minus
  select distinct deptno from emp;

like 模糊查询 %任意个任意字符 _任意一个字符
姓名以A开头的所有员工信息

• select * from emp where ename like ‘A%’;

使用模糊匹配做精确匹配

• select * from emp where ename like ‘JONES’;

姓名中第二个字符为A的所有员工姓名

• select ename from emp where ename like ‘_A%’;

姓名以H结尾的所有员工信息

• select ename from emp where ename like ‘%H’;

查找名字中带有%的用户

• select * from emp where ename like ‘%A%’;
• select * from emp where ename like ‘%C%%’ escape(‘C’);
  任意指定转义字符,需要使用escape() 中说明

in 匹配后面结果集中的任意一个数据,无法做区间判断
查询薪资为 1500 2000 2500 5000的员工信息

• select * from emp where sal=1500 or sal = 2000 or sal=2500 or
  sal=5000;
• select * from emp where sal in (1500,2000,2500,5000);

查找 KING 和 SMITH 两个员工的信息

• select * from emp where ename = ‘KING’ or ename = ‘SMITH’;
• select * from emp where ename in (‘KING’,‘SMITH’);

exists 存在即保留 存在即合法

• select * from emp where exists (select * from dept where
  dname=‘SALES’); select * from emp where exists (select deptno,
  dname from dept where dname in (‘SALES’, ‘ACCOUNTING’));

获取所有行记录

• select * from emp;
• select * from emp where 1=1;

排序 order by desc 降序 asc升序
select * from 表名 where 行过滤条件 order by 排序字段; --默认升序
from–where–select–order by
select * from emp order by sal asc;

根据薪资进行升序排序,薪资相同的根据员工编号进行降序排序

• select * from emp order by sal asc,empno desc;

根据员工的奖金进行降序排序 nulls first nulls last

• select * from emp where deptno=30 order by comm desc nulls first;