sql sever 学习笔记

SQL Server 数据库表创建

选择数据库

USE DBTEST;

判断表是否存在并删除

IF EXISTS(SELECT * FROM sys.objects WHERE name='Department' AND type='U')
    DROP TABLE Department;

创建 Department 表

CREATE TABLE Department
(
    DepartmentId INT PRIMARY KEY IDENTITY(1,1), -- 部门编号，自动增长的主键
    DepartmentName NVARCHAR(50) NOT NULL, -- 部门名称
    DepartmentRemark TEXT -- 部门描述
);

字符串类型说明

• CHAR(n): 定长字符串类型，总是占用 n 字节空间。
• VARCHAR(n): 变长字符串类型，最多占用 n 字节空间。
• TEXT: 用于存储大量文本，但已被 VARCHAR(MAX) 和 NVARCHAR(MAX) 替代。
• NCHAR, NVARCHAR, NTEXT: 用于存储 Unicode 字符，对中文等多字节字符支持更好。

创建 Rank 表

CREATE TABLE [Rank]
(
    RankId INT PRIMARY KEY IDENTITY(1,1), -- 职级编号，自动增长的主键
    RankName NVARCHAR(50) NOT NULL, -- 职级名称
    RankRemark TEXT -- 职级描述
);

创建 People 表

CREATE TABLE People
(
    PeopleId INT PRIMARY KEY IDENTITY(1,1), -- 员工编号，自动增长的主键
    DepartmentId INT REFERENCES Department(DepartmentId) NOT NULL, -- 部门ID，外键引用 Department 表
    RankId INT REFERENCES [Rank](RankId) NOT NULL, -- 职级ID，外键引用 Rank 表
    PeopleName NVARCHAR(50) NOT NULL, -- 员工姓名
    PeopleSex NVARCHAR(1) CHECK (PeopleSex IN ('男', '女')) DEFAULT('男') NOT NULL, -- 性别，默认为男
    PeopleBirth SMALLDATETIME NOT NULL, -- 出生日期
    PeopleSalary DECIMAL(12,2) CHECK (PeopleSalary BETWEEN 1000 AND 1000000) NOT NULL, -- 工资，范围检查
    PeoplePhone VARCHAR(20) UNIQUE NOT NULL, -- 员工电话，唯一约束
    PeopleAddress VARCHAR(300), -- 员工住所
    PeopleAddTime SMALLDATETIME DEFAULT(GETDATE()) -- 添加时间，默认为当前时间
);

注释和补充

• IDENTITY: 用于设置自增列，(1,1) 表示起始值为1，每次增加1。
• REFERENCES: 设置外键约束，确保参照完整性。
• CHECK: 设置值的约束条件，例如工资范围或性别选项。
• DEFAULT: 设置字段的默认值。
• UNIQUE: 确保列中所有值都是唯一的，常用于如电话号码这样的标识信息。
• SMALLDATETIME: 存储日期和时间，范围从1900到2079年。
• DECIMAL: 高精度数值类型，适用于财务数据等需要精确到小数点后几位的场景。

SQL Server 修改表结构

在业务中修改操作应尽量减少，设计时考虑冗余字段。

添加列

语法：ALTER TABLE 表名 ADD 新列名 数据类型

        -- 给员工表添加邮箱
        ALTER TABLE People ADD PeopleMail VARCHAR(200);
    

删除列

语法：ALTER TABLE 表名 DROP COLUMN 列名

        -- 删除邮箱这一列
        ALTER TABLE People DROP COLUMN PeopleMail;
    

修改列

语法：ALTER TABLE 表名 ALTER COLUMN 列名 数据类型

注意：如果列中的数据超过新的长度限制，修改会失败。

        -- 修改地址VARCHAR(300)为VARCHAR(200)
        ALTER TABLE People ALTER COLUMN PeopleAddress VARCHAR(200);
    

维护约束

删除约束

语法：ALTER TABLE 表名 DROP CONSTRAINT 约束名

        -- 删除月薪的约束
        ALTER TABLE People DROP CONSTRAINT CK__People__PeopleSa__45F365D3;
    

添加约束

CHECK约束

语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(表达式)

        -- 添加工资字段约束，工资必须在1000-100000之间
        ALTER TABLE People ADD CONSTRAINT CK__People__PeopleSa__45F365D3
        CHECK(PeopleSalary >= 1000 AND PeopleSalary <= 100000);
    

主键约束

语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名)

唯一约束

语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)

默认值约束

语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 默认值 FOR 列名

外键约束

语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 关联表名(列名)

sql sever 数据插入笔记

插入数据

向部门表插入数据

完整指定列名：

        INSERT INTO Department(DepartmentName, DepartmentRemark)
        VALUES('市场部', '……'),
              ('营销部', '……'),
              ('企划部', '……');
    

简写（不推荐，因表结构变化可能导致错误）：

        INSERT INTO Department
        VALUES('硬件部', '……');
    

一次插入多行数据：

        INSERT INTO Department(DepartmentName, DepartmentRemark)
        SELECT '测试部', '……'
        UNION
        SELECT '实施部', '……'
        UNION
        SELECT '产品部', '……';
    

向员工表插入数据

        INSERT INTO People(
            [DepartmentId], [RankId], [PeopleName], [PeopleSex], [PeopleBirth],
            [PeopleSalary], [PeoplePhone], [PeopleAddress], [PeopleAddTime]
        )
        VALUES(2, 1, '张三', '男', '1988-9-9',
               5000, '1300000000', '中国', GETDATE());