一、在查詢中過濾行(where)
select employee_id,last_name,salary
from employees
where employee_id > 200
操作符 含義
= 等于（不是==）
> 大于
>= 大于、等于
< 小于
<= 小于、等于
<> 不等于（也可以是！=）
賦值使用：=符號
?
between…and…包含邊界值：
select last_name,hire_date,salary
from employees
where salary>=4000 and salary<=7000;
select last_name,hire_date,salary
from employees
where salary between 4000 and 7000;
in取括號中的幾個值（離散值）
select last_name,hire_date,salary,department_id
from employees
where department_id =90 or department_id =80 or department_id=70
select last_name,hire_date,salary,department_id
from employees
where department_id in (70,80,90);
like模糊查詢
select last_name,hire_date,salary,department_id
from employees
where last_name like '%a%';
--like
--員工中名字含有字符a的員工
select last_name,hire_date,salary,department_id
from employees
where last_name like '%a';
--員工中名字末位是a的員工
select last_name,hire_date,salary,department_id
from employees
where last_name like '%_%' escape '';
--員工中名字含有_的員工
null 空
select last_name,hire_date,salary,department_id,commission_pct
from employees
where commission_pct is null;
?
1.邏輯運算
操作符 含義
AND 邏輯并
OR 邏輯或
NOT 邏輯否
select last_name,hire_date,salary,department_id,commission_pct
from employees
where department_id = 80 and salary <=8000;
2.優先級
優先級
1 算法運算符
2 連接符
3 比較符
4 IS (NOT) NULL,LIKE,(NOT) IN
5 (NOT) BETWEEN
6 NOT
7 AND
8 OR
可以使用括號改變優先級順序
?
二、在查詢中對行進行排序
select last_name,hire_date,salary,department_id,commission_pct
from employees
where department_id = 80
order by salary desc
--desc降序 asc升序，默認為desc
select last_name,hire_date,salary,department_id,commission_pct
from employees
where department_id = 80
order by salary asc，last_name asc;
--根據工資降序排序，工資相同時按照工資排序
總結
1.使用where子句過濾數據
- 使用比較運算
- 使用between and，in，like，null運算
- 使用邏輯運算符AND,OR和NOT
2.使用ORDER BY子句進行排序
