MySql的各种语句（最详细）-2（过滤数据）

#03-过滤数据
SELECT last_name,salary,hire_date
FROM employees
#where department_id = 90;
WHERE last_name = 'King'; 
#where hire_date = '1989-09-21';

#在查询中使用字符串、日期型变量时，需要使用一对''表示
#使用一对''表示的字符串本身，在sql标准中是区分大小写的。但是mysql中不区分。
DESC employees;

#1.常见的比较运算符： =  >  >=  <  <=  <>  != 
SELECT last_name,salary,hire_date
FROM employees
WHERE salary >= 6000;

#2. between 下界 and 上界 (包含边界)
SELECT last_name,salary
FROM employees
#where salary between 6000 and 8000;
#where salary <= 8000 and salary >= 6000;
WHERE last_name BETWEEN 'Ernst' AND 'Urman';

#3. in (set)
SELECT last_name,salary,department_id
FROM employees
#where department_id in (50,60,70);
WHERE department_id = 50 OR department_id = 60 OR department_id = 70;

#4. 模糊查询：like
#练习：查询姓名中包含字符a
# %：表示0个，1个或多个字符
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

#练习：查询姓名中包含字符a且包含字符e

SELECT last_name
FROM employees
#WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

#练习：查询第3个字符是a
# _：表示一个字符
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#练习：查询第2个字符是_且3个字符是a
SELECT last_name
FROM employees
#WHERE last_name LIKE '_\_a%';  # "刘昊\"好帅\"" 
WHERE last_name LIKE '_$_a%' ESCAPE '$';

#4. is null:查询空值
SELECT employee_id,last_name,commission_pct
FROM employees
#where commission_pct is null;
WHERE commission_pct IS NOT NULL;

# 5. 逻辑运算符：and  &&   or  ||    not   xor

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
&&    job_id LIKE '%MAN%';

# 6. 算术运算符： +  -  *  /  %