编辑
2021-04-02
后端开发
00

目录

《MySQL必知必会》读书笔记,摘录。

第一章 了解 SQL

本章主要是介绍了一些数据库相关的概念:

数据库:保存有组织的数据的容器。

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

模式 (schema):关于数据库和表的布局及特性的信息。在 MYSQL 中,模式与数据库同义。

主键:唯一标识表中每行的这个列称为主键。一个列成为主键必须满足以下条件:

  1. 唯一性,任意两行都不具有相同的主键值。
  2. 不为空,每行数据必须具有一个主键值。

第二章 MySQL 简介

DBMS(数据库管理软件)通常分为两类:

  1. 基于共享文件系统的 DBMS。通常用于桌面(例如 Microsoft Access 和 FileMaker)

  2. Client-Sever 的 DBMS。日常见到的 MySQL,Oracle,SQL Server 数据库都是这种类型的。Client 主要负责与用户进行交互,接受用户的指令,然后发出请求给 Server,Server 负责数据访问和处理,然后将结果返回给 Client。

第三章 使用 MySQL

本章学习如何连接和登录到 MySQL,如何执行 MySQL 语句,以及如何获得数据库和表的信息。

连接到 Mysql 需要以下信息:

  • 主机名(本地为 localhost)
  • 端口(默认 3306)
  • 用户名
  • 密码

连接成功之后,使用 USE 关键字选择要使用的数据库:

sql
USE crashcourse; // 选择数据库(不返回结果)

使用 SHOW 命令显示数据库、表、列的信息:

sql
show DATABASES; // 展示当前可用的数据库列表 show Tables; // 展示当前选择的数据库下的所有表 show COLUMNS FROM customers; // 展示 customers 表所有的列信息(会包含字段名,类型,是否允许为 NULL,键信息,默认值,其他信息),

在 MySQL 中,DESCRIBE customers;SHOW COLUMNS FROM customers;的一种快捷方式。

所支持的其他 SHOW 语句还有:

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

HELP SHOW;当你不了解某个命令时,可以使用 HELP+ 这个命令,来获得一些说明信息,了解这个命令的用途,这里 HELP SHOW 会打印出 SHOW 命令的用法。

第四章 检索数据

本章介绍如何使用 SELECT 语句从表中检索一个或多个数据列。

SELECT 是最常用的 SQL 语句。使用 SELECT 检索表数据,必须至少给出两条信息:1. 想选择什么,2. 从什么地方选择。

sql
SELECT prod_name FROM products;

如果是不设置任何排序条件,返回的数据的顺序是根据它们在底层表中出现的顺序(可以是数据最初添加到表中的顺序,但是如果数据进行过更新或删除,顺序会受到 MySQL 重用回收存储空间的影响)

SQL 语句不区分大小写,因此 SELECT 与 select 是相同的。

sql
SELECT prod_id, prod_name FROM products; // 检索多列 SELECT * FROM products; // 检索所有列

使用 DISTINCT 去重:

sql
SELECT DISTINCT vend_id FROM products;

如果想要对让返回的数据不包含重复值,可以使用 DISTINCT 来对列进行修饰

sql
SELECT DISTINCT vend_id FROM products;

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

使用 LIMIT 来限制结果数量

sql
SELECT prod_name FROM products LIMIT 5// 限制返回的数据为 5SELECT prod_name FROM products LIMIT 4,5// 限制返回的数据是从第 4 行开始后面的 5SELECT prod_name FROM products LIMIT 4 offset 5// 同上

使用完全限定的表名:

sql
SELECT products.prod_name FROM crash_course.products;

可以限制在某个数据库的某个表中进行查询,上面的例子是限制了,必须在 crash_course 数据库的 products 表取 prod_name 列的数据。

第五章 排序检索数据

这一章主要讲的是 ORDER BY 对查询结果进行排序,以及使用 ASC,DESC 控制升序,降序。

检索出的数据并不是以纯粹的随机顺序显示的。如果不排序,数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。

子句(clause):SQL 语句由子句构成,有些子句是必需的,而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有 SELECT 语句的 FROM 子句,我们在前一章看到过这个子句。

ORDER BY也是子句的一种,可以使用 ORDER BY 进行排序

sql
SELECT prod_name FROM products ORDER BY product_name;

按多个列进行排序

只要指定列名,列名之间用逗号分开即可(就像选择多个列时所做的那样)

sql
SELECT prod_id, prod_price, prod_name FROM products ORDER BY product_price, product_name;

在 ORDER BY 指定多个字段,可以按规定的顺序,按多个列排序,例子中的数据会先根据 product_price 从低到高进行排序,如果 product_price 相同,再按 product_name ,如果 prod_price 列中所有的值都是唯一的,则不会按 prod_name 排序。

指定排序方向

sql
SELECT prod_name FROM products ORDER BY product_name DESC;

默认的排序方向是升序,也就是 ASC,有时候需要进行降序排序,例如价格从高到低进行排序,可以使用降序 DESC.

使用 ORDER BY 和 LIMIT 的组合,能够找出一个列中最高或最低的值。下面的例子演示如何找出最昂贵物品的值:

sql
SELECT prod_price FROM products ORDER BY product_price DESC LIMIT 1;

ORDER BY 子句的位置:在给出 ORDER BY 子句时,应该保证它位于 FROM 子句之后。如果使用 LIMIT,它必须位于 ORDER BY 之后。

第六章 过滤数据

本章讲授如何使用 SELECT 语句的 WHERE 子句指定搜索条件。

数据库表一般包含大量的数据,很少需要检索表中所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件(searchcriteria),搜索条件也称为过滤条件(filter condition)。

sql
SELECT prod_price FROM products WHERE prod_price = 2.50;

条件判断符

一些常见的 WHERE 语句的条件判断符:

= 等于 != 不等于 < 小于 > 大于 <= 小于等于 >= 大于等于 BETWEEN 在指定两个值之间 除了上面这些,有一个不太常见的操作符号 <>, 代表不等于,与!= 同义

范围值检查

使用 BETWEEN 操作符会匹配范围中所有的值,包括指定的开始值和结束值

例如:

sql
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
prod_nameprod_price
.5 ton anvil5.99
1 ton anvil9.99
bird seed10.00
oil can8.99

!= 与 IS NULL

!= 是返回不具备特定值的行,NULL 值代表未知,所以不会拿 NULL 值去跟特定值比较,所以不会具有 NULL 值的行。如果想要获取具有 NULL 值的行,必须使用IS NULL。例如:

对下面这个表执行 SELECT * FROM table WHERE value != 100;

idvalue
1100
2NULL
3200

返回的结果:只会返回 value 为 200 的这一行,不会返回值为 NULL 的行。

idvalue
3200

SELECT 语句有一个特殊的 WHERE 子句,可用来检查具有 NULL 值的列:

sql
SELECT prod_name FROM products WHERE prod_price IS NULL;

第七章 数据过滤

本章讲授如何组合 WHERE 子句以建立功能更强的更高级的搜索条件,以及 AND,OR, IN,NOT 这四个操作符,

为了进行更强的过滤控制,MySQL 允许给出多个 WHERE 子句。这些子句可以两种方式使用:以 AND 子句的方式或 OR 子句的方式使用。

AND:满足所有给定条件; OR:满足任意给定条件;

sql
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 AND prod_price >= 10;

计算次序

sql
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

组合 AND 和 OR 使用时,因为 AND 优先级最高,计算时会优先处理 AND 操作符,会将 AND 两边的条件进行提取,所以上面这个 SQL 语句其实会等价于:

sql
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR (vend_id = 1003 AND prod_price >= 10);

可能会与我们想要的结果会有一定差距,我们是想要 vend_id 为 1002 或 1002,且 prod_price 大于 10 的数据,所以在日常使用中,最好使用()明确地分组相应的操作符,而不是依赖操作符的优先级,像下面这样:

sql
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

IN 操作符

IN 操作符用来指定条件范围,范围中的每个条件都可以进行匹配。

sql
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003); // 检索供应商 10021003 制造的所有产品 等价于: SELECT prod_name, prod_price FROM products WHERE vend_id=1002 OR vend_id=1003;

IN 操作符的优点具体如下:

  • 在使用长的合法选项清单时,IN 操作符的语法更清楚且更直观。
  • 在使用 IN 时,计算的次序更容易管理(因为使用的操作符更少)。
  • IN 操作符一般比 OR 操作符清单执行更快。
  • 可以包含其他 SELECT 语句,使得能够更动态地建立 WHERE 子句。(第 14 章将对此进行详细介绍)

NOT 操作符

NOT 操作符有且只有一个功能,那就是否定它之后所跟的任何条件。

其他 DBMS 允许使用 NOT 对各种条件取反,但在 MySQL 中,只支持使用 NOT 对 IN、BETWEEN 和 EXISTS 子句取反。

sql
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003); // 检索除了供应商 10021003 以外制造的所有产品

第八章 用通配符进行过滤

这一章主要是介绍了LIKE操作符,以及%_这两个通配符。

LIKE 操作符

前面介绍的所有操作符都是针对已知值进行过滤的。对于未知值的过滤需要使用通配符。

LIKE 主要是配合通配符一起使用的,LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

% 通配符

% 表示任何字符出现任意次数。

sql
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE '% ton anvil';

结果:

prod_idprod_name
ANV02.5 ton anvil
ANV021 ton anvil
ANV022 ton anvil

在一个查询语句中也可以使用多个%通配符

sql
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE '%anvil%'; // 搜索模式 %anvil% 表示匹配任何位置包含文本 anvil 的值,而不论它之前或之后出现什么字符

下划线通配符

另一个有用的通配符是下划线_。下划线的用途与%一样,但下划线只匹配单个字符而不是多个字符。

sql
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE '_ ton anvil';

结果:

prod_idprod_name
ANV021 ton anvil
ANV022 ton anvil

尾空格

可能会干扰通配符匹配。例如,在保存词 anvil 时,如果它后面有一个或多个空格,则子句WHERE prod_name LIKE '%anvil'将不会匹配它们,因为在最后的 l 后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数(第 11 章将会 介绍)去掉首尾空格。

注意 NULL:虽然似乎%通配符可以匹配任何东西,但有一个例 外,即 NULL。即使是 WHERE prod_name LIKE '%' 也不能匹配 用值 NULL 作为产品名的行。 下划线_通配符 _通配符与%通配符类似,只不过只能匹配单个字符,不能匹配 0 个字符,也不能匹配多个字符

使用通配符的技巧

  1. 在能使用其他操作符的情况下,尽量不要使用通配符,因为它的搜索时间要比其他操作符的长

  2. 尽量不要把通配符用在搜索模式的开始处。放在搜索模式的开始处,搜索起来是最慢的。

  3. 仔细检查通配符的位置。如果放错地方,可能不会返回想要的数据。

第九章 用正则表达式进行搜索

这一章主要讲正则相关的知识,建议专门学习。

第十章 创建计算字段

某些场景下,存储在的表中的数据不是我们所需要的,我们需要对它进行转换、计算或格式化,这就是计算字段的用途。

使用 Concat() 函数对字段进行拼接

多数 DBMS 使用 + 或 || 来实现拼接, MySQL 则使用 Concat() 函数来对字段进行拼接。Concat() 可以将多个字符串拼接成一个,如下所示:

sql
SELECT Concat(vend_name, (', vend_country, ')) FROM products ORDER BY vend_name;

使用 Trim() 函数来去除空格

Trim() 函数 :去除字符串左右两边的空格 LTrim() 函数 :去除字符串左边的空格 RTrim() 函数 :去除字符串右边的空格 下面是使用 RTrim() 函数的例子

sql
SELECT Concat(RTrim(vend_name), (', RTrim(vend_country), ')) FROM products ORDER BY vend_name;

使用别名

我们使用 Concat() 函数拼接出来的字段是没有名字的,可以使用 AS 关键字给它赋予一个名字,当然当已有的字段包含不符合规定的字符时,也可以 AS 关键字给一个已有字段起别名。

sql
SELECT Concat(RTrim(vend_name), (', RTrim(vend_country), ')) AS bend_title FROM products ORDER BY vend_name;

执行算术计算

除了使用 Concat() 函数得到一个计算字符,也可以使用+,-,_,/计算得到一个字段。如下所示:

sql
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitmes WHERE order = 20005;
prod_idquantityitem_priceexpanded_price
ANV01105.9959.90
ANV0239.9929.97
TNT2510.0050.00
FB110.0010.00

_expanded_price列为一个计算字段,是由quantity_item_price计算得到的。

第十一章 使用数据处理函数

本章介绍什么是函数,MySQL 支持何种函数,以及如何使用这些函数。

能运行在多个系统上的代码称为可移植的(portable)。相对来说,多数 SQL 语句是可移植的,在 SQL 实现之间有差异时,这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的 DBMS 的实现都支持其他实现不支持的函数,而且有时差异还很大。

如果你决定使用函数,应该保证做好代码注释,以便以后你(或其他人)能确切地知道所编写 SQL 代码的含义。

文本处理函数

函数名称说明
Left()返回串左边的字符
Right()返回串右边的字符
Length(str)返回字符串长度
Locate(substr,str)返回子串 substr 在字符串 str 中第一次出现的位置
LTrim()去掉串首的空格
RTrim()去掉串尾的空格
Upper()将文本转换为大写并返回
Lower()将文本转换为小写并返回
Position(substr IN str)返回 substr 在 str 中第一次出现的位置
SubString(str,pos)返回从第 pos 位置出现的子串的字符
Soundex()返回字符串的 SOUNDEX 值

上面这些常见函数大家可能都能够理解,只有Soundex()不太常见,SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

sql
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cus_contact) = Soundex('Y Lie');
cust_namecust_contact
CoyoteY Lee

如上图所示,假设有一个顾客的cust_contact值为Y.Lee,但是我们不知道Y.Lee,只知道这个顾客的名字的发音近似于Y.Lie,这个时候我们可以使用Soundex()cust_contact列值转换为它的SOUNDEX值,因为Y.LeeY.Lie发音相似,所以它们的SOUNDEX值匹配,因此可以查询到这个顾客。

常用日期和时间处理函数

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

举例,使用 Date 函数提取日期部分:

sql
-- 查询九月一日当天的所有订单 SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
sql
-- 查询九月的所有订单 SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-9-30'; -- 或者 SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理函数

函 数说 明
Abs()返回一个数的绝对值
Cos()返回一个角度的余弦
Exp()返回一个数的指数值
Mod()返回除操作的余数
Pi()返回圆周率
Rand()返回一个随机数
Sin()返回一个角度的正弦
Sqrt()返回一个数的平方根
Tan()返回一个角度的正切

第十二章 汇总数据

本章介绍什么是 SQL 的聚集函数以及如何利用它们汇总表的数据。

聚集函数

我们经常需要汇总数据而不用把它们实际检索出来,为此 MySQL 提供了专门的函数。

函 数说 明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返向某列的坡大值
MIN()返回某列的圾小值
SUM()返回某列值之和

AVG() 函数

AVG() 是计算特定列的平均值,会忽略掉值为 NULL 的列。

sql
SELECT AVG(prod_price) AS avg_price FROM products;
avg_price
16.1333

AVG()函数也可以搭配 DISTINCT 关键字使用,将重复的数据去重后,然后计算平均值,如下所示:

sql
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
avg_price
15.998

在使用了DISTINCT后,此例子中的avg_price比较高,因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

COUNT() 函数

两种用法:

  1. 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值 (NULL) 还是非空值。
  2. 使用COUNT(column)对特定列中具有值的行进行计数,会忽略 NULL 值。
sql
-- 返回 customers 表中客户的总数 SELECT COUNT(*) AS num_cust FROM customers;
num_cust
5

NULL 值:如果指定列名,则指定列的值为空的行被 COUNT() 函数忽略,但如果COUNT()函数中用的是星号*,则不忽略。

组合使用聚集函数

sql
SELECT COUNT(*) AS num_items MIN(prod_price) AS price_min MAX(prod_price) AS price_max AVG(prod_price) AS price_avg FROM products;
num_itemsprice_minprice_maxprice_avg
142.5055.0016.13

第十三章 分组数据

这一章主要讲了如果使用 GROUP BY 子句和 HAVING 子句对数据进行分组。

创建分组

sql
-- 返回 customers 表中客户的总数 SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
vend_idnum_prods
10013
10022
10037
10042

在具体使用 GROUP BY 子句前,需要知道一些重要的规定。

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

过滤分组

如果要对分组进行过滤,我们可以使用 WHERE 语句对表中数据进行过滤后,然后使用 GROUP BY 进行分组,也可以在使用 GROUP BY 进行分组后,再使用 HAVING 语句过滤掉一些分组(WHERE 过滤指定的是行而不是分组)。 例如:

idvalue
1100
2150
3200
3500

对于上面这个表的数据,如果我们想要过滤掉 id 为 3 的分组,那么可以写成使用 WHERE 语句的方式:

sql
SELECT id,COUNT(*) FROM table WHERE id != 3 GROUP BY id;

也可以写成使用 HAVING 语句的方式:

sql
SELECT id,COUNT(*) FROM table GROUP BY id HAVING id !=3;

当然在过滤分组这方面,HAVING 要比 WHERE 更加强大,比如我们想要对数据分组,并且得到数量大于 2 的组,那么 WHERE 就无法实现,只能用 HAVING 语句。如下:

sql
SELECT id,COUNT(*) AS orders FROM orders GROUP BY id HAVING COUNT(*) >= 2;

HAVING 支持所有 WHERE 操作符。

分组和排序

使用 GROUP BY 在对数据进行分组后,输出的组的顺序通常是按从小到大,从 A 到 Z 升序输出的,但是 SQL 规范并没有对此进行明确要求,所以有可能不是顺序的,可以使用 ORDER BY 来对分组进行升序或者降序排序。

SELECT 子句顺序

在使用这些语句时,它们的先后顺序应该要按下面的表中顺序来写:

子 句说 明是否必须使用
SELECT要返回的列或表达式
FROM从中检索数据的表仅在从农选择数据时使用
WHERE行级过滤
GROUP BY分组说明仅在按组计算聚集时使用
HAVING组级过滤
ORDER BY输出排序顺序
LIMIT要检索的行数

第十四章 使用子查询

SQL 还允许创建子查询(subquery),即嵌套在其他查询中的查询。

子查询作为 WHERE 子句的条件

有时候一条 SELECT 语句无法满足我们的需求,我们可以把一条 SELECT 语句的结果用于另外一条 SELECT 语句的 WHERE 子句,来实现复杂查询。

例如:我们想要获取订购物品 TNT2 的所有客户的名字和联系方式: 可以按照下面的复杂查询实现:

  1. 查询包含物品 TNT2 的所有订单的编号。
  2. 根据订单编号查询所有客户的 ID。
  3. 根据客户的 ID 查询名字和联系方式。
sql
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN ( SELECT cust_id FROM orders WHERE order_num IN( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ));
cust_namecust_contact
CoyoteY Lee
YostmiteY Sam

在 WHERE 子句中使用子查询能够编写出功能很强并且很灵活的 SQL 语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。

需要的注意的地方:

  1. 能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
  2. 列必须匹配,在 WHERE 子句中使用子查询,应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列。通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列。
  3. 子查询一般与 IN 操作符结合使用,但也可以用于测试等于=、 不等于<>等。

子查询结果作为计算字段

例如:我们想要在获取顾客的信息的同时,获取客户的订单数,可以使用子查询来实现,如下图所示:

sql
SELECT cust_name, cust_state, (SELECT COUNT(*) From orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
cust_namecust_stateorders
CoyoteMI2
FuddIL1
MouseOH0
WscalsIN1

第十五章 连接表

TODO

本文作者:青波

本文链接:

版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!