MySQL数据库（1/5）安装启动基本操作

MySQL数据库

类比：数据库--文件夹，数据表--文件，文件内某行--数据表记录。
数据库管理系统：DBMS，Database Management System。可以帮助我们实现对文件夹中的文件进行操作，而我们只要学习DBMS能识别的指令，就能控制它去帮助我们实现的文件和文件夹的处理。

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

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

由于各大公司都是使用MySQL，所以我们课程主要给大家讲解MySQL数据库。
通过8个步骤操作数据库：

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

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

一、MySQL安装

1.1 windows系统下安装

版本：5.7.x 、 8.x（新功能，还不完善），大多公司还在使用5.6或5.7，这里用5.7.31。

1. 下载地址：https://downloads.mysql.com/archives/community/

2. 解压缩：建议解压缩到该目录，方便后面学习

3. 创建配置文件，在解压缩目录中创建 my.ini ，填入一下内容：

[mysqld]
port=3306
basedir=c:\Program Files\mysql-5.7.31-winx64
datadir=c:\Program Files\mysql-5.7.31-winx64\data

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

强烈建议：大家还是把配置文件放在MySQL安装目录下，这样以后电脑上想要安装多个版本的MySQL时，配置文件可以相互独立不影响。
注意：如果你电脑的上述其他目录存在MySQL配置文件，建议删除，否则可能会影响MySQL的启动。
命令：mysqld --help --verbose
输出结果包含配置文件目录：
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\mysql-5.7.31-winx64\my.ini C:\Program Files\mysql-5.7.31-winx64\my.cnf

4. 初始化：>>> "C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --initialize-insecure

【注意】MySQL自5.7之后，默认不包含data文件夹和配置文件，因此需要手动创建my.ini配置文件，通过初始化命令创建data文件夹和一些必要的数据。

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

自动创建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服务管理。

"完整的可执行文件路径" --install [-manual] [服务名]

其中的-manual 可以省略，加上它的话表示在Windows 系统启动的时候不自动启动该服务，否则会自动启动。
服务名也可以省略，默认的服务名就是MySQL。

所以如果我们想把它注册为服务的话可以在命令行里这么写：
命令： >>>"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57

【注意】这里mysqld必须使用带路径的方式，不然创建的服务文件路径（"C:\Program Files\MySQL\MySQL Server 5.7\mysqld" MySQL）不对，服务无法启动。

创建好服务之后，可以通过命令启动和关闭服务，例如：
net start mysql57 #启动
net stop mysql57 #停止
以后不再想要使用window服务了，也可以将制作的这个MySQL服务删除。
命令：>>>"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --remove mysql57

6. 测试连接MySQL：

【注意】如果把bin目录加入环境变量，每次在运行命令时，就不用再重新输入绝对路径了。
连接数据库命令：>>> mysql -h localhost -P 3306 -u root -P
一般本机连接可以省略ip和端口号：
简化版命令：>>> mysql -u root -p

【连接注意事项】

最好不要在一行命令中输入密码。
我们直接在黑框框里输入密码很可能被别人看到，这和你当着别人的面输入银行卡密码没啥区别，所以我们在执行mysql 连接服务器的时候可以不显式的写出密码，就像这样：
mysql -hlocahhost -uroot -p
点击回车之后才会提示你输入密码：
Enter password:
不过这回你输入的密码不会被显示出来，心怀不轨的人也就看不到了，输入完成点击回车就成功连接到了服务器。
如果你非要在一行命令中显式的把密码输出来，那-p和密码值之间不能有空白字符（其他参数名之间可以有空白字符），就像这样：
mysql -h localhost -u root -p123456
如果加上了空白字符就是错误的，比如这样：
mysql -h localhost -u root -p 123456
mysql 的各个参数的摆放顺序没有硬性规定，也就是说你也可以这么写：
mysql -p -u root -h localhost
如果你的服务器和客户端安装在同一台机器上，-h参数可以省略，就像这样：
mysql -u root -p
如果你使用的是类UNIX系统，并且省略-u参数后，会把你登陆操作系统的用户名当作MySQL 的用户名去处理。

比方说我用登录操作系统的用户名是xiaohaizi，那么在我的机器上下边这两条命令是等价的：
mysql -u xiaohaizi -p
mysql -p
对于Windows 系统来说，默认的用户名是ODBC，你可以通过设置环境变量USER 来添加一个默认用户名。

show databases; # 显示所有的数据库

如果我们想断开客户端与服务器的连接并且关闭客户端的话，可以在mysql> 提示符后输入下边任意一个命令：

quit
exit
\q

比如我们输入quit 试试：
mysql> quit
Bye
输出了Bye 说明客户端程序已经关掉了。注意注意注意，这是关闭客户端程序的方式，不是关闭服务器程序的方式，怎么关闭服务器程序上一节里唠叨过了。
如果你愿意，你可以多打开几个黑框框，每个黑框框都使用mysql -hlocahhost -uroot -p123456 来运行多个客户端程序，每个客户端程序都是互不影响的。如果你有多个电脑，也可以试试把它们用局域网连起来，在一个电
脑上启动MySQL 服务器程序，在另一个电脑上执行mysql命令时使用IP 地址作为主机名来连接到服务器。

1.2 MAC系统下安装MySQL 【省略】

1.3 关于配置文件

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

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

1.4 修改和设置root 密码

设置root密码：

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

忘记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.1 内置mysql连接工具操作

查看当前所有的数据库： show databases;
查看当前所处的数据库： select database();
查看某数据库信息：show create database 数据库名;
创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
删除数据库：drop database 数据库名;
进入数据（进入文件夹）：use 数据库;
查看数据库中所有的表： show tables;

2.2 python代码操作

pip 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 = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

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

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

# 5. 进入数据库，查看表
# 发送指令
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()

【总结】凡是查询类的操作，通过 cursor.execute(sql语句)后，通过cursor.fetchall()获取查询到的结果的返回值，一般是元组组成的元组，其他增、删、改的操作一般没有返回值，那么通过cursor.execute(sql语句)后，不需要接收返回值，但是必须通过conn.commit()去执行这个操作，才能成功。

【注意】通过mysql终端输入命令时一般以分号；结尾，但是使用pymysql操作时，sql语句末尾的分号；可加可不加。

锚点：三、数据表管理

三、数据表管理

3.1 内置客户端操作

进入数据库：use db1；
查看所有的表： show tables;

清空控制台：system clear;

创建表

 create table 表名(
     列名  类型,
     列名  类型,
     列名  类型
 )default charset=utf8;

【注意】创建表的命令可以写成一行，也可以写成多行，直接回车换行，直到分号结束。

  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;

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

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

修改表

添加列

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; -- 允许为空，删除默认值，删除自增。

【注意最后一个修改会删除掉id字段原有的主键、默认值等特性，主键特性删不掉】

修改列默认值

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

删除列默认值

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

添加主键

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

删除主键
```
alter table 表名 drop primary key;
```

【总结】想要一次性统一修改多个属性，使用change指令，单独修改某个属性则使用具体的某个指令。

【注意】这里的命令不要求全部记住，不要求背会，写在笔记中，知道原理，用的时候查询使用即可。

3.2 常见列类型

 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>

【建议】将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;


```sql
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是小数点后个数。

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

双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

【小数总结】基本上只使用decimal，float和double基本不用。

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("alexsb","sbalex");

【注意】char(m):m指的是字符个数，而不是字节数。

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;

【默认MySQL是严格模式。如果在配置文件中加入如下配置，sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）】

【varchar(m):这里m指的是字节数。】

    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("wupeiqi","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)

【总结】一般只使用到text类型，mediumtext和longtext几乎用不到。

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

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'）
```

【总结】开发过程中使用比较多的是datetime类型，其他的不常用。

MySQL还有很多其他的数据类型（整数、小数、字符串、时间），常用的就是上面介绍的几种，其他基本上用不到，因此，掌握上面几种类型基本就够用了，真到用到其他类型的时候，再去了解也不迟。例如：set、enum、TinyBlob、Blob、MediumBlob、LongBlob 等，详细见官方文档：https://dev.mysql.com/doc/refman/5.7/en/data-types.html

上述就是关于数据表的一些基本操作。

3.3 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.1 内置客户端操作

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

新增数据

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

insert into tb1(name,password) values('武沛齐','123123');
insert into tb1(name,password) values('武沛齐','123123'),('alex','123');

insert into tb1 values('武沛齐','123123'),('alex','123'); -- 如果表中只有2列，MySQL会按照列的顺序添加记录，列数跟添加的数量必须一致

删除数据

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

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

【注意】这里where后面的条件，判断是否相等是单等于号=

修改数据

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

update tb1 set name="wupeiqi";
update tb1 set name="wupeiqi" 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; -- 表明增加一列，表头和每条记录该值都是111，如果是111 as value，则表头是value，值为111

select * from tb1 where id = 1;
select * from tb1 where id > 1;
select * from tb1 where id != 1;
select * from tb1 where name="wupeiqi" 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()

【总结】connect（）函数中增加 db='db1'参数传值，则默认连接的时候就进入数据库db1，因此后面执行命令时不需要再提前执行use db1；指令了。

【总结】增删改都只用conn.commit()就可以了，查则需要cursor.fetchall()获取数据。

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

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

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

先使用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("请输入用户名：") # alex
    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("alex","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='wupeiqi' and password='123'
    sql = "select * from users where name='{}' and password='{}'".format(user, password)
    cursor.execute(sql)
    
    result = cursor.fetchone() # 去向mysql获取结果
    # fetchone：如果指令（sql语句）执行后获取了很多行数据，只拿第一行；如果没有获取到数据，返回None。
    # fetchall：如果指令（sql语句）执行后获取了很多行数据，所有数据都获取到行；如果没有获取到数据，返回None。获取到的数据是元组组成的元组的形式。
    # 第一种情况result值：None
    # 第二种情况result值：(1,wupeiqi,123)
    
    
    # 关闭数据库连接
    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，你会发现，在项目开发时，数据库 & 数据表的操作其实就做那么一次，最最常写的还是对数据行的操作。

锚点：五、SQL注入

五、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='alex' 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()

【总结】使用%s传值可以，但是传递数据库名、表名、字段名的时候因为会带引号，因此sql语句是不正确的，因此包含这些名字的execute传参，应该先把这些名称包含的sql语句使用字符串拼接好，然后之传递含值的参数，例如：

sql = ‘insert into {} values(%s, %s);'
cursor.execute(sql, [22, 'john'])

本节内容大家需要掌握：

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

posted @ 2021-11-03 23:41 #缘起阅读(257) 评论(0) 编辑收藏举报

指间灵动，快码加编

刷新页面返回顶部

缘起