2_数据库(二)

数据库(二)

---

多表操作

 

1.多表之间如何操作

select [distinct] [*] [列名,列名2] from 表名 [where 条件]

distinct: 去除重复的数据

 

 

--商品分类: 手机数码, 皮靴箱包

1.分类ID

2.分类名称

3.分类描述

Create table category (

    cid int primary key auto_increment,

    cname varchar(10),

    cdesc varchar(30)

);

 

 

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.商品名称

3.商品价格

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,3);

insert into product values(null, '敬酒',298,null,3);

insert into product values(null, '小熊饼干',1,null,4);

insert into product values(null, '卫龙辣条',3,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(31),

        password varchar(31),

        phone varchar(11)

);

insert into user values(1, 'zhangsan', '123', '13800000000');

 

• 订单表(订单编号, 总价 ,订单时间, 地址,外键用户的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,3);

insert into product values(null, '敬酒',298,3);

insert into product values(null, '小熊饼干',1,4);

insert into product values(null, '卫龙辣条',3,5);

 

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

create table category(

    cid int primary key auto_increment,

    cname varchar(31),

    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, 3, 1, 99);

 

--给二号订单添加商品

    insert into orderitem values(2, 7 ,50 ,150);

    insert into orderitem values(2 , 6,100,100);

 

3.多表查询

• 交叉连接查询  笛卡尔积

select * from product;

select * from category;

 

笛卡尔积,两张表的乘积, 并没有意义

select * from product , category;

 

--过滤出有意义的数据

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;

--显示内连接

select * from product inner join category on cno = cid;

--结果没有区别

区别: 

    --隐式内连接: 在查询出结果的基础上去做的where 条件过滤

    --显式内连接: 带着条件去查询结果的( 执行效率要高 )

• 左外连接

--数据准备

INSERT INTO product VALUES(NULL,'耐克帝',10,NULL);

 

--左外连接会将左表中所有的信息都查询出来, 如果右表中没有对应数据, 用NULL代替

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

• 右外连接

- 准备工作

  INSERT INTO category VALUES(100,'电脑办公','电脑叉叉差');

 

- 右外连接: 会将右表所有数据都查询出来, 如果左表没有对应数据的话, 用NULL代替

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

4.分页查询

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

    起始索引: index代表显示第几页

    每页显示3条数据

    startindex = (index - 1) * 3;    0,3,6,9.....

第一个参数: 索引

第二个参数: 个数

select * from product limit 0,3;

select * from product limit 3,3;

 

5.子查询(了解)

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

--左连接

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 * 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 = '手机数码');