MySQL 第四章-运算符-课后练习

#1、算数运算符：+ - * /

SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 
FROM DUAL;

#在SQL中，+没有连接的作用，就表示加法运算。此时会将字符串转换为数值（隐形转换）

SELECT 100 + "1" 
FROM DUAL;
SELECT 100 + 'a' //#此时将'a'看做0处理
FROM DUAL;

#null值参与运算，结果为null

SELECT 100 + NULL
FROM DUAL;

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;

#练习：查询员工id为偶数的员工信息
SELECT * 
FROM employees
WHERE employee_id MOD 2 = 0;

#2、比较运算符
#2.1 = <=> <> ! = < <= > >=
#字符串存在隐式转换。如果转换数值不成功，则看做 0

SELECT 1=2,1!=2,1='a',0='a'  
FROM DUAL;

#两边都是字符串的话，则按照ANSI的比较规则

SELECT 'a' = 'b' , 'ab' = 'ba' , 'a' = 'b' 
FROM DUAL;

#只要有null参与比较，结果就为null

SELECT 1 = NULL,NULL=NULL  
FROM DUAL;

SELECT last_name,salary,commission_pct
FROM employees;
//where salary = 6000;
WHERE commission_pct = NULL; 

#此时执行，不会有任何的结果
#<=> ; 安全等于 记忆技巧 为null而生

SELECT 1<=>2,1<=>2,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 salary = 6000;
WHERE commission_pct <=> NULL;

#2.2 运算符关键字
#① IS NULL \ IS NOT NULL \ ISNULL

#练习：查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
//where salary = 6000;
#WHERE commission_pct IS NULL;
WHERE ISNULL(commission_pct);

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

#② LEAST(返回数据的最小值) \ GREATEST(返回最大值)
SELECT LEAST('a','b','c'),GREATEST('G','C','S')
FROM DUAL;

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

#③ BETWEEN (条件一）下界 AND （条件二）上界 【查询条件一和条件二范围内的数据，包含边界】
#查询工资在6000 到 9000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 9000;

#④ IN(SET) \ NOT IN (SET)
#练习：查询部门为10,20,30部门的员工信息 
SELECT employee_id,last_name,department_id
FROM employees
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'的员工信息
SELECT employee_id,last_name,department_id
FROM employees
WHERE last_name LIKE '%a%';

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

#: _ :(下划线) 代表一个不确定的字符
#查询第二个字符是'a'的员工信息
SELECT employee_id,last_name,department_id
FROM employees
WHERE last_name LIKE '_a%'; 

#查询第二个字符是_且第三个字符是'a'的员工信息
#需要使用转义字符  \  表示后面的 字符变成 指定的字符
SELECT employee_id,last_name,department_id
FROM employees
WHERE last_name LIKE '_\_a%';

SELECT employee_id,last_name,department_id
FROM employees
WHERE last_name LIKE '_&_a%' ESCAPE '&';

#表示让 & 变为 转义字符
#⑥ REGEXP \ RLIKE 正则表达式
SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;

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

#or and and 优先级大

SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 10 OR department_id = 20 OR  department_id = 23;

#not 否定

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

#XOR 满足其中一个

SELECT last_name,salary,department_id
 FROM employees
 WHERE department_id = 50 XOR salary > 6000;

#4.位运算符 & | ^ ~ >> <<
#&（不同为1） |（只要有一个1就为1） ^（相同为1）

SELECT 12 & 5,12 | 5,12^4
FROM DUAL;

二进制对比,索引对比
0101 0010 1010
0100 1000 1001
//  &为:
0101 1010 1011
//  |为:
0101 1010 1011
//  ^为:
1110 0101 1100

#~1 表示进制中0变1,1变0

SELECT 10 & ~1 
FROM DUAL;
0101 0010 1010
//变为:
1010 1101 0101

#在一定范围内 >> （乘2) << （除2)
#右边为n就是2^(n)次幂

SELECT 4 << 1 ,8>>1
FROM DUAL;

#练习
#【题目】
USE atguigudb;
#1.选择工资不在5000到12000的员工的姓名和工资

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

#2.选择在20或50号部门工作的员工姓名和部门号

SELECT last_name,department_id
FROM employees
//where department_id in (20,50);
WHERE department_id = 20 OR department_id = 50;

#3.选择公司中没有管理者的员工姓名及job_id

SELECT last_name,job_id,commission_pct
FROM employees
//where  manager_id <=> null;
WHERE manager_id IS NULL ;

#4.选择公司中有奖金的员工姓名，工资和奖金级别

SELECT last_name,salary,commission_pctFROM employees
#where   commission_pct is not  null;
WHERE NOT commission_pct IS `NULL;WHERE NOT commission_pct <=> NULL;

#5.选择员工姓名的第三个字母是a的员工姓名

SELECT last_name,first_name
FROM employees
WHERE first_name LIKE '__a%';

#6.选择姓名中有字母a和k的员工姓名

SELECT last_name,first_name
FROM employees
//where last_name like '%k%a%' or  last_name like '%k%a%';
WHERE last_name LIKE '%k%' AND last_name LIKE '%a%';

#7.显示出表 employees 表中 first_name 以 'e’结尾的员工信息

SELECT first_name
FROM employees
WHERE first_name LIKE '%e';

#8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

SELECT department_id,last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

#9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

SELECT manager_id,last_name,salary
FROM employees
WHERE manager_id IN (100,101,110);