MySQL复习——20211027

MySQL复习——20211027"/>

MySQL复习——20211027

MYSQL

MySQL创建数据库

我们可以在登录MySQL服务后，使用create命令创建数据库，语法如下：

CREATE DATABASE 数据库名;

使用root用户登录，root用户拥有最高权限，可以使用mysql mysqladmin 命令来创建数据库

# mysqladmin -u root -p create NOWCODER
# Enter password:******

以上命令执行成功后会创建MySQL数据库NOWCODEER

MySQL删除数据库

使用普通用户登陆 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

drop命令删除数据库

drop命令格式：

drop database <数据库名>;

例如删除名NOWCODER的数据库：

drop database NOWCODER;

MySQL选择数据库

从命令提示窗口中选择MySQL数据库

# mysql -u root -p
Enter password:******
mysql>use NOWCODER;
Database changed

MySQL数据类型

可分为三类：

1. 数值
2. 日期/时间
3. 字符串（字符）

• 数值

  • MySQL支持所有标准SQL数值数据类型
    • 这些类型包括严格数值数据类型（INTEGER、SMALLINT、DECIMAL和NUMERIC），以及近似数值数据类型（FLOAT、REAL和DOUBLE PRECISION）
    • 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词
    • BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表
    • 作为SQL标准的扩展，MySQL也支持TINYINT、MEDIUMINT和BIGINT。

  类型	大小	范围（有符号）	范围（无符号）	用途
  TINYINT	1 字节	(-128，127)	(0，255)	小整数值
  SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
  MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
  INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
  BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
  FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
  DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
  DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

MySQL 创建数据表

创建MySQL数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段

语法

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 NOWCODER 数据库中创建数据表nowcoder_tbl：

CREATE TABLE IF NOT EXISTS `nowcoder_tbl`(`nowcoder_id` INT UNSIGNED AUTO_INCREMENT,`nowcoder_title` VARCHAR(100) NOT NULL,`nowcoder_author` VARCHAR(40) NOT NULL,`submission_date` DATE,PRIMARY KEY ( `nowcoder_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

• 如果你不想字段为NULL可以设置字段的属性为NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于逐渐，数值会自动加1
• PRIMARY KEY关键字用于定义列为主键
• ENGINE设置存储引擎，CHARSET设置编码

通过命令提示符创建表

通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。

实例

以下为创建数据表 nowcoder_tbl 实例:

root@host# mysql -u root -p
Enter password:*******
mysql> use NOWCODER;
Database changed
mysql> CREATE TABLE nowcoder_tbl(-> nowcoder_id INT NOT NULL AUTO_INCREMENT,-> nowcoder_title VARCHAR(100) NOT NULL,-> nowcoder_author VARCHAR(40) NOT NULL,-> submission_date DATE,-> PRIMARY KEY ( nowcoder_id )-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

**注意：**MySQL命令终止符为分号 ; 。

注意： -> 是换行符标识，不要复制。

##MySQL 删除数据表

MySQL中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

语法

以下为删除MySQL数据表的通用语法：

DROP TABLE table_name;

在命令提示窗口中删除数据表

在mysql>命令提示窗口中删除数据表SQL语句为 DROP TABLE ：

实例

以下实例删除了数据表nowcoder_tbl:

root@host# mysql -u root -p
Enter password:******
mysql>use NOWCODER;
Database changed
mysql>DROP TABLE nowcoder_tbl
Query OK, 0 rows affected (0.8 sec)

MySQL 插入数据

MySQL 表使用INSERT INTO SQL语句来插入数据。

INSERT INTO table_name (field1,field2,...fieldN)VALUES(value1,value2,...valueN);

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

通过命令提示窗口插入数据

以下我们将使用 SQL INSERT INTO 语句向 MySQL 数据表 nowcoder_tbl 插入数据

实例

nowcoder_tbl 表插入三条数据:

root@host# mysql -u root -p password;
Enter password:*******
mysql>use NOWCODER;
Database changed
mysql>INSERT INTO nowcoder_tbl->(nowcoder_title,nowcoder_author,submission_date)->VALUES->("学习 PHP","牛客",NOW());
uery OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO nowcoder_tbl-> (nowcoder_title, nowcoder_author, submission_date)-> VALUES-> ("学习 MySQL", "牛客教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO nowcoder_tbl-> (nowcoder_title, nowcoder_author, submission_date)-> VALUES-> ("JAVA 教程", "NOWCODER.COM", '2019-10-06');
Query OK, 1 rows affected (0.00 sec)

注意： 使用箭头标记 -> 不是 SQL 语句的一部分，它仅仅表示一个新行，如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号 ;。

在以上实例中，我们并没有提供 nowcoder_id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。

接下来我们可以通过以下语句查看数据表数据：

SELECT * FROM nowcoder_tbl;

输出结果：

mysql> ``select` `* ``from` `nowcoder_tbl;
+``-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+``-------------+----------------+-----------------+-----------------+
|      1 | 学习 PHP    | 牛客教程     | 2019-10-10   |
|      2 | 学习 MySQL   | 牛客教程     | 2019-10-10   ||      3 | JAVA 教程    | NOWCODER.COM  | 2019-10-06   |
+``-------------+----------------+-----------------+-----------------+
3 ``rows` `in` `set` `(0.00 sec)

MySQL查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。

你可以通过 mysql> 命令提示窗口中在数据库中查询数据

语法

以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][OFFSET M]

• 查询语句中你可以受用一个或者多个表，表之间使用逗号（，）分割，并使用WHERE语句来设定查询条件。
• SELECT命令可以读取一条或者多条记录
• 你可以使用星号（＊）来代替其他字段，ＳＥＬＥＣＴ语句回返回表的所有字段数据
• 你可以使用ＷＨＥＲＥ语句来包含任何条件。
• 你可以使用ＬＩＭＩＴ属性来设定返回的记录数
• 你可以通过ＯＦＦＳＥＴ指定ＳＥＬＥＣＴ语句开始查询的数据偏移量。默认情况下偏移量为０

实例

返回数据表 nowcoder_tbl 的所有记录:

SELECT * FROM nowcoder_tbl;

输出结果：

mysql> ``select` `* ``from` `nowcoder_tbl;
+``-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+``-------------+----------------+-----------------+-----------------+
|      1 | 学习 PHP    | 牛客教程     | 2019-10-10   |
|      2 | 学习 MySQL   | 牛客教程     | 2019-10-10   |
|      3 | JAVA 教程    | NOWCODER.COM  | 2019-10-06   |
|      4 | 学习 Python   | NOWCODER.COM  | 2019-10-06   |
+``-------------+----------------+-----------------+-----------------+
4 ``rows` `in` `set` `(0.00 sec)

MySQL WHERE 子句

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法

以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT  field1, field2,...fieldN   FROM  table_name1, table_name2...
[WHERE condition1 [AND[OR]] condition2.....

• 查询语句中你可以使用一个或者多个表，表之间使用逗号（，）分割，并使用WHERE语句来设定查询条件
• 你可以在WHERE子句中指定任何条件
• 你可以使用AND或者OR指定一个或多个条件
• WHERE子句也可以运用于SQL的DELETE或者UPDATE命令
• WHERE子句类似于程序语言中的if条件，根据MYSQL表中的字段值来读取指定的数据

以下为操作符列表，可用于 WHERE 子句中。

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

MySQL UPDATE 更新

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

语法

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET  field- = new-value1,field2 = new-value2
[WHERE Clause]

• 你可以同时更新一个或多个字段
• 你可以在WHERE子句中指定任何条件
• 你可以在一个单独表中同时更新数据

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

通过命令提示符更新数据

以下我们将在 SQL UPDATE 命令使用 WHERE 子句来更新 nowcoder_tbl 表中指定的数据：

实例

更新数据表中 nowcoder_id 为 3 的 nowcoder_title 字段值：

update  nowcoder_tbl set nowcoder_title='学习 C++' where nowcoder_id=3;

MySQL DELETE语句

使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

语法

DELETE FROM table_name [WHERE  Clause];

• 如果没有指定WHERE子句，MySQL表中的所有记录将被删除。
• 你可以在WHERE子句中指定任何条件
• 你可以在单个表中一次性删除记录

从命令行中删除数据

这里我们将在 SQL DELETE 命令中使用 WHERE 子句来删除 MySQL 数据表 nowcoder_tbl 所选的数据。

删除 nowcoder_tbl 表中 nowcoder_id 为3 的记录

DELETE FROM nowcoder_tbl WHERE nowcoder_id = 3;

MySQL LIKE子句

MySQL 中使用 SQL SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 “nowcoder_author = ‘NOWCODER.COM’”。

但是有时候我们需要获取 nowcoder_author 字段含有 “COM” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分号%字符来表示任意字符，类似于UNIX或正则表达式中的星号*

如果没有使用百分号%，LIKE子句与等号 = 的效果是一样的

语法

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1,field2,...fieldN
FROM  table_name
WHERE  field1  LIKE  condition1[AND[OR]] filed2 = 'somevalue'

• 你可以在WHERE子句中指定任何条件
• 你可以在WHERE子句中使用LIKE子句
• 你可以使用LIKE子句代替等号 =
• LIKE通常与% 一同使用，类似于一个元字符的搜索
• 你可以使用AND或者OR指定一个或多个条件
• 你可以在DELETE或UPDATE命令中使用WHERE…LIKE子句来指定条件

在命令提示符中使用 LIKE 子句

将 nowcoder_tbl 表中获取 nowcoder_author 字段中以 COM 为结尾的的所有记录：

SELECT * FROM nowcoderr_tbl WHERE nowcoder_author LIKE '%COM';

1、%

表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（’%%’）表示

找出u_name中既有”三“又有”猫“的记录

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%';

2、_

表示任意单个字符。

匹配单个任意字符，它常用来限制表达式的字符长度语句：

 SELECT * FROM [user] WHERE u_name LIKE '_三_' 

只找出”唐三藏“这样u_name为三个字且中间一个字是”三“的；

3、[]

表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为他们中的任一个

SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'

将找出"张三"、“李三”、“王三”（而不是”张李王三“）

如果[]内有一系列字符（01234、abcde之类的）则可略写为”0-4“、”a-e“

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'

将找出“老1”、“老2”、……、“老9”

4、[^]

表示不在括号所列之内的单个字符。

其取值和[]相同，但它要求所匹配对象为指定字符以外的任一个字符

SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'

将找不出姓"张"、“李”、“王"的"赵三”、“孙三”

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';

将排除“老1”到“老4”，寻找“老5”、“老6”、……

5、查询内容包含通配符时

由于通配符的缘故，导致我们查询特殊字符"%"、"_"、"[“的语句无法正常实现，而把特殊字符用”[ ]"括起来便可正常查询。

据此我们写出以下函数： 
function sqlencode(str) str=replace(str,"';","';';") 
str=replace(str,"[","[[]") ';
此句一定要在最先 
str=replace(str,"_","[_]") 
str=replace(str,"%","[%]") 
sqlencode=str end function

MySQL UNION操作符

MySQL UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。

多个SELECT语句回删除重复的数据。

语法

MySQL UNION 操作符语法格式：

SELECT expression1,expression2,...expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1,expression2,...expression_n
FROM tables
[WHERE conditions];

• expression1, expression2, … expression_n：要检索的列
• tables：要检索的数据表
• WHERE conditions：可选，检索条件
• DISTINCT：可选，删除结果集中重复的数据。默认情况下UNION操作符已经删除了重复数据，所以DISTINCT修饰符对结果没啥影响
• ALL：可选，返回所有结果集，包含重复数据

演示数据库

在本教程中，我们将使用 NOWCODER 样本数据库。

下面是选自 “Websites” 表的数据：

mysql> select * from websites;
+----+---------------+-------------------------------+-------+---------+
| id | name          | url                           | alexa | country |
+----+---------------+-------------------------------+-------+---------+
|  1 | Google        |         |     1 | USA     |
|  2 | 淘宝          |         |    13 | CN      |
|  3 | 牛客网        |       |  6524 | CN      |
|  4 | 微博          |          |    20 | CN      |
|  5 | Facebook      |       |     3 | USA     |
|  7 | Stackoverflow |  |     0 | IND     |
+----+---------------+-------------------------------+-------+---------+
6 rows in set (0.00 sec)

下面是 “apps” APP 的数据：

mysql> select * from apps;
+----+------------+-------------------+---------+
| id | app_name   | url               | country |
+----+------------+-------------------+---------+
|  1 | QQ APP     |   | CN      |
|  2 | 微博 APP   |   | CN      |
|  3 | 淘宝 APP   |  | CN      |
+----+------------+-------------------+---------+
3 rows in set (0.00 sec)

SQL UNION实例

下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）：

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

执行以上 SQL 输出结果如下：

mysql> SELECT country FROM Websites-> UNION-> SELECT country FROM apps-> ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| IND     |
| USA     |
+---------+
3 rows in set (0.00 sec)

**注释：**UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

SQL UNION ALL 实例

下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的country（也有重复的值）：

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

执行以上 SQL 输出结果如下：

mysql> SELECT country FROM Websites-> UNION ALL-> SELECT country FROM apps-> ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| IND     |
| USA     |
| USA     |
+---------+
9 rows in set (0.00 sec)

带有 WHERE 的 SQL UNION ALL

下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据（也有重复的值）：

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

执行以上 SQL 输出结果如下：

mysql> SELECT country, name FROM Websites-> WHERE country='CN'-> UNION ALL-> SELECT country, app_name FROM apps-> WHERE country='CN'-> ORDER BY country;
+---------+------------+
| country | name       |
+---------+------------+
| CN      | 淘宝       |
| CN      | 牛客网     |
| CN      | 微博       |
| CN      | QQ APP     |
| CN      | 微博 APP   |
| CN      | 淘宝 APP   |
+---------+------------+
6 rows in set (0.00 sec)

MySQL 排序

MySQL 表中使用 SQL SELECT 语句来读取数据。

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 你可以添加 WHERE…LIKE 子句来设置条件。

在命令提示符中使用ORDER BY子句

以下将在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表 nowcoder_tbl 中的数据：

实例

尝试以下实例，结果将按升序及降序排列。

mysql> select * from nowcoder_tbl order by submission_date asc;
+-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA 教程      | NOWCODER.COM    | 2019-10-06      |
|           4 | 学习 Python    | NOWCODER.COM    | 2019-10-06      |
|           1 | 学习 PHP       | 牛客教程        | 2019-10-10      |
|           2 | 学习 MySQL     | 牛客教程        | 2019-10-10      |
+-------------+----------------+-----------------+-----------------+
4 rows in set (0.00 sec)mysql> select * from nowcoder_tbl order by submission_date desc;
+-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | 学习 PHP       | 牛客教程        | 2019-10-10      |
|           2 | 学习 MySQL     | 牛客教程        | 2019-10-10      |
|           3 | JAVA 教程      | NOWCODER.COM    | 2019-10-06      |
|           4 | 学习 Python    | NOWCODER.COM    | 2019-10-06      |
+-------------+----------------+-----------------+-----------------+
4 rows in set (0.00 sec)

MySQL GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

MySQL连接的使用

JOIN 按照功能大致分为如下三类：

• INNER JOIN （内连接，或等值连接）：获取两个表中字段匹配关系的记录
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录
• RIGHT JOIN（右链接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS `nowcoder_tbl`;
CREATE TABLE `nowcoder_tbl` (`nowcoder_id` int(11) NOT NULL AUTO_INCREMENT,`nowcoder_title` varchar(100) NOT NULL,`nowcoder_author` varchar(40) NOT NULL,`submission_date` date DEFAULT NULL,PRIMARY KEY (`nowcoder_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;BEGIN;
INSERT INTO `nowcoder_tbl` VALUES ('1', ***习 PHP', '牛客教程', '2017-04-12'), ('2', ***习 MySQL', '牛客教程', '2017-04-12'), ('3', ***习 Java', 'NOWCODER.COM', '2015-05-01'), ('4', ***习 Python', 'NOWCODER.COM', '2016-03-06'), ('5', ***习 C', 'FK', '2017-04-05');
COMMIT;DROP TABLE IF EXISTS `tcount_tbl`;
CREATE TABLE `tcount_tbl` (`nowcoder_author` varchar(255) NOT NULL DEFAULT '',`nowcoder_count` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;BEGIN;
INSERT INTO `tcount_tbl` VALUES ('牛客教程', '10'), ('NOWCODER.COM ', '20'), ('Google', '22');
COMMIT;SET FOREIGN_KEY_CHECKS = 1;

INNER JOIN

我们在NOWCODER数据库中有两张表 tcount_tbl 和 nowcoder_tbl。两张数据表数据如下：

mysql> select * from tcount_tbl;
+-----------------+----------------+
| nowcoder_author | nowcoder_count |
+-----------------+----------------+
| 牛客教程        |             10 |
| NOWCODER.COM    |             20 |
| Google          |             22 |
+-----------------+----------------+
3 rows in set (0.00 sec)mysql> select * from nowcoder_tbl;
+-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | 学习 PHP       | 牛客教程        | 2017-04-12      |
|           2 | 学习 MySQL     | 牛客教程        | 2017-04-12      |
|           3 | 学习 Java      | NOWCODER.COM    | 2015-05-01      |
|           4 | 学习 Python    | NOWCODER.COM    | 2016-03-06      |
|           5 | 学习 C         | FK              | 2017-04-05      |
+-------------+----------------+-----------------+-----------------+
5 rows in set (0.00 sec)

接下来我们就使用MySQL的**INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)**来连接以上两张表来读取nowcoder_tbl表中所有nowcoder_author字段在tcount_tbl表对应的nowcoder_count字段值：

mysql> SELECT a.nowcoder_id, a.nowcoder_author, b.nowcoder_count FROM nowcoder_tbl a INNER JOIN tcount_tbl b ON a.nowcoder_author = b.nowcoder_author;
+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

以上 SQL 语句等价于：

mysql> SELECT a.nowcoder_id, a.nowcoder_author, b.nowcoder_count FROM nowcoder_tbl a,tcount_tbl b where a.nowcoder_author=b.nowcoder_author;
+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fyG8CujZ-1635325284650)(C:\Users\supan\AppData\Roaming\Typora\typora-user-images\image-20211027145426593.png)]

LEFT JOIN

MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
左连接时，左表是主表，会将左表中的所有字段信息显示出来，右表此时为辅助表
要注意是否有字段发生匹配

以 nowcoder_tbl 为左表，tcount_tbl 为右表

SELECT a.nowcoder_id,a.nowcoder_author,b.nowcoder_count
FROM nowcoder_tbl a 
LEFT JOIN tcount_tbl b ON a.nowcoder_author = b.nowcoder_author;+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
|           5 | FK              |           NULL |
+-------------+-----------------+----------------+
5 rows in set (0.00 sec)

以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 nowcoder_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 nowcoder_author 字段值。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FXZomXwQ-1635325284653)(C:\Users\supan\AppData\Roaming\Typora\typora-user-images\image-20211027145842103.png)]

RIGHT JOIN

MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。
右连接时，右表是主表，会将右表中的所有字段信息显示出来，左表此时为辅助表
要注意是否有字段发生匹配
以 nowcoder_tbl 为左表，tcount_tbl 为右表

SELECT a.nowcoder_id,a.nowcoder_author,b.nowcoder_count
FROM nowcoder_tbl a
RIGHT JOIN tcount_tbl bON a.nowcoder_author = b.nowcoder_author;+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
|        NULL | NULL            |             22 |
+-------------+-----------------+----------------+
5 rows in set (0.00 sec)

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 nowcoder_tbl 中没有对应的nowcoder_author 字段值。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-etoTualD-1635325284654)(C:\Users\supan\AppData\Roaming\Typora\typora-user-images\image-20211027150232745.png)]

MySQL NULL值处理

当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL:当列的值是NULL，此运算符返回true
• IS NOT NULL：当列的值不为NULL，运算符返回true
• <=>:比较操作符（不同于 = 运算符），当比较的两个值为NULL时返回true

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

MySQL正则表达式

在前面的章节我们已经了解到MySQL可以通过 LIKE …% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

如果您了解PHP或Perl，那么操作起来就非常简单，因为MySQL的正则表达式匹配与这些脚本的类似。

下表中的正则模式可应用于 REGEXP 操作符中。

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
.	匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用象 ‘[.\n]’ 的模式。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，‘z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

查找name字段中以’st’为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含’mar’字符串的所有数据：

 SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

MySQL ALTER命令

当需要修改数据表名或者修改数据表字段时，就需要使用到ALTER命令

删除、添加或修改表字段

如下命令使用了ALTER命令及DROP子句来删除以上创建表的i字段

ALTER TABLE testalter_tbl  DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

ALTER TABLE testalter_tbl MODIFY c CHAR(10)

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

尝试如下实例：

ALTER TABLE testalter_tbl CHANGE i j BIGINT;
ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER 对NULL值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值

指定字段 j 为 NOT NULL 且默认值为100 。

ALTER TABLE test_tbl
MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值

你可以使用ALTER来修改字段的默认值

ALTER TABLE test_tbl ALTER i SET DEFAULT 1000;

你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值

ALTER TABLE test_tbl ALTER i DROP DEFAULT;

修改表名

如果需要修改数据表的名称，可以在ALTER TABLE语句中使用RENAME子句实现

ALTER TABLE test_tbl RENAME TO alter_name_tbl;

MySQL索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式

CREATE INDEX indexName ON mytable(username(length))

如果是CHAR，VARCHAR类型，length可以小于字段实际长度

如果是BLOB和TEXT类型，必须指定length

修改表结构（添加索引）

ALTER TABLE tableName ADD INDEX indexName(columnName)

创建表时候直接指定

CREATE TABLE mytable( 
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL, 
INDEX [indexName] (username(length)) 
);

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable( 
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL, 
UNIQUE [indexName] (username(length)) 
);

使用ALTER命令添加和删除索引

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
• **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ，用于全文索引。

使用ALTER命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，必须确保主键默认不为空（NOT NULL）

ALTER TABLE test_tbl MODIFY i INT NOT NULL;
ALTER TABLE test_tbl ADD PRIMARY KEY(i);

你也可以使用ALTER命令删除主键

ALTER TABLE test_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY ，但在删除索引时，必须知道索引名

显示索引信息

你可以使用SHOW INDEX命令来列出表中的相关的索引信息；

可以通过添加\G来格式化输出信息

SHOW INDEX FROM table_name;\G