MySQL高级SQL语句（1）

#####创建两个表以表内容进行试验#####

use AAA; #进入数据库

#创建一个location表添加数据东部（波士顿纽约）西部（洛杉矶休斯顿）

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

#创建一个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');

一。select语句处理显示的数据内容

---- SELECT ----显示表格中一个或数个栏位的所有资料

语法∶ SELECT "栏位" FROM "表名";

SELECT Store_Name FROM store_info;

--- DISTINCT ----不显示重复的资料

语法∶ SELECT DISTINCT "栏位" FROM "表名";

SELECT DISTINCT Store_Name FROM store_info;

---- ORDER BY ----按关键字排序

#数字按大小，字符按长度

语法：SELECT "栏位" FROM "表名" [WHERE "条件"] ORDER BY "栏位" [ASC,DESC];

#ASC是按照升序进行排序的，是默认的排序方式。

#DESC 是按降序方式进行排序。

SELECT * FROM store_info ORDER BY Sales; #显示表数据将sales字段排序

SELECT * FROM store_info ORDER BY store_name desc; #显示表数据将 store_name字段降序排序

SELECT * FROM store_info where store_name='Los Angeles' ORDER BY Sales; #显示符合where匹配的数据并将sales字段排序

二。select语句匹配符合条件的数据

---- WHERE ----有条件查询

语法∶SELECT "栏位" FROM"表名" WHERE "条件";

SELECT Store_Name FROM store_info WHERE Sales > 1000; #将销售额栏位大于1000的店名栏位打印

---- AND OR ----且、或

语法∶ SELECT "栏位" FROM "表名" WHERE "条件1" AND/OR "条件2";

SELECT Store_Name FROM store_info WHERE Sales>1000 OR(Sales<500 AND Sales> 200); #将store_info表里的Store_Name栏位数据的sales销售额栏位符合大于1000的或者大于500并且小于200的显示出来

---- IN ----显示己知的值的资料

#如果设置的值不存在则跳过该值

语法∶ SELECT "栏位" FROM "表名" WHERE "栏位" IN ('值1','值2'...);

SELECT * FROM store_info WHERE Sales IN (250,300); #显示sales为250，300所在行的数据

---- BETWEEN ----显示两个值范围内的资料

#包含between的两个值

语法∶ SELECT "栏位" FROM "表名" WHERE "栏位" BETWEEN '值1' AND '值2';

SELECT * FROM store_info WHERE Date BETWEEN '2020-12-07' AND '2020-12-10';

#显示出符合where匹配data字段的值在两个日期之间的包括两个日期

---- 通配符 ----

#通常都是跟LIKE一起使用的

%∶百分号表示零个、一个或多个字符

_∶下划线表示单个字符，必须有一个字符，不能为空

例如：

'A_Z'：所有以'A'起头，另一个任何值的字符，且以 'z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这个模式，而 'AKKZ'并不符合（因为在A和Z之间有两个字符，而不是一个字符）。

'ABC%'∶所有以'ABC'起头的字符串。例如 'ABCD'和 'ABCABC'都符合这个模式。'%XYZ '∶所有以 'XYZ' 结尾的字符串。例如 'WXYZ'和 'ZZXYZ' 都符合这个模式。

'%AN%'∶所有含有'AN'这个模式的字符串。例如 'LOS ANGELES' 和 'SAN ERANCISCO' 都符合这个模式。

'_AN%'∶所有第二个字母为'A'和第三个字母为 'N'的字符串。例如 'SAN FRANCISCO'符合这个模式，而 'LOS ANGELES'则不符合这个模式。

---- LIKE ----匹配一个模式来找出需要的数据

#模式使用的单引号

语法∶ SELECT "栏位" FROM "表名" WHERE "栏位" LIKE '模式';

SELECT * FROM Store_info WHERE Store_Name like '%os%'; #模糊匹配，显示出Store_Name该字段符合like通配符内容的数据

三。select语句对数据分组后处理显示

---- GROUP BY ----

#对GROUP BY后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的

GROUP BY有一个原则，就是 SELECT 后面的所有列中，没有使用聚合函数的列，必须出现在GROUP BY后面。

语法∶ SELECT "栏位1",SUM("栏位2")...FROM "表名" GROUP BY "栏位1"...;

SELECT Store_Name,SUM(Sales) FROM store_info GROUP BY Store_Name ORDER BY sales desc;

#先根据Store_Name内容进行汇总分组，显示分组后的Store_Name字段和sales字段和值并且将sales的值进行降序排序

---- HAVING ----

#用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用

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

语法∶ SELECT "栏位1",SUM("栏位2") FROM "表格名" GROUP BY "栏位1" HAVING (函数条件);

SELECT Store_Name,SUM(Sales) FROM store_info GROUP BY Store_Name HAVING SUM(Sales)<1500 order by sales;

#先根据Store_Name内容进行汇总分组，然后判断符合sales字段和值小于1500的使用升序显示

四。select语句中的函数应用

---- 数字函数 ----

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(5, 3),power(2,3),round(1.89),round (1.8937,3),truncate(1.238,2);

SELECT sqrt(3),ceil(5.2),floor(2.1),greatest(1.8,3,6,2.1),least(1.8,3,6,2.5);

---- 聚合函数 ----

avg()	返回指定列的平均值
count ()	返回指定列中非NULL值的个数，统计行数，0字符的行数也会统计
min()	返回指定列的最小值
max()	返回指定列的最大值
Sum(x)	返回指定列的所有值之和

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

#count(列名)只包括列名那一列的行数，在统计结果的时候，会忽略列值为NULL的行，但不会忽略0字符的行

SELECT count(id)FROM test1;

SELECT count(DISTINCT id)FROM test1; #统计Store-Name列非NULL值并且去重

SELECT avg(Sales) FROM store_info; #显示sales字段值的平均值

SELECT max(Sales) FROM store_info; #显示sales字段值的最大值

SELECT min(Sales)FROM store_info; #显示sales字段值的最小值

SELECT sum(Sales)FROM store_info; #显示sales字段值的总和

---- 字符串函数 ----

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（x大，一样大，y大）
reverse (x)	将字符串 x 反转