MySQL数据库学习-基础DQL

2025-08-01

DQL，Data Query Language(数据库查询语言)，SELECT。

SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

笔记

基本查询
SELECT 字段1, 字段2, 字段3, …… FROM 表名;
SELECT * FROM 表名;
设置别名：SELECT 字段1[AS 别名1]，字段2[AS 别名2]……FROM 表名;
去重查询：SELECT DISTINC 字段列表 FROM 表名;
条件查询（WHERE）
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件：“>”、“<”、“>=”、“<=”、“=”、“!=(<>)”、“BETWEEN……AND……”、“IN(……)”、“LIKE 占位符(_匹配单个字符，%匹配任意个字符)”、“IS NULL”、“AND(&&)”、“OR(||)”、“NOT(!)”
聚合函数（COUNT, MAX, MIN, AVG, SUM）
介绍：将一列数据作为一个整体，进行纵向计算。
SELECT 聚合函数(字段列表) FORM 表名;
分组查询（GROUP BY）
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]——where和having，where是分组之前进行过滤，having是在分组之后过滤。where不能对聚合函数进行判断，而having可以。
排序查询（ORDER BY）
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;
ASC升序，DESC降序。多字段排序，优先按照第一个字段值排序，然后才会对第二个字段进行排序。
分页查询（LIMIT）
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意：
起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数；
分页查询是数据库的方言，不同的数据库有不同的实现。
如果查询的是第一页数据，起始索引可以省略，直接简写为limit10。

题目

任务零：数据准备与场景重构 (DDL/DML)

你的数据库现在太“干净”了，不利于练习。我们先把它搞得复杂点。

恢复并增强字段：你之前把 tasks 表的 due_date 字段删了，这是一个很常见的错误——轻易删除字段。现在发现还是得有截止日期才能评估风险。请把它加回来。顺便，我们再给 tasks 表增加一个 completion_date (完成日期，DATE 类型，可以为空)，用来记录任务实际完成的时间。
增加新项目和人员：
再加一个新用户：'sunba', 'sunba@example.com', 'Developer'。
再新建一个项目：'Data Analysis Platform', 描述是 'Build a platform for internal data analysis and reporting.', 开始日期是 '2023-03-01', 状态是 'Planning'。这个项目暂时没有负责人。
批量添加任务：为两个项目（'Smart Factory OS Development' 和 'Data Analysis Platform'）补充大量任务，让数据看起来更丰富。注意，有些任务要故意不分配给任何人 (assigned_to_user_id 为 NULL)，有些任务要有截止日期。
项目 'Smart Factory OS Development' (project_id=1):
'Refactor API Endpoints', 分配给 'wangwu', 状态 'In Progress', 截止日期 '2023-09-15'.
'Write Unit Tests for Core Modules', 分配给 'qianqi', 状态 'New', 截止日期 '2023-10-01'.
'Deploy to Staging Server', 不分配, 状态 'Blocked'.
项目 'Data Analysis Platform' (project_id=2):
'Define Data Schema', 分配给 'hongcha', 状态 'Done', 截止日期 '2023-04-10', 完成日期 '2023-04-08'.
'Develop Data Ingestion Pipeline', 分配给 'sunba', 状态 'In Progress', 截止日期 '2023-09-30'.
'Create Dashboard Mockups', 分配给 'hongcha', 状态 'In Progress', 截止日期 '2023-08-25'.
'User Authentication Module', 分配给 'sunba', 状态 'New', 截止日期 '2023-11-01'.
'Initial Market Research', 不分配, 状态 'Open'.

任务一：简单到中等的查询 (SELECT, WHERE, ORDER BY)

现在数据准备好了。开始查询。

基本信息检索：
作为 PM，你想看看现在团队里有谁。查询 users 表中所有角色为 'Developer' 的用户名和邮箱。
项目出了问题，你想知道哪些任务被卡住了 (Blocked)，以及是哪个项目的。查询 tasks 表，显示任务名和其所属的 project_id。
条件与排序组合：
你想看看最近有什么紧急任务。查询 tasks 表中所有状态不是 'Done' 的任务，按 due_date (截止日期) 升序排序，越早到期的越靠前。只看最重要的前5个。
找出所有在2023年8月之后（包含8月）需要截止的任务。

任务二：聚合与分组 (Aggregate Functions, GROUP BY, HAVING)

这部分是数据分析的精髓，也是你作为 PM 最需要理解的部分。

基本统计：
公司领导问你，现在总共有几个项目在跑？系统里总共有多少个任务？用聚合函数 COUNT() 分别查出来。
你想知道哪个任务的截止日期最晚，是哪一天？用 MAX() 函数查询 tasks 表中最大的 due_date。
分组分析：
这才是重点：你想评估每个开发者的工作负载。按 assigned_to_user_id 分组，统计每个用户（user_id）手上有多少个未完成（状态不是 'Done'）的任务。
在上面的基础上，你只想关注那些工作量饱和的人。筛选出上一问的结果中，任务数量大于等于2个的用户 ID 和他们的未完成任务数。这里你要想想到底是用 WHERE 还是 HAVING。
统计每个项目（project_id）当前的 Open, In Progress, Blocked 状态的任务分别有多少个。

代码

use INDUSTRIAL_PROJECT_MANAGEMENT;
alter table tasks add due_date date null;
alter table tasks add completion_date date null;

insert users (username,email,role) values ('sunba','sunba@example.com','Developer');
insert projects 
	(project_name,description,start_date,status) 
	values 
	('Data Analysis Platform','Build a platform for internal data analysis and reporting.',
	'2023-03-01','Planning');

insert tasks (project_id,task_name,assigned_to_user_id,status,due_date,completion_date)
	values
	('1','Refactor API Endpoints','3','In Progress',null,null),
	('1','Write Unit Tests for Core Modules','5','New','2023-10-01',null),
	('1','Deploy to Staging Server',null,'Blocked',null,null),
	('2','Define Data Schema','1','Done','2023-04-10','2023-04-08'),
	('2','Develop Data Ingestion Pipeline','6','In Progress','2023-09-30',null),
	('2','Create Dashboard Mockups','1','In Progress','2023-08-25',null),
	('2','User Authentication Module','6','New','2023-11-01',null),
	('2','Initial Market Research',null,'Open',null,null);

-- 1.基本信息检索
select username,email from users where role='Developer';
select task_name,project_id from tasks where status='Blocked';

-- 2. 条件与排序组合
select task_name,project_id,due_date 
	FROM tasks 
	where status!='Done' and due_date is not null
	ORDER BY due_date asc 
	limit 5;
select task_name,due_date 
	from tasks 
	where due_date>='2023-08-01';

-- 3.聚合和分组-基本统计
select count(*) as total_projects from projects;
select MAX(due_date) from tasks;

-- 4.聚合和分组-分组分析
select assigned_to_user_id,count(task_name) from tasks 
	where status!='Done'
	group by assigned_to_user_id;

select assigned_to_user_id,count(task_name) from tasks 
	where status!='Done'
	group by assigned_to_user_id
	having count(task_name)>=2;

select project_id,status,count(task_name)
	from tasks
	-- where status='Open' or status='In Progress' or status='Blocked'
	where status in ('Open','In Progress','Blocked')
	group by project_id,status;

← 返回