Ravindra BagaleCourses & study guides

10. MySQL Part 1: Install, Create, Insert and SELECT

10.3 Creating Tables and Choosing Data Types

By Last updated:

Table mhanje rows aani columns. Column banavtana tyacha data type nit nivda – chukicha type mhanje chukicha data aani kadhi kadhi security problem.

Type Stores Example
INT / BIGINT Whole numbers id, age
DECIMAL(10,2) Exact money values fees
VARCHAR(n) Text up to n characters name VARCHAR(100)
TEXT Long text caption
DATE / DATETIME / TIMESTAMP Dates and times joined_on DATE
BOOLEAN (TINYINT(1)) True/false is_active
ENUM('a','b') One of a fixed list mode ENUM('online','offline') 
USE cybercourse;
CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(150) UNIQUE,
  city       VARCHAR(50)  NOT NULL,
  age        INT,
  fees_paid  DECIMAL(10,2) DEFAULT 0,
  joined_on  DATE
);

CREATE TABLE courses (
  course_id   INT AUTO_INCREMENT PRIMARY KEY,
  course_name VARCHAR(100) NOT NULL,
  duration_wk INT
);

CREATE TABLE enrollments (
  enroll_id  INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_id  INT,
  score      INT,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

SHOW TABLES;
DESCRIBE students;             -- or: DESC students;
SHOW CREATE TABLE students\G   -- full definition

Ravindra Bagale's Tip

Many students create a VARCHAR(20) column for passwords – which means they plan to store plain passwords! Never store a password in plain text; hash it in PHP with password_hash() and store it in VARCHAR(255). Security starts from the table design itself.

Practice task

Create the three tables above and run DESCRIBE on each. Explain what AUTO_INCREMENT PRIMARY KEY and FOREIGN KEY do in your own words.