说明:


本文档可能因为某些原因或纯粹因为我本人忘了,导致更新不及时。所以请有条件的同学到钉钉群的相关文档中寻找想要的内容。一般是:群文件/数据库高级设计/数据库高级设计.DOCX这个目录。
以下部分由Word转换而来。机器转换,出现语法混乱或排版问题请评论反馈,我有时间就修。

数据库高级设计

目标:设计符合规范的数据库、实现基于数据库设计的程序开发

期望:课前百度预习课程内容,即使看不懂也要坚持看完;课上做好笔记并按时完成作业要求;课后及时总结,多模仿,多练习,多浏览技术论坛,获取他人的开发经验。

数据库的设计

回顾:

  1. 对数据库数据的操作的语句及语法是什么
  2. 常用的聚合函数有哪些
  3. 表连接有哪几种类型

本章目标:

  1. 了解设计数据库的步骤
  2. 掌握如何绘制数据库的E-R图
  3. 掌握如何绘制数据库模型
  4. 使用三大范式实现数据库设计规范化

为什么要设计数据库

引入:修建茅草屋需要设计吗?修建大厦需要设计吗?

结论:在比较复杂的项目当中,我们需要先通过设计思考项目的可行性

分析:

糟糕的数据库设计往往有较多的数据冗余,存储空间浪费;内存空间浪费;还可能会出现数据更新和插入的异常

良好的数据库设计会尽可能节省数据的存储空间;能够保证数据的完整性;能够方便进行数据库应用系统的开发

软件项目开发周期中的数据库设计周期

  • 需求分析阶段:分析客户的业务和数据处理需求

    • 信息收集
    • 标识实体(entity)
    • 标识每个实体的属性/字段(attribute)
    • 标识实体之间的关系(relationship)
  • 概要设计阶段:设计数据库的E-R模型图,确认需求信息的正确与完整
  • 详细设计阶段:应用三大范式审核数据库结构
  • 代码编写阶段:物理实现数据库,编码实现应用
  • 软件测试阶段
  • 安装部署

绘制E-R图

含义符号
实体矩形
属性圆形
关系菱形

映射基数

实体与实体之间的多种关系类型

  • 一对一(1:1):主表和从表的信息一一对应
  • 一对多(1:N):主表的一条信息对从表的多条信息
  • 多对一(N:1):主表的多条信息对应从表的一条信息
  • 多对多(N:N):主表的一条信息对应从表的多条信息,从表的一条信息也对应主表的多条信息。当涉及多对多关系时,必须在两表之间创立一张新的关系表。例如:客人信息表和客房信息表的关系,一个客人可以订多个房间,一个房间可以住多个客人。遇到这种情况则必须创建入住信息表,用于记录两表的多对多关系

E-R图的设计工具有:

最典型的:visio、rose

一个关系的属性名的集合,称之为关系模式

E-R图

数据库字典的制作

在数据库的下拉列表中,右键选择数据库关系图,选择新建,则可以根据需要,创建关系图样

三大范式

不合规范的表设计:

  • 信息重复
  • 更新异常
  • 插入异常:无法正确表示信息
  • 删除异常:丢失有效数据

三大范式:

第一范式(1st
NF):目标是确保每列的原子性,如果每列都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式(1NF)

第二范式(2st NF):要求每个表只描述一件事情(只描述一种实例)

第三范式(3st
NF):如果一个关系满足2NF,并且除了主键以外的其他列都不传递且依赖于主键列,则满足第三范式

数据库的实现

目标:

  • 建库
  • 建表
  • 建约束

回顾:

数据库的文件组成:

数据库文件分为两类:数据文件和日志文件(ldf)

数据文件分为:主数据文件(.mdf)和次数据文件(.ndf)

数据库文件的组成

数据文件:

组名、数据库名称(name)、初始大小(size)、增长大小(filegiowth)、最大大小(maxsize)、文件存放路径(filename)

日志文件:

数据库名称(name)、初始大小(size)、增长大小(filegiowth)、最大大小(maxsize)、文件存放路径(filename)

创建数据库的写法:

create database 数据库名称

on 数据组名

(

name = '数据文件物理名',

size = 初始大小,

filegiowth = 增长大小,

maxsize = 最大大小,

filename = '文件路径'

)

log on -- 日志文件

(

name = '日志文件物理名_log',

size = 初始大小,

filegiowth = 增长大小,

maxsize = 最大大小,

filename = '文件路径'

)

Go

剖析数据库

数据组:日志文件没有组的概念,数据文件有组的概念,其中默认的,必须的,数据组名为:主数据组(primary)——一个数据库至少包含一个primary组文件和一个日志文件

数据库名称(name):是未来的代码调用数据库时所使用的名称,由于数据和日志文件的名称相同,因此,需在日志文件名后增加“_log”以示区分

初始大小(size):默认单位为MB(兆),当然也可以是其他的数据单位,例如GB、TB、B等;数据文件的初始大小要求不低于5MB,日志文件的初始大小要求不低于2M

增长大小(filegiowth):默认单位为MB(兆),当然也可以是其他的数据单位,例如GB、TB、B等;增长方式还可以是百分比递增,例如20%表示文件达到当前最大容量时,文件扩增到当前大小的120%

最大大小(maxsize):默认单位为MB(兆),当然也可以是其他的数据单位,例如GB、TB、B等

文件路径(filename):指数据库文件的存放地址,只能是绝对地址

数据库的其他相关指令

创建数据库:create database 数据库名称

删除数据库:drop database 数据库名称

查询数据库信息:select * from sysdatabases

判断数据是否存在:if exists(数据列表)

判断数据库是否存在:

If exists(select * from sysdatabases where name = ‘数据库名称’)

修改数据库设置

ALTER DATABASE [MySchool] SET 设置列表

注意:数据库的所有指令都必须在master中执行(use master)

数据表的相关指令

创建数据表:create table 数据表名称

删除数据表:drop table 数据表名称

查询数据表信息:select * from sysobjects

注意:数据表的所有指令都必须在指定数据库中执行(use 表所在的数据库)

数据表写法:

create table 数据库名称(

字段名 数据类型 约束列表,

...

字段名 数据类型 约束列表

)

数据类型

数据类型存储特点编码方式 (unicode编码每个汉字一个字节)
Char(长度)固定长度
varchar(长度)可变长度
nchar(长度)固定长度Unicode
nvarchar(长度)可变长度Unicode
int整形
float浮点型
moneyDecimal浮点型
Datetime日期时间类型
Date日期类型
Time时间类型
Bit布尔类型,其值只能是0,1

注:当数据类型后面接小括号时,括号的内容为能够接收的最大长度,如果需要使用极限大长度时,可以用max代替

约束

约束的种类

  • 实体完整性:通过某个列标识一个实体的方式

    • 唯一约束(unique)名称以UQ_开头

      • 列的所有值,不能有重复项
      • 值可以为null(只能出现一次)
      • 可以在一张表中设置多个唯一约束
    • 主键约束(primary key)名称以PK_开头

      • 非空+唯一
      • 一张表只能有一个主键,主键不一定是一个列
      • 主键约束的列,不允许为null
    • 标识列(identity(种子值,增长值))
  • 域完整性:指定某个列的数据有效性,确保存储内容的真实有效

    • 数据类型
    • 数据长度
    • 非空约束(not null)
    • 检查约束(check(约束条件)) 名称以CK_开头
    • 默认约束(default())名称以DF_开头
  • 引用完整性:保证表与表之间的数据真实有效,且能相互对应

    • 外键约束(references 主表表名(主表主键名))(foreign

      key主表表名(主表主键名))名称以FK_开头
      
  • 自定义完整性:确保项目业务的特定规则能够被实施

    • 触发器
    • 存储过程

---创建约束的写法:

alter table 表名 add constraint 约束名 约束类型(要约束的列)

---删除约束

alter table 表名 drop constraint 约束名

---如果现有表中已存在数据,我们需要在不检查原有数据的前提下,添加check约束的方式

alter table 表名 with nocheck add constraint 约束名 约束类型(要约束的列)

具体约束的具体写法:

约束类型写法
主键约束Primary key(主键列名)
Check约束Check(表达式)
Check 非空写法Check(列名 is not null)

TP11-SQL编程

本章目标:

  • 掌握变量的声明及赋值
  • 掌握数据的显示与输出
  • 掌握if、while、case逻辑控制语句
  • 批处理指令完成多语句的批量处理

变量

变量分为局部变量和全局变量

局部变量:是自定义的变量,变量名以@开头

写法:

变量声明:

declare 变量名 数据类型

例如:

declare @num int

变量赋值:

Set 变量名 = 值 --单行声明时

Select 变量名 = 值 --数据查询时

例如:

set @num = 1

select @num = 5

set:

不支持同时存储多个值

值无结果时,变量被赋null值

Select:

允许多次赋值,但只接收最后一个数据存储

值无结果时,变量保持原有值

全局变量:是由系统定义和维护的,只能读取不能手动修改,以@@开头

/*全局变量*/

print '最后一个SQL错误编号:'+convert(varchar(10),@@ERROR)
--最后一个SQL错误编号

print @@IDENTITY --最后一次插入的标识列的值

print '服务器名称'+@@SERVERNAME --本地服务器名称

print @@VERSION --当前服务器版本信息

print @@LANGUAGE --当前服务器使用的语言

print @@ROWCOUNT --上一个SQL语句的受影响行数

print @@MAX_CONNECTIONS --可以同时连接的最大数目

print @@TRANCOUNT --当前已连接打开的事务数

打印和输出

Print:以文本显示内容

Select:以表格显示内容

逻辑控制语句

If..else语句:

If(条件)

Begin

方法体

end

else

begin

方法体

End

注:

在SQL中一行表示一句代码,不需要分号分隔;

当方法体中只有一句代码时,可以省略begin..end

Case..end语句:

Case

When 条件 then 条件成立时要执行的内容

When 条件 then 条件成立时要执行的内容

Else 上述条件皆不满足时要执行的内容

End

While循环

写法:

While(条件)

begin

循环体

end

注:当循环可以继续但此时应当终断循环时,可以用break来实现

批处理命令:

包含一个或者多个SQL语句的组,从应用程序一次性发送到SQL解释器执行

SQL将每一组的SQL语句编译成一个执行单元,这个单元称之为执行计划

执行单元的描述方式为:最后一行以go结尾

TP12-高级查询

目标:

  • 掌握子查询的用法
  • 掌握in子查询的用法
  • 掌握exists子查询的用法

子查询

子查询就是在SQL语句中嵌套查询语句

子查询的运行机制:

系统会优先执行小括号的内容,即子查询优先执行(跟四则运算规则一样)

注意:将子查询和比较运算符联合使用时,必须保证子查询返回的值不多于一个。

子查询的应用及注意事项:

  • 子查询作为条件使用

    • 查询年龄比金蝶大的学生信息
    • select * from Student where BornDate > (select BornDate from Student
      where StudentName = '金蝶')
  • 子查询作为字段使用

    • --查询所有学生成绩
    • select
    • 学生姓名 = (select StudentName from Student s where s.StudentNo =

      r.StudentNo),
      
    • 科目名称 = (select SubjectName from Subject j where j.SubjectId =

      r.SubjectId),
      
    • 学生成绩 = StudentResult,
    • 考试时间 = ExamDate
    • from Result r
    • 注意:作为字段使用时,子查询中必须具备与主表相关联的连接条件
  • 子查询作为表使用

    • --子查询作为表使用
    • select StudentName
    • from
    • Student s,
    • (select e.StudentNo
    • from
    • Result e,
    • Subject j
    • where e.SubjectId = j.SubjectId and
    • j.SubjectName like '%C#%' and
    • e.StudentResult < 60
    • ) r
    • where
    • s.StudentNo = r.StudentNo
    • group by s.StudentName
    • 将子查询作为临时表使用时,关键用途在于将多个表进行分组和归类,便于修改和查找问题

配套子查询

In:当子查询需要返回多条记录时,则用in替换“=”来实现

Exists:表示是否存在(然后执行一些语句)

例如:在Master数据库中

If Exists(select * from sysDatabases where name=’要查询的数据库名称’)

在指定数据库中:

If Exists(select * from sysObjects where name=’要查询的表名称’)

Distinct:表示去重。写法:distinct 列名

作用:可以省略查询结果中特定列的重复数据

阶段总结

事务、索引和视图

存储过程

课程总复习

Last modification:March 16th, 2020 at 08:18 pm
如果觉得我的文章对你有用,请随意赞赏