Jinsi ya Kucreate Database na Table kwenye MySQL Step by Step kwa Beginners

Jifunze jinsi ya kucreate database, kucreate table, kuweka columns, primary key, foreign key, na kuinsert data kwenye MySQL kwa hatua rahisi. Tembelea https://faulink.com

Jinsi ya Kucreate Database na Table kwenye MySQL Step by Step

Kama unajifunza MySQL, hatua ya kwanza kabisa ni kuelewa jinsi ya kutengeneza database na tables. Database ndiyo sehemu kuu inayohifadhi taarifa, na tables ndizo sehemu zinazopanga taarifa hizo kwa mfumo wa rows na columns.

Kwa mafunzo zaidi ya PHP, MySQL, HTML, CSS na project za web development, tembelea:

👉 https://faulink.com

1. Database ni nini?

Database ni sehemu ya kuhifadhi taarifa kwa mpangilio maalum.

Mfano kwenye mfumo wa shule, unaweza kuwa na database inayoitwa:

school_db

Ndani yake unaweza kuwa na tables kama:

students
teachers
classes
subjects
marks
users
2. Table ni nini?

Table ni sehemu ndani ya database inayohifadhi taarifa za aina moja.

Mfano table ya students inaweza kuhifadhi:

jina la mwanafunzi
jinsia
darasa
namba ya simu
tarehe ya kusajiliwa

Mfano wa table:

id student_name sex class_name phone
1 Asha Juma Female Form One 0712345678
2 John Musa Male Form Two 0788888888
3. Column ni nini?

Column ni sehemu ya table inayobeba aina fulani ya data.

Mfano kwenye table ya students:

id
student_name
sex
class_name
phone
created_at

Kila column ina aina yake ya data kama INT, VARCHAR, DATE, au DECIMAL.

4. Row ni nini?

Row ni record moja ndani ya table.

Mfano:

1 | Asha Juma | Female | Form One | 0712345678

Hii ni taarifa ya mwanafunzi mmoja.

5. Jinsi ya Kucreate Database

Ili kutengeneza database mpya kwenye MySQL, tumia command hii:

CREATE DATABASE school_db;

Baada ya kucreate database, lazima uichague ili uitumie:

USE school_db;

Mfano kamili:

CREATE DATABASE school_db;
USE school_db;
6. Kucreate Database Kama Haipo

Ni vizuri kutumia IF NOT EXISTS ili kuepuka error kama database tayari ipo.

CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

Hii inamaanisha: tengeneza database school_db kama bado haipo.

7. Jinsi ya Kucreate Table

Mfano wa table rahisi ya wanafunzi:

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
sex VARCHAR(10) NOT NULL,
class_name VARCHAR(50),
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
8. Maelezo ya Code ya Table
id INT AUTO_INCREMENT PRIMARY KEY

Hii ni column ya utambulisho wa kila record.

id INT AUTO_INCREMENT PRIMARY KEY

Maana yake:

id ni jina la column
INT ni namba kamili
AUTO_INCREMENT inaongeza id yenyewe
PRIMARY KEY inafanya id kuwa ya kipekee
student_name VARCHAR(100) NOT NULL
student_name VARCHAR(100) NOT NULL

Maana yake:

student_name ni jina la mwanafunzi
VARCHAR(100) inaruhusu maandishi hadi characters 100
NOT NULL inamaanisha lazima ijazwe
sex VARCHAR(10) NOT NULL
sex VARCHAR(10) NOT NULL

Hii itahifadhi jinsia kama:

Male
Female
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Hii inaweka tarehe na muda record ilipotengenezwa automatically.

9. Data Types Muhimu kwenye MySQL
INT

Hutunza namba kamili.

age INT
VARCHAR

Hutunza maandishi mafupi.

name VARCHAR(100)
TEXT

Hutunza maandishi marefu.

description TEXT
DATE

Hutunza tarehe.

date_of_birth DATE
TIMESTAMP

Hutunza tarehe na muda.

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
DECIMAL

Hutunza pesa au marks zenye desimali.

amount DECIMAL(10,2)
score DECIMAL(5,2)
10. Mfano wa Table ya Users

Table ya users hutumika kwenye login system.

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role VARCHAR(30) DEFAULT 'STUDENT',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Maelezo:

username UNIQUE hairuhusu username kujirudia
email UNIQUE hairuhusu email kujirudia
password VARCHAR(255) ni kwa password iliyohifadhiwa kwa password_hash()
role inaweza kuwa Admin, Teacher, Student
11. Mfano wa Table ya Classes
CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Mfano wa data:

Form One
Form Two
Form Three
Form Four
12. Mfano wa Table ya Subjects
CREATE TABLE subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
subject_name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Mfano wa subjects:

Mathematics
English
Kiswahili
Physics
Chemistry
Biology
History
Geography
Civics
13. Table ya Students yenye Foreign Key

Badala ya kuweka class_name moja kwa moja kwenye students, ni bora kuweka class_id inayounganishwa na table ya classes.

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
sex ENUM('Male','Female') NOT NULL,
class_id INT NOT NULL,
phone VARCHAR(20),
address VARCHAR(150),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (class_id) REFERENCES classes(id)
);

Hapa class_id inaunganisha mwanafunzi na darasa lake.

14. Foreign Key ni nini?

Foreign Key ni column inayounganisha table moja na table nyingine.

Mfano:

FOREIGN KEY (class_id) REFERENCES classes(id)

Maana yake ni kwamba class_id kwenye table ya students lazima iwe id iliyopo kwenye table ya classes.

Hii husaidia kuzuia makosa kama kuingiza mwanafunzi kwenye darasa ambalo halipo.

15. Kucreate Table ya Marks

Table ya marks inaweza kuunganisha students na subjects.

CREATE TABLE marks (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
exam_type VARCHAR(50) NOT NULL,
score DECIMAL(5,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

Mfano exam_type inaweza kuwa:

Midterm
Mock
Pre-National
Final
16. Full Example ya Database ya Shule

Hii ni script kamili ya kutengeneza database ya shule.

CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
subject_name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
sex ENUM('Male','Female') NOT NULL,
class_id INT NOT NULL,
phone VARCHAR(20),
address VARCHAR(150),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (class_id) REFERENCES classes(id)
);

CREATE TABLE marks (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
exam_type VARCHAR(50) NOT NULL,
score DECIMAL(5,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (subject_id) REFERENCES subjects(id)
);
17. Kuinsert Data kwenye Tables

Baada ya kucreate tables, unaweza kuingiza data.

Insert classes
INSERT INTO classes (class_name) VALUES
('Form One'),
('Form Two'),
('Form Three'),
('Form Four');
Insert subjects
INSERT INTO subjects (subject_name) VALUES
('Mathematics'),
('English'),
('Kiswahili'),
('Physics'),
('Chemistry'),
('Biology'),
('History'),
('Geography'),
('Civics');
Insert students
INSERT INTO students (student_name, sex, class_id, phone, address) VALUES
('Asha Juma', 'Female', 1, '0712345678', 'Mtwara'),
('John Musa', 'Male', 1, '0788888888', 'Masasi'),
('Neema Ally', 'Female', 2, '0755555555', 'Lindi');
Insert marks
INSERT INTO marks (student_id, subject_id, exam_type, score) VALUES
(1, 1, 'Midterm', 85),
(1, 2, 'Midterm', 78),
(2, 1, 'Midterm', 67),
(3, 3, 'Mock', 90);
18. Kuonyesha Data kwa SELECT

Kuonyesha classes:

SELECT * FROM classes;

Kuonyesha subjects:

SELECT * FROM subjects;

Kuonyesha students:

SELECT * FROM students;

Kuonyesha marks:

SELECT * FROM marks;
19. Kuonyesha Students na Darasa Lao

Kwa sababu students wanatumia class_id, tunahitaji JOIN kuona jina la darasa.

SELECT
students.id,
students.student_name,
students.sex,
classes.class_name,
students.phone,
students.address
FROM students
JOIN classes ON students.class_id = classes.id;
20. Kuonyesha Marks na Majina ya Wanafunzi
SELECT
students.student_name,
subjects.subject_name,
marks.exam_type,
marks.score
FROM marks
JOIN students ON marks.student_id = students.id
JOIN subjects ON marks.subject_id = subjects.id;
21. Kuupdate Table

Kubadilisha jina la mwanafunzi:

UPDATE students
SET student_name = 'Asha Mohamed'
WHERE id = 1;

Kubadilisha phone:

UPDATE students
SET phone = '0799999999'
WHERE id = 2;

Muhimu: usisahau WHERE, vinginevyo utabadilisha records zote.

22. Kufuta Record

Kufuta mwanafunzi mmoja:

DELETE FROM students WHERE id = 3;

Kufuta subject moja:

DELETE FROM subjects WHERE id = 5;

Muhimu: kama record inatumika kwenye foreign key, MySQL inaweza kukataa kufuta ili kulinda data.

23. ALTER TABLE: Kuongeza Column

Mfano kuongeza email kwenye students:

ALTER TABLE students ADD email VARCHAR(100);

Kuongeza date of birth:

ALTER TABLE students ADD date_of_birth DATE;
24. ALTER TABLE: Kubadilisha Column

Kubadilisha ukubwa wa phone:

ALTER TABLE students MODIFY phone VARCHAR(30);

Kubadilisha jina la column:

ALTER TABLE students CHANGE phone phone_number VARCHAR(30);
25. ALTER TABLE: Kufuta Column
ALTER TABLE students DROP COLUMN email;

Tumia kwa makini kwa sababu data ya column hiyo itapotea.

26. Kuonyesha Structure ya Table
DESCRIBE students;

Au:

SHOW COLUMNS FROM students;

Kuonyesha tables zote kwenye database:

SHOW TABLES;
27. Kufuta Table
DROP TABLE marks;

Hii hufuta table kabisa.

Kama hutaki error kama table haipo:

DROP TABLE IF EXISTS marks;
28. Kufuta Database
DROP DATABASE school_db;

Tumia kwa makini sana kwa sababu database yote itafutwa.

29. Makosa ya Kawaida kwa Beginners
Kusahau semicolon

Mbaya:

CREATE DATABASE school_db

Sahihi:

CREATE DATABASE school_db;
Kuandika jina la table vibaya

Mfano table ni students lakini ukaandika:

SELECT * FROM student;
Foreign key error

Inatokea ukiingiza class_id ambayo haipo kwenye classes.

Mfano:

INSERT INTO students (student_name, sex, class_id)
VALUES ('Peter', 'Male', 100);

Kama class id 100 haipo, utapata error.

Kusahau USE database

Kabla ya kucreate table, hakikisha umechagua database:

USE school_db;
30. Best Practices za Kucreate Database na Tables

Tumia majina ya tables kwa wingi:

students
users
classes
subjects
marks

Tumia underscore badala ya space:

Mbaya:

student name

Nzuri:

student_name

Kila table iwe na id.

Tumia created_at kwenye tables muhimu.

Tumia UNIQUE kwa email, username, class name, au subject name.

Tumia FOREIGN KEY kuunganisha tables.

Usihifadhi data inayojirudia sana kwenye table moja.

Panga database kwanza kabla ya kuanza PHP code.

31. Mfano wa Database ya Blog System

Kama unataka kutengeneza blog, unaweza kutumia tables hizi.

CREATE DATABASE IF NOT EXISTS blog_db;
USE blog_db;

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role VARCHAR(30) DEFAULT 'admin',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
category_id INT,
title VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
content TEXT NOT NULL,
image VARCHAR(255),
status ENUM('draft','published') DEFAULT 'draft',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (category_id) REFERENCES categories(id)
);
32. Mfano wa Database ya Login System
CREATE DATABASE IF NOT EXISTS login_db;
USE login_db;

CREATE TABLE roles (
id INT AUTO_INCREMENT PRIMARY KEY,
role_name VARCHAR(50) NOT NULL,
role_key VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role_id INT NOT NULL,
status ENUM('active','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (role_id) REFERENCES roles(id)
);

INSERT INTO roles (role_name, role_key) VALUES
('Admin', 'ADMIN'),
('Teacher', 'TEACHER'),
('Student', 'STUDENT');
33. Hitimisho

Kucreate database na table ni msingi wa kujifunza MySQL. Ukielewa database, tables, columns, rows, primary key, foreign key, na data types, utakuwa umeanza vizuri sana kwenye database design.

Kumbuka:

CREATE DATABASE database_name;
USE database_name;
CREATE TABLE table_name (...);

Baada ya hapo unaweza kuanza kutumia:

INSERT
SELECT
UPDATE
DELETE
JOIN
ALTER TABLE

Kwa mafunzo zaidi ya MySQL, PHP, HTML, CSS, Bootstrap na project kamili, tembelea:

👉 https://faulink.com