第三单元 创建和管理数据表

3.1数据表的设计

数据表设计的要点包括三个方面：

1.确定表名和属性名

最常用的方法：

(1)帕斯卡(Pascal Case)命名方法，要求名称使用大小写混合的单词，将每个单词的首字母大写，然后把它们连接在一起，中间不使用分隔符，如：TableName.

(2)驼峰法，这种方法要求，名称中除了第一个单词以外的其它单词的首字母都是大写，其他字符都是小写。如：tableName.

命名时还要注意：

命名的单词一般采用英文单词或英文单词的缩写，应尽量避免使用拼音命名，英文单词来自于具体业务定义，要尽量表达清楚；不能使用汉字，因为汉字会导致较多的程序错误。

 

对学生选课管理系统SCC中的二维表和属性进行命名：

院部(院部编号，院部名称)

Department(Dno,Dname)

班级(班级编号，班级名称，专业，入学年份，院部编号)

Class(ClassNo,ClassName,Specialty,EnterYear,Dno)

学生(学号，姓名，性别，生日，班级编号)

Student(Sno,Same,Sex,Birth,ClassNo)

课程(课程编号，课程名称，任课教师，学分，限报人数，学时)

Course(Cno,Cname,Teacher,Credit,LimitNum,CourseHour)

成绩(学号，课程编号，平时成绩，期末成绩)

Score(Sno,Cno,Uscore,EndScore)

2.选取字段的数据类型

数据类型的作用：数据类型决定了数据存储的空间和格式，有助于正确、有效地存储数据。

数据类型的分类：

数值型：数值型数据包括整型、定点小数型和浮点型。

字符型 ：字符型编码方式有两种，普通字符编码和统一字符编码。

普通字符编码是指不同国家或地区的编码长度不同，例如英文字母的编码为1个字节(8位)，中文汉字的编码是2个字节(16位)。

统一字符编码，是指世界上所有的字符统一进行编码。不管对哪个国家、哪种语言都采用双字节(16位)编码。

需要注意使用char,nchar,varchar,nvarchar这四种数据类型时要在后面添加小括号，标明能存放的最大字符数。

货币型

日期时间型

3.设置数据完整性约束

 

 

3.2数据表的创建

数据表的创建有两种方法：

1.使用图形化界面SSMS创建数据表

实例1：使用图形化界面完成对SCC数据库中Student表的创建

SCC------表-------右键新建表------保存

2.使用SQL语句创建数据表

标识列

标识列能够自动为表生成行号，行号是按照指定的标识增量和标识种子排序。

Create TABLE 表名
(列名1 列属性,
列名2 列属性,
......,
列名n 列属性
)
#语法格式中的列属性，包括字段数据类型、长度、是否允许为空、字段默认值、是否为标识列等。

Create TABLE 表名
(列名1 列属性,
列名2 列属性,
......,
列名n 列属性
)
#语法格式中的列属性，包括字段数据类型、长度、是否允许为空、字段默认值、是否为标识列等。

CREATE TABLE Student
(ID int NOT NULL IDENTITY(1,1),  #使用IDENTITY(标识种子=1，标识增量=1)
Sno nvarchar(15) NOT NULL,
Sname nvarchar(10) NOT NULL,
Sex nchar(1) NOT NULL,
Birth date,
ClassNo nvarchar(10) NOT NULL)

书写SQL语句时，注意2个问题：

1.标点符号必须使用英文标点符号，

2.在执行语句前，先要检查当前数据库是否为SCC，否则会造成将当前表创建到其他数据库中。

在执行按键左侧可以选择可用数据库

3.3数据完整性约束(一)

由于数据库的数据是从外界输入的，然而数据的输入由于种种原因，会发生输入无效或错误信息，数据的完整性正是为了保证输入的数据符合规定而提出的。

数据完整性分为四类：

实体完整性

域完整性

参照完整性

用户自定义完整性

1.实体完整性：实体完整性要求如果属性A是关系R的主属性，则属性A不能取空值。实体完整性用于保护关系数据库表中的每条记录都是唯一的，建立主键的目的就是为了实现实体完整性。

2.域完整性：用来保证数据的有效性，它可以限制录入的数据与数据类型是否一致，规定字段的默认值，设置字段是否可以为空，域完整性可以确保不会输入无效的数据。

3.参照完整性：参照完整性是基于外键的，如果表中存在外键，则外键的值必须与主表中的某条记录的被参照列的值相同，参照完整性用于确保相关联表之间的数据保持一致。当添加、删除或修改数据表中记录时，可以借助于参照完整性来保证相关表之间数据的一致性。

4.用户自定义完整性：用户自定义完整性约束就是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

 

如何实现数据完整性，可以通过表的字段设置约束来保证表中数据完整性。

约束包括六种：

主键约束(PRIMARY KEY)

非空约束(NOT NULL)

检查约束(CHECK)

唯一约束(UNIQUE)

外键约束(FOREIGN KEY)

默认值约束(DEFAULT)

 

1.主键约束(PRIMARY KEY)用于满足实体完整性，要求主键列数据唯一，并且不允许为空，主键约束的命名推荐使用"PK_表名"的命名格式，方便后续使用时进行识别。

主键约束的设置可以通过图形化界面和SQL语句两种方式：

(1).使用SQL语句设置主键

实例1：设置Student学生表的主键。

Student表中包括Sno,Sname,Sex,Birth,ClassNo 5个字段，Sno学号是这张表的唯一标识，把它设置为主键。

方法一：在创建表时设置主键，使用列级约束。

CREATE TABLE Student
(Sno nvarchar(15) PRIMARY KEY,
Sname nvarchar(10),
Sex nchar(1),
Birth date,
ClassNo nvarchar(10))

方法二：在创建表时设置主键，使用表级约束。

CREATE TABLE Score
(Sno nvarchar(15),
Cno nvarchar(10),
Uscore numeric(4,1),
EndScore numeric(4,1),
constraint PK_Score Primary Key(Sno,Cno)
)

方法三：在修改表时，设置主键。

ALTER TABLE Student
ADD CONSTRAINT PK_Student
PRIMARY KEY(Sno)   
#这种方法前提是表已经创建完成了

实例2：设置Score成绩表的主键。

分析：

Score成绩表包含的字段有4个：Sno学号，Cno课程编号，Uscore平时成绩和EndScore期末成绩，这张表存储了学生选课的数据信息，将学号和课程编号这两个字段组合作为Score成绩表的主键。

方法一：在创建表时设置主键，使用列级约束。

CREATE TABLE Score
(Sno nvarchar(15),
Cno nvarchar(10) Primary Key(Sno,cno),
Uscore numeric(4,1),
EndScore numeric(4,1)
)

方法二：在创建表时设置主键，使用表级约束。

CREATE TABLE Score
(Sno nvarchar(15),
Cno nvarchar(10),
Uscore numeric(4,1),
EndScore numeric(4,1),
constraint Pk_Score Primary Key(Sno,Cno))

方法三：在修改表时，设置主键

QLTER TABLE Score
ADD CONDTRAINT PK_Score PRIMARY KEY(Sno,Cno)

(2)通过图形化界面设置主键

注意：主键值不能为空。

在对象资源管理器中选中表格右键------设计---------选中某一列名-------设置主键--------保存

需要设置多个主键时，按住Ctrl键多个选择即可。

 

 

删除主键约束

1.通过SQL语句删除student学生表中的主键约束PK_student.

Alter Table Student
Drop Constraint PK_student

2.使用图形化界面删除主键约束

需要在SSMS的对象资源管理器中，找到student表中的主键约束PK_student，右击删除即可。

3.4数据完整性约束(二)

外键约束

外键约束用于实现表和表之间的参照完整性，外键约束的核心是给表创建外键。外键的作用是关联两张二维表，使二维表所描述的实体建立联系。

外键可以理解为两张二维表含义上的公共字段，这个字段的字段名在两张表中可以不同，但是要注意，公共字段在两张表中的数据类型一定是相同的。

外键约束在创建时的命名规则为，FK外键表名主键表名。

 

1.使用SQL语句创建外键约束

实例1：在SCC中创建外键约束，关联Student学生表和Class班级表，实现两张表的参照完整性约束。

方法1：在创建表时创建外键约束，使用列级约束

CREATE TABLE Student
(Sno nvarchar(15) PRIMARY KEY,
Sname nvarchar(10),
Sex nchar(1),
Birth date,
ClassNo nvarchar(10) FOREIGN
KEY REFERENCES Class(ClassNo))
#语法格式 ：FOREIGN KEY REFERENCES
          主键表名(主键字段名)

方法二：在创建表时创建外键约束，使用表级约束

语法格式：

CONSTRAINT 外键约束名
FOREIGN KEY (外键字段名)
REFERENCES 主键表名(主键字段名)

CREATE TABLE Student
(Sno nvarchar(15) PRIMARY KEY,
Sname nvarchar(10),
Sex nchar(1),
Birth date,
ClassNo nvarchar(10),
CONSTRAINT FK_Student_Class FOREIGN
KEY(ClassNo)REFERENCES Class(ClassNo))

方法三：在修改表时，设置外键约束

ALTER TABLE Student
ADD CONSTRAINT FK_Student_Class
FOREIGN KEY(ClassNo) REFERENCES
Class(ClassNo)

2.使用图形化界面创建外键约束

对象资源管理器----单击dbo.Student中键-----右击----新建外键

----表和列规范的三个点----修改主键表(关系名(外键名)会自动生成)---保存

 

建立了外键约束以后，对外键表和主键表的操纵会产生约束：

1.对外键表操作的约束：当向外键表插入数据时，外键的值必须是主键表的主键值之一，更新外键表的外键值时，同样必须满足这个条件。

2.对主键表更新操作的约束：当有级联更新时，可以更新主键表主键的值，同时外键表中所有关联的记录的外键值会被全部更新。

3.对主键表删除操作的约束：当有删除更新时，可以删除主键表主键的值，同时外键表中所有关联的记录同时被全部删除。

 

删除外键约束

实例2：使用SQL语句在Student表中删除外键约束FK_Student_Class.

1.使用SQL语句

Alter Table Student
Drop Constraint FK_Student_Class

2.使用图形化界面

 

非空约束

非空约束用来指定某个字段的值是否允许取空值

注意：取空值和值为0或值为空字符串是不同的。

(Sno nvarchar(15) NOT NULL PRIMARY KEY,
Sname nvarchar(10) NOT NULL,
Sex nchar(1) NOT NULL,
Birth date,  #为标识默认为NULL
ClassNo nvarchar(10) NOT NULL FOREIGN
KEY REFERENCES Class(ClassNo))

3.5数据完整性约束(三)

检查约束

检查约束是用来限制列数据的有效范围。当对约束的列数据进行添加或修改时，SQL Server会自动检查列数据的有效性，从而保证数据库中数据的用户自定义完整性。

在设计检查约束时，可以使用逻辑表达式表示数据的有效性范围，检查约束的命名规则是CK_字段名。

 

实例1：为Student学生表中的Sex性别字段创建检查约束，使得性别字段的数据值只能是"男"或者"女".

 

方法一：在创建表时设置检查约束，使用列级约束进行设置。

CREAT TABLE Student
(Sno nvarchar(15) NOT NULL PRIMARY KEY,
Sname nvarchar(10) NOT NULL,
Sex nchar(1) NOT NULL check (Sex='男' or Sex='女'),  #字段名不能省略
Birth date,
ClassNo nvarchar(10) NOT NULL FOREIGN KEY REFERENCES
Class(ClassNo))
#语法格式：check(逻辑表达式)

方法二：在创建表时设置检查约束，使用表级约束。

CREATE TABLE Student
(Sno nvarchar(15) NOT NULL PRIMARY KEY,
Sname nvarchar(10) NOT NULL,
Sex nchar(1) NOT NULL,
Birth date,
ClassNo nvarchar(10) NOT NULL FOREIGN KEY REFERENCES
Class(ClassNo)
Constraint CK_Sex check (Sex='男' or Sex='女'))

方法三：在修改表时设置检查约束。

ALTER TABLE Student
ADD CONSTRAINT CK_Sex check
(Sex='男' or Sex='女')

 

使用图形化界面创建检查约束

右击表------设计-------右击Sex列名----CHECK 约束----添加----常规表达式三个点-----填写Sex='男' or Sex='女'------确定-----修改名称CK_Sex-----关闭-------保存

 

唯一约束

唯一约束应用于表中的非主键列，用于指定一个或多个字段的组合的值具有唯一性，以防止在字段中输入重复的值。

 

 

唯一约束与主键约束的区别：

1.一张表可以设置多个唯一约束，但是主键约束在一个表中只能有一个，因为它是表中字段的唯一标识。

2.设置了唯一约束的列值必须唯一，但允许有一个空值NULL。而设置了主键约束的列值必须唯一，而且不允许为空。唯一约束的命名规则为UQ_字段名。

 

实例2：将Department院部表的Dname院部名称字段，设置为唯一约束，不容许该字段出现重复的数据值。

方法一：在创建表时设置唯一约束，使用列级约束进行设置。

CREATE TABLE Department
(Dno nvarchar(10) NOT NULL PRIMARY KEY,
Dname nvarchar(30) NOT NULL UNIQUE)

方法二：在创建表时设置唯一约束，使用表级约束。

CREATE TABLE Department
(Dno nvarchar(10) NOT NULL PRIMARY KEY,
Dname nvarchar(30) NOT NULL,
CONSTRAINT UQ_Dname UNIQUE(Dname))

方法三：在修改表时设置唯一约束。

ALTER TABLE Department
ADD CONSTRAINT UQ_Dname UNIQUE(Dname)

 

默认值约束

默认值约束是指在表记录添加后用户没有输入某个字段值时，该字段值是由系统自动提供。

实例3：将Course课程表中的Teacher任课教师字段，设置默认值约束为"待定"。

方法一：在创建表时设置默认值约束，注意默认约束只能用列级约束来定义。

CREATE TABLE Course
(Cno nvarchar(10) NOT NULL,
Cname nvarchar(30) NOT NULL,
Teacher nvachar(10) NOT NULL default '待定',
Credit numeric(4,1) NOT NULL,
LimitNum int NOT NULL check(LimitNum>0),
CourseHour int)

方法二：在修改表时设置默认值约束。

ALTER TABLE Course
ALTER COLUMN Teacher SET DEFAULT '待定'

3.6数据表的修改

有时候，我们希望对表的某些信息进行修改，这时就需要修改数据表。

所谓修改数据表，指的是修改数据库中已经存在的数据表结构。

我们如果需要修改一张表，首先想到的就是修改表的名称、修改表的列名、表中列的数据类型，删除字段以及添加字段。

 

使用SQL语句修改表结构

1.修改表名

数据库中，不同的数据表是通过表名来区分的。

更改数据表的名称时，通常要用到sp_name存储过程，具体语法为：

EXECUTE sp_rename 旧表名,新表名

2.修改列名

EXECute sp_rename '表名.旧表名','新列名'[,'COLUMN'];

3.修改列的数据类型

ALTER TABLE 表名 ALTER column 列名 数据类型
#表名：修改列所在表的名称
#列名：要修改的列
#数据类型：修改后列的数据类型

alter table stu alter column username varchar(20)

4.添加列

ALTER TABLE 表名 ADD 新列名 列数据类型

alter table stu add gender char(5)

5.删除列

ALTER TABLE 表名 DROP column 列名;

alter table stu drop column gender

注意：

1.在ALTER TABLE语句中，一次只能包含ALTER Column、ADD、drop字句中的一条。

2.使用ALTER Column字句，一次只能修改一个列的属性，因此，如果要修改表的多个列的属性的话，需要些多条ALTER TABLE语句。

 

使用SSMS修改表结构

1.修改表名

对象资源管理器-----右击要修改的表名------选择重命名进行表名修改

2.添加列

对象资源管理器-----右击要修改的表名-----设计打开表设计窗口-----在最后一行直接插入列或者右击并执行插入列操作

3.删除列

对象资源管理器-----右击要修改的表名-----设计打开表设计窗口-----选择要删除的列右击并执行删除列操作。

4.修改列名、列数据类型

对象资源管理器-----右击要修改的表名-----设计打开表设计窗口-----选择要修改的列名或者数据类型单击进行编辑，编辑完成后保存即可。

注意:

1.数据表的修改

如果与数据库中的其他表有依赖关系，则需要先解除依赖关系，在进行表名修改操作。

2.在修改列的数据类型

如果被修改的列中存在值，要谨慎操作。

3.7表数据的增删改

添加数据

添加数据是指，向数据库表中插入新的记录，这些数据可以从其他来源得到，需要被转存或引入表中；也可能是新数据要被添加到新创建的表中或已存在的表中。

(1)为表中所有列添加数据

INSERT语句中指定所有字段名

INSERT INTO 表名(列名1，列名2) #数据表中的列名称，此处必须列出所有列的名称

VALUES(值1，值2,......); #表示每个列的值，每个值的顺序、类型必须与对应的字段相匹配。

insert into student (Sno,Sname,Sex,Birth,ClassNo) values (1,'张三','男','2000-01-01',3)
select * from student

 

INSERT语句中指定字段名

INSERT INTO 表名 VALUES(值1，值2,......)；#值用于指定要添加的数据，需要注意的是，由于INSERT语句中没有指定字段名，添加的值的顺序必须和字段在表中定义的顺序相同。

(2)向表中指定列添加数据

也可以在INSERT语句中只向部分列中添加值。

INSERT INTO 表名(字段1，字段2，...) #字段表示数据表中的列名称，此次只指定表中部分列的名称。

VALUES(值 1，值2，...) #值表示指定列的值，每个值的顺序、类型必须与对应的列相匹配。

insert into student (Sno,Sname) values (2,'李四','女')

(3)向表中同时添加多条数据

有时，需要一次向表中添加多条记录。INSERT语句具有同时添加多条记录的功能。

INSERT INTO 表名[(字段名1,字段名2，......)]

VALUES(值1，值2,......),(值1，值2，......),

......

(值1，值2,......);

insert into student (Sno,Sname,Sex,Birth,ClassNo) values (2,'李四','男','2000-01-01',2)，(3,'王五','男','2000-01-01',5)
select * from student;

更新数据

指对表中存在的记录进行修改，比如某个学生改了名字，就需要对其记录信息中的name字段值进行修改。使用UPDATE语句来更改表中的记录：

UPDATE 表名

SET 字段名1 = 值1[，字段名2=值2，......]

[WHERE 条件表达式] #指定更新数据需要满足的条件

注意：

更新表中的某一条或者某几条记录，需要使用WHERE字句来指定更新记录的条件。如果没有使用WHERE字句，则会将表中所有记录的指定字段都进行更新。

update student set Sname='赵四' where Sno=2

 

删除数据

假如一个同学转学了，就需要在student表中将其信息记录删除。使用DELETE语句来删除表中的记录。

UPDATE FROM 表名 [WHERE 条件表达式]

注意：

根据指定条件删除表中的某一条或者某几条记录，需要使用WHERE字句来指定删除记录的条件。在DELETE语句中如果没有使用WHERE字句，则会将表中的所有记录都删除。

delete from student where name='王五'

还有一种方式可以用来删除表中所有的记录，这种方式需要用到一个关键字TRUNCATE

TRUNCATE [TABLE] 表名

 

使用SSMS进行数据增删改

对象资源管理器-----右击要修改的表名-----编辑前200行------进行增删改操作-------保存