SQL 查询语句总结
基础查询与算术运算
- 计算折扣因子:在
customers
表中选取last_name
、first_name
列,并计算每位客户的折扣因子(增加10点后乘以100)。
SELECT last_name, first_name, (points + 10) * 100 AS discount_factor
FROM customers;
- 去除重复项:获取
customers
表中不重复的州名。
SELECT DISTINCT state
FROM customers;
- 价格调整:选择产品表中的
name
和unit_price
列,并添加一个新列new price
,其值为原单价的110%。
SELECT name, unit_price, unit_price * 1.1 AS "new price"
FROM products;
条件查询
- 不等于操作:从
customers
表中选取非VA
状态的所有记录。
SELECT *
FROM customers
WHERE state <> 'VA';
- 时间范围筛选:选取
orders
表中订单日期在’2019-01-01’之后的所有记录。
SELECT *
FROM orders
WHERE order_date >= '2019-01-01';
- 逻辑运算符优先级:选取
customers
表中出生日期不在1990年1月1日之后且积分不超过1000分的记录。
SELECT *
FROM customers
WHERE NOT (birth_date >= '1990-01-01' OR points > 1000);
- 数值比较与计算:选取
order_items
表中订单ID为6且单位价格乘以数量大于30的记录。
SELECT *
FROM order_items
WHERE order_id = '6' AND (unit_price * quantity) > 30;
- IN运算符:选取状态为’VA’、'FL’或’GA’的客户记录。
SELECT *
FROM customers
WHERE state IN ('VA','FL','GA');
- BETWEEN运算符:选取出生于1990年1月1日至2000年1月1日之间的客户记录。
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01';
字符串模式匹配
- LIKE子句:使用通配符
%
和_
匹配姓名中的特定模式,如以’b’开头并由5个字母组成的姓氏最后以’y’结尾。
SELECT *
FROM customers
WHERE last_name LIKE 'b____y';
- 地址筛选:选取地址中包含’TRAIL’或’AVENUE’的客户记录。
SELECT *
FROM customers
WHERE address LIKE '%TRAIL%' OR address LIKE '%AVENUE%';
- 电话号码筛选:选取电话号码以9结尾或不以9结尾的客户记录。
-- 以9结尾
SELECT *
FROM customers
WHERE phone LIKE '%9';
-- 不以9结尾
SELECT *
FROM customers
WHERE phone NOT LIKE '%9';
- 正则表达式 REGEXP:使用正则表达式进行复杂模式匹配,如筛选姓氏包含’field’、以’field’开头、以’field’结尾或包含’field’、‘mac’、'rose’的客户记录。
-- 包含'aig'之一的'e'
SELECT *
FROM customers
WHERE last_name REGEXP '[aig]e';
-- 更多例子...
排序与限制结果集
- 排序结果:按照
first_name
降序和state
升序排列customers
表中的记录。
SELECT *
FROM customers
ORDER BY first_name DESC, state ASC;
- 按商品总价排序:返回ID为2的订单中的商品信息并按总价排序。
SELECT *, quantity * unit_price AS total_price
FROM order_items
WHERE order_id = '2'
ORDER BY total_price;
-
LIMIT子句:用于限制返回的结果记录数。
-
表连接查询
- 内连接(INNER JOIN):在多个表之间建立关联,如根据
product_id
字段连接order_items
表和products
表。
SELECT oi.product_id, name, quantity, oi.unit_price FROM order_items oi JOIN products p ON oi.product_id = p.product_id;
- 左连接(LEFT JOIN / LEFT OUTER JOIN):保留左表所有记录,即使无匹配的右表记录,如查询所有客户及其对应的订单信息。
SELECT customers.customer_id, ..., o.order_id, ... FROM customers LEFT JOIN orders o ON customers.customer_id = o.customer_id;
- 右连接(RIGHT JOIN / RIGHT OUTER JOIN):保留右表所有记录,即使无匹配的左表记录。
-- 示例未体现实际应用场景,因为通常情况下,客户总是有对应ID的订单更好理解 SELECT * FROM customers RIGHT JOIN orders o ON customers.customer_id = o.customer_id;
-
全外连接(FULL OUTER JOIN):保留左右表所有记录,当一方无匹配记录时填充NULL。
-
复合主键:在
order_items
表中,order_id
和product_id
构成复合主键。
SELECT * FROM order_items WHERE order_id = 2;
- 多表连接:同时连接多张表,如连接
orders
、customers
、order_statuses
表。
SELECT order_id, ..., c.first_name, os.name AS status FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_statuses os ON o.status = os.order_status_id;
- 自连接:在同一张表中连接自己,如查询员工及其直接上级。
SELECT e.office_id, ..., m.first_name AS manager FROM employees e LEFT JOIN employees m ON e.reports_to = m.employee_id;
- USING子句:简化连接条件,仅适用于列名称相同的场景。
SELECT customers.customer_id, ..., s.name AS shipper FROM customers JOIN orders o USING (customer_id) LEFT JOIN shippers s USING (shipper_id);
-
避免使用右连接:通常更倾向于使用左连接,因为它能够确保展示左表的所有记录。
-
Natural Join:基于具有相同名称和数据类型的列自动进行连接,但在实践中较少使用,因为存在隐式转换风险。
SELECT o.order_id, c.first_name FROM orders o NATURAL JOIN customers c;
- CROSS JOIN:产生两个表的笛卡尔积,即所有可能的组合。
SELECT c.first_name AS customer, p.name AS product FROM customers c CROSS JOIN products p;
- 内连接(INNER JOIN):在多个表之间建立关联,如根据
结合查询与集合操作
-
UNION:合并两个或多个查询结果,要求列数相同且对应列的数据类型兼容。
- 将不同时间段内的订单状态分类汇总。
SELECT order_id, ..., 'ACTIVE' AS status FROM orders WHERE order_date >= '2019-01-01' UNION SELECT order_id, ..., 'ARCHIVED' AS status FROM orders WHERE order_date < '2019-01-01';
- 将不同表中数据混合在一起,如将客户和托运人信息合并为一个结果集。
SELECT first_name, 'customer' AS identify FROM customers UNION SELECT name, 'shipper' AS identify FROM shippers;
-
根据条件对客户分类:根据积分划分客户等级,并按名字排序。
SELECT customer_id, ..., 'Bronze' AS type FROM customers WHERE points < '2000' UNION SELECT customer_id, ..., 'Silver' AS type FROM customers WHERE points BETWEEN '2000' AND '3000' UNION SELECT customer_id, ..., 'Gold' AS type FROM customers WHERE points BETWEEN '3000' AND '4000' ORDER BY first_name;
其他注意事项
-
IS NOT NULL
:查询非空值记录。 -
IS NULL
:查询空值记录。
以上SQL语句涵盖了基础查询、条件筛选、正则表达式匹配、排序、聚合函数、表连接以及集合操作等多个SQL查询功能的应用实例。通过这些示例,我们可以看到SQL在处理各种数据查询需求时