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子句设置要更新记录的条件。
UPDATE子句：告诉DBMS要使用哪个表，并打开该表。WHERE子句：将表中满足条件的记录放入结果集。SET子句：更新结果集中所有记录的特定字段的数据。注意：UPDATE语句中的WHERE子句可以被省略，但是这么做的后果是，更新在所有记录上进行；因此，在省略WHERE子句前应当考虑清楚，是否真的要更新所有记录的数据。
备份数据表的所有内容| -- 创建备份表（MySQL） CREATE TABLE employees_backup AS SELECT * FROM employees;
--导出表结构和数据到新表（SQL Server） SELECT * INTO NewTableName FROM TableName; |
| 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、不能用于更新的视图
在 SQL Server 中，当一个视图的 SELECT 子句包含聚合函数时，该视图通常是不可更新的。这是因为聚合函数（如 SUM, COUNT, AVG, MIN, MAX）会将多个行的值汇总为单个值，导致视图无法对应回基础表中的特定行，因此无法确定如何更新这些行。 为什么包含聚合函数的视图不可更新 聚合函数的结果：聚合函数将多个行的值汇总为一个结果。例如，SUM(SaleAmount) 计算特定产品的总销售额，这是多个销售记录的汇总。无法映射回原始数据：由于聚合函数的结果无法唯一映射回基础表中的特定行，SQL Server 无法确定如何更新基础表中的数据。 在 SQL Server 中，仅包含 GROUP BY 子句的视图通常也不可更新，因为 GROUP BY 子句将多行数据分组成一个单一的结果集，这使得无法将更新操作映射回基础表中的具体行。 为什么仅包含 GROUP BY 子句的视图不可更新 分组的结果：GROUP BY 子句将多行数据分组，每个组仅生成一行结果。无法映射回原始数据：分组后的结果无法唯一映射回基础表中的具体行，因此 SQL Server 无法确定如何将更新操作应用到基础表中的具体行。 在 SQL Server 中，包含 DISTINCT 关键字的视图是不可更新的。DISTINCT 关键字用于去除重复记录，使得每行都是唯一的。这导致 SQL Server 无法唯一映射视图中的行到基础表中的具体行，从而无法进行更新操作。 为什么包含 DISTINCT 关键字的视图不可更新 去除重复行：DISTINCT 关键字去除重复行，使得视图中的每行都是唯一的。无法映射回原始数据：由于 DISTINCT 关键字去除了重复行，视图中的每一行可能对应多个基础表中的行，因此无法确定如何将更新操作应用到基础表中的具体行。 在 SQL Server 中，当视图的 SELECT 语句包含计算字段（计算列）时，该视图通常是不可更新的。这是因为计算字段的值通常是根据其他列的值计算得出的，更新计算字段没有明确的方式映射回基础表中的具体行或列。 为什么包含计算字段的视图不可更新 计算字段的值：计算字段的值是根据其他列的值计算得出的，并不是直接存储在基础表中的独立值。无法映射回原始数据：更新计算字段没有明确的方式映射回基础表中的具体列，因此 SQL Server 无法确定如何将更新操作应用到基础表中的具体行或列。 在 SQL Server 中，基于多表连接的视图通常是不可更新的。这是因为多表连接的视图可能会产生一个组合的数据集，无法唯一地映射回单个基础表中的特定行，从而导致 SQL Server 无法确定如何应用更新操作。 为什么基于多表连接的视图不可更新 数据集的组合：视图基于多个表的连接，生成一个组合的数据集。无法唯一映射：视图中的每一行可能对应多个基础表中的行，无法唯一地映射回单个表中的特定行，因此 SQL Server 无法确定如何应用更新操作 在 SQL Server 中，如果视图不包含基础表中具有非空约束且没有默认值的字段，则该视图不能用于更新数据。这是因为在插入或更新操作时，必须提供这些非空字段的值，而视图中没有这些字段导致无法满足这一要求。或者为这些字段定义默认值，以确保在插入或更新操作时能够提供所有必需的值。。 为什么这种视图不可更新 非空约束：将表中的 列1 和 列3 字段定义为 NOT NULL，因此在插入或更新操作时必须提供这些字段的值。视图不包含非空字段：视图 不包含 列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; |
只是删除了视图中可见的数据，而没有删除在视图中看不到的记录。