Day09-mySQL-02多表

一，可视化软件介绍

---

SQLyog和navicat8

SQLyog:

创建数据库：右键-创建数据库
创建表：点击数据库，下方的table文件夹，右键创建表（pk？   是否为主键）
     点击创建表，然后给表命名。
创建记录：选中表，右键。打开表：
     表数据下方一排按钮，“在结果集中创建一条新纪录”
     或者直接写在下方写。点击刷新保存
查询前先指定数据库，use
          点击：架构设计器，拖动有关系的表进去。

---

二，多表间关系

---

1.为什么要拆表

1.1表的准备

​ 创建一张分类表(类别id,类别名称.备注:类别id为主键并且自动增长)
​ 创建一张明星表(商品id,商品名称,商品价格,商品数量,类别.备注:商品id为主键并且自动增长)

2.引用完整性

​ 表和表之间存在一种关系，但是这个关系需要谁来维护和约束？

2.1外键约束

​ 外键作用:保证引用完整性,也就是说数据的准确
​ 外键需要注意的地方:
​ 1.外键列的类型一定要和参照的主键的类型一致 （主键是拿来参照的）
指定的时候类型要一致，type只能指向cid。
外键只能够指向主键。就是说在“多” 对“一” 中，“一” 是主键表/引用表，“多” 是外键表

添加外键的语句:foreign可以省略；
建议一般外键名命名的时候以”fk_“开头或者结尾，建议写上外键名字，为了后面使用方便和好删除。
alter table 表 add [CONSTRAINT 外键名] foreign key(字段) references 表(字段);

      删除外键的语句:

alter table 表名 drop foreign key外键约束名

• 给商品表添加外键

 alter table star add constraint fk_star foreign key(type) references category(cid);

删除外键：

alter table star drop foreign key fk_star;

SQLyog中的备注标志 -- ;

一对多的关系建表原则：
面试：
在多的一方指定、创建一个外键指向1的一方主键。

---

2.2练习

    CREATE TABLE student(
        sid int primary key,
        name varchar(50) not null,
        sex varchar(10)
    );

    create table score(
        id int primary key,
        score int,
        sid1 int , -- 外键列的数据类型一定要与主键的类型一致
    );

添加外键方式:

ALTER TABLE score ADD  FOREIGN KEY(sid1) REFERENCES student(sid);

注意:
- 有主键才能有外键,就是说外键中出现的数据一定需要在主键中出现过，不然会导致外键添加不上去

---

3.多表的关系product

---

注意绑定的时候，外键约束的外键表可以绑定任何的列，但是主键表却只能够绑定在主键表的主键列；

3.1一对多(掌握)
- 在多的一方创建一个字段作为外键，指向一的一方主键

3.2 多对多(掌握)
- 新建一张第三方表，至少包含两个字段，都作为外键，分别指向各自的主键
建表原则：
创建一个第三方表，（除了id字段外，其实id也可以不用），还至少需要两个字段，分别作为外键指向各自的主键。

3.3一对一(了解)

• 先当做一对多，在外键字段添加唯一约束。

3.4.练习

• 抽取商城实体分析表的关系，模拟创建表
  

三，多表查询(重点)

---

1.交叉查询(了解)

​ 若干表没有条件的连接在一起
这是交叉查询，交叉查询会有很多错误数据，交叉查询出来的结果叫做笛卡尔积。

select a.*,b.* from a,b ;
或者 select * from a,b;

注:
- 交叉查询其实是一种错误.数据有很多无用数据,叫笛卡尔积.
- 假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

2.内连接查询(重点)

就是交叉查询后面加上适当的连接条件
- 交叉查询产生这样的结果并不是我们想要的，那么怎么去除错误的，不想要的记录呢，当然是通过条件过滤。
通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

2.1 隐式内连接，条件前面用where

select a.*,b.* from a,b where 连接条件
或者:
select * from a, b where 连接条件

select * from product,category2 where product.type = category2.cid;
select * from product p,category2 c where p.type = c.cid;

2.2 显示内连接，条件前面用on
显示内连接和隐式内连接只有写法的区别，查询结果没有任何区别；
条件是on；

select a.*,b.* from a [inner] join b on 连接条件
或者:
select *from a [inner] join b on 连接条件 where 其它条件

select * from product p inner join category2 c on p.type = c.cid;

注:
- 使用主外键关系做为条件来去除无用信息.抓住主外键的关系,用主外键作为连接条件 b表里面的外键=a表里面主键
- 显示里面的,on只能用主外键关联作为条件,如果还有其它条件,后面加where

练习:
查询所有类别下的商品,并且商品价格大于4000，如果该类别下没有商品则不显示
select * from product p inner join category2 c on p.type = c.cid where p.price>4000;
上述写法的and 和where都是可以的。

以左边的表为主表，如果满足条件则显示，不满足则不显示。

---

4.外连接(重点)

内连接和外连接的唯一区别就是：
内连接中，主表的数据只有在外键表中出现了才会出现在结果中，而外连接中，主表中的数据是一定会出现在结果中，不管主表中的数据是不是出现在了外键表中，如果没有出现在外键表中，那么就会用null来显示。

左外连接和右外连接的区别：
左连接左边是左边作为主表，右连接是右边作为主表；

4.1左外连接

以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示

select a.*,b.* from a left [outer] join b on 条件
或者:
select *from a表 left [outer] join b表 on 条件

练习:查询所有类别下的商品信息.

select * from product p left outer join category2 c on p.type = c.cid;

4.2右外连接(了解)

​ 以join右边的表为主表,展示它的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示

select a.*,b.* from a right [outer] join b on 条件
或者:
select *from 表a right [outer] join 表b on 条件

练习:查询所有商品所对应的类别信息

select * from product p right outer join category2 c on p.type = c.cid;

---

5.子查询(重点)

5.1什么是子查询

• 一个select语句中包含另一个完整的select语句。
• 子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

5.2步骤解析
eg:查询价格高于IPhone7的商品信息

select * from product where price>(select price from product where pname = 'iphone7'）；

分解步骤：
- 第一步:查询Iphone7的商品价格

   select price from product where pname = 'Iphone7';

• 第二步:查询价格高iPhone7的商品信息

   select *from product where price >(第一步语句作为条件)

5.4练习
- 查询和农夫山泉是同一类别的商品信息

   SELECT *from product where cno = (SELECT cno from Product where pname ='农夫山泉') and pname <> '农夫山泉';

• 查询类别是手机数码的所有商品信息

select *from product where cno =  (SELECT cid FROM category where cname ='手机数码');

---

6.联合查询(了解，几乎用不到)

用union和union all连接两个查询语句；
联合查询的时候的列名可以不一样，但是数据类型需要一样才能够查询
当且仅当两个表的结构相同的时候，结构不包含列名。

6.1作用
- 合并结果集就是把两个select语句的查询结果合并到一起

6.2语法
- UNION：去除重复记录，例如：

SELECT * FROM t1 UNION SELECT * FROM t2

• UNION ALL：不去除重复记录，例如：

SELECT * FROM t1 UNION ALL SELECT * FROM t2

6.3练习

两种联合查询的结果如下：

---

7 分页查询(掌握)，写法：分页更在排序后面！

(客户端向服务器端传页数和一页显示的数据条数)
- limit a,b; (a不是代表页数！！！)写在查询语句的最后。
- a:从哪里开始(从0开始计数的)，所以：a = (curPage-1)*b；
- b:一页显示数据的数量 固定值 前端或者安卓,ios

目标:将客户端传过来的页数curPage转换成a(开始的下标)
     a = (curPage-1)*b；// curPage当前页数  ，b表示每页显示的数据条数

一页显示3条数据: 第1页: a = 0; b = 3;
​ 第2页: a = 3; b = 3;
​ 第3页:a = 6; b = 3;
​ 第四页:a=9,b=3.

​ 练习: 查询商品表前两条记录

     select * from product limit 0,2;

​ 查询商品表第三条和第四条记录

     select * from product limit 2,2;

​ 查询商品表中最低价格所对应的商品名

     select name from product order by  price asc limit 0,1 ;

场景: 百度分页

​ 安卓、IOS里面下拉刷新,上拉加载更多

客户端发送请求给服务器，请求中包含要请求的页数和每页的数据量

下拉加载更多:

​ 1.单个条目的数据量不是很大的时候,每次下拉都请求服务器,服务器把数据给他们 limit（每次显示一页数据都要向服务器发送请求）

​ 2.单个条目的数据量很大,一次给他们,让他们自己做(一般不用！！！！)

---

四，数据库的备份和恢复

---

备份：
在SQLyog中选中需要备份的数据库，右键，备份/导出,以SQL转储文件备份数据库；
恢复：
右键，从SQL转储文件导入数据库；

易错案例：
查询订单价格大于300的订单信息及相关用户的信息。
SELECT *FROM USER,orders WHERE user.id=orders.user_id AND orders.price > 300;
（加粗部分不能够省略，不然就成为了交叉查询了）