SQL server学习03-创建和管理数据表

目录

 一，SQL server的数据类型

1，基本数据类型

2，自定义数据类型

 二，使用T-SQL创建表

1，数据完整性的分类

2，约束的类型

3，创建表时创建约束

4，任务

5，由任务编写的SQL语句 

三，使用T-SQL管理表 

1，创表时添加约束名 

1）添加约束名 

2） 修改由任务编写的SQL语句 （添加约束名）

2，使用ALTER TABLE修改表

1）从表中删除列

2）向表中添加列 

3）修改表中列的长度

4）对指定表添加默认约束 

 3，使用DROP TABLE删除表

---

通过前一篇SQL server学习02-使用T-SQL创建数据库-CSDN博客的学习

我已经创建好了数据库，接下来开始使用创好的库创表。首先了解表的概念：

1. 表是数据关系模型中表示实体的方式 
2. 表是数据库中用来组织和存储数据，具有行列结构的数据库对象，数据库中的数据都存储在表中。
3. 表由行和列组成。
   
   值得注意的是，一张表就是一个关系。 表有行和列，关系有记录和字段。表有表名，每个关系都有一个关系名。
   
   行称为记录，是组织数据的单位，每行都是一条独立的数据记录。
   列称为字段，主要描述数据的属性。

如下就是常见的一张excel二维表。

表分为普通表和系统表。

1. 普通表由用户创建：用户定义的表也称为标准表，用来存储数据库应用系统中的数据。
2. 系统表由系统创建：存储了有关数据库服务器的配置，数据库设置，用户和数据库对象的描述等系统信息，用户不能创建。

在同一个数据库中，表名不能重复，在同一张表中列名不允许相同，但在不同的表中列名可以相同。

【ps：同一张表列名重复无意义。由于不同表之间可能存在联系，因此会有相同列名。】

 一，SQL server的数据类型

 在创建表之前，需要考虑要创建的表包含哪些内容，例如一张表包含

1. 哪些列
2. 每列都是什么数据类型

可以看到，在创表时，数据类型很关键。SQL server提供了基本数据类型和自定义数据类型。

先来学习基本数据类型（也称系统数据类型）。

1，基本数据类型

基本数据类型是系统本身就自带的，可以直接拿来用，如下👇 

2，自定义数据类型

自定义数据类型不是系统自带的基本数据类型，但用户需要以SQL server系统数据类型为基础创建。

当多张表中的列要存储相同类型的数据时，往往要确保这些列具有完全相同的数据类型，长度和为空性（数据的值是否允许为空） ，可以通过用户自定义数据类型来实现。例如：

1. 电话的长度都为11，非空，且唯一。
   
   如果某个系统要求电话这一列的长度为11个字符，那么有效的电话内容可以是“12345678912”或“98765432121”，但“123”或“123456”就不符合这个要求。
    
2. 身份证的长度为18，非空，且唯一。

下面通过SSMS创建一个名为char30的自定义数据类型，要求基于char系统数据类型，大小为30个字符。

 鼠标右击“用户定义数据类型”子节点，可以看到之前的自定义数据类型char30已被排列在“用户自定义数据类型”列表中：

 二，使用T-SQL创建表

不管是在MySQL中还是在SQL server中，创库创表都是一样的关键字"CREATE"，如果创库后面接DATABASE ，创表后面则接TABLE 。 

 在SQL server中使用T-SQL语句创表的语法如下：

CREATE TABLE 数据表名
(列名 数据类型[列级完整性约束条件]
[,...n]
[,表级完整性约束条件]
)

 可以看到，在上述语法总，“约束条件”出现了两次。约束时SQL server提供的自动保持数据库完整性的一种方法。之所以存在约束条件，是因为需要保持数据库的完整性（即数据完整性）。

 用户在向表格中添加数据时，有些数据的内容可能与实际情况不相符，例如：

1. 年龄：1000岁     （没有人能够活到1千岁）
2. 性别：好             （性别没有“好”，要么是“男”或“女”，要么是美国的97 种性别中的一个）
3. 身份证号：12     （身份证号必须是18位，极少数是15位）
4. 邮箱：123           （邮箱格式写错）

可以看到，这些数据都是不可靠的，为了能够保证数据的准确可靠，就需要对数据表进行数据完整性设置。

1，数据完整性的分类

1.  实体完整性（行完整性）：
   
   指表的每一行在表中是唯一的实体。一般用主键约束 PRIMARY KEY。
   
   例如，学生表中的每一行代表表中唯一的一名学生，不会有两行或两行以上的记录来表示同一个学生（太浪费空间，没必要）。
    
2. 域完整性（列完整性）：
   
   指列满足特定的数据类型和约束。可以使用检查约束或非空约束。
   
   例如，成绩表中成绩这一列的值限定在[0,100]，学生表中性别只能为“男”或“女”，年龄非空。
    
3. 参照完整性（引用完整性）：
   
   指表和表之间的字段指是有关联的（特殊情况是产生在同一张表的不同字段值之间），参照表中的外键值必须存在于被参照表中的主键值中（这句话的意思就是：你要参照我的数据，那么我的外键列在你的表中必须是主键列）。使用外键约束。
   
   例如，成绩表中的学号这一列（外键）的值必须存在于学生表中学号这一列（主键）的值中。
    
4. 用户定义的完整性：
   
   指某一具体的应用必须满足的语义要求或用户实际的业务规则。（较少使用）
    

 接下来了解SQL server中的约束类型。

2，约束的类型

分为：

1. 检查约束（CHECK）
   
   指定某列可取值的集合或范围，用于实现域完整性。一张表可以有多个检查约束。
   
   在设置检查约束时，需要加上逻辑表达式，即：CHECK(逻辑表达式)
    
2. 默认约束（DEFAULT）
   
   若在表中定义了默认值约束，用户在插入新的数据行时，如果没有为该列指定数据，那么系统会将默认值赋值给该列。
    
3. 主键约束（PRIMARY KEY）
   
   主键是表中某列或多个列的组合，可以唯一确定一条记录。可以保证实体完整性。每张表只能有一个主键，主键不能为空（即如果一个字段为主键，就必须同时设置为非空约束）。
    
4. 外键约束（FOREIGN）
   
   表与表之间是有关系的，有两张表分别为表A和表B，两张表中有相同的列m，假设列m在表A中是主键（或具有唯一约束），则列m在表B中为外键，需要使用外键约束。语法如下：
    

   --表A中
   CREATE TABLE A(
   列名m 数据类型 约束条件为主键PRIMARY KEY,
   )--表B中
   CREATE TABLE B(
   列名m 数据类型,
   FOREIGN KEY (m) REFERENCES A(m)
   )

   
   
   此时，表A称为主表（或被参考表），表B为从表或参考表。存在相互联系的两种表中，谁有主键，谁就是主表。
   
   外键表示一张表中的列于另外一张表中列的引用关系，以保证不同表中数据的一致性。用于实现参照完整性。
5. 唯一约束（UNIQUE）
   
   用于指定表中某列或多个列的组合值，具有唯一性，确保在非主键列中输入非重复值。可以保证实体的完整性。虽然这点和主键约束很像，但是使用唯一约束的字段允许为空null，并且只能有一个该字段值为空，即如果有一条记录的该字段为空，那么其他记录的该字段必须有值，并且这些值必须是唯一的。在一张表中，唯一约束可有多个，而主键约束只能有一个。
   
   例如，现在我有一张表的数据如下，sphone设置了唯一约束：
    

   sno	sphone
   1	153xxxxxxxx
   2	null
   3	189xxxxxxxx

   如果再试图插入另一条记录，如果sphone也为空，就会违反唯一约束，从而导致插入失败。

6. 非空约束（NOT NULL）
   
   指定表中的某些列必须有具体值，可以实现域完整性。
    

知道了数据完整性和约束条件这两个概念，就可以在创建表的同时创建约束，也可以在修改表时创建约束。

为了方便后期删除指定约束，有些时候在创建约束的时候可以给当前的约束指定一个约束名字（约束名），删除表中指定约束时，可以使用指定约束的约束名来进行删除该约束。 constraint拘束。

添加表中约束的语法如下👇

ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束条件(表中需要加指定约束的列名)

删除表中约束时，使用关键字DROP，如下👇

ALTER TABLE 表名
DROP CONSTRAINT 约束名;

一般不用自己写，使用默认的约束名就行。

3，创建表时创建约束

语法格式如下：

CREATE TABLE 数据表名（从表）
(
列名 数据类型,
.
.
.
FOREIGN KEY (列名) REFERENCES 主表名(列名)
)

4，任务

在StuScore数据库中，根据需要还需要创建系部表，班级表和用户表。使用T-SQL语句按照下表所示的表结构创建所需的三张表。以下是数据库中各张表的结构👇

注意点：

1. 学生表不是第一张先创的表，从图中可以看到，学生表有一个班级编号外键classid，表示在学生表students和班级表classes之间，班级表是主表，学生表是从表。因此要先将创建班级表，再创建学生表。
2. 根据之前所学的约束条件，上面的建表就会很简单，使用默认的约束条件。
3. 约束条件：
   
   主键  PRIMARY KY
   检查  CHECK(gender='男' or gender='女')
   外键  FOREIGN KEY
   唯一  UNIQUE
    

上述表中，唯一特别的就是成绩表score中的sno和cno都是主键约束，通过之前的学习，我知道了一张表只能有一个主键约束。

为什么成绩表score中会有两个主键？

答：因为每个学生可以选修多门课，所以成绩表中的学生学号sno就会有重复，这意味着仅使用sno作为主键无法保证数据的实体完整性，因为它不能唯一标识每一条成绩记录。然而，每个学生在同一门课程中只会有一条成绩记录，因此sno和cno的组合可以唯一标识每一条成绩记录。通过将这两个字段合并为一个复合主键，能够有效地保证数据的实体完整性。

“复合主键”：就如上面的情况，一张表只能有一个主键，但是有些时候需要多个字段同时为一个主键，这种时候就需要使用到复合主键。

值得注意的是，因为我是事先看表结构来创表的，因此，我知道要用到复合主键，sno和cno先不加任何约束，之后再一起添加主键约束。

如果不小心将学号设置成了主键，就需要先使用如下语句删掉原有的主键，再添加复合主键进去。

--删除约束
ALTER TABLE 表名
DROP CONSTRAINT 约束名  -- 添加复合主键约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名  PRIMARY KEY(列名1,列名2)

上面这张表主要是用于连接数据库，对应数据库的sa用户及密码。 如果后面做项目的话，会用到。

按照生活常识：

1. 没有系部的成立，就没有班级。
2. 学生达到一定人数，才会开设课程。
3. 没有学生考试，就不会有成绩。

可以看到，首先应该创建的是系部表，再是班级表，接着就是学生表，课程表，成绩表。

编写的sql语句如下：

5，由任务编写的SQL语句 

USE Stuscore  --之前创建的数据库
----系部表
-- 检查并删除表 dept
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dept' AND type = 'U')DROP TABLE dept
CREATE TABLE dept(deptno char(5) NOT NULL PRIMARY KEY,dname nchar(10) NOT NULL,dean nchar(4),phone char(8) UNIQUE
);--班级表
-- 检查并删除表 classes
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'classes' AND type = 'U')DROP TABLE classes;
CREATE TABLE classes(classid char(6) NOT NULL PRIMARY KEY,speciality nchar(12) NOT NULL,deptno char(5) NOT NULL, counselor nchar(4),FOREIGN KEY (deptno) REFERENCES dept(deptno)
);--学生表
-- 检查并删除表 students
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'students' AND type = 'U')DROP TABLE students;
CREATE TABLE students
(sno char(8) NOT NULL PRIMARY KEY,sname nchar(4) NOT NULL,gender nchar(1) CHECK(gender='男' or gender='女'),classid char(6) NOT NULL,  birthday date,phone char(13) UNIQUE,FOREIGN KEY (classid) REFERENCES classes(classid)
)--课程表
-- 检查并删除表 courses
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'courses' AND type = 'U')DROP TABLE courses;
CREATE TABLE courses
(cno char(10) NOT NULL PRIMARY KEY,cname nvarchar(20) NOT NULL,period int NOT NULL,credit tinyint NOT NULL,  type nchar(5) NOT NULL
)--成绩表
-- 检查并删除表 score
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'score' AND type = 'U')DROP TABLE score;
CREATE TABLE score
(sno char(8) NOT NULL,cno char(10) NOT NULL,CONSTRAINT PK_score_sno_cno PRIMARY KEY(sno, cno),grade tinyint CHECK(grade>=0 and grade<=100),FOREIGN KEY (sno) REFERENCES students(sno), FOREIGN KEY (cno) REFERENCES courses(cno)    
)--用户表
-- 检查并删除表 users
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'users' AND type = 'U')DROP TABLE users;
CREATE TABLE users(login char(10) NOT NULL PRIMARY KEY,username nchar(5) NOT NULL,  pwd char(10) NOT NULL
)-- 检查是表中的字段是否完整
SELECT * FROM dept
SELECT * FROM classes
SELECT * FROM students
SELECT * FROM score
SELECT * FROM users

三，使用T-SQL管理表 

1，创表时添加约束名 

这里以系部表dept为例。

1）添加约束名 

系部表中的deptno为主键约束，如果不添加主键约束名的语句就如下编写：

deptno char(5) NOT NULL PRIMARY KEY,

但如果想要给主键加上约束名，就需要先写好列deptno不急着添加主键约束，需要再写一行语句给列deptno添加主键约束及主键约束名：

    deptno char(5) NOT NULL,CONSTRAINT PK_dept_deptno PRIMARY KEY(deptno),

 为什么需要给约束添加上约束名，主要是因为随着时间的推移，有些约束条件需要改变，比方说，成绩表中的成绩grade现在的约束是0~100分，但是满分可能会变成150分，即约束为0~150。

如果想要在不删除表格的前提下修改该约束，就需要通过约束名来修改该约束条件。

如果不给约束条件加上约束名，系统会为该约束条件分配名字，但由系统给的约束名字会变。

使用如下语句查看系统给的约束名（这里是dept表，查看主键PK）：

SELECT name FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('dept') AND type = 'PK';

先不手动添加约束名，dept表的deptno列约束名就会随着程序的每一次运行而改变，如下👇：

第一次执行下面的语句： 

 再次执行下面的语句： 

仔细看，会发现表示主键约束的关键字PK及表名dept不变，但是后面的约束名会变。 如果想要通过约束名来删除该约束无疑是天方夜谭。因此建议只要含有主键约束的列最好都加上对应的约束名。约束名可以写成如下形式：

约束条件单词首位字母大写_数据表名_被添加约束的列名

手动添加约束名之后再去查看约束名，就不会随着程序的运行而改变： 

2） 修改由任务编写的SQL语句 （添加约束名）

--USE Stuscore  --之前创建的数据库----系部表
-- 检查并删除表 dept
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dept' AND type = 'U')DROP TABLE dept
CREATE TABLE dept(dno char(5) NOT NULL CONSTRAINT PK_dept_dno PRIMARY KEY(dno), --dno系编号 dne nchar(100) NOT NULL,  --dne系名 dept nameddn nchar(100), --ddn系主任  dept deandpe char(11),   --dpe系联系电话 dept phoneCONSTRAINT CK_deptno_dpn UNIQUE(dpe)
)--班级表
-- 检查并删除表 classes
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'classes' AND type = 'U')DROP TABLE classes;
CREATE TABLE classes(cno char(6) NOT NULLCONSTRAINT PK_classes_cno  PRIMARY KEY(cno), --cno班级编号cne nchar(100) NOT NULL,  --cne班级名 classes namedno char(5) NOT NULL,  --dno系编号ccr nchar(100),  --班级辅导员 classes counsellorCONSTRAINT FK_dept_dno FOREIGN KEY (dno) REFERENCES dept(dno)
);--学生表
-- 检查并删除表 students
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'students' AND type = 'U')DROP TABLE students;
CREATE TABLE students
(sno char(8) NOT NULL  CONSTRAINT PK_students_sno  PRIMARY KEY(sno),  --sno学生编号sne nchar(100) NOT NULL,  --sne 学生名 students namessx nchar(6)CONSTRAINT CK_students_ssx  CHECK(ssx='male' or ssx='female'), --ssx学生性别 students sexcno char(6) NOT NULL,    --cno班级编号sbd date,  --sbt学生出生日期 students birth dayspe char(11) CONSTRAINT UQ_students_spe UNIQUE(spe),   --spe学生联系方式 students phonesnn nvarchar(100)  CONSTRAINT DF_students_snn DEFAULT 'China', --snn学生国籍 students nationCONSTRAINT FK_students_cno FOREIGN KEY (cno) REFERENCES classes(cno)
);--课程表
-- 检查并删除表 courses
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'courses' AND type = 'U')DROP TABLE courses;
CREATE TABLE courses
(cno char(10) NOT NULLCONSTRAINT PK_courses_cno  PRIMARY KEY(cno),   --cno课程编号 cne nvarchar(100) NOT NULL,   --cne 课程名 courses namecpd int NOT NULL,      --cpd课程学时  courses periodcct tinyint NOT NULL,   --cct  课程学分  courses creditcte nchar(20) NOT NULLCONSTRAINT CK_courses_type CHECK(cte='optional_course' or cte='required_course')  --cte课程类型 courses type
)--成绩表
-- 检查并删除表 score
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'score' AND type = 'U')DROP TABLE score;
CREATE TABLE score
(sno char(8) NOT NULL, cno char(10) NOT NULL,CONSTRAINT PK_score_sno_cno PRIMARY KEY(sno, cno),get tinyint,  --get 成绩得分CONSTRAINT CK_score_get  CHECK(get>=0 and get<=100),CONSTRAINT FK_score_sno FOREIGN KEY (sno) REFERENCES students(sno), CONSTRAINT FK_score_cno FOREIGN KEY (cno) REFERENCES courses(cno)    
)--用户表
-- 检查并删除表 users
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'users' AND type = 'U')DROP TABLE users;
CREATE TABLE users(uln char(100) NOT NULL   CONSTRAINT PK_users_uln  PRIMARY KEY(uln),  --uln 用户登录 users loginune nchar(100) NOT NULL,    --une  用户名  users name  upd char(10) NOT NULL    -- upd 用户登录密码 users password
)-- 检查是表中的字段是否完整
--SELECT * FROM users
SELECT * FROM studentsSELECT * FROM deptSELECT * FROM classesSELECT * FROM score

2，使用ALTER TABLE修改表

有些时候已经创好表，点击执行之后，会存在少列，少约束等问题。这些问题都不用担心，能够在创建好数据表后添加。

可以使用ALTER  TABLE... DROP COLUMN...删除列，ALTER...ADD...添加列。

1）从表中删除列

语法如下： 

ALTER TABLE 数据表名 DROP COLUMN 要删除的列名（无任何约束条件）

 需要注意的是，删除的列必须没有任何约束，这里以用户表users为例，我现在删除主键列login

可以看到，因为有约束条件-主键约束，所以该列无法删除。

但是如果我想要删除没有任何约束的列pwd，是完全可以删除的，如下👇

2）向表中添加列 

 语法如下：

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

依旧可以使用ALTER TABLE关键字将删除的列pwd再添加回来：

3）修改表中列的长度

语法如下： 

ALTER TABLE 数据表明 COLUMN 列名(新的长度)

以上面的用户表users为例，将列pwd的长度由原来的10变成20，可以如下编写语句：

ALTER TABLE users ALTER COLUMN  pwd char(20)

 如果想要查看修改的长度是否生效，可以使用如下语句查看列的长度：

--查看列的长度
SELECT column_name AS 列名, data_type AS 数据类型, character_maximum_length AS 数据长度
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'pwd';

如上，长度修改成功。 

4）对指定表添加默认约束 

 这里以学生表students为例，向学生表中添加新列nation国籍 nvarchar(10)，默认值为“中国”。

 ALTER TABLE studentsADD nation nvarchar(10)  CONSTRAINT DF_students_nation DEFAULT('中国')

 通过上图可以无法直观的感受到默认值的优势，后面插入数据的时候会更直观。

ALTER TABLE courses ADD  CONSTRAINT DF_courses_cte DEFAULT 'required_course' FOR cte
INSERT INTO dept(dno,dne,ddn,dpe)
VALUES('1','Computer','QiangguoGong','1531111111')INSERT INTO classes(cno,cne,ccr,dno)
VALUES('1','Computer24','JianjunSong','1')INSERT INTO students(sno,sne,ssx,sbd,spe,cno) 
VALUES('1','zs','male','2000.1.1','18911111111','1')INSERT INTO courses(cno,cne,cpd,cct)
VALUES('1','SQLserver DataBase Application principle','96','6')INSERT INTO users(uln,une,upd)
VALUES('SQL_identity','sa','123456')

 3，使用DROP TABLE删除表

 能够创建表自然也就能够删除表。删表的语法如下：

DROP TABLE 数据表名

有问题请在评论区留言或者是私信我，回复时间不超过1天。