【Python】Python 2 和 Python 3 操作 MySQL 数据库实现创建表、删除表、增删改查操作

1、MySQL数据库和表的编码格式

（1）创建数据库并指定字符集

mysql> create database testpythondb character set utf8;
Query OK, 1 row affected, 1 warning (0.60 sec)

（2）查看数据库的编码格式

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.10 sec)

（3）创建表并指定字符集

mysql> create table tb_books (
    -> id varchar(10) not null,
    -> name varchar(20) not null,
    -> author varchar(20) not null,
    -> price double not null) default charset = utf8;
Query OK, 0 rows affected, 1 warning (2.16 sec)

mysql> show tables;
+------------------------+
| Tables_in_testpythondb |
+------------------------+
| tb_books               |
+------------------------+
1 row in set (0.17 sec)

mysql> desc tb_books;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | varchar(10) | NO   |     | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
| author | varchar(20) | NO   |     | NULL    |       |
| price  | double      | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.10 sec)

（4）查看数据表的编码格式

mysql> show create table tb_books;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                          |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `author` varchar(20) NOT NULL,
  `price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

（5）修改数据库的编码格式

mysql> alter database testpythondb character set gbk;
Query OK, 1 row affected (0.17 sec)

mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | gbk   |
+------------------------+-------+
1 row in set (0.06 sec)

（6）修改数据表的编码格式

mysql> alter table tb_books character set gbk;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_books;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                  |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `name` varchar(20) CHARACTER SET utf8 NOT NULL,
  `author` varchar(20) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（7）修改字段的编码格式

mysql> create database testpythondb character set utf8;
mysql> alter table tb_books change name bookname varchar(25) character set gbk not null;
Query OK, 0 rows affected, 1 warning (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc tb_books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | varchar(10) | NO   |     | NULL    |       |
| bookname | varchar(25) | NO   |     | NULL    |       |
| author   | varchar(20) | NO   |     | NULL    |       |
| price    | double      | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table tb_books;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                            |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `id` varchar(10) CHARACTER SET utf8 NOT NULL,
  `bookname` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `author` varchar(20) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（8）添加表外键

mysql> create database testpythondb character set utf8;
mysql> create table tb_bookstore (
    -> storeid varchar(10) not null,
    -> storename varchar(20) not null,
    -> address varchar(50) not null);
Query OK, 0 rows affected (1.02 sec)

mysql> show tables;
+------------------------+
| Tables_in_testpythondb |
+------------------------+
| tb_books               |
| tb_bookstore           |
+------------------------+
2 rows