网络知识 科技 SQL之WHERE子句中常用的运算符

SQL之WHERE子句中常用的运算符

{"data":{"title":"SQL之WHERE子句中常用的运算符","abstract":"【四】WHERE子句中常用的运算符4.","articleType":"article","itemId":"7112401467796881961","groupId":"7112401467796881961","groupSource":2,"isOriginal":true,"banComment":false,"publishTime":"2022-06-24 08:05","source":"浪花小子啊","tag":"technique","mediaSite":null,"pathname":"/article/7112401467796881961/","loginUserInfo":null,"favorite":false,"relation":{"isFollowing":false,"isFollowed":false},"likeData":{"userLikeStatus":0,"count":3},"isSelf":false,"content":"

【四】WHERE子句中常用的运算符

4.1 运算符及优先级:

算数运算符

*,/,+,-,

逻辑运算符

not, and ,or 可以通过()改变运算优先级

比较运算符

1)单值比较运算 =, >, >=, <, <=, <>,between and

2)多值比较运算 >any, >all, <any, <all, in, not in

3)模糊比较 like(配合“%”和“_”)

4)特殊比较 is null

SQL>select ename, job, sal ,comm from emp where job='SALESMAN' OR job='PRESIDENT' AND sal> 1500;

注意:条件子句使用比较运算符比较两个选项,重要的是要理解这两个选项的数据类型。必须得一致,所以这里常用显性转换。

数值型、日期型、字符型都可以与同类型比较大小,但数值和日期比的是数值的大小,而字符比的是acsii码的大小

试比较下面语句,结果为什么不同

SQL> select * from emp where hiredate>to_date('1981-02-21','yyyy-mm-dd');

SQL> select * from emp where to_char(hiredate,'dd-mon-rr')>'21-feb-81';

4.2 常用谓词

4.2.1 用BETWEEN AND操作符来查询出在某一范围内的行.

SQL> SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 1500;

between 低值 and 高值,包括低值和高值。

4.2.2 模糊查询及其通配符:

在where子句中使用like谓词,常使用特殊符号"%"或"_"匹配查找内容,也可使用escape可以取消特殊符号的作用。

SQL>

create table test (name char(10));

insert into test values ('sFdL');

insert into test values ('AEdLHH');

insert into test values ('A%dMH');

commit;

SQL> select * from test;

NAME

----------

sFdL

AEdLHH

A%dMH

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

NAME

----------

A%dMH

4.2.3' '和" "的用法:

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

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

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

EMPNO||'ISSCOTT''SEMPNO'

--------------------------------------------------------

7788 is Scott's empno

4.2.4 交互输入变量符&和&&的用途:

①使用&交互输入

SQL> select empno,ename from emp where empno=&empnumber;

输入 empnumber 的值: 7788

&后面是字符型的,注意单引号问题,可以有两种写法:

SQL> select empno,ename from emp where ename='&emp_name';

输入 emp_name 的值: SCOTT

SQL> select empno,ename from emp where ename=&emp_name;

输入 emp_name 的值: 'SCOTT'

②使用&&可以在当前session下将&保存为变量

作用是使后面的相同的&不再提问,自动取代。

SQL> select empno,ename,&&salary from emp where deptno=10 order by &salary;

输入 salary 的值: sal

&&salary的意思就是把salary的值在当前session存储,使用define可以查看salary变量,如果想删除使用undefine salary。

&&salary和&的提示是按所在位置从左至右边扫描,&&salary写在左边(首位),&salary(第二位)

③define(定义变量)和undefine命令(解除变量)

SQL> define --显示当前已经定义的变量(包括默认值)

set define on|off可以打开和关闭&。

SQL> define emp_num=7788 --定义变量emp_num

SQL>select empno,ename,sal from emp where empno=&emp_num;

SQL>undefine emp_num --取消变量

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

④Accept接收一个变量

类似define功能,但通常和&配合使用

SQL> accept lowdate prompt 'Please enter the low date range ("MM/DD/YYYY"):';

SQL> accept highdate prompt 'Please enter the highdate range ("MM/DD/YYYY"):';

SQL> select ename||','||job as EMPLOYEES, hiredate from emp

where hiredate between to_date('&lowdate','MM/DD/YYYY') and to_date('&highdate','MM/DD/YYYY');

4.2.5 使用逻辑操作符: AND; OR; NOT

AND 两个条件都为TRUE ,则返回TRUE

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

OR 两个条件中任何一个为TRUE,则返回TRUE

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

NOT 如果条件为FALSE,返回TRUE

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

4.2.6什么是伪列

简单理解它是表中的列,但不是你创建的。Oracle数据库有两个著名的伪列rowid和rownum

ROWID的含义:rowid 可以说是物理存在的,表示记录在表空间中的唯一位置ID,所以表的每一行都有一个独一无二的物理门牌号。

ROWNUM的含义:rownum是对结果集增加的一个伪列,即先查到结果集,当输出时才加上去的一个编号。rownum必须包含1才有值输出。

小伙伴们,一起来试试:

SQL> select rowid,rownum,ename from emp;

SQL> select * from emp where rowid='AAARZ+AAEAAAAG0AAH';

SQL> select * from emp where rownum<=3;

the end !!!

@jackman 共筑美好!

","imageList":[],"mediaInfo":{"userId":"MS4wLjABAAAA78AUiwdRGw3bJs7anMyeQ8FsBznBIKEEti87fozm-jA","unsafeUserId":"6744409510","name":"浪花小子啊","avatarUrl":"https://p26.toutiaoimg.com/large/pgc-image/2575ffd28ed24bbd837da026c249a5ae","description":"来自二线城市工资一线城市房价的打工仔一名","userVerified":1,"userAuthInfo":""},"seoTDK":{"title":"SQL之WHERE子句中常用的运算符-今日头条","description":"【四】WHERE子句中常用的运算符4.","keywords":"SQL","publishTimestamp":"1656029100","modifiedTimestamp":"1656045152"},"logId":"20220624133233010198060161155C27BB","sylpageConfig":{"card":{"id":""}},"identity":{"web_id":"7111199547455817229","user_is_login":false},"abtestInfo":{"rsp_type":5,"version_name":"4252807,4164637","parameters":{"feat_repost_type":{"new":true},"home_nav_conf":{"dcd_out":1},"local_filter":{"core_filter":{"filter_list":{"ms::TicaiFilter":true}}},"page_upgrade":{"new_profile":true,"video_double_column":true},"sati":{"enable_ad_prime":true,"enable_sorter_optimus":true,"prime_rule_rank_version":"toutiao_web","use_toutiao_web_feed":true,"format_max_consecutive_middle":2,"format_max_consecutive_nogroups":3,"enable_reduce_nogroup":true},"seraph":{"score_rule":{"default":{"replace":{"group_util":"_CTR - 1000*dislike"}},"new_user":{"replace":{"group_util":"_CTR - 1000*dislike"}}}},"sort":{"allowed_ticai":["forum_post","pgc_text","pgc_video"]},"video_detail_page_upgrade":{"new_page":true}},"env_flag":0},"localCityInfo":{"name":"北京","code":"110000","channelId":3202164529},"showResearch":false}}

返回顶部