数据转换是将数据从一种表现形式转换为另一种表现形式的过程。不同的数据挖掘方法对于数据格式都有相应的要求，而同一份原始数据集并不能适用于所有的挖掘方法，因此我们需要对原始数据进行转换。但数据转换一定要把握力度，一味的追求数据转换后的结果可能会严重扭曲原始数据本身的内涵 。本节将介绍如何用MySQL进行数据转换，主要包括数据标准化和数据离散化。 4.3.1 数据标准化   由于原始数据中常常会具有不同的量纲和变异程度，因此在进行数据分析之前，我们需要先将数据进行标准化。数据标准化一方面能够消除量纲的影响，解决数据可比性的问题，另一方面能够弱化数据本身的变异程度，使得数据分布状态更加均匀。关于数据标准化常用处理方法包括：min-max标准化、z-score标准化、按小数定标标准化。 本节使用样例表order_inf1，表结构及创建语句如下： 字段名称 数据类型 备注 ordercode varchar(20) 订单编号 username varchar(20) 用户名 consignee varchar(20) 收货人 pay_date varchar(20) 支付日期 province varchar(20) 收货省份 city varchar(20) 收货城市 total_amount float(20) 订单金额 carriage float(20) 运费 remark varchar(20) 备注 MySQL输入语句如下： 这时我们查看order_inf1表中所有的字段。MySQL输入及输出如下： 接下来将数据插入order_inf1表中，再执行查询语句。MySQL输入及输出如下： 接下来，让我们对上述数据进行标准化。 （1）min-max标准化   min-max标准化是借助最小值和最大值对原始数据进行线性变化，其计算公式为： min-max标准化 =（原数据-最小值）/（最大值-最小值） MySQL中使用select语句处理min-max标准化的问题，MySQL输入及输出如下： （2）z-score标准化   z-score标准化是基于原始数据的均值和标准差进行数据标准化，其计算公式为： z-score标准化 =（原数据-均值）/ 标准差 MySQL中使用select语句处理z-score标准化的问题。 【例】用z-score标准化方法处理total_amount订单金额，MySQL输入及输出如下： （3）小数定标标准化   小数定标标准化是通过移动数据小数点的位置来进行标准化。MySQL中使用select语句处理小数定标标准化的问题。 【例】将total_amount订单金额向左移动3个小数点，MySQL输入及输出如下： 4.3.2 数据离散化   数据离散化是指将连续型数据进行分段，使数据分布在不同的离散化区间中。由于实际数据库中存在较多的连续型数据，而很多数据挖掘算法只能处理离散型的数据，因此数据离散化是应用这些算法的前提。   有效的数据离散化能够减少算法时间和空间的开销、提高系统对样本的聚类能力、增强系统抗数据噪音的能力并提高算法学习的精确度。MySQL中使用条件判断函数处理数据离散化的问题。 【例】以均值为标准将total_amount订单金额离散化，大于等于均值为大额订单，记为“1”，小于均值为小额订单，记为“0”，MySQL输入及输出如下：

  现实世界中的数据通常是不完整的、有噪声的和不一致的。因此每拿到一批原始数据，我们首先需要对数据进行清洗。数据清洗是对数据进行重新审查和校验的过程，目的在于删除重复信息、纠正错误信息、保证数据的一致性。本节将介绍用MySQL进行数据清洗的方法，其中包括：处理缺失值、剔除重复值、识别异常值、发现数据不一致。 4.2.1处理缺失值   缺失值是指原始数据中缺少部分信息而造成某个变量或某些属性值不完整。缺失值产生的原因主要分为机器原因和人为原因。前者是由于机器导致的数据收集或记录失败造成的数据缺失，后者是由于人主观失误或有意隐瞒造成的数据缺失。对于缺失值的处理，常用方法主要包括：估算填充、样本删除、字段删除。   本节使用样例表order_inf，表结构及创建语句如下： 字段名称 数据类型 备注 ordercode varchar(20) 订单编号 username varchar(20) 用户名 consignee varchar(20) 收货人 pay_date varchar(20) 支付日期 province varchar(20) 收货省份 city varchar(20) 收货城市 total_amount float(20) 订单金额 carriage float(20) 运费 remark varchar(20) 备注 我们查看order_inf表中所有的字段，MySQL输入及输出如下： 接下来将数据插入order_inf表中，再执行查询语句，MySQL输入及输出如下： （1）估算填充 估算填充是指用估算值填充变量缺失数据的部分。估算值一般会选择变量的均值、中位数或者众数，这种填充方法能够使得数据分布较为均匀，但同时也会产生已有数据信息失真的问题。MySQL中使用select语句和update语句处理估算填充的问题。 【例】用众数填充carriage运费字段缺失部分，MySQL输入及输出如下： （2）样本删除 样本删除是指剔除缺失值较多的样本，这种方法比较适合整体样本量较大，且含有多个缺失值的样本比重较小的情况。MySQL中使用delete from语句处理整例删除的问题。 【例】删除缺失值过多的样本数据，MySQL输入及输出如下： （3）字段删除 如果某个字段的缺失值特别多，且该字段对于所研究的问题不是特别重要，则可以考虑将该字段删除。MySQL中使用alter table语句处理字段删除的问题。 【例】删除缺失值过多的字段，MySQL输入及输出如下： 4.2.2 剔除重复值   重复值是指同一数据被多次记录在数据库中。重复值的出现会导致数据冗余并影响统计结果的精确性，因此在进行数据分析前，需要根据业务需求考虑是否剔除重复值。MySQL中使用distinct关键字剔除重复值。 【例】剔除order_inf表中具有重复数据的记录，MySQL输入及输出如下： 4.2.3 识别异常值   异常值是指一组数据中与平均值偏差超过两倍标准差以上的数据。异常值的出现会使得该组数据分布不均匀，从而导致估算偏误。但异常值并不一定是错误值，它可能反映了某个特定节点的突发性状况。因此我们需要识别异常值，然后根据具体业务信息判断异常值的取舍。 MySQL中使用聚合函数识别异常值，输入及输出如下： 【例】查询total_amount中的异常信息。 4.2.4 发现数据不一致   数据不一致是指各类数据之间具有不相容性，具体体现为单组数据之间数据编码格式不同，或者是多组数据之间数据记录相互矛盾。MySQL使用update语句修改数据不一致的情况，输入与输出如下： 【例】更新pay_date字段使得日期格式一致。 【例】更新province字段使得收货城市与省份数据一致。

  数据集成是把不同来源、格式的数据整合到一起，从而为企业提供系统化的数据检索、数据挖掘和建模功能。企业通常会采用联邦式、基于中间件模型和数据仓库等技术手段来构造数据集成系统。为了把不同来源、格式的数据存入到MySQL数据库中，本节将介绍以下数据集成方法：使用load data语句导入数据以及使用Workbench菜单式导入数据。 4.1.1 使用load data语句导入数据   MySQL中提供了load data语句来将不同格式的数据表集成到数据库中，常用存储结构化数据的格式包括Excel、CSV和TXT。   MySQL中load data导入数据基本语法形式为： load data [local] infile ‘filename’ into table table_name; 注意：往MySQL中导入数据前，我们需要先使用create table语句构建一张用来存储该文件数据的表空间。 本节将继续使用样例表emp，表结构及创建语句如下： 字段名称 数据类型 备注 empno int(10) 员工编号 ename varchar(20) 员工姓名 job varchar(20) 员工职位 leader int(10) 直系领导 hiredate datetime(6) 雇佣时间 sal float(20) 员工薪资 comm float(20) 员工津贴 deptno int(10) 部门编号 接下来将表中数据分别以CSV、TXT格式进行存储，表数据及存储结果如下： ① CSV文件格式 【例】将emp.csv文件数据导入emp表中，MySQL输入及输出如下： 小贴士：实际操作中若遇到Excel格式的数据，我们通常会将其另存为成CSV格式文件再导入到MySQL数据库中。 ② TXT文件格式 【例】将emp.txt文件数据导入emp表中，MySQL输入及输出如下： 4.1.2 使用Workbench菜单式导入数据   Workbench可视化工具中也提供了菜单化的MySQL数据导入方法，具体的操作步骤如下： ① 在test01数据库中创建好emp表空间，左侧Schemas中选择test01数据库，找到emp表空间。 ② 右键点击emp表，选择Table Data Import Wizard，弹出Table Data Import界面。 ③ 在Table Data Import界面中，点击Browse选择emp.csv文件并打开。 ④ 点击Next后，在Use existing table中选择test01.emp。 ⑤ 点击Next后可以看到emp表的数据状态。 ⑥ 继续点击Next直到数据导入成功。 ⑦ 点击Finsh完成数据导入。 ⑧ 最后用select语句查看导入的数据表emp。MySQL输入及输出如下：

  MySQL提供了众多功能强大、方便易用的高级查询功能，例如函数的运算、正则表达式等。灵活地使用高级查询能够极大提高数据库查询效率，并完成复杂的业务需求。本节将介绍MySQL中函数的运算和正则表达式查询方法，包括：数学函数、字符串函数、日期和时间函数、条件判断函数、正则表达式查询。 3.4.1 函数的运算   函数也称为函数关系式，假定输入参数值A，函数关系式会返回一个特定的关系值B。MySQL中提供了大量的函数，借助函数对数据进行处理可以满足各种复杂的业务需求。本节将使用样例表emp介绍数学函数、字符串函数、日期和时间函数、条件判断函数的主要作用和使用方法。 （1）数学函数   数学函数主要用来处理数值型数据，日常业务中经常会遇到求均值、求总数的问题，这时就要借助数学函数。 【例】查询emp表中每个部门的部门人数、平均薪资、最大薪资、最小薪资、薪资合计，输入及输出如下： 函数的作用： 函数符号 函数名称 函数作用 count() 计数函数 计算字段值出现的个数 avg() 平均值函数 计算字段所有值的平均数 max() 最大值函数 查找字段中的最大值 min() 最小值函数 查找字段中的最小值 sum() 求和函数 计算字段所有值的总和 【例】查询emp表中每个部门的人均津贴，输入及输出如下： 字段中包含null空值的处理方法：由于comm员工津贴中包含null空值，而使用平均值函数会直接跳过空值记性计算，所以将导致计算结果失真。因此需要利用coalesce()函数对空值进行转化，从而使得平均值函数能够读取该值并进行计算。 （2）字符串函数   字符串函数主要用来处理字符型数据，日常业务中若遇到截取字符、替换字符、合并字符串、数值转字符等问题都需要借助字符串函数，MySQL中常用字符串函数实例如下： ① 截取字符串 【例】查询emp表中每个员工姓名的首字母，输入及输出如下： ② 替换字符串 【例】将emp表中为manager的职位名称替换为leader，输入及输出如下： ③ 合并字符串 【例】将emp表中员工姓名和员工职位合并到一个字段中，输入及输出如下： 小贴士： concat()不仅能够合并字符串，还能够合并字符串与数值，合并后的新字段为字符串类型。 【例】将emp表中员工编号、员工姓名、员工职位合并到一个字段中。 ④ 数值转字符 【例】将emp表中员工编号数值型字段转为字符型字段，输入及输出如下： （3）日期和时间函数   日期和时间函数主要用来处理日期和时间值，日常业务中若遇到查询当前日期、查询年月日、加减日期等问题都需要借助日期和时间函数，MySQL中常用日期和时间函数实例如下： 【例】获取系统当前日期和时间，输入及输出如下： 【例】查询emp表中hiredate雇佣时间的年、月和日，输入及输出如下： 【例】将emp表中hiredate雇佣时间增加1天，输入及输出如下： 小贴士：date_add()函数也能用于减少日期。 【例】将emp表中hiredate雇佣时间减少1天，输入及输出如下： （4）条件判断函数   条件判断函数也称为控制流程函数，程序根据不同条件执行不同的流程，MySQL中条件判断函数包括if、ifnull和case。 ① if函数   if函数的基本形式为if(express,d1,d2)，若表达式express成立则返回值为d1，否则返回值为d2。 【例】新建一个字段名为薪资状态，薪资大于20000的员工设定为“较高”，否则为“正常”，输入及输出如下： ② ifnull函数   ifnull函数的基本形式为ifnull(d1,d2)，若d1不为空，则返回值为d1，否则返回值为d2。 【例】新建一个字段名为津贴状态，津贴为空的员工显示津贴状态为0，输入及输出如下： ③ case函数 case函数的基本形式为 case when express1 then d1 [when express2 then d2] [else dn] end 若表达式express1成立则返回值为d1，若表达式express2成立则返回值为d2，依次类推，最后返回值为dn。 【例】新建一个字段名为薪资状态，薪资大于20000的员工设定为“较高”，薪资介于8000到20000之间为“正常”，否则为“较低”，输入及输出如下： 3.4.2 正则表达式 问题A：正则表达式的含义及作用是什么？ 回答A：正则表达式（Regular Expression）又称规则表达式，是计算机科学的一个概念。正则表达式通常被用来检索或替换那些符合某个规则的文本内容。例如从文本文件中提取特有信息或者替换敏感词等。 问题B：正则表达式常用匹配字符有哪些？ 回答B：MySQL中正则表达式常用匹配字符如下： 选项 说明 实例 ^ 匹配每行开始的字符串 “^a”匹配以a开头的字符串 $ 匹配每行结束的字符串 “b$”匹配以b结尾的字符串 . 匹配任意单个字符 “a.b”匹配任何a和b之间有一个字符的字符串 * 匹配任意多个字符 “a.b”匹配任何a和b之间有多个字符的字符串 + 匹配前一个字符一次或多次 “a+”匹配a一次或多次 [] 匹配括号中任意一个字符 “[ab]”匹配a或者b 问题C：如何使用正则表达式查询？ 回答C：MySQL中使用regexp作为正则表达式查询语句的固定语法格式，基本SQL语法格式为： select column_name from table_name where column_name regexp 查询条件; 【例】查询emp表中员工姓名开头为a的数据，输入及输出如下： 【例】查询emp表中员工姓名结尾为a的数据，输入及输出如下： 【例】查询emp表中员工姓名包含an的数据，输入及输出如下： 【例】查询emp表中员工姓名不包含an的数据，输入及输出如下： 【例】查询emp表中员工姓名包含a或n的数据，输入及输出如下： 练习 1、查询emp表中每个职位的职位人数、平均薪资、平均津贴、最大薪资、最小薪资、薪资合计。 2、新建一个字段名为雇佣状态，将emp表中1990年以后雇佣的员工设定为“新员工”，1990年以前的员工为“老员工”。 3、查询emp表中员工职位包含sale的数据。 练习答案 1、第一题的语句如下： 2、第二题的语句如下： 3、第三题的语句如下：

  子查询指的是一条查询语句嵌套在另一条查询语句内部，即在一条select语句的from或where子句中包含另一条select语句。在查询语句中，外层select语句称为主查询，from或where子句中包含的select语句称为子查询，也称嵌套查询。理论上子查询可以出现在select语句的任何位置，但实际开发中，子查询经常出现在from和where子句中。本节将介绍SQL子查询方法，其中包括：from型子查询和where型子查询。 3.3.1 from型子查询   from型子查询也称为表子查询，子查询语句位置在from后，用来返回多行多列的数据记录，返回的数据记录可以当作一张临时表。MySQL中from型子查询基本语法形式为： select column_name from (select column_name from table_name) as table_name_new; 小贴士:使用from型子查询必须给内层select语句所生成的临时表提供一个新表名！   本节将使用样例表player和penalty，数据表结构、创建及插入数据语句如下： 表 player球员信息表 字段名称 数据类型 约束 备注 id int(10) 主键约束 球员编号 name varchar(20) / 球员姓名 birthday datetime(6) / 出生日期 sex varchar(20) / 球员性别 town varchar(20) / 居住城市 player表数据如下： id name birthday sex town 1 Dempsey 1982-9-1 M Stratford 2 Aldrich 1981-6-25 M Stratford 7 Alex 1983-5-11 M Stratford 8 Nic 1986-11-1 F Inglewood 27 Devin 1982-12-28 F Eltham 28 Dick 1981-6-22 F Midhurst 39 Fitch 1986-10-29 M Stratford 44 Mason 1983-1-9 M Inglewood 57 Noah 1978-8-17 M Stratford 83 Hope 1986-11-11 M Stratford 95 Cumberland 1983-5-14 M Douglas 100 Parmenter 1983-2-28 M Stratford 104 Esther 1975-5-10 F Eltham 112 Conrad 1983-10-1 F Plymouth 表 penalty球员信息表 字段名称 数据类型 约束 备注 p_id int(10) 外键约束 球员编号 pay_no int(10) 主键约束 罚款编号 pay_date datetime(6) / 罚款时间 amount float(10) / 罚款金额 penalty表数据如下： p_id pay_no pay_date amount 44 2 1991-5-5 75000 27 3 1983-9-10 10000 104 4 1984-12-8 50000 44 5 1991-12-8 25000 8 6 1991-11-12 25000 44 7 1992-12-30 30000 27 8 1984-11-12 75000 player球员信息表的代码输入如下： 结果输出如下： penalty罚款信息表的代码输入如下： 结果输出如下： 【例】查询球员编号小于10的男性球员姓名，上述需求可拆解成以下几个部分： · 编号小于10的球员 · 性别为男性的球员 可以看出这是一个单表查询的问题，实际上我们可以用多条件查询解决，MySQL输入及输出如下： 但我们也可以将上述多条件查询改写成子查询的方法，MySQL输入及输出如下： 3.3.2 where型子查询   where型子查询包括行子查询和列子查询两种，其子查询语句位置在where后，行子查询用来返回单行的数据记录，而列子查询用来返回多行的数据记录。 （1）行子查询   行子查询主要包括单行单列和单行多列的情况，MySQL基本语法形式为： select column_name from table_name where column_name 逻辑操作符 ( select column_name from table_name where 查询条件); ① 单行单列 【例】查询与100号球员性别相同的球员编号和姓名。 ② 单行多列 【例】查询与100号球员性别相同且居住城市相同的球员编号和姓名。 小贴士：当用where进行多条件判断时，也可以用in关键字来简化代码量，即上述语句可写为： （2）列子查询   行子查询主要面对多行单列的情况，通常会包含in、any、all、exists等关键字。 ① 带有in关键字的列子查询 当主查询的条件存在于子查询的结果中时，可以通过关键字in进行判断。相反如果想实现主查询的条件不存在于子查询的结果中时，可以通过关键字not in进行判断，MySQL基本语法形式为： select column_name from table_name where column_name in (select column_name from table_name where 查询条件); 【例】查询所有有过罚款记录的球员编号和球员姓名。 ② 带有any关键字的列子查询 当主查询的条件满足子查询返回结果中任意一条数据记录时，可以通过关键字any进行判断，MySQL基本语法形式为： select column_name from table_name where column_name 逻辑操作符 any(select column_name from table_name where 查询条件); 【例】查询至少比另一个球员年轻且居住在同一城市的球员编号和球员姓名。 上述需求可拆解成以下2个部分： ·居住城市相同的球员； ·至少比另一个球员年轻的球员。 MySQL输入及输出如下： ③ 带有all关键字的列子查询 当主查询的条件满足子查询返回结果中所有数据记录，可以通过关键字all进行判断，MySQL基本语法形式为： select column_name from table_name where column_name 逻辑操作符 all( select column_name from table_name where 查询条件); 【例】查询最年轻的球员编号和球员姓名，MySQL输入及输出如下： ④ 带有exists关键字的列子查询 关键字exists是一个布尔类型，当有返回结果时为True，不能返回结果时为False，MySQL基本语法形式为： select column_name from table_name where exists ( select column_name from table_name where 查询条件); 【例】查询至少支付了一次罚款的球员编号和球员姓名，MySQL输入及输出如下： 小贴士： exists表示返回结果为True，如果想要查询返回结果为False时，可以使用not exists关键字 【例】查询没有支付过罚款的球员编号和球员姓名。 练习 1.用子查询方法，从emp表中查询姓名首字母为c，薪资高于6000，津贴不为空的员工； 2.从player表中查询与27号球员性别相同且居住城市相同的球员编号和姓名； 3.从player表中查询最年长的球员编号和球员姓名。 练习答案 1.第一题的语句如下： 2.第二题的语句如下： 方法1 方法2 3.第三题的语句如下：

  表连接是关系型数据库最突出的特性，通过连接运算符能够实现多表连接查询，查询方式主要包括纵向连接和横向连接。当实际进行MySQL数据库查询时，通常会遇到以下两种情况：① 纵向合并多个表中具有相同字段的数据；② 横向合并多个表中描述同一类事物不同的字段。本章将介绍MySQL多表联合查询方法，其中包括：纵向内连接、纵向全连接、横向内连接、横向左/右连接、横向全连接。 3.2.1 多表纵向连接   多表纵向连接的作用是将数据表中相同的数据合并到一起，因此纵向连接也可以称为行连接。纵向连接主要有两种连接方式：内连接和全连接，前者将数据合并到一起后剔除整行重复的数据，后者直接将所有数据合并到一起不再去重。本节将使用样例表emp01、emp02进行讲解，数据表创建及插入数据语句如下： （1）纵向内连接 MySQL中使用union实现纵向内连接，基本语法形式为： select 字段1,字段2,```,字段n from table_name1 union select 字段1,字段2,```,字段n from table_name2； 【例】查询emp01和emp02中所有不重复的员工信息，输入及输出如下： 小贴士： MySQL进行纵向连接时也能加入其他子句，下面举两个例子： 【例】查询emp01和emp02中所有comm津贴小于8000的员工编号和员工姓名，输入及输出结果如下： 【例】查询emp表中各部门薪资排名前三的员工姓名、员工薪资、部门编号，输入及输出如下： 注意：MySQL进行多表纵向连接时，若只包含where子句可有选择性的添加括号，若包含group by或order by子句则必须添加括号。 （2）纵向全连接 MySQL中使用union all实现纵向全连接，基本语法形式为： select 字段1,字段2,```,字段n from table_name1 union all select 字段1,字段2,```,字段n from table_name2； 【例】查询emp01和emp02中所有的数据，输入及输出如下： 小贴士：纵向内连接和全连接最大的区别就在于合并后的数据去不去重的问题，句法本身除了union和union all关键字不同，基本可以复用。 3.2.2 多表横向连接 问题A：多表横向连接的作用及常见类型有哪些？ 回答A：多表横向连接的作用是将分在不同数据表中描述同一类事物的多个字段合并在一起，因此横向连接也可以称为列连接。横向连接主要有四种连接方式：内连接、左连接、右连接、全连接，连接方式如下图所示： 小贴士：日常工作中有80%的概率会碰到需要横向连接的场景，因此我们日常交流中提及的内连接、全连接默认指的是横向内连接、横向全连接。 问题B：如何编写多表横向连接SQL语句？ 回答B：编写多表横向连接SQL语句，将使用样例表manager和employee，数据表结构、创建及插入数据语句如下： 表 manager领导信息表 字段名称 数据类型 约束 备注 id int(10) 主键约束 领导编号 name varchar(20) / 领导姓名 表 employee员工信息表 字段名称 数据类型 约束 备注 m_id int(10) 外键约束 领导编号 empno int(10) 主键约束 员工编号 ename varchar(20) / 员工姓名 （1）横向内连接 MySQL中一共有3种方法可以实现横向内连接，基本语法形式为： # 方法1 select 字段1,字段2,```,字段n from table_name1 join table_name2 on 匹配条件； # 方法2 select 字段1,字段2,```,字段n from table_name1 inner join table_name2 on 匹配条件； # 方法3 select 字段1,字段2,```,字段n from table_name1,table_name2 where 匹配条件； 注意事项：只有横向内连接-方法3是用where作为匹配条件的语法，其它所有横向连接全以on作为匹配条件的语法。 【例】对表manager和表employee进行横向内连接，输入及输出如下： 小贴士：当遇到较长的表名称时，通常会给数据表起一个临时的别名，MySQL输入及输出如下： 另外两个方法的输入及输出如下： （2）横向左连接 MySQL中使用left join实现横向左连接，基本语法形式为： select 字段1,字段2,```,字段n from table_name1 left join table_name2 on 匹配条件； 【例】对表manager和表employee进行横向左连接，输入及输出如下： （3）横向右连接 MySQL中使用right join实现横向右连接，基本语法形式为： select 字段1,字段2,```,字段n from table_name1 right join table_name2 on 匹配条件； 【例】对表manager和表employee进行横向右连接，输入及输出如下： 小贴士：在日常编写SQL查询语句时，大部分程序员会采用将两张表前后位置颠倒的方法，达到用左连接代替右连接的目标，因此我们很少会看到SQL语句里有右连接的情况。 （4）横向全连接 MySQL中不支持使用full join实现横向全连接，但可以通过将左连接与右连接纵向拼接的方式达到横向全连接的效果。 【例】对表manager和表employee进行横向全连接，输入及输出如下： 问题C：如何理解多表横向连接查询？ 回答C：想要理解多表横向连接查询的原理，首先得了解笛卡尔积。 小贴士：笛卡尔积又称为笛卡尔乘积，是两个集合相乘的结果。假设存在集合A{a1,a2,a3}和集合B{b1,b2}，那么集合A与B的笛卡尔积为{(a1,b1),(a1,b2),(a2,b1),(a2,b2),(a3,b1),(a3,b2)}。   数据库在进行多表横向连接查询时，就是先计算作为匹配条件字段的笛卡尔积，然后筛选出符合规则的元素组合。下面我们举一个横向内连接的例子。 【例】对表manager和表employee进行横向内连接，输入如下： select * from manager join employee on manager.id=employee.m_id; 基于笛卡尔积的计算方式为： ① 计算作为匹配条件字段id和m_id的笛卡尔积 集合id{1002,2001,3001} 集合m_id{2001,3001} 集合A与B的笛卡尔积为 {(1002,2001),(1002,3001),(2001,2001),(2001,3001),(3001,2001),(3001,3001)} ② 内连接要求筛选出字段id等于m_id的元素组合 {(2001,2001),(3001,3001)} ③ 显示上述结果所查询的字段 再举个横向左连接的实例： 【例】对表manager和表employee进行横向左连接，输入如下： select * from manager left join employee on manager.id=employee.m_id; 基于笛卡尔积的计算方式为： ① 计算作为匹配条件字段id和m_id的笛卡尔积 集合id{1002,2001,3001} 集合m_id{2001,3001} 集合A与B的笛卡尔积为 {(1002,2001),(1002,3001),(2001,2001),(2001,3001),(3001,2001),(3001,3001)} ② 左连接要求筛选出字段id等于m_id的元素组合，并且保留包含字段id的所有元素 {(2001,2001),(3001,3001)}和{(1002,null)} ③ 显示上述结果所查询的字段 练习 1.查询emp表中各部门薪资小于10000的员工姓名、员工薪资、部门编号 2.查询employee表中所有员工的领导编号和领导姓名 3.基于笛卡尔积描述【例】对表manager和表employee进行横向右连接的数据库计算方式 练习答案 1.第一题的语句如下： 2.第二题的语句如下： 方法1 方法2 方法3 3.第三题的描述如下： 基于笛卡尔积的计算方式为： （1）计算作为匹配条件字段id和m_id的笛卡尔积 集合id{1002,2001,3001} 集合m_id{2001,3001} 集合A与B的笛卡尔积为 {(1002,2001),(1002,3001),(2001,2001),(2001,3001),(3001,3001),(3001,3001)} （2）右连接要求筛选出字段id等于m_id的元素组合，并且保留包含字段m_id的所有元素 {(2001,2001),(3001,3001)} （3）显示上述结果所查询的字段

  数据库管理系统最重要的功能就是数据查询，数据查询不仅仅是简单的从数据库中提取数据，还需要根据业务要求对查询的数据进行清洗及整理。而MySQL提供了一系列功能强大、灵活的SQL语句来实现查询操作，本节作为数据库查询的起始章节，将介绍如何使用select语句进行单表查询，并且在单表查询中如何实现筛选、分组、排序等操作。 3.1.1 select查询框架需牢记   使用select语句从数据库中查询数据是数据分析师日常最基本的工作内容，那么select语句的基础框架是怎样的？该如何利用select语句进行数据表查询？编译select语句需要注意哪些细节？接下来，将逐一为大家答疑解惑。 （1）select语句长啥样 select column_name from table_name 注意：实际操作中我们经常会看到类似select * from table_name; 其中*是通配符，表示查询数据表中所有字段。 本节将使用样例表emp，表结构及创建语句如下： 字段名称 数据类型 备注 empno int(10) 员工编号 ename varchar(20) 员工姓名 job varchar(20) 员工职位 leader int(10) 直系领导 hiredate datetime(6) 雇佣时间 sal float(20) 员工薪资 comm float(20) 员工津贴 deptno int(10) 部门编号 这时我们查看emp表中所有的字段。 MySQL输入及输出如下： 接下来将数据插入emp表中，再执行查询语句，emp表数据如下： empno ename job leader hiredate sal comm deptno 3005 chloe salesman 3001 1987-02-20 5600 6000 3 3002 danae salesman 3001 1987-02-24 6000 10000 3 3005 chloe salesman 3001 1987-02-20 5600 6000 3 3002 danae salesman 3001 1987-02-24 6000 10000 3 1001 blanche persident 　 1980-11-17 50000 　 1 2001 an manager 1001 1982-09-02 35000 5000 2 3003 snow salesman 3001 1991-11-28 5500 5400 3 3004 celina manager 1001 1982-07-01 30000 5000 3 1002 sicily manager 1001 1981-06-09 25000 5000 1 2002 claudia analyst 2001 1985-11-17 30000 　 2 2003 elina analyst 2001 1987-04-19 24000 　 2 3001 dolores salesman 3001 1985-07-08 5500 2000 3 1011 claudia clerk 2001 1987-05-23 9000 　 2 3006 laraine clerk 3001 1992-12-03 9500 　 3 2004 gina analyst 2001 1991-12-03 18000 　 2 1003 nana clerk 1002 1992-01-23 8000 　 1 MySQL输入及输出如下： （2）select语句及它的四个子句 select column_name from table_name where group by having order by 注意事项： ① 四个子句使用顺序不能颠倒； ② 四个子句可少用但不可多用或重复使用； ③ having子句与group by子句连用，没有group by不能用having，但没有having仍可使用group by。   接下来我们尝试利用这四个子句完成一个比较复杂的业务需求： 查询单个员工薪资高于20000且总人数大于1的职位，按照总人数由小到大进行排序。当我们拿到比较复杂的业务需求时，首先要学会对业务需求进行拆解，上述需求可以拆解为以下几个部分： ① 筛选出薪资高于20000的员工——条件查询where ② 按职位进行分组并计算总人数——分组查询group by ③ 筛选出总人数大于1的职位——分组后筛选having ④ 按照总人数由小到大进行排序——排序查询order by 根据我们的分析，MySQL输入及输出如下： 3.1.1 select查询框架需牢记   条件查询作为select语句中第一条子句，在数据库日常查询过程中使用率大概达到了80%以上，例如提取某个地区的用户表或者是某一天的订单表等。MySQL中使用where对数据进行条件查询，基本语法形式为： select column_name from table_name where 查询条件; （1）查询指定行 【例】查询emp表中所有salesman销售人员的数据，MySQL输入及输出如下： 逻辑操作符 备注 = 相等 ！=，<> 不相等 > 大于 >= 大于等于 < 小于 <= 小于等于 between …and … 介于两者之间 （2）查询空值 【例】查询emp表中comm津贴为空的员工，MySQL输入及输出如下： 注意：MySQL中null空值不属于任何数据类型，有且只能与is连用，其中is null表示为空，is not null表示非空。 （3）多条件查询 【例】查询emp表中部门编号为1和2的员工，MySQL输入及输出如下： 注意：MySQL进行多条件判断时，如果字段相同，可以用in来减少代码冗余，上述句式可改写成： 【例】查询emp表中部门编号为1和部门编号为2中薪资大于20000的员工 上述需求可以拆解为以下几个部分： ① 查询部门编号为1的员工 ② 查询部门编号为2且薪资大于20000的员工 根据我们的分析，MySQL输入及输出如下： 注意：MySQL做多条件查询时会用到and/or作为连接符，and表示且，or表示或。当判断条件多于两个时，我们需要注意判断条件的先后顺序，必要时可添加括号。 （4）模糊查询 【例】查询emp表中员工姓名第二个字母为a的数据，MySQL输入及输出如下： 注意：模糊查询通常用于检索字符串类型数据。 3.2.3 数据分组巧用group by   数据查询时经常会遇到分组的情况，分组查询是对数据按照单个或多个字段进行分组，通常会与count()、sum()等集合函数一起使用，MySQL中使用group by对数据进行分组，基本语法格式为： select column_name from table_name group by 字段; （1）单字段分组 【例】查询emp表中各部门薪资总和，MySQL输入及输出如下： （2）多字段分组 【例】查询emp表中各部门、各职位薪资总和，MySQL输入及输出如下： （3）条件判断与数据分组 【例】查询emp表中各部门员工薪资大于10000的人数 上述需求可以拆解为以下几个部分： ① 员工薪资大于10000 ② 各部门员工人数 根据我们的分析，MySQL输入及输出如下： 3.2.4 分组后筛选别忘having   having子句用来筛选满足条件的分组，因此只有当数据分组后才会使用having子句，MySQL基本语法格式为： select column_name from table_name group by 字段 having 条件表达式; 【例】查询emp表中各部门、各职位薪资总和大于20000的数据，MySQL输入及输出如下： 3.2.5 整齐排序还看order by   通常情况下为了更清晰的展示数据集，我们需要对查询的数据结果进行排序，MySQL中使用order by进行数据排序，基本语法格式为： select column_name from table_name order by 字段; （1）单字段排序 【例】查询emp表中所有数据，并按sal薪资由小到大升序排列，MySQL输入及输出如下： 注意：MySQL也支持数据由大到小进行降序排列，只需在字段后加desc固定语法即可。 【例】查询emp表中所有数据，并按sal薪资由大到小降序排列，MySQL输入及输出如下： （2）多字段排序 【例】查询emp表中所有数据，并按deptno部门编号升序，sal薪资降序排列，MySQL输入及输出如下： 多字段排序时需要注意： ① 多个需要排序的字段间用逗号分隔； ② 字段前后位置要与业务需求一致； ③ 哪个字段需要降序排列则desc紧随其后； （3）分组排序 【例】查询emp表中各部门薪资总和，按薪资总和降序排列，MySQL输入及输出如下： 练习 根据样例表emp，我们来尝试以下练习： 1、查询姓名首字母为c，薪资高于6000，津贴不为空的员工。 2、查询人数超过1个的职位，并按照人数升序排列。 3、尝试用两种方法查询职位为analyst分析师的员工人数。 练习答案 1、第一题的语句如下： 2、第二题的语句如下： 3、第三题的语句如下： 方法1 方法2

  索引类似于一本书的目录，它记录了数据在数据库中存放的位置，创建索引可以帮助用户快速找出某个字段中特定值的行，由此提高MySQL查询数据的速度。视图是由一个或多个数据表组合而成的一张虚拟表，用户可以从这张虚拟表中查询到需要的数据，但其本身不存储数据。本节将详细介绍索引和视图，主要内容包括：创建索引、修改索引、查看索引、删除索引、创建视图、查看视图、修改视图、删除视图。 2.5.1 索引 问题A：索引的含义及作用是什么？ 回答A：索引是对数据表中一个或多个字段的值进行排序的一种数据结构，使用索引可以提高数据库中特定数据的查询效率。例如一张有3万条记录的学生信息表，现在想要查询id编号为10000的学员信息，如果没有对id列建立索引，则计算机需要逐行遍历整个数据表，直到找出id等于10000这一行为止，相反如果已经在id列上创建索引，则MySQL不需要逐行遍历全表，而是直接在索引字段里找到10000，从而得知这一行的位置。 问题B：索引的存储类型及常见分类有哪些？ 回答B：MySQL中索引的存储类型包括B-Tree和HASH两种，具体和表的存储引擎相关，例如MyISAM、InnoDB存储引擎只支持B-Tree索引，而Memory存储引擎可以同时支持B-Tree和HASH索引。 索引类型 索引名称 相关代码块 备注 普通索引 Index index(column_name) 基本索引类型，没有任何限制 唯一索引 Unique unique index(column_name) 索引列的值必须唯一，但允许有空值 主键索引 Primary primary key(column_name) 索引列的值必须唯一，且不允许有空值 组合索引 MultiIdx Index(column_name1,column_name2```) 多个字段组合创建一个索引，遵循“最左前缀”原则 全文索引 Fulltext fulltext index(column_name) 仅支持MyISAM存储引擎，用于检索文本信息 约束与索引 （1）约束与索引到底有什么联系和区别？ 约束与索引是两种不同的概念，前者是为了维护数据库的完整性，后者是为了提高数据库查询效率，但在创建约束的同时相当于在指定列上创建了一个索引。 （2）唯一约束与唯一索引又有什么关系？ 创建唯一约束时，系统会自动创建唯一索引，都要求唯一字段不重复但可以为空，程序实现起来并没有任何区别。 （3）主键约束与主键索引有什么关系？ 同理，创建主键约束时，系统也会自动创建主键索引，都要求主键字段不重复且不为空，程序实现起来同样没有任何区别。 注意：索引能够有效缩短数据检索、分组和排序的时间，加速多表连接的查询速度，但过多的索引会占用大量磁盘空间，并降低数据表增删改的效率，因此在设计数据库时，需要合理的添加索引，避免索引的滥用。 问题C：索引创建、查看、删除的方法有哪些？ 回答C：MySQL提供多样化的索引创建、查看、删除方法，具体方法如下： （1）创建索引   MySQL支持3种索引创建的方式： ① 创建表create table时指定索引字段，基本SQL语法格式如下： create table table_name( column_name column_type [key_type], index_type(column_name) ); 【例】创建一张名为stu_inf4的学员信息表，在id列上创建普通索引，输入语句如下： 【例】创建一张名为stu_inf5的学员信息表，在id列上创建唯一索引，输入语句如下： 【例】创建一张名为stu_inf6的学员信息表，在id列上创建主键索引，输入语句如下： 创建索引注意事项 （1）同一张表中，用户自定义创建的索引名称不能一样； （2）同一张表中，有且只能创建一个主键索引； （3）同一张表中，可以创建多个唯一索引； （4）同一个字段，若已设置索引则需要删除后再创建索引，而不能直接覆盖； ② 修改表alter table时创建索引字段，基本SQL语法格式如下： alter table table_name add index_type index_name(column_name); 【例】修改名为stu_inf4的学员信息表，在stu_name列上创建普通索引，输入语句如下： 【例】修改名为stu_inf5的学员信息表，在stu_name列上创建唯一索引，输入语句如下： 【例】创建一张名为stu_inf7的学员信息表，修改表时在id列上创建主键索引，输入语句如下： ③ 使用create index语句添加索引，基本SQL语法格式如下： create index_type index_name on table_name (column_name); 【例】在stu_inf4的学员信息表中stu_sex列上创建普通索引，输入语句如下： 【例】在stu_inf5的学员信息表中stu_sex列上创建唯一索引，输入语句如下： （2）查看索引   上述例子中都采用查看表结构的方法来查看索引，其实查看索引有标准的SQL语法： show index from table_name; 【例】查看stu_inf4的学员信息表中已创建的索引，输入语句如下： （3）删除索引   MySQL支持2种索引的删除方式： ① 修改表alter table时删除索引字段，基本SQL语法格式如下： alter table table_name drop index index_name; 【例】删除stu_inf4的学员信息表中名为idx的索引，输入语句如下： ② 使用drop index语句删除索引，基本SQL语法格式如下： drop index index_name on table_name; 【例】删除stu_inf4的学员信息表中名为idx1的索引，输入语句如下： 2.5.2 “增删改查”视图   视图是由数据库中一个或多个表中组合而成的虚拟表，视图一经定义便存储在数据库，但与其对应的数据并没有再复制一份，而是存储在原始数据表中。如果把数据库比作一间房子，那么视图就是这间房子上的一扇窗户，我们可以从窗户外看到房内的设施，但具体的设施还在摆在房间里。因此想要修改视图中看到的数据，必须修改原始数据表，换句话说原始表中数据发生变化会自动地反映到视图中。 （1）创建视图   MySQL既支持单个数据表上创建视图，也支持在多个表上创建视图，基本SQL语法格式如下： create view view_name as select column_name from table_name; ① 单表上创建视图 【例】在order_sale订单表上创建一张名为view_t的视图，输入语句如下： 注意：默认情况下创建的视图与原始表的字段名称是一样的，但也可以在创建视图的同时修改视图字段名称，输入语句如下： ② 多表上创建视图 【例】在order订单表和product产品表上创建一张名为view_sum的视图，输入语句如下： （2）查看视图   MySQL支持多样化的视图查看方式，本书介绍2种常用的视图查看方法： ① 使用describe语句查看视图基本信息，基本SQL语法格式如下： describe view_name; 【例】查看名为view_t视图的基本信息，输入语句如下： ② 使用show create view语句查看视图详细信息，基本SQL语法格式如下： show create view view_name; 【例】查看名为view_t视图的详细信息，输入语句如下： （3）修改视图   MySQL支持2种视图修改方法： ① 使用create or replace view语句修改视图，基本SQL语法格式如下： create or replace view view_name as select column_name from table_name; 注意：修改视图本质上是创建新视图然后覆盖原始视图的过程。 【例】修改视图view_t，令该视图字段与原始表字段保持一致，输入语句如下： ② 使用alter语句修改视图，基本SQL语法格式如下： alter view view_name as select column_name from table_name; 【例】修改视图view_t1，令该视图字段与原始表字段保持一致，输入语句如下： （4）删除视图 删除视图与删除数据表SQL语法类似： drop view view_name; 【例】删除名为view_t的视图，输入语句如下： 练习 练习使用数据表store库存信息表，表结构如下： 表 store表结构 字段名称 数据类型 备注 p_name varchar(20) 商品名称 quantity int(10) 库存数量 trade_price float(20) 批发价格 retail_price float(20) 零售价格 （1）利用3种不同的方式，在store库存信息表中p_name字段上创建普通索引。 （2）基于store库存信息表创建视图，视图名为view_store。 练习答案 （1）利用3种不同的方式，在store库存信息表中p_name字段上创建普通索引，输入语句如下： （2）基于store库存信息表创建视图，视图名为view_store，输入语句如下：

  在MySQL中创建表空间时，我们还可以对表字段进行约束设置，不同的约束条件所要求的数据存储也有所不同。当用户创建好表空间后，我们需要将数据值插入到表空间使其成为完整的数据表。本节将详细介绍数据表约束和数据表管理，其中包括：约束设置、数据表关联、添加表数据、修改表数据、删除表数据。 2.4.1 数据表约束 问题A：什么是SQL约束？ 回答A：约束是在数据表中定义的用于维护数据库完整性的条件规则，通过对数据表中的字段定义约束条件不仅可以防止将错误数据插入数据表，还可以保持表之间数据的一致性。 问题B：SQL中的约束有哪些？ 回答B：SQL中的约束一般分为列级约束和表级约束，     列级约束共6种类型，包括：主键、外键、唯一、检查、默认和非空;     表级约束共4种类型，包括：主键、外键、唯一、检查。 约束名称 约束类型 是否支持列级 是否支持表级 primary key 主键约束 √ √ foreign key 外键约束 √ √ unique 唯一约束 √ √ check 检查约束 √ √ not null 非空约束 √ × default 默认约束 √ × 问题C：SQL中列级约束和表级约束的区别？ 回答C： （1）列级约束只作用于一个字段，表级约束能够同时作用于多个字段； （2）列级约束设置时用空格分隔、不必指定要约束的字段名， 表级约束设置时用逗号分隔、必须指定要约束的字段名； 问题D：SQL中设置列级约束和表级约束的语法格式？ 回答D： 数据表约束设置一般在创建表空间时，因此可以将MySQL中创建数据表的基本SQL语法格式修改为： create table table_name( column_name1 column_type key_type, #列级约束 column_name2 column_type key_type, #列级约束 ··· constraint key_name key_type(column_name1,column_name2) #表级约束 ); 表级约束也可以不定义约束名称，即去掉constarint key_name，直接写成key_type(column_name)的语法格式。 【例】在test01数据库中，创建一张名为stu_inf1的学员信息表，要求主键约束用表级约束方法设置，其它约束用列级约束方法设置，表结构如图所示： 字段名称 数据类型 约束 备注 Id int(10) 主键约束 学员编号 stu_name varchar(20) 唯一约束 学员姓名 stu_sex varchar(20) 非空约束 学员性别 stu_score float(10) / 学员成绩 输入语句如下： 问题E：SQL中设置不同类型约束的作用及语法格式是什么？ 回答E：SQL中约束类型共6大类：主键、外键、唯一、检查、非空、默认，设置主键与外键约束的主要作用是保证表与表之间数据的完整性和一致性，设置其它约束的主要作用是防止将错误数据插入数据表。 下面介绍主键约束和外键约束，具体作用及语法格式如下： （1）主键约束   主键（Primary key）是最常见的约束类型，通常结合外键约束来定义不同数据表之间的关联关系。主键要求被约束的字段数据唯一且不包含空值，一般分为单字段主键和多字段联合主键两种类型。 ① 单字段主键约束 单字段主键由一个字段组成，支持列级或表级约束设置方法，MySQL中设置单字段主键的基本SQL语法格式为： # 列级约束方法设置主键 create table table_name( column_name column_type primary key ); # 表级约束方法设置主键 create table table_name( column_name column_type, constraint key_name primary key(column_name) ); 【例】创建一张名为stu_inf2的学员信息表，其主键为id列，输入语句如下： # 列级约束方法设置主键 create table stu_inf2( id int(10) primary key, stu_name varchar(20), stu_sex varchar(20), stu_score float(10) ); # 表级约束方法设置主键 create table stu_inf2( id int(10), stu_name varchar(20), stu_sex varchar(20), stu_score float(10), constraint pk_1 primary key(id) ); ② 多字段主键约束 多字段主键由多个字段联合组成，仅支持表级约束设置方法，MySQL中设置多字段主键的基本SQL语法格式为： create table table_name( column_name1 column_type, column_name2 cloumn_type, primary key(column_name1,column_name2) ); MySQL中设置多字段主键约束时，不需要使用contraint语法定义约束名称。 主键约束注意事项： （1）每张数据表只能设置一个主键约束，但数据库中很少有不设置主键的数据表； （2）多字段主键约束是数据表中多个字段联合作为一个主键约束，并不是数据表中多个字段分别作 【例】创建一张名为emp_inf的员工表，表结构如下图所示，表中没有主键id，为了确定唯一一个员工，将name、dept_id联合起来做主键 字段名称 数据类型 备注 Name varchar(20) 员工姓名 dept_id int(10) 部门编号 Exp float(10) 工作年限 Salary float(10) 员工薪资 输入语句如下： （2）外键约束   外键（Foreign key）主要用来建立表与表之间的关联关系，一个表中的外键通常会指向另一个表中的主键。那么对于两个具有关联关系的数据表而言，主键所在的那张表被称为主表/父表，外键所在的那张表被称为从表/子表。外键约束建立好后，不允许删除父表中具有关联关系的行，并且若不先删除子表，父表也不允许被删除，由此保证数据引用的完整性和一致性。MySQL中设置外键约束的基本SQL语法格式为： create table table_name( column_name column_type, constraint key_name foreign key(column_name) references master_table_name(master_column_name) ); 【例】创建一张名为stu_inf3的学员信息表，其主键为id列，输入语句如下： create table stu_inf3( id int(10) primary key, stu_name varchar(20), stu_sex varchar(20), stu_score float(10) ); 【例】再创建一张名为teacher的教师信息表，表结构如图所示： 字段名称 数据类型 备注 id int(10) 教师编号 s_id int(10) 学员编号 c_name varchar(20) 课程名称 t_name varchar(20) 教师姓名 将名为s_id的字段作为外键关联到stu_inf3的主键id，输入语句如下： E-R图   E-R图也称实体关联图（Entity Relationship Diagram），它提供了展示实体、属性和关系的方法，是用来描述现实世界的概念模型。E-R图由四个部分绘制而成： （1）矩形框：表示实体，在框中记录实体名； （2）椭圆形框：表示属性，在框中记录属性名； （3）菱形框：表示关系，在框中记录关系名； （4）连线：将各框之间连接起来，并标注连接类型（1:1、1:n、n:m）； 在MySQL中，E-R图可以用来描述表与表之间的关联关系，以上述stu_inf3和teacher表为例，E-R图为： 2.4.2 数据表管理   数据存储是数据库存在的核心价值，MySQL中提供了功能丰富的数据操作语句，用户可以使用INSERT语句往表空间中插入数据，也可以使用UPDATE语句更新数据或者使用DELETE语句删除数据。 （1）插入数据   在查询数据表之前，数据表中必须要有数据，MySQL中插入数据的基本SQL语法格式为： insert into table_name(column_name1,column_name2```) values(value1,value2```); 【例】往表名为stu_inf1的学员信息表中添加数据，表数据如图所示： id stu_name stu_sex stu_score 1 张三 男 83.5 2 李四 男 80 3 王五 女 90.5 4 熊六 男 77.5 方法一：逐行插入数据 insert into stu_inf1(id,stu_name,stu_sex,stu_score) values(1,"张三","男",83.5); insert into stu_inf1(id,stu_name,stu_sex,stu_score) values(2,"李四","男",80); insert into stu_inf1(id,stu_name,stu_sex,stu_score) values(3,"王五","女",90.5); insert into stu_inf1(id,stu_name,stu_sex,stu_score) values(4,"熊六","男",77.5); 方法二：同时插入多行数据 insert into stu_inf1(id,stu_name,stu_sex,stu_score) values(1,"张三","男",83.5), (2,"李四","男",80), (3,"王五","女",90.5), (4,"熊六","男",77.5); 插入数据时要保证每列插入的数据值与字段的数据类型相一致，如果数据类型不一致MySQL会报错且无法插入数据。 小贴士：为数据表中所有字段都插入数据时，可以不需要指定字段名，基本SQL语法格式为： insert into table_name values(value1,value2```); 【例】往表名为stu_inf1的学员信息表中添加数据，输入语句如下： # 逐行插入数据 insert into stu_inf1 values(1,"张三","男",83.5); insert into stu_inf1 values(2,"李四","男",80); insert into stu_inf1 values(3,"王五","女",90.5); insert into stu_inf1 values(4,"熊六","男",77.5); # 同时插入多行数据 insert into stu_inf1 values(1,"张三","男",83.5), (2,"李四","男",80), (3,"王五","女",90.5), (4,"熊六","男",77.5); （2）更新数据   当表中有数据之后，可以对数据进行更新操作，MySQL中更新数据的基本SQL语法格式为： update table_name set column_name=new_value where column_name=value; 【例】将stu_inf1学员信息表中，学员姓名为张三的成绩修改为85分，输入语句如下： 论数据类型的重要性： 在MySQL中定位数据时，一定要注意数据所在字段的数据类型，若遇到字符型数据必须在数据前后加上单引号或双引号。不同数据类型的使用方法，将在3.4高级查询中详细展开。 （3）删除数据   当数据不在使用时，用户也可以对数据进行删除操作，MySQL中删除数据的基本SQL语法格式为： delete from table_name where column_name=value; 【例】删除stu_inf1学员信息表中，学员姓名为张三的信息，输入语句如下： 注意：删除数据时，删除的是用户指定数据值所在数据表的某一行，而不是数据值所在的单元格。 练习 （1）在test_stu数据库中，分别创建student学生信息表、score学生成绩表和teacher教师信息表，表结构和约束设置如下： 表 student学员信息表 字段名称 数据类型 约束 备注 id int(10) 主键约束 学员编号 s_name varchar(20) / 学员姓名 s_age int(10) / 学员年龄 s_sex varchar(20) / 学员性别 表 score学员信息表 字段名称 数据类型 约束 备注 s_id int(10) 外键约束 学员编号 c_name varchar(20) / 课程名称 c_score float(10) / 课程成绩 表 teacher学员信息表 字段名称 数据类型 约束 备注 id int(10) 主键约束 教师编号 s_id int(10) 外键约束 学员编号 c_name varchar(20) / 课程名称 t_name varchar(20) / 教师姓名 （2）绘制student学生信息表、score学生成绩表和teacher教师信息表的E-R图： （3）分别往student学生信息表、score学生成绩表和teacher教师信息表中插入数据，表数据如下： 表 student学员信息表 id s_name s_age s_sex 101 张三 17 男 102 李四 18 男 103 王五 17 女 104 熊六 18 男 表 score学员信息表 s_id c_name c_score 101 语文 95 101 数学 75 101 英语 73.5 102 语文 89 102 数学 85 102 英语 86 103 语文 65 103 数学 93.5 103 英语 90.5 104 语文 89 104 数学 92.5 104 英语 94 表 teacher学员信息表 id s_id c_name t_name 1 101 语文 张玲 2 102 语文 张玲 3 103 语文 张玲 4 104 语文 张玲 5 101 数学 杨猛 6 102 数学 杨猛 7 103 数学 杨猛 8 104 数学 杨猛 9 101 英语 王爽 10 102 英语 王爽 11 103 英语 王爽 12 104 英语 王爽 练习答案 （1）分别创建student学生信息表、score学生成绩表和teacher教师信息表，输入语句如下 ： （2）绘制student学生信息表、score学生成绩表和teacher教师信息表的E-R图，如下图所示： （3）往student学生信息表、score学生成绩表和teacher教师信息表中插入数据，输入代码如下：

  MySQL数据库管理系统安装好之后，首先需要用户创建数据库和表空间，数据库是存储数据表的容器，数据表是数据库的基本操作对象。本章将详细介绍数据库的基本操作，主要内容包括：创建数据库、查看数据库、选择数据库、删除数据库、创建表空间、查看表空间、修改表空间、删除表空间。 2.3.1 “增删选查”数据库   每次使用MySQL数据库前，我们都需要将MySQL连接到对应的服务器DBMS上。远程连接服务器时一般会要求我们输入用户名和密码，但如果使用的是本地服务器，直接在MySQL Workbench中单击Local instance MySQL进行本地连接即可。 （1）创建数据库   最初连接到MySQL时，除存放系统配置文件的数据库外，没有任何其他可供用户操作的数据库。因此在执行数据库操作前，需要我们自行创建并选择一个数据库，即在系统磁盘上划分一块区域用来存储和管理数据。MySQL中创建数据库的基本SQL语法格式为： create database database_name; 【例】创建库名为test01的数据库，输入语句如下： create database test01; create database是固定语法格式，表示创建数据库，database_name是用户自定义创建的数据库名称，该名称不能与已存在的数据库重名。 （2）查看数据库   数据库创建好之后，我们可以通过固定命令来查看所有的数据库名称，MySQL中查看数据库的基本SQL语法格式为： show databases; MySQL输出结果如下: （3）选择数据库   每当用户登录MySQL时，都需要先选择一个数据库，才能对该数据库中的数据表进行相关操作，MySQL中选择数据库的基本SQL语法格式为： use database_name; 【例】选择库名为test01的数据库，输入语句如下： use test01; 为什么要选择数据库？ 把数据库想象为Windows操作系统下的文件夹，数据表想象为文件夹中的文件。当我们想要在Windows下编辑某个文件的时候，必须先打开对应的文件夹，同理，当我们想要操作数据表的时候，必须先选择对应的数据库才行。 （4）删除数据库   删除数据库是将已存在的数据库从系统磁盘上清除，清除之后，数据库包括数据库中所有的数据都会被一同清除，删除数据库命令和创建数据库命令基本类似，MySQL中删除数据库的基本SQL语法格式为： drop database database_name; 【例】删除库名为test01的数据库，输入语句如下： drop database test01; 2.3.2 “增删改查”表空间   数据表是数据库中最基本也是最重要的操作对象，是数据存储的基本单位。由于MySQL属于结构化数据库，因此其所存储的数据表也多为结构化数据表，即按照行和列的格式进行存储。 （1）创建表空间 MySQL中创建数据表的基本SQL语法格式为： create table table_name( column_name1 column_type ); 【例】在test01数据库中，创建一张名为stu_inf的学员信息表，表结构如下： 字段名称 数据类型 备注 id int(10) 学员编号 stu_name varchar(20) 学员姓名 stu_sex varchar(20) 学员性别 stu_score float(10) 学员成绩 括号+数字表示字段长度，可以想象成在Excel表格中设置列宽，但由于部分数据类型有默认字段长度，若无特殊需求也可以省略，例如int(10)可以直接写int。 输入语句如下： 想要对数据表进行操作，首先得选择一个数据库，因此我们在创建表空间前，应该使用use database_name语句指定数据库，否则会出现No database selected的错误。 MySQL创建数据表注意事项： 1.同一数据库中表名称不能重复； 2.创建的表名称，不区分大小写，不能使用SQL语言中drop、alter等关键字； 3.数据表中每一个字段名必须配上相应的数据类型； MySQL数据类型： 问题A：SQL中的数据类型是什么？ 回答A：数据类型是数据的一种属性，它表示数据信息的类型。每张数据表都包含一个到多个字段，每个字段由字段名和数据类型组合而成，不同数据类型的存储及展现方式都有所不同。 问题B：SQL中的数据类型有哪些？ 回答B：数据类型共3大类，包括数值型、字符型和日期型 数值型：int整数、float浮点数 字符型：char固定长度字符串、varchar可变长度字符串 日期型：datetime日期和时间组合，格式“YYYY-MM-DD hh:mm:ss” 更多详细数据类型可参考：MySQL数据类型详解 （2）查看表空间   数据表创建好之后，我们可以使用SQL语句列出所有数据表的名称，也可以根据数据表名称，查看该表中所包含的字段及数据类型等详细信息，MySQL中查看表空间的基本SQL语法格式如下： ① 查看所有数据表名称 【例】查看test01数据库所有表名称，输入语句如下： ② 查看单个数据表结构 【例】查看表名为stu_inf的学员信息表，输入语句如下： （3）修改表空间   修改表指的是修改数据库中已经存在的数据表结构，MySQL支持多样化数据表修改操作，包括修改表名称、添加表中字段、修改表中字段、删除表中字段。MySQL中修改数据表的SQL语句全部以alter table作为开头，表示用户当前需要进行修改表的操作。 ① 修改表名称 alter table table_name_old rename table_name_new; 【例】将stu_inf学员信息表名称修改为student_inf，输入语句如下： ② 添加表中字段 alter table table_name add column column_name column_type; 【例】在student_inf学员信息表中添加字段名为stu_class、数据类型为varchar(20)的字段，输入语句如下： ③ 修改表中字段 alter table table_name change column column_name_old column_name_new column_type_new; 【例】修改student_inf学员信息表中stu_class字段，修改后的字段名为class、数据类型为char(20)，输入语句如下： ④ 删除表中字段 alter table table_name drop column column_name; 【例】删除student_inf学员信息表中class字段，输入语句如下： （4）删除表空间 MySQL中删除数据表的基本SQL语法格式为： drop table table_name; 【例】删除test01数据库中，表名为student_inf的学员信息表，输入语句如下： drop table student_inf; 删除数据表时，若该数据表作为父表被其他子表关联，则需要先删除子表后才能删除父表，父表与子表的概念将在下个章节中详细展开。 练习 （1）创建一个库名为test_stu的数据库； （2）查看所有已存在的数据库； （3）进入数据库test_stu； （4）在test_stu数据库中，创建emp_inf员工信息表，表结构如下： 表 emp_inf员工信息表 字段名称 数据类型 备注 id int(10) 员工编号 e_name varchar(20) 员工姓名 e_age int(10) 员工年龄 e_sex varchar(20) 员工性别

  MySQL具有多种版本且支持多平台操作，不同版本在不同平台下的安装与配置过程也不尽相同。在Windows平台下可以使用二进制安装包或免安装包进行安装，二进制的安装包提供了图形化的安装向导过程，而免安装包直接解压缩即可使用，Linux平台下则可以直接使用命令行进行安装。本节将以Windows平台下的二进制安装包为例，详细讲解MySQL下载和安装过程。 2.2.1 下载MySQL安装包   MySQL是完全网络化的跨平台关系型数据库系统，分为社区版（Community）、企业版（Enterprise）和集群版（Cluster）等多个版本，MySQL各版本之间的区别如下图所示： MySQL版本 版本名 费用情况 技术支持 适用人群 Mysql Community Editon 社区版 开源免费 不提供 大多数普通用户 Mysql Enterprise Edition 企业版 付费，试用30天 官方提供 企业级用户 Mysql Cluster CGE 高级集群版 付费 官方提供 大型企业级用户 本书使用当前最新的MySQL 5.7社区版作为教学版本，具体下载步骤如下： （1）访问MySQL的官方网站（www.mysql.com），如图2.1所示 图2.1 MySQL首页 （2）打开MySQL官方网站后，点击Downloads导航栏进入MySQL产品下载页面，该页面包括以下三个版本如图2.2所示：Mysql Enterprise Edition企业版；Mysql Cluster CGE集群版；Mysql Community Editon社区版 图2.2 MySQL下载页面 （3）单击MySQL Community Edition下载链接跳转到社区版下载页面，如图2.3所示 图2.3 MySQL下载页面 （4）点击Mysql Community Server下的Download超链接跳转到最终的下载页面，如图2.4所示 图2.4 MySQL下载页面 （5）在Select Platform下拉菜单中选择Microsoft Windows，然后点击Download按钮，跳转到文件下载页面，单击Windows（x86,32-bit）右边Download按钮，如图2.5所示 图2.5 MySQL下载页面 （6）单击Windows（x86,32-bit）右边Download按钮，如图2.5，跳转到最终下载页面，点击No thanks, just start my download直接下载 图2.5 MySQL下载页面 2.2.2 安装MySQL数据库   上文介绍了下载MySQL数据库的详细步骤，那么如何安装我们的第一个数据库呢？具体步骤如下： （1）双击安装程序Setup.exe文件，开始安装MySQL数据库，如图2.7所示 图2.7 MySQL初始界面 （2）点击Next按钮进入到Choose a Setup Type窗口，如图2.8所示，选择Custom自定义安装类型 图2.8 MySQL安装界面 （3）点击Next按钮进入到Select Products and Features窗口，如图2.9所示，在MySQL Server下选择安装对应的Windows操作系统（x64对应64位，x86对应32位），随后在Applications下选择相同操作系统安装MySQL Workbench 图2.9 MySQL安装界面 MySQL Workbench简介 MySQL Workbench是MySQL官方发布的一款可视化的数据库设计软件，它为数据库管理员、程序开发者、系统规划师提供了SQL开发（SQL Development）、模型建立（Data Modeling）和数据库管理（Server Administration）等各种强大的功能。 （4）点击Next按钮进入到Path Conflits窗口，如图2.10所示，选择安装路径 图2.10 MySQL安装界面 （5）连续点击Next按钮3次进入到Accounts and Roles窗口，如图2.11所示，设定MySQL本地登录密码 图2.11 MySQL安装界面 （6）继续多次点击Next按钮进入到最终Apply Server Configuration窗口，当Configuration Steps下所有任务都执行成功，如图2.12所示，最后点击Finish完成安装。 图2.12 MySQL安装界面

  数据库是由一批存放在数据表里的原始数据堆积而成，这些数据表之间相互关联，记录了客观事物日常的运动规律。如今我们的生活中无时无刻不在使用着数据库，例如在因特网上进行搜索，或者是在电商网站上进行购物，甚至是在自动取款机上使用银行卡，背后都是利用数据库对各类数据进行存储、修改和查询。本节将介绍数据库的一些基本概念，首先介绍数据库的发展历程，其次介绍数据库、数据库管理系统及SQL之间的联系，最后介绍我们后续要用到的MySQL数据库管理系统。 2.1.1 数据库历史发展阶段   所谓数据库，可以想象成一个容器中存放着各种数据集。若把这个容器比作书架，书架上的每一本书就相当于一个数据集，若把这个容器比作文件夹，文件夹里的每一个文件就相当于一个数据集。据此，我们能够对数据库内的数据进行存储、分类、检索、提取和维护。发展到现在，数据库已经完成了由人工存储到机器存储的蜕变，共经历了三个阶段：人工管理阶段、文件系统阶段、数据库管理系统阶段。 （1）人工管理阶段 时间：20世纪50年代以前 事件：计算机主要用于科学计算。从硬件层面看，当时没有直接用于存储的设备，仅能借助卡片、磁带等外存达到数据存储的目的；从软件层面看，当时也没有完整的操作系统以及专门管理数据的软件；从数据层面看，当时数据量非常小且有没固定的数据结构，所有数据都直接由用户管理。 特点：数据不能长期保存，没有对数据进行管理的软件系统，数据不能共享，数据不具有独立性。 （2）文件系统阶段 时间：50年代后期到60年代中期 事件：计算机不仅用于科学计算，还运用到信息管理方面。从硬件层面看，这时出现了磁盘等数据存储设备；从软件层面看，系统可以按照文件的名称对其进行检索和访问，并可以实现文件内容的增加、修改与删除；从数据层面看，实现了文本内数据的结构化，即单个文本中各数据之间具有一定的关系，但从整体来看每个文件中的数据却是无结构的。 特点：数据可以长期保存，由文件系统管理数据，数据具有一定的共享性和独立性。 （3）数据库管理系统阶段 时间：60年代后期 事件：数据库系统克服了文件系统的缺陷，提供了对数据更高级、更有效的管理，这个阶段程序与数据的联系通过数据库管理系统（DBMS）来实现，因此计算机可以用来存储和处理规模庞大的数据集。 特点：数据实现结构化，共享性高、独立性强。 2.1.2 数据库、数据库管理系统与SQL语言 问题A：数据库与数据库管理系统是一回事么？ 答案A：不是哦，数据库是用于存储数据的地方，它提供了一个数据存储的空间，而数据库管理系统是用于管理数据库的软件，它是用户创建、管理和查询数据库时所使用的软件。如果把数据库想象成一个装有Excel表格的文件夹，那么数据库管理系统就是管理这个文件夹的软件。 问题B：当今主流的数据库管理系统有哪些？ 答案B：关系型数据库包括：MySQL，Oracle，SQL server，PostgreSQL等     非关系型数据库包括：MongoDB，Hbase等 要点B：关系型数据库与非关系型数据库的区别？ 区别1：关系型数据库中存放的是结构化数据集，类似于Excel表格，即行与列组合而成的二维表；非关系型数据库中存放的是非结构化数据集，例如用户得聊天记录、拍摄的图片等。 区别2：关系型数据库有且只能存放结构化数据集，但非关系型数据库不仅能存放非结构化数据集，还能存放结构化数据集。 问题C：那SQL语言又是什么呢？ 答案C：SQL是Structure Query Language（结构化查询语言）的缩写，是目前广泛使用于关系型数据库的标准语言，是执行数据库管理系统的程序语言。 要点C：SQL语言包含以下4个部分： （1）数据定义语言（DDL）：Drop，Create，Alter等语句 （2）数据操作语言（DML）：Insert，Update，Delete等语句 （3）数据查询语言（DQL）：Select语句 （4）数据控制语言（DCL）：Grant，Revoke，Commit等语句 作为数据分析师，我们需要集中精力攻克查询语言（DQL）。下面简单列举几条常用的SQL语言，期望能给读者留下一个直观的印象： （1）数据定义语言（DDL）： 【例】创建一张学生信息表，表名为student，输入语句如下： create table student( stu_id int(10), name char(20), sex char(20), birthday datetime(5) ); （2）数据操作语言（DML）： 【例】往student表中插入一条学生信息数据，输入语句如下： insert into student(stu_id,name,sex,birthday) values(1001,"张三","男","1989-11-11"); （3）数据查询语言（DQL）： select * from student; # MySQL输出结果如下 +--------+------+-----+---------------------+ | stu_id | name | sex | birthday | +--------+------+-----+---------------------+ | 1001 | 张三 | 男 | 1989-11-11 00:00:00 | +--------+------+-----+---------------------+ 问题D：数据库、数据库管理系统与SQL语言之间有什么联系？ 答案D： 2.1.3 MySQL数据库管理系统 问题A：什么是MySQL？ 答案A：MySQL是一个轻量级的关系型数据库管理系统，与Oracle、SQL server等大型数据库管理系统相比，MySQL规模较小、功能有限，但正是因为它规模小，所以速度快、成本低，并且从实际应用角度来看，它所提供的功能完全可以满足于大部分业务功能，诸如此类的特性使得MySQL成为全世界最受欢迎的开源的数据库。 问题B：MySQL的优势在哪？ 答案B：MySQL数据库管理系统的优势主要表现在以下几个方面： 优势 详细描述 效率高 轻量级关系型数据库，运行速度极快 成本低 对于大多数个人而言免费开源 易用性 数据库设置复杂程度低，易于学习 规范性 国际公认的标准SQL语法 兼容性 兼容数种不同的系统平台，例如Windows、Linux、Mac OS等 安全性 允许主机验证，所有密码传输均采用加密形式 接口丰富 提供C、C++、JAVA、Python等语言的API接口 问题C：MySQL相关工具有哪些？ 答案C：MySQL数据库管理系统提供了许多命令行工具，也提供了可视化的管理工具。命令行工具能够用来管理MySQL服务器、登录用户以及数据库备份和恢复等，而可视化工具Workbench使得用户操作数据库更加快捷。 命令行工具实用小程序： # 1.启动MySQL服务 net start mysql # 2.登录MySQL mysql -u root -p # 回车后输入密码，例如1234 # 3.导出数据 mysqldump --opt test > mysql.test Workbench主要功能： 1.数据库设计和模型建立 2.SQL开发（取代MySQL Query Browser） 3.数据库管理（取代MySQL Administrator）

Workbench可视化工具中提供了菜单化的MySQL数据导入方法，具体步骤如下： 1.在test01数据库中创建emp表空间 # 进入test01数据库 use test01; # 创建emp表空间 create table emp( empno int(10), ename varchar(20), job varchar(20), leader int(10), hiredate datetime(6), sal float(20), comm float(20), deptno int(10) ); 2.左侧Schemas中选择test01数据库，找到emp表空间 3.右键点击emp表，选择Table Data Import Wizard，弹出Table Data Import界面 4.在Table Data Import界面中，点击Browse选择emp.csv文件并打开 5.点击next后，在use existing table中选择test01.emp 6.点击next后可以看到emp表的数据状态 7.继续点击next直到数据导入成功 8.点击Finsh完成数据导入 9.最后用select语句查看导入的数据表emp，MySQL输入及输出如下

  接下来讲一个分组添加序号的问题，场景需求是依据每个文件名分组标识序号，标识以后可以根据序号筛选出每个文件组的前几行，具体形式如下： 上述表格转化为： 想要分组添加序号，需要利用MySQL中@变量功能，具体语句如下： select * from (select IF(@file=filename,@rank:=@rank+1,@rank:=1) as topic_rank, @file:=filename as filename, a.topic from transposition a) b; -- 注意：@file变量需要先赋值 transposition数据表创建及导入代码： -- 创建数据表 create table transposition( filename varchar(100), topic varchar(100) ); -- 往表中插入数据 insert into transposition values("0849053.txt","临时调额-操作-卡片"); insert into transposition values("0849053.txt","卡片-状态-正常"); insert into transposition values("0849053.txt","卡片-属性-固定额度"); insert into transposition values("0849053.txt","卡片-包含-分期"); insert into transposition values("0849053.txt","卡片-包含-分期"); insert into transposition values("0907432.txt","还款金额-依赖-账单"); insert into transposition values("0907432.txt","账单-条件-正常还款"); insert into transposition values("0907432.txt","账单-条件-全额还款"); insert into transposition values("0907432.txt","账单-条件-全额还款"); insert into transposition values("0907432.txt","账单-依赖-还款"); insert into transposition values("0907432.txt","核实-操作-信息"); insert into transposition values("0907432.txt","卡片-属性-账单分期"); insert into transposition values("0909268.txt","调整-操作-额度"); insert into transposition values("0909268.txt","释放-操作-额度"); insert into transposition values("0909268.txt","额度-依赖-还款"); insert into transposition values("0909268.txt","核实-操作-身份"); insert into transposition values("0909268.txt","账单-依赖-还款"); insert into transposition values("0909268.txt","账单-依赖-还款");

  近期在做光大POC项目的时候，遇到了分组转置的问题，场景需求是将每个文件名对应的多行合并到一行，具体形式如下： 上述表格转化为： 想要达到分组转置的效果，需要使用group_concat()函数，具体语句如下： select filename, group_concat(topic) as trans_topic from transposition group by filename; -- 注意：必须使用group by分组 transposition数据表创建及导入代码： -- 创建数据表 create table transposition( filename varchar(100), topic varchar(100) ); -- 往表中插入数据 insert into transposition values("0849053.txt","临时调额-操作-卡片"); insert into transposition values("0849053.txt","卡片-状态-正常"); insert into transposition values("0849053.txt","卡片-属性-固定额度"); insert into transposition values("0849053.txt","卡片-包含-分期"); insert into transposition values("0849053.txt","卡片-包含-分期"); insert into transposition values("0907432.txt","还款金额-依赖-账单"); insert into transposition values("0907432.txt","账单-条件-正常还款"); insert into transposition values("0907432.txt","账单-条件-全额还款"); insert into transposition values("0907432.txt","账单-条件-全额还款"); insert into transposition values("0907432.txt","账单-依赖-还款"); insert into transposition values("0907432.txt","核实-操作-信息"); insert into transposition values("0907432.txt","卡片-属性-账单分期"); insert into transposition values("0909268.txt","调整-操作-额度"); insert into transposition values("0909268.txt","释放-操作-额度"); insert into transposition values("0909268.txt","额度-依赖-还款"); insert into transposition values("0909268.txt","核实-操作-身份"); insert into transposition values("0909268.txt","账单-依赖-还款"); insert into transposition values("0909268.txt","账单-依赖-还款");

  最近发现不少同学在实际业务场景中对数据类型的选择有诸多困惑，遂写下该博文，期望能够帮助同学进一步加深对数据类型的理解。MySQL中数据类型主要包括三大类：数值型、字符型和日期型，本章将详细描述不同数据类型的名称、适用场景以及如何选择数据类型。 · 数值型   数值型数据类型主要用来存储数字，MySQL中可以把数值型数据分为两类：整数型和小数点型。整数型包括：tinyint、smallint、mediumint、int、bigint，小数点型包括：float、double、decimal(M,D)，具体请见下表： MySQL中整数类型 类型名称 具体描述 存储需求 tinyint 非常小的整数 1个字节 smallint 较小的整数 2个字节 mediumint 中等大小的整数 3个字节 int 普通大小的整数 4个字节 bigint 较大的整数 8个字节 MySQL中小数点类型 类型名称 具体描述 存储需求 float 单精度浮点数 4个字节 double 双精度浮点数 8个字节 decimal(M,D) 定点数(M数值总位数,D小数点后位数) M+2个字节 要点： 1.如果业务不需要小数部分，使用整数来保存数据，通常情况下我们会使用int整数类型，否则使用浮点数来保存数据，通常情况下我们会使用float浮点数类型； 2.浮点数float、double与定点数decimal，在长度一定的情况下，使用浮点数能表示更大的数据范围，但如果对数据精确度要求较高时，建议使用定点数来存储； · 字符型   字符型数据类型用来存储字符串数据，MySQL支持两种字符型数据存储：文本字符串和二进制字符串，文本字符串包括：char、varchar、text、enum、set，二进制字符串包括：bit、binary、varbinary、tinyblob、blob、mediumblob、longblob，具体请见下表: MySQL中文本字符串类型 类型名称 具体描述 存储需求 char 固定长度的字符串 M个字节,1<=M<=255 varchar 可变长度的字符串 L+1个字节,L<=M且1<=M<=255 tinytext 非常小的字符串 L+1个字节,L<2^8 text 较小的字符串 L+2个字节,L<2^16 mediumtext 中等大小的字符串 L+3个字节,L<2^24 longtext 较大的字符串 L+3个字节,L<2^32 enum 枚举类型   set 字符串对象，可以有零或多个值   MySQL中二进制字符串类型 类型名称 具体描述 存储需求 bit 位字段类型 约M+7个字节 binary 固定长度二进制字符串 M个字节 varbinary 可变长度二进制字符串 M+1个字节 tinyblob 非常小的BLOB L+1个字节,L<2^8 blob 较小的BLOB L+2个字节,L<2^16 mediumblob 中等大小的BLOB L+3个字节,L<2^24 longblob 较大的BLOB L+4个字节,L<2^32 要点： 1.文本字符串和二进制字符串本质区别在于数据存储方式不同，前者多以文本的ASCII码形式存储在计算机中，后者以文本的二进制形式存储在计算机中； 2.char与varchar,char是固定长度、自动删除插入数据的尾部空格，varchar是可变长度、不会删除尾部空格，char的优势在于处理数据更快，varchar的优势在于节省存储空间；

  最近发现许多同学对日期数据（YYYY-MM-DD hh:mm:ss）比较陌生，导致在表存储和表查询阶段出现了诸多问题，遂写下该博文，期望能够帮助同学掌握日期数据的常见处理方法。   日期数据有其固定的数据存储格式为datetime，但有时我们为了便捷会直接将其存成char字符型的数据，注意不要将日期数据存为数值型，由此我们分别建立两张test表，相关程序如下： · 日期数据存储方法 -- 创建test01表，为datetime型 create table test01( user_id int(10), birthday datetime #datetime最大宽度为6，默认不填 ); insert into test01 values(10001,"1988-01-05"),(10002,"1991-02-05"); -- 创建test02表，为char型 create table test02( user_id int(10), birthday char(20) ); insert into test02 values(10001,"1988-01-05"),(10002,"1991-02-05"); · 日期数据查询方法 -- 其中test01替换成test02结果相同 -- 查询年、月、日 select year(birthday) 年份,month(birthday) 月份,day(birthday) 日期 from test01; -- 增加一年、添加一月、减少一月 select date_add(birthday,interval 1 day) as 日期加一天, date_add(birthday,interval 1 month) as 日期加一月, date_add(birthday,interval -1 month) as 日期减一月 from test01; -- 查询当前时间 -- 对应输出为"YYYY-MM-DD hh:mm:ss","YYYY-MM-DD","hh:mm:ss" select now(),CURDATE(),CURTIME(); -- 计算当前日期与生日相差天数 select DATEDIFF(CURDATE(),birthday) 相差天数 from test01;

1.在数据库中创建emp表空间 # 注意：emp表所有字段的数据类型必须为字符型 create table emp( empno char(100), ename char(100), job char(100), leader char(100), hiredate char(100), sal char(100), comm char(100), deptno char(100) ); 2.将emp表中内容粘贴到Notepad编辑器上，粘贴完成后如下图所示 3.利用Notepad正则表达式批量替换文本中内容  注意：Notepad查找/替换快捷键”Ctrl+F”;下述（1）-（3）查找模式为正则、（4）为普通 （1）替换开头列 -- ^ 正则表达式中代表开头 insert into emp values\(" -- 注意"\("代表转译符 （2）替换结尾列 -- $ 正则表达式中代表结尾 "\); -- 注意"\)"代表转译符 （3）替换空格列 -- "空格" "," -- 注意前后单引号 （4）将空值替换成null "\N" null 4.将替换好的代码全部复制到数据库中运行 5.查看emp表 select * from emp;

1.在数据库中创建emp表空间 create table emp ( empno int, ename varchar(20), job varchar(20), leader int, hiredate datetime, sal float, comm float, deptno int ); 2.将emp表保存为csv格式文件，放在D盘下   emp表数据要求格式如下：emp表下载路径  （1）删除表中变量名，确保要插入的数据从第一行开始；  （2）将表中所有空值部分用\N替代；  （3）确保每一行的观测值与表空间数据类型一致； 3.查询数据表安全加载路径 show variables like '%secure%'; 4.将emp表复制到指定路径下  （1）打开Windows运行下cmd命令框  （2）进入到数据库安全加载路径目录 cd C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\  （3）将D盘中emp.csv复制到当前目录下，并查看 copy d:\emp.csv .\ dir #查看当前目录下所有文件 5.利用load data命令插入数据到emp表空间 load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/emp.csv' into table emp fields terminated by ',' ; # 注：路径切换符注意使用/，而不是\，不然会报错 # 通用规则：load data infile 'filename_path' into table table_name fields terminated by ',' ; # filename_path表示文件存放路径 # table_name表示数据表名称 # ','表示字段间以逗号分隔 6.查看emp表 select * from emp;

1.初始化数据库 service postgresql initdb 2.启动数据库 service postgresql start 3.切换用户修改密码 su postgres psql alter user postgres with password '1234'; \q 4.修改认证文件/var/lib/pgsql/data/pg_hba.conf vi /var/lib/pgsql/data/pg_hba.conf 把配置文件METHOD栏下方，修改为trust，从而解决用户Ident认证失败这个问题 5.重启postgresql服务器使设置生效 service postgresql restart pg_hba.conf文件的完整代码如下： #========================================= # PostgreSQL Client Authentication Configuration File # =================================================== # # Refer to the "Client Authentication" section in the PostgreSQL # documentation for a complete description of this file. A short # synopsis follows. # # This file controls: which hosts are allowed to connect, how clients # are authenticated, which PostgreSQL user names they can use, which # databases they can access. Records take one of these forms: # # local DATABASE USER METHOD [OPTIONS] # host DATABASE USER ADDRESS METHOD [OPTIONS] # hostssl DATABASE USER ADDRESS METHOD [OPTIONS] # hostnossl DATABASE USER ADDRESS METHOD [OPTIONS] # # (The uppercase items must be replaced by actual values.) # # The first field is the connection type: "local" is a Unix-domain # socket, "host" is either a plain or SSL-encrypted TCP/IP socket, # "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a # plain TCP/IP socket. # # DATABASE can be "all", "sameuser", "samerole", "replication", a # database name, or a comma-separated list thereof. The "all" # keyword does not match "replication". Access to replication # must be enabled in a separate record (see example below). # # USER can be "all", a user name, a group name prefixed with "+", or a # comma-separated list thereof. In both the DATABASE and USER fields # you can also write a file name prefixed with "@" to include names # from a separate file. # # ADDRESS specifies the set of hosts the record matches. It can be a # host name, or it is made up of an IP address and a CIDR mask that is # an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that # specifies the number of significant bits in the mask. A host name # that starts with a dot (.) matches a suffix of the actual host name. # Alternatively, you can write an IP address and netmask in separate # columns to specify the set of hosts. Instead of a CIDR-address, you # can write "samehost" to match any of the server's own IP addresses, # or "samenet" to match any address in any subnet that the server is # directly connected to. # # METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", # "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert". Note that # "password" sends passwords in clear text; "md5" is preferred since # it sends encrypted passwords. # # OPTIONS are a set of options for the authentication in the format # NAME=VALUE. The available options depend on the different # authentication methods -- refer to the "Client Authentication" # section in the documentation for a list of which options are # available for which authentication methods. # # Database and user names containing spaces, commas, quotes and other # special characters must be quoted. Quoting one of the keywords # "all", "sameuser", "samerole" or "replication" makes the name lose # its special character, and just match a database or username with # that name. # # This file is read on server startup and when the postmaster receives # a SIGHUP signal. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect. You can # use "pg_ctl reload" to do that. # Put your actual configuration here # ---------------------------------- # # If you want to allow non-local connections, you need to add more # "host" records. In that case you will also need to make PostgreSQL # listen on a non-local interface via the listen_addresses # configuration parameter, or via the -i or -h command line switches. # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident #=========================================

· 安装PostgreSQL 1.查看系统是否安装了PostgreSQL： rpm -qa | grep postgresql 注：如果没有结果就说明未安装,跳转到2   如果有就利用 rm -rf 删除 2.利用yum安装PostgreSQL数据库 yum -y install postgresql* 3.查看安装好的PostgreSQL版本 psql --version · 启动PostgreSQL 1.给linux添加一个PostgreSQL超级管理用户 # 添加一个pg用户 adduser pguser # 查看是否添加成功 cat /etc/passwd | grep pguser 2.启动PostgreSQL服务 service postgresql start 3.切换到PostgreSQL用户（postgres是数据库默认的角色） su postgres 4.进入PostgreSQL数据库 psql 注：系统提示变为“postgres=#”,即进入到数据库中 · 操作PostgreSQL 1.查看所有数据库 \l 2.选择数据库 \c db_name 3.查看当前数据库所有表格 \d 4.查看当前数据库所有表格 \d 5.查看表格的结构 \d table_name 6.退出控制台（快捷键Ctrl+D） \q

· 单表查询 select * from table_name; where 条件查询 group by 分组查询 having 分组后筛选 order by 结果排序 -- 查询emp表中所有的行和列（emp表位于表管理单元测试中） select * from emp; -- 从表中检索部分的变量 select empno,ename,job,leader,hiredate,sal,comm,deptno from emp; select empno as 员工编号,ename as 员工姓名,job as 职位,leader as 直属领导,hiredate as 雇用时间,sal as 薪水,comm as 津贴,deptno as 部门编号 from emp; select empno 员工编号,ename 员工姓名,job 职位,leader 直属领导,hiredate 雇用时间,sal 薪水,comm 津贴,deptno 部门编号 from emp; -- 查询去重 select distinct deptno from emp; ---- 条件查询 ---- -- 从表中检索部分观测（大小写不敏感） select * from emp where job='salesman'; -- 从表中查找空值 select * from emp where comm is null; select * from emp where comm is not null; -- 将空值转化为实际值 select coalesce(comm,0) from emp; -- 多条件查询 select * from emp where deptno=10 or deptno=20; select * from emp where deptno=10 or (deptno=20 and sal<=2000); -- 模糊查询 select * from emp where ename like 'a%'; select * from emp where ename like '%a%'; select * from emp where ename like '_a%'; ---- 分组查询 ---- select deptno,sum(sal) from emp group by deptno; select deptno,sum(comm) from emp group by deptno; #空值不计算 select deptno,sum(coalesce(comm,0)) from emp group by deptno; #空值变为0 select deptno,sum(sal) from emp where sal>2000 group by deptno; select deptno,job,sum(sal) from emp group by deptno,job; ---- 分组后筛选 ---- select deptno,job,sum(sal) from emp group by deptno,job having job = 'manager'; select deptno,job,sum(sal) from emp where job = 'manager' group by deptno,job; #where条件查询更快 -- 为什么还要having？ select deptno,job,sum(sal) from emp group by deptno,job having sum(sal)>5000; select deptno,job,new_sal from (select deptno,job,sum(sal) as new_sal from emp group by deptno,job) as a where new_sal>5000; ---- 结果排序 ---- select * from emp order by sal; select * from emp order by sal desc; select * from emp order by sal desc limit 5; select deptno,job,sum(sal) as new_sal from emp group by deptno,job order by deptno,new_sal; ---- 逻辑操作符 ---- and,or 且，或 !=(<>) 不等于 is null 查询空值 like 模糊查询 between ... and ... (b<=a<c) ---- 逻辑表达式 ---- select ename,sal, case when sal<= 2000 then '过低' when sal>= 4000 then '过高' else '可行' end as status from emp; · 多表查询 创建测试表 a1、a2 create table a1(x int,y varchar(20)); create table a2(m int,n varchar(20)); insert into a1(x,y) values(1,"one"),(2,"two"),(2,"two2"),(3,"three"); insert into a2(m,n) values(1,"one"),(2,"two"),(4,"four"); ---- 纵向查询（连接的观测值——行）---- select * from a1 union select * from a2; -- 加入条件语句 select x,y from a1 where x=1 union select m,n from a2 where m=2 order by 1 desc; -- emp各部门工资排名前三的员工 (select deptno,ename,sal from emp where deptno=10 order by sal desc limit 3) union (select deptno,ename,sal from emp where deptno=20 order by sal desc limit 3) union (select deptno,ename,sal from emp where deptno=30 order by sal desc limit 3); ---- 横向连接（连接的是变量——列） ---- -- 内连接 select * from a1 inner join a2 on a1.x=a2.m; select * from a1 join a2 on a1.x=a2.m; select * from a1,a2 where a1.x=a2.m; -- 左连接 select * from a1 left join a2 on a1.x=a2.m; -- 右连接 select * from a1 right join a2 on a1.x=a2.m; -- 全连接（Mysql不支持） select * from a1 full join a2 on a1.x=a2.m; # MySQL不支持 select * from a1 left join a2 on a1.x=a2.m union select * from a1 right join a2 on a1.x=a2.m; · 子查询 创建测试表players,penalties ---- 表 子查询from型 ---- -- 获取编号小于10的男性球员的号码 select playerno from players where playerno<10 and sex='M'; select playerno from (select playerno,sex from players where playerno<10) as players10 where sex='m'; ---- 行 子查询where型 ---- -- 获取和100号球员性别相同并且居住在同一城市的球员号码 select playerno,sex,town from players where (sex,town)=(select sex,town from players where playerno=100); -- 获取和27号球员出生在同一年的球员的号码 select playerno,birth_date from players where year(birth_date)=(select year(birth_date) from players where playerno = 27); select playerno,birth_date from players where year(birth_date)=1964; #需求转化 ---- 列 子查询 ---- -- 1.实例（in）：获取球员性别为女的所有球员的球员号，名字及所在城市 select playerno,name,town from players where playerno in (select playerno from players where sex="F"); -- 2.实例（any）：获取至少比同城的另一球员年轻的所有球员的号码，日期和居住城市 select playerno,birth_date,town from players as p1 where birth_date> any(select birth_date from players as p2 where p1.town = p2.town); -- 3.实例（all）：获取最老球员的号码，名字及生日。（即出生日期数值小于或等于所有其它球员的球员） select playerno,name,birth_date from players where birth_date<= all(select birth_date from players); ---- exists型子查询 ---- -- 1.实例（exists）：获取那些至少支付了一次罚款的球员的名字和首字母 select name,initials from players where exists (select * from penalties where playerno = players.playerno); -- 2.实例（not exists）：获取那些从来没有罚款的球员的名字和首字母 select name,initials from players where not exists (select * from penalties where playerno = players.playerno); · 高级查询 ---- 数字运算 ---- -- 每个部门平均值、最小值、最大值、工资合计、部门人数、获得提成人数 select deptno as 部门编号, avg(sal) as 平均值, min(sal) as 最小值, max(sal) as 最大值, sum(sal) as 工资合计, count(ename) 部门人数, count(comm) 获得提成人数 from emp group by deptno; -- 每个部门人均提成的算法 select deptno 部门编号, avg(comm) 错误的人均提成算法, avg(coalesce(comm,0)) 正确的人均提成算法 from emp group by deptno; ---- 日期运算 ---- select hiredate as 聘用日期, date_add(hiredate,interval 1 day) as 日期加一天, date_add(hiredate,interval 1 month) as 日期加一月, date_add(hiredate,interval -1 month) as 日期减一月 from emp; ---- 字符串运算 ---- select substring(ename,1,4) from emp; #截取字符串 select replace(ename,"smith","SMITH") from emp; #替换字符串 select concat(job,sal) as data from emp; #字符与数字合并 select convert(empno,signed) from emp; #数字转字符 ---- 正则表达式 ---- #Mysql大小写不敏感，binary属性让大小写敏感 create table aa1(id int auto_increment primary key,data varchar(20) binary); insert into aa1(data) values ("123"),("abc"),("123abc"),("abc123"); insert into aa1(data) values ("DeF456"),("456dEf"); select data from aa1 where data regexp "[0-9]"; select data from aa1 where data regexp "[a-z]"; select data from aa1 where data regexp "def"; select data from aa1 where data regexp "[De][Ee][Ff]";

· 约束 约束作用：1.防止将错误值插入数据表；2.保持表中数据的一致性 包含哪些：1.非空约束、唯一约束、检查约束；2.主键约束、外键约束 -- 主键约束 create table s_user( u_id int auto_increment primary key, u_name varchar(20), u_pwd varchar(20) ); # 添加自增列 create table s_user( u_id int auto_increment primary key, u_name varchar(20), u_pwd varchar(20) ); -- 外键约束 -- 方法1 create table s_order( o_id int auto_increment primary key, o_buyer_id int, o_totalprices float, foreign key(o_buyer_id) references s_user(u_id) ); -- 方法2 create table s_order2( o_id int auto_increment primary key, o_buyer_id int, o_totalprices float ); alter table s_order2 add foreign key(o_buyer_id) references s_user(u_id); · 表管理 -- 添加数据 insert into s_user(u_name,u_pwd) values("carry","123"); insert into s_user(u_name,u_pwd) values("carry","123"),("harry","456"),("marry","789"); -- 修改数据 set sql_safe_updates=0; #设置数据库安全等级 update s_user set u_pwd="963" where u_name="harry"; -- 删除数据 delete from s_user where u_name="marry"; · 单元测试 创建员工表emp · 相关连接 MySQL基础讲义—— 数据库与表空间 MySQL基础讲义—— 约束与表管理 MySQL基础讲义—— SQL查询

· 数据库 -- 创建数据库 create database test1; -- 查看数据库 show databases; -- 选择数据库 use test1; -- 删除数据库 drop database test1; · 表空间 表空间是什么？ 数据类型（共3大类）：数值型、字符型、日期型 数值型：int整数型、float浮点型 字符型：char、varchar 注意:区别，填满和不填满 日期型：datetime “YYYY-MM-DD hh:mm:ss” -- 创建表 create table stu_inf(id int(10),stu_name varchar(20),stu_sex varchar(20),stu_score float(10)); -- 查询表 show tables; describe stu_inf; -- 修改表(修改表名称，添加变量，修改变量，删除变量) alter table stu_inf rename a1; #修改表名称 alter table a1 add column class varchar(20); #添加变量 alter table a1 change column class stu_class varchar(20); #修改变量 alter table a1 drop column stu_class; #删除变量 -- 删除表 drop table a1; 索引：主键索引（一张表里只能有一个）    唯一索引（一张表里可能存在多个） 作用：提高后台查询效率（为什么?） -- 添加索引 alter table stu_inf add primary key(id); alter table stu_inf add unique(stu_name); -- 查询索引 show index from stu_inf; -- 修改索引（先删除后添加） -- 删除索引 alter table stu_inf drop primary key; #删除主键索引 alter table stu_inf drop index stu_name; #删除其他索引 · 单元测试 创建一个数据库 test_stu 进入数据库 test_stu 在数据库 test_stu 中建立四张表： Student 学生表（id, S_name, S_age, S_sex） Score 成绩表（S_id, C_name, score） Teacher 教师表（id, S_id, C_name, T_name） · 相关连接 MySQL基础讲义—— 数据库与表空间 MySQL基础讲义—— 约束与表管理 MySQL基础讲义—— SQL查询

1.双击Setup.exe文件，开始安装 2.选择安装Custom 3.选择操作系统x64表示64位，x86表示32位 4.选择安装路径 5.点击3次下一步，直到要求输入账户密码（可根据个人的喜好填写） 6.继续点击N次下一步+Finish，完成安装

1.输入网址（www.mysql.com） 2.点击Downloads =>下载Mysql Community Editon社区版  注：该页面包括以下三个版本  Mysql Enterprise Edition 企业版  Mysql Cluster CGE 集群版  Mysql Community Editon 社区版 3.点击Mysql Community Server下Download 4.下拉找到 Mysql Install for Windows => 点击Download 5.选择下载文件 6.点击直接下载  注:该版本内包含32位和64位，安装时注意选项配置