本文主要是阅读《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 特性
- 原子性 ——
A
tomicity- 原子性是指在事务结束时,其中所包含的更新处理要么全部执行,要么完全不执行的特性
- 一致性 ——
C
onsistency- 一致性指的是事务中包括的处理,要满足数据库提前设置的约束,如主键约束或者
not null
约束等
- 一致性指的是事务中包括的处理,要满足数据库提前设置的约束,如主键约束或者
- 隔离性 ——
I
solation- 隔离性值的是保证不用食物之间互相不干扰的特性
- 持久性 ——
D
urability- 持久性指的是事务一旦结束,
DBMS
会保证该时刻点的数据状态得以保存的特性
- 持久性指的是事务一旦结束,