【SQL Server】数据库开发指南（二）MSSQL数据库开发对于库、表、数据类型、约束等相关操作

我不是女神ヾ 2023-09-25 21:26 10阅读 0赞

本系列博文还在更新中，收录在专栏：[\#MS-SQL Server][MS-SQL Server] 专栏中。

本系列文章列表如下：

[【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作][SQL Server_ Linux _ SQL Server]  
[【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤][SQL Server]  
[【SQL Server】数据库开发指南（二）MSSQL数据库开发对于库、表、数据类型、约束等相关操作][SQL Server_MSSQL]  
[【SQL Server】数据库开发指南（三）面向数据分析的 T-SQL 编程技巧与实践][SQL Server_ T-SQL]  
[\[ 云原生 | Docker \] 构建高可用性的 SQL Server：Docker 容器下的主从同步实现指南][_ _ Docker _ _ SQL Server_Docker]  
[【SQL Server】数据库开发指南（五）T-SQL 高级查询综合应用与实战][SQL Server_T-SQL]  
[【SQL Server】数据库开发指南（六）索引和视图的使用技巧、方法与综合应用][SQL Server 1]  
[【SQL Server】数据库开发指南（七）MS-SQL存储过程全面解析：种类、优点和创建方法详解][SQL Server_MS-SQL]  
[【SQL Server】数据库开发指南（八）高级数据处理技术 MS-SQL 事务、异常和游标的深入研究][SQL Server_ MS-SQL]  
[【SQL Server】数据库开发指南（九）详细讲解 MS-SQL 触发器的基础概念与应用场景][SQL Server_ MS-SQL 1]

### 文章目录 ###

*   *  一、SQL Server 中的 GO 关键字
     *  二、切换不同数据库
     *  三、创建、删除数据库
     *   *  3.1 创建方式1：基本创建（适合演示和学习）
         *  3.2 创建方式2：设置存储位置以及大小等
         *  3.2 创建方式3：同时设置主与次数据文件信息
     *  五、SQL Server 的基本数据类型
     *   *  5.1 精确数字类型
         *  5.2 近似数字类型
         *  5.3 日期时间类型
         *  5.5 字符串类型
         *  5.6 Unicode字符串类型
         *  5.7 二进制字符串类型
     *  六、SQL Server 判断表或其他对象及列是否存在
     *   *  6.1 判断某个表或对象是否存在
         *  6.2 判断该列名是否存在，如果存在就删除
     *  七、SQL Server 中创建、删除表
     *  八、SQL Server 中修改的表
     *   *  8.1 给表添加字段、修改字段、删除字段
         *  8.2 给表添加、删除约束
     *  九、SQL Server 中操作数据
     *   *  9.1 插入数据
         *  9.2 查询、修改、删除数据
     *  十、备份数据、备份表

## 一、SQL Server 中的 GO 关键字 ##

`GO` 是 T-SQL 中的一种分隔符，用于将批处理中的 T-SQL 语句分成多个批次。每个批次在 GO 后面执行。GO 通常被用于以下情况：

*  在一个脚本中，用于分隔多个 SQL 语句，因为 SQL Server 需要知道每个语句的结束位置。
 *  在一个事务中，用于提交或回滚一个批处理的事务。  
    `GO` 并不是 T-SQL 的语句或命令，而是由 SQL Server 客户端应用程序识别的一个指令。

以下是一个使用 `GO` 分隔批处理的示例：

-- 创建新的表
    create table Employee (
       ID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       Age INT
    )
    GO
    
    -- 向表中插入数据
    insert into Employee values (1, 'John', 'Doe', 25)
    insert into Employee values (2, 'Jane', 'Doe', 30)
    insert into Employee values (3, 'Bob', 'Smith', 40)
    GO
    
    -- 查询表中数据
    select * from Employee

注意：`GO` 必须单独一行，不能和其他语句或命令在同一行。另外，GO 后面可以指定一个可选的参数，表示要执行多少次批处理。例如，`GO 5` 表示要执行当前批处理 5 次。但是这个功能只能在某些客户端应用程序中使用，而在 SQL Server Management Studio 中不支持。

> GO 是用于将 T-SQL 批处理分成多个批次的分隔符，通常在一个脚本中或一个事务中使用。

## 二、切换不同数据库 ##

SQL Server 和其他数据库一样，都是使用 `use` 关键字，即可切换成所需要使用的数据库。

use master
    go

## 三、创建、删除数据库 ##

### 3.1 创建方式1：基本创建（适合演示和学习） ###

--判断是否存在该数据库，存在就删除
    if (exists (select * from sys.databases where name = 'demoTest'))
        drop database demoTest
    go
    --创建数据库，设置数据库文件、日志文件保存目录
    create database demoTest
    on(
        name = 'demoTest',
        filename = 'c:\data\demoTest.mdf'    
    )
    log on(
        name = 'demoTest_log',
        filename = 'c:\data\demoTest_log.ldf'
    )
    go

### 3.2 创建方式2：设置存储位置以及大小等 ###

注意 上述 T-SQL 中， `size` 与 maxSize 都是以 MB 为单位，`fileGrowth` 可以以 MB 为单位，也可以以百分比为单位。

### 3.2 创建方式3：同时设置主与次数据文件信息 ###

在 SQL Server 中，可以在创建数据库时同时设置**主**数据文件和**次**数据文件。主数据文件是数据库的主要数据文件，包含数据库的系统表和用户数据。次数据文件是包含用户数据的文件，可以帮助扩展数据库的存储容量并提高性能。

if (exists (select * from sys.databases where name = 'demoTest'))
        drop database demoTest
    go
    create database demoTest
    --默认就属于 primary 主文件组，可省略
    on primary (    
        --数据文件的具体描述
        name = 'demoTest_data',                --主数据文件的逻辑名
        fileName = 'c:\demoTest_data.mdf',     --主数据文件的物理名
        size = 5MB,                            --主数据文件的初始大小
        maxSize = 50MB,                        --主数据文件增长的最大值
        fileGrowth = 15%                       --主数据文件的增长率
    ),
    --次数据文件的具体描述
    (    
        --数据文件的具体描述
        name = 'demoTest2_data',               --主数据文件的逻辑名
        fileName = 'c:\demoTest2_data.mdf',    --主数据文件的物理名
        size = 2MB,                            --主数据文件的初始大小
        maxSize = 50MB,                        --主数据文件增长的最大值
        fileGrowth = 10%                       --主数据文件的增长率
    )
    --日志文件的具体描述，各参数含义同上
    log on (
        name = 'demoTest_log',
        fileName = 'c:\demoTest_log.ldf',
        size = 1MB,
        fileGrowth = 1MB
    ),
    (
        name = 'demoTest2_log',
        fileName = 'c:\demoTest2_log.ldf',
        size = 1MB,
        fileGrowth = 1MB
    )
    go

## 五、SQL Server 的基本数据类型 ##

### 5.1 精确数字类型 ###

在 SQL Server 中，有几种精确数字类型可用于存储精确数字值。这些类型的特点是可以精确地存储小数点前后的数字，并避免精度丢失问题。以下是 SQL Server 中可用的精确数字类型：

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">tinyint</td> 
   <td align="left">用于存储非负整数值，范围为 0 到 255。该类型存储为 1 字节的整数。</td> 
  </tr> 
  <tr> 
   <td align="left">smallint</td> 
   <td align="left">用于存储较小的整数值，范围为 -32,768 到 +32,767。该类型存储为 2 字节的整数。</td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td align="left">用于存储整数值，范围为 -2^31（-2,147,483,648）到 2^31-1（+2,147,483,647）。该类型存储为 4 字节的整数。</td> 
  </tr> 
  <tr> 
   <td align="left">bigint</td> 
   <td align="left">用于存储大整数值，范围为 -2^63（-9,223,372,036,854,775,808）到 2^63-1（+9,223,372,036,854,775,807）。该类型存储为 8 字节的整数。</td> 
  </tr> 
  <tr> 
   <td align="left">bit</td> 
   <td align="left">可以取值为 1、0 或 NULL 的整数数据类型，每8个bit占一个字节，16bit就2个字节，24bit就3个字节</td> 
  </tr> 
  <tr> 
   <td align="left">decimal</td> 
   <td align="left">用于存储精确的定点数值，例如货币金额。DECIMAL/NUMERIC 数据类型包含两个参数：精度和小数位数。例如，有效值从 - 10^38 +1 到 10^38 - 1。DECIMAL(10,2) 表示具有最大总位数为 10 位，其中有 2 位小数。</td> 
  </tr> 
  <tr> 
   <td align="left">numeric</td> 
   <td align="left">与<code>decimal </code>相同</td> 
  </tr> 
  <tr> 
   <td align="left">money</td> 
   <td align="left">用于存储货币值，范围为 -2^63（-922,337,203,685,477.5808）到 2^63-1（+922,337,203,685,477.5807）。该类型存储为 8 字节的定点数。</td> 
  </tr> 
  <tr> 
   <td align="left">smallmoney</td> 
   <td align="left">用于存储较小的货币值，范围为 -214,748.3648 到 +214,748.3647。该类型存储为 4 字节的定点数。</td> 
  </tr> 
 </tbody> 
</table>

### 5.2 近似数字类型 ###

除了精确数字类型之外，SQL Server 还提供了近似数字类型，可以存储小数点后面一定位数的数字，但是不保证完全准确，因为存储的值会被截断或四舍五入。以下是 SQL Server 中的一些近似数字类型：

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>float</td> 
   <td>表示浮点数值数据的大致数值数据类型。浮点数据为近似值；范围-1.79E + 308 至 -2.23E - 308、0 以及 2.23E - 308 至 1.79E + 308</td> 
  </tr> 
  <tr> 
   <td>real</td> 
   <td>real 的 SQL-92 同义词为 float(24)，范围在-3.40E + 38 至 -1.18E - 38、0 以及 1.18E - 38 至 3.40E + 38</td> 
  </tr> 
 </tbody> 
</table>

### 5.3 日期时间类型 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>datetime</td> 
   <td>表示某天的日期和时间的数据类型，范围在1753 年 1 月 1 日到 9999 年 12 月 31 日</td> 
  </tr> 
  <tr> 
   <td>smalldatetime</td> 
   <td>范围在1900 年 1 月 1 日到 2079 年 6 月 6 日</td> 
  </tr> 
 </tbody> 
</table>

### 5.5 字符串类型 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char</td> 
   <td>固定长度或可变长度的字符数据类型，范围在范围为 1 至 8,000字节</td> 
  </tr> 
  <tr> 
   <td>text</td> 
   <td>最大长度为 2^31-1</td> 
  </tr> 
  <tr> 
   <td>varchar</td> 
   <td>固定长度或可变长度的字符数据类型，最大存储大小是 2^31-1 个字节</td> 
  </tr> 
 </tbody> 
</table>

### 5.6 Unicode字符串类型 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>nchar</td> 
   <td>字符数据类型，长度固定，在必须在 1 到 4,000 之间</td> 
  </tr> 
  <tr> 
   <td>nvarchar</td> 
   <td>可变长度 Unicode 字符数据。最大存储大小为 2^31-1 字节</td> 
  </tr> 
  <tr> 
   <td>ntext</td> 
   <td>长度可变的 Unicode 数据，最大长度为 2^30 - 1 (1,073,741,823) 个字符</td> 
  </tr> 
 </tbody> 
</table>

### 5.7 二进制字符串类型 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>binary</td> 
   <td>长度为 n 字节的固定长度二进制数据，范围从 1 到 8,000 的值。存储大小为 n 字节。</td> 
  </tr> 
  <tr> 
   <td>varbinary</td> 
   <td>可变长度二进制数据。n 可以取从 1 到 8,000 的值。最大的存储大小为 2^31-1 字节</td> 
  </tr> 
  <tr> 
   <td>image</td> 
   <td>长度可变的二进制数据，从 0 到 2^31-1 (2,147,483,647) 个字节</td> 
  </tr> 
 </tbody> 
</table>

## 六、SQL Server 判断表或其他对象及列是否存在 ##

### 6.1 判断某个表或对象是否存在 ###

--判断某个表或对象是否存在
    if (exists (select * from sys.objects where name = 'username'))
        print '存在';
    go
    if (exists (select * from sys.objects where object_id = object_id('student')))
        print '存在';
    go
    if (object_id('student', 'U') is not null)
        print '存在';
    go

### 6.2 判断该列名是否存在，如果存在就删除 ###

--判断该列名是否存在，如果存在就删除
    if (exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard'))
        alter table student drop column idCard
    go
    if (exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
        alter table student drop column tel
    go

## 七、SQL Server 中创建、删除表 ##

--判断是否存在当前table
    if (exists (select * from sys.objects where name = 'classes'))
        drop table classes
    go
    create table classes(
        id int primary key identity(1, 2),
        name varchar(22) not null,
        createDate datetime default getDate()
    )
    go
    if (exists (select * from sys.objects where object_id = object_id('student')))
        drop table student
    go
    --创建table
    create table student(
        id int identity(1, 1) not null,
        name varchar(20),
        age int,
        sex bit,
        cid int
    )
    go

## 八、SQL Server 中修改的表 ##

### 8.1 给表添加字段、修改字段、删除字段 ###

--添加字段
    alter table student add address varchar(50) not null;
    --修改字段
    alter table student alter column address varchar(20);
    --删除字段
    alter table student drop column number;
     
    --添加多个字段
    alter table student 
    add address varchar(22),
        tel varchar(11),
        idCard varchar(3);
     
    --判断该列名是否存在，如果存在就删除
    if (exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard'))
        alter table student drop column idCard
    go
    if (exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
        alter table student drop column tel
    go

### 8.2 给表添加、删除约束 ###

--添加新列、约束
    alter table student 
        add number varchar(20) null constraint no_uk unique;  
    --增加主键
    alter table student  
        add constraint pk_id primary key(id);  
    --添加外键约束
    alter table student
        add constraint fk_cid foreign key (cid) references classes(id)
    go
    --添加唯一约束
    alter table student
        add constraint name_uk unique(name);
    --添加check约束
    alter table student with nocheck   
        add constraint check_age check (age > 1);
    alter table student
        add constraint ck_age check (age >= 15 and age <= 50)
    --添加默认约束
    alter table student
        add constraint sex_def default 1 for sex;
    --添加一个包含默认值可以为空的列
    alter table student 
        add createDate smalldatetime null
        constraint createDate_def default getDate() with values;
     
    ----- 多个列、约束一起创建--------
    alter table student add   
        /*添加id主键、自增*/  
        id int identity constraint id primary key,   
        /* 添加外键约束*/   
        number int null    
        constraint uNumber references classes(number),  
        /*默认约束*/  
        createDate decimal(3, 3)  
        constraint createDate default 2010-6-1  
    go 
     
    --删除约束
    alter table student  drop constraint no_uk;

## 九、SQL Server 中操作数据 ##

### 9.1 插入数据 ###

插入数据的关键字用`insert into values`关键字，也可以像其他数据库一样，使用`insert into select`。

insert into classes(name) values('1班');
    insert into classes values('2班', '2011-06-15');
    insert into classes(name) values('3班');
    insert into classes values('4班', default);
     
    insert into student values('zhangsan', 22, 1, 1);
    insert into student values('lisi', 25, 0, 1);
    insert into student values('wangwu', 24, 1, 3);
    insert into student values('zhaoliu', 23, 0, 3);
    insert into student values('mazi', 21, 1, 5);
    insert into student values('wangmazi', 28, 0, 5);
    insert into student values('jason', null, 0, 5);
    insert into student values(null, null, 0, 5);
     
    insert into student 
    select 'zhangtian' name, age, sex, cid 
    from student 
    where name = 'tony';
        
    --多条记录同时插入
    insert into student
        select 'jack', 23, 1, 5 union
        select 'tom', 24, 0, 3 union
        select 'wendy', 25, 1, 3 union
        select 'tony', 26, 0, 5;

### 9.2 查询、修改、删除数据 ###

--查询数据
    select * from classes;
    select * from student;
    select id, 'bulise' name, age, sex, cid from student 
    where name = 'tony';
    select *, (select max(age) from student) from student 
    where name = 'tony';
     
    --修改数据
    update student set name = 'hoho', sex = 1 where id = 1;
     
    --删除数据（from可省略）
    delete from student where id = 1;

## 十、备份数据、备份表 ##

常用的备份表操作 select

--备份、复制student表到stu
    select * into stu from student;
    select * into stu1 from (select * from stu) t;
    select * into stu2 from student where 1=2;   --只备份表结构
    select * from stu;
    select * from stu1;
    select * from stu2;

[MS-SQL Server]: https://blog.csdn.net/dietime1943/category_6436988.html
[SQL Server_ Linux _ SQL Server]: https://bluetata.blog.csdn.net/article/details/129864499
[SQL Server]: https://bluetata.blog.csdn.net/article/details/129905010
[SQL Server_MSSQL]: https://bluetata.blog.csdn.net/article/details/129928569
[SQL Server_ T-SQL]: https://bluetata.blog.csdn.net/article/details/130040273
[_ _ Docker _ _ SQL Server_Docker]: https://bluetata.blog.csdn.net/article/details/130124814
[SQL Server_T-SQL]: https://bluetata.blog.csdn.net/article/details/130168306
[SQL Server 1]: https://bluetata.blog.csdn.net/article/details/130899849
[SQL Server_MS-SQL]: https://bluetata.blog.csdn.net/article/details/130907754
[SQL Server_ MS-SQL]: https://bluetata.blog.csdn.net/article/details/131039893
[SQL Server_ MS-SQL 1]: https://bluetata.blog.csdn.net/article/details/131093456