数据库复习资料 期末考试
第一章数据库基础
什么是数据库:数据库是为了实现一定的目的、按某种规则组织起来的“数据”的“集合”,简而言之,它是有结构的数据集合。
数据库系统的三级模式,二级映像:外模式,模式,内模式。
外模式/模式映像:保证逻辑独立性,模式/内模式映像:保证物理独立性
数据库技术的发展阶段:人工管理阶段,文件系统阶段,数据库系统阶段。
数据模型的组成部分:数据结构,数据操作,完整性约束。
常见的数据模型:层次模型,网状模型,关系模型。
完整性约束:实体完整性,参照完整性,用户自定义完整性。
SQL结构化查询语言,包括数据定义语言(DDL):create,drop,alter 数据操作语言(DML):select,insert,update,delete 数据控制语言(DCL):grant,revoke
第三章
数据库对象:表(table),视图(view),索引(index),存储过程(proc或procedure),触发器(trigger),约束(constraint).
SQL Server 2008数据库主要由文件和文件组组成.
文件主要分为三类:主要数据文件(有且仅有一个,默认扩展名.mdf),次要数据文件(可以有一个或多个,也可以没有,默认扩展名.ndf),事务日志文件(至少有一个,可以有多个,默认扩展名.ldf)。
文件组分为:主文件组,用户定义文件组。(使用filegroup在使用create或者alter创建或修改数据库的时候指定文件组)。
数据库对象的全称:服务器名.数据库名.拥有者名.对象名。
用SQL语句创建数据库
create database booksys --创建数据库booksys
on primary --主要数据文件
(name=booksys, --逻辑名称
filename='I:\sql_data\test1\booksys.mdf',--文件存放路径
size=5mb, --初始大小
maxsize=10mb, --最大容量
filegrowth=2mb) --增长率
log on --日志文件
(name=booksys_ldf,
filename='I:\sql_data\test1\booksys.ldf',
size=2mb,
maxsize=10mb,
filegrowth=2mb
)
添加日志文件:
use booksys --使用数据库
alter database booksys --修改数据库
add log file --添加日志文件
(name=booksys_ldf,
filename='I:\sql_data\test1\booksys.ldf',
maxsize=10mb,
filegrowth=2mb
)
to filegroup 文件组名--将文件添加到文件组中
添加次要数据文件:
use booksys
alter database booksys
add file
(name=booksys,
filename='I:\sql_data\test1\booksys.ndf',
size=2mb,
maxsize=10mb,
filegrowth=2mb
)
添加文件组:
Alter database 数据库名
Add filegroup 文件组名
删除文件:
Alter database 数据库名
Remove file 文件名
删除数据库:
Drop 数据库名
创建表:
use booksys
create table book
(bookid varchar(20) primary key,--主键bookname varchar(60) unique,--唯一约束
editor char(8) not null,--非空约束
price numeric(5,2) check(price>20),--检查约束publish v archar(30) default(‘电子科大’),--默认值publishdata datetime,
qty int,
foreign key(字段) references 参照表名(字段[主键]) )
ALTER table 表名
(
ADD 列名类型约束,
| ALTER COLUMN 列名数据类型约束,
| DROP COLUMN列名
…
)
添加约束
alter table borrow
add constraint pk_id primary key(bookid,cardid,bdate) —添加主键约束
alter table borrow
add constraint fk_card foreign key(cardid)references reader(cardid) —添加外键约束
ALTER TABLE student
ADD CONSTRAINT CHECK_sex CHECK (sex='男' or sex = '女') —添加检查约束
ALTER TABLE student
ADD CONSTRAINT df_sp DEFAULT '三院' FOR 列名—为某一列添加默认值
ALTER TABLE student
ADD CONSTRAINT uq_ID UNIQUE (ID) --添加唯一约束
删除约束:
ALTER TABLE 表名
DROP 约束名
常见的约束:1、NULL/NOT NULL:空值/非空值约束。
2、PRIMARY KEY:主键约束,等价非空、单值。
3、CHECK (逻辑表达式):检查约束。
4、DEFAULT 常量表达式:默认值约束。
5、UNIQUE:单值约束。
6、foreign key REFERENCES 父表名(主键):外键约束
删除表:drop table 表名
插入数据:insert into book values('TP2001--001','数据结构','李国庆','22.00','清华大学出版社','2001-1-8',20)
更新数据:update student set classno='05' where spno='03'
删除数据:delete student where spno='03'
第四章
局部变量定义:
DECLARE
{
@变量名数据类型,@变量名数据类型[ ,…n ]
}
例如:declare @ID_name char(10)
比较运算符包括:>(大于)、<(小于)、=(等于)、>=(大于等于)、<=(小于等于)、!=(不等于)、!>(不大于)、!<(不小于)
运算符优先级:
(1)+(正)?(负)~(位反)
(2)*(乘)/(除)%(取余)
(3)+(加)+(字符串串联运算符)-(减)
(4)=、>、<、>=、<=、<>、!=、!>、!<(比较运算符)
(5)^(按位异或)&(按位与)| (按位或)
(6)NOT
(7)AND
(8)ALL ANY BETWEEN IN LIKE OR SOME(逻辑运算符)
(9)=(赋值)
通配符:_匹配单个字符;%匹配多个字符;[]匹配范围字符(注意如果是数字只能匹配一位数);[^]、[!]用来查询不属于指定范围([a-f]) 或集合([abcdef]) 的任何单个字符。
T-SQL语言控制流:IF…ELSE分支
CASE多重分支
WHILE循环结构
GOTO语句
WAITFOR语句(delay延时,time定时)
RETURN语句
第五章
查询语法格式:SELECT 列名
[INTO 新表名]
FROM <表或视图>
[WHERE 逻辑表达式] --列级条件
[GROUP BY 列名]
[HAVING逻辑表达式] --组级条件
[ORDER BY 列名[ASC|DESC]] asc升序,desc降序
常用的聚合函数:sum()
Avg()
Max()
Min()
Count()
Count(distinct)
交叉连接(笛卡尔积):SELECT fieldlist FROM table1,table2
SELECT fieldlist FROM table1 cross JOIN table2
内连接:内连接是从结果中删除其他被连接表中没有匹配行的所有行,所以内连接可能会丢失信息
SELECT fieldlist
FROM table1 [INNER] JOIN table2
ON table1.column=table2.column
左外连接:左外连接保留了第1个表的所有行,但只包含第2个表中与第1个表匹配的行,第2个表相应的空行被放入NULL值
SELECT fieldlist
FROM table1 left JOIN table2
ON table1.column= table2.column
右外连接:右外连接保留了第2个表的所有行,但只包含第1个表中与第2个表匹配的行,第1个表相应的空行被放入NULL值
SELECT fieldlist
FROM table1 right JOIN table2
ON table1.column=table2.column
全外连接:全外连接把两个表中所有行都显示在结果表中,并尽可能多的匹配数据和连接条件
SELECT fieldlist
FROM table1 full JOIN table2
ON table1.column=table2.column
第六章
视图来自多个基本表时,不允许添加和删除数据。
创建视图:
CREATE VIEW [ schema_name . ] view_name [ (column[ ,...n ] ) ]
AS select_statement
修改视图:
ALTER VIEW [ schema_name . ] view_name [ (column[ ,...n ] ) ]
AS select_statement [ ; ]
删除视图:DROP VIEW 视图名
通过视图添加数据:Insert into 视图名values(列值1,列值2,…,列值n)
通过视图更新数据:update 视图名set 列1=列值1,列2=列值2,…,列n=列值n Where 条件表达式
通过视图删除数据:DELETE FROM 视图名WHERE 逻辑表达式
第七章
每个数据表只能建立一个聚集索引,并且会在第一个建立,常常会在主键所在的列或者最常查询的列上建立聚集索引。
索引将占用用户数据库的空间。
唯一索引是指索引值必须是唯一的,不允许数据表中具有两行相同的索引值
创建索引语法:
CREATE
[UNIQUE][CLUSTERED|NONCLUSTERED] --唯一,聚集,非聚集
INDEX 索引名
ON 表(视图)名(列名[ASC|DESC][,…n])
删除索引:drop index 表名.索引名
域完整性常见的实现机制:
(1)默认值(Default)。
(2)检查(Check)。
(3)外键(Foreign Key)。
(4)数据类型(Data Type)。
(5)规则(Rule)。
第八章
在SQL Server 2008中存储过程分为三类:系统提供的存储过程、用户自定义存储过程和扩展存储过程。
创建语法:
CREATE PROCEDURE [proc] procedure_name [@参数]
As sql语句
通过Execute或Exec语句执行。
常量传值的调用方法:Exec 存储过程名参数
变量传值的调用方法:
declare @变量名数据类型
set @变量名=value
Exec procedure_name @变量名
获得存储过程信息:sp_helptext 存储过程名
查看存储过程的相关性信息:sp_depends 存储过程名
查看存储过程的一般信息:sp_help 存储过程名
修改存储过程:alter PROCEDURE [proc] procedure_name [@参数]
As sql语句
删除存储过程:drop proc 存储过程名
触发器分为DML触发器和DDL触发器。
DML触发器是在执行数据操作语言事件时被调用的触发器,其中数据操作语言事件包括:INSERT、UPDATE和DELETE语句.
DML触发器可以分为如下5种类型。
①UPDATE触发器
②INSERT触发器
③DELETE触发器
④INSTEAD OF触发器
⑤AFTER触发器
DDL触发器只能在触发事件发生后才会调用执行,即它只能是AFTER触发器。
创建DML触发器语法:
CREATE TRIGGER 触发器名on 表名或视图名for|after|instead of insert,updata,delete as SQL语句
创建DDL触发器语句:
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
With encryption
{ FOR | AFTER } 触发的事件类型
AS sql_statement
删除触发器:DROP TRIGGER 触发器名
第九章
系统函数:标量函数,聚合函数,行集函数。
三种用户自定义函数:
标量函数:返回单个数据值。
内连表值函数:返回值是一个记录集合——表。在此函数中,return语句包含一条单独的select语句。
多语句表值函数:返回值是由选择的结果构成的记录集。
函数不能有输出参数,存储过程可以有输出参数。
创建函数语法:
create function 函数名(@形参名数据类型[,@形参名数据类型,…,n])
returns 返回值数据类型
as
begin
命令行或程序块( 必须有Return 子句)
End
Transact-SQL调用函数的语法格式如下:
Print dbo.函数([实参])或select dbo.函数([实参])
删除自定义函数: drop function 函数名
事务的四个属性:原子性,一致性,隔离性,持久性(acid)
事务管理主要使用四条语句;
1. BEGIN TRANSACTION(事务的起点)
2. COMMIT TRANSACTION(事务的终点)
3. ROLLBACK TRANSACTION(事务回滚)
4. SAVE TRANSACTION(事务保存点)
begin transaction
……-- A组语句序列
save transaction 保存点1
……-- B组语句序列
if @@error <> 0
rollback transaction 保存点1 --回滚到保存点1
else
commit transaction
--提交A组语句,同时如果未回滚B组语句则
提交B组语句
事务运行的三种模式:
(1)自动提交事务
(2)显式事务
(3)隐性事务
锁的类型:共享锁(s),独占锁/排它锁(x),更新锁(u)
可以上锁的资源:行、页、扩展盘区(Extent )、表和数据库
第十章
SQL Server的安全性管理是建立在验证(authentication)和访问许可(permission)这两种机制上的。
SQL Server 2008的验证方式包括Windows验证模式与混合验证模式.
SQL Server的安全模型中包括以下几部分:
?SQL Server身份验证;
?数据库用户;
?角色;
?权限
在SQL Server中,账号有两种:一种是登录服务器的登录账号,另外一种就是使用数据库的用户账号.
创建登陆名:create login 登录名with password=’密码’
修改登录名:alter login 登录名with password=’密码’
删除登录名:drop login 登录名
常用的关键字:create(创建),drop(删除),alter(修改),insert(插入),delete(删除),update(更新),table(表),column(列),database(数据库),select(查询),constraint(约束),unique(唯一),not null(非空),primary key(主键),default(默认),foreign key references(外键),check(检查),declare(定义),set(赋值),delay(延时),group by(分组),order by (排序),asc(升序),desc(降序),distinct(消除重复),view(视图),index(索引), clustered(聚集),nonclustered(非聚集),proc(存储过程),exec(调用),trigger(触发器),insteaed of(操作之前),for/after(操作之后),function(函数), begin transaction(事务开始), commit transaction(提交事务),rollbacck transaction(事务回滚),save transaction(事务保存点),login(登陆)。