Java第二十六天（mysql多表操作）

SQL创建多表及多表的关系

多表间关系的维护

外键约束：foreign key

		给product中的这个cno添加一个外键约束
		
		alter table product add foreign key(cno) references category(cid);

		再插入cid不匹配的数据会失败

	从分类表中删除分类为5的信息
	delete from category where cid=4;//删除失败
	首先得去product表中删除分类id为4的商品

建数据库原则

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

多表之间的建表原则

• 一对多：商品和分类

    建表原则：在多的一方添加一个外键指向一的一方的主键
  

• 多对多：老师和学生，学生和课程

    建表原则：多建一张中间表，将多对多的关系拆成一对多的关系，中间至少要有两个外键，这两个外键分别指向原来的那张表
  

• 一对一

    建表原则：
    	将一对一的情况，当作是1对多的情况处理，在任意一张表添加一个外键，并且这个外键唯一，指向另一张表
    	直接将两张表合并成一张表
    	将两张表主键建立起链接，让两张表里面主键相等
    实际用途：用的不是很多（拆表操作）
    	相亲网站：
    		个人信息：姓名，性别，年龄，身高，体重，三维，兴趣爱好（年收入、特长、学历、职业、择偶目标）
    		拆表操作：将个人信息的常用信息和不常用信息拆分出来，减少表的臃肿
  

网上商城表实例分析：用户购物流程

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

create table user(
	uid int primary key auto_increment,
	username varchar(31),
	password varchar(31),
	phone varchar(11)
);

insert into user values(1,'Alsa','123','15822224444');

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

create table orders(
	oid int primary key auto_increment,
	sum int not null,
	otime timestamp,
	address varchar(100),
	uno int,
	foreign key(uno) references user(uid)
);

insert into orders values(1,200,null,'jingsu',1);
insert into orders values(2,1176,null,'lianyungang',1);

商品表（商品ID，商品名称，商品价格，外键cno）[先建商品分类]

create table product(
	pid int primary key auto_increment,
	pname varchar(30),
	price double,
	cno int,
	foreign key(cno) references category(cid)
);

insert into product values(null,'Wuliangye',998,4);
insert into product values(null,'Cocktail',6,4);
insert into product values(null,'Jenny Bakery',5,3);
insert into product values(null,'Huawei Glory v10',2000,5);
insert into product values(null,'Dress',168,1);
insert into product values(null,'High-heeled shoes',400,2);
insert into product values(null,'Sandals',100,2);
insert into product values(null,'Stewed Chicken Point',42,3);
insert into product values(null,'Mousse Cake',58,3);
insert into product values(null,'Samsung S10',5000,5);
insert into product values(null,'shirt',120,1);

订单项：中间表（订单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,7,1,100);
insert into orderitem values(1,9,1,58);
insert into orderitem values(1,8,1,42);
	给2号订单添加1176元商品
insert into orderitem values(2,1,1,998);
insert into orderitem values(2,3,2,10);
insert into orderitem values(1,5,1,168);

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

create table category(
	cid int primary key auto_increment,
	cname varchar(15),
	cdesc varchar(100)
);

insert into category values(null,'clothes','Clothes that can not be trimmed are not good fabrics');
insert into category values(null,'shoes','Walking in all directions begins at the foot');
insert into category values(null,'food','Make a tasteful meal');
insert into category values(null,'Beverages','A little tightening makes life better');
insert into category values(null,'Digital','Look at the world without going out');

主键约束：默认是指向另一张表的主键

外键都是指向另外一张表的主键
主键一张表只能有一个

唯一约束：列里面的内容，必须是唯一，不能重复出现重复，可以为空

唯一约束不能作为其他表的外键
可以有多个唯一约束

多表查询

交叉连接查询 笛卡尔积

笛卡尔积，查出来是两张表的乘积结果没有意义
select * from product,category;

过滤出有意义的数据
select * from product,category where cno=cid;
select * from product as p,category as c where p.cno=c.cid;
select * from product p,category c where p.cno=c.cid;

内连接查询

隐式内连接
select * from product p,category c where p.cno=c.cid;
显式内连接(inner可省略)
select * from product p inner join category c on p.cno=c.cid;
区别：
	隐式内连接是在查询出结果的基础上去做的where条件过滤
	显示内连接是带着条件去查询结果，执行效率要高。

数据准备：在product中插入一个没有对应cid的数据

insert into product values(null,'flowers',40,null);

左外连接(outer可省略)

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

数据准备：在category中插入一行数据

insert into category values(100,'birds','Birds twitter and fragrance of flowers');

右外连接(outer可省略)

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

分页查询

每页数据10条，起始索引1

select * from product limit 0,3;
select * from product limit 3,3;
第一个参数是数据索引，第二个参数是显示的个数
起始索引：index代表显示第几页,页数从1开始
每页显示3条数据
每页开始的数据索引startindex=(index-1)*3

子查询（了解）

查询出（商品名称，商品分类名称）信息

左连接(outer可省略)
select p.pname,c.cname from product p left outer join category c on p.cno=c.cid;
子查询
select pname,(select cname from category c where p.cno = c.cid) from product p;

查询出分类名称为手机数码的所有商品

select cid from category where cname='Beverages';
select * from product where cno = (select cid from category where cname='Beverages');

转载于:https://my.oschina.net/u/4110331/blog/3068248