oracle数据库select基本语法

SELECT

一、查询列(字段)

• SELECT * FROM 表名;
  • 查询某个表中所有的记录的所有字段信息
• SELECT 列名 FROM 表名;
  • 查询某个表中所有的记录的指定字段信息
• SELECT 列名1,列名2 FROM 表名;
  • 查询某个表中所有的记录的字段1 字段2
• SELECT distinct 列名 FROM 表名;
  • 去除重复记录
• SELECT 表达式 FROM 表名;
  • 查询表达式
• SELECT xxx as 别名 FROM 表名 表别名
  • 使用别名

1.部分列

• 查询 部分字段，指定的字段名:
  –1)、检索单个列
  select ename from emp; --查询雇员姓名
  –2)、检索多个列
  select deptno,dname,loc from dept; --查询部门表的deptno,dname, loc 字段
  的数据。
  –以下查询的数据顺序不同(查询的字段顺序代表数据顺序)
  select loc,dname,deptno from dept;
  select deptno,dname,loc from dept;

2.所有列

• 查询所有的字段 通配符 *( 书写方便、可以检索未知列;但是降低检索的性能) ,数据的顺序跟定义表结构的顺序一致:
  –1)、检索所有列1
  select * from dept; --查询部门的所有信息
  –2)、检索所有列2
  select deptno,dname,loc from dept; --查询部门的所有信息

3.去除重复

• 使用distinct去重，确保查询结果的唯一性 :
  select distinct deptno from emp; --去重

4.别名

• 使用别名便于操作识别 、隐藏底层信息。存在字段别名和表别名：
  select ename as “雇员 姓名” from emp;
  select ename “雇员姓名” from emp;
  select ename 雇员姓名 from emp;
  select ename as 雇员姓名 from emp;
  select ename as " Ename" from emp;
• as: 字段别名可以使用as;表别名不能使用as
• “”:原样输出，可以存在 空格与区分大小写

5.字符串

• 使用’ ‘表示字符串(注意区分””) ，拼接使用 ||
  select ‘my’ from emp;
  select ename||‘a’||’–>’ info from emp;

6.伪列

• 不存在的列，构建虚拟的列
  select empno, 1*2 as count,‘cmj’ as name,deptno from emp;

7.虚表

• 用于计算表达式，显示单条记录的值
  select 1+1 from dual;

8.null

• null 遇到数字参与运算的结果为 null,遇到字符串为空串
  select 1+null from dual;
  select ‘1’||null from dual;
  select 1||‘2’||to_char(null) from dual;
  select ename,sal12+comm from emp;
  –nvl内置函数，判断是否为null,如果为空，取默认值0，否则取字段实际值
  select ename,sal12+nvl(comm,0) from emp;

二、查询行(记录)

• where 过滤行记录条件 ,条件有
  a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
  b)、and 、or、 not、 union、 union all、 intersect 、minus
  c)、null :is null、 is not null、 not is null
  d)、like :模糊查询 % _ escape(‘单个字符’)
  f)、in 、 exists(难点) 及子查询

1.比较条件

• = 、>、 <、 >=、 <=、 !=、 <>
  select * from emp where deptno !=20;
  select * from emp where deptno <>20;
  select * from emp where sal between 800 and 950; --between and是成对出现的
  –查询 员工的年薪大于20000的 员工名称、岗位 年薪
  –1)、nvl
  select ename,job,12*(nvl(comm,0)+sal) income from emp;
  –2)、年薪大于20000
  –错误不能使用别名: select ename,job,12*(nvl(comm,0)+sal) income from emp where income>2000;
  –a)、嵌套一个: 查询在前 过滤在后
  select ename,job,income from (select ename,job,12*(nvl(comm,0)+sal) income from emp) where income>2000;
  –b)、不使用别名 (推荐) :过滤在前，查询在后
  select ename,job,12*(nvl(comm,0)+sal) income from emp where 12*(nvl(comm,0)+sal) >2000 ;
  –了解 any some all
  – >=any(值列表) 大于最小值 <=any(值列表)小于最大值
  select * from emp where sal >=any(900,2000);
  select * from emp where sal <=any(900,2000);
  – some与any 一样的效果
  – all 大于最大值 小于最小值
  select * from emp where sal >=all(900,2000);
  select * from emp where sal <=all(900,2000);

2.且 或 非

• and、 or、 not
  select * from emp where sal>=900 and sal<=950;

3.null

• null不能使用条件判断，只能使用is ：
  –存在佣金的员工名称
  select * from emp where comm is null;
  –不存在佣金的员工名称
  select * from emp where comm is not null;
  select * from emp where not comm is null;

4.集合操作

• Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
• Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序 ；
• Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
• Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序
  –查询工资大于1500 或 含有佣金的人员姓名
  –union 去除重复行
  select ename from emp where sal>1500
  union
  select ename from emp where comm is not null;
  – union all 不去除重复行
  select ename from emp where sal>1500
  union all
  select ename from emp where comm is not null;
  –查询显示不存在雇员的所有部门号。
  select deptno from dept
  minus
  select distinct deptno from emp
  –查询工资大于1500 且 含有佣金的人员姓名
  select ename,sal,comm from emp where sal>1500
  intersect
  select ename,sal,comm from emp where comm is not null;

5.like :模糊查询

• %:零个及以上(任意个数的)的字符
• _:一个字符
• 遇到内容中包含 % _ 使用escape(‘单个字符’)指定转义符
  –查询员工姓名中包含字符A的员工信息
  select * from emp where ename like ‘%A%’;
  –查询员工姓名中包含第二个A的员工名称信息
  select * from emp where ename like ‘A%’;
  –数据中 员工姓名中 存在 _ % ,如何查找：
  –1)、编写测试数据
  insert into emp(empno,ename,sal) values(1000,'t%test’,8989);
  insert into emp(empno,ename,sal) values(1200,‘t_tes%t’,8000);
  –2)、查找
  –查询员工姓名中包含字符%的员工名称 岗位 工资 部门编号
  select ename,job,sal,deptno from emp where ename like ‘%a%%’
  escape(‘a’);

6.in 与 exists

• in相当于使用or的多个等值，定值集合 ,如果存在 子查询，确保 类型相同、字 段数为1，如果记录多，效率不高，用于 一些 少量定值判断上：
  select * from emp where sal in(900,800)
  –子查询(查询中再有查询) in 只能存在一个字段
  select * from emp where sal in (select sal from emp e where
  deptno=10)
  –10或30部门的雇员信息
  select * from emp where deptno in(10,30);
  –部门名称为 SALES 或 ACCOUNTING 的雇员信息
  select deptno from dept where dname in(‘SALES’,‘ACCOUNTING’);
  select *
  from emp
  where deptno in
  (select deptno from dept where dname in (‘SALES’,
  ‘ACCOUNTING’));
• exists条件为true,存在记录则返回结果，后续不再继续比较查询，与查询的字段无关，与记录有关:
  –exists :条件为true,存在记录，则返回结果，后续不再继续 ,与字段无关，与记录有关
  –exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true)

7.获取所有行的记录
select * from emp;
select * from emp where 1=1 ;
select * from emp where ename like ‘%’;

三、排序

• 使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。
  • 顺序 :asc（默认） desc
  • 多字段: 在前面字段相等时，使用后面的字段排序
  • 空排序: 降序为 desc，注意 null 为最后
    –按工资降序
    select * from emp order by sal desc;
    –null问题
    select * from emp order by nvl(comm,0),comm desc;
    select * from emp order by comm nulls first;
    –查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
    select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm
    desc;
    –查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
    select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm
    desc;
    –对部门编号为 20 或30的雇员，工资+佣金 进行升序排序，如果相同，则按姓名降序。
    –1、查询20、30 雇员
    select * from emp where deptno in(20,30);
    –2、工资+佣金排序
    select ename,sal,comm,sal+nvl(comm,0) c from emp where deptno
    in(20,30) order by c;
    –3、多个字段排序使用, 排序的字段可以使构建出来的虚拟的字段
    select ename,sal,comm from emp where deptno in(20,30) order by
    sal+nvl(comm,0),ename desc;