學習目標

** 查詢中過濾行**
** 查詢中對行進行排序**

select employee_id, last_name from employees;-- 查詢工號大于200的 select employee_id, last_name from employees where employee_id > 200;-- 查詢員工工資大于五千的員工姓名、工號和工資 select last_name, employee_id, salary from employees where salary > 5000;select last_name, hire_date 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';-- 其他值比較 -- 操作符 -- 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 300-- in select last_name, department_id, salary from employees where department_id = 90;select last_name, department_id, salary from employees where department_id = 90 or department_id = 80 or department_id = 70;select last_name, department_id, salary from employees where department_id in(70, 80, 90);-- 模糊查詢 -- 員工中字符含有a的 select last_name, department_id, salary from employees where last_name like '%a%'; -- 員工中字符末尾是含有a的 select last_name, department_id, salary from employees where last_name like '%a'; -- 員工中名字的第二位字符是a的 _是一個字符 select last_name, department_id, salary from employees where last_name like '_a%'; -- 員工名字中含有下劃線的員工 -- 數據庫中沒有_的名字，所以修改了一個人的名字 update employees set last_name = 'Wha_len' where last_name = 'Whalen'; --查詢 需要用到轉義字符 select last_name, department_id, salary from employees where last_name like '%\_%' escape '\`'; --多加一個編譯器有問題其實是不加的--空值 is null -- 查詢公司員工的獎金率是否為空值 select last_name, department_id, salary, commission_pcy from employees where commission_pcy is null; -- 查詢公司員工的獎金率是非空值 select last_name, department_id, salary, commission_pcy from employees where commission_pcy is not null;--排序 order by--查詢80號部門的工資，讓工資從高往低排 select last_name, department_id, salary from employees where department_id = 80 order by salary desc; --查詢80號部門的工資，讓工資從低往高排 select last_name, department_id, salary from employees where department_id = 80 order by salary asc;--多層排序 -- 先按照工資來排序，在按照部門，最后按照姓名排序 select last_name, department_id, salary from employees order by salary asc, department_id asc, last_name asc; --按照年工資來排序，可以使用別名 select last_name, department_id, salary, salary * 12 as "annual" from employees order by "annual" desc;-- practise --1.查詢工資大于12000 的員工 select last_name from employees where salary > 12000; --2.查詢工號為176的員工的姓名和部門號 select last_name, department_id from employees where department_id = 176; --3.選擇工資不在5000 到12000的員工的姓名和工資 select last_name, salary from employees where salary not between 5000 and 12000; --4.選擇雇傭時間在1998-02-01 到1998-05-01之間的員工姓名，job_id 和雇傭時間 select last_name, job_id, hire_date from employees where to_char(hire_date, "yyyy-mm-dd") between = '1998-05-10' and '1998-05-10'; --5.選擇在20或50號部門工作的員工姓名和部門號 select last_name, department_id from employees where department_id = 20 or department_id = 50; -- where department_id in (20, 50); 6.選擇在1994年雇傭的員工姓名和雇傭時間 select last_name, department_id from employees where tochar(hire_date, 'yyyy') = '1994'; --where tochar(hire_date, 'yyyy-mm-dd') like '1994%'; -- where hire_date like '%94'; 7.選擇公司中沒有管理者的員工姓名及job_id select last_name, job_id from employees where manager_id = null; 8.選擇公司中有獎金的員工姓名，工資和獎金級別 select last_name, salary, commission_pcy from employees where commission_pcy is not null; 9.選擇員工姓名的第三個字母是a的員工姓名 select last_name from employees where last_name like '__a%'; 10.選擇姓名中有a和e的員工姓名 select last_name from employees where between last_name like '%a%e%' or last_name like '%e%a%';

總結

以上是生活随笔為你收集整理的Oracle select 基础查询语句 day02的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。