mysql 11关系图_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);