2-2课件：SQL入门及应用‏

2-2SQL入门及应用目录任务要求3任务描述2任务准备1任务实现4MySQL基本概念MySQL是一种开源的关系型数据库管理系统（RDBMS），广泛应用于Web开发中。MySQL是一种客户端-服务器系统，其中客户端和服务器可以在不同的机器上，支持多用户、多线程。数据库数据库(database)是按照数据结构来组织、存储和管理数据的仓库。数据库是一个结构化的数集合，而关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。这种所谓的“关系型”可以理解为“表格”的概念，一个关系型数据库由一个或多个表格组成。MySQL是一个关系型数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样可以提高速度和灵活性。它支持大型的数据库，可以处理拥有上千万条的记录。数据库的连接不管使用什么工具，连接MySQL数据库需要用到的参数包括主机、端口、用户名、密码和数据库名。(1)主机/服务器(host)：数据库所在主机名或IP地址。(2)端口(port)：连接到MySQL数据库所在服务器的端口号。(3)用户名(username)：MySQL数据库的用户名。(4)密码(password)：MySQL数据库用户的密码。(5)数据库名(dbname)：连接数据库的名称。数据类型MySQL支持多种数据类型，大致可以分为三类：数值、日期/时间和字符串类型，主要数据类型如表所示。类型说明INT或INTEGER整数值FLOAT单精度浮点数DOUBLE双精度浮点数DECIMAL小数值，用于高精度计算场景DATE日期值TIME时间值YEAR年份值VARCHAR可变长度字符串，适用于存储变长文本基本的SELECT语句MySQL数据库使用SELECT语句来查询数据，列名可以通过AS进行重命名，语法如下：SELECT列1,列2AS新列名FROM表名查询所有列的数据时，可以用通配符“*”代替，语法如下：SELECT*FROM表名查询去掉重复值的列值时，可以在列名前加DISTINCT，语法如下：SELECTDISTINCT列名FROM表名应用示例如下：--查询利润表中营业收入和营业成本的数据SELECT报表日期,其中：营业收入AS营业收入,其中：营业成本AS营业成本FROM利润表_幸福蛋糕--查询利润表所有列数据SELECT*FROM利润表_幸福蛋糕--查询门店信息表中门店类型的唯一值SELECTDISTINCT门店类型FROM门店信息表条件查询如果需要有条件地从表中选取数据，可将WHERE子句添加到SELECT语句中，WHERE子句用于在MySQL中过滤查询结果，只返回满足特定条件的行，语法如下：SELECT列1,列2FROM表名WHERE条件在WHERE子句中，可以使用各种条件运算符(如=、<、>、<=、>=、!=、IN、NOTIN)、逻辑运算符(如AND、OR、NOT)及通配符(如%)等，应用示例如下：--查找《门店信息表》中门店类型为“新零售店”且位于深圳市的门店信息SELECT*FROM门店信息表WHERE门店类型="新零售店"AND市="深圳市"--查找《门店信息表》中位于湖南省或湖北省的信息SELECT*FROM门店信息表WHERE省='湖南'OR省='湖北'--查找《门店信息表》中门店设立时间满足特定条件的信息SELECT*FROM门店信息表WHERE门店设立时间IN('2020-03-17','2020-04-02','2020-04-29')模糊查询在条件查询的语句中，使用WHERE子句来获取指定的记录时，可以使用“=”来设定获取数据的条件，但有时获取数据的条件并不精确，例如，只知道列的开头、结尾或中间包含某些字符，这时，可以使用LIKE子句。在MySQL中，LIKE子句是用于在WHERE子句中进行模糊匹配的关键字，它通常与通配符一起使用，用于搜索符合某种模式的字符串。LIKE子句中常用的通配符包括“_”“%”等。其中，“_”可代替单个任何字符，“%”代替任何字符出现任意次数。应用示例如下：--查找《门店信息表》中“门店”字段中包含“市”且以“三店”结尾的信息SELECT*FROM门店信息表WHERE门店LIKE'%市%三店'排序和分组如果需要对读取的数据进行排序，可以使用MySQL的ORDERBY子句来设定排序方式，ORDERBY子句可以按照一个或多个列的值进行升序(ASC)或降序(DESC)排序，语法如下：SELECT列1,列2FROM表名ORDERBY列1[ASC|DESC],列2[ASC|DESC]GROUPBY子句可以根据一个或多个列对结果集进行分组。在分组的列上可以使用COUNT、SUM、AVG等聚合函数对数据进行汇总，语法如下：SELECT列1,COUNT(*)FROM表名GROUPBY列1排序和分组ORDERBY、GROUPBY子句也可以和WHERE子句结合使用，应用示例如下：--将《门店信息表》中的门店按设立时间的先后进行排序SELECT*FROM门店信息表ORDERBY门店设立时间ASC--统计烘焙行业各年度平均净利润SELECT报表日期,AVG(净利润)AS行业平均净利润FROM利润表_烘焙行业GROUPBY报表日期--统计烘焙行业除“桃李面包外”的公司各年度总营业收入，并按报表时间倒序排序SELECT报表日期,SUM(营业收入)FROM利润表_烘焙行业WHERE公司名称!='桃李面包'GROUPBY报表日期ORDERBY报表日期DESC多表查询多表查询可以通过条件关联语句或JOIN子句实现，可以根据关联条件从多个表中获取数据。例如，计算净利润占期末总资产的比例时，需要从利润表和资产负债表两张数据表中获取数据，示例代码如下：SELECTt1.报表日期,t1.五、净利润AS净利润,t2.资产总计FROM利润表_幸福蛋糕ASt1,资产负债表_幸福蛋糕ASt2WHEREt1.报表日期=t2.报表日期嵌套查询在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE等子句的条件中的查询称为嵌套查询。应用示例如下：--查找位于四川省的门店的计划销售额信息SELECT*FROM门店销售计划表WHERE门店编号IN(SELECT门店编号FROM门店信息表WHERE省='四川')--查询《年度销售额汇总表》中下午茶的销售额和总销售额，并按年度汇总SELECTa.年度,b.下午茶销售额,a.总销售额FROM(SELECTYEAR(期间)AS年度,SUM(销售额)AS总销售额FROM年度销售额汇总表GROUPBYYEAR(期间))a,(SELECTYEAR(期间)AS年度,SUM(销售额)AS下午茶销售额FROM年度销售额汇总表WHERE商品类别='下午茶'GROUPBYYEAR(期间))bWHEREa.年度=b.年度表的连接在多表查询中，MySQL还可以使用JOIN在两个或多个表中查询数据。JOIN按功能分为内连接、左连接和右连接三类。内连接左连接右连接表的合并在MySQL中，可以使用UNION将两个以上的SELECT语句的查询结果组合到一个结果集合，并去除重复的行。UNION操作符必须由两个或多个SELECT语句组成，每个SELECT语句的列数和对应位置的数据类型必须相同。例如，合并案例企业“幸福蛋糕”和“烘焙行业”的利润表部分字段，示例代码如下：SELECT报表日期,'幸福蛋糕'AS公司名称,其中：营业收入AS营业收入FROM利润表_幸福蛋糕UNIONSELECT报表日期,公司名称,营业收入FROM利润表_烘焙行业SELECT报表日期,'幸福蛋糕'AS公司名称,其中：营业收入AS营业收入FROM利润表_幸福蛋糕UNIONSELECT报表日期,公司名称,营业收入FROM利润表_烘焙行业常用函数MySQL中有很多函数，可以分别用于字符类（如下表）、数值类和日期时间类数值的处理与统计的函数，以及逻辑函数。函数说明CONCAT(s1,s2)将s1,s2合并为一个字符串LEFT(s,n)返回字符串s的前n个字符MID(s,n,len)从字符串s的n位置截取长度为len的子字符串，同SUBSTRING(s,n,len)POSITION(s1INs)从字符串s中获取s1的开始位置REPLACE(s,s1,s2)将字符串s2替代字符串s中的字符串s1RIGHT(s,n)返回字符串s的后n个字符RPAD(s1,len,s2)在字符串s1的结尾处添加s2，使字符串的长度达到lenSTRCMP(s1,s2)比较字符串s1和s2，如果s1与s2相等返回0，如果s1>s2返回1，如果s1<s2则返回-1SUBSTR(s,start,length)或SUBSTRING(s,start,length)从字符串s的start位置截取长度为length的子字符串常用函数MySQL中有很多函数，可以分别用于字符类、数值类（如下表）和日期时间类数值的处理与统计的函数，以及逻辑函数。函数说明AVG(expression)返回一个表达式的平均值，expression是一个字段COUNT(expression)返回查询的记录总数，expression参数是一个字段或者*号MAX(expression)返回字段expression中的最大值MIN(expression)返回字段expression中的最小值ROUND(x[,y])返回离x最近的整数，可选参数y表示要四舍五入的小数位数，如果省略，则返回整数。SUM(expression)返回指定字段的总和常用函数MySQL中有很多函数，可以分别用于字符类、数值类和日期时间类数值（如下表）的处理与统计的函数，以及逻辑函数。函数说明DATE()从日期或日期时间表达式中提取日期值DATEDIFF(d1,d2)计算日期

d1->d2之间相隔的天数DATE_ADD(d，INTERVALexprtype)计算起始日期

d加上一个时间段后的日期，type值可以是：DAY、MONTH、YEAR等日期DATE_FORMAT(d,f)按表达式

f的要求显示日期dDATE_SUB(date,INTERVALexprtype)函数从日期减去指定的时间间隔。DAY(d)返回日期值

d的日期部分LAST_DAY(d)返回给给定日期的那一月份的最后一天MONTH(d)返回日期d中的月份值，1到12YEAR(d)返回年份常用函数MySQL中有很多函数，可以分别用于字符类、数值类和日期时间类数值的处理与统计的函数，以及逻辑函数（如下表）。函数说明IF(condition,value_if_true,value_if_false)如果condition为真，返回value_if_true，否则返回value_if_falseCASEWHENcondition1THENresult1WHENcondition2THENresult2...ELSEelse_resultEND当条件condition1成立时返回result1，条件condition2成立时返回result2，依次类推，最后如果不满足上面任意条件，返回else_result常用运算符MySQL中的运算符包括算术运算符（如下表）、比较运算符、逻辑运算符等。运算符作用+加法-减法*乘法/或DIV除法常用运算符MySQL中的运算符包括算术运算符、比较运算符（如下表）、逻辑运算符等。运算符作用=等于<>,!=不等于>大于<小于<=小于等于>=大于等于IN在集合中NOTIN不在集合中LIKE模糊匹配ISNULL为空ISNOTNULL不为空常用运算符MySQL中的运算符包括算术运算符、比较运算符、逻辑运算符（如下表）等。运算符作用NOT或!逻辑非AND逻辑与OR逻辑或数据采集任务要求3任务描述2任务准备1任务实现4任务描述小蝶利用寒假的时间在幸福蛋糕财务部实习，财务经理给小蝶安排任务，让她基于系统中的财务报表数据做一些简单的指标统计。数据采集任务要求3任务描述2任务准备1任务实现41.基于《利润表_幸福蛋糕》，统计近三年的营业收入、毛利率。2.基于《资产负债表_幸福蛋糕》，统计各年的存货增长率。3.基于《利润表_幸福蛋糕》和《资产负债表_幸福蛋糕》，统计幸福蛋糕在各个年度的总资产净利率。任务要求数据采集任务要求3任务描述2任务准备1任务实现4任务实现SELECTa.报表日期,a.存货AS本期存货,b.上期存货,(a.存货-b.上期存货)/b.上期存货AS存货增长率FROM

资产负债表_幸福蛋糕a,(SELECTDATE_ADD(报表日期,INTERVAL1YEAR)AS报表日期