壹·建库建表 - 《SQL Server · 语记》

一、检查数据库名是否存在

如果需要创建数据库，可能会出现数据库名字重名的现象，我们可以使用如下代码查询数据库名是否存在，存在则删除此数据库。

--删除数据库
if exists(select * from sys.databases where name = 'DBTEST')
    drop database DBTEST

此代码检查数据库中是否存在”DBTEST”数据库，如果存在则删除此数据库，此处理方式最好只在学习阶段使用，在正式生产环境中慎用，操作不当可能会删除重要数据。

二、创建数据库

--创建数据库
create database DBTEST
on  --数据文件
(
    name = 'DBTEST',   --逻辑名称
    filename = 'D:\Data\DBTEST.mdf',  --物理路径及名称
    size = 5MB,  --数据文件初始大小
    filegrowth = 2MB --数据文件增长速度,也可以使用百分比来设置
)
log on  --日志文件
(
    name = 'DBTEST_log', --逻辑名称
    filename = 'D:\Data\DBTEST_log.ldf', --物理路径及名称
    size = 5MB, --日志文件初始大小
    filegrowth = 2MB --日志文件增长速度,也可以使用百分比来设置
)

以上代码创建”DBTEST”数据库，并且分别使用on和log on规定了数据文件和日志文件的信息。

创建数据库也可以按照如下简单语法来创建：

create database DBTEST

如果按照上述方式创建数据库，数据库的数据文件和日志文件的相关信息，全部采取默认值。

三、建表

使用数据库和删除数据表：

use DBTEST  --切换当前数据库为DBTEST
--删除表(先判断表在当前数据库是否存在,存在则删除,其中type='U'判断对象类型为用户定义表类型)
if exists(select * from sys.objects where name = 'Department' and type = 'U')
    drop table Department

创建数据表语法：

create table 表名
(
    字段名1 数据类型(长度),
    字段名2 数据类型(长度)
)

其中数据类型，我们在后面用到什么类型，在介绍什么类型，有的类型可以不填写长度。

创建数据表示例（部门表，职级表，员工信息表）：

--创建部门表
create table Department -
(
    --创建部门编号;int代表整数类型;primary key代表主键;identity(1,1)代表从1开始步长为1自增长;
    DepartmentId int primary key identity(1,1),
    --创建部门名称;nvarchar(50)代表长度50的字符串;not null代表不能为空;
    DepartmentName nvarchar(50) not null,
    --创建部门描述；text代表长文本;
    DepartmentRemark text
)

字符串类型比较：

char：定长，例如 char(10)，不论你存储的数据是否达到了10个字节，都要占去10个字节的空间。

varchar：变长，例如varchar(10)，并不代表一定占用10个字节，而代表最多占用10个字节。最大长度8000，也可以使用varchar(max)表示2G以内的数据，但存储机制会和text一样，效率会降低。

text：长文本，最大长度为2^31-1(2,147,483,647)个字符。

nchar,nvarchar,ntext：名字前面多了一个n，它表示存储的是Unicode数据类型的字符，区别varchar(100)可以存储100个英文字母或者50个汉字，而nvarchar(100)可以存储100个英文字母，也可以存储100个汉字。

--创建职级表，rank为系统关键字，此处使用[]代表自定义名字，而非系统关键字
create table [Rank]
(
    RankId int primary key identity(1,1),
    RankName nvarchar(50) not null,
    RankRemark text
)

--创建员工信息表
create table People
(
    PeopleId int primary key identity(1,1),
    --references代表外键引用,此字段必须符合与其它表的外键约束
    DepartmentId int references Department(DepartmentId)  not null,
    RankId int references [Rank](RankId) not null,
    PeopleName nvarchar(50) not null,
    --default代表字段默认值; check可以规定字段值的约束条件;
    PeopleSex nvarchar(1) default('男') check(PeopleSex='男' or PeopleSex='女') not null,
    PeopleBirth datetime not null,
    PeopleSalary decimal(12,2) check(PeopleSalary>= 1000 and PeopleSalary <= 100000) not null,
    --unique代表唯一约束，为数据提供唯一性保证;
    PeoplePhone nvarchar(20) unique not null,
    PeopleAddress nvarchar(100),
    --datetime和smalldatetime都可以表示时间类型，getdate()用于获取系统当前时间
    PeopleAddTime smalldatetime default(getdate())
)

四、修改表结构

（1）如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name
ADD column_name datatype

例如该员工表添加一列员工邮箱：

alter table People
add PeopleMail nvarchar(100)

（2）如需在表中删除列，请使用下面的语法:

ALTER TABLE table_name
DROP COLUMN column_name

例如删除员工表中的邮箱这一列

alter table People
drop column PeopleMail

（3）如需改变表中列的数据类型，请使用下列语法：

ALTER TABLE table_name
ALTER COLUMN column_name datatype

例如需要改变邮箱列的数据类型为varchar(100)

alter table People
alter column PeopleMail varchar(100)

五、删除添加约束

删除约束语法：

if exists(select * from sysobjects where name=约束名)
alter table 表名 drop constraint 约束名;
go

添加约束语法：

--添加主键约束
alter table 表名 add constraint 约束名称 primary key(列名)
--添加check约束
alter table 表名 add constraint 约束名称 check(条件表达式)
--添加unique约束
alter table 表名 add constraint 约束名称 unique(列名)
--添加default约束
alter table 表名 add constraint 约束名称 default 默认值 for 列名
--添加外键约束
alter table 表名 add constraint 约束名称 foreign key (列名) references 关联表名(关联表列名)