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

Katika somo lililopita tulijifunza kutumia:

ALTER TABLE

kubadilisha structure ya table iliyopo.

Tuliona jinsi ya:

ADD COLUMN
MODIFY COLUMN
RENAME COLUMN
DROP COLUMN

Sasa tutajifunza commands tatu ambazo mara nyingi huwachanganya beginners:

DELETE

TRUNCATE

na:

DROP

Commands hizi zote zinahusiana na kuondoa data, lakini hazifanyi kazi sawa.

Kwa mfano:

DELETE FROM students;

TRUNCATE TABLE students;

na:

DROP TABLE students;

zinaonekana kama zote zinafuta kitu, lakini matokeo yake yana tofauti kubwa sana.

Hili ni somo muhimu sana kwa sababu kutumia command isiyo sahihi kunaweza kusababisha kupoteza data au hata table nzima.

Malengo ya Somo

Baada ya kumaliza somo hili utaweza:

• Kuelewa DELETE
• Kuelewa TRUNCATE
• Kuelewa DROP
• Kutofautisha commands hizo tatu
• Kujua ni command ipi inaweza kutumia WHERE
• Kujua ni command ipi inaacha table structure
• Kujua ni command ipi inaondoa table nzima
• Kuelewa athari kwenye AUTO_INCREMENT
• Kufuta record moja kwa usalama
• Kusafisha table nzima
• Kufuta table
• Kufuta database
• Kutumia commands hizi kwa usalama
• Kuepuka data loss

1. Kwa Nini DELETE, TRUNCATE na DROP Zinachanganya?

Fikiria tuna table:

students

yenye records 1,000.

Unaweza kuandika:

DELETE FROM students;

au:

TRUNCATE TABLE students;

au:

DROP TABLE students;

Queries zote zinaweza kufanya data isipatikane tena.

Lakini tofauti ni:

DELETE
→ Inafuta records.

TRUNCATE
→ Inaondoa records zote kwenye table.

DROP
→ Inaondoa table yenyewe.

2. DELETE ni Nini?

DELETE hutumika kuondoa records kutoka kwenye table.

Syntax:

DELETE FROM table_name
WHERE condition;

Mfano:

DELETE FROM students
WHERE student_id = 5;

Hii inafuta student mmoja.

3. DELETE Inaweza Kutumia WHERE

Hii ndiyo advantage kubwa ya DELETE.

Unaweza kulenga record maalumu.

Mfano:

DELETE FROM students
WHERE student_id = 10;

Au:

DELETE FROM students
WHERE class_name = 'Form Four';

4. DELETE Bila WHERE

Ukiandika:

DELETE FROM students;

records zote zinaweza kufutwa.

Lakini table:

students

itaendelea kuwepo.

5. Mfano wa Table ya Students

Tengeneza:

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

Ingiza data:

INSERT INTO students (
    student_name,
    gender,
    class_name
)
VALUES
('Asha Mussa', 'Female', 'Form One'),
('John Peter', 'Male', 'Form Two'),
('Neema Joseph', 'Female', 'Form One'),
('Peter Michael', 'Male', 'Form Three');

6. Kuangalia Data

SELECT *
FROM students;

Unaweza kupata:

1  Asha Mussa      Female  Form One
2  John Peter      Male    Form Two
3  Neema Joseph    Female  Form One
4  Peter Michael   Male    Form Three

7. DELETE Record Moja

DELETE FROM students
WHERE student_id = 2;

Baada ya hapo:

SELECT *
FROM students;

John Peter hatakuwepo.

8. DELETE Records Nyingi

Mfano:

DELETE FROM students
WHERE class_name = 'Form One';

Records zote zinazolingana na:

Form One

zinaweza kufutwa.

9. Workflow Salama ya DELETE

Kabla ya:

DELETE FROM students
WHERE class_name = 'Form One';

fanya:

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

Hakikisha matokeo ndiyo unayotaka kufuta.

Kisha ndiyo ufanye DELETE.

10. DELETE Haiondoi Table

Baada ya:

DELETE FROM students;

unaweza bado kufanya:

DESC students;

Structure inaweza kubaki:

student_id
student_name
gender
class_name

11. Unaweza Kuingiza Data Tena Baada ya DELETE

Kwa sababu table bado ipo:

INSERT INTO students (
    student_name,
    gender,
    class_name
)
VALUES (
    'Anna Charles',
    'Female',
    'Form Two'
);

Hii inaweza kufanya kazi.

12. AUTO_INCREMENT Baada ya DELETE

Fikiria ulikuwa na:

1
2
3
4
5

Halafu:

DELETE FROM students;

Ukiongeza record mpya, AUTO_INCREMENT kwa kawaida inaweza kuendelea na value iliyofuata.

Mfano:

6

badala ya kurudi moja.

Behavior halisi inaweza pia kutegemea operation na table configuration, lakini DELETE yenyewe haitumiki kama njia ya kawaida ya ku-reset AUTO_INCREMENT.

13. Kufuta Record Hakujazi ID Gap

Fikiria:

1
2
3
4

Umefuta ID 3:

DELETE FROM students
WHERE student_id = 3;

Unaweza kubaki na:

1
2
4

Hili si tatizo.

14. TRUNCATE ni Nini?

TRUNCATE hutumika kuondoa records zote kutoka kwenye table.

Syntax:

TRUNCATE TABLE table_name;

Mfano:

TRUNCATE TABLE students;

15. TRUNCATE Haina WHERE

Huwezi kufanya:

TRUNCATE TABLE students
WHERE student_id = 5;

Hiyo si syntax sahihi.

TRUNCATE inalenga table nzima.

16. TRUNCATE Inaacha Table Structure

Baada ya:

TRUNCATE TABLE students;

table bado ipo.

Unaweza kufanya:

DESC students;

na kuona structure yake.

17. TRUNCATE Inaondoa Data Zote

Kabla:

students = 10,000 records

Baada ya:

TRUNCATE TABLE students;

unaweza kupata:

0 records

18. TRUNCATE na AUTO_INCREMENT

Katika MySQL, TRUNCATE TABLE kawaida hu-reset AUTO_INCREMENT counter ya table.

Mfano kabla:

student_id:
1
2
3
...
100

Ukifanya:

TRUNCATE TABLE students;

kisha ukaingiza record mpya:

INSERT INTO students (
    student_name
)
VALUES (
    'Asha Mussa'
);

ID mpya kwa kawaida inaweza kuanza tena:

1

ikiwa AUTO_INCREMENT ilianza 1.

19. DELETE vs TRUNCATE kwa AUTO_INCREMENT

DELETE:

DELETE FROM students;

kwa kawaida haifanyi reset ya AUTO_INCREMENT kama TRUNCATE.

TRUNCATE:

TRUNCATE TABLE students;

kwa kawaida hu-reset AUTO_INCREMENT.

20. TRUNCATE Inaweza Kuwa Haraka Zaidi

Kwa table kubwa yenye records nyingi:

TRUNCATE TABLE logs;

mara nyingi ni efficient zaidi kwa kusafisha table yote kuliko:

DELETE FROM logs;

lakini hizi operations zina semantics tofauti na zinapaswa kuchaguliwa kulingana na unachotaka kufanya.

21. Wakati Gani TRUNCATE Inafaa?

Inaweza kufaa kwa:

• Temporary tables
• Test data
• Import staging tables
• Logs ambazo zimeruhusiwa kufutwa zote
• Development database
• Table unayotaka kuanza upya

Mfano:

TRUNCATE TABLE temporary_imports;

22. Usitumie TRUNCATE Kama Unataka Record Moja

Kama unataka student ID 10 pekee, tumia:

DELETE FROM students
WHERE student_id = 10;

Usitumie:

TRUNCATE TABLE students;

kwa sababu hiyo inaondoa records zote.

23. DROP ni Nini?

DROP huondoa database object yenyewe.

Mfano:

DROP TABLE students;

Hapa table:

students

inaondolewa kabisa.

24. DROP TABLE

Syntax:

DROP TABLE table_name;

Mfano:

DROP TABLE students;

Baada ya hapo table haipo tena.

25. DROP Huondoa Data na Structure

Hii ndiyo tofauti kubwa.

DROP TABLE students;

inaondoa:

Data
+
Table structure

26. Baada ya DROP, DESC Haitafanya Kazi

Ukifanya:

DROP TABLE students;

kisha:

DESC students;

MySQL inaweza kutoa error kwa sababu table haipo tena.

27. Baada ya DROP, SELECT Haitafanya Kazi

SELECT *
FROM students;

haitafanya kazi ikiwa table tayari imefutwa.

28. Ili Kutumia Table Tena Lazima Uitengeneze

Baada ya:

DROP TABLE students;

lazima utengeneze tena:

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

29. Tofauti ya DELETE na DROP

DELETE:

DELETE FROM students;

Table bado ipo.

DROP:

DROP TABLE students;

Table haipo tena.

30. Tofauti ya TRUNCATE na DROP

TRUNCATE:

TRUNCATE TABLE students;

Data zote zinaondolewa lakini table inabaki.

DROP:

DROP TABLE students;

Data pamoja na table structure vinaondolewa.

31. Tofauti ya DELETE na TRUNCATE

DELETE inaweza kutumia:

WHERE

Mfano:

DELETE FROM students
WHERE student_id = 3;

TRUNCATE haiwezi kutumia WHERE:

TRUNCATE TABLE students;

32. Comparison Table

33. Mfano wa DELETE

DELETE FROM students
WHERE student_id = 10;

Maana:

Futa student ID 10 pekee.

34. Mfano wa TRUNCATE

TRUNCATE TABLE students;

Maana:

Ondoa students wote lakini acha table.

35. Mfano wa DROP

DROP TABLE students;

Maana:

Ondoa students table kabisa.

36. DELETE kwa Mfumo wa Shule

Katika school management system unaweza kuwa na:

students
teachers
classes
subjects
marks
attendance

Ikiwa unataka kufuta mark moja:

DELETE FROM marks
WHERE mark_id = 5;

Hapa DELETE inafaa.

37. TRUNCATE kwa Mfumo wa Test

Ikiwa una test database na unataka kuondoa marks zote ili kuanza testing upya:

TRUNCATE TABLE marks;

Hii inaweza kuwa useful kwenye development environment.

38. DROP kwa Table Isiyotumika Tena

Fikiria una table:

old_students_backup

ambayo umehakikisha haitumiki tena.

Unaweza kufanya:

DROP TABLE old_students_backup;

Lakini kabla ya DROP, hakikisha table kweli haihitajiki.

39. DROP DATABASE

DROP haiishii kwenye tables.

Unaweza pia kufuta database nzima:

DROP DATABASE school_management;

Hii ni command yenye hatari kubwa sana.

Inaweza kuondoa:

• Tables
• Data
• Relationships
• Views na objects nyingine za database hiyo

40. Tahadhari Kubwa kwa DROP DATABASE

Usifanye:

DROP DATABASE school_management;

kwenye production system bila uhakika wa kutosha na backup inayofaa.

Hii inaweza kuondoa database nzima.

41. DROP DATABASE IF EXISTS

Unaweza kukutana na:

DROP DATABASE IF EXISTS test_database;

Hii ina maana:

Futa database ikiwa ipo.

42. DROP TABLE IF EXISTS

Mfano:

DROP TABLE IF EXISTS temporary_students;

Hii inaweza kusaidia kwenye setup scripts.

Kama table haipo, IF EXISTS husaidia kuepuka error ya kawaida ya object kutokuwepo.

43. CREATE TABLE IF NOT EXISTS

Kinyume chake:

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

Hii inaweza kutumika kuhakikisha table inatengenezwa ikiwa haipo.

44. DELETE na Foreign Keys

Fikiria tuna:

students
marks

na marks.student_id inareference:

students.student_id

Ukifanya:

DELETE FROM students
WHERE student_id = 5;

MySQL inaweza kukataa ikiwa student huyo ana child records kwenye marks na constraint hairuhusu deletion.

45. Example ya Foreign Key Error

Unaweza kukutana na ujumbe unaofanana na:

Cannot delete or update a parent row:
a foreign key constraint fails

Hii ni protection ya referential integrity.

Tutajifunza Foreign Keys kwa undani kwenye somo linalofuata.

46. TRUNCATE na Foreign Keys

TRUNCATE TABLE inaweza pia kuzuiwa ikiwa table inareferenced na Foreign Key constraints.

Kwa hiyo si kila mara utaweza:

TRUNCATE TABLE students;

ikiwa tables nyingine zinategemea students.

47. Usizime Foreign Key Checks Bila Sababu

Unaweza kuona tutorials zinazotumia:

SET FOREIGN_KEY_CHECKS = 0;

halafu kufanya destructive operations.

Hii inapaswa kutumika kwa uelewa mkubwa sana.

Kuzima Foreign Key checks bila mpango kunaweza kuacha database yenye relationships zilizovunjika.

48. DELETE na Transactions

Fikiria:

START TRANSACTION;

kisha:

DELETE FROM students
WHERE student_id = 5;

Kabla ya COMMIT unaweza kuwa na nafasi ya:

ROLLBACK;

kulingana na storage engine na transaction context.

Tutajifunza transactions kwa undani baadaye.

49. TRUNCATE na Transactions

Usichukulie TRUNCATE kama DELETE ya kawaida inayoweza kurejeshwa kwa urahisi na transaction.

Katika MySQL, TRUNCATE TABLE ni DDL operation na ina behavior tofauti kuhusu implicit commits.

Kwa hiyo tumia kwa tahadhari.

50. DROP na Transactions

DROP TABLE pia ni structural operation.

Usiandike:

DROP TABLE important_table;

ukitegemea kwamba utaweza tu kufanya ROLLBACK kama DELETE ya kawaida.

Backup na migration planning ni muhimu.

51. DML na DDL

Kwa concept ya kawaida:

DELETE

ni sehemu ya:

DML – Data Manipulation Language

kwa sababu inafanya kazi kwenye rows.

Lakini:

TRUNCATE
DROP

kwa kawaida huwekwa katika:

DDL – Data Definition Language

kwa sababu zinaathiri object/table structure au table-level definition.

52. DELETE ni Row-Oriented

Mfano:

DELETE FROM students
WHERE student_id = 5;

inalenga row fulani.

Au:

DELETE FROM students
WHERE gender = 'Male';

inalenga rows zinazolingana.

53. TRUNCATE ni Table-Level Operation

TRUNCATE TABLE students;

haitafuti student mmoja mmoja kwa WHERE.

Inalenga table nzima.

54. DROP ni Object-Level Operation

DROP TABLE students;

inalenga object:

students table

yenyewe.

55. DELETE na ROW_COUNT()

Baada ya DELETE unaweza kuangalia rows zilizoathirika:

DELETE FROM students
WHERE class_name = 'Form Four';

Halafu:

SELECT ROW_COUNT();

Inaweza kukuonyesha idadi ya rows zilizoathiriwa na statement iliyopita.

56. Kwanza COUNT Records

Kabla ya mass delete unaweza kufanya:

SELECT COUNT(*)
FROM students
WHERE class_name = 'Form Four';

Ukiona:

125

unajua DELETE inaweza kuathiri records 125.

57. SELECT Kabla ya DELETE

Usianze na:

DELETE FROM marks
WHERE score < 30;

Anza na:

SELECT *
FROM marks
WHERE score < 30;

Hakikisha logic.

58. COUNT Kabla ya DELETE

Unaweza pia kufanya:

SELECT COUNT(*)
FROM marks
WHERE score < 30;

Kisha uamue kama deletion hiyo kweli inahitajika.

59. DELETE Sio Njia Pekee

Katika systems nyingi, badala ya kufuta record unaweza kutumia:

status

Mfano:

UPDATE students
SET status = 'Inactive'
WHERE student_id = 5;

Hii huitwa:

Soft Delete

60. Hard Delete

Query:

DELETE FROM students
WHERE student_id = 5;

ni mfano wa:

Hard Delete

Record inaondolewa.

61. Soft Delete

UPDATE students
SET status = 'Deleted'
WHERE student_id = 5;

Record inabaki kwenye database.

62. Kwa Nini Soft Delete ni Muhimu?

Inaweza kusaidia:

• Audit trail
• Restore record
• Data history
• Reports
• Financial records
• Academic history
• User account management

63. DELETE Katika Accounting System

Fikiria contribution:

TSh 500,000

imetumwa kimakosa.

Badala ya:

DELETE FROM contributions
WHERE contribution_id = 10;

professional accounting system inaweza kutumia:

UPDATE contributions
SET status = 'Voided'
WHERE contribution_id = 10;

ili history ibaki.

64. DELETE Katika Marks System

Student akifutwa kwenye marks table:

DELETE FROM marks
WHERE mark_id = 7;

record inaweza kupotea kabisa.

Kwa mfumo unaohitaji audit history, inaweza kuwa bora kuweka status au audit log.

65. TRUNCATE Katika Logs Table

Fikiria development environment ina:

application_logs

na unataka kuanza test mpya.

Unaweza kufanya:

TRUNCATE TABLE application_logs;

ikiwa logs hizo si muhimu tena.

66. TRUNCATE Katika Import Table

Mfano:

student_import_temp

Baada ya import kukamilika:

TRUNCATE TABLE student_import_temp;

inaweza kuandaa table kwa import inayofuata.

67. DROP Katika Temporary Development Table

Umetengeneza:

test_students_old

na sasa haitumiki.

Baada ya kuhakikisha:

DROP TABLE test_students_old;

68. DELETE vs TRUNCATE – Performance Concept

Kwa records nyingi:

DELETE FROM logs;

inaweza kufanya processing tofauti na:

TRUNCATE TABLE logs;

TRUNCATE mara nyingi huwa faster kwa kusafisha table nzima.

Lakini speed haipaswi kuwa sababu pekee.

Lazima uangalie:

• Foreign Keys
• Transaction behavior
• Auto Increment
• Permissions
• Audit requirements

69. DELETE Inaweza Kutumia LIMIT

Katika single-table DELETE ya MySQL unaweza kutumia:

DELETE FROM logs
WHERE status = 'Old'
LIMIT 100;

Hii inaweza kufuta records 100 tu zinazolingana.

70. DELETE na ORDER BY

MySQL pia inaweza kuruhusu:

DELETE FROM logs
WHERE status = 'Old'
ORDER BY created_at ASC
LIMIT 100;

Hii inaweza kufuta old records kwa batches.

71. Batch Deletion

Kwa table kubwa sana, badala ya kufuta records milioni moja kwa query moja, application inaweza kufuta kwa batches.

Mfano:

DELETE FROM logs
WHERE created_at < '2025-01-01'
LIMIT 1000;

Lakini batch cleanup ya production inahitaji planning ya performance na data retention.

72. Data Retention

Professional systems zinaweza kuwa na policy kama:

Keep logs for 12 months.

Kisha records za zamani zinafutwa kulingana na policy.

Hii haipaswi kufanywa bila kuelewa business, legal na audit requirements.

73. DELETE vs DROP COLUMN

Usichanganye:

DELETE FROM students;

na:

ALTER TABLE students
DROP COLUMN phone_number;

DELETE inaondoa rows.

DROP COLUMN inaondoa column.

74. DROP COLUMN

Mfano:

ALTER TABLE students
DROP COLUMN email;

Hii inafuta:

email column
+
data zake

lakini table inabaki.

75. DROP TABLE

DROP TABLE students;

Hii inafuta table nzima.

76. DROP DATABASE

DROP DATABASE school_management;

Hii inafuta database nzima.

Kwa hiyo neno:

DROP

linahitaji tahadhari kubwa.

77. IF EXISTS ni Muhimu kwa Scripts

Mfano:

DROP TABLE IF EXISTS test_students;

Hii inaweza kutumika kwenye development/setup scripts.

Bila IF EXISTS, MySQL inaweza kutoa error ikiwa table haipo.

78. IF EXISTS Haifanyi DROP Kuwa Salama kwa Data

Hili ni muhimu.

DROP TABLE IF EXISTS students;

haimaanishi:

Futa ikiwa ni salama.

Inamaanisha:

Jaribu kufuta table ikiwa table hiyo ipo.

Kwa hiyo IF EXISTS hailindi data.

79. DROP TABLE Yenye Foreign Keys

Ikiwa tables zina relationships, dropping table inaweza kuzuiwa au kuathiri database design.

Mfano:

classes
↓
students
↓
marks

Huwezi tu kufuta tables bila kuelewa relationships.

80. Order ya Kufuta Tables

Katika relational database unaweza kuhitaji kushughulikia child tables kabla ya parent tables, kutegemea constraints.

Mfano:

students
marks

Marks zinamtegemea student.

Tutajifunza vizuri zaidi kwenye Foreign Keys.

81. Backup Kabla ya DELETE Kubwa

Kabla ya:

DELETE FROM students;

kwenye database muhimu, backup inaweza kuwa muhimu.

82. Backup Kabla ya TRUNCATE

Kabla ya:

TRUNCATE TABLE students;

hakikisha data haitahitajika tena.

83. Backup Kabla ya DROP

Zaidi sana kabla ya:

DROP TABLE students;

au:

DROP DATABASE school_management;

84. Mfano wa Backup Table

Kwa learning environment unaweza kufanya:

CREATE TABLE students_backup AS
SELECT *
FROM students;

Lakini kumbuka hii si backup kamili ya constraints, indexes na other table metadata.

Kwa backup professional, database dump ni bora zaidi.

85. SHOW CREATE TABLE

Kabla ya DROP unaweza kuhifadhi structure:

SHOW CREATE TABLE students;

Hii inaweza kukusaidia kuona SQL ya table.

Lakini data bado inahitaji backup tofauti.

86. Makosa ya Kawaida – DELETE Bila WHERE

Ulikusudia:

DELETE FROM students
WHERE student_id = 5;

lakini ukaandika:

DELETE FROM students;

Records zote zinaweza kufutwa.

87. Makosa ya Kawaida – DELETE * FROM

Beginners wengine huandika:

DELETE * FROM students;

Hii si syntax ya kawaida sahihi ya MySQL DELETE.

Sahihi:

DELETE FROM students;

88. Makosa ya Kawaida – TRUNCATE na WHERE

Mbaya:

TRUNCATE TABLE students
WHERE student_id = 5;

Sahihi kwa student mmoja:

DELETE FROM students
WHERE student_id = 5;

89. Makosa ya Kawaida – DROP Badala ya TRUNCATE

Ulikusudia kusafisha data:

TRUNCATE TABLE students;

lakini ukaandika:

DROP TABLE students;

Sasa table inaweza kuondolewa kabisa.

90. Makosa ya Kawaida – DROP DATABASE Isiyo Sahihi

Fikiria una:

school_management_test
school_management

Ukichagua database isiyo sahihi na kufanya destructive operation unaweza kupoteza production data.

Hivyo database name lazima ihakikiwe vizuri.

91. SELECT DATABASE ya Sasa

Unaweza kuangalia database unayotumia:

SELECT DATABASE();

Hii ni useful kabla ya operations hatari.

92. SHOW TABLES

Kabla ya DROP unaweza kufanya:

SHOW TABLES;

Hakikisha table name ni sahihi.

93. DESCRIBE Table

DESC students;

Hakikisha structure ni ile unayotarajia.

94. Count Data Kabla ya TRUNCATE

SELECT COUNT(*)
FROM students;

Fikiria result:

35,000

Ukifanya:

TRUNCATE TABLE students;

unajua records zote 35,000 zitaondolewa.

95. Workflow Salama ya DELETE

1. SELECT DATABASE()
2. SELECT target records
3. COUNT target records
4. Verify condition
5. Backup ikiwa inahitajika
6. DELETE
7. Verify

96. Workflow Salama ya TRUNCATE

1. Hakikisha database
2. Hakikisha table
3. COUNT records
4. Backup ikiwa data ni muhimu
5. Hakikisha unataka records zote ziondoke
6. TRUNCATE
7. Verify

97. Workflow Salama ya DROP TABLE

1. Hakikisha database
2. Hakikisha table name
3. Angalia dependencies
4. Backup data
5. Hifadhi table structure ikiwa ni muhimu
6. DROP
7. Verify application

98. Query ya Verification Baada ya DELETE

Baada ya:

DELETE FROM students
WHERE student_id = 5;

fanya:

SELECT *
FROM students
WHERE student_id = 5;

Ikiwa hakuna record, deletion imetokea.

99. Verification Baada ya TRUNCATE

SELECT COUNT(*)
FROM students;

Result inaweza kuwa:

0

100. Verification Baada ya DROP

SHOW TABLES;

Table iliyodropiwa haitapaswa kuonekana.

101. Practical Example – School Management

Fikiria tuna:

students
classes
subjects
marks
exams

Student mmoja ameongezwa kimakosa.

Tumia:

DELETE FROM students
WHERE student_id = 100;

Lakini kwanza:

SELECT *
FROM students
WHERE student_id = 100;

102. Practical Example – Test Marks

Una test marks ambazo hazihitajiki tena kwenye development database:

TRUNCATE TABLE test_marks;

Table bado itakuwa tayari kwa test nyingine.

103. Practical Example – Old Table

Migration imehamisha data kutoka:

students_old

kwenda:

students

Baada ya backup na verification unaweza kuamua:

DROP TABLE students_old;

104. Practical Example – Blog System

Post moja:

DELETE FROM posts
WHERE post_id = 50;

Posts zote za temporary import:

TRUNCATE TABLE temporary_posts;

Table ya zamani isiyotumika:

DROP TABLE old_posts;

105. Practical Example – Products

Product moja:

DELETE FROM products
WHERE product_id = 10;

Test products zote:

TRUNCATE TABLE test_products;

Table ya test:

DROP TABLE test_products;

106. Practical Example – Users

Account moja inaweza kufutwa:

DELETE FROM users
WHERE user_id = 20;

Lakini kwenye system halisi unaweza kutumia:

UPDATE users
SET status = 'Inactive'
WHERE user_id = 20;

ili history ibaki.

107. Ni Command Ipi Nitumie?

Kama unataka record moja:

DELETE

Kama unataka records fulani kwa condition:

DELETE

Kama unataka kusafisha records zote lakini table ibaki:

TRUNCATE

Kama unataka kuondoa table kabisa:

DROP TABLE

Kama unataka kuondoa database kabisa:

DROP DATABASE

108. Decision Example

Swali:

Nataka kufuta student ID 10.

Tumia:

DELETE FROM students
WHERE student_id = 10;

109. Decision Example

Swali:

Nataka kuondoa students wote wa Form Four.

Tumia:

DELETE FROM students
WHERE class_name = 'Form Four';

110. Decision Example

Swali:

Nataka kusafisha table ya test students na kuanza upya.

Tumia:

TRUNCATE TABLE test_students;

ikiwa data yote kweli haihitajiki.

111. Decision Example

Swali:

Table ya old_students haitumiki kabisa.

Baada ya verification na backup:

DROP TABLE old_students;

112. Mazoezi ya Somo

Tengeneza:

CREATE TABLE test_students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100),
    class_name VARCHAR(50)
);

Ingiza students 10.

Kisha:

1. Futa student ID 3.
2. Futa student ID 7.
3. Futa students wote wa Form One kwa DELETE.
4. Angalia table structure.
5. Ongeza students wapya.
6. Angalia AUTO_INCREMENT.
7. Fanya TRUNCATE TABLE.
8. Ingiza student mpya.
9. Angalia AUTO_INCREMENT.
10. Fanya DROP TABLE.

Fanya zoezi hili kwenye test table, si kwenye data muhimu.

113. Challenge ya DELETE

Tengeneza products 15.

Futa:

• Product ID 4
• Product ID 8
• Products zenye quantity 0

Kabla ya kila DELETE tumia:

SELECT

kuhakikisha records.

114. Challenge ya TRUNCATE

Tengeneza:

CREATE TABLE temporary_marks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100),
    score DECIMAL(5,2)
);

Ingiza records 20.

Angalia:

SELECT COUNT(*)
FROM temporary_marks;

Kisha:

TRUNCATE TABLE temporary_marks;

Angalia tena count.

115. Challenge ya DROP

Baada ya kumaliza test:

DROP TABLE temporary_marks;

Kisha:

SHOW TABLES;

hakikisha haipo.

116. Challenge Kubwa

Tengeneza database ya mazoezi:

CREATE DATABASE mysql_delete_practice;

Itumie:

USE mysql_delete_practice;

Tengeneza tables:

students
products
temporary_logs

Kisha:

• DELETE record moja
• DELETE records kwa condition
• TRUNCATE temporary_logs
• DROP products
• Angalia tables zilizobaki

Usitumie challenge hii kwenye production database.

117. Maswali ya Kujipima

Jaribu kujibu:

1. DELETE inafanya nini?
2. TRUNCATE inafanya nini?
3. DROP TABLE inafanya nini?
4. Ni command ipi inaweza kutumia WHERE?
5. Je TRUNCATE inaweza kutumia WHERE?
6. Je DELETE huondoa table?
7. Je TRUNCATE huondoa table?
8. Je DROP TABLE huondoa table structure?
9. Ni command ipi kwa kawaida hu-reset AUTO_INCREMENT?
10. Kuna tofauti gani kati ya DELETE na TRUNCATE?
11. Kuna tofauti gani kati ya TRUNCATE na DROP?
12. Kuna tofauti gani kati ya DELETE na DROP?
13. Soft Delete ni nini?
14. Kwa nini SELECT ni muhimu kabla ya DELETE?
15. DROP DATABASE inafanya nini?
16. IF EXISTS ina maana gani?
17. Kwa nini Foreign Keys zinaweza kuzuia DELETE au TRUNCATE?
18. Kwa nini backup ni muhimu kabla ya destructive operations?

118. Muhtasari wa Somo

Katika somo hili tumejifunza commands tatu muhimu:

DELETE

TRUNCATE

na:

DROP

DELETE inaweza kuondoa record maalumu:

DELETE FROM students
WHERE student_id = 5;

DELETE inaweza pia kuondoa records zote:

DELETE FROM students;

lakini table structure inabaki.

TRUNCATE:

TRUNCATE TABLE students;

inaondoa records zote na table structure inabaki.

Katika MySQL, TRUNCATE kwa kawaida pia hu-reset AUTO_INCREMENT.

DROP:

DROP TABLE students;

inaondoa:

Data
+
Table Structure

Table haipo tena.

Tofauti muhimu zaidi ni:

DELETE
→ Records

TRUNCATE
→ Records zote, table inabaki

DROP
→ Table nzima inaondoka

Kabla ya destructive query, workflow nzuri ni:

SELECT
↓
COUNT
↓
VERIFY
↓
BACKUP
↓
DELETE / TRUNCATE / DROP
↓
VERIFY

Hii inaweza kukuepusha na data loss kubwa.

Somo Linalofuata

Katika Somo la 21 tutajifunza:

FOREIGN KEY – Jinsi ya Kuunganisha Tables Katika MySQL

Hapo ndipo tutaanza sehemu muhimu sana ya Relational Database Design.

Tutajifunza:

• Foreign Key ni nini
• Primary Key vs Foreign Key
• Parent table
• Child table
• Referential Integrity
• Students na Classes
• Students na Marks
• REFERENCES
• CONSTRAINT
• Kuongeza Foreign Key wakati wa CREATE TABLE
• Kuongeza Foreign Key kwa ALTER TABLE
• Foreign Key errors
• Kufuta parent record yenye child records
• ON DELETE
• ON UPDATE
• CASCADE
• SET NULL
• RESTRICT
• Mifano ya school management system
• Mifano ya accounting system
• Mifano ya users na posts
• Database relationship diagrams
• Mazoezi na challenges

Hili litakuwa moja ya masomo muhimu zaidi katika course kwa sababu tutaanza kujenga tables zinazohusiana badala ya tables zinazofanya kazi peke yake.