MySQL运算符的使用

#算数运算符: + - * (/ div) (% mod)
SELECT 100,100+0,100-0,100+50,100+50*30,100+35.5,100-35.5
FROM DUAL;#伪表

#在sql中，+ 没有连接的作用，就表示加法运算。此时，会将字符串转换为数值（隐式转换）
SELECT 100+'1' # 在java语言中，结果是：1001。 
FROM DUAL;  #结果是101

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

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

#100/2的结果为浮点型 因为mysql默认大多数情况下除不尽
#100 div 结果为null 分母不能为0 不然结果为null
SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 /2,
 100+2*5/2, 100 /3,100 DIV 0
FROM DUAL;
# 取模运算 (% mod)
SELECT 12%3,12%5,12 MOD -5,-12%5,-12%-5#
FROM DUAL;
#结果为 0 2 2 -2 -2 结论被模数的符号和结果的符号一致的

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

#比较运算符
# = <=> <> !=  < <= > >=
SELECT 1 = 2, 1!=2,1='1',1='a',0='a'
FROM DUAL;
#false为0 true为1  字符串转换为数值（隐式转换）如果字符串无法转换为数值 那么当作0

SELECT 'a'='a','ab'='ab','a'='b'
FROM DUAL;
#结果为 1 1 0 单纯是字符串之间的比较就不会隐式转换为数值，至是单纯的ANSI的比较规则进行比较

SELECT 1=NULL,NULL=NULL#只要有null参与运算那么结果就为null
FROM DUAL

SELECT last_name,salary,commission_pct
FROM employees
#where salary=6000;
WHERE commission_pct=NULL;#此时执行不会有任何结果 因为有null参与运算

# <=>: 安全等于 可以用来对null进行判断 为null而生
SELECT 1<=>2,1=2,1<=>'1',1<=>'a',0<=>'a'
FROM DUAL;
#结果 0 1 0 1 

SELECT 1<=>NULL,NULL<=>NULL
FROM DUAL;
#结果 0 1

#练习:查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct<=>NULL;
#结果 commission_pct为null的数据

 #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('g','b','t','m'),GREATEST('g','b','t','m')
FROM  DUAL;
#查询最小的名字 字典序                                   长度最短的姓名    
SELECT LEAST(first_name,Last_name),last_name,first_name,LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;
#查询最大的名字 字典序                                   长度最长的姓名   
SELECT GREATEST(first_name,Last_name),last_name,first_name,GREATEST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

#BETWEEN ... AND ....(查询条件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;
#where salary < 6000 or salary >8000;
#不在6000到8000的数据
WHERE salary NOT BETWEEN 6000 AND 8000; #NOT 非 表示不在6000和8000之间
#where salary>8000 or salary<6000;#OR 或

#IN(set) \ NOT IN (set)
#练习查询：查询部门为10，20，30部门员工的信息
SELECT department_id,first_name,last_name
FROM employees
#where department_id=10||department_id=20||department_id=30;
#where department_id=10 or 20 or 30 #错误写法  非0就是1 会得到所有数据 比较像java里的||逻辑运算符结果只能是true 或者 false
#WHERE department_id=10 OR department_id=20 OR department_id=30#必须把条件写完整 满足条件就是1 不满足就是0 过滤
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);

#LIKE：模糊查询
#练习:查询last_name中包含字符'a'的员工信息
# % :代表不确定个数的字符 (0个，1个,或者多个)
SELECT last_name
FROM employees
WHERE last_name LIKE('%a%');
#去除前面的百分号 意味着插叙的是首字母为a(不分大小写)的last_name
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%') && last_name LIKE('%e%');
#写法二
SELECT last_name
FROM employees
WHERE last_name LIKE('%a%e%') OR last_name LIKE('%e%a%');#或

SELECT last_name
FROM employees
WHERE last_name LIKE('%a%e%') || last_name LIKE('%e%a%');

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

#查询第三个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE'__a%';

#查询第二个字符是_且第三个字符是'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 :正则表达式
# 	^ 以什么开头 		以什么结尾 $   		是否包含zx
SELECT 'zxend' REGEXP '^z','zxend' REGEXP 'd$','zxend' REGEXP 'zx'
FROM DUAL;#结果为 1 1 1
# .代表任意一个字符        [ab]包含a或者包含b
SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'
FROM DUAL;
# 逻辑运算符：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=10 and department_id=20;#且 &&
WHERE department_id AND salary>6000;

#NOT
SELECT last_name,department_id,salary
FROM employees
#where salary not between 6000 and 8000;
#where  commission_pct is not null;
#WHERE  commission_pct IS NULL;
WHERE commission_pct <=>NULL;

#XOP: 追求的就是'异'
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=50 XOR salary > 6000;

/*OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先
对AND两边的操作数进行操作，再与OR中的操作数结合。*/

#位运算符 & | ^  ~ >> <<
# 二进制且  二进制或 二进制异或
 SELECT 12&5,12|5,12^5
 FROM DUAL; 
#结果 4    13    9 
# 	 ~二进制取反0变1 1变0
SELECT 10& ~1
#结果 10
FROM DUAL;
#在一定范围内满足：每向左移到1位相当于乘以2：每向右移动一位，想当于除以2
SELECT 4<<1,8>>1
FROM DUAL;
#课后练习
# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary <5000 OR salary>12000;
# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE department_id=20 OR department_id=50;
# 3.选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id
FROM employees
WHERE manager_id<=>NULL;
# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
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%k%') OR last_name LIKE('%k%a%');
# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT first_name,salary
FROM employees
WHERE first_name LIKE('%e');
# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id>=80 AND department_id<=100;
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id =100 OR manager_id =101 OR manager_id=110;