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');