SQL入门语句介绍.ppt

1、应用分析师专业人才培训项目SQL入门语句介绍,1,2018年4月,SQL基础教程（第二版） 作者: MICK 出版社: 人民邮电出版社 译者: 孙淼 / 罗勇 出版年: 2017-6-1 页数: 320 购买渠道: 当当、京东等,2,推荐书籍,本书是畅销书SQL基础教程第2版，介绍了关系数据库以及用来操作关系数据库的SQL语言的使用方法。书中通过丰富的图示、大量示例程序和详实的操作步骤说明，让读者循序渐进地掌握SQL的基础知识和使用技巧，切实提高编程能力。另外，本书还将重要知识点总结为“法则”，方便读者随时查阅。,一. SQL语言简介 二. SQL中的SELECT语句 三. SQL中的常用函数

2、 四. SQL中的子句 五. SQL中的表连接,3,目录,SQL 语言简史,目前，已被大多数商品化关系 数据库采用，如：Oracle（甲骨文）、Sybase、DB2、Informix、SQL Server、Teradata。,结构化查询语言Structured Query Language，简称SQL语言，1974年诞生于IBM 公司在加利福尼亚州的San Jose试验室中。其特点： 语言简洁、功能丰富、使用灵活。,4,SQL语言共分为四大类： 1、数据查询语言最基本的SQL语言 负责进行数据查询而不会对数据本身进行修改的语句。 2、数据操纵语言 对数据进行存取。 3、数据定义语言 负责数据结

3、构的定义。 4、数据控制语言 控制用户存取数据的权限。,SQL语言分类,5,客户营销 通用查询,Title in here,Title n here,信息创造价值,考核评价 灵活查询,建模分析 数据挖掘,掌握SQL语言的必要性,6,SELECT Name as “姓名” ,Zone_Num as “地区号” ,Branch_Num as “支行号” ,Mobile as “手机号” FROM Custom_Info WHERE Zone_Num=0200 and Wap_MobBank_Ind=0,字段,表名,筛选条件,SQL脚本举例-提取未开通手机银行客户清单,ORDER BY Name,排

4、序,通过SQL提取本地区的客户中未开通手机银行的客户信息，联系客户向其营销手机银行产品。,7,一. SQL语言简介 二. SQL中的SELECT语句 三. SQL中的常用函数 四. SQL中的子句 五. SQL中的表连接,目录,8,Select 语句 SELECT 语句是SQL 中最基本、应用最多的语句,它会从数据库中检索需要的数据并把结果返回给用户。,可以说，通用查询、灵活查询中所需要用到的SQL语言几乎都会包含SELECT语句的使用。 学会了使用SELECT语句，也就掌握了通用查询、灵活查询工具的基本技巧。,二、SQL中的SELECT语句,9,可以使用通配符“*”显示所有的字段。例如： S

5、ELECT * -返回所有列。 可以罗列出所需要的字段。例如： SELECT 字段1 ,字段2 ,字段n -返回指定的字段。,二、SQL中的SELECT语句,10,数据表 Custom_Info,二、SQL中的SELECT语句,11,任务一 从数据表中找到客户编号为“020001234567891” 的那条记录。,二、SQL中的SELECT语句,12,任务一,SELECT * FROM Custom_Info WHERE Party_Id=020001234567891,Select 部分,表名,筛选条件,二、SQL中的SELECT语句,13,任务二 找出所属地区为0200或3000的所有客户

6、，输出他们的 姓名、性别代码、所属地区和总资产（以“万元”为单位）。,二、SQL中的SELECT语句,14,任务二,SELECT Name ,Zone_Num ,Gender_Cd ,Total_Asset/10000 as Asset FROM Custom_Info WHERE Zone_Num=0200 or Zone_Num=3000,Select 部分,表名,筛选条件,以万元为单位,二、SQL中的SELECT语句,15,任务三 所属地区为0200或3000、并且总资产大于等于 100万的客户，输出他们的的姓名、所属地区、性别 代码和总资产（以“万元”为单位）。,二、SQL中的SELE

7、CT语句,16,任务三,SELECT Name ,Zone_Num ,Gender_Cd ,Total_Asset/10000 as Asset FROM Custom_Info WHERE (Zone_Num=0200 or Zone_Num=3000) and Total_Asset = 1000000,二、SQL中的SELECT语句,17,练习1 从数据表中找到所属地区为1001，并且总资产大于40万的客户，输出他们的客户编号、姓名、出生日期、手机号码和总资产。,参考结果,二、SQL中的SELECT语句,18,一. SQL语言简介 二. SQL中的SELECT语句 三. SQL中的常用函

8、数 四. SQL中的子句 五. SQL中的表连接,目录,19,三、SQL中的常用函数,聚合函数 SUM 求和函数 COUNT 计数函数 MAX/MIN 最大/最小值 AVG 平均值函数,字符串函数 SUBSTR 截取函数,20,SUM 求和函数 用途：对于满足WHERE子句中限制条件的记录，返回某一列所有数值之和。 任务：计算0200地区所有客户的总资产之和（以万元为单位）,三、SQL中的常用函数,21,SUM 求和函数,SELECT sum ( Total_Asset ) /10000 as Sum_Asset FROM Custom_Info WHERE Zone_Num=0200,三、S

9、QL中的常用函数,22,COUNT 计数函数 用途：返回满足WHERE子句中限制条件的记录个数 任务：计算0200地区的男性客户数量,三、SQL中的常用函数,23,COUNT 计数函数,SELECT count ( * ) as number FROM Custom_Info WHERE Zone_Num=0200 and Gender_Cd=1,三、SQL中的常用函数,24,MAX/MIN 最大值/最小值函数 用途：对于满足WHERE子句中限制条件的记录，返回某一列的最大值/最小值。 任务：计算0200地区的总资产最大和最小的客户的总资产是多少。,三、SQL中的常用函数,25,MAX/MIN

10、 最大值/最小值函数,SELECT max( Total_Asset ) as Max_Asset , min ( Total_Asset ) as Min_Asset FROM Custom_Info WHERE Zone_Num=0200,三、SQL中的常用函数,26,AVG 平均值函数 用途：对于满足WHERE子句中限制条件的记录，返回某一列的平均值。 任务：计算0200地区的客户平均资产。,三、SQL中的常用函数,27,AVG 平均值函数,SELECT avg ( Total_Asset ) as Avg_Asset FROM Custom_Info WHERE Zone_Num=02

11、00,三、SQL中的常用函数,28,SUBSTR 截取函数 用途：从字符串中的任意位置截取指定长度的字符串。标准格式为 substr(Field,m,n)，它有三个参数，第一个参数为目标字符串，第二个参数是将要输出的子串的起点，第三个参数是将要输出的子串的长度。 任务：找出0200地区所有客户的姓名的第一个字。,三、SQL中的常用函数,29,SUBSTR 截取函数,SELECT substr ( Name,1,2 ) as Family_Name FROM Custom_Info WHERE Zone_Num=0200,三、SQL中的常用函数,30,练习2 统计手机“139”号段的客户的最大资

12、产和最小资产 分别是多少？,提示：可以在Where部分中使用Substr函数，对手机号进行筛选。,参考结果,三、SQL中的常用函数,31,如何学习应用更多函数？ -参考书籍 -网络资料 -软件帮助文档,三、SQL中的常用函数,32,一. SQL语言简介 二. SQL中的SELECT语句 三. SQL中的常用函数 四. SQL中的子句 五. SQL中的表连接,目录,33,分组子句,GROUP BY,HAVING,筛选子句,排序子句,ORDER BY,四、SQL中的子句,34,CASE WHEN,条件控制语句,where子句查询条件,WHERE,WHERE 子句中的 查询条件,四、SQL中的子句,

13、35,在WHERE子句的条件表达式中，可以用的比较运算符有：,四、SQL中的子句,36,任务：筛选手机号非空、且基金余额不为0的客户，输出客户的姓名、手机号、基金余额。,中间业务产品表 Products,四、SQL中的子句,37,SELECT Party_Name ,Mobile ,Fund_Bal FROM Products WHERE Mobile is not null and Fund_Bal0,手机号非空,基金余额不为0,四、SQL中的子句,38,在WHERE子句的条件表达式中，可以用的逻辑运算符有：,四、SQL中的子句,39,用途：关键字IN，用于查询满足所有罗列条件的信息。 格式

14、：字段值 IN (value1, value2, 表达式n) 任务：筛选地区号为1001、3000的所有客户记录,四、SQL中的子句,40,SELECT * FROM Custom_Info WHERE Zone_Num in (1001,3000),四、SQL中的子句,41,用途：在两个值之间进行筛选。 对数值型使用BETWEENAND进行范围筛选 对字符型使用BETWEENAND进行范围筛选,四、SQL中的子句,42,对数值型使用BETWEENAND进行范围筛选 任务：筛选总资产在10万100万之间的客户，输出 客户的姓名、总资产。,四、SQL中的子句,43,SELECT Name ,To

15、tal_Asset FROM Custom_Info WHERE Total_Asset between 100000 and 1000000,四、SQL中的子句,44,对字符型使用BETWEENAND进行范围筛选 任务：筛选姓名在“王五”和“赵六”之间的客户，输出客户的姓名。,四、SQL中的子句,45,SELECT * FROM Custom_Info WHERE Name between 王五 and 赵六,四、SQL中的子句,46,前面提到的查询实际上都是精确查询，即对查询的字段值有准确的描述。但在实际应用中往往要用到模糊查询，根据某一关键字搜索相关信息。 通常，运算符LIKE和通配符%

16、组合起来使用，即可实现模糊查询。,四、SQL中的子句,47,任务：筛选单位中含“铝业”的客户，输出客户的姓名、单位名称。,Products,四、SQL中的子句,48,SELECT Party_Name ,Corp_Name FROM Products WHERE Corp_Name like %铝业%,四、SQL中的子句,49,GROUP BY 子句 用途：以指定的字段作为分组标志，实现分组汇总。 任务一：计算各个地区的客户的总资产是多少。,四、SQL中的子句,50,GROUP BY 子句 任务一,SELECT Zone_Num ,sum( Total_Asset ) as Asset FRO

17、M Custom_Info GROUP BY Zone_Num,分组标志,汇总函数,分组标志,四、SQL中的子句,51,GROUP BY 子句 任务二：对于每个地区的每个网点，计算其客户数量、客户总资产、客户平均资产、单客户最大资产和单客户最小资产。,四、SQL中的子句,52,GROUP BY 子句 任务二,SELECT Zone_Num ,Branch_Num ,COUNT(*) as number ,SUM(Total_Asset) as Sum_Asset ,AVG(Total_Asset) as Avg_Asset ,MAX(Total_Asset) as Max_Asset ,MIN

18、(Total_Asset) as Min_Asset FROM Custom_Info GRORP BY Zone_Num ,Branch_Num,分组标志,汇总函数,分组标志,四、SQL中的子句,53,ORDER BY 子句 用途：对输出结果按照一定次序进行排序。 任务一：取出0200地区的所有客户记录，并按照客户总资产从小到大进行排列。,四、SQL中的子句,54,ORDER BY 子句 任务一,SELECT * FROM Custom_Info WHERE Zone_Num=0200 ORDER BY Total_Asset,Order By 子句,默认升序排列ASC,四、SQL中的子句,

19、55,ORDER BY 子句 任务二：计算每个地区、每个网点的所有客户总资产，按照地区号从小到大的次序排列。对于同一个地区的各网点，按照所有客户总资产从大到小排列。,四、SQL中的子句,56,ORDER BY 子句 任务二,SELECT Zone_Num ,Branch_Num ,SUM(Total_Asset) as Sum_Asset FROM Custom_Info GRORP BY Zone_Num ,Branch_Num ORDER BY Zone_Num ,Sum_Asset DESC,分组标志,汇总函数,分组标志,Order By 子句,四、SQL中的子句,57,HAVING 子

20、句 用途：对分组汇总后的数据进行限制筛选。 任务：对于客户数量大于1的每个地区的每个网点，计算其客户数量、客户总资产、客户平均资产、单客户最大资产和单客户最小资产。,四、SQL中的子句,58,HAVING 子句,SELECT Zone_Num ,Branch_Num ,COUNT(*) as number ,SUM(Total_Asset) as Sum_Asset ,AVG(Total_Asset) as Avg_Asset ,MAX(Total_Asset) as Max_Asset ,MIN(Total_Asset) as Min_Asset FROM Custom_Info GRORP

21、 BY Zone_Num ,Branch_Num HAVING Number1,分组标志,汇总函数,分组标志,Having 子句,四、SQL中的子句,59,CASE WHEN,是SQL编程中常用的条件控制语句。,新数据项加工,汇总信息加工,CASE WHEN,四、SQL中的子句,60,用途：根据现有字段，利用Case When语句进行逻辑判断，可加工得到新的字段。 任务一：对于全体客户，按照其资产金额进行资产等级认定，输出其客户编号、姓名、总资产和资产等级。,?,四、SQL中的子句,61,SELECT Party_Id ,Name ,Total_Asset ,CASE WHEN Total_A

22、sset5000 THEN 普通客户 WHEN Total_Asset50000 THEN 潜力客户 WHEN Total_Asset200000 THEN 中端客户A WHEN Total_Asset1000000 THEN 中端客户B WHEN Total_Asset8000000 THEN 高端客户 ELSE 私人银行客户 END AS level FROM Custom_Info,四、SQL中的子句,62,用途：Case When 语句与汇总函数（如Sum函数）相结合，能够实现更加灵活的汇总信息加工功能。 任务二：统计80前与80后生人中，男、女客户的人数。,四、SQL中的子句,63,

23、SELECT CASE WHEN Birth_Dt mdy(1,1,1980) THEN 80前 ELSE 80后 END AS 年龄段 ，sum ( CASE WHEN Gender_Cd=1 THEN 1 ELSE 0 END ) AS 男性数量 ， sum( CASE WHEN Gender_Cd=2 THEN 1 ELSE 0 END ) AS 女性数量 FROM Custom_Info GROUP BY 年龄段,四、SQL中的子句,64,用途：Case When 还可以用在 筛选条件中，以实现更加灵活的筛选条件控制。 任务三：筛选目标客户名单，对于0200地区，以资产大于等于100万

24、的客户为目标客户，对于其它地区，以资产大于等于50万为目标客户。输出目标客户的客户编号、姓名、手机号码、地区号和总资产。,四、SQL中的子句,65,SELECT Party_Id ,Name ,Mobile ,Zone_Num ,Total_Asset FROM Custom_Info WHERE CASE WHEN Zone_Num=0200 THEN Total_Asset=1000000 ELSE Total_Asset=500000 END,四、SQL中的子句,66,练习3 统计男性、女性客户的数量和总资产分别是多少，按照从 总资产从大到小的顺序排列。,提示一：男、女性别的表示方法参见

25、CASE WHEN语句的 任务一； 提示二：先做分组汇总，再按照汇总字段进行排序。,参考结果,四、SQL中的子句,67,一. SQL语言简介 二. SQL中的SELECT语句 三. SQL中的常用函数 四. SQL中的子句 五. SQL中的表连接,目录,68,表连接 在很多情况下，我们想要查询的内容分布在很多张不同的数据表中，这时就需要用到表的连接。所谓表的连接，就是按照一定的连接条件，将两张或多张独立的数据表中的特定列拼接成一张合成表，在这张合成表上再进行各种操作（如创建新字段、汇总、排序等）就和我们之前讲述的操作方法基本一致了。 内连接： INNER JOIN 左/右连接: LEFT/RI

26、GHT JOIN 外连接: FULL JOIN 合并: UNION/UNION ALL,五、SQL中的表连接,69,数据表,五、SQL中的表连接,70,内连接 用途：将两张数据表拼接为一张，返回的记录要求在两张表中同时存在。 任务一：找到同时有资产和负债的客户，取出他们的客户编号、资产、负债以及资产与负债之和。,五、SQL中的表连接,71,SELECT a.Id ,a.Asset ,b.Loan ,a.Asset+b.Loan as All FROM Asset as a INNER JOIN Loan as b ON a.Id = b.Id,内连接 任务一,Select 部分,From 部分

27、,表连接 部分,等值连接条件,定义基表,五、SQL中的表连接,72,内连接 任务二：找到同时有资产和负债且资产大于负债的客户，取出他们的客户编号、资产和负债。,五、SQL中的表连接,73,SELECT a.Id ,a.Asset ,b.Loan FROM Asset as a INNER JOIN Loan as b ON a.Id = b.Id and a.Asset b.Loan,内连接 任务二,定义基表,等值连接条件,不等值连接条件,五、SQL中的表连接,74,左连接 用途：将两张数据表拼接为一张，返回的记录以左表为准。 任务一：找出所有有资产的客户，取出他们的客户编号、资产和负债。,五

28、、SQL中的表连接,75,SELECT a.Id ,a.Asset ,coalesce ( b.Loan,0 ) as Loan FROM Asset as a LEFT JOIN Loan as b ON a.Id = b.Id,左连接 任务一,定义基表,五、SQL中的表连接,76,左连接 任务二：找出所有持有产品数量大于等于3的客户，取出他们的客户编号、持有产品数量、资产和负债,按持有产品数量从大到小排序。,五、SQL中的表连接,77,SELECT a.Id ,a.Product ,coalesce( b.Asset,0 ) as Asset ,coalesce( c.Loan,0 ) a

29、s Loan FROM Product as a LEFT JOIN Asset as b ON a.Id = b.Id LEFT JOIN Loan as c ON a.Id = c.Id WHERE duct = 3 ORDER BY a.Product DESC;,左连接 任务二,定义基表,连续使用两次左连接,五、SQL中的表连接,78,外连接 用途：将两张数据表拼接为一张，在任何一张表中出现过的记录都将返回。 任务：找出所有拥有资产或有负债的客户（二者有其一即可），取出他们的客户编号、资产和负债。,五、SQL中的表连接,79,外连接,SELECT coalesce ( a.I

30、d,b.Id ) as Id ,coalesce ( a.Asset,0 ) as Asset ,coalesce ( b.Loan,0 ) as Loan FROM Asset as a FULL JOIN Loan as b ON a.Id = b.Id,五、SQL中的表连接,80,合并 用途：将两张数据表上下合并为一张（不需要连接条件），要求两张表的列数量和对应列的数据类型必须一致。其中union将去掉合并后重复的行，union all则会保留合并后的重复行。 任务：找出所有在Asset表或Loan表中出现过的客户，取出他们的客户编号。,+,五、SQL中的表连接,81,UNION 和 UNION ALL的区别：,SELECT a.Id FROM Asset as a UNION SELECT b.Id FROM Loan as b,SELECT a.Id FROM Asse