油管课程 三小时MYSQL初学入门 SQL查询语句总结

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;
  

结合查询与集合操作

• 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在处理各种数据查询需求时