Oracle过滤和排序数据

Oracle过滤和排序数据

开发工具与技术：工具：Oracle数据库，语言：pl/sql
作者：刘胜
撰写时间:2019-03-25

---

一、作为Oracle的初学者，学习总结是必要的，通过本章小结学习Where过滤条件和Order By排序数据，两者的语句使用规则都放在From语句后面，Oracle的语法结构以及关键字是与很多流行的数据库都是大同小异的。
为了能够直观的理解Oracle数据库的关键字的使用方法，在Oracle数据库创建几张表，如下：

1.表的结构以及表之间的数据关系

2.创建表以及往表中插入数据的代码

//首先以创建两张表为例,练习一下在单行函数和多行函数里的关键字使用方法。下面代码是创建表的实例
//创建Employees表格的代码
Create table Employees(
     employee_id     number(10)    primary key,
     first_name      varchar2(25)  not null,
     last_name       varchar2(25),
     email           varchar2(25)  not null,
     phone_number    varchar2(20),
     hire_date       date not null,
     job_id          varchar2(10),
     salary          number(8,2),
     commission_pct  number(6),
     department_id   number(4),
     constraint pf_departments foreign key(department_id)
     references Departments(department_id),
     constraint pm_email unique(email)
) ;
//创建Departments表格的代码
Create table Departments(
     department_id   number(6),
     department_name varchar2(10) not null,
     constraint pk_departmentId primary key (department_id)     
);
//往employees表格里插入数据的代码
insert into Employees values (1,'Liu1','Sheng1','wwwtyke1@qq.com','17819789876',to_date('1999-01-19','yyyy-mm-dd'),0,'1000','1.2','1');
insert into Employees values (2,'Liu2','Sheng2','wwwtyke2@qq.com','17819789876',to_date('1999-01-19','yyyy-mm-dd'),0,'2000','1.2','2');
insert into Employees values (3,'Liu3','Sheng3','wwwtyke3@qq.com','17819789876',to_date('1999-01-19','yyyy-mm-dd'),0,'3000','1.2','3');
//往departments表格里插入数据的代码
Insert into Departments values(1,department1);
Insert into Departments values(2,department2);
Insert into Departments values(3,department3);

3.使用Order by和Where关键字排序和筛选数据

//Order by 排序  ORDER BY 子句在SELECT语句的结尾!
Select * from Employees Order by Emplyee_id DESC;//Order by Desc 倒序排序。
Select * from Employees Order by EMplyee_id ASC;//Order by ASC 升序排序，可以不用写ASC，系统默认是升序排序。
//Where 过滤
Select * from Employees Where First_name = 'Liu1' and Last_name = 'Sheng1';
Select * from Employees Where Salary > 2000 and Salary < 4000;
Select * from Employees Where Salary Between 2000 and 4000;
Select * from Employees Where Employee_id in (1,3);
Select * from Employees Where Employee_id not in (1,3);
Select * from Employees Where Employee_id = 1 or Employee_id = 3;
Select * from Employees Where First_name like ('%i%') and Commission_pct is not null;
Select * from Employees Where First_name like ('%i%') and Commission_pct is null;

二、总结
使用WHERE 子句过滤数据包括（比较运算，Between ··· And ··· ，In ，Like，Null运算和逻辑运算符And，Or还有Not）。
使用Order by 子句进行排序包括（Desc 倒叙，Asc升序）。