更新中 -SQL SERVER笔记

第一章 认识什么是数据库

---

概念:按照数据结构来组织和管理数据的仓库，是一个长期存在计算机内的、有组织的、可共享的、统一管理的大量数据集合。
应用场景 ：在软件系统重无处不在，几乎所有软件系统背后都有数据库，如淘宝，QQ，游戏等
数据库表现形式：

姓名	学号	性别	电话
AA	1	MAN	1
BB:	2	MAN	11
CC	3	MAN	111

主流关系数据库：SQL SERVER,MySql,oracle

1.1 数据库的安装：

1.在微软官网下载；
2.百度搜索i tell you【http://msdn.itellyou.cn/】

1.2 SQL SERVER 服务启动

方法1：：

【命令行启动，管理员权限打开】在cmd中net start mssqlserver 回车

方法2：

[搜索]SQL Server 2022 配置管理器-》sqlserver服务-》sqlser（mssqlserver）-》右键-》启动

方法3

[windows服务]-》sqlserver（mssqlserver）-》右键启动

1.3 管理工具

• sql的管理工具
  软件名字：“sql server management studio”，如果没有，则需要安装（csdn有安装教程）；
• 登录
  打开软件-》弹框链接到服务器-》选择本地计算机名-》windows或者sqlserver身份验证；
  • 方式1）windows登录，sqlserver服务器是装在本机的，服务和管理工具是在同一个电脑上的，不需要密码即可登录；
    下图为登录成功界面：
    
  • 方式2）选择sqlserver连接方式【使用场景：sqlserver服务或服务器在异地（如上海），使用人在本地（如西安），可以异地连接到上海的服务器，进行网络版远程连接】

1.4新建数据库

参考图1，右键数据库-》新建数据库-》输入数据库名-》确定

执行上述操作后得到一张空表的数据库，接下来需要建表

1.5.新建数据表

右键db下的【表】-》新建表-》输入相应的列名及对应的数据类型

可以在列名的属性中选择【标识规范】，可以用于选择自增等操作，如ID每次自动+1；
下图为设置主键的用法【即作为唯一标识，比如唯一标识一个人】

建立好表后，ctrl+s进行保存，设置表名

• 表中数据的维护：
  如图：右键dbo.*，选择编辑前200行
  
  

1.6 数据库的迁移备份还原

三种方式实现

1.6.1 数据库的 分离 和 附加

选择【数据库】-》【任务】-》【分离】-》【选择删除和更新√并确认】，数据库即分离

数据库的附加

点击【数据库名】-》【属性】-》【文件】-》【找到路径】-》【复制到windows文件浏览器】

如上图，打开后有 .mdf和.ldf 格式文件，将此两个数据库文件拷贝走后，可以在其他电脑上数据库中进行附加加入该数据库的内容；

存在的弊端：此分离和附加过程中，数据库状态为不可用；

1.6.2 数据库的备份与还原

• 数据库的备份
  数据库名->任务->备份->*.back[即为备份的数据库文件]
  
• 数据库的还原
  点击mssqlserverManagement中的“数据库”-》右键点击还原数据库-》设备-》选择对应的*.bak文件

1.6.4 数据库的脚本实现保存和迁移

拓展：使用脚本方式实现数据库的迁移和保存：如下图：
脚本复制数据库：右键数据库名-》任务-》生成脚本-》设置脚本编写选项中：要编写脚本的数据类型选择数据和架构，选择脚本的路径位置*.sql-》一直下一步直到完成；

加载脚本数据库： 在新的电脑上导入脚本备份的数据库：用SSMS打开脚本文件*.sql-》点击执行-》成功后选择刷新数据库即为导入成功

二、SQL脚本的建库和建表

---

在哪写脚本？
打开SSMS，选择数据库名-》点击上方新建查询-》弹出可以进行coding的窗口。

SQLSERVER基本语法
注释：- -

使用脚本进行建库和删库

2.1 建库和删库操作

2.1.1 删库操作

if exists(select * from sys.databases where name ='db_coding')
	--删除数据库
	drop database db_coding  --删除操作非常危险，正常不要这样使用。仅限学习使用

2.12 带参建库操作

--创建数据库方法一
create database db_coding
on  --数据文件
(
	name = 'db_coding',   --逻辑名称
	filename = "D:\DATA_SQLSERVER\db_coding.mdf"  ,       --完整路径
	size = 5MB ,    --文件初始大小
	filegrowth = 2MB   --文件的增长方式，达到size后每次增加值或者可以写百分比，如10%


)
log on   --日志文件
(
	name = 'db_coding_log',   --逻辑名称
	filename = "D:\DATA_SQLSERVER\db_coding——log.ldf"  ,       --完整路径
	size = 5MB ,    --文件初始大小
	filegrowth = 2MB   --文件的增长方式，达到size后每次增加值或者可以写百分比，如10%
)

2.1.3 默认建库方法

–创建数据库方法二

create database db_coding_1             --上面方法1中的on和log on均可省略，数据文件和日志文件均采用的是默认值

2.2 在库的基础上建表

2.2.1 确定要给哪个库建表

首先要确定使用哪个库，如果不是需要的库，则需要进行库的切换。

切换库的方式有
1).在状态栏选择下拉框切换库

2).使用命令行进行切换
use [数据库名]

2.2.2 创建表的代码

--创建表
create table 表名
(
	字段名1 数据类型,
	字段名2 数据类型
)

插入一段字符串说明

–字符串讲解:
–1.char(10)，定长字符串。无论存储有效数据是否到达10个字节
–2.varchar(10),变长字符串，最多占用10个字节。如果varchar(10)存储的是‘a,b’,则占用的是2个字节
–3.text 表示长文本类型，功能强大，但是执行效率会低
–4. nchar，nvchar，ntext，对unicode友好，如果有汉语文本，前面加n比较好

以下是创建表的过程

--使用指定的数据库名
use db_coding_1
--先判断表是否存在
if exists(select *from sys.objects where name ='Department' and type = 'U')
	--删除表，很危险，正式不建议用
	drop table Department


--建表  部门-职级-员工
create table Department
(
	DepartmentID int primary key identity(1,1),--设置主键，唯一标识，设置identity的初始值1和自增值1
	DepartmentName nvarchar(50) not null,--设置部门名称最多存储50个字
	DepartmentRemark text
)
if exists(select *from sys.objects where name ='Rank' and type = 'U')
	--删除表，很危险，正式不建议用
	drop table [Rank]
--职级
create table [Rank]
(
	RankID int primary key identity(1,1),--设置主键，唯一标识，设置identity的初始值1和自增值1
	RankName nvarchar(50) not null,--设置部门名称最多存储50个字
	RankRemark text
)
if exists(select *from sys.objects where name ='People' and type = 'U')
	--删除表，很危险，正式不建议用
	drop table People


create table People
(
	DepartmentID int references Department(DepartmentID) not null,--引用其它表的主键作为外键。references表示当前的数据必须从它后面的表中来
	RankID int references [Rank](RankID) not null, --引用其他表的主键作为外键
	PeopleID int primary key identity(1,1),--设置主键，唯一标识，设置identity的初始值1和自增值1
	PeopleName nvarchar(50) not null,--设置部门名称最多存储50个字
	PeopleSex nvarchar(1) default('男') check(PeopleSex='男' or PeopleSex='女') not null,
	PeopleBirth smalldatetime not null ,--存储年月日时分秒。如果是date是只有年月日。smalldatetime是代表近期的时间，占用时间小
	PeopleSalay decimal(12,2) check(PeopleSalay>100 and PeopleSalay<1000000) not null,--数据库中存储精度要求较高时，可以用money类型，decimal类型，decimal(12,2)代表总长12，小数点后2位
	PeoplePhone varchar(20) unique not null,--unique代表唯一的表示，如果表中有重复的则添加不进去
	PeopleAddress nvarchar(300),
	PeopleAddTime smalldatetime default(getdate()) not null,--getdate()默认位当前时间
	PeopleRemark text
)

2.2.3 表结构和约束的维护

---

–修改表结构
–1）删除列
–语法：alter table [表名] drop column [列名]
–删除一列邮箱

alter table People drop column Email 

–2）添加列
–语法：alter table [表名] add [新列名] [数据类型]
–添加一列邮箱

alter table People add Email varchar(20)  

–3）修改列

alter table People alter column Email varchar(30) 

-------维护约束（删除约束，添加约束）
–删除约束
–语法：alter table [表名] drop constraint [约束名]

—添加check约束
–语法： alter table [表名] add constraint [约束名] [约束]

alter table People add constraint CK__People__PeopleSa1
check(PeopleSalary>50 and PeopleSalary<500000)

–添加主键约束

–alter table [表名] add constraint [约束名] primary key(列名)

–添加唯一约束

–alter table [表名] add constraint [约束名] unique(列名)

–添加默认值约束

–alter table [表名] add constraint [约束名] default 默认值 for 列名

–添加约束(外键）-关联表的主键

–alter table [表名] add constraint [约束名] foreign key(列名) refrences 关联表名(列名)

---

三、数据表的数据插入

3.1 如何向数据表插入数据?

insert into Department(DepartmentName,DepartmentRemark)
values('市场部','market')
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','soft')
insert into Department(DepartmentName,DepartmentRemark)
values('工程部','engineering')

3.2 如何一次性插入多行数据？

insert into Department(DepartmentName,DepartmentRemark)
values
('测试部','Test'),
('产品部','Product'),
('人事部','HR')

3.3 附加题-解决sql语句执行失败但主键identity仍会自动增加导致不连续的问题

由于主键中identity的设置，sql语句设置失败，也会消耗主键值并+1，导致实际的主键值不连续的情况；
----------解决方案：使用 SEQUENCE 代替 IDENTITY 是一种更灵活的方式，可以手动管理主键值，并在插入失败时回滚 SEQUENCE，从而避免主键值被消耗。
–例子介绍

-----------由于主键中identity的设置，sql语句设置失败，也会消耗主键值并+1，导致实际的主键值不连续的情况；
----------解决方案：使用 SEQUENCE 代替 IDENTITY 是一种更灵活的方式，可以手动管理主键值，并在插入失败时回滚 SEQUENCE，从而避免主键值被消耗。
–例子介绍
–步骤 1：判断是否有表，没有则创建 表

if exists(select *from sys.objects where name ='People' and type = 'U')
	--删除表，很危险，正式不建议用
	drop table People
create table People
(
    DepartmentID int references Department(DepartmentID) not null,
    RankID int references [Rank](RankID) not null,
    PeopleID int primary key default (next value for PeopleSeq), -- 使用 SEQUENCE
    PeopleName nvarchar(50) not null,
    PeopleSex nvarchar(1) default('男') check(PeopleSex='男' OR PeopleSex='女') not null,
    PeopleBirth smalldatetime NOT NULL,
    PeopleSalary decimal(12,2) check(PeopleSalary>100 AND PeopleSalary<1000000) not null,
    PeoplePhone varchar(20) unique not null,
    PeopleAddress nvarchar(300),
    PeopleAddTime smalldatetime default(GETDATE()) not null,
    PeopleRemark text
);

– 步骤 2：创建 SEQUENCE
– 首先，创建一个 SEQUENCE 对象，用于生成主键值。
– 检查 SEQUENCE 是否存在

IF NOT EXISTS (SELECT 1 FROM sys.sequences WHERE name = 'PeopleSeq')
BEGIN
    -- 如果不存在，则创建 SEQUENCE
    CREATE SEQUENCE PeopleSeq
        START WITH 1      -- 从 1 开始
        INCREMENT BY 1;   -- 每次增加 1
END

– 步骤 3：插入数据时使用事务和 SEQUENCE
– 在插入数据时，使用事务确保操作的原子性。如果插入失败，回滚事务并回滚 SEQUENCE

BEGIN TRANSACTION;

	-- 获取表中最大的 PeopleID
	DECLARE @MaxPeopleID INT;
	SELECT @MaxPeopleID = ISNULL(MAX(PeopleID), 0) FROM People;

	-- 重置 SEQUENCE 的值
	DECLARE @ResetSequenceSQL NVARCHAR(MAX);
	SET @ResetSequenceSQL = N'ALTER SEQUENCE PeopleSeq RESTART WITH ' + CAST(@MaxPeopleID + 1 AS NVARCHAR) + ';';
	EXEC sp_executesql @ResetSequenceSQL;

	-- 检查重置后的 SEQUENCE 值
	SELECT CONVERT(INT, current_value) AS CurrentSequenceValue
	FROM sys.sequences
	WHERE name = 'PeopleSeq';

BEGIN TRY
    -- 获取当前 SEQUENCE 值
    DECLARE @CurrentValue INT;
    SELECT @CurrentValue = CONVERT(INT, current_value) FROM sys.sequences WHERE name = 'PeopleSeq';
    PRINT '当前 SEQUENCE 值: ' + CAST(@CurrentValue AS VARCHAR(10));

    -- 获取下一个 SEQUENCE 值
    DECLARE @NextPeopleID INT;
    SELECT @NextPeopleID = NEXT VALUE FOR PeopleSeq;
    PRINT '下一个 SEQUENCE 值: ' + CAST(@NextPeopleID AS VARCHAR(10));

    -- 向员工表插入数据
    INSERT INTO People (PeopleID, DepartmentID, RankID, PeopleName, PeopleSex, PeopleBirth, PeopleSalary, PeoplePhone, PeopleAddress, PeopleRemark)
    VALUES			   
        (@NextPeopleID + 0, 2, 1,     '老李', '女', '2014/12/6', '88888', '3999', '西二环888号', '厉害的人'),
        (@NextPeopleID + 1, 1, 1, '老马', '男', '2014/11/6', '61188', '11112669', '西san环118号', '好厉害的人'),
        (@NextPeopleID + 2, 1,  1,   '老四', '女', '2014/12/6', '88888', '1999', '西二环888号', '厉害的人'),
        (@NextPeopleID + 3, 1, 1, '老吴', '男', '2014/11/6', '61188', '1100', '西san环118号', '好厉害的人'),
        (@NextPeopleID + 4, 1, 1,   '老王', '女', '2014/12/6', '88888', '19', '西二环888号', '厉害的人'),
        (@NextPeopleID + 5, 1, 1, '老五', '男', '2014/11/6', '61188', '10001', '西san环118号', '好厉害的人'),
        (@NextPeopleID + 6, 1, 1,    '老刘', '女', '2014/12/6', '88888', '9', '西二环888号', '厉害的人'),
        (@NextPeopleID + 7, 1, 1, '老六', '男', '2014/11/6', '61188', '11000', '西san环118号', '好厉害的人'),
        (@NextPeopleID + 8, 1, 1,    '老七', '女', '2014/12/6', '88888', '39', '西二环888号', '厉害的人'),
        (@NextPeopleID + 9, 1, 1, '老八', '男', '2014/11/6', '61188', '900', '西san环118号', '好厉害的人');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- 回滚事务
    ROLLBACK TRANSACTION;

    -- 回滚 SEQUENCE 到插入前的值
    DECLARE @RangeFirstValue SQL_VARIANT;
    EXEC sp_sequence_get_range 
        @sequence_name = 'PeopleSeq', 
        @range_size = 1, -- 重置 SEQUENCE
        @range_first_value = @RangeFirstValue OUTPUT;

    PRINT '插入失败，事务已回滚，SEQUENCE 已回滚';
    PRINT '错误编号: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
    PRINT '错误消息: ' + ERROR_MESSAGE();
    PRINT '错误行号: ' + CAST(ERROR_LINE() AS VARCHAR(10));
    PRINT '错误状态: ' + CAST(ERROR_STATE() AS VARCHAR(10));
    PRINT '错误严重级别: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
    PRINT '错误过程: ' + ISNULL(ERROR_PROCEDURE(), 'N/A');
END CATCH;

四、修改和删除数据

---