SQL基础简练 (二)

多对多SQL关系简解 （结尾有春节小惊喜！）
分析:

多对多我们可以拆分成两个一对多去做,根据我们之前的分析如果创建多对多关系的话必须有两个实体表和一个中间表
中间表和两个实体表之前保持多对一关联;(如京东商品与商品品牌表的关系、如学生与班级的关系)

– 创建用户表

CREATE TABLE user_info (
u_id INT (8) NOT NULL PRIMARY KEY auto_increment,
u_name VARCHAR (32)
) – 创建角色表
CREATE TABLE rple_info (
r_id INT (8) NOT NULL PRIMARY KEY auto_increment,
r_name VARCHAR (32),
r_desc VARCHAR (200)
)

– 中间表

CREATE TABLE middle_user_role (user_id INT, role_id INT)

– 创建中间表和用户表外键

ALTER TABLE middle_user_role ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES user_info (u_id);

– 创建中间表和角色表外键

ALTER TABLE middle_user_role ADD CONSTRAINT FOREIGN KEY (role_id) REFERENCES rple_info (r_id);

– 为什么要设置两张主键？？？？？？？？
– 设置联合主键(一张表没有主键不好)可省略

ALTER TABLE middle_user_role ADD PRIMARY KEY (user_id, role_id );

– 测试多对多关系、加载数据

insert into user_info (u_name) values (‘李磊’),(‘韩梅梅’),(‘赵文明’);
INSERT into rple_info values (null,‘普通学生’,‘梦想成为班长王的loser’);
INSERT into rple_info values (null,‘计算机课代表’,‘班级中计算机技术王者般的存在’);
INSERT into rple_info values (null,‘班长’,‘已经成为班长王的VIP’);

– 删除多余的数据

DELETE FROM user_info WHERE u_id IN (4,5,6);
insert into middle_user_role values (1,1),(2,1),(3,1),(2,2),(3,3);

– 删除中间表的数据，取消赵文明的班长职务

解析:人名表id:3代表赵文明，角色表id:3代表班长职务

DELETE FROM middle_user_role WHERE user_id=3 AND role_id = 3;

– 向中间表添加数据(数据不存在) – 执行异常

解析:中间表的添加只能添加外键已有的，不能无中生有

INSERT INTO middle_user_role VALUES (250,250);

– 删除用户表或者角色表数据 、 执行异常

解析:当存在外键且外键正在使用时也是无法删除个体表的数据的！

DELETE FROM user_info WHERE u_id = 1;

注意:

主外键约束除了可以帮助我们保证数据的完整性,同时也限制了数据操作的灵活性,此处大家要注意自己公司的具体要求,
如果要求数据完整那就必须创建主外键约束如果要求保证操作灵活那么除了主键约束外不可以创建外键约束,大家要根据具体需求自己把控;

新年思考！

1. 配置MySQL需要注意哪几个问题？
2. 如何使用root账号登录MySQL?
3. 创建表、查看表、查看表定义、删除表的SQL语句是什么？
4. 请列举现实生活中存在关系的实体,以及他们的关系;
   答案参考:下一章《mySQL用户与授权》