创建和操纵表

梦里梦外; 2022-05-23 05:58 193阅读 0赞

**一、表的创建**

一般我们有两种创建表的方法：  
1.使用交互式创建和管理表的工具。（比如Navicat，MySQLworkBench）

2.直接使用MySQL的语句创建。

1.1表的创建基础

CREATE TABLE customers
    (
        列  名  类型  是否可空  设置了自增 最后加上了逗号
        cust_id int NOT NULL AUTO_INCREMENT,
        cust_name char(50) NOT NULL ,
        cust_city   char(50)    NULL ,
        cust_state char(5)  NULL ,
        cust_email  char(255) NULL ,
        --这里设置了主键是哪一列--
        PRIMARY KEY(cust_id)
    );

上面的SQL语句将会创建表。我们是利用了 CREATE TABLE 来创建的表，而利用那条语句，我们必须给出下列信息：   
1.新表的名字，在关键字CREATE TABLE之后给出；

2.表列的名字和定义，用逗号隔开；

这里我们强调一点，在创建新表的时候，指定的表名一定要是不存在地！否则就会报错！如果我们仅仅想在一个表不存在时创建它，应该在表名后给出 IF NOT EXISTS 。

1.2 关于每列的NULL  
我们知道NULL值就是没有值或缺值。允许NULL 的列就允许在插入行时不给出该列的值，反之，若该列不允许NULL值，则该列将不接受没有值的行，换句话说，再插入或更新行时，该列必须有值。

补充一下：如果我们在创建的时候，没有明确指出是否NULL值，那我们将会有一个默认设置 就是每一个列都可以为NULL 。 还有，NULL 不是 空串，空串是个有效值，它不是无值。

1.3 主键再介绍

我们都知道，主键必须唯一。就是说，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则他的值必须唯一。 如果使用多个列，则 这些列的 组合值 必须唯一。

CREATE TABLE customers (
        cust_id INT NOT NULL AUTO_INCREMENT,
        cust_name CHAR (50) NOT NULL,
        cust_city CHAR (50) NULL,
        cust_state CHAR (5) NULL,
        cust_email CHAR (255) NULL,
        PRIMARY KEY (cust_id,cust_name)
    );

我们将上面的SQL命令稍稍改动即可，上面我们就用了多列来作为主键，其中各列用逗号分隔，

比如：PRIMARTY KEY(cust\_id, name)。

1.4 使用AUTO\_INCREMENT

在上方的SQL语句中，我们已经使用了 AUTO\_INCREMENT 来实现自增了。  
AUTO\_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。这样每行非陪一个唯一的cust\_id，从而可以作为主键值。

每个表只允许一个AUTO\_INCREMENT列，而且他必须被索引。（比如成为主键）

我们可以通过`SELECT last_insert_id()`返回最后一个AUTO\_INCREMENT值，然后用于SQL语句。

1.5 指定默认值

如果插入行时没有给出值，MySQL允许指定此时使用默认值。我们需要在 CREATE TABLE 语句定义列 时 给出

DEFAULT关键字 来指定默认值。

CREATE TABLE customers (
        cust_id INT NOT NULL AUTO_INCREMENT,
        cust_name CHAR (50) NOT NULL,
        cust_city CHAR (50) NULL,
        cust_state CHAR (5) NULL,
        cust_email CHAR (255) NULL DEFAULT '123',
        PRIMARY KEY (cust_id,cust_name)
    )ENGINE = InnoDB;

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

1.5 引擎类型  
你可能注意到了，我们最后给的SQL语句的例子，最后一行 加了`ENGINE = InnoDB`，这个意思是指定了引擎的类型。  
和其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。在你使用 CREATE TABLE时，该引擎具体创建表，在使用SELECT语句时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内。 而MySQL，他具有多种引擎。  
如果省略 ENGINE = ？？？ ，我们就会使用默认的引擎，很可能是MyISAM，多数SQL语句都会默认使用它，但也不是所有，这就是为什么 明确指出 ENGINE = ??? 很重要的原因。

InnoDB是一个可靠的事物处理引擎，但是不支持全文本搜索。

MEMORY在功能上等同于MyISAM，但由于数据存储在内存中（不是磁盘），所以速度特别快。

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

注意：混用引擎类型有一大缺陷，外键不能跨引擎，即使用一个引擎的表不能引用具有不同引擎的表的外键。

二、创建之后改变表的结构  
为了在创建表后还能更新表的定义，我们可以使用ALTER TABLE 语句。但是不建议后期频繁改动。

例如，给表添加一个列：

ALTER TABLE vendors ADD vend_phone CHAR(20);

也可以删除我们的列：

ALTER TABLE vendors DROP COLUMN vend_phone;

重命名表

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

RENAME TABLE customer2 TO customers;