几种常见的MySQL查询命令以及例子

最新推荐文章于 2025-02-12 16:51:06 发布

原创最新推荐文章于 2025-02-12 16:51:06 发布 · 2.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql

本文详细介绍了SQL中in/not in, between and, is null/is not null等操作符的应用实例，通过具体案例帮助读者掌握如何使用这些操作符进行高效的数据筛选。

#2、in
/*
功能：查询某字段的值是否属于指定的列表之内
a in(常量值1，常量值2，常量值3，...)
a not in (常量值1，常量值2，常量值3，...);


*/
#a案例一：查询部门编号是30/50/90的员工名和部门编号
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

#方式二：
SELECT last_name,department_id
FROM employees
WHERE department_id =30 OR department_id=50 OR department_id=90;
#案例二:查询工种编号不是的员工信息
SELECT *
FROM employees
WHERE job_id NOT IN ('sh_clerk','it_prog');

#3/between and/not between and
/*判断某个字段的值是否介于什么什么之间
*/
#案例一：部门编号是30-90之间的部门编号员工姓名
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND department_id<=30;

#案例二：查询年薪不是10000-200000之间的员工姓名、工资、年薪
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))NOT BETWEEN 100000 AND 200000;

#4、is null/is not null
#案例1：查询奖金率为空的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;



#= 只能判断普通的内容
#isnull 只能判断null值
#<=>安全等于，既能判断普通内容，又能判断null值
SELECT *
FROM employees
WHERE commission_pct <=> 0.3;
练习：
SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)),department_id
FROM employees
WHERE employee_id=176;

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 30 AND 50;

SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;

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

SELECT job_id 
FROM employees
WHERE department_id BETWEEN 80 AND 100;