数据库之SQL语句（二）
多表的设计-外键
- 创建brand表
CREATE TABLE IF NOT EXISTS `brand` (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
websit VARCHAR(100),
phoneRank INT
)
- 添加一个 brand_id字段
ALTER TABLE `product` ADD `brand_id` INT;
- 修改 brand_id的外键
ALTER TABLE `product` ADD FOREIGN KEY(brand_id) REFERENCES brand(id)
- 设置brand——id的值
UPDATE product SET brand_id = 1 WHERE brand=‘华为’
- 修改和删除外键引用ID
执行会报错，不能改也不能删除
//报错
UPDATE brand SET id = 100 WHERE id = 1;
- 修改brand_id 关联外键的action
- 获取到目前外键的名称
SHOW CREATE TABLE product;
-- CREATE TABLE `product` (
-- `id` int NOT NULL AUTO_INCREMENT,
-- `brand` varchar(20) DEFAULT NULL,
-- `title` varchar(100) NOT NULL,
-- `price` double NOT NULL,
-- `score` decimal(2,1) DEFAULT NULL,
-- `voteCnt` int DEFAULT NULL,
-- `url` varchar(100) DEFAULT NULL,
-- `pid` int DEFAULT NULL,
-- `brand_id` int DEFAULT NULL,
-- PRIMARY KEY (`id`),
-- KEY `brand_id` (`brand_id`),
-- CONSTRAINT `product_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
- 根据名称将为案件删除掉
ALTER TABLE `product` DROP FOREIGN KEY product_ibfk_1;
- 重新添加我们的外检约束
ALTER TABLE `product` ADD FOREIGN KEY(brand_id) REFERENCES brand(id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
- 测试修改主表外键表
父表也会改变
UPDATE brand SET id = 100 WHERE id = 1;
附 ：外键更新或者删除时设置几个值：
-
RESTRICT（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或删除； -
NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的； -
CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：
更新：那么会更新对应的记录；
删除：那么关联的记录会被一起删除掉； -
SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL；
多表的查询
# 108 * 4 = 648 笛卡尔乘积
SELECT * FROM product,brand;
# 获取到笛卡尔乘积进行筛选
SELECT * FROM product,brand WHERE product.brand_id = brand.id;
多表之间的连接.png
- 左连接
- 查询所有手机（包括没有品牌信息的手机）以及对应品牌 null (全部A) LEFT : LEFT OUT JOIN
SELECT * FROM product LEFT JOIN `brand` ON product.brand_id = brand.id
- 查询没有对应品牌数据的手机 (除了交集区域)
SELECT * FROM product LEFT JOIN `brand` ON product.brand_id = brand.id WHERE brand.id IS NULL;
-
右连接
1 . 查询所有品牌（没有对应的数据，品牌也显示）以及对应 的手机数据 (全部B) RIGHT : RIGHT OUT JOIN
SELECT * FROM product RIGHT JOIN `brand` ON product.brand_id = brand.id
2 查询没有对应品牌数据的手机 (除了交集区域)
SELECT * FROM product RIGHT JOIN `brand` ON product.brand_id = brand.id WHERE product.brand_id IS NULL;
- 内连接
SELECT * FROM product JOIN brand ON product.brand_id = brand.id;
SELECT * FROM product JOIN brand ON product.brand_id = brand.id WHERE price = 8699;
-
全连接
mysql不支持 FULL OUTER JOIN
SELECT * FROM product FULL OUTER JOIN brand ON product.brand_id = brand.id;
- 联合 (A B 中间不为null)
(SELECT * FROM product LEFT JOIN `brand` ON product.brand_id = brand.id)
UNION
(SELECT * FROM product RIGHT JOIN `brand` ON product.brand_id = brand.id)
- 联合（A B 中间为null）
(SELECT * FROM product LEFT OUTER JOIN `brand` ON product.brand_id = brand.id WHERE brand.id IS NULL)
UNION
(SELECT * FROM product RIGHT OUTER JOIN `brand` ON product.brand_id = brand.id WHERE product.brand_id IS NULL)
多对多查询_设计
- 创建students表
CREATE TABLE IF NOT EXISTS studens (
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
)
- 建立关系表
CREATE TABLE IF NOT EXISTS `students_select_courses` (
id INT AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE,
)
- 学生选课
-- 选择了 英语 数学 历史
INSERT INTO `students_select_courses` (student_id,course_id) VALUES (1,3);
INSERT INTO `students_select_courses` (student_id,course_id) VALUES (1,4);
INSERT INTO `students_select_courses` (student_id,course_id) VALUES (2,1);
INSERT INTO `students_select_courses` (student_id,course_id) VALUES (2,2);
- 查询需求
-- 有谁选择了课程，选择了那些课程 两次内连接
-- 内连接： INNER JOIN || CROSS JOIN || JOIN
//第一种 ，查询students内部的所有符合的数据
SELECT * FROM `students` JOIN `students_select_courses` ON students.id = students_select_courses.student_id;
//第二种，查询需要的数据
SELECT stu.id id,stu.name stuName,stu.age stuAge,cs.id csId,cs.name csName,cs.price csPrice
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 id,stu.name stuName,stu.age stuAge,cs.id csId,cs.name csName,cs.price csPrice
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 id,stu.name stuName,stu.age stuAge,cs.id csId,cs.name csName,cs.price csPrice
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 id,stu.name stuName,stu.age stuAge,cs.id csId,cs.name csName,cs.price csPrice
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;
- 查询某个学生选择了那门课
SELECT stu.id id,stu.name stuName,stu.age stuAge,cs.id csId,cs.name csName,cs.price csPrice
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= 1;
对象和数组类型
- 将联合查询到的数据转成对象{}(
JSON_OBJECT) 一对多
SELECT product.id id,product.title,product.price price,
JSON_OBJECT('id',brand.id,'name',brand.name,'websit',brand.websit) brand
FROM product
LEFT JOIN brand ON product.id = brand.id;
- 将查询到的多条数据，组织成对象，放到数组中转换成数组对象(
JSON_ARRAYAGG(JSON_OBJECT()))
SELECT stu.id,stu.name,stu.age,
JSON_ARRAYAGG(JSON_OBJECT('id',cs.id,'name',cs.name,'price',cs.price))
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
GROUP BY stu.id;
