SQLServer数据库学习之表的操作

本篇文章讲述了SQLServer数据库中的表的概念，重点是表的操作，增删改查，通过学习我们可以熟练掌握表的基本操作。
文章内容：属于基础、入门级水平，适合有一定数据库理论基础的新手学习。

0x01 表的基本概念

在数据库中，表是由数据库按一定的顺序和格式构成的数据集合，是数据库的主要对象。每一行代表一条记录，每一列代表记录的一个字段（也叫属性），这个表很像excel中的表格

在关系型数据库中每一个关系都体现为一张二维表。

1.1 设计表

对于具体的某一个表，在创建之前，需要确定表的下列特征：

• 表中的属性有哪些？
• 属性的域，也就是属性的数据类型和长度。
• 属性的完整性约束设置（是否为空值(null or not null)，是否唯一(unique)…）
• 那个是主键，哪些是外键。

0x02 数据类型

整型（没有小数位）

名称	描述
bigint(大整型)：	是整数类型中存储容量最大的一种，长度是8次节，可以存储正负数，范围是-2的63次方到2的63次方减1
Int	最常用的数据类型，长度4字节，可以存储正负数，范围是-2的31次方到2的31次方减1
smallint(小整型)	长度2字节，可以存储正负数，范围是-2的15次方到2的15次方减1
tinyint(微整型)	长度1字节，范围：0-255范围内所有数据
bit	是位类型，长度为1字节。其数据类型有两种类型取值：0和1。再输入0以外的其他值时系统均把它看做1
decimal[(p[,s])]or numeric[(p[,s])]	两者类型完全相同，提供了小数所需的实际存储空间，但也有限制。p确定了精确的总为数，s确定了小数位。p默认设置为18。s的默认值为0
money	存储的货币值由8个字节组成，前四字节代表整数部分，后4字节代表小数部分
Smallmoney	与money数据类型相似，但范围比money小，由四字节组成

近似数字（浮点型）

名称	描述
float	长度8个字节，最大可以有15位精确数
real	长度为四字节，最大可以有7为精确数

日期和时间

名称	描述
Datetime	长度8个字节，日期范围：1753.1.1-9999.12.31
Smalldatetime	长度四个字节，日期范围：1900.1.1-2079.12.31

字符串

名称	占用字节数	n的范围	描述
char(n)	每个字符和符号占用1字节	1-8000	n值默认为1，如果输入数据的长度小于n，则系统自动在其后添加空格来填充，如果数据过长，则会截断其超出的部分，如果定义一个char类型，且允许该属性为空，则该字段被当做varchar来处理
varchar(n)	每个字符和符号占用1字节	1-8000	具有变动长度的特性。varchar的数据长度为实际数值长度,此类型可以节省空间
nchar(n)	每个字符和符号占用2字节	1-4000	和char类似，存储unicode字符串，不容易出现编码冲突
nvarchar(n)	每个字符和符号占用2字节	1-4000	和varchar类似，存储unicode字符串，不容易出现编码冲突

虽然nvarchar存储字符比varchar()大一倍，但是存储代价已经很低廉，考虑兼容性，我们可以选择nvarchar

二进制字符串

名称	描述
Binary(n)	数据存储长度是固定的，为n+4个字节。当输入的二进制数据长度小于n时，余下部分填充0，n值最大为8000，常用于存储图像等数据。如果定义了一个binary类型，且允许该列为空，则该字段被当做varbinary来处理
varbinary(n)	数据存储长度是变化的，为实际所输入数据长度加上4字节
Image	用于存储照片或图画等

个人建议使用varbinary，节省空间，Image用的不多，了解即可。

0x03 创建表

使用create table 语句来创建表，语法如下：

create table [database_name.[schema_name]].table_name
{
column_name column_property column_constraint,
......
}

练习：创建一个学生表,

-- 选择哪个数据库:use 数据库名
use tsgl
--创建学生表(学号，姓名，年龄，性别)
create table student
(
sno varchar(20) not null,
name varchar(10) not null,
age int,
gender varchar(5),
)

在创建表之前，需要指定一个数据库，指定数据库可以用sql语句，也可以用过managementstudio来做

执行完后，我们可以在tsgl库中找到该表（要刷新一下数据库，才能看到）

使用managementstudio来创建表，比较简单，这里就不写了。

0x04 修改表

创建表后，可以更改最初创建表时定义的许多选项。这些选项包括：

• 添加、修改、或删除列
• 可以添加或删除约束

修改表的语法：

1.添加(列)属性
	alter table 表名
	add 属性 数据类型[长度] [列级完整性约束]
2.修改数据类型
	alter table 表名
	alter column 属性名 数据类型
3.删除(列)属性
	alter table
	drop column 属性名
4.修改列名
	exec sp_rename '表名.列名','新列名'
5.修改表名
	exec sp_rename '原表名','新表名'

练习：

--在student表中添加sno和age属性
alter table student 
add sno varchar(15)  not null

alter table student
add age int

--修改name属性的数据类型为varchar(20)
alter table student
alter column name varchar(20)

--删除student.age属性
alter table student
drop column age 

-- 修改列名 name-->sname
exec sp_rename 'student.name','sname'

-- 修改表名 student-->stu
exec sp_rename 'student','stu'

0x05 创建和删除约束

1.主键约束：primary key
唯一确定表中每一条记录的表示符
2.外键约束：foreign key
用于建立和加强两个表数据之间的连接
3.唯一约束：unique
指定一个列或多个列值具有唯一性
4.检查约束：check
设置检查条件，以限制输入值
5.默认约束：default
插入操作中没有提供输入值时系统会自动加上指定值

语法格式如下：

-- 创建约束
alter table 表名
add constraint 约束名 约束类型 具体约束说明
--删除约束
 alter table 表名
 drop constraint 约束名

5.1 创建约束

--选择数据库
use tsgl

--对stu表中的sno添加主键约束primary key
--注意为空的列不能创建主键约束
alter table stu
add constraint PK primary key (sno)

--给姓名添加一个唯一约束：unique
alter table stu
add constraint UN_sname unique (sname)

--添加检查约束，给年龄添加约束：年龄大于18且小于100 check
alter table stu
add constraint CK_age check (age>=18 and age<=100) 

--添加默认约束；给姓名添加默认约束 为女
alter table stu
add constraint DF_gender default '男' for gender  

再创建一个course(sno,cno,grade)，将course表的sno属性与student表建立外键约束，

那么何为外键呢？举例来说，course表中sno并非course表的主键，但是sno是另一个表的主键(student中的主键)，我们就称sno是course表的外键，也称外码。

代码如下：

--创建课程表
create table course
(
sno varchar(15) not null,
cno varchar(10),
grade float
)

--对course表中的sno属性建立外键约束
alter table course
add constraint FK_sno foreign key (sno)
references stu(sno)

我们在建立外键约束的时，要建立的属性数据类型及长度在两个表中必须一致。
要引用的属性必须是主键约束。两个表的列名尽量保持一致。

5.2 删除约束

我们在删除一个约束时，如果该属性被其他表引用，我们必须先解除引用，然后才能删除该约束

--删除学生表gender默认约束
alter table stu
drop constraint DF_gender

--在删除主键约束时，如果该属性被其他表引用，则删除失败，必须将引用删除后才能删除主键约束
--先删除course表中的外键约束
alter table course
drop constraint FK_sno
--然后再删除stu中的主键约束
alter table stu
drop constraint PK 

5.3 创建表的同时创建约束

题目描述：在tsgl数据库中，分别使用命令创建图书表ts、读者表dz、借阅表jy这三个表的结构及完整性约束如下（要求建立所有的约束）：
1）ts（书号，书名，出版社，类别，作者，出版时间，价格）；其中，书号为主键，价格为数值型；出版时间为日期型，其他属性都是字符型，长度自己设定；要求价格为正。
2）dz（编号，姓名，单位，性别，电话）；编号为主键，所有属性都是字符型，长度自己设定；限制性别取值为男、女，单位缺省值为阳光图书馆
3）jy（书号，编号，借阅日期，还书日期还书标识）；书号和编号为主键，同时分别是ts和dz的外键，借阅日期和还书日期为日期型，还书标识为长度2的字符型。

代码如下：

/*
ts（书号，书名，出版社，类别，作者，出版时间，价格）；
其中，书号为主键，价格为数值型；
出版时间为日期型，其他属性都是字符型，长度自己设定；
要求价格为正。
*/

use tsgl
create table ts
(
书号 varchar(20) primary key, --主键约束的写法
书名 varchar(20),
出版社 varchar(20),
类别 varchar(20),
作者 varchar(20),
出版时间 datetime,
价格 int check (价格>0) --限制约束的写法
)

/*
dz（编号，姓名，单位，性别，电话）；
编号为主键，所有属性都是字符型，长度自己设定；
限制性别取值为男、女，单位缺省值为阳光图书馆
*/

use tsgl
create table dz
(
编号 varchar(20) primary key,
姓名 varchar(20),
性别 varchar(2) check (性别='男' or 性别='女'),
单位 varchar(20) default '阳光图书馆',--默认约束的写法
电话 varchar(12) 
)
/*
jy（书号，编号，借阅日期，还书日期还书标识）；
书号和编号为主键，同时分别是ts和dz的外键
借阅日期和还书日期为日期型
还书标识为长度2的字符型。
*/
use tsgl
create table jy
(
书号 varchar(20) not null foreign key references ts(书号), --外键约束的声明
编号 varchar(20) not null foreign key references dz(编号),
借阅日期 datetime,
还书日期 datetime,
还书标识 varchar(2)
primary key(书号,编号), --联合主键的声明方式,写在所有属性的声明最后,这里也叫表级完整性约束
)

0x06 删除表

使用drop table 语句删除表
语法：drop table 表名

代码如下：

--删除ts,dz,jy表
use tsgl
drop table ts
drop table dz
drop table jy

以上我们讲述了表的创建、修改、添加约束、删除操作。其中添加约束是重点和难点。需要多加练习加以掌握。