TSQL查询进阶数据集之间运算.docx

TSQL查询进阶数据集之间运算.docx

《TSQL查询进阶数据集之间运算.docx》由会员分享，可在线阅读，更多相关《TSQL查询进阶数据集之间运算.docx（86页珍藏版）》请在冰点文库上搜索。

TSQL查询进阶数据集之间运算

简介

     数据库范式在数据库设计中的地位一直很暧昧，教科书中对于数据库范式倒是都给出了学术性的定义，但实际应用中范式的应用却不甚乐观，这篇文章会用简单的语言和一个简单的数据库DEMO将一个不符合范式的数据库一步步从第一范式实现到第四范式。

范式的目标

     应用数据库范式可以带来许多好处，但是最重要的好处归结为三点：

     1.减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）

     2.消除异常（插入异常，更新异常，删除异常）

     3.让数据组织的更加和谐…

      但剑是双刃的，应用数据库范式同样也会带来弊端，这会在文章后面说到。

什么是范式

     简单的说，范式是为了消除重复数据减少冗余数据，从而让数据库内的数据更好的组织，让磁盘空间得到更有效利用的一种标准化标准，满足高等级的范式的先决条件是满足低等级范式。

（比如满足2nf一定满足1nf）

DEMO

     让我们先从一个未经范式化的表看起,表如下：

先对表做一个简单说明，employeeId是员工id,departmentName是部门名称，job代表岗位，jobDescription是岗位说明，skill是员工技能，departmentDescription是部门说明，address是员工住址

对表进行第一范式（1NF）

   如果一个关系模式R的所有属性都是不可分的基本数据项，则R∈1NF。

   简单的说,第一范式就是每一个属性都不可再分。

不符合第一范式则不能称为关系数据库。

对于上表，不难看出Address是可以再分的，比如”北京市XX路XX小区XX号”，着显然不符合第一范式，对其应用第一范式则需要将此属性分解到另一个表,如下:

对表进行第二范式（2NF）

若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于R的码，则R∈2NF

简单的说，是表中的属性必须完全依赖于全部主键，而不是部分主键.所以只有一个主键的表如果符合第一范式，那一定是第二范式。

这样做的目的是进一步减少插入异常和更新异常。

在上表中，departmentDescription是由主键DepartmentName所决定，但却不是由主键EmployeeID决定，所以departmentDescription只依赖于两个主键中的一个，故要departmentDescription对主键是部分依赖，对其应用第二范式如下表：

对表进行第三范式（3NF）

关系模式RY）,使得X→Y，Y→Z，成立，则称R中若不存在这样的码X、属性组Y及非主属性Z（Z∈3NF。

简单的说，第三范式是为了消除数据库中关键字之间的依赖关系，在上面经过第二范式化的表中，可以看出jobDescription（岗位职责）是由job（岗位）所决定，则jobDescription依赖于job,可以看出这不符合第三范式，对表进行第三范式后的关系图为：

上表中，已经不存在数据库属性互相依赖的问题，所以符合第三范式

对表进行BC范式（BCNF）

设关系模式R∈1NF，如果对于R的每个函数依赖X→Y，若Y不属于X，则X必含有候选码，那么R∈BCNF。

简单的说，bc范式是在第三范式的基础上的一种特殊情况，既每个表中只有一个候选键（在一个数据库中每行的值都不相同，则可称为候选键），在上面第三范式的noNf表中可以看出，每一个员工的email都是唯一的（难道两个人用同一个email?

?

）则，此表不符合bc范式，对其进行bc范式化后的关系图为:

对表进行第四范式（4NF）

关系模式R∈1NF，如果对于R的每个非平凡多值依赖X→→Y（YX），X都含有候选码，则R∈4NF。

简单的说，第四范式是消除表中的多值依赖，也就是说可以减少维护数据一致性的工作。

对于上面bc范式化的表中，对于员工的skill，两个可能的值是”C#,sql,javascript”和“C#，UML,Ruby”,可以看出，这个数据库属性存在多个值，这就可能造成数据库内容不一致的问题，比如第一个值写的是”C#”,而第二个值写的是”C#.net”,解决办法是将多值属性放入一个新表，则第四范式化后的关系图如下：

而对于skill表则可能的值为:

总结

    上面对于数据库范式进行分解的过程中不难看出，应用的范式登记越高，则表越多。

表多会带来很多问题：

1查询时要连接多个表，增加了查询的复杂度

2查询时需要连接多个表，降低了数据库查询性能

而现在的情况，磁盘空间成本基本可以忽略不计，所以数据冗余所造成的问题也并不是应用数据库范式的理由。

因此，并不是应用的范式越高越好，要看实际情况而定。

第三范式已经很大程度上减少了数据冗余，并且减少了造成插入异常，更新异常，和删除异常了。

我个人观点认为，大多数情况应用到第三范式已经足够，在一定情况下第二范式也是可以的。

由于本人对数据库研究还处于初级阶段，所以上述如有不当之处，还望高手不吝指教…

SQL查询入门（上篇）

引言

     SQL语言是一门简单易学却又功能强大的语言，它能让你快速上手并写出比较复杂的查询语句。

但对于大多数开发者来说，使用SQL查询数据库并没有一个抽象的过程和一个合理的步骤，这很可能会使在写一些特定的SQL查询语句来解决特定问题时被”卡”住，本系列文章主要讲述SQL查询时一些基本的理论，以及写查询语句的抽象思路。

SQL查询简介

     SQL语言起源于1970年E.J.Codd发表的关系数据库理论，所以SQL是为关系数据库服务的。

而对于SQL查询，是指从数据库中取得数据的子集,这句话貌似听着有些晦涩是吧，下面通过几张图片简单说明一下：

     假如一个数据库中只有一个表，再假如所有数据如下图（取自AdventureWork示例数据库）:

    而对于子集的概念,look下图:

    最后,子集如下：

   其实，SQL中无论多复杂的查询，都可以抽象成如上面的过程.

精确查询的前置条件

   对于正确取得所需要的数据子集.除了需要思路正确并将思路正确转变为对应SQL查询语句之外。

还有很重要的一点是需要数据库有着良好的设计.这里的良好设计我所指的是数据库的设计符合业务逻辑并至少实现第三范式,对于实现第三范式，这只是我个人观点，对于范式的简单介绍，请看我的博客:

数据库范式那些事.如果数据库设计很糟糕，存在很多冗余，数据库中信息存在大量异常，则即使SQL写的正确，也无法取得精确的结果。

两种方式，同一种结果

   在SQL中，取得相同的数据子集可以用不同的思路或不同的SQL语句，因为SQL源于关系数据库理论，而关系数据库理论又源于数学，思考如何构建查询语句时，都可以抽象为两种方法:

   1.关系代数法

     关系代数法的思路是对数据库进行分步操作，最后取得想要的结果.

     比如如下语句:

SelectName,Department,Age

FromEmployee

whereAge>20

     关系代数的思路描述上面语句为:

对表Employee表进行投影（选择列）操作，然后对结果进行筛选，只取得年龄大于20的结果.

     2.关系演算法

      相比较关系代数法而言，关系演算法更多关注的是取得数据所满足的条件.上面SQL可以用关系演算法被描述为：

我想得到所有年龄大于20的员工的姓名，部门和年龄。

    为什么需要两种方法

   对于简单的查询语句来说，上面两种方法都不需要.用脚就可以想出来了。

问题在于很多查询语句都会非常复杂。

对于关系演算法来说更多的是关注的是所取出信息所满足的条件，而对于关系代数法来说，更多关注的是如何取出特定的信息.简单的说，关系演算法表示的是”what”,而关系代数法表达的是”how”.SQL语句中所透漏的思路，有些时候是关系代数法，有些时候是关系演算法，还有些是两种思路的混合.

    对于某些查询情况，关系代数法可能会更简单，而对于另外一些情况，关系演算法则会显得更直接.还有一些情况.我们需要混合两种思路。

所以这两种思维方式在写SQL查询时都是必须的.

单表查询

     单表查询是所有查询的中间状态，既是多个表的复杂查询在最终进行这种连接后都能够被抽象成单表查询。

所以先从单表查询开始。

选择列的子集

      根据上面数据子集的说法，选择列是通过在select语句后面添加所要选择的列名实现的：

    比如下面数据库中通过在select后面选择相应的列名实现选择列的子集.

  相应sql语句如下：

SELECT[Name]

[GroupName]

FROM[AdventureWorks].[HumanResources].[Department]

选择行的子集

     选择行的子集是在Sql语句的where子句后面加上相应的限制条件，当where子句后面的表达式为“真”时，也就是满足所谓的“条件”时，相应的行的子集被返回。

     where子句后面的运算符分为两类，分别是比较运算符和逻辑运算符.

    比较运算符是将两个相同类型的数据进行比较，进而返回布尔类型（bool）的运算符，在SQL中，比较运算符一共有六种，分别为等于（=）,小于（<）,大于（>）,小于或等于（<=）,大于或等于（>=）以及不等于（<>）,其中小于或等于和大于或等于可以看成是比较运算符和逻辑运算符的结合体。

     而逻辑运算符是将两个布尔类型进行连接，并返回一个新的布尔类型的运算符，在SQL中，逻辑运算符通常是将比较运算符返回的布尔类型相连接以最终确定where子句后面满足条件的真假。

逻辑运算符一种有三种，与（AND），或（OR），非（NOT）.

  比如上面，我想选择第二条和第六条，为了说明比较运算符和逻辑运算符，可以使用如下Sql语句：

SELECT[Name]

[GroupName]

FROM[AdventureWorks].[HumanResources].[Department]

WHEREDepartmentID>1andDepartmentID<3orDepartmentID>5andDepartmentID<7

 由此我们可以看出，这几种运算符是有优先级的，优先级由大到小排列是比较运算符>于（And）>非（Or）

  当然，运算符也可以通过小括号来改变优先级，对于上面那个表

  对于不加括号时：

SELECT*

FROM[AdventureWorks].[HumanResources].[Department]

WHEREDepartmentID>=1andDepartmentID<=3andDepartmentID>=5orDepartmentID<=7

 加了括号改变运算顺序后：

SELECT*

FROM[AdventureWorks].[HumanResources].[Department]

WHEREDepartmentID>=1andDepartmentID<=3and（DepartmentID>=5orDepartmentID<=7）

很特别的NULL

   假如在一个用户注册的表中，一些选填信息并不需要用户必须填写，则在数据库中保存为null,这些null值在利用上面where子句后的运算符时，有可能造成数据丢失，比如一个选填信息是性别（Gender）,假设下面两条条件子句:

whereGender="M"

whereNOT（Gender="M"）

  由于null值的存在，这两条语句返回的数据行加起来并不是整个表中的所有数据。

所以，当将null值考虑在内时，where后面的条件子句拥有可能的值从真和假，增加为真，假，以及未知（null）。

这些是我们在现实世界中想一些问题的时候可能的答案--真的，假的，我不知道。

   所以我们如何在这种情况下不丢失数据呢，对于上面的例子来说，如何才能让整个表的数据不被丢失呢，这里必须将除了“真”，“假”以外的“未知”这个选项包含在内，SQL提供了ISNULL来表明未知这个选项：

whereGenderISNULL

  将上面语句加入进去，则不会再丢失数据。

排序结果

     上面的那些方法都是关于取出数据，而下面是关于将取出的子集进行排序。

SQL通过Orderby子句来进行排序，Orderby子句是Sql查询语句的最后一个子句，也就是说Orderby子句之后不能再加任何的子句了。

     OrderBy子句分为升序（ASC）和降序（DESC），如果不指定升序或者降序，则默认为升序（由小到大），而Orderby是根据排序依据的数据类型决定，分别为3种数据类型可以进行排序：

1.字符

2.数字

3.时间日期

    字符按照字母表进行排序，数字根据数字大小排序，时间日期根据时间的先后进行排序。

其它一些有关的

 视图

      视图可以看作是一个保存的虚拟表，也可以简单看做是保存的一个查询语句。

视图的好处是视图可以根据视图所查询表的内容的改变而改变，打个比方来理解这句话是：

    使用视图的优点是可以对查询进行加密以及便于管理，据说还可以优化性能（我不认可这点）.

防止重复

      有时候我们对于取出的数据子集不想重复，比如你想知道一些特定的员工一共属于几个部门

SELECT[EmployeeID]

[DepartmentID]

FROM[AdventureWorks].[HumanResources].[EmployeeDepartmentHistory]

  这样的结果是没有意义的，SQL提供了Distinct关键字来实现这点：

SELECTdistinctDepartmentID

FROM[AdventureWorks].[HumanResources].[EmployeeDepartmentHistory]

聚合函数

    所谓聚合函数，是为了一些特定目的，将同一列多个值聚合为一个，比如我想知道一群人中最大年龄是多少可以利用MAX（Age）,比如我想知道一个班级平均测验成绩是多少可以用AVG（Result）……

总结

    文章简单概述了SQL查询的原理以及简单的单表查询，这些都是数据库查询的基础概念，对于进行复杂查询来说，弄明白这些概念是必不可少的。

SQL查询入门（中篇）

引言

   在前篇文章中（SQL查询入门（上篇）,我对数据库查询的基本概念以及单表查询做了详细的解释，本篇文章中，主要说明SQL中的各种连接以及使用范围，以及更进一步的解释关系代数法和关系演算法对在同一条查询的不同思路。

多表连接简介

    在关系数据库中，一个查询往往会涉及多个表，因为很少有数据库只有一个表，而如果大多查询只涉及到一个表的，那么那个表也往往低于第三范式，存在大量冗余和异常。

    因此，连接（Join）就是一种把多个表连接成一个表的重要手段.

    比如简单两个表连接学生表（Student）和班级（Class）表，如图:

    进行连接后如图：

笛卡尔积

     笛卡尔积在SQL中的实现方式既是交叉连接（CrossJoin）。

所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合,上图中两个表连接即为笛卡尔积（交叉连接）

     在实际应用中，笛卡尔积本身大多没有什么实际用处，只有在两个表连接时加上限制条件，才会有实际意义，下面看内连接

内连接

     如果分步骤理解的话，内连接可以看做先对两个表进行了交叉连接后，再通过加上限制条件（SQL中通过关键字on）剔除不符合条件的行的子集,得到的结果就是内连接了.上面的图中，如果我加上限制条件

     对于开篇中的两个表，假使查询语句如下：

SELECT*

FROM[Class]c

innerjoin

[Student]s

onc.ClassID=s.StudentClassID

   可以将上面查询语句进行分部理解，首先先将Class表和Student表进行交叉连接,生成如下表:

    然后通过on后面的限制条件，只选择那些StudentClassID和ClassID相等的列（上图中划了绿色的部分）,最终，得到选择后的表的子集

    当然，内连接on后面的限制条件不仅仅是等号，还可以使用比较运算符,包括了>（大于）、>=（大于或等于）、<=（小于或等于）、<（小于）、!

>（不大于）、!

<（不小于）和<>（不等于）。

当然，限制条件所涉及的两个列的数据类型必须匹配.

    对于上面的查询语句，如果将on后面限制条件由等于改为大于:

SELECT*

FROM[Class]c

innerjoin

[Student]s

onc.ClassID>s.StudentClassID

    则结果从第一步的笛卡尔积中筛选出那些ClassID大于StudentClassID的子集:

   虽然上面连接后的表并没有什么实际意义，但这里仅仅作为DEMO使用:

-）

      关系演算

      上面笛卡尔积的概念是关系代数中的概念，而我在前一篇文章中提到还有关系演算的查询方法.上面的关系代数是分布理解的，上面的语句推导过程是这样的：

“对表Student和Class进行内连接，匹配所有ClassID和StudentClassID相等行，选择所有的列”

     而关系演算法，更多关注的是我想要什么,比如说上面同样查询，用关系演算法思考的方式是“给我找到所有学生的信息，包括他们的班级信息，班级ID，学生ID，学生姓名”

    用关系演算法的SQL查询语句如下：

SELECT*

FROM[Class]c

[Student]s

wherec.ClassID=s.StudentClassID

    当然，查询后返回的结果是不会变的：

外连接

    假设还是上面两个表，学生和班级.我在学生中添加一个名为Eric的学生，但出于某种原因忘了填写它的班级ID：

    当我想执行这样一条查询：

给我取得所有学生的姓名和他们所属的班级:

SELECTs.StudentName,c.ClassName

FROM[fordemo].[dbo].[Student]s

innerjoin

[fordemo].[dbo].[Class]c

on

s.StudentClassID=c.ClassID

  结果如下图:

  可以看到，这个查询“丢失”了Eric..

  这时就需要用到外连接，外连接可以使连接表的一方，或者双方不必遵守on后面的连接限制条件.这里把上面的查询语句中的innerjoin改为leftouterjoin:

SELECTs.StudentName,c.ClassName

FROM[fordemo].[dbo].[Student]s

leftouterjoin

[fordemo].[dbo].[Class]c

on

s.StudentClassID=c.ClassID

 结果如下：

   Eric又重新出现.

     右外连接

     右外连接和左外连接的概念是相同的，只是顺序不同，对于上面查询语句，也可以改成:

SELECTs.StudentName,c.ClassName

FROM[fordemo].[dbo].[Class]c

rightouterjoin

[fordemo].[dbo].[Student]s

on

s.StudentClassID=c.ClassID

     效果和上面使用了左外连接的效果是一样的.

       全外连接

      全外连接是将左边和右边表每行都至少输出一次,用关键字”fullouterjoin”进行连接，可以看作是左外连接和右外连接的结合.

自连接

      谈到自连接，让我们首先从一个表和一个问题开始：

     上面员工表（Employee），因为经理也是员工的一种，所以将两种人放入一个表中，MangerID字段表示的是当前员工的直系经理的员工id.

     现在，我的问题是，如何查找CareySon的经理的姓名?

      可以看出，虽然数据存储在单张表中，但除了嵌套查询（这个会在后续文章中讲到），只有自连接可以做到.正确自连接语句如下:

SELECTm.EmployeeName

FROM[fordemo].[dbo].[Employee]e

innerjoin[fordemo].[dbo].[Employee]m

one.ManagerID=m.idande.EmployeeName='Careyson'

    在详细解释自连接的概念之前，请再看一个更能说明自连接应用之处的例子:

     这个表是一个出席会议记录的表，每一行表示出席会议的记录（这里，由于表简单，我就不用EmployeeID和MeetingID来表示了，用名称对于理解表更容易些）

      好了，现在我的问题是:

找出既参加“谈论项目进度”会议，又参加”讨论职业发展”会议的员工

     乍一看上去很让人迷惑是吧,也许你看到这一句脑中第一印象会是:

SELECTEmployeeName

FROM[fordemo].[dbo].[MeettingRecord]m

whereMeetingName='¨¬?

?

?

?

?

?

?

?

?

?

¨¨'andmeetingName='¨¬?

?

?

?

¡ã¨°¦Ì¡¤¡é?

1'

   （我用的代码高亮插件不支持中文，所以上面where子句后面第一个字符串是’谈论项目进度’，第二个是’讨论职业发展’）

    恩，恭喜你，答错了…如果这样写将会什么数据也得不到.正确的写法是使用自连接！

    自连接的是一种特殊的连接，是对物理上相同但逻辑上不相同的表进行连接的方式。

我看到XX百科上说自连接是一种特殊的内连接,但这是错误的,因为两个相同表之间不光可以内连接，还可以外连接，交叉连接…在进行自连接时，必须为其中至少一个表指定别名以对这两个表进行区分！

    回到上面的例子，使用自连接，则正确的写法为:

SELECTm.EmployeeName

FROM[fordemo].[dbo].[MeettingRecord]m,

[fo