mysql查询重点 之 连接查询 and 外键

mysql查询重点--连接查询 

1、需求1：查询所有存在商品分类的商品信息

　　注意： 内连接是取两张表的交集，如果商品没有对应的商品分类信息，则不会被显示出来

-- 需求1：内连接，查询所有存在商品分类的商品信息
-- 查询商品数据
select * from goods;
select * from catergray;

-- 在查询中，需要将两张表存在的对应关系的数据全部显示出来时，需要使用内连接
-- 内连接：显示两张表中存在对应关系的数据，无对应关系的数据不显示
-- 语法： 表1 inner join 表2 on 表1.字段 = 表2.字段
select * from goods inner join catergray on goods.typeId = catergray.typeId;

-- 连接查询共同知识点
-- 对连接后的表进行字段显示限制
-- 注意： 需要由对应的 表名.字段 的方式实现（防止表与表之间存在相同的字段名）
select goods.goods_name,catergray.catergray_name from goods inner join catergray on goods.typeId = catergray.typeId;
-- 表名.* ：代表显示该表的所有字段
select goods.* ,catergray.catergray_name from goods inner join catergray on goods.typeId = catergray.typeId;

-- 连接查询中，往往会给表取别名，目的：1）缩短表名 2）给表单独创建空间
select go.goods_name,ca.catergray_name from goods as go inner join catergray as ca on go.typeId = ca.typeId;

2、左连接

　　-- 左连接： 以left join 为关键字，关键字左侧的表信息要全部显示出来，而关键字右侧表的信息，有对应的部分显示，无对应的部分 以null 占位填充即可

　　-- 语法： 表1 left join 表2 on 表1.字段 = 表2.字段

-- 左连接：以left join 关键字为界，关键字左侧的表的信息要全部显示出来，而关键字右侧的表的信息
-- 有对应的部分显示出来，无对应的部分以null 占位填充即可
-- 语法 表1 left join 表2 on 表1.字段 = 表2.字段
-- 查询 所有商品信息包含商品分类

select * from goods left join catergray on goods.typeId = catergray.typeId;

　　-- 查询所有的商品分类 及其对应的商品信息

-- 查询所有商品分类，及其对应的商品信息
select * from catergray left join goods on catergray.typeId = goods.typeId;

3、右连接

　　-- 需求2：查询所有商品信息，包含商品分类

　　-- 注意： 右连接主要解决三张及以上表 进行连接查询时常见

　　--右连接： 以right join 关键字为界，关键字右侧的表的信息要全部显示出来，而关键字左侧表的信息，有对应的部分显示出来，无对应的部分以null占位填充

　　-- 语法： 表1 right join 表2 on 表1.字段 = 表2.字段

-- 需求2：查询所有商品信息，包含商品分类
-- 注意： 右连接主要解决三张及以上表 进行连接查询时常见
-- 右连接： 以right join 关键字为界，关键字右侧的表的信息要全部显示出来，而关键字左侧表的信息，有对应的部分显示出来，无对应的部分以null占位填充
-- 语法： 表1 right join 表2 on 表1.字段 = 表2.字段
-- 解析： 就是catergray表中的所有信息都要显示出来，如果catergray中有多的信息并且goods 中没有对应的信息，就以null 占位
-- 那张表要显示所有信息 就把哪张表放在 right Join 右侧
select * from catergray right join goods on goods.typeId = catergray.typeId;

-- 查询所有商品分类 及其对应的商品信息
select * from goods right join catergray on goods.typeId = catergray.typeId;

4、内连接、左连接、右连接 小结

　　-- 内连接：是两张表的交集

　　-- 左连接是 左侧整张表的信息加上两张表的交集

　　-- 右连接 是右侧整张表的信息加上 两站表的交集

 

 5、连接查询的实质

　　-- 连接查询的实质：只是将分布于多张表的数据，通过连接的方式进行整理，形成数据源

　　-- 连接操作完成后，之前的所有的查询语法，都可以继续使用

　　-- 具体的连接查询方法的选择，要以具体的需求为准

　　-- 内连接： 如果有商品 没有分类对应的信息，不会被显示出来

　　-- 注意： 如果使用内连接，会造成数据缺失，查询结果错误

-- 显示所有价格低于120 的商品信息，包含器对应的商品分类
select * from goods left join catergray on goods.typeId = catergray.typeId where goods.price < 120; 
-- 注意：如果使用内连接，会造成数据缺失；因为内连接 如果没有商品分类信息，不会被显示出来
select * from goods inner join catergray on goods.typeId = catergray.typeId where goods.price < 120;

-- 需求1：内连接，查询所有存在商品分类的商品信息
-- 查询商品数据
select * from goods;
select * from catergray;

-- 在查询中，需要将两张表存在的对应关系的数据全部显示出来时，需要使用内连接
-- 内连接：显示两张表中存在对应关系的数据，无对应关系的数据不显示
-- 语法： 表1 inner join 表2 on 表1.字段 = 表2.字段
select * from goods inner join catergray on goods.typeId = catergray.typeId;

-- 连接查询共同知识点
-- 对连接后的表进行字段显示限制
-- 注意： 需要由对应的 表名.字段 的方式实现（防止表与表之间存在相同的字段名）
select goods.goods_name,catergray.catergray_name from goods inner join catergray on goods.typeId = catergray.typeId;
-- 表名.* ：代表显示该表的所有字段
select goods.* ,catergray.catergray_name from goods inner join catergray on goods.typeId = catergray.typeId;

-- 连接查询中，往往会给表取别名，目的：1）缩短表名 2）给表单独创建空间
select go.goods_name,ca.catergray_name from goods as go inner join catergray as ca on go.typeId = ca.typeId;
-- 左连接：以left join 关键字为界，关键字左侧的表的信息要全部显示出来，而关键字右侧的表的信息
-- 有对应的部分显示出来，无对应的部分以null 占位填充即可
-- 语法 表1 left join 表2 on 表1.字段 = 表2.字段
select * from goods left join catergray on goods.typeId = catergray.typeId;

-- 查询所有商品分类，及其对应的商品信息
select * from catergray left join goods on catergray.typeId = goods.typeId;

-- 需求2：查询所有商品信息，包含商品分类
-- 注意： 右连接主要解决三张及以上表 进行连接查询时常见
-- 右连接： 以right join 关键字为界，关键字右侧的表的信息要全部显示出来，而关键字左侧表的信息，有对应的部分显示出来，无对应的部分以null占位填充
-- 语法： 表1 right join 表2 on 表1.字段 = 表2.字段
-- 解析： 就是catergray表中的所有信息都要显示出来，如果catergray中有多的信息并且goods 中没有对应的信息，就以null 占位
-- 那张表要显示所有信息 就把哪张表放在 right Join 右侧
select * from catergray right join goods on goods.typeId = catergray.typeId;

-- 查询所有商品分类 及其对应的商品信息
select * from goods right join catergray on goods.typeId = catergray.typeId;

-- 显示所有价格低于120 的商品信息，包含器对应的商品分类
select * from goods left join catergray on goods.typeId = catergray.typeId where goods.price < 120; 
-- 注意：如果使用内连接，会造成数据缺失；因为内连接 如果没有商品分类信息，不会被显示出来
select * from goods inner join catergray on goods.typeId = catergray.typeId where goods.price < 120;

 

6、自关联介绍

　　特征： 只有一张表，表中最少存在两列字段具备对应关系

　　原理：通过起别名的方式，将一张表变为两张表，通过对应字段的对应关系，进行条件比对，实现连接查询

　　1）需求4： 查询河南省所有的地市

　　-- 自关联：

　　--1）通过起别名的方式，将一表变两张表

　　--2）通过 表1.字段 = 表2.字段 实现表间的关联

　　

select * from maps as m1 inner join maps as m2 on m1.aid = m2.pid where m1.name = '河南省';

-- 需求5：查询河南省所有的市和区
select * from maps as m1 inner join maps as m2 on m1.aid = m2.pid left join maps as  m3 on m2.aid = m3.pid where m1.name = '河南省';

7、子查询：

　　定义：在一条查询语句中，利用另一条语句作为条件或输入源，充当条件 或 数据源 的语句称之为子查询语句

　　注意：子查询语句在使用时，务必使用括号括起来

　　-- 需求1：查询价格高于平均价格的商品信息

　　-- 需求2： 查询所有来自拼多多的商品信息，包含商品分类

select * from goods where price > (select avg(price) from goods );
-- 需求 查询所有来自并西西的商品，包含商品分类
select * from (select * from goods where company = '并西西') as m  left join catergray on m.typeId = catergray.typeId;

8、扩展：

　　根据子查询语句返回的结果形式提现： 子查询共分为4类：

　　子查询返回的结果是一个值（一行一列） 称为标量子查询

　　子查询返回的结果是一列数据（一列多行） 称为列子查询

　　子查询返回的结果 是 一行数据（一行多列） 称为行子查询

　　子查询返回的是多行多列（相当于一张表） 称为表级子查询

 

9、外键

　　如果一个表中的某个字段指向另一个实体的主键，就称为外键，被指向的实体，称之为主表，也叫父表。负责指向的实体，称之为从表，也叫字表；

　　作用： 对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错。

 

 

-- 需求1：内连接，查询所有存在商品分类的商品信息
-- 查询商品数据
select * from goods;
select * from catergray;

-- 在查询中，需要将两张表存在的对应关系的数据全部显示出来时，需要使用内连接
-- 内连接：显示两张表中存在对应关系的数据，无对应关系的数据不显示
-- 语法： 表1 inner join 表2 on 表1.字段 = 表2.字段
select * from goods inner join catergray on goods.typeId = catergray.typeId;

-- 连接查询共同知识点
-- 对连接后的表进行字段显示限制
-- 注意： 需要由对应的 表名.字段 的方式实现（防止表与表之间存在相同的字段名）
select goods.goods_name,catergray.catergray_name from goods inner join catergray on goods.typeId = catergray.typeId;
-- 表名.* ：代表显示该表的所有字段
select goods.* ,catergray.catergray_name from goods inner join catergray on goods.typeId = catergray.typeId;

-- 连接查询中，往往会给表取别名，目的：1）缩短表名 2）给表单独创建空间
select go.goods_name,ca.catergray_name from goods as go inner join catergray as ca on go.typeId = ca.typeId;
-- 左连接：以left join 关键字为界，关键字左侧的表的信息要全部显示出来，而关键字右侧的表的信息
-- 有对应的部分显示出来，无对应的部分以null 占位填充即可
-- 语法 表1 left join 表2 on 表1.字段 = 表2.字段
select * from goods left join catergray on goods.typeId = catergray.typeId;

-- 查询所有商品分类，及其对应的商品信息
select * from catergray left join goods on catergray.typeId = goods.typeId;

-- 需求2：查询所有商品信息，包含商品分类
-- 注意： 右连接主要解决三张及以上表 进行连接查询时常见
-- 右连接： 以right join 关键字为界，关键字右侧的表的信息要全部显示出来，而关键字左侧表的信息，有对应的部分显示出来，无对应的部分以null占位填充
-- 语法： 表1 right join 表2 on 表1.字段 = 表2.字段
-- 解析： 就是catergray表中的所有信息都要显示出来，如果catergray中有多的信息并且goods 中没有对应的信息，就以null 占位
-- 那张表要显示所有信息 就把哪张表放在 right Join 右侧
select * from catergray right join goods on goods.typeId = catergray.typeId;

-- 查询所有商品分类 及其对应的商品信息
select * from goods right join catergray on goods.typeId = catergray.typeId;

-- 显示所有价格低于120 的商品信息，包含器对应的商品分类
select * from goods left join catergray on goods.typeId = catergray.typeId where goods.price < 120; 
-- 注意：如果使用内连接，会造成数据缺失；因为内连接 如果没有商品分类信息，不会被显示出来
select * from goods inner join catergray on goods.typeId = catergray.typeId where goods.price < 120;

select * from goods where price > (select avg(price) from goods );
-- 需求 查询所有来自并西西的商品，包含商品分类
select * from (select * from goods where company = '并西西') as m  left join catergray on m.typeId = catergray.typeId;
select * from maps as m1 inner join maps as m2 on m1.aid = m2.pid where m1.name = '河南省';

-- 需求5：查询河南省所有的市和区
select * from maps as m1 inner join maps as m2 on m1.aid = m2.pid left join maps as  m3 on m2.aid = m3.pid where m1.name = '河南省';