数据库概念.docx
《数据库概念.docx》由会员分享，可在线阅读，更多相关《数据库概念.docx（16页珍藏版）》请在冰点文库上搜索。
数据库概念
使用T-SQL语句创建数据库和表
一，约束的目的：
确保表中数据的完整性
常用的约束类型：
主键约束（PrimaryKeyConstraint）：
要求主键列数据唯一，并且不允许为空
唯一约束（UniqueConstraint）：
要求该列唯一，允许为空，但只能出现一个空值。
检查约束（CheckConstraint）：
某列取值范围限制、格式限制等，如有关年龄的约束
默认约束（DefaultConstraint）：
某列的默认值，如我们的男性学员较多，性别默认为“男”
外键约束（ForeignKeyConstraint）：
用于两表间建立关系，需要指定引用主表的哪列
二，语法
ALTERTABLE表名
ADDCONSTRAINT约束名
约束名的取名规则推荐采用：
约束类型_约束字段
主键（PrimaryKey）约束：
如PK_DeviceId
唯一（UniqueKey）约束：
如UQ_DeviceName
默认（DefaultKey）约束：
如DF_Status
检查（CheckKey）约束：
如CK_Pwd
外键（ForeignKey）约束：
如FK_DeviceCId
SELECT之查询机制
一．SELECT<列名>
FROM<表名>
[WHERE<查询条件表达式>]
[GROUPBY<分组条件表达式>]
[HAVING<筛选条件表达式>]
[ORDERBY<排序的列名>[ASC或DESC]]
二．批量插入
1.将查询结果插入到新表中（表不能预先存在），语法：
selectintofrom
2.将查询结果插入到现有表中（表必须预先存在），语法；INSERTINTOSELECTFROM
数据的增删改
一.数据插入
1.语法：
INSERT[INTO]<表名>[列名]VALUES<值列表>
2.注意事项
不能为标识列指定值
每个数据值的数据类型、精度和小数位数必须与相应的列匹配
如果在设计表的时候就指定了某列不允许为空，则必须插入数据
插入的数据项，要求符合检查约束的要求
具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值
主键列的值不能重复
插入的外键列的数值必须在主表中存在
提示：
varchar等字符数据、日期数据用半角的'单引号引起来，数值类型不需要
二.数据更新UPDATE<表名>SET<列名=更新值>
[WHERE<更新条件>]
三．数据删除DELETEFROM<表名>[WHERE<删除条件>]
1.注意事项
删除语句中不能使用*，是以行为单位删除，*号代表所有列
表中数据被其它表引用时，数据不能删除
注意删除的条件（UPDATE也一样），否则可能把其他重要数据删除
四．清空表
TRUNCATETABLE表名
认识SQL和T-SQL
一.SQL（StructuredQueryLanguage）结构化查询语言：
它是查询和管理关系型数据库管理系统（RDBMS）中的数据而专门设计的一种标准语言。
T-SQL=Transact-SQL
T-SQL是SQL的加强版
对功能进行了扩充：
如变量、流程控制、功能函数
二.SQL组成
数据查询语言（DQL）：
数据检索语句SELECT
数据操作语言（DML）:
数据的增删改INSERT、UPDATE、DELETE等
事务处理语言（TPL）：
用于事务控制BEGINTRANSACTION、COMMIT、ROLLBACK等
数据控制语言（DCL）:
用来控制存取许可、存取权限GRANT、REVOKE等
数据定义语言（DDL）：
用于定义数据库及其对象CREATETABLE、DROPTABLE等
指针控制语言（CCL）：
像DECLARECURSOR，FETCHINTO等用于对一个或多个表单独行的操作
三．DBA与程序员
数据库系统管理员（DatabaseAdministrator，DBA）：
对数据库系统进行管理、维护、调优，调整安全性是数据库系统管理员的职责
程序员：
使用数据库进行程序开发的人员是程序员
程序员与DBA的区别：
DBA大部分时间是在监视系统、备份/恢复系统、优化系统，而开发人员则无需精通这些技能
开发人员大部分时间是在用SQL实现业务逻辑
二者知识的重合点就是SQL
一个开发人员如果不熟悉SQL的话就无法很好的实现业务逻辑，而一个DBA如果不熟悉SQL的话就无法完成数据库的调优工作
SELECT之联接查询
一．分类
交叉联接（CROSSJOIN）
内联接（INNERJOIN）
外联接
——左外联接（LEFTJOIN）
——右外联接（RIGHTJOIN）
——完整外联接（FULLJOIN）
二，实现步骤
1.交叉联接：
交叉联接只实现一个逻辑查询步骤（笛卡尔积）
2.内联接：
首先对两个输入表进行笛卡尔积运算，然后根据用户指定的条件对结果进行过滤
3.多表联接：
当FROM子句中包含多个表运算符时，表运算符在逻辑上是按照从左到右的顺序进行处理；第一个表运算符的结果作为第二个表运算符的输入；第二个表运算符的结果将作为第三个表运算符左边的输入；以此类推……
4.外联接：
将一个表标记为“保留的”表:
LEFT关键字表示左边表的行是保留的，RIGHT关键字表示右边表的行是保留的；对两个输入表进行笛卡尔积运算；根据ON条件对结果进行过滤；
识别保留表中按照ON条件在另一个表找不到匹配的那些行，再把这些行添加到联接的前两个步骤生成的结果表中，对于来自联接的非保留表的那些列，采用NULL值进行填充
三，语法
1.内联接
SELECTFROMINNERJOINON
2.外联接
SELECTFROMLEFTJOINON
SELECTFROMLEFTJOINON
常用函数
一，字符串函数
LEN,LOWER,UPPER,LTRIM,RTRIM,RIGHT,REPLACE
二，日期函数
DATEDIFF,DATEADD,DATEPART,DATENAME,CONVERT
三，数学函数
CLEING,FLOOR,ROUND,POWER
四，系统函数
ROWNUMBER,CONVERT
运算符、通配符和逻辑表达式
一．1.在SELECT子句中可以指定常量列
2.NULL与任何值运算的结果都是NULL，使用ISNULL函数可以进行NULL的特殊处理
3.使用+号进行字符串的拼接，字段之间或对单个字段可以进行运算
4.IN用于检查一个值是否与一组元素中的至少一个相等
5.BETWEEN用于检查一个值是否在指定的范围内
6.LIKE用于检查一个字符串值是否与指定的模式匹配
7.DISTINCT关键字可从SELECT语句的结果中消除重复的行。
如果没有指定DISTINCT，将返回所有行，包括重复的行
8.使用UNION运算符可以组合结果集：
指定合并多个结果集并将其作为单个结果集返回
（1）UNION使用注意事项
每个结果集必须要有相同的列数
每个结果集的列必须类型相容，也就是说结果集的每个对应列的数据类型必须相同或者能够转换为同一种数据类型
如果需要对结果进行排序，排序条件必须放在最后一个SELECT语句中
UNION会过滤重复数据，如果想保留重复数据，请使用UNIONALL
子查询
一．最外层查询的结果集会返回给调用者，称为外部查询，嵌套在其他查询中的查询称为子查询
通过使用子查询，可以避免在查询解决方案中把操作分成多个步骤，并在变量中保存中间查询结果的需要
二.独立子查询
独立子查询是独立于其外部查询的子查询
独立子查询调试起来非常方便，其代码可以独立出来运行，并确保他能够实现默认的功能
在逻辑上，独立子查询在执行外部查询之前只要先执行一次，接着外部查询再使用子查询的结果继续进行查询
根据查询返回的结果数量，独立子查询又分为独立标量子查询和独立多值子查询
三．相关子查询
相关子查询是指引用了外部查询中出现的表的列的子查询
相关子查询用依赖于外部查询，不能独立的调用它
在逻辑上，子查询会为每个外部行单独计算一次
相关子查询的查询效率低
四．表表达式
表表达式是一种命名的查询表达式，代表一个有效的关系表
1.SQLServer支持4中类型的表表达式：
派生表
公用表表达式
视图
内联表值函数
派生表
派生表（也称为表子查询）是在外部查询的FROM子句中定义的
派生表的存在范围为定义它的外部查询，只要外部查询一结束，派生表也就不存在了
定义派生表的查询语句要写在一对圆括号内，后便跟着AS子句和派生表的名称
索引
索引：
是SQLServer编排数据的内部方法。
它为SQLServer提供一种方法来编排查询数据
索引页：
数据库中存储索引的数据页
数据库中的索引使您可以快速找到表或索引视图中的特定信息
索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针
这些键存储在一个B树（BalanceTree）结构中，使SQLServer可以快速有效地查找与键值关联的行
表扫描时会对表加锁，如果有索引的话则在索引行上加锁，在并发访问时速度会很快
索引的分类：
聚集索引（ClusteredIndex）：
表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引（Non-ClusteredIndex）：
非聚集索引指定表的逻辑顺序。
数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。
可以有多个，小于249个
唯一索引（UniqueIndex）：
唯一索引不允许两行具有相同的索引值
一．聚集索引的选择
聚集索引的选择很关键，因为一个表只能拥有一个聚集索引
聚集索引经常选用以范围来查询的列
频繁更新的列以及非唯一列通常不是聚集索引键的好选择
应该避免在非常多或非常宽（许多字节的）的键上创建聚集索引
当使用CREATETABLE或ALTERTABLE在列上创建主键时，主键也会创建聚集索引
二.非聚集索引
索引不是越多越好
在重复列上不要创建索引
当选择用在非聚集索引的列时，要查找在WHERE、JOIN以及ORDERBY子句中频繁引用的那些列
一个索引最多可以使用16个键列，只要所有索引键列组合起来不超过900字节就可以
不可以在索引键中使用大值对象数据类型，包括VARCHAR（MAX）、NVARCHAR（MAX）、VARBINARY（MAR）、XML、NTEXT、TEXT以及IMAGE数据类型
三.惟一索引
按照实现的功能，有一类索引被称作“惟一索引”。
惟一索引要求所有数据行中任意两行的被索引列不能存在重复值，它既可采用聚集索引的结构，又可以采用非聚集索引的结构。
惟一索引的特征如下：
不允许两行具有相同的索引值。
实施实体完整性。
在创建主键约束和惟一性约束时自动创建
不能对表中的计算列建立索引。
四.语法
CREATE[NONCLUSTERED][CLUSTERED][UNIQUE]INDEXIX_表名_字段
on表名（字段）
[WITH]
[FILLFACTION=填充因子]
视图
一.视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
视图中并不存放数据，而是存放在视图所引用的原始表（基表）中
同一张原始表，根据不同用户的不同需求，可以创建不同的视图
二.视图的用途
筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库
三.语法
CREATEVIEWview_nameAS
四．在定义视图的SELECT列表中不能包含聚合函数。
在定义视图的SELECT语句中不能使用GROUPBY或HAVING子句。
单个INSERT、UPDATE或DELETE语句更新视图时，发生变化的数据只能来自一个源表。
该视图必须包含源表的必填列
事务
一.事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作
这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元
二.事务的特性
原子性（Atomicity）：
事务必须是原子工作单元。
事务的各步操作是不可分的；要么都执行，要么都不执行
一致性（Consistency）：
当事务完成时，数据必须处于一致状态
隔离性（Isolation）：
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
永久性（Durability）：
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性
三.T-SQL使用下列语句来管理事务：
开始事务：
BEGINTRANSACTION
提交事务：
COMMITTRANSACTION
回滚（撤销）事务：
ROLLBACKTRANSACTION
锁
锁是事务获取的一种控制资源，用于保护数据资源，防止其他事务对数据进行冲突或不兼容的访问。
熟悉两种主要的锁模式
排它锁：
当试图修改数据时，事务会为所依赖的数据资源请求排它锁，一旦授予，事务将一直持有排它锁，直至事务完成。
共享锁：
当试图读取数据时，事务默认会为所依赖的数据资源请求共享锁，读操作一完成，就立即释放资源上的共享锁。
防止数据覆盖更新：
WITH（XLOCK）
存储过程
一.在SQLServer中定义的过程被称为存储过程。
存储过程是一组预先编译好的T-SQL代码，作为一个整体用于执行特定的操作。
存储过程属于数据库对象，它们存放在数据库中，需要时用户可以调用
二.优点
执行速度更快
允许模块化程序设计
提高系统安全性
减少网络流通量
三．语法
CREATEPROC[EDURE]存储过程名
@参数1数据类型=默认值OUTPUT,
……,
@参数n数据类型=默认值OUTPUT
AS
SQL语句
GO
四．注意
参数分为输入参数、输出参数
输入参数允许有默认值
存储过程的返回值为整形
用户自定义函数
一.函数是用于封装频繁执行的逻辑的例程。
任何必须执行的代码都可调用函数，而无需重复所有的函数逻辑
函数是由一条或多条T-SQL语句组成的例程，可用于封装代码以便进行重用。
函数接受零个或多个输入参数，并返回标量值或表。
函数不支持输出参数
二.T-SQL用户定义函数（UDF）分成3类：
标量函数：
标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的
内联表值函数：
内联表值函数返回一个表，该表是单个SELECT语句的结果。
它类似于视图，但是比视图提供了更多的灵活性，因为可向函数提供参数
多语句表值函数：
语句表值函数返回由一条或多条T-SQL语句构建的表并且类似于存储过程。
与存储过程不同的是，多语句表值函数可以在SELECT语句的FROM子句中进行引用，就像它是视图或表一样
三．语法
CREATEFUNCATION函数名（）
RETURN返回值类型AS
BEGIN
函数体
Return标量短语
END
四．自定义函数调用时要制定用户
游标
一.使用游标包括以下五个步骤
声明游标DECLARE游标名CURSORFORSELECT语句
打开游标OPEN
提取数据FETCH
关闭游标CLOSE
释放游标DEALLOCATE
二.语法
DECLAREcursor_name[INSENSITIVE][SCROLL]
CURSORFORselect_statement
[FORREADONLY|UPDATE[OFcolumn_name[,...]]]
触发器
一.触发器实际上就是一种特殊类型的存储过程，它在一定操作时自动触发执行
二.触发器的功能
完成更复杂的数据约束
检查所做的SQL操作是否允许
修改其它数据表里的数据
调用更多的存储过程
返回自定义的错误信息
更改原本要操作的SQL语句
防止数据表结构被更改或数据表被删除
三．类型
DML触发器：
DML触发器是当数据库服务器中发生数据操作语言（DataManipulationLanguage）事件时执行的存储过程
DDL触发器：
DDL触发器是在响应数据定义语言（DataDefinitionLanguage）事件时执行的存储过程
登录触发器：
登录触发器将为响应LOGON事件而激发存储过程。
与SQLServer实例建立用户会话时将引发此事件
四．优点
触发器是自动的：
它们在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活
触发器可以通过数据库中的相关表进行层叠更改
触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。
与CHECK约束不同的是，触发器可以引用其它表中的列
触发器可以用于跟踪
五．DML触发器
1.当数据库中发生数据操作语言（DML）事件时将调用DML触发器
2.DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。
DML触发器可以查询其他表，还可以包含复杂的Transact-SQL语句
3.将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。
如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。
六．DML触发器类型
1.After触发器：
这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用RollbackTransaction语句来回滚本次的操作
2.InsteadOf触发器：
这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作
七．DML触发器工作原理
1.After触发器的工作原理：
After触发器是在记录更变完之后才被激活执行
2.InsteadOf触发器的工作原理：
InsteadOf触发器与After触发器不同。
After触发器是在Insert、Update和Delete操作完成后才激活的，而InsteadOf触发器，是在这些操作进行之前就激活了并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。
八．创建DML触发器注意事项
1.CREATETRIGGER语句必须是批处理中的第一条语句
2.创建DML触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户
3.长时间的运行触发器会严重减慢数据修改操作
4.只能在当前数据库中创建DML触发器
5.不能对临时表或系统表创建DML触发器
6.不记录日志的更新不会引起DML触发器的触发
九．语法
1.AFTER触发器
CREATETRIGGERTRG_NAME
ON表名或视图名
{FOR|AFTER}
{[INSERT]|[UPDATE]|[DELETE]}
AS
2.INSTEADOF触发器
CREATETRIGGERTRG_NAME
ON表名或视图名
INSTEADOF{[INSERT]|[UPDATE]|[DELETE]}
AS
升级会员 