java的mysql多表操作_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');