一对一、join 查询、级联方式
– 新建 user 表
CREATE TABLE hello-mysql
.user
(id
INT NOT NULL AUTO_INCREMENT COMMENT ‘id’,name
VARCHAR(45) NOT NULL COMMENT ‘名字’,
PRIMARY KEY (id
)
);
– 新建 id_card 表
CREATE TABLE id_card
(id
int NOT NULL AUTO_INCREMENT COMMENT ‘id’,card_name
varchar(45) NOT NULL COMMENT ‘身份证号’,user_id
int DEFAULT NULL COMMENT ‘用户 id’,
PRIMARY KEY (id
),
INDEX card_id_idx
(user_id
),
CONSTRAINT user_id
FOREIGN KEY (user_id
) REFERENCES user
(id
)
) CHARSET=utf8mb4;
– 插入数据
INSERT INTO user
(name
)
VALUES
(‘张三’),
(‘李四’),
(‘王五’),
(‘赵六’),
(‘孙七’),
(‘周八’),
(‘吴九’),
(‘郑十’),
(‘钱十一’),
(‘陈十二’);
– 查询
SELECT * FROM user
;
– 插入 id_card 表数据
INSERT INTO id_card (card_name, user_id)
VALUES
(‘110101199001011234’,1),
(‘310101199002022345’,2),
(‘440101199003033456’,3),
(‘440301199004044567’,4),
(‘510101199005055678’,5),
(‘330101199006066789’,6),
(‘320101199007077890’,7),
(‘500101199008088901’,8),
(‘420101199009099012’,9),
(‘610101199010101023’,10);
SELECT * FROM id_card;
– 一对一查询 join on 其实默认是 inner join on,只返回2个表中能关联上的数据
SELECT * FROM user
JOIN id_card ON user.id = id_card.user_id;
SELECT user.id,name,id_card.id as card_id, card_name FROM user INNER JOIN id_card ON user.id = id_card.user_id;
– left join 是额外返回左表中没有关联上的数据
– right join 是额外返回右表中没有关联上的数据
– 在 FROM 后的左表, JOIN 后表是右表。
SELECT user.id, name, id_card.id as card_id,card_name FROM user RIGHT JOIN id_card ON user.id = id_card.user_id;
SELECT user.id, name, id_card.id as card_id,card_name FROM user LEFT JOIN id_card ON user.id = id_card.user_id;
– MySQL 中的外键约束定义了当主表(被引用表)发生更新或删除操作时,从表(引用表)应该如何响应
– CASCADE: 主表主键更新,从表关联记录的外键跟着更新,主表记录删除,从表关联记录删除
– SET NULL:主表主键更新或者主表记录删除,从表关联记录的外键设置为 null
– RESTRICT:只有没有从表的关联记录时,才允许删除主表记录或者更新主表记录的主键 id
– NO ACTION: 同 RESTRICT,只是 sql 标准里分了 4 种,但 mysql 里 NO ACTION 等同于 RESTRICT。