day22-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步：初始化

1. >>> "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常见的有两种方式：

• 临时启动
  1. >>> "C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe"

  复制代码
  
  注意：此时程序会挂起，内部就是可以接收客户端发来的MySQL指令，关闭窗口或Ctrl+c 就可以停止运行。
  这种启动方式每次开机或想要开启都需要手动执行一遍命令比较麻烦。
  
• 制作windows服务，基于windows服务管理。
  1. >>>"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57

  复制代码
  
  创建好服务之后，可以通过命令 启动和关闭服务，例如：
  1. >>> net start mysql57
  2. >>> net stop mysql57

  复制代码
  也可以在window的服务管理中点击按钮启动和关闭服务。例如：
  
  
  
  
  
  
  以后不再想要使用window服务了，也可以将制作的这个MySQL服务删除。
  1. >>>"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 脚本（建议）
  1. sudo /usr/local/mysql/support-files/mysql.server start
  2. # 输入电脑密码
  4. sudo mysql.server start
  5. # 输入电脑密码

  复制代码
  1. sudo /usr/local/mysql/support-files/mysql.server stop

  复制代码
  
  为了避免每次执行命令都需要些路径，可以将路径 /usr/local/mysql/support-files加入到环境变量中。
  
  
  操作完成之后，再在终端执行下命令：source ~/.zprofile 让设置的环境变量立即生效。
  注意：mac系统的版本如果比较老，会显示空白的 zprofile 文件，此就要去打开   bash_profile 文件。
  这样设置好之后，以后就可以使用下面的命令去启动和关闭MySQL了。
  1. sudo mysql.server start
  2. 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
  1. [mysqld]
  2. ...
  3. skip-grant-tables=1
  4. ...

  复制代码
• 重启MySQL，再次登录时，不需要密码直接可以进去了
  
  
  
  • windows重启
    1. net stop mysql57
    2. net start mysql57

    复制代码
  • mac重启
    1. sudo mysql.server restart

    复制代码
  重启后，无需密码就可以进入。
  1. >>> mysql -u root -p

  复制代码
• 进入数据库后执行修改密码命令
  1. use mysql;
  2. update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';

  复制代码
• 退出并再次修改配置文件，删除 [mysqld] 节点下的 skip-grant-tables=1
  1. [mysqld]
  2. ...
  3. # skip-grant-tables=1
  4. ...

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

2. 数据库 管理

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

如果将数据库管理系统与之前的文件管理做类比的话：
数据库管理系统文件管理数据库文件夹数据表文件夹下的excel文件接下来，我们先学习 数据库（文件夹）相关操作的指令。

2.1 内置客户端操作

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

• 查看当前所有的数据库：  show databases;
  
• 创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
  1. create database day25db;
  3. create database day25db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

  复制代码
• 删除数据库：drop database 数据库名;
  
• 进入数据（进入文件）：use 数据库;
  

示例：

1. # 1.登录MySQL
2. wupeiqi@wupeiqideMBP ~ % /usr/local/mysql/bin/mysql -u root -p
3. Enter password:
4. Welcome to the MySQL monitor.  Commands end with ; or \g.
5. Your MySQL connection id is 5
6. Server version: 5.7.31 MySQL Community Server (GPL)
8. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
10. Oracle is a registered trademark of Oracle Corporation and/or its
11. affiliates. Other names may be trademarks of their respective
12. owners.
14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16. # 2.查看当前数据库
17. mysql> show databases;
18. +--------------------+
19. | Database           |
20. +--------------------+
21. | information_schema |
22. | mysql              |
23. | performance_schema |
24. | sys                |
25. +--------------------+
26. 4 rows in set (0.00 sec)
28. # 3. 创建数据库:  create database 数据库名 default charset 编码 collate 排序规则;
29. mysql> create database db1 default charset utf8 collate utf8_general_ci;
30. Query OK, 1 row affected (0.00 sec)
32. mysql> show databases;
33. +--------------------+
34. | Database           |
35. +--------------------+
36. | information_schema |
37. | db1                |
38. | mysql              |
39. | performance_schema |
40. | sys                |
41. +--------------------+
42. 5 rows in set (0.01 sec)
44. # 4. 删除数据库
45. mysql> drop database db1;
46. Query OK, 0 rows affected (0.00 sec)
48. # 5. 查看当前数据库
49. mysql> show databases;
50. +--------------------+
51. | Database           |
52. +--------------------+
53. | information_schema |
54. | mysql              |
55. | performance_schema |
56. | sys                |
57. +--------------------+
58. 4 rows in set (0.00 sec)
60. # 6. 进入数据库
61. mysql> use mysql;
62. Reading table information for completion of table and column names
63. You can turn off this feature to get a quicker startup with -A
65. Database changed
66. # 7. 进入mysql数据库（文件夹），查看此数据库下的所有表。
67. mysql> show tables;
68. +---------------------------+
69. | Tables_in_mysql           |
70. +---------------------------+
71. | columns_priv              |
72. | db                        |
73. | engine_cost               |
74. | event                     |
75. | func                      |
76. | general_log               |
77. | gtid_executed             |
78. | help_category             |
79. | help_keyword              |
80. | help_relation             |
81. | help_topic                |
82. | innodb_index_stats        |
83. | innodb_table_stats        |
84. | ndb_binlog_index          |
85. | plugin                    |
86. | proc                      |
87. | procs_priv                |
88. | proxies_priv              |
89. | server_cost               |
90. | servers                   |
91. | slave_master_info         |
92. | slave_relay_log_info      |
93. | slave_worker_info         |
94. | slow_log                  |
95. | tables_priv               |
96. | time_zone                 |
97. | time_zone_leap_second     |
98. | time_zone_name            |
99. | time_zone_transition      |
100. | time_zone_transition_type |
101. | user                      |
102. +---------------------------+
103. 31 rows in set (0.00 sec)
105. # 8. 退出
106. mysql>exit;

复制代码

2.2 Python代码操作

无论通过何种方式去连接MySQL，本质上发送的 指令 都是相同的，只是连接的方式和操作形式不同而已。
当连接上MySQL之后，执行如下指令，就可以对MySQL的数据进行操作。（同上述过程）

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

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

1. pip3 install pymysql

复制代码

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

1. import pymysql
3. # 连接MySQL（socket）
4. conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
5. cursor = conn.cursor()
7. # 1. 查看数据库
8. # 发送指令
9. cursor.execute("show databases")
10. # 获取指令的结果
11. result = cursor.fetchall()
12. print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
14. # 2. 创建数据库（新增、删除、修改）
15. # 发送指令
16. cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
17. conn.commit()
19. # 3. 查看数据库
20. # 发送指令
21. cursor.execute("show databases")
22. # 获取指令的结果
23. result = cursor.fetchall()
24. print(result) # (('information_schema',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))
26. # 4. 删除数据库
27. # 发送指令
28. cursor.execute("drop database db3")
29. conn.commit()
31. # 3. 查看数据库
32. # 发送指令
33. cursor.execute("show databases")
34. # 获取指令的结果
35. result = cursor.fetchall()
36. print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
38. # 5. 进入数据库，查看表
39. # 发送指令
40. cursor.execute("use mysql")
41. cursor.execute("show tables")
42. result = cursor.fetchall()
43. print(result) # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),....
45. # 关闭连接
46. cursor.close()
47. conn.close()

复制代码

3. 数据表 管理

如果将数据库管理系统与之前的文件管理做类比的话：
数据库管理系统文件管理数据库文件夹数据表文件夹下的文件接下来，我们先学习 数据表（文件夹中的文件）相关操作的指令。

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

数据表常见操作的指令：

• 进入数据库 use 数据库;，查看当前所有表：show tables;
  
• 创建表结构
  
  1. create table 表名(
  2.     列名  类型,
  3.     列名  类型,
  4.     列名  类型
  5. )default charset=utf8;

  复制代码
  1. create table tb1(
  2.         id int,
  3.     name varchar(16)
  4. )default charset=utf8;

  复制代码
  1. create table tb2(
  2.         id int,
  3.     name varchar(16) not null,   -- 不允许为空
  4.     email varchar(32) null,      -- 允许为空（默认）
  5.     age int
  6. )default charset=utf8;

  复制代码
  1. create table tb3(
  2.         id int,
  3.     name varchar(16) not null,   -- 不允许为空
  4.     email varchar(32) null,      -- 允许为空（默认）
  5.     age int default 3            -- 插入数据时，如果不给age列设置值，默认值：3
  6. )default charset=utf8;

  复制代码
  1. create table tb4(
  2.         id int primary key,                         -- 主键（不允许为空、不能重复）
  3.     name varchar(16) not null,   -- 不允许为空
  4.     email varchar(32) null,      -- 允许为空（默认）
  5.     age int default 3            -- 插入数据时，如果不给age列设置值，默认值：3
  6. )default charset=utf8;

  复制代码
  主键一般用于表示当前这条数据的ID编号（类似于人的身份证），需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。
  1. create table tb5(
  2.         id int not null auto_increment primary key,        -- 不允许为空 & 主键 & 自增
  3.     name varchar(16) not null,                   -- 不允许为空
  4.     email varchar(32) null,                      -- 允许为空（默认）
  5.     age int default 3                            -- 插入数据时，如果不给age列设置值，默认值：3
  6. )default charset=utf8;

  复制代码
  注意：一个表中只能有一个自增列【自增列，一般都是主键】。
  
• 删除表 drop table 表名;
  
• 清空表 delete from 表名; 或 truncate table 表名;（速度快、无法回滚撤销等）
  
• 修改表
  
  
  
  • 添加列
    1. alter table 表名 add 列名 类型;
    2. alter table 表名 add 列名 类型 DEFAULT 默认值;
    3. alter table 表名 add 列名 类型 not null default 默认值;
    4. alter table 表名 add 列名 类型 not null primary key auto_increment;

    复制代码
  • 删除列
    1. alter table 表名 drop column 列名;

    复制代码
  • 修改列 类型
    1. alter table 表名 modify column 列名 类型;

    复制代码
  • 修改列 类型 + 名称
    1. alter table 表名 change 原列名 新列名 新类型;

    复制代码
    1. alter table  tb change id nid int not null;
    2. alter table  tb change id id int not null default 5;
    3. alter table  tb change id id int not null primary key auto_increment;
    5. alter table  tb change id id int; -- 允许为空，删除默认值，删除自增。

    复制代码
  • 修改列 默认值
    1. ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

    复制代码
  • 删除列 默认值
    1. ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

    复制代码
  • 添加主键
    1. alter table 表名 add primary key(列名);

    复制代码
  • 删除主键
    1. alter table 表名 drop primary key;

    复制代码
  
  
• 常见列类型
  1. create table 表(
  2.         id int,
  3.     name varchar(16)
  4. )default charset=utf8;

  复制代码
  
  
  • int[(m)][unsigned][zerofill]
    1. int                                表示有符号，取值范围：-2147483648 ～ 2147483647
    2. int unsigned        表示无符号，取值范围：0 ～ 4294967295
    3. int(5)zerofill        仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

    复制代码
    1. mysql> create table L1(id int, uid int unsigned, zid int(5) zerofill) default charset=utf8;
    2. Query OK, 0 rows affected (0.03 sec)
    4. mysql> insert into L1(id,uid,zid) values(1,2,3);
    5. Query OK, 1 row affected (0.00 sec)
    7. mysql> insert into L1(id,uid,zid) values(2147483641,4294967294,300000);
    8. Query OK, 1 row affected (0.00 sec)
    10. mysql> select * from L1;
    11. +------------+------------+--------+
    12. | id         | uid        | zid    |
    13. +------------+------------+--------+
    14. |          1 |          2 |  00003 |
    15. | 2147483641 | 4294967294 | 300000 |
    16. +------------+------------+--------+
    17. 2 rows in set (0.00 sec)
    19. mysql> insert into L1(id,uid,zid) values(214748364100,4294967294,300000);
    20. ERROR 1264 (22003): Out of range value for column 'id' at row 1
    21. mysql>

    复制代码
  • tinyint[(m)] [unsigned] [zerofill]
    1. 有符号，取值范围：-128 ～ 127.
    2. 无符号，取值范围：0 ～ 255

    复制代码
  • bigint[(m)][unsigned][zerofill]
    1. 有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
    2. 无符号，取值范围：0  ～  18446744073709551615

    复制代码
  • decimal[(m[,d])] [unsigned] [zerofill]
    1. 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
    3. 例如：
    4. create table L2(
    5.         id int not null primary key auto_increment,
    6.         salary decimal(8,2)
    7. )default charset=utf8;

    复制代码
    1. mysql> create table L2(id int not null primary key auto_increment,salary decimal(8,2))default charset=utf8;
    2. Query OK, 0 rows affected (0.03 sec)
    4. mysql> insert into L2(salary) values(1.28);
    5. Query OK, 1 row affected (0.01 sec)
    7. mysql> insert into L2(salary) values(5.289);
    8. Query OK, 1 row affected, 1 warning (0.00 sec)
    10. mysql> insert into L2(salary) values(5.282);
    11. Query OK, 1 row affected, 1 warning (0.00 sec)
    13. mysql> insert into L2(salary) values(512132.28);
    14. Query OK, 1 row affected (0.00 sec)
    16. mysql> insert into L2(salary) values(512132.283);
    17. Query OK, 1 row affected, 1 warning (0.00 sec)
    19. mysql> select * from L2;
    20. +----+-----------+
    21. | id | salary    |
    22. +----+-----------+
    23. |  1 |      1.28 |
    24. |  2 |      5.29 |
    25. |  3 |      5.28 |
    26. |  4 | 512132.28 |
    27. |  5 | 512132.28 |
    28. +----+-----------+
    29. 5 rows in set (0.00 sec)
    31. mysql> insert into L2(salary) values(5121321.283);
    32. ERROR 1264 (22003): Out of range value for column 'salary' at row 1
    33. mysql>

    复制代码
  • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
    1. 单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。

    复制代码
  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
    1. 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

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

    复制代码
  • varchar(m)
    1. 变长字符串，m代表字符串的长度，最多可容纳65535个字节。
    3. 变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。
    4.     如果在配置文件中加入如下配置，
    5.         sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    6.     保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。
    8. 例如：
    9. create table L3(
    10.     id int not null primary key auto_increment,
    11.     name varchar(5),
    12.     depart char(3)
    13. )default charset=utf8;

    复制代码
    1. mysql> create table L3(id int not null primary key auto_increment,name varchar(5),depart char(3))default charset=utf8;
    2. Query OK, 0 rows affected (0.03 sec)
    4. -- 插入多行
    5. mysql> insert into L3(name,depart) values("wu","WU"),("wupei","ALS");
    6. Query OK, 2 rows affected (0.00 sec)
    7. Records: 2  Duplicates: 0  Warnings: 0
    9. mysql> select * from L3;
    10. +----+-------+--------+
    11. | id | name  | depart |
    12. +----+-------+--------+
    13. |  1 | wu    | WU     |
    14. |  2 | wupei | ALS    |
    15. +----+-------+--------+
    16. 2 rows in set (0.00 sec)
    18. -- 非严格模式下，不会报错。
    19. mysql> insert into L3(name,depart) values("wupeiqi","ALS");
    20. ERROR 1406 (22001): Data too long for column 'name' at row 1
    21. mysql> insert into L3(name,depart) values("wupei","ALSB");
    22. ERROR 1406 (22001): Data too long for column 'depart' at row 1
    23. mysql>
    25. -- 如果 sql-mode 中加入了 PAD_CHAR_TO_FULL_LENGTH ，则查询时char时空白会保留。
    26. mysql> select name,length(name),depart,length(depart) from L3;
    27. +-------+--------------+--------+----------------+
    28. | name  | length(name) | depart | length(depart) |
    29. +-------+--------------+--------+----------------+
    30. | wu    |            2 | WU     |              3 |
    31. | wupei |            5 | ALS    |              3 |
    32. +-------+--------------+--------+----------------+
    33. 4 rows in set (0.00 sec)
    34. mysql>

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

    复制代码
    1. create table L4(
    2.         id int not null primary key auto_increment,
    3.     title varchar(128),
    4.         content text
    5. )default charset=utf8;

    复制代码
  • mediumtext
    1. A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

    复制代码
  • longtext
    1. A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)

    复制代码
  • datetime
    1. YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

    复制代码
  • timestamp
    1. YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）

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

    复制代码
    1. mysql> create table L5(
    2.     -> id int not null primary key auto_increment,
    3.     -> dt datetime,
    4.     -> tt timestamp
    5.     -> )default charset=utf8;
    6. Query OK, 0 rows affected (0.03 sec)
    8. mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44");
    10. mysql> select * from L5;
    11. +----+---------------------+---------------------+
    12. | id | dt                  | tt                  |
    13. +----+---------------------+---------------------+
    14. |  1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 |
    15. +----+---------------------+---------------------+
    16. 1 row in set (0.00 sec)
    18. mysql> show variables like '%time_zone%';
    19. +------------------+--------+
    20. | Variable_name    | Value  |
    21. +------------------+--------+
    22. | system_time_zone | CST    |
    23. | time_zone        | SYSTEM |
    24. +------------------+--------+
    25. 2 rows in set (0.00 sec)
    26. -- “CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写，China Standard Time UT+8:00
    28. mysql> set time_zone='+0:00';
    29. Query OK, 0 rows affected (0.00 sec)
    31. mysql> show variables like '%time_zone%';
    32. +------------------+--------+
    33. | Variable_name    | Value  |
    34. +------------------+--------+
    35. | system_time_zone | CST    |
    36. | time_zone        | +00:00 |
    37. +------------------+--------+
    38. 2 rows in set (0.01 sec)
    40. mysql> select * from L5;
    41. +----+---------------------+---------------------+
    42. | id | dt                  | tt                  |
    43. +----+---------------------+---------------------+
    44. |  1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 |
    45. +----+---------------------+---------------------+
    46. 1 row in set (0.00 sec)

    复制代码
  • date
    1. YYYY-MM-DD（1000-01-01/9999-12-31）

    复制代码
  • time
    1. 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之后，想要进行数据表的管理的话，发送的指令其实都是相同的，例如：

1. import pymysql
3. # 连接MySQL
4. conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
5. cursor = conn.cursor()
7. # 1. 创建数据库
8. """
9. cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")
10. conn.commit()
11. """
13. # 2. 进入数据库、查看数据表
14. """
15. cursor.execute("use db4")
16. cursor.execute("show tables")
17. result = cursor.fetchall()
18. print(result)
19. """
21. # 3. 进入数据库创建表
22. cursor.execute("use db4")
23. sql = """
24. create table L4(
25.     id int not null primary key auto_increment,
26.     title varchar(128),
27.     content text,
28.     ctime datetime
29. )default charset=utf8;
30. """
31. cursor.execute(sql)
32. conn.commit()
34. # 4. 查看数据库中的表
35. """
36. cursor.execute("show tables")
37. result = cursor.fetchall()
38. print(result)
39. """
41. # 5. 其他 drop table... 略过
44. # 关闭连接
45. cursor.close()
46. conn.close()

复制代码

4.数据行

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

4.1 内置客户端操作

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

• 新增数据
  1. insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);

  复制代码
  1. insert into tb1(name,password) values('武沛齐','123123');
  2. insert into tb1(name,password) values('武沛齐','123123'),('alex','123');
  4. insert into tb1 values('武沛齐','123123'),('alex','123'); -- 如果表中只有2列

  复制代码
• 删除数据
  1. delete from 表名;
  2. delete from 表名 where 条件;

  复制代码
  1. delete from tb1;
  2. delete from tb1 where name="wupeiqi";
  3. delete from tb1 where name="wupeiqi" and password="123";
  4. delete from tb1 where id>9;

  复制代码
• 修改数据
  1. update 表名 set 列名=值;
  2. update 表名 set 列名=值 where 条件;

  复制代码
  1. update tb1 set name="wupeiqi";
  2. update tb1 set name="wupeiqi" where id=1;
  4. update tb1 set age=age+1;  -- 整型
  5. update tb1 set age=age+1 where id=2;
  7. update L3 set name=concat(name,"db");
  8. update L3 set name=concat(name,"123")  where id=2;  -- concat一个函数，可以拼接字符串

  复制代码
• 查询数据
  1. select * from 表名;
  2. select 列名,列名,列名 from 表名;
  3. select 列名,列名 as 别名,列名 from 表名;
  4. select * from 表名 where 条件;

  复制代码
  1. select * from tb1;
  2. select id,name,age from tb1;
  3. select id,name as N,age, from tb1;
  4. select id,name as N,age, 111 from tb1;
  6. select * from tb1 where id = 1;
  7. select * from tb1 where id > 1;
  8. select * from tb1 where id != 1;
  9. select * from tb1 where name="wupeiqi" and password="123";

  复制代码

4.2 Python代码操作

1. import pymysql
3. # 连接MySQL，自动执行 use userdb; -- 进入数据库
4. conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
5. cursor = conn.cursor()
8. # 1.新增（需commit）
9. """
10. cursor.execute("insert into tb1(name,password) values('武沛齐','123123')")
11. conn.commit()
12. """
14. # 2.删除（需commit）
15. """
16. cursor.execute("delete from tb1 where id=1")
17. conn.commit()
18. """
20. # 3.修改（需commit)
21. """
22. cursor.execute("update tb1 set name='xx' where id=1")
23. conn.commit()
24. """
26. # 4.查询
27. """
28. cursor.execute("select * from tb where id>10")
29. data = cursor.fetchone() # cursor.fetchall()
30. print(data)
31. """
33. # 关闭连接
34. cursor.close()
35. conn.close()

复制代码

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

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

例如：实现一个 用户管理系统。
先使用MySQL自带的客户端创建相关 数据库和表结构（相当于先创建好Excel结构）。

1. create database usersdb default charset utf8 collate utf8_general_ci;

复制代码

1. create table users(
2.     id int not null primary key auto_increment,
3.     name varchar(32),
4.     password varchar(64)
5. )default charset=utf8;

复制代码

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

1. import pymysql
4. def register():
5.     print("用户注册")
7.     user = input("请输入用户名：") # alex
8.     password = input("请输入密码：") # sb
10.     # 连接指定数据
11.     conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
12.     cursor = conn.cursor()
14.     # 执行SQL语句（有SQL注入风险，稍后讲解）
15.     # sql = 'insert into users(name,password)values("alex","sb")'
16.     sql = 'insert into users(name,password) values("{}","{}")'.format(user, password)
17.    
18.     cursor.execute(sql)
19.     conn.commit()
21.     # 关闭数据库连接
22.     cursor.close()
23.     conn.close()
25.     print("注册成功，用户名：{},密码:{}".format(user, password))
28. def login():
29.     print("用户登录")
31.     user = input("请输入用户名：")
32.     password = input("请输入密码：")
34.     # 连接指定数据
35.     conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
36.     cursor = conn.cursor()
38.     # 执行SQL语句（有SQL注入风险，稍后讲解）
39.     # sql = select * from users where name='wupeiqi' and password='123'
40.     sql = "select * from users where name='{}' and password='{}'".format(user, password)
41.     cursor.execute(sql)
42.    
43.     result = cursor.fetchone() # 去向mysql获取结果
44.     # None
45.     # (1,wupeiqi,123)
46.    
47.    
48.     # 关闭数据库连接
49.     cursor.close()
50.     conn.close()
52.     if result:
53.         print("登录成功", result)
54.     else:
55.         print("登录失败")
58. def run():
59.     choice = input("1.注册；2.登录")
60.     if choice == '1':
61.         register()
62.     elif choice == '2':
63.         login()
64.     else:
65.         print("输入错误")
68. if __name__ == '__main__':
69.     run()

复制代码

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

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

1. import pymysql
3. # 输入用户名和密码
4. user = input("请输入用户名：") # ' or 1=1 --
5. pwd = input("请输入密码：") # 123
8. conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb')
9. cursor = conn.cursor()
11. # 基于字符串格式化来 拼接SQL语句
12. # sql = "select * from users where name='alex' and password='123'"
13. # sql = "select * from users where name='' or 1=1 -- ' and password='123'"
14. sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
15. cursor.execute(sql)
17. result = cursor.fetchone()
18. print(result) # None，不是None
20. cursor.close()
21. conn.close()

复制代码

如果用户在输入user时，输入了：   ' or 1=1 --      ，这样即使用户输入的密码不存在，也会可以通过验证。
为什么呢？
因为在SQL拼接时，拼接后的结果是：

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

复制代码

注意：在MySQL中 -- 表示注释。
那么，在Python开发中 如何来避免SQL注入呢？
切记，SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法。

1. import pymysql
3. # 输入用户名和密码
4. user = input("请输入用户名：")
5. pwd = input("请输入密码：")
7. conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
9. cursor = conn.cursor()
11. cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
12. # 或
13. # cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd})
15. result = cursor.fetchone()
16. print(result)
18. cursor.close()
19. conn.close()

复制代码

总结

除了 【第5步 用户授权】以外，现在使用的是默认root账户（拥有最大权限），上述所有的过程已讲解完毕。
本节内容大家需要掌握：

• 安装和启动MySQL
  
• SQL语句：
  
  
  
  • 数据库操作
    
  • 表操作
    
  • 数据行操作
    
  
  
• 基于Python操作MySQL
  
• 注意SQL注入的问题
  

作业

• 根据要求 创建表 结构并编写相应的SQL语句（基于MySQL自带客户端操作）
  列名类型备注idint不为空 & 自增 & 主键namevarchar(32)不为空passwordvarchar(64)不为空genderchar(1)不为空，支持：男、女emailvarchar(64)可以为空amountdecimal(10,2)不为空 & 默认值为 0ctimedatetime新增时的时间
  提示：可基于datetime模块实现
  
  
  
  • 根据上述表的要求创建相应的数据和表结构（注意编码）。
    
  
  

1. create table admin(
2.   id int not null auto_increment primary key,        -- 不允许为空 & 主键 & 自增
3.   name varchar(32) not null,
4.   password varchar(64) not null,
5.   gender char(1) not null,
6.   email varchar(64) null,
7.   amount decimal(10,2) not null default 0,
8.   ctime datetime
9. )default charset=utf8;

复制代码

• 任意插入5条数据。
  

1. insert into admin(name,password,gender,email,amount,ctime) values("武沛齐","123123","男","xxx@live.com",19991.12,NOW());
2. insert into admin(name,password,gender,email,amount,ctime) values("alex","sb","男","alex@live.com",991.12,NOW());
3. insert into admin(name,password,gender,email,amount,ctime) values("eric","8888","女","eric@live.com",991.12,NOW());
4. insert into admin(name,password,gender,email,amount,ctime) values("tony","123123123","女","xxxxxxxx@live.com",200.12,NOW()), ("kelly","8888","女","kelly@live.com",991.12,NOW());

复制代码

• 将 id>3的所有人的性别改为  男。
  

1. update admin set gender="男" where id>3;

复制代码

• 查询余额 amount>1000的所有用户。
  

1. select * from admin where amount>1000;

复制代码

• 让每个人的余额在自己原的基础上 +1000 。
  

1. update admin set amount=amount+1000;

复制代码

• 删除性别为男的所有数据。
  

1. delete from admin where gender="男";

复制代码

• 通过Python代码实现上述除了第一个以外的操作。
  1. 插入5条数据时，ctime那一列不要自己写“2021-11-11.。。” 而是使用datatime模块生成当前时间。

  复制代码
  1. import pymysql
  2. import datetime
  4. # 1.连接数据库
  5. conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", db="test", charset="utf8")
  6. cursor = conn.cursor()
  8. # 验证语句，执行完每次操作都查询一下
  9. sql = 'select * from admin;'
  11. # 2.任意插入5条数据。
  12. insert_sql = 'insert into admin(name,password,gender,email,amount,ctime) values(%s,%s,%s,%s,%s,%s);'
  13. # 单独增加一条数据
  14. cursor.execute(insert_sql, ["武沛齐", "123123", "男", "xxx@live.com", 19991.12, datetime.datetime.now()])
  15. conn.commit()
  16. # 一次性增加多条数据
  17. cursor.executemany(insert_sql, [
  18.    ["alex", "sb", "男", "alex@live.com", 991.12, datetime.datetime.now()],
  19.    ["eric", "8888", "女", "eric@live.com", 991.12, datetime.datetime.now()],
  20.    ["tony", "123123123", "女", "xxxxxxxx@live.com", 200.12, datetime.datetime.now()],
  21.    ["kelly", "8888", "女", "kelly@live.com", 991.12, datetime.datetime.now()],
  22. ])
  23. conn.commit()
  24. cursor.execute(sql)
  25. data = cursor.fetchall()
  26. print(data)
  28. # 3.将 id>3 的所有人的性别改为  男。
  29. update_sql = 'update admin set gender="男" where id >3;'
  30. cursor.execute(update_sql)
  31. conn.commit()
  32. cursor.execute(sql)
  33. data = cursor.fetchall()
  34. print(data)
  36. # 4.查询余额 amount>1000 的所有用户
  37. select_sql = 'select * from admin where amount >1000;'
  38. cursor.execute(select_sql)
  39. result = cursor.fetchone()
  40. print(result)
  42. # 5. 让每个人的余额在自己原的基础上 +1000 。
  43. add_sql = 'update admin set amount=amount+1000;'
  44. cursor.execute(add_sql)
  45. conn.commit()
  46. cursor.execute(sql)
  47. data = cursor.fetchall()
  48. print(data)
  50. # 6. 删除性别为男的所有数据。
  51. delete_sql = ' delete from admin where gender="男";'
  52. cursor.execute(delete_sql)
  53. conn.commit()
  54. cursor.execute(sql)
  55. data = cursor.fetchall()
  56. print(data)
  58. cursor.close()
  59. conn.close()

  复制代码

来源:https://www.cnblogs.com/sbhglqy/p/18155011
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！