MYSQL高级语句 (一)

什么都学
235 阅读2分钟

一、高级语句之进阶查询

先准备两个表

location表

use test;

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','Los Angeles');
insert into location values('West','Houston');

image.png 一个store_info表

create table store_info (Store_Name char (20),Sales int (10) ,Date char(10));
insert into store_info values('Los Angeles', '1500', '2020-12-05');
insert into store_info values ('Houston', '250', '2020-12-07');
insert into store_info values('Los Angeles', '300', '2020-12-08');
insert into store_info values('Boston', '700', '2020-12-08');

image.png

1.1、select

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

select "字段"/* from "表名";

image.png

1.2 distinct

不显示重复的数据记录
ps：关系型数据库，单词只能对一个字段去重
select distinct "字段" from "表名";

image.png

image.png

1.3 where

有条件查询
select "字段" from "表名" where "条件";

image.png

1.4 and 和 for

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 > 200 and sales < 500);

image.png image.png

1.5 in

显示已知的数据记录
select "字段" from "表名" where "字段" in （值1,值2...）;  #in,遍历一个取值列表

select "字段" from "表名" where "字段" not in (值1，值2...);  #not in取反，查询不在这个范围的值

 #查询商店名为“Los Angeles”，以及商店名为“Houston”的记录。
 select * from store_info where store_name in ('Los Angeles','Houston');    

 #查询商店名称不是“Los Angeles”、不是“Houston”的记录。
 select * from store_info WHERE store_name not in ('Los Angeles','Houston');

image.png

image.png

1.6 between

between，显示两个值范围内的数据记录。
select "字段" from "表名" where "字段" between "值1" and "值2";

 #查询销售额在500~1500之间的数据记录（包含500和1500）
 select * from store_info where sales between 500 and 1500;
 ​
 #查询店铺开业日期在2020-12-06到2020-12-10之间的记录（包含这两个日期）
 select * from store_info where date between '2020-12-06' and '2020-12-10';

image.png

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

匹配一个模式来找出我们要的数据记录
select * from "表名" where like 模式";

 #查询商店名称包含字符串"os"的记录
 select * from store_info where store_name like '%os%';
 ​
 #查询商店名称以字符串"on"结尾的记录
 select * from store_info where store_name like '%on';
 ​
 #查询商店名称的第二、第三个字符为"os"的记录
 select * from store_info where store_name like '_os%';

image.png

image.png

image.png

1.9 order by

按关键字查询
一般是对数值字段进行排序
如果对字符类型的字段进行排序，则会按照字母顺序
select "字段" from "表名" [where "条件"] order by "字段" [asc|desc] ;
ps：
asc是升序，desc是降序

#查看store_info表中的所有记录，按销售额进行升序排列。  select * from store_info order by sales ASC；   #ASC可以不加，默认升序  
#查看store_info表中的所有记录，按销售额进行倒序排列。  select * from store_info order by sales desc;

image.png

image.png

二、函数

2.1数字函数

ps：函数名和括号之间不能有空格

image.png

 SELECT abs(-1),rand(), mod(5,3) ,power(2,3);
 ​
 SELECT round(1.234),sqrt(2);
 ​
 SELECT round(1.5555,3),truncate (1.234,2);
 ​
 SELECT ceil(5.5) ,floor(5.5) ,least(1.89,3,6.1,2.1);

image.png

image.png

image.png image.png

2.2聚合函数

image.png ps：

  • count(列名)：只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行。
  • count(*) ：包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL。
 select avg(sales) from store_info;   #求表中销售额字段的平均值
 ​
 select sum(sales) from store_info;   #求表中销售额字段所有值的和

image.png

image.png

 select max(sales) from store_info;    #显示表中销售额字段的最大值
 ​
 select min(sales) from store_info;    #显示表中销售额字段的最小值

image.png

image.png

 #统计表中store_name字段有多少个非空值
 select count(store_name) from store_info;
 ​
 #统计表中store_name字段有多少个不重复的非空值
 select count(distinct store_name) from store_info;

image.png

image.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)
select concat('zhang','san');  #将字符串"zhang"和"san"拼接在一起

select concat('zhang',' ','san');   #将字符串"zhang"、空格、"san"拼接在一起

image.png

image.png

 #将location表中，region字段值和store_name字段值拼接在一起。
 select concat(region,store_name) from location;
 ​
 #将location表中，region字段值和store_name字段值拼接在一起，且中间加空格。
 select concat(region,' ',store_name) from location;

image.png

image.png

[方法二]
 #将location表中，region字段值和store_name字段值拼接在一起。
 select Region || store_name from location;
 ​
 #将location表中，region字段值和store_name字段值拼接在一起，且中间加空格。
 select Region || ' ' || store_name from location;

image.png

2.3.2截取

substr(x,y)和 substr(x,y,z)
 #从商店名称的第3个字符开始截取直到最后一个字符。
 select substr(Store_Name,3) from location;
 ​
 #从商店名称的第2个字符开始截取长度为4的字符串。
 select substr(Store_Name,2,4) from location;

image.png

image.png

2.3.4 去除格式trim ()

select trim ([位置] [要移除的字符] from '字符串');

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

 select trim(leading 'Ne' from 'New York');    #移除"New York"开头的"Ne"
 ​
 select trim(trailing 'k' from 'New York');    #移除"New York"结尾的"k"

image.png

image.png

2.3.4返回字符长度lengh

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

image.png

2.3.5替换replace

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

image.png

进阶查询

3.1 group by

group by 对group by 后面的字段的查询结果进行汇总分组，通常是结合函数一起使用的（还有去重的效果）
凡是在group by 后面出现的字段，必须在select 后面出现；
凡是在select 后面出现的、且未在聚合函数中出现的字段，必须出现在group by 后面
语法:select "字段" SUM("字段2") from "表名" group by "字段1";

select store_name from store_info group by store_name; #对store_name字段值进行分组汇总。

image.png

3.2having

having 用来过滤由group by语句返回的记录集，通常与group by 语句联合使用
having 语句的存在弥补了where 关键字不能与聚合函数联合使用的不足
语法：select "字段"，sum(字段2) from "表格名" group by “字段2” having(函数条件);
select Store_Name，SUM(Sales) from Store_Info group by Store_Name having SUM(Sales) > 1500

image.png

3.3字段别名，表别名

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

select store_name,sum(sales) as total_sales from store_info group by store_name having sum(sales) > 1000;  #设置sum(sales)字段的别名为total_sales。  
#对store_name字段进行分组汇总，并计算组内销售额之和，之后查询出组销售额大于1000的记录。

image.png

 SELECT 表格别名.字段1 [AS] 字段别名  FROM 表格名 [AS] 表格别名; #AS可以省略不写
 ​
 示例：
 select A.store_name store, sum(A.sales) total_sales from store_info A group by A.store_Name;
 #设置store_info表的别名为A，A.store_name字段的别名为store，sum(A.sales)字段的别名为total_sales。
 #对A表的store_Name字段进行分组汇总，并计算组销售额。即分组后求组内之和。

image.png

3.4表的自我连接

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

image.png

3.5子查询

子查询：连接表格，在WHERE 子句或HAVING 子句中插入另一个SQL语句。
SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符]     #外查询
 (SELECT "字段1" FROM "表格2" WHERE "条件") ;             #内查询
 ​
#可以是符号的运算符，例如=、>、<、>=、<= ;也可以是文字的运算符，例如LIKE、 IN、BETWEEN
 ​
#先执行内查询，再执行外查询。内查询的查询结果，作为外查询的条件

两张表连接在一起
 select * from store_info A, location B where A.store_name=B.store_name;

image.png

avatar
文章
阅读
粉丝