数据库SQL语言

2、 技术细节：（数据库 的具体使用环境 MSSQL、mySQL、Oracle）

1、数据库简介：

数据库是专门开发数据管理的软件，或者说专门管理数据的软件就是数据库。

数据库存在的意义就是：减轻开发人员的负担。数据库是一个综合的软件，那么我们不需要队要进行2进制保存数据进行处理了，但是却是要与数据库产生交互，那么命令式SQL，有技巧的 ，数据库就是万物皆关系（面向对象，万物皆是对象）有所区别。

2、数据库的发展：

一开始的是层次化的数据与网状数据库 ，后来也发现使用确实很麻烦。

于是到了1970年EFCO公司开创了关系性的数据库 ，技术难度大。后来，Oracle（甲骨文）公司的创始人，拉里投入到关系型数据库的研发，并且得到了一个大客户 —美国国防部。随即开始出现关系数据库的旋风，随后各个公司都纷纷推出自己的数据库系统。比如：IBM的DB2 ，还有风靡一时的DBS3。

但是随即出现不兼容的问题，由于最早的时候都没有进行没规范。所以到最后各个数据库巨头统一了操纵数据库的SQL（结构化数据查询语言） 变成了标准语言，而关系型数据库也俨然变成大家的宠儿，Oracle也从一个小公司，变成现在的数据的巨头，而我们的微软也推出了SQLserver。当然还有PHPer的最爱mySQL。但是mySQL被SUN，SUN被Oracle收购，现在有免费版与收费专业版了。所以我们学习SQL语言的时候，先学共同点，再学特异性。各种数据库软件在使用上有一点区别。

3、数据库系统详解：

为适应数据处理的需要而发展起来的一种较为理想的数据处理的核心机构。计算机的高速处理能力和大容量存储器提供了实现数据管理自动化的条件。 数据库系统一般由4个部分组成：

①数据库，即存储在磁带、磁盘、光盘或其他外存介质上、按一定结构组织在一起的相关数据的集合。（个体）

②数据库管理系统（DBMS）。一组能完成描述、管理、维护子数据库的程序系统。它按照一种公用的和可控制的方法完成插入新数据、修改和检索原有数据的操作。 ③数据库管理员（DBA）。

④用户和应用程序。（微软的称作SSMS）

4、数据库系统的基本要求是：

① 能够保证数据的性。数据和程序相互有利于加快软件开发速度，节省开发费用。 ② 冗余数据少，数据共享程度高。

③ 系统的用户接口简单，用户容易掌握，使用方便。

④ 能够确保系统运行可靠，出现故障时能迅速排除；能够保护数据不受非受权者访问或破坏；能够防止错误数据的产生，一旦产生也能及时发现。

⑤ 有重新组织数据的能力，能改变数据的存储结构或数据存储位置，以适应用户操作特性的变化，改善由于频繁插入、删除操作造成的数据组织零乱和时空性能变坏的状况。 ⑥ 具有可修改性和可扩充性、可维护性。 ⑦ 能够充分描述数据间的内在联系。

5、数据库（Database）:

由众多的数据、数据表、约束、存储过程、函数、视图、索引构成的一个数据存储与交互单元，是按照数据结构来组织、存储和管理数据的仓库。

6、数据表（table）:

数据表，实际上是一个二维表。一般是围绕一个事务动作记录，或者是一个信息主题作为一个数据表。数据表由行与列构成。

7、列（column、field）:

列，其实就是字段。也是决定了信息的基本单元。列，包含有数据类型的设定。

8、行（row、record）；

行，实际上就是一条基本信息。一行包含了多列数据的存储的信息。所以一行也有一条记录之称。

9、行业（trade）

每一个行业每一个需求，没一个需求每一种数据库的设计模式与思想。每个行业的数据设计的重点都是不同的。侧重查询还是操作就是自己选择的问题了。

10、索引（index）

索引其实就是一个B+树，但是这个索引是N^n层数次方的。目的就是在数据库中划分出一定的区域优化查询。可以提升大量数据的查询速度。索引一般可以分为：基于字段优化查询速度的普通索引、唯一性索引、主键索引、全文索引、单列与多列索引。现在由于数据库系统的不断升级，我们只要设定索引就可以了，不需要特殊的维护。而且数据在查询的时候也会根据查询适当地选择是利用索引查询，还是仅仅是表查询。由于数据库系统的发展，系统内部已经自动帮我们完成对索引的维护。但是在设计的时候要考虑到索引的损耗问题。

11、视图（view）

固化的子查询，将一个子查询起了一个固化的名字，保存在数据库中，方便以后的使用。其实调用大量的Join来进行一个查询一般也是用视图。视图与索引都是为了优化查询的速度与语句。视图是优化语句，索引是优化单查速度。一般是DBA来设定数据库的视图， 封装内部数据库的数据关系，范式修改数据容易了，视图让我们查询复杂关系的数据变得容易。

12、命名规范：

数据库对象 表(Table) 字段(Column) 视图(View)

存储过程(Stored procedure) 触发器(Trigger) 索引(Index) 主键(Primary key) 外键(Foreign key)

Check约束(Check Constraint) Unique约束

用户定义函数(User-defined function)

事务表命名：

这个表的起名，建议的写法是将两个表的表名合并(如果表名比较长可做简化)，此处如 tbl_StudentCourse。

比如给StudentCourse表中加AccessLevel字段，值域D{只读,完全,禁止}，就可以实现访问级别。 tbl_StudentCourse_ReadOnly

字段命名：

所有表示时间的字段，统一以 Date 来作为结尾。登陆时间 LogionDate 所有表示数目、次数的字段，都应该以Count作为结尾。登陆次数 LoginCount 所有代表链接的字段，均为Url结尾。广告链接 LoginUrl

按照微软的建议，布尔类型的值均以 Is是否、Has有无 或者 Can 能否开头。true都表示肯定。

比如tbl_User中全部都是UserId，UserName?没必要，只要我们坚持在他处使用[tbl_User.UserId]、[tbl_User.Name] 所有的主键都是 信息体Id

参数命名： @CurrentDate @ActivityCount @EquipmentType、

存储过程命名：

prCalculateTotalPrice ——估算总价

pr CountTotalAmountOfMonthlyPayments ——计算每月付费的总金额 prGetParentOrganizationalUnitName ——获取上级单位名称

14、Null的三值关系 null为unknow，逻辑为假，关系忽略，当为不跳过断言

Null也是一个集合，但是仅仅表示的是一个空集，但是实际上是null=Unknow。

1、 NULL与别的值进行+-*/等计算操作（包括在大多数函数中使用NULL）后，结果是NULL（标量表达式）。 NULL与别的值进行=、>、<等比较操作后，结果是Unknown（断言）。

一句话：就是true就是真，false就当做false，null在bool当做false，要是主键外键的话，一律是unknow，即是根据内外联进行筛选。 判断语句中Unknown当作False来处理。 [code=sql]

NOT Unknown --> Unknown

Unknown AND/OR Unknown --> Unknown Unknown OR TRUE --> TRUE Unknown AND TRUE --> Unknown Unknown OR FALSE --> Unknown Unknown AND FALSE --> FALSE [/code]

具体可查三值逻辑的真值表。

2. 在where/on/having和if/case when中，只有true(1=1)才使条件成立（即在判断语句中Unknown当作False来处理）。比如： where column = value：表中column为NULL的行永远不会返回，即使value是NULL； case value when NULL then XXX when ... end：XXX永远不会执行，即使value是NULL；

if {XXX} else {YYY} end或case when then XXX else YYY end：这两种情况下，,即是null会执行。 3. 包含外键约束和Check约束的字段允许NULL（即约束只当条件为False时出错，Unknown是不管的）。

4. 包含唯一约束（unique index）的字段只允许一个NULL的行，再插入或更新该字段为NULL的行会报字段重复的错误。 5. GROUP BY时，所有NULL被视为一组。

6. ORDER BY时，所有NULL排在一起，但NULL排在非空值的前面（最前面）（如SQL Server）还是后面（如Oracle），SQL标准未规定。 7. 聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行，即是不返回此行数据。 8. declare的变量，在未赋值之前为NULL。 9. 与NULL处理相关的运算符和函数：

- IS NULL/IS NOT NULL：用这两个运算符来判断一个值是否为NULL，而不是=或<>。

前缀 tbl 无 v pr tr ix_ pk_ fk_ ck_ uq_ fn

举例 Pascal命名法 tbl_Student Title，studentID vActivity prDelOrder trOrder_D ix_CustomerID pk_Admin

fk_Order_OrderType ck_TableColumn uq_TableColumn udtPhone fnDueDate

用户定义数据类型(User-defined data type) udt

- ISNULL/COALESCE：取第一个非空值（注意两个函数的数据类型转换规则不同）。 - NULLIF(a,b)：等价于CASE WHEN a = b THEN NULL ELSE a END。

15、Not Null的建表

由于三值关系的存在，我们很难辨认出null的时候，我发现很多开发人员在建表的时候，如果要新建一个字段，他的思路是这样的：默认这个字段是可以为Null的，然后去判断是不是非要Not Null不可，如果不是这样，OK，这个字段可以为Null，接着继续进行下一个字段。结果往往是一张表除了主键以外所有的字段都可以为Null。之所以会有这样的思路，是因为Null好啊，程序不容易出错啊，你插入记录的时候如果不小心忘输了一个字段，程序依然可以Run，而不会出现 “XX字段不能为Null”的错误消息。

但是，这样做的结果却是很严重的，也会使你的程序变得更加繁琐，你不得不进行一些无谓的空值处理，以避免程序出错。更糟的是，如果一些重要数据，比如说订单的某一项值为Null了，那么大家知道，任何值与Null相操作(比如加减乘除)，结果都是Null，导致的结果就是订单的总金额也为Null。 你可以运行下面的代码尝试一下：

Select Null + 5 As Result

你可能会说，就算我将字段设置成Not Null，但是它依然可以接受空字符串，这样一来在程序中还是要进行空值处理。请别忘了，数据库还赋予你一个强力武器，就是 Check 约束， 当你需要确保一个字段既不可以为Null，又不可以为空的时候，可以这么写：

Abstract Varchar(500) Not Null Constraint ck_ColumnName Check(Len(ColumnName)>0),

[Type] TinyInt Not Null Default 0 Constraint ck_ArticleType Check([Type] in (0,1,2)),

所以，合理的思维方式应该是这样的：默认这个字段是 Not Null的，然后判断这个字段是不是非Null不可，如果不是这样，OK，这个字段是Not Null的，进行下一个字段。

16、MSSQL数据类型详解

数据 bit int smallint tinyint numeric 类型 整型 整型 整型 整型 精确数字型 decimal money smallmoney float real datetime Smalldatetime cursor timestamp 精确数值型 货币型 货币型 近似数值型 近似数值型 日期时间型 日期时间型 特殊数据型 特殊数据型 Uniqueidentifier 特殊数据型 char varchar text ntext nchar nvarchar 字符型 字符型 字符型 字符型 统一编码字符 统一编码字符型 ntext 统一编码字符型 binary 二进制数据类binary数据类型用来存储可达8000 字节长的定长的二进制数据。当输入表的内容接近相同的长度时，你应该使用这种数据类型 ntext 数据类型用来存储大量的统一编码字符型数据。这种数据类型能存储230 -1或将近10亿个字符，且使用的字节空间增加了一倍 描述 bit 数据类型是整型，其值只能是0、1或空值(null)。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或Fa lse 、On 或Off 。 int 数据类型可以存储从- 231(-21474838)到231 (2147483 7)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节 smallint 数据类型可以存储从- 215(-32768)到215(32767)之间的整数。这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。这种数据类型在数据库里占用2个字节空间 tinyint 数据类型能存储从0到255 之间的整数。它在你只打算存储有限数目的数值时很有用。 这种数据类型在数据库中占用1 个字节 numeric数据类型与decimal 型相同 decimal 数据类型能用来存储从-1038-1到1038-1的固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。 范围是小数点左右所能存储的数字的总位数。精度是小数点右边存储的数字的位数。左边是总数位，右边是小数点后的数位。 money 数据类型用来表示钱和货币值。这种数据类型能存储从-9220亿到9220 亿之间的数据，精确到货币单位的万分之一 smallmoney 数据类型用来表示钱和货币值。这种数据类型能存储从-214748.38 到214748.37 之间的数据，精确到货币单位的万分之一 float 数据类型是一种近似数值类型，供浮点数使用。说浮点数是近似的，是因为在其范围内不是所有的数都能精确表示。浮点数可以是从-1.79E+308到1.79E+308 之间的任意数 real 数据类型像浮点数一样，是近似数值类型。它可以表示数值在-3.40E+38到3.40E+38之间的浮点数 datetime数据类型用来表示日期和时间。这种数据类型存储从1753年1月1日到9999年12月3 1日间所有的日期和时间数据， 精确到三百分之一秒或3.33毫秒 smalldatetime 数据类型用来表示从1900年1月1日到2079年6月6日间的日期和时间，精确到一分钟 cursor 数据类型是一种特殊的数据类型，它包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用 timestamp 数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。 一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。尽管它的名字中有“time”， 但timestamp列不是人们可识别的日期。在一个数据库里，timestamp值是唯一的 Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列 char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，你必须指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型的列宽最大为8000 个字符 varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。与char 型不一样，此数据类型为变长。当定义一列为该数据类型时，你要指定该列的最大长度。 它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度。其实就是可变长的char类型。 text 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有231-1或20亿个字符。有点像BLOB、CLOB，只保留16个字节的地址，速度会变慢。它不是char所以字符串的函数对其无效 也是用Unicode的text。 nchar 数据类型用来存储定长统一编码字符型数据。统一编码用2个字节来存储每个字符，而不是用单字节(普通文本中的情况)。它允许大量的扩展字符。此数据类型能存储4000种字符，使用的字节空间上增加了一倍。可以针对特殊的字符。就会使用Unicode的编码来存储。8000个字节。 nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍。变长、特殊字符。这种Unicode编码要比char、varchar存储相同的数据耗用了 型 varbinary 二进制数据类型 image 二进制数据类型 BLOB、CLOB 统一编码字符型

17、结合信息对象与业务设计数据库设计

1、数据库的设计师根据信息要素、关系来设计的； 2、数据库的信息查找依赖于信息的使用方式的； 3、对于可有可无的东西，我们需要花点时间去好好斟酌； 4、简短的查询，比囧长的业务查询更加需要思考；

5、使用数据的方式将会与我们占用系统内存与硬盘息息相关，影响着服务器的整体表现，所以我们的设计会有所倚重与侧重； 现在基本是双数据库，外层是方便查询，内层是方便修改。

SQL换一句话其实就是关系数据库管理系统 Realtional Database Management System ,RDBMS 表的设计的方法：

1、挑出事物对象（对象信息浓缩法）

2、围绕事物对象的信息收集（信息扩散聚拢法） 3、信息拆分成块（字段原子性） 4、事务需求拆分法（关系设计）

5、数据的发散与聚合程度的分析(表设计的检验)

18、数据库的原子性

1、你的表是描述什么事物，明确表的信息对象主体 2、你的表是为了适合什么操作语句的设计的，面向操作设计

3、列的内容要不要原子性的切块，让查询直逼要害，列的设计的小块的程度要拿捏好

4、表必须是二维表，而且要根据不同的需求进行设计，表的设计与列的设计一样都要依赖与操作与需求

原子性的存在是为了切分适合的信息块 ，从而达到信息的最大化、最优化的使用，并不是无限切分就好，要看实际的应用。但是由于原子性数据的表容易设计，而且运行的所需时间较短（查询信息的指向性比较强，同时也便于维护喝更新操作，但是却是在海量数据搜索面前有点乏力），所以对大量的数据处理的时候要加分效果。 1、 同一列中不能存在多个类型相同的值，一列不能多值（除非是） 2、 同一个表中也不能存在有多个存储相同类型数据的列

3、 无重复的行，每列只存储一份同类型下的一份数据（列是单值的），无重复列名 4、

19、规范化建表

让数据具有原子性是设计出规范化数据表的第一步（同时也是为了提高数据库的可读性），但是到后期你可能会因为数据量以及性能的问题打破这种规范化。因为你的数据库投入使用后数据库的大小日益增大，设计规范化的表可以时刻保持你应对以后各种复杂的组合查询 （主要是应对数据库过分膨胀后的，查询缓慢的问题）

规范化建表的优点：

1、可以减少表中重复数据的存储，减少数据库的大小；

2、因为数据库的切割使得你的查询的指向性更强，查找的数据少，查找得更加快速； 3、但是最后归根到底还是 避免存储重复的数据节省我们的硬盘空间。

20、范式 1NF 2NF 3NF 4NF

1NF，主要是解决表中字段设计、信息与业务之间的关系的问题

1、 数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。在第一范式（1NF）中表的每一行只包含一个

实例的信息。简而言之，第一范式就是无重复的列，而且单列无重复的值。

2、 所有属性都必须是原子的——每个属性一个值。一个实体的所有实例都必须包含同等数量的值。一个实体的所有实例必须两两不相同。 2NF，主要是设定主键，解决数据库的数据冗余、更新、插入、更新的指向性的问题

1、 要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码、唯一

关联。引入主键约束primary key，Constraint pk_Article Primary Key(Id)。主键的设定也是有要求的。主键默认不能为空，保持唯一性。插入、更新删、除记录的时候必须指定主键的值。要求主键必须简洁，主键值不能被修改。

2、 实体必须满足第一范式。所有属性描述的事实都必须与整个键相关，而不能只描述键的一个子集。 3NF，主要是解决排除部分列之间的依赖关系，利用了表与表的主外键关系解决。

1、 要求实体数据记录(record)相对于外部完全依赖于主关键ID。所谓完全依赖，是指在除主键外其他的属性列之间不存在对应依赖。如果存在，那么这一依赖部分应该分离出来形

成一个表（关联），一般抽离出这一部分的主依赖项的ID，把这一部分作为一个新的表记录，同时原表也存储此次依赖项ID，然后利用建立主外键关系，将一表分成两表，解决原表因为主次依赖项存在而造成的数据冗余等一系列问题（但是对于以查询操作为主、记录大量数据的表此范式就需要斟酌了）。第二范式就是建立主键，与排除其他列对其属性列的依赖（消除部分列的依赖）。

2、 实体必须满足第二范式。如果每个非键属性描述的事实都与一个键属性相关，则实体满足第三范式所有属性描述的都必须是关于键的事实，除此之外别无其他。

4NF，主要是解决根据属性列的相似（不同的从属对象）与共同（相同的从属对象）信息的建表问题，即是表中所有列对外的一对多关系不受

1、 因为比较抽象，即是A表中含有a1,a2两个属性列，B表中记录a1的信息（包含b1），C表中包含a2的信息（包含c1），但是b1，c1实际上是相同类型的信息，但是从对象

不同。此处就是要我们明确建表的时候，要仔细区分共有与相似（决定建表）的关系，从属对象与属性列之间关系与一对多关系的搭设与否的问题。 Max的突破，可以存特别长的数据。这样就可以突破原本的数据类型的长度。 记录大量的文字，适合记录博文。 image 数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节 varbinary 数据类型用来存储可达8000 字节长的变长的二进制数据。当输入表的内容大小可变时，你应该使用这种数据类型 2、 在实体中，不会出现多于一个的多值依赖。

5NF，主要是解决将信息切割得更细，清除所有冗余，但是在4NF设计会出现联接不当而产生的错误record

1、在4NF下，一般我们仅仅是区分了属性列的相似与共有信息，但是这个时候就会因为新建的b、c而产生误解数据或者是错误记录。因此可以从第五范式（实际上是所有范式）中得到的启示是，当你觉得可以将表分解为带着不同自然键的更多、更小的表时，这么干很可能就是最好的方式。如果你想要把各个部分联结到一起，表示最初的、分解得更少的数据形式，数据库干这事比你干得强多了。很明显，如果你不能通过联结来重新构建分解前的表，那就最好不要进行分解。即是有一个道理，更少得冗余，更多的错误。唯有靠联接来解决了。或者采用树状分层设计法，同层合并，也可以单独建立一个事务记录表。 2、当分解是信息无损的时候，所有关系都被分解成了二元关系。

但是面对日益变态的信息几何级的增长，为了令到数据库从焦油坑变成金矿我们会加入数据挖掘的思想，但是这个时候就需要无损的信息与高度分块的。所以在物理建表之前，我们还是需要遵循规范化建表的原则。

20、不规范化建表

规范化主要用来改善性能，过度规范化的结构会造成查询处理器和SQL Server中其他进程的开销过大（不适用在查询表的设计），或者，有时需要降低复杂性来使得实现更容易些，非规范化在这些时候就会发挥作用。正如我在这章中一直试图阐明的那样，虽然人们大都认为非规范化到第三范式就可以了（靠着减少所需联结的数量，查询过程已经被简化了）然而，这样做冒着引入数据异常的风险。每个使用数据库的应用程序都要复制任何额外写的，用来处理这些异常代码，因此就增加了人为错误的可能性。在这种情况下必须作出的判断是：是一个稍微慢点（但是100％正确）的应用更好呢，还是一个更快但正确性稍差的应用更好些。

在逻辑建模阶段，绝不应该从我们规范化的结构倒退，对应用程序过早过激地进行性能调优工作。OLTP数据库结构上，所以设计中最重要的部分就是：保证我们的逻辑模型体现了最终的数据库含有的所有实体和属性。一旦开始物理建模过程（这与性能调优应该是同义语），那就有足够充足的理由来对结构进行非规范化操作，要么是出于性能的考虑，要么是出于减少复杂性的考虑，但是，这两种考虑都不适合用在逻辑模型上。当我们物理上的实现在逻辑上也正确的时候，碰到的问题几乎总是会更少些。对几乎所有情况来说，我都建议等到物理建模阶段才对解决方案进行实现，或者，至少等到有某种非做不可的原因时（比如，系统中某些部分失败了）才进行非规范化工作。

总结：一般，我们是采用两层数据库的，一层是外层，用于直接的查询。一层是内层数据库，用于记录与维护更新的。维护更新资料的时候，对内层数据库进行操作，当操作完成的时候就会马上更新外层数据库。所以外层数据库（以查询为主）设计的范式要求比较低，但是内层数据库（维护更新为主）的要求就比较高了。

13、数据库设计建议：

数据库不仅是用来保存数据，还应负责维护数据的完整性和一致性

我看过很多的开发人员设计出来的数据库，给我的感觉就是：在他们眼里，数据库的作用就如同它的名称一样――不仅仅是用来存放数据的，除了不得不建的主键以外，什么都没有...没有 Check约束，没有索引，没有外键约束，没有视图，甚至没有存储过程。 一个很好的数据库该有的还是有的。什么都没有的数据库插入操作是很好，但是多了这点东西的数据库还是很快的。

如果要写代码来确保表中的行都是唯一的，就为表添加一个主键，即是distinct的使用频率应该是比价少的。 如果要写代码来确保表中的一个单独的列是唯一的，就为表添加一个约束，一般是事务状态记录表。 如果要写代码确定表中的列的取值只能属于某个范围，就添加一个Check约束。 如果要写代码来连接 父－子 表，就创建一个关系，上下其实都是依据主外键的。

如果要写代码来维护“一旦父表中的一行发生变化，连带变更子表中的相关行”，就启用级联删除和更新。 如果要调用大量的Join来进行一个查询，就创建一个视图。

如果要逐条的写数据库操作的语句来完成一个业务规则，就使用存储过程。

没有提到触发器，实践证明触发器会使数据库迅速变得过于复杂，更重要的是触发器难以调试，如果不小心建了个连环触发器，就更让人头疼了，所以我更倾向于根本就不使用触发器trigger。

3、技术实例：（数据库）

1、 加载数据库脚本

source C:\\Users\\Administrator\\Desktop\\script-1.sql ;——mySQL

source (\\.) Execute an SQL script file. Takes a file name as an argument.

2、 保存为数据库脚本

Tee C:\\Users\\Administrator\\Desktop\\script-1.sql ;

tee (\\T) Set outfile [to_outfile]. Append everything into given outfile. notee 输入的时候就会把tee与notee进行保存为sql脚本。

3、 查询当前实例的数据库集合

Show databases ; ——mySQL mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | drinks | | mysql | | performance_schema | | test | +--------------------+

4、使用某个数据库

mysql> use drinks; Database changed

5、查询当前数据库的表集

mysql> show tables; +------------------+ | Tables_in_drinks | +------------------+ | easy_drinks | | my_contacts | +------------------+

6、查询表的结构

mysql> desc my_contacts;

+------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ 恶补：

1、 判断存在

--利用系统视图判断数据表是否存在,而且是先判断是否存在[dbo].[tb]对象，在判断是否是N'IsUserTable' --利用系统视图判断对象是否存在,而且是先判断是否存在[dbo].[对象名]对象，在判断是否是N'类型' Sysobjects是Master系统视图 object_id是抓取对象在视图中的ID

OBJECTPROPERTY是根据对象判断类型函数，返回的是bool

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tb];

2、 模糊搜索，删除对象，动态SQL语句

declare @a varchar(2000) ——MSSQL 删除约束

set @a=(select name from sysobjects where name like 'ConstraintName') set @a= 'ALTER TABLE [dbo].[TableName] DROP CONSTRAINT '+@a exec (@a)

7、数据库的新建、删、改

①：新建数据库

mysql> create database myDB; ——mySQL Query OK, 1 row affected (0.00 sec)

create database mmc; ——MSSQL

②：数据库修改

exec sp_rename 'a1.name ', 'Name ', 'database' ——MSSQL

③：删除数据库

mysql> drop database drinks;——mySQL Query OK, 2 rows affected (0.34 sec)

drop database mySQL;——MSSQL

8、数据表的新建、删、改

①：新建数据表

mysql> show create table servers;——mySQL显示表的创建方式 | servers | CREATE TABLE `servers` (

`Server_name` char() NOT NULL DEFAULT '',

`Host` char() NOT NULL DEFAULT '', `Db` char() NOT NULL DEFAULT '',

`Username` char() NOT NULL DEFAULT '', `Password` char() NOT NULL DEFAULT '', `Port` int(4) NOT NULL DEFAULT '0', `Socket` char() NOT NULL DEFAULT '', `Wrapper` char() NOT NULL DEFAULT '', `Owner` char() NOT NULL DEFAULT '', PRIMARY KEY (`Server_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table' |

mysql> create table my_contacts( ——mySQL -> last_name int not null Auto_increment, -> first_name varchar(20) not null , -> email varchar(50) not null, -> birthday date,

-> profession varchar(50), -> location varchar(50), -> status varchar(20), -> interests varchar(20),

-> seeking varchar(100) ， Parmary kry (名 ) );

Create Table Article ——MSSQL ( Id

Int Identity(1,1) Not Null,——自增 Varchar(50) Varchar(50)

Not Null Constraint uq_ArticleTitle Unique,——1、唯一约束

Not Null Constraint ck_KeywordsLength Check (len(Keywords)<10),——2、检查约束 Not Null, ——3、非空约束

Not Null,

Not Null Default 0 Constraint ck_ArticleType Check([Type] in (0,1,2)), Not Null Default 1, ——默认值 Not Null, Null,

Not Null Default 'TraceFact', Null,

Not Null Default GetDate(), Not Null Default 0, Not Null ,

Title Keywords Abstract Author [Type]

Varchar(500)

Varchar(50) TinyInt

IsOnIndex Bit Content Text [Source] SrcUrl PostDate ClassId

SourceCode Varchar(100)

Varchar(50) DateTime

Int

Varchar(150)

ViewCount Int

Constraint pk_Article Primary Key(Id), —— 4、主键约束

Constraint fk_Article foreign key(scoreId) references student(id)——5、外键约束，为主表student建立外键关系

)

②：修改数据表

mysql> alter table 旧表名 rename to 新表名; ——mySQL use databasename;

EXEC sp_rename '旧表名', '新表名';——MSSQL

③：删除数据表

mysql> drop table mytables;——mySQL

drop table a1;——MSSQL

9、字段、约束（数据表成员）的增加、删、改

①：增加字段

mysql> alter table mytable——mySQL 添加字段

-> add column mycolumn2 int first , 其中还有 after column 排在指定的列之后 -> add primary key (mycolumn2); ——mySQL 添加主键

alter table test1 add nob int identity(1,1) not null ——MSSQL 添加字段 alter table test1 add primary key (nob); ——MSSQL 添加主键

alter table [dbo].[Article2] add DEFAULT ((1)) FOR [IsOnIndex] ——MSSQL添加默认约束

ALTER TABLE [dbo].[Article2] WITH CHECK ADD CONSTRAINT [ck_KeywordsLength2] CHECK ((len([Keywords])<(10))) ——新建check ALTER TABLE [dbo].[Article2] CHECK CONSTRAINT [ck_KeywordsLength2] ——MSSQL把ck_KeywordsLength2添加check约束集合

ALTER TABLE [dbo].[Article2] WITH CHECK ADD CONSTRAINT [ck_ArticleType2] CHECK (([Type]=(2) OR [Type]=(1) OR [Type]=(0)))

②：修改字段

mysql> alter table mytables -> add primary key ( lastname );

mysql> alter table mytable——mySQL 不改变名字，改类型 -> modify mycolumn2 varchar(20);

-> change column firstname firstName varchar(30) ,——mySQL 改变名字、类型

exec sp_rename 'a1.name ', 'Name ', 'column ' ——MSSQL 只改变名字

alter table test1 alter column ID varchar(200) null ——MSSQL 不改变名字，改类型 alter table test1 drop Constraint constraintname; ——MSSQL 删除约束 alter table test1 add primary key (nob); ——MSSQL 添加主键约束

alter table Articles2 add constraint fk_job_id foreign key (Id) references Article3(Id); ——MSSQL添加外键约束 alter table dbo.Article2 add constraint uk_Id unique nonclustered (Id); ——MSSQL添加唯一约束

alter table [dbo].[Article2] add constraint uk_Id default ((1)) for [IsOnIndex] ——MSSQL添加默认约束

alter table [dbo].[Article2] with check add constraint [ck_KeywordsLength2] CHECK ((len([Keywords])<(10))) ——新建check alter table [dbo].[Article2] chenk constraint [ck_KeywordsLength2] ——MSSQL把ck_KeywordsLength2添加check约束集合

alter table [dbo].[Article2] with check add constraint [ck_ArticleType2] check (([Type]=(2) OR [Type]=(1) OR [Type]=(0)))

alter table [dbo].[Article2] with check add constraint [ck_KeywordsLength2] check ((len([Keywords])<(10))) ——新建check alter table [dbo].[Article2] check constraint [ck_KeywordsLength2] ——MSSQL把ck_KeywordsLength2添加check约束集合

备注：最后一栏column/database/index/object等等，还有约束的修改只能是先删除再新建。

③：删除字段

mysql> alter table mytable ——mySQL -> drop column mycolumn2;

alter table test1 drop column nob; ——MSSQL 删除字段

alter table test1 drop Constraint constraintname; ——MSSQL 删除约束

10、连接词 and、or 、not

①：and：A and B，即是A与B 要同时成立。 ②：or：A或者B，只要其中一个成立就成立

③：not：not A and B，当A不成立而且B成立；A and not B，当A成立而且B不成立； not (A and B)，当A与B都不成立

①：<：小于 ②：>：大于

③：<=：小于或者等于 ④：>=：大于或者等于 ⑤：<>：不等于；=等于 ⑥：is null 或者是 not null

⑦：<‟D‟: field的第一个字母早于D；>‟A‟:field的第一个字母要晚于A

12、匹配关键字LIKE、通配符“%”与占位符 “_”与区间关键字“between”与集合关键字“in“

①：where field Like ‘%CA%’，filed字段里面包含CA字符的所有结果； ②：where field Like ‘CA%’，field字段前面是CA字符的结果； ③：where field Like ‘%CA’，field字段后面是CA字符的结果； ③：where field Like ‘_CA%’，field字段前面第二第三个是CA的结果；

④：where field between 20 and 30 在20与30之间 或者 between „A‟ and „Z‟ 在A与Z之间。

⑤：where field in (1,2,3) filed（类型相同）是1,2,3中的其中一个 或者 where field in (‘1’,‘2’,‘3’)field在‘1’、‘2’、‘3’中的其中之一，即是filed的类型要

与in里面的类型相同。

13、SQL语句select、insert、delete、update

①：select field from table

②：insert into table (fields) values (values) ③：delete from table where field 条件

④：update table set field = value where field 条件，例子：update table set table.column2= Right(table,2) ⑤：SQL语句是可以结合运算的。Update set const=const=1；

11、符号 <、>、=、<=、=>、<>、null

14、SQL字符串处理函数

①：Right (column，2) 从右边开始截取2位的字符

②：Substring _index( column，“，”，2) 截取第二个逗号（字符）前的字符串

③：Substring (column，numberA，numberB) 截取列中，从A位开始读取后面B位的字符串 ④：Upper(column) 转换大写、 ⑤：Lower (column) 转换小写

⑥：Reverse(column) 反转字符串的顺序 ⑦：Ltim( colum ) 左去除空 ⑧：Rtrim( column) 右去除空

⑨：Length (column) 获取字符长度，返回一个int，但是在MSSQL len(Keywords)<10

14、数据表约束Constraint（唯一、非空、检查、主键、外键）

①：唯一约束：Constraint uq_ArticleTitle Unique,——1、唯一约束

alter table dbo.Article2 add constraint uk_Id unique nonclustered (Id); ——MSSQL添加唯一约束

唯一约束即是指定的列的值在此列中只出现一次，null也是不能出现第二次的。

②：非空约束：Content Text Not Null,

alter table student add constraint codenn check(code is not null);

非空约束指定列不能为空。

③：检查约束：Constraint ck_ArticleType Check([Type] in (0,1,2)),

alter table [dbo].[Article2] with check add constraint [ck_KeywordsLength2] check ((len([Keywords])<(10)))

check约束是功能最强大的约束，其中可以写很多逻辑条件进去。

④：主键约束：Constraint pk_Article Primary Key(Id), —— 4、主键约束

alter table test1 add primary key (nob); ——MSSQL 添加主键约束

主键约束是指此列为唯一标识，一般配合identity(1,1)使用。

⑤：外键约束：Constraint fk_Article foreign key(scoreId) references student(id)——5、外键约束，为主表student建立外键关系

alter table Articles2 add constraint fk_job_id foreign key (Id) references Article3(Id); ——MSSQL添加外键约束

B表中使用外键约束字段ID，那么确保B表中的输入的ID的值与A表中ID的值匹配。A表ID为主键，B表ID为外键，B的ID依赖与A的ID的存在。

15、case when then语句

①：一般使用：select case column when value1 then operate1 end when value2 then operate2 end else operate3 end from table 返回的operate的值 ②：结合连接词 ：select ('Disk'+Ltrim(Rtrim(Convert(char,a.Disk_ID)))+'Movie'+Rtrim(Ltrim(Convert(char,b.Movie_ID)))) as 编号,

b.Movie_Name as 影片名称,a.RentCount as 租借次数, (case a.States when 0 then '没租' when 1 then '已租出' when 2 then '已预定' when 3 then '损毁' end ) as 状态 from (tbl_MovieDisk a inner join tbl_Movies b on

a.Movie_ID=b.Movie_ID) left join tbl_MovieTypes e on b.Type_ID=e.Type_ID;

③：结合组函数转置：

16、排序关键字 order by column（顺序） 与 order by column desc（倒序）MSSQL的功能都不是单一、的，所以必须对结果集合进行收缩

create table #tmp1 (

name nvarchar(20), name2 nvarchar(20) )

select * from #tmp1;

insert into #tmp1 (name) values ('A'); insert into #tmp1 (name,name2) values (1,1); insert into #tmp1 (name,name2) values ('a',1); insert into #tmp1 (name,name2) values (1,'b'); insert into #tmp1 (name,name2) values (1,'?'); insert into #tmp1 (name,name2) values (1,'B'); insert into #tmp1 (name,name2) values (1,'A'); select name2 from #tmp1 order by name2;

①：对于数字1到9，顺序是1-9，由小到大；而倒序是9-1；

②：对于数字1到9，而且夹杂a-z与A-Z，顺序是aABbcCDd 小大大小模式；

③：对于字母a到z，顺序是a-z；而倒序是z-a；

④：对于字母a到z，即是夹杂a-z与A-Z，没有排序的时候：aABbcCDd 小大大小模式；

⑤：其实是MSSQL对大小写都是同等对待。select tmpChar from #tmp1 group by tmpChar，只出现一组的a-g,而且是不区分大小写的，导致了。

⑥：order by的多组合，先执行列1再在列1的区域里面再进行列2的排序select tmpChar from #tmp1 order by tmpChar desc倒序,tmpNum asc顺序;

⑦：对于符号的排序的问题，我们有时候要对nvarchar类型的字段进行排序，里面有很多特殊的字符，那么顺序是：！“&‘（*+=@

⑧：一般规律是：顺序上到下 ，null 非字母字符 数字 大写字母 小写字母 非字母字符

17、分组关键字 group by

create table #tmp2 (

[Count] int,

name2 nvarchar(20) )

select * from #tmp2;

insert into #tmp2 ([Count],name2) values (1,1) insert into #tmp2 ([Count],name2) values (2,1) insert into #tmp2 ([Count],name2) values (3,2) insert into #tmp2 ([Count],name2) values (4,2) insert into #tmp2 ([Count],name2) values (5,3) select name2 from #tmp2 group by name2;OK

select [Count],name2 from #tmp2 group by name2;wrong select [Count],name2 from #tmp2 group by name2,[Count]; OK select SUM([Count]) as 汇总 ,name2 from #tmp2 group by name2; OK

①：指定的列进行分组，返回的结果集合要是包含其他的列（没有组函数运算），那么需要把其他的列依次加入到分组集合中。

18、聚合SUM()、AVG()、COUNT()、MAX()、MIN()

①：SUM()，汇总聚合函数

②：AVG（），平均聚合函数； ③：COUNT（），计数聚合函数； ④：MAX（），求最大值聚合函数； ⑤：MIN（），求最小值聚合函数。

备注：使用聚合函数在查询范围后一定要使用group by 对聚合函数以外的列进行分组。而且所有的对象是除字符类型的所有数值类型。 ②：在结果集合里面，除分组集合中的列外，其他列必须有组函数修饰。

19、子查询、范围匹配关键字in与聚合函数后的查询子句Having

①：子查询是指当一个查询是令一个查询的条件的时候。即是：查询出来的结果是另外一个查询的搜索域或者条件。 ①-1：

搜索域（双重嵌套）

select ss.studentid, ts.[name], ss.totalmark from (

select s.studentid, sum( mark ) as TotalMark from

( tbl_students s inner join tbl_marks m on s.studentId = m.sid ) inner join tbl_courses c on m.cid = c.courseId group by s.studentId ) ss inner join tbl_students ts on ss.studentid = ts.studentid 条件（返回的集合区间） select Name

from AdventureWorks.Production.Product where ListPrice = ( select ListPrice

from AdventureWorks.Production.Product where Name = 'Chainring Bolts' )

①-2：集合in、having、exists

②：in是确定指定的值是否与子查询或列表区域中的值相匹配。返回一个bool 区间条件：·

select FirstName, LastName, e.Title from HumanResources.Employee as e

JOIN Person.Contact as c on e.ContactID = c.ContactID

where e.Title in ('Design Engineer', 'Tool Designer', 'Marketing Assistant'); GO

Check约束：Constraint ck_ArticleType Check([Type] in (0,1,2)), –-check约束中的使用

③：Having是指定组或聚合的搜索条件，相当于group by后的where。Having 只能与 select 语句一起使用。Hav8ng 通常在 group by 子句中使用。如果不使用 group by 子句，则 Having 的行为与 where 子句一样。在 Having 子句中不能使用 text、image 和 ntext 数据类型

select SalesOrderID, sum(LineTotal) as SubTotal –-此处有聚合函数 from Sales.SalesOrderDetail group by SalesOrderID

Having sum(LineTotal) > 100000.00 –Having不能使用text、image、ntext order by SalesOrderID ;

20、内连接inner join table on ，外连接 right join table on 、 Left join table on与全连接 full join table on

现在有table A 与 table B。A、B表有主外键关于 ID主外键关系。A表是主表，B表是子表。A是影片表，B是影碟表，ID是MovieID。

Create table #tmpMovie (

MovieID int ,

MovieName nvarchar(30) )

select * from #tmpMovie; --6条数据，2条没用

insert into #tmpMovie (MovieID,MovieName)values (1,'影片'); insert into #tmpMovie (MovieID,MovieName)values (2,'影片'); insert into #tmpMovie (MovieID,MovieName)values (3,'影片'); insert into #tmpMovie (MovieID,MovieName)values (4,'影片');

insert into #tmpMovie (MovieID,MovieName)values (5,'影片');--多余的 insert into #tmpMovie (MovieID,MovieName)values (6,'影片');--多余的

create table #tmpDisk (

DiscID int,

DiscName nvarchar(30), MovieID int )

select * from #tmpDisk; --10条数据，4条没用

insert into #tmpDisk (DiscID,DiscName,MovieID) values (1,'影碟',1); insert into #tmpDisk (DiscID,DiscName,MovieID) values (2,'影碟',1); insert into #tmpDisk (DiscID,DiscName,MovieID) values (3,'影碟',2); insert into #tmpDisk (DiscID,DiscName,MovieID) values (4,'影碟',2); insert into #tmpDisk (DiscID,DiscName,MovieID) values (5,'影碟',3); insert into #tmpDisk (DiscID,DiscName) values (6,'影碟');--为空 insert into #tmpDisk (DiscID,DiscName,MovieID) values (7,'影碟',4); insert into #tmpDisk (DiscID,DiscName) values (8,'影碟');--为空

insert into #tmpDisk (DiscID,DiscName,MovieID) values (9,'影碟',7); --影片不存在 insert into #tmpDisk (DiscID,DiscName,MovieID) values (10,'影碟',8); --影片不存在 --内连A，6条

select a.MovieID from #tmpMovie a inner join #tmpDisk b on a.MovieID=b.MovieID; --内连B，6条

select b.MovieID from #tmpDisk b inner join #tmpMovie a on a.MovieID=b.MovieID;--效果一致 --左连接A，6条+A2条

select * from #tmpMovie a left join #tmpDisk b on a.MovieID=b.MovieID; --左连接B，6条+B4条

select * from #tmpDisk b left join #tmpMovie a on a.MovieID=b.MovieID; --右连接A，6条+B4条

select * from #tmpMovie a right join #tmpDisk b on a.MovieID=b.MovieID; --右连接B，6条+A2条

select * from #tmpDisk b right join #tmpMovie a on a.MovieID=b.MovieID; --外连接A，6条+A2条+B4条

select * from #tmpMovie a full join #tmpDisk b on a.MovieID=b.MovieID; --外连接B，6条+A2条+B4条

select * from #tmpDisk b full join #tmpMovie a on a.MovieID=b.MovieID;

①：内连接，select ID from A inner join B on A.ID = B.ID，查询出A中依赖于B中有的，即是A与B共有的。

select ID from B inner join A on A.ID = B.ID，查询出B中依赖于A中有的。与上面一样。

内连，只查询出A与B有的数据，不会出现对应ID为null的情况。不完全的查询，没有null的存在。

②：左连接：select * from A Left join B on A.ID=B.ID，先查询出A的，再根据A的结果查询B，B中没A的会以A列为null的形式出现。5

左连接，只根据左表的ID对应查询出右表的数据，右表不存在时将出现null的情况。左全右不全

Select * from B Right join A on A.ID=B.ID，先查询A的，再根据A的结果查询B，A中没B的会以B列为null的形式出现。

右连接，只根据右表的ID对应查询出左表的数据，左表不存在时将出现null的情况。右全左不全 全连接，把A、B表的数据全部显示，实际上是内、左、右连接的并集。共有的，两边独自的。笛卡尔积，

④：全连接：select * from A full join B on A.ID=B.ID，即是在查询出A与B共有的数据的同时，一并把A有B没有，B有A没有的数据全部显示。 备注：凡是出现连接字段（主键没外键、外键没主键）的数据一律称为数据冗余。

影片 NULL NULL NULL

select * from B Left join A on A.ID=B.ID，先查询出B的，再根据B的结果查询A，A中没B的会以B列为null的形式出现。10影碟8 NULL NULL

③：右连接：select * from A Right join B on A.ID=B.ID，先查询B的，再根据B的结果查询A，B中没A的会以A列为null的形式出现。

19、SQL语句执行顺序

19、视图

在不影响任何关联应用程序的情况下对表进行更改，这种抽象化使安全性得以提高。而且可以封装一些复杂的查询，可以快速地获取所需要的数据。一般是DBA来设定数据库的视图， 封装内部数据库的数据关系，范式修改数据容易了，视图让我们查询复杂关系的数据变得容易。

create view vMoneyUsing as select * from MoneyUsing;

19、转置 转置一般有两种办法，一种是case+聚合函数、分组，一种是 pivat

①：case when then 结合聚合函数与分组，值得注意的是结果字段要是没聚合函数修饰，那么就要在查询域后对field进行分组

select [sid],

sum ([mark]) as 总分,

avg ([mark]) as 平均分,

avg ( case when [cid]=1 then [mark] end) as [database], avg (case when [cid]=2 then [mark] end) as [C#], avg (case when [cid]= 3 then [mark] end) as [xml] from tbl_marks

group by [sid] –-要查询结果字段中没有聚合函数的字段进行分组

②：pivot语句，

select 'AverageCost' as Cost_Sorted_By_Proction_Days,–-排除子查询只能够piovt中的字段就是剩下的唯一字段进行默认分组 [0], [1], [2], [3], [4] --对应的列

From -- 记得在查询域中不能包含non-pivoted column，即是除了最后单一分组字段，其余字段必须是pivoted column (

select DaysToManufacture, StandardCost

from Production.Product) as SourceTable

PIVOT

for DaysToManufacture IN ([0], [1], [2], [3], [4] –for old field in 集合 )

) as PivotTable;

( avg(StandardCost) -–对原来行的数据的目标列进行聚合函数的修饰

Select *

From –- 筛选子查询字段信息，排除no pivoted column (

select [sid], [cid], [mark] from tbl_marks) p pivot (

sum (mark) for [cid] in ( [1], [2], [3] ) ) as pvt