数据库原理及应用 SqlServer期末复习总结_sqlserver -u root -p

                                                                                                                                                                                   SQL Server

数据库：

              狭义：存储数据的仓库

          广义：可以对数据进行存储和管理的软件，以及数据本身，-》数据库

数据库由表、关系、操作组成

B/S架构

• 数据库简介

数据库和数据结构的区别：

              区别：

                            数据库，应用软件级别研究数据的存储和操作

                            数据结构 ，系统软件研究数据

       数据库原理强调他的原理-----》理论部分

建数据库

删除数据库

连接

一、基础知识

1.1数据库

1.存储数据的仓库，数据是有组织的的进行存储  DataBase(DB)

2.数据库管理系统   软件

3.SQL 操作关系型数据库的编程语言，定义了一套操作关系型的数据库--标准

1.2主流数据库管理系统

1.Oracle

2.MySQL

3.SQL Server

4.PostgreSQL

5.IBM Db2

6.Access

7.SQLite

8.MariaDB

9.Azure SQL Database

10.Hivezx

数据（data）、数据库（database）、数据库管理系统（DBMS）、数据库系统（DBS）

1.3 DBMS的主要功能

1.数据定义功能--->数据定义语言DDL

2.数据组织、存储和管理

3.数据操纵功能--->数据操纵语言DML

4.数据库的事务管理和运行管理

5.数据库的建立和维护功能

6.其他

1.4 历史发展

人工管理阶段->文件系统阶段->数据库系统阶段（数据高共享，低冗余，高独立）

数据管理系统角度：三级模式和两级映射   三级模式（模式、外模式、内模式）

用户角度：但用户结构、主从式、分布式、客户/服务器、浏览器/应用服务器/数据库服务器多层结构

数据模型

1.4.1两类数据模型

1.概念模型    2.逻辑模型和物理模型

1.5概念模型

实体、属性、码、域、实体型、实体集、联系

实体-联系方法图（E-R）

1.6数据模型的组成要素

常用数据模型

层次数据模型、网状数据模型、关系数据模型、面向对象数据模型

1.7关系模型

关系模型的完整性约束

1.实体完整性

2.参照完整性

3.用户定义完整性

1.8 关系型数据库（RDBMS）

概念：建立关系模型上的，由多张相互连接的二维表组成的数据库

特点：

       1.使用表存储数据，格式统一，便于维护

       2.使用SQL语言操作，标准统一，使用方便

1.3 CMD Win+r进入数据库命令

1.win+r

2.cmd

3.输入  mysql -u root -p

密码：123456

第二章 关系数据库

1.域：一组具有相同数据类型的值的集合

2.笛卡尔积：域上一种集合运算       元组，分量，基数

3.关系

操作：查询和更新

查询：选择、投影、连接、除、并、交、差、笛卡尔积

更新：插入、删除、修改

2.1关系模型的完整性约束

2.1.1实体完整性

       1.所有表都必须有主码

       2.主码值不能为空，主码值不能重复

2.1.2参照完整性

       1.关联关系

       2.外码：这个属性一个表中的主码，对于另一个表来说这个属性就是外码

                     外码要求：或者值为空

                                          或者等于其所关联的主表中的某个元组的主码值

                     外码值可以有重复

2.1.3用户定义完整性

       类型和长度限制

       取值范围限制

2.1.4关系代数运算

选择、投影、连接、并、交、差、笛卡尔积

第三章 关系数据库标准语言

3.1 SQL简介

SQL：是 MySQL 的主要查询语言，可以执行各种数据操作，如数据查询、数据更新、数据插入和数据删除等。

3.1.1 SQL通用语法

1，可以单行或多行书写，以分号结尾

2，SQL语句可以使用空格/缩进来增强语句的可读性

3，MySQL数据库的SQL语句不区分大小写，关键字建议使用大小

4，注释

       单行：--注释内容   #注释内容

       多行：/*注释内容*/

3.1.2一体化

DDL(数据定义语言)，DML(数据操纵语言)、DCL(数据控制语言)

功能：增、删、改、查

查询：select

定义：create(创建),drop（删除）,alter（）

操作：insert（插入）,update,delete（删除）

控制：grant,revoke

视图       基本表   存储文件（file）  存储文件（file）{(.dbf),(.mdb),(.mdf)}

  用来对数据库表中的数据库进行增删改

3.2数据定义

3.2.1 数据类型

1.数值型

       整数型：bigint、int、smallint、tinyint

       小数型：numeric、decimal

       货币型：money、smallmoney

       近似数值类型：float、real

       逻辑型：bit

2.字符串类型

       字符型：char、varchar、text、ncahr、image

3.日期时间型

       datetime、Smalldatetime

4.逻辑型
 

3.2.2数据库概述

       1.系统数据库

              master数据库：记录系统中所有系统级信息

              model数据库：模板

              msdb数据库：保存关于调度报警、作业、操作员等信息

              tempalb数据库：临时数据库

每个数据库包含仅一个主数据文件（.mdf）,至少一个日志文件（.ldf）,零个或多个辅助数据文件（.ndf）

每个文件都有逻辑文件名，逻辑文件名

文件名级位置物理、初始大小、增长方式、最大空间

       2.用户数据库

3.2.3定义与删除

       建库：create database  删除库：drop database

       建表：create table         删除表：drop table 修改：alter table

       视图：create view         drop view

       索引：create index        drop index

1.定义库：create database  库名；

2.指定方式定义库：create datebase 库名 on (

                                   name=逻辑文件名，

                                   filename=物理文件名,

                                   size=初始大小,

                                   maxsize=最大空间,

                                   filegrowth=自动增量）

                            log on

(name=逻辑文件名,

filename=物理文件名，

size=初始大小，

maxsize=最大空间，

filegrowth=自动增量）

列级完整性约束，定义

not null:非空

default:默认

unique:不能重复

check：限制取值范围

primary key：主码       禁止为null和唯一

foreign key：外码       则是在一个数据表中的字段，其值来自于另一个数据表的主码。它是一种引用完整性约束，用于建立两个表之间的关联

4.基本表的定义、删除与修改

       定义外码约束：foreign key (列名) references <外表名> （<外表列名>）

例：

删除：drop table 表名;

修改：alter table 表名;

5、索引的建立与删除

索引定义：对数据库表中一列或者多列的值进行排序的一种结构。

作用：相当于图书的目录。

3.3数据更新操作

3.3.1插入数据

第一种：insert into Student values(‘5667’,’成东’);

第二种：insert into Student (no,name) values (‘5667’,’成东’);

3.3.2修改数据

update student set age=22 where sno=’123’；

update student set Sage=Sage+1;

3.3.3删除数据

delete from Student where Sno=’1234’；

3.4 数据查询

3.4.1单表查询

       查询指定列

              select Sno,Sname,from student;

       选出所有属性列

              select * from Student;

       查询经过计算的列

              select Sname,2023-Sage from Student;

       给查询结果中的列指定列名

              select Sname,2020-Sage as ‘出生年份’ from Student;

消除查询结果中重复的行

       select distinct sno from sc;//一般在子句中查询（本质没有解决表中重复的数据）

空值（NULL）

is null 或is not null

order by 子句

       作用：改变查询结果的显示顺序

       升序：asc     降序：desc 默认升序

聚合函数

count统计数量

max 最大值

min 最小值

avg 平均值

sum 求和

select sum(Grade) from sc where sno=’34323’;

注意：计算函数不能出现在where里

having 与 where 的区别

       where作用于基表或者视图，从表中选择满足条件的元组

       having 作用于组，从各组选择满足的条件的组

3.4.2连接查询

       连接两个表的条件称为连接条件或连接谓词

交叉连接

       非限制连接，两个广义笛卡尔积

       select st.sname,sc.name from student st,sc;

内连接

定义：等值连接，将目标列中重复属性列去掉

       第一种：select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno;

       第二种：select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC inner join SC on Student.sno=SC.sno;

外连接

定义：可以限制一个表，而对另外一个表不加限制（即所有的行都只出现在结果集中）

       左外连接（left outer join）

select Student.sno,sname,ssex,sage,sdept,cno,Grade from Student left outer join sc on Student.Sno=SC.sno

       右外连接（right outer join）

       全外连接（full outer jion）

自身连接

复合条件连接

              where子句中除了含表的连接条件外，还有其他条件限制；

3.4.3嵌套查询

       语句称为查询块

       将一个查询块嵌套在另一个查询块的where子句中或having短语的条件中的查询称为嵌套查询

       子查询限制：不能使用order by子句

带有in谓词的子查询

       select Sno,Sname,Sdept from Student where Sdept in(select Sdept from Student where Sname=’ghj’);

带有比较运算符的子查询

       select Sno,Cno from SC as x where Grade >=(select AVG(Grade) from SC as y where y.Sno=x.Sno)

带有exists谓词的子查询

       使用exists谓词测试子查询是否存在返回值

       select Sname from Student where exists (select * from sc where Sno=Student.Sno and Cno=’2’);

3.4.4 查询时建表

--（12）对每个班，求学生的平均年龄，并把结果以“bj_age”表名存入数据库。

--（提示：可使用 select„into„from ）

 select clno as '班级',avg(sage) as '平均年龄' into  bj_age from student1 group by clno;

3.5 含有子查询的数据更新

将子查询结果插入指定表中

要求：select 子句的目标列必须与into子句的属性列匹配【值的类型相同，值的个数相等，一一对应】

1.查询信息并将结果保存

select * into computer from student where sdept=’计算系’;

3.6 视图

视图简化用户操作

视图使用户能以多种角度看待同一数据

视图对重构数据库提供了一定程度的逻辑独立性

视图能够对机密数据提供安全保护

适当的利用视图可以更清晰的表达查询

视图就是对数据库中数据的“过滤器”，它可以根据用户的需求和权限，只显示用户需要的数据。

第四章 数据库的安全性

4.1概述

保护数据库，防止非法篡改操作造成的数据泄露，篡改或损坏

安全级别：数据库系统级，操作系统级，网络级，物理级，人员级

静态口令—用户名/口令，简单易行

动态口令—一次一密

智能卡鉴别—不能复制的硬件，硬件加密

生物特征鉴别—指纹，虹膜，掌纹

非法：非法读取，修改，破坏

4.2安全控制

非法使用数据库：用户编写一段合法的程序，绕过DBMS及授权机制，通过操作系统直接存取，修改，备份

存取控制机制：将用户权限登记到数据字典中去

       合法权限检查

       用户权限

              数据对象 操作类型

1.权限授予grant

              语法：

1.查询Student表权限授权给用户UI

       grant select on student to UI;

2.把对course的所有权限授予给用户U1和U2

       grant all privileges on course to U1,U2;

3.将对表SC的grade列的update权限授予U4，并允许他将此权限授权于其他用户

       grant update(grade) on sc to U4 with grant option;

回收权限—DBA或revoke

cascade:级联回收

restrict：受限回收

1.把用户U4修改学生成绩的权限收回

       revoke update(grade) on sc from cascade;

禁止权限—deny

用户管理

       创建用户        exec sp_addlogin UI,123;

       将登录用户U1添加为数据库用户----------use school ecex Sp_adduser U1;

第二种方法

       创建登录用户-------exec Sp_addlogin U2,123

       授权访问数据库--------use school exec Sp_grantdbaccess U2;

数据库角色

       1.创建一个角色-----create role r1;

       2.授权----grant update,insert on Student to R1;

       3.将这个角色授予u1

       exec SP_droprolemember r1,u1;

      

4.3视图控制

       可视化控制

第五章 数据的完整性

前提是防止数据库存在不符合语义的数据，及不正确的数据

DBMS:  1.提供定义完整性约束条件的机制，2.提供完整性检查的方法 3.违约处理

5.1实体完整性

5.1.1实体完整性定义

create table 用primary key 定义主码

alter table 用add primary key

5.1.2实体完整性检查和违约处理

1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改

2. 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

5.2 参照完整性

5.2.1 参照完整性定义

Create table 用foreign key 定义外码

Alter table 用add foreign key 定义外码

必须用 REFERENCES 短语指明这些外码参照哪些表的主码

一个参照完整性将两个表或者多个表中的相应元组联系起来

5.2.2参照完整性检查和违约处理

5.3 用户定义完整性

5.3.1定义

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求

属性上的约束条件的定义

1. create table时

列值非空

列值唯一

检查列值是否满足一个布尔表达式

元组上的约束条件的定义

用户定义的完整性约束条件检查和违约处理

1.不允许取空值

2.列值唯一

3.用check短语指定列值应该满足的条件

5.4 完整性约束命名子句

5.5触发器

5.5.1定义

：用户定义在关系表的一类由事件驱动的特殊过程

注意一旦定义，任何用户对数据库的增、删、改操作均由服务器自动激活相应的触发器，在DBMA核心层进行集中的完整性控制。

触发器可以实施比FOREIGN  KEY约束和CHECK约束更为复杂的检查和操作，具有更精细和更强大的数据控制能力 。

SQL3(SQL99)标准支持触发器，不同的DBMS实现的触发器语法各不相同，互不兼容，但阐述的概念是通用的。

5.5.2 定义触发器

语法：

Create trigger <触发器名> on <表名>for <触发事件>as <触发动作体>

1. 创建者：表的拥有者

2. 触发器名：同一数据库中，触发器名唯一

3. 表名：触发器的目标表，与触发器名在同一数据库中。

4. 触发事件INSERT，DELETE，UPDATE.

5. 触发动作体sql过程块（begin、sql语句组、end）

• • INSERT事件触发器：当对指定表执行INSERT操作时，将插入表中的新数据复制并送入inserted表（系统建立的临时特殊逻辑表）中，按触发动作体中的语句进行检查并处理。
  •  DELETE事件触发器：当对指定表执行DELETE操作时，将删除的元组放入deleted表（系统建立的临时特殊逻辑表）中，按触发动作体中的语句进行检查并处理。
  •  UPDATE事件触发器：当对指定表执行UPDATE操作时，先把将要被更新的原数据移入deleted表中，再将更新后的新数据的备份送入inserted表中，根据触发动作体中的语句对deleted表和inserted表进行检查和比较，决定如何处理。

 rollback  transaction ：回滚事务，先前进行的操作全部撤销

例

我们在对学生表进行插入时，要求学生的年龄不能超过25岁，如果超过25岁，则输出“插入年龄不能超过25岁”的提示。

          CREATE  TRIGGER  stu_Insert

           ON   student

           FOR    INSERT        /*触发事件是插入操作*/

           AS

               BEGIN           /*定义触发动作体，是SQL过程块*/

                  IF ( select   sage   from  inserted )>25

                     BEGIN   

                        print  '插入年龄不能超过25岁!‘

                        rollback  transaction

                     END

               END       

5.5.3 激活触发器

1.定义：触发器的执行，是由触发事件激活的，并由数据库服务器自动执行

       一个数据表，同一个操作可定义多个触发器

       遵循“先创建先执行”的原则

5.5.4 删除触发器

语法：drop trigger stu_insert

第六章 关系数据库设计理论

6.1 事务的基本概念

6.1.1定义

是用户定义的一个数据库操作序列，是恢复和并发控制的基本单位

对比：

       一个事务可以是一条sql语句，一组sql语句或整个

一个程序包含多个事务

6.1.2 显示定义的方式

第一种：begin transaction 事务名 SQL语句1，SQL语句2 commit tansaction

第二种：begin transaction 事务名 SQL语句1，SQL语句2 rollback transaction

6.1.3 事务的ACID特性

1．原子性：要么做，要么都不做

2. 一致性：

3.隔离性

4.持续性

BEGIN TRANSACTION  tran1

  UPDATE  支付表  SET  帐户总额 = 帐户总额 - 1

         WHERE 帐户名 = ‘A’

  UPDATE  支付表  SET  帐户总额 = 帐户总额 + 1

               WHERE 帐户名 = ‘B’

COMMIT  TRANSACTION

事务的acid特性遭到破坏的因素

1. 多个事务的并行运行时，不同的事务操作有交叉情况
2. 事务在运行过程中被强迫停止

6.2 数据库恢复概述

6.2.1 定义

是指把数据库从错误状态恢复到某一已知的正确状态。

6.3 数据库故障的种类

1.事务故障

2.系统故障

3.介质故障（硬故障）

4.计算机病毒（人为故障）

6.4 恢复的实现技术

6.4.1 基础

1.建立冗余数据

   2.数据存储，数据备份

   3.登记日志文件

6.4.2 转储方式

1. 静态转储
2. 动态转储
3. 海量转储与增量转储

6.4.3  日志文件

日志文件：日志文件(log)是用来记录事务对数据库的更新操作的文件

分类：1.以记录为单位 2.以数据块为单位

作用：进行事务故障恢复，进行系统故障恢复，协助数据库备份进行介质故障恢复

6.4.4 登记日志文件

为保证数据库是可以恢复

2条原则

登记的次序严格按并发事务执行的时间次序

必须先写日志文件，后写数据库

6.5 恢复的策略

··故障的类型不同，恢复的策略也有所不同

6.5.1 事务故障的恢复

1.反向扫描日志文件，查找该事务的更新操作

2.对事务的更新操作执行逆操作。即将日志记录中“更新前的值”写入数据库

3.继续反向扫描日志文件，如此处理下去，指导此事务的开始标记 下去，事务故障就恢复了

6.5.2 系统故障的恢复

1、Undo故障发生时未完成的事务

2、Redo故障发生前已提交的事务

系统故障的恢复是系统在重新启动时自动完成的，不需要用户干预。

6.5.3 介质故障

1、首先装入最近转储的数据库副本（离故障发生时刻最近的转储副本），使数据库恢复到最近一次转储时的一致性状态；

2、装入转储结束时刻的日志文件副本；

3、启动系统恢复命令，由DBMS完成恢复功能，即重做已完成的事务。

介质故障恢复比较费时，为了提高数据库的可用性，

          可采用数据库镜像（Mirror）技术。

6.6 具有检查点的恢复技术

6.6.1 问题的提出

搜索整个日志将耗费大量的时间

重做处理：重新执行，浪费了大量时间

解决方案：

在日志文件中增加检查点记录（checkpoint）

增加重新开始文件

恢复子系统在登录日志文件期间动态地维护日志

6.6.2 检查点技术

1.动态维护日志文件的方法

步骤:

1. 将当前日志缓冲区中的所有日志记录写入磁盘的日志文件上
2. 恢复子系统在登录日志文件期间动态地维护日志
3. 在日志文件中写入一个检查点记录
4. 把检查点记录在日志文件中的地址写入一个重新开始文件

2. 建立检查点的时间

定期和不定期

6.6.3 利用检查点技术的恢复策略

6.7 SQL Server数据库备份与恢复

6.7.1 创建备份设备

在SSMS工具的对象资源管理器中，展开“服务器对象”。

在“备份设备”上单击鼠标右键，在弹出的菜单中单击“新建备份设备”命令，打开 “备份设备”窗口。

在此窗口中进行相应设置。备份设备名bk1  ，

     备份设备的默认文件扩展名为.bak，一定要给出。

6.7.2 进行数据库备份

1.完整备份

2.差异备份

3.事务日志备份

设置恢复模式

在要设置恢复模式的数据库名上右击鼠标

在弹出的菜单中选择“属性”命令

在弹出的属性窗口“选择页”中，单击左边的“选项”，在右侧窗口中找到“恢复模式”选项。

备份策略

完整备份

完整备份 + 日志备份

完整备份 + 差异备份 + 日志备份

进行数据库备份

用SSMS实现

用T-SQL语句实现

6.7.3 恢复数据库