大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

数据库学习笔记——17创建和操纵表

从这节课开始,主要结合《MySQL必知必会》学习

1 创建表

1.1 表创建基础

为利用 CREATE TABLE 创建表,必须给出下列信息:

  • 新表的名字,在关键字 CREATE TABLE 之后给出;
  • 表列的名字和定义,用逗号分隔。
CREATE TABLE customers(
cust_id INT NOT NULL auto_increment,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) NULL,
cust_city CHAR(50) NULL,
cust_state CHAR(5) NULL,
cust_zip CHAR(10) NULL,
cust_country CHAR(50) NULL,
cust_contact CHAR(50) NULL,
cust_email CHAR(255) NULL,
PRIMARY KEY(cust_id)
) ENGINE = INNODB;

1.2 使用NULL值

NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受该列没有值的行,换句话说,在插入或更新行时,该列必须有值。

每个表列或者是 NULL 列,或者是 NOT NULL 列,这种状态在创建时由表的定义规定。请看下面的例子:

CREATE TABLE orders (
order_num INT NOT NULL auto_increment,
order_date datetime NOT NULL,
cust_id INT NOT null,
PRIMARY KEY (order_num)
)ENGINE = INNODB; 

每个列的定义都含有关键字 NOT NULL ,这将会阻止插入没有值的列。如果试图插入没有值的列,将返回错误,且插入失败。
下一个例子将创建混合了 NULL 和 NOT NULL 列的表:

CREATE TABLE vendors (
vend_id INT NOT NULL auto_increment,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) NULL,
vend_city CHAR(50) NULL,
vend_state CHAR(5) NULL,
vend_zip CHAR(10) NULL,
vend_country CHAR(50) NULL,
PRIMARY KEY (vend_id)
)ENGINE = INNODB;

供应商ID和供应商名字列是必需的,因此指定为 NOT NULL 。其余5个列全都允许 NULL 值,所以不指定NOT NULL 。 NULL 为默认设置,如果不指定 NOT NULL ,则认为指定的是 NULL 。

1.3指定默认值

如果在插入行时没有给出值,MySQL允许指定此时使用的默认值。
默认值用 CREATE TABLE 语句的列定义中的 DEFAULT 关键字指定。

请看下面的例子:

CREATE TABLE orderitems (
order_num INT NOT NULL,
order_item INT NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INT NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL,
PRIMARY KEY (order_num,order_item)
)ENGINE=INNODB;

这条语句创建包含组成订单的各物品的 orderitems 表(订单本身存储在 orders 表中)。 quantity 列包含订单中每项物品的数量。在此例子中,给该列的描述添加文本 DEFAULT 1 指示MySQL,在未给出数量的情况下使用数量 1 。

不允许函数
与大多数DBMS不一样,MySQL不允许使用函数作为默认值,它只支持常量。

使用默认值而不是 NULL 值
许多数据库开发人员使用默认值而不是 NULL 列,特别是对用于计算或数据分组的列更是如此。

1.4 引擎类型

你可能已经注意到,迄今为止使用的 CREATE TABLE 语句全都以ENGINE=InnoDB 语句结束。
与其他DBMS一样,MySQL有一个具体管理和处理数据的内部引擎。在你使用 CREATE TABLE 语句时,该引擎具体创建表,而在你使用 SELECT语句或进行其他数据库处理时,该引擎在内部处理你的请求。多数时候,此引擎都隐藏在DBMS内,不需要过多关注它。
但MySQL与其他DBMS不一样,它具有多种引擎。它打包多个引擎,这些引擎都隐藏在MySQL服务器内,全都能执行 CREATE TABLE 和 SELECT等命令。
为什么要发行多种引擎呢?因为它们具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。
当然,你完全可以忽略这些数据库引擎。如果省略 ENGINE= 语句,则使用默认引擎(很可能是 MyISAM ),多数SQL语句都会默认使用它。但并不是所有语句都默认使用它,这就是为什么 ENGINE= 语句很重要的原因。
以下是几个需要知道的引擎:

  • InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索;
  • MEMORY 在功能等同于 MyISAM ,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
  • MyISAM 是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。

外键不能跨引擎
混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

2 更新表

为更新表定义,可使用 ALTER TABLE 语句。

为了使用 ALTER TABLE 更改表结构,必须给出下面的信息:

  • 在 ALTER TABLE 之后给出要更改的表名(该表必须存在,否则将出错);
  • 所做更改的列表。

下面的例子给表添加一个列:

ALTER TABLE vendors ADD vend_phone CHAR(20);

删除刚刚添加的列:

ALTER TABLE vendors DROP COLUMN vend_phone;

ALTER TABLE 的一种常见用途是定义外键。
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders(order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products(prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers(cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors(vend_id);

复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:

  • 用新的列布局创建一个新表;
  • 使用 INSERT SELECT 语句从旧表复制数据到新表。如果有必要,可使用转换函数和计算字段;
  • 检验包含所需数据的新表;
  • 重命名旧表(如果确定,可以删除它);
  • 用旧表原来的名字重命名新表;
  • 根据需要,重新创建触发器、存储过程、索引和外键。

3 删除表

DROP TABLE custcopy;

4 重命名表

使用 RENAME TABLE 语句可以重命名一个表:

RENAME TABLE custnew TO customers2;

RENAME TABLE 所做的仅是重命名一个表。可以使用下面的语句对多个表重命名:


alter.jpg