MySQL基础之创建和操纵表

创建表

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

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

为了用程序创建表, 可使用SQL的CREATE TABLE语句。

表创建基础

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

新表的名字, 在关键字CERATE TABLE之后给出
表列的名字和定义, 用逗号隔开

CREATE TABLE语句也可能会包括其他关键字或选项, 但至少要包括表的名字和列的细节

MariaDB [crashcourse]> 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;

使用NULL值

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

不要把NULL值与空串相混淆。NULL值是没有值, 它不是空串。如果指定' '(两个单引号, 期间没有字符), 这在NOT NULL列中是允许的。空串是一个有效的值, 它不是无值。NULL值用关键字NULL而不是空串指定。

主键再介绍

主键值必须是唯一的。即, 表中的每个行必须具有唯一的主键值。如果主键使用单个列, 则它的值必须唯一。如果使用多个列, 则这些列的组合值必须唯一。

为创建由多个列组成的主键, 应该以逗号分割的列表给出各列名。

MariaDB [crashcourse]> CREATE TABLE orderitems
(
    order_num      int             NOT NULL,
    order_item     int             NOT NULL,
    prod_id        char(10)        NOT NULL,
    quantity       int             NOT NULL,
    item_price     decimal(8, 2)   NOT NULL,
    PRIMARY KEY (order_num, order_item)
)ENGINE=InnoDB;

使用AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL, 本列每当增加一行时自动增量。每次执行一个INSERT操作时, MySQL自动对该列增量, 给该列赋予下一个可用的值。这样给每个行分配一个唯一的id, 从而可以用作主键值。

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

指定默认值

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

MariaDB [crashcourse]> 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;

MySQL不允许使用函数作为默认值, 它只支持常量

引擎类型

与其他DBMS一样, MySQL有一个具体管理和处理数据的内部引擎。在使用CREATE TABLE语句时, 该引擎具体创建表, 而在使用SELECT 语句或进行其他数据库处理时, 该引擎在内部处理请求。

但是不一样的是, 它具有多种引擎。它打包多个引擎, 这些引擎都隐藏在MySQL服务器内, 全都能执行CREATE TABLE和SELECT命令。

为什么要发行多种引擎?

因为它们各自不同的功能和特性, 为不同的任务选择正确的引擎能获得良好的功能和灵活性

常见的引擎有:

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

更新表

为更新表定义, 可使用ALTER TABLE语句。但是理想状态下, 当表中存储数据以后, 该表就不应该再被更新。

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

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

添加列

MariaDB [crashcourse]> ALTER TABLE vendors ADD vend_phone CHAR(20);Query OK, 0 rows affected (0.027 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [crashcourse]>

删除列

MariaDB [crashcourse]> ALTER TABLE vendors DROP COLUMN vend_phone;
Query OK, 0 rows affected (0.030 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [crashcourse]>

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

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

删除表

删除表(删除整个表而不是其内容)非常简单, 使用DROP TABLE语句即可。

MariaDB [crashcourse]>  DROP TABLE customers2;

重命名表

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

MariaDB [crashcourse]> RENAME TABLE customer2 TO customer;