September 2, 2026 16 min read Database

MySQL Database Full Course – Somo la 19: ALTER TABLE – Jinsi ya Kubadilisha Structure ya Table Iliyopo Katika MySQL

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

Katika somo lililopita tulijifunza kuhusu:

NULL

IS NULL

IS NOT NULL

NOT NULL

na:

DEFAULT

Sasa tutajifunza jinsi ya kubadilisha structure ya table ambayo tayari imeshatengenezwa.

Command kuu tutakayotumia ni:

ALTER TABLE

ALTER TABLE ni muhimu sana kwa sababu baada ya kuanza kutumia database unaweza kugundua kuwa:

Umesahau kuongeza column
Unataka kubadilisha jina la column
Unataka kubadilisha data type
Unataka kuongeza DEFAULT
Unataka kuongeza NOT NULL
Unataka kufuta column
Unataka kubadilisha jina la table
Unataka kuongeza constraint mpya

Badala ya kufuta table na kuitengeneza upya, mara nyingi unaweza kutumia:

ALTER TABLE

Malengo ya Somo

Baada ya kumaliza somo hili utaweza:

Kuelewa ALTER TABLE
Kuongeza column mpya
Kutumia ADD COLUMN
Kuongeza columns nyingi
Kubadilisha data type
Kutumia MODIFY COLUMN
Kubadilisha jina la column
Kutumia RENAME COLUMN
Kufuta column
Kutumia DROP COLUMN
Kubadilisha jina la table
Kuongeza DEFAULT value
Kuondoa DEFAULT
Kuongeza au kubadilisha NOT NULL
Kuangalia structure kwa DESC
Kuelewa tahadhari kabla ya kubadilisha table

1. ALTER TABLE ni Nini?

ALTER TABLE ni SQL command inayotumika kubadilisha structure ya table iliyopo.

Mfano:

ALTER TABLE students
ADD email VARCHAR(150);

Hii inaongeza column mpya:

email

kwenye table ya:

students

2. Kwa Nini Tunahitaji ALTER TABLE?

Fikiria umetengeneza table:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100),
    gender VARCHAR(10)
);

Baada ya siku kadhaa unagundua unahitaji:

phone_number
email
address

Si lazima ufute table.

Unaweza kutumia:

ALTER TABLE

3. Tutumie Table ya Students

Tengeneza:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    gender VARCHAR(10),
    age INT
);

Angalia structure:

DESC students;

Unaweza kuona columns:

student_id
student_name
gender
age

4. ADD COLUMN

Kutengeneza column mpya tunatumia:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

Mfano:

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

5. Kuangalia Structure Baada ya ADD COLUMN

DESC students;

Sasa unaweza kuona:

student_id
student_name
gender
age
phone_number

6. COLUMN Keyword Si Lazima Kila Mara

MySQL inaweza pia kuruhusu:

ALTER TABLE students
ADD email VARCHAR(150);

Badala ya:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

Lakini kwa beginner, kutumia:

ADD COLUMN

inafanya query iwe wazi zaidi.

7. Kuongeza Address

ALTER TABLE students
ADD COLUMN address VARCHAR(200);

8. Kuongeza Date of Birth

ALTER TABLE students
ADD COLUMN date_of_birth DATE;

9. Kuongeza Status yenye DEFAULT

ALTER TABLE students
ADD COLUMN status VARCHAR(20) DEFAULT 'Active';

Records mpya zinaweza kupata:

Active

automatically ikiwa status haijatolewa.

10. Kuongeza Timestamp

ALTER TABLE students
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Hii inaweza kuhifadhi muda record ilitengenezwa.

11. Kuongeza Column Mwisho wa Table

Kwa kawaida:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

column inaweza kuongezwa mwisho wa structure.

12. Kuongeza Column Kwanza

MySQL inaweza kutumia:

FIRST

Mfano:

ALTER TABLE students
ADD COLUMN registration_number VARCHAR(50) FIRST;

Column mpya inaweza kuwekwa mwanzo wa table structure.

13. Kuongeza Column Baada ya Column Fulani

Tumia:

AFTER

Mfano:

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20)
AFTER student_name;

Hii inaweza kuweka phone_number baada ya student_name.

14. Je Position ya Column ni Muhimu?

Kwa database logic, position ya column mara nyingi si jambo muhimu sana.

Lakini inaweza kusaidia structure kuwa rahisi kusoma.

Mfano:

student_id
student_name
phone_number
email
gender
age

Badala ya kuwa na related columns zimechanganyika bila mpangilio.

15. Kuongeza Columns Nyingi

Unaweza kuongeza zaidi ya column moja kwa ALTER moja.

Mfano:

ALTER TABLE students
ADD COLUMN email VARCHAR(150),
ADD COLUMN class_name VARCHAR(50),
ADD COLUMN guardian_name VARCHAR(100);

16. Kuongeza Phone na Guardian Contact

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20),
ADD COLUMN guardian_contact VARCHAR(20);

17. MODIFY COLUMN ni Nini?

MODIFY COLUMN hutumika kubadilisha definition ya column iliyopo.

Mfano:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(200);

Hii inaweza kubadilisha:

VARCHAR(100)

kuwa:

VARCHAR(200)

18. Kubadilisha VARCHAR Size

Kabla:

student_name VARCHAR(100)

Baada:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(150);

Sasa inaweza kuwa:

student_name VARCHAR(150)

19. Kubadilisha INT

Fikiria column:

age INT

Unaweza kubadilisha data type ikiwa kuna sababu.

Mfano:

ALTER TABLE students
MODIFY COLUMN age TINYINT;

Lakini kabla ya kubadilisha data type hakikisha existing data inaweza kutoshea kwenye type mpya.

20. MODIFY COLUMN na NOT NULL

Fikiria:

student_name

inaruhusu NULL.

Unaweza kufanya:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(100) NOT NULL;

Sasa column hairuhusu NULL.

21. Tahadhari Kabla ya Kuongeza NOT NULL

Ikiwa tayari una records zenye:

student_name = NULL

query ya kuongeza NOT NULL inaweza kushindwa.

Kabla ya kufanya:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(100) NOT NULL;

angalia:

SELECT *
FROM students
WHERE student_name IS NULL;

22. Kubadilisha Column Kuruhusu NULL

Mfano:

ALTER TABLE students
MODIFY COLUMN phone_number VARCHAR(20) NULL;

Hii inaweza kuruhusu NULL.

23. Kubadilisha DEFAULT kwa MODIFY COLUMN

Mfano:

ALTER TABLE students
MODIFY COLUMN status VARCHAR(20)
NOT NULL DEFAULT 'Active';

24. CHANGE COLUMN

MySQL pia ina:

CHANGE COLUMN

ambayo inaweza kubadilisha jina na definition ya column.

Mfano:

ALTER TABLE students
CHANGE COLUMN phone phone_number VARCHAR(20);

Hapa:

phone

imebadilishwa kuwa:

phone_number

25. RENAME COLUMN

Katika versions za kisasa za MySQL unaweza kutumia:

ALTER TABLE students
RENAME COLUMN phone TO phone_number;

Hii ni rahisi kusoma ikiwa unataka kubadilisha jina pekee.

26. Mfano wa RENAME COLUMN

Fikiria ulikosea:

studnet_name

Badilisha:

ALTER TABLE students
RENAME COLUMN studnet_name TO student_name;

27. Mfano wa Typo ya Bookkeeping

Fikiria column imeandikwa:

bookeeping

badala ya:

bookkeeping

Unaweza kufanya:

ALTER TABLE students
RENAME COLUMN bookeeping TO bookkeeping;

Hii ni matumizi ya kawaida sana ya ALTER TABLE.

28. Kubadilisha Jina la Column kwa CHANGE COLUMN

Alternative:

ALTER TABLE students
CHANGE COLUMN bookeeping bookkeeping VARCHAR(100);

Kumbuka kwa CHANGE COLUMN mara nyingi unahitaji kutaja definition mpya ya column pia.

29. DROP COLUMN ni Nini?

DROP COLUMN hutumika kuondoa column kutoka kwenye table.

Mfano:

ALTER TABLE students
DROP COLUMN age;

Hii itaondoa:

age

na data yote iliyokuwemo kwenye column hiyo.

30. DROP COLUMN ni Hatari

Hii:

ALTER TABLE students
DROP COLUMN phone_number;

haiondoi jina la column tu.

Inaondoa pia data zote za phone numbers zilizopo katika column hiyo.

Kwa hiyo kabla ya kufanya DROP COLUMN, hakikisha:

Column kweli haitumiki
Data yake haihitajiki
Una backup ikiwa ni muhimu
Application code haitategemea column hiyo

31. Kuangalia Data Kabla ya DROP COLUMN

Kabla ya:

ALTER TABLE students
DROP COLUMN email;

unaweza kuangalia:

SELECT student_id, student_name, email
FROM students;

32. Kufuta Column ya Temporary Data

Mfano:

ALTER TABLE students
DROP COLUMN temporary_note;

ikiwa column hiyo haitumiki tena.

33. RENAME TABLE

Unaweza kubadilisha jina la table.

Mfano:

ALTER TABLE students
RENAME TO school_students;

Sasa table inaitwa:

school_students

34. Alternative ya RENAME TABLE

MySQL pia ina:

RENAME TABLE students TO school_students;

Hii inaweza kufanya kazi kwa kubadilisha jina la table.

35. Tahadhari ya Kubadilisha Jina la Table

Ukibadilisha:

students

kuwa:

school_students

PHP code yako ya zamani kama:

SELECT * FROM students;

haitafanya kazi mpaka uibadilishe kuwa:

SELECT * FROM school_students;

Kwa hiyo database structure na application code lazima ziende pamoja.

36. ADD DEFAULT

Fikiria column:

status

tayari ipo lakini haina default.

Unaweza kufanya:

ALTER TABLE students
ALTER COLUMN status SET DEFAULT 'Active';

Katika MySQL versions zinazounga syntax hii.

37. Alternative ya Kubadilisha DEFAULT

Njia inayotumika sana ni:

ALTER TABLE students
MODIFY COLUMN status VARCHAR(20)
DEFAULT 'Active';

Hapa lazima ujue current data type ya column.

38. DROP DEFAULT

Unaweza kuondoa default:

ALTER TABLE students
ALTER COLUMN status DROP DEFAULT;

Baada ya hapo status haitakuwa tena na default value hiyo.

39. ADD NOT NULL kwa Existing Column

Fikiria:

class_name

lazima iwepo.

Unaweza kufanya:

ALTER TABLE students
MODIFY COLUMN class_name VARCHAR(50) NOT NULL;

40. Kuondoa NOT NULL

Unaweza kuruhusu NULL:

ALTER TABLE students
MODIFY COLUMN class_name VARCHAR(50) NULL;

41. Kuongeza UNIQUE Constraint

Fikiria:

registration_number

inatakiwa kuwa unique.

Unaweza kufanya:

ALTER TABLE students
ADD CONSTRAINT uq_registration_number
UNIQUE (registration_number);

Sasa registration number mbili zinazofanana hazipaswi kuruhusiwa.

42. Short Syntax ya UNIQUE

Unaweza pia kukutana na:

ALTER TABLE students
ADD UNIQUE (registration_number);

Lakini named constraints ni rahisi zaidi kusimamia kwenye systems kubwa.

43. Tahadhari Kabla ya UNIQUE

Kabla ya:

ALTER TABLE students
ADD UNIQUE (registration_number);

hakikisha hakuna duplicates.

Mfano:

SELECT registration_number, COUNT(*)
FROM students
GROUP BY registration_number
HAVING COUNT(*) > 1;

Aggregate functions tutajifunza kwa undani baadaye.

44. Kuongeza Primary Key

Fikiria table:

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

Unaweza kuongeza Primary Key:

ALTER TABLE classes
ADD PRIMARY KEY (class_id);

45. Tahadhari ya ADD PRIMARY KEY

Primary Key inahitaji values kuwa:

Unique
NOT NULL

Ikiwa tayari una duplicate IDs, query inaweza kushindwa.

46. Kuondoa Primary Key

MySQL inaweza kutumia:

ALTER TABLE classes
DROP PRIMARY KEY;

Lakini usifanye hivi bila kuelewa relationships za database.

Primary Key ni muhimu sana.

47. Kuongeza AUTO_INCREMENT

Fikiria:

student_id

tayari ni integer na key.

Unaweza kubadilisha:

ALTER TABLE students
MODIFY COLUMN student_id INT NOT NULL AUTO_INCREMENT;

Lakini AUTO_INCREMENT ina requirements zake na mara nyingi inatumika kwenye indexed key column.

48. Kuongeza Index

Mfano:

ALTER TABLE students
ADD INDEX idx_student_name (student_name);

Index inaweza kusaidia search performance.

Tutajifunza Indexes kwa undani kwenye somo lake.

49. Kuondoa Index

Mfano:

ALTER TABLE students
DROP INDEX idx_student_name;

50. Kuongeza Foreign Key

Fikiria tuna:

students
classes

Unaweza kufanya:

ALTER TABLE students
ADD CONSTRAINT fk_students_class
FOREIGN KEY (class_id)
REFERENCES classes(class_id);

Foreign Keys tutajifunza kwa undani katika somo lake.

51. ALTER TABLE katika Mfumo wa Shule

Fikiria table ya students ilikuwa:

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

Baadaye mfumo unahitaji:

gender
date_of_birth
phone_number
class_id
status
created_at

Unaweza kuongeza:

ALTER TABLE students
ADD COLUMN gender VARCHAR(10),
ADD COLUMN date_of_birth DATE,
ADD COLUMN phone_number VARCHAR(20),
ADD COLUMN class_id INT,
ADD COLUMN status VARCHAR(20) DEFAULT 'Active',
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

52. ALTER TABLE katika Marks System

Fikiria table:

marks

ina:

mark_id
student_id
score

Unataka kuongeza:

subject_id
exam_type
term
academic_year

Unaweza kufanya:

ALTER TABLE marks
ADD COLUMN subject_id INT,
ADD COLUMN exam_type VARCHAR(50),
ADD COLUMN term VARCHAR(20),
ADD COLUMN academic_year YEAR;

53. ALTER TABLE katika Blog System

Fikiria table ya posts haina:

slug
featured_image
status
views

Unaweza kufanya:

ALTER TABLE posts
ADD COLUMN slug VARCHAR(255),
ADD COLUMN featured_image VARCHAR(255),
ADD COLUMN status VARCHAR(20) DEFAULT 'Draft',
ADD COLUMN views INT DEFAULT 0;

54. ALTER TABLE katika Accounting System

Fikiria table:

contributions

inahitaji:

reference_number
payment_method
status
created_at

Unaweza kufanya:

ALTER TABLE contributions
ADD COLUMN reference_number VARCHAR(100),
ADD COLUMN payment_method VARCHAR(50),
ADD COLUMN status VARCHAR(20) DEFAULT 'Pending',
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

55. Kubadilisha DECIMAL Size

Fikiria:

amount DECIMAL(8,2)

haitoshi kwa amounts kubwa.

Unaweza kufanya:

ALTER TABLE contributions
MODIFY COLUMN amount DECIMAL(12,2);

56. Kubadilisha VARCHAR kwenda TEXT

Fikiria:

description VARCHAR(255)

lakini unahitaji content ndefu zaidi.

Unaweza kufanya:

ALTER TABLE posts
MODIFY COLUMN description TEXT;

57. Kubadilisha TEXT kwenda VARCHAR

Inawezekana pia:

ALTER TABLE posts
MODIFY COLUMN title VARCHAR(255);

Lakini hakikisha existing data haitazidi size mpya.

58. Hatari ya Kupunguza Column Size

Fikiria:

student_name VARCHAR(200)

na una record yenye characters 150.

Ukibadilisha:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(50);

existing data inaweza kusababisha warning, truncation au error kulingana na SQL mode na data.

Kwa hiyo angalia data kwanza.

59. Kuangalia Longest Data Kabla ya Kupunguza VARCHAR

Mfano:

SELECT
    student_name,
    CHAR_LENGTH(student_name) AS name_length
FROM students
ORDER BY name_length DESC;

Hii inaweza kusaidia kujua size inayohitajika.

60. DESCRIBE Kabla ya ALTER

Kabla:

DESC students;

Hifadhi au elewa structure ya sasa.

Kisha fanya ALTER.

Baada:

DESC students;

Linganisha structure.

61. SHOW CREATE TABLE

Command muhimu zaidi ni:

SHOW CREATE TABLE students;

Hii inaweza kukuonyesha SQL definition ya table.

Ni muhimu kabla ya kufanya changes kubwa.

62. Workflow Salama ya ALTER TABLE

Njia nzuri ni:

1. Angalia structure
2. Angalia data
3. Tengeneza backup ikiwa ni muhimu
4. Andika ALTER TABLE
5. Run query
6. Angalia structure tena
7. Test application

63. Mfano wa Workflow

Kwanza:

DESC students;

Kisha:

SELECT *
FROM students
LIMIT 10;

Halafu:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

Baada:

DESC students;

64. Backup Kabla ya Structural Changes

Kwa production database, backup ni muhimu.

Kwa mfano unaweza kutengeneza copy ya table:

CREATE TABLE students_backup AS
SELECT *
FROM students;

Lakini kumbuka method hii inaweza isihifadhi constraints/indexes zote sawa na original table.

Kwa backup kamili, tools kama database dump zinaweza kuwa bora zaidi.

65. Makosa ya Kawaida – Column Tayari Ipo

Mfano:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

ikiwa email tayari ipo, MySQL inaweza kutoa error.

Kwanza:

DESC students;

66. Makosa ya Kawaida – Column Haipo

Mfano:

ALTER TABLE students
DROP COLUMN location;

lakini location haipo.

MySQL inaweza kutoa error.

67. Makosa ya Kawaida – Wrong Table

Mfano:

ALTER TABLE student
ADD COLUMN email VARCHAR(150);

lakini table ni:

students

Query itashindwa.

68. Makosa ya Kawaida – Wrong Data Type Syntax

Mfano mbaya:

ALTER TABLE students
ADD COLUMN email VARCHAR;

Kwa MySQL, VARCHAR inahitaji length.

Sahihi:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

69. Makosa ya Kawaida – Kusahau COLUMN Name

Mfano mbaya:

ALTER TABLE students
ADD VARCHAR(100);

Sahihi:

ALTER TABLE students
ADD COLUMN address VARCHAR(100);

70. Makosa ya Kawaida – DROP Bila Kuelewa Data Loss

Query:

ALTER TABLE students
DROP COLUMN phone_number;

inaweza kufuta phone numbers zote.

Usifanye kwa sababu tu hutaki column ionekane kwenye frontend.

Frontend visibility na database structure ni vitu tofauti.

71. Makosa ya Kawaida – Kubadilisha Data Type Bila Kuangalia Existing Data

Fikiria:

phone_number VARCHAR(20)

Ukibadilisha kuwa:

ALTER TABLE students
MODIFY COLUMN phone_number INT;

unaweza kupata matatizo kwa:

0712345678
+255712345678

Phone numbers ni identifiers, si numbers za kufanya arithmetic.

Kwa hiyo mara nyingi VARCHAR ni bora kwa phone numbers.

72. Kwa Nini Phone Number Iwe VARCHAR?

Phone number inaweza kuwa:

0712345678
+255712345678

Ikiwa unatumia integer unaweza:

Kupoteza leading zero
Kushindwa kuhifadhi +
Kukutana na size limitations

Kwa hiyo:

phone_number VARCHAR(20)

ni design nzuri zaidi.

73. ALTER TABLE na Existing Records

Ukiongeza:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

existing records zinaweza kuwa na:

email = NULL

ikiwa column inaruhusu NULL.

74. ADD COLUMN yenye NOT NULL

Fikiria una records tayari.

Ukiandika:

ALTER TABLE students
ADD COLUMN status VARCHAR(20) NOT NULL;

behavior inaweza kutegemea SQL mode na definition.

Ni vizuri kutoa default:

ALTER TABLE students
ADD COLUMN status VARCHAR(20)
NOT NULL DEFAULT 'Active';

75. Hii Inaweza Kujaza Existing Records

Kwa:

ALTER TABLE students
ADD COLUMN status VARCHAR(20)
NOT NULL DEFAULT 'Active';

existing rows zinaweza kupata:

Active

kwa column mpya.

76. ADD COLUMN kwa Position

Mfano:

ALTER TABLE students
ADD COLUMN middle_name VARCHAR(100)
AFTER student_name;

Structure inaweza kuwa:

student_id
student_name
middle_name
gender
...

77. MODIFY Position ya Existing Column

MySQL inaweza pia kutumia:

ALTER TABLE students
MODIFY COLUMN phone_number VARCHAR(20)
AFTER gender;

Hii inabadilisha definition/position ya column.

78. CHANGE COLUMN na Position

Mfano:

ALTER TABLE students
CHANGE COLUMN phone phone_number VARCHAR(20)
AFTER gender;

Hapa tumefanya:

Rename
Data type definition
Position

kwa query moja.

79. ALTER TABLE na Multiple Operations

Unaweza kufanya:

ALTER TABLE students
ADD COLUMN email VARCHAR(150),
ADD COLUMN status VARCHAR(20) DEFAULT 'Active',
MODIFY COLUMN student_name VARCHAR(150) NOT NULL;

Hii inafanya changes nyingi kwa statement moja.

80. Je Ni Bora Kufanya Changes Nyingi Kwa Pamoja?

Inaweza kuwa efficient, lakini kwa beginner ni rahisi zaidi kufanya change moja moja.

Mfano:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

Kagua.

Kisha:

ALTER TABLE students
ADD COLUMN status VARCHAR(20) DEFAULT 'Active';

Hii inaweza kusaidia kugundua error kwa urahisi.

81. ALTER TABLE Kwenye Production Database

Kwenye database yenye records nyingi sana, ALTER TABLE inaweza kuwa operation nzito.

Inaweza:

Kuchukua muda
Kufunga resources
Kuathiri performance
Kuhitaji migration planning

Kwa beginner na development database, hii si issue kubwa mara nyingi, lakini ni concept muhimu ya kujua mapema.

82. Database Migration ni Nini?

Katika professional software development, structural changes mara nyingi huandikwa kama:

Database migrations

Mfano migration inaweza kusema:

Add email column to students table

na SQL yake:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

Frameworks kama Laravel zina migration systems.

83. ALTER TABLE Katika PHP Project

Fikiria mfumo wako una table:

students

baadaye umeongeza field kwenye form:

Guardian Phone

PHP form pekee haitoshi.

Database pia inahitaji column:

ALTER TABLE students
ADD COLUMN guardian_phone VARCHAR(20);

84. Form na Database Lazima Zilingane

Ikiwa HTML form ina:

email

lakini database haina:

email

INSERT inaweza kushindwa.

Kwa hiyo:

Frontend Form
PHP Backend
Database Table

lazima zilingane.

85. Mfano wa Final Students Table

Baada ya ALTERs kadhaa unaweza kuwa na:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    registration_number VARCHAR(50) UNIQUE,
    student_name VARCHAR(150) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    date_of_birth DATE,
    phone_number VARCHAR(20),
    email VARCHAR(150),
    guardian_name VARCHAR(100),
    guardian_phone VARCHAR(20),
    class_name VARCHAR(50),
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

86. Mazoezi ya Somo

Tengeneza table:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100),
    gender VARCHAR(10)
);

Kisha:

Ongeza phone_number.
Ongeza email.
Ongeza date_of_birth.
Ongeza status yenye default Active.
Ongeza created_at.
Badilisha student_name kuwa VARCHAR(150).
Weka student_name kuwa NOT NULL.
Badilisha jina la phone_number kuwa student_phone.
Futa column ya email.
Angalia structure baada ya kila hatua.

87. Challenge ya Table ya Products

Tengeneza:

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(8,2)
);

Kisha:

Ongeza category.
Ongeza quantity yenye default 0.
Ongeza status yenye default Active.
Badilisha price kuwa DECIMAL(12,2).
Badilisha product_name kuwa VARCHAR(200) NOT NULL.
Ongeza created_at.
Rename quantity kuwa stock_quantity.
Futa status.
Angalia final structure.

88. Challenge ya School Management System

Tengeneza table:

school_students

yenye columns za msingi:

student_id
student_name
gender

Halafu tumia ALTER TABLE kuongeza:

registration_number
date_of_birth
phone_number
email
guardian_name
guardian_contact
class_name
status
created_at

Weka:

student_name = NOT NULL
status = Active by default
created_at = CURRENT_TIMESTAMP

Kisha:

DESC school_students;

uone final structure.

89. Challenge ya Kurekebisha Makosa

Fikiria table ina columns:

studnet_name
bookeeping
phone

Badilisha kuwa:

student_name
bookkeeping
phone_number

Mfano:

ALTER TABLE students
RENAME COLUMN studnet_name TO student_name;

ALTER TABLE students
RENAME COLUMN bookeeping TO bookkeeping;

ALTER TABLE students
RENAME COLUMN phone TO phone_number;

90. Maswali ya Kujipima

Jaribu kujibu:

ALTER TABLE inatumika kufanya nini?
ADD COLUMN inafanya nini?
DROP COLUMN inafanya nini?
MODIFY COLUMN inafanya nini?
RENAME COLUMN inafanya nini?
Kuna tofauti gani kati ya MODIFY na RENAME?
Unawezaje kuongeza email kwenye students?
Unawezaje kubadilisha VARCHAR(50) kuwa VARCHAR(150)?
Unawezaje kuweka NOT NULL?
Unawezaje kuongeza DEFAULT Active?
Kwa nini DROP COLUMN ni hatari?
Kwa nini ni vizuri kutumia DESC kabla na baada ya ALTER?
FIRST na AFTER zinafanya nini?
Kwa nini unapaswa kuangalia existing data kabla ya kubadilisha data type?
Database migration ni nini?

91. Muhtasari wa Somo

Katika somo hili tumejifunza command muhimu:

ALTER TABLE

Kuongeza column:

ALTER TABLE students
ADD COLUMN email VARCHAR(150);

Kubadilisha definition:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(150) NOT NULL;

Kubadilisha jina:

ALTER TABLE students
RENAME COLUMN phone TO phone_number;

Kufuta column:

ALTER TABLE students
DROP COLUMN email;

Kubadilisha jina la table:

ALTER TABLE students
RENAME TO school_students;

Kuongeza default:

ALTER TABLE students
ADD COLUMN status VARCHAR(20)
NOT NULL DEFAULT 'Active';

Tumejifunza pia kutumia:

DESC students;

na:

SHOW CREATE TABLE students;

kuangalia structure ya table.

Jambo muhimu zaidi ni:

ALTER TABLE hubadilisha structure ya database, hivyo structural changes zinapaswa kufanywa kwa umakini mkubwa, hasa ikiwa table tayari ina data.

Somo Linalofuata

Katika Somo la 20 tutajifunza:

DROP, DELETE na TRUNCATE – Tofauti na Matumizi Yake Katika MySQL

Tutajifunza:

DELETE FROM
TRUNCATE TABLE
DROP TABLE
Tofauti ya DELETE na TRUNCATE
Tofauti ya TRUNCATE na DROP
Tofauti ya DELETE na DROP
WHERE inatumika wapi
Table structure inabaki lini
AUTO_INCREMENT inaathirikaje
Hatari ya kila command
Ni command ipi utumie katika hali gani
Mifano ya school systems
Mifano ya test databases
Mazoezi na challenges

Hili litakuwa somo muhimu sana kwa kuelewa tofauti kati ya kufuta records, kusafisha table na kuondoa table kabisa.

🚀 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