Karibu kwenye Tutorial 02 ya MySQL Database Full Course.

Katika Tutorial 01 tulijifunza msingi wa database, tables, columns, records na SQL commands muhimu kama CREATE, INSERT, SELECT, UPDATE na DELETE.

Sasa tutajifunza jambo muhimu sana katika database design:

MySQL Data Types

Data type ndiyo inayoiambia MySQL aina ya data inayoruhusiwa kuhifadhiwa kwenye column fulani.

Kwa mfano:

age INT

ina maana age itahifadhi integer numbers.

Wakati:

student_name VARCHAR(100)

ina maana student_name itahifadhi maandishi.

Kuchagua data type sahihi husaidia database kuwa:

• Sahihi
• Salama
• Rahisi kusimamia
• Yenye matumizi mazuri ya storage
• Yenye performance nzuri
• Rahisi kuunganisha na PHP au application nyingine

1. MySQL Data Type ni nini?

Data type ni sheria inayobainisha aina ya taarifa ambayo column inaweza kuhifadhi.

Mfano:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(100),
    date_of_birth DATE
);

Hapa tuna data types tatu tofauti:

INT
VARCHAR
DATE

id itahifadhi integer.

student_name itahifadhi text.

date_of_birth itahifadhi tarehe.

2. Makundi Makuu ya MySQL Data Types

Data types za MySQL zinaweza kugawanywa katika makundi makubwa kama:

Tutazipitia hatua kwa hatua.

3. INT

INT hutumika kuhifadhi integer numbers.

Integer ni namba ambazo hazina decimal.

Mfano:

1
10
250
5000
-25

Mfano wa column:

age INT

Au:

quantity INT

Mfano kamili:

CREATE TABLE products (
    id INT,
    quantity INT
);

4. INT na AUTO_INCREMENT

Moja ya matumizi maarufu ya INT ni kutengeneza ID.

Mfano:

id INT AUTO_INCREMENT PRIMARY KEY

Mfano kamili:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100)
);

Ukiongeza mwanafunzi:

INSERT INTO students (student_name)
VALUES ('Amina Hassan');

MySQL inaweza kumpa ID:

1

Ukiongeza mwingine:

INSERT INTO students (student_name)
VALUES ('John Peter');

Atapata:

2

Hivyo huhitaji kuandika ID mwenyewe.

5. TINYINT, SMALLINT, MEDIUMINT, INT na BIGINT

MySQL ina integer types za ukubwa tofauti.

Kwa applications nyingi:

INT

inatosha kwa IDs na quantities.

Kwa mifumo mikubwa sana yenye records nyingi, unaweza kukutana na:

BIGINT

6. UNSIGNED

Kwa kawaida integer inaweza kuhifadhi positive na negative values.

Mfano:

-100
0
100

Lakini baadhi ya data hazipaswi kuwa negative.

Kwa mfano:

• Quantity
• Student ID
• Product ID
• Number of students

Unaweza kutumia:

INT UNSIGNED

Mfano:

quantity INT UNSIGNED

Hii inazuia negative integer values kwenye column hiyo.

7. VARCHAR

VARCHAR ni moja ya data types zinazotumika sana katika MySQL.

Inatumika kuhifadhi text yenye urefu unaoweza kutofautiana.

Mfano:

student_name VARCHAR(100)

Inaweza kuhifadhi:

Amina

au:

Amina Hassan Mohamed

8. VARCHAR(50), VARCHAR(100), VARCHAR(255) Maana Yake Nini?

Mfano:

VARCHAR(100)

Namba 100 inaonyesha maximum character length inayoruhusiwa kwenye column hiyo.

Mfano:

username VARCHAR(50)

email VARCHAR(150)

address VARCHAR(255)

Usichague size bila sababu.

Database design nzuri inahitaji kufikiria aina na ukubwa wa taarifa utakazohifadhi.

9. VARCHAR vs INT kwa Phone Number

Watu wengi huandika:

phone INT

Hii si design nzuri kwa phone number.

Ni bora:

phone VARCHAR(20)

Kwa nini?

Phone number si namba inayotumika kufanya hesabu.

Pia inaweza kuwa na:

+

Mfano:

+255712345678

Kwa hiyo:

phone VARCHAR(20)

ni bora zaidi.

10. CHAR

CHAR hutumika kuhifadhi text yenye fixed length.

Mfano:

gender_code CHAR(1)

Inaweza kuhifadhi:

M

au:

F

Mfano mwingine:

country_code CHAR(2)

Inaweza kuhifadhi:

TZ

11. CHAR vs VARCHAR

Tofauti ya msingi:

Mfano:

country_code CHAR(2)

Lakini:

student_name VARCHAR(100)

12. TEXT

TEXT hutumika kuhifadhi maandishi marefu.

Mfano:

description TEXT

Hii ni nzuri kwa:

• Blog post description
• Product description
• Comments
• Notes
• Messages
• Article content

Mfano:

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

13. VARCHAR vs TEXT

Kwa taarifa fupi kama:

Name
Email
Username
Title
Phone

mara nyingi tumia:

VARCHAR

Kwa taarifa ndefu kama:

Article
Description
Comment
Message
Biography

unaweza kutumia:

TEXT

14. TEXT Types

MySQL ina aina kadhaa za text:

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

Kwa kawaida:

TEXT

inatosha kwa content nyingi.

Kwa content kubwa sana unaweza kutumia:

MEDIUMTEXT

au:

LONGTEXT

Mfano wa blog:

content LONGTEXT

15. DECIMAL

DECIMAL ni muhimu sana kwa financial data.

Mfano:

price DECIMAL(10,2)

Inaweza kuhifadhi:

100.00
2500.50
99999.99

16. DECIMAL(10,2) Ina Maana Gani?

Mfano:

DECIMAL(10,2)

10 ni jumla ya digits.

2 ni digits zinazoweza kuwa baada ya decimal point.

Mfano:

12345.67

Kwa financial applications kama:

• Accounting
• Shop
• Fees
• Contributions
• Loans
• Payments

ni kawaida kutumia:

DECIMAL

17. Usitumie FLOAT Bila Sababu kwa Pesa

Kwa money calculations, mara nyingi ni bora kutumia:

DECIMAL(10,2)

badala ya:

FLOAT

Kwa sababu DECIMAL inafaa zaidi pale ambapo unahitaji decimal values zenye usahihi wa fixed-point, jambo muhimu kwenye fedha.

Mfano:

amount DECIMAL(12,2)

18. FLOAT

FLOAT hutumika kuhifadhi approximate floating-point numbers.

Mfano:

temperature FLOAT

Inaweza kuhifadhi:

36.7
25.4
100.25

19. DOUBLE

DOUBLE pia hutumika kwa floating-point numbers lakini kwa precision kubwa zaidi kuliko FLOAT.

Mfano:

measurement DOUBLE

Inaweza kuwa muhimu kwa:

• Scientific calculations
• Measurements
• Coordinates
• Engineering data

Lakini kwa fedha, fikiria DECIMAL.

20. DATE

DATE hutumika kuhifadhi tarehe.

Format ya kawaida ni:

YYYY-MM-DD

Mfano:

2026-08-30

Column:

date_of_birth DATE

Mfano:

INSERT INTO students
(student_name, date_of_birth)
VALUES
('Amina Hassan', '2008-05-15');

21. DATETIME

DATETIME huhifadhi tarehe pamoja na muda.

Format:

YYYY-MM-DD HH:MM:SS

Mfano:

2026-08-30 14:30:00

Column:

created_at DATETIME

22. TIMESTAMP

TIMESTAMP pia hutumika kuhifadhi date na time na mara nyingi hutumika kwenye fields kama:

created_at
updated_at

Mfano mzuri:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Hapa MySQL itaweka muda wa sasa record inapoongezwa.

23. CURRENT_TIMESTAMP

Mfano:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ukiingiza:

INSERT INTO users (username)
VALUES ('faustine');

Huna haja ya kuingiza created_at manually.

MySQL itaweka timestamp kulingana na mazingira/configuration ya database server.

24. updated_at

Unaweza pia kuwa na column inayorekodi wakati record ilipobadilishwa.

Mfano:

updated_at TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP

Mfano kamili:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

25. TIME

TIME hutumika kuhifadhi muda.

Mfano:

08:30:00

Column:

start_time TIME

Mfano:

CREATE TABLE lessons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lesson_name VARCHAR(100),
    start_time TIME
);

26. YEAR

YEAR inaweza kutumika kuhifadhi mwaka.

Mfano:

registration_year YEAR

Inaweza kuhifadhi:

2026

27. BOOLEAN

Boolean hutumika pale ambapo tuna hali mbili:

TRUE
FALSE

Mfano:

is_active BOOLEAN

Unaweza kutumia:

1 = TRUE
0 = FALSE

Mfano:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE
);

28. ENUM

ENUM inakuwezesha kuweka list ya values zinazokubalika.

Mfano:

gender ENUM('Male', 'Female')

Hivyo column hiyo inatarajiwa kupata moja ya values zilizotajwa.

Mfano:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100),
    gender ENUM('Male', 'Female')
);

29. ENUM kwa Status

Mfano mwingine:

status ENUM(
    'pending',
    'approved',
    'rejected'
)

Inaweza kutumika kwenye:

• Orders
• Applications
• Payments
• Loans
• Accounts

Mfano:

CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    applicant_name VARCHAR(100),
    status ENUM(
        'pending',
        'approved',
        'rejected'
    ) DEFAULT 'pending'
);

30. JSON

MySQL pia inaweza kuhifadhi JSON data kwa kutumia:

JSON

Mfano:

settings JSON

Unaweza kuwa na:

{
    "theme": "dark",
    "language": "sw",
    "notifications": true
}

JSON inaweza kuwa muhimu kwa structured data inayobadilika, lakini usitumie JSON kama mbadala wa relational database design bila sababu.

31. NULL

NULL ina maana hakuna value iliyowekwa.

Mfano:

phone VARCHAR(20) NULL

Hii inaruhusu phone kutokuwepo.

Mfano wa record:

Amina Hassan | NULL

32. NOT NULL

NOT NULL ina maana column lazima iwe na value.

Mfano:

student_name VARCHAR(100) NOT NULL

Ukijaribu kuongeza record bila student_name, MySQL inaweza kukataa operation hiyo kwa sababu field imewekewa NOT NULL.

33. DEFAULT

DEFAULT huweka value ya kawaida kama user hajatoa value.

Mfano:

status VARCHAR(20) DEFAULT 'active'

Mfano:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active'
);

Ukiingiza:

INSERT INTO users (username)
VALUES ('john');

status inaweza kuwa:

active

automatically.

34. Mfano wa Professional Students Table

Sasa tuunganishe tuliyojifunza.

CREATE DATABASE school_management;

USE school_management;

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    admission_number VARCHAR(50) NOT NULL,

    student_name VARCHAR(150) NOT NULL,

    gender ENUM('Male', 'Female') NOT NULL,

    date_of_birth DATE,

    phone VARCHAR(20),

    email VARCHAR(150),

    address TEXT,

    class_name VARCHAR(50),

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

Hapa tumechanganya data types tofauti kulingana na aina ya taarifa.

35. Mfano wa Professional Products Table

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    product_name VARCHAR(150) NOT NULL,

    description TEXT,

    price DECIMAL(12,2) NOT NULL,

    quantity INT UNSIGNED DEFAULT 0,

    status ENUM(
        'available',
        'out_of_stock',
        'disabled'
    ) DEFAULT 'available',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

Hii inaweza kuwa mwanzo wa database ya shop au inventory system.

36. Mfano wa Accounting Table

Kwa accounting system tunaweza kuwa na:

CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    transaction_type ENUM(
        'income',
        'expense'
    ) NOT NULL,

    description VARCHAR(255) NOT NULL,

    amount DECIMAL(15,2) NOT NULL,

    transaction_date DATE NOT NULL,

    notes TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Angalia:

amount DECIMAL(15,2)

Tumetumia DECIMAL kwa sababu tunahifadhi fedha.

37. Mfano wa Blog Table

CREATE TABLE blog_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(255) NOT NULL,

    slug VARCHAR(255) NOT NULL,

    description TEXT,

    content LONGTEXT NOT NULL,

    status ENUM(
        'draft',
        'published'
    ) DEFAULT 'draft',

    published_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

Hapa LONGTEXT inaweza kutumika kwa article kubwa.

38. Jinsi ya Kuchagua Data Type Sahihi

Usichague data type kwa kubahatisha.

Jiulize:

Je data ni integer?

Tumia:

INT

Mfano:

quantity
age
number_of_students

Je ni text fupi?

Tumia:

VARCHAR

Mfano:

name
email
phone
username

Je ni text ndefu?

Tumia:

TEXT

au kwa content kubwa zaidi:

LONGTEXT

Je ni fedha?

Tumia:

DECIMAL

Je ni tarehe?

Tumia:

DATE

Je ni tarehe pamoja na muda?

Tumia:

DATETIME

au TIMESTAMP kulingana na mahitaji yako.

Je ni true/false?

Tumia:

BOOLEAN

39. Makosa ya Kawaida

Kosa 1: Kutumia INT kwa Phone Number

Usifanye:

phone INT

Bora:

phone VARCHAR(20)

Kosa 2: Kutumia VARCHAR kwa Kila Kitu

Usifanye:

price VARCHAR(100)

Bora:

price DECIMAL(10,2)

Kosa 3: Kuhifadhi Date kama Text Bila Sababu

Usifanye:

date_of_birth VARCHAR(100)

Bora:

date_of_birth DATE

Kosa 4: Kuhifadhi Quantity kama VARCHAR

Usifanye:

quantity VARCHAR(100)

Bora:

quantity INT UNSIGNED

40. Important Tip

Data type sahihi husaidia MySQL kuelewa data yako.

Kwa mfano:

price DECIMAL(10,2)

ni bora kuliko:

price VARCHAR(100)

kwa sababu MySQL inajua price ni numeric value inayoweza kutumika kwenye calculations.

Mfano:

SELECT SUM(price)
FROM products;

41. Practical Project

Tutengeneze database ndogo ya shop.

CREATE DATABASE mysql_course_shop;

USE mysql_course_shop;

Tengeneza products table:

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED DEFAULT 0,
    status ENUM(
        'available',
        'out_of_stock'
    ) DEFAULT 'available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ingiza data:

INSERT INTO products
(product_name, description, price, quantity)
VALUES
(
    'Laptop',
    'Laptop for office and school work',
    1200000.00,
    5
),
(
    'Keyboard',
    'USB computer keyboard',
    25000.00,
    20
),
(
    'Mouse',
    'Wireless computer mouse',
    30000.00,
    15
);

42. Angalia Data

SELECT * FROM products;

Expected output inaweza kufanana na:

+----+--------------+------------+----------+
| id | product_name | price      | quantity |
+----+--------------+------------+----------+
| 1  | Laptop       | 1200000.00 | 5        |
| 2  | Keyboard     | 25000.00   | 20       |
| 3  | Mouse        | 30000.00   | 15       |
+----+--------------+------------+----------+

43. Practice Challenge

Tengeneza database:

hospital_system

Ndani yake tengeneza table:

patients

Iwe na fields:

id
patient_name
gender
date_of_birth
phone
address
blood_group
is_admitted
admission_date
created_at

Chagua data type sahihi kwa kila field.

Baada ya hapo ingiza patients watano.

Kisha tumia:

SELECT * FROM patients;

kuhakikisha data zimeingia.

44. Advanced Challenge

Tengeneza table:

payments

Iwe na:

id
student_id
amount
payment_method
reference_number
payment_date
status
notes
created_at

Fikiria kwa makini data type inayofaa kwa kila column.

Hii itakusaidia kuanza kufikiria kama database developer badala ya kukariri SQL commands pekee.

45. Muhtasari wa Data Types

46. Umejifunza nini?

Baada ya Tutorial 02 unapaswa kuelewa:

• Data type ni nini
• INT
• BIGINT
• UNSIGNED
• VARCHAR
• CHAR
• TEXT
• LONGTEXT
• DECIMAL
• FLOAT
• DOUBLE
• DATE
• DATETIME
• TIMESTAMP
• TIME
• YEAR
• BOOLEAN
• ENUM
• JSON
• NULL
• NOT NULL
• DEFAULT
• Jinsi ya kuchagua data type sahihi

Huu ni msingi muhimu sana kabla ya kuanza professional database design.

Tutorial Inayofuata

Tutorial 03: CREATE DATABASE, CREATE TABLE na MySQL Constraints kwa Undani

Katika Tutorial 03 tutajifunza kwa undani:

• CREATE DATABASE
• IF NOT EXISTS
• DROP DATABASE
• CREATE TABLE
• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• AUTO_INCREMENT
• CONSTRAINT
• Composite Primary Keys
• Named Constraints
• Table Relationships
• Makosa ya kawaida katika table design
• Professional database examples

Tutaanza pia kuondoka kwenye tables rahisi na kuingia kwenye professional relational database design ambapo tables zinaanza kuhusiana.