MySQL学习之运算符学习

#第04章 运算符

#1.算数运算符 + - * / div % mod 
SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
FROM DUAL;
#在java语言中，结果是：1001.在SQL中，+没有连接的作用，就表示加法运算。
#此时会把字符串转换为数值（隐式转换）
SELECT 100+'1'
FROM DUAL;

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

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

#取模运算 % mod
SELECT 12 % 5,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'#两边都是字符串，则按照ANSI的比较规则进行比较
FROM DUAL;

SELECT 1=NULL,NULL=NULL #只要有null参与比较，结果就为null
FROM DUAL;

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

#<=>:安全等于 为NULL而生 
SELECT 1 <=> 2,1 <=> 2,1 <=> '1',1 <=> 'a',0 <=> 'a'
FROM DUAL;

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

#练习：查询表中commission_pct为null的数据 
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct<=>NULL;

SELECT 3<>2,'4'<>NULL,''<>NULL,NULL<>NULL
FROM DUAL;

#2.2
#①IS NULL\ IS NOT NULL \ ISNULL
#练习：查询表中commission_pct为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);


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

#②LEAST()\GREATEST()
SELECT LEAST('b','g','m','n'),GREATEST('b','g','m','n')
FROM DUAL;


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

#③ BETWEENt 条件下界1 AND条件上界2 查询条件1和条件2之间的数据，包含边界
#练习：查询工资在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 BETWEEN 8000 AND 6000;

#练习：查询工资不在6000-8000之间的员工信息
SELECT employee_id,last_name,salary 
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#where salary>8000 or salary<6000

#IN(set)\ONT IN(set)

#练习：查询部门为10、20、30部门的员工信息
SELECT last_name,salary,department_id
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 last_name,salary,department_id
FROM employees
WHERE salary NOT IN(6000,7000,8000);

#⑤LIKE：模糊查询
#%:代表不确定个数的字符（0个，1个，或多个）
#练习：查询last_name中包含字符‘a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#练习：查询last_name中以字符‘a’开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';

#练习：查询last_name中包含字符‘a’且包含字符‘e’的员工信息
#写法一：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法二：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

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

#练习：查询第2个字符是‘a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';

#练习：查询第2个字符是_且第3个字符是‘a’的员工信息
#需要使用转义字符：\
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
#或者(了解) ESCAPE是告诉哪一个是转义字符
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%'ESCAPE '$';

#⑥REGEXP\RLIKE:正则表达式
SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;

SELECT 'atguigu'REGEXP 'gu.gu','atguigu'REGEXP '[ab]'#是否包含 gu未知gu 结构  是否包含a或者包含b
FROM DUAL;

#3.逻辑运算符：OR || \ AND && \ NOT ! \XOR

#OR AND 
SELECT last_name,salary,department_id
FROM employees
#where department_id=10 or department_id=20
WHERE department_id=50 AND salary>6000;

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

#XOR 追求的"异"
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=50 XOR salary>6000;

#注意：AND的优先级高于OR

#4.位运算符：& | ^ >> <<
SELECT 12 & 5, 12 | 5,12 ^ 5 
FROM DUAL;

SELECT 10 & ~1
FROM DUAL;


#在一定范围内，向左移动一位，相当于乘以2，向右移动一位，相当于除以2
SELECT 1 >> 2, 4 >> 2
FROM DUAL;

# 运算符练习
# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
#where salary not between 5000 and 12000;
WHERE salary<5000 OR salary>12000;

# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,department_id
FROM employees
#where department_id=20 or department_id=50;
WHERE department_id IN(20,50);

# 3.选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id
FROM employees
#where manager_id <=> null;
WHERE manager_id IS NULL;

# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
#where  commission_pct is NOT null;
WHERE NOT commission_pct <=>NULL;
# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT last_name 
FROM employees
WHERE last_name LIKE '__a%';

# 6.选择姓名中有字母a和k的员工姓名
SELECT last_name 
FROM employees
#where last_name like '%a%' and last_name LIKE '%k%';
WHERE last_name LIKE '%k%a%' OR last_name LIKE '%a%k%';

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE '%e';

SELECT first_name
FROM employees
WHERE first_name REGEXP 'e$';# '^e'：以e开头

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name,job_id,department_id
FROM employees
#方式一：推荐
#where department_id between 80 and 100;
#方式二：推荐，与方式一相同
WHERE department_id>=80 AND department_id<=100;
#方式三：仅使用于本题的方式
WHERE department_id IN (80,90,100);

# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN(100,101,110);