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-8000n值默认为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

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

  • 3
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值