MySql02 算术运算符与比较运算符、逻辑运算符、位运算符

#day-02 算术运算符与比较运算符

#1.算术运算符：+ - * div % mod
SELECT 100,100 + 0,100 - 0,100 + 50,100 + 50 * 30,100 + 32.2,100 - 32.1
FROM DUAL;

#在sql中 + 没有连接的作用，就表示加法运算。此时，会将字符串转换为数值(隐式转换)
SELECT 100 + '2' #在java中，结果是 1002
FROM DUAL;

SELECT 100 + 'a' #此时将'a'看作0处理
FROM DUAL;

SELECT 100 + NULL #null值参与运算，结果为null
FROM DUAL;

SELECT 100,100 * 1,100 * 1.0,100 / 1.0,100 / 2, #做除法，结果自动保留小数位
100 + 2 * 5 / 2,100 / 3,100 DIV 0 #分母如果为0，结果为null
FROM DUAL;

#取模运算：% mod
SELECT 12 % 3,12 % 5,12 MOD -5,-12 % 5,-12 % -5
FROM DUAL; #结果的符号与被模数相同，与模数无关

#查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;

 

#2.比较运算符
#2.1 = <=> <= >= <> != < >
SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a' #字符串存在隐式转换，如果转换数值不成功，则看作0
FROM DUAL;

SELECT 'a' = 'a','ab' = 'ab','a' = 'b' #如果=两边都是字符串，则MySQL会按照字符串的ASCII码进行比较
FROM DUAL;

SELECT 1 = NULL,NULL = NULL #在=中，只要有null参与判断，值为null
FROM DUAL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct = NULL;#此时执行，不会有任何结果

#安全等于<=> 与=的区别在于可以对null进行判断，两个操作数均为null时，其返回值为1
#当一个操作数为null是其返回值为0
SELECT 1 <=> 2,1 <=> '1',1 <=> 'a',0 <=> 'a'
FROM DUAL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

SELECT 3 <> 2,'2' <> NULL,'' != NULL
FROM DUAL;

#2.2 关键字
#·1 IS NULL\ISNULL(),IS NOT NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct );

SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

SELECT last_name,salary,commission_pct
FROM employees
#WHERE commission_pct <=> NULL;
WHERE NOT commission_pct <=> NULL;

#·2 LEAST() \ GREATEST()
SELECT LEAST('a','d','c'),GREATEST('sa','dsa','derr')
FROM DUAL;

SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

#·3 between ... and ...
#查询工资在6000到8000的员工信息,左闭右闭,[左为下界，右为上界]
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
#where salary >= 6000 && salary <= 8000;
#查询工资不在在6000到8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;

#·4 in(set)\not in(set)
#查询部门为10，20，30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20,department_id = 30;
WHERE department_id IN(10,20,30);

#查询工资不是6000，8000，9000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN(6000,8000,9000);

#·5 like:模糊查询
#%代表(0或1或多个)不确定字符
#查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#查询以a开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';
#查询包含a且包含e的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#下划线表示一个字符 _
#查询第二个字符是a的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';

#查询第二个字符是下划线且第三个字符是u的员工信息
#需要使用转义字符 \
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_u%';

#或者,使用escape定义转义字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_u%' ESCAPE '$';

#·6 正则表达式 REGEXP \ RLIKE
#了解

 

#3.逻辑运算符 OR || AND && NOT ! XOR
SELECT last_name,salary
FROM employees
WHERE salary > 8000 AND salary <10000 OR last_name NOT LIKE '%a%';

SELECT last_name,phone_number,commission_pct
FROM employees
WHERE department_id != 20 && commission_pct <=> NULL;

#XOR 异或，只满足一个就行
SELECT last_name,phone_number,commission_pct
FROM employees
WHERE department_id != 20 XOR commission_pct <=> NULL;

 

#4.位运算符 & | ^ ~ >> <<
#按位与& 、按位或 | 、按位取反 ^ 、左移右移>> <<