第4章：运算符

1.算术运算符 

①

SELECT 100+10,100-35.5,100*2,100/2,100%30
FROM DUAL;

 ②在sql中“+”没有连接作用，表示加法运算，字符串转换为数值（隐式转换）。非数值看作0处理

SELECT 1001 + '1'
FROM DUAL;

SELECT 100 + 'a'
FROM DUAL;

 

 ③加法运算+NULL 结果是NULL

SELECT 100 + NULL
FROM DUAL;

 

【练习】

查询员工id是偶数的员工信息

select employee_id,last_name,salary
from employees
where employee_id % 2 = 0;

 2.比较运算符

2.1 算术运算符

 2.1.1 等于=

①字符串隐式转换。数值转换为数值，非数值转换为0

select 1 = 2,1 != 2,1 = 1,1 = '1',1 = 'a', 0 = 'a'
from DUAL;

② 字符串跟字符串比较是ANSI编码

select 'a' = 'a','a'='b'
from DUAL;

 ③有NULL参与的结果是NULL

select 1=NULL,NULL=NULL
from DUAL;

 ④查询commission_pct是NULL的结果集

where条件是NULL，结果是NULL。结果集得到结果是1的语句。

select last_name,salary
from employees
where commission_pct = null;

 2.1.2 安全等于<=> 为NULL而生

①没有NULL参与

select 1<=>1,1<=>'1',1<=>'a',0<=>'a'
from dual

②有NULL参与

select 1<=>NULL,NULL<=>NULL
from dual

③查询commission_pct是NULL的结果集

select last_name,salary,commission_pct
from employees
where commission_pct <=> null;

 

 2.2 比较运算符

 2.2.1. is null\is not null\isnull(exp)

①查询commission_pct为null的结果集

select last_name,commission_pct
from employees
where commission_pct is null
或
select last_name,commission_pct
from employees
where  isnull(commission_pct)

 ②查询commission_pct不为null的结果集

select last_name,commission_pct
from employees
where commission_pct is not nul
或
select last_name,commission_pct
from employees
where not commission_pct <=> null

 

【总结】

查询为空的用is null

查询不为空的用 is not null

2.2.2.least() \ greatest

①least()比较两个名字的asci码最小的

select first_name,last_name,least(first_name,last_name)
from employees

 2.2.3.between 条件1 and 条件2

①查询工资在6000到8000的员工信息

select first_name,salary
from employees
where salary between 6000 and 8000;
或
select first_name,salary
from employees
where salary >= 6000 and salary <= 8000;

②查询工资不在6000到8000的员工信息

select first_name,salary
from employees
where not salary between 6000 and 8000;
或
select first_name,salary
from employees
where salary < 6000 or salary > 8000;

 

2.2.4.in（set） \ not in (set)

①查询部门号是10，20，30的员工信息

select first_name,salary,department_id
from employees
where department_id=10 or  department_id=20 or  department_id=30
或
select first_name,salary,department_id
from employees
where department_id IN(10,20,30)

 

 ②查询工资不是6000，7000，8000的员工信息

select first_name,salary
from employees
where salary !=6000 and  salary !=7000 and salary!=8000
或
select first_name,salary
from employees
where salary not in (6000,7000,8000)

 

2.2.5 like ：模糊查询

%代表不确定的个数的字符（0个，1个，2个）

_代表不确定的字符

①查询first_name包含’a’员工信息 。

select first_name,salary
from employees
where first_name like '%a%'

 ②查询字符a开头的first_name的员工信息

select first_name,salary
from employees
where first_name like 'a%'

 ③查询first_name包含字符’a’且包含字符’s’的员工信息

select first_name,salary
from employees
where first_name like '%a%s%' or first_name like '%s%a%'
或
select first_name,salary
from employees
where first_name like '%a%' and  first_name like '%s%'

 ④查询第2个字符是’a’的员工信息

select first_name,salary
from employees
where first_name like '_a%'

 ⑤查询第2个字符是‘_‘第3个字符是’a’的员工信息。使用转义字符\

select first_name,salary
from employees
where first_name like '_\_a%'

 3.逻辑运算符，运算结果是1，0，null

 

XOR

a、b两个值不相同，则异或结果为1。如果a、b两个值相同，异或结果为0

【课后练习】

 

1.
select first_name,salary
from employees
where salary not between 5000 and 12000
或
select first_name,salary
from employees
where salary < 5000 or salary > 12000

2.
select first_name,department_id
from employees
where department_id= 20 or department_id=50
或
select first_name,department_id
from employees
where department_id in (20,50)

3.
select first_name,job_id
from employees
where manager_id is null
或
select first_name,job_id
from employees
where manager_id <=> null

4.
select first_name,salary,commission_pct
from employees
where commission_pct is not null
或
select first_name,salary,commission_pct
from employees
where not commission_pct  <=> null

5.
select first_name
from employees
where first_name like '__a%'

6.
select first_name
from employees
where first_name like '%a%' and first_name like '%k%'

7.
select first_name
from employees
where first_name like '%e'

8.
select first_name,job_id,department_id
from employees
where department_id between 80 and 100

9.
select first_name,salary,manager_id
from employees
where manager_id in (100,101,110)