AI教我学编程之SQL Server常见指令以及数据类型

前言

今天在工作的过程中，遇到了许多常见的属性，在此做下记录，方便以后查询

SQL（Structured Query Language）是一种用于与数据库进行通信的编程语言。它被用于存储、检索、修改和管理关系数据库系统中的数据。SQL 的设计目的是提供一种简洁、直观的方式来处理数据，使得无论是技术人员还是非技术人员都能够理解和使用。

基础概念涵盖了几个关键点：

数据操作语言 (DML): 这部分的 SQL 指令用于处理数据。它包括如下指令：
- SELECT：查询数据库中的数据。
- INSERT：向数据库表中插入新数据。
- UPDATE：修改表中的现有数据。
- DELETE：从表中删除数据。
数据定义语言 (DDL): DDL 指令用于定义或修改数据库结构。这些指令包括：
- CREATE：创建新的数据库或数据库表。
- ALTER：修改现有数据库或表的结构。
- DROP：删除整个数据库或表。
数据控制语言 (DCL): 这类指令用于控制对数据库的访问。主要包括：
- GRANT：授予用户或角色访问数据库的权限。
- REVOKE：撤销先前授予的权限。
事务控制语言 (TCL): 用于管理数据库事务的指令，主要包括：
- BEGIN TRANSACTION：开始一个新的事务。
- COMMIT：提交当前事务，使所有更改成为永久性。
- ROLLBACK：撤销当前事务中的所有更改。

每种类型的 SQL 指令都有其特定的用途和应用场景，它们共同构成了 SQL 语言的基础，使得 SQL 成为处理关系数据库中数据的强大工具。

由此，我们了解到SQL分为四种类型，分别是数据操作语言 (DML)，数据定义语言 (DDL),数据控制语言 (DCL),事务控制语言 (TCL)

光有概念怎么行

根据你以上分四类列举的SQL指令的特定的用途和应用场景构建一个实例，在这个实例中需要体现SQL不同类型指令的不同之处

当然，让我们通过一个实例来展示 SQL 中不同类型指令的特定用途和应用场景。在这个例子中，我们将创建一个简单的数据库环境，涉及创建表、插入和查询数据、修改表结构、控制访问权限，以及事务处理。

假设我们正在管理一个图书馆的数据库，需要记录书籍和借书记录。

1. 数据定义语言 (DDL) - 创建表

首先，我们需要创建两个表：Books 和 BorrowRecords。

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title NVARCHAR(100),
    Author NVARCHAR(100),
    PublishedYear INT
);

CREATE TABLE BorrowRecords (
    RecordID INT PRIMARY KEY,
    BookID INT,
    BorrowerName NVARCHAR(100),
    BorrowDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

2. 数据操作语言 (DML) - 插入和查询数据

接下来，我们向 Books 表中插入一些数据，并查询数据。

-- 插入数据
INSERT INTO Books (BookID, Title, Author, PublishedYear) VALUES (1, 'SQL for Beginners', 'John Doe', 2020);

-- 查询数据
SELECT * FROM Books WHERE Author = 'John Doe';

3. 数据定义语言 (DDL) - 修改表结构

假设我们需要为 Books 表增加一个新列 Genre。

ALTER TABLE Books ADD Genre NVARCHAR(50);

4. 数据控制语言 (DCL) - 控制访问权限

我们想要授予用户 Alice 查询 Books 表的权限。

GRANT SELECT ON Books TO Alice;

5. 事务控制语言 (TCL) - 管理事务

最后，假设一个借书记录的创建需要作为一个事务来处理。

BEGIN TRANSACTION;
    INSERT INTO BorrowRecords (RecordID, BookID, BorrowerName, BorrowDate, ReturnDate) VALUES (1, 1, 'Alice', '2021-01-01', '2021-01-15');
    UPDATE Books SET Quantity = Quantity - 1 WHERE BookID = 1;
COMMIT;

在这个事务中，我们向 BorrowRecords 表中添加一条记录，并更新 Books 表中的书籍数量。如果在执行过程中出现任何错误，整个事务将回滚，以保持数据的一致性。

阶段总结

这个实例展示了如何使用不同类型的 SQL 指令来管理一个简单的图书馆数据库。通过创建表、插入和查询数据、修改表结构、控制用户权限，以及处理复杂的事务，我们能够有效地管理和操作数据库中的信息。

在以上的例子中我们看到有很多的关键字，诸如：VALUES,FOREIGN，那么这些关键字是什么意思呢？

SQL Server关键字

在你列举的例子中，有哪些关键字？

在提供的例子中，使用了多个 SQL 关键字。这些关键字是 SQL 语法的核心组成部分，用于构造数据库操作的命令和语句。以下是例子中使用的关键字：

CREATE：用于创建新的数据库对象，如表或索引。
TABLE：指定操作的对象是表。
PRIMARY KEY：定义表中的主键约束。
INT 和 NVARCHAR：数据类型关键字，用于定义列的数据类型。
INSERT INTO：用于向表中插入新的数据行。
SELECT：用于从一个或多个表中检索数据。
FROM：指定 SELECT、UPDATE 和 DELETE 操作的表。
WHERE：用于指定查询或操作的条件。
ALTER：用于修改现有数据库对象的结构。
ADD：与 ALTER TABLE 一起使用，用于向表中添加新列。
GRANT：用于给用户授权访问数据库的特定权限。
ON：与 GRANT、SELECT、JOIN 等语句一起使用，指定操作的对象或条件。
TO：在 GRANT 语句中使用，指定权限授予的目标用户或角色。
BEGIN TRANSACTION：标记事务的开始。
UPDATE：用于更新表中的现有数据。
SET：与 UPDATE 一起使用，指定要更新的列和新值。
COMMIT：用于提交事务，使事务中所有操作成为永久性更改。

这些关键字在 SQL 语言中扮演着构建和执行数据库操作的基础角色。通过组合这些关键字，可以构建从简单到复杂的各种数据库查询和命令。

这样看其实很累的，那么有没有更简便的办法呢？

我们试着输入以下指令

将这些关键字分类列表展示，列表格式如下
关键字类型| 关键字 | 作用 | 应用场景
数字类型 | INT | ··· | ···
不同的类型以不同的表格返回，格式不变

按照您提供的格式，我将例子中提及的 SQL 关键字分类并列出它们的作用和应用场景。由于这些关键字涵盖了不同的方面，我将它们分为几个类别：

数据定义语言 (DDL)

关键字类型	关键字	作用	应用场景
DDL	`CREATE`	创建新的数据库对象（如表、索引）	创建新表或其他数据库结构
DDL	`TABLE`	指定操作的对象是表	与 `CREATE`、`ALTER` 等语句一起使用
DDL	`ALTER`	修改现有数据库对象的结构	修改表结构，如添加或删除列
DDL	`ADD`	向表中添加新列	用于在现有表中添加新的列

数据类型

关键字类型	关键字	作用	应用场景
数据类型	`INT`	整型数据类型	存储整数值
数据类型	`NVARCHAR`	可变长度字符串类型	存储需要国际化支持的文本数据

数据操作语言 (DML)

关键字类型	关键字	作用	应用场景
DML	`INSERT INTO`	向表中插入新数据	向表中添加新行
DML	`SELECT`	从一个或多个表中检索数据	查询表中的数据
DML	`UPDATE`	更新表中的现有数据	修改表中的数据
DML	`DELETE`	删除表中的数据	从表中移除数据
DML	`SET`	指定更新操作中列的新值	与 `UPDATE` 语句一起使用
DML	`VALUES`	指定 `INSERT` 语句的数据值	在插入数据时指定列的值
DML	`FROM`	指定 `SELECT` 操作的表	指定要查询的数据表
DML	`WHERE`	指定查询或操作的条件	在 `SELECT`、`UPDATE` 和 `DELETE` 中筛选数据

约束和关系

关键字类型	关键字	作用	应用场景
约束	`PRIMARY KEY`	定义表的主键约束	用于唯一标识表中的每一行
约束	`FOREIGN KEY`	定义外键约束	确保引用的完整性，引用另一张表的键

数据控制语言 (DCL)

关键字类型	关键字	作用	应用场景
DCL	`GRANT`	授予用户访问权限	授予用户对数据库对象的访问权限

事务控制语言 (TCL)

关键字类型	关键字	作用	应用场景
TCL	`BEGIN TRANSACTION`	开始一个新的事务	控制一系列操作作为单个工作单位进行
TCL	`COMMIT`	提交当前事务	确认事务中的所有操作

这些分类提供了一个对 SQL 关键字及其作用和应用场景的概览，有助于理解它们在数据库操作中的不同角色和用途。

边学边练

根据以上指令，补充以必要的其他指令，写一段SQL语句，要求，逻辑完整且包含上表中所有指令，在这段SQL语句中添加中文注释，便于初学者阅读

以下是一个包含上表中所有指令的 SQL 语句示例，用于 SQL Server。这段脚本演示了一个简单的数据库操作过程，包括创建表、插入数据、更新数据、查询数据、创建索引、删除数据和最终删除表。我在每个步骤中都加入了中文注释，以便初学者理解。

-- 使用特定数据库
USE myDatabase;

-- 创建新表
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT,
    Department NVARCHAR(50)
);

-- 向表中插入数据
INSERT INTO Employees (ID, Name, Age, Department) VALUES (1, 'Alice', 30, 'HR');
INSERT INTO Employees (ID, Name, Age, Department) VALUES (2, 'Bob', 35, 'Finance');
INSERT INTO Employees (ID, Name, Age, Department) VALUES (3, 'Charlie', 40, 'IT');

-- 更新表中的数据
UPDATE Employees SET Age = 31 WHERE Name = 'Alice';

-- 从表中选择数据
SELECT * FROM Employees;

-- 为表创建索引，提高查询性能
CREATE INDEX idx_department ON Employees(Department);

-- 删除表中的某些数据
DELETE FROM Employees WHERE Name = 'Charlie';

-- 使用JOIN操作来结合另一个表的数据（这里假设有另一个表Departments）
-- 注意：此操作假定存在一个名为Departments的表
SELECT Employees.Name, Employees.Age, Departments.DepartmentName
FROM Employees
JOIN Departments ON Employees.Department = Departments.ID;

-- 对查询结果进行分组
SELECT Department, COUNT(*) as NumberOfEmployees FROM Employees GROUP BY Department;

-- 对结果进行排序
SELECT * FROM Employees ORDER BY Age;

-- 筛选特定条件的数据
SELECT * FROM Employees WHERE Age > 30;

-- 开始一个事务
BEGIN TRANSACTION;
    -- 在事务中执行多个操作
    INSERT INTO Employees (ID, Name, Age, Department) VALUES (4, 'David', 28, 'HR');
    DELETE FROM Employees WHERE Name = 'Bob';
-- 提交事务
COMMIT;

-- 删除表
DROP TABLE Employees;

这段 SQL 脚本涵盖了基本的数据库操作，从创建和使用数据库表开始，到更新、查询、索引创建和删除数据，最后删除整个表。对于初学者来说，理解这些基本概念是学习 SQL 的重要第一步。

以上是较为基础的SQL指令的使用案列，看到这里的道友不妨拿出你的电脑，跟着敲一遍吧！

数据类型

看图说话

除了常见指令外，经常见到的当属数据类型了，如下图

我们从中可以看到，在这个SQL语句段中，刚开始就是一系列的对参数的数据类型进行定义，那么这些数据类型有什么含义呢？

对话AI

针对上图解释这段语句的含义

这段语句是 SQL Server 中的一个存储过程的定义。存储过程是一组为了执行特定任务而预编译的 SQL 语句。它们可以接受参数，执行复杂的操作，并返回结果。下面是对这个存储过程 SPDONGSHAN002014 的参数及其数据类型的解释：

参数和数据类型:
- @FDONGSHAN019001, @FDONGSHAN019002, …, @FDONGSHAN019015, @SDONGSHAN019PID, @SDONGSHAN019UID, @FDONGSHAN016001: 这些参数都使用 VARCHAR(255) 数据类型。VARCHAR 是一个变长字符串类型，255 表示字符串最大长度为 255 个字符。这种类型适用于文本或字符串数据。
- @FDONGSHAN019007, @FDONGSHAN019008: 这些参数使用 BIGINT 数据类型。BIGINT 是一个大范围的整数类型，通常用于存储非常大的整数值。
- @FDONGSHAN019011, @FDONGSHAN019012, @FDONGSHAN019013: 这些参数使用 NUMERIC(18,4) 数据类型。NUMERIC 类型用于精确数值，18 表示数字的总位数，而 4 表示小数点后的位数。这种类型通常用于需要高精度的财务和科学计算。
- @OPTTYPE: 使用 INT 类型，这是一个整数数据类型，用于存储普通范围的整数。
输出参数:
- @PER001: 使用 SMALLINT 输出类型。SMALLINT 是一个较小范围的整数类型，用于存储较小范围的整数值。
- @PER002: 使用 VARCHAR(255) 输出类型。这表明这个输出参数是一个字符串，最大长度为 255 个字符。
存储过程的作用:
- 存储过程的具体逻辑没有在代码段中给出，但从参数列表来看，它可能涉及对数据库中的一些记录进行操作，例如添加、更新或删除记录。参数的多样性表明这个存储过程可能执行复杂的任务，并处理多种数据类型。
应用场景:
- 存储过程通常用于封装复杂的逻辑，提高代码重用性，减少网络通信量，提高性能。在这个例子中，由于参数众多且类型多样，SPDONGSHAN002014 可能用于处理涉及多个字段和数据类型的复杂业务逻辑。

总结来说，这段存储过程的定义展示了如何在 SQL Server 中创建一个接受多种数据类型的存储过程，以及如何定义输出参数。这种多参数和多数据类型的结构通常用于执行复杂的数据库操作。

由此，我们见到了一般情况下比较常见的数据类型，只有这些吗？别的数据类型长什么样？又用在什么地方呢？

数据类型我知道

列举中SQL中常见的数据类型，以列表形式返回
列表格式如下
数据类型 | 作用 | 应用场景
INT | ··· | ···

在 SQL 中常见的数据类型及其作用和应用场景如下：

数据类型	作用	应用场景
`INT`	存储整数。	用于不需要小数的数字，如年龄、数量等。
`BIGINT`	存储更大范围的整数。	用于大范围的数值，比如人口统计。
`SMALLINT`	存储小范围的整数。	用于较小数值范围的情况，如楼层号等。
`TINYINT`	存储非常小的整数。	用于非常小的数值，如一个小组的人数。
`DECIMAL`	存储精确的小数。	金融计算，需要精确小数的场景。
`NUMERIC`	与 `DECIMAL` 相同，存储精确的小数。	同 `DECIMAL`。
`FLOAT`	存储近似的小数。	需要存储大范围浮点数时使用。
`REAL`	存储近似的小数，比 `FLOAT` 精度低。	用于需要较小范围的浮点数。
`VARCHAR`	存储可变长度的字符串。	用于文本或字符数据，长度可变。
`CHAR`	存储固定长度的字符串。	用于长度固定的文本。
`TEXT`	存储大量文本。	存储大型文本，如文章或日志等。
`DATE`	存储日期。	用于存储日期数据，不包括时间。
`DATETIME`	存储日期和时间。	用于需要同时存储日期和时间的情况。
`TIME`	存储时间。	只存储时间的场景。
`BIT`	存储二进制数字，通常用于存储布尔值（0 或 1）。	常用于存储真/假值。
`BLOB`	存储二进制大对象，如图像、音频或其他多媒体文件。	存储大型二进制数据。

这些数据类型在 SQL 数据库设计和操作中扮演着关键角色，确保了数据的准确性和效率。根据应用场景的不同，选择合适的数据类型是数据库设计的重要环节。

细心的同学已经注意到了，刚才明明看到这些关键字后面跟了个（18,3）这样的字样，怎么没看到，别急，就在下方

跟在这些数据类型关键字后面括号中的数字表示什么意思？

括号里的神秘数字

在 SQL 中，跟在数据类型关键字后面的括号中的数字具有特定的意义，通常表示该数据类型的大小或长度。不同数据类型的这些数字代表的意义也各不相同。以下是一些常见数据类型及其数字含义的概述：

VARCHAR(n) 和 CHAR(n):
- 对于字符串类型如 VARCHAR 和 CHAR，括号中的数字 n 表示字符的最大数量。例如，VARCHAR(50) 表示可变长度的字符串，最多包含 50 个字符；CHAR(10) 表示固定长度的字符串，恒定为 10 个字符。
DECIMAL(p, s) 和 NUMERIC(p, s):
- 对于 DECIMAL 和 NUMERIC 类型，p（精度）表示数字中允许的最大总位数（包括小数点两侧），而 s（标度）表示小数点后面的位数。例如，DECIMAL(10, 2) 表示数字最多有 10 位，其中小数部分最多 2 位，整数部分最多 8 位。
FLOAT(n) 和 REAL:
- 对于 FLOAT 类型，n 表示精度，即所需的近似数字的位数。REAL 是 FLOAT 的特定子类型，通常有固定的精度。
BINARY(n) 和 VARBINARY(n):
- 对于二进制类型如 BINARY 和 VARBINARY，n 表示字节的数量。BINARY(10) 表示固定长度为 10 字节的二进制数据；VARBINARY(100) 表示最多 100 字节的可变长度二进制数据。
BIT(n):
- 对于 BIT 类型，n 表示存储的位数。例如，BIT(1) 常用于存储布尔值，只能是 0 或 1。

这些数字的设定对于优化数据库存储空间、确保数据完整性和性能调优非常重要。选择合适的长度或大小有助于有效地使用数据库资源，并确保数据满足应用的需求。

还是那句话，光看没用，得用了才记得住，上才艺

边练边学

当然，我们可以通过创建一个简单的数据库示例来演示不同数据类型的应用。在这个示例中，我们将创建一个名为 School 的数据库，其中包含一个 Students 表和一个 Courses 表。我将一步一步地解释每个数据类型的应用，并提供相应的 SQL 代码，以便初学者能够跟着敲击键盘，同时理解这些数据类型的含义。

首先，让我们创建 Students 表：

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,           -- INT: 用于存储整数。
    FirstName VARCHAR(50),              -- VARCHAR(50): 可变长度字符串，最多50个字符。
    LastName VARCHAR(50),
    DateOfBirth DATE,                   -- DATE: 仅存储日期。
    Gender CHAR(1),                     -- CHAR(1): 固定长度的字符，这里用于性别，M或F。
    PhoneNumber VARCHAR(15)             -- 可变长度字符串，这里用于电话号码。
);

解释：

StudentID 是 INT 类型，适合用作主键，因为学生ID是整数。
FirstName 和 LastName 是 VARCHAR(50) 类型，用于存储最多 50 个字符的名字和姓氏。
DateOfBirth 是 DATE 类型，适合存储生日日期。
Gender 是 CHAR(1) 类型，用于存储单个字符，如 ‘M’ 或 ‘F’。
PhoneNumber 是 VARCHAR(15) 类型，可以存储格式化的电话号码。

接着，创建 Courses 表：

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,            -- INT: 用于存储整数。
    Title VARCHAR(100),                 -- VARCHAR(100): 可变长度字符串，最多100个字符。
    CreditHours TINYINT,                -- TINYINT: 存储小的整数，如学分小时数。
    CourseFee DECIMAL(10, 2)             -- DECIMAL(10, 2): 存储金额，最多10位，小数点后2位。
);