MySQL条件查询语句常用操作有哪些

发布时间:2022-04-28 12:03:55 作者:iii
来源:亿速云 阅读:201

MySQL条件查询语句常用操作有哪些

MySQL作为最流行的关系型数据库管理系统之一,提供了丰富的查询功能。条件查询是SQL中最基础也是最常用的操作之一,它允许我们从数据库中筛选出符合特定条件的数据。本文将详细介绍MySQL中常用的条件查询操作,帮助读者更好地掌握这一核心技能。

1. 基本条件查询

1.1 WHERE子句

WHERE子句是条件查询的基础,用于指定筛选条件。其基本语法如下:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

例如,查询employees表中salary大于5000的员工:

SELECT * FROM employees WHERE salary > 5000;

1.2 比较运算符

MySQL支持多种比较运算符,常用的包括:

例如,查询employees表中department_id不等于10的员工:

SELECT * FROM employees WHERE department_id <> 10;

1.3 逻辑运算符

逻辑运算符用于组合多个条件,常用的有:

例如,查询employees表中salary大于5000且department_id为10的员工:

SELECT * FROM employees WHERE salary > 5000 AND department_id = 10;

2. 模糊查询

2.1 LIKE运算符

LIKE运算符用于模糊匹配,通常与通配符一起使用。常用的通配符有:

例如,查询employees表中last_nameS开头的员工:

SELECT * FROM employees WHERE last_name LIKE 'S%';

2.2 ESCAPE子句

当需要匹配包含通配符的字符串时,可以使用ESCAPE子句指定转义字符。例如,查询employees表中last_name包含%的员工:

SELECT * FROM employees WHERE last_name LIKE '%\%%' ESCAPE '\';

3. 范围查询

3.1 BETWEEN运算符

BETWEEN运算符用于查询某个范围内的数据。其语法如下:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

例如,查询employees表中salary在5000到10000之间的员工:

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

3.2 IN运算符

IN运算符用于查询某个字段值在指定列表中的数据。其语法如下:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

例如,查询employees表中department_id为10、20或30的员工:

SELECT * FROM employees WHERE department_id IN (10, 20, 30);

4. 空值查询

4.1 IS NULL运算符

IS NULL运算符用于查询某个字段值为空的数据。其语法如下:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

例如,查询employees表中commission_pct为空的员工:

SELECT * FROM employees WHERE commission_pct IS NULL;

4.2 IS NOT NULL运算符

IS NOT NULL运算符用于查询某个字段值不为空的数据。其语法如下:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

例如,查询employees表中commission_pct不为空的员工:

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

5. 排序查询

5.1 ORDER BY子句

ORDER BY子句用于对查询结果进行排序。其语法如下:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

例如,查询employees表中所有员工,并按salary降序排列:

SELECT * FROM employees ORDER BY salary DESC;

5.2 多列排序

ORDER BY子句支持多列排序,优先按第一列排序,第一列相同时再按第二列排序,以此类推。例如,查询employees表中所有员工,先按department_id升序排列,再按salary降序排列:

SELECT * FROM employees ORDER BY department_id ASC, salary DESC;

6. 分组查询

6.1 GROUP BY子句

GROUP BY子句用于将查询结果按指定列进行分组。其语法如下:

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...;

例如,查询employees表中每个部门的平均工资:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

6.2 HAVING子句

HAVING子句用于对分组后的结果进行筛选。其语法如下:

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

例如,查询employees表中平均工资大于5000的部门:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

7. 子查询

7.1 标量子查询

标量子查询返回单个值,通常用于SELECTWHEREHAVING等子句中。例如,查询employees表中工资高于平均工资的员工:

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

7.2 列子查询

列子查询返回一列数据,通常用于INANYALL等操作符中。例如,查询employees表中工资高于部门平均工资的员工:

SELECT * FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);

7.3 行子查询

行子查询返回一行数据,通常用于INEXISTS等操作符中。例如,查询employees表中工资和奖金与某个特定员工相同的员工:

SELECT * FROM employees
WHERE (salary, commission_pct) = (SELECT salary, commission_pct FROM employees WHERE employee_id = 100);

8. 联合查询

8.1 UNION运算符

UNION运算符用于合并两个或多个SELECT语句的结果集,并去除重复行。其语法如下:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

例如,查询employees表和contractors表中所有员工的姓名:

SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM contractors;

8.2 UNION ALL运算符

UNION ALL运算符与UNION类似,但不去除重复行。其语法如下:

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

例如,查询employees表和contractors表中所有员工的姓名,包括重复行:

SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM contractors;

9. 连接查询

9.1 内连接(INNER JOIN)

内连接返回两个表中满足连接条件的行。其语法如下:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

例如,查询employees表和departments表中员工的姓名和部门名称:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

9.2 左连接(LEFT JOIN)

左连接返回左表中所有行,以及右表中满足连接条件的行。如果右表中没有匹配的行,则返回NULL。其语法如下:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

例如,查询employees表中所有员工及其部门名称,即使员工没有分配部门:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

9.3 右连接(RIGHT JOIN)

右连接返回右表中所有行,以及左表中满足连接条件的行。如果左表中没有匹配的行,则返回NULL。其语法如下:

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

例如,查询departments表中所有部门及其员工姓名,即使部门没有员工:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

9.4 全连接(FULL JOIN)

全连接返回左表和右表中所有行,如果某一行在另一表中没有匹配的行,则返回NULL。MySQL不支持FULL JOIN,但可以通过UNION实现类似效果。其语法如下:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name
UNION
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

例如,查询employees表和departments表中所有员工和部门,即使员工没有分配部门或部门没有员工:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

10. 总结

MySQL的条件查询功能非常强大,涵盖了从基本查询到复杂查询的多种操作。通过掌握这些常用操作,可以有效地从数据库中提取所需的数据,满足各种业务需求。希望本文能帮助读者更好地理解和应用MySQL的条件查询语句。

推荐阅读:
  1. MySQL 查询语句SELECT和数据条件过滤
  2. MySQL有哪些常用操作

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:vue3+vite中如何使用svg图标

下一篇:安装node时会不会自动安装npm

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》