首页 > 数据库 >MySQL高级SQL语句之多表连接

MySQL高级SQL语句之多表连接

时间：2023-09-22 14:11:25浏览次数：41  
标签：info name MySQL store SQL 之多表 where select location

目录

MySQL高级SQL语句

1. 实际操作

1.1 新建表

use Grade;

create table location (Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');

create table store_info (Store_Name char(20),Sales int(10),Date char(10));
insert into store_info values('Los Angeles','1500','2020-12-05');
insert into store_info values('Houston','250','2020-12-07');
insert into store_info values('Los Angeles','300','2020-12-08');
insert into store_info values('Boston','700','2020-12-08');

1.2 查看表及内容

show tables;
select * from location;
select * from store_info;


---- SELECT ----显示表格中一个或数个字段的所有数据记录
语法：SELECT "字段" FROM "表名";
SELECT Store_Name FROM Store_Info;
  • select可以指定字段顺序去显示字段
select Date,Store_Name,Sales from store_info;

select Date,Sales from store_info;


---- DISTINCT ----不显示重复的数据记录
语法：SELECT DISTINCT "字段" FROM "表名";
SELECT DISTINCT Store_Name FROM Store_Info;
  • distinct对字段去重
select distinct store_name from store_info;

  • select加上sales字段，去重效果失效，distinct只对单个字段查看时实现去重效果
select distinct store_name,sales from store_info;


---- WHERE ----有条件查询
语法：SELECT "字段" FROM "表名" WHERE "条件";
SELECT Store_Name FROM Store_Info WHERE Sales > 1000;

select * from store_info where store_name='Los Angeles';
#过滤店名为洛杉矶的字段

select * from store_info where sales <= 1000;
#过滤销售额小于等于1000的字段

select * from store_info where sales >= 1000;
#过滤销售额大于等于1000的字段

select * from store_info where sales != 1500;
#过滤出销售额不等于1500的字段



---- AND OR ----且 或
语法：SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
SELECT Store_Name FROM Store_Info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200);

select * from store_info where sales > 200 and sales < 500;
#销售额大于200和销售额小于500

select * from store_info where sales > 1000 or (sales > 200 and sales < 500);
#销售额大于1000或者销售额大于200且小于500


---- IN ----显示已知的值的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);
SELECT * FROM Store_Info WHERE Store_Name IN ('Los Angeles', 'Houston');

select * from store_info where store_name in ('Houston','Boston');
#匹配店名是Houston、Boston的字段

select * from store_info where store_name = 'Houston' or store_name='Boston';
#匹配店名是Houston、Boston的字段


select * from store_info where store_name not in ('Houston','Boston');
#匹配店名不是Houston、Boston的字段


select * from store_info where store_name != 'Houston' and store_name !='Boston';
##匹配店名不是Houston且不是Boston的字段


---- BETWEEN ----显示两个值范围内的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';
SELECT * FROM Store_Info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10';

select * from store_info where sales between 300 and 1000;
#匹配销售额在300-1000之间的包括300

select * from store_info where date between '2020-12-06' and '2020-12-08';
#匹配日期在6号到12号之间的

1.3 通配符

  • 通配符都是跟 LIKE语句 一起配合使用的
    • %：百分号表示 零个 、一个 或 多个字符（相当于linux中的*号，可能会不存在）
    • _：下划线表示单个字符
---- LIKE ----匹配一个模式来找出我们要的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};
SELECT * FROM Store_Info WHERE Store_Name like '%os%';

show variables like '%slow%';
#只要字段里包含slow都会被查出来

show variables like 'slow%';
#代表以slow开头的

show variables like '%slow';
#代表以slow结尾的


select * from store_info;

select * from store_info where store_name like '%on';
#代表以on结尾的，匹配Housto和Boston


select * from store_info where store_name like '_os%';
#代表字段有os的，匹配Los Angeles和Boston


#模糊匹配尽量少用，会全表查询，在大表中尽量不使用模糊查询
show variables;


---- ORDER BY ----按关键字排序
语法：SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序。
SELECT Store_Name,Sales,Date FROM Store_Info ORDER BY Sales DESC;

select * from store_info order by sales asc;
#按升序排序

select * from store_info order by sales desc;
#按降序排序

1.4 函数

1.4.1 数学函数：

abs（x）：返回 x 的绝对值
## rand（）：返回 0 到 1 的随机小数
## mod（x，y）：返回 x 除以 y 以后的余数(取余)
power（x^y）：返回 x 的 y 次方
round（x）：返回离 x 最近的整数
## round （x，y）：保留 x 的 y 位小数四舍五入后的值
sqrt（x）：返回 x 的平方根
## truncate（x，y）：返回数字 x 截断为 y 位小数的值
ceil（x）：返回大于或等于 x 的最小整数
floor（x）：返回小于或等于 x 的最大整数
## greatest（x1，x2…）：返回集合中最大的值，也可以返回多个字段的最大的值
## least（x1，x2…）：返回集合中的最小的值，也可以返回多个字段的最小的值

SELECT abs(-1),rand(),mod(5,3),power(2,3),round(1.89);

SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);

SELECT round(1.8937,2), truncate(1.235,1), ceil(4.9), floor(1.9), least(1.89,1,6.1,2.1);



SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), greatest(1.89,3,6.1,2.1);

1.4.2 聚合函数：

avg（）：返回指定列的平均值
count（）：返回指定列中非NULL值的个数
min（）：返回指定"列"的最小值
max（）：返回指定"列"的最大值
sum（x）：返回指定列的所有值之和

select * from store_info;

select sum(sales) from store_info;
#sales列求和

select min(sales) from store_info;
#sales列最小值

select max(sales) from store_info;
#sales列最大值

select avg(sales) from store_info;
#sales列平均值

select count(sales) from store_info;
#统计sales列非null的数量

select count(*) from store_info;
#统计所有列行数




create table city(name varchar(20));

show tables;

insert into city(name) values('beijing');

insert into city(name) values('nanjing');

insert into city(name) values('tianjing');

insert into city(name) values('xian');

insert into city(name) values('wuhan');

insert into city(name) values(' ');

insert into city(name) values(null);

insert into city(name) values(null);

insert into city(name) values('shanghai');

select * from city;

select count(name) from city;

select count(*) from city;



1.4.3 字符串函数：

trim（）：返回去除指定格式的值
## concat（x，y）：将提供的参数x和y拼接成一个字符串
substr（x，y）：获取从字符串x中的第y个位置开始的字符串，跟substring（）函数作用相同
## substr（x，y，z）：获取从字符串x中的第y个位置开始长度为z的字符串
## length（x）：返回字符串x的长度
## replace（x，y，z）：将字符串z替代字符串x中的字符串y
upper（x）： 将字符串x的所有字母变成大写字母
lower（x）：将字符串x的所有字母变成小写字母
left（x，y）：返回字符串x的前y个字符
right（x，y）：返回字符串x的后y个字符
repeat（x，y）：将字符串x重复y次
space（x，y）：返回x个空格
strcmp（x，y）：比较x和y，返回的值可以为-1，0，1
reverse（x）：将字符串x反转

a=123456789
echo ${a:6:3}

substr

select substr('abcdefg',5);
#获取从字符串abcdefg中的第5个位置开始的字符串
select substr('abcdefg',5,1);
#获取从字符串abcdefg中的第5个位置开始长度为1的字符串

replace

select replace('abcdefg','abc','123');
#将字符串abc替换成123


select repeat('abc',3);
#将字符串abc重复3次


select strcmp('abc','efg');
#左边字符串比右边小返回-1

select strcmp('abc','abc');
#字符串值一样返回0

select strcmp('456','123');
#左边字符串比右边大返回1


select reverse('abc');
#将字符串abc反转


select concat ('abc','123');
select concat ('abc',' ','123');


select concat(Region,'+',Store_Name) from location where store_name='Los Angeles';
#将店名为Los Angeles的两个字段拼接成一个字符串

select concat(Region,'+',Store_Name) from location where store_name='New York';
#将店名为New York的两个字段拼接成一个字符串


select concat('Region','+','Store_Name') from location where store_name='New York';
#加单引号对字段拼接


select region || store_name from location;
#将两个字段拼接起来


select region ||' ' ||store_name from location;
#将两个字段拼接，空格分隔

截取

select * from location;

select substr(store_name,5) from location where store_name='Los Angeles';
#截取Angeles


select substr(store_name,5,6) from location where store_name='Los Angeles';
#只截取Angele


select substr(store_name,1,3) from location where store_name='New York';
#提取New


select substr(store_name,1,3) from location where store_name='Los Angeles';
#提取Los

替换

select region from location;
#查看location表的region字段

select replace(region,'st','stern') from location;
#将st替换成stern

select replace(region,'st','stern'),store_name from location;


select replace(region,'st','stern'),store_name,length(store_name) from location;
#获取店名长度


select trim(leading 'abc' from 'abccda');
#删除指定开头字符串


select trim(trailing 'cda' from 'abccda');
#删除指定结尾字符串


select trim(both 'a' from 'abccba');
#开头和结尾删除


select * from location;

select store_name from location where store_name='Los Angeles';
#过滤店名为Los Angeles

select trim(leading 'Los' from (select store_name from location where store_name='Los Angeles'));
#删除指定开头的字符串


select store_name from location where store_name='New York';

select trim(trailing 'York' from (select store_name from location where store_name='New York'));
#删除指定结尾的字符串

1.5 语句

order by 字段  ASC|DESC               #排序
group by 字段                         #分组
group by 字段  having 条件表达式        #根据group by分组后的结果再进行条件过滤

1.5.1 GROUP BY

  • 对GROUP BY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
格式：SELECT "字段1", SUM("字段2") FROM "表名" GROUP BY "字段1";

select * from store_info;

select * from store_info group by store_name;
#根据店名进行按升序排序

select store_name,count(store_name) from store_info group by store_name;
#统计有几家店

sql语句的执行顺序

FROM  #确定表
<left table>

ON
<join_condition>

<join_type>
JOIN
<right_table>

WHERE
<where condition>

GROUP BY   #对字段进行分组和汇总
<group_by_list>

HAVING
<having_condition>

SELECT   #相关查询

DISTINCT
<select list>

ORDER BY  #语句排序
<order_by_condition>

LIMIT
<limit number>

select store_name,sum(sales) from store_info group by store_name;
#对字段进行分组汇总

select store_name,sum(sales) from store_info group by store_name order by sum(sales);
#根据销售总额进行排序

select store_name,sum(sales) from store_info group by store_name order by sum(sales) desc;
#根据销售总额进行降序排序

select store_name,sum(sales) from store_info group by store_name order by sum(sales) desc limit 1;
#对语句进行分页



1.5.2 HAVING

  • 过滤由 GROUP BY 语句返回的记录集，通常与 GROUP BY 语句联合使用，HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
语法：SELECT "字段1", SUM("字段2") FROM "表格名" GROUP BY "字段1" HAVING (函数条件);

select store_name,sum(sales) from store_info group by store_name having sum(sales) > 1000;
#having在group by之后,查找总销售额大于1000的字段

通过sql查找到门店数大于等于2的地区

select * from store_info;

select store_name from store_info group by store_name having count(store_name) >= 2;
#查找门店数大于等于2的地区

select store_name,count(store_name) from store_info group by store_name having count(store_name) >=2;
#统计门店数大于等于2的数量

1.5.3 别名

  • 字段别名，表格别名，只在当前sql语句中生效
语法：SELECT "表格別名"."字段1" [AS] "字段別名" FROM "表格名" [AS] "表格別名";

select store_name,count(store_name) as num from store_info group by store_name having count(store_name) >=2;

select store_name,count(store_name) num from store_info group by store_name having count(store_name) >=2;

select store_name,count(store_name) num from store_info group by store_name having num >=2;

1.6 查询

1.6.1 子查询

  • 作用：连接表格，在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句
语法：

SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] 	   #外查询

SELECT "字段1" FROM "表格2" WHERE "条件";					  #内查询

select * from location;

select store_name from location where region='west';
#查找字段west的店名

#一条语句中有2个select语句，两个语句会分哪个先执行，内查询语句的结果会作为外查询的条件
#外查询                                                 #内查询
select sum(sales) from store_info where store_name in (select store_name from location where region='west');
#查找出匹配的店名总销售额

select * from store_info;



select * from store_info;

insert into store_info values ('nanjing',1000,'2023-09-20');

select sum(A.sales) from store_info as A where A.store_name in (select B.store_name from location as B where A.store_name=B.store_name);
#查询location表中和store_info表中店名相同的字段的总销售额


1.6.2 EXISTS

  • 用来测试内查询有没有产生任何结果，类似布尔值是否为真，类似if-else语句

  • 如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果

语法：
SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

select sum(sales) from store_info where store_name in (select store_name from location where region='Eastern');

select sum(sales) from store_info where exists (select store_name from location where region='Eastern');

select sum(sales) from store_info where exists (select store_name from location where region='East');

select sum(sales) from store_info where exists (select store_name from location where region='West');

1.7 表连接

inner join    内连接   #只返回两个表的字段相等的行记录
left join     左连接   #返回左表所有的行记录和右表字段相等的行记录，不相等的行返回NULL
right join    右连接   #返回右表所有的行记录和左表字段相等的行记录，不相等的行返回NULL
union         联集     #将两个select查询语句的结果合并，并去重
union all     联集     #将两个select查询语句的结果合并，不去重

1.7.1 inner join 内连接

UPDATE store_info SET store_name='Washington' WHERE sales=300;

select * from location;

select * from store_info;


select * from location as A inner join store_info as B on A.store_name = B.store_name;
#内连接，两个表有交集的行数据

1.7.2 left join 左连接

select * from location as A left join store_info as B on A.store_name = B.store_name;
#左连接，左表中独有的行数据以及右表中有交集的行数据

1.7.3 right join 右连接

select * from location as A right join store_info as B on A.store_name = B.store_name;
#右连接，右表中的独有的数据以及左表中有交集的行数据

1.7.4 多表查询

select * from location A,store_info B where A.store_name = B.store_name;
#多表查询，分别是别名A、别名B，A表字段等于B表字段

1.7.5 子连接

select * from store_info where store_name in (select store_name from location);
#子连接，查出有交集的行数


select * from location A,store_info B where A.store_name = B.store_name group by A.region;


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

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

1.7.6 UNION 联集

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

  • UNION ：生成结果的数据记录值将没有重复，且按照字段的顺序进行排序

语法：[SELECT 语句 1] UNION [SELECT 语句 2];

select * from location;

select * from store_info;

select store_name from location union select store_name from store_info;
#将两个表店名联集在一起

1.7.7 UNION ALL 联集

  • 将生成结果的数据记录值都列出来，无论有无重复
语法：[SELECT 语句 1] UNION ALL [SELECT 语句 2];

select store_name from location union select store_name from store_info;
#将两个表店名联集在一起,不去重

1.8 求交集

#内连接
select A.字段 from 左表 A inner join 右表 B on A.字段 = B.字段；
select A.字段 from 左表 A inner join 右表 B using(同名字段);

#多表查询
select A.字段 from 左表 A,右表 B where A.字段 = B.字段；

#子连接
select A.字段 from 左表 A where A.字段 in (select B.字段 from 右表 B)；

#左连接
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is not null;

#右连接
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is not null;



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

select B.store_name from location A,store_info B where A.store_name = B.store_name;

select A.store_name from location A where A.store_name in (select B.store_name from store_info B);
1.8.1 求左表无交集
select A.字段 from 左表 A left join 右表 B on A.字段 = B.字段 where B.字段 is null;

select 字段 from 左表 where 字段 not in (select 字段 from 右表);
1.8.2 求右表无交集
select B.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A.字段 is null;

select 字段 from 右表 where 字段 not in (select 字段 from 左表);
1.8.3 求多表的无交集
select A.字段 from (select distinct 字段 from 左表 union all select distinct 字段 from 右表) A group by A.字段 having count(A.字段)=1;

create view 视图表名 AS select distinct 字段 from 左表 union all select distinct 字段 from 右表;
select 字段 from 视图表名 group by 字段 having count(字段) = 1;

select 字段 from 表 where 字段 like '通配符表达式';  % _
select 字段 from 表 where 字段 regexp '正则表达式';  ^ $ . * +

标签：info,name,MySQL,store,SQL,之多表,where,select,location
From： https://www.cnblogs.com/LJ69/p/17722049.html

相关文章

  • MySQL中的Statistics等待
    【作者】吴宙旭，携程数据库专家【问题描述】线上我们偶尔会碰到MySQL的状态是statistics.但如果出现大量的statistics等待，会引起MySQL性能急剧下降。官方的文档对这个状态的解释如下：Theserveriscalculatingstatisticstodevelopaqueryexecutionplan.Ifathreadis......
  • nods中mysql时间相差8小时
    前言最近在做自己的一个记账项目，后端nestjs中使用typeorm的mysql。当添加记录时，发现所以时间都相差了8小时。后面查了一下资料发现因为默认timezone是用UTC的。所以只需要设置成我们自己的时区即可。解决方法ormconfig.json{"type":"mysql","host":"localhost","po......
  • Exam DP-300: Administering Microsoft Azure SQL Solutions 微软Azure SQL Solutions
    作为该考试的考生，您应具备构建数据库解决方案方面的主题专业知识，这些解决方案旨在支持使用数据库构建的多种工作负载：企业内部SQLServerAzureSQL服务您是一名数据库管理员，负责管理使用SQLServer和AzureSQL服务构建的内部部署和云数据库。作为Azure数据库管理员，您......
  • PG-DBA培训16：PostgreSQL负载均衡分发与双主HA架构
    一、风哥PG-DBA培训16：PostgreSQL负载均衡分发与双主HA架构本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL负载均衡分发与双主HA架构，学完本课程可以掌握PostgreSQL高可用负载均衡解决方案，PostgreSQL+Keepalived流复制......
  • PG-DBA培训17：PostgreSQL连接池管理与代理分发
    一、风哥PG-DBA培训17：PostgreSQL连接池管理与代理分发本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL连接池管理与代理分发，学完本课程可以掌握PostgreSQL连接池管理与代理分发之Pgbouncer，Pgbouncer连接池技术基础，Post......
  • PG-DBA培训19：PostgreSQL高可用集群项目实战之Patroni
    一、风哥PG-DBA培训19：PostgreSQL高可用集群项目实战之Patroni课程目标：本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL高可用集群项目实战之Patroni，学完本课程可以掌握Patroni运行架构与基础知识，PostgreSQL+Patroni集......
  • PG-DBA培训20：PostgreSQL逻辑复制技术与项目实战
    一、风哥PG-DBA培训20：PostgreSQL逻辑复制技术与项目实战本课程由风哥发布的基于PostgreSQL数据库的系列课程，本课程属于PostgreSQL主从复制与高可用集群阶段之PostgreSQL逻辑复制技术与项目实战，学完本课程可以掌握PostgreSQL逻辑复制基础与架构，PostgreSQL逻辑复制配置之内置默认，Post......
  • MySQL 主从复制与读写分离
    MySQL主从复制与读写分离1、什么是读写分离？读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。2、为什么要读写分离呢？因为数据库的“写”（写10000......
  • docker 部署mysql8.3.0
    以下内容全部来源于https://blog.csdn.net/Weirdo_zhu/article/details/126358872?spm=1001.2014.3001.5501一、拉取镜像dockerpullmysql:8.0.30二、创建挂载路径 //根目录选择自身实际最大磁盘路径一般为/home或者定义/datamkdir-p/data/mysql/datamkdir-p/data/m......
  • 将hive数据库中的数据导入到mysql数据库中时需要注意到的问题
    在hive中使用jdbc将hive与mysql连接起来时一定要注意到每个数据库不同的字段个数，在打算直接复制时，不能只是修改表的名称和字段名称，还要记得修改几个？那里，个数要与字段名称保持一致！！！不然就会报错（比如我）；......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99