MySQL创建及操作表_百度小程序_上海毫米网络优化公司

您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >

创建和操纵表

本章讲授表的创建、更改和删除的基本知识。

创建表

MySQL不仅用于表数据操纵,而且还可以用来执行数据库和表的所有操作,包括表本身的创建和处理。

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

使用具有交互式创建和管理表的工具(如第2章讨论的工具);
表也可以直接用MySQL语句操纵。
为了用程序创建表,可使用SQL的 CREATE TABLE 语句。值得注意的是,在使用交互式工具时,实际上使用的是MySQL语句。但是,这些语句不是用户编写的,界面工具会自动生成并执行相应的MySQL语句(更改现有表时也是这样)。

1.表创建基础

为利用 CREATE TABLE 创建表,必须给出下列信息:
- 新表的名字,在关键字 CREATE TABLE 之后给出;
- 表列的名字和定义,用逗号分隔。
  CREATE TABLE 语句也可能会包括其他关键字或选项,但至少要包括表的名字和列的细节。下面的MySQL语句创建我们所用的 customers 表:
```
MariaDB [test]> 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;
```
  从上面的例子中可以看到,表名紧跟在 CREATE TABLE 关键字后面。实际的表定义(所有列)括在圆括号之中。各列之间用逗号分隔。这个表由9列组成。每列的定义以列名(它在表中必须是唯一的)开始,后跟列的数据类型(关于数据类型的解释,请参阅第1章。此外,附录D列出了MySQL支持的数据类型)。表的主键可以在创建表时用PRIMARY KEY 关键字指定。这里,列 cust_id 指定作为主键列。整条语句由右圆括号后的分号结束。( 现在先忽略ENGINE=InnoDB 和AUTO_INCREMENT ,后面会对它们进行介绍。)
  
  语句格式化可回忆一下,以前说过MySQL语句中忽略空格。语句可以在一个长行上输入,也可以分成许多行。它们的作用都相同。这允许你以最适合自己的方式安排语句的格式。前面的 CREATE TABLE 语句就是语句格式化的一个很好的例子,它被安排在多个行上,其中的列定义进行了恰当的缩进,以便阅读和编辑。以何种缩进格式安排SQL语句没有规定,但我强烈推荐采用某种缩进格式。
  
  处理现有的表在创建新表时,指定的表名必须不存在,否则将出错。如果要防止意外覆盖已有的表,SQL要求首先手工删除该表,然后再重建它,而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它,应该在表名后给出 IF NOT EXISTS 。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在,并且仅在表名不存在时创建它。
```
DROP TABLE IF EXISTS columns_priv;
CREATE TABLE columns_priv (
  Host char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  Db char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  User char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  Table_name char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  Column_name char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  Column_priv set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (Host,Db,User,Table_name,Column_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
```
  2.使用 NULL 值
  
  前面章节中说过, NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受该列没有值的行,换句话说,在插入或更新行时,该列必须有值。
  
  每个表列或者是 NULL 列,或者是 NOT NULL 列,这种状态在创建时由表的定义规定。请看下面的例子:
  
  创建 orders 表
```
MariaDB [test]> create table orders
    -> (
    -> order_num int not null auto_increment,
    -> order_date datetime not null,
    -> cust_id int not null,
    -> primary key (order_num)
    -> ) engine=innodb;
```
  orders 包含3个列,分别是订单号、订单日期和客户ID。所有3个列都需要,因此每个列的定义都含有关键字 NOT NULL 。这将会阻止插入没有值的列。如果试图插入没有值的列,将返回错误,且插入失败。
  
  创建 vendors 表
```
CREATE TABLE vendors (
  vend_id int NOT NULL AUTO_INCREMENT,
  vend_name char(50) NOT NULL,
  vend_address char(50) NULL,
  vend_city char(50) NULL,
  vend_state char(5) NULL,
  vend_zip char(10) NULL,
  vend_country char(50) NULL,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
```
  供应商ID和供应商名字列是必需的,因此指定为 NOT NULL 。其余5个列全都允许 NULL 值,所以不指定 NOT NULL 。 NULL 为默认设置,如果不指定 NOT NULL ,则认为指定的是 NULL 。
  
  理解 NULL 不要把 NULL 值与空串相混淆。 NULL 值是没有值,它不是空串。如果指定 ‘’ (两个单引号,其间没有字符),这在 NOT NULL 列中是允许的。空串是一个有效的值,它不是无值。 NULL 值用关键字 NULL 而不是空串指定。
  
  3.主键再介绍
  
  正如所述,主键值必须唯一。即,表中的每个行必须具有唯一的主键值。如果主键使用单个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一。
  
  迄今为止我们看到的 CREATE TABLE 例子都是用单个列作为主键。其中主键用以下的类似的语句定义:primary key (vend_id)
  
  为创建由多个列组成的主键,应该以逗号分隔的列表给出各列名,如下所示:
```
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;
```
  orderitems 表包含orders表中每个订单的细节。每个订单有多项物品,但每个订单任何时候都只有1个第一项物品,1个第二项物品,如此等等。因此,订单号( order_num 列)和订单物品( order_item 列)的组合是唯一的,从而适合作为主键,其定义为:RIMARY KEY (order_num,order_item)
  
  主键可以在创建表时定义(如这里所示),或者在创建表之后定义(本章稍后讨论)。
  
  4.使用 AUTO_INCREMENT
  
  让我们再次考察 customers 和 orders 表。 customers 表中的顾客由列cust_id 唯一标识,每个顾客有一个唯一编号。类似, orders 表中的每个订单有一个唯一的订单号,这个订单号存储在列 order_num 中。这些编号除它们是唯一的以外没有别的特殊意义。在增加一个新顾客或新订单时,需要一个新的顾客ID或订单号。这些编号可以任意,只要它们是唯一的即可。
  
  显然,使用的最简单的编号是下一个编号,所谓下一个编号是大于当前最大编号的编号。例如,如果 cust_id 的最大编号为 10005 ,则插入表中的下一个顾客可以具有等于 10006 的 cust_id 。
  
  简单吗?不见得。你怎样确定下一个要使用的值?当然,你可以使用 SELECT 语句得出最大的数,然后对它加1。但这样做并不可靠(你需要找出一种办法来保证,在你执行 SELECT和 INSERT 两条语句之间没有其他人插入行,对于多用户应用,这种情况是很有可能出现的),而且效率也不高(执行额外的MySQL操作肯定不是理想的办法)。
  
  这就是 AUTO_INCREMENT 发挥作用的时候了。请看以下代码行(用来创建 customers 表的 CREATE TABLE 语句的组成部分):cust_id int NOT NULL AUTO_INCREMENT
  
  AUTO_INCREMENT 告诉MySQL,本列每当增加一行时自动增量。每次执行一个 INSERT 操作时,MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT ),给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id,从而可以用作主键值。
  
  每个表只允许一个 AUTO_INCREMENT 列,而且它必须被索引(如,通过使它成为主键)
  
  覆盖 AUTO_INCREMENT 如果一个列被指定为 AUTO_INCREMENT ,则它需要使用特殊的值吗?你可以简单地在 INSERT 语句中指定一个值,只要它是唯一的(至今尚未使用过)即可,该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。
  
  确定 AUTO_INCREMENT 值让MySQL生成(通过自动增量)主键的一个缺点是你不知道这些值都是谁。
  
  考虑这个场景:你正在增加一个新订单。这要求在 orders 表中创建一行,然后在 orderitms 表中对订购的每项物品创建一行。 order_num 在 orderitems 表中与订单细节一起存储。这就是为什么 orders 表和 orderitems 表为相互关联的表的原因。这显然要求你在插入 orders 行之后,插入 orderitems 行之前知道生成的 order_num 。那么,如何在使用 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,
  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;
```
  这条语句创建包含组成订单的各物品的 orderitems 表(订单本身存储在 orders 表中)。 quantity 列包含订单中每项物品的数量。在此例子中,给该列的描述添加文本 DEFAULT 1 指示MySQL,在未给出数量的情况下使用数量 1 。
  
  不允许函数与大多数DBMS不一样,MySQL不允许使用函数作为默认值,它只支持常量。
  
  使用默认值而不是 NULL 值许多数据库开发人员使用默认值而不是 NULL 列,特别是对用于计算或数据分组的列更是如此。
  
  6.引擎类型
  
  你可能已经注意到,迄今为止使用的 CREATE TABLE 语句全都以ENGINE=InnoDB 语句结束。
  
  与其他DBMS一样, MySQL有一个具体管理和处理数据的内部引擎。
  
  在你使用 CREATE TABLE 语句时,该引擎具体创建表,而在你使用 SELECT语句或进行其他数据库处理时,该引擎在内部处理你的请求。多数时候,此引擎都隐藏在DBMS内,不需要过多关注它。
  
  但MySQL与其他DBMS不一样,它具有多种引擎。它打包多个引擎,这些引擎都隐藏在MySQL服务器内,全都能执行 CREATE TABLE 和 SELECT等命令。
  
  为什么要发行多种引擎呢?
  
  因为它们具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。
  
  当然,你完全可以忽略这些数据库引擎。如果省略 ENGINE= 语句,则,多数SQL语句都会默认使用它。但并使用默认引擎(很可能是 MyISAM )不是所有语句都默认使用它,这就是为什么 ENGINE= 语句很重要的原因(也就是为什么本书的样列表中使用两种引擎的原因)。
  
  以下是几个需要知道的引擎:
  - InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索;
  - MEMORY 在功能等同于 MyISAM ,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
  - MyISAM 是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
    引擎类型可以混用。除 productnotes 表使用 MyISAM 外,本书中的样例表都使用 InnoDB 。原因是希望支持事务处理(因此,使用 InnoDB ),但也需要在 productnotes 中支持全文本搜索(因此,使用 MyISAM )。
    
    外键不能跨引擎混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性,如第1章所述)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
    
    那么,你应该使用哪个引擎?这有赖于你需要什么样的特性。 MyISAM由于其性能和特性可能是最受欢迎的引擎。但如果你不需要可靠的事务处理,可以使用其他引擎。
    
    更新表
    
    为更新表定义,可使用 ALTER TABLE 语句。但是,理想状态下,当表中存储数据以后,该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑,以便后期不对该表进行大的改动。
    
    为了使用 ALTER TABLE 更改表结构,必须给出下面的信息:
    - 在 ALTER TABLE 之后给出要更改的表名(该表必须存在,否则将出错);
    - 所做更改的列表。
      下面的例子给表添加一个列:
      
      给 vendors 表增加一个名为 vend_phone 的列
```
MariaDB [test]> alter table vendors add vend_phone char(20);
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0
MariaDB [test]> desc vendors;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| vend_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| vend_name    | char(50) | NO   |     | NULL    |                |
| vend_address | char(50) | YES  |     | NULL    |                |
| vend_city    | char(50) | YES  |     | NULL    |                |
| vend_state   | char(5)  | YES  |     | NULL    |                |
| vend_zip     | char(10) | YES  |     | NULL    |                |
| vend_country | char(50) | YES  |     | NULL    |                |
| vend_phone   | char(20) | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
```
      删除刚刚添加的列vend_phone
```
MariaDB [test]> alter table vendors drop column vend_phone;
Query OK, 6 rows affected (0.12 sec)
Records: 6  Duplicates: 0  Warnings: 0
MariaDB [test]> desc vendors;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| vend_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| vend_name    | char(50) | NO   |     | NULL    |                |
| vend_address | char(50) | YES  |     | NULL    |                |
| vend_city    | char(50) | YES  |     | NULL    |                |
| vend_state   | char(5)  | YES  |     | NULL    |                |
| vend_zip     | char(10) | YES  |     | NULL    |                |
| vend_country | char(50) | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
```
      ALTER TABLE 的一种常见用途是定义外键。下面是用来定义讲义中的表所用的外键的代码:
```
MariaDB [test]> alter table orderitems
  -> add constraint fk_orderitems_orders
  -> foreign key (order_num) references orders (order_num);
MariaDB [test]> alter table orderitems
  -> add constraint fk_orderitems_products
  -> foreign key (prod_id) references orders (prod_id);
MariaDB [test]> alter table orders
  -> add constraint fk_orderitems_customers
  -> foreign key (cust_id) references orders (cust_id);
MariaDB [test]> alter table products
  -> add constraint fk_orderitems_vendors
  -> foreign key (vend_id) references orders (vend_id);
```
      这里,由于要更改4个不同的表,使用了4条 ALTER TABLE 语句。为了对单个表进行多个更改,可以使用单条 ALTER TABLE 语句,每个更改用逗号分隔。
      
      复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:
      - 用新的列布局创建一个新表;
      - 使用 INSERT SELECT 语句从旧表复制数据到新表。如果有必要,可使用转换函数和计算字段;
      - 检验包含所需数据的新表;
      - 重命名旧表(如果确定,可以删除它);
      - 用旧表原来的名字重命名新表;
      - 根据需要,重新创建触发器、存储过程、索引和外键。
        小心使用 ALTER TABLE 使用 ALTER TABLE 要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据。
        
        删除表
        
        删除表(删除整个表而不是其内容)非常简单,使用 DROP TABLE 语句即可: drop table customers2;
        
        ariaDB [test]> create table customer2 as select * from customers; Query OK, 15 rows affected (0.08 sec) Records: 15 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from customers; +---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | The Fudds | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 10007 | Pep E. LaPew | 100 Main Street | Los Angeles | CA | 90046 | USA | NULL | NULL | | 10008 | M. Martian | 42 Galaxy Way | New York | NY | 11213 | USA | NULL | NULL | | 20001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 20002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 20003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 20004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 20005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 20006 | Pep E. Lapew | 100 Main Street | Los Angeles | CA | 90046 | USA | NULL | NULL | | 20007 | Pep E. LaPew | 100 Main Street | Los Angeles | CA | 90046 | USA | NULL | NULL | | 20008 | M. Martian | 42 Galaxy Way | New York | NY | 11213 | USA | NULL | NULL | +---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+ 15 rows in set (0.00 sec)
        
        这条语句删除 customers2 表(假设它存在)也不能撤销,执行这条语句将永久删除该表。
        
        重命名表
        
        使用 RENAME TABLE 语句可以重命名一个表:
        
        MariaDB [test]> rename table customers to customers3; Query OK, 0 rows affected (0.05 sec) MariaDB [test]> rename table customers3 to customers; Query OK, 0 rows affected (0.04 sec)
        
        RENAME TABLE 所做的仅是重命名一个表。可以使用下面的语句对多个表重命名:
        
        MariaDB [test]> rename table vendors to vendors0,products to products0,orders to orders0; Query OK, 0 rows affected (0.08 sec) MariaDB [test]> rename table vendors0 to vendors,products0 to products,orders0 to orders; Query OK, 0 rows affected (0.06 sec)
        
        小结
        
        本章介绍了几条新SQL语句。 CREATE TABLE 用来创建新表, ALTER TABLE 用来更改表列(或其他诸如约束或索引等对象),而 DROP TABLE 用来完整地删除一个表。这些语句必须小心使用,并且应在做了备份后使用。本章还介绍了数据库引擎、定义主键和外键,以及其他重要的表和列选项。
        
        昆明网站制作定制东莞网站建设方案报价免费制作动画网站长安网站建设制作公司注册域名需要多少钱开网站公司

上一篇：Nacos集群搭建

下一篇：【腾讯云 TDSQL-C Serverless 产品测评】- 云原生时代的TDSQL-C MySQL数据库技术实践