本篇博客中的公式支持不完整，请您访问这个链接：http://blog.csdn.net/github_35160620/article/details/52684656

在上一篇博客中，我们重点了解了关于自动控制原理的一些基本概念 以及一些相关的术语，以及能够分析控制系统的基本组成以及相应的工作原理。那么本篇博客我们重点学习的是控制原理的数学模型。

我们在上一篇博客中曾经提到过：在经典控制理论当中有三个理论基石：时域分析法，根轨迹法、频域分析法。而这三种方法只是我们分析系统的一种手段，而不管是采用那一钟方法，我们都离不开系统它的数学模型。

数学模型是什么呢？ 是我们依据系统的工作原理抽象出来的一些数学表达式或者一些图形。

本篇博客中，我们会讲解上面这些数学模型。（微分方程、传递函数、结构图、信号流图）

一 . 微分方程

在时域当中的数学模型，叫做 微分方程。在微分方程这样的数学模型当中，我们要了解一下几个方面：

1 . 微分方程它的建立以及如何化成标准化（什么叫做标准化？所谓标准化是指：我们把微分方程的左右分布安装输出和输入的 各阶导数，以及导数从高到低的次序来排列）。

2 . 此外，微分方程中，除了线性系统之外，还有一些非线性系统的微分方程。（不解释）

3 . 列写这些微分方程的目的是什么呢？我们要通过这些微分方程的解来分析系统的性能。这是我们在时域当中要见到的一种数学模型，这种数学模型（微分方程）它的列写是有一定的技巧的，也就是说我们拿到一些系统以后我们要来分析这些系统它的工作性质，究竟是什么样的系统？是力学系统呢，还是热学系统，还是电学系统。按照这些系统它所依据的物理定律，比如说 ：物理学系统它说依据的是牛顿第二定律，而电学系统主要依据的是基尔霍夫电压或者电流定律，列出来原始平衡方程，对这些原始平衡方程进行化解得到标准形式，并且利用微分方程的求解得到微分方程的解，从这些解当中，我们分析控制系统的性能。

二 . 传递函数

在 复频域 当中的数学模型，叫做 传递函数。这种数学模型在经典控制理论当中是站着举足轻重的作用，我们后面的每一篇博客的学习都离不开这种数学模型。

1 . 那么对于这样的数学模型，我们要了解它的定义，以及它的一些性质。尤其是知道了它的性质，在我们分析系统的响应的时候也会起到简化的作用；还有传递函数在描述系统的时候是不是还有一些局限性，这我们也需要了解。

2 . 还需要了解一些常用的控制元件它的数学模型，也就是它的传递函数。

传递函数我们常见的表现形式有几种，这些表现形式有什么样的特点，我们将重点讲两种表现形式。

（1）. 一个是以零、极点的模型形式来表现的。我们可以把它写作：一个系统的传递函数 等于 所对应的所有的系统的零点它的乘积 比上 所有的极点它的乘积。这种数学模型在我们后面介绍根轨迹方法当中，经常会见到。

$$G(s) = \frac{K\prod{m}^{i=1}(s - z{i})}{\prod{n}^{j=1}(s - p{j})}$$

（2） . 除了零、极点这种数学模型之外，还有一种数学模型是以典型环节的形式来表述的，也就是说 这个系统的数学模型，我们能够把它写做：传递函数 等于 由一些比例环节或者是一阶的微分环节或者是二阶的微分环节 比上 积分环节或者一阶惯性环节或者二阶的震荡环节等等，这样的形式来表示的，这样的数学模型的形式，我们在频域分析法当中经常会见到。

传递函数这种数学模型在考研当中，求系统的传递函数这样的考题，我们都会见到。所以这种数学模型的形式，我们要非常熟练的掌握。

三 . 动态结构图

除此之外，还有一种数学模型，是以图形的形式来表示的，我们把它叫做：动态结构图。那么动态结构图，我们要掌握它的绘制原则。也就是说：给你一个抽象的物理系统以后，你如何从这样的物理系统当中分析它的工作原理，进而建立它的动态结构图。这种动态结构图的建立是有一定的技巧的，这个我们在后面的博客中会介绍到。那么这种动态结构图的绘制， 绘制出来之后还没有完，我们还需要对动态结构图进行等效，得到系统最终的传递函数，从而分析它的性能。

那么在动态结构图中的等效当中，我们会经常见到这样的几种等效原则： 串联、并联、以及反馈，还有最重要的是：引出点和比较点它们该如何移动。这些我们在后面的博客中也会详细介绍 。

（在各个院校的考研试卷中，动态结构图的题即使不是一专门的题来出现，也会在各种题型中出现，因为从系统的动态结构图建立它的传递函数这一过程，是分析系统的基础，所以这种动态结构图的等效我们也需要牢牢的掌握。）

四 . 信号流图

而动态结构图的等效我们并不提倡大家采用动态结构图化简的方式。因为这种动态结构图化简的方式在遇到了：引出点和比较点相互交叉的时候，经常容易出错，那么我们该怎么办呢？ 这在我们下一种数学模型：信号流图的化简 中我们会学习到一钟更简便的方法。

那么信号流图和动态结构图一样都是图形形式的数学模型，那么二者的区别在哪里呢？

信号流图除了可以描述线性系统之外，对于非线性系统，我们一样可以来进行描述。

信号流图的绘制有两种方式：

• 如果一个时域当时的微分方程或者代数方程我们已经知道了，我们就可以从方程当中来绘制它的信号流图。
• 如果动态结构图我们已经知道了，那么我们从动态结构图中，也能绘制信号流图。

其实，信号流图的绘制并不重要，因为我们只要知道了动态结构图，在利用以后会学到的一个公式（Mason增益公式），对于任何系统，它的传递函数都可以很容易的来获得。（Mason增益公式 会在以后的博客中详细的介绍。）

我们刚刚说了：传递函数是控制系统当中非常重要的数学模型，在考试当中会直接或者间接的见到传递函数的求解，这种求解会以什么样的形式出现呢？首先它可能让你去求一个控制系统它的开环或者闭环的传递函数。这在什么情况下呢：给了你动态结构图或者信号流图，那么这就涉及到了动态结构图和信号流图的化简。此外，我们还会见到，比如说：一个系统它是一个多输入的系统，它既有参考输入的作用又有扰动信号的作用，对于这样的系统，分别在不同输入信号的输入下，所对应的传递函数或者是误差传递函数它的求解在考研的各种试题当中也可能见到。

我们重点需要学会的就是这三个问题：

• 传递函数该如何求取
• 动态结构图的化简
• Mason增益公式的使用。

下面我们从重要的知识点开始了解。

五 . 传递函数的定义

刚才我们说过了：在控制系统的数学模型中，传递函数 站着举足轻重的作用，那么什么是传递函数？所谓的传递函数的定义是有这样的前提条件的：

首先：（1）传递函数它针对的是线性的定常系统。那么非线性的系统，或者这个系统是变系数的系统都是不能使用传递函数来表述它的数学模型。

再有：（2）要在零初始条件下。所谓的 零初始条件 是指：在时间 $t=0$ 时刻，输出的各阶导数都应该等于0，并且 $t=0$ 时刻，系统是没有输入的。 这就是零初始条件 的定义。

在满足了这样的两个前提条件的情况下，输出的拉氏变换（$C(s)$）和输入的拉氏变换（$R(s)$）的比值，我们把它定义为系统的传递函数。

$$G(s) = \frac {C(s)}{R(s)}$$

传递函数和其他的各种数学模型之间还是不割裂的，它们之间是可以相互转换的。比如说，如果现在我们已经知道了时域中的数学模型（微分方程）。这种微分方程和传递函数之间是可以相互转换的 。

下面是一个n阶线性定常系统在时域中的数学模型（n阶的微分方程）。

$$a{0} \frac{d^{n}}{dt^{n}}c(t) + a{1} \frac{d^{n-1}}{dt^{n-1}}c(t) + \cdots + a{n-1} \frac{d}{dt}c(t) + a{n}c(t) = b{0}\frac{d^{m}}{dt^{m}}r(t) + b{1}\frac{d^{m-1}}{dt^{m-1}}r(t) + \cdots + b{m-1}\frac{d}{dt}r(t) + b{m}r(t)$$

这样一个这么长的微分方程，我们只需要把里面所对应的 微分算子 $\frac {d}{dt}$ 全部转换为复频域当中的变量s （ $\frac{d}{dt} \rightarrow s$），那么这样一个微分方程，我们就可以将它转换为系统的一个传递函数了。

当我们将微分方程里面每个微分算子 $\frac {d}{dt}$ 使用 $s$ 替代以后，就变成了下面这样的形式。

$$a{0}s^{n}C(s) + a{1} s^{n-1}C(s) + \cdots + a{n-1} sC(s) + a{n}C(s) = b{0}s^{m}R(s) + b{1}s^{m-1}R(s) + \cdots + b{m-1}sR(s) + b{m}R(s)$$

按照传递函数的定义，零初始条件下，输出（C）和输入（R）的拉氏变换的比值叫做 传递函数。我就得到了相应传递函数的标准形式。

所以，传递函数 和 微分方程 之间是存在一一对应的关系。也就是说，我们要是知道了微分方程，我就可以很容易的获得它的传递函数；反过来，如果传递函数已经知道了，我们只要把其的复变量 $s$ 置换为微分算子 $\frac {d}{dt}$ ，我一样可以得到时域当中的微分方程。

这就是我们提到的传递函数的定义。

六 . 传递函数的性质

传递函数这样的数学模型有着它自身的性质，这些性质对于我们分析系统而言也是非常重要的。这些性质包括以下几种：

1 . 由于现实世界当中能量的传递总是有限的，所以微分方程它的输出端的阶次总是会高于输入端的阶次，那么所对应的传递函数它的分子多项式的阶次就一定会低于（最多能过达到等于）分母多项式的最高阶次，并且我们的传递函数描述的是一个线性定常的系统 ，所以所对应的所有系数都是实数 。

2 . 传递函数描述的是系统输入输出之间的关系式，这种关系式它只取决于系统的绝对参数，而和系统的输入信号是什么是没有关系的。也就是说传递函数是系统的一种固有属性，它和外加的激励是没有关系的，当然和系统的初始条件也没有关系，这在传递函数的定义当中我们就可以了解到，传递函数我们是在零初始条件下来定义的。

3 . 传递函数和微分方程之间是可以相互转换的，因此也是一一对应的，这里也不需要多说。

4 . （做题的时候可能会见到） 传递函数的拉氏反变换是系统的单位脉冲响应。 为什么会是这样呢？传递函数我们是这样定义的：$G(s) = \frac {C(s)}{R(s)}$（输出的拉氏变换与输入的拉氏变换在零初始条件下所对应的比值）

假设外加的激励是一个单位脉冲信号，也就是说：当$r(t)$ 它等于 $r(t) = \delta(t)$ 。那么这个时候，脉冲信号（也叫：冲击信号）就等于 $R(s) = 1$，所以系统的输出的拉氏变换也就等于：$C(s) = R(s)\cdot G(s)$ （输入的拉氏变换和系统的传递函数的乘积），那么这个时候对于复频域当中的方程两侧来去拉氏变换，我们可以得到：在时域当中，单位脉冲信号作用下的时域响应就应该等于：我们对传递函数做一个拉氏反变换。（这个性质在很多的题里面会用到。）

5 . 传递函数只是对系统的数学描述，并不反应系统的物理构成。

举例：一个是我们在电路当中经常会见到的一种二阶系统（下图），由于电容和电感的存在，两个惯性元件，所以是一个二阶电路，所以在时域当中，描述这个系统的微分方程将会是一个二阶的常系数的微分方程。

再举例：下图是我们经常在力学系统中见到的：一个物体现在在弹簧和阻尼器的共同作用下处于平衡状态，现在加一个外力，原来的平衡被打破，由于这个力学系统当中出现了两个元件（我们认为这个弹簧也好，阻尼器也好，它都是在线性区工作，而且我们认为这个物体可以作为一个集中参数的一个质点来出来），那么这种情况下，描述这个力学系统的数学模型也是一个二阶的常系数的微分方程。

这样两个系统它们的物理形式是截然不同的，所述的领域也不同（一个是 电学中的二阶电路，一个是力学系统中的经典模型），虽然它们的外在表现形式不同，但是由于描述它们二者的数学模型都是一个二阶常系数的微分方程，所以只要这两个系统当中所对应的元件的参数配置恰当，我们完全可以在实验室中用一个二阶电路来分析一个类似的力学系统它的工作性质。

$$a{0}\frac {d^{2}c(t)}{dt^{2}} + a{1}\frac {dc(t)}{dt} + a_{2}c(t) $$

因此我们认为：传递函数只是对系统的一种抽象的数学描述，它并不反应系统的组成，不同形式的系统它可能具有相同的传递函数。

七 . 那么我们如何来建立系统的数学模型呢？

我们可以采用下面这些方法：

方法1 . 求系统的传递函数，我们可以先分析系统的工作原理，利用它的工作原理列出它的动态结构图，然后利用动态结构图的化简或者是直接在动态结构图当中利用Mason公式，我们可以求得系统的传递函数。

方法2 . 除此之外，如果现在我们得到的数学模型是时域当中的微分方程，只要对这样的微分方程消去中间变量，然后得到一个仅仅与输入和输出有关的这样一个微分方程，我们只要把微分算子使用s来取带，一样可以得到系统的传递函数。

方法3 . 当然，如果系统的数学模型得到的是信号流图，我们可以在信号流图上面直接利用 Mason增益公式 来求解系统的传递函数。

八 . 对于控制类的考生来说最为常见的的控制系统是这样几个：

无源网络（比如：RLC电路）、有源网络（比如：由理想运放搭建而成的模拟电路）、简单的电气控制系统（比如：简单的电力拖动、一些电力控制系统）等。

由于微分方程与传递函数之间存在一一对应的关系，因此我们可以在拿到系统之后，首先先分析系统的工作原理。比如说无源网络，我们可以使用基尔霍夫电压或者电流定律来列写电压和电流之间的微分方程，然后消去中间变量得到系统最终的微分方程；再比如：有源网络。对于有源网络，我们可以使用理想运放的 续断和虚段的性质来列写出有源网络输入和输出之间的关系；再比如：运动控制系统，我们可以利用运动控制系统当中的学到的一些运动的平衡方程来列写它的微分方程。微分方程写出来了，传递函数我就也知道了。

那么如果是有源网络或者无源网络，得到它们的传递函数真的就这么麻烦吗？其实没有这么麻烦。电路不管是有源的还是无源的，我们可以直接利用电路当中复阻抗法来列写系统的传递函数，我们来举个例子：

下面这个图是一个有源网络：输入端是一个电阻元件，输出端是一个电容元件，现在我们来分析它的工作。我们可以这样做：

直接在电路中将R和C使用复阻抗来表示：电阻对应的复阻抗仍然是R；电容对应的复阻抗是1/CS。

现在想要知道输入输出之间的关系，我们就可以直接得到：（利用理想电路续断和虚段的原理）流出A点的电流等于流入A点的电流。

同时，A点和B点是等电位的，它们的电位都等于0，在这种情况下：

输出和输入的拉氏变换的比值就等于：

很明显，这样的一个电路完成的功能就是一个：积分器。

所以如果是电网络，我们可以直接使用复阻抗法来求电路的传递函数。

九 . 动态结构图 和 信号流图 绘制

常见的数学模型有两种，分别是：动态结构图 和 信号流图。这两种形式的数学模型是模式系统各个元部件之间的信号传递关系的图型。

这两种图型的绘制是有一定的技巧。

（1）动态结构图的绘制：

我们可以分两步走：

第一步 ：化整为零。

在考虑负载效应的情况下，分别列出系统中各元部件的时域方程或复频域方程（代数方程的时域形式与复频域形式相同，微分方程则必须写成复频域形式）

比如：这样的一个二阶的RC电路，如果我们要想绘制它的动态结构图，我们可以将电路划分为两个部分：也就是相当于两个一阶的RC电路。

需要注意：后一阶的RC电路是前一阶RC电路的负载，所以我们需要在考虑负载效应的情况下分别列写系统当中每一个部分所对应的时域方程，或者是复频域方程。（这就是第一步）

第二步：积零为整。

就是说在我们把各个元部件、各个部分所对应的方程列写出来以后，我们按照信号流动的单向性，用信号线把两个部分或诸多个部分之间依次连接起来。

（2）信号流图的绘制

因为很少会让你去绘制信号流图，所以这一部分我们不讲。

十 . 动态结构图的变换与化简

对于动态结构图，它的基本连接方式有三种：串联、并联 和 反馈 。它们所对应的化简是比较简单的。

动态结构图的变换和化简只是一种手段而已：通过动态结构图变换，使系统当中只出现三种基本形式（下面会讲）以后，我们就可以直接来求解系统的传递函数了。

当然，在结构图的变换和化简当中，我们只能减少（或者增加）一些中间变量，但各变量之间的数学关系不能改变。

最常见的变化方式：

1 . 比较点前移

比如说：

在比较点前移之前，系统的输出等于：（上上图）

$$X{3}(s) = X{1}(s)\cdot G(s) \pm X_{2}(s)$$

而在做了比较点前移的变换之后，系统的输出等于：

$$X{3}(s) = [X{1}(s)\pm X{2}(s)\cdot \frac {1}{G(s)}] \cdot G(s)$$ $$ = X{1}(s)\cdot G(s) \pm X_{2}(s)$$

也就是说，在我们做出等效变换之后，系统的输出是没有发生过改变的。

2 . 比较点后移

比较点前移也好，后移也好，这些等效变换，我们要掌握的一个原则是：在变换前后系统的输出没有发生改变。

比较点的前移和后移，以至于引出点的前移和后移，这个都没有问题。

但是在做动态结构图化简的题型中要特意注意一个问题：如果遇到了比较点和引出点相互交叉的情况，那么在这种情况下，不适于采用化简的方式对动态结构图做简单。那么我们可以怎么做呢？

我建议大家熟练的掌握 Mason公式 ，Mason公式可以直接从动态结构图或者是信号流图当中求出来系统的传递函数。这样情况我们只需要找清楚（从输入节点到输出节点的）前向通道，找清楚 独立回路，这个时候牢记Mason公式，将会使我们的解题得到极大的简化。

（现在先不讲 Mason公式）

十一 . 考研要点

1 . 如何建立控制系统的微分方程（有源网络和无源网络如何列写它的微分方程）

2 . 传递函数的概念、性质以及表示形式（表示形式有两种：零、极点的表示形式（跟轨迹增益）； 典型环节相互串联的形式（系统增益））

3 . 动态结构图的等效变换

4 . 求复杂系统的传递函数

在下一讲中，我们针对本篇博客的重要知识讲讲一些例题。通过这些典型的例题，对知识点进行巩固。

完

一 . 自动控制系统的组成

自动控制装置：自动控制装置的组成当中涉及到了这样一些东西：

需要有（1）被控对象，那么这些被控对象需要有谁来控制呢？一定要有控制器，这些控制器，我们也把它叫做（2）自动控制装置。所以一个系统要叫做自动控制系统，它一定要包含 自动控制器 和 被控对象。

常见的被控对象 有：电机、锅炉 等。

而常见的自动控制系统的基本方式有三种

二 . 自动控制系统的基本控制方式 — 三种

1 . 最简单的：开环控制，所谓的开环控制，就是只有输入很输出的一个顺向作用。可能在理想的状态下，输入加入以后，输出会出现一个期望的结果。可是在实际工作当中，一定会有扰动的存在，因此，开环控制在有了扰动以后，它没有办法对扰动产生抑制。所以这种控制方式虽然结构简单，但是控制的精度是比较低的。

2 . 为了提高控制精度，我们考虑在开环控制的基础上增加了检查装置，这样的控制系统称为：闭环控制。把我们输出端的实际输出量引回到输入端，这样的话，在系统中，即有输入对输出的一个顺向引向，也有输出对输入的一个反作用，而且由于 检测装置 的引入，当系统的前向通道当中出现了扰动，利用闭环自身的作用，我们就可以有效的抑制这种扰动。因此，闭环控制方式也是我们控制当中最常用的控制方式。这种闭环控制虽然结构比开环复杂了，但是它能有效的抑制扰动，因此它是我们最常见的控制了。

3 . 复合控制。还有一种情况，闭环控制是：当输出偏离期望值的时候才介入的。可是如果现在我们可以提前就可以预知系统当中出现了误差，而不是当误差出现了以后，我们借助于闭环来补偿这种误差，那么这个时候，对于提前就可以预知的误差，我们可以考虑使用前馈控制，来进行补偿。那么如果在一个系统当中，即有前馈又有反馈，那么这个时候，我们管这种控制叫做 复合控制。

这些就是我们自动控制系统中常见的控制方式。

三 . 自动控制系统的分类

我们常见的自动控制系统，可以区分为几种类型。

• 比如说，按照上面的控制方式分，可以分为：

  • 开环
  • 闭环
  • 复合

• 如果按照元件来分：

  • 机械
  • 电气
  • 机电
  • 液压
  • 气动
  • 生物系统等

• 如果按照系统的功能来 分，就是说：如果我们的

  • 被控量是温度，就是温控
  • 是压力，就是压控…

• 此外，按照系统的性能，描述它的数学模型的特点不一样，我们又可以把系统分为：（在我们经典控制理论分析当中，针对的对象大部分都是：线性的、连续的、定常的系统。当然，我们在后面也会介绍线性的离散，还会讨论一下非线性的系统。）

  • 线性
  • 非线性
  • 连续
  • 离散
  • 定常
  • 时变

• 当然还有，如果我们按照参考量的变化规律来分：

  • 有恒值控制，这是我们最常见的，比如说我们的控制对象时电机，那么我们希望电机的转速维持在什么样的值上面，那么这种控制叫做： 恒值控制。
  • 此外还有一种控制叫做：随动控制，所谓随动控制要求输出必须快速并且准确的跟踪输入，也就是说，输入要是变了，我要求输出要以很快的速度来跟随输入的变化，这叫随动控制。
  • 再有一类，再我们熟客机床，我们现在 希望被控对象能够安装提前设计好的一段程序来运行，这种控制叫做：程序控制。

这就是自动控制系统的分类。除了分类，我们还对自动控制做了几个要求：

四 . 自动控制系统的基本要求 — 三个方面

三个方面：稳、快、准。

其中 稳 包括两个 恒量，其中包括：

• 它是否稳定。（稳不稳定是正常工作的前提条件。）
• 除此之外，在运动控制当中，它的震荡剧不剧烈，它的运动相对平稳性好不好。（我们希望一个好的系统，它的震荡越小越好，震荡的次数越少越好，超调量越小越好。）

稳 主要是靠稳定器，平稳器。

快 其实是说 ： 从 一种运动过程 转移到另一种运动过程，它的持续时间我们希望是越短越好。快 主要是靠一些时间指标来衡量，比如上升时间、调节时间、分时时间等等。

准 就是 稳态误差， 我们希望越小越好。指理想情况下，当过度过程结束后，被控量达到的稳定值与期望值之间的差值，指的就是稳态误差。

上面这些就是自动控制原理的一些基本概念。

五 . 自动控制系统框图讲解

例子： 我们来分析一下这个图。

分析：这是一个反馈控制系统的一般结构图，在这个结构图里面，我们来分析一下。

在被控对象当中，经常会有扰动量的存在。

Q： 如果现在系统即有参考输入，又有扰动，那么这样的系统不就变成了多输入单输出的系统了吗？

A： 不是。要注意，我们分析的对象是线性的，所谓线性是什么，它一定要满足叠加性，也就是说，在两个不用的作用量输入（控制量和扰动量），它等于每个输入单独作用下输出的叠加。也就是说：如果是线性系统，系统的实际输出 等于 参考作用对应的输出叠加上 扰动作用对应的输出。

Q： 而被控对象怎么才能够按照我们希望的控制规律运行呢？

A： 我们要加控制器，也就是自动控制装置，这些自动控制转置包括一些功率变换转置（变换放大）、一些校正转置（串联校正）。

在这个基础上系统往往不是单闭环的，在闭环的基础上还有可能附加内环。在这里我们还有一个比较重要的概念要注意：就是反馈（测量元件）。可以说 ：整个经典控制理论都是围绕着反馈展开的。

我们提到的反馈，通常就是负反馈，只有负反馈才能自动减少偏差，而正反馈能使偏差增大。那么系统当中有没有正反馈呢？有的，这个正反馈可能是出现在局部反馈的位置（反馈校正），如果现在我们想改善局部的性能，我们可以在整个系统上附加一个内环。（就如上图所示）

这就是我们常见系统基本结构。

那么在这个结构当中出现的实物装置有哪些呢？

测量元件： 我们先来看反馈装置中的测量元件。测量元件有这么几个功能：

1. 测量元件是检测被控量，而且要把被控量送回到输入端。由于我们常见的是电气控制系统，所以测量元件大部分是传感器，比如温度传感器、湿度传感器、速度传感器等等。这种传感器除了检测被控量之外，还要把一些非电的物理量，比如说温度等，转换为电量，送回到输入端。
2. 在系统当中有比较元件时，这个比较元件的功能主要是：得到被控量和控制量之间形成偏差信号。也就是说：对控制量和反馈量做比较，形成一个偏差信号。

变换放大转置： 除此之外，在这个系统中还有一些变换放大转置。为什么要加这样的转置呢？由于偏差信号通常是比较微弱的，那么怎么样要这个比较微弱的信号，能够驱动后端的装置正常的工作。答案就是：我们通常对这个偏差信号，要么是 功率放大，要么就是 电压放大。目的就是：为了让微弱的偏差信号有足够大的幅值和功率，能够驱动后端的装置来运行。

执行机构： 那么 在系统当中，还会有执行机构。所谓的执行机构，我们是指：直接作用在被控对象上面，驱动被控对象工作的装置。那么执行机构在我们常见的运动控制系统当中就是一些：功率变换装置这样的一些东西。当然，这些执行机构在一些过程控制里面也可能是一些障碍力调节器等等这样的元件。

校正元件： 系统中还会有校正元件的存在。所谓的校正元件是指：为了保证系统能够正常工作，我们增加了提高系统控制能力的这些元件，我们会在后面的教学中（频域校正）详细的讨论：校正元件我们该如何的选择；校正元件在系统当中的作用有哪些。

这 就是所有的自动控制系统的基本概念。

完

无论是学什么技术，你第一件事情就要搞清楚：为什么要学它；学它可以解决那些问题。

为什么要学习 自动控制原理

如果你做个平衡车或者四轴飞机或者倒立摆，你肯定会使用PID控制函数，它很神奇吧，没错，它就是最简单的自动控制原理其中的一个原理。

如果你停留在PID的程度，你会觉得控制就是调参数；学了极点配置之后，你起码对稳定性有了理解；学了lqr之后，你对怎么处理扰动、怎么track referance有了理解；学了mpc之后，你就会怎么处理约束；学了robust和adaptive时候，你懂得uncertainty怎么处理；而现在的control研究已经进展到与传统control完全不同的程度。比如：机器人这种十几个自由度的非线性系统如何稳定，如何让它稳定地走路。

我现在所知道的就是这些，— 2016年10月25日 — 不断完善中…。

我们接下来都是学习经典自动控制原理。而我学它的目的是：做机器人、替代人力机器。

既然决心要学，那定会认真的学。

图片来源于：http://www.acznw.com/archives/13854.html

• 我的电脑系统：Windows 10 64位
• SQL Server 软件版本： SQL Server 2014 Express

本篇博客里面使用了 scott 库，如何你现在还没有添加这个库到你的服务器里面，请在查看本篇博客前，访问这篇博文来在你的服务器里面附加scott库。

为什么需要事务

事务主要用来保证数据的合理性和并发处理的能力！

通俗的说：

• 事务可以保证避免数据处于一种不合理的中间状态
• 利用事务可以实现多个用户对共享资源的同时访问

例子：

银行中的转账操作，账户A把一定数量的款项转到账户B上，这个操作包括两个步骤，一个是从账户A上把存款减去一定数量，二是在账户B上把存款加上相同的数量。这两个步骤显然要么都完成，要么都取消，否则银行就会受损失。显然，这个转账操作中的两个步骤就构成一个事务。

假设A和B用户都希望查询修改M表数据，A用户不应该刚把M表的数据改成5，查询时显示的数据却是8（因为B用户修改M表的数据成8了）。事务必须得保证多个用户对共享资源同时访问时，数据库给用户的反应是合理的。

事务就是专门解决这个问题的。

什么是事务

一系列操作要么全部执行成功，要么全部执行失败，这就是事务。

如何创建事务

T-SQL 使用下列语句管理事务：

• 开启事务：begin transaction
• 提交事务：commit transaction
• 回滚（撤销）事务：rollback transaction

一旦事务提交或回滚，则事务结束。

判断某条语句执行是否出错：

• 使用全局变量 @@error
• @@error 只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计。如：set @errorSum=@errorSum+@@error

初学者必须要掌握的三个问题：

一 . 事务时用来研究什么的：

• 避免数据处于不合理的中间状态。（比如：转账）
• 怎样避免多用户同时访问时，呈现给用户的数据是合理的。

二 . 事务和线程的关系：

事务也是通过锁来解决很多问题的。（线程同步就是通过锁来解决的 sychronized）

三 . 事务和第三方插件的关系：

• 直接使用事务库技术难度很大，很多人是借助第三方插件来实现。（因为我们一般人不需要细细的研究数据库中事务的语法细节。）
• 第三方插件要想完成预期的功能，一般必须得借助数据库中的事务机制。

事务 — 总结 和 实例

总结事务的四大特性：事务必须具备以下四个属性，简称ACID属性。

• 原子性：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
• 一致性：当事务完成时，数据必须处于一致状态，要么处于开始状态要么处于结束状态，不允许出现中间状态！
• 隔离性：指当前的事务与其他未完成的事务是隔离的。在不同的隔离级别下，事务的读取操作，可以得到的结果是不同的。
• 持久性：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。

一个例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

--创建Test库
create database Test
--进入Test库
use Test
--创建一个bank表
create table bank
(
  customerEname nvarchar(200),
  currrentMoney money
)
--插入数据
insert into bank values ('张三', 1000)
insert into bank values ('李四', 1)
--给bank表中的currentMoney属性添加一个约束
alter table bank add constraint check_currentMoney check(currentMoney>=1)

现在我要将张三的1000打给李四。（这样做会失败，因为上面已经给check_currentMoney属性添加了一个约束：currentMoney>=1）。

普通的查询指令进行转账：

1
2

update bank set currentMoney=currentMoney-1000 where customerEname='张三'
update bank set currentMoney=currentMoney+1000 where customerEname='李四'

使用事务实现进行转账：（不重要，掌握就可以，语法怪异，实用性不强。）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

begin transaction
declare @errorSum int
set @errorSum = 0
update bank set currentMoney=currentMoney-1000
  where customerEname='张三'
set @errorSum = @errorSum + @@error
update bank bank set currentMoney=currentMoney+1000
  where customerEname='李四'
set @errorSum = @errorSum + @@error
if (@errorSum <> 0)
  begin
      print '转账失败'
      rollback transaction
  end
else
  begin
      print '转账成功'
      commit transaction
  end

• 我的电脑系统：Windows 10 64位
• SQL Server 软件版本： SQL Server 2014 Express

本篇博客里面使用了 scott 库，如何你现在还没有添加这个库到你的服务器里面，请在查看本篇博客前，访问这篇博文来在你的服务器里面附加scott库。

为什么需要视图

以一个例子为例：

求出平均工资最高的部门的编号和部门的平均工资。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

--使用我们目前所知道的方法去编写查询代码。
select *
  from (
      select deptno, avg(sal) "avg_sal"
          from emp
          group by deptno
  ) "T"
  where "T"."avg_sal" = (
      select max("E"."avg_sal") from (
          select deptno, avg(sal) "avg_sal"
              from emp
              group by deptno
      ) "E"
  )

上面的查询代码中，有下面的一段代码，这段代码是创建一个临时表。我们可以将这个临时表创建为一个视图：

1
2
3

  select deptno, avg(sal) "avg_sal"
      from emp
      group by deptno

创建了一个视图：

1
2
3
4
5

create view v$_emp_1
as
  select deptno, avg(sal) "avg_sal"
      from emp
      group by deptno

输出这个视图中的数据信息：

1

select * from v$_emp_1

下面的代码的输出同上面使用目前所知道的方法去编写查询代码。

1
2

select * from v$_emp_1
  where avg_sal = (select max(avg_sal) from v$_emp_1)

总结： 视图的优点是可以简化查询代码。（简化查询）。即避免了代码的冗余；避免了属性大量重复的sql语法。

什么是视图

• 视图从代码上看视图是一个select语句。
• 视图从逻辑上被当做一个虚拟表看待。

如何创建视图

视图的格式

1
2
3
4
5

create view 视图的名字
  as
      --select的前面不能添加begin
      select
      --select的后面不能添加end

视图的优点

1. 视图的优点是可以简化查询代码。
2. 增加数据的保密性

1
2
3
4
5

--屏蔽了入职日期和员工工资 这两个属性。
create view v$_emp2
as
  select empno, ename, job, mgr, comm, deptno
      from emp

视图的缺点

1. 增加了数据库维护的成本。
   • 原表里面的属性被改了，那么视图就会报错。
2. 视图只是简化了查询的代码，但是并不能加快查询的速度，这也是视图使用不足的地方。

使用视图要注意的三个问题

1 . 创建视图的select语句必须得为所有的计算列指定别名

1
2
3
4

--error
create view v$_a
as
  select avg(sal) from emp;

1
2
3
4

--ok
create view v$_a
as
  select avg(sal) as "avg_sal" from emp;

2 . 视图不是物理表，而是虚拟表

3 . 不建议通过视图更新视图所依赖的原始表的数据和结果。（因为很麻烦，有许多语法的限制。）

• 我的电脑系统：Windows 10 64位
• SQL Server 软件版本： SQL Server 2014 Express

本篇博客里面使用了 scott 库，如何你现在还没有添加这个库到你的服务器里面，请在查看本篇博客前，访问这篇博文来在你的服务器里面附加scott库。

identity 的用法 — 如何设置主键自动增长（用户不需要为identity修饰的主键赋值）

1
2
3
4
5
6
7
8
9
10
11

create table student3
(
  student_id int primary key identity (100, 5),
  student_name nvarchar(200) not null
)

select * from student3
insert into student3 (student_name) values ('张三');
insert into student3 values ('李四');  -- ok
delete from student3 where student_name = '李四';
insert into student3 (student_name) values ('王五');

如何重新设置identity 字段的值

表中删除数据又插入数据会导致主键不连续递增 怎么办？

不重要。主键是否连续增长不是非常重要。

如果对表中数据进行了删除操作，如何让 identity 字段重新从某个值开始自增？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

create table emp
(
  empid int identity(1, 1),
  ename nvarchar(20) not null
);

insert into emp values('aaaa');
insert into emp values('bbbb');
insert into emp values('cccc');
insert into emp values('dddd'); -- 10行
select * from emp
delete from emp where emp where wmpid=4 -- 删除empid为4的记录
select * from emp
insert into emp values('eeee') -- 因为执行10行时empid为4，所以执行本句时，empid为5
select * from emp
delete from emp where empid=5
dbcc checkident('emp', reseed, 3) -- 17行 把emp表中identity字段的初始值重新设置为3

insert into emp values('eeee') -- 此时插入记录时。empid为4，因为17行代码把empid设置成了3
select * from emp

1

dbcc checkident('emp', reseed, 0)

种子的值也可以是零，这样设置的话，用户插入值时，种子的初始值将从1开始。

总结：

identity 表示该字段的值会自动更新，不需要我们维护，通常情况下我们不可以直接给 identity 修饰的字符赋值，否则编译时会报错。

语法格式为：

• identity [(m, n)]
• m 表示的是初始值，n 表示的是每次自动增加的值
• 要么同时指定 m 和 n 的值，要么 m 和 n 都不指定，不能只写其中一个值。如：identity(3,2)、identity 都是正确的，但是 identity(3)、identity(2)、identity 都是错误的。
• 如果 m 和 n 都未指定，则取默认值(1, 1)

数据类型是整型的列才能被定义成标识列：

• int、bigint、smallint 列都可以被定义成 identity
• 不含有小数位的 decimal 和 numeric 也可以被标记为 identity。如：decimal、decimal(6, 0) 字段都可以被标记为 identity，但是 decimal(6, 2) 字段就不能被标记为 identity

标识列通常与 primary key 约束一起用作表的唯一行标识符：

• 非主键也是可以被定义为 identity的，但不推荐。

如何向 identity 字段插入数据

通常identity标记的字段我们是不需要插入数据的，即我们不需要维护 identity 字段的值，它会自动更新，如果我们需要向identity修饰的字段插入值，则必须满足如下两点：

1. 先得执行 set identity_insert [database.[owner.]] {table} {on | off}
2. 插入数据时必须得指定 identity 修饰的字段的名字

如何向 identity 字段插入数据示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create database Test
use test;
create table dept
(
  deptid decimal(6, 0) identity,
  deptname varchar(20),
);
set identity_insert test.dbo.dept on
  --执行本语句的目的是：希望可以向identity修饰的字段插入值
  --不可以改为set identity_insert dept on
  --不可以改为set identity_insert dbo.test.dept on
  --不可以改为set identity_insert dbo.test.dept.on
insert into dept (deptid, deptname) values (1, 'zhangsan')
  --不能改为：insert into dept values (1, 'zhangsan') -- 13

默认情况下，使用identity关键字修饰的主键是自动增长的，如果想手动设置主键的值，需要先将目标表打开：set identity_insert test.dbo.dept on，然后再使用insert into dept (deptid, deptname) values (1, 'zhangsan') 命令来手动设置主键的值。

• 我的电脑系统：Windows 10 64位
• SQL Server 软件版本： SQL Server 2014 Express

本篇博客里面使用了 scott 库，如何你现在还没有添加这个库到你的服务器里面，请在查看本篇博客前，访问这篇博文来在你的服务器里面附加scott库。

连接查询

定义：

将两个表或者两个以上的表以一定的连接条件连接起来。从中检索出满足条件的数据。

内连接 （innor join 或者 join）

请看这篇博客：SQL 数据库学习 查询 — 内连接。

左（右）外连接 （left join 和 right join）

1
2
3

select * from emp "E"
  left join dept "D"
  on "E".deptno = "D".deptno

实现的步骤：

1. 用左表的第一行分别和右表的所有行进行的连接，如果有匹配的行，则一起输出，如果右表有多行匹配，则结果集输出多行，如果没有匹配行，则结果集中只输出一行，该输出行左边为左表第一行内容，右边全部输出null。
2. 然后再用左表第二行和右边所有行进行连接，如果没有匹配行，则结果集中只输出一行，该输出行左边为左表第二行内容，右边全部输出null。
3. 以此类推，直至左边所有行连接完毕。
4. 因为右边很可能出现有多行和左边的某一行匹配，所以左连接产生的结果集的行数很可能大于 left join 左边表的记录的总数。
5. 帮助文档：左向外连接的结果集包括LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表均为空值。

左外连接的实际意义

• 返回一个事情及其该事物的相关信息，如果该事物没有相关信息，则输出null
• 例子：
  • 已知条件： productStocks 货物库存表、 orderform 订单表、pID 是产品的编号
  • sql语句
  • select productStocks.*, orderform.* from productStocks left join orderform on productStocks.pID=orderform.pID
  • 实际意义： 返回仓库中现存货物的信息及其该货物的订单信息，如果该货物没有订单信息，在把该货物的订单信息全部输出为null。

完全连接（full join）

1
2
3

select * from productStocks
  full join orderform
  on productStocks.pid = orderform.pid

结果集中包含三部分内容：

1. 两个表中匹配的所有行记录
2. 左表中那些在右表中找不到匹配的行的记录。这些记录的右边全为null
3. 右表中那些在左表中找不到匹配的行的记录。这些记录的左边全为null

交叉连接（cross join）

1
2
3

select *
  from emp
  cross join dept

等价于：

1

select * from emp, dept

效果都是将两个表进行笛卡尔积。

自连接

定义： 一张表自己和自己连接起来查询数据。

例子：不准用聚合函数 求薪水最高的员工的信息。

1
2
3
4

--用聚合函数 求出薪水最高的员工的信息
select *
  from emp
  where sal = (select max(sal) from emp)

1
2
3
4
5
6
7
8

--不准用聚合函数 求出薪水最高的员工的信息
select * from emp
  where empno not in (
      select distinct "E1".empno
      from emp "E1"
      join emp "E2"
      on "E1".sal < "E2".sal
  )

联合（union）

例子：

输出每一个员工的姓名、工资、上司的姓名。

1
2
3
4
5

select "E1".ename as "员工姓名","E1".sal as "员工工资", "E2".ename as "上司姓名"
  from emp "E1", emp "E2"
  where "E1".mgr = "E2".EMPNO
union
select ename, sal, '已是最大老板' from emp where mgr is null

定义：

表和表之间的数据以纵向的方式连接在一起。注意：我们以前讲的所有的连接是以横向的方式连接在一起的。

注意：

联合就是：若干个select 子句要联合成功的话，必须得满足两个条件：

1. 这若干个 select 子句输出的列数必须是相等的。
2. 这若干个 select 子句输出列的数据类型至少是兼容的。

• 我的电脑系统：Windows 10 64位
• SQL Server 软件版本： SQL Server 2014 Express

SQL Server 查询语句的顺序

1
2
3
4
5
6
7
8
9
10

select top ...
  from A
  join B
  on ...
  join C
  on ...
  where ...
  group by ...
  having ...
  order by ...

例子

本例子里面使用了 scott 库，如何你现在还没有添加这个库到你的服务器里面，请在查看本篇博客前，访问这篇博文来在你的服务器里面附加scott库。

求出平均薪水最好的部门的标号和部门的平均工资

1
2
3
4
5
6

--求出平均薪水最好的部门的标号和部门的平均工资
--第1种写法：
select top 1 deptno, avg(sal) "avg_sal"
  from emp "E"
  group by deptno
  order by avg(sal) desc

等价于：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

--求出平均薪水最好的部门的标号和部门的平均工资
--第2种写法：
select "E".*
  from (
      select deptno, avg(sal) "avg_sal"
          from emp
          group by deptno
  ) "E"
  where "E"."avg_sal" = (
      select max("avg_sal")
          from (
              select deptno, avg(sal) "avg_sal"
                  from emp
                  group by deptno
          ) "T"
  )

• 我的电脑系统：Windows 10 64位
• SQL Server 软件版本： SQL Server 2014 Express

本篇博客里面使用了 scott 库，如何你现在还没有添加这个库到你的服务器里面，请在查看本篇博客前，访问这篇博文来在你的服务器里面附加scott库。

连接查询

定义：

将两个表或者两个以上的表以一定的连接条件连接起来。从中检索出满足条件的数据。

一 . 内连接 — 知识点 （重点中的重点）

牛刀小试

1
2
3
4

select "E".ename "员工姓名", "D".dname "部门名称"
  from emp "E"
  join dept "D"
  on "E".deptno = "D".deptno

需要学的知识点：

1. select ... from A, B 的用法
2. select ... from A, B where ... 的用法
3. select ... from A join B on ... 的用法
4. select ... from A, B where ... 与 select ... from A join B on ... 的比较
5. select、from、where、join、on、group、order、top、having 的混合使用。

1 . select ... from A, B 的用法 — 笛卡尔积

1
2
3

--emp是14行； dept是5行3列
select * from emp, dept
  --输出是70行。不任何条件，进行笛卡尔积连接。

A 表里面没有个元组都会连接 B 表一个元组，这样就导致 B 表中的每一个元组都连接了 A 表中的没有个元组。

产生的结果： 行数是 A和B的乘积；列数是A和B之和。或者说：把A表的每一条记录都和B表的每一条记录组合在一起，形成的是个笛卡尔积。

2 . select ... from A, B where ... 的用法

1
2
3
4

select *
  from emp, dept
  where empno = 7369
  --输出5行，11列

其中有两个列deptno 属性，一列是来自 dept 表，一列是来自 emp 表。

select ... from A, B where ... 中的 where 是对 select * from emp, dept 产生的dept 和 emp 两个表进行笛卡尔积后得到的临时表进行过滤。

注意：

• select * from emp, dept where 1-1 输出70行 11列
• select * from emp, dept where empno = 7369 5行
• select * from emp, dept where deptno = 10 error
• select * from emp, dept where emp.deptno = 10 5行的倍数
• select * from emp, dept where dept.deptno = 10 14行

3 . select ... from A join B on ... 的用法

1
2
3
4
5
6

select "E".ename "员工姓名", "D".dname "部门名称"
  from emp "E"   --"E" 是别名
  join dept "D"  --"D" 是别名 join是连接
  on "E".deptno = "D".deptno  ---on 表示：连接条件
  --连接条件是：两个表里面的deptno属性相同，才能连接
  --所以现在得到的结果不是70行。

1
2
3
4
5

select *
  from emp "E"
  join dept "D"
  on "E".deptno = "D".deptno
  --输出是11列（有两列(deptno)是重复的）

下面的两个指令输出都是一样的结果。

1
2
3
4

select "E".ename "员工姓名", "D".dname "部门名称"
  from emp "E"
  join dept "D"
  on 1=1   --连接条件是 1=1 表示永远为真，所以执行后得到的结果是70行2列。

1
2
3
4

select "E".ename "员工姓名", "D".dname "部门名称"
  from emp "E"
  join dept "D"
  on 3>2   --连接条件是 3>2 表示永远为真，所以执行后得到的结果是70行2列。

on 不能省略，有 join 就必须有 on。

1
2
3
4
5

--error 多个表里面有相同的属性，就必须指定是哪个表的属性，否则报错。
select deptno
  from emp "E"
  join dept "D"
  on 1=1

别名取中文也可以：

1
2
3
4

select "部门表".deptno "部门编号"
  from emp "员工表"
  join dept "部门表"
  on 1=1

4 . from where 和 join on 的区别

• select ... from A, B where ... 是sql92标准。
• select ... join A, B where ... 是 sql99标准。

输出结果是一样的，但是推荐使用SQL99标准。

原因：

1. sql99 更容易理解。
2. 在sql99标准中，on 和 where 可以做不同的分工。on 指定连接条件，而where 是对连接之后临时表的数据进行过滤。

1
2
3

select *
  from emp, dept
  where emp.deptno = dept.deptno

等价于

1
2
3
4

select *
  from emp
  join dept
  on emp.deptno = dept.deptno

5 . where 是否可以写在 join on 的前面

1
2
3
4
5
6
7
8
9
10

--error
select "E".ename, "D".dname, "S".grade
  from emp "E"
  where "E".sal > 2000
  join dept "D"
  on "E".deptno = "D".deptno
  join SALGRADE "S"
  on "E".sal >= "S".losal and "E".sal <= "S".hisal
  --我只知道where写在join on前面是错的。但是我也不知道，为什么。
  --关键字的顺序都是固定的，是死的。

1
2
3
4
5
6
7
8

--OK
select "E".ename, "D".dname, "S".grade
  from emp "E"
  join dept "D"
  on "E".deptno = "D".deptno
  join SALGRADE "S"
  on "E".sal >= "S".losal and "E".sal <= "S".hisal
  where "E".sal > 2000

6 . 判断下列两个语句的输出结果

select ... from A join B on ... 的用法：A 和 B 互换， 输出结果不变。

select ... from A, B where ... 的用法： A 和 B 互换，输出结果不变。

下面的四个指令，输出的结果都是一样的。

1
2
3
4

select *
  from emp
  join dept
  on emp.deptno = dept.deptno

1
2
3
4

select *
  from dept
  join emp
  on emp.deptno = dept.deptno

1
2
3

select *
  from emp, dept
  where emp.deptno = dept.deptno

1
2
3

select *
  from dept， emp
  where emp.deptno = dept.deptno

7 . sql92标准 的语句如何用sql99的语句实现

1
2
3

--输出
select * from emp, dept
  where dept.deptno = 10

考虑如何把上面的sql语句用sql99来实现。

1
2
3
4
5
6

--这个sql语句和上面的sql语句输出结果是一模一样的
select *
  from emp
  join dept  --join 是连接
  on 1=1  --on 后面是连接条件
  where dept.deptno = 10  -- where 是对数据进行过滤

二 . 内连接 — 练习

1. 输出工资最高的前三名的每一个员工的姓名、工资、工资等级 和 部门名称
2. 输出姓名不包含A工资最高的前三名的每一个员工的姓名、工资、工资等级 和 部门名称
3. 求出每个员工的姓名、部门编号、薪水、薪水等级
4. 查找每个部门的编号、该部门所有员工的平均工资、平均工资的等级
5. 查找每一个部门的编号、部门名称、该部门所有员工的平均工资、平均工资的等级
6. 求出emp表中所有领导的姓名
7. 求出平均薪水最好的部门的标号和部门的平均工资
8. 有一个人工资最低，把这个人排除掉，剩下的人中工资最低的前3个人的姓名、工资、部门编号、部门名称、工资等级 输出。

1 . 输出工资最高的前三名的每一个员工的姓名、工资、工资等级 和 部门名称

1
2
3
4
5
6
7
8

--输出工资最高的前三名的每一个员工的姓名、工资、工资等级 和 部门名称
select top 3 "E".ename, "E".sal, "S".grade, "D".dname
  from emp "E"
  join dept "D"
  on "E".deptno = "D".deptno
  join SALGRADE "S"
  on "E".sal between "S".LOSAL and "S".hisal
  order by "E".sal desc

2 . 输出姓名不包含A工资最高的前三名的每一个员工的姓名、工资、工资等级 和 部门名称

1
2
3
4
5
6
7
8
9

--输出姓名不包含A工资最高的前三名的每一个员工的姓名、工资、工资等级 和 部门名称
select top 3 "E".ename, "E".sal, "S".grade, "D".dname
  from emp "E"
  join dept "D"
  on "E".deptno = "D".deptno
  join SALGRADE "S"
  on "E".sal between "S".LOSAL and "S".hisal
  where "E".ename not like '%A%'
  order by "E".sal desc

3 . 求出每个员工的姓名、部门编号、薪水、薪水等级

1
2
3
4
5

--求出每个员工的姓名、部门编号、薪水 和 薪水的等级
select "E".ename, "E".deptno, "E".sal, "S".grade
  from emp "E"
  join SALGRADE "S"
  on "E".sal >= "S".losal and "E".sal <= "S".hisal

4 . 查找每个部门的编号、该部门所有员工的平均工资、平均工资的等级

先要将部门的平均工资找出来。

1
2
3
4

--输出部门的平均工资
select deptno, avg(sal) as "avg_sal"
  from emp
  group by deptno

接下来是输出部门平均工资的等级。做法很简单，上面的指令生成了一张部门平均工资的临时表，接下来我们就对这个临时表通过join ... on ... 的join 关键字将这个临时表和 SALGRADE 表通过on 关键字后面的条件来得到部门平均工资的等级。

1
2
3
4
5
6
7
8
9

--查找每个部门的编号、该部门所有员工的平均工资、平均工资的等级
select "T".deptno, "T"."avg_sal" "部门平均工资", "S".grade "工资等级"
  from SALGRADE "S"
  join (
      select deptno, avg(sal) as "avg_sal"
          from emp
          group by deptno
  ) "T"
  on "T"."avg_sal" between "S".LOSAL and "S".HISAL

等价于

1
2
3
4
5
6
7
8

--查找每个部门的编号、该部门所有员工的平均工资、平均工资的等级
select "T".deptno, "T"."avg_sal" "部门平均工资", "S".grade "工资等级"
  from SALGRADE "S", (
      select deptno, avg(sal) as "avg_sal"
          from emp
          group by deptno
  ) "T"
  where "T"."avg_sal" between "S".LOSAL and "S".HISAL

输出结果都是：

5 . 查找每一个部门的编号、部门名称、该部门所有员工的平均工资、平均工资的等级

1
2
3
4
5
6
7
8
9
10
11

--查找每一个部门的编号、部门名称、该部门所有员工的平举工资、平均工资的等级
select "T".deptno, "D".dname "部门名称", "T"."avg_sal" "部门平均工资", "S".grade "工资等级"
  from SALGRADE "S"
  join (
      select deptno, avg(sal) as "avg_sal"
          from emp
          group by deptno
  ) "T"
  on "T"."avg_sal" between "S".LOSAL and "S".HISAL
  join dept "D"
  on "T".deptno = "D".deptno

其中 on 后面跟的是 表与表之间的连接条件。

6 . 求出 emp 表领导的姓名

1
2

--先把emp表列出来
select * from emp

1
2
3

--求出`emp`表领导的姓名
select ename from emp
  where empno in (select mgr from emp) --如果员工编号在领导编号里面，就输出

1
2
3
4

--求出emp表中所有非领导的信息
--error
select * from emp
  where empno not in (select mgr from emp)

这样写是不对的，我们需要注意：in 与 null 的组合所带来的问题。

7 . 求出平均薪水最好的部门的标号和部门的平均工资

1
2
3
4

--先得到部门平均工资的临时表
select deptno, avg(sal) "avg_sal"
  from emp "E"
  group by deptno

然后排序，在输出第1个。

1
2
3
4
5
6

--求出平均薪水最好的部门的标号和部门的平均工资
--第1种写法：
select top 1 deptno, avg(sal) "avg_sal"
  from emp "E"
  group by deptno
  order by avg(sal) desc

等价于：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

--求出平均薪水最好的部门的标号和部门的平均工资
--第2种写法：
select "E".*
  from (
      select deptno, avg(sal) "avg_sal"
          from emp
          group by deptno
  ) "E"
  where "E"."avg_sal" = (
      select max("avg_sal")
          from (
              select deptno, avg(sal) "avg_sal"
                  from emp
                  group by deptno
          ) "T"
  )

8 . 有一个人工资最低，把这个人排除掉，剩下的人中工资最低的前3个人的姓名、工资、部门编号、部门名称、工资等级 输出。

先得到工资最低的那个人的工资。

1

select min(sal) from emp

去掉工资最低的那个人：

1
2
3
4

--有一个人工资最低，把这个人排除掉
select *
  from emp
  where sal > (select min(sal) from emp)

1
2
3
4
5
6
7
8
9
10
11
12
13

--有一个人工资最低，把这个人排除掉，
--剩下的人中工资最低的前3个人的姓名、工资、部门编号、部门名称、工资等级 输出。
select top 3 "T".ename, "T".sal, "T".deptno, "D".dname, "S".grade
  from (
      select *
          from emp
          where sal > (select min(sal) from emp)
  ) "T"
  join dept "D"
  on "T".deptno = "D".deptno
  join SALGRADE "S"
  on "T".sal between "S".LOSAL and "S".HISAL
  order by "T".sal asc

搞定

• 我的电脑系统：Windows 10 64位
• SQL Server 软件版本： SQL Server 2014 Express

本篇博客里面使用了 scott 库，如何你现在还没有添加这个库到你的服务器里面，请在查看本篇博客前，访问这篇博文来在你的服务器里面附加scott库。

having — 对分组之后的信息进行过滤

1
2
3
4
5

--输出部门平均工资大于2000的部门的部门编号，部门的平均工资
select deptno, avg(sal)
  from emp
  group by deptno
  having avg(sal)>2000;

加入别名。

1
2
3
4
5

--判断下列sql语句是否正确
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having avg(sal) > 2000

1
2
3
4
5

--error
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having "平均工资" > 2000

正确的指令： sql select deptno, avg(sal) from emp where ename not like '%A%' group by deptno having avg(sal) > 2000

1
2
3
4
5

--以部门编号分组后，显示组内元组大于3个元组的部门编号和平均工资
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having count(*) > 3

1
2
3
4
5

--error
select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having ename like '%A%'

1
2
3
4

select deptno, avg(sal) as "平均工资"
  from emp
  group by deptno
  having deptno>1

having 和 where 的异同

1
2
3
4
5
6
7

--把姓名不包含`A`的所有的员工按部门编号分组，
--统计输出部门平均工资大于2000的部门的部门编号、部门的平均工资
select deptno, avg(sal)
  from emp
  where ename not like '%A%'
  group by deptno
  having avg(sal) > 2000

1
2
3
4
5
6
7

--把工资大于2000，
--统计输出部门平均工资大于3000的部门的部门编号、部门的平均工资、部门人数、部门最高工资
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门最高工资"
  from emp
  where sal > 2000    --where是对原始的记录过滤
  group by deptno
  having avg(sal) > 3000   --对分组之后的记录过滤 

如果参数的顺序变化了，执行的时候会不会受到影响？执行下面的指令，你就会知道答案。

1
2
3
4
5
6

--error
select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门最高工资"
  from emp
  group by deptno
  having avg(sal) > 3000
  where sal > 2000

总结： 所有select 的参数的顺序是不允许变化的（所有的参数的位置都是固定的，你可以省略某个参数，但是不能变。），否则编译时出错。

having 和 where 的异同：

• 相同： 都是对数据过滤，只保留有效的数据。where 和 having 一样，都不允许出现字段的别名，只允许出现最原始的字段的名字。

1
2
3
4

--error where子句不应该出现聚合函数
select deptno, avg(sal)
  from emp
  where avg(sal) > 2000   --error 因为where是对原始的数据过滤，不能使用聚合函数

• 不同： where 是对原始的记录过滤，having 是对分组之后的记录过滤。where 必须得写在having的前面，顺序不可颠倒，否则运行出错。

总结 having 的用法

1. having 子句是用来对分组之后的数据进行过滤。因此使用having时通常都会先使用 group by。
2. 如果没有使用 group by ，而使用了 having，则意味着 having 把所有的记录当做一组来进行过滤。

1
2
3

select count(*)
  from emp
  having avg(sal) > 1000

1. having 子句出现的字段必须得是分组之后的组的整体信息。having 子句不允许出现组内的详细信息。

1
2
3
4

--error
select deptno avg(sal) as "平均工资", job
  from emp
  group by deptno

1
2
3
4

--OK
select deptno avg(sal) as "平均工资", count(*) as "部门人数"
  from emp
  group by deptno

1. 尽管 select 字段中可以出现别名。但是having 子句中不能出现字段的别名，只能使用字段最原始的名字。（原因不得而知。）

2. 就是上面说的：having 和 where 的异同 。（这里不重复说明了。）