MySQL Database Course Kwa Beginners 2026: Jifunze Database, CRUD, SQL Code, DESC na Project ya Vitendo

MySQL Database Course Kwa Beginners 2026 – Jifunze SQL, CRUD, Tables, DESC na PHP Database
Meta Description: Jifunze MySQL database step by step kwa Kiswahili. Course hii inaeleza database, tables, SQL commands, CRUD, DESC, relationships na mifano ya code kwa beginners.

Link muhimu: https://faulink.com

1. MySQL Database ni nini?

Database ni mfumo wa kuhifadhi taarifa kwa mpangilio. Mfano, shule inaweza kuhifadhi taarifa za wanafunzi, walimu, madarasa, masomo na matokeo.

MySQL ni database system inayotumika sana kwenye website na software mbalimbali.

Mfano wa vitu vinavyoweza kuhifadhiwa kwenye database:

Majina ya wanafunzi
Barua pepe
Namba za simu
Marks
Malipo
Akaunti za watumiaji
Blog posts
Orders

Jifunze zaidi kupitia: https://faulink.com

2. Maneno Muhimu ya Database
Database

Ni sehemu kubwa inayohifadhi tables nyingi.

Table

Ni jedwali linalohifadhi data. Mfano students.

Column

Ni field ndani ya table. Mfano student_name, email, phone.

Row

Ni record moja ya data. Mfano mwanafunzi mmoja.

Primary Key

Ni column inayotambulisha record kipekee. Mara nyingi huitwa id.

SQL

Ni lugha ya kuwasiliana na database.

3. Kutengeneza Database
CREATE DATABASE school_db;

Kutumia database:

USE school_db;

Kuangalia databases zote:

SHOW DATABASES;

Kufuta database:

DROP DATABASE school_db;

Tahadhari: DROP DATABASE hufuta database yote.

Link ya kujifunza zaidi: https://faulink.com

4. Kutengeneza Table

Mfano tutengeneze table ya wanafunzi.

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
gender VARCHAR(20),
phone VARCHAR(20),
email VARCHAR(100),
class_name VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Maelezo:

id ni primary key.
AUTO_INCREMENT inaongeza namba moja kwa moja.
VARCHAR huhifadhi maandishi.
NOT NULL maana yake field lazima ijazwe.
TIMESTAMP huhifadhi muda.

5. Kuangalia Tables
SHOW TABLES;

Kuangalia muundo wa table:

DESC students;

Au:

DESCRIBE students;

Mfano wa matokeo:

Field Type Null Key Default
id int NO PRI NULL
student_name varchar(100) NO NULL
gender varchar(20) YES NULL
phone varchar(20) YES NULL
email varchar(100) YES NULL
class_name varchar(50) YES NULL
created_at timestamp YES CURRENT_TIMESTAMP

Link: https://faulink.com

6. CRUD ni nini?

CRUD ni operations kuu nne kwenye database:

C = Create — kuongeza data
R = Read — kusoma data
U = Update — kubadilisha data
D = Delete — kufuta data

7. CREATE: Kuongeza Data
INSERT INTO students
(student_name, gender, phone, email, class_name)
VALUES
('Juma Ally', 'Male', '0712345678', 'juma@gmail.com', 'Form One');

Kuongeza wanafunzi wengi kwa mara moja:

INSERT INTO students
(student_name, gender, phone, email, class_name)
VALUES
('Asha Said', 'Female', '0755555555', 'asha@gmail.com', 'Form Two'),
('John Peter', 'Male', '0766666666', 'john@gmail.com', 'Form Three'),
('Neema Joseph', 'Female', '0744444444', 'neema@gmail.com', 'Form One');
8. READ: Kusoma Data

Kuonyesha data zote:

SELECT * FROM students;

Kuonyesha columns maalumu:

SELECT student_name, phone, class_name FROM students;

Kuonyesha mwanafunzi mmoja kwa id:

SELECT * FROM students WHERE id = 1;

Kutafuta kwa class:

SELECT * FROM students WHERE class_name = 'Form One';

Kutafuta kwa jina:

SELECT * FROM students WHERE student_name LIKE '%Juma%';

Kupanga matokeo:

SELECT * FROM students ORDER BY student_name ASC;

Kupanga kuanzia mpya:

SELECT * FROM students ORDER BY id DESC;

Kupunguza idadi ya matokeo:

SELECT * FROM students LIMIT 10;

Link: https://faulink.com

9. UPDATE: Kubadilisha Data

Kubadilisha namba ya simu:

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

Kubadilisha class:

UPDATE students
SET class_name = 'Form Four'
WHERE student_name = 'Juma Ally';

Kubadilisha fields zaidi ya moja:

UPDATE students
SET phone = '0788888888',
email = 'newemail@gmail.com'
WHERE id = 2;

Muhimu: Usisahau WHERE, vinginevyo data zote zitabadilishwa.

10. DELETE: Kufuta Data

Kufuta mwanafunzi mmoja:

DELETE FROM students WHERE id = 1;

Kufuta wanafunzi wa class fulani:

DELETE FROM students WHERE class_name = 'Form One';

Kufuta data zote lakini table ibaki:

TRUNCATE TABLE students;

Kufuta table kabisa:

DROP TABLE students;
11. Data Types Muhimu za MySQL
INT
VARCHAR
TEXT
DATE
DATETIME
TIMESTAMP
DECIMAL
BOOLEAN

Mfano:

CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(150),
description TEXT,
price DECIMAL(10,2),
quantity INT,
is_active BOOLEAN DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
12. ALTER TABLE: Kubadilisha Table

Kuongeza column:

ALTER TABLE students ADD address VARCHAR(150);

Kubadilisha column:

ALTER TABLE students MODIFY phone VARCHAR(30);

Kubadilisha jina la column:

ALTER TABLE students CHANGE class_name student_class VARCHAR(50);

Kufuta column:

ALTER TABLE students DROP COLUMN address;

Kuangalia table baada ya mabadiliko:

DESC students;

Link: https://faulink.com

13. Constraints Muhimu
PRIMARY KEY
id INT AUTO_INCREMENT PRIMARY KEY
NOT NULL
student_name VARCHAR(100) NOT NULL
UNIQUE
email VARCHAR(100) UNIQUE
DEFAULT
status VARCHAR(20) DEFAULT 'active'
FOREIGN KEY

Hutumika kuunganisha tables.

14. Relationships Kwenye Database

Mfano: class moja inaweza kuwa na wanafunzi wengi.

CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(50) NOT NULL
);
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
class_id INT,
FOREIGN KEY (class_id) REFERENCES classes(id)
);

Kuingiza classes:

INSERT INTO classes (class_name)
VALUES ('Form One'), ('Form Two'), ('Form Three');

Kuingiza student:

INSERT INTO students (student_name, class_id)
VALUES ('Juma Ally', 1);
15. JOIN: Kuunganisha Tables
SELECT students.student_name, classes.class_name
FROM students
JOIN classes ON students.class_id = classes.id;

Matokeo yanaonyesha jina la mwanafunzi pamoja na darasa lake.

16. Project ya Vitendo: School Marks Database

Tutengeneze database ya matokeo ya wanafunzi.

CREATE DATABASE marks_system;
USE marks_system;
Table ya classes
CREATE TABLE classes (
id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(50) NOT NULL
);
Table ya subjects
CREATE TABLE subjects (
id INT AUTO_INCREMENT PRIMARY KEY,
subject_name VARCHAR(100) NOT NULL
);
Table ya students
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
gender VARCHAR(20),
class_id INT,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (class_id) REFERENCES classes(id)
);
Table ya marks
CREATE TABLE marks (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
test_name VARCHAR(50),
marks DECIMAL(5,2),
term VARCHAR(20),
exam_year YEAR,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (subject_id) REFERENCES subjects(id)
);
17. Kuingiza Data za Project
INSERT INTO classes (class_name)
VALUES ('Form One'), ('Form Two'), ('Form Three');
INSERT INTO subjects (subject_name)
VALUES ('Mathematics'), ('English'), ('Kiswahili'), ('Physics'), ('Biology');
INSERT INTO students (student_name, gender, class_id, phone)
VALUES
('Juma Ally', 'Male', 1, '0711111111'),
('Asha Said', 'Female', 1, '0722222222'),
('Neema John', 'Female', 2, '0733333333');
INSERT INTO marks (student_id, subject_id, test_name, marks, term, exam_year)
VALUES
(1, 1, 'Test 1', 78, 'Term 1', 2026),
(1, 2, 'Test 1', 65, 'Term 1', 2026),
(2, 1, 'Test 1', 88, 'Term 1', 2026);
18. Kusoma Marks kwa JOIN
SELECT
students.student_name,
classes.class_name,
subjects.subject_name,
marks.test_name,
marks.marks,
marks.term,
marks.exam_year
FROM marks
JOIN students ON marks.student_id = students.id
JOIN classes ON students.class_id = classes.id
JOIN subjects ON marks.subject_id = subjects.id;
19. Average, SUM na COUNT

Jumla ya marks za mwanafunzi:

SELECT student_id, SUM(marks) AS total_marks
FROM marks
GROUP BY student_id;

Average:

SELECT student_id, AVG(marks) AS average_marks
FROM marks
GROUP BY student_id;

Idadi ya tests:

SELECT student_id, COUNT(*) AS total_tests
FROM marks
GROUP BY student_id;

Marks kubwa:

SELECT MAX(marks) AS highest_marks FROM marks;

Marks ndogo:

SELECT MIN(marks) AS lowest_marks FROM marks;
20. Grade kwa kutumia CASE
SELECT
students.student_name,
subjects.subject_name,
marks.marks,
CASE
WHEN marks.marks >= 75 THEN 'A'
WHEN marks.marks >= 65 THEN 'B'
WHEN marks.marks >= 45 THEN 'C'
WHEN marks.marks >= 30 THEN 'D'
ELSE 'F'
END AS grade
FROM marks
JOIN students ON marks.student_id = students.id
JOIN subjects ON marks.subject_id = subjects.id;
21. Kutengeneza User Accounts Table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(100),
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
password VARCHAR(255),
role ENUM('admin','teacher','student') DEFAULT 'teacher',
status ENUM('active','inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
22. Backup ya Database

Kutumia terminal:

mysqldump -u root -p marks_system > marks_system_backup.sql

Kurestore backup:

mysql -u root -p marks_system < marks_system_backup.sql
23. Security Muhimu

Usihifadhi password plain text. Tumia password hashing kwenye PHP.

Mfano PHP:

$password = password_hash($_POST['password'], PASSWORD_DEFAULT);

Kuthibitisha password:

if (password_verify($entered_password, $stored_password)) {
echo "Login successful";
} else {
echo "Wrong password";
}
24. MySQL na PHP Connection
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "marks_system";

$conn = mysqli_connect($host, $user, $password, $database);

if (!$conn) {
die("Database connection failed: " . mysqli_connect_error());
}

echo "Connected successfully";
?>
25. PHP CRUD Example Rahisi
<?php
$conn = mysqli_connect("localhost", "root", "", "marks_system");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// CREATE
if (isset($_POST['add'])) {
$name = $_POST['student_name'];
$gender = $_POST['gender'];
$class_id = $_POST['class_id'];
$phone = $_POST['phone'];

$sql = "INSERT INTO students (student_name, gender, class_id, phone)
VALUES ('$name', '$gender', '$class_id', '$phone')";
mysqli_query($conn, $sql);
}

// DELETE
if (isset($_GET['delete'])) {
$id = $_GET['delete'];
mysqli_query($conn, "DELETE FROM students WHERE id=$id");
}

// UPDATE
if (isset($_POST['update'])) {
$id = $_POST['id'];
$name = $_POST['student_name'];
$phone = $_POST['phone'];

mysqli_query($conn, "UPDATE students SET student_name='$name', phone='$phone' WHERE id=$id");
}

// READ
$result = mysqli_query($conn, "SELECT * FROM students");
?>

<form method="POST">
<input type="text" name="student_name" placeholder="Student Name" required>
<select name="gender">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
<input type="number" name="class_id" placeholder="Class ID">
<input type="text" name="phone" placeholder="Phone">
<button type="submit" name="add">Add Student</button>
</form>

<table border="1" cellpadding="10">
<tr>
<th>ID</th>
<th>Name</th>
<th>Gender</th>
<th>Class ID</th>
<th>Phone</th>
<th>Action</th>
</tr>

<?php while ($row = mysqli_fetch_assoc($result)) { ?>
<tr>
<td><?= $row['id']; ?></td>
<td><?= $row['student_name']; ?></td>
<td><?= $row['gender']; ?></td>
<td><?= $row['class_id']; ?></td>
<td><?= $row['phone']; ?></td>
<td>
<a href="?delete=<?= $row['id']; ?>" onclick="return confirm('Delete this student?')">Delete</a>
</td>
</tr>
<?php } ?>
</table>
26. Best Practices za MySQL
Tumia id kama primary key.
Tumia majina ya tables kwa lowercase.
Usitumie spaces kwenye column names.
Tumia created_at na updated_at.
Tumia foreign keys kwa relationships.
Tumia backup mara kwa mara.
Usifute data bila WHERE.
Tumia DESC table_name kabla ya kuandika queries.
Tumia password hashing.
Tumia prepared statements kwenye PHP kwa security.
27. Commands Muhimu za Kukumbuka
CREATE DATABASE database_name;
USE database_name;
SHOW DATABASES;
SHOW TABLES;
CREATE TABLE table_name (...);
DESC table_name;
INSERT INTO table_name (...) VALUES (...);
SELECT * FROM table_name;
UPDATE table_name SET column='value' WHERE id=1;
DELETE FROM table_name WHERE id=1;
ALTER TABLE table_name ADD column_name VARCHAR(100);
DROP TABLE table_name;
28. Hitimisho

MySQL ni muhimu sana kwa kutengeneza website na systems kama:

School management system
Accounting system
Blog system
Login system
Online shop
Student marks system
Payment system

Ukielewa CREATE, READ, UPDATE, DELETE, DESC, JOIN, ALTER TABLE, PRIMARY KEY na FOREIGN KEY, utakuwa umeanza vizuri kujenga database systems za kitaalamu.

Kwa mafunzo zaidi tembelea: https://faulink.com