MySQL高级SQL语句

寒_嗷呜
255 阅读8分钟

先准备两个表

region表

use bbc;
create table region(region char(20),site char(20));
insert into region values('south','shenzhen');
insert into region values('suoth','hongkong');
insert into region values('north','beijing');
insert into region values('north','tianjin');

fare表

create table fare (site char (20),money int (10) ,Date char(10));
insert into fare values('shenzhen', '1500', '2022-10-25') ;
insert into fare values ('hongkong', '350', '2022-10-27') ;
insert into fare values('beijing', '500', '2022-10-28') ;
insert into fare values('tianjin', '700', '2022-10-28') ;
insert into fare values('hongkong', '1200', '2022-10-28') ;

1、select

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

语法: SELECT "字段" FROM "表名";
 
示例：
select regin from region;
select site,date from fare;
Snipaste_2022-10-28_10-04-19.png Snipaste_2022-10-28_10-04-58.png

1.2 distinct 去重

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

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

select distinct "字段" from "表名";
​
示例：
select distinct region from region; ##对地区进行去重
select distinct date from fare; ##对时间进行去重

Snipaste_2022-10-28_10-09-01.png

1.3 where 有条件查询

where，有条件查询

select '字段' from 表名 where 条件;
​
示例：
select * from fare where money > 1000; #查找销售额大于1000的数据记录
select site from fare where money > 1000;  #查找销售额大于1000的地区

Snipaste_2022-10-28_10-13-20.png

1.4 and和or

and：且

or：或

select 字段 from 表名 where 条件1 and 条件2;
select 字段 from 表名 where 条件1 or 条件2;

示例：

#查询销售额大于500且小于1000的数据记录。
select * from fare where money > 500 and money < 1000 ; 
​
#查询销售额大于200且小于500的记录，或者销售额大于1000的记录。
select * from fare where money > 1000 or (money < 500 and money > 200); 
​
#查询销售额大于300且小于800的地区名，或者销售额大于1000的地区名。
select site from fare where (money < 500 and money > 200) or money > 1000;

Snipaste_2022-10-28_10-17-06.png

1.5 in

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

select 字段 from 表名 where 字段 in ('值1','值2',....)  ##in 遍历一个取值列表
select 字段 from 表名 where 字段 not in ('值1','值2',....) ##not in 表示取反

示例：

#查询地区名为“beijng”，以及地区名为“hongkong”的记录。
select * from fare where site in ('beijing','hongkong');    
​
#查询地区不是“beijng”、不是“hongkong”的记录。
select * from fare where site not in ('beijng','hongkong');

Snipaste_2022-10-28_10-19-36.png

1.6 betweem

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

select 字段 from 表名 where 字段 between '值1'and'值2'

示例：

#查询销售额在500~1000之间的数据记录（包含500和1000）
select * from fare where money between 500 and 1000;

Snipaste_2022-10-28_10-44-48.png

#查询时间在2022-10-26到2022-10-30之间的记录（包含这两个日期）
 select * from fare where date between '2022-10-26' and '2022-10-30';

Snipaste_2022-10-28_10-46-27.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 '模式';

示例：

select * from fare where site like 'be%';

select * from fare where site like '%ng';

select * from fare where site like '_ji%'

Snipaste_2022-10-28_10-50-30.png

1.9 排序 order by

order by,按关键字排序

注意：

一般对数值字段进行排序

如果对字符类型的字段进行排序，则会按首字母排序

select 字段 from 表名 [where 条件] order by 字段 [asc|desc]
##ASC是按照升序进行排序的，是默认的排序方式
##DESC是按降序方式进行排序

示例：

select * from fare order by money desc;
select * from fare order by money asc;

Snipaste_2022-10-28_10-53-24.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,......)返回集合中最小的值

示例：

select abs(-1),rand(),mod(7,4),power(3,2);
##返回：1,0-1之间的随机数，3,9
​
select round(1,89),sqrt(2);
##返回：2,1.414
​
select round(1.8937,3),truncate(1.235,2);
##返回：1.894,1.23
​
select ceil(5),floor(2),least(1.89,3,6.1,2.1);
##返回：6,2,1.89

Snipaste_2022-10-28_10-55-49.png

Snipaste_2022-10-28_10-57-15.png

2.2 聚合函数

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

注意：

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

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

示例：

select avg(money) from fare;  ##求表中销售额字段的平均值
​
select sum(money) from fare;  ##求表中的销售额字段之和

Snipaste_2022-10-28_10-59-20.png 示例2：

select min(money) from fare; ##求表中销售额字段的最小值
​
select max(money) from fare; ##求表中销售额字段的最大值

Snipaste_2022-10-28_11-00-38.png

示例三：

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

Snipaste_2022-10-28_11-02-38.png

示例四：

比较count(列名)和count(*)

  • count(列名)：会忽略null值的行。
  • count(*) ：会统计所有行。
#先准备一个数据表city
create table city(name char(20));
insert into city values('beijing');
insert into city values('nanjing');
insert into city values('hangzhou');
insert into city values();
insert into city values();
​
#统计name字段有多少个非空值，会忽略字段值为null的行
select count(name) from city;
​
#统计所有行，不会忽略字段值为null的行。全表扫描。
select count(*) from city;
Snipaste_2022-10-28_11-05-35.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)返回字符串想的后y个字符
repeat(x,y)将字符串x重复y次
space(x)返回x个空格
strcmp(x,y)比较x和y,返回的值可以为-1,0,1
reverse(x)将字符串x反转

拼接的两种方法

方法一：concat(x,y)

示例1：

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

Snipaste_2022-10-28_11-07-31.png

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

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

##将region表中，region字段值和site字段值拼接在一起
select region || site from region;
​
##将region表中，region字段值和site字段值拼接在一起,且中间加空格
select region || ' ' || site from region;

Snipaste_2022-10-28_11-08-54.png

截取

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

#从地区的第3个字符开始截取直到最后一个字符。
select substr(site,3) from fare;
​
#从地区的第2个字符开始截取长度为4的字符串。
select substr(site,2,4) from fare;
​
#当地区名为"shenzhen"时，从商店名称的第3个字符开始截取直到最后一个字符。
select substr(site,3) from fare where site='shenzhen';
​
#当地区名为"beijing"时，从商店名称的第2个字符开始截取长度为4的字符串。
select substr(site,2,4) from fare where size='beijing';

Snipaste_2022-10-28_11-11-50.png

Snipaste_2022-10-28_11-14-35.png

去除格式 trim()

语法：

select trim ([位置] [要移除的字符串] from ] 字符串);
​
#[位置]：值可以为 leading (起头), trailing (结尾), both (起头及结尾)。 
#[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。

示例：

select trim(leading 'be' from 'beijing');    #移除"beijing"开头的"be"
​
select trim(trailing 'g' from 'beijing');    #移除"beijing"结尾的"g"

Snipaste_2022-10-28_11-17-34.png

返回字符长度 length

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

Snipaste_2022-10-28_11-20-19.png

替换 replace

select replace(region,'th','tion') from region;  #将region字段值中的字符串"th"替换为字符串"tion"

Snipaste_2022-10-28_11-21-54.png

avatar
文章
阅读
粉丝