轩小陌的Python笔记-day25 MySQL入门

第四模块 MySQL数据库

从今天开始将进入第四模块的学习，这个模块就是给大家讲解MySQL数据库。

以前，在开发程序时，我们会把很多的数据和信息存储到某个文件夹中的文件中，例如：user.txt 、db.xlsx 等。

现在，有一个叫：数据库管理系统（DBMS，Database Management System）的软件，可以帮助我们实现对文件夹中的文件进行操作，而我们只要学习DBMS能识别的指令， 就能控制它去帮助我们实现对文件和文件夹的处理。例如：

• 在一台计算机上实现程序与数据库管理系统的交互：

• 在不同计算机上实现程序与数据库管理系统的交互：

数据库管理系统（DBMS）专注于帮助开发者解决数据存储的问题，这样开发者就可以把主要精力放在实现业务功能上了。

业内有很多的的数据库管理系统产品，例如：

• MySQL，原来是sun公司，后来被甲骨文收购。现在互联网企业几乎均在使用。【免费 + 收费】
• Oracle，甲骨文。收费，一般国企、事业单位使用居多。【收费】
• Microsoft SQL Server，微软开发。【收费】
• DB2，IBM开发。【免费 + 收费】
• SQLite，D. Richard Hipp个人开发。【免费】
• Access， 微软开发。【收费】
• PostgreSQL，加州大学伯克利分校开发。【免费】
• …

由于MySQL的使用范围最广，所以课程主要给大家讲解MySQL数据库。

在项目开发中想要基于MySQL来进行数据存储，大致应该怎么做呢？

本系列的MySQL模块会分为5部分来讲解：

• MySQL入门，安装和快速应用Python实现数据库的操作。
• 必备SQL和授权，学习更多必备的指令让数据库实现更多业务场景。
• SQL强化和实践，强化练习项目开发写的最多的必备”指令“。
• 索引和函数以及存储过程，掌握常见性能提升手段以及那些应用不是很频繁却又需了解的知识点。
• Python操作MySQL和应用，侧重点在于Python开发，让大家了解Python开发中必备的实战应用，例如：锁、事务、数据库连接池等。

day25 MySQL入门

课程目标：学习安装和快速应用Python实现数据库的操作。

课程概要：

• 安装 & 配置 & 启动
  • win
  • mac
• 数据库 管理（类比文件夹）
• 表 管理 （类比文件夹下的Excel文件）
• 数据行 管理（类比Excel文件中的数据行）
• Python操作MySQL及相关安全的问题

1. 安装 & 配置 & 启动

MySQL现在的版本主要分为：

• 5.x 版本，现在互联网企业中的主流版本，包括：头条、美图、百度、腾讯等互联网公司主流的版本。
• 8.x 版本，新增了一些了窗口函数、持久化配置、隐藏索引等其他功能。

所以，我们课程会以常用大版本中最新的版本为例来讲解，即：5.7.31 （依然有很多企业在用5.6.x，但新项目基本上都是5.7.x了）。

1.1 win系统

第1步：下载安装

https://downloads.mysql.com/archives/community/

第2步：解压至任意文件夹

建议解压至软件安装目录，例如：

第3步：创建配置文件

在MySQL的安装目录下创建 my.ini 的文件，作为MySQL的配置文件。

其实，MySQL的配置文件可以放在很多的目录，下图是配置文件的优先级：

强烈建议大家还是把配置文件放在MySQL安装目录下，这样以后如果电脑上想要安装多个版本的MySQL，配置文件可以相互独立不影响。

注意：如果你电脑的上述其他目录存在MySQL配置文件，建议删除，否则可能会影响MySQL的启动。

第4步：初始化

>>> "C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe"  --initialize-insecure

初始化命令在执行时，会自动读取配置文件并执行初始化，此过程主要会做两件事：

• 自动创建data目录，以后我们的数据都会存放在这个目录。
• 同时创建建必备一些的数据，例如默认账户 root （无密码），用于登录MySQL并通过指令操作MySQL。

在windowns安装过程中如果有报错 （ msvcr120.dll不存在 ），请下载并安装下面的两个补丁：

• vcredist：https://www.microsoft.com/zh-cn/download/confirmation.aspx?id=40784 （主要）
  

第5步：启动

启动MySQL常见的有两种方式：

• 临时启动

  >>> "C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe"
  

注意：此时程序会挂起，内部就是可以接收客户端发来的MySQL指令，关闭窗口或Ctrl+c 就可以停止运行。

这种启动方式每次开机或想要开启都需要手动执行一遍命令比较麻烦。

• 制作windows服务，基于windows服务管理。

  >>>"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57
  

创建好服务之后，可以通过命令 启动和关闭服务，例如：

>>> net start mysql57
>>> net stop mysql57

也可以在window的服务管理中点击按钮启动和关闭服务。例如：

以后不再想要使用window服务了，也可以将制作的这个MySQL服务删除。

>>>"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --remove mysql57

第6步：测试连接MySQL

安装并启动MySQL之后，就可以连接MySQL来测试是否已正确安装并启动成功。

以后在开发时，肯定是要用Python代码来连接MySQL并且进行数据操作（后面讲）。

在安装MySQL时，其实也自动安装了一个工具（客户端），让我们快速实现连接MySQL并发送指令。

注意：如果把bin目录加入环境变量，每次在运行命令时，就不用再重新输入绝对路径了。

上述过程如果操作完成之后，证明你的安装和启动过程就搞定了。

1.2 mac系统

mac系统和win不同，MySQL为他提供了非常方便的一站式安装程序，只要点击、next就可以安装、初始化完成。

第1步：安装和初始化

https://downloads.mysql.com/archives/community/

这个基于dmg文件的安装过程，其实包含了：

• 安装，默认安装在了 /usr/local/mysql-5.7.31-macos10.14-x86_64/目录。
• 初始化，在安装目录下创建data目录用于存放数据； 初始化模块数据库以及账户相关等，例如： 账cd

第2步：创建配置文件

建议在MySQL安装目录下创建 etc/my.cnf 作为MySQL的配置文件。

MySQL的配置文件按照优先级，会在以下目录中寻找：

为了避免多个版本共存时，配置文件混乱的问题，建议大家还是把配置文件放在当前MySQL的安装目录下。

第3步：启动

在Mac系统中启动MySQL常见的有2种方式：

• 安装目录中自带 mysql.server 脚本（建议）

  sudo /usr/local/mysql/support-files/mysql.server start
  # 输入电脑密码
  
  sudo mysql.server start
  # 输入电脑密码
  

  sudo /usr/local/mysql/support-files/mysql.server stop
  

为了避免每次执行命令都需要些路径，可以将路径 /usr/local/mysql/support-files加入到环境变量中。

操作完成之后，再在终端执行下命令：source ~/.zprofile 让设置的环境变量立即生效。

注意：mac系统的版本如果比较老，会显示空白的 zprofile 文件，此就要去打开 bash_profile 文件。

这样设置好之后，以后就可以使用下面的命令去启动和关闭MySQL了。

sudo mysql.server start
sudo mysql.server stop

第一种mysql.server脚本的形式，内部是使用 mysqld_safe运行，可以守护我们的MySQL进程，如意外挂掉可自动重启。

第4步：测试连接MySQL

安装并启动MySQL之后，就可以连接MySQL来测试是否已正确安装并启动成功。

以后在开发时，肯定是要用Python代码来连接MySQL并且进行数据操作（后面讲）。

在安装MySQL时，其实也自动安装了一个工具（客户端），让我们快速实现连接MySQL并发送指令。

注意：/usr/local/mysql/bin也可以加入到环境变量。

至此，在Mac系统中关于MySQL的安装和配置就完成了。

1.3 关于配置文件

上述的过程中，我们在配置文件中只添加了很少的配置。

其实，配置项有很多，而那些配置项都有默认值，如果我们不配置，MySQL则自动使用默认值。

1.4 关于密码

1. 设置和修改root密码

在windows系统中模块默认 root 账户是没有密码的，如果想要为账户设定密码，可以在利用root账户登录成功之后，执行：

2. 忘记root密码

如果你忘记了MySQL账户的密码。

• 修改配置文件，在 [mysqld] 节点下添加 skip-grant-tables=1

  [mysqld]
  ...
  skip-grant-tables=1
  ...
  

• 重启MySQL，再次登录时，不需要密码直接可以进去了

  • windows重启

    net stop mysql57
    net start mysql57
    

  • mac重启

    sudo mysql.server restart
    

  重启后，无需密码就可以进入。

  >>> mysql -u root -p
  

• 进入数据库后执行修改密码命令

  use mysql;
  update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';
  

• 退出并再次修改配置文件，删除 [mysqld] 节点下的 skip-grant-tables=1

  [mysqld]
  ...
  # skip-grant-tables=1
  ...
  

• 再次重启，以后就可以使用新密码登录了。

2. 数据库管理

安装上数据库之后，就需要开始学习指令了，通过指令让MySQL去做出一些文件操作。

如果将数据库管理系统与之前的文件管理做类比的话：

数据库管理系统	文件管理
数据库	文件夹
数据表	文件夹下的excel文件

接下来，我们先学习数据库（文件夹）相关操作的指令。

2.1 内置客户端操作

当连接上MySQL之后，执行如下指令（一般称为SQL语句），就可以对MySQL的数据进行操作。

• 查看当前所有的数据库：show databases;

• 创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

  • 删除数据库：drop database 数据库名;

• 进入数据（进入文件）：use 数据库;

示例：

# 1.登录MySQL
C:\Windows\system32>mysql.exe -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 2.查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

# 3. 创建数据库:  create database 数据库名 default charset 编码 collate 排序规则;
mysql> create database db1 default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

# 4. 删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

# 5. 查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

# 6. 进入数据库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
# 7. 进入mysql数据库（文件夹），查看此数据库下的所有表。
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 8. 退出
mysql>exit;

2.2 Python代码操作

无论通过何种方式去连接MySQL，本质上发送的 指令都是相同的，只是连接的方式和操作形式不同而已。

当连接上MySQL之后，执行如下指令，就可以对MySQL的数据进行操作。（同上述过程）

• 查看当前所有的数据库 show databases;
• 创建数据库：create database 数据库名 default charset utf8 collate utf8_general_ci;
• 删除数据库：drop database 数据库名;
• 进入数据（进入文件）：use 数据库;

想要使用Python操作MySQL需要安装第三方模块：

pip3 install pymysql

安装完成后，就可以编写代码：

import pymysql

# 连接MySQL（socket）
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
cursor = conn.cursor()

# 1. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result