MYSQL查询

合集 - mysql(2)

1.MySQL存储引擎：InnoDB与MyISAM2024-09-14

2.MYSQL查询2024-09-14

收起

MySQL查询是数据库操作中最常用的功能之一，它允许用户从数据库中检索数据。

一. 基本语法

MySQL查询的基本语法通常遵循SELECT语句，其基本结构如下：

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name ASC|DESC;

1. 查询常量

查询常量意味着在SELECT语句中直接返回固定值（而不是从表中检索）。例如：

SELECT 1 AS constant_value;

这里，1是一个常量，AS constant_value为这个常量提供了一个别名。

2. 查看表达式

你可以在SELECT语句中使用表达式来计算字段值或生成新的值。例如，计算两个字段的和：

SELECT column1, column2, (column1 + column2) AS total
FROM table_name;

3. 查询函数

MySQL提供了许多内置函数，可以在SELECT语句中使用这些函数来处理数据。例如，使用COUNT()函数计算行数：

SELECT COUNT(*) FROM table_name;

或者使用CONCAT()函数合并两个或多个字段的值：

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

4. 查询指定字段

你可以指定要查询的列名，而不是返回表中的所有列。例如：

SELECT column1, column2 FROM table_name;

5. 查询所有列

如果你想要检索表中的所有列，可以使用星号（*）作为列名的占位符：

SELECT * FROM table_name;

6. 列别名

在SELECT语句中，你可以为列或表达式的结果指定一个别名，以便于理解或用于后续的查询。例如：

SELECT column1 AS alias_name FROM table_name;

7. 表别名

当查询涉及多个表时，你可以为表指定别名以简化查询。这在连接（JOIN）操作中特别有用。例如：

SELECT a.column1, b.column2
FROM table1 AS a
JOIN table2 AS b ON a.id = b.foreign_id;

在这里，AS关键字用于指定table1和table2的别名a和b，这样在查询中就可以使用更简短的别名来引用表的列。

二、条件查询

条件查询运算符

条件查询运算符是用来在WHERE子句中筛选特定记录的。这些运算符允许你基于一个或多个条件来选择行。

• =：等于。用于查找与指定值相等的记录。

  SELECT * FROM employees WHERE department_id = 10;
  

• != 或 <>：不等于。用于查找与指定值不等的记录。

  SELECT * FROM employees WHERE department_id != 10;
  

• >、<、>=、<=：比较运算符。用于基于数值或日期进行比较。

  SELECT * FROM employees WHERE salary >= 50000;
  

• BETWEEN ... AND ...：范围内的值。用于查找在某两个值之间的记录。

  SELECT * FROM employees WHERE hire_date BETWEEN '2001-01-01' AND '2005-12-31';
  

• IN (...)：列表中的任意一个值。用于查找属于给定列表的记录。

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

• LIKE：模式匹配。用于模糊搜索。

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

• IS NULL 或 IS NOT NULL：检查是否为空。

  SELECT * FROM employees WHERE commission_pct IS NULL;
  

逻辑查询运算符

逻辑运算符用来连接多个条件或改变条件的逻辑关系。

• AND：逻辑与。所有条件都必须为真。

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

• OR：逻辑或。只要有一个条件为真即可。

  SELECT * FROM employees WHERE department_id = 10 OR salary > 50000;
  

• NOT：逻辑非。反转条件的结果。

  SELECT * FROM employees WHERE NOT (department_id = 10 AND salary > 50000);
  

三、排序与分页

排序

使用ORDER BY子句对查询结果进行排序。

• 升序 (ASC)：按字母顺序或数值从小到大排序。

  SELECT * FROM employees ORDER BY hire_date ASC;
  

• 降序 (DESC)：按字母顺序或数值从大到小排序。

  SELECT * FROM employees ORDER BY hire_date DESC;
  

分页

使用LIMIT和OFFSET来限制结果集的大小和偏移量。

• LIMIT：限制结果的数量。

  SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10;
  

• OFFSET：跳过初始行数。

  SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10 OFFSET 20;
  

四、分组查询

单字段分组

根据单个字段分组并聚合数据。

• 例如：SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

多字段分组

根据多个字段分组。

• 例如：SELECT department_id, job_id, COUNT(*) FROM employees GROUP BY department_id, job_id;

WHERE与HAVING的区别

• WHERE：在分组前过滤记录。
• HAVING：在分组后过滤记录。
• 例如：SELECT department_id, COUNT(*) AS emp_count FROM employees WHERE salary > 50000 GROUP BY department_id HAVING emp_count > 5;

五、常用函数

数值型函数

• SUM(column)：求和。

  SELECT SUM(salary) FROM employees;
  

• AVG(column)：平均值。

  SELECT AVG(salary) FROM employees;
  

• MIN(column)：最小值。

  SELECT MIN(salary) FROM employees;
  

• MAX(column)：最大值。

  SELECT MAX(salary) FROM employees;
  

字符串函数

• CONCAT(str1, str2, ..., strN)：连接字符串。

  SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
  

• SUBSTRING(str, pos, length)：提取子字符串。

  SELECT SUBSTRING(last_name, 1, 3) AS initials FROM employees;
  

• UPPER(str)：转换为大写。

  SELECT UPPER(last_name) FROM employees;
  

• LOWER(str)：转换为小写。

  SELECT LOWER(last_name) FROM employees;
  

日期和时间函数

• NOW()：获取当前日期和时间。

  SELECT NOW();
  

• CURDATE()：获取当前日期。

  SELECT CURDATE();
  

• DATE_ADD(date, INTERVAL expr unit)：增加时间间隔。

  SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
  

• TIMESTAMPDIFF(unit, datetime1, datetime2)：计算时间差。

  SELECT TIMESTAMPDIFF(DAY, '2000-01-01', '2000-01-31');
  

流程控制函数

• IF(condition, true_part, false_part)：条件判断。

  SELECT IF(gender = 'M', 'Male', 'Female') AS gender FROM employees;
  

• CASE语句：多条件判断。

  SELECT 
    CASE 
      WHEN gender = 'M' THEN 'Male'
      ELSE 'Female'
    END AS gender
  FROM employees;
  

六、子查询

子查询是一个查询语句嵌套在另一个查询语句中。

• 作为条件的一部分：例如：SELECT first_name, last_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
• 作为FROM子句的一部分：例如：SELECT * FROM (SELECT first_name, last_name FROM employees) AS emp;

七、联表查询

笛卡尔乘积

如果没有指定连接条件，则会发生笛卡尔乘积。

• 例如：SELECT * FROM employees, departments;

内连接

只返回两个表中有匹配的数据行。

• 例如：SELECT e.first_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;

左外连接

返回左表的所有行，即使右表中没有匹配。

• 例如：SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

右外连接

返回右表的所有行，即使左表中没有匹配。

• 例如：SELECT e.first_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

全连接

返回左表和右表的所有行。

• MySQL不直接支持全连接，但可以通过UNION来模拟：

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

自连接

一个表与自身进行连接。

• 例如：SELECT e1.first_name, e2.first_name FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

八、SQL执行顺序

SQL查询的执行顺序通常是：

1. 解析FROM子句中的表名或视图名称，并进行连接。
2. 应用ON子句中的连接条件。
3. 应用WHERE子句中的过滤条件。
4. 应用GROUP BY子句进行分组。
5. 应用WITH ROLLUP子句（如果存在的话）。
6. 应用HAVING子句中的过滤条件。
7. 解析SELECT子句中的列，并执行聚合函数。
8. 应用DISTINCT子句（如果存在的话）。
9. 应用ORDER BY子句进行排序。
10. 应用LIMIT子句限制结果数量。
11. 应用OFFSET子句跳过初始行数。