MySQL必知必会1-13章笔记

笔者前面已经学习过数据库系统概念一书，对于一般的SQL书写有着一定的经验。然后想着去看一下专门讲某一个数据库管理系统的SQL书写，因此就去看了这本MySQL必知必会，顺便记录了一些以前自己没太注意或者是不知晓的知识。
因为只记录了我自己觉得有用的部分，所有我觉得本文更像一个小册，让学过一些SQL的或者是想学习MySQL应用方面的人看。当对某方面有疑问时不记得语法时可随时查看。
另外，写好SQL的关键支持还是多练习，本文并没有记录我自己实践的SQL语句。读者可以在自己本机的MySQL上进行操作，在感觉自己已经会基本的连表以及查询后就可以前往LeetCode或者是牛客进行对应的SQL练习。

关系型数据库基本术语

数据库（databa）：保存有组织的数据的容器（可以是一个文件或一组文件）
数据库系统（DBMS）：我们日常所使用的数据库软件，DBMS负责创建和操纵数据库
数据库可以是保存在硬件上的文件也可以不是。在很大程度上说，数据库究竟是文件还是别的什么东西并不重要，因为你并不直接访问数据库；你使用的是DBMS，它替你访问数据库。
表（table）：结构化的文件，某种特定类型数据的结构化清单。（表具有唯一的名字，它所属数据库中名字唯一）
模式（schema）：关于数据库和表的布局以及特性（数据在表中如何存储）的信息
列（column）：表中的一个字段，一张表由若干列组成
数据类型（datatype）：所容许的数据的类型，每一列都有着相应的数据类型，它限制着存储该列的数据。（数据类型有助于排序，优化磁盘等）
行（row）：表中的一个记录
主键（primary key）：表中的若干列，可唯一表示表中的每一行，同时其任意真子集不能唯一表示表中每一行

MySQL简介

数据的所有存储、检索、管理和处理都是由数据库管理系统（DBMS）完成的，MySQL是一款非常流行的DBMS系统。主要有以下几个原因：

1. 成本很低（MySQL开放源代码）
2. 性能较好
3. 可信赖（很多大公司在用）
4. 简单（MySQL简单安装和使用）

MySQL是基于客户机——服务器的软件，服务器部分软件负责所有数据的访问和处理（与数据文件打交道），而客户机软件负责将对于数据的访问命令或者是SQL语句通过网络发送给服务器。服务器软件收到请求后，对于数据进行处理，最后将结果返回给客户机软件。

客户机和服务器软件可能安装在两台计算机或一台计算机上。不管它们在不在相同的计算机上，为进行所有数据库交互，客户机软件都要与服务器软件进行通信。

这些活动对于用户是透明的，数据存储在别的地方或者说数据库服务器软件为你完成数据处理任务的事实是隐藏的，不需要直接访问数据库文件。甚至于，你不能直接访问数据库文件。
这样设计的好处有许多，MySQL客户机可以是mysql命令行工具，图形化操作工具，以及程序设计语言所提供的驱动包。也就说一台机器上的数据库系统，可以被不同的客户机轻松连接。

使用MySQL

1. 拥有可用的MySQL DBMS软件和MySQL客户机，客户机通过网络利用指定的用户登录到MySQL系统上
   1. 登录连接到MySQL上所需信息
   2. 主机名（计算机名）——本机为localhost
   3. 端口（若使用的是默认端口3306，则该选项不是必须的）
   4. 用户名（MySQL中已添加的合法用户名）
   5. 口令（密码）
2. 成功连接MySQL之后，此时不能直接通过表名访问某张表中的数据，可以使用数据库名.表名的形式访问一张具体的表。当然，最好是使用use databasename;命令切换到指定的数据库中，这样就可以直接通过表名访问该数据库中的表

USE语句并不返回任何结果。依赖于使用的客户机，显示某种形式的通知。在mysql命令行工具中，若数据库切换成功，则显示Database Changed

MySQL中利用show命令从内部提取出相关信息：

• show databases；：返回数据库的一个可用列表
• show tables;：返回数据库中表的列表
• show columns from table_name;：返回表名为table_name的表中列的一些相关信息，包括列名，数据类型，是否可以为空，键信息，默认值以及其它信息
• describe table_name;：上面一条命令的简化版本
• show create database; show create table;：显示特定数据库或者是表的MySQL创建语句（DDL）
• show grants;：显示授权用户的安全权限
• show status; show errors; show warnings;：显示服务器的一些消息

数据的检索

最简单及通用的检索模版：select [列名] from 表名;其中[ ]表示1至多个

• 列名必须在相应表中存在
• *表示检索表中的所有列
• 可以使用distinct 列名消除在该列上值重复的元组，但是值得注意的是distinct关键字不能部分使用，即distinc a, b所检索出来的元组并不会在a列上进行一个去重
• limit a：只取检索出来的元组的前a条；limit a, b;只取检索出来元组的第a条到a+b条，若a+b大于总条数，则只取到结果的最后一个元素

普通检索出来的数据并不是随件顺序显示的。如果不排序，数据一般以它在底层表中顺序显示，这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。

关系数据库理论认为，如果不明确规定排序顺序，则不应该假定检索出来数据的顺序有意义。

对于检索数据的排序

order by字句
默认排序方式：升序
升序关键字：asc，降序关键字desc
order by a：按a属性的值多检索结果进行升序排列
order by a desc：按a属性的值对检索结果进行降序排列
order by a, b desc：对于检索结果，首先按a属性升序排列，在a属性值相同的情况下，按b属性降序排列
在一般情况下，MySQL中字典序'a' == 'A'。
**order by**结合**limit**的结合使用：

• 找出包含某一个列中最大值或最小值的元素的元组，甚至是找出某一列第k大值的元组（前提是没有重复元素）
• 在用重复元素的情况下，一般就要结合去重或者是子查询一起使用

一个SQL语句中同时出现order by字句和limit字句时，order by字句要出现在from字句之后，而limit字句需要出现在order by字句之后。

对检索数据的过滤

where字句：只保留满足where字句条件的元组
where字句一般位于from字句后面，同时order by字句位于where字句后面。

为什么选择用SQL在数据库中查询时对于数据进行过滤，而不是在应用层对所有数据进行一个过滤？

• 数据库是做过优化的，可以在查询时对于快速地对数据进行过滤
• 让客户机应用去处理数据库的一个工作可能会极大的影响应用性能
• 最终从数据库中查询出来结果（无论是过滤前还是过滤后），都需要通过网络传给客户机应用，相对来说网络传输的速度还是比较慢的

where字句支持的条件操作符：常规的<,<=等与常规编程语言中的几乎一致，需要注意的是它有三个不同条件判断符：

• <>表示不等于，而不是用常规!=
• between表示范围条件，如a between 10 and 20表示a属性的值要大于等于10并且小于等于20的值才能留下
• 等于符号是=，而不是一众程序设计语言中的==

**注意：**MySQL在执行匹配时，默认是不区分大小写的，如条件a = 'ab'，此时a的值为'ab'或'AB'或'Ab'或'aB'都满足此条件
在where字句的条件中，只有在数据类型为字符串的列进行比较时，值才需要加引号（单引号和双引号皆可），而对于数值列的比较则是不需要加引号的。
空值条件判断：
空值（NULL）：在一个列不包含值时，称其包含空值。空值不是0也不是空字符串。
MySQL中有一条特殊的条件判断语句判断值是否为空，where a is null将筛选出a属性值为空的元组。

空值与不匹配问题：有一张表t，t只有一个属性(money)，数据类型为整型，且t中只有3个元组，[2], [NULL], [NULL]，此时执行select * from t where money <> 2将会返回空集合，即没有一个元组匹配该条件。
实际上，这里是因为在SQL中条件判断的结果除了true，false之外还有unknown。对于牵扯到NULL的条件表达式返回值为unknown，只有使条件表达式返回值为true的元组的才能被留下。

进一步过滤：
where字句中可以有多个条件语句，它们之间用逻辑操作符连接（And 和 Or）。
逻辑操作符And表示逻辑与，Or表示逻辑或，含义与大多数程序设计语言中逻辑与和逻辑或一致，其中And操作符的优先级比Or高，也就是说在where字句中同时出现And和Or逻辑符，强烈建议通过小括号表示我们想要的一个运算执行顺序。
In操作符：测试元素是否在某个集合中，在的话返回true，否则返回false。示例：where a in (1, 2, 3)此时某元组在a上的取值为1或2或3则返回true，否则返回false。同时该where字句也可以用逻辑操作符or表示，where a = 1 or a = 2 or a = 3，那么为什么要有in操作符呢？

• 当要判断的元素较多时，使用or操作符比较麻烦，而in操作符比较直观
• in操作符一般比or操作符执行的快
• in操作符后面可以跟select子句

Not操作符：用于否定跟在它后面的条件语句的结果

MySQL中只支持使用Not对in，exists和between，is null，like子句进行取反

通配符对于数据进行过滤：
通配符是指占位符（% 和 _)，在判断条件中我们也不确定某个位置具体需要什么值的时候，可以使用合适的通配符预定好位置。
通配符需要和like操作符搭配使用，因为在不使用like的情况下，一般都是采用相等匹配的形式。通配符%表示0至多个字符，而通配符_表示有且仅能是一个字符，不能多也不能少。
通配符常用形式：

• abc%：以abc开头
• %abc：以abc结尾
• a_b：只能匹配aab,abb,acb等形式的字符串

通配符搜索很常用，但它相对于普通搜索所花的时间更长，因此有一些合理使用通配符的技巧：

1. 不要过度使用通配符，能够使用普通搜索完成的就尽量用普通搜索
2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的

正则表达式对数据进行过滤：
where字句允许通过正则表达式的形式过滤检索出来的数据，具体用法是使用regexp指定某个属性与正则表达式进行匹配。

regexp与like的区别：
like是整行匹配，也就是说某个元组它只有在like谓词要搜索的属性的值上与通配符表达式完全匹配，条件结果才为true。而regxep则不一样，它只需要值的某一部分匹配正则表达式即可。
例子：值abc，通配表达式：ab，正则表达式ab。abc like ab返回false，而abc regexp ab返回true。

再接着就是MySQL中所支持的大部分正则表达式以及其用法：

• .：任意一个字符
• |：表示或，2 | 3即表示2或者3
• [123]：[]表示其中1个即可，因此[123]表示1或2或3
• [^123]：表示除了1,2,3之外的一个字符皆行
• [0-9]：表示0至9中的一个数字，[a-z]表示a到z中的一个字符
• \\转义字符：通过转义字符可以匹配正则表达式中有特殊含义的字符，如\\.匹配.，而\\[匹配[等等等，有意义的单个字符都需要通过转义字符转义才能匹配
• ()：该符号是将正则表达式中的字符所匹配的字符串看作一个整体，它本身并不匹配任何字符，感觉挺重要的

特殊字符及其含义图：

这本书是2009年出版的，用的是mysql5.1吧，但我现在用的mysql8.5是已经支持\w表示任意数字和字母以及\d表示任意数字的。那么mysql可能较为早期的版本也已经支持这种形式了

匹配多个实例：

• *：0个或多个实例
• +：1个或多个实例
• ?：0个或1个实例
• {n}：指定匹配n个实例
• {n, }：大于等于n个实例
• {n, m}：大于等于n小于等于m个实例

定位符：

• ^：文本的开始
• $：文本的结尾

计算字段

原数据库中不存在的字段，通过某些已存在字段进行一系列运算得到的新结果就被称为是计算字段。
字段与列的意思几乎相同，经常互换使用，但一般在数据库中称为列。而且只有数据库知道select语句中哪些是实际的列，哪些是计算字段。从客户机（如应用程序）的角度来看，计算字段和实际的列没啥区别。

在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

拼接字段：
MySQL中的字段拼接是通过concat函数实现的，concat函数接受若干的需要拼接的串，将它们按照所在参数中的顺序拼接起来。concat(0, 1, 2, 3)的结果为0123。
RTrim函数去除字符串右边的空格，LTrim函数去除字符串左边的空格，Trim函数去除字符串两边的空格。
使用别名：
计算字段实际上是没有名字的，这就意味着我们无法直接引用它（子查询中经常要引用查询结果），因此我们需要给它起一个别名，关键字as就是起这个作用的，concat(0, 1, 2, 3) as s表示将拼接字段的列名起为s。
别名在表名重合（表与自己连接时）非常有用，可以直接解决重名问题。
执行算数运算：
这个没啥好说的，就是指整型字段之间可以进行+，-，*，/这四种基本算数运算。

测试计算：
感觉这也是MySQL中一个比较方便用户进行简单测试的一个特性，它可以直接使用select语句而省略from字句。如select 1就返回1，select now()就返回当前时间，select trim(’ ac ')返回ac

使用数据处理函数

SQL可以利用函数处理数据，它给数据转化和处理带来了很大的方便。

不同的DBMS支持不同的函数，而且函数之间的差异可能很小也可能很大。为了代码的可移植，很多SQL程序员不建议使用函数，但是如果不使用函数，编写实现某些功能的代码会很艰难。
因此如果决定使用函数，应该做好注释，以便其它人能够确切地知道这段SQL代码的含义。

一般函数类型：

• 字符串处理函数
• 算数函数
• 日期函数
• 系统函数

注意部分：

• date, time和datetime的区别
• round和trancate的区别

MySQL函数API参考链接

聚集函数汇总数据

聚集函数一般是完成以下任务的：

• 获取满足一定条件的行的个数
• 获取满足一定条件的行的和（一般指的是整数）
• 找出某一列满足条件的行的最大值，最小值或者是平均值

聚集函数：运行在行组上，计算和返回单个值的函数。
在不使用group by分组的情况下，所有的行（元组）属于同一个分组。

avg函数：只能作用于单个列，并且会自动忽略NULL所在的行（即不会将改行算在计算的总行数内）
count函数：有两种形式，一种是count(*)它直接返回所用的行的数目，不管行中各列是什么值。一种是count（指定列名）这是它会忽略指定列的值为NULL的行。
max函数：首先它会忽略指定列的值为NULL的行，如果该列的数据类型是整型或日期的话直接返回数字或日期的最大值，如果数据类型是字符串（文本）的话，先按照指定列进行排序，max函数返回的是最后一行对应的值。
min函数：与max函数用法几乎一致，只不过它返回的是最小值
sum函数：忽略指定列为NULL的行，再接着就是计算指定列的和

利用基本算数运算可以聚集函数在多个列上执行计算

聚集不同的值：
默认是对满足条件的所有行（包括重复行）进行一个行数的聚集。但是可以在聚集函数的参数中（指定列的前面）加上distinct关键字，这样就可以去除重复行，进而再次进行聚合。

• count函数中，只有在指定列的情况下才能使用使用distinct关键字，count（*）这种情况是不能够使用distinct关键字的
• max函数和min函数中，虽然可以用distinct关键字，但最后得到的答案是一样的

对数据进行分组

group by字句：将where字句过滤完得到的元组（也可能未使用where字句过滤）进行一个逻辑分组，进而可以对每个分组进行相应的聚集运算。
group by字句使用的一些注意事项：

• group by字句需要位于where字句后面，order by字句前面
• 如果分组列中具有NULL值，则它将作为一个分组返回。如果该列中有多行NULL值，它们将分为一组
• 在使用group by字句的SQL语句中，除了group by字句中列出的字段，其它的字段想要在select字句中出现，必须是以作为聚集函数参数的形式出现
• group by字句中出现的列要么是表中合法的列，要么是有效的表达式（不能是聚集函数），因为group by字句中不能出现别名，所以select字句中也只能是对应一模一样的表达式

过滤分组：
having字句：where字句中的条件语句都可以在having字句中出现，但是where字句是针对于行级元素的过滤，也就是说在分组之前，where字句就过滤完了。分组之后，借助于having字句可以直接舍弃不满足条件的分组（一个分组可能有很多行）。在有group by的SQL语句中，与select字句一致，having字句中的列要么呢是，group by字句中列出来的列，要么是作为聚集函数的参数显示。

select语句执行次序

后续将继续子查询，联表查询以及高阶部分的内容，子查询和联表查询我觉得还是得以习题为主，在不同环境下写出正确的SQL才是真的。

如果感觉本文对你有所帮助的话，麻烦给笔者一个三连，你的支持是我前进的动力。一起加油！！！