当前位置：首页 > news >正文

MYSQL 高级SQL语句（二）

news 2025/8/11 20:22:26

表连接查询

MYSQL数据库中的三种连接：

inner join(内连接)：只返回两个表中联结字段相等的行（有交集的值）
left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录

left join（左连接）

left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录。

select * from location A LEFT JOIN store_info B on A.store_name=B.store_name; #左连接

右表中与左表无关联的数据，会用null填充

right join（右连接）

right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录。

select * from location A RIGHT JOIN store_info B on A.store_name=B.stor

左表中与右表无关联的数据，会用null填充

inner join（内连接）

inner join(内连接)：只返回两个表中联结字段相等的行。

方法一: select * from location A inner join store_info B on A.store_name=B.store_name;

方法二: select * from location A, store_info B where A.store_name=B.store_name;

方法三： select * from location A inner join store_info B using(store_name); #using

视图

视图：可以被当作是虚拟表或存储查询。

视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。 比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。
视图表就是保存了select语句的查询结果，可以理解为select语句的别名。

原表数据变化后，视图表的结果也会发生变化。

CREATE VIEW "视图表名" AS "SELECT 语句"; #创建视图表

DROP VIEW "视图表名"; #删除视图表

派生表，两个select查询写在一个句子当中，比较复杂冗长。

视图表，简化复杂的查询。只需创建一次，后面可以直接对已创建好的视图表进行操作。

#派生表：C就是子查询中select语句的派生表。

select sum(C.sales) from (select A.region region,sum(B.sales) sales from location A,store_info B where A.store_name=B.store_name group by region) C;

#视图表：只需创建一次，后面可以直接对已创建好的视图表进行操作。

create view v_region_sales as select A.region region,sum(B.sales) sales from location A inner join store_info B on A.store_name=B.store_name group by region; select sum(sales) from v_region_sales; #对视图表进行操作

视图表能否插入数据

如果视图表是两个表的连接查询，则无法插入数据。因为表结构和原表不一致。

如果视图表的结构和原表结构一致，可以修改和插入数据。
- 例如原表有3个字段，视图表有2个字段，只要这2个字段和原表结构是一致的，也可以修改和插入数据。（只要该视图表是对单个表的查询结果即可）

联集

UNION联集：将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类。

UNION（合并后去重）

生成结果的数据记录值将没有重复，且按照字段的顺序进行排序。#合并后去重

[select 语句1] UNION [select 语句2];

UNION ALL（合并后不去重）

将生成结果的数据记录值都列出来，无论有无重复。#合并后不去重

[select 语句1] UNION ALL [SELECT 语句2];

交集值

交集值：取两个SQL语句结果的交集。

使用union all + group by+having求交集值

注意：使用联集的方式求交集时，两个表的目标字段值必须先各自去重，之后再合并。避免因为单个表内存在重复值，导致错误计算。

#两个表各自将store_name字段的值进行去重，之后合并，再创建视图表。 create view v_store_name as select distinct store_name from location union all select distinct store_name from store_info;

#对视图表的store_name字段进行分组汇总，计算每组的数量。 select store_name,count(*) from v_store_name group by store_name;

#对视图表的store_name字段进行分组汇总，计算每组的数量，过滤出数量大于1的store_name字段值，就是两个表的交集部分。 select store_name from v_store_name group by store_name having count(*) >1;

使用内连接求交集值

取两个表的store_name字段值的交集部分：

select A.store_name from location A inner join store_info B on A.store_name=B.store_name;

select A.store_name from location A inner join store_info B using(store_name);

取两个表的store_name字段值的交集部分，之后去重，加distinct：

select distinct A.store_name from location A inner join store_info B using(store_name);

使用左连接求交集值

#使用左连接查询store_name字段的交集部分 select * from location A left join store_info B using(store_name);

#使用左连接查出store_name字段的交集值，之后去重 select distinct A.store_name from location A left join store_info B using(store_name) where B.store_name is not null;

使用右连接求交集值

#使用右连接查出store_name字段的交集值，之后去重 select distinct A.store_name from location A right join store_info B using(store_name) where A.store_name is not null;

#方法二： select distinct A.store_name from location A right join store_info B on A.store_name=B.store_name where A.store_name is not null;