本篇文章讲述了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
以上我们讲述了表的创建、修改、添加约束、删除操作。其中添加约束是重点和难点。需要多加练习加以掌握。