This section covers essential MySQL table operations, including creating and modifying tables, choosing storage engines, and using special features like temporary tables, generated columns, and auto-incrementing keys.

1. MySQL Storage Engines

A storage engine handles the actual storage and retrieval of data. Different engines provide different features — some support transactions, others full-text search, and some are optimized for speed. Popular engines include InnoDB and MyISAM.

Syntax:
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype
) ENGINE = storage_engine_name;

Example:
CREATE TABLE logs (
  id INT,
  message TEXT
) ENGINE = MyISAM;

2. CREATE TABLE

Creates a new table where data will be stored, defining columns, data types, and constraints.

Syntax:
CREATE TABLE table_name (
  column1 datatype [constraints],
  column2 datatype [constraints],
  ...
);

Example:
CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  author VARCHAR(100),
  published_year YEAR
);

3. MySQL Data Types Uses:

MySQL supports various data types to store numbers, text, dates, and logical values.

Syntax:
column_name INT;
column_name FLOAT;
column_name VARCHAR(length);
column_name TEXT;
column_name DATE;
column_name DATETIME;
column_name BOOLEAN;
column_name ENUM('value1', 'value2', ...);

Example:
price FLOAT;
name VARCHAR(100);
created_at DATETIME;

4. AUTO_INCREMENT

Automatically generates unique numbers for a column, usually for primary keys.

Syntax:
CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50)
);

Example:
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(100)
);

5. ALTER TABLE

Modifies an existing table’s structure.

5.1 Add a Column

Example:
ALTER TABLE users ADD email VARCHAR(100);

5.2 Remove a Column

Example:
ALTER TABLE users DROP COLUMN email;

5.3 Modify a Column

Example:
ALTER TABLE users MODIFY username VARCHAR(150);

6. RENAME TABLE

Syntax:
RENAME TABLE old_table_name TO new_table_name;

Example:
RENAME TABLE users TO members;

7. DROP TABLE

Syntax:
DROP TABLE table_name;

Example:
DROP TABLE members;

8. TRUNCATE TABLE

Clears all rows in a table but keeps the structure for reuse.

Syntax:
TRUNCATE TABLE table_name;

Example:
TRUNCATE TABLE products;

9. Temporary Tables

Temporary tables exist only during the current session and are automatically deleted when the connection closes.

Syntax:
CREATE TEMPORARY TABLE temp_orders (
  id INT,
  status VARCHAR(20)
);

Example:
CREATE TEMPORARY TABLE temp_sales (
sale_id INT,
amount DECIMAL(10,2)
);

10. Generated Columns

Generated columns automatically calculate values based on other columns. They can be virtual or stored.

Syntax:
CREATE TABLE employees (
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name))
);

Example:
CREATE TABLE products (
price DECIMAL(10,2),
quantity INT,
total_cost DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED
);
PreviousNext