数据库_mysql多表操作

Java学习

针对Java初学者以及自学者！

公告通知

如果你是初学者，或者是自学者！你可以加小编微信（xxf960513）！小编可以给你学习上，工作上的一些建议以及可以给你(免费)提供学习资料！最重要我们还可以交个朋友！你在学习上有什么问题都可以加小编微信进行私聊！小编都会为你解答！

注:本公众号纯属个人公益公众号！不存在任何收费项目！免费分享所有学习资料！只求大家多多支持！多多关注！！

多表操作

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

1.1    表与表之间的关系

l  一对多关系：

n  常见实例：客户和订单，分类和商品，部门和员工.

n  一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.

l  多对多关系：

n  常见实例：学生和课程、用户和角色

n  多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

l  一对一关系：(了解)

n  在实际的开发中应用不多.因为一对一可以创建成一张表.

n  两种建表原则：

u  外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。

u  外键是主键：主表的主键和从表的主键，形成主外键关系。

 

1.2    外键约束

现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键

         此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

         外键特点：

u  从表外键的值是对主表主键的引用。

u  从表外键类型，必须与主表主键类型一致。

 

l  声明外键约束       

语法：alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称]用于删除外键约束的，一般建议“_fk”结尾

    altertable 从表 drop foreignkey 外键名称

l  使用外键目的：

n  保证数据完整性

 

 

1.3    一对多操作

1.3.1    分析

• category分类表，为一方，也就是主表，必须提供主键cid

• products商品表，为多方，也就是从表，必须提供外键category_id

 

1.3.2    实现：分类和商品

 

###创建分类表

create table category(

  cidvarchar(32) PRIMARY KEY ,

  cnamevarchar(100)        #分类名称

);

 

# 商品表

CREATE TABLE `products` (

  `pid`varchar(32) PRIMARY KEY  ,

  `name`VARCHAR(40) ,

  `price`DOUBLE

);

 

#添加外键字段

alter table products add column category_id varchar(32);

 

#添加约束

alter table products add constraint product_fkforeign key (category_id) references category (cid);

 

1.3.3    操作

#1 向分类表中添加数据

INSERT INTO category (cid ,cname) VALUES('c001','服装');

 

#2 向商品表添加普通数据,没有外键数据，默认为null

INSERT INTO products (pid,pname) VALUES('p001','商品名称');

 

#3 向商品表添加普通数据，含有外键信息(数据存放在)

INSERT INTO products (pid ,pname ,category_id)VALUES('p002','商品名称2','c001');

 

#4 向商品表添加普通数据，含有外键信息(数据不存在) -- 不能异常

INSERT INTO products (pid ,pname ,category_id)VALUES('p003','商品名称2','c999');

 

#5 删除指定分类(分类被商品使用) -- 执行异常

DELETE FROM category WHERE cid = 'c001';

 

1.4    多对多

1.4.1    分析

• 商品和订单多对多关系，将拆分成两个一对多。

• products商品表，为其中一个一对多的主表，需要提供主键pid

• orders 订单表，为另一个一对多的主表，需要提供主键oid

• orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid

 

 

1.4.2    实现：订单和商品

 

### 商品表[已存在]

 

### 订单表

create table `orders`(

  `oid`varchar(32) PRIMARY KEY ,

 `totalprice` double   #总计

);

 

### 订单项表

create table orderitem(

  oidvarchar(50),-- 订单id

  pidvarchar(50)-- 商品id

);

 

###---- 订单表和订单项表的主外键关系

alter table `orderitem` add constraintorderitem_orders_fk foreign key (oid) references orders(oid);

 

###---- 商品表和订单项表的主外键关系

alter table `orderitem` add constraintorderitem_product_fk foreign key (pid) references products(pid);

 

### 联合主键（可省略）

alter table `orderitem` add primary key (oid,pid);

 

 

1.4.3    操作

#1 向商品表中添加数据

INSERT INTO products (pid,pname) VALUES('p003','商品名称');

 

#2 向订单表中添加数据

INSERT INTO orders (oid ,totalprice)VALUES('x001','998');

INSERT INTO orders (oid ,totalprice)VALUES('x002','100');

 

#3向中间表添加数据(数据存在)

INSERT INTO orderitem(pid,oid)VALUES('p001','x001');

INSERT INTO orderitem(pid,oid)VALUES('p001','x002');

INSERT INTO orderitem(pid,oid)VALUES('p002','x002');

 

#4删除中间表的数据

DELETE FROM orderitem WHERE pid='p002' AND oid ='x002';

 

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

INSERT INTO orderitem(pid,oid)VALUES('p002','x003');

 

#6删除商品表的数据 -- 执行异常

DELETE FROM products WHERE pid = 'p001';