《MySQL必知必会》| 读书笔记(上)

1.相关术语

数据库(database)：保存有组织的数据的容器(通常是一个文件或一组文件)。
数据库软件(DBMS)：通过DBMS创建和操作的数据库。
表(table)：某种特定类型的结构化清单。
模式(schema)：关于数据库和表的布局及特性的信息。模式可以用来描述数据库中特定过的表以及整个数据库(和其中表的关系)。
列(column)：表中的一个字段。所有表都是由一个或多个列组成的。
数据类型(datatype)：所容许的数据的类型。每个表列都有相应的数据类型，它限制(或容许)该列中存储的数据。
行(row)：表中的一个记录。
主键(primary key)：一列(或者一组列)，其值能够唯一区分表中的每个行。唯一标识表中每行的这个列(或这组列)成为主键。主键用来表示一个特定的行。
作为表中主键的条件：

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

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

建议：

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

客户机—服务器软件
DBMS可分为俩类：一类为基于共享文件系统的DBMS，另一类为基于客户机—服务器的DBMS。
MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机—服务器的数据库。
与数据文件打交道的只有服务器软件。
客户机是与用户打交道的软件。

---

2.使用MySQL数据库

链接MySQL:

mysql -u root -p

• 主机名(计算机名)–本地MySQL服务器为localhost；
• 端口(默认3306)；
• 合法用户名；
• 用户口令(可选)；

选择数据库：

执行数据库操作前，需要选择一个数据库。使用USE关键字。

USE database_name;
Database changed

关键字：作为MySQL语言组成部分的一个保留字。绝不要用关键字命名一个表或者列。

SHOW DATABASES;		//返回可用数据库信息
SHOW TABLES;		//返回当前选择的数据库内可用表的列表

//对表中的每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息以及其他信息
SHOW COLUMNS FROM table_name;	
DESCRIBE table_name;	//快捷方式

SHOW STATUS;		//显示广泛的服务器状态信息
//显示创建特定数据库或表的MySQL语句
SHOW CREATE DATABASE;
SHOW CREATE TABLE;

SHOW GRANTS;		//显示授予用户(所有用户或特定用户)的安全权限
//显示服务器错误或警告
SHOW ERRORS;
SHOW WARNINGS;

HELP SHOW;			//显示允许的SHOW语句

自动增量：某些表列需要唯一值，在每行添加到表中时，MySQL可以自动的为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值(手动分配必须记住最后一次使用的值)。这个功能就是所谓的自动增量。

---

3.检索数据

检索单个列：
SELECT column_name FROM table_name;

• 返回的数据没有过滤，也没有排序。
• 结束SQL语句必须以";"结尾
• SQL语句不区分大小写，但习惯SQL关键字使用大写，而对所有列名和表名使用小写。
• 在处理SQL语句时，其中所有空格都会被忽略掉

检索多个列：
SELECT column_id,column_name,column_contents FROM table_name;

• 选择多个列时，使用逗号分隔；

检索所有的列：
SELECT * FROM table_name;

• 尽量少使用通配符*，因为检索不需要的列通常会降低检索和应用程序的性能；
• 检索未知索引，使用通配符可以检索出名字未知的列；

检索不同的列：

SELECT DISTINCT column_name FROM table_name;

• DISTINCT关键字应用于所有的列而不仅是前置它的列；
• 不能部分使用DISTINCT

限制结果：
SELECT column_name FROM table_name LIMIT num1,num2;
SELECT column_name FROM table_name LIMIT num1 OFFSET num2; //替代语法，从行num2开始取num1行

• 带一个值的LIMIT总是从第一行开始，给出的数为返回的行数；
• 带俩个值的LIMIT可以指定从行号为第一个值的位置开始，第二个值为要检索的行数；
• 需要注意的是检索出来的第一行为行0，而不是行1；
• 行数不够时，检索最大行数；

完全限定名
SELECT table_name.column_name FROM table_name;
SELECT table_name.column_name FROM database_name.table_name;

---

4.排序检索数据

子句：SQL语句由子句构成，一个子句通常由一个关键字和所提供的数据组成。

排序数据
SELECT column_name FROM table_name ORDER BY column_name;

• ORDER BY子句取一个或多个列的名字，据此输出进行排序；
• ORDER BY使用的列不一定是检索列，使用非检索的列排序数据是完全合法的；

按多个列排序
SELECT column_name1,column_name2,column_name3 FROM table_name ORDER BY column_name1,column_name2;

• 按多个列排序，只要指定列名，列名之间用逗号分开即可；
• 在按多个列排序时，排序完全按规定的顺序进行：先比较第一个列，相同则比较第二个；

指定排序方向
SELECT column_name FROM table_name ORDER BY column_name1 DESC,column_name2;

• ORDER BY子句默认使用升序排列；
• 使用降序排列需要指定DESC关键字；
• 在多个列上降序排列，必须对每个列指定DESC关键字；
• 与DESC相反的关键字是ASC,但升序是默认的；
• 子句位置 FROM - ORDER BY - LIMIT

5.WHERE子句

使用WHERE子句
SELECT column_name1,column_name2 FROM table_name WHERE [filter condition];

• 只检索所需数据需要指定搜索条件(search criteria),搜索条件也称为过滤条件(filter condition).
• SQL过滤与应用过滤，数据可以在应用层过滤，但建议多对数据库进行优化，以便快速有效的对数据进行过滤；
• 让客户机应用处理数据库的工作将会极大的影响应用的性能，并且使所创建的应用完全不具备可伸缩性；
• 且在客户机上使用过滤数据，服务器将不得不发送多余的数据，将导致网络宽带的浪费；
• 子句位置FROM - WHERE - ORDER BY - LIMIT

WHERE子句操作符

=				//等于
<>				//不等于
!=				//不等于
<				//小于
<=				//小于等于
>				//大于
>=				//大于等于
BETWEEN			//在指定的俩个值之间

MySQL在执行匹配时默认不区分大小写。
何时使用引号：单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

范围值检查
SELECT column_name1,column_name2 FROM table_name WHERE column_name BETWEEN num1 AND num2;

• 使用BETWEEN 时需要指定俩个值——所需范围的低端值和高端值，俩个值必须用AND关键字分割；
• BETWEEN匹配范围中所有的值，包括指定的开始值和结束值；

空值检查
SELECT column_name FROM table_name WHERE column_name IS NULL;

• NULL 无值，它与字段包含0、空字符串或仅仅包含空格不同；
• 因为NULL具有特殊含义，所以在匹配过滤或不匹配过滤时不返回他们；
• 在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行；

---

6.数据过滤

操作符(operator) ：用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符(logic opertor)。
MySQL允许给出多个WHERE子句。这些子句可以俩种方式使用：以AND子句的方式或OR子句的方式使用。

AND操作符
SELECT column_name1,column_name2 FROM table_name WHERE [condition1] AND [condition2];
AND用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。

OR操作符
SELECT column_name1,column_name2 FROM table_name WHERE [condition1] OR [condition2];
OR指示MySQL检索匹配任一条件的行。

计算次序
SELECT column_name1,column_name2 FROM table_name WHERE ([condition1] OR [condition2]) AND [condition3];

• WHERE可包含任一数目的AND和OR操作符。允许俩者结合以进行复杂和高级的过滤;
• SQL在处理OR操作符前，优先处理AND操作符，即操作符的优先级;
• 圆括号具有较AND或OR操作符的计算次序，DBMS首先过滤圆括号内的OR条件；
• 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符；

IN操作符
SELECT column_name FROM table_name WHERE column_name IN (num1,num2) ORDER BY column_name;

• IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配；
• IN用来制定要匹配值的清单，功能与OR相当；
• 使用长的合法选项清单时，IN操作符的语法更清楚更直观；
• 使用IN操作符，计算次序更容易管理；
• IN操作符一般比OR操作符清单执行更快；
• IN的最大优点就是可以包含其他的SELECT语句，使得能够更动态的建立WHERE子句；

NOT操作符
SELECT column_name FROM table_name WHERE column_name NOT IN (num1,num2) ORDER BY column_name;

• NOT操作符，否定其之后的所跟的任何条件；
• MySQL支持使用NOT对IN、BETWEEN、EXISTS子句取反，与多数其他DBMS允许使用NOT对各种条件取反有很大的差别；

---

7.用通配符进行过滤

通配符(wildcard)：用来匹配值的一部分的特殊字符；
搜索模式(search pattern)：由字面量、通配符或俩者组合构成的搜索条件；
在搜索语句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较；

LIKE操作符

从技术上说，LIKE是谓词而不是操作符。

百分号(%)通配符

在搜索串中，%表示任何字符出现任意次数[0,+∞]。
SELECT column_name FROM table_name WHERE column_name LIKE '%string%';

• 在MySQL的配置中可以设置搜索区分大小写;
• 通配符可以在搜索模式的任何位置使用，并且可以使用多个通配符;
• 通配符也可以出现在搜索模式的中间；
• 尾空格可能会干扰通配符匹配；
• %不能匹配NULL;

下划线(_)通配符

下划线只匹配单个字符而不是多个字符。
SELECT column_name FROM table_name WHERE column_name LIKE '_ string';

使用通配符的技巧

通配符的处理一般比前述搜索所花时间更长。

• 不要过度使用通配符，尽量少使用；
• 在确实需要使用通配符时，除非绝对有必要，否则不要将他们用在搜索模式的开始处；
• 仔细主要通配符的位置；

---

8.用正则表达式进行搜索

正则表达式是用来匹配文本的特殊的串(字符集合)。
MySQL用WHERE子句对正则表达式提供初步支持，允许你指定正则表达式，过滤SELECT检索出的数据。
MySQL仅支持多数正则表达式实现的一个很小的子集。

基本字符匹配
SELECT column_name FROM table_name WHERE column_name REGEXP 'string' ORDER BY column_name;

LIKE和REGEXP的区别：
LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不会被返回(除非是有通配符)。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。

匹配不区分大小写：
MySQL中的正则表达式匹配不区分大小写。为区分大小写，可使用BINARY关键字，如WHERE column_name REGEXP BINARY 'string'

正则表达式：

|					//正则表达式的`OR`操作符，表示匹配其中之一。
[123]				//匹配任何单一字符
[^123]				//字符集合的否定，匹配除这些字符外的任何东西
[1-6]				//匹配范围
\\					//匹配特殊字符，必须用\\为前导(即转义)。`\\-`表示查找`-`，`\\.`表示查找`.`

字符类：预定义的字符集

重读元字符

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

定位符

^					//文本的开始
&					//文本的结尾
[[:<:]]				//词的开始
[[:>:]]				//词的结尾

MySQL中正则表达式的转义

\\f					//换页
\\n					//换行
\\r					//回车
\\t					//制表
\\v					//纵向制表
\\\					//匹配反斜杠

多数正则表达式使用一个反斜杠实现转义，但MySQL要求俩个，MySQL自己解释一个，正则表达式库解释另一个。

---

9.创建计算字段

存储在表中的数据不是应用程序需要的。需要直接从数据库中检索出转换、计算或格式化的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。
可在SQl语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但在数据库服务器上完成这些操作比在客户机上完成要快的多，因为DBMS就是设计来快速高效完成这种处理的。

拼接字段

拼接(concatenate)：将值联结到一起构成单个值。
SELECT Concat(column_name1,column_name2) FROM table_name ORDER BY column_name;

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

Trim函数

SELECT Concat(RTrim(column_name1),':',LTrim(column_name2)) FROM table_name ORDER BY column_name;

RTrim()			//去掉串右边的所有空格
LTrim()			//去掉串左边的所有空格
Trim()			//去掉串左右俩边的空格

别名

别名(alias)是一个字段或值的替换名。别名用AS关键字赋予。
SELECT Concat(column_name1,column_name2) AS alias_name FROM table_name ORDER BY column_name;

• 别名还可以在实际的表列名包含不符合规定的字符时重新命名它，在原来的名字含混或容易误解时扩充它；
• 别名有时也称为导出列(derived column)；

执行算术计算

SELECT column_name1*column_name2 AS alias_name FROM table_name WHERE [condition];
MySQL算术操作符：+、-、*、/

虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单的访问和处理表达式。

---

10.使用数据处理函数

因为函数没有SQL的可移植性强，所以在使用函数时，应该保证做好代码注释，以便以后能确切地知道所编写SQL代码的含义。

能运行在多个系统上的代码成为可移植性(portable)。

文本处理函数

Left()				//返回串左边的字符
Length()			//返回串的长度
Locate()			//找出串的一个子串
Lower()				//将串转换为小写
LTrim()				//去掉串左边的空格
RTrim()				//去掉串右边的空格
Soundex()			//返回串的SOUNDEX值
SubString()			//返回子串的字符
Upper()				//将串转换为大写

SOUNDEX是一个将任何文子串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似发音字符和音节，使得能对串进行发音比较而不是字母比较。

日期和时间处理函数

MySQL中，无论什么时候指定一个日期，日期必须为格式yyyy-mm-dd

BWTWEEN '2019-10-01' AND '2019-10-15';
BETWEEEN操作符用来把2019-10-01和2019-10-15定义为一个要匹配的日期范围。

数值处理函数

---

11.汇总数据

聚集函数，运行在组上，计算和返回单个值的函数。可用于检索数据，以便于分析和报表生成。

• 确定表中行数；
• 获得表中行组的和；
• 找出表列的最大值、最小值和平均值；

AVG()			//返回某列的平均值，忽略列值为NULL的行
COUNT()			//返回某列的行数
MAX()			//返回某列的最大值，忽略列值为NULL的行，用于文本数据时返回最后一行
MIN()			//返回某列的最小值，忽略列值为NULL的行，用于文本数据时范湖最前面的行
SUM()			//返回某列值只和，忽略列值为NULL的行

COUNT()函数有俩种使用方式：

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值NULL还是非空值；
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

以上五个聚集函数都可以如下使用：

• 对所有的列执行计算，指定ALL参数或不给参数(因为ALL是默认行为);
• 只包含不同的值，指定DISTINCT参数；

可以对聚集函数的结果取别名，在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。

---

12.分组数据

SELECT column_name,COUNT(*) AS alias_name FROM table_name GROUP BY column_name WITH ROLLUP;

• GROUP BY子句可以包含任意数目的列；
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组进行汇总；
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)；
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出；
• 如果分组列中具有NULL值，则NULL将作为一个分组返回；
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前；
• 使用WITH ROLLUP关键字可以得到每个分组记忆每个分组汇总级别的值；

过滤分组

WHERE过滤行，而HAVING过滤分组。
HAVING支持所有的WHERE操作符。
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中，这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

分组和排序

OREDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用(甚至非选择的列也可以使用)	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列(或表达式)，则必须使用

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。不要仅仅因爱GROUP BY排序。

SELECT子句顺序

---

13.使用子查询

SQL允许建立子查询(subquery)，即嵌套在其他查询中的查询。
在SELECT语句中，子查询总是从内向外处理。
对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。
虽然子查询一般与IN操作符结合使用，但也可以用测试等于=、不等于<>等。
相关子查询(correlated subquery)：涉及外部查询的子查询。涉及外部查询的子查询，尽量使用完全限定的语法。
子查询最常见的使用时在WHERE子句的IN操作符中，以及用来填充计算列。

用子查询建立(和测试)查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。
首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后菜嵌入子查询。这时再次测试它，对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后的大量时间，并且极大的提高了查询一开始就正常工作的可能性。

---

14.联结表

关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值(即关系设计中的关系[relational])互相关联。

外键：外键为表中的一列，它包含另一个表的主键值，定义俩个表之间的关系。

可伸缩性(scale)：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)。

分解数据为多个表能够更有效的存储，更方便的处理。

• 联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结；
• 联结不是物理实体，联结由MySQL根据需要建立，它存在于查询的执行当中；
• 通过在表的定义中制定主键和外键可以实现维护引用完整性；

SELECT column_name1,column_name2 FROM table_name1,table_name2 WHERE table_name1.column_name1 = table_name2.column_name2 ORDER BY column_name1,column_name2;

• 在引用的列可能出现二义性时，必须使用完全限定列名(用一个点分隔的表名和列名)；
• 笛卡尔积(cartesian product)，由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
• 因此应该保证所有联结都有WHERE子句，且保证其正确性；

内部联结

等值联结，基于俩个表之间的相等测试，成为内部联结。
SELECT column_name1,column_name2 ,column_name3 FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name1 = table_name2.column_name2;

• SQL对一条SELECT语句中可以联结的表的数目没有限制；
• 联结表比较耗费资源，因此不要联结不必要的表。联结的表越多，性能下降越厉害；

---

15.创建高级联结

别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做可以：

• 缩短SQL语句；
• 允许在单条SELECT语句中多次使用相同的表；
• 表别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。
• 表别名只在查询执行中使用，并不返回到客户机；

SELECT column_name1,column_name2 
FROM table_name1 AS alias_name1,table_name2 AS alias_name2,table_name3 AS column_name3
WHERE alias_name1.column_name1 = alias_name2.column_name1
	AND alias_name2.column_name2 = alias_name3.column_name2
	AND column_name3 = 'string';

自联结

对自己本身的表查询结果作为查询条件进行查询；

SELECT alias_name1.column_name1,alias_name1.column_name2
FROM table_name AS alias_name1,table_name AS alias_name2
WHERE alias_name1.column_name = alias_name2.column_name
	AND alias_name2.column_name = 'tring';

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终结果是相同的，但有时候处理联结远比处理子查询快得多。

自然联结

参考：mysql中的自然联结、内联结、外联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结(内部联结)返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。
一般是通过对表使用通配符SELECT *，对其他表的列使用明确的子集来完成的。

//实例
SELECT c.*,order_num,o.order_date,
	   oi.prod_id,oi.quantity,oi.item_price
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'string';

外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。
联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

//实例
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

• 内部联结关联俩个表中的行，而外部联结还包括没有关联行的行；
• 在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表；
• RIGHT指出的是OUTER JOIN右边的表，LEFT指出的是OUTER JOIN左边的表；
• 外部联结存在俩种基本的外部联结形式：左外部联结和右外部联结。它们之间唯一的差别就是所关联的表的顺序不同；

使用带聚集函数的联结

SELECT customers.cust_name,
	   customers.cust_id,
	   COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

使用联结和联结条件

• 注意使用的联结类型；
• 保证使用正确的联结条件；
• 应该总是提供联结条件，否则会得出笛卡尔积；
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。输入这样做合法，也有用，但应该在测试前，分别测试每个联结。这样使得故障排除更为简单。