Oracle 19C学习 - 03. 使用where进行条件查询和数据排序

（本博文采用的数据库是Oracle自带的hr用户范本数据库)

 

WHERE命令进行搜索结果的限制

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

 

WHERE子句位于FROM命令之后。

SELECT * FROM EMPLOYEES WHERE department_id = 100;

 

作为常量的字符串和日期都要用单引号引起来，字符串要区分大小写。

日期 常量按照‘DD-MMM-YY’来写，中文系统的月份用中文写，英文系统的月份用OCT, AUG这样的缩写来写。

SELECT * FROM employees WHERE last_name = 'Popp';

SELECT * FROM employees WHERE hire_date='07-12月-07' 

 

比较运算符操作

 

SELECT last_name 名字, salary 工资
FROM employees
WHERE salary <> 3000;   
--不等于还可以写为 !=

SELECT last_name 名字, salary 工资
FROM employees
WHERE salary BETWEEN 2500 AND 3000  
 --工资在2500 -3000的人

-- IN运算符查找department_id为90 或 100的人
SELECT *
FROM employees
WHERE department_id IN (90,100);

-- LIKE命令使用的时候， %代表通配符，_代表单字符通配符。

SELECT *
FROM employees
WHERE last_name LIKE 'K%' AND first_name LIKE '%g';

-- 使用LIKE找到姓的第二个字母是a，倒数第二个字母也是a的人。

SELECT *
FROM employees
WHERE last_name LIKE '_a%' AND last_name LIKE '%a_';

-- IS NULL 查找含有空值的记录。

SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL;

-- IS NOT NULL 查找不为空值的记录。
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

　　

逻辑运算符操作

 

-- AND： 找出来工资大于1000并且名字带man的人

SELECT last_name, first_name, salary
FROM employees
WHERE salary > 1000
AND last_name LIKE '%man%'

 

-- OR： 找出来工资大于1000，或者名字带man的人

SELECT last_name, first_name, salary
FROM employees
WHERE salary > 1000
OR last_name LIKE '%man%'

 

-- NOT： 找出来所有职位不是'IT_PROG', 'ST_CLERK', 'SA_REP'的人

SELECT last_name, salary
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP')

　　

 

 使用ORDER BY子句对结果进行排序

SELECT column_name,column_name
FROM table_name
ORDER BY column_name ASC|DESC ,column_name ASC|DESC;

 

--使用ORDER BY对工资进行降序排序。

SELECT first_name || '.' ||last_name 姓名, salary 工资
FROM employees
ORDER BY salary DESC;

 

--使用列别名同样可以排序。

SELECT first_name || '.' ||last_name as 姓名, salary as 工资
FROM employees
ORDER BY 姓名;

 

--按照列号也能够排序。

SELECT first_name ，last_name 姓名, salary 工资  --按照last_name进行排序
FROM employees
ORDER BY 2;

 

--按照多列进行排序。比如先按照部门进行升序排序，然后再在本部门内，进行工资的降序排序。

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

　

SQL行限制子句

FETCH FIRST n ROWS ONLY：取出前n行的数据。

-- Fetch FIRST n ROWS ONLY 取出前5行数据
SELECT last_name, department_id, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 ROWS ONLY;

　　

OFFSET n ROWS FETCH NEXT n ROWS ONLY : 跳过某些行以后再往后取n行数据。

-- OFFSET n ROWS FETCH NEXT n ROWS ONLY 跳过两行，取5行数据。
SELECT last_name, department_id, salary
FROM employees
ORDER BY salary
OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY;

　　

 

替代变量

在SQL语句中，可以使用& 或者&&定义变量。

一个&的变量，每次遇到都要求进行赋值， 即使是同一句语句的同名变量，都要每次赋值。

SELECT last_name, salary
FROM employees
WHERE employee_id = '&JOBID';  --&JOBID为变量名

SELECT last_name, salary
FROM employees
WHERE employee_id = '&JOBID';  --再执行这个语句时，仍然要求输入&JOBID的值

 

 

两个&&的变量相当于全局变量，全局变量在给定一次值以后，运行所有的语句都不会再要求输入值。

SELECT last_name, salary 
FROM employees
WHERE employee_id = '&&EMP_ID';  --这里直接以102代入

SELECT last_name, salary 
FROM employees
WHERE employee_id = '&&EMP_ID';  --这里直接以102代入，不再提示输入EMP_ID的值了

 

如果是字符的变量，必须将变量名放到单引号之间。

SELECT last_name, salary, job_id 
FROM employees
WHERE last_name = '&emp_name'  --输入king可以查询 last_name是King的人员。

SELECT last_name, salary 
FROM employees
WHERE hire_date = '&hiredate' --输入30-1月-04

 

用变量替代列名。

SELECT last_name, department_id, job_id, &&column
FROM employees
ORDER BY &&column

 

 

DEFINE和UNDEFINE进行变量操作

DEFINE var_name = value  声明一个变量并赋值。

UNDEFINE var_name 删除变量

&var_name  引用变量

 

DEFINE employee_name = 'King'

SELECT * FROM employees WHERE last_name = '&employee_name'  --要用&来引用变量，字符串变量用单引号包围。
ORDER BY last_name ASC;

UNDEFINE employee_name;

 

 

SET VERIFY ON 验证变量的值

在SQLPLUS中使用这个命令，可以用来显示变量新旧值的变化。

默认SET VERIFY ON的。

#默认VERIFY是ON的，会提示新旧值。

SQL> select first_name, last_name from employees
  2  where first_name like '&emp';
Enter value for emp: L%
old   2: where first_name like '&emp'
new   2: where first_name like 'L%'

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Laura                Bissot
Lex                  De Haan

#这里设置为OFF，不再提示新旧值。
SQL> set verify off;
SQL> select first_name, last_name from employees
  2  where first_name like '&emp';
Enter value for emp: L%

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Laura                Bissot
Lex                  De Haan

　　

在SQLDeveloper中要把SET VERIFY ON;与其他的语句一起选择起来执行才能看到效果。