MySQL 高级(进阶) SQL 语句二

最新推荐文章于 2024-05-09 16:18:12 发布
最新推荐文章于 2024-05-09 16:18:12 发布
阅读量1k 收藏 13
点赞数
文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Yuzhang2046/article/details/130596510
版权

一、表连接查询

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 RIGHT JOIN store_info B on A.store_name=B.store_name;  #右连接

1.3 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 视图表能否插入数据

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

  1. 如果视图表的结构和原表结构一致，可以修改和插入数据。

    • 例如原表有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生成的结果去重

 

 

3.1 UNION ALL（合并后不去重）

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

 [select 语句1] UNION ALL [SELECT 语句2];
 ​
 示例：
 select store_name from location union all select store_name from store_info;
 #将两个表的store_name字段的值合并，全都列出来，union all生成的结果不去重

 

四、交集值

交集值：取两个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);

 

4.3 使用左连接求交集值

 #使用左连接查询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 使用右连接求交集值 

#使用右连接查出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;

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）和无值（' '）的区别：

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

先准备一个表city：

 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;        #会统计所有行

 

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

语法：

select "字段" from "表名" where "字段" regexp {模式};
#查询store_name字段包含字符串"os"的行
 select * from store_info where store_name regexp 'os';
 ​
 #查询store_name字段以A-G开头的行
 select * from store_info where store_name regexp '^[A-G]';
 ​
 #包含Ho或者Bo
 select * from store_info where store_name regexp 'Ho|Bo'; 
 ​
 #以字符串“on”为结尾
 select * from store_info where store_name regexp 'on$';
 ​
 #以“Bo”开头且以“on”为结尾
 select * from store_info where store_name regexp '^Bo.*on$';
 ​
 #查询所有QQ邮箱
 select * from store_info where store_name regexp '.*@qq.com$';

 

  

 

 

Yuzhang2046
关注
  • 0
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql基础sql进阶语句
04-28
新入门mysql的初学者的福音，内含基础mysql语句，基本适用于普通开发。
MySQL高级（进阶SQL语句）
weixin_44938203的博客
02-14 2653
MySQL高级（进阶SQL语句） 文章目录MySQL高级（进阶SQL语句）创建测试用数据库可测试用表selec---t显示表格中一个或数个字段的所有数据记录DISTINCT ----不显示重复的数据记录WHERE -―--有条件查询AND OR----且/或IN--―-显示己知的值的数据记录BETWEEN ----显示两个值范围内的数据记录通配符LIKE------匹配一个模式来找出我们要的资料ORDER BY ----按关键字排序函数聚合函数:字符串函数GROUP BYHAVING------用来过滤由G
mysql高级（进阶）语句
鸡汤的博客
06-09 2759
location表 store_info表 连接查询 内连接1 内连接2
Linux5.7 MySQL 高级(进阶) SQL 语句
wfwj_的博客
06-20 1539
Mysql高级SQL语句，select，distinct，where，and or，in，between，通配符，like，order by，函数——数学函数、聚合函数、字符串函数，group by，having，别名，子查询，exists，连接查询——内连接、左连接、右连接、融合连接union，create view（视图），交集值，无交集值，case做if then操作，空值NULL和无值''的区别，正则表达式，存储过程——创建、调用、查看、参数、删除、控制语句
【数据库】MySQL 高级(进阶) SQL 语句
一个萌新的博客
06-20 3160
主要介绍了Mysq高级SQL语句，包括select、distinct、where、and、or、in、between、通配符、like、order by、函数（数学函数、聚合函数、字符串函数）、group by、having、别名、子查询、exists、表连接、view（视图）、union、交集/无交集、case、空值/非空值、正则表达式、存储过程以及用法
MySQL 高级(进阶) SQL 语句 (一)
weixin_44841019的博客
06-24 2947
(增、删、改、查) 对MysQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取10条数据、对查询结果进行排序或分组等等。按关键字排序 PS:类比于windows任务管理器，使用SELECT语句可以将需要的数据从MySQL数据库中查询出来，如果对查询的结果进行排序，可以使用ORDER BY语句来对语句实现排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段语法 二、升序与降序（ASC和DESC） ASC： 是按照升序进行排序的，是默认的排
MySQL高级（进阶）语句
weixin_55609833的博客
06-30 1345
MySQL高级（进阶）语句
MySQL进阶SQL语句2之表连接
q1y2y3的博客
06-24 1601
当视图中字段保留源表中字段（没有经过函数处理），视图数据可以修改，源表数据也随之改变。当视图中字段经过处理后，视图数据无法修改（例：字段经过聚合函数修改）
Mysql之进阶查询语句
m0_54594153的博客
09-20 2628
视图：优化操作+安全方案数据库中的虚拟表，这张虚拟表不包含真实数据。只是做了真实数据的映射。视图可以理解为镜花水月倒影，动态保存结果集作用场景：针对不同的人（不同权限），提供不同的结果集的表（以表格的形式展示）功能简化查询结果集，灵活查询，可以针对不同用户呈现不同的结果集，相对有更高的安全性本质而言，视图是一种select（结果集的呈现）注意视图适合于多表连接浏览时使用，不适合增，删，改，而存储过程适合于使用较频繁的sql语句，这样可以提供执行效率常用查询，
MySQL 高级(进阶) SQL 语句
低温热源的博客
06-23 3503
先求AB表集联（并集），DISTINCT去除A B表中重复值（以免后面count=1时把表A出现两次的当成A与B的并集），UNION ALL获取所有包括重复值，再GROUP BY获取分组，COUNT统计只出现过一次的，即为A与B表的无交集（AB表交集由于合并会出现两次）'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
MySQL进阶SQL语句（二）
2301_76875445的博客
06-24 3963
MySQL进阶SQL语句（二）
Mysql数据库之常用sql语句进阶与总结
09-08
主要介绍了Mysql数据库之常用sql语句,总结分析了MySQL数据库常用的查询、条件查询、排序、连接查询、子查询等相关操作技巧,需要的朋友可以参考下
MySQL入门到进阶sql脚本案例资源合集.zip
08-11
每个案例都提供了详细的描述，解释了所使用的SQL语句和操作的目的和效果。 这些案例旨在帮助读者熟悉和理解MySQL的高级特性和技术，如复杂的查询语句、联结查询、子查询、触发器、存储过程等。通过学习和实践这些...
MySQL数据库进阶实战 完整版PDF.pdf
05-07
通过学习本手册，读者可以快速掌握MySQL数据库的部署、操作和基本SQL语句，同时了解如何在云端创建数据库并进行最佳应用实践，如搭建个人博客等。 其他说明：建议读者在学习过程中积极参与实战练习，动手操作并实践...
mysql 按字段查询重复的数据
退役熬夜选手的博客
05-06 268
如果您只想识别具有重复值的列名，则方法 1 或 2 可能就足够了。如果您需要有关重复值的更多信息，例如每个值的计数，则方法 3 或 4 可能更好。主键是唯一索引的一种特殊类型，它还标识表中的每个行。在 MySQL 中，可以使用多种方法来查询按字段重复的数据。您可以使用 DISTINCT 关键字来删除结果中的重复行。该查询将为每个行返回列名的值，以及该值在同一列中出现的次数。该查询将返回所有具有重复值的列名的值，以及每个值的计数。该查询将返回所有具有重复值的列名的值。该查询将返回所有具有重复值的列名的值。
MySQL没有初始化配置文件设置属性
zwjzone的博客
05-06 285
安装mysql的时候，为了速度，并没有配置my.ini或者my.cnf文件，数据库因为断电，导致数据都看不见了，一直提示不存在，这时候需要修改配置文件，将innodb_force_recovery设置为0到6的值，依次尝试，比如innodb_force_recovery = 1,但是现在没有配置文件，该怎么设置呢？
springboot整合mybatis配置多数据源（mysql/oracle）
WLKUYM的博客
05-03 1012
springboot整合mybatis配置多数据源，可以都是mysql数据源，也可以都是oracle数据源，也可以mysql/oracle数据源都有，下面是配置多数据源的步骤（不局限与某一类数据库），之前配过都是oracle数据源的配置，下面是验证这个想法的实现，实现效果后，好像也可以同时配MongoDB、Redis等其他的数据源配置，但是好像也没碰到有人在一个项目里面这么干过…要么都是mysql数据源要么都是oracle数据源。
MySQL变量的定义与使用
最新发布
2301_80888280的博客
05-09 296
MySQL变量的定义与使用
数据库（MySQL）—— 子查询
qq_67693066的博客
05-03 711
数据库（MySQL）—— 子查询
mysql进阶查询语句
01-09
以下是MySQL进阶查询语句的一些示例： 1. 子查询： ```sql SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition); ``` 2. 排序查询： ```sql ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值