美文网首页《mysql必知必会》笔记
Mysql语法之创建和操纵表

Mysql语法之创建和操纵表

作者: etron_jay | 来源:发表于2019-08-11 16:30 被阅读1次

    一、创建表

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

    • 使用具有交互式创建和管理表的工具
    • 表也可以直接用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值就是没有值或缺值。

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

    每个表列或者是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是一个可靠的<u>事务处理引擎</u>,它不支持全文本搜索
    • 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;
    
    

    <u>ALTER TABLE的一种常见用途是定义外键。</u>

    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;
    

    相关文章

      网友评论

        本文标题:Mysql语法之创建和操纵表

        本文链接:https://www.haomeiwen.com/subject/yadsjctx.html