-day25--mysql入门

第四模块 MySQL数据库

数据库管理系统（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 （主要）
  
• dirctx：https://www.microsoft.com/zh-CN/download/details.aspx?id=35

第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目录用于存放数据； 初始化模块数据库以及账户相关等

第2步：创建配置文件

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

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

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my

为了避免多个版本共存时，配置文件混乱的问题，建议大家还是把配置文件放在当前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加入到环境变量中。

export = PATH=$PATH:/xx/xx/xx

操作完成之后，再在终端执行下命令：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并发送指令。

/xx/xx/mysql -h 127.0.0.1 -p 3306 -u root -p

# 第一次进入，需要重新设置密码
mysql> set password = password('123456')

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

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

1.3 Linux下二进制快速安装

# 下载
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
# 解压
tar -xzf  mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz 

# 卸载自动的mariadb
 rpm -qa | grep mysql
 rpm -qa | grep mariadb
 yum -y remove mariadb-libs-5.5.56-2.el7.x86_64
    
# 创建mysql用户
useradd -M -s /sbin/nologin -r mysql


# 创建mysql数据文件夹
mv mysql-5.7.35-linux-glibc2.12-x86_64 mysql
mkdir -p  /usr/local/mysql/data
cd /usr/local/mysql

# 授权文件夹
chown -R mysql.mysql /usr/local/mysql/

# 二进制安装
cd ../mysql/

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

# 生成了一个随机密码 H!<PVjE44j42

# 验证
echo $?

# 配置mysql配置文件
vim /etc/my.cnf
export PATH=/usr/local/mysql/bin:$PATH
vim /etc/profile

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
socket=/usr/local/mysql/mysql.sock
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld.pid
[mysql]
socket=/usr/local/mysql/mysql.sock
[client]
socket=/usr/local/mysql/mysql.sock

# 配置并验证环境变量
tail -n1 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
source /etc/profile
which mysql

# 拷贝启动文件
ls support-files/
cp support-files/mysql.server /etc/init.d/mysqld

# 给执行权限
chmod +x /etc/init.d/mysqld

# 配置数据库数据文件
vim /etc/init.d/mysqld

 46 basedir=/usr/local/mysql
 47 datadir=/usr/local/mysql/data

# 启动mysql
/etc/init.d/mysqld start


# 设置密码
 mysqladmin -uroot -p password 123456


# 忘记密码并修改密码
# 在配置文件[mysqld]中加入如下，重启mysql后可跳过验证
skip-grant-tables

# 进入mysql执行如下修改密码
update mysql.user set authentication_string=password('123456') where user='root';
flush pritvileges;



# 放通主机ip连接mysql
grant  all on *.* to root@'%'  identified by '123456';

1.4 关于配置文件

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

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

1.5 关于密码

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;

  create database day25db;
  
  create database day25db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
  # 数据库的较验规则，会影响排序，ci是case insensitive的缩写，意思是大小写不明感。
  
  -- collate utf_bin是 以二进制值比较，也就是区分大小写，collate是核对的意思
  -- uft-8_general_ci  一般比较，不区分大小写
  

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

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

示例：

# 1.登录MySQL
[root@localhost ~]# mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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.

mysql>

# 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> 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")

#开启游标功能，创建游标对象，这里使用的是数据库对象db中的cursor()方法，
cursor = conn.cursor()

# 1. 查看数据库
# 发送指令
#  使用execute()方法，执行SQL语句
cursor.execute("show databases")

# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

'''
# 使用fetchone()或fetchall()获取数据
# 一次性获取一条数据
a = cursor.fetchone()
 
# 一次性获取所有数据
a = cursor.fetchall()
'''

# 2. 创建数据库（新增、删除、修改）
# 发送指令
cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()

# 3. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))

# 4. 删除数据库
# 发送指令
cursor.execute("drop database db3")
conn.commit()

# 5. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 6. 进入数据库，查看表
# 发送指令
cursor.execute("use mysql")
cursor.execute("show tables")
result = cursor.fetchall()
print(result) # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),....

# 关闭连接[断开数据库，释放资源]
cursor.close()
conn.close()

3. 数据表 管理

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

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

其实在数据库中创建数据库 和 创建Excel非常类似，需要指定： 表名、列名称、类类型（整型、字符串或其他)。

3.1 内置客户端操作

数据表常见操作的指令：

• 进入数据库 use 数据库;，查看当前所有表：show tables;

• 创建表结构
  

  create table 表名(
      列名  类型,
      列名  类型,
      列名  类型
  )default charset=utf8; -- 表示设置数据库的默认字符集为utf8
  
  # 语法：
  create table  <表名> (<字段名1><类型1>,<字段名2><类型2>,...<字段名n><类型n>);
  

  create table tb1(
  	id int,
      name varchar(16)
  )default charset=utf8;
  

  create table tb2(
  	id int,
      name varchar(16) not null,   -- 不允许为空
      email varchar(32) null,      -- 允许为空（默认）
      age int
  )default charset=utf8;
  

  create table tb3(
  	id int,
      name varchar(16) not null,   -- 不允许为空
      email varchar(32) null,      -- 允许为空（默认）
      age int default 3            -- 插入数据时，如果不给age列设置值，默认值：3
  )default charset=utf8;
  
  # --表示注释
  

  create table tb4(
  	id int primary key,			 -- 主键（不允许为空、不能重复）
      name varchar(16) not null,   -- 不允许为空
      email varchar(32) null,      -- 允许为空（默认）
      age int default 3            -- 插入数据时，如果不给age列设置值，默认值：3
  )default charset=utf8;
  
  # 主见：约束，不允许为空，不能重复，确保唯一性以及加速查找。
  

  主键一般用于表示当前这条数据的ID编号（类似于人的身份证），需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。

  create table tb5(
  	id int not null auto_increment primary key,	-- 不允许为空 & 主键 & 自增
      name varchar(16) not null,   		-- 不允许为空
      email varchar(32) null,      		-- 允许为空（默认）
      age int default 3            		-- 插入数据时，如果不给age列设置值，默认值：3
  )default charset=utf8;
  
  # auto_increment 自增，在数据库中一般会将主键和自增结合
  

  注意：一个表中只能有一个自增列【自增列，一般都是主键】。

• 删除表 drop table 表名;

  drop table tb1
  

• 清空表 delete from 表名; 或 truncate table 表名;（速度快、无法回滚撤销等）

  区别：
  drop 用于删除表(表结构，属性已经缩印也会被删除)
  delete DML语言，可以回退，可以有条件的删除
  truncate DDL语言，无法回退，默认所有的表内容都删除，删除速度比delete快。
  

• 查看列

  desc 表名;
  

• 修改表

  • 添加列

    alter table 表名 add 列名 类型;
    alter table 表名 add 列名 类型 DEFAULT 默认值;
    alter table 表名 add 列名 类型 not null default 默认值;
    alter table 表名 add 列名 类型 not null primary key auto_increment;
    

  • 删除列

    alter table 表名 drop column 列名;
    

  • 修改列 类型

    alter table 表名 modify column 列名 类型;
    

  • 修改列 类型 + 名称

    alter table 表名 change 原列名 新列名 新类型;
    

    alter table  tb change id nid int not null;
    alter table  tb change id id int not null default 5;
    alter table  tb change id id int not null primary key auto_increment;
    
    alter table  tb change id id int; -- 允许为空，删除默认值，删除自增。
    

  • 修改列 默认值

    ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;
    

  • 删除列 默认值

    ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
    

  • 添加主键

    alter table 表名 add primary key(列名);
    

  • 删除主键

    alter table 表名 drop primary key;
    

  • 在指定列后添加一列

    alter table t1 add stu_id int after id; # 表示添加列stu_id 在 id列后
    

  • 在表中最前添加一列 sid int

    alter table t1 add sid int first;
    

• 常见列类型

  create table 表(
  	id int,
      name varchar(16)
  )default charset=utf8;
  

  • int[(m)][unsigned][zerofill]

    int				表示有符号，取值范围：-2147483648 ～ 2147483647
    int unsigned	表示无符号，取值范围：0 ～ 4294967295
    int(5)zerofill	仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。
    

    mysql> create table L1(id int, uid int unsigned, zid int(5) zerofill) default charset=utf8;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into L1(id,uid,zid) values(1,2,3);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into L1(id,uid,zid) values(2147483641,4294967294,300000);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from L1;
    +------------+------------+--------+
    | id         | uid        | zid    |
    +------------+------------+--------+
    |          1 |          2 |  00003 |
    | 2147483641 | 4294967294 | 300000 |
    +------------+------------+--------+
    2 rows in set (0.00 sec)
    
    mysql> insert into L1(id,uid,zid) values(214748364100,4294967294,300000);
    ERROR 1264 (22003): Out of range value for column 'id' at row 1
    mysql>
    

  • tinyint[(m)] [unsigned] [zerofill]

    有符号，取值范围：-128 ～ 127.
    无符号，取值范围：0 ～ 255
    

  • bigint[(m)][unsigned][zerofill]

    有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
    无符号，取值范围：0  ～  18446744073709551615
    

  • decimal[(m[,d])] [unsigned] [zerofill]

    准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
    
    例如：
    create table L2(
    	id int not null primary key auto_increment,
    	salary decimal(8,2)
    )default charset=utf8;
    

    mysql> create table L2(id int not null primary key auto_increment,salary decimal(8,2))default charset=utf8;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into L2(salary) values(1.28);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into L2(salary) values(5.289); -- 自动四舍五入
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> insert into L2(salary) values(5.282);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> insert into L2(salary) values(512132.28);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into L2(salary) values(512132.283);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> select * from L2;
    +----+-----------+
    | id | salary    |
    +----+-----------+
    |  1 |      1.28 |
    |  2 |      5.29 |
    |  3 |      5.28 |
    |  4 | 512132.28 |
    |  5 | 512132.28 |
    +----+-----------+
    5 rows in set (0.00 sec)
    
    mysql> insert into L2(salary) values(5121321.283);
    ERROR 1264 (22003): Out of range value for column 'salary' at row 1
    mysql>
    

  • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

    单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。
    # 底层存储32位二进制
    

  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

    双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
    # 底层存储64位二进制
    

  • char(m)

    定长字符串，m代表字符串的长度，最多可容纳255个字符。
    
    定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：yes，底层也会占用5个字符；如果超出m长度限制（默认MySQL是严格模式，所以会报错）。
        如果在配置文件中加入如下配置，
            sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
        保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。。
    
    注意：默认底层存储是固定的长度（不够则用空格补齐），但是查询数据时，会自动将空白去除。 如果想要保留空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
    查看模式sql-mode，执行命令：show variables  like 'sql_mode';
    
    一般适用于：固定长度的内容。
    
    create table L3(
        id int not null primary key auto_increment,
        name varchar(5),
        depart char(3)
    )default charset=utf8;
    
    insert into L3(name,depart) values("Emmasb","sbEmma");
    
    # 特点：浪费空间，存取速度快。
      select id,name,depart,length(depart) from L3;
    

  • varchar(m)

    变长字符串，m代表字符串的长度，最多可容纳65535个字节。
    
    变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。
          如果在配置文件中加入如下配置，
              sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
          保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。
    
    例如：
    create table L3(
    	id int not null primary key auto_increment,
    	name varchar(5),
    	depart char(3)
    	)default charset=utf8;
    
      # 精准，节省空间，存储速度慢。SQL优化：创建表时，定长的类型往前方，变成的往后放，比如，性别，比如地址或描述信息。
    

    mysql> create table L3(id int not null primary key auto_increment,name varchar(5),depart char(3))default charset=utf8;
    Query OK, 0 rows affected (0.03 sec)
    
    -- 插入多行
    mysql> insert into L3(name,depart) values("wu","WU"),("wupei","ALS");
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from L3;
    +----+-------+--------+
    | id | name  | depart |
    +----+-------+--------+
    |  1 | wu    | WU     |
    |  2 | wupei | ALS    |
    +----+-------+--------+
    2 rows in set (0.00 sec)
    
    -- 非严格模式下，不会报错。
    mysql> insert into L3(name,depart) values("baijuyi","ALS");
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    mysql> insert into L3(name,depart) values("wupei","ALSB");
    ERROR 1406 (22001): Data too long for column 'depart' at row 1
    mysql>
    
    -- 如果 sql-mode 中加入了 PAD_CHAR_TO_FULL_LENGTH ，则查询时char时空白会保留。
    mysql> select name,length(name),depart,length(depart) from L3;
    +-------+--------------+--------+----------------+
    | name  | length(name) | depart | length(depart) |
    +-------+--------------+--------+----------------+
    | wu    |            2 | WU     |              3 |
    | wupei |            5 | ALS    |              3 |
    +-------+--------------+--------+----------------+
    4 rows in set (0.00 sec)
    mysql>
    

  • text

    text数据类型用于保存变长的大字符串，可以最多到65535 (2**16 − 1)个字符。
    
    一般情况下，长文本会用text类型。例如：文章、新闻等。
    

    create table L4(
    	id int not null primary key auto_increment,
        title varchar(128),
    	content text
    )default charset=utf8;
    

  • mediumtext

    A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
    

  • longtext

    A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)
    

  • datetime 不做任何改变，原样输入和输出，常用datetime

    YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）
    

  • timestamp 和时区有关系

    YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）
    

    对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储，查询时，将其又转化为客户端当前时区进行返回。
    
    对于DATETIME，不做任何改变，原样输入和输出。
    

    mysql> create table L5(
        -> id int not null primary key auto_increment,
        -> dt datetime,
        -> tt timestamp
        -> )default charset=utf8;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44");
    
    mysql> select * from L5;
    +----+---------------------+---------------------+
    | id | dt                  | tt                  |
    +----+---------------------+---------------------+
    |  1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 |
    +----+---------------------+---------------------+
    1 row in set (0.00 sec)
    
    # 查询MySQL时区
    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | CST    | 
    | time_zone        | SYSTEM |
    +------------------+--------+
    2 rows in set (0.00 sec)
    -- “CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写，China Standard Time UT+8:00
    
    
    # 修改时区
    mysql> set time_zone='+0:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | CST    |
    | time_zone        | +00:00 |
    +------------------+--------+
    2 rows in set (0.01 sec)
    
    # 再次查询tt已经发生改变
    mysql> select * from L5;
    +----+---------------------+---------------------+
    | id | dt                  | tt                  |
    +----+---------------------+---------------------+
    |  1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 |
    +----+---------------------+---------------------+
    1 row in set (0.00 sec)
    

  • date 年月日

    YYYY-MM-DD（1000-01-01/9999-12-31）
    

  • time 时分秒

    HH:MM:SS（'-838:59:59'/'838:59:59'）
    

  MySQL还有很多其他的数据类型，例如：set、enum、TinyBlob、Blob、MediumBlob、LongBlob 等，详细见官方文档：https://dev.mysql.com/doc/refman/5.7/en/data-types.html

3.2 MySQL代码操作

基于Python去连接MySQL之后，想要进行数据表的管理的话，发送的指令其实都是相同的，例如：

import pymysql

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

# 1. 创建数据库
"""
cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")
conn.commit()
"""

# 2. 进入数据库、查看数据表
"""
cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""

# 3. 进入数据库创建表
cursor.execute("use db4")
sql = """
create table L4(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()

# 4. 查看数据库中的表
"""
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""

# 5. 其他 drop table... 略过


# 关闭连接
cursor.close()
conn.close()

4.数据行

当数据库和数据表创建完成之后，就需要对数据表中的内容进行：增、删、改、查了。

4.1 内置客户端操作

数据行操作的相关SQL语句（指令）如下：

• 新增数据

  insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);
  

  insert into tb1(name,password) values('孙悟空','123123');
  insert into tb1(name,password) values('孙悟空','123123'),('猪八戒','123');
  
  insert into tb1 values('孙悟空','123123'),('猪八戒','123'); -- 如果表中只有2列
  

• 删除数据

  delete from 表名;
  delete from 表名 where 条件;
  

  delete from tb1;
  delete from tb1 where name="baijuyi";
  delete from tb1 where name="baijuyi" and password="123";
  delete from tb1 where id>9;
  delete from tb1 where id=1 and  name="baijuyi";
  

• 修改数据

  update 表名 set 列名=值;
  update 表名 set 列名=值 where 条件;
  

  update tb1 set name="baijuyi"; # 这张表所有的name列都改成baijuyi
  update tb1 set name="baijuyi" where id=1;
  
  update tb1 set age=age+1;  -- age必须整型
  update tb1 set age=age+1 where id=2;
  
  update L3 set name=concat(name,"db"); # 字符串可以使用concat实现拼接
  update L3 set name=concat(name,"123")  where id=2;  -- concat一个函数，可以拼接字符串
  

• 查询数据

  select * from 表名;
  select 列名,列名,列名 from 表名;
  select 列名,列名 as 别名,列名 from 表名;
  select * from 表名 where 条件;
  

  select * from tb1;
  select id,name,age from tb1;
  select id,name as N,age, from tb1;
  select id,name as N,age, 111 from tb1;
  select id,name,111 as age from tb1;
  
  select * from tb1 where id = 1;
  select * from tb1 where id > 1;
  select * from tb1 where id != 1;
  select * from tb1 where name="baijuyi" and password="123";
  

4.2 Python代码操作

import pymysql

# 连接MySQL，自动执行 use userdb; -- 进入数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()


# 1.新增（需commit）
"""
cursor.execute("insert into tb1(name,password) values('白居易','123123')")
conn.commit()
"""

# 2.删除（需commit）
"""
cursor.execute("delete from tb1 where id=1")
conn.commit()
"""

# 3.修改（需commit)
"""
cursor.execute("update tb1 set name='xx' where id=1")
conn.commit()
"""

# 4.查询
"""
cursor.execute("select * from tb where id>10")
data = cursor.fetchone() # cursor.fetchall()
print(data)
"""

# 关闭连接
cursor.close()
conn.close()

其实在真正做项目开发时，流程如下：

• 第一步：根据项目的功能来设计相应的 数据库 & 表结构（不会经常变动，在项目设计之初就确定好了）。
• 第二步：操作表结构中的数据，已达到实现业务逻辑的目的。

例如：实现一个 用户管理系统。

先使用MySQL自带的客户端创建相关 数据库和表结构（相当于先创建好Excel结构）。

create database usersdb default charset utf8 collate utf8_general_ci;

create table users(
	id int not null primary key auto_increment,
    name varchar(32),
	password varchar(64)
)default charset=utf8;

再在程序中执行编写相应的功能实现 注册、登录 等功能。

import pymysql


def register():
    print("用户注册")

    user = input("请输入用户名：") # Emma
    password = input("请输入密码：") # sb

    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()

    # 执行SQL语句（有SQL注入风险，稍后讲解）
    # sql = 'insert into users(name,password)values("Emma","sb")'
    sql = 'insert into users(name,password) values("{}","{}")'.format(user, password)
    
    cursor.execute(sql)
    conn.commit()

    # 关闭数据库连接
    cursor.close()
    conn.close()

    print("注册成功，用户名：{},密码:{}".format(user, password))


def login():
    print("用户登录")

    user = input("请输入用户名：")
    password = input("请输入密码：")

    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()

    # 执行SQL语句（有SQL注入风险，稍后讲解）
    # sql = select * from users where name='baijuyi' and password='123'
    sql = "select * from users where name='{}' and password='{}'".format(user, password)
    cursor.execute(sql)
    
    result = cursor.fetchone() # 去向mysql获取结果
    # 没有返回 None
    # (1,baijuyi,123)
    # fetchone，如果指令（SQL语句）执行后获取了很多行数据，只拿第一行
    # fetchall，如果指令（SQL语句）执行后获取了很多行数据，获取所有数据，没有则返回None，有则以元组的方式返回所有数据。
    
    # 关闭数据库连接
    cursor.close()
    conn.close()

    if result:
        print("登录成功", result)
    else:
        print("登录失败")


def run():
    choice = input("1.注册；2.登录")
    if choice == '1':
        register()
    elif choice == '2':
        login()
    else:
        print("输入错误")


if __name__ == '__main__':
    run()

So，你会发现， 在项目开发时，数据库 & 数据表 的操作其实就做那么一次，最最常写的还是 对数据行 的操作。

5.关于SQL注入

假如，你开发了一个用户认证的系统，应该用户登录成功后才能正确的返回相应的用户结果。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：") # ' or 1=1 -- 
pwd = input("请输入密码：") # 123


conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb')
cursor = conn.cursor()

# 基于字符串格式化来 拼接SQL语句
# sql = "select * from users where name='Emma' and password='123'"
# sql = "select * from users where name='' or 1=1 -- ' and password='123'"
sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
cursor.execute(sql)

result = cursor.fetchone()
print(result) # None，不是None，用户就是存在的

cursor.close()
conn.close()

如果用户在输入user时，输入了： ' or 1=1 -- ，这样即使用户输入的密码不存在，也会可以通过验证。

为什么呢？

因为在SQL拼接时，拼接后的结果是：

select * from users where name='' or 1=1 -- ' and password='123'

注意：在MySQL中 -- 表示注释。

那么，在Python开发中 如何来避免SQL注入呢？

切记，SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')

cursor = conn.cursor()

cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
# 或
# cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd})

result = cursor.fetchone()
print(result)

cursor.close()
conn.close()

# 自动检测非法符号，自动实现转义