【Oracle学习07】使用SQL 检索，限制和排序数据
文章来源: 陶老师运维笔记-微信公众号
7.1 列出SQL SELECT 语句功能
7.2 数据规范化
7.3 执行基本的SELECT语句
7.3.2
7.3.3 SQL表达式和运算符
算术运算符:
alias列名:
select employee_id, job_id, start_date,end_date,(end_date -start_date) +1 as Employed
from job_history;
select employee_id, job_id, start_date,end_date,(end_date -start_date) +1 "Days Employed"
from job_history;
#有空格用''引起来
字符串链接符||
select 'The '||region_name || ' is on earth ' as region from regions;
字面值和DUAL表:
select 10*10 from dual;
select 10*10 from hr.regions; --4行
**引用符q' **
select "Tom's cat" from dual; #error
select 'Tom''s cat' as cat from dual; -- ok
select q'< Tom's cat >' from dual; -- ok
select q'| Tom's cat |' from dual; --ok
#
select 'The job ID for the ' || job_title || q'<'s is:>' || job_id from hr.jobs;
7.3.4 null
字符串与null运算时会略过null值，但是null的算术运算结果总是产生null值。
select 'a'|| null || 'b' from dual;
select 10*null , 2+null from dual;
#
select last_name,commission_pct from employees where commission_pct is null;
7.4 限制查询检索的行
#条件子句中的value会区分大小写
select * from hr.jobs where job_id = 'SA_REP'; #有结果
select * from hr.jobs where job_id = 'Sa_rep'; #无结果
select * from hr.jobs where job_id || Job_title = 'SA_REP'||'Sales Representative';
7.4.2 比较运算符
# between是闭集合,和>= xx <=xx，两者相等
select last_name,salary from hr.employees where salary between 3400 and 4000;
select last_name,salary from hr.employees where salary >=3400 and salary <=4000;
#like
select first_name from employees where first_name like 'A%';
select first_name from employees where first_name like 'A___';
#用escape可定义转义
select * from jobs where job_id like 'SA\_%' escape '\'; -- job_id=SA_REP
select * from jobs where job_id like 'SA$_%' escape '$';
#null
select last_name,commission_pct from employees where commission_pct is null;
条件运算符:
select last_name,commission_pct from employees where commission_pct != ''; --结果为空??和MySQL的结果不同。
select last_name,commission_pct from employees where commission_pct is not null;
select * from hr.employees where first_name not like 'R%';
7.4.4 优先规则
7.5 排序
#依查询列的位置排序。相当于order by hire_date
select last_name,hire_date,salary from hr.employees where job_id in ('SA_REP','MK_MAN') order by 2 desc;
select last_name,hire_date,salary from hr.employees where job_id in ('SA_REP','MK_MAN') order by job_id desc ,last_name;
#
select job_id,job_title,(MAX_SALARY-MIN_SALARY) as variance from hr.jobs where job_title in ('President','Manager') order by variance ,job_title desc ;
SQL限制行的子句
select last_name,salary from hr.employees order by salary offset 100 rows fetch first 5 rows only;
#
select last_name,salary from hr.employees order by salary offset 100 rows fetch first 5 rows with ties;
7.6 &符号替换
替换变量可以看成是占位符。执行时解析&变量，有时称为绑定(runtime binding)或 替换(runtime substitution)。
当有相同的变量在SQL中重复出现，可以使用&&来替换避免重复输入。
select * from hr.employees where last_name = '&LASTNAME' or employee_id = &EMPNO;
##&替换
SET DEFINE ON;
SET VERIFY ON;
SQL> select first_name,last_name from hr.employees where last_name like '%&SEARCH%' and first_name like '%&SEARCH%';
Enter value for search: K
Enter value for search: S
old 1: select first_name,last_name from hr.employees where last_name like '%&SEARCH%' and first_name like '%&SEARCH%'
new 1: select first_name,last_name from hr.employees where last_name like '%K%' and first_name like '%S%'
FIRST_NAME LAST_NAME
---------------------------------------- --------------------------------------------------
Steven King
Sundita Kumar
##双 &&替换,重复的
SQL> select first_name,last_name from hr.employees where last_name like '%&&SEARCH%' and first_name like '%&SEARCH%';
Enter value for search: G
old 1: select first_name,last_name from hr.employees where last_name like '%&&SEARCH%' and first_name like '%&SEARCH%'
new 1: select first_name,last_name from hr.employees where last_name like '%G%' and first_name like '%G%'
FIRST_NAME LAST_NAME
---------------------------------------- --------------------------------------------------
Girard Geoni
替换符&, 在SQL中的任何元素都可替换，如列名等。
##替换列
SQL> select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col;
Enter value for col: EMAIL
old 1: select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col
new 1: select first_name,job_id, EMAIL from hr.employees where job_id = 'MK_MAN' order by EMAIL
FIRST_NAME JOB_ID EMAIL
---------------------------------------- -------------------- --------------------------------------------------
Michael MK_MAN MHARTSTE
#替换SQL
SQL> select &rest_sql ;
Enter value for rest_sql: 10*10 from dual
old 1: select &rest_sql
new 1: select 10*10 from dual
10*10
----------
100
7.6.2 定义和验证
1.DEFINE 和UNDEFINE
替换&，在第一次引用时会隐式的创建了会话级别的变量。可用DEFINE 和UNDEFINE来定义及删除变量。
SQL> UNDEFINE col;
SQL> select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col;
# DEFINE
SQL> DEFINE col=EMAIL
SQL> select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col;
old 1: select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col
new 1: select first_name,job_id, EMAIL from hr.employees where job_id = 'MK_MAN' order by EMAIL
SET DEFINE OFF，可使客户工具本次不再保存会话变量或给&特殊含义。
SET DEFINE OFF;
select 'Coda & Sid' from dual;
'CODA&SID'
--------------------
Coda & Sid
2. VERIFY
SET 命令控制客户端环境变量。
SQL> SET VERIFY OFF
SQL> select 'Coda & Sid' from dual;
Enter value for sid: aa
'CODAAA'
--------------
Coda aa
SQL> SET VERIFY ON
SQL> select 'Coda & Sid' from dual;
Enter value for sid: bb
old 1: select 'Coda & Sid' from dual
new 1: select 'Coda bb' from dual
'CODABB'
--------------
Coda bb
7.7 本章知识点回顾
7.8 作业
#2) number(8,2)
-- SALARY NUMBER(8,2)
select employee_id,salary from hr.employees where employee_id=199;
update hr.employees set salary=12345678 where employee_id=199; #会出错
#3) END_DATE - START_DATE 是什么类型？ 答案：NUMBER类型。并不是日期型
select EMPLOYEE_ID,END_DATE - START_DATE from hr.job_history where rownum<3;
#8)
select first_name,last_name from hr.employees where last_name like '%&SEARCH%' and first_name like '%&&SEARCH%'; #--会要输入2次。
