2

2

数据库(二)

---

多表操作 1.多表之间如何操作 select [distinct] [*] [列名,列名2] from 表名 [where 条件] distinct: 去除重复的数据 --商品分类: 手机数码, 皮靴箱包 1.分类ID 2.分类名称 .分类描述 Create table category (     cid int primary key auto_increment,     cname varchar(10),     cdesc varchar(0) ); 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 * form category; select cname,cdesc from category; --所有商品 1.商品ID 2.商品名称 .商品价格 4.商品数量 5.生产日期 (时间戳,默认当前时间) 6.商品分类ID 商品和商品分类: 所属关系 create table product(     pid int primary key auto_increment,     pname varchar(10) ,     price double,     pdate timestamp,            cno int ); insert into product values(null, 小米mix8 ,998,null,1); insert into product values(null, 锤子 ,2888,null,1); insert into product values(null, 阿迪王 ,99,null,2); insert into product values(null, 老白干 ,598,null,); insert into product values(null, 敬酒 ,298,null,); insert into product values(null, 小熊饼干 ,1,null,4); insert into product values(null, 卫龙辣条 ,,null,5); 技术分析: 多表之间如何维护? --外键约束 :foreign key

•     给product表 添 加外键约束

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

•     自己挖坑
•      从分类中删除分类信息为5
  • delete from category where cid = 5; //删除失败
  • 首先删除product 中所有分类id为 5 的商品

--添加一个外键: alter table product add foreign key(cno) references category(cid);

• foreign key(cno) references category (cid);
• 删除的时候,先删除关联的所有数据,才能删除分类数据

--建数据库原则:

• 通常情况下一个项目建一个数据库

--多表之间建表原则

• 一对多 : 商品和分类
  • 建表原则: 在 多 的一方添加一个 外键   指向 一的 一方的 主键
• 多对多 : 老师和学生, 学生和课程
  • 建表原则:  建立一张 中间表 , 将多对多拆称  两个一对多 , 中间表至少有两个 外键指向原来两张表
• 一对一:  班级和班长, 公民和身份证, 国家和国旗
  • 将一对一的情况 当作是一对多 情况处理,在任何一张表添加一个外键,并且这个外键唯一指向另一张表
  • 直接将两张表 合并
  • 将两张表的主键建立起链接,让两张表里面 主键相等

实际用途: 用的不是很多( 拆表操作 )         相亲网站:              个人信息: 姓名,年龄,身高,体重,兴趣爱好( 年收入,特长,学历,职业 )             拆表操作: 将个人常用信息和不常用信息分开, 减少表的臃肿 --主键约束和唯一约束的区别? 主键约束:                      默认 不能为空, 唯一的                     外键指向另一张表的主键                     一张表 只有一个 主键约束     唯一约束:                      列里面的内容,必须是唯一的,不能重现重复情况, 可以为空                     唯一约束不能作为其他表的主键                     一张表能 有多个 唯一约束 2.商城案例分析

• 用户表( 用户ID, 用户名, 密码, 手机 )

create table user(         uid int primary key auto_increment,         username varchar(1),         password varchar(1),         phone varchar(11) ); insert into user values(1, zhangsan , 12 , 1800000000 );

• 订单表(订单编号, 总价 ,订单时间, 地址,外键用户的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, 黑马平台 ,1); insert into orders values(2,250,null, 黑马后台 ,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, 小米mix8 ,998,1); insert into product values(null, 锤子 ,2888,1); insert into product values(null, 阿迪王 ,99,2); insert into product values(null, 老白干 ,598,); insert into product values(null, 敬酒 ,298,); insert into product values(null, 小熊饼干 ,1,4); insert into product values(null, 卫龙辣条 ,,5);

• 商品分类表( 分类ID,分类名称,分类描述 )

create table category(     cid int primary key auto_increment,     cname varchar(1),     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, 馋嘴零食 , 花生瓜子八宝粥 );

• 订单项 中间表( 订单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 ); --给一号订单添加商品     insert into orderitem values(1, 6 , 101 ,101);     insert into orderitem values(1, , 1, 99); --给二号订单添加商品     insert into orderitem values(2, 7 ,50 ,150);     insert into orderitem values(2 , 6,100,100); .多表查询

• 交叉连接查询  笛卡尔积

select * from product; select * from category; 笛卡尔积,两张表的乘积, 并没有意义 select * from product , category; --过滤出有意义的数据 select * from product As p,category As c where po = ; select * from product  p,category  c where po = ;

• 内连接查询

--隐式内连接 select * from product  p,category  c where po = ; --显示内连接 select * from product inner join category on cno = cid; --结果没有区别 区别:      --隐式内连接: 在查询出结果的基础上去做的where 条件过滤     --显式内连接: 带着条件去查询结果的( 执行效率要高 )

• 左外连接

--数据准备 ISERT ITO product VALUES(ULL, 耐克帝 ,10,ULL); --左外连接会将左表中所有的信息都查询出来, 如果右表中没有对应数据, 用ULL代替 select * from product p left outer join category c on po = ;

• 右外连接

- 准备工作   ISERT ITO category VALUES(100, 电脑办公 , 电脑叉叉差 ); - 右外连接: 会将右表所有数据都查询出来, 如果左表没有对应数据的话, 用ULL代替 select * from product p right outer join category c on po = ; 4.分页查询

• 每页数据10条
• 起始索引从0
• 第一页: 0
• 第二页: 11

    起始索引: index代表显示第几页     每页显示条数据     startindex = (index - 1) * ;    0,,6,9..... 第一个参数: 索引 第二个参数: 个数 select * from product limit 0,; select * from product limit ,; 5.子查询(了解)

• 查询出(商品名称,商品分类名称) 信息

--左连接 select p.pname, came from product p left outer join category c  on po = ; --子查询 select pname,(select cname from category c where po = ) from product p;

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

select * from product where cname = 手机数码 ;  ×   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 category where cname = 手机数码 );