最近由于工作原因，自己需要单独开发一些统计数据接口，处理大量订单信息，关联表，分组，多表联查，筛选相关信息，以便前端生成图表相关信息，目前主要采用echarts、F2等组件库做pc和移动端，就此机会，抽空梳理了下MySQL相关内容，以供学习参考

相关资料：https://book.douban.com/subject/3354490/

• 一、了解 SQl
  • 数据库基础
    • 表 table
    • 模式 schema
    • 列和数据类型
    • 行
    • 主键
  • SQL
  • 手动模拟一个数据库样例
• 二、 MySQL 简介
• 三、 使用数据库
  • 连接数据流
  • 查看数据库和表
  • 选择数据库
  • 查看表
  • 查看表列
  • 其他语句
• 四、检索数据
  • 检索单个列
  • 检索多个列
  • 检索所有列
  • 检索不同的行
  • 限制结果
  • 使用完全限定的表名
• 五、排序数据
  • 子句（clause）
  • 多个列排序
  • 指定排序方向
• 六、 过滤数据
  • SQL 过滤与应用过滤
  • where 子句操作符
  • 空值检查
• 七、 数据过滤
  • 组合 where 句子
    • AND
    • OR
    • 优先级
  • IN 操作符
  • NOT 操作符
• 八、 通配符 过滤
  • % 通配符
  • 下划线
  • 使用通配符的技巧
• 九、 正则表达式搜索
• 十、创建计算字段
  • 计算字段
  • 拼接字段
  • 使用别名
  • 执行算术计算
• 十一、数据处理函数
  • 函数
  • 文本处理函数
  • 日期和时间处理函数
    • 日期比较

一、了解 SQl

数据库基础

数据库是一个以某种有组织的方式存储的数据集合。理解数据库的一种最简单的办法是将其想象为一个文件柜。此文件柜是一个存放数据的物理位置，不管数据是什么以及如何组织的,
数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）。

表 table

某种特定类型数据的结构化清单。

数据库中的每个表都有一个名字，用来标识自己。此名字是唯一的

表具有一些特性，这些特性定义了数据在表中如何存储，如可以存
储什么样的数据，数据如何分解，各部分信息如何命名，等等

模式 schema

关于数据库和表的布局及特性的信息。

描述表的这组信息就是所谓的模式，模式可以用来描述数据库中特定的表以及整个数据库（和其中表的关系）。

列和数据类型

表由列组成，列中存储着表中某部分的信息,列（column）是表中的一个字段。所有表都是由一个或多个列组成的。

数据类型（datatype） 所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据

数据类型限制可存储在列中的数据种类，数据类型还帮助正确地排序数据，并在优化磁盘使用方面起重要的作用。因此，在创建表时必须对数据类型给予特别的关注。

行

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。表中的行数为记录的总数,一行就是一条记录(record)

主键

表中每一行都应该有可以唯一标识(primary key)自己的一列（或一组列）

表中的任何列都可以作为主键，只要它满足以下条件:

1.任意两行都不具有相同的主键值 2.每个行都必须具有一个主键值（主键列不允许 NULL 值）

SQL

SQL 是结构化查询语言（Structured QueryLanguage）的缩写,SQL 是一种专门用来与数据库通信的语言

优点：

1. 学习此语言使你几乎能与所有数据库打交道
2. SQL 简单易学。它的语句全都是由描述性很强的英语单词组成
3. 灵活
   使用其语言元素，可以进行非常复杂和高级的数据库操作。

手动模拟一个数据库样例

 管理供应商 ： vendors
 管理产品目录 : products
 管理顾客列表 : customers
 录入顾客订单 ： orders

要完成这几个任务需要作为关系数据库设计成分的紧密联系的 6 个表。以下几节描述各个表。

二、 MySQL 简介

1. 简单命令行实用程序

 mysql -u username
    //为了给出用户名、主机名、端口和口令，
    // 完整的命令行选项和参数列表可用 mysql --help 获得

 mysql -u username -p -h myserver -P 9999

2. MySQL Administrator,是一个图形交互客户机，用来简化 MySQL 服务器的管理

3. MySQL Query Browser为一个图形交互客户机，用来编写和执行 MySQL 命令。

三、 使用数据库

连接数据流

mysql -u root -p -h 127.0.0.1 -P 3306;

查看数据库和表

show databases;

选择数据库

use databasesName;

查看表

show tables;

查看表列

show columns from customers;

其他语句

SHOW StATUS 用于显示广泛的服务器状态信息
SHOW CREATE DATABASE和SHOW CREATE TABLE ，分别用来显示创建特定数据库或表的MySQL语句；
SHOW GRANTS ，用来显示授予用户（所有用户或特定用户）的安全权限；
SHOW ERRORS 和 SHOW WARNINGS ，用来显示服务器错误或警告消息。

四、检索数据

1. SQL 语句不区分大小写，但有些标识符（如数据库名、表名、列名）可能不同
2. 在处理 SQL 语句时，其中所有空格都被忽略
3. 多条 SQL 语句必须以分号（；）分隔
4. 在选择多个列时，一定要在列名之间加上逗号

检索单个列

select prod_name from products

检索多个列

select prod_name,vend_id from products

检索所有列

select * from products

检索不同的行

select distinct vend_id from customers

不能部分使用 DISTINCT,如果给出 SELECT DISTINCT vend_id,prod_price ，除非指定的两个列都不同，否则所有行都将被检索出来。

限制结果

此语句使用 SELECT 语句检索单个列。 LIMIT 5 指示 MySQL 返回不多于 5 行

select prod_name from products limit 5
select prod_name from products limit 2,5

使用完全限定的表名

select products.prod_name from products

//限定的表名
select products.prod_name form databaseName.products

五、排序数据

如果数据后来进行过更新或删除，则此顺序将会受到 MySQL 重用回收存储空间的影响

子句（clause）

SQL 语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有 SELECT 语句的 FROM 子句

为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。ORDER BY 子句取一个或多个列的名字，据此对输出进行排序

//根据产品价格排序
select prod_name,vend_id,prod_price from products ORDER BY prod_price;

通常， ORDER BY 子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的

多个列排序

select prod_name,vend_id,prod_price from products ORDER BY prod_price,prod_id;

指定排序方向

这只是默认的排序顺序 A-Z,还可以使用 ORDER BY 子句以降序（从 Z 到 A ）顺序排序。为了进行降序排序，必须指定 DESC 关键字

//按价格以降序排序产品（最贵的排在最前面）：
select prod_name,prod_price from products ORDER BY prod_price DESC;

//以降序排序产品（最贵的在最前面），然后再对产品名排序
select prod_name,prod_price from products ORDER BY prod_price DESC,prod_name;

与 DESC 相反的关键字是 ASC （ ASCENDING ），在升序排序时可以指定它。但实际上， ASC 没有多大用处，因为升序是默认的（如果既不指定 ASC 也不指定 DESC ，则假定为 ASC ）。

六、 过滤数据

搜索条件也称为过滤条件（filter condition）。

SQL 过滤与应用过滤

数据也可以在应用层过滤。为此目的，SQL 的 SELECT 语句为客户机应用检索出超过实际所需的数据，然后客户机代码对返回数据进行循环，以提取出需要的行。
这种实现并不令人满意。因此，对数据库进行了优化，以便快速有效地对数据进行过滤

在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后

where 子句操作符

=
<>  不等于
!=
<
<=
>
=>
between 两个值之间

空值检查

在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含空值 NULL

NULL 无值（no value），它与字段包含 0 、空字符串或仅仅包含空格不同,SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。

select prod_name from products where prod_price IS NULL
select cust_id from customers where cust_email is null;

七、 数据过滤

组合 where 句子

操作符（operator） 用来联结或改变 WHERE 子句中的子句的关键字。也称为逻辑操作符（logical operator）。

AND

用来指示检索满足所有给定条件的行。

//供应商 1003 制造且价格小于等于10美元的所有产品的名称和价格
select * from products where vend_id=1003 and prod_price<=10;

OR

它指示 MySQL 检索匹配任一条件的行。

//检索由任一个指定供应商制造的所有产品的产品名和价格
select * from products where vend_id=1002 or vend_id=1003

优先级

WHERE 可包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂和高级的过滤
在处理 OR 操作符前，优先处理 AND 操作符

//由于 AND 在计算次序中优先级更高，操作符被错误地组合了
select * from products where vend_id=1002 or vend_id=1003 and prod_price>=10;
//由供应商 1003 制造的任何价格为10美元（含）以上的产品，或者由供应商 1002 制造的任何产品
+---------+---------+----------------+------------+-------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                                                                |
+---------+---------+----------------+------------+-------------------------------------------------+
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runner
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                                                                      |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                                                             |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                                                               |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                                                              |
+---------+---------+----------------+------------+------------------------------------                                                         -------------+


//此问题的解决方法是使用圆括号明确地分组相应的操作符,因为圆括号具有较 AND 或 OR 操作符高的计算次序


select * from products where (vend_id=1002 or vend_id=1003) and prod_price>=10;
//由供应商 1002 或 1003 制造的且价格都在10美元（含）以上的任何产品
+---------+---------+----------------+------------+-------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                       |
+---------+---------+----------------+------------+-------------------------------------------------+
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)           |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                      |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                     |
+---------+---------+----------------+------------+-------------------------------------------------+

IN 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配, IN 操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号

//此 SELECT 语句检索供应商 1002 和 1003 制造的所有产品
select * from products where vend_id in (1002,1003) ORDER BY prod_price

IN 与 OR 能实现相同的功能,但是为什么还要使用 IN 呢?

1. 在使用长的合法选项清单时， IN操作符的语法更清楚且更直观
2. 在使用 IN时，计算的次序更容易管理（因为使用的操作符更少）。
3. IN 操作符一般比 OR 操作符清单执行更快
4. IN 的最大优点是可以包含其他 SELECT 语句，使得能够更动态地建
立 WHERE

NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定它之后所跟的任何条件

//MySQL不是匹配 1002和 1003 的 vend_id ，而是匹配 1002 和 1003 之外供应商的vend_id

select * from products where vend_id  not in (1002,1003) ORDER BY prod_price

对于简单的 WHERE 子句，使用 NOT 确实没有什么优势。但在更复杂的子句中， NOT 是非常有用的。
例如，在与 IN 操作符联合使用时， NOT 使找出与条件列表不匹配的行非常简单

八、 通配符 过滤

利用通配符可创建比较特定数据的搜索模式

% 通配符

select * from products where prod_name like '米%'

下划线

下划线只匹配单个字符而不是多个字符

//总是匹配一个字符，不能多也不能少
select * from products where prod_name like '_num'  // 1 nums  而 .5 nums就不行

使用通配符的技巧

1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
3. 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数

九、 正则表达式搜索

更新中

十、创建计算字段

计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的

字段（field） 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

拼接字段

拼接（concatenate） 将值联结到一起构成单个值。

如要生成一个供应商报表，需要在供应商的名字中按照 name(location) 这样的格式列出供应商的位置。

Concat() 拼接串，即把多个串连接起来形成一个较长的串,需要一个或多个指定的串，各个串之间用逗号分隔。

1. 存储在 vend_name 列中的名字；
2. 包含一个空格和一个左圆括号的串；
3. 存储在 vend_country 列中的国家；
4. 包含一个右圆括号的串

select concat(vend_name,"(",vend_country,")")  from vendors ORDER BY vend_name

+----------------------------------------+
| concat(vend_name,"(",vend_country,")") |
+----------------------------------------+
| ACME(USA)                              |
| Anvils R Us(USA)                       |
| Furball Inc.(USA)                      |
| LT Supplies(USA)                       |
+----------------------------------------+
4 rows in set (0.00 sec)

使用别名

select concat(vend_name,"(",vend_country,")") as vend_title from vendors ORDER BY vend_name;

+----------------------------------------+
|             vend_title                 |
+----------------------------------------+
| ACME(USA)                              |
| Anvils R Us(USA)                       |
| Furball Inc.(USA)                      |
| LT Supplies(USA)                       |
+----------------------------------------+
4 rows in set (0.00 sec)

执行算术计算

select prod_id,quantity,item_price,quantity*item_price as total from orderitems;
/*
    +,-,*,/
*/

未完持续...