吉林大学《互联网数据库程序设计》4.6-4.7数据维护与查询及统计分析

吉林大学《互联网数据库程序设计》4.6-4.7数据维护与查询及统计分析。逻辑表达式，修改数据记录，删除数据记录，数据基本查询，数据排序分析，数据分组统计分析，多个数据源链接

4.6-4.7数据维护与查询及统计分析

• 4.7.4多个数据源连接
  • 数据源连接
    • 在数据源内每找到一组满足条件的记录，就连接成一条被操作的记录
  • 两种连接方法
    • Select …From <数据源名1> … [，<数据源名m>] Where <连接条件> ……
    • Select …From <数据源名1> … [<连接类型> <数据源名n>][On <连接条件>] ……
  • 常用连接类型
    • 内连接([ Inner ] Join)
      • 连接符合条件的数据记录，与Where 连接条件功能相同，但速度更快。
    • 自然连接(Natural Join)
      • 同名字段等值、无重复列的内连接。*输出数据源中全部无重复列，多数据源的同名字段，可省数据源名和圆点。不需要写 On <条件>
    • 左连接(Left Join)
      • 符合条件的记录，再加上左数据源中不符合条件的记录。
    • 右连接(Right Join)
      • 左右数据源互换位置，左右连接功能相同。
• 4.6.1逻辑值表达式
  • 逻辑值表达式
    • 值真 (True、非0)或假(False、0)的表达式，输入逻辑数据时，一般输入0表示假，1表示真
  • 逻辑值表达式构成
    • 常数(0为假，非0为真)、变量(字段)和逻辑值函数、比较运算符(关系表达式)、逻辑运算符(逻辑表达式)、谓词运算符
  • 比较运算
    • 1 比较运算符
      • =、<、<=(≤)、>和>=(≥)，<>、!=(≠)。数值(逻辑) 遵循数学规定大小；日期(时间) 在前的小。运算项含Null，结果为Null
    • 2 Null 等值运算符<=>
      • 与=区别仅在于含Null运算项。<=>运算项之一为Null，结果为0；两项均为Null，结果为1
    • 3 字符串比较规则
      • 字符规则
        • 0<1< … <9<A<B … <Z, 字母不区分大小写；字母小于汉字
      • 总体规则
        • 去掉尾空格自左向右按对应位比较。若某位不等, 则含小字符的串小; 若比较到较短串末尾, 则短串较小; 若两个串相同, 则结果相等
    • 4 不同类型比较
      • 若含数值(逻辑), 则转另一项为数值比较；字符串与日期(时间)比较，转成字符串比较
      • 数值比较
      • 字符串比较
  • 逻辑运算
    • 取反(非)
      • { Not|! } <逻辑值表达式>，结果与逻辑值表达式的值相反(0、1互变)
    • 与(并且)运算
      • <逻辑式1> { And|&& } <逻辑式2>。两个式的值均为真(非0)，则结果为1，否则，结果为0
    • 或运算
      • <逻辑式1> { Or 或|| } <逻辑式2>。两个逻辑式的值均为假(0)，则结果为0，否则，结果为1
  • 谓词运算
    • 区间判断
      • X Between Y And Z，等效于X>=Y And X<=Z
    • Null、真或假
      • X Is [Not] { Null|True|False }，表达式值为Null、True(真)或False(假)，结果为1或0
    • 成员判断
      • X [Not] In(X1[，X2 … ，Xn])，若存在Xi使X=Xi，则结果为1，否则结果为0。等效：X=X1[ Or X=X2 … Or X=Xn]
    • 匹配运算
      • <串1> [Not] Like <串2>：若两个串匹配，则结果为1，否则结果为0
    • 选择匹配运算
      • <串1> [Not] {RLike| REGEXP } <串2>。串2中子串间用|分隔：<子串1>[|<子串2>……|<子串n>]
  • 分支运算（非重点）
    • 1 多分支取值
      • Case X When Y1 Then Z1 … When Yn Then Zn [Else Zn+1] End，依次找Yi=X，若找到则结果为Zi。若没有，有Else Zn+1时，结果为Zn+1；没Else Zn+1时，结果为Null
    • 2 多条件取值
      • Case When <条件1> Then Z1 … When <条件n> Then Zn [Else Zn+1] End。从条件1到n判断，若条件i成立，则结果为Zi。若所有条件都不成立，有Else Zn+1时，结果为Zn+1；没Else Zn+1时，结果为Null
  • 相关函数
    • IsNull(X)
      • Null函数。如果X值为Null，则返回1，否则返回0
    • If(X，Y，Z)
      • 分支函数。如果X值为真则返回Y值，否则返回Z值
    • IfNull(X，Y)
      • Null分支函数。如果X值是Null，则返回Y值，否则返回X值
• 4.6.2修改数据记录
  • 语句格式
    • Update <表名称表> Set <字段名1> =<表达式1> [……,<字段名n> =<表达式n>] [ Where <条件表达式> ]
    • 表名称表
      • 被修改记录所在的表，通常1个表名
    • 表达式
      • 修改后对应字段的值，对允许Null的字段，可以为Null
    • Where <条件表达式>
      • 仅修改满足条件记录相关字段值。若省略，则修改全部记录相关字段的值
      • Update GWB Set 年薪 = 年薪 * (1 + 10 / 100) /* 年薪调整为原年薪的1.1倍 */;
• 4.6.3删除数据记录
  • 语句格式
    • Delete From <表名> [ Where <条件表达式>]
  • 删除满足条件的记录。若省略，则删除全部记录
• 4.6.4参照完整性对数据维护的影响（非重点）
  • 参考3.7.3
• 4.7.1数据基本查询
  • 1 [<数据源别名>.]*|<表达式1>[[As]<列名1 >]…,<表达式n>[[As] <列名n>]
    • Select [All | Distinct[Row]] [<数据源别名>.]*|<表达式1>[[As]<列名1 >]…,<表达式n>[[As] <列名n>] From <数据源名1>[ [As]<数据源别名1>] …… [，<数据源名m> [ [As]<数据源别名m>]] ……
    • 语句格式
      • All
        • Select All YPRYB.身份证号,姓名 From YPRYB, GWCJB Where YPRYB.身份证号=GWCJB.身份证号 Order By YPRYB.身份证号 /*申报人员
      • Distinct
        • Select Distinct YPRYB.身份证号,姓名 From YPRYB, GWCJB Where YPRYB.身份证号=GWCJB.身份证号 Order By YPRYB.身份证号 /*申报人员*/；
      • 数据源别名
        • 重名字段前，需要加表别名： Select YPRYB.身份证号,姓名 From YPRYB, GWCJB Where YPRYB.身份证号=GWCJB.身份证号 Order By YPRYB.身份证号 /*申报人员*/；
      • *
        • 表示(某)数据源中全部字段： Select 姓名, GWCJB .* From YPRYB, GWCJB Where YPRYB.身份证号=GWCJB.身份证号 Order By YPRYB.身份证号 /*姓名加GWCJB 中全部列*/；
      • 表达式
        • 按表达式提取或运算各列的数据，称含常数、运算符或函数的列为计算列： Select 身份证号,岗位编号,笔试成绩+面试成绩 From GWCJB /* 字段可直接作为表达式 */；
      • 列名
        • 不重新定义列名，表达式即为列名，As可省略 ： Select 身份证号,岗位编号, 笔试成绩+面试成绩 As 总分 From GWCJB；
  • 2 From <数据源名1>[ [As]<数据源别名1>]
    • 数据源名
    • 数据源别名
  • 3 [Where <条件表达式>]
• 4.7.2数据排序分析
  • [Order By <排序关键字1>[ASC|DESC]……
• 4.7.3数据分组统计分析
  • [Group By <分组关键字1>[ASC|DESC]……
    • 关键字值相同记录整理成结果中的一行，多个关 键字实现多级分组。可加排序方式ASC或DESC
      • Select GWB.岗位编号 , 岗位名称 From GWB, GWCJB Where GWB.岗位编号= GWCJB.岗位编号 Group By GWB.岗位编号；
    • 用AVG、Count、Max和Sum可统计各组数据
      • Select GWB.岗位编号 , 岗位名称, Count(*) As 申报, Max(笔试成绩) As 最高分, AVG(笔试成绩) As 平均分 From GWB, GWCJB Where GWB.岗位编号= GWCJB.岗位编号 Group By GWB.岗位编号；
  • [With Rollup]
    • 结果中增加分组合计和总计行
    • Select IfNull(GWB.岗位编号 ,"总计" ) As 编号 , 岗位名称 , Count(*) As 申报 , Max(笔试成绩) As 最高分, AVG(笔试成绩) As 平均分 From GWB, GWCJB Where GWB.岗位编号= GWCJB.岗位编号 Group By GWB.岗位编号 With Rollup ；
  • [Having <条件表达式>]
    • 对结果进一步过滤(筛选)，可含列名和统计函数
    • 对结果进一步过滤(筛选)，可含列名和统计函数。 Select GWB.岗位编号 , 岗位名称 , Count(*) As 申报 , Max(笔试成绩) As 最高分, AVG(笔试成绩) As 平均分 From GWB, GWCJB Where GWB.岗位编号= GWCJB.岗位编号 Group By GWB.岗位编号 Having 平均分>=80 ；
  • 分组结果说明
    • 关键字对统计列、能惟一确定的列有统计含义，其他列只是本组中的一员