【Oracle】第2天 条件查询

条件查询

语法:

SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME WHERE CONDITION;

应用：查询工资大于500的员工信息

SELECT * FROM EMP WHERE SAL>500;

→

返回了14条数据

SELECT EMPNO,ENAME,SAL S,JOB FROM EMP WHERE S=800--报错，标识符S无效

→条件里不要用别名

思考：
字符串可不可以比较？

可以比较

比如：

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE ENAME>'SMITH';--可执行且有结果

比较的依据是ASCII码

ASCII(N)--求字符N的ASCII码值

含有多个字符时，返回首个字符的ASCII码值

CHR(76)--求ASCII码值76对应的字符

日期可不可以比较？
可以

SELECT * FROM EMP WHERE HIREDATE='1980/12/17';--报错：类型不匹配

正解↓

SELECT * FROM EMP WHERE HIREDATE=TO_DATE('1980/12/17','YYYY/MM/DD');

注意:字符串要记得加单引号

日期的比较需要用到几个函数:

TO_DATE(日期字符串,日期格式):将输入的字符串形式的日期转换成日期格式的日期

日期格式：
年YYYY月MM日DD

应用：

SELECT TO_DATE('1980/12/17','YYYY/MM/DD') FROM DUAL;

←
将1980/12/17这个字符串转化为一个YYYY/MM/DD格式的日期

mysql
select DATE_FORMAT(SYSDATE(),‘%Y#%m#%d %H/%i/%s’) ;

SELECT * FROM EMP WHERE HIREDATE=TO_DATE('1980/12/17','YYYY/MM/DD');

←
找到雇用日期为1980年12月17日的员工

TO_CHAR(日期字符串,日期格式)

应用:
寻找雇用日期早于当前系统时间的员工→

SELECT * FROM EMP WHERE HIREDATE<TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD');

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY/MM/DD')='1980/12/17';

←
找到雇用日期为1980年12月17日的员工

SYSDATE:当前电脑的系统时间

日期格式按照系统格式去显示,系统设置在首选项里设置

包含查询

语法:

SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME WHERE COLUMN1 BETWEEN VALUE1 AND VALUE2;

VALUE1<VALUE2

BETWEEN A AND B:在A和B之间的数据(包含A 和B),多用于数据比较,[A,B]

应用:
查询工资在800到1500之间的员工信息
→

SELECT * FROM EMP WHERE SAL BETWEEN 800 AND 1500 ;

SELECT * FROM EMP WHERE ENAME BETWEEN 'S' AND 'W';--这样情况下也可以查询

→

空值判断

语法：

SELECT COLUMN1,COLUMN2.COLUMN3 FROM TABLE_NAME WHERE COLUMN1 IS NULL;

SELECT COLUMN1,COLUMN2.COLUMN3 FROM TABLE_NAME WHERE COLUMN1 IS NOT NULL;

如何判断一个列的值是不是空的？

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE SAL<>NULL;

↑这个方法可以吗？

注意：

• 空值判断比较恒不成立
• 空值参与运算恒为空
• 空值参与比较，条件恒不成立

SELECT * FROM EMP WHERE COMM IS NULL;

去空查询：

去空值函数

NVL(COLUMN1,VALUE1):判断COLUMN1一列是否为空，如果为空，将其值设为VALUE1,否则不动

NVL2(COLUMN1,VALUE1,VALUE2):判断COLUMN1一列是否为空，如果为空，将其值设为VALUE2,否则将其设置为VALUE1

去空查询：

SELECT COMM,NVL(COMM,0) FROM EMP;
SELECT COMM,NVL2(COMM,0,100) FROM EMP;

应用：
定义一个员工一年的全部所得叫做年薪，查询所有人的员工编号，工资，职位，员工姓名和年薪
→

SELECT EMPNO,ENAME,JOB,SAL,12*SAL+NVL(COMM,0) AS "年薪" FROM EMP;

↑此处要去空

布尔连接(and、or)

AND   OR

AND：用来连接多个条件的，当且仅当全部条件都满足时返回数据

语法：

SELECT COLUMN1,COLUMN2... 
FROM TANLE_NAME
WHERE CONDITION1...
AND CONDITION2
;

应用：
查询20号部门SMITH的其他信息
→

SELECT EMPNO,JOB,SAL,COMM,HIREDATE,MGR FROM EMP WHERE ENAME='SMITH' AND DEPTNO=20

应用：查询SAL>800且是30号员工部门的员工信息
→

SELECT * FROM EMP WHERE SAL>800 AND DEPTNO=30;

应用：查询年收入大于5000且工种是MANAGER 的员工姓名,月收入和部门编号，并以其中文显示
→

SELECT ENAME "员工姓名",SAL "工资",DEPTNO "部门编号" FROM EMP WHERE 12*SAL+NVL(COMM,0)>5000 AND JOB='MANAGER';

OR：用来连接多个条件的，当任一条件满足时返回数据

语法：

SELECT COLUMN1,COLUMN2... 
FROM TANLE_NAME
OR CONDITION1...
OR CONDITION2
;

应用：查询工资大于2000或者等于1500的员工的信息
→

SELECT * FROM EMP WHERE SAL>2000 OR SAL=1500;

查询工资大于2000或者部门编号等于20的员工信息
→

SELECT * FROM EMP WHERE SAL>2000 OR DEPTNO=20;

查询工种为MANAGER和CLERK的员工的姓名，工资，工作列信息
→

SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER' OR JOB='CLERK';

查询有奖金且薪金大于1250，或者无奖金且薪金小于200的员工员工编号，姓名，工资，工作列信息
→

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE SAL>1250 AND COMM IS NOT NULL OR SAL<2000 AND COMM IS NULL;

AND优先级高于OR

查询20号部门或者30号部门薪金大于1500且有奖金 或者 10或20号部门薪金小于2000且无奖金的员工员工编号，姓名，工资，工作列信息
→

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE  (DEPTNO=20 OR DEPTNO=30) AND SAL>1500 AND COMM IS NOT NULL OR (DEPTNO=10 OR DEPTNO=20) AND SAL<2000 AND COMM IS NULL;

IN查询

IN /NOT IN

语法1：SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME WHERE COLUMN1 IN(VALUE1);
语法2：SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME WHERE COLUMN1 NOT IN(VALUE1);
语法3：SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME WHERE (COLUMN1,COLUMN2) NOT IN((VALUE1,VALUE2),(VALUE3,VALUE4)...);

VALUE 只能是数字吗？

应用：查询工资为800、1250、1600的员工的员工编号、员工姓名、薪资、职业
→

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE SAL IN (800,1250,1600);

应用：查询职业为CLERK和MANAGER的员工的员工编号、员工姓名、薪资、职业
→

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE JOB IN ('CLERK','MANAGER');

注意：
用IN 查询时，要注意VALUE的数据类型一致

应用：查询20号部门的经理，30号部门的职员，10号部门的售货员的员工编号，员工姓名，薪资和职业
→

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE (DEPTNO,JOB) IN ((20,'MANAGER'),(30,'CLERK'),(10,'SALESMAN'));

注意：
多列IN 查询时，要和比较的列名一一对应并且数据类型要保持一致

ANY ALL查询

ANY：指任一个
ALL：指所有的

语法1：SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME WHERE COLUMN1 > ANY(VALUE1,VALUE2)
语法2：SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLE_NAME WHERE COLUMN1 > ALL(VALUE1,VALUE2)

应用：

SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE SAL= ANY(800,1250,1600,3000);
SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE SAL IN(800,1250,1600,3000);
SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE SAL=800 OR SAL=1250 OR SAL=1600 OR SAL=1600 OR SAL=3000;

←
查询工资为800或者1250或者1600或者3000的员工的员工编号，员工姓名，薪资，职业

查询员工工资大于800，5000,10000中任意一个数字的员工信息
→

SELECT * FROM EMP WHERE SAL>ANY(800,5000,10000);

查询员工工资全部都大于 800,5000,10000的员工信息
→

SELECT * FROM EMP WHERE SAL>ALL(800,5000,10000);

模糊查询

语法：

SELECT COLUMN1,COLUMN2 FROM TABLE_NAME WHERE COLUMN1 LIKE CONDITION;

通配符_：代表当前位置有1个任意字符
通配符%：代表当前位置有任意个任意字符

应用：查询所有员工中姓名以S为开头的员工的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE 'S%';

应用：查询姓名是以A和S开头的员工的 EMPNO, ENAME,JOB, HIREDATE列信息
→

SELECT EMPNO,ENAME,JOB,HIREDATE FROM EMP WHERE ENAME LIKE 'A%' OR ENAME LIKE 'S%';

有且仅有这一种表达,并且列名作比较时，一个列名匹配一个LIKE/一个条件匹配一个LIKE

应用：查询姓名包含 T H 的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '%T%H%' OR ENAME LIKE '%H%T%';

应用：查询姓名包含TH且TH相邻的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%HT%';

应用：查询姓名包含AD且AD不相邻的员工信息
→

SELECT * FROM EMP WHERE (ENAME LIKE '%A%D%' OR ENAME LIKE '%D%A%') AND ENAME NOT LIKE '%AD%' AND ENAME NOT LIKE '%DA%';

应用：查询姓名长度为5，且倒数第二位为T的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '___T_';

查询姓名长度为5，且倒数第二位不为T的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '_____' AND ENAME NOT LIKE '___T_';

应用：查询姓名长度不为5，且倒数第二位不为Ｔ的员工信息
→

SELECT * FROM EMP WHERE ENAME NOT LIKE '_____' AND ENAME NOT LIKE '%T_';

应用：查询姓名第二三位为LA但是长度不为6或者倒数第二三位为LA但是长度为6的员工信息
→

SELECT * FROM EMP WHERE ENAME NOT LIKE '______' AND ENAME  LIKE '_LA%' OR ENAME LIKE '______' AND ENAME LIKE '&LA_';

应用：查询姓名第二五位为MH但是长度为5或者倒数第一三位为NL但是长度不为6的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '_____' AND ENAME LIKE '_M__H%' OR ENAME NOT LIKE '______' AND ENAME LIKE '%L_N';

注意：
汉字可不可以用模糊查询？
可以

应用;查询姓张的学生信息
→

SELECT * FROM EMP WHERE ENAME LIKE '张%';

汉字使用规则和字符有区别吗？
没有

如果比较的列有特殊字符怎么办？

转义字符/

语法：

SELECT COLUMN1,COLUMN2 FROM TABLE_NAME LIKE 'xx/%%' ESCAPE '/';

ESCAPE '/':声明反斜杠后面的一个字符为普通字符，去除其本身特定的含义

应用：查询姓名中含有％的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '%/%%' ESCAPE '/';

应用：查询姓名中含有%且含有S,T的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '%S%' AND ENAME LIKE '%T%' AND ENAME LIKE '%/%%' ESCAPE '/';

转义字符可以是其他的字符但是不建议使用

应用：查询姓名中既含有%又含有_的员工信息
→

SELECT * FROM EMP WHERE ENAME LIKE '%/%%' ESCAPE '/' AND ENAME LIKE '%/_%' ESCAPE '/';

注意：
1、如果要转义，就要一个条件搭配一个ESCAPE，否则报错
2、转义字符，只在当前条件中有效

应用:查找emp2表中 ename列同时有%和_，且不挨在一起的数据；
→

SELECT * FROM EMP2 
WHERE ENAME LIKE '%/%%' ESCAPE '/' 
AND ENAME LIKE '%/_%' ESCAPE '/' 
AND NOT LIKE '%/%/_%' ESCAPE '/' 
AND NOT LIKE '%/_/%%' ESCAPE '/'; 

PS:
逻辑运算符 :

>  <   >=   <=  =   <>/!=

** 习题：
查询佣金和工资一样高的员工信息
→

SELECT * FROM EMP WHERE SAL = COMM;

SELECT  * FROM EMP E WHERE SAL =(SELECT COMM FROM EMP WHERE ENAME = E.ENAME);