mysql高级语句

最新推荐文章于 2023-09-03 16:33:47 发布

算了，还是睡吧！

最新推荐文章于 2023-09-03 16:33:47 发布

阅读量45

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_59629968/article/details/127551667

版权

一.准备两个表：表一：location

表二：store_info

distinct用法：去重

like:模糊查询：

order by：按关键字进行排序：

SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC|DESC] ; #ASC是按照升序进行排序的，是默认的排序方式。 #DESC是按降序方式进行排序。

group by（用于分组和汇总）

having：对分组内容进行再过滤

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

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

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

替换 replace

别名：字段别名、表別名

SELECT 字段1,字段2 AS 字段2的别名 from 表名; #AS可以省略不写

字段别名：

表别名：

SELECT 表格别名.字段1 [AS] 字段别名 FROM 表格名 [AS] 表格别名; #AS可以省略不写

表的自我连接：

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

子查询：

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

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

语法:

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

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

#内查询 #可以是符号的运算符，例如=、>、<、>=、<= ;也可以是文字的运算符，例如LIKE、 IN、BE

right join（右连接）

inner join（内连接）

inner join(内连接)：只返回两个表中联结字段相等的行。

left join（左连接）

二、视图

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

视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。
临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。 比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写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; #删除视图表

视图表与派生表比较

派生表，两个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; #对视图表进行操作

视图表能否插入数据

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

如果视图表的结构和原表结构一致，可以修改和插入数据。
- 例如原表有3个字段，视图表有2个字段，只要这2个字段和原表结构是一致的，也可以修改和插入数据。（只要该视图表是对单个表的查询结果即可）。

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

#该视图表是两个表的连接查询 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);

如果视图表的结构和原表结构一致，可以修改和插入数据。（只要该视图表是对单个表的查询结果即可）

#该视图表只是对单个表的查询结果。

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语句所产生的字段需要是同样的数据记录种类。

UNION（合并后去重）

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

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语句结果的交集。

使用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;

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

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

五.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那个字段的字段名。

#注：查询并不改变原表的值。