MySQL（十一）-多表查询（上）

多表关系

1 一对一

方式1：通过修改表结构添加外键
修改表结构： ALTER TABLE 当前表名 ADD CONSTRAINT FOREIGN KEY(id) REFERENCES 被关联表名 (id);

方式1：通过修改表结构添加外键 
-- 用户和资料表 
-- 用户表 
CREATE TABLE customer ( 
      id INT PRIMARY KEY AUTO_INCREMENT, 
      user_name VARCHAR(50) 
);
-- 资料表 
CREATE TABLE extension( 
      id INT PRIMARY KEY AUTO_INCREMENT, 
      info VARCHAR(200) 
);
-- 外键(主键)关联主键 
ALTER TABLE extension 
      ADD CONSTRAINT FOREIGN KEY(id) REFERENCES customer(id); 

方式2： 在创建表时候，直接建立外键 
-- 用户表同上 
-- 资料表 
      --创建字段关联id 
CREATE TABLE extension2( 
      id INT PRIMARY KEY AUTO_INCREMENT, 
      info VARCHAR(200), 
      cid INT UNIQUE, 
      foreign key(cid) REFERENCES customer(id) );

注意：物理外键（主）和逻辑外键

-- 测试数据 
insert into customer values(1,'张三'); 
insert into extension values(1,'开发工程师'); 

insert into extension values(2,'测试工程师'); -- 报错，资料表时依赖于用户表 

insert into customer values(1,'李四'); 
insert into extension values(2,'测试工程师'); -- 成功 

insert into extension2 values(1,'开发工程师',100); -- customer中没有id=100

注意：auto_increment:如果没有指定编号，那么根据当前表中最大编号按照递增顺序编号，即使数据
被删除，该编号也不会被再次利用

方式2:通过建表时,添加外键约束

创建表时候增加字段，用于外键约束：

• 创建新字段 INT UNIQUE
• foreign key(当前表字段名) REFERENCES 被关联表(id)

2 一对多关系

方式1：在多的一方表中增加字段，去关联少的一方的主键

-- 商店表 
CREATE TABLE shop( 
      id INT PRIMARY KEY, 
      sname VARCHAR(100) 
);
-- 商品表 
CREATE TABLE goods( 
      id INT PRIMARY KEY auto_increment,
      gname VARCHAR(100), 
      price DOUBLE, 
      sid INT, 
      foreign key(sid) REFERENCES shop(id)
 );
-- 在商店表中插入具体数据 
insert into shop values(1,"华天"),(2,"好再来"); 

-- 向商品表中增加数据 
insert into goods values(1,'沙发',3999.9,1),(0,"电视",4888.8,1); --成功 i

nsert into goods values(0,'笔记本',3999.9,3); -- 失败，shop不存在该编号

方式2：新建一个关系表，用于关联之前两个表的各自主键。

-- 商店表 
CREATE TABLE shop( 
      id INT PRIMARY KEY, 
      sname VARCHAR(100) 
);
-- 商品表 
CREATE TABLE goods2( 
      id INT PRIMARY KEY auto_increment, 
      gname VARCHAR(100), 
      price DOUBLE );
-- 新建关系表 
create table shop_goods( 
      id int primary key, 
      shop_id int, 
      goods_id int unique, 
      foreign key(shop_id) references shop(id), 
      foreign key(goods_id) references goods2(id) );

-- 向goods2表中插入数据 
insert into goods2 values (1,'沙发',999.9),(2,"电视",1999),(3,'笔记本',8999); 

-- 向关系表中增加数据 
insert into shop_goods values(1,2,3); --成功 
insert into shop_goods values(2,2,3); -- 失败，同一个商品是能由一家卖 

-- 向shop添加数据 
insert into shop values (3,"家乐福");

insert into shop_goods values(2,3,2); -- 成功 
insert into shop_goods values(3,300,1); -- 失败，没有编号为300的店

3 多对多
创建第三方表用于记录各自主键

• 学生和学科
• 商品类型和商品

-- 创建学生表 
create table students( 
      id int primary key auto_increment not null, 
      s_name varchar(10) 
);
-- 创建学科表 
create table courses( 
      id int primary key auto_increment not null, 
      c_name varchar(10) 
);
--第三张表 
create table stu_cos( 
      id int primary key auto_increment not null, 
      s_id int, 
      c_id int, 
      foreign key(s_id) REFERENCES students(id), 
      foreign key(c_id) references courses(id)
);

-- 向学生表和课程表中插入数据 
insert into students values(1,'张三'),(2,"李四"),(3,"王五"),(4,"马六"); 
insert into courses values(1,"python"),(2,'java'),(3,"c++"),(4,'web'); 

-- 向关系表中插入数据 
insert into stu_cou values 
      (1,1,1),
      (2,3,3), 
      (3,2,1),
      (4,1,2), 
      (5,2,2),
      (6,4,4), 
      (7,4,3),
      (8,3,1);