一、表连接查询
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(内连接)
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生成的结果去重
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)和无值(' ')的区别:
- 无值的长度为0,不占用空间;而NULL值的长度是NULL,是占用空间的。
IS NULL
或者IS NOT NULL
,是用来判断字段是不是为NULL或者不是NULL,不能查出是不是无值的。
- 无值的判断使用
=' '
或者< >' '
来处理。<>代表不等于。 - 在通过
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; #会统计所有行
八、正则表达式——精确查询
匹配模式 | 描述 | 实例 |
^ | 匹配文本的开始字符 | '^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 {模式};
示例:
#查询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$';