木道寻08

这个屌丝很懒，什么也没留下！

热门标签

第四模块MySQL数据库

作者：木道寻08 | 2024-07-18 03:55:56

踩

第四模块MySQL数据库

从今天开始，我们将进入系列课程的第四模块的学习，这个模块就是给大家讲解MySQL数据库。

以前，在开发程序时，我们会把很多的数据和信息存储到某个文件夹中的文件中，例如：user.txt 、db.xlsx 等。

现在，有那么一个叫：数据库管理系统（DBMS，Database Management System）的软件，可以帮助我们实现对文件夹中的文件进行操作，而我们只要学习DBMS能识别的指令，就能控制它去帮助我们实现的文件和文件夹的处理。例如：

数据库管理系统（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
1

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

自动创建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"
1
```
注意：此时程序会挂起，内部就是可以接收客户端发来的MySQL指令，关闭窗口或Ctrl+c 就可以停止运行。

这种启动方式每次开机或想要开启都需要手动执行一遍命令比较麻烦。
制作windows服务，基于windows服务管理。
```
>>>"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57
1
```
创建好服务之后，可以通过命令启动和关闭服务，例如：
```
>>> net start mysql57
>>> net stop mysql57
1
2
```
也可以在window的服务管理中点击按钮启动和关闭服务。例如：

以后不再想要使用window服务了，也可以将制作的这个MySQL服务删除。
```
>>>"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --remove mysql57
1
```

第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 脚本（建议）

sudo /usr/local/mysql/support-files/mysql.server start
# 输入电脑密码

sudo mysql.server start
# 输入电脑密码
1
2
3
4
5

sudo /usr/local/mysql/support-files/mysql.server stop
1

为了避免每次执行命令都需要些路径，可以将路径 /usr/local/mysql/support-files加入到环境变量中。

操作完成之后，再在终端执行下命令：source ~/.zprofile 让设置的环境变量立即生效。

注意：mac系统的版本如果比较老，会显示空白的 zprofile 文件，此就要去打开 bash_profile 文件。

这样设置好之后，以后就可以使用下面的命令去启动和关闭MySQL了。

sudo mysql.server start
sudo mysql.server stop
1
2

系统偏好设置（不推荐）

第一种mysql.server脚本的形式，内部是使用 mysqld_safe运行，可以守护我们的MySQL进程，如意外挂掉可自动重启。

第4步：测试连接MySQL

安装并启动MySQL之后，就可以连接MySQL来测试是否已正确安装并启动成功。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LVxXHSug-1648216045232)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210510153336093.png)]

以后在开发时，肯定是要用Python代码来连接MySQL并且进行数据操作（后面讲）。

在安装MySQL时，其实也自动安装了一个工具（客户端），让我们快速实现连接MySQL并发送指令。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XPamLbyb-1648216045232)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210510171029083.png)]

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

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

1.3 关于配置文件

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

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

1.4 关于密码

1. 设置和修改root密码

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

2. 忘记root密码

如果你忘记了MySQL账户的密码。

修改配置文件，在 [mysqld] 节点下添加 skip-grant-tables=1
```
[mysqld]
...
skip-grant-tables=1
...
1
2
3
4
```
重启MySQL，再次登录时，不需要密码直接可以进去了
- windows重启
```
net stop mysql57
net start mysql57
1
2
```
- mac重启
```
sudo mysql.server restart
1
```
重启后，无序密码就可以进入。
```
>>> mysql -u root -p
1
```

进入数据库后执行修改密码命令

use mysql;
update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';
1
2

退出并再次修改配置文件，删除 [mysqld] 节点下的 skip-grant-tables=1
```
[mysqld]
...
# skip-grant-tables=1
...
1
2
3
4
```
再次重启，以后就可以使用新密码登录了。

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;
1
2
3

删除数据库：drop database 数据库名;
进入数据（进入文件）：use 数据库;

示例：

# 1.登录MySQL
wupeiqi@wupeiqideMBP ~ % /usr/local/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

# 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数据库（文件夹），查看此数据库下的所有表。
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

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
1

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

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")
# 获取指令的结果
# .fetchall 接受返回值
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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

3. 数据表管理

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

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

接下来，我们先学习数据表（文件夹中的文件）相关操作的指令。

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

3.1 内置客户端操作

数据表常见操作的指令：

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

创建表结构

create table 表名(
    列名  类型,
    列名  类型,
    列名  类型
)default charset=utf8;
1
2
3
4
5

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

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

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

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;
1
2
3
4
5
6

主键一般用于表示当前这条数据的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;
1
2
3
4
5
6

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

删除表 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;
1
2
3
4

删除列

alter table 表名 drop column 列名;
1

修改列类型

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

修改列类型 + 名称

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

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

修改列默认值

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 表(
	id int,
    name varchar(16)
)default charset=utf8;
1
2
3
4

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

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

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

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

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

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

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;
1
2
3
4
5
6
7

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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

单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。
1

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

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

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

mysql> select * from L3;
+----+-------+--------+
| id | name  | depart |
+----+-------+--------+
|  1 | alexs | sba    |
|  2 | alexs | sba    |
+----+-------+--------+
2 rows in set (0.00 sec)

# 固定长度为3个字符
mysql> select id,name,depart,length(depart) from L3;
+----+-------+--------+----------------+
| id | name  | depart | length(depart) |
+----+-------+--------+----------------+
|  1 | alexs | sba    |              3 |
|  2 | alexs | sba    |              3 |
+----+-------+--------+----------------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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;
1
2
3
4
5
6
7
8
9
10
11
12
13

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

text

text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

一般情况下，长文本会用text类型。例如：文章、新闻等。
1
2
3

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

mediumtext

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

longtext

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

datetime

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

timestamp

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

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储，查询时，将其又转化为客户端当前时区进行返回。

对于DATETIME，不做任何改变，原样输入和输出。
1
2
3

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

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

time

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

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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

4.数据行

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

4.1 内置客户端操作

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

新增数据

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

insert into tb1(name,password) values('小乔','123123');
insert into tb1(name,password) values('小乔','123123'),('alex','123');

insert into tb1 values('小乔','123123'),('大桥','123'); -- 如果表中只有2列
1
2
3
4

删除数据

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

delete from tb1;
delete from tb1 where name="xiaoqiao";
delete from tb1 where name="xiaoqiao" and password="123";
delete from tb1 where id>9;
1
2
3
4

修改数据

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

update tb1 set name="xiaoqiao";
update tb1 set name="xiaoqiao" where id=1;

update tb1 set age=age+1;  -- 整型
update tb1 set age=age+1 where id=2;

update L3 set name=concat(name,"db");
update L3 set name=concat(name,"123")  where id=2;  -- concat一个函数，可以拼接字符串
1
2
3
4
5
6
7
8

查询数据

select * from 表名;
select 列名,列名,列名 from 表名;
select 列名,列名 as 别名,列名 from 表名;
select * from 表名 where 条件;
1
2
3
4

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 * from tb1 where id = 1;
select * from tb1 where id > 1;
select * from tb1 where id != 1;
select * from tb1 where name="xiaoqiao" and password="123";
1
2
3
4
5
6
7
8
9

4.2 Python代码操作

import pymysql

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


# 1.新增（需commit）

cursor.execute("insert into tb1(name,password) values('xiaoqiao','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")
# .fetchone 在数据库中查询到的结果区第一条结果
# .fetchall 在数据库中查询到的结果全部结果
data = cursor.fetchone() # cursor.fetchall()
print(data)


# 关闭连接
cursor.close()
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

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

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

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

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

create database usersdb default charset utf8 collate utf8_general_ci;
1

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

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

import pymysql


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

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

    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', 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='123456', charset="utf8", db="usersdb")
    cursor = conn.cursor()

    # 执行SQL语句（有SQL注入风险，稍后讲解）
    # sql = select * from users where name='xiaoqiao' and password='123'
    sql = "select * from users where name='{}' and password='{}'".format(user, password)
    cursor.execute(sql)
    
    result = cursor.fetchone() # 去向mysql获取结果
    # None
    # (1,xiaoqiao,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()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

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='123456', 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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

为什么呢？

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

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

注意：在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='123456', 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()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

总结

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

本节内容大家需要掌握：

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

day26 必备SQL和表关系及授权

课程目标：掌握开发中最常见的SQL语句和表关系及授权相关知识点。

课程概要：

必备SQL（8个必备）
表关系
授权

1. 必备SQL语句

上一节讲解了最基础SQL语句：增删改查，其实在日常的开发中还有很多必备的SQL语句。

这一部分的SQL语句都是围绕着对表中的数据进行操作的。

提示：今天的所有操作我都只会在 MySQL自带的客户端工具上进行操作。

例如：现在创建如下两张表。

create database day26db default charset utf8 collate utf8_general_ci;
1

create table depart(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
	id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13

insert into depart(title) values("开发"),("运营"),("销售");

insert into info(name,email,age,depart_id) values("小乔","xiaoqiao@live.com",19,1);
insert into info(name,email,age,depart_id) values("狄仁杰","direnjie@live.com",49,1);
insert into info(name,email,age,depart_id) values("孙悟空","sunwukong@live.com",9,2);
insert into info(name,email,age,depart_id) values("tony","tony@live.com",29,1);
insert into info(name,email,age,depart_id) values("kelly","kelly@live.com",99,3);
insert into info(name,email,age,depart_id) values("james","james@live.com",49,1);
insert into info(name,email,age,depart_id) values("李杰","lijie@live.com",49,1);
1
2
3
4
5
6
7
8
9

1.1 条件

根据条件搜索结果。

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4

select * from info where name = 'xiaqo' and age = 19;
select * from info where name = 'direnjie' or age = 49;
select * from info where (name = '李杰' or email="xiaoqiao@live.com")  and age=49;

select * from info where id in (1,4,6); -- id等于1,4,6
select * from info where id not in (1,4,6); -- id不等于1,4,6
select * from info where id in (select id from depart); -- 判断id=5是否存在，
# select * from info where id in (1,2,3);

# exists select * from depart where id=5，去查数据是否存在，如果存在，如果不存在。
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);

select * from (select * from info where id>2) as T where age > 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

select * from info where info.id > 10;
select * from info where id > 10;
1
2

1.2 通配符

一般用于模糊搜索。

select * from info where name like "%小%"; -- %代表多个字符
select * from info where name like "%孙";
select * from info where email like "%@live.com";
select * from info where name like "孙%空";
select * from info where name like "k%y";
select * from info where email like "xiaoqiao%";


select * from info where email like "_@live.com"; -- _代表一个字符
select * from info where email like "_iaoqiao@live.com";
select * from info where email like "__aoqiao@live.com";
select * from info where email like "__aoqia_live.co_";
1
2
3
4
5
6
7
8
9
10
11
12

注意：数量少，数据量大的搜索。

1.3 映射

想要获取的列。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7t9DSxaT-1648216045235)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210517184600205.png)]

select * from info;

select id, name				from info;
select id, name as NM 		from info;
select id, name as NM, 123  from info;
注意：少些select * ,自己需求。

select 
	id,
	name,
	666 as num,
	( select max(id) from depart ) as mid, -- max/min/sum
	( select min(id) from depart) as nid, -- max/min/sum
	age
from info;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1
from info;

# 注意：效率很低

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1,
	( select title from depart where depart.id=info.id) as x2
from info;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" else "其他" end v2
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1,
	case depart_id when 1 then "第1部门" else "其他" end v2,
	case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
	case when age<18 then "少年" end v4,
	case when age<18 then "少年" else "油腻男" end v5,
	case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.4 排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fmYxbfuA-1648216045235)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210517184600205.png)]

select * from info order by age desc; -- 倒序
select * from info order by age asc;  -- 顺序

select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大；如果age相同则按照id从大到小。

# 先跟条件，在排序
select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;
1
2
3
4
5
6
7
8
9
10

1.5 取部分

一般要用于获取部分数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BGXyyPlb-1648216045235)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210517184600205.png)]

select * from info limit 5;   										-- 获取前5条数据
select * from info order by id desc limit 3;						-- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3;			-- 先排序，再获取前3条数据


select * from info limit 3 offset 2;	-- 从位置2开始，向后获取前3数据
1
2
3
4
5
6

数据库表中：1000条数据。

第一页：select * from info limit 10 offset 0;
第二页：select * from info limit 10 offset 10;
第三页：select * from info limit 10 offset 20;
第四页：select * from info limit 10 offset 30;
…

1.6 分组

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
-- sum 求和
-- avg 平均数
1
2
3

select age,count(1) from info group by age;
1

select depart_id,count(id) from info group by depart_id;
1

# 分组之后，在使用聚合函数是不能使用where，必须使用having
select depart_id,count(id) from info group by depart_id having count(id) > 2;
1
2

select count(id) from info;
select max(id) from info;
1
2

select age,max(id),min(id),sum(id),count(id) from info group by age;
1

select age,name from info group by age;  -- 不建议
select * from info where id in (select max(id) from info group by age);
1
2

select age,count(id) from info group by age having count(id) > 2;
select age,count(id) from info where id > 4 group by age having count(id) > 2;  -- 聚合条件放在having后面
1
2

到目前为止SQL执行顺序：
    where 
    group by
    having 
    order by
    limit 
1
2
3
4
5
6

select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条
1
2
3
4
5
6
7

1.7 左右连表

多个表可以连接起来进行查询。

展示用户信息&部门名称：

主表 left outer join 从表 on 主表.x = 从表.id 
1

# left outer join 左连接
select * from info left outer join depart on info.depart_id = depart.id;
1
2

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
1

从表 right outer join 主表 on 主表.x = 从表.id
1

# right outer join 右连接
select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
1
2

为了更加直接的查看效果，我们分别在 depart 表和 info 中额外插入一条数据。

insert into depart(title) values("运维");
1

这样一来主从表就有区别：

info主表，就以info数据为主，depart为辅。

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
1

depart主表，，就以depart数据为主，info为辅。

select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
1

select * from info left outer join depart on ....
select * from depart left outer join info on ....
1
2

简写：select * from depart left join info on ....

-- 内连接:    表  inner join 表  on 条件
select * from info inner join depart on info.depart_id=depart.id;

+----+-----------+------------------+------+-----------+----+--------+
| id | name      | email            | age  | depart_id | id | title  |
+----+-----------+------------------+------+-----------+----+--------+
|  1 | 武沛齐    | wupeiqi@live.com |   19 |         1 |  1 | 开发   |
|  2 | 于超      | pyyu@live.com    |   49 |         1 |  1 | 开发   |
|  3 | alex      | alex@live.com    |    9 |         2 |  2 | 运营   |
|  4 | tony      | tony@live.com    |   29 |         1 |  1 | 开发   |
|  5 | kelly     | kelly@live.com   |   99 |         3 |  3 | 销售   |
|  6 | james     | james@live.com   |   49 |         1 |  1 | 开发   |
|  7 | 李杰      | lijie@live.com   |   49 |         1 |  1 | 开发   |
+----+-----------+------------------+------+-----------+----+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

到目前为止SQL执行顺序：
    join 
    on 
    where 
    group by
    having 
    order by
    limit 
1
2
3
4
5
6
7
8

写在最后：多张表也可以连接。

1.8 联合

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n5vxD7rK-1648216045236)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210519093004885.png)]

select id,title from depart 
union -- 上下连接
select id,name from info;


select id,title from depart 
union
select email,name from info;
-- 列数需相同
1
2
3
4
5
6
7
8
9

select id from depart 
union
select id from info;

-- 自动去重
1
2
3
4
5

select id from depart 
union all
select id from info;

-- 保留所有
1
2
3
4
5

小结

到目前为止，你已经掌握了如下相关指令（SQL语句）：

数据库
数据表
数据行
- 增加
- 删除
- 修改
- 查询（各种变着花样的查询）

2.表关系

在开发项目时，需要根据业务需求去创建很多的表结构，以此来实现业务逻辑，一般表结构有三类：

单表，单独一张表就可以将信息保存。
一对多，需要两张表来存储信息，且两张表存在 一对多 或 多对一关系。
多对多，需要三张表来存储信息，两张单表 + 关系表，创造出两个单表之间多对多关系。

在上述的表：一对多的 info.depart_id字段、多对多的 boy_girl.boy_id 、girl_id 直接用整型存储就可以，因为他们只要存储关联表的主键ID即可。

在开发中往往还会为他们添加一个 外键约束，保证某一个列的值必须是其他表中的特定列已存在的值，例如：info.depart_id的值必须是 depart.id中已存在的值。

一对多示例：

create table depart(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
	id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果表结构已创建好了，额外想要增加外键：

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);
1

删除外键：

alter table info drop foreign key fk_info_depart;
1

多对多示例：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S7NQbgRV-1648216045236)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210520171843289.png)]

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

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


create table boy_girl(
	id int not null auto_increment primary key,
    boy_id int not null,
    girl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

如果表结构已创建好了，额外想要增加外键：

alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);
1
2

删除外键：

alter table info drop foreign key fk_boy_girl_boy;
alter table info drop foreign key fk_boy_girl_girl;
1
2

在以后项目开发时，设计表结构及其关系的是一个非常重要的技能。一般项目开始开发的步骤：

需求调研
设计数据库表结构（根据需求）
项目开发（写代码）

大量的工作应该放在前2个步骤，前期的设计完成之后，后续的功能代码开发就比较简单了。

案例：简易版路飞学城

create database luffy default charset utf8 collate utf8_general_ci;

use luffy;
1
2
3

create table info(
	id int not null auto_increment primary key,
    username varchar(16) not null,
    mobile char(11) not null,
    password varchar(64) not null
)default charset=utf8;
1
2
3
4
5
6

create table course(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table module(
	id int not null auto_increment primary key,
    title varchar(16) not null,
    course_id int not null,
    constraint fk_module_course foreign key (course_id) references course(id)
)default charset=utf8;


create table day(
	id int not null auto_increment primary key,
    title varchar(16) not null,
	module_id int not null,
    constraint fk_day_module foreign key (module_id) references module(id)
)default charset=utf8;


create table video(
	id int not null auto_increment primary key,
    title varchar(16) not null,
	day_id int not null,
    constraint fk_video_day foreign key (day_id) references day(id)
)default charset=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

create table module_record(
	id int not null auto_increment primary key,
    user_id int not null,
    module_id int not null,
    constraint fk_user_id foreign key module_record(user_id) references info(id),
    constraint fk_module_id foreign key module_record(module_id) references module(id)
)default charset=utf8;
1
2
3
4
5
6
7

3. 授权

之前我们无论是基于Python代码 or 自带客户端去连接MySQL时，均使用的是 root 账户，拥有对MySQL数据库操作的所有权限。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ysleWW0X-1648216045237)(/Users/daihao/Documents/第四模块MySQL数据库.assets/image-20210520173921204.png)]

如果有多个程序的数据库都放在同一个MySQL中，如果程序都用root账户就存在风险了。

这种情况怎么办呢？

在MySQL中支持创建账户，并给账户分配权限，例如：只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。

3.1 用户管理

在MySQL的默认数据库 mysql 中的 user 表中存储着所有的账户信息（含账户、权限等）。

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

mysql> select user,authentication_string,host from  mysql.user;
+----------------------------------+-------------------------------------------+-------------------------------+
| user                             | authentication_string                     | host                          |
+----------------------------------+-------------------------------------------+-------------------------------+
| root                             | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | localhost                     |
| mysql.session                    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
| mysql.sys                        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
+----------------------------------+-------------------------------------------+-------------------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

创建和删除用户

create user '用户名'@'连接者的IP地址' identified by '密码';
1

create user wupeiqi1@127.0.0.1 identified by 'root123';
drop user wupeiqi1@127.0.0.1;

create user wupeiqi2@'127.0.0.%' identified by 'root123';
drop user wupeiqi2@'127.0.0.%';

create user wupeiqi3@'%' identified by 'root123';
drop user wupeiqi3@'%';

create user 'wupeiqi4'@'%' identified by 'root123';
drop user 'wupeiqi4'@'%';
1
2
3
4
5
6
7
8
9
10
11

修改用户

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
1

rename user wupeiqi1@127.0.0.1 to wupeiqi1@localhost;

rename user 'wupeiqi1'@'127.0.0.1' to 'wupeiqi1'@'localhost';
1
2
3

修改密码

set password for '用户名'@'IP地址' = Password('新密码')
1

set password for 'wupeiqi4'@'%' = Password('123123');
1

3.2 授权管理

创建好用户之后，就可以为用户进行授权了。

授权

grant 权限 on 数据库.表 to   '用户'@'IP地址'
1

grant all privileges on *.* TO 'wupeiqi'@'localhost';         -- 用户wupeiqi拥有所有数据库的所有权限
grant all privileges on day26.* TO 'wupeiqi'@'localhost';     -- 用户wupeiqi拥有数据库day26的所有权限
grant all privileges on day26.info TO 'wupeiqi'@'localhost';  -- 用户wupeiqi拥有数据库day26中info表的所有权限

grant select on day26.info TO 'wupeiqi'@'localhost';          -- 用户wupeiqi拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'wupeiqi'@'localhost';      -- 用户wupeiqi拥有数据库day26所有表的查询和插入权限

grant all privileges on day26db.* to 'wupeiqi4'@'%';


注意：flush privileges;   -- 将数据读取到内存中，从而立即生效。
1
2
3
4
5
6
7
8
9
10
11

对于权限

all privileges  除grant外的所有权限
select          仅查权限
select,insert   查和插入权限
...
usage                   无访问权限
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke  all privileges
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和存储过程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
select                  使用select
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(关闭MySQL)
super                   声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/木道寻08/article/detail/843459

推荐阅读

相关标签

第四模块MySQL数据库