MySQL 高级语句 一

一、MySQL高级进阶SQL语句

我们下面将在下面两个表中讲解一些高级的SQL语句

use benet;
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');

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

1. select

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

语法SELECT "字段" FROM "表名";

SELECT Store_Name FROM Store_Info;

2. distinct

不显示重复的数据记录

语法select distinct "字段" from "表名";

select distinct store_name from store_info;

3. where

有条件查询

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

select store_name from store_info where sales >1000;

4. and or

且 或

语法SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;

SELECT Store_Name FROM Store_Info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200);

5. in

显示已知的值的数据记录

语法SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);

select * from store_info where store_name in ('los angeles','houston');

6. between

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

语法select "字段" from "表名" where "字段" beteen '值1' and '值2';

select * from store_info where sales between '300' and '1500';

7. 通配符

通常通配符都是跟 LIKE 一起使用的

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

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

语法select "字段" from "表名"where "字段"like{模式};

select * from store_info where store_name like '%os%';

8. order by

按关键词进行排序

语法select "字段" from "表名" [where "条件"] order by "字段" [ascdesc];
#ASC 是按照升序进行排序的是默认的排序方式。
#DESC 是按降序方式进行排序。

 select * from store_info order by sales desc;

9. 函数

9.1 数学函数

函数	含义
absx	返回x的绝对值
rand	返回0到1的随机数
modxy	返回 x 除以 y 以后的余数
powerxy	返回 x 的 y 次方
roundx	返回离 x 最近的整数
roundxy	保留 x 的 y 位小数四舍五入后的值
sqrtx	返回 x 的平方根
truncatexy	返回数字 x 截断为 y 位小数的值
ceilx	返回大于或等于x的最小整数
floorx	返回小于或等于 x 的最大整数
greatest x1x2…	返回集合中最大的值也可以返回多个字段的最大的值
leastx1x2…	返回集合中最小的值也可返回多个字段的最小的值

SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);

9.2 聚合函数

函数	含义
avg	返回指定列的平均值
count	返回指定列中非null值的个数
min	返回指定列的最小值
max	返回指定列的最大值
sumx	返回指定列的所有值的和

#求表中的平均值
select avg(sales) from store_info;

返回指定列中非 NULL 值的个数
select count(store_name) from store_info;
select count(distinct store_name) from store_info;

返回指定列的最小值
select min(sales) from store_info;

返回指定列的最大值
select max(sales) from store_info;

返回指定列的所有值之和
select sum(sales) from store_info;

#count(*) 包括了所有的列的行数在统计结果的时候不会忽略列值为 NULL
#count(列名) 只包括列名那一列的行数在统计结果的时候会忽略列值为 NULL 的行

9.3 字符串函数

函数	含义
trim	返回去除指定格式的值
concatxy	将提供的参数 x 和 y 拼接成一个字符串
substrxy	获取从字符串 x 中的第 y 个位置开始的字符串跟substring()函数作用相同
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
lengthx	返回字符串 x 的长度
replacexyz	将字符串 z 替代字符串 x 中的字符串 y
upperx	将字符串 x 的所有字母变成大写字母
lowerx	将字符串 x 的所有字母变成小写字母
leftxy	返回字符串 x 的后 y 个字符
repeat xy	将字符串 x 重复 y 次
space x	返回 x 个空格
strcmp xy	比较 x 和y 返回的值可以为 -101
reversex	将字符串 x 反转

1trim

语法
select trim (位置 要移除的字符串 from 字符串)
 
其中位置的值可以是 
leading开始 
trailing(结尾)
both(起头及结尾)
 
#区分大小写
要移除的字符串:从字符串的起头、结尾或起头及结尾移除的字符串缺省时为空格。

移除以字串开头, 移除 Los Angeles
select trim(leading 'Los' from 'Los Angeles');

去除以字符开头以字符结尾
select trim(both 'l' from 'los Angel');

2截取 substr

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

select substr(store_name,2) from store_info;

 select substr(store_name,2,5) from store_info;

3字段拼接 concatxy

select concat(store_name,sales) from store_info;

3.1 使用 || 符号

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

4 返回字符长度 length

select length(store_name) from store_info;

5 替换 replace

select replace(store_name,'os','kk') from store_info;

二、高级查询语句

2.1 group by 用于分组和汇总

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

• "group by"有一个原则凡是在"group by"后面出现的字段必须在select 后面出现

• 凡是在select 后面出现的、且未在聚合函数中出现的字段必须出现在"group by"后面。

1 汇总统计

#语法
select 字段1聚合函数(字段2) from 表名 group by 字段1

select store_name, count(store_name) from store_info group by store_name;

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

select store_name, sum(sales) from store_info group by(store_name);

2.2 having

• having用来过滤由group by语句返回的记录集通常与group by语句联合使用

• having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏那就不需要GROUP BY子句。

• where只能对原表中的字段进行筛选不能对group by后的结果进行筛选

#语法:
SELECT 字段1SUM("字段")FROM 表格名 GROUP BY 字段1 having函数条件);

2.3 别名设置查询

字段別名 表格別名

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

1 字段别名

select store_name,sum(sales) as tongji from store_info group by store_name having sum(sales);

select store_name,sum(sales) as tongji from store_info group by store_name having tongji >250;

2 表别名

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

2.4 子查询语句

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

 SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符]     #外查询
 (SELECT "字段1" FROM "表格2" WHERE "条件") ;             #内查询
 #可以是符号的运算符例如 =、>、<、>=、<= 也可以是文字的运算符例如 LIKE、IN、BETWEEN

select sum(sales) from store_info where store_name in (select store_name from location where region='west');

2.5 exits

用来测试内查询有没有产生任何结果类似布尔值是否为真

#如果有的话系统就会执行外查询中的SQL语句。若是没有的话那整个 SQL 语句就不会产生任何结果。
语法SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
SELECT SUM(Sales) FROM Store_Info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West');

select sum(sales) from store_info where exists (select * from location where region='east');

select sum(sales) from store_info where exists (select * from location where region='beijing');