MySQL快速入门

安装mysql

Ubuntu直接安装：

sudo apt-get install mysql-server mysql-client-8.0
# 在成功安装mysql后，可以直接使用root账户登录，注意这个账户是默认没有密码的。
# 因此为了数据库的安全，需要第一时间给root用户设置密码。
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "passwd";

Docker启动MySQL：

docker run -p 3306:3306 --name mysql --restart=always -v /root/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=passwd  -d mysql:8.0.24

Windows启动MySQL：

下载二进制包: https://downloads.mysql.com/archives/community/
解压
创建配置文件
根目录下创建 my.ini

[mysqld]
basedir=C:\Users\Mek\Desktop\mysql-9.0.0-winx64\bin
datadir=C:\Users\Mek\Desktop\mysql-9.0.0-winx64\data
plugin_dir=C:/Users/Mek/Desktop/mysql-9.0.0-winx64/lib/plugin
port=3306

进入bin目录初始化并启动

# 初始化
mysqld.exe --initialize --console
# 启动
mysqld.exe --console

链接到数据库

mysql -h 127.0.0.1 -u root -p
Enter password:   # 此处输入密码
# 数据库链接成功
mysql> 

# 退出
exit
quit

常见命令速记

# 数据库操作
show				# 显示
desc				# 数据表详情
use					# 选择数据库
drop				# 删除
create				# 创建

# 名称
database			# 数据库
table				# 数据表
databases			# 所有数据库
tables				# 所有数据表

# 数据表操作
select				# 查询
delete				# 删除
insert				# 插入
update				# 更新
where				# 查询条件
between				# 范围
limit				# 检索数量
offset				# 跳过数量
join				# 连接

# 逻辑操作
AND					# 和
OR					# 或
XOR					# 非

数据库操作

# 查看已有的数据库
mysql> show databases;

# 创建数据库
create DATABASE <databasename>;

# 进入数据库
use <databasename>;

# 删除数据库
drop DATABASE <databasename>;

数据表操作

创建数据表：

CREATE TABLE table_name (<字段描述>);

例如：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

具体操作应结合实际情况或使用第三方工具创建数据表

查看数据表字段信息：

desc <tablename>;

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| id              | varchar(20)   | NO   | PRI | NULL    |       |
| bid             | varchar(12)   | NO   |     | NULL    |       |
| user_id         | varchar(20)   | YES  |     | NULL    |       |
| screen_name     | varchar(30)   | YES  | MUL | NULL    |       |
| text            | varchar(2000) | YES  |     | NULL    |       |
| article_url     | varchar(100)  | YES  |     | NULL    |       |
| topics          | varchar(200)  | YES  | MUL | NULL    |       |
| at_users        | varchar(1000) | YES  |     | NULL    |       |
| pics            | varchar(3000) | YES  |     | NULL    |       |
| video_url       | varchar(1000) | YES  |     | NULL    |       |
| location        | varchar(100)  | YES  |     | NULL    |       |
| created_at      | datetime      | YES  |     | NULL    |       |
| source          | varchar(30)   | YES  |     | NULL    |       |
| attitudes_count | int           | YES  |     | NULL    |       |
| comments_count  | int           | YES  |     | NULL    |       |
| reposts_count   | int           | YES  |     | NULL    |       |
| retweet_id      | varchar(20)   | YES  |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+
17 rows in set (0.01 sec)

# Field 		字段名称
# Type 			数据类型(最大长度)
	-- varchar	文本
	-- int		整数
	-- datetime	时间
				* 更多数据类型可查看下面表格
	
# Null			是否允许为空
# Key			数据表表约束
	-- PRI		主键
	-- MUL		可以重复
	-- UNI		唯一
	
# Default		默认值
# Extra 		拓展属性

删除数据表

DROP TABLE <tablename>;

数据表所支持的数据类型：

整数	含义（有符号）
tinyint(m)	1个字节范围(-128~127)
smallint(m)	2个字节范围(-32768~32767)
mediumint(m)	3个字节范围(-8388608~8388607)
int(m)	4个字节范围(-2147483648~2147483647)
bigint(m)	8个字节范围(±9.22*10的18次方)

浮点	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

字符串	含义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

时间	含义
date	日期 ‘2008-12-2’
time	时间 ‘12:25:36’
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间

数据表约束类型：

约束	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

数据操作

插入数据：

INSERT INTO table_name ( field1, field2,...fieldN )  # 字段
                       VALUES
                       ( value1, value2,...valueN ); # 值

查找数据：

数据库查询语句格式为：

SELECT <字段1>,<字段2> FROM <表名> [WHERE 条件] [LIMIT N] [OFFSET M];
# [LIMIT N] 	获取数量
# [OFFSET M] 	起始位置

例： * 注意表名要加点号

# 查找该表全部数据
SELECT * FROM `<tablename>`;

# 查找指定字段
SELECT <字段1>,<字段2> FROM `<tablename>`;

# 输出数据排序
# 升序
SELECT * FROM `<tablename>` ORDER BY `<tablename>`.`<字段>` ASC
# 降序
SELECT * FROM `<tablename>` ORDER BY `<tablename>`.`<字段>` DESC

# 获取指定数量的数据
SELECT <字段1>,<字段2> FROM `<tablename>` limit <数量>;
# 设定起始位置
SELECT <字段1>,<字段2> FROM `<tablename>` limit <数量> offset <数量>；

# 条件查询 * 注意字符串需要加引号
# 判断式查询 可用字符 = > < >= <= !=
SELECT * FROM `<tablename>` WHERE `<字段>` = '索尼';
# 模糊查询 配合%使用
SELECT * FROM `blog_article` WHERE `title` LIKE '如何%' ORDER BY `title` DESC
SELECT * FROM `blog_article` WHERE `title` LIKE '%合集' ORDER BY `title` DESC
SELECT * FROM `blog_article` WHERE `title` LIKE '%py%' ORDER BY `title` DESC
# 分别表示 以什么开头 以什么结尾 包含什么

# 多条件查询 多条件用连接符连接
SELECT * FROM `blog_article` WHERE `title` LIKE '%py%' AND `body` LIKE '%flask%';
# AND=和 | OR=或 | XOR=非

# 范围查询 显示 views 1000到1200之间的数据
SELECT * FROM `<tablename>` WHERE `views` BETWEEN 1000 AND 1200

更新数据：

使用 UPDATE 语句修改单个表，语法格式为：

UPDATE <tablename> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

语法说明如下：

<表名>：用于指定要更新的表名称。
SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
LIMIT 子句：可选项。用于限定被修改的行数。

注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

例：

UPDATE `blog_article` SET `views` = '999' WHERE `blog_article`.`id` = 50

删除数据：

使用 DELETE 语句从单个表中删除数据，语法格式为：

DELETE FROM <tablename> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

语法说明如下：

<表名>：指定要删除数据的表名。
ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

注意：在不使用 WHERE 条件的时候，将删除所有数据。

【实例 1】删除 tb_courses_new 表中的全部数据，输入的 SQL 语句和执行结果如下所示。

mysql> DELETE FROM tb_courses_new;
Query OK, 3 rows affected (0.12 sec)
mysql> SELECT * FROM tb_courses_new;
Empty set (0.00 sec)

【实例 2】指定条件删除

# 注意条件字段不要打符号，值要打引号
mysql> DELETE FROM `image` WHERE pic_id = '0016aXrply1guyxgkhwt6j635t4qmb2c02';
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM `image` WHERE pic_id like '%0009%';
Query OK, 97 rows affected (1.49 sec)

*对于有外键引用的数据行，要先删除外键引用，再删除该数据。

高级查询

交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。

本节的末尾介绍了笛卡尔积，不了解笛卡尔积的读者可以先阅读文章末尾部分，然后再继续学习交叉连接。

交叉连接的语法格式如下：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

或

SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

语法说明如下：

字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件。

注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

当连接的表之间没有关系时，我们会省略掉 WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。需要注意的是，如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

交叉连接可以查询两个或两个以上的表，为了让读者更好的理解，下面先讲解两个表的交叉连接查询。

例 1

查询学生信息表和科目信息表，并得到一个笛卡尔积。

为了方便观察学生信息表和科目表交叉连接后的运行结果，我们先分别查询出这两个表的数据，再进行交叉连接查询。

1）查询 tb_students_info 表中的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

2）查询 tb_course 表中的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

3）使用 CROSS JOIN 查询出两张表中的笛卡尔积，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | MySQL       |  1 | Dany   |   25 | 男   |    160 |         1 |
|  3 | Python      |  1 | Dany   |   25 | 男   |    160 |         1 |
|  4 | Go          |  1 | Dany   |   25 | 男   |    160 |         1 |
|  5 | C++         |  1 | Dany   |   25 | 男   |    160 |         1 |
|  1 | Java        |  2 | Green  |   23 | 男   |    158 |         2 |
|  2 | MySQL       |  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Python      |  2 | Green  |   23 | 男   |    158 |         2 |
|  4 | Go          |  2 | Green  |   23 | 男   |    158 |         2 |
|  5 | C++         |  2 | Green  |   23 | 男   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | 女   |    185 |         1 |
|  2 | MySQL       |  3 | Henry  |   23 | 女   |    185 |         1 |
|  3 | Python      |  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Go          |  3 | Henry  |   23 | 女   |    185 |         1 |
|  5 | C++         |  3 | Henry  |   23 | 女   |    185 |         1 |
|  1 | Java        |  4 | Jane   |   22 | 男   |    162 |         3 |
|  2 | MySQL       |  4 | Jane   |   22 | 男   |    162 |         3 |
|  3 | Python      |  4 | Jane   |   22 | 男   |    162 |         3 |
|  4 | Go          |  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | C++         |  4 | Jane   |   22 | 男   |    162 |         3 |
|  1 | Java        |  5 | Jim    |   24 | 女   |    175 |         2 |
|  2 | MySQL       |  5 | Jim    |   24 | 女   |    175 |         2 |
|  3 | Python      |  5 | Jim    |   24 | 女   |    175 |         2 |
|  4 | Go          |  5 | Jim    |   24 | 女   |    175 |         2 |
|  5 | C++         |  5 | Jim    |   24 | 女   |    175 |         2 |
|  1 | Java        |  6 | John   |   21 | 女   |    172 |         4 |
|  2 | MySQL       |  6 | John   |   21 | 女   |    172 |         4 |
|  3 | Python      |  6 | John   |   21 | 女   |    172 |         4 |
|  4 | Go          |  6 | John   |   21 | 女   |    172 |         4 |
|  5 | C++         |  6 | John   |   21 | 女   |    172 |         4 |
|  1 | Java        |  7 | Lily   |   22 | 男   |    165 |         4 |
|  2 | MySQL       |  7 | Lily   |   22 | 男   |    165 |         4 |
|  3 | Python      |  7 | Lily   |   22 | 男   |    165 |         4 |
|  4 | Go          |  7 | Lily   |   22 | 男   |    165 |         4 |
|  5 | C++         |  7 | Lily   |   22 | 男   |    165 |         4 |
|  1 | Java        |  8 | Susan  |   23 | 男   |    170 |         5 |
|  2 | MySQL       |  8 | Susan  |   23 | 男   |    170 |         5 |
|  3 | Python      |  8 | Susan  |   23 | 男   |    170 |         5 |
|  4 | Go          |  8 | Susan  |   23 | 男   |    170 |         5 |
|  5 | C++         |  8 | Susan  |   23 | 男   |    170 |         5 |
|  1 | Java        |  9 | Thomas |   22 | 女   |    178 |         5 |
|  2 | MySQL       |  9 | Thomas |   22 | 女   |    178 |         5 |
|  3 | Python      |  9 | Thomas |   22 | 女   |    178 |         5 |
|  4 | Go          |  9 | Thomas |   22 | 女   |    178 |         5 |
|  5 | C++         |  9 | Thomas |   22 | 女   |    178 |         5 |
|  1 | Java        | 10 | Tom    |   23 | 女   |    165 |         5 |
|  2 | MySQL       | 10 | Tom    |   23 | 女   |    165 |         5 |
|  3 | Python      | 10 | Tom    |   23 | 女   |    165 |         5 |
|  4 | Go          | 10 | Tom    |   23 | 女   |    165 |         5 |
|  5 | C++         | 10 | Tom    |   23 | 女   |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
50 rows in set (0.00 sec)

由运行结果可以看出，tb_course 和 tb_students_info 表交叉连接查询后，返回了 50 条记录。可以想象，当表中的数据较多时，得到的运行结果会非常长，而且得到的运行结果也没太大的意义。所以，通过交叉连接的方式进行多表查询的这种方法并不常用，我们应该尽量避免这种查询。

例 2

查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容， SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info 
    -> WHERE tb_students_info.course_id = tb_course.id;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | MySQL       |  2 | Green  |   23 | 男   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | 女   |    185 |         1 |
|  3 | Python      |  4 | Jane   |   22 | 男   |    162 |         3 |
|  2 | MySQL       |  5 | Jim    |   24 | 女   |    175 |         2 |
|  4 | Go          |  6 | John   |   21 | 女   |    172 |         4 |
|  4 | Go          |  7 | Lily   |   22 | 男   |    165 |         4 |
|  5 | C++         |  8 | Susan  |   23 | 男   |    170 |         5 |
|  5 | C++         |  9 | Thomas |   22 | 女   |    178 |         5 |
|  5 | C++         | 10 | Tom    |   23 | 女   |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
10 rows in set (0.01 sec)

如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。

子查询在 WHERE 中的语法格式如下：

WHERE <表达式> <操作符> (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

1）IN | NOT IN

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

2）EXISTS | NOT EXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

例 1

使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info 
    -> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.01 sec)

结果显示，学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行，实现效果是相同的。

二进制日志

开启二进制日志

修改/etc/my.cnf 文件,添加如下内容:

# 这个ID在主服务器和从服务器之间必须唯一
server-id = 1
# 指定二进制日志的位置
log_bin = /var/log/mysql/mysql-bin.log
# 最大连接数
max_connections = 4096

检查文件夹是否存在,没有则创建,并设置正确的权限

查询二进制日志

# 登录mysql
mysql -u your_username -p
SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |      1250 |
+------------------+-----------+
2 rows in set (0.00 sec)
SHOW BINLOG EVENTS IN 'filename';

# 设置二进制日志最大容量和保存天数
SET GLOBAL max_binlog_size = 2G;
SET GLOBAL expire_logs_days = 7;

# 使用系统工具查看
mysqlbinlog /path/to/binlog-file

回放二进制日志

从MySQL的二进制日志文件中进行数据恢复通常需要一些步骤和谨慎的操作，因为二进制日志包含了数据库的更改历史。以下是从二进制日志文件进行数据恢复的一般步骤：

备份当前数据： 在执行任何恢复操作之前，请确保首先备份当前数据库状态，以防万一。你可以使用mysqldump工具来创建数据库备份。
确定要恢复的时间点： 确定你要恢复到的时间点或特定的二进制日志文件。你可以查看二进制日志文件列表以及其内容来选择合适的时间点。
停止MySQL服务器： 在进行数据恢复之前，通常需要停止MySQL服务器以避免在恢复过程中出现数据不一致的情况。你可以使用以下命令停止MySQL服务器：
```
sudo service mysql stop
```
回放二进制日志： 使用mysqlbinlog工具来回放选定的二进制日志文件，将其中的SQL操作逐个执行。命令如下：
```
mysqlbinlog /path/to/binlog-file | mysql -u username -p
```
替换 /path/to/binlog-file 为你选择的二进制日志文件的实际路径和文件名，username 为MySQL用户名。你将需要输入MySQL密码以继续。

请注意，这个操作会执行二进制日志文件中的SQL语句，因此只应该选择那些你确信不会导致数据损坏的日志文件。
启动MySQL服务器： 在数据恢复完成后，启动MySQL服务器，让其以恢复后的状态运行：
```
sudo service mysql start
```
验证恢复： 确保数据库恢复后的数据状态与你期望的一致。检查数据完整性和准确性。

请注意，从二进制日志中进行数据恢复是一项复杂的任务，可能需要特定的数据库管理和恢复经验。如果你不确定如何执行这些步骤，建议寻求专业数据库管理员的帮助，以避免意外数据丢失或数据库损坏。

此外，二进制日志文件应该定期备份，以便在需要时执行数据恢复操作。数据库备份和恢复策略是确保数据安全性和可用性的关键组成部分。

自动备份脚本

if [ $# -gt 0 ]
then
  MY="$1"
else
  timestamp=$(date +%Y%m%d%H%M%S)
  MY="$timestamp.sql"
fi

echo "导出文件为：$MY"
# passwd 为数据库密码
mysqldump --all-databases -uroot -ppasswd > $MY

# 定义目标文件扩展名
file_extension=".sql"

# 获取当前目录下的所有匹配扩展名的文件并按修改时间排序
files=($(find . -maxdepth 1 -type f -name "*$file_extension" -exec stat -c "%Y %n" {} \; | sort -n -r | awk '{print $2}'))

# 获取最新的三个文件
latest_files=("${files[@]:0:3}")

# 遍历所有文件并删除不在最新三个文件列表中的文件
for file in "${files[@]}"; do
  if [[ ! "${latest_files[@]}" =~ "$file" ]]; then
    rm "$file"
    echo "删除文件: $file"
  fi
done

添加到自动任务

# 编辑自动任务
crontab -e
# 添加

# 每日凌晨2点运行
0 2 * * * cd /data/backup/ && /data/backup/backup.sh
# 间隔30分钟运行
*/30 * * * * cd /data/backup/ && /data/backup/backup.sh
# 周六零点运行
0 0 * * 6 cd /data/backup/ && /data/backup/backup.sh

文大师的藏经阁