Contents
MySQL Installation Introduction to Databases Database Creation Tables & Data Types Primary Key & Auto Increment DML Statements Quick Revision Assignment

Session Overview

  • MySQL Installation
  • Introduction to Databases
  • Database Creation
  • Tables and Data Types
  • Primary Key and Auto Increment
  • DML Statements

MySQL Installation and MySQL Workbench

Download MySQL

Download MySQL Installer from the official website:

Download MySQL Installer

Installation Steps
  1. Run MySQL Installer.
  2. Choose Developer Default.
  3. Ensure that MySQL Server and MySQL Workbench are selected.
  4. Click Next → Execute.
  5. Select Standalone MySQL Server.
  6. Set a root password and remember it.
  7. Complete the installation wizard.
Important: Save your root password securely. It will be required to connect MySQL Server from MySQL Workbench.
Open MySQL Workbench

After installation, launch MySQL Workbench test connection.

Introduction to Databases

A Database is a collection of related data stored in an organized manner.

Databases help us store, manage and retrieve data efficiently.

What is RDBMS?

RDBMS (Relational Database Management System) stores data in the form of tables and allows relationships between tables.

Examples of RDBMS
  • MySQL
  • Microsoft SQL Server
  • Oracle Database
  • PostgreSQL
Example Table
StudentId Name City
1 Shree Mumbai
2 Vinayak Pune
SQL vs NoSQL
Feature SQL NoSQL
Storage Tables Documents / Collections
Schema Fixed Structure Flexible Structure
Examples MySQL, SQL Server MongoDB, Firebase
Best For Structured Business Data Large Flexible Data
Why MySQL? - MySQL is one of the most popular SQL databases and is widely used with Django, PHP, .NET and many enterprise applications.

Database Creation

A Database is a collection of related tables used to store and manage data.

Create Database
CREATE DATABASE CollegeDB;
View All Databases
SHOW DATABASES;
Select Database
USE CollegeDB;
Example
CREATE DATABASE CollegeDB;

SHOW DATABASES;

USE CollegeDB;
Note: Before creating tables, we must select a database using the USE command.

Tables and Data Types

A Table stores data in rows and columns.

Create Table
CREATE TABLE Student
(
    Id INT,
    Name VARCHAR(100),
    City VARCHAR(100)
);
View Tables
SHOW TABLES;
Table Structure
DESCRIBE Student;

Commonly Used Data Types

Data Type Description Example
INT Stores Whole Numbers 101
VARCHAR(100) Stores Text Shree
DECIMAL(10,2) Stores Decimal Values 5500.75
DATE Stores Date 2026-06-13
DATETIME Stores Date & Time 2026-06-13 10:30:00

Common Constraints

Constraint Purpose
PRIMARY KEY Uniquely Identifies Records
NOT NULL Value Cannot Be Empty
UNIQUE Prevents Duplicate Values
AUTO_INCREMENT Automatically Generates Numbers
DEFAULT Assigns Default Value
Example Table with Constraints
CREATE TABLE Student
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    City VARCHAR(100) DEFAULT 'Mumbai'
);

Primary Key and Auto Increment

Primary Key

A Primary Key is used to uniquely identify each record in a table.

Features of Primary Key
  • Must contain unique values.
  • Cannot contain NULL values.
  • Only one Primary Key can exist in a table.
Example
CREATE TABLE Student
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    City VARCHAR(100)
);
Auto Increment

AUTO_INCREMENT automatically generates sequential numbers for new records. 

CREATE TABLE Student
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    City VARCHAR(100)
);
Example Records
Id Name City
1 Shree Mumbai
2 Vinayak Pune
3 Rohit Kolhapur
Note: When using AUTO_INCREMENT, we do not need to manually insert values into the Id column.

DML Statements

DML (Data Manipulation Language) statements are used to insert, retrieve, update and delete records from a table.

Sample Table
CREATE TABLE Student
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    City VARCHAR(100)
);
INSERT
INSERT INTO Student(Name, City)
VALUES('Shree', 'Mumbai');
INSERT INTO Student(Name, City)
VALUES('Vinayak', 'Pune');
SELECT
SELECT * FROM Student;
Result 
+----+---------+---------+
| Id | Name    | City    |
+----+---------+---------+
| 1  | Shree   | Mumbai  |
| 2  | Vinayak | Pune    |
+----+---------+---------+
UPDATE
UPDATE Student
SET City = 'Kolhapur'
WHERE Id = 2;
DELETE
DELETE FROM Student
WHERE Id = 2;
View Updated Records
SELECT * FROM Student;
Important: Always use the WHERE clause with UPDATE and DELETE statements. Without WHERE, all records in the table may be affected.

Quick Revision

RDBMS
  • Stores Data in Tables
  • Supports Relationships
  • Example: MySQL
Database
  • Collection of Tables
  • Created using CREATE DATABASE
  • Selected using USE
Table
  • Rows & Columns
  • Stores Records
  • Created using CREATE TABLE
Constraints
  • PRIMARY KEY
  • AUTO_INCREMENT
  • NOT NULL
  • UNIQUE
DML
  • INSERT
  • SELECT
  • UPDATE & DELETE

Assignment

Complete the following tasks in MySQL Workbench:
  1. Create a database named CompanyDB and select it for use.
  2. Create an Employee table with the following columns: Id, Name, Department and Salary.
  3. Insert at least 3 employee records into the Employee table.
  4. Update the Department of one employee and verify the changes using SELECT.
  5. Delete one employee record and display the remaining records.