数据库之基本功：Where 中常用运算符

1. 运算符及优先级

 ( )优先级最高

SQL> show user;
USER is "SCOTT"
SQL> select ename, job, sal, comm from emp where job='SALESMAN' OR job='PRESIDENT' and sal> 1500;ENAME                          JOB                                SAL       COMM
------------------------------ --------------------------- ---------- ----------
ALLEN                          SALESMAN                          1600        300
WARD                           SALESMAN                          1250        500
MARTIN                         SALESMAN                          1250       1400
TURNER                         SALESMAN                          1500          0SQL> 

知识点:条件子句使用比较运算符比较两个选项,重要的是要理解这两个选项的数据类型。

2.BETWEEN AND

查询出在某一范围内的行.

-- between低值and高值, 包括低值和高值.SQL> SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 1500;ENAME                                 SAL
------------------------------ ----------
WARD                                 1250
MARTIN                               1250
TURNER                               1500
ADAMS                                1100
MILLER                               1300SQL> 

3. %模糊查询及其通配符:

在where字句中使用like谓词,常使用特殊符号"%"或"_"匹配查找内容

SQL> 
SQL> create table test (name char (10));
insert into test values ('sFdL');
insert into test values ('AEdLHH');
insert into test values ('A%dMH');Table created.SQL> 
1 row created.SQL> 
1 row created.SQL> 
1 row created.SQL> insert into test values('Jack');1 row created.SQL> commit;Commit complete.SQL> select * from test;NAME
------------------------------
sFdL
AEdLHH
A%dMH
JackSQL> select * from test where name like 'A%';NAME
------------------------------
AEdLHH
A%dMHSQL> select * from test where name like '%A%';NAME
------------------------------
AEdLHH
A%dMHSQL> 

4. 转义通配符\或/

使用escape \可以取消特殊符号如%的作用.

SQL> 
SQL> -- \%.将A%dMH里的%转义,或者将\换成/也可以.自定义转义符.
SQL> select * from test where name like 'A\%%' escape '\';NAME
------------------------------
A%dMHSQL> select * from test where name like '%\%%' escape '\';NAME
------------------------------
A%dMHSQL>

5. 连接符||

SQL> 
SQL> select empno||' is Scott''s empno' from emp where empno=7788;EMPNO||'ISSCOTT''SEMPNO'
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7788 is Scott''s empnoSQL> select empno||ename from emp where empno=7788;EMPNO||ENAME
------------------------------------------------------------------------------------------------------------------------------------------------------
7788SCOTTSQL>

6. Q-quote

Oracle还提供了一个Q-quote的表达式.来简化SQL或PLSQL中字符串的表示.其格式为q'[字符串]',输出为方括号中的原始字符串格式.其中方括号可以更换为其他的任意一对特殊符号.

SQL> 
SQL> select q'[God isn''t a girl]' name from dual;NAME
---------------------------------------------------
God isn''t a girlSQL> select q'\God isn''t a girl\' name from dual;NAME
---------------------------------------------------
God isn''t a girlSQL> select q'+God isn''t a girl+' name from dual;NAME
---------------------------------------------------
God isn''t a girlSQL> select q'2God isn''t a girl2' name from dual;NAME
---------------------------------------------------
God isn''t a girlSQL>

7. '' 和" "的用法:

''内表示字符或日期数据类型；
而"" 一般用于别名中有大小写、保留字、空格等场合,引用recyclebin中的《表名》也需要" ".

单引号的转义:连续两个单引号表示转义.

sql中字符串的两边是需要加单引号的.表示字符串的开始和结束.如果我们想在sql中输出一个实际的单引号’,那么需要连续输入四个单引号’’’’.其中中间的两个单引号表示一个单引号.
第一个单引号:表示字符串开始
第二个单引号:表示转义.后面接的单引号转义为真正的单引号
第三个单引号:表示实际输出的单引号
第四个单引号:表示字符串结束

SQL> select '''' N from dual;N
---
'SQL> select 'God isn''t a girl'name from dual;NAME
------------------------------------------------
God isn't a girlSQL>

8. 使用逻辑操作符: AND, OR, NOT

AND 两个条件都为TRUE ,则返回TRUE
OR 两个条件中任何一个为TRUE,则返回TRUE
NOT 如果条件为FALSE,返回TRUE

SQL> 
SQL> 
SQL> SELECT empno, ename, job, sal FROM emp WHERE sal>=1100 AND job= 'CLERK';EMPNO ENAME                          JOB                                SAL
---------- ------------------------------ --------------------------- ----------7876 ADAMS                          CLERK                             11007934 MILLER                         CLERK                             1300SQL> SELECT empno, ename, job, sal FROM emp WHERE sal>=1100 OR job = 'CLERK';EMPNO ENAME                          JOB                                SAL
---------- ------------------------------ --------------------------- ----------7369 SMITH                          CLERK                              8007499 ALLEN                          SALESMAN                          16007521 WARD                           SALESMAN                          12507566 JONES                          MANAGER                           29757654 MARTIN                         SALESMAN                          12507698 BLAKE                          MANAGER                           28507782 CLARK                          MANAGER                           24507788 SCOTT                          ANALYST                           30007844 TURNER                         SALESMAN                          15007876 ADAMS                          CLERK                             11007900 JAMES                          CLERK                              9507902 FORD                           ANALYST                           30007934 MILLER                         CLERK                             130013 rows selected.SQL> SELECT ename, job FROM emp WHERE job NOT IN ('CLERK', 'MANAGER', 'ANALYST');ENAME                          JOB
------------------------------ ---------------------------
ALLEN                          SALESMAN
WARD                           SALESMAN
MARTIN                         SALESMAN
TURNER                         SALESMANSQL>

9.用IN操作符检验某值是否在某列表中

SQL> 
SQL> SELECT empno,ename,sal,mgr from emp where mgr IN (7902, 7566, 7788);EMPNO ENAME                                 SAL        MGR
---------- ------------------------------ ---------- ----------7369 SMITH                                 800       79027788 SCOTT                                3000       75667876 ADAMS                                1100       77887902 FORD                                 3000       7566SQL>

10. 交互输入变量符&和&&的用途

10.1 &两种写法

-- 输入 empnumber 的值:  7788,其中&后可以写任意字符SQL> 
SQL> select empno,ename from emp where empno=&empnumber;
Enter value for empnumber: 7788
old   1: select empno,ename from emp where empno=&empnumber
new   1: select empno,ename from emp where empno=7788EMPNO ENAME
---------- ------------------------------7788 SCOTTSQL> set verify off;
SQL> select empno,ename from emp where empno=&empnumber;
Enter value for empnumber: 7788EMPNO ENAME
---------- ------------------------------7788 SCOTTSQL> 

-- &后面是字符型的,注意单引号问题,可以有两种写法:
-- 1、 带''输入emp_name 的值:  SCOTT
SQL> select empno,ename from emp where ename='&emp_name';
EMPNO    ENAME
-----    -----
7788    SCOTT
-- 2、 不带''输入emp_name 的值要加'':  ‘SCOTT’
SQL> select empno,ename from emp where ename=&emp_name;
EMPNO    ENAME
-----    -----
7788    SCOTT

SQL> 
SQL> select empno,ename from emp where ename='&emp_name';
Enter value for emp_name: SCOTTEMPNO ENAME
---------- ------------------------------7788 SCOTTSQL>
SQL> select empno,ename from emp where ename=&emp_name;
Enter value for emp_name: 'SCOTT'EMPNO ENAME
---------- ------------------------------7788 SCOTTSQL> 

10.2 set verify off

如果不想显示"原值"和"新值"的提示,可以使用set verify on|off命令

-- 去掉 原值、新值 提示语SQL> 
SQL> set verify off;
SQL> select empno, ename from emp where ename='&emp_name';
Enter value for emp_name: SCOTTEMPNO ENAME
---------- ------------------------------7788 SCOTTSQL> 

10.3 &&存储定义

&&存储了第一次输入值,使后面的相同的&不再询问,自动取代.

-- 输入 salary 的值:sal	//&后的字符定义了要输入的查询列
SQL> select empno,ename,&&salary from emp where deptno=10 order by &salary; 
Enter value for salary: sal	-- 第一次询问,define了sal	
EMPNO 	ENAME	SAL
-----	------	-----
7934 	MILLER	1300
7782 	CLARK	2450
7839 	KING	5000
SQL> select empno, ename, &&salary from emp where deptno=10 order by &salary;
EMPNO 	ENAME	SAL			-- 第二次不在询问
-----	------	-----
7934 	MILLER	1300
7782 	CLARK	2450
7839 	KING	5000

10.4 define定义变量

-- 定义变量
SQL> 
SQL> show user;
USER is "SCOTT"
SQL> show con_name;CON_NAME
------------------------------
PDB1
SQL> define emp_num=7788
SQL> select empno,ename,sal from emp where empno=&emp_num;EMPNO ENAME                                 SAL
---------- ------------------------------ ----------7788 SCOTT                                3000SQL> 

10.5 define查询定义变量

-- 显示当前已经定义的变量(包括默认值)
SQL> 
SQL> define
DEFINE _DATE           = "07-MAR-23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "PDB1" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
DEFINE SALARY          = "sal" (CHAR)
DEFINE EMP_NUM         = "7788" (CHAR)
SQL> 

10.6 define开关和解除

注:上面给的 &salary已经在当前session下存储了,可以使用undefine salary解除.
define(定义变量)和undefine命令(解除变量)

SQL> define
DEFINE _DATE           = "07-MAR-23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "PDB1" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
DEFINE SALARY          = "sal" (CHAR)
DEFINE EMP_NUM         = "7788" (CHAR)
SQL> 
SQL> undefine emp_num
SQL> undefine SALARY
SQL> 
SQL> define
DEFINE _DATE           = "07-MAR-23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "PDB1" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
SQL>