MySQL基本语法之过滤数据
SELECT last_name,salary
FROM employee
WHERE department_id = 90;
WHERE last_name = 'king'
WHERE hire_date = '1985-09-21'
#在查询中使用字符串、日期型变量,需要使用一对’‘表示
#使用一对’'表示的字符串本身,在sql标准中是区分大小写的。但是mysql中不区分。
DESC employees;
1.常见的比较运算符:= > >= < <= <> !=
SELECT last_name,salary,hire_date
FROM employees
WHERE salary >= 6000;
between ...and...
(包括边界)(小值在左边,大值在右边)(不一定非要是数值)
SELECT last_name,salary
FROM employee
WHERE salary BETWEEN 6000 AND 8000;
#where salary >=6000 and <=8000;
in(set)
SELECT last_name,salary,department_id
FROM employees
#where departrment_id in (50,60,70);
WHERE department_id = 50 OR department_id = 60 OR department_id 70;
- 模糊查询:
like
举例:查询姓名中包含的字符a
%
表示被查询对象中包含了一个或多个字符(例如这里的举例中就是指查询姓名中包含了一个或多个a
字符)
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
练习:查询字符中包含字符a且包含字符b
SELECT last_name
FROM employees
#where last_name like '%a%b%' or last_name LIKE '%b%a%';
WHERE last_name LIKE '%a%' AND last_name LIKE '%b%'
练习:查询第二个字符是a
的
_
表示任意一个字符(占位)
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';
练习:查询第三个字符是a
的(用两个连续的_)
_
表示任意一个字符(占位)
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
练习:查询第二个字符是_
且第三个字符是a
的
\
表示转义字符(规定这里\
后面的_
就表示单纯的下划线)
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
is null
查询空值
SELECT employees_id,last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
WHERE commission_pct IS NOT NULL; #查询非空的
- 逻辑运算符 :
and(&&) or(||) not(xor)
SELECT employees_id, last_name,job_id , salary
FROM employees
WHERE salary > 10000
AND job_id LIKE '%man%';
&& job_id LIKE '%man%';
- 基本逻辑运算符
+ - * / %(取模)