运算的使用

运算符

算数运算符（+ - * / % mod）

SELECT 100, 100+10,100-10,100/5,100*5.0,100%2
 FROM DUAL;
 
 # 在mysql中 + 没有链接的作用，会将字符串隐式转换为数值
 SELECT 100 + '1'
 FROM DUAL;
 
 # 'a' 当做0
 SELECT 100 + 'a'
 FROM DUAL;
 
 # 分母为零，结果为null
 SELECT 100 / 0 FROM DUAL;
 
 # 取模运算 %/Mod
 SELECT 10 % 3, 10% -3,-10% 3,-10% -3 FROM DUAL
 
  #查询员工 工资为偶数的员工
 SELECT employee_id,last_name,salary 
 FROM employees
 WHERE salary%2 = 0;

比较运算符

#比较运算符
#2.1 = <=> <> != < <= > >=

# =：等于
#字符串隐式转换，转换不了数值，则为零
SELECT 1 = 2, 3 != 4,1 = '1',0 = 'a'
FROM DUAL;
#当两边 为字符串时按照ansi 比较
SELECT '1' = '1', 'a' = 'b', 'ab' = 'ab'
FROM DUAL;

#只要有null参与的判断，结果就为null
SELECT 1 = NULL, null = NULL, 'a' = NULL
FROM DUAL;

SELECT last_name, salary
FROM employees
-- WHERE salary = 6000;
where commission_pct = null;#此时执行没有结果

#<=>:安全等于，可以对null 进行判断 ，为null而生

SELECT last_name, salary
FROM employees
-- WHERE salary = 6000;
where commission_pct <=> null;

# <>/!= :不等于，< <= > >=
SELECT 1<>2,2 != '2',2 <>NULL
FROM DUAL;

SELECT 1>=2,2 <= '2',2 < NULL
FROM DUAL;

#2.1关键字
# 1.  IS NULL / IS NOT NULL / ISNULL
#查询表中为 NULL的数据
SELECT last_name, salary
FROM employees
-- WHERE salary = 6000;
where commission_pct IS null;

#查询表中不为的数据
SELECT last_name, salary
FROM employees
-- WHERE salary = 6000;
where commission_pct IS NOT NULL;
#或
SELECT last_name, salary
FROM employees
-- WHERE salary = 6000;
where NOT commission_pct <=> null;

SELECT last_name, salary
FROM employees
-- WHERE salary = 6000;
where ISNULL(commission_pct);
#2. LEAST / GREATEST 
SELECT LEAST('g','a','f'),GREATEST('g','a','f')
FROM DUAL;

#3. BETWEEN(条件一)AND（条件二）查询条件一到条件2的范围，包含边界
SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
#错误
-- WHERE salary BETWEEN 8000 AND 6000;

SELECT employee_id, last_name, salary
FROM employees
WHERE NOT salary BETWEEN 6000 AND 8000;

# 4. IN（set） \ NOT IN (set)

#查询部门为 10,20,30的员工信息
SELECT department_id, last_name,salary
FROM employees
-- where department_id = 10 or department_id = 20 or department_id =30;
WHERE department_id IN(10,20,30);

#查询工资不是 6000,7000,8000的
SELECT employee_id, last_name, salary
FROM employees
WHERE salary NOT in(6000,7000,8000);

#5. LIKE 模糊查询

#查询包含字符‘a’的员工信息 
SELECT last_name
FROM employees
WHERE last_name LIKE 'a';#同 last_name = ‘a’ 一致

# '%' 代表不定数量的字符
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%'

SELECT last_name
FROM employees
WHERE last_name LIKE 'a%'#查询以‘a’开头的员工信息

# _ 代表一个不确定的字符 位

#查询第二个字符为‘a’ 的员工信息 
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%'

-- #查询第二个字符是_ , 且第三个字符是a
-- #需要使用转义字符 \

SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%'

SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';

#6. REGEXP \ RLIKE :正则表达式

逻辑运算符

-- 逻辑运算符 ：
SELECT 
  not 1 as result_not,
  0 AND 0 as result_and,
  0 OR 1 as result_or,
  1 XOR 0 as result_xor;
	from DUAL

)

逻辑运算符

-- 逻辑运算符 ：
SELECT 
  not 1 as result_not,
  0 AND 0 as result_and,
  0 OR 1 as result_or,
  1 XOR 0 as result_xor;
	from DUAL