CREATE DATABASE and CREATE TABLE in MySQL

Creating a database

CREATE DATABASE IF NOT EXISTS school
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE school;
SHOW DATABASES;
DROP DATABASE IF EXISTS old_db;

utf8mb4 is the only charset that stores the full Unicode range — required for Bengali text and emojis.

Creating a table

CREATE TABLE students (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  roll_no     VARCHAR(20)  NOT NULL UNIQUE,
  name_en     VARCHAR(100) NOT NULL,
  name_bn     VARCHAR(100),
  email       VARCHAR(150) UNIQUE,
  age         TINYINT UNSIGNED CHECK (age BETWEEN 5 AND 100),
  department  VARCHAR(50)  DEFAULT 'CSE',
  cgpa        DECIMAL(3,2) DEFAULT 0.00,
  admitted_on DATE         NOT NULL,
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Column options

Option	Meaning
`NOT NULL`	Value is required
`DEFAULT x`	Value used when none is given
`AUTO_INCREMENT`	Server generates the next number
`UNIQUE`	No duplicates allowed
`PRIMARY KEY`	Unique + not null, one per table
`CHECK (...)`	Condition enforced on insert/update (MySQL 8.0.16+)
`COMMENT 'text'`	Documentation stored with the schema

Inspecting a table

DESCRIBE students;          -- or DESC students;
SHOW CREATE TABLE students\G
SHOW COLUMNS FROM students;
SHOW TABLE STATUS LIKE 'students'\G

Copying tables

CREATE TABLE students_backup LIKE students;           -- structure only
INSERT INTO students_backup SELECT * FROM students;   -- plus data

CREATE TABLE cse_students AS
SELECT id, name_en, cgpa FROM students WHERE department = 'CSE';

Note that CREATE TABLE ... AS SELECT copies data and column types but not keys, indexes or auto-increment.

Temporary tables

CREATE TEMPORARY TABLE tmp_top AS
SELECT * FROM students ORDER BY cgpa DESC LIMIT 10;

Temporary tables live only for the current session and disappear when you disconnect.