数据库概念.docx

1、数据库概念 使用T-SQL语句创建数据库和表一，约束的目的：确保表中数据的完整性常用的约束类型：主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的哪列 二，语法 ALTER T

2、ABLE 表名 ADD CONSTRAINT 约束名约束名的取名规则推荐采用：约束类型_约束字段主键（Primary Key）约束：如 PK_DeviceId 唯一（Unique Key）约束：如 UQ_DeviceName 默认（Default Key）约束：如 DF_Status 检查（Check Key）约束：如 CK_Pwd 外键（Foreign Key）约束：如 FK_DeviceCId SELECT 之 查询机制一 SELECT FROM WHERE GROUP BY HAVING ORDER BY ASC或DESC二 批量插入1. 将查询结果插入到新表中（表不能预先存在），语法：

3、select into from 2. 将查询结果插入到现有表中（表必须预先存在），语法；INSERT INTO SELECT FROM 数据的增删改一.数据插入1.语法：INSERT INTO 列名 VALUES 2.注意事项不能为标识列指定值 每个数据值的数据类型、精度和小数位数必须与相应的列匹配 如果在设计表的时候就指定了某列不允许为空，则必须插入数据 插入的数据项，要求符合检查约束的要求 具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值 主键列的值不能重复 插入的外键列的数值必须在主表中存在 提示：varchar等字符数据、日期数据用半角的单引号引起来，数值类型不需

4、要 二. 数据更新 UPDATE SET WHERE 三数据删除DELETE FROM WHERE 1.注意事项删除语句中不能使用*，是以行为单位删除，*号代表所有列 表中数据被其它表引用时，数据不能删除 注意删除的条件（UPDATE也一样），否则可能把其他重要数据删除 四清空表 TRUNCATE TABLE 表名 认识SQL 和 T-SQL一.SQL(Structured Query Language)结构化查询语言 ：它是查询和管理关系型数据库管理系统(RDBMS)中的数据而专门设计的一种标准语言。 T-SQL = Transact-SQLT-SQL是SQL的加强版对功能进行了扩充：如变量

5、、流程控制、功能函数二.SQL组成数据查询语言(DQL)：数据检索语句SELECT 数据操作语言(DML）:数据的增删改INSERT、 UPDATE 、DELETE等 事务处理语言（TPL）：用于事务控制BEGIN TRANSACTION、COMMIT、ROLLBACK等 数据控制语言(DCL）:用来控制存取许可、存取权限GRANT、REVOKE 等 数据定义语言（DDL）：用于定义数据库及其对象CREATE TABLE 、DROP TABLE 等 指针控制语言（CCL）：像DECLARE CURSOR，FETCH INTO等用于对一个或多个表单独行的操作三DBA与程序员数据库系统管理员（Da

6、tabase Administrator，DBA）：对数据库系统进行管理、维护、调优，调整安全性是数据库系统管理员的职责 程序员：使用数据库进行程序开发的人员是程序员 程序员与DBA的区别： DBA 大部分时间是在监视系统、备份/恢复系统、 优化系统，而开发人员则无需精通这些技能 开发人员大部分时间是在用SQL 实现业务逻辑 二者知识的重合点就是SQL一个开发人员如果不熟悉SQL的话就无法很好的实现业务逻辑，而一个DBA 如果不熟悉SQL的话就无法完成数据库的调优工作 SELECT 之 联接查询一分类交叉联接(CROSS JOIN)内联接(INNER JOIN)外联接左外联接 (LEFT JO

7、IN)右外联接 (RIGHT JOIN)完整外联接(FULL JOIN) 二，实现步骤1.交叉联接：交叉联接只实现一个逻辑查询步骤（笛卡尔积） 2.内联接：首先对两个输入表进行笛卡尔积运算 ，然后根据用户指定的条件对结果进行过滤 3.多表联接：当FROM子句中包含多个表运算符时，表运算符在逻辑上是按照从左到右的顺序进行处理 ；第一个表运算符的结果作为第二个表运算符的输入 ；第二个表运算符的结果将作为第三个表运算符左边的输入 ；以此类推4.外联接：将一个表标记为“保留的”表:LEFT关键字表示左边表的行是保留的，RIGHT关键字表示右边表的行是保留的； 对两个输入表进行笛卡尔积运算 ；根据ON条

8、件对结果进行过滤； 识别保留表中按照ON条件在另一个表找不到匹配的那些行，再把这些行添加到联接的前两个步骤生成的结果表中，对于来自联接的非保留表的那些列，采用NULL值进行填充 三，语法1. 内联接SELECT FROM INNER JOIN ON 2. 外联接SELECT FROM LEFT JOIN ONSELECT FROM LEFT JOIN ON 常用函数一， 字符串函数LEN,LOWER,UPPER,LTRIM,RTRIM,RIGHT,REPLACE二， 日期函数DATEDIFF,DATEADD,DATEPART,DATENAME,CONVERT三， 数学函数CLEING,FLOO

9、R,ROUND,POWER四， 系统函数ROW NUMBER,CONVERT运算符、通配符和逻辑表达式一 1.在SELECT 子句中可以指定常量列 2.NULL与任何值运算的结果都是NULL，使用ISNULL函数可以进行NULL的特殊处理 3.使用+号进行字符串的拼接 ，字段之间或对单个字段可以进行运算 4.IN用于检查一个值是否与一组元素中的至少一个相等 5.BETWEEN用于检查一个值是否在指定的范围内 6.LIKE 用于检查一个字符串值是否与指定的模式匹配 7.DISTINCT 关键字可从 SELECT 语句的结果中消除重复的行。如果没有指定 DISTINCT，将返回所有行，包括重复的行

10、 8.使用UNION运算符可以组合结果集：指定合并多个结果集并将其作为单个结果集返回 （1）UNION使用注意事项每个结果集必须要有相同的列数 每个结果集的列必须类型相容， 也就是说结果集的每个对应列的数据类型必须相同或者能够转换为同一种数据类型 如果需要对结果进行排序，排序条件必须放在最后一个SELECT语句中 UNION会过滤重复数据，如果想保留重复数据，请使用UNION ALL子查询一最外层查询的结果集会返回给调用者，称为外部查询，嵌套在其他查询中的查询称为子查询 通过使用子查询，可以避免在查询解决方案中把操作分成多个步骤，并在变量中保存中间查询结果的需要 二.独立子查询独立子查询是独立

11、于其外部查询的子查询 独立子查询调试起来非常方便，其代码可以独立出来运行，并确保他能够实现默认的功能 在逻辑上，独立子查询在执行外部查询之前只要先执行一次，接着外部查询再使用子查询的结果继续进行查询 根据查询返回的结果数量，独立子查询又分为独立标量子查询和独立多值子查询 三相关子查询相关子查询是指引用了外部查询中出现的表的列的子查询 相关子查询用依赖于外部查询，不能独立的调用它 在逻辑上，子查询会为每个外部行单独计算一次 相关子查询的查询效率低 四表表达式表表达式是一种命名的查询表达式，代表一个有效的关系表 1.SQL Server支持4中类型的表表达式： 派生表 公用表表达式 视图 内联表值

12、函数 派生表派生表（也称为表子查询）是在外部查询的FROM子句中定义的 派生表的存在范围为定义它的外部查询，只要外部查询一结束，派生表也就不存在了 定义派生表的查询语句要写在一对圆括号内，后便跟着AS子句和派生表的名称 索引索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 索引页：数据库中存储索引的数据页数据库中的索引使您可以快速找到表或索引视图中的特定信息索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针 这些键存储在一个B 树(Balance Tree)结构中，使 SQL Server 可以快速有效地查找与键值关联

13、的行 表扫描时会对表加锁，如果有索引的话则在索引行上加锁，在并发访问时速度会很快 索引的分类：聚集索引(Clustered Index)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个 非聚集索引(Non-Clustered Index)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个 唯一索引(Unique Index)：唯一索引不允许两行具有相同的索引值 一聚集索引的选择聚集索引的选择很关键，因为一个表只能拥有一个聚集索引 聚集索引经常选用以范围来查询的列 频繁更新的列以及非唯一列通常不是聚

14、集索引键的好选择 应该避免在非常多或非常宽（许多字节的）的键上创建聚集索引 当使用CREATE TABLE或ALTER TABLE在列上创建主键时，主键也会创建聚集索引二.非聚集索引索引不是越多越好 在重复列上不要创建索引 当选择用在非聚集索引的列时，要查找在WHERE 、JOIN以及ORDER BY子句中频繁引用的那些列 一个索引最多可以使用16个键列，只要所有索引键列组合起来不超过900字节就可以 不可以在索引键中使用大值对象数据类型，包括VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAR)、XML、NTEXT、TEXT以及IMAGE数据类型 三.惟一索引按照

15、实现的功能，有一类索引被称作“惟一索引”。 惟一索引要求所有数据行中任意两行的被索引列不能存在重复值，它既可采用聚集索引的结构，又可以采用非聚集索引的结构。 惟一索引的特征如下： 不允许两行具有相同的索引值。 实施实体完整性。 在创建主键约束和惟一性约束时自动创建 不能对表中的计算列建立索引。 四.语法 CREATE NONCLUSTEREDCLUSTEREDUNIQUE INDEX IX_表名_字段 on 表名（字段）WITHFILLFACTION=填充因子视图一.视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上视图中并不存放数据，而是存放在视

16、图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图 二.视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库 三. 语法CREATE VIEW view_name AS 四在定义视图的SELECT列表中不能包含聚合函数。 在定义视图的SELECT语句中不能使用GROUP BY或HAVING子句。 单个INSERT、UPDATE或DELETE语句更新视图时，发生变化的数据只能来自一个源表。 该视图必须包含源表的必填列事务一.事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整

17、体一起向系统提交，要么都执行、要么都不执行 事务是一个不可分割的工作逻辑单元二.事务的特性原子性（Atomicity）：事务必须是原子工作单元。事务的各步操作是不可分的；要么都执行，要么都不执行一致性（Consistency）：当事务完成时，数据必须处于一致状态隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性三.T-SQL使用下列语句来管理事务：开始事务：BEGIN TRANSACTION提交事务：COMMIT

18、TRANSACTION回滚（撤销）事务：ROLLBACK TRANSACTION锁锁是事务获取的一种控制资源，用于保护数据资源，防止其他事务对数据进行冲突或不兼容的访问。 熟悉两种主要的锁模式 排它锁：当试图修改数据时，事务会为所依赖的数据资源请求排它锁，一旦授予，事务将一直持有排它锁，直至事务完成。 共享锁：当试图读取数据时，事务默认会为所依赖的数据资源请求共享锁，读操作一完成，就立即释放资源上的共享锁。 防止数据覆盖更新：WITH (XLOCK)存储过程一.在SQL Server中定义的过程被称为存储过程。存储过程是一组预先编译好的T-SQL代码，作为一个整体用于执行特定的操作。存储过程属

19、于数据库对象，它们存放在数据库中，需要时用户可以调用 二.优点 执行速度更快 允许模块化程序设计 提高系统安全性减少网络流通量三语法 CREATE PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT, , 参数n 数据类型 = 默认值 OUTPUT AS SQL语句 GO四注意参数分为输入参数、输出参数 输入参数允许有默认值 存储过程的返回值为整形用户自定义函数一.函数是用于封装频繁执行的逻辑的例程。任何必须执行的代码都可调用函数，而无需重复所有的函数逻辑 函数是由一条或多条T-SQL 语句组成的例程，可用于封装代码以便进行重用。函数接受零个或多个输入参数，并返回标量值

20、或表。函数不支持输出参数 二.T-SQL用户定义函数(UDF)分成3类： 标量函数：标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的 内联表值函数：内联表值函数返回一个表，该表是单个SELECT 语句的结果。它类似于视图，但是比视图提供了更多的灵活性，因为可向函数提供参数 多语句表值函数：语句表值函数返回由一条或多条T-SQL 语句构建的表并且类似于存储过程。与存储过程不同的是，多语句表值函数可以在 SELECT 语句的FROM 子句中进行引用，就像它是视图或表一样 三语法 CREATE FUNCATION 函数名（） RETURN 返回值类型 AS BEGIN 函数体 Ret

21、urn 标量短语 END四自定义函数调用时要制定用户 游标一.使用游标包括以下五个步骤声明游标 DECLARE 游标名 CURSOR FOR SELECT语句打开游标 OPEN提取数据 FETCH关闭游标 CLOSE释放游标 DEALLOCATE 二.语法 DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,. 触发器一.触发器实际上就是一种特殊类型的存储过程，它在一定操作时自动触发执行 二.触发器的功能完成更复杂的数据约束 检查所做的

22、SQL操作是否允许 修改其它数据表里的数据 调用更多的存储过程 返回自定义的错误信息 更改原本要操作的SQL语句 防止数据表结构被更改或数据表被删除 三类型 DML 触发器： DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程 DDL 触发器：DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程登录触发器：登录触发器将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件 四优点触发器是自动的：它们在对表的数据作了任何修改（比如手

23、工输入或者应用程序采取的操作）之后立即被激活 触发器可以通过数据库中的相关表进行层叠更改 触发器可以强制限制，这些限制比用CHECK 约束所定义的更复杂。与 CHECK 约束不同的是，触发器可以引用其它表中的列 触发器可以用于跟踪五DML触发器1.当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器 2.DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句 3.将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间

24、不足），则整个事务即自动回滚。六DML触发器类型1.After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作 2.Instead Of触发器：这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作 七 DML触发器工作原理1.After触发器的工作原理 ：After触发器是在记录更变完之后才被激活执行 2.Instead Of触发器的

25、工作原理 ：Instead Of触发器与After触发器不同。After触发器是在Insert、Update和Delete操作完成后才激活的，而Instead Of触发器，是在这些操作进行之前就激活了并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。 八创建DML触发器注意事项1.CREATE TRIGGER语句必须是批处理中的第一 条语句 2.创建DML触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户 3.长时间的运行触发器会严重减慢数据修改操作 4.只能在当前数据库中创建DML触发器 5.不能对临时表或系统表创建DML触发器 6.不记录日志的更新不会引起DML触发器的触发 九语法 1.AFTER 触发器 CREATE TRIGGER TRG_NAME ON 表名或视图名 FOR | AFTER INSERT|UPDATE|DELETE AS 2.INSTEAD OF 触发器 CREATE TRIGGER TRG_NAME ON 表名或视图名 INSTEAD OF INSERT|UPDATE|DELETE AS