SQL基础之数据更新

本文主要是阅读《SQL 基础教程》这本书总结而来，有需求的话可以阅读原著。

---

1. 数据的插入 —— insert

• 使用insert语句插入数据，原则上insert语句每次执行一行数据的插入
• 列名和值用逗号隔开，分别括在括号，这种形式称之为清单
• 对表中所有列进行insert操作时可以省略表明后的列清单
• 插入null时需要valuse自己的值清单中写入null
• 如果需要在插入的时候使用默认值，就必须在创建表的时候指定默认值，设置default约束来设定
• 插入默认值可以通过两种方式实现，指定default关键字或者省略列清单
• 使用insert ... select可以从其他表中复制数据

---

1.1 语法格式

insert into <表名> (列1, 列2, ...) values (值1, 值2, ...);

---

1.2 基本插入方式

• 原则上，执行一次insert语句会插入一行数据
• 但是，有些关系型数据库中可以进行多行插入
• 多行插入就是在 valuse 之后添加多组的值清单，以逗号分割

# 实例插入数据(列清单和值清单)
mysql> insert into Shohin
    -> (shohin_id, shohin_mei, shohin_bunrui,
    -> hanbai_tanka, shiire_tanka, torokubi) values
    -> ('0009', '鞋子', '衣服', 1200, 500, '2010-09-01');

---

1.3 列清单省略的插入方式

• 只需要将希望的数值进行插入，省略的列默认填充null
• 但是在定义表的时候，设定为not null的列必须存在

# 省略列清单
mysql> insert into Shohin
    -> (shohin_id, shohin_mei,
    -> shohin_bunrui, shiire_tanka) values
    -> ('0009', '鞋子', '衣服', 500);

---

1.4 插入 null

• 在插入的时候，对于那些在列清单指定了的列，在valuse中不存在使用null填充

# 插入null
mysql> insert into Shohin
    -> (shohin_id, shohin_mei, shohin_bunrui,
    -> hanbai_tanka, shiire_tanka, torokubi) values
    -> ('0010', '鞋子', '衣服', null, 500, null);

---

1.5 插入默认值

• 通过显示方式设定默认值，使用 default 关键字
• 通过隐藏方式插入默认值，对于列的值也进行省略

# 通过显示方式设定默认值
mysql> insert into Shohin
    -> (shohin_id, shohin_mei, shohin_bunrui,
    -> hanbai_tanka, shiire_tanka, torokubi) values
    -> ('0009', '鞋子', '衣服', default, 500, '2010-09-01');

# 通过隐藏方式插入默认值
mysql> insert into Shohin
    -> (shohin_id, shohin_mei, shohin_bunrui,
    -> shiire_tanka, torokubi) values
    -> ('0009', '鞋子', '衣服', 500, '2010-09-01');

---

1.6 从其他表中复制数据

• 格式语法

  • insert ... select ...

• 备份表中数据

# 创建复制表ShohinCopy
mysql> create table ShohinCopy(
    -> shohin_id char(4) not null,
    -> shohin_mei varchar(100) not null,
    -> shohin_bunrui varchar(32) not null,
    -> hanbai_tanka integer,
    -> shiire_tanka integer,
    -> torokubi date,
    -> primary key (shohin_id));
Query OK, 0 rows affected (0.01 sec)

# 复制Shohin中的数据到ShohinCopy表中
mysql> insert into ShohinCopy
    -> (shohin_id, shohin_mei, shohin_bunrui,
    -> hanbai_tanka, shiire_tanka, torokubi)
    -> select shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka,shiire_tanka, torokubi
    -> from Shohin;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

# 使用如下语句进行查询
mysql> select * from ShohinCopy;

• 对数据处理之后存放

# 创建新表
mysql> create table ShohinBunrui
    -> (shohin_bunrui varchar(32) not null,
    -> sum_hanbai_tanka integer,
    -> sum_shiire_tanka integer,
    -> primary key (shohin_bunrui));
Query OK, 0 rows affected (0.00 sec)

# 插入数据库
mysql> insert into ShohinBunrui (
    -> shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka)
    -> select shohin_bunrui, sum(hanbai_tanka), sum(shiire_tanka)
    -> from Shohin
    -> group by shohin_bunrui;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 查看结果
mysql> select * from ShohinBunrui;
+---------------+------------------+------------------+
| shohin_bunrui | sum_hanbai_tanka | sum_shiire_tanka |
+---------------+------------------+------------------+
| 衣服        |             5000 |             3300 |
| 办公用品  |              600 |              320 |
| 厨房用具  |            11180 |             8590 |
+---------------+------------------+------------------+
3 rows in set (0.00 sec)

---

2. 数据的删除 —— drop 和 delete

2.1 数据删除分类

• drop语句可以将表完全删除
• delete语句会留下表而删除表中的数据

---

2.2 delete 基本语法

• 语法格式

delete from <表名>;

• 实例演示

# 我们来删除之前创建的ShohinBunrui的表
mysql> delete from ShohinBunrui；

mysql> select * from ShohinBunrui;
Empty set (0.00 sec)

---

2.3 指定删除对象的 delete 语句

• 语法格式

mysql> delete from <表名> where <条件>;

• 实例演示

# 删除销售单价大于等于4000的数据
delete from Shohin where hanbai_tanka >= 4000;

---

3. 数据的更新 —— update

3.1 基本语法

• 语法格式

mysql> update <表名> set <列名> = <表达式>;

• 实例演示

mysql> update <表名> set <列名> = <表达式>;

---

3.2 指定条件的 update 语句

• 语法格式

mysql> update <表名> set <列名> = <表达式> where <条件>;

• 实例演示

# 将商品种类为厨房用具的记录的销售单位更新为原本的10倍
mysql> update Shohin
    -> set hanbai_tanka = hanbai_tanka * 10
    -> where shohin_tanka = '厨房用具';

---

3.3 使用 null 进行更新

• 使用update语句可以将值清空为null，但是只限制于未设置not null的约束列

# 将商品编号为0008的数据的登记日期更新为null
mysql> update Shohin set torokubi = null where shohin_id = '0008';

---

3.4 多列更新

# 能够正确执行的繁杂的update语句
mysql> update Shohin
    -> set hanbai_bunrui = hanbai_bunrui * 10
    -> where shohin_bunrui = '厨房用具';

mysql> update Shohin
    -> set shiire_bunrui = shiire_bunrui / 2
    -> where shiire_bunrui = '厨房用具';

# 优化1
mysql> update Shohin
    -> set hanbai_bunrui = hanbai_bunrui * 10
    -> set shiire_bunrui = shiire_bunrui / 2
    -> where shohin_bunrui = '厨房用具';

# 优化2
mysql> update Shohin
    -> set (hanbai_bunrui, shiire_bunrui) = (hanbai_bunrui * 10, shiire_bunrui / 2)
    -> where shohin_bunrui = '厨房用具';

---

4. 事务

• 失误就是需要在同一个处理单元中执行的一系列更新处理的集合

4.1 创建事务

• 注意事项

  • 不用的关系型数据库会没有事务开始语句的，但是结束语句都必须有

• 语法格式

事务开始语句；
    DML语句1；
    DML语句2；
    DML语句3；
    ...
事务结束语句（commit或者rollback）

• 实例演示

begin transaction;
    update Shohin
    set hanbai_bunrui = hanbai_bunrui * 10
    where shohin_bunrui = '厨房用具';

    update Shohin
    set hanbai_bunrui = hanbai_bunrui * 10
    where shohin_bunrui = '厨房用具';
commit;

---

2.2 事务结束

commit

• 提交处理
• 一旦提交，就无法恢复道是无开始前的状态了

roolback

• 取消处理
• 一旦回滚，数据库就会恢复到事务开始之前的状态

事务处理方式

• 方式一：每条SQL语句就是一个事务（自动提交模式）
• 方式二：知道用户执行commit或者rollback为止算作一个事务
• MySQL、SQL Server和PostgreSQL为方式一，Oracle为方式二

---

2.3 ACID 特性

• 原子性 —— Atomicity
  • 原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行的特性
• 一致性 —— Consistency
  • 一致性指的是事务中包括的处理，要满足数据库提前设置的约束，如主键约束或者not null约束等
• 隔离性 —— Isolation
  • 隔离性值的是保证不用食物之间互相不干扰的特性
• 持久性 —— Durability
  • 持久性指的是事务一旦结束，DBMS会保证该时刻点的数据状态得以保存的特性