数据库系统的三级模式:模式,内模式,外模式。
外模式
外模式也成为用户模式,它是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图,是与某一应用有关的数据的逻辑
表示。外模式是模式的子集。一个数据库可以有多个外模式。
注意:外模式是保障数据库安全性的一个有力措施
模式
模式也称概念模式或逻辑模式,它是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共数据视图。一个数据库只有一个模式,模式处于三级结构的中间层。
注意:定义模式时,不仅要定义数据的逻辑结构,而且要定义数据之间的关系,定义与数据有关的安全性、完整性要求。
内模式
内模式也称为存储模式,一个数据库只有一个内模式。它是数据物理结构和存储方式的描述,是数据在数据库内部的表示方式。
三级模式之间的映射
1.外模式/模式映射(多对一)
2.模式/内模式(一对一,具有唯一性)
视图
视图为数据呈现提供了多样的表现形式,用户可以通过他浏览表中感兴趣的数据。分类如下:
1.标准视图:保存在数据库中的select语句,即通常意义上的视图
2.索引视图:创建有索引的视图称之为索引视图。它经过计算并存储有自己的数据,可以提高某些类型查询的性能,尤其适用于聚合多行的查询,但不太适用于经常更新的基本数据集
3.分区视图:是在一台或多台服务器间水平联结一组表中的分区数据,以使数据看上去来源于一个表
使用create view创建视图
CREATE VIEW TestView
AS
SELECT ID,Name,Address FROM TestTable
通过系统存储过程刷新视图
EXEC SP_REFRESHVIEW ‘TestView’
将视图绑定到表的架构
ALTER VIEW TestView
WITH SCHEMABINDING
AS
SELECT ID,Name,Address FROM dbo.TestTable --这里的表名字必须写两个部分，就是包含了架构名(dbo)
注意:当将视图绑定到表的结构后,如果再次尝试修改TestTable就会得到错误提示.因为它已经被一个视图绑定了
T-SQL概述
T-SQL主要由一下几部分组成:
1.数据定义语言(DDL):用于在数据库系统中对数据库、表、视图、索引等数据库对象进行创建和管理
2.数据控制语言(DCL):用于实现对数据库中数据的完整性、安全性等的控制
3.数据操纵语言(DML):用于插入、修改、删除和查询数据库中的数据
T-SQL语句结构
select语句主要结构:
select 字句
[into 字句]
from 字句
[where 字句]
[group by 字句]
[having 字句]
[order by 字句]
变量
书写格式,各种声明方式
use 表名
declare @变量名称1 类型1，@变量名称2 类型2,@变量名称3 类型3
select @变量名称1=值1,@变量名称2=值2
set @变量名称3 类型3
print @变量名称1
print @变量名称2
print @变量名称3
注意:1个select后面可以接多个变量名称,1个set后面只能接1个变量名称;一个print一次只能打印一个变量的值
eg:5秒后’输出时间到了!'字符信息
waitfor delay ‘00:00:05’
print ‘时间到了!’
常用命令
DBCC(数据库一致性检查程序)命令用于验证数据库完整性、查找错误和分析系统使用情况等。DBCC命令后面必须加上子命令,系统才知道执行什么
- dbcc checkalloc 数据库名称
说明: 检查指定数据库的磁盘空间分配结构的一致性
eg: dbcc checkalloc (‘eccard’)
- dbcc showcontig
说明: 显示指定表的数据和索引的碎片信息
SQL语句查询
select检索数据
select 字句
[into 字句]
from 字句
[where 字句]
[group by 字句]
[having 字句]
[order by 字句]
select … from 字句的参数说明
参数 描述
ALL 指定在结果集中可以包含重复行。ALL是默认值
DISTINCT 指定在结果集中只能包含唯一行对于distinct关键词来说,NULL值是相等的
expression 常量、函数以及一个或多个运算符连接的列名、常量和函数的任意组合,或者是子查询。例如在表达式中可以使用行聚合函数(常用行聚合函数如下)
with (<table_hint>) 指定查询优化器对此表和此语句使用优化或锁定策略
INNER 指定返回所有匹配的行对
JOIN 指示指定的连接操作应在指定的表源或视图之间执行
常用的行聚合函数和功能
行聚合函数 功能
COUNT(*) 返回组中的项数
COUNT({[[ALL |DISTINCT] 列名]}) 返回某列的个数
AVG({[[ALL |DISTINCT] 列名]}) 返回某列的平均值
MAX({[[ALL |DISTINCT] 列名]}) 返回某列的最大值
MIN({[[ALL |DISTINCT] 列名]}) 返回某列的最小值
SUM({[[ALL |DISTINCT] 列名]}) 返回某列值得和
INTO字句
创建新表并将来自查询的结果行插入新表中。语法格式如下:
INTO new_table
参数说明
new_table:根据选择列表中的列和where字句选择的行,指定要创建的新表名。new_table的格式通过对选择列表中的表达式进行取值来确定。new_table中的列按选择列表指定的顺序
创建。new_table中的每列与选择列表中的相应表达式具有相同的名称、数据类型和值
eg:使用INTO字句创建一个新表tb_EM,tb_EM表中包含EM表中的name和age字段
select name,age into tb_EM from
EXISTS关键字
EXISTS关键字用于指定一个子查询,测试行是否存在。语法如下
EXISTS subquery
参数说明
subquery:受限制的select语句,不允许使用COMPUTE子句和INTO关键字
结果类型:Boolean类型
group by 字句
group by表示按照一个或多个列或表达式的值将一组选定行组合成一个摘要行集,针对每一组返回一行。
having字句
指定组或聚合的搜索条件。having只能与select语句一起使用。having通常在group by 字句中使用。如果不使用group by 字句,则having的行为与where字句一样。
order by 字句
指定在select语句返回的列中所使用的排序顺序。除非同时指定了top,否则order by 字句在视图、内联函数、派生表和子查询中无效
ASC:指定按升序 DESC:指定按降序
UNIO合并多个查询结果
表的合并操作将两个表的行合并到了一个表中,且不需要对这些行做任何更改
子查询与嵌套查询
在使用select语句检索数据时,可以使用where字句指定用于限制返回的行的搜索条件,group by字句将结果集分成组,order by字句定义结果集中的行的排列顺序。使用这些语句
可以方便的查询表中的数据。但是当where字句指定的搜索条件指向另一张表时,就需要使用子查询或嵌套查询。
子查询的定义
子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用条件表达式的地方都可以使用子查询。
嵌套查询的定
嵌套查询是指将一个查询块嵌套在另一个查询块的where字句或having短语的条件中查询。嵌套查询中上层查询块称为外侧查询或父查询,下层查询块称为内层查询或子查询。sql语言
允许错层嵌套,但是在子查询中不允许出现order by字句,order by字句只能用在最外层的查询块中。
存储过程和触发器
存储过程的概念
存储过程(stored procedure)是预编译SQL语句的集合,这些语句存储在一个名称下并作为一个单元来处理。存储过程代替了传统的逐条执行SQL语句的方式。一个存储过程中可
包含、插入、删除、更新等操作的一系列SQL语句,当这个存储过程被调用执行时,这些操作也会同时执行。
存储过程与其他编程语言中的过程类似,它可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值;包含用于在数据库中执行操作(包括调用其他过程)的编程语句;
向调用过程或批处理返回状态值,以指明成功或失败(以及失败的原因)
SQL Server提供了三种类型的存储过程,各类型存储过程如下
①系统存储过程:用来管理SQL Server和显示有关数据库和用户信息的存储过程。
②自定义存储过程:用户在SQL Server中通过采用SQL语句创建存储过程。
③扩展存储过程:通过编程语句(如C语言)创建外部例程,并将这个例程在SQL Server中作为存储过程使用。
使用create proc语句创建存储过程,其语法格式如下
create proc[dure] procedure_name [; number]
[{@parameter date_type}
[varying] [=default] [output]
] [,...n]
as sql_statement
参数及说明
参数 描述
create proc[dure] 关键字,也可以写成create proc
procedure_name 创建存储的名字
number 对存储过程进行分组
@parameter 存储过程参数,存储过程可以申明一个或多个参数
date_type 参数的数据类型,所有数据类型均可用作存储过程的参数,但sursor数据类型只能用于output参数
varying 可选项,指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化),该关键字仅适用于游标参数
default 可选项,表示为参数设置默认值
output 可选项,表名参数是返回参数,可以将参数值给调用的过程
n 表示可以定义多个参数
AS 指定存储过程要执行的操作
sql_statement 存储过程中的过程体
eg:使用create proc语句创建一个存储过程,用来根据学生编号查询学生信息
create proc proc_student
@proc_sno int
as
select * from student where sno=@proc_sno
执行存储过程
存储过程创建完成后,可以通过execute执行,可简写为exec.
eg: exec proc_student
触发器的概述
触发器是一种特殊类型的存储过程,当指定表中的数据发生变化时触发器自动生效。它与表紧密相连,可以看作是表定义的一部分，触发器不能通过名称被直接调用,更不允许设置参数。
在SQL中,一张表可以有多个触发器。用户可以使用insert、update或delete语句对触发器进行设置,也可以对一张表上的特定操作设置多个触发器。不论触发器所进行的操作
有多复杂,触发器都只作为一个独立的单元被执行,被看作是一个事务。如果在执行触发器的过程中发生了错误,则整个事务将自动回滚。
触发器的种类
SQL Server包含三种常规类型的触发器:DML触发器、DDL触发器和登陆触发器
当数据库中发生数据操作语言(DML)事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的insert语句、update语句或delete语句。DML触发器可以查询其他表,还
可以包含复杂的SQL语句。
用户可以设计一下类型的触发器
①:After触发器:在执行了insert、update或delete语句操作之后执行After触发器。
②:Instead of 触发器:执行Instead of 触发器代替通常的触发动作。还可为带有一个或多个基表的视图定义定义Instead of 触发器。CLR触发器可以是After触发器或Instead of
触发器,而这些触发器能够扩展视图可支持的更新类型。
③:CLR触发器:CLR触发器可以是After触发器或Instead of 触发器。CLR触发器还可以是DDL触发器。
DDL触发器是一种特殊的触发器,它在相应数据定义语言(DDL)语句时触发,可以用于在数据库中执行管理任务,例如审核和规范数据库操作。
创建触发器
①创建DML触发器
如果用户要通过数据操作语言(DML)事件编辑数据,则执行DML触发器。DML触发器是针对表或视图的Insert、Update或Delete语句。
eg:为student表创建DML触发器,当向该表中插入数据时给出提示信息
Create Trigger Trigger_stu
on Student
After Insert
AS
Raiserror ('正在向表中插入数据',16,10);
②创建DDL触发器
DDL触发器用于相应各种数据定义语言(DDL)事件。这些事件主要对应于Create、Alter和Drop语句,以及执行类似DDL操作的某些系统存储过程。
eg:为Student表创建DDL触发器,防止用户对表进行删除或修改操作
Create Trigger Trigger_stuDDl
on Database
for Drop_Table,Alter_Table
AS
Print '只有"Trigger_stuDDL"触发器无效时,才可以删除或修改表'
RollBack
③创建登陆触发器
登陆触发器在遇到Logon事件时触发。Logon事件是在建立用户回话时引发的。触发器可以由SQL语句直接创建,也可以由程序集方法创建。
eg:创建一个登陆触发器,该触发器拒绝TM登录名的成语登陆SQL
Create Login TM with password=‘TMsoft’ Must_Change,
Check_Expiration=on;
Go
Grant View Server State to TM
Go
Create Trigger connection_limit_trigger
On all Server with Execute As 'TM'
For Logon
AS
Begin
If Original_login()='TM' And
(Select Count(*) From sys.dm_exec_sessions
Where is_user_process=1 And
original_login_name='TM') > 1
RollBack;
End;
索引与数据完整性
定义:
索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。
索引的优点:
①创建唯一性索引,保证数据库表中每一行数据的唯一性;
②大大加快数据的检索速度,这也是创建索引最主要的原因;
③加快表与表之间的连接;
④在使用分组和排序字句进行数据检索时,同样可以减少查询中分组和排序的时间;
索引的分类:
聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文视图、空间索引、筛选索引和XML索引。
P268
1402
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
