MySQL高级SQL语句

梅你不可的小东西
207 阅读6分钟

一、高级SQL语句（进阶查询）

1.1 select

select，显示表格中一个或数个字段的所有数据记录。

 语法: SELECT "字段" FROM "表名";
 #可以指定多个字段

微信截图_20221106180924.png

微信截图_20221106181043.png

1.2 distinct

distinct，不显示重复的数据记录。

注意：关系型数据库，单次只能对一个字段去重。

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

微信截图_20221106181323.png

1.3 where

where，有条件查询。

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

微信截图_20221106181533.png

1.4 and 和 or

and：且。

or：或。

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

示例：

 #查询销售额大于500且小于1000的数据记录。
 select * from store_info where sales > 500 and sales < 1000 ; 
 ​
 #查询销售额大于200且小于500的记录，或者销售额大于1000的记录。
 select * from store_info where sales > 1000 or (Sales < 500 and Sales > 200); 
 ​
 #查询销售额大于200且小于500的商店名，或者销售额大于1000的商店名。
 select store_name from store_info where sales > 1000 or (Sales < 500 and Sales > 200);

微信截图_20221106182425.png

1.5 in

in，显示已知的值的数据记录。

 SELECT "字段" FROM "表名" WHERE "字段" IN ('值1'， '值2'， ...); 
 #in,遍历一个取值列表
 SELECT "字段" FROM "表名" WHERE "字段" NOT IN ('值1'， '值2'， ...); 
 #not in取反，查询不在这个范围内的值

微信截图_20221106182818.png

1.6 between

between，显示两个值范围内的数据记录。

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

微信截图_20221106184239.png

微信截图_20221106184258.png

1.7 通配符

通配符一般都是跟 like 一起使用的。

 %：百分号表示零个、一一个或多个字符
 _：划线表示单个字符

示例：

 'A_Z'：所有以'A'起头，另一个任何值的字符，且以'Z'为结尾的字符串。
 例如，'ABZ'和'A2Z' 都符合这一一个模式，而'AKKZ'并不符合(因为在A和z之间有两个字符，而不是一个字符)。
 ​
 'ABC%'：所有以'ABC'起头的字符串。例如，'ABCD' 和'ABCABC' 都符合这个模式。
 ​
 '%XYZ'：所有以'XYZ' 结尾的字符串。例如，'WXYZ' 和'ZZXYZ' 都符合这个模式。
 ​
 '%AN%'：所有含有'AN'这 个模式的字符串。例如，'LOS ANGELES'和'SAN FRANCISCO' 都符合这个模式。
 ​
 '_AN%': 所有第二个字母为'A' 和第三个字母为'N' 的字符串。
 例如，'SAN FRANCISCO' 符合这个模式，而'Los ANGELES'则不符合这个模式

1.8 like

like，匹配一个模式来找出我们要的数据记录。

 SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式} ;

微信截图_20221106184821.png

微信截图_20221106184950.png

微信截图_20221106185114.png

1.9 order by

order by，按关键字排序。

注意：

  • 一般对数值字段进行排序。
  • 如果对字符类型的字段进行排序，则会按首字母排序。
 SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC|DESC] ;
 #ASC是按照升序进行排序的，是默认的排序方式。
 #DESC是按降序方式进行排序。

微信截图_20221106185652.png

微信截图_20221106185812.png

微信截图_20221106190043.png

二、函数

注意：函数名和括号之间不能有空格。

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位小数的值 #不四舍五入
ceil(x)返回大于或等于x的最小整数
floor(x)返回小于或等于x的最大整数
greatest(x1,x2,...)返回集合中最大的值
least(x1,x2,...)返回集合中最小的值

微信截图_20221107085015.png

微信截图_20221107085520.png

微信截图_20221107090300.png

2.2 聚合函数

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

注意：

  • count(列名)：只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行。

  • count(*) ：包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL。

微信截图_20221107091038.png

微信截图_20221107091215.png

微信截图_20221107091748.png

微信截图_20221107094408.png

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 反转

2.3.1 拼接的两种方法

方法一：concat(x,y)

微信截图_20221107095043.png

微信截图_20221107095334.png

微信截图_20221107095821.png

方法二：使用 || 符号（5.7之后的版本才支持）

如果sql_mode开启了PIPES_AS_CONCAT（可查看配置文件进行确认），"||"视为字符串的连接操作符而非”或“运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的。

微信截图_20221107191538.png

微信截图_20221107191703.png

微信截图_20221107191804.png

2.3.2 截取

substr(x,y)和 substr(x,y,z)

微信截图_20221107192244.png

2.3.3 去除格式 trim()

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

微信截图_20221107192649.png

2.3.4 返回字符长度 length

 select store_name,length(store_name) from location;   
 #统计store_name字段值的字符长度

微信截图_20221107192913.png

2.3.5 替换 replace

select replace(字段名,'原始字符','替换字符') from 表名; 
#将region字段值中的字符串"st"替换为字符串"stern"。

微信截图_20221107193319.png

三、进阶查询（二）

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

  • "GROUP BY"有一个原则，凡是在"GROUP BY"后面出现的字段，必须在SELECT 后面出现；
  • 凡是在SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在"GROUP BY"后面。
 SELECT 字段1,SUM(字段2) FROM 表名 GROUP BY 字段1;

微信截图_20221107193657.png

微信截图_20221107193805.png

微信截图_20221107194055.png

微信截图_20221107194219.png

3.2 HAVING（和 group by配合使用）

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

微信截图_20221107194438.png

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

字段别名

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

微信截图_20221107194710.png

表别名

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

微信截图_20221107195307.png

3.4 表的自我连接

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;
#count统计的是A表中销售额的每个值小于等于B表中每个值的次数。（A和B都是store_info的别名，即表的自我连接，自己跟自己对比。）

微信截图_20221107201836.png

表中有相同数值的销售额

select A.store_name,A.sales,count(A.sales) rank from store_info as A,store_info as B where A.sales<B.sales or (A.sales=B.sales and A.store_name=B.store_name) group by A.store_name,A.sales order by rank asc;

微信截图_20221107202412.png

3.5 子 查询

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

语法:

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

示例1：两张表连接在一起

微信截图_20221107202602.png

示例2

微信截图_20221107202949.png

示例3

微信截图_20221107203230.png

示例4

微信截图_20221107203440.png

微信截图_20221107203650.png

3.6 EXISTS

  • 用来测试内查询有没有产生任何结果，类似布尔值是否为真。
  • 如果内查询有结果的话，系统就会执行外查询中的SQL语句。若是没有结果的话，那整个SQL语句就不会产生任何结果。
 SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

微信截图_20221107204025.png

avatar
文章
阅读
粉丝