多表操作

首先定义两张表

	create table category(
	cid int primary key auto_increment,
	cname varchar(10),
	cdesc varchar(31)
	);
	insert into category values(null,'手机数码','电子产品');
	insert into category values(null,'鞋靴箱包','江南皮鞋厂');
	insert into category values(null,'香烟酒水','过年送礼');
	insert into category values(null,'馋嘴零食','卫龙');
	insert into category values(null,'酸奶饼干','好吃');

	create table product(
	pid int primary key auto_increment,
	pname varchar(10),
	price double,
	pdate timestamp,
	cno int
	);
	insert into product values(null,'小米',998,null,1);
	insert into product values(null,'锤子',2888,null,1);
	insert into product values(null,'Nike',1998,null,2);
	insert into product values(null,'中华',98,null,3);
	insert into product values(null,'劲酒',38,null,3);
	insert into product values(null,'卫龙',2,null,4);
	insert into product values(null,'安慕希',68,null,5);
	insert into product values(null,'小熊饼干',3,null,5);

多表之间的关系如何来维护

• 外键约束 ： foreign key
  • 给 product 中的 cno 添加一个外键约束

  alter table product add foreign key(cno) references category(cid);	
  

建数据库原则

• 通常情况下，一个项目/应用建一个数据库

多表之间的建表原则

• 一对多 ：如商品和分类
  • 建表原则：在多的一方添加一个外键指向一的一方的主键
• 多对多 ：如老师和学生
  • 建表原则：建立一张中间表，将多对多的关系，拆分成一对多的关系，中间表至少要有两个外键，分别指向原来的那两张表
• 一对一 ：公民和身份证
  • 建表原则：
    • 将一对一的情况，当做是一对多的情况处理，在任意一张表添加一个外键，并且这个外键要唯一，指向另一张表
    • 直接将两张表合并成一张表
    • 将两张表的主键建立起连接，让两张表里面主键相等
  • 实际用途：用的不是很多.(拆表操作)
    • 个人信息：姓名，性别，年龄，身高，体重，职业（收入，爱好，特长）
    • 拆表操作：将个人的常用信息和不常用信息拆分开来，减少表的臃肿

多表搭建实例

用户表（用户的ID，用户名，密码，手机）

	create table user(
	 uid int primary key auto_increment,
	 username varchar(31),
	 password varchar(31),
	 phone varchar(31)
	);
	insert into user values(1,'xcz','123','18838888888');

订单表（订单编号，总价，订单时间，地址，外键用户的ID）

	create table orders(
		oid int primary key auto_increment,
		sum int not null,
		otime timestamp,
		address varchar(50),
		uno int,
		foreign key(uno) references user(uid)
	);
	insert into orders values(1,200,null,'school',1);
	insert into orders values(2,300,null,'jia',1);

商品表（商品ID，商品名称，商品价格，外键cno）

create table product(
	pid int primary key auto_increment,
	pname varchar(10),
	price double,
	cno int,
	foreign key(cno) references category(cid)
);
insert into product values(null,'小米',998,1);
insert into product values(null,'锤子',2888,1);
insert into product values(null,'Nike',1998,2);
insert into product values(null,'中华',98,3);
insert into product values(null,'劲酒',38,3);
insert into product values(null,'卫龙',2,4);
insert into product values(null,'安慕希',68,5);
insert into product values(null,'小熊饼干',3,5);

订单项：中间表（订单ID,商品ID，商品数量，订单项总价）

create table orderitem(
	ono int,
	pno int,
	foreign key(ono) references orders(oid),
	foreign key(pno) references product(pid),
	ocount int,
	subsum double
);
---给1号订单添加200块商品
insert into orderitem values(1,6,85,170);
insert into orderitem values(1,8,10,30);
---给2号订单添加250块商品
insert into orderitem values(2,8,80,240);
insert into orderitem values(2,6,5,10);

商品分类表（分类ID，分类名称，分类描述）

create table category(
cid int primary key auto_increment,
cname varchar(15),
cdesc varchar(100)
);
insert into category values(null,'手机数码','电子产品');
insert into category values(null,'鞋靴箱包','江南皮鞋厂');
insert into category values(null,'香烟酒水','过年送礼');
insert into category values(null,'馋嘴零食','卫龙');
insert into category values(null,'酸奶饼干','好吃');

主键约束和唯一约束

主键约束：

• 不能为空，必须是唯一的
• 外键都是指向另一张表的主键
• 主键一张表只能有一个

唯一约束：

• 列里面的内容必须唯一，不能出现重复的情况，可以为空
• 唯一约束不可以作为其他表的外键
• 可以有多个唯一约束

多表查询

• 交叉连接查询 笛卡尔积
  • 笛卡尔积
    SELECT * FROM product,category;

内连接查询

• 隐式内连接

		SELECT * FROM product AS p,category AS c WHERE p.cno=c.cid;

• 显示内连接

		SELECT * FROM product p INNER JOIN category c ON p.cno=c.cid;

区别：
- 隐式内连接：在查询出结果的基础上去做的where 条件过滤
-显示内连接：带着条件去查询结果，执行效率要高

• 左外连接

		select * from product p left outer join category c on p.cno=c.cid;

会将左表中的所有数据都查询出来，如果右表中没有对应的数据，用 NULL 代替

• 右外连接

		select * from product p right outer join category c on p.cno=c.cid;

会将右表中的所有数据都查询出来，如果左表中没有对应的数据，用 NULL 代替

分页查询

• 每页数据3条
• 起始索引从0开始
• 第一页起始索引为0，第二页为4
• 起始索引用算法：
  • index 代表显示第几页,页数从1开始
  • 每页显示三条数据
  • startindex = (index-1)*3

 select * from product limit 0,3;

第一个参数是索引，第二个参数是个数

子查询

• 查询分类名称为手机数码的所有商品
  - 1.查询分类名称为手机数码的 ID
  SELECT cid FROM category WHERE cname=‘手机数码’;
  - 2.得出 ID 为1 的结果
  SELECT * FROM product WHERE cno =1;

select * from product where cno = (select cid from product where cname='手机数码');

• 查询出（商品名称，商品分类名称）信息
  • 左连接
  • select p.pname,c.cname from product p left outer join category c on p.cno=c.cid;
  • 子查询
  • select pname,cno from product;
  • select cname from category c where p.cno=c.cid;

select pname,(select cname from category  c where p.cno=c.cid) from product p;