数据库之基本功：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>