MySQL必知必会读书笔记

最新推荐文章于 2024-03-18 16:58:50 发布

CurryCoder

最新推荐文章于 2024-03-18 16:58:50 发布

阅读量366

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/cdlwhm1217096231/article/details/99300984

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

第一章了解SQL

1.1 数据库基础

数据库：保存有组织的数据的容器(通常是一个文件或一组文件)。注意要与数据库软件DBMS（数据库管理系统）区别，数据库是通过DBMS创建和操纵的容器。数据库可以是保存在硬件设备上的文件，但也可以不是。很大程度上来说，数据库究竟是文件还是别的什么并不重要，因为你并不直接访问数据库，你使用的是DBMS，它替你访问数据库。
表：某种特定类型数据的结构化清单。关键点：存储在表中的数据是一种类型的数据或是一个清单。数据库中的每个表都有一个名字，用来标识自己。此名字是唯一的，标识数据库中没有其他表具有相同的名字。虽然相同的数据库中不能两次使用相同的表名，但是在不同的数据库中可以使用相同的表名。
模式：关于数据库和表的布局和特性的信息。
列：表由列组成的。列中存储着表中某部分的信息。列即表中的一个字段，所有表都是由一个或多个列组成的。数据库中每列都有相应的数据类型。数据类型定义列可以存储的数据种类。
数据类型：所容许的数据的类型。每个表的列都有相应的数据类型，它限制该列中存储的数据。数据类型限制可存储在列中的数据种类。
行：表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表看出一个网格，网格中的垂直的列是表的列，水平行位表行。行（也称为记录）即表中的一个记录。
主键/键码/码：一列（或者一组列），其值能够唯一区分表中每个行。唯一标识表中每行的这个列（或这组列）称为主键。主键用来表示一个特定的行，没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。应该总是定义主键，虽然并不总是都需要主键，但大多数情况下应该保证创建的每个表具有一个主键，便于后序的操作。表中任何列都可以作为主键，只有满足下面的条件：
- 任意两行都不具有相同的主键值；
- 每个行都必须具有一个主键值（主键值不允许为NULL值）；
注意：主键通常定义在表的一列上，但这不是必须的。也可以一起使用多个列作为主键。在使用多个列作为主键时，上面的条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的（但是单个列的值可以不唯一）。
主键使用的最好习惯：
- 不更新主键列中的值；
- 不重用主键列中的值；
- 不在主键列中使用可能会更改的值；

1.2 什么是SQL

SQL是结构化查询语言，是一种专门用来与数据库通信的语言。
SQL语言的优点：
- SQL不是某个特定数据库供应商专有的语言，几乎所有重要的DBMS都支持SQL
- SQL简单易学
- SQL尽管看上去简单，但是实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

第二章 MySQL简介

2.1 什么是MySQL

数据的所有存储、检索、管理和处理实际上是由数据库软件—DBMS（数据库管理系统）完成的。MySQL是一种DBMS，即它是一种数据库软件。
DBMS的分类：
- a.基于共享文件系统的DBMS；用于桌面用途，通常不用于高端或更关键的应用。
- b.基于客户机-服务器的DBMS；MySQL、Oracle、SQL Server等数据库是基于客户机-服务器的数据库，可以分为两个不同的部分。服务器部分负责所有数据的访问和处理的一个软件，这个软件运行在称为数据库服务器的计算机上。与数据文件打交道的只有服务器软件。关于数据、数据添加、删除、更新的所有请求都是由服务器软件完成的。这些请求或更改都是来自客户机软件的计算机。客户机是与用户大打交道的软件。
客户机和服务器软件可能安装在两台计算机或一台计算机上。不管它们是否在相同的计算机上，为了进行所有数据库交互，客户机软件都要与服务器软件进行通信。
命令是用;或\g结束的，就是说仅按Enter不执行命令；输入help或\h获得帮助；\q是退出

第三章使用MySQL

1. 连接

为了连接到MySQL，需要下面的信息：
- 主机名（计算机名），如果连接到本地MySQL服务器，为localhost；
- 端口，如果使用默认端口3306之外的端口；
- 一个合法的用户名；
- 用户口令（如果需要的话）；

2.选择数据库

在你能执行任意数据库操作前，需要选择一个数据库。可以使用use关键字。use语句并不返回任何结果，依赖于使用的客户机，显示某种形式的通知。例如，显示的Database changed消息是mysql命令行实用程序在数据库选择成功后显示的。必须先使用use打开数据库，才能读取其中的数据。
```
mysql> use dalao;
Database changed
```
关键字：作为MySQL语言组成部分的一个保留字，绝不要用关键字命名一个表或列！！！

数据库、表、列、用户、权限等的信息被存储在数据库和表中。不过，内部的表一般不直接访问，可以使用show命令来显示这些信息。show databases;返回可用数据库中的一个列表。包含在这个列表中的可能是MySQL内部使用的数据库（如，information_schema）。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dalao              |
| demo_db            |
| django             |
| django_database    |
| mysite             |
| mysql              |
| performance_schema |
| polls              |
| runoob             |
| spiders            |
| students           |
| sys                |
+--------------------+
13 rows in set (0.00 sec)

为了获得一个数据库中的表的列表，使用show tables;此命令返回当前选择的数据库内可用表的列表，show也可以显示表的列，等价于desc 表名是一种show columns from 表名的一种快捷方式！。如下所示：

mysql> show tables;
+-----------------+
| Tables_in_dalao |
+-----------------+
| caiji           |
| njust           |
| tlu             |
+-----------------+
3 rows in set (0.00 sec)

mysql> show columns from caiji;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | char(10)    | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
| grade | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (1.66 sec)
分析：show columns 要求给出一个表的名字，它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值及其他信息（如 auto_increment）等。
mysql> desc caiji;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | char(10)    | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
| grade | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

auto_increment(自动增量)：某些表列需要唯一值。在每行添加到表中时，MySQL可以自动地为每个行分配下一个可用编号，不用在添加一行时收到分配唯一值（这样做就必须记住最后一次使用的值），这个功能就是所谓的自动增量。如果需要它就必须在用create语句创建表时把它作为表定义的组成部分。
所支持的其他show语句有：
- show status;用于显示广泛的服务器状态信息；
- show create database 数据库名;和 show create table 表名;分别用来显示创建特定数据库或表的MySQL语句；
- show grants;用来显示授权用户（所有用户或特定用户）的安全权限；
- show errors;和show warnings;用来显示服务器错误或警告消息；
忘记一条SQL指令用法时，使用help 指令名来进行帮助！！！！
MySQL5支持一个新的INFORMATION_SCHEMA命令，可以用它来获得和过滤模式信息。information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。用法：select * from INFORMATION_SCHEMA.TABLES;

第四章检索数据

1.select语句

最常用的SQL语句就是select语句，它的用途是从一个表或多个表中检索信息。为了使用select检索表数据，必须至少给出两条信息（想选择什么、从什么地方选择）。

2.检索单个列

```
mysql> select name from caij
+--------+
| name   |
+--------+
| Curry  |
| Durant |
| James  |
| Zion   |
+--------+
4 rows in set (0.11 sec)
```

上面语句中利用select语句从caiji表中检索一个名为name的列。所需的列名在select关键字之后给出，from关键字指出从其中检索数据的表名。SQL语句不区分大小写，因此SELECT和select是相同的。一般，所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试；将SQL语句分成多行更易于阅读和调试。

3.检索多个列

要想从一个表中检索出多个列，使用相同的SELECT语句，唯一的不同是必须在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。注意逗号一定要在列名之间加上，但最后一个列名后不加！！！

mysql>
mysql> SELECT id, name, age, grade
    -> FROM caiji;
+------+--------+------+-------+
| id   | name   | age  | grade |
+------+--------+------+-------+
|    1 | Curry  |   31 | 09年  |
|    2 | Durant |   30 | 08年  |
|    3 | James  |   34 | 03年  |
|    4 | Zion   |   19 | 新秀  |
+------+--------+------+-------+
4 rows in set (0.00 sec)

4.检索所有列

检索所有列而不必逐个列出它们，通过在实际列名的位置使用星号*通配符来达到，如下所示。如果给定一个通配符，增返回表中所有列，列的顺序一般是列在表定义中出现的顺序。但是，当表的模式（添加或删除列）可能会导致顺序的变化。一般不建议使用通配符，因为检索不需要的列通常会降低检索和应用程序的性能。使用通配符的优点是，
```
mysql> SELECT *
    -> FROM caiji;
+------+--------+------+-------+-------+
| id   | name   | age  | score | grade |
+------+--------+------+-------+-------+
|    1 | Curry  |   31 |    96 | 09年  |
|    2 | Durant |   30 |    97 | 08年  |
|    3 | James  |   34 |    99 | 03年  |
|    4 | Zion   |   19 |    81 | 新秀  |
+------+--------+------+-------+-------+
4 rows in set (0.00 sec)
```

5.检索不同的行

	mysql> SELECT name
    -> FROM caiji;
	+--------+
	| name   |
	+--------+
	| Curry  |
	| Durant |
	| James  |
	| Zion   |
	| Curry  |
	+--------+
	5 rows in set (0.00 sec)
	
	mysql> SELECT DISTINCT name
	    -> FROM caiji;
	+--------+
	| name   |
	+--------+
	| Curry  |
	| Durant |
	| James  |
	| Zion   |
	+--------+
	4 rows in set (0.06 sec)

使用distinct关键字，表示只返回不同的值。注意distinct关键字放的位置，它必须放在列名的前面！不能部分使用DISTINCT distinct关键字应用于所有列而不仅仅是前置它的列。如果给出SELECT DISTINCT name, grade;除非指定的两个列都不同，否则所有行都将被检索出来。

6.限制结果

SELECT语句返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句，如下所示：

mysql> SELECT name
    -> FROM caiji
    -> LIMIT 3;
+--------+
| name   |
+--------+
| Curry  |
| Durant |
| James  |
+--------+
3 rows in set (0.00 sec)

上面的语句使用SELECT语句检索单个列，LIMIT 3表示MySQL返回不多余3行。为了得到下一个4行，可指定要检索的开始行和行数，如下所示：

mysql> SELECT name
    -> FROM caiji
    -> LIMIT 1, 4;
+--------+
| name   |
+--------+
| Durant |
| James  |
| Zion   |
| Curry  |
+--------+
4 rows in set (0.00 sec)

上面的LIMIT 1, 4表示MySQL返回从行1开始的4行，第一个数是开始位置，第二个数是检索的行数。注意的地方：
- 检索出来的第一行是行0而不是行1.因此，LIMIT 1,1将检索出第二行而不是第一行；
- 在行数不够时，LIMIT中指定要检索的行数是检索的最大行数。如果没有足够的行，MySQL将只返回它能返回的那么多行。 LIMIT 3, 4 的另一种写法是LIMIT 4 OFFSET 3表示从行3开始取4行，如下所示：
```
mysql> SELECT name
-> FROM caiji
-> LIMIT 3 OFFSET 1;
+--------+
| name   |
+--------+
| Durant |
| James  |
| Zion   |
+--------+
3 rows in set (0.00 sec)
```

7.使用完全限定的表名

前面的例子中，只通过列名引用列。也可能会使用完全限定的名字来引用列（同时使用表名和列字），如下所示：

mysql> SELECT caiji.name
    -> FROM caiji;
+--------+
| name   |
+--------+
| Curry  |
| Durant |
| James  |
| Zion   |
| Curry  |
+--------+
5 rows in set (0.00 sec)

mysql> SELECT caiji.name
    -> FROM dalao.caiji;
+--------+
| name   |
+--------+
| Curry  |
| Durant |
| James  |
| Zion   |
| Curry  |
+--------+
5 rows in set (0.00 sec)

第五章排序检索数据

1.排序数据

下面的SQL语句返回某个数据库表的单个列，注意看到它的输出并没特定的顺序。

mysql> SELECT age
    -> FROM njust;
+------+
| age  |
+------+
|   12 |
|   15 |
|   14 |
|   13 |
|   12 |
+------+
5 rows in set (0.00 sec)

其实，检索出来的数据并不是以随机的顺序显示的。如果不使用排序语句，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序，但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重回收存储空间的影响。关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
子句：SQL语句由子句组成，有些子句是必须的，有些子句则是可选择的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句。为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个列或多个列的名字，据此对输出进行排序。实例如下：
```
mysql> SELECT age
    -> FROM njust
    -> ORDER BY age;
+------+
| age  |
+------+
|   12 |
|   12 |
|   13 |
|   14 |
|   15 |
+------+
5 rows in set (0.00 sec)
```

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

mysql> SELECT name, age
    -> FROM njust
    -> ORDER BY address;
+------+------+
| name | age  |
+------+------+
| 大雄 |   15 |
| 静香 |   14 |
| 小度 |   12 |
| 小明 |   12 |
| 胖虎 |   13 |
+------+------+
5 rows in set (0.06 sec)

2.按多个列排序

为了按多个列排序，只要指定列名，列名之间用逗号分开即可（就像选择多个列那样做就可以）。在按多个列排序时，排序完全按所规定的顺序进行。也就是说，下面的例子中，当多个行具有相同的姓名name时，才会按照年龄age排序。如果姓名name列中所有的值都是唯一的，则不会按年龄age来排序。
```
mysql> SELECT name, age, sex
    -> FROM njust
    -> ORDER BY name, age;    // 首先按姓名排序，然后再按年龄排序
+------+------+------+
| name | age  | sex  |
+------+------+------+
| 大雄 |   15 | 男   |
| 小度 |   12 | 男   |
| 小明 |   12 | 男   |
| 胖虎 |   13 | 男   |
| 静香 |   14 | 女   |
+------+------+------+
5 rows in set (0.00 sec)
```

3.指定排序方向

数据排序不限于升序排序（A到Z），这是默认的排序顺序。可以使用ORDER BY子句以降序（Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

mysql> SELECT name, age, sex
    -> FROM njust
    -> ORDER BY age DESC;
+------+------+------+
| name | age  | sex  |
+------+------+------+
| 大雄 |   15 | 男   |
| 静香 |   14 | 女   |
| 胖虎 |   13 | 男   |
| 小明 |   12 | 男   |
| 小度 |   12 | 男   |
+------+------+------+
5 rows in set (0.00 sec)

如果对多个列排序时，DESC关键字只应用在直接位于其前面的列名。例如下面的例子中，只对age列指定DESC，对name列不指定。因此age列以降序排序；而name列仍然按标准升序排序。如果想对多个列进行降序排序，必须对每个列指定DESC关键字。

mysql> SELECT name, age, sex
    -> FROM njust
    -> ORDER BY age DESC, name;
+------+------+------+
| name | age  | sex  |
+------+------+------+
| 大雄 |   15 | 男   |
| 静香 |   14 | 女   |
| 胖虎 |   13 | 男   |
| 小度 |   12 | 男   |
| 小明 |   12 | 男   |
+------+------+------+
5 rows in set (0.00 sec)

与DESC关键字相关的关键字是ASC，在升序排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的。

使用ORDER BY和LIMIT的组合，能够找出一个列中的最大值或最小值，如下面的例子：

mysql> SELECT age
    -> FROM njust
    -> ORDER BY age DESC
    -> LIMIT 1;
+------+
| age  |
+------+
|   15 |
+------+
1 row in set (0.06 sec)

注意：ORDER BY子句的位置应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。

第六章过滤数据

1.使用WHERE子句

数据库表中一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件，搜索条件也称为过滤条件。在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE 子句在表名（FROM子句）之后给出，如下所示：
```
mysql> SELECT name, age
    -> FROM njust
    -> WHERE age = 12;
+------+------+
| name | age  |
+------+------+
| 小明 |   12 |
| 小度 |   12 |
+------+------+
2 rows in set (0.09 sec)
```
上面的例子中，是从表njust中检索两列，但不返回所有行，只返回age=12的行。采用的是相等的测试条件，它检查一个列是否具有指定的值，根据此条件进行过滤。但SQL允许做的事情不仅仅是相等测试。
WHERE子句的位置：在同时使用ORDER BY 和 WHERE子句时，应该让ORDER BY位于WHERE子句之后，否则会产生错误！！！

2.WHERE子句操作符

常见WHERE子句操作符

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

2.1 检测单个值

检查WHERE name = "小明"语句，它返回name的值是“小明”的行。MySQL在执行匹配时默认不区分大小写，所以A与a相同的。

mysql> SELECT name, age
    -> FROM njust
    -> WHERE name = "小明"
+------+------+
| name | age  |
+------+------+
| 小明 |   12 |
+------+------+
1 row in set (0.05 sec)

下面的例子是列出年龄age大于12的所有同学：

mysql> SELECT name, age
    -> FROM njust
    -> WHERE age > 12;
+------+------+
| name | age  |
+------+------+
| 大雄 |   15 |
| 静香 |   14 |
| 胖虎 |   13 |
+------+------+
3 rows in set (0.00 sec)

2.2 不匹配检查

下面的例子是列出年龄age不是18岁的所有同学：

mysql> SELECT name, age
    -> FROM njust
    -> WHERE age <> 18;     // <>与！=等价的！
+------+------+
| name | age  |
+------+------+
| 小明 |   12 |
| 大雄 |   15 |
| 静香 |   14 |
| 胖虎 |   13 |
| 小度 |   12 |
+------+------+
5 rows in set (0.00 sec)

2.3范围值检查

为了检查某个范围的值，可以使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同，因为它需要有两个值，即开始值和结束值。在使用BETWEEN时，必须指定两个值（所需范围的低端值和高端值）。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。
```
mysql> SELECT name, age
    -> FROM njust
    -> WHERE age BETWEEN 12 AND 15;
+------+------+
| name | age  |
+------+------+
| 小明 |   12 |
| 大雄 |   15 |
| 静香 |   14 |
| 胖虎 |   13 |
| 小度 |   12 |
+------+------+
5 rows in set (0.05 sec)
```

4.空值检查

在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为包含空值NULL。NULL即无值，它与字段包含0、空字符串或仅含有空格不同！
SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列，这个WHERE子句就是IS NULL子句。
```
mysql> SELECT name, age
    -> FROM njust
    -> WHERE age IS NULL;
Empty set (0.00 sec)
```
由于表中没有这样的行，所以没有返回数据。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。

第七章数据过滤

1.组合WHERE子句

上面一章中，所有的WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以以两种方式使用：
- a.以AND子句的方式
- b.以OR子句的方式
操作符：用来连接或改变WHERE子句中的子句的关键字，也称为逻辑操作符。

1.1 AND操作符

为了通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。如下所示：

mysql> SELECT name, age
    -> FROM njust
    -> WHERE age = 12 AND id <= 3;
+------+------+
| name | age  |
+------+------+
| 小明 |   12 |
+------+------+
1 row in set (0.09 sec)

上面的SQL语句检索年龄age=12岁并且id小于等于3的所有同学的姓名和年龄。AND用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。上面的例子中，只使用了包含一个关键字AND的语句，把两个过滤条件组合在一起。还可以添加多个过滤条件，每添加一条就要使用一个AND。

1.2 OR操作符

OR操作符与AND操作符不同，它表示MySQL检索匹配任一条件的行。如下面的例子：

mysql> SELECT id, name, age
    -> FROM njust
    -> WHERE id = 1 OR id = 3;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 小明 |   12 |
|  3 | 静香 |   14 |
+----+------+------+
2 rows in set (0.00 sec)

1.3 计算次序

WHERE子句可以包含任意数量的AND和OR操作符。允许两者结合来进行复杂和高级的过滤。如下面的例子：

mysql> SELECT id, name, age
    -> FROM njust
    -> WHERE id = 2 or id = 3 AND age < 12;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | 大雄 |   15 |
+----+------+------+
1 row in set (0.00 sec)

返回的行没有按照预期的进行过滤。SQL在处理OR操作符前，优先处理AND操作符。当SQL看到上述WHERE子句时，它理解为年龄age小于12的同学或者id = 2的同学，而不管其年龄是多少。换句话说，由于AND在计算次序中优先级更高，操作符被错误组合了。解决方法是使用圆括号明确地分组相应的操作符，如下所示：
```
mysql> SELECT id, name, age
    -> FROM njust
    -> WHERE (id = 2 or id = 3) AND age < 12;
Empty set (0.00 sec)
```
WHERE子句中使用圆括号，在任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它的确是你想要的东西也是如此。

2.IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。如下面的例子所示：

mysql> SELECT id, name, age
    -> FROM njust
    -> WHERE id IN (1, 3)
    -> ORDER BY name;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 小明 |   12 |
|  3 | 静香 |   14 |
+----+------+------+
2 rows in set (0.00 sec)

IN操作符完成与OR相同的功能，IN操作符一般比OR操作符执行的更快。IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。看下面的例子：

mysql>  SELECT id, name, age
    -> FROM njust
    -> WHERE id = 1 OR id = 3
    -> ORDER BY name;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 小明 |   12 |
|  3 | 静香 |   14 |
+----+------+------+
2 rows in set (0.00 sec)

3.NOT操作符

WHERE子句中NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反。如下面的例子：

mysql> SELECT id, name, age
    -> FROM njust
    -> WHERE id NOT IN (1,3)
    -> ORDER BY name;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | 大雄 |   15 |
|  5 | 小度 |   12 |
|  4 | 胖虎 |   13 |
+----+------+------+
3 rows in set (0.00 sec)

第八章用通配符进行过滤

1.LIKE操作符

利用通配符可以创建比较特定数据的搜索模式。通配符是用来匹配值的一部分的特殊字符。搜索模式**由字面值、通配符两者组合构成的搜所条件 **。通配符本身实际是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL，后面跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

1.1 百分号%通配符

最常使用的通配符是%。在搜索串中，%表示任何字符出现任意次数。例如，为了找出所有以cu开头的姓名，可以使用如下SELECT语句：
```
	mysql> SELECT name
	    -> FROM caiji
	    -> WHERE name LIKE 'cu%';
	+-------+
	| name  |
	+-------+
	| Curry |
	| Curry |
	+-------+
	2 rows in set (0.06 sec)
```

通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它位于模式的两端：

mysql> SELECT name
    -> FROM caiji
    -> WHERE name LIKE '%ur%';
+--------+
| name   |
+--------+
| Curry  |
| Durant |
| Curry  |
+--------+
3 rows in set (0.00 sec)

通配符也可出现在搜索模式的中间，但是这样做不太有用。注意除了一个或多个字符外，%还可以匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。注意尾空格，尾部空格可能会干扰通配符的匹配。例如在保存词curry时，如果它后面有一个或多个空格，则子句WHERE name LIKE '%curry’将不会匹配它们，因为在最后的y后有多余的字符。解决的方法是在搜索模式后加一个%。
注意NULL，**虽然似乎%通配符可以匹配任何东西，但是也有例外，即NULL。**即使是WHERE name LIKE '%'也不能匹配用值为NULL的行。

1.2 下划线_通配符

另一个有用的通配符是下划线，下划线的用途和%一样。但下划线只匹配单个字符而不是多个字符。与%能匹配0个字符不同，_总能匹配一个字符，不能多也不能少。
```
mysql> SELECT name
    -> FROM caiji
    -> WHERE name LIKE 'curr_';
+-------+
| name  |
+-------+
| Curry |
| Curry |
+-------+
2 rows in set (0.00 sec)
```

2.使用通配符的技巧

MySQL通配符是很有用，但是也是有代价的。通配符搜索的处理一般要比前面讨论的其他搜索所花的时间更长。使用通配符的技巧如下：
- a.不要过多使用通配符。如果其他操作符能达到同样的效果，应该使用其他操作
- b.在确实需要通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来最慢。
- c.仔细注意通配符的位置，如果方错地方，可能不会返回想要的数据。

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

1.正则表达式介绍

正则表达式是用来匹配文本的特殊的串。如果想从一个文本文件中提取电话号码，可以使用正则表达式。如果你需要查找名字中间有数字的所有文件，可以使用一个正则表达式。如果你想在一个文本块中找到所有重复的单词，可以使用一个正则表达式。如果你想替换一个页面中的所有URL为这些URL的实际HTML链接，也可以使用一个正则表达式。

2.使用MySQL正则表达式

正则表达式的作用是匹配文本，将一个，模式与一个文本串进行比较。MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

2.1 基本字符的匹配

下面的语句是检索出列name包含文本Curry的所有行：

mysql> SELECT name
    -> FROM caiji
    -> WHERE name REGEXP 'Curry'
    -> ORDER BY name;
+-------+
| name  |
+-------+
| Curry |
| Curry |
+-------+
2 rows in set (0.04 sec)

上面的语句，处理关键字LIKE被REGEXP替换外，这条语句看上去非常像使用LIKE的语句。它告诉MySQL：REGEXP后跟的东西作为正则表达式处理。
为什么使用正则表达式，上面的例子并没有显示正则表达式的作用，.是正则表达式中一个特殊的字符，它表示匹配任意一个字符。再看下面的例子：
```
mysql> SELECT name
    -> FROM caiji
    -> WHERE name REGEXP '.ur';
+--------+
| name   |
+--------+
| Curry  |
| Durant |
| Curry  |
+--------+
3 rows in set (0.00 sec)
```
LIKE与REGEXP的重要区别如下：
LIKE语句会匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不会被返回（除非使用通配符）。而REGEXP语句在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行也将会被返回。REGEXP也可以用来匹配整个列值，使用^和$定位符即可。
- LIKE语句
```
mysql> SELECT name
    -> FROM caiji
    -> WHERE name LIKE 'ur';
Empty set (0.00 sec)
```
- REGEXP语句
```
mysql> SELECT name
-> FROM caiji
-> WHERE name REGEXP 'ur';
+--------+
| name   |
+--------+
| Curry  |
| Durant |
| Curry  |
+--------+
3 rows in set (0.00 sec)
```
匹配不区分大小写，MySQL中的正则表达式匹配不区分大小写，为了区分大小写，可以使用BINARY关键字，如WHERE name REGEXP BINARY ‘UR.’;

2.2 进行OR匹配

为了搜索两个串之一（或者这个，或者另一个串），使用|，如下所示：

mysql> SELECT name
    -> FROM caiji
    -> WHERE name REGEXP 'ur|me'   // 中间不能有空格！！！
    -> ORDER BY name;
+--------+
| name   |
+--------+
| Curry  |
| Curry  |
| Durant |
| James  |
+--------+
4 rows in set (0.00 sec)

2.3 匹配几个字符之一

匹配任何单一字符。但是，只想匹配特定的字符时。可以通过指定一组用[]括起来的字符来完成。如下所示：

mysql> SELECT name
    -> FROM caiji
    -> WHERE name REGEXP '[CDZ]'
    -> ORDER BY name;
+--------+
| name   |
+--------+
| Curry  |
| Curry  |
| Durant |
| Zion   |
+--------+
4 rows in set (0.00 sec)

使用正则表达式[CDZ]，表示匹配C或D或Z，[]是另一种形式的OR语句。事实上，正则表达式[CDZ]是C|D|Z的缩写，也可以使用后者。但是需要用[]来定义OR语句查找什么，看下面的例子：
```
mysql> SELECT grade
    -> FROM caiji
    -> WHERE grade REGEXP '9|8|03新秀';
+-------+
| grade |
+-------+
| 09年  |
| 08年  |
+-------+
2 rows in set (0.07 sec)
```
上面的结果不是期望的输出，期望的输出还有03年。这是因为MySQL假定你的意思是9或8或03新秀，除非你把字符|括在一个集合中，否则它将应用于整个串。
字符集合也可以被否定。即，它们将匹配除了指定字符外的任何东西，为一个否定字符集，在集合的开始处放置一个^就可以，[^123]就表示匹配除了这些字符外的任何东西。

2.4 匹配范围

集合可以用来定义匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：[0123456789]。为了简化这种类型的集合，可以使用-来定义一个范围，下面的式子功能等同于上述数字列表：[0-9]。范围不局限完整的集合，[1-3]和[6-9]是合法的范围，此外范围不一定是数值，[a-z]匹配任意的字母字符。
```
mysql> SELECT grade
    -> FROM caiji
    -> WHERE grade REGEXP '[0-9]'
    -> ORDER BY grade;
+-------+
| grade |
+-------+
| 03年  |
| 08年  |
| 09年  |
| 13年  |
+-------+
4 rows in set (0.11 sec)
```

2.5 匹配特殊字符

特殊字符需要使用\进行转义，例如：

SELECT name 
FROM caiji
WHERE name REGEXP '\\.' ORDER BY name;

2.6匹配多个实例

特殊符号

// sticks?匹配stick和sticks（s后的?使s可选，因为？匹配它前面的任何字符的0次或1次出现）
SELECT name
FROM caiji
WHERE name REGEXP '\\([0-9]sticks?\\)' 
ORDER BY name;

下面是另一个例子，打算匹配连在一起的4位数字:

SELECT name
FROM caiji
WHERE name REGEXP '[[:digit:]]{4}'
ORDER BY name;

2.7 定位符的使用

定位符

// 找出以数字(包括小数点)开始的所有产品名
SELECT name
FROM caiji
WHERE name REGEXP '^[0-9\\.]' 
ORDER BY  name;

第十章创建计算字段

1.计算字段

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
计算字段：存储在表中的数据都不是应用程序所需要的，我们需要直接从数据库中检索出转换、计算或格式化的数据；而不是检索出数据，然后再在客户机应用程序中进行格式化。

2.拼接字段(两个列拼接起来成为一个列)

方法是将两个列拼接起来，在MySQL的SELECT语句中，可使用Concat()函数来拼接列。多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现。

mysql> SELECT Concat(name, ' (',score, ')')
    -> FROM caiji
    -> ORDER BY name;
+-------------------------------+
| Concat(name, ' (',score, ')') |
+-------------------------------+
| Curry (96)                    |
| Curry (78)                    |
| Durant (97)                   |
| James (99)                    |
| Zion (81)                     |
+-------------------------------+
5 rows in set (0.13 sec)

分析：Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。删除数据右侧多余的空格来整理数据，可以使用MySQL的RTrim()函数来完成，如下所示：
```
mysql> SELECT Concat(RTrim(score), ' (', RTrim(grade), ')')
    -> FROM caiji
    -> ORDER BY score;
```
Trim()函数：MySQL除了支持RTrim()，还支持LTrim()以及Trim()[去掉左右两边的空格]。

3.使用别名(alias)

AS:一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。为了解决这个问题，SQL支持列别名。别名是一个字段或值的替换名，别名用AS关键字赋予。

mysql> SELECT Concat(name, ' (',score, ')') AS player_info
    -> FROM caiji
    -> ORDER BY name;
+-------------+
| player_info |
+-------------+
| Curry (96)  |
| Curry (78)  |
| Durant (97) |
| James (99)  |
| Zion (81)   |
+-------------+
5 rows in set (0.05 sec)

4.执行算术计算

计算字段的另一个常见的用途：是对检索出来的数据进行算术计算。

mysql> SELECT name, age, score, age * score AS total
    -> FROM caiji
    -> WHERE id = 1;
+-------+------+-------+-------+
| name  | age  | score | total |
+-------+------+-------+-------+
| Curry |   31 |    96 |  2976 |
+-------+------+-------+-------+
1 row in set (0.11 sec)

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

函数没有SQL的可移植性强。能运行在多个系统上的代码称为可移植的。相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而有时候差异很大。
大多数SQL实现支持以下类型的函数：
- a.用于处理文本串(如删除或填充值,转换大小写)的文本函数
- b.用于处理数值数据上进行算法操作（如返回绝对值，进行代数运算）的数值函数
- c.用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差，检查日期有效性等)的日期和时间函数。
- d.返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数。

1.文本处理函数

// Upper()函数：将文本转换成大写
mysql> SELECT name, UPPER(name) AS name_upper
    -> FROM caiji
    -> ORDER BY name;
+--------+------------+
| name   | name_upper |
+--------+------------+
| Curry  | CURRY      |
| Curry  | CURRY      |
| Durant | DURANT     |
| James  | JAMES      |
| Zion   | ZION       |
+--------+------------+
5 rows in set (0.06 sec)

常用的文本处理函数.png

2.日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效的排序或过滤，并且节省物理存储空间。
需要注意的是MySQL使用的日期格式。指定一个日期时，不管是插入或更新表值还是用where子句进行过滤，日期必须为格式yyyy-mm-dd。下面的语句中DATE()指示MySQL仅提取列的日期部分，而不是将给出的日期(给出的日期中还包含了小时:分钟:秒)与整个列值进行比较，因此使用**DATE()**函数仅提取列中的日期部分。

// SQL中年月日的写法：2005-09-01
SELECT id, num
FROM orders
WHERE DATE(order_date) = '1992-10-21';

如果你想要查询2005年9月的所有订单，可以使用下面两种SQL语句：

SELECT id, num 
FROM orders
WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
// 或者
SELECT id, num
FROM orders
WHERE Year(order_date) = 2005 AND
Month(order_date) = 9;

Year()：是一个从日期中返回年份的函数。类似地，Month()从日期中返回月份。

3.数值处理函数

常用的数值处理函数如下：

第12章汇总数据

1.聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有以下的几种：
- 确定表中行数（或者满足某个条件或包含某个特定值的行数）
- 获得表中行组的和
- 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值
聚集函数：运行在行组上，计算和返回单个值的函数。常用的5个聚集函数如下：

1.1 AVG函数

AVG()：通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可以用来返回所有列的平均值，也可以用来返回特定行或列的平均值,AVG()函数会忽略列值为NULL的行。AGV()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
```
mysql> SELECT AVG(age) AS age_avg
    -> FROM caiji;
+---------+
| age_avg |
+---------+
| 28.4000 |
+---------+
1 row in set (0.12 sec)

mysql> SELECT AVG(age) AS age_avg
-> FROM caiji
-> WHERE id = 1;
+---------+
| age_avg |
+---------+
| 31.0000 |
+---------+
1 row in set (0.00 sec)
```

1.2 COUNT()函数

a.COUNT(*)：对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

b.COUNT(column)：对特定列中具有值得行进行计数，忽略值为NULL的行。

mysql> SELECT COUNT(*) AS num
    -> FROM caiji;
+-----+
| num |
+-----+
|   5 |
+-----+
1 row in set (0.06 sec)

mysql> SELECT COUNT(age) AS num_age
    -> FROM caiji;
+---------+
| num_age |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

1.3 MAX()函数

MAX()：返回指定列中最大的值，忽略值为NULL的行。

mysql> SELECT MAX(age) AS max_age
    -> FROM caiji;
+---------+
| max_age |
+---------+
|      34 |
+---------+
1 row in set (0.11 sec)

1.4 MIN()函数

MIN()：返回指定列中最小的值，忽略列值为NULL的行。

mysql> SELECT MIN(score) AS min_score
    -> FROM caiji;
+-----------+
| min_score |
+-----------+
|        78 |
+-----------+
1 row in set (0.00 sec)

1.5 SUM()函数

SUM()：用来返回指定列值的和（总计）,SUM()函数忽略列值为NULL的行。

mysql> SELECT SUM(age) AS sum_age
    -> FROM caiji
    -> WHERE id = 1;
+---------+
| sum_age |
+---------+
|      31 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT SUM(age*score) AS num
    -> FROM caiji
    -> WHERE id = 1;
+------+
| num  |
+------+
| 2976 |
+------+
1 row in set (0.08 sec)

1.6 聚集不同的值(MySQL 5及后面的版本才支持)

对所有行执行计算，指定ALL参数或不给参数（因为ALL参数是默认的，不需要指定）。只包含不同的值，指定DISTINCT参数。
```
SELECT AVG(DISTINCT price) AS avg_price
FROM products
WHERE id = 1003;
```

1.7 组合聚集函数

SELECT语句可以根据需要包含多个聚集函数，如下所示：

mysql> SELECT COUNT(*) AS num_players,
    -> MIN(age) AS age_min,
    -> MAX(age) AS age_max,
    -> AVG(age) AS age_avg
    -> FROM caiji;
+-------------+---------+---------+---------+
| num_players | age_min | age_max | age_avg |
+-------------+---------+---------+---------+
|           6 |      19 |      34 | 28.3333 |
+-------------+---------+---------+---------+
1 row in set (0.00 sec)

第十三章分组数据

概述：介绍如何分组数据，以便能汇总表内容的子集。涉及到两个新的SELECT语句子句，分别是GROUP BY 和 HAVING子句。

1.数据分组

CurryCoder

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL必知必会读书笔记

第一章了解SQL1.1 数据库基础数据库：保存有组织的数据的容器(通常是一个文件或一组文件)。注意要与数据库软件DBMS（数据库管理系统）区别，数据库是通过DBMS创建和操纵的容器。数据库可以是保存在硬件设备上的文件，但也可以不是。很大程度上来说，数据库究竟是文件还是别的什么并不重要，因为你并不直接访问数据库，你使用的是DBMS，它替你访问数据库。表：某种特定类型数据的结构化清单。关键点...
复制链接

扫一扫