创建数据库以及该数据库下的表单

创建数据库以及该数据库下的表单

本文来自于对《MySQL必知必会》的整理
本文将学习如何连接和登录到MySQL，如何执行MySQL语句，以及如何获得数据库和表的信息。

1. 登录MySQL

在MySQL安装目录的bin文件夹下输入以下指令：

mysql -u root -p

输入设置的密码之后，显示如下界面：

Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2. 创建crashcourse数据库

CREATE DATABASE crashcourse;

输入完之后，按照以下命令查看现有数据库列表：

SHOW DATABASES;

结果如下：

+--------------------+
| Database           |
+--------------------+
| crashcourse        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

3. 创建crashcourse数据库下的表单

输入以下命令转到crashcourse数据库：

use crashcourse;

输出结果如下：

Database changed

通用语法：

CREATE TABLE table_name (column_name column_type);

3.1 创建customers表单

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列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型。表的主键可以在创建表时用PRIMARY KEY关键字指定。这里，列cust_id指定作为主键列。整条语句由右圆括号后的分号结束。

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

示例：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用AUTO_INCREMENT

customers表中的顾客由列cust_id唯一标识，每个顾客有一个唯一编号。这些编号除它们是唯一的以外没有别的特殊意义。在增加一个新顾客或新订单时，需要一个新的顾客ID或订单号。这些编号可以任意，只要它们是唯一的即可。

显然，使用的最简单的编号是下一个编号，所谓下一个编号是大于当前最大编号的编号。例如，如果cust_id的最大编号为10005，则插入表中的下一个顾客可以具有等于10006的cust_id。

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

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。

3.2 创建orderitems表单

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表中每个订单的细节。每个订单有多项物品，但每个订单任何时候都只有1个第一项物品，1个第二项物品，如此等等。因此，订单号（order_num列）和订单物品（order_item列）的组合是唯一的，从而适合作为主键。其定义为：

PRIMARY KEY (order_num,order_item)

主键中只能使用不允许NULL值的列

指定默认值

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定：
相关解释：
这条语句创建包含组成订单的各物品的orderitems表（订单本身存储在orders表中）。quantity列包含订单中每项物品的数量。在此例子中，给该列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1。

3.3 创建orders表单

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;

使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。
相关解释：
这条语句创建本书中所用的orders表。orders包含3个列，分别是订单号、订单日期和客户ID。所有3个列都需要，因此每个列的定义都含有关键字NOT NULL。这将会阻止插入没有值的列。如果试图插入没有值的列，将返回错误，且插入失败。

3.4 创建products表单

CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

3.5 创建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值是没有值，它不是空串。如果指定’ '（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

3.6 创建productnotes表单

CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;

引擎类型

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

3.7 定义外键（建立表单之间的联系）

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

相关解释：
外键是指引用另外一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束。外键用来建立和加强两个表数据之间的连接。
使用如下命令查看外键的作用：

show create table orderitems;

4. 删除表和重命名表

通用语法：
删除表：

DROP TABLE table_name;

重命名表：

RENAME TABLE original_name TO now_name;

示例（重命名多个表）：

RENAME TABLE backup_customers TO customers,
			 backup_vendors TO vendors,

5. 查看crashcourse数据库下的表单

SHOW TABLES;

结果如下：

+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers             |
| orderitems            |
| orders                |
| productnotes          |
| products              |
| vendors               |
+-----------------------+
6 rows in set (0.01 sec)

6. 显示表列

SHOW COLUMNS FROM customers;

结果如下：

+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int       | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.02 sec)

分析：
SHOW COLUMNS 要求给出一个表名（这个例子中的 FROM customers），它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息（如字段cust_id的auto_increment）。
其他show语句：

• SHOW STATUS，用于显示广泛的服务器状态信息；
• SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句；
• SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；
• SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。