MySQL高级（进阶）SQL语句

一、准备工作

1、安装MySQL数据库

2、实验准备，配置数据表

mysql -uroot -p
show databases;

create database gj
use gj
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');
select * from location;

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

二、MySQL 高级(进阶) SQL 语句

1、SELECT

• 显示表格中一个或数个字段的所有资料
• 语法：SELECT 字段 FROM 表名

select Sales 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

• 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 ‘值一’ and ‘值二’;

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

7、通配符

• 通常通配符都是跟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'则不符合这个模式。

8、LIKE 

• 匹配一个模式来找出需要的数据
• 模式使用的单引号
• 语法∶ SELECT "栏位" FROM "表名" WHERE "栏位" LIKE '模式';

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

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、GROUP BY

• 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  s  desc;

11、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(Salesles) > 1500;

三、函数

1、数学函数

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

2、聚合函数

• count(*)包括所有列的行数，在统计结果时，不好忽略值为null
• count(字段)只包括那一行的列数，在统计结果的时候，会忽略列值为null的值

SELECT avg (Sales)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;

3、字符串函数

select concat (Region, Store_name ) from location where Store_Name = 'Boston';

| | 连接符

• 如果sql_mode开启开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的

SELECT Region || ' '  || Store_Name FROM location WHERE Store_Name ='Boston';

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