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

Katika somo lililopita tulijifunza kutumia:

IN

NOT IN

BETWEEN

na:

NOT BETWEEN

kwa kuchagua records nyingi au values zilizopo ndani ya range fulani.

Sasa tutajifunza concept muhimu sana katika MySQL inayohusu data ambayo haipo, haijajulikana au haijaingizwa.

Tutajifunza:

NULL

IS NULL

IS NOT NULL

na:

DEFAULT

Concept hizi ni muhimu sana kwenye mifumo halisi kwa sababu si kila taarifa lazima iwepo wakati wa kusajili record.

Kwa mfano:

• Student anaweza kutokuwa na email
• Phone number inaweza kuwa haijaingizwa
• Guardian contact inaweza kuwa haijulikani
• Product inaweza kutokuwa na description
• User mpya anaweza kuwa na status ya default
• Payment inaweza kuwa haijalipwa bado
• Profile picture inaweza kutokuwa imewekwa

Malengo ya Somo

Baada ya kumaliza somo hili utaweza:

• Kuelewa NULL ni nini
• Kutofautisha NULL na empty string
• Kutofautisha NULL na zero
• Kutumia IS NULL
• Kutumia IS NOT NULL
• Kuelewa kwa nini = NULL si matumizi sahihi
• Kutengeneza columns zinazokubali NULL
• Kutengeneza columns zenye NOT NULL
• Kutumia DEFAULT
• Kutumia default values kwenye INSERT
• Kubadilisha value kuwa NULL kwa UPDATE
• Kutafuta records zenye taarifa zinazokosekana
• Kutengeneza default status
• Kutumia default timestamps
• Kuepuka makosa ya kawaida

1. NULL ni Nini?

Katika MySQL, NULL ina maana:

Hakuna value iliyopo au value haijulikani.

Mfano:

student_name = Asha Mussa
phone_number = NULL

Hii inaweza kumaanisha phone number ya Asha haijaingizwa.

NULL si neno la kawaida la text.

Ni special value kwenye SQL.

2. Mfano wa NULL

Fikiria tuna student:

student_id: 1
student_name: Asha Mussa
phone_number: NULL
email: NULL

Hii inaweza kumaanisha:

• Jina lipo
• Phone number haipo
• Email haipo

3. NULL Sio Zero

Hili ni jambo muhimu sana.

NULL

si sawa na:

0

Mfano:

score = 0

inaweza kumaanisha student alipata zero.

Lakini:

score = NULL

inaweza kumaanisha mark bado haijaingizwa.

Hizi ni concepts tofauti kabisa.

4. NULL Sio Empty String

Hii:

''

ni empty string.

Lakini:

NULL

ina maana hakuna value.

Mfano:

phone_number = ''

ina text yenye urefu wa sifuri.

Lakini:

phone_number = NULL

ina missing value.

5. NULL Sio Neno 'NULL'

Hii:

NULL

ni special SQL value.

Lakini:

'NULL'

ni text yenye herufi:

N U L L

Kwa hiyo:

UPDATE students
SET phone_number = NULL
WHERE student_id = 1;

ni tofauti na:

UPDATE students
SET phone_number = 'NULL'
WHERE student_id = 1;

6. Kutengeneza Column Inayokubali NULL

Mfano:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NULL
);

Hapa:

student_name

hairuhusu NULL.

Lakini:

phone_number

inaruhusu NULL.

7. NULL Mara Nyingi ni Default Ikiwa NOT NULL Haijawekwa

Mfano:

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100),
    email VARCHAR(150)
);

Kwa kawaida columns kama:

username
email

zinaweza kuruhusu NULL ikiwa hazijafanywa NOT NULL, kutegemea definition ya table.

8. NOT NULL ni Nini?

NOT NULL ina maana:

Column lazima iwe na value.

Mfano:

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

Hapa huwezi kuacha student_name bila value inayokubalika.

9. Kwa Nini Student Name Iwe NOT NULL?

Kwa sababu record ya student bila jina inaweza kutokuwa na maana kwenye mfumo.

Mfano:

student_name VARCHAR(100) NOT NULL

Hii husaidia kuhakikisha kila student ana jina.

10. Columns Zinazoweza Kuruhusu NULL

Mfano:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    gender VARCHAR(10),
    phone_number VARCHAR(20),
    email VARCHAR(150)
);

Unaweza kuamua:

• student_name lazima iwepo
• gender inaweza kuwa haijaingizwa
• phone_number inaweza kuwa haijaingizwa
• email inaweza kuwa haijaingizwa

11. Kutumia NULL Kwenye INSERT

Mfano:

INSERT INTO students (
    student_name,
    phone_number,
    email
)
VALUES (
    'Asha Mussa',
    NULL,
    NULL
);

Hapa Asha atasajiliwa bila phone number na email.

12. Kuacha Optional Column Kwenye INSERT

Ikiwa column inaruhusu NULL au ina DEFAULT, unaweza pia kuiacha.

Mfano:

INSERT INTO students (
    student_name
)
VALUES (
    'John Peter'
);

Kama phone_number na email zinaruhusu NULL, zinaweza kupata NULL automatically.

13. Tutumie Table Kamili ya Students

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

Ingiza data:

INSERT INTO students (
    student_name,
    gender,
    age,
    phone_number,
    email,
    class_name
)
VALUES
(
    'Asha Mussa',
    'Female',
    16,
    '0711111111',
    'asha@example.com',
    'Form One'
),
(
    'John Peter',
    'Male',
    17,
    NULL,
    'john@example.com',
    'Form Two'
),
(
    'Neema Joseph',
    'Female',
    15,
    '0733333333',
    NULL,
    'Form One'
),
(
    'Peter Michael',
    'Male',
    18,
    NULL,
    NULL,
    'Form Three'
);

14. Jinsi ya Kutafuta NULL

Hatutumii:

WHERE phone_number = NULL;

Badala yake tunatumia:

WHERE phone_number IS NULL;

Mfano:

SELECT *
FROM students
WHERE phone_number IS NULL;

15. IS NULL ni Nini?

IS NULL hutumika kutafuta records ambazo column haina value.

Mfano:

SELECT *
FROM students
WHERE email IS NULL;

Hii inaweza kuonyesha students ambao hawana email.

16. Kutafuta Students Wasio na Phone Number

SELECT *
FROM students
WHERE phone_number IS NULL;

Matokeo yanaweza kuwa:

John Peter
Peter Michael

17. Kutafuta Students Wasio na Email

SELECT *
FROM students
WHERE email IS NULL;

Matokeo yanaweza kuwa:

Neema Joseph
Peter Michael

18. IS NOT NULL ni Nini?

IS NOT NULL hutumika kutafuta records ambazo zina value.

Mfano:

SELECT *
FROM students
WHERE phone_number IS NOT NULL;

Hii inaweza kuonyesha students wenye phone number.

19. Students Wenye Email

SELECT *
FROM students
WHERE email IS NOT NULL;

20. Tofauti ya IS NULL na IS NOT NULL

IS NULL

ina maana:

Hakuna value.

IS NOT NULL

ina maana:

Kuna value.

21. Kwa Nini = NULL Si Sahihi?

Mfano huu:

SELECT *
FROM students
WHERE phone_number = NULL;

si njia sahihi ya kawaida ya kulinganisha NULL.

Kwa sababu SQL hutumia logic maalumu kwenye NULL.

Tumia:

SELECT *
FROM students
WHERE phone_number IS NULL;

22. Kwa Nini != NULL Si Sahihi?

Usitumie:

WHERE email != NULL;

Tumia:

WHERE email IS NOT NULL;

23. UPDATE Value Kuwa NULL

Fikiria student ameomba phone number yake iondolewe.

Unaweza kufanya:

UPDATE students
SET phone_number = NULL
WHERE student_id = 1;

24. Kuangalia Baada ya UPDATE

SELECT *
FROM students
WHERE student_id = 1;

Phone number sasa inaweza kuwa:

NULL

25. UPDATE NULL Kuwa Value

Mfano student alikuwa hana phone number.

Unaweza kufanya:

UPDATE students
SET phone_number = '0766666666'
WHERE student_id = 2;

Sasa NULL imebadilishwa kuwa value.

26. IS NULL na AND

Mfano:

SELECT *
FROM students
WHERE phone_number IS NULL
AND gender = 'Male';

Hii itaonyesha Male students wasio na phone number.

27. IS NULL na OR

SELECT *
FROM students
WHERE phone_number IS NULL
OR email IS NULL;

Hii itaonyesha students wanaokosa angalau moja kati ya:

• Phone number
• Email

28. Students Wasio na Phone na Email

Ikiwa tunataka wanaokosa zote mbili:

SELECT *
FROM students
WHERE phone_number IS NULL
AND email IS NULL;

29. Students Wenye Phone na Email

SELECT *
FROM students
WHERE phone_number IS NOT NULL
AND email IS NOT NULL;

Hii inaweza kusaidia kupata profiles zilizokamilika zaidi.

30. NULL Katika Marks System

Fikiria table:

CREATE TABLE marks (
    mark_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    subject VARCHAR(100) NOT NULL,
    score DECIMAL(5,2)
);

Score inaweza kuwa NULL ikiwa mark bado haijaingizwa.

31. Student Ambaye Mark Haijaingizwa

INSERT INTO marks (
    student_name,
    subject,
    score
)
VALUES (
    'Asha Mussa',
    'Mathematics',
    NULL
);

Hii inaweza kumaanisha:

Mark bado haijawekwa.

32. Kutafuta Marks Ambazo Hazijaingizwa

SELECT *
FROM marks
WHERE score IS NULL;

Hii ni useful sana kwa teacher kuona students ambao marks zao bado hazijaingizwa.

33. Kutafuta Marks Zilizokwishaingizwa

SELECT *
FROM marks
WHERE score IS NOT NULL;

34. Kwa Nini Score 0 Si NULL?

Fikiria:

Asha = 0
John = NULL

Asha alipata:

0

Lakini John:

NULL

inaweza kumaanisha bado hajapewa mark.

Kwa hiyo usibadilishe missing marks kuwa 0 bila sababu.

35. NULL Katika Accounting System

Fikiria table:

CREATE TABLE contributions (
    contribution_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100),
    amount DECIMAL(10,2),
    paid_date DATE
);

Ikiwa payment haijalipwa bado, paid_date inaweza kuwa NULL.

36. Kutafuta Contributions Ambazo Hazina Paid Date

SELECT *
FROM contributions
WHERE paid_date IS NULL;

Hii inaweza kusaidia kuona transactions ambazo bado hazijakamilika.

37. Kutafuta Zilizolipwa

SELECT *
FROM contributions
WHERE paid_date IS NOT NULL;

38. DEFAULT ni Nini?

DEFAULT ni value ambayo MySQL inaweza kuweka automatically ikiwa value haijatolewa kwenye INSERT.

Mfano:

status VARCHAR(20) DEFAULT 'Active'

Ikiwa user mpya atasajiliwa bila status, MySQL inaweza kuweka:

Active

automatically.

39. Mfano wa DEFAULT

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Active'
);

40. INSERT Bila Kutaja Status

INSERT INTO users (
    username
)
VALUES (
    'faustine'
);

MySQL inaweza kuhifadhi:

username = faustine
status = Active

41. Kutaja Status Mwenyewe

Unaweza pia kuandika:

INSERT INTO users (
    username,
    status
)
VALUES (
    'john',
    'Inactive'
);

Hapa default haitatumika kwa sababu tumetoa value yetu.

42. DEFAULT na Numbers

Mfano:

quantity INT DEFAULT 0

Table:

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    quantity INT DEFAULT 0
);

43. INSERT Product Bila Quantity

INSERT INTO products (
    product_name
)
VALUES (
    'Laptop'
);

Quantity inaweza kuwa:

0

automatically.

44. DEFAULT na Boolean-Like Status

Unaweza kuwa na:

is_active TINYINT(1) DEFAULT 1

Mfano:

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    is_active TINYINT(1) DEFAULT 1
);

Hapa:

1

inaweza kutumika kuwakilisha active.

45. DEFAULT na CURRENT_TIMESTAMP

Hii ni muhimu sana.

Mfano:

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ukiongeza post:

INSERT INTO posts (
    title
)
VALUES (
    'MySQL Database Tutorial'
);

created_at inaweza kuwekwa automatically.

46. updated_at Automatically

Unaweza kuwa na:

updated_at TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP

Mfano:

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

47. created_at vs updated_at

created_at:

Record ilitengenezwa lini.

updated_at:

Record ilibadilishwa mara ya mwisho lini.

Hizi ni columns muhimu kwenye applications.

48. Kutumia DEFAULT Kwenye INSERT

Unaweza pia kutumia keyword:

DEFAULT

Mfano:

INSERT INTO users (
    username,
    status
)
VALUES (
    'anna',
    DEFAULT
);

Ikiwa default ni Active, MySQL itatumia value hiyo.

49. UPDATE Kurudisha Default Value

Mfano:

UPDATE users
SET status = DEFAULT
WHERE user_id = 3;

Ikiwa default ni:

Active

status inaweza kurudi kuwa Active.

50. DEFAULT na NULL Si Vitu Vilevile

Mfano:

status VARCHAR(20) DEFAULT 'Active'

DEFAULT value ni:

Active

Lakini NULL ni:

No value

Kwa hiyo ni concepts tofauti.

51. DEFAULT NULL

Unaweza pia kuwa na:

phone_number VARCHAR(20) DEFAULT NULL

Hii ina maana ikiwa phone haijatolewa, iwe NULL.

52. NOT NULL na DEFAULT Pamoja

Mfano:

status VARCHAR(20) NOT NULL DEFAULT 'Active'

Hii ina maana:

• Status hairuhusiwi kuwa NULL
• Ikiwa status haijatolewa, tumia Active

Hii ni design nzuri kwa columns fulani.

53. Mfano wa Users Table

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    role VARCHAR(30) NOT NULL DEFAULT 'user',
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Hapa tuna default values mbili:

role = user
status = Active

54. INSERT User Mpya

INSERT INTO users (
    username,
    email
)
VALUES (
    'Asha',
    'asha@example.com'
);

MySQL inaweza kuweka:

role = user
status = Active
created_at = current date/time

automatically.

55. DEFAULT katika School System

Table ya students inaweza kuwa:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

Student mpya anaweza kuwa Active automatically.

56. DEFAULT katika Marks System

Unaweza kuwa na:

exam_status VARCHAR(20) DEFAULT 'Pending'

Mfano:

CREATE TABLE exams (
    exam_id INT PRIMARY KEY AUTO_INCREMENT,
    exam_name VARCHAR(100) NOT NULL,
    exam_status VARCHAR(20) DEFAULT 'Pending'
);

57. DEFAULT katika Orders System

Mfano:

order_status VARCHAR(20) DEFAULT 'Pending'

Order mpya inaweza kuanza kama:

Pending

baadaye kubadilishwa kuwa:

Paid
Processing
Shipped
Completed
Cancelled

58. NULL na ORDER BY

Unaweza kupanga records hata kama baadhi ya values ni NULL.

Mfano:

SELECT *
FROM students
ORDER BY phone_number ASC;

Position ya NULL kwenye sorting inaweza kutegemea order na MySQL behavior.

Kama unataka logic maalumu, unaweza kutumia advanced expressions baadaye.

59. NULL na COUNT

Baadaye tutajifunza aggregate functions.

Lakini ni muhimu kujua:

COUNT(column_name)

kwa kawaida haihesabu NULL values.

Mfano:

SELECT COUNT(phone_number)
FROM students;

Inaweza kuhesabu students wenye phone number tu.

Lakini:

SELECT COUNT(*)
FROM students;

inahesabu rows zote.

Tutajifunza hili kwa undani kwenye COUNT, SUM, AVG, MIN na MAX.

60. NULL na AVG

Fikiria scores:

80
70
NULL
90

Aggregate functions kama AVG(score) kwa kawaida hupuuza NULL badala ya kuitafsiri kuwa zero.

Hii ndiyo sababu:

NULL

na:

0

ni muhimu kutofautishwa.

61. Makosa ya Kawaida – Kutumia = NULL

Mfano mbaya:

SELECT *
FROM students
WHERE email = NULL;

Sahihi:

SELECT *
FROM students
WHERE email IS NULL;

62. Makosa ya Kawaida – Kutumia != NULL

Mfano mbaya:

SELECT *
FROM students
WHERE email != NULL;

Sahihi:

SELECT *
FROM students
WHERE email IS NOT NULL;

63. Makosa ya Kawaida – Kuweka 'NULL'

Mfano:

UPDATE students
SET email = 'NULL'
WHERE student_id = 2;

Hii inaweka neno NULL kama text.

Ikiwa unataka real NULL:

UPDATE students
SET email = NULL
WHERE student_id = 2;

64. Makosa ya Kawaida – Kutumia 0 Badala ya NULL

Kwa marks:

score = 0

na:

score = NULL

hazifanani.

Usitumie zero kuwakilisha missing data ikiwa zero yenyewe ni valid value.

65. Makosa ya Kawaida – Empty String Badala ya NULL

Mfano:

phone_number = ''

Hii inaweza kufanya filtering kuwa ngumu ikiwa wengine wana NULL na wengine empty string.

Ni vizuri system yako iwe na consistent data design.

66. Kutafuta NULL au Empty String

Ikiwa database tayari ina zote mbili:

SELECT *
FROM students
WHERE phone_number IS NULL
OR phone_number = '';

Hii inaweza kupata records zote ambazo phone number haijajazwa.

67. Kutafuta Email Isiyokosekana na Isiyo Empty

SELECT *
FROM students
WHERE email IS NOT NULL
AND email != '';

68. NULL na NOT IN

Hapa tunahitaji umakini.

Fikiria:

SELECT *
FROM students
WHERE student_id NOT IN (1, 2, NULL);

NULL inaweza kusababisha result isiyotarajiwa kwa sababu SQL hutumia three-valued logic.

Kwa beginner, epuka kuweka NULL ndani ya IN au NOT IN lists bila kuelewa logic yake.

69. NULL na Unique Columns

Katika MySQL, UNIQUE columns zina behavior maalumu kuhusu NULL.

Mfano:

email VARCHAR(150) UNIQUE

Unaweza kukuta more than one NULL inaruhusiwa kutegemea database behavior.

Lakini email values zisizo NULL zinapaswa kuwa unique.

70. Mfano wa Table ya Professional Users

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    phone_number VARCHAR(20),
    role VARCHAR(30) NOT NULL DEFAULT 'user',
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

Table hii ina concepts nyingi tulizojifunza.

71. Mfano wa Student Management System

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

72. Student Mpya Bila Phone na Email

INSERT INTO students (
    student_name,
    gender,
    class_name
)
VALUES (
    'Asha Mussa',
    'Female',
    'Form One'
);

MySQL inaweza kuweka:

phone_number = NULL
email = NULL
status = Active
created_at = current timestamp

73. Query ya Students Wenye Missing Contacts

SELECT *
FROM students
WHERE phone_number IS NULL
OR email IS NULL;

74. Query ya Complete Contacts

SELECT *
FROM students
WHERE phone_number IS NOT NULL
AND email IS NOT NULL;

75. Query ya Active Students

Ikiwa default status ni Active:

SELECT *
FROM students
WHERE status = 'Active';

76. Soft Delete kwa Status

Badala ya DELETE:

DELETE FROM students
WHERE student_id = 5;

unaweza kufanya:

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

Hii inatumia default/status concept katika system design.

77. Restoring Student

Unaweza kurudisha:

UPDATE students
SET status = DEFAULT
WHERE student_id = 5;

Ikiwa default ni:

Active

student anaweza kuwa active tena.

78. NULL na Forms za Website

Form inaweza kuwa na:

Student Name: required
Phone Number: optional
Email: optional

Database design inaweza kuwa:

student_name VARCHAR(100) NOT NULL,
phone_number VARCHAR(20) NULL,
email VARCHAR(150) NULL

Hii inaendana na requirements za form.

79. Required vs Optional Data

Required data inaweza kutumia:

NOT NULL

Optional data inaweza kuruhusu:

NULL

Mfano:

Required:
student_name
gender
class

Optional:
phone
email
address

80. Usitumie NULL Bila Mpangilio

Si kila column inapaswa kuruhusu NULL.

Mfano:

student_id
student_name
class_name

zinaweza kuwa muhimu sana.

Lakini:

middle_name
phone_number
profile_picture

zinaweza kuwa optional.

Database design nzuri huamua hili mapema.

81. NULL vs DEFAULT – Mfano Rahisi

Fikiria:

status VARCHAR(20) DEFAULT 'Active'

Ikiwa value haijatolewa:

Active

Lakini:

phone_number VARCHAR(20) DEFAULT NULL

ikiwa value haijatolewa:

NULL

82. DEFAULT Inapunguza Repetition

Bila default:

INSERT INTO users (
    username,
    status
)
VALUES (
    'Asha',
    'Active'
);

Kila mara lazima uandike Active.

Kwa DEFAULT:

INSERT INTO users (
    username
)
VALUES (
    'Asha'
);

Database inajua status ya kuanza.

83. DEFAULT Husaidia Consistency

Kama system ina users 1,000 na wote wanapaswa kuanza na:

Active

ni rahisi kutumia:

DEFAULT 'Active'

badala ya kutegemea kila INSERT iweke value hiyo.

84. Mfano wa Full INSERT

INSERT INTO students (
    student_name,
    gender,
    phone_number,
    email,
    class_name
)
VALUES (
    'Neema Joseph',
    'Female',
    NULL,
    NULL,
    'Form One'
);

85. Mfano wa Full UPDATE

UPDATE students
SET
    phone_number = '0712345678',
    email = 'neema@example.com'
WHERE student_id = 3;

86. Mfano wa Missing Data Report

SELECT
    student_id,
    student_name,
    phone_number,
    email
FROM students
WHERE phone_number IS NULL
OR email IS NULL
ORDER BY student_name ASC;

Hii inaweza kuwa useful kwa admin kuona students wenye taarifa ambazo hazijakamilika.

87. Mfano wa Completed Profiles

SELECT
    student_id,
    student_name,
    phone_number,
    email
FROM students
WHERE phone_number IS NOT NULL
AND email IS NOT NULL
ORDER BY student_name ASC;

88. Mazoezi ya Somo

Tumia table ya students.

Andika queries za:

1. Kutafuta students wasio na phone number.
2. Kutafuta students wenye phone number.
3. Kutafuta students wasio na email.
4. Kutafuta students wenye email.
5. Kutafuta students wasio na phone wala email.
6. Kutafuta students wenye phone na email.
7. Weka email ya student ID 2 kuwa NULL.
8. Weka phone number mpya kwa student ID 3.

89. Mazoezi ya DEFAULT

Tengeneza table:

users

yenye:

user_id
username
email
role
status
created_at

Weka:

role = user
status = Active

kuwa default.

Kisha ingiza user bila role na status.

Angalia data:

SELECT *
FROM users;

90. Challenge ya Marks

Tengeneza table:

marks

yenye:

mark_id
student_name
subject
score

Ingiza students 6.

Kwa students wawili, weka:

score = NULL

Kisha:

1. Tafuta marks ambazo hazijaingizwa.
2. Tafuta marks zilizokwishaingizwa.
3. Weka mark ya student mmoja aliyekuwa na NULL.
4. Badilisha score nyingine kuwa NULL.
5. Hakikisha zero na NULL zinatofautiana.

91. Challenge ya School System

Tengeneza:

CREATE TABLE school_students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ingiza students angalau 5.

Baadhi wawe na phone/email na wengine wasiwe nazo.

Kisha tengeneza reports za:

Students wote
Students wasio na phone
Students wasio na email
Students wenye contact zote
Active students

92. Maswali ya Kujipima

Jaribu kujibu:

1. NULL ina maana gani?
2. Je NULL ni sawa na 0?
3. Je NULL ni sawa na empty string?
4. Je NULL ni sawa na 'NULL'?
5. IS NULL hutumika kufanya nini?
6. IS NOT NULL hutumika kufanya nini?
7. Kwa nini hatutumii = NULL?
8. NOT NULL ina maana gani?
9. DEFAULT ina maana gani?
10. Unawezaje kuweka default status ya Active?
11. Unawezaje kuweka default quantity ya 0?
12. CURRENT_TIMESTAMP inaweza kutumika kufanya nini?
13. Kuna tofauti gani kati ya NULL na DEFAULT?
14. Kwa nini score 0 haitakiwi kuchanganywa na score NULL?

93. Muhtasari wa Somo

Katika somo hili tumejifunza:

NULL

ambayo ina maana hakuna value au value haijulikani.

Kutafuta NULL:

SELECT *
FROM students
WHERE phone_number IS NULL;

Kutafuta values ambazo zipo:

SELECT *
FROM students
WHERE phone_number IS NOT NULL;

Tumeona kuwa:

= NULL

si njia sahihi ya kawaida.

Badala yake tunatumia:

IS NULL

na:

IS NOT NULL

Tumejifunza pia:

NOT NULL

kwa columns ambazo lazima ziwe na value.

Na:

DEFAULT

kwa kuweka value automatically.

Mfano:

status VARCHAR(20) NOT NULL DEFAULT 'Active'

Pia tumeona:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

ambayo inaweza kuhifadhi muda wa record kutengenezwa automatically.

Sasa tunaelewa vizuri jinsi MySQL inavyoshughulikia missing values, optional information na default values.

Somo Linalofuata

Katika Somo la 19 tutajifunza:

ALTER TABLE – Jinsi ya Kubadilisha Structure ya Table Iliyopo Katika MySQL

Tutajifunza:

• ALTER TABLE
• Kuongeza column mpya
• ADD COLUMN
• Kubadilisha data type
• MODIFY COLUMN
• Kubadilisha jina la column
• RENAME COLUMN
• Kufuta column
• DROP COLUMN
• Kuongeza DEFAULT
• Kuongeza NOT NULL
• Kubadilisha table name
• Kuangalia structure kabla na baada ya ALTER
• Makosa ya kawaida
• Tahadhari kabla ya kubadilisha structure
• Mazoezi na challenges

Katika somo hilo tutaanza kujifunza jinsi ya kubadilisha structure ya database baada ya table tayari kutengenezwa.