Mysql高级（进阶）sql语句

MySQL高级语句简介

在进行高级SQL语句之前，先创建两张表

use gxs;
create table localtion (Region char(20),Store_Name char(20));
insert into localtion values('East','Boston');
insert into localtion values('East','New York');
insert into localtion values('West','Los Angeles');
insert into localtion 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 "栏位" BETWEEN '值1' AND '值2';
SELECT * FROM Store_Info WHERE Sales BETWEEN '250' AND '700';

例：

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' 则不
符合这个模式。

8、LIKE：匹配一个模式来找出我们要的资料

语法：SELECT "栏位" FROM "表名" WHERE "栏位" LIKE {模式};
SELECT * FROM Store_Info WHERE Store_Name like '%os%';

例：

9、ORDER BY：按关键字排序

语法：SELECT "栏位" FROM "表名" [WHERE "条件"] ORDER BY "栏位" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序。
SELECT Store_Name,Sales,Date FROM Store_Info ORDER BY Sales DESC;

例：

10、函数
数学函数：

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

例：

11、聚合函数

SELECT avg(Sales) FROM Store_Info;

SELECT count(Store_Name) FROM Store_Info;
SELECT count(DISTINCT Store_Name) FROM Store_Info;

SELECT max(Sales) FROM Store_Info;
SELECT min(Sales) FROM Store_Info;

SELECT sum(Sales) FROM Store_Info;

例：

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

+------------+
| name       |
+------------|
|   beijing  |
|   nanjing  |
|  shanghai  |
|   <null>   |
|   <null>   | 
+------------+

SELECT count(name) FROM Ctiy;
SELECT count(*) FROM Ctiy;

例：

12、字符串函数

SELECT concat(Region, Store_Name) FROM localtion WHERE Store_Name = 'Boston';
#如sql_mode开启开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和
Oracle数据库使用方法一样的
SELECT Region || ' ' || Store_Name FROM localtion WHERE Store_Name = 'Boston';

SELECT substr(Store_Name,3) FROM localtion WHERE Store_Name = 'Los Angeles';
SELECT substr(Store_Name,2,4) FROM localtion WHERE Store_Name = 'New York';

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

SELECT TRIM(LEADING 'Ne' FROM 'New York');

SELECT Region,length(Store_Name) FROM localtion;

SELECT REPLACE(Region,'ast','astern')FROM localtion;

例：

13、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;

例：

14、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;

例：

15、别名：栏位別名 表格別名

语法：SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";
SELECT A.Store_Name Store, SUM(A.Sales) "Total Sales" FROM Store_Info A GROUP BY A.Store_Name;

例：

16、子查询：连接表格，子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 localtion WHERE Region = 'West');

SELECT SUM(Sales) FROM Store_Info WHERE Store_Name IN ('Los Angeles','Houston');

SELECT SUM(A.Sales) FROM Store_Info A WHERE A.Store_Name IN
(SELECT Store_Name FROM localtion B WHERE B.Store_Name = A.Store_Name);

例：

17、EXISTS：用来测试内查询有没有产生任何结果，类似布尔值是否为真
如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。

语法：SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
SELECT SUM(Sales) FROM Store_Info WHERE EXISTS (SELECT * FROM localtion WHERE Region = 'West');

例：