# MySql 必知必會

## data


## WAL
https://vicxu.medium.com/%E6%B7%BA%E5%85%A5%E6%B7%BA%E5%87%BA-mysql-ep3-transaction-%E8%A7%A3%E6%9E%90-%E6%96%B0%E5%A2%9E%E4%BF%AE%E6%94%B9%E5%88%AA%E9%99%A4%E6%98%AF%E5%A6%82%E4%BD%95%E8%A2%AB%E8%99%95%E7%90%86%E7%9A%84-380cae2eebca

## case
## 索引
b TREE


位圖索引

文黨索引

## 約束跟索引

## 主键

表中每一行都应该有可以唯一标识自己的一列（或一组列）。一个顾
客表可以使用顾客编号列，而订单表可以使用订单ID，雇员表可以使用
雇员ID或雇员社会保险号。
主键（primary key）①一一列（或一组列），其值能够唯一区分表
中每个行。

唯一标识表中每行的这个列（或这组列）称为主键。主键用来表示
一个特定的行。没有主键，更新或删除表中特定行很困难，因为没有安
全的方法保证只涉及相关的行。
应该总是定义主键 虽然并不总是都需要主键，但大多数数据

库设计人员都应保证他们创建的每个表具有一个主键，以便于
以后的数据操纵和管理。

表中的任何列都可以作为主键，只要它满足以下条件：
 任意两行都不具有相同的主键值；
 每个行都必须具有一个主键值（主键列不允许NULL值）。

`主键值规则 这里列出的规则是MySQL本身强制实施的。`
主键通常定义在表的一列上，但这并不是必需的，也可以一起使用
多个列作为主键。在使用多列作为主键时，上述条件必须应用到构成主
键的所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一）。

主键的最好习惯 除MySQL强制实施的规则外，应该坚持的
几个普遍认可的最好习惯为： 
 不更新主键列中的值；
 不重用主键列的值；
 不在主键列中使用可能会更改的值。（例如，如果使用一个
名字作为主键以标识某个供应商，当该供应商合并和更改其
名字时，必须更改这个主键。

## MySql

我们在前一章中介绍了数据库和SQL。正如所述，数据的所有存储、
检索、管理和处理实际上是由数据库软件——DBMS（数据库管理系统）
完成的。MySQL是一种DBMS，即它是一种数据库软件。

## Select
如果你 Select from 出來的可能都不一樣

**未排序数据** 如果读者自己试验这个查询(selct語句)，可能会发现显示输
出的数据顺序与这里的不同。出现这种情况很正常。如果没有
明确排序查询结果（下一章介绍），则返回的数据的顺序没有
特殊意义。返回数据的顺序可能是数据被添加到表中的顺序，
也可能不是。只要返回相同数目的行，就是正常的。

**结束SQL语句** 多条SQL语句必须以分号（；）分隔。MySQL
如同多数DBMS一样，不需要在单条SQL语句后加分号。但特
定的DBMS可能必须在单条SQL语句后加上分号。当然，如果
愿意可以总是加上分号。事实上，即使不一定需要，但加上
分号肯定没有坏处。如果你使用的是mysql命令行，必须加上
分号来结束SQL语句。

**SQL语句和大小写** 请注意，SQL语句不区分大小写，因此
SELECT与select是相同的。同样，写成Select也没有关系。
许多SQL开发人员喜欢对所有SQL关键字使用大写，而对所有
列和表名使用小写，这样做使代码更易于阅读和调试

**使用空格** 在处理SQL语句时，其中所有空格都被忽略。SQL
语句可以在一行上给出，也可以分成许多行。多数SQL开发人
员认为将SQL语句分成多行更容易阅读和调试

**检索多个列**
要想从一个表中检索多个列，使用相同的SELECT语句。唯一的不同
是必须在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。
当心逗号 在选择多个列时，一定要在列名之间加上逗号，但
最后一个列名后不加。如果在最后一个列名后加了逗号，将出
现错误

## DISTINCT

只能唯一
要放在列前面

不能部分使用DISTINCT DISTINCT关键字应用于所有列而
不仅是前置它的列。如果给出SELECT DISTINCT vend_id,
prod_price，除非指定的两个列都不同，否则所有行都将被
检索出来

## limit
限制結果
第一个数为开始
位置，第二个数为要检索的行数

行0 检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1
将检索出第二行而不是第一行。

在行数不够时 LIMIT中指定要检索的行数为检索的最大行
数。如果没有足够的行（例如，给出LIMIT 10, 5，但只有13
行），MySQL将只返回它能返回的那么多行。

MySQL 5的LIMIT语法 LIMIT 3, 4的含义是从行4开始的3
行还是从行3开始的4行？如前所述，它的意思是从行3开始的4
行，这容易把人搞糊涂。
由于这个原因，MySQL 5支持LIMIT的另一种替代语法。LIMIT
4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样

## order By
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。
如果列中有多行 NULL 值，它们将分为一组

不一定要select的才能排序

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

区分大小写和排序顺序 在对文本性的数据进行排序时，A与
a相同吗？a位于B之前还是位于Z之后？这些问题不是理论问
题，其答案取决于数据库如何设置。
在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL
（和大多数数据库管理系统）的默认行为。但是，许多数据库
管理员能够在需要时改变这种行为（如果你的数据库包含大量
外语字符，可能必须这样做）。
这里，关键的问题是，如果确实需要改变这种排序顺序，用简
单的ORDER BY子句做不到。你必须请求数据库管理员的帮助。

## WHERE子句操作符

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操
作符。


有between
要用and連結

空值檢查

### IN操作符

为什么要使用IN操作符？其优点具体如下。
* 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
* 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
* IN操作符一般比OR操作符清单执行更快。
* IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建
立WHERE子句。第14章将对此进行详细介绍。
IN WHERE子句中用来指定要匹配值的清单的关键字，功能与OR
相当

### NOT操作符
在where後面 跟in那些一樣

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

MySQL中的NOT MySQL支持使用NOT对IN、BETWEEN和
EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件
取反有很大的差别。

## 通識符


区分大小写 根据MySQL的配置方式，搜索可以是区分大小
写的。如果区分大小写，'jet%'与JetPack 1000将不匹配
可以用

like binary
二進制的會只會找一樣的

但這個
也可以用
sounds like

使用通配符的技巧
正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配
符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一
些使用通配符要记住的技巧。
1.  不要过度使用通配符。如果其他操作符能达到相同的目的，应该
使用其他操作符。
1.  在确实需要使用通配符时，除非绝对有必要，否则不要把它们用
在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起
来是最慢的。
1.  仔细注意通配符的位置。如果放错地方，可能不会返回想要的数

## 用正则表达式 进行搜索

### 基本字符匹配
Like 換成 REGEXP



LIKE匹配整个列。如果被匹配的文本在列值中出现

REGEXP在列值内进行匹配，如果被匹配的文本在
列值中出现

那么，REGEXP能不能用来匹配整个列值（从而起与LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可，
本章后面介绍。

匹配不区分大小写 MySQL中的正则表达式匹配（自版本
3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大
小写，可使用BINARY关键字，如WHERE prod_name REGEXP 
BINARY 'JetPack .000'。

### 进行OR匹配
用 |

[]是另一种形式的OR语句。事实上，正则表达式[123]Ton
为[1|2|3]Ton的缩写，也可以使用后者。但是，需要用[]来定义OR语句
查找什么。为更好地理解这一点

### 匹配特殊字符
为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。

注意注意

## 创建计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。下面举
几个例子。
1.  如果想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般包含在不同的表列中。
1.  城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签 打印程序却需要把它们作为一个恰当格式的字段检索出来。
1.  列数据是大小写混合的，但报表程序需要把所有数据按大写表示 出来。
3.  物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）。为打印发票，需要物品的总价 格。
5.  需要根据表数据进行总数、平均数计算或其他计算。

计算字段是运行时在SELECT语句
内创建的

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

MySQL的不同之处 多数DBMS使用+或||来实现拼接，
MySQL则使用Concat()函数来实现。当把SQL语句转换成
MySQL语句时一定要把这个区别铭记在心


## 函數
用于处理文本串（如删除或填充值，转换值为大写或小写

用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）
的数值函数。

用于处理日期和时间值并从这些值中提取特定成分（例如，返回
两个日期之差，检查日期有效性等）的日期和时间函数。

返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本
细节）的系统函数

**表11-1 常用的文本处理函数**
函 数 说 明
1. Left() 返回串左边的字符
1. Length() 返回串的长度
1. Locate() 找出串的一个子串
1. Lower() 将串转换为小写
1. LTrim() 去掉串左边的空格
1. Right() 返回串右边的字符
1. RTrim() 去掉串右边的空格
1. Soundex() 返回串的SOUNDEX值
1. SubString() 返回子串的字符
1. Upper() 将串转换为大写


### 日期和时间处理函数
* AddDate() 增加一个日期（天、周等）
* AddTime() 增加一个时间（时、分等）
* CurDate() 返回当前日期
* CurTime() 返回当前时间
* Date() 返回日期时间的日期部分
* DateDiff() 计算两个日期之差
* Date_Add() 高度灵活的日期运算函数
* Date_Format() 返回一个格式化的日期或时间串
* Day() 返回一个日期的天数部分
* DayOfWeek() 对于一个日期，返回对应的星期几
* Hour() 返回一个时间的小时部分
* Minute() 返回一个时间的分钟部分
* Month() 返回一个日期的月份部分
* Now() 返回当前日期和时间
* Second() 返回一个时间的秒部分
* Time() 返回一个日期时间的时间部分
* Year() 返回一个日期的年份部

使用`WHERE order_date = '2005-09-01'`可靠吗？```

order_ 
date

的数据类型为datetime。这种类型存储日期及时间值。样例表中
的值全都具有时间值00:00:00，但实际中很可能并不总是这样。如果
用当前日期和时间存储订单日期（因此你不仅知道订单日期，还知道
下 订 单 当 天 的 时 间 ）， 怎 么 办 ？ 比 如 ， 存 储 的 order_date 值 为
`2005-09-01 11:30:05`，则`WHERE order_date = '2005-09-01'`失败。
即使给出具有该日期的一行，也不会把它检索出来，因为WHERE匹配失
败。
解决办法是指示MySQL仅将给出的日期与列中的日期部分进行比
较，而不是将给出的日期与整个列值进行比较。为此，必须使用Date()
函数。Date(order_date)指示MySQL仅提取列的日期部分，


### SQL聚集函数


* AVG() 返回某列的平均值
* COUNT() 返回某列的行数
* MAX() 返回某列的最大值
* MIN() 返回某列的最小值
* SUM() 返回某列值之和
####

**AVG**
只用于单个列 AVG()只能用来确定特定数值列的平均值，而
且列名必须作为函数参数给出。为了获得多个列的平均值，
必须使用多个AVG()函数。
NULL值 AVG()函数忽略列值为NULL的行。

**COUNT()函数**
进行计数。可利用COUNT()确定表中行的数目或符合特
定条件的行的数目。
COUNT()函数有两种使用方式。
* 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空
值（NULL）还是非空值。
* 使用COUNT(column)对特定列中具有值的行进行计数，忽略
NULL值

**MAX()函数**

对非数值数据使用MAX() 虽然MAX()一般用来找出最大的
数值或日期值，但MySQL允许将它用来返回任意列中的最大
值，包括返回文本列中的最大值。在用于文本数据时，如果数
据按相应的列排序，则MAX()返回最后一行。
NULL值 MAX()函数忽略列值为NULL的行

**MIN()函数**

跟max一樣

**SUM()函数**
在多个列上进行计算 如本例所示，利用标准的算术操作符，
所有聚集函数都可用来执行多个列上的计算。

**涵式中用DISTINCT**

如果指定列名，则DISTINCT只能用于COUNT()。
DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），
否则会产生错误。类似地，DISTINCT必须使用列名，不能用
于计算或表达式。

将DISTINCT用于MIN()和MAX() 虽然DISTINCT从技术上可
用于MIN()和MAX()，但这样做实际上没有价值。一个列中的
最小值和最大值不管是否包含不同值都是相同的。

**组合聚集函数**

## 　分 组 数 据
分组数据，以便能汇总表内容的子集。这涉及两个
新SELECT语句子句，分别是GROUP BY子句和HAVING子句

### group By
GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，
为数据分组提供更细致的控制。
 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上
进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
（所以不能从个别的列取回数据）。
 GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在
GROUP BY子句中指定相同的表达式。不能使用别名。
 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子
句中给出。
 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列
中有多行NULL值，它们将分为一组。
 GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前

**ROLLUP**

ROLLUP是GROUP BY子句的擴充套件。 ROLLUP選項允許包含表示小計的額外行，通常稱為超級聚合行，以及總計行。 通過使用ROLLUP選項，可以使用單個查詢生成多個分組集。

ROLL UP 搭配 GROUP BY 使用，可以为每一个分组返回一个小计行，为所有分组返回一个总计行

https://zhuanlan.zhihu.com/p/58639733

**HAVING**
因为WHERE过滤指定的是行而不是分组。事实
上，WHERE没有分组的概念。
那么，不使用WHERE使用什么呢？MySQL为此目的提供了另外的子
句，那就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所
学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是
WHERE过滤行，而HAVING过滤分组

**不要忘记ORDER BY 一般在使用GROUP BY子句时，应该也给
出ORDER BY子句。这是保证数据正确排序的唯一方法。千万
不要仅依赖GROUP BY排序数据。**

https://www.1keydata.com/tw/sql/sqlhaving.html

請讀者注意： 如果被 SELECT 的只有函數欄， 那就不需要 GROUP BY 子句

## subQuery 子查詢

相关子查询（correlated subquery） 涉及外部查询的子查询。
这种类型的子查询称为相关子查询。任何时候只要列名可能有多义
性，就必须使用这种语法（表名和列名由一个句点分隔）

## 拼接字串
## 算法操作符



## 联结
现在，假如有由同一供应商生产的多种物品，那么在何处存储供应
商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信
息分开存储的理由如下。

*  因为同一供应商生产的每个产品的供应商信息都是相同的，对每
个产品重复此信息既浪费时间又浪费存储空间。
*  如果供应商信息改变（例如，供应商搬家或电话号码变动），只需
改动一次即可。
*  如果有重复数据（即每种产品都存储供应商信息），很难保证每次
输入该数据的方式都相同。不一致的数据在报表中很难利用。

完全限定列名 在引用的列可能出现二义性时，必须使用完
全限定列名（用一个点分隔的表名和列名）。如果引用一个
没有用表名限制的具有二义性的列名，MySQL将返回错误。

笛卡儿积（cartesian product） 由没有联结条件的表关系返回
的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘
以第二个表中的行数。

### 等值連結
等值联结（equijoin），它基于两个表之间的
相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍

上面等於下面
用where過濾兩個表的inner
等於
下面用on的用法

此语句中的SELECT与前面的SELECT语句相同，但FROM子句不
同。这里，两个表之间的关系是FROM子句的组成部分，以INNER
JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE
子句给出。传递给ON的实际条件与传递给WHERE的相同。

### 自联结

用自联结而不用子查询 自联结通常作为外部语句用来替代
从相同表中检索数据时使用的子查询语句。虽然最终的结果是
相同的，但有时候处理联结远比处理子查询快得多。应该试一
下两种方法，以确定哪一种的性能更好

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被
联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚
至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次

Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求
首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客

subquery

### 外部联结
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需
要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作：
 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的
客户；
 列出所有产品以及订购数量，包括没有人订购的产品；
 计算平均销售规模，包括那些至今尚未下订单的客户。
在上述例子中，联结包含了那些在相关表中没有关联行的行。这种
类型的联结称为外部联结

inner是内联结

## 组 合 查 询
多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语
句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个
查询结果集返回。这些组合查询通常称为并（union）或复合查询
（compound query）。

## UNION规则

并（union）或复合查询
（compound query）

主要有两种情况需要使用组合查询：
 在一个查询中从不同的表返回结构数据；
 对一个表执行多个查询，按一个查询返回数据

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关
键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个
UNION关键字）。
 UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以
隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与
单条SELECT语句中使用多个WHERE子句条件一样）。

## 插 入 数 据
不好
好
column跟 value都要完整

其优点是，即使表的结构改变，
此INSERT语句仍然能正确工作

总是使用列的列表 一般不要使用没有明确给出列的列表的
INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使
表结构发生了变化。

## update
從表名開始
set 欄位
然後Where指定

## view

### 为什么使用视图
我们已经看到了视图应用的一个例子。下面是视图的一些常见应用。
 重用SQL语句。
 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必
知道它的基本查询细节。
 使用表的组成部分而不是整个表。
 保护数据。可以给用户授予表的特定部分的访问权限而不是整个
表的访问权限。
 更改数据格式和表示。视图可返回与底层表的表示和格式不同的
数据

### 视图的规则和限制
下面是关于视图创建和使用的一些最常见的规则和限制。
 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相
同的名字）。
 对于可以创建的视图数目没有限制。
 为了创建视图，必须具有足够的访问权限。这些限制通常由数据
库管理人员授予。
 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造
一个视图。
 ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也
含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
 视图不能索引，也不能有关联的触发器或默认值。
 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT
语句。

## 视图的创建。
 视图用CREATE VIEW语句来创建。
 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
 用DROP删除视图，其语法为DROP VIEW viewname;。
 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR
REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创
建一个视图；如果要更新的视图存在，则第2条更新语句会替换原
有视图

## 利用视图简化复杂的联结
视图的最常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结

###### tags: `MySql`