因本人较懒，有些图片缺失和东西写的比较简单，请多包涵
编码规范： 命名的规范（入乡随俗）
编码习惯：
团队合作： 团队配置管理软件: (TFS SVN)
第一章
更改身份验证模式：服务器 属性 安全性
Master: 记录所有系统信息
Model: 模板
Msdb: 自动备份
Tempdb: 临时资源
常用的数据库对象： 表， 视图， 存储过程， 触发器， 用户自定义的函数， 索引等
文件构成：主数据文件（主数据文有且只有一个.mdf）， 次要数据文件.ndf(0 ~ n个)， 事务日志文件(增删（修改一条）改（修改两条）.ldf)（至少一个）
第二章
Catalog（数据库）
数据库就是数据的仓库
由表， 关系， 操作对象组成
数据存储在表中（数据以记录的形式存在表中， 记录是客观存在的一个事物， 表的列）
定义表： 先定义表结构（列或字段 数据类型 约束）（从对象来的）
定义类： 属性（字段）， 方法(class 类名 { })（从对象来的）
行（record） 列（colum） 字段（field）
常用数据类型
Unicode(用两个字节的长度 存储一个字符)
Char比varchar效率高
数据完整性： 实体完整性（对象： 数据行 要求所有行都有唯一的标识符（主键））（实施手段： 主键约束， 唯一约束， 标识列和GUID列），
域完整性（列， 字段）（数据类型， 格式， 值域范围， 是否允许为空）（限制数据类型， 检查约束， 外键约束， 默认值和非空约束），（字符， datatime加单引号）
参照完整性（引用完整性）（主外键关系）（不允许引用不存在的实体），
用户自定义完整性（多用触发器实现）（指数据的精确性和可靠性）
主键是数据行的唯一标识， 不重复的列才能当主键
业务主键 逻辑主键
主键选取原则（量少性， 稳定性）
GUID数据类型（uniqueidentifier）(newid()函数调用)
在字表中创建主外键关联(外键引用主表的主键)
（删除数据truncate， 主表用不了）
外键在子表中建 (自引用是允许的（递归中可以出现）)
第三章
SQL
T-SQL (微软的)， PL/SQL（Oracle的（甲骨文公司的））
数据查询语言： DQL（query： 查询）select（侧重对数据的操作）
数据操作语言： DML(insert, update, delete)（对数据库）
事物处理语言: TPL (begin transaction 开始事务， commit提交， rollback回滚)
数据定义语言: DDL (create（创建） drop（删除） alter（更新/修改)(定义数据库及其对象(侧重结构))
指针控制语言：CCL (declare cursor(声名游标)， fetch into(获取游标中的数据到变量中))
DB_ID（判断数据库）
创建表
1. 确定表结构
2. 确定每列的数据类型
3. 添加约束
4. ...
OBJECCT_ID（判断表）
ALTER TABLE
add constraint 添加约束
drop constraint
PK主键（primary key）
UQ唯一（unique）
CK检查（check）
DF默认
FK外键（foreign key references（引用））
第四章
insert: 插入语句
批量插入
--创建数据库
use master;
go
if DB_ID('StuDB')is not null drop database StuDB;
go
create database StuDB;
go
--创建表
use StuDB; --打开数据库
go
if OBJECT_ID('t_classInfo') is not null drop table t_classInfo; --t_bassic_classInfo TBa_ClassInfo
go
create table t_classInfo
ClassId char(3) not null primary key,
ClassName varchar(30) not null,
Comment varchar(150)
if OBJECT_ID('t_student') is not null drop table t_student;
go
create table t_student
StuId char(3) not null primary key,
StuName varchar(30) not null,
Birthday date,
Gender varchar(4),
ClassId char(3)
--添加约束
alter table t_student
add constraint DF_t_student_Gender default('男') for Gender;
alter table t_student
add constraint CK_t_student_Gender check(Gender in ('男', '女', '未知'));
alter table t_student
add constraint FK_t_student_ClassId foreign key (ClassId) references t_classInfo(ClassId);
--科目表 t_course(CourseId, CourseName, Comment)
if OBJECT_ID('t_course') is not null drop table t_course;
go
create table t_course
CourseId char(3) not null primary key,
CourseName varchar(30),
Comment varchar(150)
go
--成绩表t_score(Id, StuId, CourseId, Score)成绩大于等于0 小于等于100 兼容一位小数
if OBJECT_ID('t_score') is not null drop table t_score;
go
create table t_score
Id int identity primary key,
StuId char(3), --references t_student(StuId),
CourseId char(3), --references t_course(CourseId),
Score numeric(4,1)
go
--添加约束
alter table t_score
add constraint FK_t_score_StuId foreign key (StuId) references t_student(StuId);
alter table t_score
add constraint FK_t_score_CourseId foreign key (CourseId) references t_course(CourseId);
alter table t_score
add constraint CK_t_score_Score check(Score >= 0 and Score <= 100)
select * from t_classInfo
insert into dbo.t_classInfo(ClassId, ClassName) values('C01','Web班');
insert into dbo.t_classInfo(ClassId, ClassName) values('C02','.net班'),
('C03','java班'),
('C04','php班');
--把查询结果插入到现有表中(insert select(表必须预先存在))
insert into dbo.t_classInfo(ClassId, ClassName)
select '','' union
select '','' union
select '','';
--用查询结果生成新表
--不使用create语句创建一个表结构和t_classInfo表结构相同的表，里面不插入任何数据
select * from t_classInfo
select top 0 * into t_class from t_classInfo
drop table t_class
select * from t_class
--#表名（临时表（保存在服务器内存中 会话结束会自动销毁））
--修改数据 不加where就是修改全表数据
update t_classInfo set ClassName = 'DotNet班',comment = '123' where ClassId = 'C02';
--删除数据行
delete from t_classInfo where ClassId = 'C04';
--*代表所有字段
第五章
is null
is not null（正规）（获取非空数据）
not + 字段 + is null （对条件取反）
规格：在sql语句中 如果字符串中有”’”，需要使用两个”’”进行替代
distinct：去除重复记录
union: 将多个查询结果合并成一个，自动去除重复行(加all 保留重复行)
使用union 进行数据排序要写在最后一个select中 并且只能使用第一条语句中出现的字段
表联结
交叉连接（cross join）
内联结（inner join） 执行原理：首先执行cross join（进行笛卡儿积产生笛卡尔集）使用on中的条件进行过滤（等值连接）
外联结：
左外联结： (left join)
右外联结： (right join)
Left (right) join执行原理： 首先执行inner join，把表中都有的数据过滤出来，两个表中没有匹配到的数据看保留字 如果是left，则把左表中没有匹配到的数据放入结果集中，否则相反（没有匹配上的字段的值以null值进行填充）
等值连接丢数据
数据库进阶
数据库设计（Database Design）
Designer: 设计者
数据库设计，根据用户需求 在某一具体的数据库管理系统上，设计数据库的结构和建立数据库的过程
良好的设计可以（节省数据的存储空间，能保证数据的完整性，方便进行数据库应用系统的开发）
糟糕的数据库设计（数据冗余、存储空间浪费， 内存空间的浪费， 数据更新和插入的异常）
设计基本步骤:
用户需求已经明确
(建模 模型转换 规范化（三大范式）)（实体 Entity）
范式（
第一范式： 确保每列的原子性(最小的原子单元)(行与列的交叉点不能存在一组值)
第二范式： 满足第一范式并除主键列都依赖于主键（每个表只描述一件事）（复合主键： 不能存在部分依赖关系（与每个主键都有依赖关系））
第三范式：满足第二范式并处主键以外的其他列都不传递依赖于主键列
）（计算列属于冗余列 但是查询性能会提升的话允许保留（性能比规范化更重要））
（沟通 语言表达（少说 多看 多听） 逻辑 条理（停5秒 分点））
(工程实例)
第二章
变量：变量是可以在程序运行期间变化的程序元素； 变量在任何程序中充当存储信息的存储器，保存在内存中； 程序运行时，为了完成算法，某些变量会发生变化； 变量都有自己所属的数据类型
局部变量：用户定义的（只能在同一批处理中使用（局部变量不能跨越批处理））
全局变量：只读的（系统维护和控制赋值）
set 一次只能为一个变量赋值
select 一次可以为多个变量赋值， 可以通过查询语句 批量赋值
convert 数据类型转换函数（目标类型， 要转换的变量）
局部变量需要初始化后使用， 否则值为null， 会导致后续结果为空
通过查询语句为变量赋值，必须保证查询结果返回一条记录，否则获取的是最后一条记录
select @a = a, @b = b from Table_A
@@ERROR 最后一个T-SQL语句的错误号
@@IDENTITY 最后一次插入的标识值
@@ROWCOUNT 受上一个SQL语句影响的行数
@@FETCH_STATUS 返回针对连接当前打开的任何游标发出的上一条游标 FETCH 语句的状态
标量：一对一
子查询
法一：采用T-SQL变量实现
declare @avgMoney money;
法二：采用子查询
in 表示在列表中存在
exists 存在检测（检测是否存在）（返回值为true 或 false）
exists 子查询
go
派生表
把子查询当表来用
第五章
索引：常规的未作索引的表中的行不会以特定的顺序存储，无序状态的表被称为堆（不用查询每行数据，有效的提高查询性能）
索引包含从表或视图中一个或多个列生成的键，映射到指定数据的存储位置的指针
键存储在一个 B树 (balance tree)中 快速有效的查找键值关联的行
只会对索引行加锁， 提高并发检索速度
聚集索引：表中各行的物理顺序和键值的逻辑（索引）顺序相同（每个表只能有一个）（不允许空值）（选用以范围查询的列）（频繁更新的列和非唯一的列通常不选用聚集索引）（避免非常多或非常宽的键上创建聚集索引）（create table 和 alter table 创建主键会默认创建聚集索引）
非聚集索引：除聚集索引的索引 可以有多个 但要小于249个（）（指针指向位于索引页中指针对应的物理数据）（页级别上不包含全部数据）
唯一索引：允许空值： 只能有一个（不允许两行具有相同的索引值）（实施实体完整性）（在创建主键约束和唯一性约束时自动创建）
非聚集索引创建原则：索引不是越多越好 （当选择用在非聚集索引的列时，要查找在where join order by 子句中频繁引用的那些列）（一个索引最多可以使用16个列 所有索引键列组合起来不超过900字节）
经常插入操作的表上， 使用Fillfactor(填充因子)建索引减少页拆分
unique 唯一索引
clustered 聚集索引
nonclustered 非聚集索引
ix_ 代指索引
多列用 , 分隔 desc 降序 不加为升序
fillfactor = 填充因子 (百分比)
split(拆分)
视图
（封装）放在from子句中（可以当作表来用）
视图的作用：
1，可以筛选表中的行
2，防止未经许可的用户访问敏感数据
3，降低数据库的复杂度
4，将多个物理数据库抽象为一个逻辑数据库
create view (name) as (select语句) go
Exec sp_helptext ‘视图名称’;
encryption（加密）
可更新视图
insert update delete 等操作只能引用一个表中的列， 不允许直接修改基础表 只允许修改视图
可更新视图中不能包含聚合函数， select语句中不能使用group by 或 having子句， insert update delete 语句更新 发生变化的数据只能来自一个表， 必须包含基表的必填列， 视图不满足以上条件 可以只用 instead of触发器更新
第七章 事务
--创建银行信息表
if object_id('Account') is not null drop table Account;
go
create table Account
AccountId char(4) primary key,
Name varchar(20) not null,
Balance decimal(18, 2) not null
);
go
alter table Account add constraint CK_Account_Balance check (Balance >= 2);
insert into Account values
('0001', 'zhangsan', 2),
('0002', 'zhangsi', 20000);
select * from Account;
begin transaction
begin try
update Account set Balance = Balance + 20000 where AccountId = '0001';
update Account set Balance = Balance - 20000 where AccountId = '0002';
commit transaction;
end try
begin catch
rollback transaction
end catch
每条查询语句自成一个事务（transaction）
事务是作为单个逻辑工作单元执行的一系列操作， 是一个不可分割的工作逻辑单元
事务的属性：
原子性（Atomicity）必须是原子工作单元 不可分
一致性（Consistency）事务完成时， 数据处于一致状态
隔离性（Isolation）并发的事务时彼此隔离的 互不影响
永久性（Durability）事务完成就永久保存
begin transaction 开始事务
commit transaction 提交事务
rollback transaction 回滚事务
save tran 创建事务保存点(rollback把事务保存点之后的操作回滚 (回到事务保存点))
自动提交事务： 每条单独的T-SQL语句作为一个事务成功自动提交 错误自动回滚
显示事务： Begin transaction 指定事务的开始
隐性事务：set implicit transactions on 语句
事务的嵌套（父事务， 子事务）父事务回滚的时候以提交的子事务也会回滚
锁 是事务获取的一种控制资源 (保护数据资源， 防止其他食物对数据进行冲突或不兼容的访问)
数据并发访问时怎么保证数据的安全性
排它锁：修改数据时 事务会为所依赖的数据资源请求排它锁， 一旦授予， 事务将一直持有排它锁， 直至事务完成
共享锁：读取数据时， 事务默认会为所依赖的数据资源请求共享锁， 读操作一完成，就立即释放资源上的共享锁（不是长期独有占用）（表后边加with (xlock)）
存储过程的概念和分类
存储过程是一组预先编译好的T-SQL代码， 作为一个整体用于执行特定的操作， 存储过程属于数据库对象，它们存放在数据库中， 需要时用户可以调用， 存储过程首次执行的时候， Sql Server创建执行计划并把它存储在计划内存缓存中，然后Sql Server就能对存储过程的后续执行重用计划， 合同没有编译且没有准备好的等价即查询相比，重用计划使得存储过程提供了更快速更可靠的性能
存储过程是数据库对象
存储过程可以包含数据操作语句，变量，逻辑控制语句等
存储过程的优点：执行速度快，允许模块化程序设计，提高系统安全性，减少网络流通量
系统存储过程 (常用的：sp_databases, sp_tables, sp_columns, sp_help, sp_helpconstraint, sp_helpindex, sp_helptext)
用户自定义存储过程
有默认值就必须都有默认值 没有就都没有
row_number 生成连续递增行号
游标和触发器
使用的五个步骤
insensitive(使用游标时，对源表数据的更新不敏感，并且不允许通过游标修改源表数据)
scroll 设置fetch语句中参数first(上一条),last,prior,next,relative,absolute
read only 只读
update 定义可通过游标更新的列
fetch into select列的数据和变量列表数据一致
@@fetch_status全局变量 等于 0 是表示成功读取到数据行了
游标默认指向第一条记录之前
游标是逐条处理的工具
动态SQL EXEC(‘语句’)
触发器（跨表的check）（一种特殊的存储过程）（强制的约束）
完成复杂的数据约束，检查所做的sql操作是否允许，修改其它数据表里的数据，调用更多的存储过程，返回自定义的错误信息，更改原本要操作的sql语句，防止数据表结构被更改或数据表被删除
优点：自动的，层叠更改，强制限制，可以用于跟踪
dml触发器：表或表的视图上执行可以触发，触发器和触发的语句在触发器内单个事务对待，检测到的错误，自动回滚
after触发器：记录已经更改完之后触发，检测到错误，可以回滚本次操作
instead of:取代原本要进行的操作
create trigger(创建触发器)
权限默认为数据库所有者，不得转让，严重减慢数据修改操作的性能，不能对系统表和临时表创建，不记录日志的更新不会引起dml触发器的触发
encryption 加密（for | after 都代表after触发器）
inserted表（存储着insert操作插入的行和update操作的新行） deleted表（村粗这update操作的旧行和delete操作删除的行）（两个逻辑表，有系统来维护，不允许用户直接修改）
第十章 函数
函数是用于封装频繁执行的逻辑的例程
存储过程只能用exec调用 不能放from后边当表来用（可以有返回值，仅限于整形）
任何必须执行的代码都可调用函数
函数是由一条或多条T-SQL语句组成的例程，可用于封装代码以便进行复用。函数接受零个或多个输入参数，并返回标量值或表，函数不支持输出参数
函数可以返回标量值，可以返回表
udf 用户定义函数（标量函数（（用于将当前的值转换为新值，根据参数进行复杂的查找）返回单个数据值，类型在returns子句中定义），
内联表值函数（返回一个表，单个select语句的结果），
多语句表值函数（由一条或多条T-SQL语句构建的表（和存储过程不同，多语句表值函数可以在select语句的from子句中进行引用）））
create function (函数名) returns(返回值类型)(子句)begin return (函数体) end
内联表值函数：通常使用视图的任何地方使用内联表值函数（内联用户定义函数）
create function ruturns + 变量名(未来返回的表) + table(类型) + (表结构 类型字段的定义) + begin return(后边什么也不加) end