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

最新推荐文章于 2024-04-29 14:49:05 发布

数据架构

最新推荐文章于 2024-04-29 14:49:05 发布

阅读量2k

点赞数 1

分类专栏： Oracle 数据库语言基础文章标签：数据库 sql oracle

本文链接：https://blog.youkuaiyun.com/u011868279/article/details/129381929

版权

Oracle 数据库语言基础专栏收录该内容

13 篇文章

订阅专栏

本文介绍了SQL查询中的关键概念，包括运算符优先级、BETWEEN和LIKE子句用于范围和模糊查询，转义特殊字符，以及连接符和Q-quote的使用。此外，还讲解了AND、OR、NOT逻辑操作符，IN操作符，以及交互式输入变量的两种方式（&和&&）及其应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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          0

SQL>

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

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                               1300

SQL>

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
Jack

SQL> select * from test where name like 'A%';

NAME
------------------------------
AEdLHH
A%dMH

SQL> select * from test where name like '%A%';

NAME
------------------------------
AEdLHH
A%dMH

SQL>

4. 转义通配符\或/

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

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

NAME
------------------------------
A%dMH

SQL> select * from test where name like '%\%%' escape '\';

NAME
------------------------------
A%dMH

SQL>

5. 连接符||

SQL> 
SQL> select empno||' is Scott''s empno' from emp where empno=7788;

EMPNO||'ISSCOTT''SEMPNO'
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7788 is Scott''s empno

SQL> select empno||ename from emp where empno=7788;

EMPNO||ENAME
------------------------------------------------------------------------------------------------------------------------------------------------------
7788SCOTT

SQL>

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 girl

SQL> select q'\God isn''t a girl\' name from dual;

NAME
---------------------------------------------------
God isn''t a girl

SQL> select q'+God isn''t a girl+' name from dual;

NAME
---------------------------------------------------
God isn''t a girl

SQL> select q'2God isn''t a girl2' name from dual;

NAME
---------------------------------------------------
God isn''t a girl

SQL>

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 girl

SQL>

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                             1100
      7934 MILLER                         CLERK                             1300

SQL> SELECT empno, ename, job, sal FROM emp WHERE sal>=1100 OR job = 'CLERK';

     EMPNO ENAME                          JOB                                SAL
---------- ------------------------------ --------------------------- ----------
      7369 SMITH                          CLERK                              800
      7499 ALLEN                          SALESMAN                          1600
      7521 WARD                           SALESMAN                          1250
      7566 JONES                          MANAGER                           2975
      7654 MARTIN                         SALESMAN                          1250
      7698 BLAKE                          MANAGER                           2850
      7782 CLARK                          MANAGER                           2450
      7788 SCOTT                          ANALYST                           3000
      7844 TURNER                         SALESMAN                          1500
      7876 ADAMS                          CLERK                             1100
      7900 JAMES                          CLERK                              950
      7902 FORD                           ANALYST                           3000
      7934 MILLER                         CLERK                             1300

13 rows selected.

SQL> SELECT ename, job FROM emp WHERE job NOT IN ('CLERK', 'MANAGER', 'ANALYST');

ENAME                          JOB
------------------------------ ---------------------------
ALLEN                          SALESMAN
WARD                           SALESMAN
MARTIN                         SALESMAN
TURNER                         SALESMAN

SQL>

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

SQL> 
SQL> SELECT empno,ename,sal,mgr from emp where mgr IN (7902, 7566, 7788);

     EMPNO ENAME                                 SAL        MGR
---------- ------------------------------ ---------- ----------
      7369 SMITH                                 800       7902
      7788 SCOTT                                3000       7566
      7876 ADAMS                                1100       7788
      7902 FORD                                 3000       7566

SQL>

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=7788

     EMPNO ENAME
---------- ------------------------------
      7788 SCOTT

SQL> set verify off;
SQL> select empno,ename from emp where empno=&empnumber;
Enter value for empnumber: 7788

     EMPNO ENAME
---------- ------------------------------
      7788 SCOTT

SQL>

-- &后面是字符型的,注意单引号问题,可以有两种写法:
-- 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: SCOTT

     EMPNO ENAME
---------- ------------------------------
      7788 SCOTT

SQL>
SQL> select empno,ename from emp where ename=&emp_name;
Enter value for emp_name: 'SCOTT'

     EMPNO ENAME
---------- ------------------------------
      7788 SCOTT

SQL>

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: SCOTT

     EMPNO ENAME
---------- ------------------------------
      7788 SCOTT

SQL>

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                                3000

SQL>

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>