How to do it...

The table contains the column definition:

mysql> CREATE TABLE IF NOT EXISTS `company`.`customers` (
`id` int unsigned AUTO_INCREMENT PRIMARY KEY,
`first_name` varchar(20),
`last_name` varchar(20),
`country` varchar(20)
) ENGINE=InnoDB;

The options are explained as follows:

• Dot notation: Tables can be referenced using database name dot table name (database.table). If you are connected to the database, you can simply use customers instead of company.customers.
• IF NOT EXISTS: If a table with the same name exists and you specify this clause, MySQL simply throws a warning that the table already exists. Otherwise, MySQL will throw an error.
• id: It is declared as an integer since it contains only integers. Along with that, there are two key words: AUTO_INCREMENT and PRIMARY KEY.
• AUTO_INCREMENT: A linearly incremental sequence is automatically generated, so you do not need to worry about assigning id to each row.
• PRIMARY KEY: Each row is identified by a UNIQUE column that is NOT NULL. Only one of these columns should be defined in a table. If a table contains an AUTO_INCREMENT column, it is taken as PRIMARY KEY.
• first_name, last_name, and country: They contain strings, so they are defined as varchar.
• Engine: Along with the column definition, you should mention the storage engine. Some types of storage engines include InnoDB, MyISAM, FEDERATED, BLACKHOLE, CSV, and MEMORY. Out of all the engines, InnoDB is the only transactional engine and it is the default engine. To learn more about transactions, refer to Chapter 5, Transactions.

To list all the storage engines, execute the following:

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 3. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

You can create many tables in a database.

Create one more table to track the payments:

mysql> CREATE TABLE `company`.`payments`(
`customer_name` varchar(20) PRIMARY KEY,
`payment` float
);

To list all the tables, use:

mysql> SHOW TABLES;
+-------------------+
| Tables_in_company |
+-------------------+
| customers         |
| payments          |
+-------------------+
2 rows in set (0.00 sec)

To see the structure of the table, execute the following:

mysql> SHOW CREATE TABLE customers\G
*************************** 1. row ***************************
 Table: customers
Create Table: CREATE TABLE `customers` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `first_name` varchar(20) DEFAULT NULL,
 `last_name` varchar(20) DEFAULT NULL,
 `country` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Or use this:

mysql> DESC customers;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(20)      | YES  |     | NULL    |                |
| last_name  | varchar(20)      | YES  |     | NULL    |                |
| country    | varchar(20)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

MySQL creates .ibd files inside the data directory :

shell> sudo ls -lhtr /usr/local/mysql/data/company
total 256K
-rw-r----- 1 mysql mysql 128K Jun 4 07:36 customers.ibd
-rw-r----- 1 mysql mysql 128K Jun 4 08:24 payments.ibd