MYSQL-详细图文使用手册(共38页).docx

《MYSQL-详细图文使用手册(共38页).docx》由会员分享，可在线阅读，更多相关《MYSQL-详细图文使用手册(共38页).docx（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上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、慢。2.MYSQL对比EXCEL相同点：都是用来处理数据的工具，而且特别擅长处理一维二维表形式的数据。不同点：a. Excel的多个sheet之间不能设计复杂的数据关系，而数据库的的表之间可以有复杂的关系，这也是数据库的最大特点，关系型数据库的名字由此而来；b. Excel不能共享，如EXCEL在其他人需要使用时需要单独发送文件，并且修改不能同步，数据库能可以轻松共享，数据随时共享且同步；c. 能力方面，Excel可以实现的数据处理功能数据库都可以做到，同时又具备很多Excel没有的功能，如SQL查询、数据聚合、数据索引等等；d. EXCEL是电子表格，其特点是所见即所得，可视性强，操作简单，

3、不需编程就能制作相对复杂的表格，数据库较为概念化，可视性不强，需要学习SQL语句；e. EXCEL 最大储存100万行数据（2017版本以上），过万级数据计算会出现卡顿，MYSQL数据库单表能储存64PB（1PB=1024TB），每秒能处理百万级数据。类似关系：MYSQL上的数据库（schema）相当于EXCEL的工作簿（workbook）文件;MYSQL上数据库内的表（table）相当于EXCEL工作簿内的工作表（sheet）;MYSQL上表内的字段（column）相当于EXCEL工作表内的表头；MYSQL上字段的数据类型（datatype）相当于EXCEL单元格数字格式；（数据库字段下数据

4、类型必须统一，在整数型的字段内不能出现文字或非整形数字，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.选中“ Iaccep

5、t the license terms”单击Next。2.选择安装类型，选择“Dveloper Default”，默认模式安装，然后点击next。3.点击next，继续4. 执行MySQL的下载和安装，单击 Execute ，然后稍等安装安装完成后点击next。5. 点击next，继续，进入配置设置。6. 选择第一项点击next，继续7.设置默认端口3306，不用修改，点击next8. 点击next，继续9.设置管理员密码，本人设置密码为qwe123（一定要记住）10.不需要更改，点击next11. 应用服务器配置，单击“Execute”执行所有配置步骤。12. 点击next，直到输入账号密码

6、，在password填写第9部填的密码，点击check后点击next13.点击Execute应用配置 ，然后点击Finish14. 点击next，继续15. 点击finish完成安装。三、MYSQL搭建1. 设置环境变量右键计算机-属性高级系统设置高级环境变量分别在用户变量和系统变量的PATH编辑内加入mysql的路径，如下：在变量值后面输入 ; +mysql.exe的路径，默认为C:Program FilesMySQLMySQL Server 8.0bin，即：; C:Program FilesMySQLMySQL Server 8.0bin点击确认完成。在点击开始，输入cmd，在命令符上输

7、入mysql 若提示mysql 不是内部或外部命令表示没有成功配置环境变量，请重新设置；2. 设置开启外网权限1. 点击开始，输入cmd，打开命令符窗口2. 输入mysql -u root p 回车然后输入管理员的密码3. 打开 mysql 数据库，在命令行输入use mysql4. 检索 user 和 host 字段，在命令行输入select user, host from user;5. 如果没有 host = % 的用户，则执行下面语句,将localhost修改为%：update user set host=% where user=root;再输入select user, host f

8、rom 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

9、. 完成以上设置后，所有用户都可以通过该电脑IP地址+3306端口连接到该数据库；3.连接数据库1. 查看本地IP地址方法点击开始输入CMD打开命令符窗口输入命令 ipconfig一般是第一个本地连接的IPV4 即：192.168.137.1312. 使用MySQL Workbench 8.0 CE连接数据库A. 打开MySQL Workbench 8.0 CE，默认路径C:Program FilesMySQLMySQL Workbench 8.0 CEMySQLWorkbench.exeB. 按下图操作输入IP地址及账号密码C. 点击右下角的Test Connection测试连接，弹出Suc

10、cessfully表示成功连接；D. 在主界面双击刚创建的连接，即可打开数据库，并进入数据库界面3. 使用EXCEL 2016连接数据库(需要是EXCEL2016专业增强版以上才可以)A. 打开一个EXCEL，点击数据新建查询从数据库从MYSQL数据库B. 输入IP地址和数据库名C. 选择数据库，然后输入账号密码，点击连接即可完成连接查看数据库内容4. 更改数据储存位置因为安装默认保存数据路径在系统C盘，为了避免磁盘空间不足，需要更改数据库储存位置。1. 关闭MYSQL服务；打开任务管理器，在服务选卡上找到MYSQL80，右击选择停止服务；2. 找到MYSQL配置文件my.ini，默认位置C:

11、ProgramDataMySQLMySQL 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

12、 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 Schemas3. 在窗口内填入新建数据库名称，比如test，点击右下角的Apply4. 在弹出的窗口内点击Apply确认创建；其中红框内的命令CR

13、EATE SCHEMA test ; 创建数据库的SQL命令，我们在创建数据库也同样可以使用命令的形式，只需要在命令窗口输入语句 CREATE SCHEMA 数据库名 ; 完成创建；(所有符号都需要是英文符号)5. 使用SQL语句创建数据test1在命令窗口输入CREATE SCHEMA test1 ; 口点击执行命令；在左边Navigator选卡下的SCHEMAS选卡内右击Refresh刷新就可以看到创建了步骤4的数据库test和test1；输出窗口也可以看到执行结果；3. 创建数据表在test数据库下创建一个学生成绩数据表右击test数据库下的Tables，选择Create Tables,

14、在新窗口内填写表格名，创建字段名，姓名和科目字段数据类型选择VARCHAR(10)，VARCHAR(10)代表0-10个字符串类型，成绩选择FLOAT浮动小数点的较小的数；点击Apply确认；同样，系统会弹出创建表格的SQL语句，我们也可以使用语句创建（可参考本章节步骤2.5）*数据类型数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。类别类型大小描述说明字符串CAHR(Length)Length字节定长字段，长度为0255个字符1255个字符的定长串，它的长度必须在创建时指定，否则MySQL假定为CHAR（1）字符串VARCHAR(Len

15、gth)String长度+1字节或String长度+2字节变长字段，长度为065 535个字符可变长度，最多不超过255字节，如在创建时指定VARCHAR（n），则可存储0n个字符的变长串字符串TINYTEXTString长度+1字节字符串，最大长度为255个字符同TEXT，最大长度为255字节字符串TEXTString长度+2字节字符串，最大长度为65 535个字符最大长度为64K的变长文本字符串MEDIUMINTString长度+3字节字符串，最大长度为16 777 215个字符同TEXT，最大长度为16K字符串LONGTEXTString长度+4字节字符串，最大长度为4 294 967

16、295个字符同Text，最大长度为4GB（纯文本，一般不会到4G）字符串ENUM1或2字节Enumeration(枚举)的简写，这意味着每一列都可以具有多个可能的值之一接受最多64K个串组成的预定义集合的某个串字符串SET1、2、3、4或8字节与ENUM一样，只不过每一列都可以具有多个可能的值接受最多64K个串组成的预定义集合的零个或多个串数值TINYINT(Length)1字节范围：-128127，或者0255（无符号）数值SMALLINT(Length)2字节范围：-32 76832 767，或者065 535（无符号）数值MEDIUMINT(Length)3字节范围：-8 388 608

17、8 388 607，或者016 777 215（无符号）数值INT(Length)4字节范围：-2 147 483 6482 147 483 647，或者04 294 967 295（无符号）数值BIGINT(Length)8字节范围：-9 223 372 036 854 775 8089 223 372 036 854 775 807，或者018 446 744 073 709 551 615（无符号）数值FLOAT(Length, Decimals)4字节具有浮动小数点的较小的数最小非零值：1.e 38数值DOUBLE(Length, Decimals)8字节具有浮动小数点的较大的数最小非

18、零值：2.72014e 308数值DECIMAL(Length, Decimals)Length+1字节或Length+2字节存储为字符串的DOUBLE，允许固定的小数点可变；其值的范围依赖于m 和d日期和时间DATE3字节采用YYYY-MM-DD格式范围：1000-01-019999-12-31日期和时间DATETIME8字节采用YYYY-MM-DD HH:MM:SS格式范围：1000-01-01 00:00:009999-12-31 23:59:59日期和时间TIMESTAMP4字节采用YYYYMMDDHHMMSS格式；可接受的范围终止于2038年范围：001407日期和时间TIME3字节

19、采用HH:MM:SS格式范围：-838:59:59838:59:59日期和时间YEAR1字节采用YYYY格式范围：19012155二进制TITYBLOB最大长度为255字节二进制BLOB最大长度为64KB二进制MEDIUMBLOB最大长度为16MB二进制LONGBLOB最大长度为4GB*绿色为常用的数据类型4.插入数据使用INSERT INTO语句插入数据；语法：INSERT INTO 数据库名.表名 （字段名1,字段名2）VALUES(值1,值2)；例如：INSERT INTO test.学生成绩 (姓名,科目,成绩) VALUES (小明,语文,84);(所有符号都需要是英文符号)插入多行

20、数据：INSERT INTO test.学生成绩 (姓名,科目,成绩) VALUES (小明,数学, 92), (小明,英语, 76), (小红,语文, 89), (小红,数学, 52), (小红,英语, 79), (小白,语文, 65), (小白,数学, 47), (小白,英语, 89), (小黑,语文, 88), (小黑,数学, 68),(小黑,英语, 79);(所有符号都需要是英文符号)执行后，提示成功插入11行数据；5.查询数据简单查询查询使用SELECT语句，基础语法为：SELECT 字段名 FROM 数据库.表名;例如:SELECT * FROM test.学生成绩;其中*代表的是

21、所有的意思，即查询数据库test下学生成绩表内所有字段数据，结果如下：只查询姓名和成绩字段：SELECT 姓名,成绩 FROM test.学生成绩;结果如下：条件查询需要查询60分以下的数据，就需要增加WHERE语句进行条件查询，语法：SELECT 字段名 FROM 数据库.表名 WHERE 条件；例如：SELECT * FROM test.学生成绩 WHERE 成绩80;分组聚合查询1. 聚合查询平均分，则需要聚合运算平均数使用公式为AVG（字段名）例如：SELECT AVG(成绩) FROM test.学生成绩;给计算结果字段重新命名使用AS，例如：SELECT AVG(成绩) AS 平均

22、分 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 分

23、组字段;例如每个学生分数在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 条

24、件例如，删除成绩在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 条件

25、;例如修改小明数学成绩为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, p

26、ip install Xlsxwriter;# !/usr/bin/python3import pymysqlimport xlrdimport mathimport xlsxwriterdef 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_wor

27、kbook(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)

28、%( 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,ops5000 * n1:5000 * (n1 + 1) cur.close() mit() conn.close() print(sucess

29、)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) she

30、et = workbook.add_worksheet(table) # 写上字段信息 for field in range(0, len(fields): sheet.write(0, field, fieldsfield0) for row in range(1, len(results) + 1): for col in range(0, len(fields): sheet.write(row, col, resultsrow - 1col) workbook.close()cur.close()mit()conn.close() print(sucess)if _name_ = _main_:#EXCEL导入mysql例子db=test #数据库名称table=学生成绩 #表名path=rd:学生成绩.xlsx #导入数据库的EXCEL路径xlstosql(db,table,path) #执行导入函数 #导出EXCEL数据例子：sql=select * from test.学生成绩 #SQL查询语句path=rE:学生成绩.xlsx #保存EXCEL的路径 xlstosql(path) #执行导出函数专心-专注-专业