oracle-03表查询

一、首先来了解emp表和dept表结构

1. emp员工信息表

列名	描述	数据类型	是否允许为空
EMPNO	员工编号	NUMBER(4)
ENAME	员工姓名	VARCHAR2(10)	Y
JOB	职位	VARCHAR2(9)	Y
MGR	上级的员工编号	NUMBER(4)	Y
HIREDATE	入职日期	DATE	Y
SAL	工资	NUMBER(7,2)	Y
COMM	奖金	NUMBER(7,2)	Y
DEPTNO	部门编号	NUMBER(2)	Y

2.dept部门信息表

列名	描述	数据类型	是否允许为空
DEPTNO	部门编号	NUMBER(2)
DNAME	部门名称	VARCHAR2(14)	Y
LOC	部门所在地	VARCHAR2(13)	Y

3.在u1用户下创建emp和dept表（以复制表的方式创建）

接下来操作emp和dept表的副表，避免修改原emp和dept表

语法：CREATE TABLE 表名 AS 查询语句;

–system或sys用户下来创建

CREATE TABLE u1.emp AS SELECT *FROM SCOTT.EMP;

CREATE TABLE u1.dept AS SELECT *FROM scott.dept;

二、基本查询

语法：SELECT [ALL|DISTINCT] {*|列名,…} FROM 表名;

ALL: 默认值，查询所有数据，包含重复数据
DISTINCT: 查询的数据不包含重复数据（重复数据只能查出一条）

1.查询所有列的数据
*： 查询出所有的列
列名,…: 只查询出指定的列

select *from emp

2.查询部分列的数据
–u1，查询emp表，员工姓名和员工工资

SELECT ENAME,SAL FROM emp;

–u1，查询dept表，只查询部门编号
select deptno from dept;

–u1, 查询emp表的部门编号列
select deptno from emp;

3.查询数据，不包含重复的数据

--u1，查询emp表的部门编号列（不包含重复数据）
select distinct deptno from emp;

4.给列起别名

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

三、条件查询

语法：
SELECT [ALL|DISTINCT] {*|列名,…} FROM 表名
[WHERE 条件表达式]

1.单个条件： >,>=,<,<=,=,!=(<>)不等于,is null,is not null, not

--u1用户下
--查询emp表中，工资大于3000的员工信息
select *from emp where sal>3000;

--查询emp表中，工资大于等于3000的员工信息
select *from emp where sal>=3000;

--查询emp表，20号部门的员工信息
select *from emp where deptno=20;

--查询emp表，除了20部门的员工信息
select *from emp where deptno!=20

--查询emp表，没有奖金的员工信息
select *from emp where comm is null;

--查询emp表，有奖金的员工信息
select *from emp where comm is not null;

2.多个条件 and ,or

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

--查询emp表，工资大于3000，或者职位是MANAGER的员工信息
select *from emp where sal>3000 or job='MANAGER';

--查询工资大于等于2000，小于等于3000的员工信息 [2000,3000]
select *from emp where sal>=2000 and sal<=3000;

列名 between 数值1 and 数值2  [数值1，数值2]

select *from emp where sal between 2000 and 3000; 

注意：oracle中命令不区分大小写，但是表中的数据是区分大小写的

四、模糊查询 --like

匹配字符：
%： 匹配0~n个字符
_: 匹配一个字符

--查询emp表，员工姓名以'S'开头的员工信息
select *from emp where ename like 'S%';

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

--查询emp表，员工姓名中包含'O'的员工信息
select *from emp where ename like '%O%';


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

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

五、排序查询

语法：
SELECT [ALL|DISTINCT] {*|列名,…} FROM 表名
[WHERE 条件表达式]
[ORDER BY 列名 [ASC|DESC] ]

ASC: 默认， 升序，从低到高
DESC: 降序 从高到低

--查询emp表，20号部门的员工信息，以工资升序显示
select *from emp where deptno=20 order by sal;

--查询emp表，20号部门的员工信息，以工资降序显示
select *from emp where deptno=20 order by sal desc;

六、分组查询

聚合函数
max(列名)：统计一组数据中此列的最大值
min(列名)：统计一组数据中此列的最小值
avg(列名)：统计一组数据中此列的平均值
sum(列名): 统计一组数据中此列的总和
count(列名)：统计一组数据中的条数，排除此列为空的数据
count(*): 统计一组数据中的所有条数

--查询所有员工的最高工资，最低工资，平均工资，工资总和，所有记录条数
select max(sal),min(sal),avg(sal),sum(sal),count(*) from emp;

--查询各个部门员工的最高工资，最低工资
  --首先以部门来分组
  --然后再统计各个部门的最高工资，最低工资

语法：
SELECT [ALL | DISTINCT] [selec_columns | *] FROM table_name --基本查询
[WHERE search_condition] --带条件查询
[GROUP BY columns] --分组查询
[HAVING search_condition] --分组查询的条件
[ORDER BY columns [ASC | DESC]] --排序查询

--查询部门编号
select deptno from emp;
  
--以部门编号分组来查询
select deptno from emp group by deptno;

注意：
a.分组查询，查询的列只能是group by 后面的列
b.分组查询，一般和聚合函数结合使用

--查询各个部门员工的最高工资，最低工资
select deptno,max(sal),min(sal) from emp group by deptno;

--查询每个部门的平均工资，且平均工资大于2000的信息
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

--查询每个部门的平均工资，且平均工资大于2000的信息，按照部门编号升序显示
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000 order by deptno;