数据库SQL语言共22页.docx
《数据库SQL语言共22页.docx》由会员分享，可在线阅读，更多相关《数据库SQL语言共22页.docx（36页珍藏版）》请在冰点文库上搜索。
数据库SQL语言共22页
1、技术标签：
（数据库使用实例）
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、数据库系统的基本要求是：
1能够保证数据的独立性。
数据和程序相互独立有利于加快软件开发速度，节省开发费用。
2冗余数据少，数据共享程度高。
3系统的用户接口简单，用户容易掌握，使用方便。
4能够确保系统运行可靠，出现故障时能迅速排除；能够保护数据不受非受权者访问或破坏；能够防止错误数据的产生，一旦产生也能及时发现。
5有重新组织数据的能力，能改变数据的存储结构或数据存储位置，以适应用户操作特性的变化，改善由于频繁插入、删除操作造成的数据组织零乱和时空性能变坏的状况。
6具有可修改性和可扩充性、可维护性。
7能够充分描述数据间的内在联系。
5、数据库（Database）:
由众多的数据、数据表、约束、存储过程、函数、视图、索引构成的一个数据存储与交互单元，是按照数据结构来组织、存储和管理数据的仓库。
6、数据表（table）:
数据表，实际上是一个二维表。
一般是围绕一个事务动作记录，或者是一个信息主题作为一个数据表。
数据表由行与列构成。
7、列（column、field）:
列，其实就是字段。
也是决定了信息的基本单元。
列，包含有数据类型的设定。
8、行（row、record）；
行，实际上就是一条基本信息。
一行包含了多列数据的存储的信息。
所以一行也有一条记录之称。
9、行业（trade）
每一个行业每一个需求，没一个需求每一种数据库的设计模式与思想。
每个行业的数据设计的重点都是不同的。
侧重查询还是操作就是自己选择的问题了。
10、索引（index）
索引其实就是一个B+树，但是这个索引是N^n层数次方的。
目的就是在数据库中划分出一定的区域优化查询。
可以提升大量数据的查询速度。
索引一般可以分为：
基于字段优化查询速度的普通索引、唯一性索引、主键索引、全文索引、单列与多列索引。
现在由于数据库系统的不断升级，我们只要设定索引就可以了，不需要特殊的维护。
而且数据在查询的时候也会根据查询适当地选择是利用索引查询，还是仅仅是表查询。
由于数据库系统的发展，系统内部已经自动帮我们完成对索引的维护。
但是在设计的时候要考虑到索引的损耗问题。
11、视图（view）
固化的子查询，将一个子查询起了一个固化的名字，保存在数据库中，方便以后的使用。
其实调用大量的Join来进行一个查询一般也是用视图。
视图与索引都是为了优化查询的速度与语句。
视图是优化语句，索引是优化单查速度。
一般是DBA来设定数据库的视图，封装内部数据库的数据关系，范式修改数据容易了，视图让我们查询复杂关系的数据变得容易。
12、命名规范：
数据库对象
前缀
举例Pascal命名法
表（Table）
字段（Column）
视图（View）
存储过程（Storedprocedure）
触发器（Trigger）
索引（Index）
主键（Primarykey）
外键（Foreignkey）
Check约束（CheckConstraint）
Unique约束
用户定义数据类型（User-defineddatatype）
用户定义函数（User-definedfunction）
tbl
无
v
pr
tr
ix_
pk_
fk_
ck_
uq_
udt
fn
tbl_Student
Title，studentID
vActivity
prDelOrder
trOrder_D
ix_CustomerID
pk_Admin
fk_Order_OrderType
ck_TableColumn
uq_TableColumn
udtPhone
fnDueDate
事务表命名：
这个表的起名，建议的写法是将两个表的表名合并（如果表名比较长可做简化），此处如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——估算总价
prCountTotalAmountOfMonthlyPayments——计算每月付费的总金额
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]
NOTUnknown-->Unknown
UnknownAND/ORUnknown-->Unknown
UnknownORTRUE-->TRUE
UnknownANDTRUE-->Unknown
UnknownORFALSE-->Unknown
UnknownANDFALSE-->FALSE
[/code]
具体可查三值逻辑的真值表。
2.在where/on/having和if/casewhen中，只有true（1=1）才使条件成立（即在判断语句中Unknown当作False来处理）。
比如：
wherecolumn=value：
表中column为NULL的行永远不会返回，即使value是NULL；
casevaluewhenNULLthenXXXwhen...end：
XXX永远不会执行，即使value是NULL；
if{XXX}else{YYY}end或casewhenthenXXXelseYYYend：
这两种情况下，,即是null会执行。
3.包含外键约束和Check约束的字段允许NULL（即约束只当条件为False时出错，Unknown是不管的）。
4.包含唯一约束（uniqueindex）的字段只允许一个NULL的行，再插入或更新该字段为NULL的行会报字段重复的错误。
5.GROUPBY时，所有NULL被视为一组。
6.ORDERBY时，所有NULL排在一起，但NULL排在非空值的前面（最前面）（如SQLServer）还是后面（如Oracle），SQL标准未规定。
7.聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行，即是不返回此行数据。
8.declare的变量，在未赋值之前为NULL。
9.与NULL处理相关的运算符和函数：
-ISNULL/ISNOTNULL：
用这两个运算符来判断一个值是否为NULL，而不是=或<>。
-ISNULL/COALESCE：
取第一个非空值（注意两个函数的数据类型转换规则不同）。
-NULLIF（a,b）：
等价于CASEWHENa=bTHENNULLELSEaEND。
15、NotNull的建表
由于三值关系的存在，我们很难辨认出null的时候，我发现很多开发人员在建表的时候，如果要新建一个字段，他的思路是这样的：
默认这个字段是可以为Null的，然后去判断是不是非要NotNull不可，如果不是这样，OK，这个字段可以为Null，接着继续进行下一个字段。
结果往往是一张表除了主键以外所有的字段都可以为Null。
之所以会有这样的思路，是因为Null好啊，程序不容易出错啊，你插入记录的时候如果不小心忘输了一个字段，程序依然可以Run，而不会出现“XX字段不能为Null”的错误消息。
但是，这样做的结果却是很严重的，也会使你的程序变得更加繁琐，你不得不进行一些无谓的空值处理，以避免程序出错。
更糟的是，如果一些重要数据，比如说订单的某一项值为Null了，那么大家知道，任何值与Null相操作（比如加减乘除），结果都是Null，导致的结果就是订单的总金额也为Null。
你可以运行下面的代码尝试一下：
SelectNull+5AsResult
你可能会说，就算我将字段设置成NotNull，但是它依然可以接受空字符串，这样一来在程序中还是要进行空值处理。
请别忘了，数据库还赋予你一个强力武器，就是Check约束，
当你需要确保一个字段既不可以为Null，又不可以为空的时候，可以这么写：
AbstractVarchar（500）NotNullConstraintck_ColumnNameCheck（Len（ColumnName）>0）,
[Type]TinyIntNotNullDefault0Constraintck_ArticleTypeCheck（[Type]in（0,1,2））,
所以，合理的思维方式应该是这样的：
默认这个字段是NotNull的，然后判断这个字段是不是非Null不可，如果不是这样，OK，这个字段是NotNull的，进行下一个字段。
16、MSSQL数据类型详解
数据
类型
描述
bit
整型
bit数据类型是整型，其值只能是0、1或空值（null）。
这种数据类型用于存储只有两种可能值的数据，如Yes或No、True或False、On或Off。
int
整型
int数据类型可以存储从-231（-2147483648）到231（2147483647）之间的整数。
存储到数据库的几乎所有数值型的数据都可以用这种数据类型。
这种数据类型在数据库里占用4个字节
smallint
整型
smallint数据类型可以存储从-215（-32768）到215（32767）之间的整数。
这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。
这种数据类型在数据库里占用2个字节空间
tinyint
整型
tinyint数据类型能存储从0到255之间的整数。
它在你只打算存储有限数目的数值时很有用。
这种数据类型在数据库中占用1个字节
numeric
精确数字型
numeric数据类型与decimal型相同
decimal
精确数值型
decimal数据类型能用来存储从-1038-1到1038-1的固定精度和范围的数值型数据。
使用这种数据类型时，必须指定范围和精度。
范围是小数点左右所能存储的数字的总位数。
精度是小数点右边存储的数字的位数。
左边是总数位，右边是小数点后的数位。
money
货币型
money数据类型用来表示钱和货币值。
这种数据类型能存储从-9220亿到9220亿之间的数据，精确到货币单位的万分之一
smallmoney
货币型
smallmoney数据类型用来表示钱和货币值。
这种数据类型能存储从-214748.3648到214748.3647之间的数据，精确到货币单位的万分之一
float
近似数值型
float数据类型是一种近似数值类型，供浮点数使用。
说浮点数是近似的，是因为在其范围内不是所有的数都能精确表示。
浮点数可以是从-1.79E+308到1.79E+308之间的任意数
real
近似数值型
real数据类型像浮点数一样，是近似数值类型。
它可以表示数值在-3.40E+38到3.40E+38之间的浮点数
datetime
日期时间型
datetime数据类型用来表示日期和时间。
这种数据类型存储从1753年1月1日到9999年12月31日间所有的日期和时间数据，精确到三百分之一秒或3.33毫秒
Smalldatetime
日期时间型
smalldatetime数据类型用来表示从1900年1月1日到2079年6月6日间的日期和时间，精确到一分钟
cursor
特殊数据型
cursor数据类型是一种特殊的数据类型，它包含一个对游标的引用。
这种数据类型用在存储过程中，而且创建表时不能用
timestamp
特殊数据型
timestamp数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。
一个表中只能有一个timestamp列。
每次插入或修改一行时，timestamp列的值都会改变。
尽管它的名字中有“time”，但timestamp列不是人们可识别的日期。
在一个数据库里，timestamp值是唯一的
Uniqueidentifier
特殊数据型
Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。
GUID确实是全局唯一的。
这个数几乎没有机会在另一个系统中被重建。
可以使用NEWID函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列
char
字符型
char数据类型用来存储指定长度的定长非统一编码型的数据。
当定义一列为此类型时，你必须指定列长。
当你总能知道要存储的数据的长度时，此数据类型很有用。
例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。
此数据类型的列宽最大为8000个字符
varchar
字符型
varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。
与char型不一样，此数据类型为变长。
当定义一列为该数据类型时，你要指定该列的最大长度。
它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度。
其实就是可变长的char类型。
text
字符型
text数据类型用来存储大量的非统一编码型字符数据。
这种数据类型最多可以有231-1或20亿个字符。
有点像BLOB、CLOB，只保留16个字节的地址，速度会变慢。
它不是char所以字符串的函数对其无效
ntext
字符型
也是用Unicode的text。
nchar
统一编码字符
nchar数据类型用来存储定长统一编码字符型数据。
统一编码用2个字节来存储每个字符，而不是用单字节（普通文本中的情况）。
它允许大量的扩展字符。
此数据类型能存储4000种字符，使用的字节空间上增加了一倍。
可以针对特殊的字符。
就会使用Unicode的编码来存储。
8000个字节。
nvarchar
统一编码字符型
nvarchar数据类型用作变长的统一编码字符型数据。
此数据类型能存储4000种字符，使用的字节空间增加了一倍。
变长、特殊字符。
这种Unicode编码要比char、varchar存储相同的数据耗用了
ntext
统一编码字符型
ntext数据类型用来存储大量的统一编码字符型数据。
这种数据类型能存储230-1或将近10亿个字符，且使用的字节空间增加了一倍
binary
二进制数据类型
binary数据类型用来存储可达8000字节长的定长的二进制数据。
当输入表的内容接近相同的长度时，你应该使用这种数据类型
varbinary
二进制数据类型
varbinary数据类型用来存储可达8000字节长的变长的二进制数据。
当输入表的内容大小可变时，你应该使用这种数据类型
image
二进制数据类型
image数据类型用来存储变长的二进制数据，最大可达231-1或大约20亿字节
BLOB、CLOB
统一编码字符型
记录大量的文字，适合记录博文。
Max的突破限制，可以存特别长的数据。
这样就可以突破原本的数据类型的长度限制。
17、结合信息对象与业务设计数据库设计
1、数据库的设计师根据信息要素、关系来设计的；
2、数据库的信息查找依赖于信息的使用方式的；
3、对于可有可无的东西，我们需要花点时间去好好斟酌；
4、简短的查询，比囧长的业务查询更加需要思考；
5、使用数据的方式将会与我们占用系统内存与硬盘息息相关，影响着服务器的整体表现，所以我们的设计会有所倚重与侧重；
现在基本是双数据库，外层是方便查询，内层是方便修改。
SQL换一句话其实就是关系数据库管理系统RealtionalDatabaseManagementSystem,RDBMS
表的设计的方法：
1、挑出事物对象（对象信息浓缩法）
2、围绕事物对象的信息收集（信息扩散聚拢法）
3、信息拆分成块（字段原子性）
4、事务需求拆分法（关系设计）
5、数据的发散与聚合程度的分析（表设计的检验）
18、数据库的原子性
1、你的表是描述什么事物，明确表的信息对象主体
2、你的表是为了适合什么操作语句的设计的，面向操作设计
3、列的内容要不要原子性的切块，让查询直逼要害，列的设计的小块的程度要拿捏好
4、表必须是二维表，而且要根据不同的需求进行设计，表的设计与列的设计一样都要依赖与操作与需求
原子性的存在是为了切分适合的信息块，从而达到信息的最大化、最优化的使用，并不是无限切分就好，要看实际的应用。
但是由于原子性数据的表容易设计，而且运行的所需时间较短（查询信息的指向性比较强，同时也便于维护喝更新操作，但是却是在海量数据搜索面前有点乏力），所以对大量的数据处理的时候要加分效果。
1、同一列中不能存在多个类型相同的值，一列不能多值（除非是）
2、同一个表中也不能存在有多个存储相同类型数据的列
3、无重复的行，每列只存储一份同类型下的一份数据（列是单值的），无重复列名
19、规范化建表
让数据具有原子性是设计出规范化数据表的第一步（同时也是为了提高数据库的可读性），但是到后期你可能会因为数据量以及性能的问题打破这种规范化。
因为你的数据库投入使用后数据库的大小日益增大，设计规范化的表可以时刻保持你应对以后各种复杂的组合查询（主要是应对数据库过分膨胀后的，查询缓慢的问题）
规范化建表的优点：
1、可以减少表中重复数据的存储，减少数据库的大小；
2、因为数据库的切割使得你的查询的指向性更强，查找的数据少，查找得更加快速；
3、但是最后归根到底还是避免存储重复的数据节省我们的硬盘空间。
20、范式1NF2NF3NF4NF
1NF，主要是解决表中字段设计、信息与业务之间的关系的问题
1、数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
在第一范式（1NF）中表的每一行只包含一个实例的信息。
简而言之，第一范式就是无重复的列，而且单列无重复的值。
2、所有属性都必须是原子的——每个属性一个值。
一个实体的所有实例都必须包含同等数量的值。
一个实体的所有实例必须两两不相同。
2NF，主要是设定主键，解决数据库的数据冗余、更新、插入、更新的指向性的问题
1、要求数据库表中的每个实例或行必须可以被唯一地区分。
为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。
这个唯一属性列被称为主关键字或主键、主码、唯一关联。
引入主键约束primarykey，Constraintpk_ArticlePrimaryKey（Id）。
主键的设定也是有要求的。
主键默认不能为空，保持唯一性。
插入、更新删、除记录的时候必须指定主键的值。
要求主键必须简洁，主键值不能被修改。
2、实体必须满足第一范式。
所有属性描述的事实都必须与整个键相关，而不能只描述键的一个子集。
3NF，主要是解决排除部分列之间的依赖关系，利用了表与表的主外键关系解决。
1、要求实体数据记录（record）相对于外部完全依赖于主关键ID。
所谓完全依赖，是指在除主键外其他的属性列之间不存在对应依赖。
如果存在，那么这一依赖部分应该分离出来形成一个表（关联），一般抽离出这一部分的主依赖项的ID，把这一部分作为一个新的表记录，同时原表也存储此次依赖项ID，然后利用建立主外键关系，将一表分成两表，解决原表因为主次依赖项存在而造成的数据冗余等一系列问题（但是对于以查询操作为主、记录大量数据的表此范式就需要斟酌了）。
第二范式就是建立主键，与排除其他列对其属性列的依赖（消除部分列的依赖）。
2、实体必须满足第二范式。
如果每个非键属性描述的事实都与一个键属性相关，则实体满足第三范式所有属性描述的都必须是关于键的事实，除此之外别无其他。
4NF，主要是解决根据属性列的相似（不同的从属对象）与共同（相同的从属对象）信息的建表问题，即是表中所有列对外的一对多关系不受限制
1、因为比较抽象，即是A表中含有a1,a2两个属性列，B表中记录a1的信息（包含b1），C表中包含a2的信息（包含c1），但
升级会员 