数据库之SQL语句（二）

数据库之SQL语句（一）

多表的设计-外键

• 创建brand表

CREATE TABLE IF NOT EXISTS `brand` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    websit VARCHAR(100),
    phoneRank INT
)

1. 添加一个 brand_id字段

ALTER TABLE `product` ADD `brand_id` INT;

2. 修改 brand_id的外键

ALTER TABLE `product` ADD FOREIGN KEY(brand_id)  REFERENCES brand(id)

3. 设置brand——id的值

UPDATE  product SET brand_id = 1 WHERE brand=‘华为’

• 修改和删除外键引用ID
  执行会报错，不能改也不能删除

//报错
UPDATE brand SET id = 100 WHERE id = 1;

• 修改brand_id 关联外键的action

1. 获取到目前外键的名称

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

2. 根据名称将为案件删除掉

ALTER TABLE `product` DROP FOREIGN KEY product_ibfk_1;

3. 重新添加我们的外检约束

ALTER TABLE `product` ADD FOREIGN KEY(brand_id) REFERENCES brand(id)
ON UPDATE CASCADE
ON DELETE RESTRICT;

4. 测试修改主表外键表
   父表也会改变

UPDATE brand SET id = 100 WHERE id = 1;

附 ：外键更新或者删除时设置几个值：

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

多表的查询

# 108 * 4 = 648 笛卡尔乘积 
SELECT * FROM product,brand;

# 获取到笛卡尔乘积进行筛选
SELECT * FROM product,brand WHERE product.brand_id = brand.id;

多表之间的连接.png

• 左连接

1. 查询所有手机（包括没有品牌信息的手机）以及对应品牌 null (全部A) LEFT : LEFT OUT JOIN

SELECT * FROM product LEFT JOIN `brand` ON product.brand_id = brand.id

2. 查询没有对应品牌数据的手机 (除了交集区域)

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;

1. 联合 (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)

2. 联合（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;