SQL基础总结(九)：插入更新删除行及创建更新删除表

本系列blog源自前年写的SQL学习笔记，汇总一下发上来。(1月份发了前三篇笔记，原以为后面的笔记误操作删了，今天在硬盘里又找到了，一起发上来)

--------------------------------

插入更新删除行

 

使用INSERT插入行

INSERT INTO table VALUES(value1,value2,value3…);

INSERT INTO table(column1,column2,column3) VALUES(value1,value2,value3);

 

INSERT INTO table(column1,column2,column3) subquery;

Subquery中列的数量必须等于INSERT的列的数量。

Subquery中SELECT 返回的结果为空是合法的，而且并不插入行。

 

可以通过视图插入行。

 

使用UPDATE更新行

UPDATE table SET column = expr [WHERE search_condition];

search_condition可以是子查询或者WHERE条件。

 

通过CASE更新，例：

UPDATE titles SET prise = prise*

 CASE type

   WHEN ‘history’ THEN 1.10

   WHEN ‘psychology’ THEN 1.12

   ELSE 1

END;

 

更新中使用子查询：

UPDATE titles SET pub_id=(SELECT pub_id FROM publishers WHERE pub_name=’aaa’)

WHERE pub_id=(SELECT pub_id FROM publishers WHERE pub_name=’bbb’);

DBMS使用引用列更新之前的值计算SET或WHERE子句中的表达式。

可以通过视图更新行。

 

SQL2003引入了MERGE语句作为在一条语句中结合运用多个UPDATE和INSERT操作的简便方法。ORACLE、DB2支持MERGE。

 

使用DELETE删除行

DELETE FROM table [WHERE search_condition];

search_condition可以是WHERE条件或子查询条件。

 

如果想删除表中的所有行，TRUNCATE语句比DELETE要快且使用的系统资源少。TRUNCATE不是标准SQL的一部分，但部分DBMS支持它。TRUNCATE清空整张表。使用TRUNCATE，代价是有错误就无法恢复到变化前（回滚）。

TRUNCATE TABLE table;

 

创建、更新和修改表

 

约束：

NOT NULL阻止向列中插入空值

PRIMARY KEY设置表的主键列

FOREIGN KEY设置表的外键列

UNIQUE阻止向列中插入重复的值

CHECK使用逻辑（布尔）表达式限制插入列中的值

 

列约束：是列定义的一部分，它设置作用于列的条件。

表约束：有别于列定义并加强于表中多个列条件，可以在一个表约束中包含多个列。

如果不显示命名约束，DBMS将自动产生并分配约束的名称。应该用CONSTRAINT子句分配自定义的约束名。约束名在一个表中必须是唯一的。

CONSTRAINT constraint_name;

 

使用NOT NULL禁止空值

 

使用DEFAULT确定默认值

在CREATE TABLE列定义中使用关键字DEFAULT定义默认值。

 

使用PRIMARY KEY指定主键

主键不允许为空值。

每个表只能有一个主键。

在CREATE TABLE的定义中使用关键字PRIMARY KEY定义主键约束。

一个表不能有一个以上的主键约束。

主键约束总是显示命名，使用CONSTRAINT子句来实现。

所有主键列都是NOT NULL的。

 

使用FOREIGN KEY指定外键

外键与引用表的主键或候选主键建立直接关系，于是外键的值被限于已经存在的父键值。这个约束被称为引用完整性。

外键不像主键，允许空值。

表可以有零个或多个外键。

外键值在表中通常不是唯一的。

在CREATE TABLE的定义中使用关键字FOREIGN KEY或REFERENCES来定义外键约束。

 

当试图更新或删除外键值所引用的键值（在父表中）时，SQL允许定义DBMS要采取的行为。要触发一个引用行为，在FOREIGN KEY约束中使用ON UPDATE或ON DELETE子句。

 

ON UPDATE action：当试图UPDATE一个行中被其它表的外键引用的键值（在父表中）时，action为下列4个值中的一个：

CASCADE:更新依赖的外键值为新的父表值。

SET NULL:将依赖的外键值改为空值。

SET DEFAULT:将依赖的外键值改为默认值。

NO ACTION:当违反外键约束时产生一个错误提示，这是默认行为。

 

ON DELETE action：当试图DELETE一个行中被其它表的外键引用的键值（在父表中）时，

action为下列4个值中的一个：

CASCADE:删除所包含的外键值与要删除的主键值匹配的行。

SET NULL:将依赖的外键值改为空值。

SET DEFAULT:将依赖的外键值改为默认值。

NO ACTION:当违反外键约束时产生一个错误提示，这是默认行为。

 

使用UNIQUE确保值唯一

唯一约束和主键约束区别：唯一列可以包含空值、表可以包含多个唯一列。

唯一约束可以禁用空值。

使用CHECK检查约束

通常检查最大值、最小值、具体值、一定范围的值。

 

CREATE TABLE table(

Column1 data_type NOT NULL,

Column2 data_type NOT NULL,

Column3 data_type DEFAULT ‘’,

Column4 data_type ,

Column5 data_type NOT NULL UNIQUE,

CONSTRAINT constraint_name_pk PRIMARY KEY(Column1,Column2),

CONSTRAINT constraint_name_fk FOREIGN KEY(Column4) REFERENCES ref_table(id) ON UPDATE cascade,

CONSTRAINT constraint_name_check CHECK(Column5 IN (‘A’,’B’,’C’,’D’))

);

 

使用CREATE TEMPORARY TABLE创建临时表

基础表：持久保存数据直到显示删除表为止。SQL也允许创建临时表来存储中间结果。

临时表是在会话或事务结束时DBMS能自动清空的表（数据和表都被删除）。

临时表最初没有行，可以像在基本表那样插入、更新和删除行。

如果创建了巨大的临时表，可以自己删除而不是等DBMS来释放内存。

CREATE {LOCAL|GLOBAL} TEMPORARY TABLE table(

//与基本表相同。

);

LOCAL表明是局部临时表，仅用户自己可用，当DBMS进程结束时就会消失。

GLOBAL表明是全局临时表，可以被其它用户访问，当DBMS会话和其它引用它的任务结束时，就会消失。

 

对于所有DBMS，查阅文档了解DBMS如何处理与基本表名字相同的临时表。

在某些情况下，临时表会在被删除之前，隐藏或封闭同名的基本表。

 

使用CREATE TABLE AS利用已存在表创建新表

CREATE TABLE new_table AS subquery;

Subquery是一个返回插入到new_table表行的SELECT语句。使用subquery的结果决定new_table的结构和列的顺序。

不管SELECT引用的数据表有多少，CREATE TABLE AS只向一个表中插入数据。新表不能和已存在的表同名。

 

为了可移植性，不要使用CREATE TABLE AS或SELECT INTO。应该使用CREATE TABLE创建新的空表，然后使用INSERT SELECT添加值。

 

使用ALTER TABLE修改表

ALTER TABLE table alter_table_action;

alter_table_action为：

ADD COLUMN column type [constraint];

ALTER COLUMN column SET DEFAULT expr;

DROP COLUMN column [RESTRICT|CASCADE];

ADD table_ constraint;

DROP CONSTRAINT constraint_name;

 

使用DROP TABLE删除表

可以删除基本表和临时表。

删除表就意味着删除了表的结构、数据、索引、约束、授权等。

删除表并未删除引用这个表的视图。

DROP TABLE table;

 

标准SQL允许指明RESTRICT（限制）或CASCADE（级联）删除行为，RESTRICT（安全的）防止删除视图或别的约束引用的表。CASCADE（不安全的）引发引用对象随着表一同被删除。