plsql 复制表结构到指定表空间_数据库知识总结—(八)数据库&表的CRUD

一.创建表

本节用到的表结构如下：

customers表

vendors表

orderitems表

一般有两种创建表的方法：

使用具有交互式创建和管理表的工具；或者直接用MySQL语句操纵。

（1）表创建基础

为利用create table创建表，必须给出下列信息：

（一）新表的名字，在create table之后给出。（二）表列的名字和定义，用逗号隔开。

//

表的主键可以在创建表时用PRIMARY KEY关键字指定。如果想在一个表不存在时创建它，应该在表名后给出if not exists。这样做不检查已有表的模式是否与你打算创建的表模式相匹配，它只是查看表名是否存在，并且仅在表名不存在时创建它。

（2）使用NULL值

每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

例如下面这个创建混合了NULL和NOT NULL列的表。该语句创建供应商表，供应商ID和供应商名字列是必需的，因此指定为NOT NULL，其余几个列全部允许NULL，所以不指定NOT NULL。

//

（3）主键

主键值必需唯一，即表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。主键可以在创建表时定义，或者在创建表之后定义。

主键用primary key(vend_id)的语句定义，为创建多个列组成的主键，应以逗号分隔的列表给出各列名：

//

（4）使用auto_increment

举一个例子：

cust_id 

该语句告诉MySQL，cust_id列每当增加一行时进行自动增量,每次执行一个insert操作时，mysql自动也对该列增量，给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id从而可以用作主键值。每个表只允许一个auto_increment列，而且它必须被索引。使用 last_insert_id()函数可以获取auto_increment列的值。例如：

select 

（5）指定默认值

默认值用create table语句的列定义中的default关键字指定,许多数据库开发人员使用默认值不是null列。

Create 

（6）引擎类型

Create table语句全部都以ENGINE=InnoDB语句结束，MySQL有一个具体管理和处理数据的内部引擎。当你使用CREATE TABLE语句时，该引擎具体创建表，而当你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。但是MySQL又与其他DBMS不一样，它具有多种引擎，且打包多个引擎，这些引擎都隐藏在MySQL服务器内，全部都能执行create和select等命令。

如果省略'ENGINE=语句'，则使用默认引擎，大多数SQL语句都会默认使用它。但不是所有语句都默认使用它，因此'ENGINE=语句'很重要。以下是需要知道的引擎：

（一）InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。

（二）MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

（三）MEMORY在功能等同于MyISAM，但由于数据存储在内存，速度很快

在我们所使用的样例表中都使用InnoDB。原因是希望支持事务处理，但也需要在productnotes中支持全文本搜索。引擎类型可以混用，MyISAM由于其性能和特性可能是最受欢迎的引擎。但如果不需要可靠的事务处理，可以使用其他引擎。

二.更新表

为更新表定义，可以使用alter table语句。但是理想状态下，当表中存储数据后，该表就不应该在被更新。为了使用ALTER TABLE更改表结构，必须给出下面信息：

（一）在ALTER TABLE之后给出要更改的表名

（二）所做更改的列表。

//

复杂表结构的更改一般需要手动删除过程，涉及如下步骤：

（一）用新的列布局创建一个新表。

（二）使用insert select语句从旧表复制数据到新表。

（三）检验包含所需数据的新表。

（四）重命名旧表，再用旧表原来的名字重命名新表。

（五）根据所需，重新创建触发器、存储过程、索引和外键。

三.删除表和重命名表

删除表使用drop table即可；使用rename table可以重命名一个表：

//

四.插入数据

Insert是用来插入行到数据库表的，插入可用几种方式使用：

（一）插入完整的行；（二）插入行的一部分；

（三）插入多行； （四）插入某些查询的结果。

//

此例子插入一个新客户到customers表，存储到表列中的数据由values子句给出，对每个列必须提供一个值。若列没有值应使用null，每个列必须以它们在表定义中出现的次序填充。第一列cust_id也为null，因为每次插入一个新行时，该列都是自动增量。

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的插入语句高度依赖表中列的定义次序，并且还依赖于其次序容易获得的信息。即使很容易获得这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。因此编写依赖于特定列次序的sql语句很不安全。编写更安全的方法如下：

Insert 

该例子在表名后的括号中列出了列名，在插入行时，用values中的相应值填入列表中的对应项。这样做的好处是，即使表的结构改变，该insert语句仍然可以正常工作。即使插入的次序改变，因为给出了列名，所有插入结果仍然正确。

使用这种语法还可以省略列，因为这种插入方式可以只给某些列提供值，给其他列不提供值，省略的列必须满足以下某个条件：

（一）该列定义为允许默认值

（二）在表定义中给出默认值，这说明如果不赋值，将使用默认值。

如果数据检索是最重要的，则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级:Insert low_priority into…..

//

INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT，它是由一条INSERT语句和一条SELECT语句组成的。

例如从另一个表custnew中合并客户列表到customers中。Select语句从custnew检索出要插入的值，插入的行数依赖于新表custnew中有多少行，若新表为空则没有数据插入。INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据

//

五.更新和删除数据

(1) 更新数据

为了更新表中数据，可使用update语句，采用两种方式使用update：

（一）更新表中特定行；（二）更新表中所有行。

基本的update语句由三部分组成：

（一）要更新的表；（二）列名和它们的新值；（三）确定要更新行的过滤条件。

//

UPDATE语句总是以要更新的表的名字开始,SET命令用来将新值赋给被更新的列,以WHERE子句结束，它告诉MySQL更新哪一行。在此例子中要更新的表为customers,SET设置了cust_email的值，若没有WHERE子句，设置的这个电子邮件值将更新customers中所有行。

//

（2）删除数据

为了删除表中数据，可使用delete语句，采用两种方式使用delete:

（一）从表中删除特定行。（二）从表中删除所有行。

// 

Delete删除整行而不是列，如果想从表中删除所有行，可以使用truncate table（实际上是删除原来的表并重新创建一个表，而不是逐行删除表数据）

（3）更新和删除数据的一些规则

（一）如果不需要更新和删除每一行，那么一定要使用带WHERE子句的UPDATE或DELETE语句。

（二）保证每个表都有主键，尽可能像WHERE子句那样使用它。

（三）在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

（四）使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。