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;