SQL，即结构化查询语言，是一种用于关系型数据库的查询和数据操作的编程语言。随着技术的发展，SQL已经成为一个非常重要的技能，被广泛应用于各种数据库管理系统中，如MySQL、SQLite、SQL Server、PostgreSQL和Oracle等。本文将主要介绍如何使用SQL进行单表数据分析，并以MySQL为例，通过实际操作来加深理解。
MySQL Workbench的使用
为了进行实践操作，使用了一个名为mavenmovies的数据库。该数据库包含了16个相关数据表，主要涉及客户、业务和库存等信息。每个数据表由行和列组成，行代表记录，列代表字段。数据表中包含主键和外键，主键是唯一的，用于标识数据库中的唯一记录，而外键则不是唯一的。
SQL查询语句的六大要素
SQL查询语句由六大要素构成，被称为“BIG 6”。这些要素构成了SQL查询语句的基本结构，包括：
SELECT — 指定需要查询的列
FROM — 指定查询的数据表
WHERE — 根据条件过滤结果，是可选的
GROUP BY — 对结果进行分组，是可选的
HAVING — 按组过滤结果，是可选的
ORDER BY — 对结果进行升序或降序排序，是可选的
一个SQL查询语句的基本形式如下：
SELECT column-name FROM table-name WHERE logical-condition GROUP BY column-name HAVING logical-condition ORDER BY column-name
LIKE操作符的使用
LIKE操作符在需要根据部分信息检索记录时非常有用。它允许使用模式匹配来查找记录。例如：
WHERE name LIKE 'Denise%' —— 名字以'Denise'开头的记录
WHERE description LIKE '%fancy%' —— 包含'fancy'的记录
WHERE name LIKE '%Johnson' —— 名字以'Johnson'结尾的记录
WHERE first_name LIKE '_erry' —— 名字以'erry'结尾，且前面恰好有一个字符的记录（如Terry, Jerry）
聚合函数
聚合函数用于对数据集进行统计分析，包括：
COUNT() —— 计算记录数
COUNT DISTINCT() —— 计算不同值的数量
MIN() —— 找出最小值
MAX() —— 找出最大值
AVG() —— 计算平均值
SUM() —— 计算总和
在Workbench中操作
SELECT first_name, last_name, email FROM customer; —— 获取客户的名字、姓氏和电子邮件
SELECT DISTINCT rental_duration FROM film; —— 查看是否有其他租赁时长
SELECT * FROM customer WHERE first_name="mary"; —— 获取名字为Mary的客户记录
SELECT * FROM payment WHERE amount>5 AND payment_date>'2006-01-06' AND customer_id<=100; —— 获取特定条件下的支付记录
SELECT title, special_features FROM film WHERE special_features LIKE "%Behind the Scenes%"; —— 获取包含“幕后制作”特别功能的影片标题
SELECT rental_duration, COUNT(film_id) FROM film GROUP BY rental_duration; —— 按租赁时长统计影片数量
SELECT replacement_cost, COUNT(film_id) AS number_of_films, AVG(rental_rate) AS avg_rental_rate, MIN(rental_rate) AS cheapest_rental, MAX(rental_rate) AS most_expensive_rental FROM film GROUP BY replacement_cost; —— 按替换成本分组统计影片数量、平均租金、最低租金和最高租金
SELECT customer_id, COUNT(rental_id) FROM rental GROUP BY customer_id HAVING COUNT(rental_id)<15; —— 获取租赁次数少于15次的客户ID
SELECT title, length, rental_rate FROM film ORDER BY length DESC; —— 按长度从长到短排序的影片标题、长度和租金
= —— 等于
<> —— 不等于
> —— 大于
< —— 小于
>= —— 大于或等于
<= —— 小于或等于
BETWEEN —— 两个值之间的范围
LIKE —— 匹配这种模式
IN() —— 等于这些值中的一个
SELECT DISTINCT first_name, last_name,
CASE
WHEN store_id = 1 AND active = 0 THEN 'store 1 inactive'
WHEN store_id = 1 AND active = 1 THEN 'store 1 active'
WHEN store_id = 2 AND active = 0 THEN 'store 2 inactive'
WHEN store_id = 2 AND active = 1 THEN 'store 2 active'
ELSE 'no sense'
END AS Active_status
FROM customer;
SELECT DISTINCT
length,
CASE
WHEN length < 60 THEN 'UNDER 1 HR'
WHEN length BETWEEN 60 AND 90 THEN '1 - 1.5'
WHEN length > 90 THEN 'OVER 1.5 HRS'
ELSE 'UH NO... CHECK LOGIC'
END AS length_bucket
FROM film;
SELECT DISTINCT
title,
CASE
WHEN rental_duration <= 4 THEN 'rental too short'
WHEN rental_rate >= 3.99 THEN 'too expensive'
WHEN rating IN ('tNC-17', 'R') THEN 'too adult'
WHEN length NOT BETWEEN 60 AND 90 THEN 'too short or too long'
WHEN description LIKE '%Shark%' THEN 'nope has sharks'
ELSE 'great recommendation'
END AS fit_for_recommendation
FROM film;
SELECT store_id,
COUNT(CASE WHEN active = 0 THEN active ELSE NULL END) AS inactive_count,
COUNT(CASE WHEN active = 1 THEN active ELSE NULL END) AS active_count
FROM customer
GROUP BY store_id;