day2｜第04章 运算符｜2022-11-13

day2｜120天搞定mysql｜2022-11-13

第04章 运算符

1. 算术运算符： +、-、*、\、div、%、mod
mysql> select 100, 100+0, 100-1, 100-1.5, 10*2 from dual;

# 在sql中，+号没有连接的作用，就只表示加法运算。此时会将字符串转为数值（隐式转换）
select 100 + '1' from dual;	# 在某些编程语言中，结果是1001.
select 100 + 'a' from dual; # 此时a看作0处理。
select 100 + null from dual; # null参与运算，结果为null。
select 100/1.0, 100/2, 100div0 from dual;	# 分母如果为0，则结果为null

# 取模运算： % mod
mysql> select 12%5, 12 mod 3, 12 mod -5, -12 mod 5, -12 mod -5  from dual; # 结果的正负取决于模数。例如这里的12。

# 练习：查询员工id为偶数的员工信息
mysql> select employee_id, last_name, salary from employees where employee_id % 2 = 0;


2. 比较运算符
# 2.1  =、<=>、<>、!=、<、<=、>、>=
mysql> select 1=2, 1!=2, 1 = '1', 1 = 'a', 0 = 'a', 'a' = 'b' from dual;#字符串存在隐式转换。如果转换数值不成功，则看做0

mysql> select 'a' = 'a', 'ab' = 'ab', 'a' = 'b' from dual; #两边都是字符串的话，则按照ANSI的比较规则进行比较。
mysql> select 1 = null, null = null from dual;# 只要有null参与判断，结果就为null
mysql> select last_name, salary, commission_pct from employees where commission_pct = null; #此时执行，不会有任何的结果

# <=> ：安全等于。 记忆技巧：为NULL而生。
mysql> select 1 <=> null, null <=> null;
#练习：查询表中commission_pct为null的数据有哪些
mysql> select last_name, salary, commission_pct from employees where commission_pct <=>
null;

# 2.2 
#① IS NULL \ IS NOT NULL \ ISNULL
#练习：查询表中commission_pct为null的数据有哪些
mysql> select last_name, salary, commission_pct from employees where commission_pct is null;
# 或者
mysql> select last_name, salary, commission_pct from employees where isnull(commission_pct);

#练习：查询表中commission_pct不为null的数据有哪些
mysql> select last_name, salary, commission_pct from employees where commission_pct is not null;
# 或者
mysql> select last_name, salary, commission_pct from employees where not commission_pct <=> null;

#② LEAST() \ GREATEST 
mysql> select least(1,2,3,4,5), least('a','b','c'), greatest(1,2,3) from dual;
mysql> select least(first_name, last_name), greatest(length(first_name), length(last_name)) from  employees;

#③ BETWEEN 条件下界1 AND 条件上界2  （查询条件1和条件2范围内的数据，包含边界）
#查询工资在6000 到 8000的员工信息
mysql> select employee_id, last_name, salary from employees where salary >= 6000 && salary <= 8000;
mysql> select employee_id, last_name, salary from employees where salary between 6000 and 8000;

#查询工资不在6000 到 8000的员工信息
mysql> select employee_id, last_name, salary from employees where salary not between 6000 and 8000;
#where salary < 6000 or salary > 8000;

#④ in (set)\ not in (set)
#练习：查询工资不是6000,7000,8000的员工信息
mysql> select last_name, salary, department_id from employees where salary not in (6000,7000,8000);

#⑤ LIKE :模糊查询
# % : 代表不确定个数的字符 （0个，1个，或多个）
#练习：查询last_name中包含字符'a'的员工信息
mysql> select last_name from employees where last_name like '%a%';
#练习：查询last_name中以字符'a'开头的员工信息
mysql> select last_name from employees where last_name like 'a%';
#练习：查询last_name中包含字符'a'且包含字符'e'的员工信息
#写法1：
mysql> select last_name from employees where last_name like '%a%' and last_name like '%e%';
#写法2：
mysql> select last_name from employees where last_name like '%a%e%' or last_name like '%e%a%';

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

#练习：查询第3个字符是'a'的员工信息
mysql> select last_name from employees where last_name like '__a%';
#练习：查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \ 
mysql> select last_name from employees where last_name like '_\_a%';

#⑥ REGEXP \ RLIKE :正则表达式
mysql> select 'shkstart' regexp '^shk', 'shkstart' regexp 't$', 'shkstart' regexp 'hk' from dual;
mysql> select 'atguigu' regexp 'gu.gu', 'atguigu' regexp '[ab]' from dual;

#3. 逻辑运算符： OR ||  AND && NOT ! XOR
# or  and 
mysql> select last_name, salary, department_id from employees where department_id = 10 or department_id = 20;
mysql> select last_name, salary, department_id from employees where department_id = 50 and salary > 6000;
# not 
mysql> select last_name, salary, department_id from employees where department_id = 50 and salary > 6000;
mysql> select last_name, salary, department_id from employees where commission_pct is not null;
mysql> select last_name, salary, department_id from employees where not commission_pct <=> null;
# XOR :追求的"异"
mysql> select last_name, salary, department_id from employees where department_id = 50 xor salary > 6000; #注意：AND的优先级高于OR

#4. 位运算符： & |  ^  ~  >>   <<
mysql> select 12 & 5, 12 | 5, 12 ^5 from dual;
mysql> select 10&~1 from dual;
#在一定范围内满足：每向左移动1位，相当于乘以2；每向右移动一位，相当于除以2。
mysql> select 4 << 1, 8 >> 1 from dual;

第04章 课后练习题

# 第04章_运算符课后练习

# 1.选择工资不在5000到12000的员工的姓名和工资
mysql> select last_name, salary from employees where salary not between 5000 and 12000;
mysql> select last_name, salary from employees where salary < 5000 or salary > 12000;

# 2.选择在20或50号部门工作的员工姓名和部门号
mysql> select last_name, department_id from employees where department_id in (20, 50);
mysql> select last_name, department_id from employees where department_id = 20 or department_id = 50;

# 3.选择公司中没有管理者的员工姓名及job_id
mysql> select last_name, job_id, manager_id from employees where manager_id is null;
mysql> select last_name, job_id, manager_id from employees where manager_id <=> null;

# 4.选择公司中有奖金的员工姓名，工资和奖金级别
mysql> select last_name, salary, commission_pct from employees where commission_pct is not null;
mysql> select last_name, salary, commission_pct from employees where not commission_pct <=> null;

# 5.选择员工姓名的第三个字母是a的员工姓名
mysql> select last_name from employees where last_name like '__a%';

# 6.选择姓名中有字母a和k的员工姓名
mysql> select last_name from employees where last_name like '%a%k%' or last_name like '%k%a%';
mysql> select last_name from employees where last_name like '%a%' and last_name like "%k%";

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
mysql> select first_name, last_name from employees where first_name like '%e';
mysql> select first_name, last_name from employees where first_name regexp 'e$'; # 以e开头的写法：'^e'

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
mysql> select last_name, job_id from employees where department_id between 80 and 100;
mysql> select last_name, job_id from employees where department_id >=80 and department_id <= 100;

# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
mysql> select last_name, salary, manager_id from employees where manager_id in (100,101,110);