数据库高级应用知识点.doc

第一章：创建数据库和数据表1.1数据定义语言(用于创建数据库及数据表的结构)：包括 create database,crate table, alter table,drop table, drop database1.2 创建数据库1.3 修改数据库1.4 删除数据库1.5 创建表1.6 修改表结构-修改列的数据类型（添加非空约束）Alter table 表名 Alter column 字段 字段类型 not null-添加列Alter table 表名 add 字段 字段类型（varchar指明长度） not null-删除列Alter table 表名 drop column 字段-删除约束Alter table 表名 drop constraint 约束名-主键约束Alter table 表名 add constraint 约束名 primary key (字段)-唯一约束Alter table 表名 add constraint 约束名 unique (字段)-检查约束Alter table 表名 add constraint 约束名 check(约束表达式)-默认约束Alter table 表名 add constraint 约束名 default xxx for 字段名 （xxx表示默认值）-外键约束Alter table 表名 add constraint 约束名 foreign key (字段) references 表名(字段)-标识列约束Alter table 表名 add 字段 int identity(1,1) primary key1.7 删除数据表 drop table 表名第二章：创建数据库和数据表T-SQL 中的变量p291.定义局部变量：用于批处理，存储过程或是触发器内声明 declare 变量名 数据类型变量名必须以符开头，数据类型 不能是text,ntext 或image赋值 select/set 变量名=表达式/select 字句2.定于全局变量p30全局变量 xxx 优先于 系统及的变量3.函数p31聚合函数 count,sum,avg,max, min日期时间函数 dateadd() :返回加上一个时间的新时间datediff(): 计算两时间差datename(): 返回年月日星期等字符串datepart(): 返回部分日期字符函数p334.流程控制与语句If 表达式else(满足相应的条件，则执行相应的语句)While 表达式 ：只要条件为真，就重复执行语句，可以用break,continue关键字在循环内部控制while 循环中语句的执行Waitfor :延迟语句，在指定的时间执行该语句Return 语句：从查询中无条件退出，不再执行return 后面得语句。在存储过程中，return 不能返回空值。Case 表达式：第三章：视图，索引和事务视图：用sql语句查询出来，一张虚拟的表使用视图的优点：简化查询语句：让复杂的语句不再复杂添加可读性:方便程序的维护：增加数据的安全和保密性：创建视图Create view 视图名 as select 字句查看视图 select * from 视图名创建视图时，select 子句里不能包括以下内容不能包括compute,compute bt 字句不能包括order by 字句,除非在select 字句中有top 字句不能包括option 字句不能包括into 关键字不能引用临时表或表变量只有在创建视图后才可以使用order by字句 （select top 10* from view_xx order by xxx desc）删除视图：drop view view_xx索引什么是索引：索引的作用：解决数据查询性能问题，加快访问速度索引类型：聚集索引非聚集索引如何建立好的索引：该列频繁用于搜索，该列用于对数据的排序.索引的缺点：需要更多的空间操作数据的命令需要更长时间处理，字段修改，需要对索引进行更新创建索引create unique index idxname on tabname(col。) 删除索引 drop index idxname on tabname注：索引是不可更改的，想更改必须删除重新建。 -添加聚合索引Create clustered index index_xxx on 表名字段 with fillfactor=80-添加非聚合索引Create nonclustered index index_xxx on 表名字段 with fillfactor=80-在查询中使用索引Select * from 表名 with (index= index_xxx )事务事务的概念：在数据库系统中，把一系列具有“要么什么也不做，要么都做完”特征的数据库操作定义为一个事务。事务反映了现实世界中需要以一个完整的单位提交的一项工作。事务是一个逻辑工作单元，它必须完整地执行，或者全都不执行。事务的4个性质：p88原则性一致性隔离性持久性 事务的状态活动状态（active）:初始状态，事务能够执行时处于这个状态部分提交状态（partially committed）:最好一条语句被执行之后失败状态（failed）:发现正常的执行不能举行之后、中止状态（aborted）:事务回滚并且数据库已被恢复到事务开始执行前得状态提交状态（committed）：成功完成之后事务三种模式显示事务隐性事务自动提交事务创建事务的语法begin transaction insert 成绩表values(06501011,EIT37401,91) if error=0 commit transaction /*若无误，则提交此事务*/ else rollback transaction /*若有误，则回退此事务*/事物并发处理：指CPU利用分时方法实现多个事务的交替进行并发处理可能产生的问题(1)丢失更新(2)读未提交数据(3)不一致检索产生上述问题的原因：违背了事务的隔离性。封锁机制：封锁机制是实现并发控制的重要技术，并发控制的作用是为了实现事务的隔离性。封锁的类型：共享锁：也称读锁。如果事务T给数据对象X加上共享锁，则其他事务只能对X再加共 享锁，不能加排它锁。T及其他事务都可以且只能读数据。排它锁：也称写锁。如果事务T对数据对象X加上排它锁，则只允许事务T独占数据项X，即对数据项X可读、可写。其他任何事务都不能对X加任何类型的锁，直到T释放X上的锁。（2）封锁规则 1）数据项X未加锁，谁都可以给它加锁。 2）数据项X已加共享锁，其他事务只能加共享锁。 3）数据项X已加排他锁，其他事务不能加任何锁。 4）谁加的锁，谁释放该锁。（3）封锁的粒度 指封锁对象的大小。封锁的对象可以是数据库、表、行、列等逻辑单元。（4）死锁 死锁是若干事务相互等待对方释放封锁，死锁是由于封锁而产生的。（5）解决死锁的方法 死锁的预防：要求每个事务必须一次性封锁所要使用的全部数据；规定封锁的顺序。 死锁的诊断：DBMS周期性测试DBS系统是否处于死锁状态。 死锁的解除：将一个处理代价小的事务撤销，回滚该事务，并释放该事务持有的锁。第四章 存储过程存储过程：是一组为了完成特定功能的语句集。存储过程分类：系统存储过程（主要是从系统表中获取信息，从而系统管理员提供支持）自定义存储过程：存储过程的优点：（1）由一系列T-SQL语句组成，可作为一个整体被程序多次调用；（2）修改容易，可移植性好；（3）存储过程是预编译的，执行速度快；（4）在网络中调用过程，只需要传送调用过程的语句，因此可减少网络流量；（5）可通过对执行存储过程的权限控制来保证系统数据的安全；（6）存储过程可嵌套调用，即在一个存储过程中调用另一个存储过程。创建和使用存储过程：create proc 过程名 parameter 参数类型 /*输入参数*/parameter 参数类型 ouput /*输出参数*/as begin命令行或命令块End使用存储过程execute 过程名 参数1,参数2， output不带参数的存储过程use 学生成绩管理 go create proc proc1 as begin select a.学号,姓名,所属院系,成绩 from 学生表 a,课程表 b,成绩表 c where 课程名称=数据库技术及网络应用 and b.课程编号=c.课程编号 and c.学号=a.学号endgoexecute proc1 /*执行存储过程*/带输入参数的存储过程use 学生成绩管理 go create proc proc2 scrose_name varchar(20) as begin select 姓名,职称,联系电话 from 课程表 a,教师表 b where 课程名称= scrose_name and a.教师编号 =b.教师编号 end go execute proc2 C语言程序设计 /*执行存储过程*/带输入和输出参数的存储过程use 学生成绩管理 go create proc proc3 yuanxi char(20), maxage int output, average real output as begin select maxage=max(datepart(yy,getdate()-datepart(yy,出生日期) from 学生表 where 所属院系=yuanxi select average=avg(datepart(yy,getdate()-datepart(yy,出生日期) from 学生表 where 所属院系=yuanxi end godeclare x1 int,x2 real execute proc3 教育学院 ,x1 output,x2 outputselect x1 最大年龄,x2 平均年龄修改存储过程利用T-SQL修改存储过程的语法格式：alter proc 过程名 parameter 参数类型 /*输入参数*/parameter 参数类型 ouput /*输出参数*/as begin 命令行或命令块end修改存储过程是指修改存储过程的内容。如果要修改某一存储过程，在创建该存储过程的代码中，将命令动词creat改为alter，再修改其内容即可。存储过程重命名sp_rename 原过程名, 新过程名给存储过程重命名只是修改存储过程的名字，和存储过程内容本身无关。该功能用系统存储过程sp_rename实现，该系统存储过程存放在master数据库里，在任何数据库中都可直接调用它。删除存储过程利用T-SQL语句删除存储过程的语法格式： drop proc 存储过程名1,存储过程名2，删除存储过程是指将存储过程作为数据库对象从数据库中彻底删除。一次可删除多个存储过程。第五章触发器触发器的概念：触发器是一种特殊的存储过程，它和表密切相连，可以看作是表格定义的一部分 。 当用户对指定表操作时， 触发器会自动执行。 触发器在 update、insert、delete等操作执行结束后才执行。触发器的功能：（1）强化约束（2）跟踪变化（3）级联运行（4）存储过程的调用( 5 )在一张表的update、insert、delete操作上可设置多个触发器。 触发器的种类：after触发器和instead of触发器（说明）使用触发器应注意的问题(1)当使用约束、规则、默认值等方法能够实现数据的完 整性，就不用触发器实现；(2)只有表的拥有者才可以在表上创建或删除触发器，这 种权限不准转授；(3)使用update语句可以一次对多行数据进行修改，而触 发器只被触发一次；(4)触发器只能在当前数据库中创建，触发器的命名必须 要遵守标识符的命名规则。触发器的工作原理inserted表和deleted表的特点如下： （1）这两张表是逻辑表，由数据库管理，用户不能对他们进行修改；（2）这两张表存储在内存中，而不是存储在数据库中；（3）这两张表的结构与被该触发器作用的表结构相同；（4）当触发器完成操作后，这两张表会自动删除；（5）两张表中保存的数据是因用户操作而被影响到的原数据或新数据；（6）这两张表是只读表。 触发器的创建与执行creat trigger 触发器名称 on 表名 for update,insert ,delete as begin sql语句 end return语法注释：触发器名称 是用户给要创建的触