数据库设计与管理-优快云博客

本文详细介绍数据库的基础知识，包括系统结构、角色与架构、主键、约束等核心概念，并深入探讨了查询语句、表连接、视图、事务处理及存储过程等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库基础部分

1. 数据库系统结构

网络连接接口、关系引擎、存储引擎、内存引擎、内存

2. 角色与架构

(1)架构规定了能查看的表

(2)角色相当于将操作权限分组了。有动作的概念在里面。

总之就是角色分配了权限，而架构是

T-SQL创建架构

use MyDB

go

create schema xwb authorization dbo;

3. 逻辑主键与业务主键

①逻辑主键：与描述数据无关的一个字段

②业务主键：与描述数据相关的一个字段

T-SQL创建主键

alter table 表名

add

constraint PK_表明_字段名 primary key(字段名)

4.自动增长

(1) identity(1,1) 表示字段的值从1开始，每次添加数据的时候自动增长1个

一旦数据添加了自动增长，那么添加数据的时候就不允许手动添加这个值执行一个设定

一旦添加了自动增长，那么添加数据的时候就不允许手动添加这个值

修改手动添加

set identity_insert 数据库名.表名 on----开启修改

set identity_insert 数据库名.表名 off----关闭修改

5.约束

唯一约束

alter table 表名

add

constraint UQ_表名_字段名 unique(字段)

->主键定位了表，一张表中只允许有一个主键，但是可以有多个唯一约束

->主键不允许为空

默认约束（default）

->有时候需要有默认值

->用户插入数据的时间

->软删除（IsDel）

T-SQL为字段设默认值

alter table 表名
add
constraint DF_表名_字段名 default(值) for 字段

检查约束(check)

->用来限定输入的数据，检查是否符合某些规则

->一个人的性别

->一个人的年龄

T-SQL为字段增加约束检查

alater table 表名

add

constraint CK_表名_字段名 check(表达式)

ex: ->stuAge>=0 and stuAge<=150 or stuAge is null

外键约束

T-SQL为字段增加外键约束

alter table 外键表名

add

constraint FK_外键表_主键表_字段

foreign key（外键表中的字段） references(主键表名) （主键表中的字段）

删除约束

drop table 表名

drop 约束名

6.补充：

alter 命令表示修改的意思，凡是要修改表的数据都可以使用这个命令

->添加约束

alter table 表名 add constraint 约束名

->删除约束

alter tbale 表名 drop constraint 约束名

->添加一个字段

alter table 表名 add 字段名类型名；

->删除字段

alter table 表名 drop colum 字段名；

->修改字段

alter table 表名 alter colum 字段名类型

->系统视图

         select * from sysobject

-> type不同表示的类型不一样

-> S   系统表

-> U   用户表

-> PK 主键

-> UQ 唯一约束

-> C   检查约束

-> D   默认约束

7.系统的查询语句

from子句

是一个查询中最先开始的地方

where子句

删选条件

->表达式：判断不等于 <> (!= !> !<)只能在T-SQL中使用

-> like

        补充：如果需要匹配‘_’, 用[] ，[]还可以表示范围 [a-z]

->处理范围

      between and

      离散范围： in(2,3,5)

->空值处理

      任何包含null的表达式，结果都是不知道（不知道的反面还是不知道）

->函数 ISNULL(a,b)

        如果a不为null，那么结果就为a，如果a为null，那么结果为b

group by子句

->按照什么分组就只能显示什么数据

select 后面只能跟你分组的字段

->聚合函数，就是讲组的数据进行汇总，求实平均、总和、总数、最大和最小

AVG、 SUM、 COUNT、 MAX、 MIN

having 子句

->having是在数据分组后进行筛选，它与where都是筛选，但是操作不同

->补充：聚合函数默认忽视NULL值

->在SQL中不能将聚合函数与其它字段写在一起

->开窗函数

select ...avg(字段) over() form ...

select 子句

->显示数据

->别名

字段 as 列名

列名=字段

-> 字段的执行顺序，同一个子句中是同时进行的

distinct选项

->select distinct 字段 from

top

->select top 数字 percent --取数字的百分比

在查询的结果中有两个类型

->表（结果集）,必须有列名

-> 游标(cursor)有序的结果集

《数据库系统概念》

《数据库系统实现》

8.数据库的简单结构

①一台远程计算机可以安装多个数据库的实例，它们除了共享硬件外，没有任何关系

处于成本的考虑，将多个实例放到一台数据库服务器中

一个实例也就标示了一个数据库

②连接数据库就是要连接计算机上的实例，使用“计算机名\实例名”或"ip地址\实例名"

Named Pipes 命名管道：一般用在局域网，它可以提供一个高速访问

Shared Memory 共享内存：当前实例安装在自己计算机上，本机上连接自己的数据库实例时，用的就是共享内存

TCP/IP 远程连接

VIA 虚拟接口适配器：与硬件挂钩的

->缓冲池(内存) 也是数据库中消耗内存最大的地方

为了使数据更安全同时有更快的响应，所以对数据的操作都是在内存中的，只是在适当的时候与硬盘交互

->查询过程

当一个查询语句发送到数据库时，数据库首先对这个查询命令通过一个协议层进行分析，然后把命令交给关系引擎，此时sql命令还是字符串，没有语义的；此时，经过语法分析器解析了命令，将分析结果经过查询优化器（进行一些代数处理），生成查询树，先去内存中检索看是否

->存储引擎

访问方法，知道该怎么做了后，先去内存找数据；如果内存没数据，然后调用一些方法去数据库中找数据，把这些数据放到内存中

->修改过程

得到一系列的查询步骤，在得到查询步骤之前，先通过事务管理器写到事务日志当中，也就是数据库日志，然后修改内存中的数据，等缓存中满掉后才更新回数据库中

--其中写入数据库日志中，方便恢复

--操作数据时直接操作内存而不是数据库（从而能完成事务的原子性），从而提高了速度

9.字段类型

(1)文本数据类型（字符数据包括任意字母、符号或数字字符的组合）

①Char （固定长度的非Unicode数据）

②Varchar（可变长度非 Unicode数据）

③Nchar（固定长度的Unicode数据）

④Nvarchar（非固定长度的Unicode数据）

⑤ 存储长文本信息

Text() ==varchar(max)

Ntext ==Nvarchar(max)

补充：

--固定长度是指比如定义字段类型为char(50),那么数据库也会给分配50字符的空间；而可变长度的是随着为字段填充的字符的多少而动态增加的

--Unicode编码

是一种在计算机上使用的字符编码。它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求

数据库进阶部分

1. 联合结果集

就是将两个结果集合并

-> 语法

查询1

uoion [all]

查询2

-> 说明

(1)列名取第一个查询设置的列名

(2)可以进行多个结果集连接

(3)保留all时就是单纯合并，如果去掉all就会将重复数据去除

2.insert语句

(1)原始：insert into 表名(列名) values(值)；

补充：insert into 表名(列名) 结果集（联合结果集）；

(2) 表值构造函数（处理灵活）

->语法

（values (值1，值2，值3)，

(值1，值2，值3)，

...

)

->①使用1：insert into 表名(列名) values 值1，值2，值3

②使用2：使用表值构造函数得到临时结果集； SQL Server 2008+才适用

3.复制表

select * into 新表 from 表

注：①这个新表不允许事先存在

②使用这个方法可以复制表结构

select * into 新表 from 表 where 1>2

4.函数

(1)字符串函数

->ASCII()转换为asscii字符

(2)日期函数

① datatimeoffset 使用标准时间+偏移时间

②输入时：在SQL Server中没有时间类型的变量值，需要赋值就使用时间格式的字符串。

③year()、day()、month()

④datediff()

⑤dateadd()

⑥datepart()

(3)转化

一般有字符串型的数字参与时，如果有数字则全部转换为数字计算

->语法

cast(变量或数据 as 类型)；

convert(类型，变量或数据[,格式]) --s适用于转化日期格式

5. truncate table 表名

截断数据库，将数据库表中的所有数据清空，并将自动增长归零，以最小的日志记录

6.(1)查询数据库的表信息

select * from INFORMATION_SCHEMA.TABLES

(2)查询数据库一个表中的详细信息

select * from INFORMATION_SCHEMA.COLUMNS

(3)数据库信息

select * from databases

数据库高级部分

1. case

(1)case语法相当于C#中的if-else if

->相当于if-else if

case

          when 包含字段的表达式1 then 值1

             when 包含字段的表达式2 then 值2

          ...

          else 值n

end

(2)类似于switch-case语法

case 字段

when 值1 then 显示1

when 值2 then 显示2

...

else 显示n

end

(3)透视变换

select

学号

, SUM(case when 课程='语文' then 成绩 else 0 end) as 语文

, SUM(case when 课程='数学' then 成绩 else 0 end) as 数学

, SUM(case when 课程='英语' then 成绩 else 0 end) as 英语

from

Score

group by 学号

2. 子查询

-->独立子查询(将直接选中也可执行)

(1)子查询的结果可以是一个单值、一个多个值或一张表;将一个查询结果（单值或多值）作为另一个查询条件

where

字段=

（

select top 1 字段 from 表2 --单值(标量)子查询

）

and

(

select 字段 from 表3 --多值子查询

)

-->相关子查询

select

字段

from

表名1 as t1

where

字段 =

（

select top 1 字段 from 表2 as t2b where t1.字段=t2.字段

）；

3.表连接

将表的各列拆散组合变成一个新的表结构

(1)交叉连接（ 笛卡尔积）外连接

cross join

(2)内连接

inner join

(3)外连接

left join（左连接）、right join( 右连接 ) 、full join( 全连接 )

SQL ->查询树->关系恒等变换（根据统计信息索引等做评估）

->是否已存在相同操作->执行计划

4.表表达式

(1) 派生表

将一个查询的结果放在另一个查询的from子句中作为数据源

步骤：①将这个查询用括起

②为这个查询取别名

③将其写在from子句中，外面使用"别名.字段名"，如果没有字段冲突，别名就可以省略

注意：不允许游标，不允许没有列名

(2) 公用表表达式( CTE)

作用与派生表一模一样，唯一不同的就是定义在前面，后面可以重复使用

语法： with 表别名

(

结果集

)

select * from 表别名

注意：CTE可以再后面的查询中多次被引用，构成一些特定结构的查询

5.视图

视图相当于表，如果不严格的考虑就可以将其作为表来看;可以看做是将一端查询的步骤的规范存储起来。

语法：

create veiw 架构. vw_视图名

（

sql语句

）

与CTE的最大区别在于用一个别名存到了数据库中

注意：视图时虚拟表，不具备存储数据的能力，在执行的时候，SQL Server 底层依旧是分别处理物理结构

作用：增强了安全性

6.row_number 分页

(1)语法

select row_number() over(order by 字段),* from tb

create view

vw_fenye

select row_number() over(order by stuid) as num,* from Student

7.内联表值函数

实际上就是带有参数的视图，用法与视图相同

8. 变量

(1)全局变量（系统变量） @@变量名--系统定义好的，自己不能定义

@@inentity 得到刚刚插入的数据的id号

(2)局部变量（自定义变量） @ 局部变量

-> 使用declare引导变量的定义

declare @num int；定义了一个变量num,是int类型 (相当于C#中int num;)

-> 赋值

-> 使用set语句

set @变量 = 标量(可以是一个数据、可以使一个子查询、也可以是表达式)ex:set @num = (select count(*) from Student)

->使用select语句

select 字段 from 表

select @变量=字段 from 表表示将查询的结果不以表的形式显示，而是赋值ex:select @num=count(*) form Student

->在SQL Server 2008+提供了新的赋值方法

declare @num int =10;

注意：

①set赋值时，等号右边不允许是多值，必须是标量（单值）

② select赋值方式，在查询中可以是多个数据，但是会根据查询的内容赋值多次

③定义的时候直接赋值

9. 流程控制（begin与end相当于C#中的大括号作用）

-> 判断

if(...)

begin

        代码

end

else if(...)

begin

        代码

end

else

begin

        代码

end

-> 循环

while(...)

begin

代码

end

-> 跳出循环

break与 continueC#中的用法相同

10. 事务（在数据库中所有的执行单元都是事务）

--数据库中执行的最小单位

(1)特性

① 原子性作为一个整体，无论多复杂的操作，要么全部成功，要么全部回滚，执行全部失败
② 隔离性，两个事务之间的隔离性,相当于锁住某些表
③ 持久性

④ 一致性保证数据的完整性（数据满足数据库描述对象的实际意义）

(2)操作事务

①隐式事务，平时的sql语句

②显示事务，手动的将多个代码操作打包成一个事务

③开启事务使用 begin transaction

从这句话开始，其后所有代码，都是这个事务中的操作

直到出现提交或回滚

->提交就死告诉数据库刚才从begin transaction开始执行的代码全部生效,使用 commit (transaction)进行提交

->回滚就是告诉数据库刚才的代码不算，操作回到执行之前的状态，使用 rollback (transaction)表示回滚

(3)判断执行的正确与否

① @@Error变量

declare @myError int;

set @myError = 0；

在每个sql语句后，加上set @myError += @@ERROR；

if(@myError = 0)

begin

commit transaction

end

else

begin

rollback transaction

end