September 1, 2026 12 min read Database

MySQL Database Full Course – Somo la 6: Jinsi ya Kutengeneza Table ya Kwanza kwa CREATE TABLE

Karibu kwenye Somo la 6 la MySQL Database Full Course kwa Kiswahili.

Katika somo lililopita tulijifunza MySQL Data Types kama:

INT
VARCHAR
TEXT
DATE
TIMESTAMP
DECIMAL
BOOLEAN

Sasa tutajifunza jinsi ya kutumia Data Types hizo kutengeneza table halisi ndani ya database yetu.

Database yetu ni:

school_management

Na table yetu ya kwanza itakuwa:

students

Malengo ya Somo

Baada ya kumaliza somo hili utaweza:

Kuelewa table ni nini kwa vitendo
Kutumia CREATE TABLE
Kutengeneza columns
Kuweka Data Types kwenye columns
Kutumia SHOW TABLES
Kutumia DESCRIBE
Kutumia DESC
Kutengeneza table kupitia phpMyAdmin
Kutumia IF NOT EXISTS
Kuelewa makosa ya kawaida ya CREATE TABLE
Kuanza kutengeneza structure ya database halisi

1. Table ni Nini?

Katika relational database, table ni sehemu inayohifadhi records zinazohusiana.

Mfano:

students

inaweza kuhifadhi taarifa za wanafunzi.

Table hiyo inaweza kuwa na columns kama:

id
student_name
gender
date_of_birth
phone_number
email
address
created_at

Kila mwanafunzi atakuwa row moja.

2. Database Yetu

Kabla ya kutengeneza table, hakikisha database yetu ipo:

CREATE DATABASE IF NOT EXISTS school_management;

Kisha ichague:

USE school_management;

Sasa commands zinazofuata zitafanya kazi ndani ya:

school_management

3. CREATE TABLE ni Nini?

CREATE TABLE ni SQL command inayotumika kutengeneza table mpya.

Syntax ya msingi ni:

CREATE TABLE table_name (
    column_name data_type,
    column_name data_type
);

Mfano:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150)
);

Hii inatengeneza table inayoitwa:

students

yenye columns mbili:

id
student_name

4. Kutengeneza Table Yetu ya Kwanza

Fungua phpMyAdmin.

Chagua database:

school_management

Kisha fungua SQL tab.

Run:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150),
    gender VARCHAR(20),
    date_of_birth DATE,
    phone_number VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    created_at TIMESTAMP
);

Hii itatengeneza table ya students.

5. Kuchambua Query Yetu

Query yetu:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150),
    gender VARCHAR(20),
    date_of_birth DATE,
    phone_number VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    created_at TIMESTAMP
);

Ina sehemu kadhaa.

6. CREATE TABLE

Sehemu:

CREATE TABLE

inaambia MySQL:

Nataka kutengeneza table mpya.

7. students

Sehemu:

students

ndilo jina la table.

Kwa hiyo:

CREATE TABLE students

ina maana:

Tengeneza table inayoitwa students.

8. Parentheses

Columns zinawekwa ndani ya:

(
)

Mfano:

CREATE TABLE students (
    ...
);

Ndani ya parentheses ndipo tunafafanua structure ya table.

9. Column ya id

Tuna:

id INT

Hapa:

id

ni jina la column.

Na:

INT

ni Data Type.

Column hii itahifadhi integer.

Mfano:

10. Column ya student_name

Tuna:

student_name VARCHAR(150)

Hii itahifadhi majina ya wanafunzi.

Mfano:

Asha Mohamed
John Peter
Neema Joseph

11. Column ya gender

Tuna:

gender VARCHAR(20)

Inaweza kuhifadhi data kama:

Male
Female

Baadaye tutajifunza constraints na options nyingine za kudhibiti values.

12. Column ya date_of_birth

Tuna:

date_of_birth DATE

Inaweza kuhifadhi tarehe kama:

2008-04-15

Format:

YYYY-MM-DD

13. Column ya phone_number

Tuna:

phone_number VARCHAR(20)

Hatukutumia INT.

Kwa nini?

Kwa sababu phone number ni identifier, si number ya kufanya calculations.

Inaweza pia kuwa na:

0
+
country code

Mfano:

0712345678
+255712345678

14. Column ya email

Tuna:

email VARCHAR(255)

Inaweza kuhifadhi:

student@example.com

15. Column ya address

Tuna:

address TEXT

Kwa sababu address inaweza kuwa na maelezo marefu.

16. Column ya created_at

Tuna:

created_at TIMESTAMP

Inaweza kuhifadhi muda ambao record iliundwa.

Baadaye tutaboresha kuwa:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

ili timestamp iwekwe automatically.

17. Comma Kati ya Columns

Columns hutenganishwa kwa comma:

Mfano:

id INT,
student_name VARCHAR(150),
gender VARCHAR(20)

Column ya mwisho haihitaji comma kabla ya kufunga ).

Mfano sahihi:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150),
    gender VARCHAR(20)
);

18. Kosa la Trailing Comma

Mfano huu unaweza kusababisha error:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150),
);

Tatizo ni comma hii:

kabla ya:

Kwa hiyo kuwa makini na syntax.

19. SHOW TABLES

Baada ya kutengeneza table, tunaweza kuangalia tables zilizopo.

Tumia:

SHOW TABLES;

Result inaweza kuwa:

students

Hii inaonyesha table imetengenezwa.

20. DESCRIBE

Ili kuona structure ya table, tumia:

DESCRIBE students;

Unaweza kuona columns kama:

id
student_name
gender
date_of_birth
phone_number
email
address
created_at

Pamoja na Data Types zake.

21. DESC

Unaweza pia kutumia shortcut:

DESC students;

DESC na DESCRIBE zinaweza kutumika kuangalia structure ya table.

22. Result ya DESCRIBE

Unaweza kuona structure kama:

+---------------+--------------+
| Field         | Type         |
+---------------+--------------+
| id            | int          |
| student_name  | varchar(150) |
| gender        | varchar(20)  |
| date_of_birth | date         |
| phone_number  | varchar(20)  |
| email         | varchar(255) |
| address       | text         |
| created_at    | timestamp    |
+---------------+--------------+

Hii ndiyo schema ya table yetu.

23. Schema ni Nini?

Kwa lugha rahisi, schema ni structure ya database au table.

Kwa mfano structure ya:

students

ni:

id
student_name
gender
date_of_birth
phone_number
email
address
created_at

Pamoja na Data Types na constraints zake.

24. Table Bado Haina Data

Baada ya CREATE TABLE, table ipo lakini bado inaweza kuwa haina records.

Mfano:

students

+----+--------------+
| id | student_name |
+----+--------------+

Hakuna mwanafunzi bado.

Tutaanza kuingiza data katika somo la INSERT.

25. CREATE TABLE IF NOT EXISTS

Kama ukirudia:

CREATE TABLE students (
    id INT
);

wakati table tayari ipo, MySQL inaweza kutoa error.

Unaweza kutumia:

CREATE TABLE IF NOT EXISTS students (
    id INT,
    student_name VARCHAR(150)
);

Hii inamaanisha:

Tengeneza table hii kama bado haipo.

26. Tahadhari ya IF NOT EXISTS

IF NOT EXISTS haibadilishi structure ya table iliyopo.

Mfano table tayari ina:

id
student_name

Halafu una-run:

CREATE TABLE IF NOT EXISTS students (
    id INT,
    student_name VARCHAR(150),
    gender VARCHAR(20)
);

MySQL haitumii query hiyo kuongeza gender kwenye table iliyopo.

Kwa kubadilisha table, tutatumia:

ALTER TABLE

katika somo la baadaye.

27. Kutengeneza Table Kupitia phpMyAdmin

Unaweza pia kutumia interface ya phpMyAdmin.

Hatua:

Chagua database school_management.
Chagua Create table.
Andika jina:

students

Weka idadi ya columns.
Jaza majina ya columns.
Chagua Data Types.
Save.

Mfano:

id             INT
student_name   VARCHAR(150)
gender         VARCHAR(20)
date_of_birth  DATE

Lakini tunapendelea kujifunza SQL ili uelewe commands zenyewe.

28. Naming Convention ya Tables

Katika course hii tutatumia table names za plural.

Mfano:

students
teachers
classes
subjects
exams
marks
users
schools

Hii ni style yetu ya course.

Muhimu zaidi ni consistency.

29. Naming Convention ya Columns

Tutatumia:

snake_case

Mfano:

student_name
date_of_birth
phone_number
created_at
class_id

Badala ya:

StudentName
DateOfBirth
PhoneNumber

Styles zote zinaweza kutumika kulingana na project, lakini consistency ndiyo muhimu.

30. Kutengeneza Table ya Classes

Tunaweza pia kujaribu table rahisi:

CREATE TABLE classes (
    id INT,
    class_name VARCHAR(100)
);

Sasa run:

SHOW TABLES;

Unaweza kuona:

classes
students

31. Kutengeneza Table ya Subjects

Mfano:

CREATE TABLE subjects (
    id INT,
    subject_name VARCHAR(150)
);

Sasa database yetu inaanza kuwa na structure:

school_management
│
├── students
├── classes
└── subjects

32. Kutengeneza Table ya Exams

Mfano:

CREATE TABLE exams (
    id INT,
    exam_name VARCHAR(100),
    exam_date DATE
);

Inaweza kuhifadhi:

Midterm
Pre-Mock
Mock
Final Exam

33. Kutengeneza Table ya Marks

Mfano wa msingi:

CREATE TABLE marks (
    id INT,
    student_id INT,
    subject_id INT,
    exam_id INT,
    marks DECIMAL(5,2)
);

Hapa unaona structure inaanza kuhusiana na masomo ya Primary Key na Foreign Key tuliyojifunza.

Lakini bado hatujaweka constraints.

Tutafanya hivyo baadaye.

34. Database Yetu Sasa

Baada ya kujaribu tables hizo tunaweza kuwa na:

school_management
│
├── students
├── classes
├── subjects
├── exams
└── marks

Huu ni mwanzo wa database ya mfumo halisi.

35. Je Tutengeneze Tables Zote Sasa?

Si lazima.

Lengo la somo hili ni kuelewa:

CREATE TABLE

Tutajenga tables za project hatua kwa hatua kadri tunavyojifunza:

Primary Key
AUTO_INCREMENT
NOT NULL
UNIQUE
DEFAULT
Foreign Keys
Relationships

Hivyo table structure zetu zitakuwa bora zaidi baadaye.

36. Table ya Students ya Sasa Si Final

Kwa sasa tunaweza kuwa na:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150),
    gender VARCHAR(20),
    date_of_birth DATE,
    phone_number VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    created_at TIMESTAMP
);

Lakini baadaye tutaboresha:

id

kuwa Primary Key na AUTO_INCREMENT.

Pia tutatumia constraints nyingine.

37. Common Error: No Database Selected

Unaweza kupata error kama:

No database selected

ikiwa umejaribu CREATE TABLE bila kuchagua database.

Suluhisho:

USE school_management;

Kisha run CREATE TABLE.

38. Common Error: Table Already Exists

Ukiona:

Table 'students' already exists

ina maana table tayari ipo.

Unaweza kuangalia kwa:

SHOW TABLES;

Usifute table bila sababu.

39. Common Error: SQL Syntax Error

Mfano:

CREATE TABLE students (
    id INT
    student_name VARCHAR(150)
);

Hapa tumekosa comma baada ya:

id INT

Sahihi:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150)
);

40. Common Error: Data Type Haijawekwa

Mfano mbaya:

CREATE TABLE students (
    id,
    student_name
);

MySQL inahitaji Data Type kwa columns.

Sahihi:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150)
);

41. Common Error: Parentheses

Mfano mbaya:

CREATE TABLE students
    id INT,
    student_name VARCHAR(150);

Sahihi:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150)
);

42. Common Error: Wrong Data Type

Mfano:

phone_number INT

inaweza kufanya leading zero ipotee.

Bora:

phone_number VARCHAR(20)

Kumbuka somo letu la Data Types.

43. Kuangalia Table kupitia phpMyAdmin

Chagua:

school_management

kisha:

students

Unaweza kuona tabs kama:

Browse
Structure
SQL
Search
Insert
Export
Import
Operations

Structure inaonyesha columns.

44. Structure Tab

Kwenye Structure unaweza kuona:

Column
Type
Null
Default
Extra

Baadaye utaona pia:

Primary Key
Indexes
AUTO_INCREMENT

tunapoanza kutumia constraints.

45. Browse Tab

Browse hutumika kuonyesha records zilizopo kwenye table.

Kwa sasa table yetu inaweza kuwa empty.

Baadaye baada ya INSERT, Browse itaonyesha wanafunzi.

46. Insert Tab

phpMyAdmin ina tab ya:

Insert

ambayo inaweza kutumika kuingiza data kupitia form.

Lakini tutajifunza kutumia:

INSERT INTO

kwa SQL.

47. SQL Tab

SQL tab ndiyo sehemu muhimu sana kwenye course yetu.

Hapa unaweza kuandika:

SHOW TABLES;

DESC students;

na queries nyingine.

48. CREATE TABLE na Data Types

Muundo wa msingi ni:

CREATE TABLE
      ↓
Table Name
      ↓
Columns
      ↓
Data Types

Mfano:

CREATE TABLE students (
    id INT,
    student_name VARCHAR(150)
);

49. Kila Column Ina Kazi Maalumu

Usiongeze columns bila kuelewa matumizi yake.

Kwa mfano:

students

inaweza kuhitaji:

id
student_name
gender
date_of_birth
class_id
phone_number

Lakini si lazima kuweka:

subject1
subject2
subject3

kwa sababu subjects zinaweza kuwa table nyingine.

Hii inarudi kwenye database relationships tulizojifunza.

50. Usitengeneze Table Moja Kubwa Sana

Mfano mbaya:

students

id
student_name
class_name
school_name
subject1
subject2
subject3
teacher1
teacher2
exam1
exam2
mark1
mark2

Hii inaweza kufanya database kuwa ngumu.

Badala yake:

students
classes
schools
subjects
teachers
exams
marks

51. CREATE TABLE ya Classes

Mfano mzuri wa msingi:

CREATE TABLE classes (
    id INT,
    class_name VARCHAR(100)
);

52. CREATE TABLE ya Schools

Mfano:

CREATE TABLE schools (
    id INT,
    school_name VARCHAR(200),
    phone_number VARCHAR(20),
    email VARCHAR(255),
    address TEXT
);

53. CREATE TABLE ya Teachers

Mfano:

CREATE TABLE teachers (
    id INT,
    teacher_name VARCHAR(150),
    gender VARCHAR(20),
    phone_number VARCHAR(20),
    email VARCHAR(255)
);

54. CREATE TABLE ya Users

Mfano wa msingi:

CREATE TABLE users (
    id INT,
    username VARCHAR(100),
    email VARCHAR(255),
    password VARCHAR(255)
);

Kumbuka:

Password halisi hatutahifadhi plain text.

Baadaye tutajifunza password hashing.

55. Table Structure Inapaswa Kutokana na Requirements

Kabla ya CREATE TABLE, jiulize:

Table hii inawakilisha entity gani?
Inahitaji data gani?
Kila column inatumika kufanya nini?
Data Type gani inafaa?
Je data hii inapaswa kuwa table nyingine?
Je kuna relationship?

Hii ndiyo database design.

56. Mfano wa Requirements

Mteja anasema:

Nataka kuhifadhi jina la mwanafunzi, jinsia, tarehe ya kuzaliwa, simu na email.

Tunaweza kupata columns:

student_name
gender
date_of_birth
phone_number
email

Kisha Data Types:

student_name   VARCHAR(150)
gender         VARCHAR(20)
date_of_birth  DATE
phone_number   VARCHAR(20)
email          VARCHAR(255)

Kisha:

CREATE TABLE students (
    student_name VARCHAR(150),
    gender VARCHAR(20),
    date_of_birth DATE,
    phone_number VARCHAR(20),
    email VARCHAR(255)
);

Hii ndiyo process ya kubadilisha requirements kuwa database structure.

57. Table Names Ziwe na Maana

Epuka:

table1
tbl1
data
records
things

Tumia:

students
teachers
subjects
payments
orders
products

Jina la table linapaswa kueleza kinachohifadhiwa.

58. Column Names Ziwe na Maana

Epuka:

a
b
x1
data1
field2

Tumia:

student_name
phone_number
date_of_birth
amount
paid_date

Hii inarahisisha maintenance.

59. DROP TABLE

Kuna SQL command ya kufuta table:

DROP TABLE students;

Lakini usiitumie kwenye table yenye data muhimu bila uhakika.

DROP TABLE inaweza kuondoa table pamoja na data yake.

Tutajifunza DROP kwa undani katika somo maalumu.

60. DROP TABLE IF EXISTS

Kwa table ya mazoezi unaweza kuona:

DROP TABLE IF EXISTS test_table;

Hii inafuta test_table kama ipo.

Lakini usitumie kwenye students yetu kwa sababu tunataka kuendelea nayo.

61. Table ya Mazoezi

Tengeneza:

CREATE TABLE test_table (
    id INT,
    name VARCHAR(100)
);

Angalia:

SHOW TABLES;

Kisha:

DESC test_table;

Baada ya mazoezi unaweza kuifuta:

DROP TABLE test_table;

62. Query Kamili ya Somo

Run:

USE school_management;

CREATE TABLE IF NOT EXISTS students (
    id INT,
    student_name VARCHAR(150),
    gender VARCHAR(20),
    date_of_birth DATE,
    phone_number VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    created_at TIMESTAMP
);

SHOW TABLES;

DESCRIBE students;

Hii ndiyo practice kuu ya somo.

63. Je Table Yetu Iko Professional Tayari?

Bado.

Table yetu ina structure nzuri ya mwanzo, lakini bado tunahitaji:

Primary Key
AUTO_INCREMENT
NOT NULL
UNIQUE
DEFAULT
Foreign Key
Indexes

Tutazijifunza hatua kwa hatua.

64. Kwa Nini Hatukuweka Primary Key Sasa?

Tulijifunza concept ya Primary Key mapema.

Lakini tunataka kuielewa vizuri kwa SQL katika somo linalofuata.

Tutabadilisha:

id INT

kuwa:

id INT AUTO_INCREMENT PRIMARY KEY

Hapo MySQL itakuwa inaweza kutengeneza ID automatically.

65. Structure Tunayoelekea

Table yetu itabadilika kutoka:

id INT

kwenda:

id INT AUTO_INCREMENT PRIMARY KEY

Na:

student_name VARCHAR(150)

inaweza kuwa:

student_name VARCHAR(150) NOT NULL

Hapa ndipo constraints zinaanza.

66. Maswali ya Kujipima

Swali la 1

CREATE TABLE inafanya nini?

Swali la 2

Query hii inatengeneza nini?

CREATE TABLE classes (
    id INT,
    class_name VARCHAR(100)
);

Swali la 3

Kwa nini kila column inahitaji Data Type?

Swali la 4

SHOW TABLES; inafanya nini?

Swali la 5

DESCRIBE students; inafanya nini?

Swali la 6

Kuna tofauti gani kati ya database na table?

Swali la 7

Kwa nini tunatumia comma kati ya columns?

Swali la 8

Kosa liko wapi hapa?

CREATE TABLE users (
    id INT
    username VARCHAR(100)
);

Swali la 9

Kwa nini phone_number inaweza kutumia VARCHAR?

Swali la 10

CREATE TABLE IF NOT EXISTS ina faida gani?

67. Zoezi la Kwanza

Tengeneza table:

teachers

iwe na:

id
teacher_name
gender
phone_number
email
created_at

Jaribu kuchagua Data Types mwenyewe.

Mfano:

CREATE TABLE teachers (
    id INT,
    teacher_name VARCHAR(150),
    gender VARCHAR(20),
    phone_number VARCHAR(20),
    email VARCHAR(255),
    created_at TIMESTAMP
);

68. Zoezi la Pili

Tengeneza table:

subjects

iwe na:

id
subject_name
subject_code

Mfano:

CREATE TABLE subjects (
    id INT,
    subject_name VARCHAR(150),
    subject_code VARCHAR(20)
);

69. Zoezi la Tatu

Tengeneza table:

payments

iwe na:

id
student_id
amount
paid_date
remarks

Fikiria Data Types.

Mfano:

CREATE TABLE payments (
    id INT,
    student_id INT,
    amount DECIMAL(12,2),
    paid_date DATE,
    remarks TEXT
);

70. Checklist ya Somo

Kabla ya kwenda somo linalofuata, hakikisha unaweza:

[ ] Kuchagua database kwa USE
[ ] Kutumia CREATE TABLE
[ ] Kutengeneza columns
[ ] Kuweka Data Types
[ ] Kutenganisha columns kwa comma
[ ] Kutumia SHOW TABLES
[ ] Kutumia DESCRIBE
[ ] Kutumia DESC
[ ] Kutumia IF NOT EXISTS
[ ] Kuelewa kwamba table bado inaweza kuwa empty

Hitimisho

Hongera. Sasa umeanza kujenga structure halisi ya database.

Tumeanza na:

school_management

kisha tukatengeneza:

students

kwa:

CREATE TABLE

Tumetumia Data Types tulizojifunza katika somo lililopita.

Mtiririko wetu sasa ni:

DATABASE
school_management
      ↓
TABLE
students
      ↓
COLUMNS
id
student_name
gender
date_of_birth
phone_number
email
address
created_at

Lakini bado tunahitaji njia nzuri ya kutambua kila record uniquely.

Hapo ndipo Primary Key na AUTO_INCREMENT zinapoingia.

Somo Linalofuata

Somo la 7: Primary Key na AUTO_INCREMENT katika MySQL

Katika Somo la 7 tutajifunza:

Primary Key ni nini kwa vitendo
Kwa nini Primary Key ni muhimu
PRIMARY KEY
AUTO_INCREMENT
Jinsi MySQL inavyotengeneza ID automatically
Unique identifier
Natural Key na Surrogate Key kwa ufupi
Jinsi ya kutengeneza id INT AUTO_INCREMENT PRIMARY KEY
Jinsi ya kuona Primary Key kwa DESCRIBE
Makosa ya kawaida ya Primary Key
Jinsi ya kuboresha table yetu ya students

Tutabadilisha table yetu kuwa na structure kama:

id INT AUTO_INCREMENT PRIMARY KEY

Baada ya hapo tutakuwa tayari kuanza kuingiza records kwa INSERT INTO.

🚀 Unahitaji mfumo au website ya biashara?

Chagua huduma hapa chini kisha mteja bofya moja kwa moja kwenda kwenye ukurasa wa huduma au kuwasiliana nasi kwa WhatsApp.

🌐 Website Design 🏫 Mfumo wa Shule 🗄️Mfumo wa Mauzo 💳 Mfumo wa vikoba 💳 Mfumo wa Ratiba 💬 Chat WhatsApp

Share this post

Previous Next