Sql schemas

The structure of your database is referred to as your schema.

Overview

Your schema may define multiple databases,
which may have multiple tables,
which have rows of data.

schema
  +-- database
  +-- database
         +-- table
         +-- table
               +-- row
               +-- row

Databases

CREATE DATABASE [IF NOT EXISTS] pets_db  # Create Database
SHOW DATABASES;                          # Show All Databases
USE pets_db;                             # Select database to work with
DROP DATABASE pets_db;                   # Delete Database

Tables

CREATE

CREATE TABLE users (
  id         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(10),
  last_name  VARCHAR(10),
  age        TINYINT
);

ALTER

ALTER TABLE users RENAME TO employees;

DROP

DROP TABLE users;

Columns

ALTER TABLE pet ADD COLUMN alive INT;             # Add Column
ALTER TABLE pet ADD COLUMN alive INT  AFTER dead; # Add Column, and choose placement
ALTER TABLE pet MODIFY alive dead INT;            # Rename Column/modify datatype
ALTER TABLE pet DROP COLUMN alive;                # Delete Column

Indexes

Rows can be indexed for faster lookup times.
Combinations of row-values must be unique if an index is created on multiple rows.

CREATE INDEX my_index ON my_table (first_name, last_name);
SHOW INDEX FROM table;