SQL Server(四) - 插入、更新和删除数据

1、主要内容

● 通过SSMS，插入、更新和删除表数据
● 通过INSERT语句向表中插入数据
● 通过UPDATE语句更新表内数据
● 通过DELETE语句删除表内数据
● 使用INSERT、UPDATE和DELETE语句的几个技巧

2、 使用INSERT语句插入数据

（1） 插入完整的行

INSERT INTO 表名
VALUES (字段1的值,字段2的值,字段3的值,……,字段n的值)

VALUES子句中必须列出所有字段的值，而且必须按表中字段顺序排列
说明：兼容的数据类型是指同一数据类型或SQL Server能自动转换成兼容类型的数据类型，例如，SQL Server能够将日期格式的字符串自动转换为日期型数据，因此，日期格式的字符串与日期型数据是兼容的。

【例1】向数据表course添加如下表所示的课程内容。

INSERT INTO course
VALUES ('009','法律基础', '必修',3);
INSERT INTO course
VALUES ('010','素描', '选修',2)

（2） 向日期时间型字段插入数据
【例5】向数据表stu_info添加如表所示的学生信息。

 

INSERT INTO stu_info
VALUES ('0016','玛丽','女','1989-02-07',' marry@163.com ','13716161616','物理系')

（3） 将数据插入到指定字段
【例6】向数据表stu_info添加如表所示的学生信息。


 

INSERT INTO stu_info(sno,sname, sex, birth, depart)
VALUES ('0017', '周伦杰','男','1987-05-07', '中文系')

INSERT INTO stu_info
VALUES ('0017','周伦杰','男','1987-05-07', NULL,NULL,'中文系')

（4） 将查询结果插入表

INSERT INTO表名[(字段列表)]
SELECT语句

下面创建一个数据表，并命名为stu_info_copy。

CREATE TABLE stu_info_copy
(
    学号      char(4)      NOT NULL,
    姓名      nchar(20)    NOT NULL,
    性别      nchar(1)     NOT NULL,
    出生日期   date,
    电子信箱   char(50),
    手机号码   char(11),
    所属院系   nchar(30)
)

【例7】将stu_info表中所有数据，通过INSERT SELECT插入到stu_info_sopy表。

分析：因为两个表的表结构相同，而且要将stu_info中所有字段的内容都插入到stu_info_copy表中，所以在INSERT子句中可以省略字段列表。

INSERT INTO  stu_info_copy
SELECT  *
FROM   stu_info

因为两表的表结构一致，而且要将stu_info表所有字段的内容都复制过去，所以在INSERT子句中没有列出字段列表。但是，如果两表的表结构不同或者只复制一部分字段，则应当在INSERT子句中列出字段列表。
3 使用UPDATE语句更新数据
（1） 更新单个字段的数据
 

UPDATE  表名
SET     字段名 = 更新值
WHERE  条件表达式

（1）UPDATE子句。指定SQLServer要使用的表，并打开表。
（2）WHERE子句。将表中满足条件的记录放入结果集。
（3）SET子句。更新结果集中所有记录的特定字段的数据。
注意：UPDATE语句中的WHERE子句可以被省略，这样一来，所有记录都会被更新。因此，在省略WHERE子句前应当考虑清楚，是否真的要更新所有记录的数据。
【例8】在stu_info表中，将名叫“张三”的学生的email更改为“zhangsan@163.com”。

UPDATE stu_info
SET email='zhangsan@163.com '
WHERE sname='张三'

（2） 更新多个字段的数据

UPDATE  表名
SET     字段名1=更新值1,
字段名2=更新值2,
字段名3=更新值3
WHERE  条件表达式

【例9】在stu_info表中，将没有院系的学生全部归为“国际交流学院”所属，并将email统一更改为“gjjl@imnu.edu.cn”。
 

UPDATE  stu_info
SET     depart ='国际交流学院',
        email='gjjl@imnu.edu.cn'
WHERE  depart IS NULL

（3） 使用表连接更新数据
  通过FROM子句和WHERE子句配合，可以进行多表连接。
【例10】score表中，在每个学生“大学英语”的平时成绩上加5分。
分析：因为score表中只有课程编号，而并没有课程名称，因此，必须从course表中得到“大学英语”的课程编号后，然后才能根据该编号更新score表中的数据。所以解决问题的最佳方式是连接score表和course表进行更新操作。

UPDATE  score
SET     usually= usually +5
FROM   score AS s,course AS c
WHERE  c.cname='大学英语'
AND    s.cno=c.cno

（4） 使用UPDATE语句删除指定字段的数据
   UPDATE语句还有一个作用，即删除指定字段的数据。所谓删除，只是使用NULL值替换原有的字段值而已。
  注意：用NULL值替换字段值时，首先必须保证该字段可以为空，否则会出现错误。
【例11】在stu_info表中，将所有外语系学生的“telephone”字段的值删除。
 

UPDATE stu_info
SET    telephone = NULL
WHERE  depart='外语系'

4、 使用DELETE语句删除数据
（1） 使用DELETE语句删除指定记录
   使用DELETE语句删除的是整行记录，而并非是记录中的某个字段值。

DELETE  FROM 表名
WHERE  条件表达式

【例12】从stu_info_copy表，删除名叫“安娜”的学生。
 

DELETE FROM  stu_info_copy
WHERE       姓名 = '安娜'

【例13】从stu_info_copy表，删除所有外语系的学生。
 

DELETE FROM  stu_info_copy
WHERE      所属院系 ='外语系'

（2） 在DELETE语句中使用多表连接
   创建一个score表的复制表score_copy。

SELECT  *
INTO   score_copy
FROM  score

【例14】从score_copy表中，删除“张三”的所有相关记录。
  先查看score_copy表中关于“张三”的全部记录。

SELECT  s.*
FROM   score_copy AS s,stu_info AS st
WHERE  st.sname = '张三'
AND    st.sno=s.sno

其次，使用下面的语句删除记录。

DELETE  score_copy
FROM   score_copy AS s,stu_info AS st
WHERE  st.sname ='张三'
AND    st.sno=s.sno

说明：删除语句中，DELETE关键字后的表名指定要从哪个数据表删除数据，在本语句中DELETE关键字后是score_copy，因此，只删除score_copy表中的相关数据，而与FROM子句中列出的其他表无关，例如与stu_info表无关。
再次使用上面的SELECT语句，查看“张三”的相关内容。
（3） 使用DELETE语句删除所有记录
如果DELETE语句后不加WHERE子句，则会将表内所有记录全部删除。这里需要注意区分的是，DELETE语句删除的是所有记录，而并不是数据表本身。
【例15】删除stu_info_copy表内的所有记录。
 

DELETE  FROM  stu_info_copy

5、 使用TRUNCATE语句删除所有记录
    实际上使用DELETE语句删除表中所有记录的效率有时非常低，因为SQL Server会向事务处理日志写入一些内容，这些内容在删除执行失败时，可以帮助用户将数据回滚（回退）到删除执行前的状态。
TRUNCATE是删除表中所有记录的另一种语句，与DELETE语句相比，TRUNCATE运行效率非常高，因为使用TRUNCATE语句时，SQL Server不会写入任何内容，换个角度说，TRUNCATE语句所做的修改是不能回滚的。
【例16】删除stu_info_copy表内的所有记录。

TRUNCATE TABLE stu_info_copy

6、 综合练习
1．判断以下INSERT语句是否能够将数据正确插入到stu_info_copy表。

INSERT INTO stu_info_copy
VALUES ('0016', '玛丽','女', '198927', 'marry@163.com ', '13716161616','物理系')

分析：从错误信息（从字符串转换日期和/或时间时，转换失败）。分析INSERT语句后发现，给出生日期（birth）字段插入的值“198927”不是日期格式的字符串，所以该语句运行错误。在本例中如果将值改为“19890207”，则插入语句会正确运行，如下所示。

INSERT INTO stu_info_copy
VALUES ('0016', '玛丽', '女', '19890207', 'marry@163.com ', '13716161616', '物理系')

2．将计算机系所有学生的“信息基础”课程的考试成绩更改为95分。
分析：在score表中没有学生的院系信息，也没有课程的课程名称信息，而stu_info表中有院系信息，course表中有课程名称信息，因此必须将stu_info表和course表连接到score表上进行更新。

UPDATE  score
SET     exam= 95
FROM   stu_info AS st ,score AS s,course AS c
WHERE  st.depart='计算机系 '
AND    c.cname='信息基础'
AND    s.cno=c.cno
AND    st.sno=s.sno

UPDATE  score
SET     exam= 95
FROM   stu_info
 INNER JOIN
        score  ON stu_info.sno = score.sno
INNER JOIN
        course ON score.cno = course.cno
WHERE  stu_info.depart = N'计算机系'
AND     course.cname = N'信息基础'