MySQL表的增删改查---多表查询和联合查询

-- 创建表
CREATE TABLE IF NOT EXISTS `students` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `age` INT(11) NOT NULL,
  `class_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
);
 
CREATE TABLE IF NOT EXISTS `classes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
);
 
-- 插入数据
INSERT INTO `students` (`name`, `age`, `class_id`) VALUES ('张三', 20, 1);
INSERT INTO `students` (`name`, `age`, `class_id`) VALUES ('李四', 21, 1);
INSERT INTO `students` (`name`, `age`, `class_id`) VALUES ('王五', 22, 2);
INSERT INTO `classes` (`name`) VALUES ('一班');
INSERT INTO `classes` (`name`) VALUES ('二班');
 
-- 查询学生及其班级的详细信息
SELECT s.name AS '学生名字', s.age AS '学生年龄', c.name AS '班级名字'
FROM students s
JOIN classes c ON s.class_id = c.id;
 
-- 查询学生及其班级的详细信息，并过滤出20岁以上的学生
SELECT s.name AS '学生名字', s.age AS '学生年龄', c.name AS '班级名字'
FROM students s
JOIN classes c ON s.class_id = c.id
WHERE s.age > 20;
 
-- 查询每个班级的学生人数
SELECT c.name AS '班级名字', COUNT(s.id) AS '学生人数'
FROM students s
JOIN classes c ON s.class_id = c.id
GROUP BY c.name;
 
-- 查询每个班级的学生人数，并按人数降序排列
SELECT c.name AS '班级名字', COUNT(s.id) AS '学生人数'
FROM students s
JOIN classes c ON s.class_id = c.id
GROUP BY c.name
ORDER BY '学生人数' DESC;

这个例子展示了如何在MySQL中使用JOIN来进行多表查询，以及如何使用WHERE、GROUP BY和ORDER BY子句来进行过滤、分组和排序操作。