MySQL2:数据库管理_ridk enable mysql2-CSDN博客

本文链接：https://blog.csdn.net/generalfuck/article/details/129001280

数据库管理

1、前言

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

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

2、sql语句

2.1通过内置客户端实现操作

查看当前所有的数据库：show databases;
创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;(指定编码和排序规则)
删除数据库：drop database 数据库名;
进入数据（进入文件）：use 数据库;

mysql> create database testdb default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

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

mysql> use testdb
Database changed

mysql> show tables;
Empty set (0.01 sec)

mysql> drop database testdb;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

2.2通过python实现操作

想要使用python操作MySQL需要安装第三方库（pymysql）

pip insatll pymysql

import pymysql

# 连接MySQL（基于socket）
db = pymysql.connect(host = "127.0.0.1", port = 3306, user = "root", passwd = "root", charset = "utf8")
# 获取操作游标
cursor = db.cursor()

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

# 2.创建数据库（增、删、改）
# 发送指令
cursor.execute("create database testdb default charset utf8 collate utf8_general_ci")
# db.commit()
cursor.execute("show databases")
print(cursor.fetchall())  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',), ('testdb',))

# 3.删除数据库
cursor.execute("drop database testdb")
cursor.execute("show databases")
print(cursor.fetchall())  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 4.进入数据库，查看表
cursor.execute("use mysql")
cursor.execute("show tables")
data = cursor.fetchall()
print("------------------------------------------------------------------")
print(data)

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

(('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
(('information_schema',), ('mysql',), ('performance_schema',), ('sys',), ('testdb',))
(('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
---------------------------------------------------------------
(('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',))

3、数据表的管理

3.1、内置客户端操作

3.1.1 、创建表

创建一个数据表

-- 规则
create table 表名(
    列名 类型，
    列名 类型，
    列名 类型
)default charset=utf8;

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

指定数据表数据

create table tb2(
    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 tb3(
    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;

查看表有几列，分别是什么类型

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.1.2、删除表

删除表drop table 表名;
清空表（删除表的内容）delete from 表名;或truncate table 表名;（速度块，无法回滚撤销等）

3.1.3、修改表

添加列

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

mysql> alter table tb2 add age int;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除列

alter table 表名 drop column 列名;

mysql> alter table tb2 drop column age;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改列–类型

alter table 表名 modify column 列名 新类型;

mysql> alter table tb2 modify column age char;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改列–类型+名称

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

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb2 change column age gender varchar(16);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(16) | YES  |     | NULL    |       |
| gender | varchar(16) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

mysql> desc tb2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(16) | YES  |     | NULL    |       |
| gender | varchar(16) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> alter table tb2 change gender age int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改列–默认值

alter table 表名 alter 列名 set default 1000;

mysql> alter table tb2 alter age set default 3;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | 3       |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除列–默认值

alter table 表名 alter 列名 drop default;

mysql> alter table tb2 alter age drop default;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除行

delete from 表名 where 主键 = "具体值";

mysql> delete from tb_char where id="1";
Query OK, 1 row affected (0.02 sec)

mysql> select * from tb_cchar;
ERROR 1146 (42S02): Table 'testdb1.tb_cchar' doesn't exist
mysql> select * from tb_char;
+----+------+
| id | name |
+----+------+
|  2 | a    |
|  3 | ab   |
|  4 | abc  |
|  5 | abcd |
+----+------+
4 rows in set (0.00 sec)

插入行

mysql> insert into tb_char(id,name) values("1","abcd");
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_char;
+----+------+
| id | name |
+----+------+
|  1 | abcd |
|  2 | a    |
|  3 | ab   |
|  4 | abc  |
+----+------+
4 rows in set (0.00 sec)

添加主键

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

mysql> alter table tb2 add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除主键

alter table 表名 drop primary key;

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb2 drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4、常见数据类型

4.1、整型 int

常见列类型

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，例如：00001；满足时，正常显示，例如：3000000

mysql> create table tb1(id int, uid int unsigned, zid int(5) zerofill)default charset=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> desc tb1;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(11)                  | YES  |     | NULL    |       |
| uid   | int(10) unsigned         | YES  |     | NULL    |       |
| zid   | int(5) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into tb1(id,uid,zid) values(1,2,3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+------+------+-------+
| id   | uid  | zid   |
+------+------+-------+
|    1 |    2 | 00003 |
+------+------+-------+
1 row in set (0.01 sec)

-- 数据超过指定类型长度会报错
mysql> insert into tb1(id,uid,zid) values(2222222222,4444444444,300000);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into tb1(id,uid,zid) values(222222222,4444444444,300000);
ERROR 1264 (22003): Out of range value for column 'uid' at row 1

mysql> insert into tb1(id,uid,zid) values(222222222,444444444,300000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+-----------+-----------+--------+
| id        | uid       | zid    |
+-----------+-----------+--------+
|         1 |         2 |  00003 |
| 222222222 | 444444444 | 300000 |
+-----------+-----------+--------+
2 rows in set (0.00 sec)

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

tinyint            -- 有符号，取值范围： -128 ~ 127
tinyint unsigned   -- 无符号，取值范围： 0 ~ 255

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

bigint            -- 有符号，取值范围： -9223372036854775808 ~ 9223372036854775807
bigint unsigned   -- 无符号，取值范围： 0 ~ 18446744073709551615

总结：bigint > int > tinyint

4.2、小数 decimal（十进制）

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

-- 准确的小数值，m是数字的总个数（符号不算），d是小数点后的个数
-- m最大值位65，d最大值位30

create table tb3(
    id int not null primary key auto_increment,
    salary decimal(8,2)
)default charset=utf8;

mysql> insert into tb3(salary) values(1.11);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb3(salary) values(2.22);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb3;
+----+--------+
| id | salary |
+----+--------+
|  1 |   1.11 |
|  2 |   2.22 |
+----+--------+
2 rows in set (0.01 sec)

当超过预设长度时

mysql> insert into tb3(salary) values(111111111.11);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1

4.3、浮点数 float double

注意：不推荐使用，小数建议使用 decimal

float[(m,d)] [unsigned] [zerofill]
- 单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数
- float存储的是32位二进制数
double[(m,d)] [unsigned] [zerofill]
- 双精度浮点数，非精准小数值，m是数字总个数，d是小数点后个数
- double存储的是64位二进制数

4.4、字符串 char varchar

char(m)

-- 定长字符串，m代表字符串的长度，最多可容纳255个字符

-- 定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：abc，底层（内存）也会占用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 tb_char(
    id int not null primary key auto_increment, 
    name char(4)
)default charset=utf8;

varchar(m)

-- 变长字符串，m代表字符串的长度，最多可容纳65535个字符
-- 变长的体现：内容小于m时，会按照真实数据长度存储；如果超过m长度限制（默认MySQL是严格模式，就会报错）

-- 如果在配置文件中添加如下配置（不建议使用）：
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
-- 保存并重启，此时MySQL则是非严格模式，超过长度则自动截断（不报错）

create table tb_char(
    id int not null primary key auto_increment, 
    name varchar(4)
)default charset=utf8;

4.5、文本类型 text mediumtext longtext

一般情况下，长文本会用text类型，例如：文章，博客等

TINYTEXT	256 bytes
TEXT	65,535 bytes	64kb
MEDIUMTEXT	16,777,215 bytes	16mb
LONGTEXT	4,294,967,295 bytes	4gb

4.6、时间 datetime timestamp date time

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（世界标准时间（时间戳））进行存储，查询时，将时间戳转换为客户端当前时区的时间进行显示

对于datetiem，不做任何改变，原样输出

create table tb_time(
    id int not null primary key auto_increment,
    dtime datetime,
    ttime timestamp
)default charset=utf8;

mysql> insert into tb_time(dtime,ttime) values("2021-07-11 22:00:00", "2021-07-11 22:00:00");
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_time;
+----+---------------------+---------------------+
| id | dtime               | ttime               |
+----+---------------------+---------------------+
|  1 | 2021-07-11 22:00:00 | 2021-07-11 22:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

查看当前时区

show variables like "%time_zone%";

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)

-- 显示为当前系统的时区

修改时区后，查看 datetime timestamp 的区别

set time_zone="+0:00";

mysql> set time_zone="+0:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_time;
+----+---------------------+---------------------+
| id | dtime               | ttime               |
+----+---------------------+---------------------+
|  1 | 2021-07-11 22:00:00 | 2021-07-11 14:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

-- 修改时区后，dtime合ttime显示的时间发生变化

date

-- 格式
YYYY-MM-DD

-- 范围
1000-01-01 ~ 9999-12-31

time

-- 格式
HH:MM:SS

-- 范围
-838:59:59 ~ 838:59:59

5、数据行增、删、改、查

5.1、新增数据

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

insert into tb1(id,name) values("001","joywon");
insert into tb1(id,name) values("001","joywon"),("002","name2");
insert into tb1 values("001","joywon"),("002","name2");  -- 按表格的默认列输入值

5.2、删除数据

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

delete from tb1;    -- 删除tb1这个表的所有内容
delete from tb1 where id="001";
delete from tb1 where id="001" and name="joywon";
delete from tb1 where id>9;

5.3、修改数据

update 表名 set 列名=值;   -- 将整列的数据全部替换
update 表名 set 列名=值 where 条件;

mysql> select * from tb1;
+------+------+-------+
| id   | uid  | zid   |
+------+------+-------+
|    1 |    1 | 00001 |
|    2 |    2 | 00002 |
|    3 |    3 | 00003 |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> update tb1 set id=4;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from tb1;
+------+------+-------+
| id   | uid  | zid   |
+------+------+-------+
|    4 |    1 | 00001 |
|    4 |    2 | 00002 |
|    4 |    3 | 00003 |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> select * from tb1;
+------+------+-------+
| id   | uid  | zid   |
+------+------+-------+
|    4 |    1 | 00001 |
|    4 |    2 | 00002 |
|    4 |    3 | 00003 |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> update tb1 set id=1 where uid=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;
+------+------+-------+
| id   | uid  | zid   |
+------+------+-------+
|    1 |    1 | 00001 |
|    4 |    2 | 00002 |
|    4 |    3 | 00003 |
+------+------+-------+
3 rows in set (0.00 sec)

5.4、查询数据

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

mysql> select * from tb1;
+------+------+-------+
| id   | uid  | zid   |
+------+------+-------+
|    1 |    1 | 00001 |
|    4 |    2 | 00002 |
|    4 |    3 | 00003 |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> select id,uid as test,zid from tb1;
+------+------+-------+
| id   | test | zid   |
+------+------+-------+
|    1 |    1 | 00001 |
|    4 |    2 | 00002 |
|    4 |    3 | 00003 |
+------+------+-------+
3 rows in set (0.00 sec)

5.5、python代码操作MySQL

import pymysql

db = pymysql.connect(host = "127.0.0.1", port = 3306, user = "root", passwd = "root", charset = "utf8",
                     db = "testdb1")
cursor = db.cursor()

# 1.新增
cursor.execute("insert into tb_change(id, name) values('001','joywon'),('002','none')")
db.commit()

# 2.删除
cursor.execute("delete from tb_change where id='001'")
db.commit()

# 3.修改
cursor.execute("update tb_change set name='joywon' where id='002'")
db.commit()

# 4.查询
cursor.execute("select * from tb_change where id>0")
data = cursor.fetchall()
print(data)
print("-------------")
date = cursor.fetchone()
print(data)
print("-------------")
date = cursor.fetchmany()
print(data)

cursor.close()
db.close()

((2, 'joywon'),)
-------------
((2, 'joywon'),)
-------------
((2, 'joywon'),)

6、综合练习用户管理系统（注册、登陆）

创建数据库 user_system

create database user_system default charset utf8 collate utf8_general_ci;

创建数据表 users

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

python实现注册、登陆

import pymysql


# 创建账号
def creat_account():
    print("--------start create account--------")
    user_name = input("please input user name:")
    password = input("please input user password:")

    # 连接数据库
    db = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = 'root', charset = 'utf8',
                         db = 'user_system')
    cursor = db.cursor()

    # 这种拼接方式不安全，容易被注入破解登陆
    sql = "insert into users(name, password) values('{}', '{}')".format(user_name, password)
    cursor.execute(sql)
    db.commit()

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

    print("--------create success--------")
    print("your user name is: {}\nyour password is: {}".format(user_name, password))
    print("--------end create account--------")


# 登陆
def login():
    print("--------start login--------")
    user_name = input("please input user name:")
    password = input("please input user password:")

    # 连接数据库
    db = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = 'root', charset = 'utf8',
                         db = 'user_system')
    cursor = db.cursor()

    # 这种拼接方式不安全，容易被注入破解登陆
    sql = "select * from users where name='{}' and password='{}'".format(user_name, password)
    cursor.execute(sql)
    # 验证成功，返回（id，name，password）
    # 验证失败，返回 None
    result = cursor.fetchone()

    cursor.close()
    db.close()

    if result:
        print("login success, your account is:", result[1])
        print("--------end login--------")
    else:
        print("login fail, please login again")
        login()


def run():
    flag = input("1.create account, 2.login:")
    if flag == "1":
        creat_account()
    elif flag == "2":
        login()
    else:
        print("input error,please choice again!")
        run()


if __name__ == "__main__":
    run()

7、注入安全问题

7.1、SQL注入示例

在MySQL中 -- xxxx表示注释，通过以下这种方式即可破解python中的拼接数据提交数据库验证

user_name = "' or 1=1 -- "
password = "123"
sql = "select * from users where name='{}' and password='{}'".format(user_name, password)
print(sql)

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

以下是在MySQL中的运行结果

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

+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | joywon | 12345    |
+----+--------+----------+
1 row in set (0.00 sec)

7.2、避免SQL注入风险

通过pymysql中的占位符拼接数据即可避免SQL注入

# sql = "insert into users(name, password) values('{}', '{}')".format(user_name, password)
cursor.execute("insert into users(name, password) values(%s, %s)", [user_name, password])

# sql = "select * from users where name='{}' and password='{}'".format(user_name, password)
cursor.execute("select * from users where name=%s and password=%s", [user_name, password])

给占位符命名，并用字典将参数传递

cursor.execute("insert into users(name, password) values(%(n1)s, %(n2)s)", {"n1": user_name, "n2": password})

cursor.execute("select * from users where name=%(m1)s and password=%(m2)s", {"m1": user_name, "m2": password})