《MySQL必知必会》阅读笔记

约定

h3：代表章

h4：代表节

📖：代表概念

✏️：代表SQL指令

🖊：代表函数

📝：代表example

了解SQL

📖数据库(database) ：保存有组织的数据的容器(通常是一个文件或一组文件)

📖数据库软件：被称为DBMS(数据库管理系统)。数据库是通过DBMS创建和操纵的容器

📖表(table)：某种特定类型数据的结构化清单

关键点:存储在表中的数据是一种类型的数据或一个清单

数据库中的每个表都有一个唯一的名字来标识自己

不同的数据库中可以使用相同的表名

📖模式(schema)：关于数据库和表的布局及特性的信息

📖列(column)： 表中的一个字段。所有表都是由一个或多个列组成的

数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类

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

📖行(row)：表中的一个记录

很大程度上，数据库记录(record)和行是可以互相替代的。但从技术上说，行才是正确的术语

📖主键(primary key)：列(或一组列)，其值能够唯一区分表中每个行

应该总是定义主键

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

• 任意两行的主键值都不同
• 每个行都必须具有一个主键值(主键列不允许NULL值)

在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的(但单个列的值可以不唯一)

Best Practice：

• 不更新主键列中的值
• 不重用主键列的值
• 不在主键列中使用可能会更改的值

MySQL简介

DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机—服务器的DBMS

客户机—服务器应用分为两个不同的部分：

• 服务器部分是负责所有数据访问和处理的一个软件，运行在称为数据库服务器的计算机上。与数据文件打交道的只有服务器软件
• 客户机是与用户打交道的软件

以MySQL为例：

• 服务器软件为MySQL DBMS。你可以在本地安装的副本上运行，也可以连接到运行在你具有访问权的远程服务器上的一个副本
• 客户机可以是MySQL提供的工具、脚本语言(如Perl)、Web应用开发语言(如ASP、ColdFusion、JSP和PHP)、程序设计语言(如C、C++、Java)等

使用MySQL

连接

为了连接到MySQL，需要以下信息：

• 主机名(计算机名)——如果连接到本地MySQL服务器，为localhost
• 端口(如果使用默认端口3306之外的端口)
• 一个合法的用户名
• 用户密码(如果需要)

选择数据库

✏️使用数据库

USE db_name;  #db_name是数据库名

必须先使用USE打开数据库，才能读取其中的数据

了解数据库和表

✏️显示可用数据库的列表

SHOW DATABASES;

✏️显示一个数据库内的所有列表

SHOW TABLES;

✏️显示表的所有列

SHOW COLUMNS FROM tb_name; #tb_name是表名

对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息(如自动增量auto_increment)

快捷方式

DESCRIBE tb_name;

✏️其他的SHOW语句

SHOW STATUS;  					#显示广泛的服务器状态信息
SHOW CREATE DATABASE db_name;  	#显示创建特定数据的MySQL语句
SHOW CREATE TABLE tb_name;  	#显示创建特定表的MySQL语句
SHOW GRANTS;  					#显示授权用户的安全权限
SHOW ERRORS;  					#显示服务器错误消息
SHOW WARNINGS;  				#显示服务器警告信息

✏️进一步了解SHOW

HELP SHOW; #可以在Command Line Client中使用HELP指令

检索数据

✏️检索单个列

SELECT col_name FROM tb_name;  #col_name是列名

未排序数据：如果没有明确要求排序，返回数据的顺序没有特殊意义

结束SQL语句：需要使用;间隔多条SQL语句

大小写：SQL语句不区分大小写

Best Practice：关键字采用大写，列名和表名采用小写

空格：在处理SQL语句时，其中所有空格都被忽略

✏️检索多个列

SELECT col_name1, col_name2, col_name3  FROM tb_name;

✏️检索所有列

SELECT * FROM tb_name;

列的顺序一般是表定义时的顺序，但表的模式的变化(如添加或删除列)可能会导致顺序的变化

Best Practice：除非你确实需要表中的每个列，否则最好别使用*通配符

检索未知列：使用通配符能检索出名字未知的列

✏️检索不同的行

SELECT DISTINCT col_name FROM tb_name;

查找列的时候，不同的行的在这一列的值可能是相同的，这时候可以采用DISTINCT关键字来去重

DISTINCT关键字必须直接放在列名的前面

不能部分使用DISTINCT：DISTINCT关键字作用于所有列，即SELECT语句后接的所有列都会受DISTINCT影响。这时候只有每一列的值都相同时才会把两行看成是相同的，才会被去重

✏️限制返回结果数目

SELECT col_name FROM tb_name LIMIT 5; # 返回最多5条结果

✏️设置开始检索的行数

SELECT col_name FROM tb_name LIMIT 10, 5; # 从第10行开始检索(不包括第10行本身)，最多返回5行

替代语法

SELECT col_name FROM tb_name LIMIT 4 OFFSET 3;
# 等同于
SELECT col_name FROM tb_name LIMIT 3, 4;

✏️使用完全限定的表名

SELECT col_name FROM tb_name;
# 等同于
SELECT tb_name.col_name FROM tb_name;
# 等同于
SELECT tb_name.col_name FROM db_name.tb_name;

使用这种语法的前提是col_name确实位于tb_name中，tb_name确实位于db_name中

排序检索数据

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

✏️排序输出结果

SELECT col_name FROM tb_name ORDER BY sort_col_name;

col_name和sort_col_name可相同，可不同

✏️按多个列排序

SELECT col_name1, col_name2, col_name3 FROM tb_name ORDER BY sort_col_name1, sort_col_name2;

排序按照先后顺序来，先按sort_col_name1排序，再按sort_col_name2排序，依此类推

如果sort_col_name1的值是唯一的，则不会再按sort_col_name2排序了，依此类推

✏️指定排序方向

SELECT col_name FROM tb_name ORDER BY sort_col_name DESC; # 按降序排序

✏️按多个列排序时指定排序方向

SELECT col_name FROM tb_name ORDER BY sort_col_name1 DESC, sort_col_name2, sort_col_name3 DESC;

DESC关键字只作用于它前面的列名，表示降序排序

上述语句先按sort_col_name1降序排序，再按sort_col_name2升序排序，最后按sort_col_name3降序排序

ASC关键字表示升序排序，是默认的排序方式，可以不显式指定它

在字典(dictionary)排序顺序中，A被视为与a相同，这是MySQL的默认行为。数据库管理员能够在需要时改变这种行为

📝找出最昂贵的物品

SELECT price FROM products ORDER BY price DESC LIMIT 1;

过滤数据

使用WHERE子句

✏️指定过滤条件

SELECT col_name FROM tb_name WHERE condition_sentence; 
# condition_sentence代表条件语句

📝找出价格为5的商品名

SELECT price FROM products WHERE price = 5;

数据也可以在应用层过滤，但这并不是一个好的选择

同时使用ORDER BY和WHERE子句时，ORDER BY应该位于WHERE之后，否则会报错

WHERE子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

📝BETWEEN操作符的使用

SELECT name FROM products WHERE price BETWEEN 5 AND 10;  
#选出价格在[5, 10]区间的商品名，注意是闭区间

控制检查

📖NULL：无值(no value)，它与字段包含0、空字符串或包含空格不同

✏️搜索缺少字段的行(可以用来检查具有NULL值的列)

SELECT col_name FROM tb_name WHERE check_col_name IS NULL;

通过过滤选择不具有特定值的行时，你可能希望返回具有NULL值的行。但是，因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。

简而言之：过滤列为NULL值的行默认不会被返回，除非你特别指定NULL相关的指令

数据过滤

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

✏️AND操作符：用在WHERE子句中，检索满足所有给定条件的行

SELECT col_name FROM tb_name WHERE condition_sentence1 AND condition_sentence2;

✏️OR操作符：用在WHERE子句中，检索满足任一给定条件的行

SELECT col_name FROM tb_name WHERE condition_sentence OR condition_sentence2;

OR操作符的优先级低于AND操作符

Best Practice：使用圆括号()给运算符分组

✏️​IN操作符：指定匹配值的集合。合法值括在圆括号中，以逗号分隔

SELECT col_name FROM tb_name WHERE match_col_name in (match_value1, match_value2, match_value3);

IN操作符的功能是可以用OR语句来实现的，但是显然匹配集合较长的时候IN操作符更简洁，更方便，更清晰

✏️NOT操作符：WHERE子句中用来否定后面的条件。

SELECT col_name FROM tb_name WHERE NOT condition_sentence;

IN (matchvalue1, matchvalue2)是属于condition_sentence的

也就是说我们可以使用NOT IN (matchvalue1, matchvalue2)这样的语句(组成的新语句依旧属于condition_sentence)

还可以使用NOT BETWEEN value1 AND value2这样的指令

用通配符进行过滤

📖通配符(wildcard)：用来匹配值的一部分的特殊字符

📖搜索模式(search pattern)：由字面值、通配符或两者组合构成的搜索条件

✏️LIKE操作符​：LIKE表明后面的搜索模式利用通配符匹配

✏️%通配符：匹配任何字符出现任意次数(包括0次)

📝%通配符的使用

SELECT name, price FROM products WHERE name LIKE 'jet%'
# 匹配所有以jet开头的词

注意，'jet%'是用''包裹的，而不能使用""

SELECT name, price FROM products WHERE name LIKE '%anvi%'
# 匹配所有包含anvi的词

SELECT name, price FROM products WHERE name LIKE 's%e'
# 匹配所有以s开头，e结尾的词

注意尾空格：'anvi '和'%anvi'是不匹配的,但是后面有无空格是很难发现的(可以使用TRIM()相关函数去除空格)

通配符是不能匹配NULL的

✏️_通配符：匹配单个字符

📝_通配符的使用

SELECT name, price FROM products WHERE name LIKE '_ ton'
# 匹配'1 ton'、'. ton'、'a ton', 但是不匹配'he ton'

Best Practice：

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。

用正则表达式进行搜索

MySQL仅支持正则表达式的一个很小的子集。

✏️REGEXP操作符：REGEXP后所跟的东西作为正则表达式处理

• .匹配任意一个字符

• |表示或，如hello|world表示匹配hello或者world

• []给出匹配字符集合，如[1ab]表示匹配1或a或b

• ^用于排除字符集合，用在[]中，如[^ab]表示匹配除了a和b之外的其他字符

• -给出匹配范围，用在[]中，如[0-9]表示匹配0到9的任意一个数字

• \\表示转义，即用来消去特殊字符的含义，如用\\.来匹配.字符。还可以用来引用元字符，如\\n表示匹配换行符。

• 字符类

  类	说明
  [:alnum:]	任意字母和数字(同[a-zA-Z0-9])
  [:alpha:]	任意字母(同[a-zA-Z])
  [:blank:]	空格和制表(同[\\t])
  [:cntrl:]	ASCII控制字符(ASCII码 0到31和127)
  [:digit:]	任意数字(同[0-9])
  [:graph:]	与[:print:]相同，但不包括空格
  [:lower:]	任意小写字母(同[a-z])
  [:print:]	任意可打印字符
  [:punct:]	不在[:alnum:]和[:cntrl:]中的其他字符
  [:space:]	包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])
  [:upper:]	任意大写字母(同[A-Z])
  [:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

• 重复元字符

  元字符	说明
  *	0个或多个匹配
  +	1个或多个匹配(等于{1,})
  ?	0个或1个匹配(等于{0,1})
  {n}	指定数目的匹配
  {n,}	不少于指定数目的匹配
  {n,m}	匹配数目的范围(m不超过255)

• 定位符

  元字符	说明
  ^	文本的开始
  $	文本的结尾
  [[:<:]]	词的开始
  [[:>:]]	词的结尾

为了匹配反斜杠\本身，需要使用\\\

^匹配符的作用有两个，一个在[]中表示排除字符集合，一个用于表明文本的开头

可以发现，REGEXP匹配有^和$这两个匹配开头或结尾的元字符，而LIKE并没有

REGEXP匹配最前面没有^时，相当于最前面是.*，匹配任意个任意字符；最后面没有$时，相当于最后面是.*

用REGEXP来理解LIKE：相当于每个LIKE匹配开头是^，末尾是$

例子：

LIKE模式：a%b可以匹配abc，但不能匹配dabc和abcd

REGEXP模式：a.*b既可以匹配abc，也能匹配dabc和abcd

REGEXP模式：^a.*b可以匹配abc和abcd，但不能匹配dabc

REGEXP模式：a.*b$可以匹配abc和dabc，但不能匹配abcd

📝正则表达式的使用

SELECT name FROM products WHERE id REGEXP '.0'
# 匹配'00'、'a0'、'1a0'、'a0h',不匹配'0'、'0a'，因为0前面至少要有1个字符，后面可以跟任意个字符

SELECT name FROM products WHERE id REGEXP '1|2|3 Ton'
# 匹配'1'或'2'或'3 Ton'，而非匹配'1 Ton'或'2 Ton'或'3 Ton'

SELECT name FROM products WHERE id REGEXP '[123] Ton'
# 匹配'1 Ton'或'2 Ton'或'3 Ton'

SELECT name FROM products WHERE id REGEXP 'sticks?'
# 匹配stick或sticks

SELECT name FROM products WHERE id REGEXP '[[:digit:]]{4}'
# 匹配4个连续的数字

✏️测试正则表达式

SELECT string