进阶查询（2）

寒_嗷呜
130 阅读5分钟

3.1 group by（用于分组和汇总 去重）

group by有一个原则，凡是在group by后面出现的字段，必须在select后面出现

凡是在select后面出现的、且未在聚合函数中出现的字段，必须出现在group by后面

语法：

select 字段1,sum(字段2) from 表名 group by 字段1;

示例：

select site,sum(money) from fare group by site;
##对site字段值进行分组汇总，并计算每组的销售额，及分组后求和
​
select site,sum(money) from fare group by site order by sum(money) desc;
##对site字段值进行分组汇总，并计算每组的销售额,并进行降序排序

Snipaste_2022-10-28_11-26-56.png

3.2 having （和group by结合使用）

用来过滤由"GROUP BY"语句返回的记录集，通常与"GROUP BY"语句联合使用。

HAVING语句的存在弥补了WHERE 关键字不能与聚合函数联合使用的不足。

where只能对原表中的真实字段进行筛选，不能对group by后的结果进行筛选。

where 真实字段条件

having 聚合函数条件

语法：

select 字段1,sum(字段2) from 表名 group by 字段1 having(函数条件);

示例：

select site,sum(money) from fare group by site having sum(money) > 1000;
##对site字段值进行分组汇总，并计算每组的销售额，之后显示销售额超过1000的数据记录

Snipaste_2022-10-28_11-31-20.png

3.3 别名——字段别名、表别名

语法：

select 字段1,字段2 [AS] 字段2的别名 from 表名;   ##AS可以省略
​
示例：
select site,sum(money) as total_money from fare group by site having total_money > 1000;
#设置sum(sales)字段的别名为total_sales。
#对site字段进行分组汇总，并计算组内销售额之和，之后查询出组销售额大于1000的记录。

Snipaste_2022-10-28_11-46-46.png

3.4 表的自我连接

例如：对销售额字段进行排名，并显示名次

#count统计的是A表中销售额的每个值小于等于B表中每个值的次数。（A和B都是fare的别名，即表的自我连接，自己跟自己对比。）
select A.site,A.money,count(A.money) rank from fare as A,fare as B where A.money <= B.money group by A.site,A.money order by rank asc;

Snipaste_2022-10-28_11-50-20.png

如果表中有相同数值的销售额，使用如下语句：

select A.site,A.money,count(A.money) rank from fare as A,fare as B where A.money < B.money or (A.money=B.money and A.site=B.site) group by A.site,A.money order by rank asc;

Snipaste_2022-10-28_11-51-27.png

3.5 子查询

子查询：连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL语句

语法：

SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符]     #外查询
 (SELECT "字段1" FROM "表格2" WHERE "条件") ;             #内查询
 
#可以是符号的运算符，例如=、>、<、>=、<= ;也可以是文字的运算符，例如LIKE、 IN、BETWEEN
​
#先执行内查询，再执行外查询。内查询的查询结果，作为外查询的条件

示例：两张表连接在一起

select * from fare A, region B where A.site=B.site;

Snipaste_2022-10-28_11-56-55.png

示例2：

select money from fare where site in   ##外查询
(select site from region where region='north')  ##内查询 查询北部区域的地区名
#内查询先查询出北部区域的地区名，之后外查询遍历这个地区名列表，查询出每个地区名对应的销售额。

Snipaste_2022-10-28_11-59-50.png

示例3：

select sum(money) from fare where site in   ##外查询
(select site from region where region='north')  ##内查询 查询北部区域的地区名
#内查询先查询出北部区域的地区名，之后外查询遍历这个地区名列表，查询出地区名对应的总销售额

Snipaste_2022-10-28_12-00-57.png

示例4：

select sum(money) from fare A where A.site in
(select site from region B where B.site=A.site);
​
#内查询先查询出B表中和A表相同的地区名，之后外查询遍历这个地区名列表，求出列表中所有地区的销售额之和。

Snipaste_2022-10-28_12-02-14.png

3.6 EXISTS

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

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

语法：

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

示例：

#内查询有结果的话，系统就会执行外查询中的SQL语句
select sum(money) from fare where exists (select * from region where region = 'north');
​
#内查询没有产生结果，会返回NULL值
select sum(money) from fare where exists (select * from region where region = 'sales');

Snipaste_2022-10-28_12-04-23.png

表连接查询

MySQL数据库中的三种连接

inner join(内连接) ：只能返回两个表中连接字段相等的行（有交集的值）

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

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

注：

oracle数据库支持 outer join(外连接)，mysql不支持

语法：

select 字段 from 左表 inner/left/right join 右表 on 左表.字段 = 右表.字段;
​
##内连接  返回  两个表字段相等的行记录
##左连接  返回  左表所有的行记录和右表中的与左表相等的行记录
##右连接  返回  右表所有的行记录和左表中的与右表相等的行记录
​
内连接二
select 字段 from  左表,右表 where 左表.字段 = 右表.字段  

1.1 left join（左连接）

left join(左连接)：返回包括左表中的所有记录和右表中连接字段相等的记录
​
select * from region A left join fare B on A.site=B.site;

Snipaste_2022-10-28_12-50-01.png

1.2 right join (右连接)

right join(右连接)：返回包括右表中的所有记录和左表中连接字段相等的记录
​
select * from region A right join fare B on A.site=B.site;

Snipaste_2022-10-28_12-51-01.png

1.3 inner join（内连接）

inner join(内连接)：只能返回两个表中连接字段相等的行（有交集的值）
​
方法一：select * from region A inner join fare B on A.site=B.site;
​
方法二：select * from region A,fare B where A.site=B.site;
​
方法三：select * from region A inner join fare B using(site);  #using()是函数，必须保证括号内的字段在两个表中是同名的

Snipaste_2022-10-28_12-53-57.png

Snipaste_2022-10-28_12-54-50.png

视图

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

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

定义：

视图是基于SQL语句的结构集的可视化的表

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样

作用：

简化复杂的SQL操作，比如复杂的连接；

只使用实绩表的一部分数据

通过只给用户访问视图的权限，保证数据的安全性

更改数据格式和表示

总结：

VIEW 视图表不实际存储表数据记录，而包含是select语句的定义，视图表的查询结果会随着select语句查询的实际数据的变化而变化

如果向视图表的更新数据，如果定义的select语句使用的是真实存在的表格字段，是可以更新原表数据的

如果定义的select语句使用的是不是真实存在的表格字段，而是通过函数或其它方式得到，是不可以更新原表数据的

语法：

create view "视图表名" AS "select 语句";  ##创建视图表
​
drop view "视图表名";  ##删除视图表
​
示例：
#将两个表的连接查询，创建为视图表
create view v_fr as select A.region,sum(B.money) from region A inner join fare B on A.site=B.site group by region;
​
select * from v_fr;     #查看视图表的存储结果

Snipaste_2022-10-30_21-09-05.png

UNION --联集

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

UNION ——合并后去重

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

[select 语句1] UNION [select 语句2];
​
示例：
select site from region union select site from fare;
#将两个表的site字段的值合并起来，union生成的结果去重

Snipaste_2022-10-30_21-12-11.png

UNION ALL ——合并后不去重

将生成结果的数据记录值都列出来，无论有无重复

select site from region union all select site from fare;
#将两个表的site字段的值合并起来，union all生成的结果不去重

Snipaste_2022-10-30_21-12-46.png

交集值

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

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

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

#两个表各自将site字段的值进行不去重，之后合并，再创建视图表。
create view v_site as select  site from region union all select  site from fare;
​
#对视图表的site字段进行分组汇总，计算每组的数量，过滤出数量大于1的site字段值，就是两个表的交集部分。
select site from v_site group by site having count(site) >1;

Snipaste_2022-10-30_21-31-01.png

Snipaste_2022-10-30_21-33-35.png

使用内连接求交集值

select A.site from region A inner join fare B on A.site=B.site;
或
select A.site from region A inner join fare B using(site);

Snipaste_2022-10-30_21-35-54.png

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

select distinct A.site from region A inner join fare B using(site);

Snipaste_2022-10-30_21-37-04.png

使用左连接求交集值

#使用左连接查出site字段的交集值，之后去重
select distinct B.site from region A left join fare B using(site) where B.site is not null;
或
select distinct B.site from region A left join fare B on A.site=B.site where B.site is not null;

Snipaste_2022-10-30_21-41-28.png

使用右连接求交集值

 #使用右连接查出site字段的交集值，之后去重
select distinct A.site from region A right join fare B using(site) where A.site is not null;
或
select distinct A.site from region A right join fare B on A.site=B.site where A.site is not null;

Snipaste_2022-10-30_21-48-57.png

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

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

Snipaste_2022-10-30_21-50-17.png

无交集值

无交集值：显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

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

#两个表各自将site字段的值进行不去重，之后合并，再创建视图表。
create view v_site as select  site from region union all select  site from fare;
​
#对视图表的site字段进行分组汇总，计算每组的数量，过滤出数量等于1的site字段值，就是两个表的无交集部分。
select site from v_site group by site having count(site) =1;

Snipaste_2022-10-30_21-51-42.png

使用左连接求无交集值

select A.site from region A left join fare B using(site) where A.site is null;

Snipaste_2022-10-30_21-51-55.png

空值（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');

Snipaste_2022-10-30_22-10-08.png

示例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的数据记录

Snipaste_2022-10-30_22-11-04.png

示例3：

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

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

Snipaste_2022-10-30_22-12-34.png

示例4：

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

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

Snipaste_2022-10-30_22-14-18.png

CASE

是SQL用来做为IF-THEN-ELSE之类逻辑的关键字

语法：

select case ("字段名")
  when "条件1" then "结果1"
  when "条件2" then "结果2"
  [else "结果N"]
  end
from "表名";
##条件可以是一个数值或者公式。else子句则并不是必须的

示例：

select site,case site
   when 'beijing' then money * 2
   when 'shenzhen' then money /2
   else  money + 500
   end  'new_m'
   from  fare;
##new_m是别名

Snipaste_2022-10-30_22-19-01.png

正则表达式

匹配模式描述实例
^匹配文本的开始字符'^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 {模式};

示例

#查询site字段包含字符串"in"的行
select * from fare where site regexp 'in';
​
#查询site字段以A-G开头的行
select * from fare where site regexp '^[A-G]';
​
#包含ji或者on
select * from fare where site regexp 'ji|on'; 
​
#以字符串“on”为结尾
select * from fare where site regexp 'on$';
​
#以“ho”开头且以“ng”为结尾
select * from fare where site regexp '^ho.*ng$';

Snipaste_2022-10-30_22-25-46.png

Snipaste_2022-10-30_22-26-50.png

avatar
文章
阅读
粉丝