MySQL高级语句（二）

最新推荐文章于 2023-04-27 14:40:16 发布

阿杰112

最新推荐文章于 2023-04-27 14:40:16 发布

阅读量100

点赞数 1

文章标签：数据库 mysql linux

本文链接：https://blog.csdn.net/Y_S_J_112/article/details/127664676

版权

MySQL高级语句（二）

一、MySQL数据库中的三种连接：

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

oracle数据库支持outer join（外连接），mysql不支持
在这里插入图片描述

1.1 left join（左连接）

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

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

在这里插入图片描述

1.2 right join（右连接）

right join（右连接）：返回包括右表中的所有记录和左表中联结字段相对的记录

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

在这里插入图片描述

1.3 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()是函数，必须保证括号内的字段在两个表中同名

在这里插入图片描述

示例：

设置location表的别名为A、store_info表的别名为B，查询A表和B表中store_name字段值相等的行，之后对region字段进行分组汇总，并计算组内销售额之和。

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

在这里插入图片描述

二、视图

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

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

语法：

创建视图
create view 视图表名 as "select 语句";

删除视图
drop view 视图表名;

示例：
视图表时保存了select语句的查询结果，可以理解为select语句的别名
原表数据变化后，视图表的结果也会发生变化

 #将两个表的连接查询，创建为视图表
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 * from v_region_sales;     #查看视图表的存储结果
 
 drop view v_region_sales;         #删除视图表

在这里插入图片描述

2.1 视图表与派生表比较

派生表，两个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;    #对视图表进行操作

在这里插入图片描述

2.2 视图能否插入数据

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

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

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

 #该视图表是两个表的连接查询
 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;
 
 #查看视图表结构，和原表不一致。
 desc v_region_sales;
 
 #插入数据失败
 insert into v_region_sales values('middle',500);

在这里插入图片描述
示例2：
如果视图表的结构和原表结构一致，可以修改和插入数据。（只要该视图表是对单个表的查询结果即可）

 #该视图表只是对单个表的查询结果。
 create view v_test_info as select * from store_info;
 
 #查看视图表结构，和原表一致。
 desc v_test_info;
 
 #插入数据成功
 insert into v_test_info values('NYC',500,'2020-12-10');

在这里插入图片描述

三、联集

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

3.1 union（合并后去重）

生成结果的数据记录值将没有重复，且按照字段的顺序进行排序。（合并后去重）
语法：
[select 语句1] union [select 语句2];

示例：

select store_name from location union select store_name from store_info;
#将两个表的store_name字段的值合并起来，union生成的结果去重

在这里插入图片描述

union all（合并后不去重）

将生成结果的数据记录值都列出来，无论有无重复。（合并后不去重）
语法：
[select 语句1] union all [select 语句2];

示例：

select store_name from location union all select store_name from store_info;
#将两个表的store_name字段的值合并，全部列出，不去重

在这里插入图片描述

四、交集值

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

4.1 使用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;

在这里插入图片描述

4.2 使用内连接求交集值

取两个表的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;

在这里插入图片描述

4.4 使用右连接求交集值

select distinct A.store_name from location A right join store_info B using(store_name) where A.store_name is not null;

在这里插入图片描述

4.5 使用子查询的方式求交集值 in

 #使用子查询的方式查出store_name字段的交集值，之后去重
 select distinct store_name from location where store_name in (select store_name from store_info);

在这里插入图片描述

五、无交集值

无交集值：显示第一个SQL语句的结果，且与第二个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;

在这里插入图片描述

六、CASE

case：是 SQL 用来作为 IF-THEN-ELSE 之类逻辑的关键字。

语法：

select case (字段名)
  when 条件1 then 结果1
  when 条件2 then 结果2
  [else 结果n]
  end
from 表名;

#条件可以是一个数值或公式（else字句不是必须的）

示例：

select store_name,case store_name        #对sttore_name字段值进行判断
     when 'Los Angeles' then sales * 2    #当店名为洛杉矶时销售额乘2
     when 'Boston' then 2000              #当店名为波士顿时销售额显示2000
     else sales / 2                       #其他店名则销售额除以2
     end                                 #判断结束
 "new sales", date                        #用于显示的字段名
 from store_info;
 
 #"new sales" 是用于case那个字段的字段名。
 
 #注：查询并不改变原表的值。

在这里插入图片描述

七、空值（NULL）和无值（’ '）的区别

空值（NULL）和无值（’ '）的区别：

无值的长度为0，不占用空间；而NULL值的长度是NULL，是占用空间的。
IS NULL或者IS NOT NULL，是用来判断字段是不是为NULL或者不是NULL，不能查出是不是无值的。
无值的判断使用=’ ‘或者< >’ '来处理。<>代表不等于。
在通过 count ()指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。

 create table city(name varchar(10));
 insert into city values('beijing');
 insert into city values('nanjing');
 insert into city values();
 insert into city values();
 insert into city values('hangzhou');
 insert into city values('');

在这里插入图片描述

示例1：
查看无值和空值的长度。

无值的长度为0，不占用空间；而NULL值的长度是NULL，是占用空间的。

 select length(NULL), length(''), length('123');

在这里插入图片描述
示例2：

IS NULL或者IS NOT NULL，是用来判断字段是不是为NULL或者不是NULL，不能查出是不是无值的。

 select * from city where name is null;       #查询字段值为NULL的数据记录
 select * from city where name is not null;   #查询字段值不为NULL的数据记录

在这里插入图片描述
示例3：

无值的判断使用 =’ ’ 或者**< >’ '来处理。< > 代表不等于**。

 select * from city where name='';      #查询字段值为无值的数据记录
 select * from city where name <> '';   #查询字段值不为NULL以及不为无值的数据记录

在这里插入图片描述
示例4：

在通过 count () 指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。

 select count(name) from city;     #会忽略null值，但不会忽略无值
 select count(*) from city;        #会统计所有行

在这里插入图片描述

八、正则表达式——精确查询

匹配模式	描述	实例
^	匹配文本的开始字符	‘^bd’ 匹配以 bd 开头的字符串
$	匹配文本的结束字符	‘qn$’ 匹配以 qn 结尾的字符串
.	匹配任何单个字符	‘s.t’ 匹配任何 s 和 t 之间有一个字符的字符串
*	匹配零个或多个在它前面的字符	‘fo*t’ 匹配 t 前面有任意个 o
+	匹配前面的字符 1 次或多次	‘hom+’ 匹配以 ho 开头，后面至少一个m 的字符串
字符串	匹配包含指定的字符串	‘clo’ 匹配含有 clo 的字符串
p1lp2	匹配 p1 或 p2	‘bglfg’ 匹配 bg 或者 fg
[…]	匹配字符集合中的任意一个字符	‘[abc]’ 匹配 a 或者 b 或者 c
[ ^…]	匹配不在括号中的任何字符	‘[ ^ab]’ 匹配不包含 a 或者 b 的字符串
{n}	匹配前面的字符串 n 次	‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m}	匹配前面的字符串至少 n 次，至多m 次	‘f{1,3}’ 匹配 f 最少 1 次，最多 3 次

语法：
select 字段名 from where 字段名 regexp {模式};

select * from store_info where store_name regexp 'os';
//查询store_name字段包含字符串“os”的行

select * from store_info where store_name regexp '^[A-G]';
//查询store_name字段以A-G开头的行

select * from store_info where store_name regexp 'Ho|Bo';
//包含Ho或者Bo

select * from store_info where store_name regexp 'on$';
//以字符串“on”为结尾

select * from store_info where store_name regexp '^Bo.*on$';
//以Bo开头且以on结尾

select * from store_info where store_name regexp '.*@qq.com$';
//查询所有QQ邮箱

在这里插入图片描述

阿杰112

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL高级语句（二）

设置location表的别名为A、store_info表的别名为B，查询A表和B表中store_name字段值相等的行，之后对region字段进行分组汇总，并计算组内销售额之和。如果视图表的结构和原表结构一致，可以修改和插入数据。视图表时保存了select语句的查询结果，可以理解为select语句的别名。而NULL值的长度是NULL，是占用空间的。如果视图表是两个表的连接查询，则无法插入数据。：返回包括左表中的所有记录和右表联结字段相对的记录。：返回包括右表中的所有记录和左表中联结字段相对的记录。
复制链接

扫一扫