SQL学习-查询数据

牛奶
460 阅读14分钟

SQL学习-查询数据

本文介绍下 SQL 的一些查询语句，包括查询所有、字段查询、限制结果、排序、过滤数据、计算字段、汇总数据、分组数据、使用子查询、联结表查询和组合查询。

供自己以后查漏补缺，也欢迎同道朋友交流学习。

引言

上面的文章主要介绍了数据库的一些基础知识，也做了下一些简单的 CRUD 操作，但其实数据库的操作还远远不止这些，哪怕是一个查询数据的操作，也可以处理的很复杂。

下面就详细介绍下 SQL 的一些查询语句，包括查询所有、字段查询、限制结果、排序、过滤数据、计算字段、汇总数据、分组数据、使用子查询、联结表查询和组合查询。

数据库表准备

在开始编写查询语句之前，我们需要准备一些数据库表作为查询的基础。以下是商品和订单相关的表的创建和数据插入语句：

商品分类表（categories）

新建一个商品分类的表，用于存储商品的分类信息：

-- 商品分类表
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(255) NOT NULL -- 分类名称
);

-- 插入商品分类数据
INSERT INTO categories (category_name) VALUES
('电子产品'),
('家居用品'),
('服装服饰'),
('图书教材'),
('运动器材');

商品表（products）

创建一个商品表，用于存储商品的信息：

-- 商品表
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL, -- 商品名称
  price DECIMAL(10, 2) NOT NULL, -- 价格
  stock INT NOT NULL, -- 库存数量
  category_id INT, -- 商品分类
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 插入商品数据
INSERT INTO products (product_name, price, stock, category_id) VALUES
('iphone 手机', 8499, 238, 1),
('Apple iPad 平板', 3049, 46, 1),
('创维侧吸式油烟机', 694.99, 24, 2),
('九牧智能马桶', 668, 12, 2),
('Baleno 外套秋冬季棉服', 119.90, 566, 3),
('罗蒙男士衬衫', 49.89, 600, 3),
('安全用电（教材）', 15.20, 1500, 4),
('七年级下册（数学）', 9.90, 700, 4),
('可调节哑铃家用 20公斤', 50, 32, 5),
('361°无绳跳绳 蓝色', 15.90, 76, 5);

订单表（orders）

创建一个订单表，用于存储订单的信息：

-- 订单表
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE NOT NULL, -- 订单日期
  customer_name VARCHAR(255) NOT NULL, -- 客户姓名
  product_id INT, -- 产品id
  quantity INT NOT NULL, -- 购买数量
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入订单数据
INSERT INTO orders (order_date, customer_name, product_id, quantity) VALUES
('2024-12-01', '李四', 1, 1),
('2024-12-01', '李四', 2, 1),
('2024-11-23', '张三', 2, 1),
('2024-06-01', '李华', 3, 1),
('2024-06-03', '张伟', 6, 3),
('2024-08-02', '张三', 7, 4),
('2024-09-01', '张三', 9, 1),
('2024-09-01', '陈杰', 9, 2),
('2024-09-02', '李晓峰', 1, 2),
('2024-09-02', '张三', 5, 2),
('2024-10-02', '李四', 10, 3);

上面 3 个表是基础的表结构类型，在后面的文章中，我们会使用这些表进行一些复杂的查询操作。

查询所有

查询所有记录可以使用 SELECT * 语句：

-- 查询所有商品分类表
SELECT * FROM categories;

结果如下：

sql-search-1

字段查询

字段查询是指在 SQL 查询中指定需要返回的列名，而不是使用 * 来返回所有列。

-- 字段查询：查询商品表中的商品名称和价格
SELECT product_name, price FROM products;

结果如下：

sql-search-2

限制结果查询

限制结果查询通常涉及使用 LIMIT 子句来指定返回的记录数，以及使用 OFFSET 子句来指定从哪条记录开始返回。这些子句常用于分页显示数据。

使用LIMIT限制结果数量

-- 使用LIMIT限制结果数量
SELECT product_name, price FROM products LIMIT 5;

结果如下：

sql-search-3

结合OFFSET、LIMIT实现分页查询

-- 结合OFFSET、LIMIT实现分页查询
SELECT product_name, price FROM products LIMIT 2 OFFSET 3;

结果如下：

sql-search-4

排序查询

排序查询是指在 SQL 中使用 ORDER BY 子句对结果集进行排序。

你可以根据一个或多个列对结果进行升序（ASC）或降序（DESC）排序，默认是按照升序排序的。

单列排序

-- 单列排序：按价格升序排序，并限制前5条
SELECT * FROM products ORDER BY price ASC LIMIT 5;

-- 单列排序：按价格降序排序，并限制前5条
SELECT * FROM products ORDER BY price DESC LIMIT 5;

升序结果如下：

sql-search-5

多列排序


-- 多列排序：按价格升序排序，若价格相同则按库存数量排序
-- 默认升序
SELECT * FROM products ORDER BY price, stock;

-- 多列排序
-- 从1开始，2表示price，3表示stock
SELECT product_name, price, stock FROM products ORDER BY 2, 3;

-- 多列排序：按价格升序排序，若价格相同则按库存数量降序排序
SELECT * FROM products ORDER BY price ASC, stock DESC;

-- 多列排序：按价格升序排序，若价格相同则按库存数量降序排序
-- 从1开始，2表示price，3表示stock
SELECT product_name, price, stock FROM products ORDER BY 2 ASC, 3 DESC;

最后一句执行结果如下：

sql-search-6

过滤数据查询

过滤数据查询是指使用 WHERE 子句来限制查询结果，只返回满足特定条件的记录。

where子句操作符

操作符描述
=等于
<> 或 !=不等于
>大于
>=大于等于
<小于
<=小于等于
BETWEEN... AND...介于...和...之间
LIKE模糊匹配
-- 查询价格大于100的商品
SELECT * FROM products WHERE price > 100;

-- 查询价格在20到50之间的商品
SELECT * FROM products WHERE price BETWEEN 20 AND 50;

-- 查询商品分类为1的产品
SELECT * FROM products WHERE category_id = 1;

最后一句执行结果如下：

sql-search-7

组合where语句

操作符描述
AND同时满足多个条件
OR满足任一条件
IN在给定的列表中
NOT条件的否定
-- 组合条件查询
-- 查询价格大于5000且商品分类为1的产品
SELECT * FROM products WHERE price > 5000 AND category_id = 1;

-- 查询价格大于5000或商品分类为4的产品
SELECT * FROM products WHERE price > 5000 OR category_id = 4;

-- 查询商品分类为1或2的产品
SELECT * FROM products WHERE category_id IN(1, 2);

-- 查询商品分类非1和非2的产品
SELECT * FROM products WHERE category_id NOT IN(1, 2);

最后一句执行结果如下：

sql-search-8

使用通配符查询

LIKE 后面使用%（匹配任意字符出现任意次数）和_（匹配任意单个字符）。

-- 查询商品名称中包含'手机'的产品
SELECT * FROM products WHERE product_name LIKE '%手机%';

-- 查询商品名称以'九'开头的产品
SELECT * FROM products WHERE product_name LIKE '九%';

-- 查询商品名称从第二个字符是a、第三个字符是l的产品
SELECT * FROM products WHERE product_name LIKE '_al%';

最后一句执行结果如下：

sql-search-9

计算字段查询

计算字段查询是指在 SQL 查询中使用表达式和函数来计算新的字段值。这些计算可以包括字符串拼接、算术运算、使用数据库函数等。

  • 常用的文本处理函数：UPPER(), LOWER(), LENGTH(), REPLACE(), SUBSTRING()。
  • 日期和时间处理函数：CURDATE(), NOW(), DATE_ADD(), DATE_SUB().
  • 数值处理函数：ROUND(), FLOOR(), CEILING().
-- 计算字段查询

-- 拼接字段
-- CONCAT(str1, str2, ...)
-- AS 起别名
SELECT CONCAT(product_name, ' - ', price) AS product_info FROM products LIMIT 5;

-- 去除空格
SELECT TRIM(CONCAT( ' - ', product_name, ' - ')) AS trimmed_string FROM products LIMIT 1;

-- 截取字符串
SELECT SUBSTRING(product_name, 1, 5) AS substring FROM products LIMIT 1;

-- 大写
SELECT UPPER(product_name) AS uppercase_string FROM products LIMIT 1;

-- 小写
SELECT LOWER(product_name) AS lowercase_string FROM products LIMIT 1 OFFSET 1;

-- 长度
SELECT LENGTH(product_name) AS string_length FROM products LIMIT 1;

-- 重复
SELECT REPEAT(product_name, 2) AS repeated_string FROM products LIMIT 1;

-- 替换
SELECT REPLACE(product_name, '手机', '手机壳') AS replaced_string FROM products LIMIT 1;

-- 日期和时间处理
-- 查询今天的订单
SELECT * FROM orders WHERE order_date = CURDATE();

-- 查询订单日期是当前日期30天内的订单
SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- 数值处理
-- 加法
SELECT 10 + 5 AS sum;
-- 减法
SELECT 10 - 5 AS difference;
-- 乘法
SELECT 10 * 5 AS product;
-- 除法
SELECT 10 / 5 AS quotient;
-- 四舍五入
SELECT ROUND(3.14159, 2) AS rounded_number;
-- 向下取整
SELECT FLOOR(3.14159) AS floored_number;
-- 向上取整
SELECT CEILING(3.14159) AS ceiled_number;

汇总数据查询

汇总数据查询通常涉及到使用聚合函数（如 SUM(), AVG(), COUNT(), MAX(), MIN()）来对数据集进行汇总。

聚集函数

-- 聚集函数

-- 所有商品库存求和
SELECT SUM(stock) AS total_stock FROM products;

-- 计算平均价格
SELECT AVG(price) AS average_price FROM products;

-- 查找最高价格
SELECT MAX(price) AS max_price FROM products;

-- 查找最低价格
SELECT MIN(price) AS min_price FROM products;

-- 计算每个人订单量
SELECT COUNT(*) AS order_count FROM orders GROUP BY customer_name;

最后一句执行结果如下：

sql-search-10

组合聚集函数

汇总每个分类的总价格和平均价格：

-- 汇总每个分类的总价格和平均价格
SELECT category_id, SUM(price) AS total_price, AVG(price) AS average_price FROM products GROUP BY category_id;

sql-search-11

分组数据查询

分组数据查询是指使用 GROUP BY 子句将结果集按照一个或多个列的值进行分组，然后通常配合聚合函数对每个分组进行汇总。

GROUP BY

-- 单列分组
SELECT category_id, SUM(price) AS total_price FROM products GROUP BY category_id;

-- 多列分组
-- 根据日期和用户名分类汇总购买的数量
SELECT customer_name, order_date, SUM(quantity) AS total_quantity FROM orders GROUP BY order_date, customer_name;

最后一句执行结果如下：

sql-search-12

HAVING

HAVING 子句用于对分组后的结果进行过滤，它通常与聚合函数一起使用。

-- 根据商品分类计算总价格，过滤总价大于1000的分类
SELECT category_id, SUM(price) AS total_price FROM products GROUP BY category_id HAVING total_price > 1000;

执行结果如下：

sql-search-13

分组和排序

-- 查询每个分类的总价格，并按总价格升序排序
SELECT category_id, SUM(price) AS total_price FROM products GROUP BY category_id ORDER BY total_price;

执行结果如下：

sql-search-14

SELECT子句顺序

在 SQL 中，SELECT 子句的顺序对于查询的结果没有影响，但是它会影响到查询的可读性和维护性。一个好的实践是按照以下顺序排列 SELECT 子句中的字段：

排序顺序子句字段描述
1选择列表（Select List）聚合函数和计算字段。
常量（如字符串或数字）。
表达式（如函数调用或字段计算）。
字段名。
2FROM 子句表名和别名。
3WHERE 子句过滤条件。
4GROUP BY 子句分组字段。
5HAVING 子句分组过滤条件。
6ORDER BY 子句排序字段。
7LIMIT 子句结果限制。

最佳实践：

SELECT
  xxx
FROM
  xxx
WHERE
  xxx
GROUP BY
  xxx
HAVING
  xxx
ORDER BY
  xxx
LIMIT 10;

使用子查询

子查询，也称为嵌套查询，是嵌入在另一个查询中的 SQL 查询。子查询通常用在 SELECT、INSERT、UPDATE 或 DELETE 语句中，并且通常跟在关键字 IN、ANY、ALL 或 EXISTS 后面。

使用IN

IN 关键字用于测试某个列的值是否包含在子查询返回的值列表中。如果子查询返回至少一个值与外部查询的列值匹配，则条件为真。

-- 先查询库存大于200的商品，再查询这些商品所属的分类，最后查询这些分类下的商品
SELECT * FROM products WHERE category_id IN (SELECT category_id FROM products WHERE stock > 200);

执行结果如下：

sql-search-15

使用ANY

ANY 关键字用于比较外部查询的列值与子查询返回的任意一行的值。如果至少有一个比较使得条件为真，则整个条件为真。

-- 先查询分类为2的商品，再查询这些商品的价格，最后查询价格大于这些价格的商品
SELECT * FROM products WHERE price > ANY(SELECT price FROM products WHERE category_id = 2);

执行结果如下：

sql-search-16

使用ALL

ALL 关键字用于比较外部查询的列值与子查询返回的所有行的值。只有当所有比较都为真时，整个条件才为真。

-- 先查询分类为2的商品，再查询这些商品的价格，最后查询价格大于所有这些价格的商品
SELECT * FROM products WHERE price > ALL(SELECT price FROM products WHERE category_id = 2);

使用EXISTS

EXISTS 关键字用于检查子查询是否返回至少一行数据。它不关心返回的是什么数据，只关心是否有数据返回。如果子查询至少返回一行数据，则条件为真。

-- 先查询订单里是否有数量大于2的订单，再查询产品名称
SELECT product_name FROM products WHERE EXISTS (SELECT 1 FROM orders WHERE orders.quantity > 2);

联结表查询

联结表查询（JOIN）是 SQL 中用于结合多个表中数据的强大功能。

内联结

内联结返回两个表中匹配的行。如果两个表中都有数据，则结果集中会显示这些数据；如果其中一个表中没有匹配的行，则这些行不会出现在结果集中。

-- 内联结
SELECT order_date, customer_name, product_name
FROM orders
INNER JOIN products ON orders.product_id = products.id;

sql-search-18

别名

别名是为表或列指定的临时名称，它可以使查询更易读、更易于维护。

SELECT order_date, customer_name, product_name
FROM orders AS o
INNER JOIN products AS p ON o.product_id = p.id;

自联结

自联结是一种特殊的内联结，其中一个表与自身进行联结。这通常用于查询表中与自身相关联的行。

假设有一个员工表 employees，其中包含员工及其直接上级的信息。表中有一个字段 manager_id，它存储每个员工的上级的 ID，而employee_id 是员工的唯一标识符。

SELECT e1.employee_name, e2.employee_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

这个查询将返回每个员工及其直接上级的姓名。

自然联结

自然联结是一种自动根据两个表中同名的列进行联结的操作。如果两个表中有相同名称的列，那么这些列将被用于联结条件。

SELECT * FROM products NATURAL JOIN orders;

执行结果如下：

sql-search-19

外联结

外联结包括左外联结（LEFT OUTER JOIN）、右外联结（RIGHT OUTER JOIN）和全外联结（FULL OUTER JOIN）。外联结返回一个表的所有行，即使另一个表中没有匹配的行。 MYSQL 不支持全外联结。

左外联结

左外联结返回左表（第一个表）的所有行，即使右表（第二个表）中没有匹配的行。如果右表中没有匹配的行，则结果集中这些行的右表字段会显示为 NULL。

SELECT o.*, p.product_name
FROM orders o
LEFT JOIN products p ON o.product_id = p.id;

执行结果如下：

sql-search-20

右外联结

右外联结返回右表（第二个表）的所有行，即使左表（第一个表）中没有匹配的行。如果左表中没有匹配的行，则结果集中这些行的左表字段会显示为 NULL。

SELECT p.*, o.order_date
FROM products p
RIGHT JOIN orders o ON p.id = o.product_id;

执行结果如下：

sql-search-21

交叉联结（CROSS JOIN）

交叉联结，也称为笛卡尔积，返回两个表中所有可能的行组合。每个表中的每一行都会与另一个表中的每一行组合。

-- 交叉联结（CROSS JOIN），写法1
SELECT c.*, p.*
FROM categories c, products p;

-- 交叉联结（CROSS JOIN），写法2
SELECT c.*, p.*
FROM categories c
CROSS JOIN products p;

执行结果如下：

sql-search-22

带聚集函数的联结

在需要对多个表进行聚合操作时，可以组合使用连接查询与聚合函数。

-- 查询每个订单的总金额
SELECT o.*, p.price, SUM(p.price * o.quantity) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY o.id;

执行结果如下：

sql-search-23

组合查询

组合查询通常指的是使用 UNION 或 UNION ALL 操作符将两个或多个 SELECT 语句的结果集合并成一个结果集。

  • UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并且会自动去除重复的行。
  • UNION ALL 操作符也用于合并两个或多个 SELECT 语句的结果集，但它不会去除重复的行。
-- 查询价格大于500和分类为2的商品名称，并去除重复的行
SELECT product_name FROM products WHERE price > 500
UNION
SELECT product_name FROM products WHERE category_id = 2;

-- 查询价格大于500和分类为2的商品名称，并保留重复的行
SELECT product_name FROM products WHERE price > 500
UNION ALL
SELECT product_name FROM products WHERE category_id = 2;

执行结果如下：

sql-search-17

SQL学习专栏系列

练习代码库地址

SQL学习 sql-study

avatar
文章
阅读
粉丝