SqlServer对表的基本操作

SqlServer对表的基本操作

手动建库建表 

脚本建库建表 

  SQL Server关系数据库的数据表结构主要是由记录(行)和字段(列)构成，每一行代表唯一的一条记录(Record)，而每列则代表所有记录中的一个域(Field)(也称为字段、属性)。 

注意：在同一个数据库里，表的名字也必须是唯一的。在同一个表里，列的名字必须是唯一的。

SQL Server中的表一共有两类，即永久表和临时表。

1. 永久表都保存在数据库文件中
2. 临时表虽然与永久表很相似，但它们却是存储在tempdb数据库中的，而且当不再使用这些临时表时，它们会被自动删除。

SQL Server的数据类型可以分为系统内置的数据类型和用户自定义数据类型。系统数据类型是系统内置的数据类型，主要有：

  1)整数型

    

  2)小数数据类型(精确数据类型)

  3)近似数值型(浮点数据类型)

  4)字符型和Unicode字符型

  5)逻辑数值型

  6)日期和时间数据

  7)二进制数据类型

  8)货币型数据

  9)其他数据类型

定义：

  数据完整性就是要求数据库表中的数据具有准确性。

方法：

  为了维护数据库中数据的准确性，通常是在创建表时为表中的字段定义约束，防止将错误的数据插入到表中。

分类：

  SQL Server中数据完整性包含四种类型分别是：实体完整性、域完整性、参照完整性、用户定义完整性。

(1)实体完整性

  实体完整性将记录(行)定义为特定表的唯一实体，即每一行数据都反映不同的实体，不能存在相同的数据行。

  通过索引、UNIQUE(唯一)约束、PRIMARY KEY(主键)约束、标识列属性可以实现实体完整性。

约束种类	功能描述
PRIMARY	KEY(主键)约束，唯一识别每一条记录的标志，可以有多列共同组成
IDENTITY(自增)约束	列值自增，一般使用此属性设置的列作为主键
UNIQUE(唯一)约束	可以使用UNIQUE约束确保在非主键列中不存在重复值，但列值可以是NULL(空)

(2)域完整性

域完整性指特定字段项的有效性。

可以强制域完整性限制类型(通过使用数据类型)、限制格式(通过使用CHECK约束和规则)或限制可能值的范围(通过使用FOREIGN KEY 约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则)。

名称	描述
CHECK(检查)约束	用于限制列中值得范围
FOREIGN KEY(外键)	一个表中的FORENIGN KEY 指向另一个表中的PRIMARY KEY
DEFAULT(默认值)约束	用于向列中插入默认值
NOT NULL(非空)约束	用于强制列不接受NULL(空)值

(3)参照完整性

在输入或删除数据行时，参照完整性约束用来保持表与表之间已定义的关系。在SQL Server 2016中，参照完整性通过FOREIGN KEY和CHECK约束，以外键与主键之间或外键与唯一键之间的关系为基础。参照完整性确保键值在所有表中一致。这类一致性要求不能引用不存在的值，如果一个键值发生更改，则整个数据库中，对该键值的所有引用要进行一致的更改。

(4)用户定义完整性

用户自定义完整性用来定义特定的规则。例如，输入学生年龄时，只能输入大于0的值。所有完整性类别都支持用户定义完整性。这包括创建表中所有列级约束和表级约束、存储过程以及触发器。

创建表的步骤:

  1)定义表结构：给表的每一列取字段名，并确定每一列的数据类型、数据长度、列数据是否可以为空等。

  2)设置约束：设置约束是为了限制该列输入值的取值范围，以保证输入数据的正确性和一致性。

  3)添加数据：表结构建立完成之后，就可以向表中输入数据了。

create table

CREATE TABLE 表名                                        
(列1定义，                                  
   列2定义，                                    
   列n 定义)
   
   /*其中：
< 列定义 > ::= { 列名 数据类型 }
       [ [ DEFAULT 约束表达式 ]
        | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
       ] 
       [ < 列约束> ] [ ...n ] 
       */

  案例1.

    创建带有参照约束的学生表，学生表的表结构定义如下表所示。“学号”字段为学生表的主键，“班级代码”字段为学生表的外键，它必须参照班级表中的“班级代码”字段的值。

CREATE TABLE 学生(   
    // identity表示自增列的意思，而intidentity(1,1)表示从1开始递增，每次自增1。
    // primary key的作用是定义主键；主键的值不可以重复，也不可以为空
学号  char(12)   IDENTITY(1,1)  PRIMARY KEY,  
姓名 varchar(20)   NOT NULL,  // not null为不为空
性别 char(2), 
出身日期 datatime, 
入学时间 datatime, 
班级代码 char(9)   CONSTRAINT fk_bjdm REFERENCES 班级(班级代码)  )

// 代码中的PRIMARY IDENTITY CONSTRAINT等关键字是对表中各列进行完整性约束的一些词语

表的查看

1、 使用系统存储过程Sp_help 查看

  使用系统存储过程sp_help来查看表的属性的方法是：在sp_help后面加上要看的表名作为参数。其格式如下： 

  EXEC sp_help  表名 

    例如，要查看上述在Students数据库中所创建的学生表的属性，可以使用如下语句： EXEC sp_help 学生表

    如果用户想查看该数据库中所有表的属性，则可以直接使用系统存储过程sp_help后不带任何参数来进行显示。 

2、使用SQL Server Management Studio(SSMS)查看 

  除了上述使用系统存储过程来显示表的属性外，在SQL Server Management Studio中查看表的定义会更加方便。其方法如下： 

  1)、在【对象资源管理器】下展开数据库并选中“表”选项，在需要查看表的名称上右键单击，在弹出的快捷菜单中选择“属性”选项，可以打开“表属性”对话框，用户可以查看表中每一列的定义。

表的修改

  要修改表，可以使用ALTER TABLE语句或SQL Server ManagementStudio两种方法来进行。

  1、使用T-SQL 语句ALTER TABLE命令修改表

  使用ALTER TABLE语句对表进行修改常见的情况有以下几种。 

    1、向表中添加新列 

    向表中添加新列时  :  需要在  ALTER TABLE  语句中使用 ADD子句。

    语法格式如下：

      修改表中列的定义  ALTER TABLE 表名 ALTER COLUMN 列名 <列属性> ;

ALTER TABLE 表名 ADD 列名  数据类型  属性1 属性2…….. 

//【例】向教材表增加单价列，列名为单价，数据类型为float，默认空值，代码如下： 

ALTER TABLE 教材  

ADD 单价 float(2)  NULL;

GO

 

注意：向已存在的表中增加列时，应使新增加的列具有默认值或允许其为空值。添加列完成时，SQL Server将向表中已存在的行填充新增列的默认值或空值。如果既没有提供默认值也不允许为空值，那么新增列的操作将出错，因为SQL Sever不知道该怎么处理那些已经存在的行。

     2、删除表中的列 

      删除表中的列需要使用ALTER TABLE语句的DROP COLUMN子句。 格式： 

      ALTER TABLE 表名 DROP COLUMN 列名 ;  // column是字段，也就是列！

    【例】将教材表中建立的“单价”列删除。其代码如下： 

ALTER TABLE 教材 DROP COLUMN单价;
GO

         3、修改表中列的定义 

        修改表中某列的属性定义语法如下：

             ALTER TABLE 表名 ALTER COLUMN 列名 <列属性> ;

    例】将教材表中教材名称字段数据类型的长度修改为20

ALTER TABLE 教材 ALTER COLUMN 教材名称 varchar(20);
GO

 

   4、修改表中列的名称

        修改表中某列的列名语法如下：

     EXEC SP_RENAME‘表名.原列名’,‘新列名’, ‘COLUMN’ ;

    【例】将班级表中系部名称修改为院系名称

EXEC SP_RENAME ‘班级.系部代码’,‘院系代码’, ’COLUMN’;
GO

 

 

数据表中的数据操作

   数据删除

    当省略WHERE语句时，将删除表中所有的行。

      注意：如果删除的数据就直接来源于后面声明的基本表，可以省略FROM命令

   1)普通DELETE语句。

     基本语法： DELETE FROM 表名 [WHERE 逻辑表达式 ]

   2)关联DELETE语句。

    在部分删除数据的任务中，需要使用其它表中的数据作为条件依据，此时就可用关联DELETE。

     DELETE 表名 [FROM 源表名 [ , … N ]] [WHERE 逻辑表达式 ]

     【例】将“选课”表中的学分字段值小于课程表中学分的课程删除。

    代码如下：

DELETE 选课 FROM 选课 a,课程 b WHERE a.课程号=b.课程号 AND a.学分<b.学生

   3)子查询DELETE语句。

 ·    基本语法： DELETE FROM 表名 [WHERE 逻辑表达式 ]

     【例】找出课程号在“选课”表中而又不在“课程”表中的记录给予删除。

    代码如下：

DELETE 选课 WHERE 课程号 NOT IN (SELECT 课程号 FROM 课程)

   4)TRUNCATE TABLE语句 。

    当需要快速清除某表的全部数据时，可以用TRUNCATE命令。TRUNCATE命令可以将表中所有数据删除，但是并不删除基本表，表的基本结构还存在。

     基本语法： TRUNCATE TABLE 表名；

     【例】清空“教材”表。

    代码如下： TRUNCATE TABLE 教材;

 

数据添加

  INSERT插入数据时有两种方式：

    插入单行数据(使用关键字VALUES)和插入多行数据(使用关键字SELECT)。

  1、使用INSERT语句插入单行数据

    使用INSERT语句一次插入一行数据,是最常用的数据添加方法。

    INSERT语句基本语法格式如下：

INSERT  [INTO] <表名>  [<字段名列表>]
VALUES (值列表)

  <字段名列表>中个各字段之间用逗号隔开，<值列表>中个各值之间也用逗号隔开。

  在插入数据的时候，需要注意以下事项：

1.   值列表与字段名列表中的各项是一一对应的，每个数据值的数据类型也必须与对应字段匹配。
2.   INSERT语句不能为标识列指定值，因为其中数据是由系统自动生成的。
3.   对于非数值型数据需用单引号括起来。

  有约束的字段，输入内容必须满足约束条件。

  对于不允许为空的字段，必须要输入内容，允许为空的字段可以用NULL代替。

  有默认值的字段，如果没有添加数据，系统会自动插入默认值。

  如果<字段名列表>省略，则对表中所有列插入数据。

【例】向“教材表”插入一行数据。

INSERT INTO 教材(教材编码,教材名称,出版商名称)
VALUES ('2008001','SQLServer','北京邮电大学')
// 由于本例是对表中所有列插入数据，所以也可改为：
INSERT INTO 教材
VALUES ('2008001','SQL Server','北京邮电大学')

  注意：只有当填入数据的数量、顺序都与基本表中字段一一对应的时候，才可以省略字段名列表。

  2、使用INSERT语句插入多行数据

    如果需要把其他表中的多条记录添加到当前表中，可以在插入数据时通过INSERT SELECT 语句可以实现将SELECT查询语句的结果集添加到当前表中，格式如下：

INSERT [INTO] <当前表名>[<字段名列表>]
SELECT <字段名列表>
FROM 源表名 [, … N ]
[WHERE 逻辑表达式 ]

其中SELECT查询语句的结果集中每个字段必须要有列名，如果无列名必须声明别名。

【例】新建一表，其名为“教材副表”，表结构完全与“教材表”相同。将“教材表”中出版商为北京邮电大学的记录插入到该表中。代码如下：

SELECT * INTO 教材副表 FROM 教材 WHERE 2=3
GO

注意：以上代码仅仅只是建立了一张空表，表的结构和教材表的结构一致，却没有具体数据，因为用到查询条件“WHERE 1=2”永远不成立，这是一常用方法

INSERT INTO 教材副表(教材编码,教材名称,出版商名称)

SELECT *

FROM 教材 WHERE 出版商名称='北京邮电大学'

GO

在本例中，也可以把INSERT语句改为以下语句：

INSERT INTO 教材副表

SELECT *

FROM 教材 WHERE出版商名称='北京邮电大学'

GO

注意：使用这种方法一定要杜绝表中有标识列的情况，因为INSERT语句不能为标识列指定值，系统会提示错误。

   1. 使用INSERT语句插入单行数据

    使用INSERT语句一次插入一行数据,是最常用的数据添加方法。

    基本语法： INSERT [INTO] <表名> [<字段名列表>] VALUES (值列表)

·    【例】向“教材表”插入一行数据。代码如下：

   INSERT INTO 教材(教材编码,教材名称,出版商名称) VALUES ('2008001','SQL Server','北京邮电大学')

   2. 使用INSERT语句插入多行数据。

    如果需要把其他表中的多条记录添加到当前表中，可以在插入数据时通过INSERT SELECT 语句可以实现将SELECT查询语句的结果集添加到当前表中。

INSERT [INTO] <当前表名> [<字段名列表>]
SELECT <字段名列表>
FROM 源表名 [, … N ]
[WHERE 逻辑表达式 ]

    【例】新建一表，其名为“教材副表”，表结构完全与“教材表”相同。将“教材表”中出版商为北京邮电大学的记录插入到该表中。代码如下： 

SELECT * INTO 教材副表 FROM 教材 WHERE 1=2    
GO
INSERT INTO 教材副表(教材编码,教材名称,出版商名称)
SELECT * 
FROM 教材 WHERE 出版商名称='北京邮电大学'
GO

数据更新

    2. 普通UPDATE语句

     使用INSERT语句一次插入一行数据,是最常用的数据添加方法。

    基本语法： UPDATE 表名 SET {字段名 = 表达式 | NULL | DEFAULT } [ , …N] [ WHERE 逻辑表达式 ]

 其中：

    表名 :需修改的表的名称

    字段名 = 表达式 | NULL | DEFAULT：指修改指定字段的值

    WHERE表示满足什么条件才能修改

    【例】修改选课表中是数据库基础的所有课程号学分为从4改为3。代码如下：

     UPDATE 选课

       SET 学分=3

       WHERE 课程号 = (SELECT 课程号 FROM 课程 WHERE 课程名称='数据库基础')

    3. 关联UPDATE语句    

// 基本语法：
UPDATE  表名
SET  {字段名 = 表达式 | NULL | DEFAULT } [ , …N]
[ FROM 源表名  [ , … N ]] 
[ WHERE 逻辑表达式 ]

  例】用“选课表”中的成绩来修改“成绩表”中的期末成绩。代码如下：

UPDATE 成绩
SET 期末成绩=b.成绩
FROM 成绩a,选课b
WHERE a.课程号=b.课程号