mysql的高级查询语句

目录

一、本文前言

二、高效查询方式

1）指定指字段进行查看

2）对字段进行去重查看

3）where条件查询

4）and 和 or 进行逻辑关系的增加

5）查询取值列表中的数据

6）between的引用 

7）like的查询方式 

8）排序方式进行查询

三、运用函数查询 

1）数据库中常用数学的函数

2）聚合函数

3）字符串函数

（1）去除字符 trim 

（2） 截取 substr 

（3）字段拼接 

（4）返回字符长度 length

（5）替换 replace 

四、高级查询语句 

1）GROUP BY（用于分组和汇总） 

（1）汇总统计

（2）汇总并对其指定字段（数字类）进行累加

（3）汇总并对其指定字段（数字类）进行累加，再进行降序 

2）HAVING 过滤

3）别名设置查询

语法格式：

（1）字段别名

​编辑

（2）表别名

4）表的自我连接 

（1）无重复数值排名 

（2）有重复数值排名 

5）子查询语句 

子查询运用升级

6）EXISTS 

格式：

五、表连接查询 

（1） 内连接 inner join 

（2）左连接 left join

（3）右连接 right join 

六、view 视图的运用 

1）视图的创建 

2） 视图提供的后续便捷操作 

3）经典定义问题：视图能否插入数据  

七、UNION 联级 

1）UNION（合并后去重）

2）UNION ALL（合并后不去重） 

八、多种方式求表与表的交集值 

1）联级视图求交集值

2）内连接求交集值  

（1）不去重求交集 

（2）去重求交集

3）使用左连接求交集值

4）使用右连接求交集 

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

6）取非交集值 

九、case 条件选择查询语句

十、正则表达式的运用

1）sql正则表达式的常见种类 

2）sql正则运用

探究：空值（NULL）和无值（' '）的区别 

---

一、本文前言

数据库是用来存储数据，更新，查询数据的工具，而查询数据是一个数据库最为核心的功能，数据库是用来承载信息，而信息是用来分析和查看的。所以掌握更为精细化的查询方式是很有必要的。本文将围绕数据的高级查询语句展开

为了下面查询语句的运用，事先准备了两张表，后续也会根据查询功能的运用会对表进行一些变动，或则创建新表： 

CREATE TABLE info (id int(4) ,name char(4),height double 
) ;CREATE TABLE info2 (name char(4) hobby char(10) date char(10)
) ;

二、高效查询方式

1）指定指字段进行查看

select 字段1，字段2 from 表名;

2）对字段进行去重查看

 SELECT DISTINCT "字段" FROM "表名";

3）where条件查询

 SELECT "字段" FROM 表名" WHERE "条件";

4）and 和 or 进行逻辑关系的增加

SELECT "字段" FROM "表名" WHERE "条件1"  AND "条件2";

 SELECT "字段" FROM "表名" WHERE "条件1"  OR "条件2";

5）查询取值列表中的数据

 SELECT "字段" FROM "表名" WHERE "字段" IN ('值1'， '值2'， ...);   #in,遍历一个取值列表

6）between的引用 

 SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';

7）like的查询方式 

like查询通常会与通配符配合使用

%：百分号表示零个、一一个或多个字符

 _：划线表示单个字符

select * from info2 where hobby like '%ing';
select * from info2 where name like '小_';

select * from info2 where name like '_刚';select * from info2 where hobby like '%ay%';

8）排序方式进行查询

order by，按关键字排序

注意：

• 一般对数值字段进行排序
• 如果对字符类型的字段进行排序，则会按首字母排序

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

  select * from info  order by height;select * from info  order by height asc;select * from info  order by height desc;

三、运用函数查询 

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);

SELECT truncate(1.89,2);
SELECT truncate(1.89,1);

select ceil(1.76);
select floor(1.76);

select greatest(1,2,3,55,12,55,61);
select least(1,2,3,55,12,55,61);

2）聚合函数

聚合函数	含义
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(字段)	返回指定列的所有值之和

select avg(height) from info;

select count(name) from info;
select count(*) from info;

select max(height) from info;
select min(height) from info;
select sum(height) from info;

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)	返回 x 个空格
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
reverse(x)	将字符串 x 反转

（1）去除字符 trim 

 SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);​#[位置]：值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。 #[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。

select trim(leading 's' from 'swmming' );
select trim(trailing 'g' from 'swmming' );
select trim(both 'l' from 'lol' );

（2） 截取 substr 

substr(x,y)    #截取x字符串 从第y个开始，截取到末尾substr(x,y,z)  #截取x字符串 从第y个开始截取 ，截取长度为z

select substr(hobby,2) from info2;
select substr(hobby,3) from info2;

 select substr(hobby,2,5) from info2;select substr(hobby,4,6) from info2;

（3）字段拼接 

① concat(x,y)

select concat(name,height) from info;

② 使用 || 符号

 #将info表中，name字段值和height字段值拼接在一起。select name || height from info;

 #将info表中，name字段值和height字段值拼接在一起，且中间加空格。select name || ' ' || height from info;

（4）返回字符长度 length

 select length(hobby) from info2;

（5）替换 replace 

select replace(name,'小','大') from info2;

四、高级查询语句 

1）GROUP BY（用于分组和汇总） 

对GROUPBY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的

• "GROUP BY"有一个原则，凡是在"GROUP BY"后面出现的字段，必须在SELECT 后面出现
• 凡是在SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在"GROUP BY"后面

（1）汇总统计

select name, count(name) from info group by name;

（2）汇总并对其指定字段（数字类）进行累加

select name,sum(saving) from info3 group by name;

（3）汇总并对其指定字段（数字类）进行累加，再进行降序 

select name,sum(saving) from info3 group by name order by sum(saving) desc;

2）HAVING 过滤

• 用来过滤由"GROUP BY"语句返回的记录集，通常与"GROUP BY"语句联合使用
• HAVING语句的存在弥补了WHERE 关键字不能与聚合函数联合使用的不足
• where只能对原表中的字段进行筛选，不能对group by后的结果进行筛选

 SELECT 字段1,SUM(字段2) FROM "表格名" GROUP BY 字段1 HAVING(函数条件) ;

select name,sum(saving) from info3 group by name having sum(saving)>1500;

3）别名设置查询

语法格式：

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

（1）字段别名

select name,sum(saving) as total_saving  from info3  group by name having sum(saving)>1000;
select name,sum(saving) as total_saving  from info3  group by name having total_saving>1000;

（2）表别名

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

4）表的自我连接 

（1）无重复数值排名 

对下面的表进行saving比较并且进行排名通过表的自我连接进行实现

 表的自我连接达到排名的原理分析及操作思路：

1. 以上面的数据表为例，假设共有四个人，他们手中的金额各不相同。我们已经进行表的自我连接
2. 使用count计数，只计数大于等于自身手上金额的人数，比如2000的小明，大于等于他的人数只有1个，就计数值也可以当作他的排名
3. 再比如800的小红，大于等于她的有4个人，就该计数值为4，同理可以证明她排名第四

select A.name,A.saving,count(A.saving) as rank from info3 as A,info3 as B where A.saving <=B.saving group by A.name,A.saving  order by rank asc;

（2）有重复数值排名 

新需求表：

select A.name,A.saving,count(A.saving) as rank from info3 as A,info3 as B where A.saving < B.saving or (A.name=B.nameme and A.saving=B.saving) group by A.name,A.saving  order by rank asc;

5）子查询语句 

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

 SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符]     #外查询(SELECT "字段1" FROM "表格2" WHERE "条件") ;              #内查询

普通的表数据连接： 

select * from info as A, info3 as B where A.name=B.name;

子查询加入表连接 ： 

select * from info where name in(select name from info3 where saving > 1000);

子查询运用升级

求“北京地区”的所有saving值之和

select sum(saving) from info3   where name in (select name from info  where address='北京');

6）EXISTS 

• 用来测试内查询有没有产生任何结果，类似布尔值是否为真。
• 如果内查询有结果的话，系统就会执行外查询中的SQL语句。若是没有结果的话，那整个SQL语句就不会产生任何结果

格式：

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

五、表连接查询 

MYSQL数据库中常用的表连接有三种： 

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

• left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录
• A  left  join  B  ： A为左表，B为右表

• right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录
• A  right join  B：  A为左表 ，B为右表

（1） 内连接 inner join 

select * from info A inner join info3 B on A.name = B.name;

其他实现内连接的方式：

 select * from info A, info3 B where A.name=B.name;select * from info A inner join info3 B using(name);

（2）左连接 left join

select * from info A LEFT JOIN info3 B on A.name=B.name;

（3）右连接 right join 

select * from info3 A right join info B on A.name=B.name ;

六、view 视图的运用 

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

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

格式：

CREATE VIEW "视图表名" AS "SELECT 语句";   #创建视图表​
DROP VIEW "视图表名";                     #删除视图表

1）视图的创建 

创建需求：独立创建一个视图，用来统计info和info3 两张表之间，先对name进行address的汇总，再计算地区的saving之和 。要求体现出address  和sum（sving） 两个字段

create view v_address_saving as select A.address,sum(B.saving) total_saving from info A inner join info3 B on A.name=B.name group by address;

视图创建的数据验证：

2） 视图提供的后续便捷操作 

视图的好处：创建视图的过程虽然和高级查询语句（通过两个select语句进行组合条件划分生成派生表）一样，过程是复杂的，但是如果该查询操作是需要经常使用的，创建视图就很有必要，不仅能简化查询过程，还能对该查询进行进一步操作，而且十分简便

进一步需求： 需要计算出苏州和上海两个地区的saving之和

select sum(total_saving) as suzhou_shanghai_saving from v_address_saving where address='苏州'or address='上海';

3）经典定义问题：视图能否插入数据  

视图能否插入数据，要看情况而定： 

（1）如果视图表是两个表的连接查询（比如视图的A字段来自A表，B字段来自B表，数据是无法插入的）。因为表结构和原表不一致。视图中的字段是根据原表中某个字段，通过函数运算，产生的新字段，而没有真正能够存储的字段，所以该数据是无法插入的

（2）如果视图表结构与原表保持一致，数据是可以插入的，插入的数据是存储在原表中，视图所更新出的数据，其实是映射原表的数据

show create view  分析创建视图的过程：  

七、UNION 联级 

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

1）UNION（合并后去重）

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

格式：[select 语句1] UNION [select 语句2];

select name from info union select name from  info3;

2）UNION ALL（合并后不去重） 

select name from info union all select name from  info3;

八、多种方式求表与表的交集值 

1）联级视图求交集值

create view v_info as select distinct name from info union all select distinct name from info3;​
select name,count(*) from v_info group by name;​select name from v_info group by name having count(*) >1;

2）内连接求交集值  

（1）不去重求交集 

select A.name from info A inner join info3 B on A.name=B.name;​
select A.name from info A inner join info3 B using(name);

（2）去重求交集

select distinct A.name from info A inner join info3 B using(name);

3）使用左连接求交集值

select * from info A left join info3 B using(name);
select distinct A.name from info A left join info3 B using(name) where B.name is not null;

4）使用右连接求交集 

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

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

select distinct name from info where name in (select name from info3);

6）取非交集值 

（1）联级方法中 count（*）<=1

  (2）左右内连接  将is not null  改为  is  null

（3）子查询 外连接查询 not in （内连接查询）

九、case 条件选择查询语句

 SELECT CASE ("字段名")WHEN "条件1" THEN "结果1"WHEN "条件2" THEN "结果2"[ELSE "结果N"]ENDFROM "表名";# "条件"可以是一个数值或是公式。ELSE子句则并不是必须的。

mysql> select address,case address-> when '上海' then height-10-> when '北京' then height+5-> else height+10-> end-> "new_height",name-> from info;

十、正则表达式的运用

1）sql正则表达式的常见种类 

正则符号	作用
^	匹配文本的开始字符
$	匹配文本的结束字符
.	匹配任何单个字符
*	匹配零个或多个在它前面的字符
+	匹配前面的字符 1 次或多次
字符串	匹配包含指定的字符串
l	或，“|”前面的不成立时，就匹配后面的字符串
[...]	匹配字符集合中的任意一个字符
[^...]	匹配不在括号中的任何字符
{n}	匹配前面的字符串 n 次
{n,m}	匹配前面的字符串至少 n 次，至多m 次

2）sql正则运用

格式：

select "字段" from "表名" where "字段" regexp '正则表达式';

探究：空值（NULL）和无值（' '）的区别 

无值的长度为0，不占用空间；而NULL值的长度是NULL，是占用空间的

IS NULL或者IS NOT NULL，是用来判断字段是不是为NULL或者不是NULL，不能查出是不是无值的

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

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