MYSQL 详细图文使用手册

MYSQL 详细图文使用手册目录
一、前言
二、MYSQL安装
1.下载
2.安装
三、MYSQL 搭建
1.设置环境变量
2.设置开启外网权限
3.连接数据库
4.更改数据储存位置
四、MYSQL创建数据库
1.MySQL Workbench 8.0 CE界面介绍
2.创建数据库
3.创建数据表
4.插入数据
5.查询数据
6.删除数据
7.修改数据
五、使用python导入导出数据
一、前言
1. 什么是 MySQL？
MySQL 是一种数据库。

数据库定义了存储信息的结构。

以表的方式存储信息，设置有索引可以快速定位到需要的内容，避免查找数据时要遍历整个库，耗费资源速度也慢。

2.MYSQL对比EXCEL
相同点：
都是用来处理数据的工具，而且特别擅长处理一维二维表形式的数据。

不同点：
a.Excel的多个sheet之间不能设计复杂的数据关系，而数据库的的表之间可以有复杂的
关系，这也是数据库的最大特点，关系型数据库的名字由此而来；
b.Excel不能共享，如EXCEL在其他人需要使用时需要单独发送文件，并且修改不能同
步，数据库能可以轻松共享，数据随时共享且同步；
c.能力方面，Excel可以实现的数据处理功能数据库都可以做到，同时又具备很多Excel
没有的功能，如SQL查询、数据聚合、数据索引等等；
d.EXCEL是电子表格，其特点是所见即所得，可视性强，操作简单，不需编程就能制作
相对复杂的表格，数据库较为概念化，可视性不强，需要学习SQL语句；
e.EXCEL 最大储存100万行数据（2017版本以上），过万级数据计算会出现卡顿，
MYSQL数据库单表能储存64PB（1PB=1024TB），每秒能处理百万级数据。

类似关系：
MYSQL上的数据库（schema）相当于EXCEL的工作簿（workbook）文件;
MYSQL上数据库内的表（table）相当于EXCEL工作簿内的工作表（sheet）;
MYSQL上表内的字段（column）相当于EXCEL工作表内的表头；
MYSQL上字段的数据类型（datatype）相当于EXCEL单元格数字格式；（数据库字段下数据类型必须统一，在整数型的字段内不能出现文字或非整形数字，EXCEL会自动识别格式类型）
二、MYSQL安装
1.下载
MYSQL是由瑞典MySQL AB 公司开发，目前属于Oracle 旗下开源免费产品，在官网下载即可：
点击download下载，在跳转的页面后选择 No thanks, just start my download.下载
2.安装
注：若操作过程与教材不一致，操作过程遇到同时存在Execute与Next按钮，优先点击Execute，否则可能会出错；
下载好之后，双击mysql-installer-web-community-5.7.21.0.msi，进入安装步骤：
1.选中“ Iaccept the license terms”单击Next。

2.选择安装类型，选择“Dveloper Default”，默认模式安装，然后点击next。

3.点击next，继续
4. 执行MySQL的下载和安装，单击 Execute ，然后稍等安装安装完成后点击next。

5. 点击next，继续，进入配置设置。

6. 选择第一项点击next，继续
7.设置默认端口3306，不用修改，点击next
8. 点击next，继续
9.设置管理员密码，本人设置密码为qwe123（一定要记住）
10.不需要更改，点击next
11. 应用服务器配置，单击“Execute”执行所有配置步骤。

12. 点击next，直到输入账号密码，在password填写第9部填的密码，点击check后点击next
13.点击Execute应用配置，然后点击Finish
14. 点击next，继续
15. 点击finish完成安装。

三、MYSQL搭建
1. 设置环境变量
右键计算机--属性—高级系统设置—高级—环境变量
分别在用户变量和系统变量的PATH—编辑内加入mysql的路径，如下：
在变量值后面输入; +mysql.exe的路径，默认为C:\Program Files\MySQL\MySQL Server 8.0\bin，即：
; C:\Program Files\MySQL\MySQL Server 8.0\bin
点击确认完成。

在点击开始，输入cmd，在命令符上输入mysql 若提示mysql 不是内部或外部命令表示没有成功配置环境变量，请重新设置；
2. 设置开启外网权限
1.点击开始，输入cmd，打开命令符窗口
2.输入mysql -u root –p回车
然后输入管理员的密码
3.打开 mysql 数据库，在命令行输入
use mysql
4.检索 user 和 host 字段，在命令行输入
select user, host from user;
5.如果没有 host = % 的用户，则执行下面语句,将localhost修改为%：
update user set host='%' where user='root';
再输入select user, host from user; 查看结果
root 用户的host已经修改为%了；
注："%"为代表任意的ip地址，如果想设为特定的值也可以设定为特定的值（以通配符%的内容增加主机/IP地址，也可以直接增加IP地址），这样就可以让对应的IP访问数据库；
6.给 root 授权（所有权限），输入命令：
grant all privileges on *.* to 'root'@'%' identified by 'qwe123' with grant option;
其中*.*代表开放所有数据库，root 为当前的账号，"%"为代表任意的ip地址，qwe123为密码
7.让赋予的权限立即生效，输入命令
flush privileges;
8.完成以上设置后，所有用户都可以通过该电脑IP地址+3306端口连接到该数据库；
3. 连接数据库
1.查看本地IP地址方法
点击开始—输入CMD打开命令符窗口
输入命令ipconfig
一般是第一个本地连接的IPV4 即：192.168.137.131
2.使用MySQL Workbench 8.0 CE连接数据库
A.打开MySQL Workbench 8.0 CE，默认路径C:\Program Files\MySQL\MySQL
Workbench 8.0 CE\MySQLWorkbench.exe
B.按下图操作输入IP地址及账号密码
C.点击右下角的Test Connection测试连接，弹出Successfully表示成功连接；
D.在主界面双击刚创建的连接，即可打开数据库，并进入数据库界面
3.使用EXCEL 2016连接数据库(需要是EXCEL2016专业增强版以上才可以)
A.打开一个EXCEL，点击数据—新建查询—从数据库—从MYSQL数据库
B.输入IP地址和数据库名
C.选择数据库，然后输入账号密码，点击连接即可完成连接查看数据库内容
4. 更改数据储存位置
因为安装默认保存数据路径在系统C盘，为了避免磁盘空间不足，需要更改数据库储存位置。

1.关闭MYSQL服务；
打开任务管理器，在服务选卡上找到MYSQL80，右击选择停止服务；
2.找到MYSQL配置文件my.ini，默认位置C:\ProgramData\MySQL\MySQL Server 8.0
注：ProgramData默认是隐藏文件，可以直接复制路径进行打开；
3.更改My.ini设置内容
使用记事本打开my.ini，查找datadir= 这行参数，默认是在96行
将datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data修改为要更改后的路径，如
datadir=D:/ /Data
然后点击保存；
如果无法保存，要设置文件控制权限，右击my.ini—属性—安全—编辑，把所有用户都勾上完全控制，确认。

4.复制data文件夹到更改后路径
把原来C:/ProgramData/MySQL/MySQL Server 8.0/Data文件夹复制到更改后的位置5.在任务管理器重启启动mysql服务（类似步骤1），完成。

注：若无法启动服务，需要设置更改后的data 文件夹设置完全控制文件夹权限（类似步骤3）
四、在MYSQL基础查询方法
1. MySQL Workbench 8.0 CE界面介绍
2. 创建数据库
1.打开MySQL Workbench 8.0 CE并连接数据库；
2.在左边Navigator选卡下的SCHEMAS选卡内右击—Create Schemas
3.在窗口内填入新建数据库名称，比如test，点击右下角的Apply
4.在弹出的窗口内点击Apply确认创建；其中红框内的命令CREATE SCHEMA `test` ;
创建数据库的SQL命令，我们在创建数据库也同样可以使用命令的形式，只需要在命令窗口输入语句CREATE SCHEMA `数据库名` ;完成创建；(所有符号都需要是英文符号)
5.使用SQL语句创建数据test1
在命令窗口输入CREATE SCHEMA `test1` ;口点击执行命令；在左边Navigator
选卡下的SCHEMAS选卡内右击Refresh刷新就可以看到创建了步骤4的数据库test和test1；输出窗口也可以看到执行结果；
3. 创建数据表
在test数据库下创建一个学生成绩数据表
右击test数据库下的Tables，选择Create Tables,在新窗口内填写表格名，创建字段名，
姓名和科目字段数据类型选择VARCHAR(10)，VARCHAR(10)代表0-10个字符串类型，
成绩选择FLOAT浮动小数点的较小的数；点击Apply确认；
同样，系统会弹出创建表格的SQL语句，我们也可以使用语句创建（可参考本章节步骤2.5）
*数据类型
数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

类别类型大小描述说明
字符串CAHR(Length) Length字节定长字段，长度为
0~255个字符1~255个字符的定长串，它的长度必须在创建时指定，否则MySQL假定为CHAR（1）
字符串VARCHAR(Length) String长度+1字节或
String长度+2字节变长字段，长度为
0~65 535个字符
可变长度，最多不超过255字
节，如在创建时指定VARCHAR
（n），则可存储0~n个字符的
变长串
字符串TINYTEXT String长度+1字节字符串，最大长度为
255个字符
同TEXT，最大长度为255字节
字符串TEXT String长度+2字节字符串，最大长度为
65 535个字符
最大长度为64K的变长文本
字符串MEDIUMINT String长度+3字节字符串，最大长度为
16 777 215个字符
同TEXT，最大长度为16K
字符串LONGTEXT String长度+4字节字符串，最大长度为4
294 967 295个字符同Text，最大长度为4GB（纯文本，一般不会到4G）
字符串ENUM 1或2字节Enumeration(枚举)的简
写，这意味着每一列都
可以具有多个可能的值
之一接受最多64K个串组成的预定义集合的某个串
*绿色为常用的数据类型
4. 插入数据
使用INSERT INTO语句插入数据；
语法：INSERT INTO 数据库名.表名（字段名1,字段名2）VALUES(值1,值2)；例如：
INSERT INTO test.`学生成绩` (`姓名`,`科目`,`成绩`) VALUES ('小明','语文',84); (所有符号都需要是英文符号)
插入多行数据：
INSERT INTO test.`学生成绩` (`姓名`,`科目`,`成绩`) VALUES ('小明','数学', 92), ('小明','英语', 76),
('小红','语文', 89),
('小红','数学', 52),
('小红','英语', 79),
('小白','语文', 65),
('小白','数学', 47),
('小白','英语', 89),
('小黑','语文', 88),
('小黑','数学', 68),
('小黑','英语', 79);
(所有符号都需要是英文符号)
执行后，提示成功插入11行数据；
5. 查询数据
简单查询
查询使用SELECT语句，基础语法为：
SELECT 字段名 FROM 数据库.表名;
例如:
SELECT * FROM test.学生成绩;
其中*代表的是所有的意思，即查询数据库test下学生成绩表内所有字段数据，结果如下：
只查询姓名和成绩字段：
SELECT 姓名,成绩 FROM test.学生成绩;
结果如下：
条件查询
需要查询60分以下的数据，就需要增加WHERE语句进行条件查询，语法：
SELECT 字段名 FROM 数据库.表名 WHERE 条件；
例如：
SELECT * FROM test.学生成绩 WHERE 成绩<60;
需要多条件查询，则在条件与条件之间使用AND、OR 和与或连接，例如查询语文成绩80
分以上的数据：
SELECT * FROM test.学生成绩 WHERE 科目='语文' AND 成绩>80;
分组聚合查询
1.聚合查询平均分，则需要聚合运算
平均数使用公式为AVG（字段名）例如：
SELECT AVG(成绩) FROM test.学生成绩;
给计算结果字段重新命名使用AS，例如：
SELECT AVG(成绩) AS 平均分 FROM test.学生成绩;
常见聚合运算有以下:
SUM ( ) 求和
AVG ( ) 平均值
COUNT ( ) 表达式中记录的数目
MAX 最大值
MIN 最小值
2.分组聚合查询：
查询每个同学的平均成绩需要使用GROUP BY 进行查询，语法：
SELECT 分组字段,聚合字段 FROM 数据库.表名 GROUP BY 分组字段;
例如：
SELECT 姓名,AVG(成绩) AS 平均分 FROM test.学生成绩 GROUP BY 姓名;
3.分组聚合条件查询
A.聚合前的条件使用WHERE，语法：
SELECT 分组字段,聚合字段 FROM 数据库.表名 WHERE 条件 GROUP BY 分组字段;
例如每个学生分数在80分以上的科目数：
SELECT 姓名,COUNT('科目') AS 80分以上科目数FROM test.学生成绩WHERE 成绩>80 group by 姓名;
B.聚合后的条件使用HAVING，语法：
SELECT 分组字段,聚合字段 FROM 数据库.表名 GROUP BY 分组字段 HAVING 条件;
例如查询平均分在75分以上的数据：
SELECT 姓名,AVG(成绩) AS 平均分 FROM test.学生成绩 GROUP BY 姓名 HAVING 平均分>75;
6. 删除数据
删除数据使用DELETE语句，语法：
delete from 数据库.表名 where 条件
例如，删除成绩在60分以下的数据
Delete from test.学生成绩 where 成绩<60;
若提示1175错误代码，是由于运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令如下命令：
SET SQL_SAFE_UPDATES = 0;
再输入
Delete from test.学生成绩 where 成绩<60;
完成删除：
重新查询下数据库数据：
若要还原安全模式，输入命令：SET SQL_SAFE_UPDATES = 1;
7. 修改数据
修改数据使用UPDATE语句，语法：
UPDATE 数据库.表名 SET 修改内容 WHERE 条件;
例如修改小明数学成绩为99分:
UPDATE `test`.`学生成绩` SET `成绩` = 99 WHERE `姓名` = '小明' AND `科目` = '数学';
重新查询下数据：
五、使用python导入导出数据
由于MYSQL在自带软件上无法实现与EXCLE交互，所以使用python进行将EXCEL上传到MYSQL和下载查询结果到EXCEL，python 需要用到的库：
Pymysql ：python操作MYSQL数据操作的库；
Xlrd：用于读取EXCEL数据的库
Xlsxwriter：用于写入EXCEL的库
3个库的安装直接使用PIP即可：pip install Pymysql, pip install Xlrd, pip install Xlsxwriter;
# !/usr/bin/python3
import pymysql
import xlrd
import math
import xlsxwriter
def xlstosql(db,table,path): ##EXCEL导入mysql
conn = pymysql.connect(
host='192.168.137.131', ###数据库的IP地址
user='root', ##数据库的账号
passwd='qwe123', ##数据库的密码
db=db ##操作的数据库名称
)
# 获得游标
cur = conn.cursor()
book = xlrd.open_workbook(path) ##打开EXCEL
sheet = book.sheet_by_index(0) ##打开sheet1
ops = []
headers=[]
for hc in range(0, sheet.ncols):
headers.append(sheet.cell(0, hc).value) # 获取表字段headers_l=len(headers)
val='%s'+',%s'*(headers_l-1) ##添加N个%s
headers=str(headers).strip('[]').replace('\'','') ##格式化字段
sql='insert into %s. %s (%s) values (%s)'%( db,table,headers,val) for r in range(1, sheet.nrows):
values = []
for c in range(0,sheet.ncols):
values.append(sheet.cell(r, c).value)
ops.append(values)
n = math.ceil(len(ops) / 5000)
for n1 in range(0, n):
cur.executemany(sql,ops[5000 * n1:5000 * (n1 + 1)])
cur.close()
mit()
conn.close()
print('sucess')
def sqltoxlsx(sql,path): ##############导出xlsX格式
conn = pymysql.connect(
host='192.168.137.131', ###数据库的IP地址
user='root', ##数据库的账号
passwd='qwe123', ##数据库的密码
db='test' ##操作的数据库名称
)
# 获得游标
cur = conn.cursor()
cur.execute(sql)
results = cur.fetchall() #所有结果
fields = cur.description ##获取字段
workbook = xlsxwriter.Workbook(path)
sheet = workbook.add_worksheet('table')
# 写上字段信息
for field in range(0, len(fields)):
sheet.write(0, field, fields[field][0])
for row in range(1, len(results) + 1):
for col in range(0, len(fields)):
sheet.write(row, col, results[row - 1][col])
workbook.close()
cur.close()
mit()
conn.close()
print('sucess')
if __name__ == '__main__':
####EXCEL导入mysql例子
db=’test’ ##数据库名称
table=’学生成绩’ ##表名
path=r’d:\学生成绩.xl sx’ ##导入数据库的EXCEL路径xlstosql(db,table,path) ##执行导入函数
##导出EXCEL数据例子：
sql=’select * from test.学生成绩’ ##SQL查询语句
path=r'E:\学生成绩.xlsx' ###保存EXCEL的路径xlstosql(path) ##执行导出函数。