CREATE DATABASE and CREATE TABLE

From using to building

Until now you have read and changed data in tables someone else designed. This section teaches you to design your own. We use a separate database called practice so the shop data stays untouched.

Create and select a database

CREATE DATABASE IF NOT EXISTS practice CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE practice;
SELECT DATABASE() AS current_database;

+------------------+
| current_database |
+------------------+
| practice         |
+------------------+

IF NOT EXISTS stops an error if the database is already there. The character set decides which characters can be stored, and utf8mb4 covers every language and emoji. The collation decides how text is compared and sorted: _ai_ci means accent-insensitive and case-insensitive. In MySQL 8 these are the defaults, but stating them makes your scripts explicit.

SHOW DATABASES; lists databases, and DROP DATABASE practice; would delete this one with all its tables, so use it with great care.

Create a table

A table definition lists the columns, each with a name, a data type and optional rules, inside parentheses and separated by commas:

DROP TABLE IF EXISTS books;
CREATE TABLE books (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  title     VARCHAR(200) NOT NULL,
  author    VARCHAR(100) NOT NULL,
  price     DECIMAL(8,2),
  published DATE
);

Read each line as column_name TYPE rules:

• id INT PRIMARY KEY AUTO_INCREMENT is a whole number that uniquely identifies the row and counts up by itself.
• title VARCHAR(200) NOT NULL is text of up to 200 characters that must have a value.
• price DECIMAL(8,2) is an exact number with up to 8 digits, 2 after the decimal point. It may be NULL.
• published DATE is a calendar date.

The next lessons explain types, keys and constraints in detail. For now, confirm what you created:

DESCRIBE books;

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| title     | varchar(200) | NO   |     | NULL    |                |
| author    | varchar(100) | NO   |     | NULL    |                |
| price     | decimal(8,2) | YES  |     | NULL    |                |
| published | date         | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

For the full definition, ask MySQL to print the statement that would recreate the table. The \G ending prints the result vertically, which is easier to read:

SHOW CREATE TABLE books\G

*************************** 1. row ***************************
       Table: books
Create Table: CREATE TABLE `books` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `author` varchar(100) NOT NULL,
  `price` decimal(8,2) DEFAULT NULL,
  `published` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The output shows additional details MySQL filled in, such as the storage engine (InnoDB) and the character set. Copy this output into a file and you have a backup of the structure.

Use the table

INSERT INTO books (title, author, price, published) VALUES
  ('Learning SQL', 'A. Writer', 29.99, '2023-05-01'),
  ('Database Basics', 'B. Author', 24.50, NULL);
SELECT * FROM books;

+----+-----------------+-----------+-------+------------+
| id | title           | author    | price | published  |
+----+-----------------+-----------+-------+------------+
|  1 | Learning SQL    | A. Writer | 29.99 | 2023-05-01 |
|  2 | Database Basics | B. Author | 24.50 | NULL       |
+----+-----------------+-----------+-------+------------+

Because id is auto-generated, you do not supply it. The second book has no publication date, which is fine because that column allows NULL.

Safe scripts: IF NOT EXISTS and IF EXISTS

Scripts that build a database are often run more than once. Two small phrases keep them from failing:

CREATE TABLE IF NOT EXISTS books (id INT PRIMARY KEY);
SHOW WARNINGS;

+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Note  | 1050 | Table 'books' already exists |
+-------+------+------------------------------+

Since books already exists, nothing changes and MySQL records a warning (Note 1050). Similarly DROP TABLE IF EXISTS books; removes a table only when it exists. Be careful: CREATE TABLE IF NOT EXISTS does not check that an existing table has the same columns as your definition, it simply skips.

Copying a table

Two ways to copy, with different results:

DROP TABLE IF EXISTS books_copy, books_cheap;
CREATE TABLE books_copy LIKE books;
CREATE TABLE books_cheap AS SELECT id, title, price FROM books WHERE price < 25;
SELECT * FROM books_cheap;

+----+-----------------+-------+
| id | title           | price |
+----+-----------------+-------+
|  2 | Database Basics | 24.50 |
+----+-----------------+-------+

CREATE TABLE ... LIKE copies the structure, including keys and defaults, but no rows. CREATE TABLE ... AS SELECT creates a table from a query's result, with its data, but it does not copy keys, indexes or AUTO_INCREMENT. For a full copy, use LIKE and then INSERT INTO books_copy SELECT * FROM books;.

Temporary tables

A TEMPORARY table exists only for your connection and disappears when you disconnect. It is handy for intermediate results:

DROP TEMPORARY TABLE IF EXISTS recent_books;
CREATE TEMPORARY TABLE recent_books AS SELECT title FROM books WHERE published >= '2023-01-01';
SELECT * FROM recent_books;

+--------------+
| title        |
+--------------+
| Learning SQL |
+--------------+

Naming conventions

Pick one style and stick to it. A widely used one: lowercase names with underscores (order_items), plural table names (customers) and singular column names (first_name). Avoid spaces, avoid reserved words such as order, group, lines or index as names (they need backticks), and keep names meaningful. Names are limited to 64 characters. Remember that table names are case-sensitive on Linux and not on Windows, which is another reason to use lowercase everywhere.

Common mistakes

• Forgetting USE and creating tables in the wrong database.
• A trailing comma after the last column.
• Choosing a name that is a reserved word, which causes confusing syntax errors.
• Assuming IF NOT EXISTS updates the table to the new definition.
• Using DROP DATABASE casually.

Exercise

In the practice database, create a table members with an auto-incrementing id, a required full_name (up to 100 characters), an optional email (up to 150 characters) and a joined_on date. Insert two members and display them, then show the table's definition.

DROP TABLE IF EXISTS members;
CREATE TABLE members (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  email     VARCHAR(150),
  joined_on DATE
);
INSERT INTO members (full_name, email, joined_on) VALUES ('Ravi Patel', 'ravi@example.com', '2024-03-01'), ('Sara Lee', NULL, '2024-03-05');
SELECT * FROM members;
SHOW CREATE TABLE members\G

+----+------------+------------------+------------+
| id | full_name  | email            | joined_on  |
+----+------------+------------------+------------+
|  1 | Ravi Patel | ravi@example.com | 2024-03-01 |
|  2 | Sara Lee   | NULL             | 2024-03-05 |
+----+------------+------------------+------------+
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int NOT NULL AUTO_INCREMENT,
  `full_name` varchar(100) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `joined_on` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci