数据治理 | 多人协同处理数据担心不安全？学会这一招，轻松管理你的数据团队

我们将在数据治理板块中推出一系列原创推文，帮助读者搭建一个完整的社科研究数据治理软硬件体系。该板块将涉及以下几个模块：

1. 计算机基础知识

1. 编程基础
2. 数据采集
3. 数据存储

(1)安装篇 数据治理 | 遇到海量数据stata卡死怎么办？这一数据处理利器要掌握

(2)本期内容：数据治理 | 多人协同处理数据担心不安全？学会这一招，轻松管理你的数据团队

1. 数据清洗
2. 数据实验室搭建

Part1引言

上一篇，我们讲解了数据库的安装（以MySQL为例），本文我们介绍数据库管理方面的知识。

使用数据库的具体优势在哪里呢？

第一，数据统一存放在数据库中，数据读取方便支持预览功能。

第二，多人协同工作时，只需提供数据库账号，就可以让其他人访问数据，免去了数据拷贝的时间。

第三，可以给用户授予特定权限，可以查看一些数据，但另一些数据不能查看，保证了数据安全。

安装并配置好MySQL服务器与DBeaver客户端后，我们开始学习如何管理MySQL数据库系统知识，主要包括：用户管理、权限管理、数据库与表管理以及数据备份。

为更好运用本文知识，推荐大家先阅读上一篇推文：数据治理 | 遇到海量数据stata卡死怎么办？这一数据处理利器要掌握

Part2用户管理

在用户管理这一小节，我们介绍如何查看用户、创建用户、删除用户、修改用户密码等操作。本节内容，我们全程使用 newbie 用户执行SQL语句。

1查看用户

使用下方语句查看所有mysql中的用户。注意：mysql.user这个字符串，点前面的mysql是指一个数据库，点后面的user是指数据库中的一个表，user表记录了mysql中所有用户的信息。

select * from mysql.user;

DBeaver展示的运行结果如下表所示（只展示了前若干列），Host表示用户可以在什么位置登录，%表示用户可以在任意位置登录，localhost表示用户只能在安装了MySQL数据库 的主机上登录；User是用户的名称；priv结尾的列名是指该用户拥有的权限，我们后面会专门有节讲解权限管理。

我们可以修改Host，让root用户可以在任意地点登录，SQL语句如下，其中第一句是修改root用户的host选项，第二句是刷新MySQL系统权限相关表，让第一句SQL生效。我们也可以重启MySQL服务，这样第一句SQL也会生效，但不推荐这么做。

update mysql.user set host='%' where user='root';
flush privileges; 

DBeaver展示的运行结果如下表所示，其中，Updated Rows表示更新了多少行记录。注意：如果SQL语句中，没有where进行限定，则会影响所有的用户。

2创建用户

使用以下语句创建用户，username为用户名，host可以选择%或者localhost，password为用户登录密码。

-- 基本语法
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 案例
CREATE USER 'user01'@'%' IDENTIFIED BY 'jR7G8O9p';
CREATE USER 'user02'@'%' IDENTIFIED BY 'WEfdsgd9p';
CREATE USER 'user03'@'%' IDENTIFIED BY 'REJUIdsgd9p';

用户创建完毕后，我们再次查看数据库中用户。

select * from mysql.user;

DBeaver展示的运行结果如下表所示（只展示了前若干列），新创建的用户除了可以登录MySQL数据，没有任何其他权限，注意：priv结尾的列代表用户拥有的权限，我们在后一章节会详细说明。

3修改用户密码

我们修改user03的密码，可以使用下方SQL语句。

ALTER USER 'user03'@'%' IDENTIFIED BY '1234567';
flush privileges;

注意：

1. MySQL修改用户密码的方式不止一种
2. 不同版本的MySQL，修改密码的方式略有不同

4删除用户

删除用户非常简单，使用下述SQL语句即可。

drop user 'user03'@'%';

Part3权限管理

在用户管理一节中，我们提到，新创建的用户只能登陆数据库，但是没有其他权限，那么MySQL数据库中有哪些权限呢？如何给新创建的用户进行授权呢？本节我们就来介绍MySQL权限管理知识。

MySQL 是一个多用户数据库，用户可以分为普通用户和root用户，root用户是超级管理员，拥有所有权限。root用户拥有MySQL数据库系统的所有权限，普通用户需要root用户的授权才能使用MySQL数据库系统的具体功能。

5权限表

MySQL 数据库管理系统通过一系列权限表来控制用户对数据库的访问，这些权限表中写明了用户具有哪些权限。权限表存放在mysql数据库中（这里我们说的mysql数据库，不是MySQL数据库系统，而是一个由各种文件组成的数据库）。

在DBeaver中，右击连接，点击【Connection view】，再点击【显示系统对象】，即可显示【mysql】、【information_schema】、【performance_schema】这些系统自带的数据库。

之前我们查询的mysql.user，就是mysql数据库中的一个非常重要的权限表，记录了允许连接到MySQL数据库系统的账号信息，user表中还记录了用户的权限。双击打开【mysql】数据库，在右侧窗口中点击【表】按钮，即可看到【mysql】数据库中的所有系统表格和权限表。

【mysql】数据库中的【权限表】及其说明如下表所示：

6创建样例数据

为了方便展示权限的授予和撤销，我们先用newbie用户创建样例数据库和样例表，然后基于样例讲解权限的授予和撤销。我们用图形化界面（DBeaver）创建数据库，提前让大家熟悉一下数据库与表格的创建，后面我们将为大家介绍，如何使用SQL代码创建数据库。

首先，我们用newbie账号登录到 MySQL 数据库系统中，然后用DBeaver客户端创建一个数据库【priv_test】。

在priv_test数据库中，创建一个样例表priv_sample。

在【priv_sample】的【属性】界面，填写【表名】，【描述】等信息，然后点击【新建 列】图标，添加一列。

给priv_sample添加一列【name】，数据类型为varchar(100)，其他默认即可（后面的表管理，我们会详细介绍如何用SQL创建表，并介绍数据类型），最后点击【确定】。

在创建一列【age】，数据类型选择【TINYINT】，其他默认，然后点击【确定】。

创建完两列数据后，点击属性界面下方的【Save】，保存我们的设置。

弹出【执行修改】的界面，我们点击【执行】即可。

然后，我们点击【priv_sample】表的【数据】界面按钮，在【网格】界面中点击下方【插入数据】按钮，添加自定义数据。

7权限的授予和撤销

在上面一个例子中，我们使用newbie用户（管理员）创建了样例数据：priv_test数据库，该数据库下有一张priv_sample表。

由于priv_sample表未授权给其他用户查看，其他用户无法检索到该表的相关信息。如图所示，我们登陆user01用户，发现数据库为空。

我们可以把priv_sample表的select权限授予user01，SQL语法如下：

-- SQL 授权语法
grant 权限 on 数据库.表 to '用户'@'登陆位置';

-- 将 priv_sample 表的 select 权限授予 user01
grant select on priv_test.priv_sample to 'user01'@'%';

执行上述授权语句后，user01就可以查看priv_sample表了，注意：需要刷新一下连接。

user01也可以使用 SQL 语句查询该表数据。

select * from priv_test.priv_sample;

在DBeaver中的执行结果如下：

那么上面的这句将priv_sample表的select权限授予给user01的 SQL 语句会对权限表产生什么影响呢？我们用newbie用户打开 mysql 数据库下面的tables_priv，SQL语句如下：

select * from mysql.tables_priv;

在DBeaver中的执行结果如下：

可以看到在tables_priv权限表中，出现了我们的授权记录priv_test数据库的priv_sample表的select权限授予给了user01，Grantor（授权人）是newbie。

如果不希望user01查看priv_sample表，我们可以回收权限，SQL语句如下，注意需要用newbie用户执行该 SQL 语句：

-- SQL 权限撤销语法
revoke 权限 on 数据库.表 from '用户'@'登陆位置';

-- 将 priv_sample 表的 select 权限授予 user01
revoke select on priv_test.priv_sample from 'user01'@'%';

MySQL的权限非常多，我们介绍几个常用的权限，如下表所示：

在实际工作中，我们要谨慎授权，一般由管理员创建一个数据库，然后根据用户实际需求，将该数据库的权限合理地授予给用户。

如果用户user01需要在priv_test数据库中，创建表格，并且需要对表格进行增、删、改、查，以及创建索引，修改表结构等操作（这些操作我们会在表管理具体介绍）。那么授权的SQL语句如下，需要用newbie用户进行操作：

grant create, drop, select, update, delete, insert, index, alter
on priv_test.* to 'user01'@'%';

上述SQL语句中，priv_test.*表示对整个priv_test数据库生效。我们可以在 mysql 数据库的db权限表中看到对应的权限。下表展示了db权限表的部分数据，在执行完授权语句后user01对数据库priv_test的权限写入到了db权限表。

授权语句末尾加上with grant option参数，那么user01可以将其拥有的权限授予给其他用户使用，也就是说user01可以使用grant语句给其他用户授权，但是只能授予user01已经拥有的权限：

grant create, drop, select, update, delete, insert, index, alter
on priv_test.* to 'user01'@'%' with grant option;

当用户user01拥有了grant option后，user01就可以将自己已有的权限授权给其他用户，user01可以使用如下SQL语句，该语句执行后，user02用户可以查看priv_test 数据库中的所有表格。注意：实际工作中，我们授权时一般不添加with grant option参数。

grant select on priv_test.* to 'user02'@'%';

如果要回收用户user01的全部权限，管理员newbie可以使用如下语句：

revoke all privileges, grant option from 'user01'@'%';

SQL语句中，all privileges表示user01拥有的全部权限，grant option表示user01给其他用户授权的能力。

Part4数据库与表管理

在权限管理的创界样例数据小节中，我们已经展示了用DBeaver客户端，创建数据库与表，这种图形化界面操作的方式较为直观，但效率比较低，在实际工作中，我们一般使用SQL命令来创建数据库和表。

8数据库管理

在实际工作中，普通用户一般没有创建数据库的权限，我们也不推荐给普通用户授予该权限。本节，我们将使用管理员newbie进行数据库管理方面的操作。首先，我们创建一个名称为testdb的数据库，使用如下SQL语句：

create database testdb;

DBeaver中的运行结果如下：

在DBeaver中，双击testdb，即可查看具体的数据库信息。

删除数据库使用如下SQL语句：

注意：删除数据库需谨慎

drop database testdb;

DBeaver中的运行结果如下：

9表管理

管理员 newbie 创建好 testdb 数据库后，希望由用户 user01 使用管理数据库的表格，给 user01 授予创建和修改表格结构，以及对表格进行增、删、改、查的权限，且允许 user01 将自己拥有的权限授予给其他普通用户，管理员 newbie 需要执行以下SQL命令：

grant create, alter, insert, delete, update, select, drop
on testdb.* to 'user01'@'%' with grant option;

现在我们使用 user01 创建一张名称为 test01 的数据表，SQL语句如下：

CREATE TABLE test01 (
  id int NOT NULL,
  name varchar(100) NOT NULL,
  age tinyint DEFAULT NULL,
  PRIMARY KEY (id)
) 
ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci 
COMMENT='测试表';

上述SQL语句中的各个参数如下：

• id，name，age 是 test01 的三个列
• int 、varchar(100)、tinyint 是这三个列的数据类型
• NOT NULL 表示一行记录中在 id 和 name 列不能为空
• PRIMARY KEY (id) 表示id列是主键（特殊的列，主键不能为空，不能存在重复值）
• ENGINE=InnoDB，使用Innodb引擎
• DEFAULT CHARSET=utf8mb4，表示 test01 表默认的字符集是utf8mb4
• COLLATE=utf8mb4_0900_ai_ci，表示 test01 表的默认排序规则
• COMMENT='测试表'，表示该表的注释信息

创建数据表需要特别注意列的定义，我们可以把列的定义拆解为三个部分：列名、列的数据类型、列的约束。例如，我们创建一个“地址”列，可以这么写：

address varchar(200) PRIMARY KEY

• address 是列名称
• varchar(200) 是该列的数据类型
• PRIMARY KEY 是该列的约束，表示该列是主键

MySQL中列的数据类型很多，在创建表的时候，需要合理选择数据类型，我们这里展示常用的数据类型，及其适用场景。

数值类型

数值（整数、小数）是我们日常生活中常见的数据类型，比如，考试成绩一般用小数表示，人口一般用整数表示。

在计算机中，整数类型可以分为有符号数和无符号数，这是计算机的设计决定。下表展示了常用的整数类型所需的存储空间和表示范围。如果存入表格的数据超出范围则会报错。

如果要在表格中存储分数、价格等小数类型的数值，可以使用decimal(n,m)定义，其中，n表示数值一共有n位，其中整数占了（n-m）位，小数占了m位，例如，decimal(10,6)，数值共有10位，其中整数占4位，小数占6位。如果存入表格的数据不符合设计，可能会出现报错或者数据存入不正确。

时间类型

在实证分析中，时间序列和面板数据是经常需要分析的数据，因此日期和时间类型的存储也是我们需要注意的。常用的时间类型如下表所示：

字符串类型

字符串类型是用的最多的mysql类型。比如，电话号码，行政区划代码，企业的统一社会信用代码，个人的身份证号，一般都存为字符串类型。常用的字符串类型如下表所示：

注意：char(n)和varchar(n)中括号中n代表字符的个数（或称，字符串的长度），并不代表字节个数，比如CHAR(30)就可以存储30个字符，CHAR(30)占用的字节数则可能是60个字节，具体占用多少个字节要看设计表格时选择的字符集。char(n)需要固定的存储空间，即当存入的字符长度小于n时，所需空间仍然是n个字符所需的存储空间（由于字符编码不确定，所以长度也无法确定，varchar(n)的所需存储空间则是随字符长度而变化的，但字符个数不能超过n个。

了解了MySQL的具体数据类型后，我们在testdb数据库中，创建一个“2013年至2019年浙江省各市（县）总人口数”的数据表，表名记为：zhejiang_county_population_2013_2019，SQL语句如下：

CREATE TABLE testdb.zhejiang_county_population_2013_2019 (
  count_year YEAR COMMENT '统计年份',
  county_name  VARCHAR(5) COMMENT '市（县）名称',
  population INT COMMENT '总人口数量，单位：人'
) 
ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci 
COMMENT='2013年至2019年浙江省各市（县）人口数';

表格结构（空表）创建完毕后，就需要往里面写入数据了。对于社科研究者而言，我们更多地是将已有的数据资料导入数据库中，这部分知识在后面会具体就介绍。如果是数据采集任务，则会频繁地往表格中写入数据。我们往“zhejiang_county_population_2013_2019”这个表格中，写入一些数据，SQL语法如下：

insert into testdb.zhejiang_county_population_2013_2019 
(count_year, county_name, population)
values('2019','杭州市','7953740'),
('2019','宁波市','6084707'),
('2019','温州市','8323647');
commit;

• 第一行：insert into是固定语法，表示插入数据
• 第二行：(count_year, county_name, population)表示需要插入数据的列
• 第三行至第五行，values关键词后面括号中的内容，是需要插入到表格的数据，数据需要用单引号或者双引号括起来。
• 最后的commit表示提交数据，如果mysql开启了自动提交，则不需要这个语句，用管理员账户执行show variables like 'autocommit'; 语句即可查看是否开启自动提交。

如果需要给“zhejiang_county_population_2013_2019”再添加一列 “province”（省份），可以用如下SQL语句：

alter table zhejiang_county_population_2013_2019 add privince varchar(5);

如果要删除这个列，可以使用如下SQL语句：

alter table zhejiang_county_population_2013_2019 drop privince;

如果发现数据有误，假设2019年，杭州市的总人口为7953741，可以使用如下语句修改：

update testdb.zhejiang_county_population_2013_2019
set population = '7953741' where county_name='杭州市' and count_year='2019'

上述语句中，如果没有用where进行条件限定，则表格中的population列的值都将被修改为7953741，因此我们在进行updata操作时，需要非常谨慎。

最后，我们删除表格 "zhejiang_county_population_2013_2019"，可以使用如下语句：

drop table testdb.zhejiang_county_population_2013_2019;

Part5数据备份

数据备份是MySQL数据库管理的重要环节，本小节介绍两种常用的数据备份方式，一般需要备份以下几个部分：数据文件、日志文件、配置文件。

10物理备份

物理备份是指，我们使用操作系统的拷贝、压缩等命令，对数据库的整个安装目录进行备份。这种备份方式的缺点是需要暂停数据库服务，优势是操作简单，速度快。下面，我们对数据库进行一次物理备份。

首先，我们进入安装了MySQL服务器的虚拟机，打开任务管理器。

选择【服务】，找到【MySQL80】，右击【停止】。

MySQL停止后，进入到我们安装MySQL的目录，选择【mysqldata】和【mysqlserver】，右击【压缩文件夹】。

稍等片刻后，出现一个zip文件，我们将其命名为【mysql_backup_20211129.zip】，将这个文件拷贝到其他位置。

接着我们删除【mysqldata】和【mysqlserver】，模拟数据库损毁。

然后，将我们备份的【mysql_backup_20211129.zip】解压到D盘，还原出mysql的文件夹。

重启启动mysql服务，即可恢复使用。

11逻辑备份

逻辑备份是指用相关备份工具，将MySQL数据库中的数据库和表转为SQL语句，写入到文件中。逻辑备份的好处是不需要关闭MySQL服务，但缺点是速度较慢。MySQL自带了逻辑备份的工具：mysqldump，下面，我们使用该工具进行逻辑备份演示。

首先，进入mysqlserver安装目录【D:\mysqlserver\bin】，按住shift，然后点击鼠标右击，选择【在此处打开命令窗口】。

在弹出的窗口中输入：

mysqldump -uroot -prootroot  --databases priv_test > D:\priv_test_backup_20211129.sql

注意：

• -uroot，表示root用户
• -prootroot，表示用户密码是rootroot
• priv_test, 表示备份 priv_test 数据库
• D:\priv_test_backup_20211129.sql，表示将 priv_test 数据库备份到 D:\priv_test_backup_20211129.sql 中。

我们切换到D盘，可以看到备份文件【priv_test_backup_20211129.sql】已经生成。

我们可以用记事本打开它，查看里面的内容，不难发现，mysqldump就是把priv_test这个数据库的内容翻译为sql代码导出，这也是逻辑备份速度较慢的原因所在。

我们进入mysql，删除priv_test数据库，模拟数据损毁。

然后，我们使用如下命令恢复该数据库：

mysql -uroot -prootroot  < D:\priv_test_backup_20211129.sql

进入数据库，确认数据是否恢复成功。

Part6总结

本文主要介绍了以下内容：

1. 数据库的用户管理，可以将你的团队成员加入到mysql数据库中，读取数据库中数据
2. 对数据库的表格设置权限，可以指定特定团队成员读取特定的表格
3. 如何创建数据库与表格，并介绍了mysql数据库常用的数据类型
4. 如何备份数据库，保证数据安全

后期推文预告：

1. 如何将现有的csv、excel等数据导入数据库
2. stata如何与mysql的连接

敬请期待！

星标⭐我们不迷路！

想要文章及时到，文末“在看”少不了！

点击搜索你感兴趣的内容吧

往期推荐

数据Seminar

这里是大数据、分析技术与学术研究的三叉路口

文 | 陈煌杰

    欢迎扫描👇二维码添加关注    

点击下方“阅读全文”了解更多