MySQL — Tables

Showing Tables

-- Show tables in current database
SHOW TABLES;

-- Show table structure
DESCRIBE users;

-- Show create table statement
SHOW CREATE TABLE users;

Creating Tables

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Modifying Tables

-- Add column
ALTER TABLE users ADD phone VARCHAR(20);

-- Modify column
ALTER TABLE users MODIFY name VARCHAR(150);

-- Drop column
ALTER TABLE users DROP phone;

-- Rename table
ALTER TABLE users RENAME TO customers;

Dropping Tables

-- Drop table
DROP TABLE users;

-- Drop if exists
DROP TABLE IF EXISTS users;

Table Examples

-- Products table
CREATE TABLE products (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    total DECIMAL(10,2),
    status ENUM('pending', 'completed', 'cancelled'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

Mini Practice

Write MySQL code that:

1. Shows all tables
2. Creates a table
3. Modifies a table
4. Drops a table

Up Next

Next: Learn about Creating Tables.