MySQL DQL进阶查询语句

最新推荐文章于 2024-11-01 14:31:36 发布

nooooooo0b

最新推荐文章于 2024-11-01 14:31:36 发布

阅读量85

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/nooooooo0b/article/details/109554727

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

条件查询

语法

select
			查询列表
from
			表名
where
			筛选条件；

分类：
		一，按条件表达式筛选
		简单条件运算符：> < = != <> >= <= //无==判断，只有=用于判断 <>等价于!=
		二，按逻辑表达式筛选
		逻辑运算符：
			&& || !
			and or not
		三，模糊查询
			like
			between and
			in
			is null

样例

一，按条件表达式筛选

SELECT	* FROM employees WHERE salary > 12000;

二，按逻辑表达式筛选

//相同
SELECT	last_name,department_id FROM employees WHERE department_id != 90;
SELECT	last_name,department_id FROM employees WHERE department_id <> 90;

SELECT	last_name,salary,commission_pct FROM employees
WHERE salary >= 10000 AND salary <= 20000;

SELECT	last_name,salary,commission_pct FROM employees
WHERE salary >= 10000 && salary <= 20000;

SELECT * FROM employees 
WHERE department_id < 90 OR department_id > 110 OR salary > 15000;

SELECT * FROM employees 
WHERE department_id < 90 || department_id > 110 || salary > 15000;

三，模糊查询

//与通配符搭配使用
//last_name包含a
SELECT * FROM employees
WHERE last_name LIKE '%a%';

//查询第三个字符为n，第五个字符为l的员工名和工资
SELECT last_name,salary FROM employees
WHERE LIKE '_n_l%';

通配符介绍
% 任意多个字符，包含0个字符
_ 任意单个字符

//包含临界值
SELECT * FROM employees
WHERE employee_id BETWEEN 100 AND 120;

//判断字段值是否等于括号中字段
SELECT last_name,job_id
FROM employees
WHERE job_id IN ('IT_PROT','AD_VP','AD_PRES');

//筛选字段是否为空
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
//注意下列写法错误
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct = NULL;
//却可以这样写
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct <=> NULL;
//<=> 安全等于