Mysql语法之创建和操纵表

2019-08-11 本文已影响1人 etron_jay

一、创建表

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

使用具有交互式创建和管理表的工具
表也可以直接用MYSQL语句操纵

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_email      char(255)   NULL,
    PRIMARY KEY(cust_id)
)ENGINE=InnoDB;

表的主键可以在创建表时用PRIMARY KEY关键字指定。

这里，列cust_id指定作为主键列。

创建新表时，指定的表名必须不存在，否则将会出错。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。

2.使用NULL值

NULL值就是没有值或缺值。

允许NULL值的列也允许在允许在插入行时不给出该列的值。

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

如果试图插入没有值的列，将返回错误，且插入失败。

NULL为默认设置。

不要把NULL值与空串相混淆，NULL值是没有值，它不是空串。如果指定‘’（两个单引号，其间没有字符），这在NOT NULL列是允许的。空串是一个有效的值，它不是无值。

NULL值用关键字而不是空串指定。

3. 主键再介绍

正如所述，主键值必须唯一。

如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

PRIMARY KEY(vend_id)

PRIMARY KEY(order_num,order_item)

主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

4.使用AUTO_INCREMENT

AUTO_INCREMENT告诉MYSQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MYSQL自动对该列增量，给该列赋予下一个可用的值。

这样给每个行分配一个唯一的cust_id,从而可以用作主键值。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引。

如何确定AUTO_INCREMENT值

可使用last_insert_id()函数获得这个值，如下所示：

select last_insert_id();

此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MYSQL语句。

5.指定默认值

如果在插入行时没有给出值，MYSQL允许指定此时使用的默认值。

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

create table orderitems
(
    order_num   int     NOT NULL,
    order_item  int     NOT NULL,
    quantity    int     NOT NULL DEFAULT 1,
    PRIMARY KEY(order_num,order_item)
)ENGINE=InnoDB;

使用默认值而不是NULL值

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

6.引擎类型

ENGINE=InnoDB

MYSQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MYSQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

以下是几个需要知道的引擎：

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

外键不能跨引擎

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

二、更新表

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

在理想状态下，当表中存储数据以后，该表就不应该再被更新。

在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）
所做更改列表

为表vendors增加一列vend_phone
ALTER TABLE vendors
ADD vend_phone char(20);

为表vendors珊瑚刚刚增加的那一列
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_orders FOREIGN KEY(prod_id)
REFERENCES products(prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY(cust_id)
REFERENCES customers(cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY(vend_id) REFERENCES vendors(vend_id);

使用ALTER TABLE之前，应该做一个完美的备份。

三、删除表

DROP table customers2;

四、重命名表

RENAME TABLE customers2 TO customers;

RENAME TABLE customers2 TO customers,
            backup_vendors TO vendors,
            backup_products TO products;