SQL插入、更新和删除数据

SQL插入、更新和删除数据

一、直接向表插入数据

1.1、插入完整的行

这里所说的完整行指的是包含表内所有字段的数据行；假设表中有n个字段，则插入完整行的语法：

INSERT INTO 表名或视图名 VALUES(字段1的值,字段2的值,字段3的值,...,字段n的值);

该语法格式由INSERT子句和VALUES子句构成；INSERT子句用于指定向哪个表或视图插入数据，VALUES子句用于指定要插入的数据，使用VALUES子句时需要注意以下几点：

• VALUES子句中必须列出所有字段的值，而且必须按表中字段顺序排列；当DBMS插入数据时，会被“字段1的值”插入到第一个字段，将“字段2的值”插入到第二个字段，以此类推。
• 将要插入的数值的数据类型必须与表相应字段的数据类型互相兼容，否则就会出现错误，导致插入失败。例如，要将一个字符串插入到数值型字段时就会出错。

PS：兼容的数据类型是指同一数据类型或DBMS能自动转换成兼容类型的数据类型；例如，大多数DBMS能够将日期格式的字符串自动转换为日期型数据，因此日期格式的字符串与日期型数据是兼容的。

• 示例：向数据表stu_course添加课程内容。

INSERT INTO stu_course VALUES('008','大学语文','必修',4),('009','法律基础','必修',3),('010','音乐欣赏','选修',2);

1.2、向日期时间型字段插入数据

如果表中有日期时间型字段，向日期时间类型的字段插入数据时，使用日期格式的字符串即可。

• 示例：向数据表stu_info添加学生信息

1.3、将数据插入到指定字段

有时并不需要向表插入完整的行，而需要将数据只插入到几个指定字段内，在表名后加上字段列表。

示例：向数据表stu_info添加学生信息

INSERT INTO stu_info(id,name,sex,birthday,institute) VALUES('016','孔乙己','男','1995-05-29','中文系');

通过运行结果发现，没有插入数据的字段都为NULL值；在VALUES子句中可以直接指定哪个字段设置为NULL值，例如，VALUES(...,...,NULL,NULL);

当只给几个字段插入数据时，应注意不能省略有非空约束的字段（NOT NULL）；如果某字段已经设置了默认值，即使有非空约束也可以将其省略。

实际上，在使用INSERT插入完整行时，也可以在表名后加入字段名列表；即使以后改变了表结构，如添加了新字段，原来的程序语句仍可用。

1.4、将查询结果插入表

在INSERT语句中可以嵌入SELECT语句，并将SELECT的查询结果集插入到指定的表；这就是通常所说的INSERT SELECT，它由INSERT子句和SELECT语句组成，其语法格式如下：

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

CREATE TABLE new_table AS SELECT * FROM stu_info WHERE 1=0;     ---复制表结构

这里的 WHERE 1=0 确保不会复制任何数据，只复制表结构。

示例：将stu_info表中所有数据，通过INSERT SELECT插入到new_stu_info表。

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

INSERT INTO new_stu_info SELECT * FROM stu_info;

1.5、INSERT SELECT与SELECT INTO的区别

• SELECT INTO在没有将数据表存在的情况下，先创建表，然后再将查询结果放进表内；如果要创建的表名和现有表名重复，则会出现错误提示。
• INSERT INTO则必须在数据表存在的前提下，才能向其插入查询结果，它不能自动创建表；如果要插入数据的表不存在，则会出现错误提示。

1.6、通过视图插入数据

授予用户操作特定视图的权限，每个用户通过拥有权限的视图，对自己能够访问到的数据进行各种操作。

通过视图插入数据，对用户来说，其实和直接向表插入数据基本相同，区别仅仅在于表名变成了视图名。如果创建的视图对用户用户隐藏列，则隐藏的列的值都为NULL，也会导致在视图中查不到新插入的记录，建立视图时应包含所属的字段。（WHERE 条件）

创建视图时加个WITH CHECK OPTION选项，可以防止用户通过视图对数据进行插入、删除和更新时，无意或故意操作不属于视图范围内的基本表数据：

CREATE VIEW vw_bb AS SELECT id,name,sex,birthday,institute FROM stu_info WHERE institute='计科系' WITH CHECK OPTION;

如果想限制用户通过视图插入不属于视图权限范围内的数据，则应当在建立视图时加上上WITH CHECK OPTION选项。

二、更新和删除数据

2.1、更新表中的数据

2.1.1、更新单个字段的数据

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

其中，UPDATE子句指定要更改哪个表中的数据，SET子句指定将哪个字段的数据用什么值替换，WHERE子句设置要更新记录的条件。

1. UPDATE子句：告诉DBMS要使用哪个表，并打开该表。
2. WHERE子句：将表中满足条件的记录放入结果集。
3. SET子句：更新结果集中所有记录的特定字段的数据。

注意：UPDATE语句中的WHERE子句可以被省略，但是这么做的后果是，更新在所有记录上进行；因此，在省略WHERE子句前应当考虑清楚，是否真的要更新所有记录的数据。

• 备份数据表的所有内容

-- 创建备份表（MySQL） CREATE TABLE employees_backup AS SELECT * FROM employees; --导出表结构和数据到新表（SQL Server） SELECT * INTO NewTableName FROM TableName;

1. 示例：在stu_info表中，将名叫“张三”的学生的联系方式更改为“010-81234567”

UPDATE stu_info SET contact = '010-81234567' WHERE name = '张三';

在使用UPDATE语句更新数据时，首先可以使用SELECT语句测试其WHERE子句的正确性，这样可以尽量避免更新错误，例如：

SELECT * FROM stu_info WHERE name = '张三';

2.1.2、更新多个字段的数据

更新多个字段数据的语法格式：

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

其中，SET子句中上的表达式之间用逗号（，）隔开。

• 示例：在stu_info表中，将所有计科系学生的所属院系值更改为“计算机学院”,联系方式改为“0471-6123456”

UPDATE stu_info SET institute = '计算机学院',     contact = '0471-6123456' WHERE institute = '计科系';

2.1.3、使用子查询更新数据

在UPDATE语句的WHERE子句中，可以使用子查询选择需要更新的记录。

示例：在score表中，将每个学生的“心理学”考试成绩增加2分

首先，查看一下score表中所有关于“心理学”课程的信息

SELECT s_id AS 学号,c_id AS 课号,result1 AS 考试成绩,result2 AS 平时成绩 FROM score WHERE c_id=(SELECT ID FROM stu_course WHERE course = '心理学');

更新语句

UPDATE score SET result1=result1+2 WHERE c_id=(SELECT ID FROM stu_course WHERE course='心理学');

2.1.4、使用表连接更新数据

在UPDATE语句中还可以使用FROM子句，通过FROM子句和WHERE子句配合，可以进行多表连接，就是说在UPDATE语句中可以通过多表连接进行数据更新。

• 示例：在score表中，将每个学生“心理学”的考试成绩减2分，将其还原为更新前的分数。

UPDATE score s,stu_course c SET s.result1=s.result1-2 WHERE c.course = '心理学' AND s.c_id = c.ID;

2.1.5、使用UPDATE语句删除指定字段的数据

UPDATE语句除了更新数据以外，还有一个作用，即删除指定字段的数据；所谓删除，就是使用NULL值替换原有的字段值。

使用NULL值替换字段值时，首先必须保证该字段可以为空，否则会出现错误。

• 示例：在stu_info表中，将所有计算机学院的联系方式的值删除。

UPDATE stu_info SET contact = NULL WHERE institute = '计算机学院';

2.2、删除表中的数据

在SQL中删除数据要使用DELETE语句。

2.2.1、使用DELETE语句删除指定记录

使用DELETE语句删除的是整行记录，而并非是记录中的某个字段值。

DELETE FROM 表名 WHERE 条件表达式;

DELETE FROM指定要从哪个表删除数据，WHERE用于设置删除记录的条件；即DELETE语句从表中删除那些满足WHERE子句条件的所有记录。当省略WHERE子句时，DELETE语句删除表中的所有记录。

• 示例：从new_stu_info表中删除名叫“孔乙己”的学生记录

DELETE FROM new_stu_info WHERE name = '孔乙己';

使用DELETE语句删除多条记录：从new_stu_info表中删除所有所属院系为NULL的记录

DELETE FROM new_stu_info WHERE institute IS NULL;

2.2.2、在DELETE语句中使用多表连接

在DELETE语句中也可以使用多表连接。

• 示例：从score_copy表中，删除“张三”和“马六”的所有相关记录。

DELETE s FROM score_copy s,new_stu_info st WHERE st.name IN('张三','马六') AND st.id = s.s_id;

运行结果为空查询结果集，这表示DELETE语句从score_copy表删除了关于“张三”和“马六的”所有记录。

删除语句中，DELETE关键字后的表名指定要从哪个数据表删除数据。

2.2.3、使用DELETE语句删除所有记录

如果DELETE语句后不加WHERE子句，则会将表内所有记录全部删除。这里要注意区分的是，DELETE语句删除的是所有记录，并不是数据表本身。

• 示例：删除new_stu_info表内的所有记录

DELETE FROM new_stu_info;

2.2.4、使用TRUNCATE语句删除所有记录

实际上使用使用DELETE语句删除表中所有记录的效率有有时非常低，这是因为DBMS会向事务处理日志写入一些内容，这些内容在删除执行失败时，可以帮助用户将数据回滚（回退）到删除执行前的状态。

TRUNCATE是删除表中所有记录的另一种语句，与DELETE语句相比，其运行效率非常高，因为使用TRUNCATE语句时，DBMS不会写入任何内容，换个角度说，就是TRUNCATE语句所做的修改是不能回滚的。TRUNCATE语句只是删除了表中的所有数据，而并没有删除表本身。

示例：删除new_stu_info表内的所有记录

INSERT INTO new_stu_info SELECT * FROM stu_info;     --向表插入内容

TRUNCATE TABLE new_stu_info;     --删除表所有记录

2.3、通过视图更新表

2.3.1、不能用于更新的视图

1. 在 SQL Server 中，当一个视图的 SELECT 子句包含聚合函数时，该视图通常是不可更新的。这是因为聚合函数（如 SUM, COUNT, AVG, MIN, MAX）会将多个行的值汇总为单个值，导致视图无法对应回基础表中的特定行，因此无法确定如何更新这些行。

• 为什么包含聚合函数的视图不可更新

1. 聚合函数的结果：聚合函数将多个行的值汇总为一个结果。例如，SUM(SaleAmount) 计算特定产品的总销售额，这是多个销售记录的汇总。
2. 无法映射回原始数据：由于聚合函数的结果无法唯一映射回基础表中的特定行，SQL Server 无法确定如何更新基础表中的数据。

1. 在 SQL Server 中，仅包含 GROUP BY 子句的视图通常也不可更新，因为 GROUP BY 子句将多行数据分组成一个单一的结果集，这使得无法将更新操作映射回基础表中的具体行。

• 为什么仅包含 GROUP BY 子句的视图不可更新

1. 分组的结果：GROUP BY 子句将多行数据分组，每个组仅生成一行结果。
2. 无法映射回原始数据：分组后的结果无法唯一映射回基础表中的具体行，因此 SQL Server 无法确定如何将更新操作应用到基础表中的具体行。

1. 在 SQL Server 中，包含 DISTINCT 关键字的视图是不可更新的。DISTINCT 关键字用于去除重复记录，使得每行都是唯一的。这导致 SQL Server 无法唯一映射视图中的行到基础表中的具体行，从而无法进行更新操作。

• 为什么包含 DISTINCT 关键字的视图不可更新

1. 去除重复行：DISTINCT 关键字去除重复行，使得视图中的每行都是唯一的。
2. 无法映射回原始数据：由于 DISTINCT 关键字去除了重复行，视图中的每一行可能对应多个基础表中的行，因此无法确定如何将更新操作应用到基础表中的具体行。

1. 在 SQL Server 中，当视图的 SELECT 语句包含计算字段（计算列）时，该视图通常是不可更新的。这是因为计算字段的值通常是根据其他列的值计算得出的，更新计算字段没有明确的方式映射回基础表中的具体行或列。

• 为什么包含计算字段的视图不可更新

1. 计算字段的值：计算字段的值是根据其他列的值计算得出的，并不是直接存储在基础表中的独立值。
2. 无法映射回原始数据：更新计算字段没有明确的方式映射回基础表中的具体列，因此 SQL Server 无法确定如何将更新操作应用到基础表中的具体行或列。

1. 在 SQL Server 中，基于多表连接的视图通常是不可更新的。这是因为多表连接的视图可能会产生一个组合的数据集，无法唯一地映射回单个基础表中的特定行，从而导致 SQL Server 无法确定如何应用更新操作。

• 为什么基于多表连接的视图不可更新

1. 数据集的组合：视图基于多个表的连接，生成一个组合的数据集。
2. 无法唯一映射：视图中的每一行可能对应多个基础表中的行，无法唯一地映射回单个表中的特定行，因此 SQL Server 无法确定如何应用更新操作

1. 在 SQL Server 中，如果视图不包含基础表中具有非空约束且没有默认值的字段，则该视图不能用于更新数据。这是因为在插入或更新操作时，必须提供这些非空字段的值，而视图中没有这些字段导致无法满足这一要求。或者为这些字段定义默认值，以确保在插入或更新操作时能够提供所有必需的值。。

• 为什么这种视图不可更新

1. 非空约束：将表中的 列1 和 列3 字段定义为 NOT NULL，因此在插入或更新操作时必须提供这些字段的值。
2. 视图不包含非空字段：视图 不包含 列1 和 列3 字段，因此无法通过该视图插入或更新这些必要的非空字段。

2.3.2、通过视图更新表数据

创建一个可更新的视图vw_update：

CREATE VIEW vw_update AS SELECT * FROM stu_info WHERE institute = '计算机学院' WITH CHECK OPTION;

• 示例：将学生“杨九”的来源地更新为“四川省”

UPDATE vw_update SET origin = '四川省' WHERE name = '杨九';

视图vw_update的定义语句带有WITH CHECK OPTION选项，所以不能使用UPDATE语句更新其他非“计算机学院”字段的内容；如果执行其他字段的内容就会出现报错，不会更新数据。

2.3.3、通过视图删除表数据

INSERT INTO new_stu_info SELECT * FROM stu_info;

CREATE VIEW vw_delete AS SELECT * FROM new_stu_info WHERE institute = ‘中文系’ WITH CHECK OPTION;

示例：通过视图vw_delete将来源地为NULL的学生删除。

DELETE FROM vw_delete WHERE origin IS NULL;

只是删除了视图中可见的数据，而没有删除在视图中看不到的记录。