First Database and Table

Creating a Database and a First Table

A server holds many databases (also called schemas), each holding tables. Create one, make it the session default with USE, and add a table:

Creating the shop database and an authors tableSQL

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
CREATE TABLE authors (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  country  CHAR(2)      NOT NULL,
  born     YEAR         NULL
);

Output

Query OK, 1 row affected (0.039 sec)
Database changed
Query OK, 0 rows affected (0.036 sec)

Rerun the script and IF NOT EXISTS turns the failure into note 1007 ("database exists"). The other bookshop tables come from the chapter's script: mysql shop < sample-db.sql. Statements end with ;; comments are -- text, # or /* */; strings take single quotes. Keywords are case-insensitive, but table names map to files, so on Linux Authors and authors are different tables. Use lowercase names.

MENU