mysql学习总结二

聚合函数

聚合函数表示对 值的集合 进行操作的 组（集合）函数。

# 华为手机价格的平均值
SELECT AVG(price) FROM `products` WHERE brand = '华为';
# 计算所有手机的平均分
SELECT AVG(score) FROM `products`;
# 手机中最低和最高分数
SELECT MAX(score) FROM `products`;
SELECT MIN(score) FROM `products`;
# 计算总投票人数
SELECT SUM(voteCnt) FROM `products`;
# 计算所有条目的数量
SELECT COUNT(*) FROM `products`;
# 华为手机的个数
SELECT COUNT(*) FROM `products` WHERE brand = '华为';

Group By分组

1. GROUP BY通常和聚合函数一起使用，表示我们先对数据进行分组，再对每一组数据，进行聚合函数的计算
2. 聚合函数相当于默认将所有的数据分成了一组，我们前面使用avg还是max等，都是将所有的结果看成一组来计算的

# 根据品牌进行分组，计算各个品牌中：商品的个数、平均价格，最高价格、最低价格、平均评分
SELECT brand,
	COUNT(*) as count,
	ROUND(AVG(price),2) as avgPrice,
	MAX(price) as maxPrice,
	MIN(price) as minPrice,
	AVG(score) as avgScore
FROM `products` 
GROUP BY brand;

如果我们希望给Group By查询到的结果添加一些约束，那么我们可以使用：HAVING

# 如果我们还希望筛选出平均价格在4000以下，并且平均分在7以上的品牌
SELECT brand,
	COUNT(*) as count,
	ROUND(AVG(price),2) as avgPrice,
	MAX(price) as maxPrice,
	MIN(price) as minPrice,
	AVG(score) as avgScore
FROM `products` 
GROUP BY brand
HAVING avgPrice < 4000 and avgScore > 7;

多表查询

在实际开发中，我们会用很多表表示一条数据，比如商品表products记录商品基本信息，还有品牌表brand记录品牌信息等等。。。
外键

# 我们可以创建外键将两张表联系起来
# 创建表时添加外键，我们可以在最后加上
# 注释：外键brand_id引用brand表的id
FOREIGN KEY (brand_id) REFERENCES brand(id)
# 如果是表已经创建好，额外添加外键
ALTER TABLE `products` ADD `brand_id` INT;
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id);
# 关联起来
UPDATE `products` SET `brand_id` = 1 WHERE `brand` = '华为';
UPDATE `products` SET `brand_id` = 4 WHERE `brand` = 'OPPO';

外键存在时更新和删除数据，会报错

1. RESTRICT（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或删除；
2. NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的
3. CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：
   更新：那么会更新对应的记录；
   删除：那么关联的记录会被一起删除掉
4. SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL

# 查看创建表的信息
SHOW CREATE TABLE `products`;
# 先删除
ALTER TABLE `products` DROP FOREIGN KEY products_ibfk_1;
# 再增加
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id)
	ON UPDATE CASCADE
	ON DELETE CASCADE;

默认多表查询的结果

SELECT * FROM `products`, `brand`;

此时出现xy条数据，也就是笛卡尔乘积，也称之为直积，表示为 XY

多表之间链接

1. 左连接
   
2. 右链接
   
3. 内链接
   
4. 全链接
   

多对多查询

在开发中我们还会遇到多对多的关系：

• 比如学生可以选择多门课程，一个课程可以被多个学生选择；
• 这种情况我们应该在开发中如何处理呢？

先创建学生表与课程表

# 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT
);
# 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
price DOUBLE NOT NULL
);
# 加入数据
INSERT INTO `students` (name, age) VALUES('why', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);
INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);

我们需要一个关系表来记录两张表中的数据关系

# 创建关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE
);
# why 选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);

查询所有的学生选择的所有课程

SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
JOIN `courses` cs
ON ssc.course_id = cs.id; 

查询所有的学生选课情况

SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs
ON ssc.course_id = cs.id;

查询单个学生的课程

SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE stu.id = 1; 

lily同学选择了哪些课程(注意，这里必须用左连接，事实上上面也应该使用的是左连接)

SELECT
stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE stu.id = 5;

哪些学生是没有选课的

SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE cs.id IS NULL;

查询哪些课程没有被学生选择

SELECT
stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
RIGHT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
RIGHT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE stu.id IS NULL;