Creating tables

While defining columns in a table, you should mention the name of the column, datatype and default value (if any). Here is an overview of all datatypes.

  • Numeric: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, and BIT.
  • Floating numbers: DECIMAL, FLOAT, and DOUBLE.
  • Strings: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
  • Spatial datatypes are also supported. Refer to https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html for more details.
  • The JSON datatype

The table contains the column definition:

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 list all the storage engines, execute the following:

SHOW ENGINES\G

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

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

To list all the tables, use:

SHOW TABLES;

+-------------------+
| Tables_in_company |
+-------------------+
| customers         |
| payments          |
+-------------------+
2 rows in set (0.01 sec)

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

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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

Or use this:

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.00 sec)

MySQL creates .ibd files inside the data directory:

sudo ls -lhtr /var/lib/mysql/company/

total 160K
-rw-r----- 1 mysql mysql 112K Sep  9 11:07 customers.ibd
-rw-r----- 1 mysql mysql 112K Sep  9 11:11 payments.ibd

Cloning table structure

You can clone the structure of one table into a new table:

CREATE TABLE new_customers LIKE customers;

You can verify the structure of the new table:

SHOW CREATE TABLE new_customers\G

*************************** 1. row ***************************
       Table: new_customers
Create Table: CREATE TABLE `new_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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

results matching ""

    No results matching ""