首页 > itarticle > 万字长文带你了解SQL更新插入数据的各个方面SQL全面

万字长文带你了解SQL更新插入数据的各个方面SQL全面

admin 12月 07, 2021 0

这是我参与更文挑战的第20天，活动详情查看： 更文挑战

数据更新大体分为插入(INSERT)、删除(DELETE)和更新(UPDATE)三类。此外，还有用以管理数据更新的事务。

数据的插入

  • 使用INSERT语句可以向表中插入数据（行）
  • 将列名和值用逗号隔开，分别括在（）内，这种形式称为清单。
  • 对表中所有列进行INSERT操作时可以省略表名后的列清单。
  • 插入NULL时需要在VALUES子句的值清单中写入NULL。
  • 表中的列设定默认值（初始值），可以在CREATE TABLE语句中为列设置DEFAULT约束实现。
  • 插入默认值可以通过两种方式实现：在INSERT语句的VALUES子句中指定DEFAULT关键字（显式方法）；或省略列清单（隐式方法）。
  • INSERT INTO <table> SELECT从其他表中复制数据；SELECT <values> INTO <table0> FROM <table1>复制表和表中数据。

首先创建表ProductIns，除了sale_price类设置了DEFAULT 0默认值约束。其他和Product完全相同

CREATE TABLE ProductIns
(
    product_id      CHAR(4)      NOT NULL,
    product_name    VARCHAR(100) NOT NULL,
    product_type    VARCHAR(32)  NOT NULL,
    sale_price      INTEGER      DEFAULT 0,
    purchase_price  INTEGER      ,
    regist_date     DATE         ,
    PRIMARY KEY (product_id)
 );
复制代码

INSERT

数据的插入通过INSERT语句实现。

INSERT INTO <TableName>(col1,col2,······) VALUES (value1,value2,······);
复制代码

在创建Product时已经使用过INSERT插入过数据。如下，INSERT插入一条数据

INSERT INTO ProductIns (product_id,product_name, product_type, sale_price, purchase_price, regist_date ) 
    VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
复制代码

字符型和日期型数据要用单引号引起来(如'0009'、'2009-09-20');

将列名和值用逗号隔开，分别包括在()内，这种形式称为清单。表名后面的列清单和VALUES子句中的值清单的列数必须保持一致。上面INSERT语句包含两个清单：

  1. 列清单——(product_id, product_name, product_type, sale_price,purchase_price, regist_date)

  2. 值清单——('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20')

原则上，执行一次INSERT语句会插入一行数据。插入多行数据时需要循环多次执行。

插入语句通常需要DBMS相应的安全权限。

多行INSERT

很多RDBMS都支持一次插入多行数据，这样的功能称为多行INSERT(multi row INSERT)。

如下语法，将多条VALUES子句用逗号分割。

INSERT INTO <TableName>(col1,col2,······) VALUES (value1,value2,······),(value3,value4,······),···;
复制代码

多行INSERT的语法并不适用于所有的RDBMS。该语法适用于DB2、SQL、SQL Server、PostgreSQL和MySQL，但不适用于OracIe。

实际中，一次性插入多条的速度比一条一次插入语句的速度快很多。尤其是大量数据插入时。

Oracle使用如下方式实现多行INSERT

-- Oracle中的多行INSERT
INSERT ALL INTO <TableName> VALUES (value1,value2,······)
            INTO <TableName> VALUES (value3,value4,······)
            ···
            INTO <TableName> VALUES (value5,value6,······)
SELECT * FROM DUAL;
复制代码

比如向ProductIns插入多条数据：

-- Oracle中的多行INSERT
INSERT ALL INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
           INTO ProductIns VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
           INTO ProductIns VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
SELECT * FROM DUAL;
复制代码

DUAL是Oracle特有的一种临时表。"SELECT * FROM DUAL"是临时性的，没有实际意义。

不知为何要在INSERT ALL结尾添加个SELECT语句，查找的例子都是如此，且SELECT不一定是DUAL表，但是都会有SELECT。

列清单的省略

对表进行全列INSERT时，可省略表名后的列清单，VALUES子句的值会默认按照从左到右的顺序赋给每一列。列的顺序是创建表时，定义的字段顺序。

-- 省略列清单
INSERT INTO ProductIns VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
复制代码

此时的限制是必须把所有列的值在值清单中依次列出，并且表的列结构、次序和数量不能变化，否则都可能导致插入出错。

如果第一列为自增的主键Id，在值列表中也可以省略。

这样的插入语句高度依赖于列的定义次序，因此是不安全和不推荐的写法。为了保证语句的正确执行，通常必须指定列清单和值清单。

插入NULL

值清单中(对应的插入NULL的列)直接写NULL即可。

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
复制代码

插入NULL的列不能设置NOT NULL约束。

插入默认值

在创建表的CREATE TABLE语句中使用DEFAULT约束设定默认值；这样可以使用INSERT语句插入默认值(初始值)。

开头ProductIns表创建语句中，DEFAULT 0就是指定默认值约束。DEFAULT <default_value>

有默认值约束的列，在INSERT语句中可以自动为列赋值。默认值的插入分为显式和隐式：

显式插入默认值

在VALUES子句中指定DEFAULT关键字

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
            VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
复制代码

隐式插入默认值

在列清单和VALUES中省略设定了默认值的列,在插入时就会隐式的给该列设置为默认值。

INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date) 
            VALUES ('0007', '擦菜板', '厨房用具', 790, '2009-04-28');
复制代码

推荐使用显式的方式，这样可以一目了然的知道哪一列使用了默认值。

省略INSERT语句中的列名，会自动设定为该列的默认值。没有默认值时，如果列可为NULL会设定为NULL；如果设置了NOT NULL约束，则插入会报错。

INSERT INTO SELECT从其他表中复制数据

将SELECT语句的结果插入到表中：

INSERT INTO <TableName1>(<col1>,<col2>...) 
SELECT <value1,value2...> FROM <TableName2>;
复制代码

从TableName2查询数据插入到TableName1。要求目标表必须存在，且查询插入的数据要和目标列的数据类型相同或兼容。

删除ProductIns表中所有数据。从Product表中选取数据复制到ProductIns。

INSERT ... SELECT语句，将查询结果插入到表中。

INSERT INTO ProductIns 
SELECT * FROM Product;

-- 或指定列清单 
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
  FROM Product;
复制代码

INSERT中的SELECT语句可结合WHERE或者GROUP BY等任何合法的子句使用(使用ORDER BY子句没有任何意义，因为无法保证表内部记录的排列顺序)。对于相关的表之间存取数据非常方便。

如下，使用含有GROUP BY的SELECT语句插入数据到另一个表中：

-- 商品种类表。根据种类汇总
CREATE TABLE ProductType(
 product_type        VARCHAR(32)  NOT NULL,
 sum_sale_price      INTEGER,
 sum_purchase_price  INTEGER,
 PRIMARY KEY (product_type)
);

-- 从Product表中选取数据插入到ProductType
INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
  FROM Product
 GROUP BY product_type;
复制代码

SELECT INTO FROM表复制，从其他表复制表字段和数据

有一种从其他表查询数据到不存在的表中的方式(不存在的表会别创建)，如下，查询会创建表并插入数据

SELECT * 
INTO <TableName1>
FROM <TableName2>;
复制代码

比如，查询插入到不存在的表ProductCopy

SELECT * INTO ProductCopy
FROM Product;
复制代码

SELECT ... INTO的用法仅PostgreSQL和SQL Server支持，DB2、MySQL/Mariadb不支持。Oracle未测试，似乎不支持。

虽说SELECT ... INTO的查询会创建表，但是创建的表并不和源表完全一致，两者只是在表字段上相同，相应的字段约束和索引并不会创建(比主键约束等)。

create table as和create table like表复制，从其他表复制数据或表结构

这两个建表语句，目前已知在PostgreSQL、MySQL/MariaDB中可用，SQL Server不支持。

oracle只支持create table as，作用和SELECT ... INTO一样，不会创建约束和索引。

以下为MySQL/MariaDB中的介绍

create table as创建表和字段并插入数据

create table as仅仅会复制表的字段，不会创建相应的约束和索引等。

Mysql/MariaDB下可以通过Create table new_table_name as (Select * from old_table_name);实现SELECT INTO功能。

as和()可以省略，也可以写为CREATE TABLE ... SELECT。

-- 三者是一样的
Create table new_table_name as (Select * from old_table_name);
Create table new_table_name as Select * from old_table_name;
Create table new_table_name Select * from old_table_name;
复制代码

如，创建和Product一样的列的表ProductAS3，并插入数据：

CREATE TABLE ProductAS3 SELECT * FROM Product;
复制代码

通过限制出入的数量，实现仅复制表字段，不插入数据。

Create table new_table_name Select * from old_table_name LIMIT 0;
-- 或 
Create table new_table_name as Select * from old_table_name where 1=2;
复制代码

CREATE TABLE ... SELECT还可以正常定义一些列，以及从SELECT中添加其他列，在查询中未命名的列将放置在其他列之前。如：

CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM
复制代码
SELECT 5 AS b, c, d FROM another_table;
复制代码
复制代码

CREATE TABLE ... AS SELECT或SELECT ... INTO可以说成是复制表的字段结构及数据。

SELECT INTO是测试新SQL语句前进行表复制的很好的方式。先进行复制，在复制的数据上测试SQL代码，而不会影响实际的数据。

create table like创建表(完全相同的表，整个表结构完全一样)

create table like用来创建一个和源表一模一样的新表，但是不包含数据，表结构相同

CREATE TABLE ... LIKE将创建一个和源表的定义(definition)完全一样的表，包括列，索引和表选项(table options)。但不会创建外键定义以及原始表上指定的任何DATA DIRECTORY或INDEX DIRECTORY表选项。

语法：

create table new_table_name like old_table_name;
复制代码

如：

create table ProductLike like Product;
复制代码

PostgreSQL中create table like/create table as和MySQL中的基本一致，但是功能更强大，可以看一下这篇介绍PostgreSQL 动态表复制（CREATE TABLE...LIKE），或官网的资料

数据的删除

  • DROP TABLE将表完全删除。

  • DELETE删除表中的数据，表依旧存在。

  • DELETE语句中使用WHERE子句删除符合条件的数据行。

DELETE

DELETE FROM <TableName> [WHERE ...];
复制代码

删除Product表全部数据

DELETE FROM Product;
复制代码

DELETE语句的删除对象不是表或者列，而是记录(行)。正是因为DELETE删除的对象行而不是列，所以DELETE后不能指定列名，无法只删除部分列的数据。DELETE *也是错误的。

使用UPDATE删除指定的列，设置列为NULL。

搜索型DELETE

DELETE可以通过WHERE子句指定删除条件。这种通过WHERE子句指定删除对象的DELETE语句称为搜索型DELETE

DELETE FROM <TableName>
 WHERE <search_condition>;
复制代码

删除销售单价（sale_price）大于等于4000的数据

DELETE FROM Product
 WHERE sale_price >= 4000;
复制代码

关于TRUNCATE

标准SQL中从表中删除数据的只有DELETE语句。但是几乎所有DBMS都支持TRUNCATE语句删除。OracIe、SQLServer、PostgreSQL、MySQL/MariaDB和DB2。

TRUNCATE只能删除表中的全部数据。由于TRUNCATE不记录(事务)日志（不记录数据的变动），执行速度会非常快。

TRUNCATE <table_name>;
复制代码

不同DBMS对TRUNCATE定义不同，在OracIe中，把TRUNCATE定义为DDL，而不是DML。因此，Oracle中的TRUNCATE不能使用ROLLBACK。执行TRUNCATE的同时会默认执行COMMIT操作。

目前所知，**在OracIe、MySQL/MariaDB中TRUNCATE语句执行后无法回滚（**即使显式的指定开启事务并且未提交）。SQL Server、PostgreSQL中可以执行ROLLBACK回滚。

如下是在PostgreSQL中执行TRUNCATE回滚的测试：

shop=# begin transaction;
BEGIN
shop=# select * from chars;
 chr
-----
 1
 2
 3
 10
 11
 222
(6 行记录)
shop=# truncate chars;
TRUNCATE TABLE
shop=# select * from chars;
 chr
-----
(0 行记录)
shop=# rollback;
ROLLBACK
shop=# select * from chars;
 chr
-----
 1
 2
 3
 10
 11
 222
(6 行记录)
复制代码

drop、truncate和delete的区别（此处具体区别本人未做完全验证）

  1. DELETE语句基于行从表中删除数据。可与根据WHERE条件过滤要删除的行(确认)

  2. TRUNCATE TABLE用于一次性地从表中删除所有的数据，TRUNCATE TABLE语句实际上就是DELETE语句，不过操作不记录各个行的删除(但是该语句会被记录)。在删除的过程中不会激活与表有关的删除触发器(创建触发器进行测试即可)。执行速度快。(确认)

在PostgreSQL中，除了INSERT，UPDATE，DELETE触发器外，还有TRUNCATE触发器，要想执行TRUNCATE时触发，可以创建对应触发器。

“TRUNCATE TABLE语句实际上就是DELETE语句，不过操作不记录各个行的删除”，此句出自CREATE TRIGGER (Transact-SQL)

  1. SQL Server、PostgreSQL中，TRUNCATE TABLE和DELETE语句都会被写入事务日志，这一点，通过备份数据库，执行TRUNCATE后再备份事务日志，然后进行还原，即可进行验证。并且在显式事务中，都可以进行回滚。(仅确认SQL Server下的情况)

目前确认的是SQL Server下会记录TRUNCATE TABLE语句到事务日志。其他DBMS是应该也会记录，需要后续确认。

此处测试不完全，仅进行了SQL Server 2014之后版本的测试，之前版本是否会写入事务日志暂时不知，查资料也是写入的(2005之前应该不确定)。

MySQL/MariaDB、PostgreSQL下是否写入事务日志后。应该可以直接查看事务日志内容。

SQL Server、PostgreSQL中TRUNCATE TABLE操作可以被回滚。

  1. 在OracIe、MySQL/MariaDB中TRUNCATE TABLE语句执行后无法回滚。执行时会产生一个隐式提交(implicit commit)。(确认)

  2. 当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小，DELETE操作不会减少表或索引所占用的空间。

  3. drop语句删除的是表，而不是表中的数据，并将表所占用的空间全释放掉。即TRUNCATE和DELETE只删除数据，DROP则删除整个表（结构和数据）(确认)

  4. TRUNCATE只能对TABLE；DELETE可以是table和view。

  5. drop语句删除表的结构、被依赖的约束（constrain)、触发器（trigger)、索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

从SQL Server 2016 (13.x)版本开始，TRUNCATE语句可以根据某一分区进行表数据的清空。

  1. truncate table在功能上与不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 效率高速度快，且使用的系统和事务日志资源少。DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

  2. TRUNCATE TABLE删除表数据后，新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。

  3. 对于被FOREIGN KEY约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE子句的DELETE语句。(仅SQL Server确认)

SQL Server中可以truncate外键引用自身的表。 “Are referenced by a FOREIGN KEY constraint. You can truncate a table that has a foreign key that references itself.”

存在被外键引用、参与索引视图、使用事务复制或合并复制发布、EDGE约束引用、系统版本化的临时表(system-versioned temporal table)等情况的表，不能使用TRUNCATE TABLE。

PostgreSQL中，通过CASCADE关键字，可以实现级联删除外键引用的表。

TRUNCATE othertable CASCADE;

PostgreSQL的TRUNCATE可以省略后面的TABLE，并且可以同时删除多个表(SQL Server、MySQL/MariaDB中一次只能删除一个表)

shop=# create table t1(c char not null);
CREATE TABLE
shop=# create table t(c char not null);
CREATE TABLE
shop=# truncate table t1,t;
TRUNCATE TABLE
shop=#
复制代码

MySQL/MariaDB中TRUNCATE后也可以省略关键字TABLE

本部分区别出自drop、truncate和delete的区别，进行了删减和过滤。

如下，为SQL Server TRUNCATE TABLE中介绍的TRUNCATE TABLE对比DELETE语句的不同：

  • 占用事务日志空间少。

    The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

  • 通常使用较少的锁。

    When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

  • 无一例外，表中剩余零页。

    After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

A TRUNCATE TABLE operation can be rolled back.

各个数据库管理系统，对Truncate的实现有不小的区别，因此其具体和DELETE语句的不同，可能需要参考具体DBMS的文档说明。这个问题可以单开一个话题说明。

数据的更新

  • UPDATE语句=更改（更新）表中的数据。使用WHERE指定搜索条件，更新部分数据行。
  • UPDATE可以将列的值更新为NULL。
  • UPDATE的SET子句中使用逗号分隔，可以更新多个列。

UPDATE

UPDATE <table_name>
  SET <col>=<express>;
复制代码

将表中登记日期全部改为"2020-10-10"

UPDATE Product
   SET regist_date = '2020-10-10';
复制代码

搜索型UPDATE

执行UPDATE时也可以指定WHERE，这种指定更新对象的UPDATE语句称为搜索型UPDATE语句

UPDATE <table_name>
  SET <col>=<express>;
  WHERE <condition>;
复制代码

如下，将商品种类（product_type）为厨房用具的记录的销售单价（sale_price）更新为原来的10倍：

UPDATE Product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';
复制代码

更新为NULL

使用UPDATE可以将列更新为NULL，也被称为NULL清空。

将商品编号为0008的数据登记日期更新为NULL：

UPDATE Product
   SET regist_date = NULL
 WHERE product_id = '0008';
复制代码

多列更新

UPDATE的SET子句支持同时更新多个列。

使用逗号对列进行分隔排列，实现更新多个列：

-- 使用逗号对列进行分隔排列
UPDATE Product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';
复制代码

将列用小括号括起来的清单形式，更新多个列：

-- 将列用小括号括起来的清单形式
UPDATE Product
   SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
 WHERE product_type = '厨房用具';
复制代码

第二种列清单和值清单方式更新多个列的写法，只有PostgreSQL、DB2支持，SQL Server、MySQL/MariaDB中均不支持。

DELETE和UPDATE语句千万注意不要省略WHERE条件。

更新和删除的指导原则

  • 除非确定更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  • 保证每个表都有主键，并尽量在WHERE子句中使用它。
  • 在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  • 使用强制实施引用完整性的数据库，这样DBMS将不允许删除其数据与其他表相关联的行。
  • 如果DBMS支持添加防止执行不带WHERE子句的UPDATE或DELETE语句的约束，应尽量添加。
微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能