SQL Server 数据库编程基础与常用语法详解

批处理与数据库管理

在 SQL Server 中，GO 是一个用于将一组 Transact-SQL 语句作为一个批处理执行的指令，并非标准的 SQL 语句。它常用于在脚本中将逻辑分组分开执行。

第1步：使用或切换数据库

通过 USE 命令可以切换到目标数据库进行操作。

USE master;
GO

第2步：创建或删除数据库

建议在建库前先检查是否存在同名数据库，以避免重复报错。以下展示了几种不同复杂度的创建方式：

方法1：基本创建并指定路径

-- 判断是否存在该数据库，存在就删除
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'testHome')
    DROP DATABASE testHome;
GO

-- 创建数据库，设置文件保存目录
CREATE DATABASE testHome
ON (
    NAME = 'testHome',
    FILENAME = 'c:\\data\\students.mdf'
)
LOG ON (
    NAME = 'testHome_log',
    FILENAME = 'c:\\data\\testHome_log.ldf'
);
GO

方法2：设置文件大小与增长率

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'testHome')
    DROP DATABASE testHome;
GO

CREATE DATABASE testHome
ON PRIMARY (
    NAME = 'testHome_data',       -- 主数据文件逻辑名
    FILENAME = 'c:\\testHome_data.mdf', -- 物理文件名
    SIZE = 3MB,                  -- 初始大小
    MAXSIZE = 50MB,              -- 最大增长限制
    FILEGROWTH = 10%             -- 每次增长的百分比
)
LOG ON (
    NAME = 'testHome_log',
    FILENAME = 'c:\\testHome_log.ldf',
    SIZE = 1MB,
    FILEGROWTH = 1MB
);
GO

方法3：包含次数据文件与日志文件

当主数据文件空间不足时，可添加次要数据文件进行扩展，同时也可增加额外的日志文件以记录事务。

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'testHome')
    DROP DATABASE testHome;
GO

CREATE DATABASE testHome
ON PRIMARY (
    NAME = 'testHome_data',
    FILENAME = 'c:\\testHome_data.mdf',
    SIZE = 3MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10%
),
( -- 次要数据文件
    NAME = 'testHome2_data',
    FILENAME = 'c:\\testHome2_data.mdf',
    SIZE = 2MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10%
)
LOG ON (
    NAME = 'testHome_log',
    FILENAME = 'c:\\testHome_log.ldf',
    SIZE = 1MB,
    FILEGROWTH = 1MB
),
( -- 额外日志文件
    NAME = 'testHome2_log',
    FILENAME = 'c:\\testHome2_log.ldf',
    SIZE = 1MB,
    FILEGROWTH = 1MB
);
GO

基本数据类型定义

合理选择数据类型是优化存储和性能的关键。SQL Server 支持多种分类的数据类型：

数值类型

• 精确数字：
  
  • bigint：8字节，范围 -2^63 到 2^63-1，适用于超大数据量。
  • int：4字节，范围 -2^31 到 2^31-1，最常用整数类型。
  • smallint：2字节，范围较小。
  • tinyint：1字节，范围 0 到 255。
  • bit：存储 0、1 或 NULL，每8位占一个字节。
  • decimal / numeric：固定精度和小数位数的数值，适用于货币或高精度计算。
  • money / smallmoney：专用的货币数据类型。
• 近似数字：
  
  • float：浮点数，范围极大但精度可能有微小误差。
  • real：SQL-92标准同义词，通常为 float(24)。

日期时间类型

• datetime：覆盖 1753年1月1日 至 9999年12月31日，精度较高。
• smalldatetime：范围较窄（1900年-2079年），精度较低。

字符与 Unicode 类型

• 非 Unicode：char（定长）、varchar（变长，最大 2^31-1 字节）、text（已过时，但在旧版兼容）。
• Unicode：nchar（定长，1-4000字符）、nvarchar（变长，最大 2^31-1 字节）、ntext（已弃用）。

二进制类型

• binary / varbinary：固定或变长二进制数据，通常用于存储图片缩略图或非结构化数据。
• image：大型二进制对象，建议改用 varbinary(max)。

对象管理与表结构维护

第3步：判断对象或列是否存在

在执行删除或修改前，检查对象存在性是编写健壮脚本的习惯。

-- 判断表是否存在
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'classes')
    PRINT '存在';
GO

-- 另一种写法，利用 object_id
IF OBJECT_ID('student', 'U') IS NOT NULL
    PRINT '存在';
GO

-- 判断特定列是否存在（使用 sys.columns）
IF EXISTS (
    SELECT * FROM sys.columns 
    WHERE object_id = OBJECT_ID('student') AND name = 'idCard'
)
BEGIN
    ALTER TABLE student DROP COLUMN idCard;
END
GO

第4步：创建与删除表

在创建表时，可以定义主键、自增列以及默认值。

-- 删除已存在的表
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'classes')
    DROP TABLE classes;
GO

-- 创建班级表
CREATE TABLE classes (
    id INT PRIMARY KEY IDENTITY(1, 2), -- 主键，从1开始，每次递增2
    name VARCHAR(22) NOT NULL,
    createDate DATETIME DEFAULT GETDATE()
);
GO

-- 创建学生表
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'student')
    DROP TABLE student;
GO

CREATE TABLE student (
    id INT IDENTITY(1, 1) NOT NULL,
    name VARCHAR(20),
    age INT,
    sex BIT,
    cid INT -- 班级ID，作为外键关联
);
GO

第5步：表结构的动态修改（字段与约束）

使用 ALTER TABLE 语句可以对现有表进行修改。建议在修改前再次确认字段存在性。

-- 添加新字段
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

第6步：完善约束管理

约束用于保证数据的完整性和一致性，包括主键、外键、唯一、检查和默认约束。

-- 添加带默认值的列
ALTER TABLE student
ADD createDate SMALLDATETIME NULL
CONSTRAINT createDate_def DEFAULT GETDATE() WITH VALUES;

-- 添加唯一约束
ALTER TABLE student ADD CONSTRAINT no_uk UNIQUE(number);

-- 添加主键约束
ALTER TABLE student ADD CONSTRAINT pk_id PRIMARY KEY(id);

-- 添加外键约束（假设 classes 表中有个 number 字段，或 id 字段）
ALTER TABLE student
ADD CONSTRAINT fk_cid FOREIGN KEY (cid) REFERENCES classes(id);

-- 添加检查约束（例如年龄限制）
ALTER TABLE student
ADD CONSTRAINT ck_age CHECK (age >= 15 AND age <= 50);

-- 添加布尔默认值（例如默认性别为女 1）
ALTER TABLE student
ADD CONSTRAINT sex_def DEFAULT 1 FOR sex;

-- 删除某个约束
ALTER TABLE student DROP CONSTRAINT no_uk;

数据操作（CRUD）

第7步：插入数据（Insert）

除了单行插入，还支持基于查询结果的插入和多行同时插入。

-- 单行插入，指名列名
INSERT INTO classes (name) VALUES ('1班');

-- 简写插入（需按列顺序提供所有值）
INSERT INTO classes VALUES ('2班', '2011-06-15');

-- 插入多条记录（使用 UNION ALL）
INSERT INTO student (name, age, sex, cid)
SELECT 'jack', 23, 1, 5 UNION ALL
SELECT 'tom', 24, 0, 3 UNION ALL
SELECT 'wendy', 25, 1, 3;

-- 基于其他表插入数据
INSERT INTO student
SELECT name, age, sex, cid FROM student WHERE name = 'tony';

第8步：查询与更新数据（Select & Update）

-- 基础查询
SELECT * FROM classes;

-- 带子查询的显示
SELECT *, (SELECT MAX(age) FROM student) AS max_age FROM student;

-- 更新数据
UPDATE student SET name = 'hoho', sex = 1 WHERE id = 1;

-- 删除特定数据
DELETE FROM student WHERE id = 1;

备份与辅助工具

表数据复制

使用 SELECT ... INTO 可以将查询结果直接存入一张新表中，常用于临时备份或生成副本。

-- 将 student 表的所有数据复制到新的 stu 表
SELECT * INTO stu FROM student;

-- 复制查询结果集到表
SELECT * INTO stu1 FROM (SELECT * FROM stu) t;

查看表结构信息

系统存储过程 sp_help 可以快速获取表的详细元数据，如索引、列信息和约束。

EXEC sp_help student;
EXEC sp_help classes;

以上就是 SQL Server 数据库编程基础的详细内容，更多关于 SQL Server 实操教程的资料请关注本站其它相关文章！