赞
踩
第一章
1.数据库(DataBase,DB):长期存储在计算机内的、有组织的、可共享的数据集合。
2.数据库的优点:
1.具有可共享性;
2.具有较小的冗余度;
3. 较高的数据独立性和易扩展性;
4.数据库中的数据具有完整性。
3. 数据库管理系统(DBMS)的功能:
1.数据定义功能(DDL);
2.数据操纵功能(DML);
3.数据库的运行管理;
4.数据库的建立和维护功能.
4. 数据库系统结构分为三层:即内模式、概念模式(模式)和外模式;
5. 两个层次的映象:外模式/概念模式映象,概念模式/内模式映象;
6.数据库用户结构:单用户数据库系统、主从式结构数据库系统、客户-服务器结构数据库系统、分布式结构数据库系统;
7.一个关系就是一张二维表,反之不成立;行:元组,列:字段;
8.关系数据库设计:
1.需求分析;
2.概念结构设计(工具E-R模型);
3.逻辑结构设计;
4.数据库表的优化与规范化(至少满足前三范式)。
注意:判断联系类型:1:1,1:n,m:n;
9. 实体完整性约束(PRIMARY):是主关键字段中的诸属性值不能取空值;
10. 唯一性约束(UNIQUE):是在约束的字段上不能有相同值出现。
11. 参照完整性约束(FOREIGN):要求外关键字的值必须来源于被参照关系表的取值或为空值。(和实体完整性一起用)
12. 检查(CHECK)和缺省值(DEFAULT)约束:是针对某一具体关系数据库的约束条件,反映某一具体应用所涉及的数据必须满足的语义要求。
第二章
1.SQL Server是一种基于客户-服务器的关系型数据库管理系统,使用Transact-SQL传送请求和服务,将所有工作分解为客户机任务和服务器任务,由两者分别完成。
2.安全机制:Windows身份验证模式、混合模式;
3.分离数据库:sp_detach_db 数据库名,
附加数据库:sp_attach_db [ @dbname = ] '数据库名' , [ @filename1 = ] '包括路径的数据库文件的物理名称' [ ,...16 ]。
第三章
1.数据库以文件为单位存储,由数据库文件(Primary Database File)(只有一个,.mdf)、事务日志文件组成(至少一个,ldf)组成。(次数据库文件(Secondary Database File),ndf)
2. 数据文件存储的基本单位是页,页的大小是 8 KB,数据库每兆字节有 128 页,每页的开始部分是 96 字节的页首,首页页码为零。
3. 每个 SQL Server 实例有四个系统数据库
master:由系统表组成,记录了安装以及随后创建的所有数据库的信息,不允许任何人对它进行修改。必须经常保留一份它的当前备份的拷贝。
model:记录了用户创建的所有临时表、临时数据和临时的存储过程,重新启动,该数据库被重新创建而不是恢复。
tempdb:是建立新数据库的模板,严格禁止删除model数据库。
msdb:SQL Server Agent服务使用的数据库。
以及一个或多个用户数据库。
4.创建数据库:CREATE DATABASE 数据库名;
修改数据库:ALTER DATABASE 数据库名 ;
删除数据库:Drop database数据库名;
第四章
1. SQL Server中的数据表分为:
永久表:创建后一直存储在数据库文件中,直到用户删除为止。
临时表分两种:
局部临时表:表名用#开头。只能由创建它的用户使用,在该用户连接断开时,它被自动删除。
全局临时表:表名用##开头。当前所有连接用户都可以使用,它在最后一个会话结束时被自动删除。
系统根据表名前有无#符号确定创建的是临时表还是永久表。
2.数据类型中只有字符串可以在后面加长度.
3.建表:CREATE TABLE 表名(空值约束(NULL or NOT NULL);主键约束(primary key constraint);唯一性约束(unique constraint);检查约束(check constraint);缺省约束(default constraint);外部键约束(foreign key constraint);规则(rule);缺省值(default)。)
修改表:ALTER TABLE 表名
ADD<新列名><数据类型>[完整性约束]
ALTER COLUMN 列名 新类型
DROP COLUMN 列名
ADD PRIMARY KEY(列名[,…])
ADD FOREIGN KEY(列名) REFERENCES 表名(列名)
ADD CONSTRAINT 约束名….
DROP CONSTRAINT 约束名
删除表:DROP TABLE 表名
创建课程表的语句如下:
CREATE TABLE COURSE
(
cno char(6) NOT NULL,
cname varchar(30) NOT NULL,
cbname varchar(30),
cedi char(8)
cpup varchar(20),
cisbn varchar(20),
cprice momey,
CONSTRAINT cno_PK PRIMARY KEY(cno),
CONSTRAINT cno_CK CHECK(cno LIKE ‘[C][0-9] [0-9] [0-9] [0-9] [0-9]’))
【例4-2】将教学管理数据库中的学生表属性“性别”的长度改为2。
其SQL语句的程序清单如下:
USE 教学管理 --打开教学管理数据库
GO
ALTER TABLE 学生表
ALTER COLUMN 性别 CHAR(2)
第五章
1.SQL(Structured Query Language):结构化查询语言,有数据定义、数据操纵、数据控制功能;
2. SQL用SELECT语句进行数据查询
SELECT语句的格式
SELECT [DISTINCT] <目标列表达式>[,…n]
FROM <表名或视图名> [,…n ]
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>] ]
[ORDER BY <列名2> [ASC | DESC] ]
SELECT语句的含义
根据WHERE条件,从FROM指定的表中找出满足条件的元组,按目标列表达式,选出属性值,形成结果表。
3. 【例5-1】查询全体学生的姓名、学号和电话号码。
SELECT 姓名, 学号, 移动电话
FROM 学生表
【例5-2】查询全体学生的详细信息
SELECT *
FROM 学生表
用“*”表示查询表的所有列。
【例5-3】将累计学分降低10%后显示出来
SELECT 姓名, 累计学分, 累计学分- 累计学分*0.1
FROM 学生表
【例5-4】将每个学生的累计学分降低10%后显示出来,要求查询结果表的标题用英语或字母显示。
SELECT 姓名 AS name, 累计学分 Ogpa, Ngpa=累计学分- 累计学分*0.1
FROM 学生表
无DISTINCT时,结果中可能含重复行
有DISTINCT时,自动消除结果中的重复行
【例5-5】查询每个院系有在读学生的专业。
SELECT DISTINCT 所在院系, 专业
FROM 学生表
【例5-6】查询来自杭州的所有学生。
SELECT * FROM 学生表
WHERE 籍贯='杭州'
【例5-7】查询累计学分在160分以下的学生姓名和累计学分。
SELECT 姓名, 累计学分 FROM 学生表
WHERE 累计学分<160
4. 【例5-21】查询选修了开课计划编号为’010101’的课程的学生学号、成绩以及加了10分后的新成绩,查询结果按原成绩降序、按新成绩升序排列。(分组查询)
SELECT 学号, 成绩, 成绩+10 AS New成绩 FROM 选课表
WHERE 开课号 = '010101'
ORDER BY 成绩 DESC, 成绩+10
5.
6. 使用GROUP BY子句进行分组
7. 【例5-29】查询学生的基本信息及其选课信息。
SELECT 学生表.*, 开课号, 成绩 FROM 学生表, 选课表
WHERE 学生表.学号= 选课表.学号
【例5-30】查询选修了开课计划编号为“010101”的课程的学生学号和姓名。
SELECT 学生表.学号, 姓名 FROM 学生表, 选课表
WHERE 学生表.学号= 选课表.学号 AND 开课号 = '010101'
8. 为FROM子句后的表起别名,FROM {<表名> [ [AS] <别名> ] }[,…n];
9. 将连接条件和普通查询条件分开。
格式:
SELECT子句
FROM <表名> {JOIN <表名> ON <连接条件> }[ …n ]
[WHERE <普通查询条件>]
[其他子句]
【例5-32】用JOIN和ON关键字实现例5-31的查询。
SELECT DISTINCT Z.学号, 姓名
FROM 选课表 X JOIN 选课表 Y ON Y.学号!=X.学号 AND Y.开课号 = X.开课号 JOIN 学生表 Z ON Y.学号=Z.学号 WHERE X.学号= 'S060110'
联合查询:
10. 插入单个元组: INSERT…VALUES语句, 格式为:
INSERT [INTO] <表名> [ ( <列名>[,…n] ) ]
VALUES ( <表达式>[,…n] )
【例5-40】将(’S060102’, ’010201’)插入Enrollment表。
INSERT INTO 选课表 (学号, 开课号)
VALUES ('S060102', '010201')
11. 数据修改语句:UPDATE, 格式为:
UPDATE <表名> SET {<列名> = <表达式>}[,…n]
[ FROM <表名>[,…n] ]
[ WHERE <修改条件> ]
【例5-44】将所有学生的累计学分增加3分。
UPDATE 学生表 SET 累计学分=累计学分+3
【例5-45】将计算机专业所有女生的籍贯改为“杭州”,累计学分增加3分。
UPDATE 学生表 SET 累计学分=累计学分+3, 籍贯='杭州'
WHERE 专业 = '计算机' AND 性别 = '女'
12. 数据删除语句:DELETE, 格式为:
DELETE [FROM] <目标表名>
[ FROM <表名>[,…n] ]
[ WHERE <删除条件> ]
【例5-48】将Student表清空。
DELETE FROM Student
【例5-49】从Student表中删除计算机专业所有女生的信息。
DELETE FROM 学生表
WHERE 专业 = '计算机' AND 性别 = '女'
课后作业:
查询某个项目的员工姓名
select 姓名
from 员工参与项目表,员工表
where 项目编号='J003'
and 员工表.员工号=员工参与项目表.员工号
查询某个职工所参加的项目的项目号、项目名称、项目所在地
select 项目表.项目编号,项目名称,所在地方
from 员工参与项目表,项目表
where 员工参与项目表.项目编号=项目表.项目编号
and 员工号='2002'
对所有项目主管的工资增加10%
update 员工表
set 工资=工资*1.1
where exists
(select *
from 项目表
where 项目表.项目主管=员工表.员工号)
第六章
1.索引:对数据库表中一个或多个列的值进行排序的结构。每个索引都有一个特定的搜索码与表中的记录相关联,索引按顺序存储搜索码的值。
2.索引的作用:
1.通过创建唯一索引,可以保证数据记录的唯一性。
2.可以大大加快数据检索速度。
3. 可以加速表与表之间的连接。
4. 显著减少查询中分组和排序的时间。
5. 提高系统性能。
3.组织结构
堆集结构、聚集索引结构、非聚集索引。
索引的类型:聚集索引和非聚集索引、主键索引和非主键索引、唯一索引和非唯一索引、单列索引和复合索引(简答题)
4. 每个表只允许建立一个聚集索引,非聚集索引最多249个。
5. 主键索引:主键创建会自动创建聚集索引。
6. 唯一索引:索引列中不包含重复值。
7. 单列索引:指对表中单个列建立索引。
索引的操作(4分)
8. CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
INDEX 索引名
ON 数据表名|视图名( 字段名 [ ASC | DESC ] [ ,...n ] )
[WITH
[PAD_INDEX]
[[,]FILLFACTOR=填充因子]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]]
[ ON 文件组名]
USE 教学管理
GO
CREATE UNIQUE CLUSTERED INDEX IX_学号 ON 学生表(学号)
WITH(pad_index,fillfactor=100)
【例6-4】为数据库“教学管理”中的数据表关于“选课表.学号”降序、“选课表.开课号”升序建立组合唯一索引IX_学号_开课号,填充因子为90,在插入数据时,可以忽略重复的值。如果已经存在IX_学号_开课号索引,则先删除后重建。
USE 教学管理
GO
CREATE UNIQUE INDEX IX_学号_开课号 ON 选课表(学号 DESC,开课号 ASC)
WITH(
PAD_INDEX=ON, --保持索引开放的空间
FILLFACTOR=90, --填充因子90
IGNORE_DUP_KEY=ON, --忽略重复键值
DROP_EXISTING=ON) --如果存在索引则删除
USE 教学管理
GO
DROP INDEX 选课表.IX_选课表_成绩
9.
1.create unique nonclustered index emp_id
on 员工表(员工号 asc)
with
fillfactor=80
2.sp_rename '员工表.emp_id','员工表_员工号'
3.create nonclustered index 员工_项目_index
on 员工参与项目表(员工号 asc ,项目编号 desc)
with(pad_index=on,
fillfactor=50)
4.drop index 员工参与项目表.员工_项目_index
drop index 员工表.员工表_员工号
第七章
1. 事务:一系列的数据库操作,是数据库应用程序的基本单元,也是并发控制和数据恢复的基本单位;
是一个用户定义的完整的工作单元,一个事务内的所有语句被作为整体执行,要么全部执行;
2.事务的特性:
原子性:事务是数据库的逻辑工作单位,事务中的操作要么都做,要么都不做。
一致性:事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
隔离性:一事务的执行不能被其它事务干扰。
持续性(永久性):指事务一旦提交,则其对数据库中数据的改变就应该是永久的。事务是恢复和并发控制的基本单位。
3.如果用户并发访问期没有保证多个事务正确交叉运行,可能会出现一下问题:
丢失修改或被覆盖、读脏数据,不能重复读、幻影读。
4.影响事务正常运行的故障:事务内部故障、系统故障、介质故障、计算机病毒。
5.事务分类:显示事务、隐式事务、自动事务。
6. 显式事务是指由用户执行T-sql事务语句而定义的事务,这类事务又称做用户定义事务。定义事务的语句包括:
BEGIN TRANSACTION:标识一个事务的开始,即启动事务。
COMMIT TRANSACTION、COMMIT WORK:标识一个事务的结束,事务内所修改的数据被永久保存到数据库中。
ROLLBACK TRANSACTION、ROLLBACK WORK:标识一个事务的结束,说明事务执行过程中遇到错误,事务内所修改的数据被回滚到事务执行前的状态。
7. 执行SET IMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。
在隐式事务模式下,当执行一个语句命令时,可使SQL Server重新启动一个事务:
需要关闭隐式事务模式时,调用SET IMPLICIT_TRANSACTIONS OFF
8.事务保存点:SAVE TRANSACTION,
9.
10. 两种并发控制: 乐观并发控制、悲观并发控制。
11. SQL Server 锁的粒度
1)RID:行标识符,锁定表中单行数据。
2)键值:具有索引的行数据。
3)页面:一个数据页面或索引页面。
4)区域:一组连续的8个数据页面或索引页面。
5)表: 整个表,包括其所有的数据和索引。
6)数据库:一个完整的数据库。
12. 1级封锁协议可防止丢失修改以及保证事务可恢复、级封锁协议可以防止丢失修改和读“脏”数据、3级封锁协议可防止丢失修改、读脏数据和不可重复读和容易造成比较多的死锁。
13.
14.事务隔离级别:未提交读、提交读、可重复读、可串行读
第八章
1. 批是一组SQL语句的集合,一个批以结束符GO而终结。
2. 使用批的基本规则:
(1)所有CREATE语句应单独构成一个批,不能在批中和其它SQL语句组合使用。
(2)使用ALTER TABLE语句修改表结构后,不能在同一个批中使用新定义的列。
(3)EXCUTE语句为批中第一个语句时,可以省略EXCUTE关键字,否则,必须使用EXCUTE关键字。
(4)批命令GO和SQL语句不能在同一行上。但在GO命令中可以包含注释。
3.一个事务有多个批,一个批有多个事务。
4.单行注释:--,多行:/*…*/;
5. 使用批的基本规则:
(1)所有CREATE语句应单独构成一个批,不能在批中和其它SQL语句组合使用。
(2)使用ALTER TABLE语句修改表结构后,不能在同一个批中使用新定义的列。
(3)EXCUTE语句为批中第一个语句时,可以省略EXCUTE关键字,否则,必须使用EXCUTE关键字。
(4)批命令GO和SQL语句不能在同一行上。但在GO命令中可以包含注释。
6. T-SQL使用SELECT或SET命令为变量赋值,其语法如下:
SELECT {@变量名=<表达式>}[, …n]
SET @变量名=<表达式>
注意:一条SET语句只能为一个变量赋值,而一条SELECT可以为多个变量赋值。
7. T-SQL的连接运算符是“+”.
8.
9.流程控制:
块语:BEGIN
T-SQL语句[ …n]
END
条件语句:IF <条件表达式> 语句1
ELSE 语句
CASE语句:
CASE 具有两种格式:
简单CASE语句:
CASE <输入表达式>
WHEN <当表达式1> THEN <结果表达式1>
WHEN <当表达式2> THEN <结果表达式2>
…………………………………………
WHEN <当表达式n> THEN <结果表达式n>
[ ELSE <结果表达式n+1> ]
END
CASE搜索语句:
CASE
WHEN <条件表达式1> THEN <结果表达式1>
WHEN <条件表达式2> THEN <结果表达式2>
…………………………………………
<条件表达式n> THEN <结果表达式n>
[ ELSE <结果表达式n+1> ]
END
【例8-30】显示每个学生的学号及学生成绩(按优、良、中、及格、不及格、未知显示)。
SELECT 学号, 成绩 = CASE
WHEN 成绩>=90 THEN '优'
WHEN 成绩>=80 THEN '良'
WHEN 成绩>=70 THEN '中'
WHEN 成绩>=60 THEN '及格'
WHEN 成绩>=0 THEN '不及格'
END
FROM 选课表
循环语句:
WHILE <条件表达式> BEGIN
T-SQL语句[ …n]
[BREAK] /*跳出循环*/
[CONTINUE] /*进入下一次循环*/
END
等待语句:
WAITFOR { DELAY <’时间’> | TIME <’时间’>
| ERROREXIT | PROCESSEXIT | MIRROREXIT }
其中时间必须为DATETIME 类型的数据,如’11:15:27’,但不能包括日期。各关键字含义如下:
DELAY:用来设定等待的时间,最多可达24小时;
TIME:用来设定等待结束的时间点;
ERROREXIT:一直等待到处理非正常中断;
PROCESSEXIT:一直等待到处理正常或非正常中断;
MIRROREXIT: 一直等待到镜像设备失败
GOTO语句:GOTO <标号>
第九章
1. 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。
2. 视图(虚拟表)对应于三级模式中的外模式(用户模式),它是从一个或几个基本表导出的表,由CREATE VIEW命令创建。
3.视图的优点:
(1)视图能够简化用户的操作
(2)视图使用户能以多种角度看待同一数据
(3)视图为数据库重构提供了一定程度的逻辑独立性。
(4)视图能够对机密数据提供安全保护
4. 创建视图(4分)
CREATE VIEW [ < database_name > .] [ < owner > .] view_name [ ( column [ ,...n ] ) ]
[ WITH < view_attribute > [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]
< view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
【例9-1】使用简单的 CREATE VIEW
下例创建具有简单 SELECT 语句的视图。当需要频繁地查询列的某种组合时,简单视图非常有用。
USE 教学管理
GO
CREATE VIEW V_课程表查询
AS
SELECT 课号, 课名, 教材名称, 编著者, 出版社
FROM 课程表
GO
【例9-2】使用WITH ENCRYPTION
下例使用WITH ENCRYPTION选项和内置函数,使用函数时,必须为派生列指定列名。
USE 教学管理
GO
CREATE VIEW V_学生平均成绩 (学号, 姓名, 平均成绩)
WITH ENCRYPTION
AS
SELECT S.学号, 姓名, AVG(成绩)
FROM 学生表 S, 选课表 E
WHERE S.学号=E.学号
GROUP BY S.学号,姓名
GO
5. 修改视图
语法格式:
ALTER VIEW [ < database_name > .] [ < owner > .] view_name [ ( column [ ,...n ] ) ]
[ WITH < VIEW_attribute > [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]
< VIEW_attribute > ::={ ENCRYPTION | SCHEMABINDING | VIEW_METADATA }
6. 视图重命名
使用系统存储过程sp_rename 对已创建的视图进行重命名。
语法格式:
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
7. 删除视图
语法格式:
DROP VIEW { View_name } [ ,...n ]
8.
9. 更新视图是指通过视图来插入(INSERT)、删除(DELETE)和修改(UPDATE)数据。
10.
11.
create view V1_视图(员工号, 姓名, 所在部门名)
as
select 员工号, 姓名,部门名
from 员工表,部门表
where 员工表.所在部门号=部门表.部门号
and 员工号 in
(select 员工号
from 员工表
where not exists
(select *
from 项目表
where not exists
(select *
from 员工参与项目表
where 员工号=员工表.员工号
and 项目编号=项目表.项目编号)))
select * from V1_视图
第十章
1. 游标:是一种能从包括多条数据记录的结果集中每次提取一条记录的机制 。
2.游标的三种类型:Transact-SQL游标、API游标、客户游标;
3.
4.打开游标:OPEN,读取游标:FETCH,关闭游标:CLOSE,释放游标:DEALLOCATE,
修改定位:UPDATE 表名
SET 子句
WHERE CURRENT OF {{[GLOBAL] 游标名}
|游标变量}
游标定位删除DELETE语句的格式为如下
DELETE FROM 表名
WHERE CURRENT OF {{[GLOBAL] 游标名}
|游标变量}
5.针对员工表定义一个只读游标“CUR1_游标”,逐行显示员工的所有信息:
use xmgl
go
declare @员工号 char(4),@姓名 char(8),@性别 char(2),@出生年月 datetime,@技术职称 char(10),@工资 numeric(18, 0) ,@部门号 char(4)
declare CUR1_游标 cursor
for
select 员工号,姓名,性别,出生年月,技术职称,所在部门号,工资
from 员工表
for read only
open CUR1_游标
fetch next from CUR1_游标 into @员工号,@姓名,@性别,@出生年月,@技术职称,@部门号,@工资
while @@fetch_status=0
begin
select @员工号 as 员工号,@姓名 as 姓名,@性别 as 性别,@出生年月 as 出生年月,@技术职称 as 技术职称,@部门号 as 部门号,@工资 as 工资
fetch next from CUR1_游标 into @员工号,@姓名,@性别,@出生年月,@技术职称,@部门号,@工资
end
close CUR1_游标
deallocate CUR1_游标
十一章
(1)重复使用编程代码,减少编程开发时间,提高工作效率。
(2)隐藏SQL细节,把SQL繁琐的工作留给数据库开发人员,而程序开发员则集中处理高级编程语言。
(3)维修集中化,可以在一个地方做业务上的逻辑修改,然后让这些修改自动应用到所有相关程序中。
(4)可在另一个SQL语句中直接调用。
(5)函数必须始终返回一个值(一个标量值或一个表格)。
3.(4分,用户自定义函数)
【例11-2】创建内联表值型函数,返回指定学院学生的信息。
USE 教学管理
GO
CREATE FUNCTION F_学生信息(@院系 CHAR(20))
RETURNS TABLE
AS
RETURN(
SELECT 学号, 姓名, 性别, 院系, 专业, 籍贯
FROM 学生表
WHERE 所在院系=@院系 )
4.
5.创建内联表值型函数:
use xmgl
go
create function F2_自定义函数(@员工号 char(4))
returns table
as
return(
select 姓名 as 员工姓名,datediff (yy, cast (出生年月 as datetime), '2021') as 年龄,性别,部门名 as 所在部门
from 员工表,部门表
where 员工表.所在部门号=部门表.部门号
and 员工号=@员工号)
select * from dbo. F2_自定义函数('2001')
select * from F2_自定义函数('2001')
十二章
exec sp_helpdb pubs
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。