一图胜“十”言：SQL Server 数据库总结 

一个大概的总结 

经过一段时间的学习，也对数据库有了一些认识。 
　　数据库基本是由表，关系，操作组成；对于初学者首先要学的： 

　　　1.数据库是如何存储数据的 
　　　　表，约束，触发器 
　　　2.数据库是如何操作数据的 
　　　　insert，update，delete T-sql 函数 存储过程 触发器 
　　　3.数据库是如何显示数据的                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
　　　　select 

SQLServer数据库学习总结 

1.SQL基础 
SQL Server2000安装、配置，服务器启动、停止，企业管理器、查询分析器 
第一代数据库--网状数据库和层次数据库；第二代数据库--关系数据库 
数据库（DB）；数据库管理系统（DBMS）；数据库系统（DBS） 
SQL Server 20## 提供了不同版本：企业版、标准版、个人版、开发版 

SQL Server中的数据类型：整数：int,smallint,tinyint,bigint;浮点数：real,float,decimal;二进制：binary,varbinary;逻辑：bit；字符：char,nchar,varchar,nvarchar；文本和图形：text,ntext,image；日期和时间：datetime,smalldatetime；货币：money,smallmoney 

数据库的创建和删除；数据库表的创建、修改和删除 

数据完整性：实体完整性：Primary Key,Unique Key,Unique Index,Identity Column；域完整性：Default,Check,Foreign Key,Data type，Rule；参照完整性：Foreign Key,Check,Triggers,Procedure；用户定义完整性：Rule,Triggers,Procedure;Create Table中得全部列级和表级约束 

SQL Server中有5种约束：主键约束（Primary Key Constraint）、默认约束（Default Constraint）、检查约束（Check Constraint）、唯一性约束（Unique Constraint）、外键约束（Foreign Key Constraint）. 

关系图 

数据库设计的步骤：需求分析、概念结构设计、逻辑结构设计、数据库物理设计、数据库实施、数据库运行和维护 

两个实体之间的联系：一对一（1:1）、一对多（1：n）、多对多（m：n） 

实体关系模型 -- E-R图 

数据库规范化：将数据库的结构精简为最简单的形式；从表中删除冗余列；标识所有依赖于其他数据库的数据。 

数据库三范式：第一范式就是无重复的列；第二范式就是非主属性非部分依赖于主关键字；第三范式就是属性不依赖于其他非主属性 

2.SQL语句 
SQL全称是“结构化查询语言（Structured Query Language）” 

SQL的4个部分： 
数据定义语言DDL（Data Definition Language）用来定义数据的结构：create、alter、drop。 
数据控制语言DCL（Data Control Language）用来控制数据库组件的存取许可、存取权限等得命令：grant、revoke。 
数据操纵语言DML（Data Manipulation Language）用来操纵数据库中得数据的命令：insert、update、delete。 
数据查询语言DQL（Data Query Language）用来查询数据库中得数据的命令：select。 

SQL中得运算符：算术运算符、位运算符、比较运算符、逻辑运算符、通配运算符、字符串连接符、赋值运算符 

3.查询 
简单查询,使用TOP子句 
查询结果排序order by 
带条件的查询where,使用算术表达式，使用逻辑表达式，使用between关键字，使用in关键字， 
模糊查询like 
在查询中使用聚合函数：sum(x),avg(x),min(x),max(x),count(x),count(*) 
使用分组查询group by，having子句 
distinct关键字 
列别名 
select top 6 * from sales order by qty desc 
select au_id,au_fname,au_lname from authors where state in('ks','ca','mi') 
select au_fname,au_lname,phone from authors where au_id like '72[234]-%' 
select type,sum(price),avg(price),count(*) from titles group by type having type in('business','psycheology') 


简单子查询：嵌套子查询、相关子查询；子查询的select语句中不能使用order by子句，roder by子句只能对最终查询结果排序。 
嵌套子查询：执行过程，先执行子查询，子查询得到的结果不被显示，而是传给外层查询，作为外层查询的条件，然后执行外层查询，并显示结果。 
嵌套子查询的执行不依赖于外层查询，子查询只执行一次。 
带有比较运算符的子查询，带有in和not in的子查询，带有any或all的子查询 
相关子查询：子查询为外层查询的每一行执行一次，外层查询将子查询引用的列的值传给了子查询。 
相关子查询的执行依赖于外层查询，子查询需要重复的执行。 
带有exists和not exists的相关子查询。 
多表联接查询：内联接（inner join）、外联接（（left、right、full）outer join）、自联接（self join）和交叉联接（cross join） 
在查询上创建新表：select into语句首先创建一个新表，然后用查询的结果填充新表。 
表别名 
select coursename from course where courseid in(select distinct courseid from grade where grade>10) 
select studname from student where sudbirthday > any (select studbirthday from student where class = '信息系') and class<>'信息系' 
select studname from student where exists (select * from grade where studid = student.studid and courseid = '01') 
select stud1.* from student as stud1 join student as stud2 on stud2.studname = 'mm' and stud1.studsex = stud2.studsex 
select * into girls from student where studsex='m' 

4.视图、索引和事务 
视图是由一个或多个数据表（基本表）导出的虚拟表或者查询表，是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。 
视图的好处：能够简化用户的操作；视图能够对机密数据提供安全保护。 
创建视图时，视图的名称存在sysobjects表中。有关视图中所定义列的信息添加到syscolumns表中，而有关视图相关性的信息添加到sysdepends表中。另外，create view语句的文本添加到syscomments表中。 
在通过视图向表中插入数据时，如果insert语句列表中包含有视图中没有选择的列和不允许为空值的列，这种操作是不允许的。 
创建视图：create view view_employee as select emp_id,fname,lname from employee 
使用视图：select * from view_employee 
修改视图：alter view view_employee as select emp_id,fname,job_id from employee where job_id>10 
删除视图：drop veiw view_employee 
查看视图结构：exec sp_help view_employee 
查看视图定义信息：exec sp_helptext 'view_employee' 

索引提供了一种基于一列或多列的值对表的数据行进行快速访问的方法。索引提供的是表中得逻辑顺序。 
聚集索引基于数据行的键值在表内排序和存储这些数据行。当数据表以某列为关键字建立聚集索引时，表中得数据行就以该列（聚集索引键）的排序次序进行存储。每个表只能有一个聚集索引。 
非聚集索引具有完全独立于数据行的结构，一个表可以建立多个非聚集索引。 
创建聚集索引：create clustered index studid_ind on stud(studid) 
创建非聚集索引：create unique index studfullname_ind on stud(fname desc,lname) 
删除索引：drop index stud.studid_ind 
查看stud表上得索引：exec sp_helpindex stud 

事务是一种机制，是一个操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤销操作请求。 
事务的特性：原子性（Atomicity）、一致性（Consistenty）、隔离性（Isolation）、永久性（Durability）。 
事务分类：显示事务、隐性事务、自动提交事务。 

视图、索引和事务的创建、使用、修改和删除 

5.Transact—SQL编程 
全局变量：由系统定义和维护，其名称以@@字符开头 
局部变量：由用户定义和赋值，其名称以@字符开头 
输出语句：print 
逻辑控制语句：begin...end ;break ;case ;continue ; goto ; if...else ;return ; while 
常用函数：行集函数，聚合函数，标量函数 
转换函数：convert(dt,e,s),cast() 
数学函数：绝对值abs(n),向上取整ceiling(n),向下取整floor(n),指定次幂power(n,y),四舍五入round(n,length),求符号sign(n),平方根sqrt(n) 
日期和时间函数：dateadd(datepart,num,date),datediff(datepart,date1,date2),datename(datepart,date),datepart(datepart,date),getdate(),year(date),month(date),day(date) 
字符串函数：lower(e),upper(e),left(e,i),right(e,i),replace(s1,s2,s3)用3替换1中的2，replicate(e,i)重复指定次数，stuff(s1,start,length,s2)用2替换1中指定位置，substring(expression,start,length) 
元数据函数：db_id('database_name'),db_name(datebase_id),object_id('obj_name'),object_name(obj_id),col_length('table','column'),col_name(table_id,col_id) 
聚合函数：avg(expr),count(expr),count(*),max(expr),min(expr),sum(expr) 
select au_lname,au_fname,contory = 
case state 
when 'ut' then 'utah' 
when 'ca' then 'california' 
else 'world' 
end,city from authors order by state desc 

while(select avg(price) from titles)<30 
begin 
update titles set price = price * 2 
if(select max(price) from titles)>50 break 
else continue 
end 
print '价格太高' 

begin 
insert into jobs values('a',80,234) 
if @@error<>0 print '数据插入失败' 
else goto M 
end 
M:print '数据插入成功' 

6.游标 
游标是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。将批操作变成行操作，对结果集中得某行进行操作。 
declare author_csr cursor read_only for --定义只读游标 
select au_fname,au_lname from authors where state = 'ca' order by au_fname,au_lname 
declare @lname varchar(20),@fname varchar(20) --定义变量 
open author_csr --打开游标 
fetch next from author_csr into @lname,@fname --执行一次数据读取操作 
while @@fetch_status=0 --循环游标读取数据 
begin 
print 'author name:'+@lname+''+@fname 
fetch next from author_csr into @lname,@fname 
end 
close author_csr --关闭游标 
deallocate author_csr --释放游标 

7.存储过程 
存储过程（stored procedure）类似c语言中的函数，是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字饼给出参数来执行它。 
常用的系统存储过程：sp_database,sp_helpdb,sp_renamedb,sp_tables,sp_column,sp_help,sp_helpconstraint,sp_helpindex,sp_stored_procedure,sp_password 
创建存储过程： 
create procedure book_num (@book_name varchar(26),@starttime datetime,@endtime datetime,@total int output) 
as 
select @total=count(jy.askbookid) from book,jyls jy where bookname like @book_name and book.isbn=jy.isbn and jy.starttime>=@starttime and endtime<=@endtime 
使用存储过程： 
declare @book_name char(26),@total int 
set @book_name='面向对象分析和设计' 
exec book_num @book_name,'20##-01-01','20##-11-01',@total output 
select @book_name as bookname,@total as num 

8.触发器 
触发器是一种特殊类型的存储过程，主要是通过实践进行触发而被执行。 
触发器的主要作用就是能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。其他功能：强化约束，跟踪变化，级联运行，存储过程调用。 
SQL Server 2000支持两种类型触发器： 
after触发器：要求只有执行某一操作之后，触发器才被执行，且只能在表上定义。 
instead of触发器：表示并不执行其所定义的操作，而仅是执行触发器本身。既可以在表上定义，也可以在视图上定义，但对同一操作只能定义一个instead of触发器。 

工作原理： 
当触发insert触发器时，新的数据行就会被插入到触发器表和inserted表中。触发器通过检查inserted表来确定是否执行触发器动作或如何执行。 
当在定义有触发器的表上执行update语句时，原始行被移入到deleted表，更新行被移入inserted表。触发器检查deleted表和inserted表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。 
当触发deleted触发器后，从受影响的表中删除的行将被放置到一个特殊的deleted表中。 

create trigger update_smoke_t_sale on smoke_t_sale for update 
as 
declare @newsalenum int,@smokeproductname varchar(40) 
select @newsalenum= salenum from inserted 
select @smokeproductname=smokeproductname from inserted 
if update(salenum) --判断是否更新 
begin 
update smoke_t_sale set saletotalprice=@newsalenum * saleprice where smokeproductname=@smokeproductname 
insert into smoke_log(logContent) values('更新成功') 
end 
else 
print '未更新' 

9.数据库高级管理 
SQL Server安全体系结构，4个等级：客户机操作系统的安全性，SQL Server的登录安全性，数据库的使用安全性，数据对象的使用安全性 
SQL Server验证模式：windows身份验证模式和混合模式（windows身份验证和SQL Server身份验证） 
登录账户：用户登录（连接）SQL Server服务器的账户和密码。 
角色管理：服务器角色（负责管理和维护SQL Server的组）；数据库角色（是对某个数据库具有相同访问权限的用户账户和组的集合） 
数据库用户：对于每个要求访问数据库的登录账户，必须在要访问的数据库中建立该数据库的访问账户，且与其登录账户链接关联，才可进入该数据库访问。 
权限管理：是指用户是否能进行访问数据库资源的相应操作。权限包括：语句权限、对象权限和暗示权限。 

授予权限： 
grant 语句 [...] to 安全账户[...] 
grant 权限 [...] on 表或视图[(列[,...])]|on 存储过程|on用户自定义函数 to 安全账户[，...] 
拒绝权限： 
deny 语句 [...] to 安全账户[...] 
deny 权限 [...] on 表或视图[(列[,...])]|on 存储过程|on用户自定义函数 to 安全账户[，...] 
撤销权限： 
revoke 语句 [...] from 安全账户[...] 
revoke 权限 [...] on 表或视图[(列[,...])]|on 存储过程|on用户自定义函数 from 安全账户[，...] 

备份和恢复： 
数据库备份设备,在进行数据库备份之前，首先要创建备份设备。包括：磁盘、磁带和命名管道 
SQL Server 备份策略：只备份数据库、备份数据库和事务日志、差异备份。 
backup database medicaldb to disk='medical_bk1' with name='medicaldb backup' description='medicaldb fullbackup' init 
restore database medicaldb from medical_bk1 

导入导出： 
DTS（Data Transformation Service）是SQL Server提供的数据传输服务。使用户可以将来自完全不同数据源的数据析取、转换并合并到单个或多个目的。 

分离与附加：创建可移动的数据库。

 

第二篇：数据库总结之外键再学习

之所以称之为再学习，是因为此前曾学习过这些知识，只不过当时没有总结。现在再次遇到它，详细再学习一下，加深印象。

什么是外键？

如果公共关键字（表中相同的属性）是一个关系中的主关键字（primary key），那么这个公共关键字被称为另一个关系的外键。

相关概念解析：

公共关键字：多个表中的相同的属性或属性组。

主关键字：即主键。

关系：即一张表。

主表与从表

表A中有a、b两个字段，a字段是主键。表B有a、c两个字段。a字段可以设置外键。此时，表A为主表，表B为从表。

外键的作用：

1、数据完整性的（数据库基础理论书中所说的“参照完整性”的数据库实现方式。）

2、保持数据一致性，

3、控制存储在外键表中的数据

简而言之：当你对一个表的数据进行操作时和他有关联的一个或更多表的数据能够同时发生改变，这就是外键的作用。

外键约束的主要目的是控制存储在外键表中的数据，但它还可以控制对主键表中数据的修改。例如，如果在 publishers

表中删除一个出版商，而这个出版商的 ID 在 titles

表中记录书的信息时使用了，则这两个表之间关联的完整性将被破坏，titles

表中该出版商的书籍因为与 publishers 表中的数据没有链接而变得孤立了。

外键约束防止这种情况的发生。如果主键表中数据的更改使之与外键表中数据的链接失效，则这种更改是不能实现的，从而确保了引用完整性。如果试图删除主键表中的行或更改主键值，而该主键值与另一个表的外键约束值相关，则该操作不可实现。若要成功更改或删除外键约束的行，可以先在外键表中删除外键数据或更改外键数据，然后将外键链接到不同的主键数据上去。

例如：

a b

两个表

a表中存有客户号，客户名称

b表中存有每个客户的订单

有了外键后,你只能在确信b

表中没有客户x的订单后，才可以在a表中删除客户x，否则b表中的数据就变得孤立了，即完整性缺失。

建立外键的原则：

1、为关联字段创建外键。

2、所有的键都必须唯一。

3、避免使用复合键。

4、外键总是关联唯一的键字段。

如何建立外键：

一、使用T-SQL语句：

指定主键关键字：foreign key(列名)

引用外键关键字：references <外键表名>(外键列名)

事件触发限制: ondelete和on update ,

可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no

action

例如：

outTable表主键 id

类型 int

创建含有外键的表：

create table temp(

id int,

name char(20),

foreign key(id) references outTable(id) on delete cascade on updatecascade); 说明：把id列设为外键，参照外表outTable的id列，当外键的值删除，本表中对应的列删除（on

delete的作用），当外键的值改变，本表中对应的列值改变（on update的作用）。

二、使用DBMS关系图建立外键：

1、建表（见图，设置主键。）

2、新建关系图

3、选择添加表：

4、建立外键，将要建立外键关系表中主键字段拖向外键字段。

5、点击确(参考：http://www.gpskld.com)定即可完成外键的建立。

使用外键的注意事项：

1、使两张表形成关联，外键只能引用外表中的列的值。

2、外建表达的是参照完整性：这是数据固有的，与程序无关。因此，应该交给DBMS来做。

3、使用外建，简单直观，可以直接在数据模型中体现，无论是设计、维护等回有很大的好处，特别是对于分析现有的数据库的好处非常明显

4、相对于触发器或程序完成的这个工作（指参照完整性约束），外键更方便。

5、阻止非法数据。实际上，前台程序应该对这种提交失败做出处理。数据是企业的而非程序的，储程序要尽量与数据分离，反之亦然。

6、建立外键的前提：本表的列必须与外键类型相同(外键必须是外表主键)。