工信版（中职）数据采集技术（初级）项目8：数据库基础及采集教学课件.pptx

Y CF（中职）数据采集技术（初级）项目8：数据库基础及采集教学课件CONTENTS 任务一：安装MySQL 任务二：创建数据库 任务三：查询数据库 任务四：创建索引 任务五：数据库数据采集项目情景零 某公司是一家民营的手机销售企业，主要从事各类手机的批发，现有客户10000余家。现将为该公司开发一套手机销售管理系统，对公司的手机销售业务进行计算机管理，以保证数据的安全性，提高工作效率。经理：小张，咱们公司现在再谈一个手机销售企业的项目，该公司想开发一套手机销售管理系统？小张：开发系统需要用到数据库，那决定使用哪个数据库呢？经理：这个还需要你去调研一下。小张：经理，我查阅了数据库相关资料，感觉这个项目用MySQL比较好。经理：恩，我也发现现在市面上中小型项目都是用MySQL，那就用这个数据库吧。小张：好的，没问题。经理：那你根据项目具体的需求去设计数据库吧。小张：保证完成任务。学习目标零根据公司的需求，设计一个手机销售管理数据库，使用MySQL作为管理系统的数据库软件，以满足手机销售系统的存库查询、客户管理、订单管理等业务操作。通过此项目掌握以下内容：1：安装MySQL数据库。2：创建数据库及数据库表并插入对应的数据。3：使用SQL语句查询数据库表中的信息。4：创建索引，查询相关内容。5：基于Logstash实现数据库数据采集。知识储备任务一：安装MySQL 壹MySQL 数据库简介MySQL数据库由瑞典MySQLAB公司开发，目前属于Oracle公司旗下的产品。作为关系型数据库最好的应用软件之一，MySQL是开放源代码的，因此任何人都可以下载并根据自己的需要对其进行修改。MySQL数据库是一个精巧的SQL数据库管理系统，主要有以下特点：超强的稳定性 支持大型数据库 支持多种字符集存储 可移植性好 强大的查询功能任务描述任务一：安装MySQL 壹安装配置MySQL软件的思路如下：（1）进入MySQL官网，进行免安装版软件下载。（2）配置MySQL。（3）验证是否配置成功。第一步：下载安装包任务一：安装MySQL 壹进入MySQL官网，点击“DOWNLOADS”，找到如左图所示，点击“MySQLCommunity(GPL)Downloads”链接。然后点击“MySQLCommunityServer”，效果如右图所示。官网地址：https:/任务一：安装MySQL 壹下载如图所示免安装版本MySQL，并将下载好的软件包放入目录“C:mysql”，目录位置可根据自身情况而定第一步：下载安装包在解压目录下，创建配置文件：my.ini，文件内容为：mysqldport=3306character_set_server=utf8basedir=C:mysqldatadir=C:mysqldataclientdefault-character-set=utf8任务一：安装MySQL 壹第二步：配置MySQL第二步：配置MySQL任务一：安装MySQL 壹 以管理员身份打开命令提示符，效果如下图所示。使用cd切换目录，转到MySQL的bin目录下。第二步：配置MySQL任务一：安装MySQL 壹 使用mysqld-install安装MySQL的服务。使用mysqld-initialize-console初始化MySQL，对该处生成的初始化随机密码务必记住。第二步：配置MySQL任务一：安装MySQL 壹 使用netstartMySQL开启MySQL的服务。使用MySQL-uroot-p登录验证，登录成功效果如下图所示。第二步：配置MySQL任务一：安装MySQL 壹 系统自动生成的密码不好记忆和使用，所以使用alteruserrootlocalhostidentifiedbyroot;命令修改密码。使用命令exit退出后，使用MySQL-uroot-p登录验证新密码。第二步：配置MySQL任务一：安装MySQL 壹设置系统的全局变量：点击“我的电脑”-“属性”-高级系统设置-环境变量,新建系统变量mysql，变量值MySQL目录。第二步：配置MySQL任务一：安装MySQL 壹设置系统的全局变量：把新建的MySQL变量添加到Path路径变量中，点击确定，即完成。第三步：验证是否配置成功任务一：安装MySQL 壹配置完成之后，每当我们想要用命令行使用MySQL时，只需要win+R，-输入cmd打开命令行，之后输入登录sql语句MySQL-urootp和密码即可。第三步：验证是否配置成功任务一：安装MySQL 壹Linux 下安装MySQL 问题及解决办法：初始化MySQL时提示错误：./mysqld:errorwhileloadingsharedlibraries:libnuma.so.1:cannotopensharedobjectfile:Nosuchfileordirectory原因：linux系统缺少numactl工具导致解决办法：执行以下命令安装numactlyum-yinstallnumactl知识储备任务二：创建数据库 贰结构化查询语言SQL（StructuredQueryLanguage）是最重要的关系数据库操作语言，经过多年的发展，SQL语言已成为关系数据库的标准语言。SQL语言不同于Java、Python等程序设计语言，它是只能被数据库识别的指令，但在程序设计中，可以利用其他编程语言组织SQL语句发送给数据库，数据库再执行相应的操作。根据功能划分，SQL语言主要由以下四个部分组成。DML(Data Manipulation Language，数据操纵语言)：用来插入、修改和删除数据库中的数据，主要包括INSERT、UPDATE、DELETE命令。DDL(Data Definition Language，数据定义语言)：用来建立数据库、建立表等，主要包括CREATEDATABASE、CREATETABLE等。DQL(Data Query Language，数据查询语言)：用来对数据库中的数据进行查询，使用SELECT命令完成查询。DCL(Data Control Language，数据控制语言)：用来控制数据库组件的存取许可、存取权限等，主要包括GRANT、REVOKE命令。任务描述任务二：创建数据库 贰 小张安装完数据库之后，开始对项目需求进行分析，发现确定数据字典和创建表是开发数据库中必不可少的阶段。本任务是创建数据库及数据库表并插入对应的数据，创建数据库及数据库表的思路如下：根据需求确定数据字典。根据数据字典创建数据库。根据提供的数据进行数据库信息的插入。第一步：确定数据表结构任务二：创建数据库 贰经过对项目的需求分析，需要创建3个表，库存表stock、会员表user、订单表ordertb。库存表stock结构：字段名 字段说明 数据类型 长度 允许为空约束 备注mobID 手机编号 varchar 50 非空 主键 brand 品牌 varchar 50 非空 model 型号 varchar 30 非空 color 颜色 varchar 30 非空 memSize 内存大小 varchar 10 非空 price 单价 float 非空 stockNum 库存数量 int 非空 字段名 字段说明 数据类型 长度允许为空 约束 备注uId 会员编号 varchar 10 非空 主键 uName 会员姓名 varchar 50 非空 password 密码 varchar 20 非空 sex 性别 char 2 非空 email 会员邮箱 varchar 30 可 phone 联系电话 varchar 20 可 address 通信地址 varchar 50 可 会员表user结构：第一步：确定数据表结构任务二：创建数据库 贰订单表ordertb结构：字段名称 字段说明 数据类型 长度 可否为空 约束 备注oid 订单号 int 10 非空 主键 自动编号uid 会员编号 varchar 10 非空 外键 引用user 表主键sid 手机编号 varchar 50 非空 外键 引用stock 表主键orderNum 订购数量 int 非空 status 订单状态 tinyint 非空 1 表已处理，0 表待处理orderTime 订购日期 datetime 非空 deliveryTime 发货时间 datetime 非空 第二步：创建数据库任务二：创建数据库 贰创建数据库mobileSale，其SQL语句代码如下。create database mobileSale;第三步：创建数据表任务二：创建数据库 贰根据设计出的“手机销售管理系统”数据表的结构，使用createtable语句创建数据表。（1）使用上一步骤创建好的数据库“usemobileSale;”，创建库存表stock，创建库存表student的SQL语句如下。create table stock(mobID varchar(50)primary key,brand varchar(50)not null,model varchar(30)not null,color varchar(30)not null,memSize varchar(10)not null,price float not null,stockNum int not null);执行上述命令执行完成后，可通过DESC命令查看stock表的结构信息。第三步：创建数据表任务二：创建数据库 贰（2）创建用户表user，创建用户表user的SQL语句如下。create table user(uID varchar(10)primary key,uName varchar(50)not null,passwd varchar(20)not null,sex char(2)not null check(sex in(男,女),email varchar(30),phone varchar(30),address varchar(50)default 地址不详;);约束：primarykey、notnull、foreignkey、defaultOracle、SQLSERVER:check约束（值范围检查）MySQL、SQLSERVER:auto_increment(主键自增，从1开始，每次增1）第三步：创建数据表任务二：创建数据库 贰（3）创建订单表ordertb，创建订单表ordertb的SQL语句如下。create table ordertb(orderID varchar(10)auto_increment,uID varchar(10)not null,mobID varchar(50)not null,orderNum int not null,orderTime date not null,status tinyint(1),deliveryTime date,primary key(orderID);Foreign key(uid)references user(uid);Foreign key(mobID)references stock(mobID);知识储备任务二：创建数据库 贰1.创建数据表其创建数据表的语法格式如下所示。参数说明：数据表名：是需要创建的数据表的名字。字段名：是指数据表中的列名。数据类型：是指表中列的类型，用于指定可以存储指定类型格式的数据。createtable数据表名(字段1数据类型，字段2数据类型，字段n数据类型);注意：在MySQL中，在录入操作命令时，所有的符号均应使用英文半角字符，如小括号、逗号、单引号或双引号等。知识储备任务二：创建数据库 贰2.查看数据表数据表创建之后，用户可以对表的创建信息进行查看，如查看所有表、查看表结构、查看表的定义等。（1）查看所有表：showtables;（2）查看指定表的结构信息：“describe表名;”，通常简写为“desc表名;”（3）查看指定表的定义信息：showcreatetable数据表名;知识储备任务二：创建数据库 贰3.修改数据表数据表创建之后，用户可以对表的结构信息进行修改，如修改表名、修改字段名、修改字段类型、添加字段、删除字段等。对表结构的修改可以通过执行SQL语句“altertable”来实现。（1）修改表名：altertable旧表名rename新表名;（2）修改字段名：altertable表名change旧字段名 新字段名 新数据类型;（3）修改字段类型：altertable表名modify字段名 新数据类型;（4）添加字段：altertable表名add新字段名 数据类型FIRST|AFTER已经存在的字段名;（5）删除字段：altertable表名drop字段名;知识储备任务二：创建数据库 贰4.删除数据表删除数据表是指删除数据库中已存在的表，同时，如果该表中已经有记录，那么该表中的记录也会一并被删除。其在数据库中删除一个表的语法格式为“droptable表名;”。任务描述任务三：操作数据 弎小张创建完数据库和数据库表后，想插入测试数据，并且想通过查询语句查询测试数据是否插入成功，除此之外想编写SQL语句实现手机销售管理系统的日常业务，比如插入某条记录、更新某条记录，本任务是使用SQL语句实现数据信息的变动，具体步骤如下：（1）使用insertinto语句插入测试数据。（2）使用select进行基本数据的查询。（3）使用where进行条件查询。（4）使用select实现多表查询。第一步：插入测试数据任务三：操作数据 贰使用SQL语句向数据库中插入测试数据：（1）对库存表stock的执行插入命令，执行SQL语句如下：insertintostockvalues(m00001,华为,P20,亮黑色,64GB,3288,2312),(m00002,华为,P20,亮黑色,128GB,3488,1798),(m00003,华为,P20,极光色,64GB,3388,2499),(m00004,华为,P20,极光色,128GB,3488,1133),(m00005,华为,P30,亮黑色,64GB,3988,580),(m00006,华为,P30,亮黑色,128GB,4388,400),(m00007,华为,P30,极光色,64GB,3988,340),(m00008,华为,P30,极光色,128GB,4288,2010),(m00009,小米,小米8,黑色,64GB,3499,1920),(m00010,小米,小米8,白色,128GB,3699,2311);第一步：插入测试数据任务三：操作数据 贰（2）对用户表user的执行插入命令，执行SQL语句如下：insert into user values(u0001,n01,123456,男,13896501267,天津市河东区),(u0002,n02,123456,男,13689772233,山东省济南市),(u0003,n03,123456,女,18590190717,四川省成都市);（3）对订单表ordertb的执行插入命令，执行SQL语句如下：insertintoordertbvalues()(E00001,u0001,m00001,200,2019-06-22,1,2019-06-30),(E00002,u0002,m00002,150,2019-06-22,1,2019-06-30),(E00003,u0002,m00003,50,2019-07-05,1,2019-07-15),(E00004,u0003,m00004,48,2019-08-10,1,2019-08-20),(E00005,u0003,m00005,135,2019-09-26,1,2019-09-29);第二步：基本数据查询任务三：操作数据 弎各数据表数据添加成功后，使用select命令查看添加结果。select*fromstocklimit10;select*fromuser;select*fromordertb;第三步：条件查询任务三：操作数据 弎分别查询库存中价格最高手机库存信息，其SQL语句如下所示：select*fromstockwhereprice=(selectmax(price)fromstock);第四步：多表连接查询任务三：操作数据 弎查询华为P20手机的销售情况，包括订单编号、手机品牌、型号、颜色、内存大小、订购数量，要求列名显示为中文，其SQL语句如下所示。selecto.orderID订单号,s.brand品牌,s.model型号,s.color颜色,s.memSize内存大小,o.orderNum订单数量 fromstockassinnerjoinordertbasoons.mobID=o.mobIDwheres.model=P20;第五步：添加一条记录任务三：操作数据 弎 在手机销售管理系统中，实现添加一款新的手机产品至数据库中，参数信息如下。手机品牌：小米，手机型号：小米8，颜色：灰色，内存大小：128GB，价格：3799，库存数量：500台。其SQL语句如下：insertintostockvalues(m00011,小米,小米8,灰色,128GB,3799,500);执行上述SQL命令后，使用select命令查看添加结果如下：第六步：删除某条记录任务三：操作数据 弎将手机编号为“m00011”的手机信息删除，其SQL语句如下。deletefromstockwheremobID=m00011;执行上述SQL命令后，使用select命令查看删除结果如下：第七步：修改某条记录任务三：操作数据 弎将华为P30，颜色“极光色”，128GB内存的手机库存增加150台，其SQL语句如下所示。updatestocksetstockNum=stockNum+150wheremodel=P30andcolor=极光色andmemSize=64GB;执行上述SQL命令后，使用select命令查看添加结果如下：知识储备任务三：操作数据 贰1.数据查询其语法格式如下（带的是可选项）：selectall|distinct要查询的内容from表名列表where条件groupby字段列表 having分组条件orderby字段列表 asc|desclimitoffset,n;知识储备任务三：操作数据 贰2.聚合函数聚合函数用于对查询结果集中的指定字段进行统计，并输出统计值。常用的聚合函数有count、sum、avg、max、min等。（1）count 函数：统计表中满足条件的行数或总行数count(all|distinct表达式|*);（2）max 和min 函数：分别用于统计表中满足条件的所有值项的最大值和最小值 max/min(all|distinct表达式);（3）sum 和avg 函数：分别用于统计表中满足条件的所有值项的总和与平均值 sum/avg(all|distinct表达式);知识储备任务三：操作数据 贰3.数据变更添加数据的操作可以使用insert语句来完成，使用insert语句可以向已有数据库表插入一行或者多行数据；修改表数据的操作可以使用update语句来完成；删除表数据的操作可以使用delete语句来完成。（1）插入单条记录：insertinto(字段名列表)values（值列表）;（2）单表数据修改：updateset字段名1=值1,字段名2=值2,where条件;（3）使用delete 语句删除表记录：deletefromwhere条件;任务描述任务四：创建索引 肆本任务是使用SQL语句创建索引，实现SQL语句创建索引的思路如下：（1）创建普通索引，索引名为idx_uid。任务实施任务四：创建索引 肆在ordertb表中的会员编号列上创建普通索引，索引名为idx_uid，其SQL语句示例代码如下。alter table ordertbaddindexidx_uID(uID);执行上述SQL命令后，可使用“showcreatetable表名称G”语句来查看创建表时候用的SQL 语句，如下图所示：知识储备任务四：创建索引 肆1.索引概述如果把数据表看成一本书，则表的索引就如同书的目录一样，可以大大的提高查询速度，改善数据库的性能。其具体表现如下：可以加快数据的检索速度。可以加快表与表之间的连接。在使用ORDERBY和GROUPBY子句进行数据检索时，可以显著减少查询中分组和排序的时间。唯一性索引可以保证数据记录的唯一性。索引可以分为下列四种:普通索引（INDEX）:最基本的索引类型，允许在定义索引的字段中插入重复值或空值唯一索引（UNIQUE）:索引字段的值必须唯一，但允许有空值全文索引（FULLTEXT）:在定义索引的字段上支持值的全文查找多列索引:指在表中多个字段上创建的索引知识储备任务四：创建索引 肆2.创建索引（1）创建表的同时创建索引用createtable命令创建表的时候就创建索引，此方式简单、方便。其语法格式如下：createtable表名(字段名 数据类型约束条件，字段名 数据类型约束条件，uniquefulltextindex|key别名(字段名长度asc|desc);（2）创建表后进行添加使用的命令是：altertable表名称 addindex索引名称(字段名称);比如刚刚所介绍的“在ordertb表中的会员编号列上创建普通索引”。知识储备任务四：创建索引 肆3.删除索引在MySQL中，如果某些索引降低了数据库的性能，或者根本没有必要继续使用该索引，可以将索引删除。其语法格式如下：dropindex索引名on表名;知识储备任务五：数据库数据采集 伍任务情境经理：小张，知道业务系统日志数据怎么采集了吗?小张：经理，知道了，我使用 ELK采集了 SpringBoot项目的日志数据。经理：不错，那你知道数据库的怎么采集吗？小张：这个还不太清楚，没研究呢？经理：那你研究下，采集数据库数据文件。小张：好的，没问题。小张和经理谈完话后，开始调研数据库数据采集，经查阅资料发现，也是用Logstash进行数据采集和分析，于是决定采集一下本地数据库文件的内容，知识储备任务五：数据库数据采集 伍步骤一：准备操作 MySQL。步骤二：配置 logstash-jdbc-input。步骤三：启动 logstash。步骤四：表中新增数据。步骤五：检查确认数据是否采集结果。知识储备任务五：数据库数据采集 伍步骤一：准备操作 MySQL。数据准备进入mysql：mysql-uroot-p1xzs20$使用test数据库：usetest创建表：CREATETABLEweb_crawler_tbl(idbigintNOTNULLAUTO_INCREMENT,namevarchar(100),update_datedatetime,PRIMARYKEY(id)ENGINE=InnoDBDEFAULTCHARSET=utf8;添加数据：insertintoweb_crawler_tbl(id,name,update_date)values(1,test,2020-03-1200:00:00);insertintoweb_crawler_tbl(id,name,update_date)values(2,1,2020-03-1207:00:00);在logstash的bin目录中新建lib目录，放入mysql-connector-java-5.1.25.jar包。知识储备任务五：数据库数据采集 伍步骤二：配置 logstash-jdbc-input。在logstash的config目录下，新建logstash.conf，并配置，代码如下：inputstdinjdbc#mysqljdbcconnectionstringtoourbackupdatabsejdbc_connection_string=jdbc:mysql:/localhost:3306/test#theuserwewishtoexcuteourstatementasjdbc_user=rootjdbc_password=1xzs20$#thepathtoourdownloadedjdbcdriverjdbc_driver_library=./lib/mysql-connector-java-5.1.25.jar#thenameofthedriverclassformysqljdbc_driver_class=com.mysql.jdbc.Driver#执行myqsl的语句 statement=select*fromweb_crawler_tblwhereid:sql_last_value#是否使用列值作为依据，进行上次运行位置的记录#如果设置为true，则使用tracking_column定义的列，作为:sql_last_value.#如果设置为false，则:sql_last_value反映的是上次SQL的运行时间。use_column_value=truetracking_column=id#是否记录本次采集数据的位置 record_last_run=true#sql脚本执行的频率，同步频率(分 时 天 月 年)，默认每分钟同步一次，如下*/2是两分钟一次 schedule=*/2*outputfilepath=/root/test-%+YYYY-MM-dd.txtstdoutcodec=json_lines知识储备任务五：数据库数据采集 伍知识储备任务五：数据库数据采集 伍步骤三：启动 logstash。进入logstash的bin目录，执行如下命令启动logstash。logstash-f./config/logstash.conf启动常见问题及解决办法：如何启动logstash失败，则可以通过以下命令检测和定位配置文件问题：logstash-f./config/logstash.conf-t知识储备任务五：数据库数据采集 伍步骤四：表中新增数据。执行以下命令添加数据：insertintoweb_crawler_tbl(id,name,update_date)values(3,test,2020-03-1200:00:00)待数据采集成功后，进入下一步。此时打开/root目录，会发现多了采集数据的文件。知识储备任务五：数据库数据采集 伍步骤五：检查确认数据是否采集结果。标准输出stdout：查看控制台的数据采集日志；文件输出结果：待数据采集成功后，此时打开/root目录，会发现多了采集数据的文件，可以查看增量数据采集的结果。