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 t26.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;
（加粗部分不能够省略，不然就成为了交叉查询了）