创建与管理数据表

1.表的概念

 

 

 

2.系统表

SQL Server 2012通过一系列表来存储所有对象、数据类型、约束、配置选项、可利用资源的相关信息，这一系列表被称为系统表。系统表用来存储SQL Server的配置、安全和数据库对象信息。SQL Server在系统表的帮助下管理每个数据库。每个数据库都有自己的系统表，master数据库中的系统表包含SQL Server的信息，其他数据库中的系统表包含数据库的信息。任何用户都不应该直接更改系统表，例如，不要尝试使用DELETE、UPDATE和INSERT语句或用户定义的触发器修改系统表。将系统表分类时，可以将其分为备份和还原表、日志传送表、变更数据捕获表、复制表、数据库维护计划表、SQL Server代理表以及SQL Server扩展事件表等。系统表中还有一种系统基表，它是基础表，用于实际存储特定数据库的元数据。在该方面，master数据库有些特别，因为它包含一些在其他任何数据库中都找不到的表，这些表包含服务器范围内的持久化元数据。系统表用来存储SQL Server的配置、安全和数据库对象信息。SQL Server在系统表的帮助下管理每个数据库。每个数据库都有自己的系统表，master数据库中的系统表包含SQL Server的信息，其他数据库中的系统表包含数据库的信息。

SQL Server 2012中包含多种系统表和多个系统基表，表2-7中只列出了部分系统基表，关于其他的系统基表和系统表，读者可参考SQL Server 2012在线文档。

3.临时表

临时表和临时变量对于复杂的查询非常有用。当不使用临时表或者表变量时，某些程序可能要经过非常复杂的处理才能得到结果。

 

 

创建数据表

1.使用“对象资源管理器”创建数据表

2.使用Create Table语句创建数据表

Create Table语句的基本语法如下：

table_name：表的名称。

Column_name：表中列的名称。

datatype：列的数据类型。

NULL/NOT NULL：该列是否允许有空值。

IDENTITY：用于需要自动产生唯一系统值的列。SEED：IDENTITY的初始值。

INCREMENT：增量，可以为负值。

ON {filegroup}DEFALT：指出将表创建在哪个文件组上。如果无ON子句或给出ON DEFUALT，则默认值为主文件组，即PRIMARY。

 

使用Create Table语句创建表

Use Student
GO
--创建Class表
Create Table Class
(
	ClassNo nvarchar(8) NOT NULL,
	ClassName nvarchar(20) NOT NULL,
	DepartNo nvarchar(2) NOT NULL

)
GO

--创建Student表
Create Table Student
(
	StuNo nvarchar(8) NOT NULL,
	StuName nvarchar(10) NOT NULL,
	Pwd nvarchar(8) NOT NULL,
	ClassNo nvarchar(8)  NOT NULL
)
GO

--创建StuCon表
Create Table StuCou
(
	StuNo nvarchar(8) NOT NULL,
	ConNo nvarchar(3) NOT NULL,
	State nvarchar(2) NOT NULL
)

显示表结构

显示表结构可以使用SQL Server Management Studio显示或使用存储过程sp_help语句显示。

可以使用sp_help语句显示表结构，语法如下：

sp_help table_name

修改数据表

使用ALTER TABLE命令为Student表添加备注列bz，数据类型为nvarchar，长度为20，允许为空。

Use Student
ALTER TABLE Student
ADD bz nvarchar(20) null
GO

注意：必须允许新增列为空，否则，表中已有数据行的那些新增列的值为空与新增列不允许为空相矛盾，会导致新增列操作失败。

使用ALTER TABLE命令删除表Student的birthday列。

Use Student
ALTER TABLE Student
DROP COLUMN Birthday
GO

修改Student表的备注列bz，将列定义的长度修改为30。在查询窗口中执行如下SQL语句：

Use Student
ALTER TABLE Student
ALTER COLUMN bz nvarchar(30)  null
GO

将Student表的备注列bz重命名为StuBz列。

本题可以使用存储过程sp_rename。

USE Student 
GO
Sp_rename 'Student.bz','StuBz','COLUMN'
GO

删除Student表bz列。

Use Student
ALTER TABLE Student
DROP COLUMN StuBz
GO

 

维护数据表的数据

数据库中的表对象建立后，用户对表的访问，可以归纳为4种基本操作：添加或插入新数据、更改更新现有数据。

使用SQL Server Management Studio对数据表进行维护

给Student表录入数据。

删除Student表中学号为20140002的记录。具体操作步骤如下：在表数据编辑界面中，选定学号为20140002的记录（选定整条记录），选择“编辑”→“删除”命令即可。如果要选定多条记录，可以使用【Ctrl】键或【Shift】键。

使用Transact-SQL对数据表进行维护

在SQL Server中可以通过INSERT、UPDATE、DELETE语句分别进行插入数据、修改数据、删除数据的操作。

（1）INSERT语句。INSERT语句可以给表添加一个或多个新行，其语法格式如下：

INSERT [ INTO ] { table_name | view_name}[（column_name [， … n] ） ] VALUES （ expression | NULL |DEFAULT [，…N] |）

参数说明如下：

INSERT：表示添加记录。

INTO：用来指向表，是可选项。

table_name：是添加记录的表名。

view_name：是添加记录的视图名。

column_name：表中添加记录的字段名，圆括号“（）”表示一列值。

VALUES：表字段取值的关键字。表中字段对应的expression值，如果字段可以为空，则该字段值可以是NULL；如果字段设置了默认值，则该字段的值可以是默认值。

使用Transact-SQL语句插入数据

USE Student
GO
INSERT INTO Student
VALUES('20140001','巴图','20140102','123456')
INSERT INTO Student
VALUES('20140002','孟和','20140102','123456')
INSERT INTO Student
VALUES('20140003','朝鲁','20140103','123456')
GO

 

按指定列顺序插入数据

USE Student
GO
INSERT INTO Student (StuNo,StuName,pwd,ClassNo)
VALUES('20140001','巴图','20140102','123456')
INSERT INTO Student (StuNo,StuName,pwd,ClassNo)
VALUES('20140002','孟和','20140102','123456')
INSERT INTO Student (StuNo,StuName,pwd,ClassNo)
VALUES('20140003','朝鲁','20140103','123456')
GO

注意：未给出值的列应允许为空。

（2）UPDATE语句。UPDATE语句用于修改现有的记录，其语法格式如下：

参数说明如下：

UPDATE：表示修改记录。

table_name：是修改记录的表名。

view_name：是修改记录的视图名。

SET：是设置新值的关键字。

column_name：表中修改记录的字段名。

new_expression：是要修改记录字段的新值。

WHERE：修改记录条件的关键字。

search_condition：修改记录的条件。

使用Transact-SQL语句修改Student表中学号为20140002的学生选课密码为000000。

USE Student
GO
UPDATE Student SET Pwd='000000' WHERE Stuno='20140002'

（3）DELETE语句。DELETE语句可删除表或视图中的一行或多行，每一行的删除都将被记入日志。其语法格式如下：

DELETE [FROM] {table_name | view_name } [WHERE<search_condition>]

参数说明如下：DELETE：表示删除记录。

FROM：指向要删除记录的表名，可选项。

table_name：要删除记录的表名。

view_name：要删除记录的视图名。

WHERE：删除记录条件的关键字。

search_condition：删除记录的条件。

使用Transact-SQL语句删除Student表中学号为20140003的学生记录。

Use Student
GO
DELETE FROM Student WHERE StuNo='20140003'
GO

注意：不带参数时使用DELETE命令将删除所有行。

 

Truncate是SQL中的一个删除数据表内容的语句，用法是：

　　语法

　　TRUNCATE TABLE name

　　参数

　　name

　　是要截断的表的名称或要删除其全部行的表的名称。

　　下面是对Truncate语句在MSSQLServer2000中用法和原理的说明：

　　Truncate table 表名 速度快,而且效率高,因为:


　　TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。


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


　　TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。


　　对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。


　　TRUNCATE TABLE 不能用于参与了索引视图的表。

　　对用TRUNCATE TABLE删除数据的表上增加数据时，要使用UPDATE STATISTICS来维护索引信息。

　　如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。

　　示例

　　下例删除 authors 表中的所有数据。

　　TRUNCATE TABLE authors

　　权限

　　TRUNCATE TABLE 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让
。

 

重命名数据表

 

删除数据表

删除表的Transact-SQL语句格式如下：DROP TABLE [database_name.]table_name[，…n]其中，table_name是要删除的表的表名。注意：定义有外键约束的表必须先删除外键约束，才能删除该表。系统表不能使用DROP TABLE语句删除。