Oracle学习之SELECT语句,过滤排序（一）

一.基本SQL-SELECT语句

• 查看表信息

  select * from emp;
  

• 查看表中的列

  desc emp;
  

  desc 是SQL Plus关键字，不是SQL关键字

• 查看表中的某列

   select employee_id from emp；
  

• 运算符

  select 2*2 from dual;	# 没有表是可用dual伪表代替
  select last_name,salary,12*salary+1000 from emp；
  

  select sysdate,sysdate+1,sysdate-2 from dual;	#日期函数只能做+，-
  

• 取别名

1. 空格

   select employee_id id from emp;
   

2. as

   select employee_id as id,last_name as name,12*salary as annual_sal from emp;`
   

3. “”

   select employee_id as "Id",last_name as “NamE”,12*salary as “annual_sal” from emp; 
   

   别名默认大写，如果想小写或其他格式，使用""

• 连接符

  select last_name||' is job_id is '|| job_id from emp; 
  

  除了起别名用双引号 除此以外都用单引号

• 去重

   select distinct department_id from emp; 
  

二. 过滤

2.1 where

#查询department_id = 90
select salary from employees where department_id = 90;

• 查询日期

#方法一
select employee_id,salary from employees where hire_date = '7-6月-1994'; 
#方法二 推荐使用方法二
select last_name,hire_date from employees where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07'; 

2.2 比较运算符

比较运算

操作符	含义
=	等于
>	大于
<	小于
>=	小于等于
<=	大于等于
<>或!=	不等于
between…and…	在两个值之间（包含边界）
in	等于值列表中的一个
like	模糊查询
is null	空值

#查询小于等于 4000和不等于7000的值
select last_name from employees where salary>= 4000 and salary != 7000;

#查询在4000和7000之间的值
select last_name from employees where salary between 4000 and 7000;

#查询等于70，80，90的值
select last_name,salary,department_id from employees where department_id in (70,80,90);

#查询last_name 含有a的值
select last_name from employees where last_name like '%a%';

#查询last_name第二位是a的值
select last_name from employees where last_name like '_a%';

#查询last_name 含有下划线的值
select last_name from employees where last_name like ’%\_%‘ escape '\';

escape ：回避特殊符号的：使用转义符

#查询为空的值
select last_name from employees where commission_pct is null;

2.3 逻辑运算符

逻辑运算

操作符	含义
and	并
or	或
not	否

#查询department_id=80并且salary <= 8000
 select last_name,salary,department_id from employees where department_id and salary <= 8000;

#查询salary=7500或者<= 8000
 select last_name,salary,department_id from employees where salary=7500  or salary <= 8000; 

#查询不为空的值
select last_name from employees where commission_pct is not null;

三. 排序

• 从高往低排desc

select last_name,salary from employees where department_id=80 order by salary desc;

• 从低往高排asc

select last_name,salary from employees where department_id=80 order by salary asc;

• 多层排序

select last_name,salary,department_id from employees order by salary desc,last_name asc;