SQL-Server(三)表的创建和操作

3 年前

半道出家的lv

醉里挑灯看剑，梦回吹角连营！

3.1表结构和数据类型

3.1.1表和表结构

表结构。组成表的各列的名称及数据类型，统称为表结构。
记录。每个表包含了若干行数据，它们是表的“值”，表中的一行称为一个记录。因此表是记录的有限集合。
字段。每个记录由若干个数据项构成，将构成记录的每个数据段称为字段。
空值。空值（null）通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列给出具体值；而一个列若不允许为空值，则在输入时必须给出具体值。
关键字。若表中记录的某一字段或字段组合能唯一标识记录，则称字段或字段组合为候选关键字（Candidate Key）。若一个表有多个候选关键字，则选定其中一个为主关键字（Primary Key），也称为主键。当一个表仅有唯一的一个候选关键字时，该候选关键字就是主关键字。

3.1.3表结构设计

创建表的实质就是定义表结构，设置表和列的属性。在创建表之前，先要确定表的名称、表的属性，同时确定表所包含的列名、列的数据类型、长度、是否可为空值、约束条件、默认值设置、规则及所需索引、哪些列是主键、哪些列是外键等，这些属性构成表的结构。

1.创建表 CREATE TABLE

CREATE TABLE [数据库名.[架构名].|架构名.]表名
	......
	[<表约束>]
)	......
<列定义>=
列名 数据类型                     /*指定列名、列的数据类型*/
[FILESTREAM]                      /*指定FILESTREAM的属性*/
[COLLATE 排序名]	              /*指定排序规则*/
[NULL | NOT NULL]	              /*指定是否为空*/
	[CONSTRAINT 约束名]                  
	DEFAULT 常量表达式            /*指定默认值*/
|[IDENTITY[(初值,增量)][NOT FOR REPLICATION]]	   /*指定列为标识列*/
[ROWGUIDCOL]	                                   /*指定列为全局标识符列*/
[<列约束>...]	                                   /*指定列的约束*/

说明：

a.FILESTREAM:允许以独立文件的形式存放大对象数据，而不是像以往一样将所有的数据都保存到数据文件中。

b.NULL | NOT NULL：NULL表示列可以取空值，NOT NULL表示列不可以取空值。

c.DEFAULT 常量表达式：为所在的列指定默认值，默认值“常量表达式”必须是一个常量值、标量函数或NULL值。DEFAULT定义可适用于除定义timestamp或带identity属性的列以外的任何列。

d.IDENTITY：指出该列为标识符列，为该列提供唯一的、递增的值。“初值”是标识字段的起始值，默认为1，“增量”是标识增量，默认值为1.如果为IDENTITY指定了NOT FOR REPLICATION选项，则复制代理执行插入时，标识列中的值将不会增加。

e.ROWGUIDCOL：表示新列是行的全局唯一标识符列，ROWGUIDCOL属性只能指派给uniqueidentifier列。该属性并不强制列中所存储值的唯一性，也不会为插入到表中的新行自动生成值。

f.<列约束>：列的完整性约束，指定主键、替代键、外键等。若指定该列为主键，则使用PRIMARY KEY关键字。

示例：

USE test1
CREATE TABLE xsb1
	学号		char(6)			NOT NULL PRIMARY KEY,
	姓名		char(8)			NOT NULL,
	性别		bit				NULL DEFAULT 1,
	出生时间	date	 		NULL,
	专业		char(12)		NULL DEFAULT '计算机',
	总学分		int				NULL DEFAULT 0,
	备注		varchar(500)	NULL
GO

2.修改表结构：ALTER TABLE

ALTER TABLE [数据库名.[架构名].|架构名.]表名
	ALTER COLUMN 列名{...}                            /*修改已有列的属性*/
	|ADD                                              /*添加列*/   
		|列名 AS 表达式 [PERSISTED[NOT NULL]]         /*定义计算列*/
		|<表约束>
	}[,...]
	|DROP
		[CONSTRAINT]约束名                            /*删除约束*/  
		[WITH(<删除聚集约束选项>...)]
		|COLUMN 列名                                  /*删除列*/ 
	}[,...]
	|[WITH{CHECK|NOCHECK}]{CHECK|NOCHECK}CONSTRAINT{ALL|约束名}
	......
}

a.表名：要修改的表名。

b.ALTER COLUMN子句：修改表中指定列的属性，“列名”给出要修改的列。

c.ADD子句：向表中增加新列，新列的定义方法与CREATE TABLE命令中定义列的方法相同。一次可以添加多个列，中间用逗号隔开。

d.DROP子句：从表中删除列或约束。（删除列之前，必须先删除基于该列的所有索引和约束）。

e.WITH子句：[WITH{CHECK|NOCHECK}]指定表中的数据是否用新添加的或重新启用的FOREIGN KEY或CHECK约束进行验证。ALL关键字指定启用或者禁用所有的约束。

ALTER COLUMN 列名
	类型名[(精度[,位数])]
	[COLLATE 排序名]
	[NULL | NOT NULL]
	......
}

f.类型名：为被修改列的新数据类型。当要修改成数值类型时，可以使用(精度[,位数])分别指定数值的精度和小数的位数。

g.[NULL | NOT NULL]:表示将列设置为是否为空，设置成NOT NULL时要注意表中该列是否有空数据。

示例：

USE test1
ALTER TABLE xsb1
	ADD 入学时间  date 
GO

3.删除表：DROP TABLE

DROP TABLE [数据库名.[架构名].|架构名.]表名[,...][;]

其中表名是要被删除的表的名称

4.插入记录 INSERT

INSERT [TOP (表达式)[PERCENT]]
[INTO] 表名|视图名
[(列表)]
VALUES (DEFAULT|NULL|表达式...)             /*指定列值*/ 
|DEFAULT VALUES                             /*强制新行包含为每个列定义的默认值*/

说明：

a.表名：被操作的表的名称。前面可以指定数据库名和架构名

b.视图名：被操作的视图名称

c.列表：需要插入数据列的列表，包含了新插入行的各列的名称。如果只给表的部分列插入数据时，需要用“列列表”指出这些列。

d.VALUES子句：包含各列需要插入的数据清单，数据的顺序要与列的顺序相对应。若省略“列列表”，则VALUES子句给出每一列（除IDENTITY属性和timestamp类型以外的列）的值。

e.DEFAULT VALUES：该关键字说明向当前表中所有列均插入其默认值。此时，要求所有列均定义列默认值。

示例：

USE test1
INSERT INTO xsb1
	VALUES('191301','王林',1,'1990-02-10','计算机',50,NULL),('191302','王林林',0,'1992-03-11','软件工程',52,'三好学生')
GO

5.修改记录：UPDATE

UPDATE [TOP(表达式)[PERCENT]]
{表名|视图名}
SET{列名=表达式,...}                            /*赋予新值*/    
[FROM<表源>...]
[WHERE<查找条件>  | ...]                        /*指定条件*/  
......

a.SET子句：用于指定要修改的列或变量名及其新值。

b.FROM子句：指定用表来为更新操作提供数据。

c.WHERE子句：WHERE子句中的<查找条件>指明只对满足该条件的行进行修改，若省略该子句，则对表中的所有行进行修改。

示例：

USE test1
UPDATE xsb1
	SET 备注='外校互认学分课程',
		总学分=总学分+3
	WHERE 学号='191301'
GO

6.删除记录：DELETE/TRUNCATE TABLE

删除负荷条件的记录

DELETE [TOP (表达式) [PERCENT]]
[FROM 表名|视图名 | <表源>]
[WHERE <查找条件> |...]                       /*指定条件*/

a.[TOP (表达式) [PERCENT]]：指定将要删除的任意行数或任意行的百分比。

b.FROM子句：说明从何处删除数据

c.WHERE子句：删除操作指定条件。若省略WHERE子句，则DELETE语句将删除所有数据。

示例：

USE test1
DELETE
	FROM xsb1
	WHERE 总学分 = 0
GO

删除表的所有记录

TRUNCATE TABLE 表名

说明：

a.使用TRUNCATE TABLE语句删除了指定表中的所有行，但表结构及其列、约束、索引等保持不变，而新行标识所用的计数值重置为该列的初始值。如果要保留标识计数值，则要使用DELETE语句。

b.“TRUNCATE TABLE”和“DELETE TABLE”二者均为删除表中的全部行。但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。

DELETE语句每次删除一行，并在事务日志中为所删除的每一行记录一项。而TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，

并且只在事务日志中记录页的释放。

对由外键（FOREIGN KEY）约束引用的表，不能用TRUNCATE TABLE删除数据，而应该用不带WHERE子句的DELETE删除。另外，TRUNCATE TABLE语句也不能用于参与了索引视图的表。

c.表记录删除后不能恢复

d.如果删除表记录的同时删除表结构，则使用“DROP TABLE 表名”命令

7.同步记录：MERGE

MERGE [TOP (表达式)[PERCENT]]
[INTO] 目标表 [ [AS] 别名]
USING <表源>
ON <连接条件>
[WHEN MATCHED [AND<查找条件>]
	THEN {UPDATE SET <set子句>|DELETE}]
[WHEN NOT MATCHED  [BY TARGET] [AND<查找条件>]
	THEN INSERT [(列列表)]{VALUES(值列表)|DEFAULT VALUES}]
[WHEN NOT MATCHED BY SOURCE [AND<查找条件>]
	THEN {UPDATE SET <set子句>|DELETE}]

说明：

a.目标表：指定要更新数据的表或视图。AS子句用于为表定义别名。

b.USING子句：指定用于更新的源数据表。

c.ON子句：指定在<表源>与目标表进行连接时所遵循的条件

d.WHEN MATCHED子句：表示在应用了ON子句的条件后，目标表存在与源表匹配的行时，对这些行在THEN子句中指定修改或删除的操作。THEN子句中，UPDATE SET<set子句>用于修改满足条件的行，DELETE关键字用于删除满足条件的行。另外还可以使用AND<查找条件>指定任何有效的查找条件。

e.WHEN NOT MATCHED [BY TARGET]子句：指定对于源表中满足ON子句中条件的每一行，如果该行与目标中的行不匹配，则向目标表中插入该行数据。要插入的数据在THEN关键字后的INSERT子句中指定。一个MERGE语句只能有一个WHEN NOT MATCHED子句。

f.WHEN NOT MATCHED BY SOURCE子句：指定对于目标表中与源表应用了ON子句中条件后返回的行不匹配，但满足其他查找条件的所有行，根据THEN关键字后的子句进行修改或删除。

一个MERGE语句最多可以有两个WHEN MATCHED子句或两个WHEN NOT MATCHED BY SOURCE子句。如果制定了两个相同的子句，则第一个子句必须同时带一个AND<查找条件>子句，而且其中一个必须指定UPDATE操作，另一个必须指定DELETE操作，另外MERGE语句必须以分号结尾。

示例：创建xsb3，表结构用于xsb1相同，进行同步

MERGE INTO xsb3
	USING xsb1 ON xsb3.学号=xsb1.学号