day09--MySQL多表&jdbc

第一章 商城系统表结构实现：

1.1 需求分析：

商城系统表结构的实现，现提供表关系图，如下：

通过对上图内容的查看，整个案例中将使用5张表，且表与表之间存在一定的关系，接下来我们将一起学习sql的多表操作。

1.2 外键：

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


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

外键特点：

• 从表外键的值是对主表主键的引用。
• 从表外键类型，必须与主表主键类型一致。

声明外键约束：
语法：

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

[外键名称] 用于删除外键约束的，一般建议"_fk"结尾
alter table 从表 drop foreign key 外键名称

使用外键目的：

• 保证数据完整性

1.3 表与表之间的关系：

表与表之间的关系，说的就是表与表数据之间的关系。

1、一对多关系：

• 常见实例：客户和订单，分类和商品，部门和员工。
• 一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。

2、多对多关系：

• 常见实例：学生和课程，商品和订单，人和角色。
• 多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。（就是将一个多对多拆分成两个一对多）
• 两张表分别都是主表，第三张表为从表，提供两个字段，都是外键。
  

3、一对一关系：（了解）

• 在实际的开发中应用不多，因为一对一可以创建一张表。
• 两种建表原则：
  外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 unique。
  外键是主键：主表的主键和从表的主键，形成主外键关系。

1.4 创建表实现：

1.4.1 一对多：分类和商品

	#创建数据库
	create database day09_db;
	#使用数据库
	use day09_db;

	###创建分类表
	create table category(
		cid varchar(32) primary key,
		cname varchar(100)
	);
	###商品表
	create table product(
		pid varchar(32) primary key,
		name varchar(40),
		price double
	);
	
	#添加外键字段 category_id
	alter table product add column category_id varchar(32);
	#添加约束
	alter table product add constraint product_fk foreign key (category_id) references category (cid);

总结：

• 从表不能够添加（更新），主表中不存在的数据。
• 从表不能够删除（更新），从表中已经使用的数据。

1.4.2 多对多：订单和商品

	### 订单表
	create table orders(
		oid varchar(32) primary key,
		totalprice double #总计
	);
	
	### 订单项表
	create table orderitem(
		oid varchar(50), --订单id
		pid varchar(50)  --商品id
	);
	### 联合主键（可省略）
	later table orderitem add primary key (oid,pid);
	
	### 订单表和订单项表的主外键关系
	alter table orderitem add constraint orderitem orders_fk foreign key (oid) references orders(oid);
	### 商品表和订单项表的主外键关系
	alter table orderitem add constraint orderitem_product_fk foreign key (pid) references products (pid);

1.5 查询操作

1.5.1 操作：添加

# 分类
insert into category(cid,cname) values('c001','家电');
insert into category(cid,cname) values('c002','服饰');

1.5.2 操作：查询

1.5.2.1 多表查询

1、交叉连接查询（基本不会使用–得到的是两个表的乘机）

	select * from a,b;

2、内连接查询（使用的关键字 inner join --inner可以省略）

隐式内连接：
sql select * from a,b where 条件;
显示内连接：
sql select * from a inner b on 条件;
3、外连接查询（使用的关键字 outer join --outer可以省略）

左外连接：left outer join

select * from a left outer join b on 条件;

右外连接：right outer join

select * from a right outer join b on 条件;

	# 1、查询哪些分类的商品已经上架
	select * from category,products where cid=category_id;
	select * from category c,products p where c.cid=p.category_id;

1.5.2.2 子查询：
子查询：一条select 语句结果作为另一条select 语法一部分（查询条件，查询结果，表等）

# 子查询，查询“化妆品”分类上架商品详情
# 隐式内连接
select * from products p,category c where p.category_id=c.cid adn cname="化妆品";

# 子查询
select * from products where category_id=(select cid from category where cname="化妆品");