mysql高级语句

最新推荐文章于 2024-11-07 22:57:55 发布
最新推荐文章于 2024-11-07 22:57:55 发布
阅读量461 收藏 1
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42054864/article/details/132802893
版权

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

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

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

 

1.高效查询方式 

1.1指定指字段进行查看

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

1.2 对字段进行去重查看  

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

 1.3  where条件查询

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

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

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

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

 1.5 查询取值列表中的数据 

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

1.6  between的引用

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

 1.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%'; 

1.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;

2.运用函数查询 

2.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位小数的值 #不四舍五入
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.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;

 2.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）字段拼接 

1）concat(x,y)

select concat(name,height) from info;

 2)使用 || 符号

  #将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;

 

3.高级查询语句

3.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;

 

 3.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.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可以省略不写

 3.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; 

 3.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='北京');


 

  3.6 EXISTS 

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

格式：

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

 

 4. 表连接查询 

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 ;

 5. 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之和

elect 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  分析创建视图的过程： 

 6.UNION 联级

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

6.1 UNION（合并后去重）

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

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

select name from info union select name from  info3;

 

6.2 UNION ALL（合并后不去重） 

select name from info union all select name from  info3;

  7. 多种方式求表与表的交集值

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

 

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

 

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

 

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

 

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

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

 

7.6 取非交集值 

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

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

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

8. case 条件选择查询语句 

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

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

 

9. 正则表达式的运用 

9.1 sql正则表达式的常见种类

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

9.2 sql正则运用  

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

 

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

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

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

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

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

Lamour d'une vie
关注
MySQL高级SQL语句
weixin_55609814的博客
06-30 2万+
MySQL高级SQL语句MySQL高级SQL语句函数数学函数聚合函数字符串函数 MySQL高级SQL语句 use kgc; 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',
MySQL高级语句
sukapulai的博客
06-01 555
显示表格中一个或数个字段的所有数据记录不显示重复的数据记录有条件查询且 或显示己知的值的数据记录显示两个值范围内的数据记录语法:SELECT"字段"TROM"表名"WHERE"字段"BETWEEN值1AND1值2; SELECT * FROM store_info WHERE Date BETWEEN '2022-5-05' AND '2022-5-08';通常通配符都是跟 LIKE 一起使用的匹配一个模式来找出我们要的数据记录按关键字排序数学函数#如sql_mode开启了PIPES_AS_CONCAT,"
Mysql高级语句
weixin_54959847的博客
08-31 2407
需要注意 的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有 值的。ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序，ORDER BY 后面跟多个字段时，字段之间使用英文。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参 考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，
MySQL高级语句的用法
System_TuTu
06-17 1426
select查询sql语句的用法 查询不重复记录 where 有条件的查询 and 且 ； or 或 通配符通常是和 like 一起使用
MySQL高级语句（终极篇）
zhuwei的博客
06-30 2472
目录 一、MySQL高级语句1、SELECT------显示表格中一个或数个栏位的所有资料2、DISTINCT------不显示重复的资料3、WHERE------有条件查询4、AND|OR------且|或5、IN------显示已知的值的资料6、BETWEEN------显示两个值范围内的资料7、通配符8、LIKE------匹配一个模式来找出我们要的资料9、ORDER BY------按关键字排序10、函数（1）数学函数（2）聚合函数（3）字符串函数 11、GROUP BY------对G..
MySQL高级语句(一)
m0_67392273的博客
09-11 492
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
MySQL高级语句（一）
m0_67402013的博客
08-02 655
本章介绍了select的高阶语句orderby降序升序排序and和or的使用distinct的去重查询limit限制输出as的别名设置先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。httpshttpshttpshttps。...
修改Mysql 8 的密码
Stan Blog
11-07 599
推荐方法：使用ALTER USER语句修改密码。其他用户：修改其他用户密码时，只需替换用户名。忘记密码：通过跳过授权表重置密码。Windows部署Mysql指南外网连接参考此文档。
阿里云 DataWorks 正式支持 SelectDB & Apache Doris 数据源，实现 MySQL 整库实时同步
SelectDB
11-07 723
以上即为将 MySQL 整库实时同步至 SelectDB Cloud 的具体操作步骤。通过使用 Dataworks，您能够高效地将 MySQL 数据库同步至 SelectDB Enterprise、SelectDB Cloud、阿里云数据库 SelectDB 版与 Apache Doris 中，确保业务连续性不受影响。同时，凭借 SelectDB 极速分析性能和灵活弹性优势，您能够加速决策过程，优化业务运营效率，推动业务持续稳健增长。
ubuntu 22.04 server 安装 mysql 5.7.40 LTS
abc
11-06 381
ubuntu 22.04 server 安装 mysql 5.7.40 LTS。
MySQL常用的关键字（详细）
qq_54523516的博客
11-02 789
MySQL数据表的列名不能是关键字，除非使用反引号标识：`else`
MySQL报错解决：The user specified as a definer (‘root‘@‘%‘) does not exist
后端开发
11-07 205
这个问题通常是由于 MySQL 用户的访问权限设置不当，特别是root用户没有正确的全局权限。只需要为root用户授予全局访问权限并刷新权限，通常就可以解决该问题。如果视图仍然无法正常工作，可以检查并修改视图的Definer用户。
如何在 Java 中使用 Canal 同步 MySQL 数据到 Redis
最新发布
斌斌(Java)
11-07 529
特别是将 MySQL 数据实时同步到 Redis，可以显著提升应用的性能和响应速度。Canal 是阿里巴巴开源的一个数据库 Binlog 同步工具，可以实时捕获 MySQL 的 Binlog 日志并将其同步到其他存储系统。Canal的原理是基于mysql binlog技术，所以这里一定需要开启mysql的binlog写入功能，并且配置binlog模式为row。的内容，还要注意JDK版本及配置。在 MySQL 中插入、更新或删除数据，观察 Java 应用是否能够实时捕获这些变化并将数据同步到 Redis。
MYSQL作业五
So lukcy To meet you(づ ●─● )づ
11-03 240
建立触发器，订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单的数量，并测试 建立触发器，实现功能：客户取消订单，恢复商品表对应商品的数量 建立触发器，实现功能：客户修改订单，商品表对应商品数量同步更新 创建存储过程s1，提取emp_new表中所有员工姓名和工资 创建存储过程s2，实现输入员工姓名后返回员工的年龄 创建存储过程s3，有两个参数，传入部门号，返回该部门的平均工资
windows在两台机器上测试 MySQL 集群实现实时备份
weixin_44895040的博客
11-03 842
确保两台机器（假设为服务器 A 和服务器 B）能够互相通信，例如它们在同一个局域网内，并且开放了 MySQL 通信所需的端口（默认是 3306）。在整个测试过程中，如果遇到问题，可以查看 MySQL 的错误日志（通常在 MySQL 的数据目录下，文件名可能是error.log）来获取更多关于错误的详细信息，以便进行故障排除。编辑 MySQL 配置文件（通常是my.cnf或my.ini，位置因操作系统而异，如在 Linux 下可能在/etc/mysql/my.cnf）。
MySQL——事务
m0_75186846的博客
11-03 1218
MySQL关于事务的概念
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值