SQLServer学习笔记四：数据操作

学习目标

• 向表中插入数据
• 更新表中数据
• 删除表中数据

DML的基本概念

DML（Data Manipulation Language，数据操作语言）是SQL的组成部分之一。用来插入、修改和删除数据库中的数据，如INSERT、UPDATE、DELETE等。

在学习数据操作之前，我们先来认识一下SQL中的运算符。

SQL中的运算符

算术运算符

运算符	说明
+	加运算，求两个数或表达式相加的和
-	减运算，求两个数或表达式相减的差
*	乘运算，求两个数或表达式相乘的积
/	除运算，求两个数或表达式相除的商，例如，5/5的值为1，5.7/3的值为1.900000
%	模运算，求两个数或表达式相除的余数，例如，5%3的值为2

赋值运算符

用于将一个数或变量或表达式赋值给另一个。

运算符	说明
=	把一个数或变量或表达式赋值给另一个变量，例如，Name=‘张三’

比较运算符

比较运算符用来判断两个表达式的大小关系，除text、ntext或image 数据类型的表较运算符几乎可以用于其他所有的表达式。

运算符	说明
>	大于，例如，price>100
<	小于
>=	大于等于
<=	小于等于
=	等于，例如，age=23
<>	不等于，符合ISO标准
!=	不等于，不遵循ISO标准

比较运算符计算结果为布尔数据类型，并根据测试条件的输出结果返回TRUE或FALSE。

逻辑运算符

逻辑运算符用来对某个条件进行判断，以获得判断条件的真假，返回布尔值TRUE或FALSE。

运算符	说明
AND	当且仅当两个布尔表达式都为TRUE时，返回TRUE，否则返回FALSE
OR	当且仅当两个布尔表达式都为FALSE时，返回FALSE
NOT	对布尔表达式的值取反，优先级别最高

练习

思考以下代码的含义：

NOT(性别 = '男') AND (年龄 >= 18 AND 年龄 <= 45)

插入数据

我们已经了解了如何创建表、修改表的结构和添加约束，现在需要学习如何向表中添加数据。主要有两种方式：

• 使用Management Studio，选择表点击右键，在弹出的快捷菜单中选择“编辑前200行”选项，就可以向表中直接输入数据行。
• 使用T-SQL添加新数据，也可以将现有表中的数据添加到新创建的表中。

使用INSERT插入数据

使用INSERT语句一行一行地插入数据是最常用的方法，其语法格式如下：

INSERT [INTO] 表名 [(列名列表)] VALUES (值列表);

其中：

• [INTO]是可选的，也可以省略。
• 表名是必需的。
• 表的列名是可选的，如果省略，将依次插入所有列。
• 多个列名和多个值列表用逗号分隔。
• 分号(:)是T-SQL语句终止符，不是必需的。

例如，以下的语句为向学生表中插入一行数据。

INSERT INTO Students(SName, SAddress, SGrade, SEmail,SSEX)VALUES('张三', '威海', 6, 'zs@Sohu.com'，0)

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

• 每次插入一整行数据，不可能只插入半行或者几列数据，但允许某些列为空或使用默认值。
• 数据值的数目必须与列数相同，每个数据值的数据类型、精度和小数位数也必须与相应的列匹配。
• 语句不能为标识列指定值，因为它的数字是自动增长的。
• 对于字符类型、日期类型的列，当插入数据的时候，用单引号(')将其引起来。
• 尽管可以不指定列名，但是最好明确指定插入的列和对应的值，以便能够将列和值清晰地对应起来。
• 若在设计表的时候指定某列不允许为空，则该列必须插入数据，否则将报告错误信息。
• 插入的数据项，要求符合检查约束的要求。与表的约束相冲突，则插入失败。

一次插入多行数据

一次插入多行数据有3种方法：

• 通过INSERT…SELECT语句可以向现有表中添加数据
• 通过SELECT…INTO语句将现有表中的数据添加到新表中
• 通过UNION关键字合并数据进行插入

通过INSERT…SELECT语句可以向表中添加数据。

例如，创建一张新表AddressList来存储本班的通讯录信息，则可以从学生表中提取相关的数据插入建好的AddressList表中，T-SQL语句如下。

INSERT INTO AddressList(姓名，地址，电子邮件)
SELECT SName, SAddress, SEmail
FROM Students

SELECT语句用于查询，上面的SQL语句用来把学生信息表中的姓名、地址和Email信息读取并保存到AddressList表中。

需要注意以下两点：

• 查询得到的数据个数、顺序、数据类型等，必须与插入的项保持一致。
• AddressList表必须预先创建好，并且具有姓名、地址和电子邮件三列。

通过SELECT…INTO语句将现有表中的数据添加到新表中

与上面的INSERT INTO类似，SELECT INTO也是从一个表中选择一些数据插入新表中，所不同的是，这个新表是执行查询语句的时候创建的，不能够预先存在。
例如，下面的T-SQL语句将创建新表AddressList，然后把学生信息表中的姓名、地址和Email信息读取并保存到新的AddressList表中

SELECT Students.SName, Students.SAddress, Students.SEmail
INTO AddressList
FROM Students

在想一个新表插入数据的时候，有会牵扯到一个新的问题，如何插入标识列？
因为标识列的数据是不允许指定的，因此可以创建一个新的标识列，语法如下：

SELECT IDENTITY(数据类型, 标识种子, 标识增长量) AS 列名
INTO 新表
FROM 原始表

上面的语句可以修改为：

SELECT Students.SName, Students.SAddress, Students.SEmail, IDENTITY(int, 1, 1) AS StudentID
INTO AddressList
FROM Students

通过UNION关键字合并数据进行插入

UNION语句用于将两个不同的数据或查询的结果组合成一个新的结果集。
例如，下面的语句把多行数据记录一起插入到Students表中。

INSERT Students(SName, SGrade,SSex)
SELECT '张可',7,1 UNION
SELECT '李扬',4,0 UNION
SELECT '杨晓',2,0 UNION
SELECT '汤美',3,0 UNION
SELECT '苏三东',7,1 UNION
SELECT '王立岩',3,1 UNION
SELECT '张伟',7,1 UNION
SELECT '陈刚',4,1 UNION
SELECT '王娟娟',7,0

更新数据

数据更新是经常发生的事情，下面了解一下如何使用T-SQL进行数据更新。语法格式如下：

UPDATE 表名 SET 列名 = 更新值 [WHERE 更新条件]

其中：

• SET后面可以紧随多个列名=更新值，修改多个数据列的值，不限一个，使用逗号分隔。
• WHERE子句是可选的，用来限制更新数据的条件。若不限制，则整个表的所有数据行将被更新。 需要注意的是，使用UPDATE语句，可能更新一行数据，也可能更新多行数据，还可能不会更任何数据。

示例1

在学生信息表中，要把所有学生的性别都改为0(女生)。

UPDATE Students SET SSEX= 0

示例2

对于地址为“威职”的学生，若想这个学校改为“威海职业技术学院”，则需要按照条件进行更新。

UPDATE Students
SET SAddress = '威海职业技术学院'
WHERE SAddress = '威职'

示例3

将所有低于或等于95分的成绩都在原来的基础上加5分。

UPDATE StudentScore
SET Score = Score + 5
WHERE Score <= 95

注意

在更新数据的时候，一般都有条件限制，别忘了书写WHERE条件语句，否则将更新表中所有行的数据，这就可能导致有效数据的丢失。

删除数据

使用T-SQL删除表中的数据，语法格式如下：

DELETE [FROM] 表名 [WHERE <删除条件>]

示例1

在学生信息表中删除姓名为“张三”的数据的SQL语句如下。

DELETE FROM Students
WHERE SName ='张三'

还有一种情况，如果要删除的行的主键值其他表引用，例如，分数表中的ShudentID引用了学生信息表中的SCode列，那么删除被引用的行时，SQL Server将报告与约束冲突的错误信息。

DELETE FROM Students
WHERE SCode = 22

另外，DELETE语句删除的是整条记录，不会只删除单个列，所以在DELETE后不能出现到名。例如，以下语句，将报告错误信息。

DELETE SAddress FROM Students

清除记录

删除记录除了使用DELETE之外，TRUNCATE TABLE也可以用来删除数据。TRUNCATE TABLE用来删除表中的所有行，功能上它类似于没有WHERE子句的DELETE语句。

示例1

要删除学生信息表中的所有记录行，可以使用以下语句。

TRUNCATE TABLE Students

TRUNCATE TABLE比DELETE执行速度快，使用的系统资源和事务日志资源更少，并且删除数据后表的标识列会重新开始编号。

注意

• TRUNCATE TABLE删除表中的所有行，但是表的结构、列、约束、索引等不会被改动。
• TRUNCATE TABLE不能用于有外键约束引用的表，这种情况下，需要使用DELETE语句。
• 实际工作中，不建议使用TRUNCATE TABLE语句，因为使用它删除的数据不能恢复还原。