sql server建库、建表、建约束技巧-猿码集

1. 建库

在 sql server 中，创建数据库的方法有两种，分别是通过 SQL Server Management Studio 进行图形界面操作，或用代码语句创建。这里我们主要讨论后者。

1.1 创建数据库

我们可以用 CREATE DATABASE 命令来创建数据库，语法格式如下：


CREATE DATABASE database_name
[ COLLATE collation_name ]
[ ON
    [ ( NAME = logical_file_name, FILENAME = 'os_file_name' [ , SIZE = size ] [ , MAXSIZE = max_size ] [ , FILEGROWTH = growth_increment ] ) ]
    [, ...n]
]
[ LOG ON     
    [ ( NAME = logical_file_name, FILENAME = 'os_file_name' [ , SIZE = size ] [ , MAXSIZE = max_size ] [ , FILEGROWTH = growth_increment ] ) ]
    [, ...n]
]

其中，database_name 为你想要创建的数据库名，collation_name 为指定的排序规则名称，logical_file_name 为逻辑文件名，os_file_name 为实际物理文件名，size 为初始大小，max_size 为最大大小，growth_increment 为文件增长率。其中 ON 和 LOG ON 分别可以指定创建主文件和主日志文件。

下面是一个创建名为 mydatabase 的数据库的例子：


CREATE DATABASE mydatabase;

1.2 设定数据库选项

我们还可以用 ALTER DATABASE 命令来更改数据库选项，语法格式如下：


USE master;
ALTER DATABASE database_name
SET option_name { ON | OFF } | option_name { = | != } option_value;

其中，database_name 为你要更改的数据库名称，option_name 为你要更改的数据库选项。

以下是一些实用的参数：

OFFLINE：让数据库脱机，不允许任何用户对其进行修改

READ_ONLY：把数据库设置为只读

ALLOW_SNAPSHOT_ISOLATION：允许使用快照隔离

RECOVERY：更改数据库恢复模型类型

下面是一个设置数据库为只读的例子：


USE master;
ALTER DATABASE mydatabase
SET READ_ONLY;

2. 建表

表是数据库中最基本的元素之一，我们可以在其中存储和组织数据，以便于进行查询和操作。在 sql server 中，要创建表需要指定表名、列名、数据类型和约束等属性。

2.1 创建表

我们可以用 CREATE TABLE 命令来创建表，语法格式如下：


CREATE TABLE table_name
(
    column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    ...
    CONSTRAINT constraint_name [ FOREIGN KEY ] [ REFERENCES table_name (column_name) ]
)

其中，table_name 为你要创建的表名称，column1、column2 等为表的列名，datatype 为数据类型，NULL 和 NOT NULL 分别指定列是否允许 NULL 值，CONSTRAINT constraint_name [ FOREIGN KEY ] [ REFERENCES table_name (column_name) ] 可以添加约束条件。

下面是一个创建名为 users 的表的例子：


CREATE TABLE users
(
    id int NOT NULL,
    name nvarchar(50) NOT NULL,
    phone nvarchar(20) NULL,
    CONSTRAINT PK_users PRIMARY KEY (id)
)

2.2 添加列

如果需要在已有的表中添加列，我们可以用 ALTER TABLE 命令来实现。语法格式如下：


ALTER TABLE table_name
ADD column_name datatype [ NULL | NOT NULL ]

以下是一个为表 users 添加列的例子：


ALTER TABLE users
ADD email nvarchar(50) NOT NULL

3. 建约束

约束是用于保证数据完整性和一致性的规则，可以用在表上的列或整个表上。在 sql server 中，有五种不同类型的约束：PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT 和 UNIQUE。

3.1 PRIMARY KEY 约束

PRIMARY KEY 约束用于定义列或列组合作为主键，用来标识表中的每一行。该约束保证了数据的唯一性和可排序性，而且一些操作（如 JOIN）需要适当的主键来执行。

我们可以在表创建时指定 PRIMARY KEY 约束，语法格式如下：


CREATE TABLE table_name
(
    column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...)
)

以下是一个为列 id 添加 PRIMARY KEY 约束的例子：


CREATE TABLE users
(
    id int NOT NULL,
    name nvarchar(50) NOT NULL,
    phone nvarchar(20) NULL,
    CONSTRAINT PK_users PRIMARY KEY (id)
)

3.2 FOREIGN KEY 约束

FOREIGN KEY 约束用于建立两个表之间的关系，保证引用表中的数据必须存在于被引用表中。该约束使用了被关联表的主键作为引用标准，可用于维护表之间的一致性和完整性。

我们可以在表创建时指定 FOREIGN KEY 约束，语法格式如下：


CREATE TABLE orders
(
    id int NOT NULL PRIMARY KEY,
    user_id int NOT NULL,
    order_date datetime NOT NULL,
    CONSTRAINT FK_orders_users FOREIGN KEY (user_id) REFERENCES users(id)
)

以上语句创建了一张名为 orders 的表，其中 user_id 建立了一个到 users 表的引用关系。并且用 CONSTRAINT FK_orders_users 命名了该约束。

3.3 CHECK 约束

CHECK 约束用于验证列中的数据是否符合指定的条件，可以是一个表达式或一个函数。只有当表达式返回 TRUE 时才会插入或更新数据。

我们可以在表创建时指定 CHECK 约束，语法格式如下：


CREATE TABLE students
(
    id int NOT NULL PRIMARY KEY,
    name nvarchar(50) NOT NULL,
    age int NOT NULL,
    gender char(1) NOT NULL,
    CONSTRAINT CK_students_age CHECK (age BETWEEN 16 AND 30),
    CONSTRAINT CK_students_gender CHECK (gender IN ('M', 'F'))
)

以上语句创建了一张名为 students 的表，其中 age 列的约束条件限定了这个值只能在 16 至 30 岁之间，而 gender 列的约束条件限定了只能是 M 或 F。

3.4 DEFAULT 约束

DEFAULT 约束是用于为列提供默认值的约束，如果插入的数据未指定该列的值，则使用该约束中指定的默认值。这个值可以是一个字面量、随时间改变的函数以及使用系统提供的函数。

我们可以在表创建时指定 DEFAULT 约束，语法格式如下：


CREATE TABLE data
(
    id int NOT NULL PRIMARY KEY,
    date date DEFAULT GETDATE(),
    amount decimal(10, 2) DEFAULT 0.00
)

以上语句创建了一张名为 data 的表，其中 date 列使用了带有默认值的系统函数 GETDATE()，amount 列使用了默认值 0.00。

3.5 UNIQUE 约束

UNIQUE 约束能确保在一列或一列组的值是唯一的，无法插入重复的值。虽然这个约束与 PRIMARY KEY 有点类似，但是它并不是一个主键，因为表中可以有多个 UNIQUE 约束，但只能有一个 PRIMARY KEY 约束。

我们可以在表创建时指定 UNIQUE 约束，语法格式如下：


CREATE TABLE products
(
    id int NOT NULL PRIMARY KEY,
    name nvarchar(50) NOT NULL,
    code nvarchar(10) NOT NULL UNIQUE
)

以上语句创建了一张名为 products 的表，其中 code 列使用了 UNIQUE 约束，确保了其值的唯一性。

总结

在 sql server 中，建库、建表、建约束是每个程序员和数据库管理员都需要掌握的基本技能。通过本文，我们可以了解到如何用代码语句来创建数据库、添加表和添加约束，以及如何通过 ALTER TABLE 来添加和更改表的结构。此外，我们还介绍了五种不同类型的约束：PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT 和 UNIQUE，理解它们的作用和使用方法，可以大大提高数据库的性能和安全性。

sql server建库、建表、建约束技巧