Oracle之单表查询

单表查询

查询语句运行顺序

(5)select (6)distinct col_name,col_name,...
(1)from tablename
(2)where 条件表达式
(3)group by 分组字段
(4)having 条件表达式
(7)order by 排序字段

• []表示其中的内容可省略

1、简单查询

select 列名[,列名,...] from 表名

• select … from：是查询关键字
• select 后面跟的是要查询的字段
• from 后面跟表明，表示要从那张表中查询数据

-- 查询emp表中员工的编号、姓名、工作、工资
select empno, ename, job, sal from emp;

-- 查询emp表中所有列的信息
select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
select * from emp;

注意：* 号可以表示查询所有的列，但执行效率比写列名时的执行效率低

sql中的别名

别名语法：对象名 [as] 别名

select empno as 员工编号,ename 姓名 from emp;

注意：给表、视图区别名时不能加 as

select e.* from emp e;
select e.empno,e.ename,e.job from emp e;
select emp.empno,emp.ename,emp.job,emp.sal from emp;

2、where 条件查询

select 列名[,列名,...] from 表名 where 条件表达式

• where：条件关键字，后面跟条件表达式，这个条件一般是和数据库中列相关
• 条件表达式
  • 大于：>
  • 小于：<
  • 大于等于：>=
  • 小于等于：<=
  • 不等于：!= , <>
  • 等于：=
• 注意：在Oracle数据库中，数字类型直接写就可以，字符串类型的数据要用单引号括起来

-- 查询工资大于1500的员工信息
select * from emp where sal>1500;

-- 查询名字叫SMITH的员工的信息
select * from emp where ename='SMITH';

in

• 后面跟一个集合或者子查询

• 集合：Oracle中用()表示一个简单的集合，如(10, 20, 30)，(‘a’, ‘b’, ‘c’)

-- 查询emp表中，工资是800，1600，或者3000的员工信息
select * from emp where sal in (800,1600,3000);

not in

• 和in是相反的

-- 查询emp表中，工资不是800，1600，或者3000的员工信息
select * from emp where sal not in (800,1600,3000);

like

• 它只用在字符串列，表示对字符串进行模糊匹配
• 两个点位符
  • %：表示0个或者多个任意字符
  • _：表示有且只能有一个任意字符

-- 查询员工姓名S结尾的员工信息
select * from emp where ename like '%S';

-- 查询emp表中员工姓名的第二个字符是L的员工信息
select * from emp where ename like '_L%';

-- 查询emp表中员工姓名第三个字符是A的员工信息
select * from emp where ename like '__A%';

-- 查询员工姓名中有S的员工信息
select * from emp where ename like '%S%';

not like

• 和like相反

-- 查询名字中没有S的员工信息
select * from emp where ename not like '%S%';

all、any

• 后面都跟一个集合或者子查询
• all：所有
  • >all：表示大于集合中最大的元素
  • <all：表示小于集合中最小的元素
• any：任意一个
  • >any：表示大于集合中最小的元素
  • <any：表示小于集合中最大的元素

-- 查询员工信息，要求员工的工资比以下的值都低 1600，2000，3000
select * from emp where sal <all(1600,2000,3000);

-- 查询比任意一个（1600,2000,3000）工资高的员工信息
select * from emp where sal>any(1000,2000,3000);

-- 查询工资比所有人（1600,2000,3000）都高的员工信息
select * from emp where sal > all(1600,2000,3000);

exists

它不和任何列一起使用，后面跟的是一个子查询（查询语句select语句），如果子查询有结果，那么这个条件就是成立的，如果无结果条件不成立

not exists

• 和exists相反

select * from emp where exists(select * from emp where 1=0);

select * from emp where exists(select * from emp);

条件连接符

• and：表示并且，当and连接的两个条件同时成立，整体条件才成立
• or：表示或者，当or连接的两个条件有一个成立，整体条件就成立
• between value1 and value2：value1<value2，value1和value2一般是数字类型还可以是日期，表示值在value1和value2之间:[value1,value2]
• is null：表示这个值是null时条件成立
• is not null：表示这个值不为null条件成立

注意：“”(空字符串), 0, null 是不同的

-- 查询10号部门，工资大于1000的员工信息
select * from emp where deptno=10 and sal>1000;

-- 查询10号部门，或者工资小于2000的员工信息
select * from emp where deptno=10 or sal<2000;

-- 查询工资在1000到3000之间的员工信息
select * from emp where sal>=1000 and sal<=3000;
select * from emp where sal between 1000 and 3000;

-- 查询工资大于1000并且小于3000的员工信息
select * from emp where sal>1000 and sal<3000;
select * from emp where sal between 1000 and 3000 and sal!=1000 and sal!=3000;

-- is null:表示这个值是空时条件成立
select * from emp where comm is null;

-- is not null：表示值不为空间条件成立
select * from emp where comm is not null;

dual表：它是oracle内置的一个单行表

select 1 from dual;
select 'abc' from dual;
select 2*3 from dual;
select 1,2,3,4 from dual;

3、order by 排序

select 列名[,列名,...] from 表名 [where 条件表达式] [group by 列名] [having 分组条件] order by 排序的列名 [asc]|desc

• asc：升序（默认），可省略
• desc：降序，不可省略

-- 查询员工信息，按照员工的工资升序排序
select * from emp order by sal asc;
select * from emp order by sal;    -- 升序排列，asc可省略不写

-- 查询员工信息，按照员工编号降序排序
select * from emp order by empno desc;    -- 降序排列，desc不可省略

• order by：排序关键字，后面可以跟多个排序列，跟多个排序列时，按照第一排序列（order by 后第一个列）排序，如果第一个排序字段的值相同时，它会安装第二个排序列进行排序

-- 查询员工信息，按照部门编号升序排序，如果部门编号相同时，按照工资的升序排序
select * from emp order by deptno asc,sal asc;

-- 查询员工信息，按照部门编号降序排序，如果部门编号相同，按照员工编号升序排序
select * from emp order by deptno desc,empno;

-- 查询员工的编号、姓名、工作、工资、部门编号，按照部门编号升序排序，如果部门相同时，按照工资降序排序
select empno,ename,job,sal,deptno from emp order by deptno,sal desc;

注意：order by 后面除了可以跟表中的列名外，还可以跟 select 和 from 之间查询结果的序号，一般不这样使用

select empno,ename,job,sal,deptno from emp order by 3,4 desc;

4、聚合函数

• 聚合函数：对一组执行计算，并返回单个值
• count(列名|*|常数)：求记录数
• max(列名)：取最大值
• min(列名)：取最小值
• avg(列名)：求平均值
• sum(列名)：求和

-- 查询员工表中的员工人数
select count(empno) from emp;
select count(*) from emp;
select count(1) from emp;


-- 它们执行效率从上到下，依次降低
count(主键列或索引列)
count(*)
count(常数)
count(普通列) -- 普通列如果是null不统计


-- 查询工资总和
select sum(sal) from emp;

-- 查询员工的平均工资
select avg(sal) from emp;

-- 查询10号部门的最高工资
select max(sal) from emp where deptno=10;

-- 查询最低工资
select min(sal) from emp;

• distinct：去重关键字，跟在列的最前面

select distinct deptno from emp;
select distinct empno,deptno from emp; 
select count(distinct deptno) from emp;

-- 这个sql语句是错误的
select empno,distinct sal from emp;  

注意：distinct后面跟多个列时，判断重复数据，所有列的值完全相同时，它才会认为是重复数据

5、group by 分组

select 列名[,列名,...] from 表名 [where 条件表达式] group by 分组列

• group by：分组关键字，后面跟分组列名，可以是一个分组列，也可以是多个列

-- 查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;

-- 查询各个部门的部门编号、最低工资、平均工资、总工资及人数
select empno,min(sal),max(sal),avg(sal),sum(sal),count(*)  from emp group by empno;

-- 查询各个部门的员工人数
select deptno,count(empno) from emp group by deptno;

-- 查询每个部门职位相同的人数
select job,deptno,count(*) from emp group by job,deptno;

注意：group by 后面跟多个列时，只有当多个列的值同时相等时，它才会分为同一个组;

6、having

select 列名[,列名,...] from 表名 [where 条件表达式] group by 分组列 having 条件

• having：它是对分组后的数据进行筛选，条件表达式可以使用聚合函数

-- 查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;

-- 求平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

where 和 having 的异同

• where：where 后面跟的条件比having 后的条件先执行，且不能使用聚合函数
• having：可以使用聚合函数，一般having 和group by 联用。