526互联

MySQL高级SQL语句之多表连接

发布时间 2023-09-22 14:06:57作者: Candy独角兽

目录

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 '正则表达式';  ^ $ . * +