SQL 课程指南

SELECT TRIM('   hello world   ') AS clean_text;
-- 结果：'hello world'

SELECT company, TRIM(company) AS clean_company
FROM customers;

-- 移除电话号码中的短横线
SELECT REPLACE(business_phone, '-', '') AS clean_phone
FROM customers;

-- 把 UK 替换为 United Kingdom
SELECT REPLACE(country_region, 'UK', 'United Kingdom')
FROM customers;

-- 公司名称的前 3 个字母
SELECT company, LEFT(company, 3) AS short_code
FROM customers;

-- 电话的后 4 位
SELECT RIGHT(business_phone, 4) AS phone_end
FROM customers;

-- 从第 2 个字符开始的 5 个字符
SELECT SUBSTRING(product_name, 2, 5) AS middle_text
FROM products;

SELECT email_address,
       LENGTH(email_address) AS email_length,
       INSTR(email_address, '@') AS at_position
FROM employees;

-- 用逗号连接的完整地址（跳过 NULL）
SELECT CONCAT_WS(', ', city, state_province, zip_postal_code) AS full_address
FROM customers;

-- 带 $ 符号的价格
SELECT CONCAT('$', ROUND(list_price, 2)) AS formatted_price
FROM products;

SELECT UPPER(company) AS company_caps
FROM suppliers;

SELECT LOWER(email_address) AS standard_email
FROM employees;

-- 价格超过 50 的产品
SELECT product_name, list_price
FROM products
WHERE list_price > 50;

-- 不是销售代表的员工
SELECT first_name, job_title
FROM employees
WHERE job_title != 'Sales Representative';

-- AND：来自美国和纽约的客户
SELECT * FROM customers
WHERE country_region = 'USA'
  AND city = 'New York';

-- OR：来自 Beverages 或 Condiments 类别的产品
SELECT product_name FROM products
WHERE category = 'Beverages'
   OR category = 'Condiments';

-- 来自 3 个城市的供应商（OR 的简写）
SELECT company, city
FROM suppliers
WHERE city IN ('Tokyo', 'London', 'New York');

-- NOT IN：不来自这些城市
SELECT * FROM customers
WHERE country_region NOT IN ('USA', 'UK');

-- 运费在 20 到 50 之间的订单
SELECT id, shipping_fee
FROM orders
WHERE shipping_fee BETWEEN 20 AND 50;

-- 以 northwindtraders.com 结尾的邮箱
SELECT * FROM employees
WHERE email_address LIKE '%northwindtraders.com';

-- 以 A 开头的名字
SELECT first_name FROM employees
WHERE first_name LIKE 'A%';

-- 尚未发货的订单
SELECT id, order_date
FROM orders
WHERE shipped_date IS NULL;

-- 有网站的客户
SELECT company, web_page
FROM customers
WHERE web_page IS NOT NULL;

-- products 表的统计信息
SELECT
  COUNT(*) AS total_products,
  AVG(list_price) AS avg_price,
  MIN(list_price) AS min_price,
  MAX(list_price) AS max_price
FROM products;

-- 每个类别中有多少个产品？
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- 每个类别的平均价格
SELECT category, AVG(list_price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

-- 产品超过 5 个的类别
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

-- WHERE（之前）+ HAVING（之后）
SELECT category, AVG(list_price) AS avg
FROM products
WHERE list_price > 10        -- 分组前筛选
GROUP BY category
HAVING AVG(list_price) > 30; -- 分组后筛选

-- 按类别计数 + 总计
SELECT
  COALESCE(category, '--- TOTAL ---') AS category,
  COUNT(*) AS total
FROM products
GROUP BY category WITH ROLLUP;

-- 来自 customers 和 suppliers 的城市合并列表
SELECT city, 'customer' AS type FROM customers
UNION
SELECT city, 'supplier' AS type FROM suppliers
ORDER BY city;