SQL server 期末复习

1 数据库的基本概念

数据（Data）为描述客户事物及其状态的符号记录。
数据库是（Database,DB）相互关联且具有一定结构的数据集合。
数据库的设计和实现具有特定的目的，随机组合的数据不能被称为数据库。
数据库中的数据具有如下特征：共享性、持久性、有效性、安全性、一致性、无冗余性。

数据库管理系统（Database Managment System，DBMS）是一种用于创建和管理数据库的计算机应用软件程序。
数据库管理系统具有的优点：将相互关联的数据集成在一起，减少冗余度，程序与数据相互独立，保证数据的安全性和可靠性，最大限度地保证数据的正确性，数据可以共享并能保证数据的一致性。

数据系统（Database System,DBS）包括与数据库相关的整个系统。
数据库系统包含五个部分：数据库、数据库管理系统、应用程序、数据管理员（Database Adminstrator，DBA）和用户。

2 数据库表和视图的相关概念，创建表和视图，包含定义，约束和索引等

2.1 创建数据库表

用T-SQL语句创建表
create table 表名(
字段1 字段1的数据类型 字段1的约束,
字段2 字段2的数据类型 字段2的约束,
…
字段n 字段n的数据类型 字段n的约束
)
字段不区分大小写
数据类型：
char(n) varchar(n) int
decimal(p,s), numeric(p,s)
p（精度）：表示最多可存储的十进制数的小数点左右两边的总位数，应在1到38位之间。 默认精度为18。
s（小数位数）：只能在0~p之间。默认的小数位数为0。
例如：
decimal (11,4)
表示11位数，小数部分有4位，整数部分7位。
如果小数部分超过4位，则从第5位四舍五入；
如果整数部分超过7位，则检查语法时编译器会出错。

约束：
not null null
check(字段>0) 该字段必须内容大于0 前提：该字段的数据类型为int
check(字段 in(‘xx’,‘yy’)) 该字段内容必须为xx或yy 前提：该字段为char()类型
例如：sex char(2) not num check(sex in(‘男’,‘女’)) 该字段内容必须为男或女且不能为空
dafault xxxx 默认值为xxxx
例如：time datetime dafault getdate() time为字段，datetime为字段类型，默认值为当前时间。

主键 primary key
例如： stuid cahr(12) primary key stuid为字段，字段类型为cahr(12)，stuid为主键。
或在建表语句最后一行声明主键，例如：

create table sc(
stuid char(12),
sname char(7),
primary key(stuid)
)

外键 foreign key
创表的时候定义外键，语法格式如下：
字段 字段类型 references 父表名 (父表的主键字段)
例如：

create table class(
class id char(8) primary key,
departmentid char(4) references department(departmentid)
)

分析：departmentid为表class的字段，department表的主键为departmentid。
创建表之后添加外键，语法格式如下：
alter table 本表 add foregin key (本表字段) references 父表(父表主键)
例如：
为teacher表添加外键约束，将departmentid定义为外键，引用department表中主键departmentid。

alter table teacher foregin key(departmentid) references department(departmentid)

删除外键约束，语法格式：
alter table 表名 drop constraint 外键约束名

2.2 表的插入

语法格式如下：
insert into 表名 (字段1,…,字段n)
values
例如：
向student表（sno，sname）插入3行记录，分别为(c1,小一)，(c2,小二)，(c3,小三)。

insert into student
values
('c1','小一'),
('c2','小二'),
('c3','小三')

向student表（sno，sname，sphone）插入3行记录，分别为(c4,12345)，(c5,67890)，(c6,12398)。

insert into student
(sno,sphone)
values
('c4',12345),
('c5',67890),
('c6',12389)

2.3 更新表数据

与where子句配合使用。
语法格式：
update 表名
set 字段=新数据值或表达式
where
例如：
将21软件工程1班的辅导员修改为李密，班级人数修改为32人

update class
set monitor='李密',studentsum=32
where classname='21软件工程1班'

2.4 删除表数据

与where子句配合使用。

2.5 索引

创建索引
语法格式：
create index 索引名 on 表名(字段)

create index ix_news_createdate on news(createdate)

删除索引
语法格式：
drop index 索引名 on 表名

drop index ix_news_createdate on news 

2.6 视图

详细视图知识点击这里
创建视图
语法格式：
create view view_name as select 字段 from 表名 [with check option]
如果加上with check option，则基于视图的更新和新增操作必须满足查询条件。
例如：

create view v_id_name 
as select departid,name from files

查询视图
在sql server图形化界面和查询语句中，视图都可以当成普通表使用。

select * from v_sex_m

修改视图
语法格式：
alter view view_name 字段 [ with check option]
as 查询语句 [with check option]

alter view v_sex_m
as 
select top 1 name from v_sex_m

删除视图
语法格式:
drop view view_name

drop view v_sex_m

3 查询语句，单表查询，分组查询，多表查询，子查询，高级查询

详细查询知识点击这里

3.1 单表查询

选择表中的列

order by子句
可以按一个或多个属性列排序
升序：ASC；降序：DESC；缺省值为升序。

通过top n选择结果中的行，n为整数，表示选择的行数，从前面开始选择。

消除取值重复的行，指定distinct关键词，去掉表中重复的行，如下例子：

select distinct sno from sc

表达式中的运算
select 子句的<目标列表达式>不仅可以为表中的属性列，也可以是表达式
例如：

select sname,2022-year(sbirth) as sage from student;

datalength(Char_expr) 字符串长度计算
返回字符串包含字符数，但不包含后面的空格
Char_exp：可以是字段/变量/表达式
如：datalength(‘abc’) 返回 3

select sname,datalength(sname) as length from student

字符串函数：
right(char_expr,int_expr) 返回字符串右边int_expr个字符
left(char_expr,int_expr) 返回字符串左边int_expr个字符
如：left(‘张三丰’，1) 返回 “张”

查询所有学生的姓名和姓氏(假设姓氏只有第一个字）

select sname,left(sname,1) as xs from student 

日期函数：
getdate() 返回当前日期
year（date_expr）获取date_expr的年份
month（date_expr）获取date_expr的月份
day（date_expr）获取date_expr的日期

查询所有学生的姓名、出生年份、出生月份，并按照年份降序排列

select sname,year(sbirth) as birth_year,
month(sbirth) as birth_month 
from student order by birth_year desc

聚集函数
count(*) 统计行数
count dintinct 字段 统计一列中值的个数

sum() 计算一列值的总和（此列必须为数值型）
avg() 计算一列值的平均值（此列必须为数值型）
max() 求一列中的最大值
min() 求一列的最小值

3.2 group by分组子句

group by将查询的结果进行分组，通常与一些聚合函数配合使用。
语法格式：
group by 字段名;
group by 字段名 having 条件;

where和having的不同之处
where用于select语句中，表示查询要满足的条件。
having 条件只能与group by分组配合使用，表示将分组后满足条件的记录输出。

查询files表各个职称的人数，语句如下：

select duty,count(*) as 人数 from files group by duty

having子句
查询files表各个职称的人数小于5的记录，语句如下：

select duty,count(*) as 人数 from files group by duty having count(*)<5

3.4 子查询

嵌套查询是在一个select语句中，可嵌套着另外的子查询，就是嵌套查询。
嵌套查询一般用于查询的内容来自多表时，且多张表有代表意义上相同的字段内容的情况。

4 函数的基本概念

sum() avg() ascii() left(‘xx’,n)

5 游标的基本概念

6 存储过程的基本概念

详细游标 存储过程知识点击这里

7 数据库概念设ER图的基本概念，包含关系和图形

矩形：表示实体。矩形内表明实体名称。
椭圆：表示属性。可以是实体或者关系所具有的属性。
菱形：表示关系。
关系：1:n ，n:1，n:m。

8 数据库的安全和备份，包含分离及附加

8.1 备份数据库

语法格式如下：
backup database 数据名 to 备份的路径
例如：
对xkgl数据库进行一次完整备份，备份到student设备上（student数据库已经建好了）。

backp database xkgl to student

对xkgl数据库进行一次差异备份，并备份到student备份设备上。

backp database xkgl to student with differential

对xkgl数据库进行一次完整备份，备份到student设备上。

backp database xkgl to student init

8.2 还原数据库

完全数据库备份恢复语法格式如下：
restore database 数据库名 from 备份设备名
例如：
已经完整备份的数据xkgl进行恢复。

restore database from student

事务日志备份恢复语法格式如下：
restore log database 数据库名 from 备份设备名

8.3 分离和附加数据库

分离数据库就是将某个数据库（如student_Mis）从SQL Server数据库列表中删除，使其不再被SQL Server管理和使用，但该数据库的文件（.MDF）和对应的日志文件（.LDF）完好无损。分离成功后，我们就可以把该数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘中作为备份保存。

附加数据库就是将一个备份磁盘中的数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到需要的计算机，并将其添加到某个SQL Server数据库服务器中，由该服务器来管理和使用这个数据库。

9 数据库设计的基本概念

数据库设计包括数据库的结构设计和数据库的行为设计两方面的内容。

数据库的结构设计包括数据库的概念设计、逻辑设计和物理设计。

数据库的结构设计又称为静态模型设计。

数据库的行为设计又称为动态模型设计。

新奥尔良法将数据库设计分成需求分析（分析用户需求）、概念设计（信息分析和定义）、逻辑设计（设计实现）和物理设计（物理数据库设计）。

基于E-R模型的数据库设计方法其基本思想是在需求分析的基础上，用E-R（实体—联系）图构造一个反映现实世界实体之间联系的企业模式，然后再将此企业模式转换成基于某一特定的DBMS的概念模式。

基于3NF的数据库设计方法其基本思想是在需求分析的基础上，确定数据库模式中的全部属性和属性间的依赖关系，将它们组织在一个单一的关系模式中，然后再分析模式中不符合3NF的约束条件，将其进行投影分解，规范成若干个3NF关系模式的集合。

基于3NF的数据库设计方法其具体设计步骤分为五个阶段
(1)设计企业模式，利用规范化得到的3NF关系模式画出企业模式；
(2)设计数据库的概念模式，把企业模式转换成DBMS所能接受的概念模式，并根据概念模式导出各个应用的外模式；
(3)设计数据库的物理模式（存储模式）；
(4)对物理模式进行评价；
(5) 实现数据库。

按规范设计法可将数据库设计分为六个阶段：

关系数据库的规范化理论主要包括三个方面的内容：函数依赖、范式（Normal Form）和模式设计。其中，函数依赖起着核心的作用，是模式分解和模式设计的基础，范式是模式分解的标准。

第一范式（1NF）是最基本的规范形式，即关系中每个属性都是不可再分的简单项。只要是关系数据库就是第一范式。

第二范式（Second Normal Form）每个非主属性都完全函数依赖于R的每个关系键。主要功能消除部分依赖。

第三范式（Third Normal Form）每个非主属性都不传递依赖于R的每个关系键。主要功能消除传递依赖。