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

Katika somo lililopita tulijifunza kutumia:

ORDER BY

kupanga data kwa:

ASC

na:

DESC

Sasa tutajifunza jinsi ya kuonyesha idadi maalumu tu ya records badala ya kurudisha records zote kutoka kwenye table.

Command muhimu tutakayotumia ni:

LIMIT

Pia tutajifunza:

OFFSET

Hizi ni muhimu sana katika mifumo halisi kama:

• Pagination
• Top 10 students
• Latest 5 posts
• Products 20 kwa page
• Recent transactions
• Latest users
• Top selling products
• Top marks
• Admin dashboards
• Search results

Malengo ya Somo

Baada ya kumaliza somo hili utaweza:

• Kuelewa LIMIT ni nini
• Kuonyesha record moja tu
• Kuonyesha records 5
• Kuonyesha records 10
• Kuchanganya LIMIT na ORDER BY
• Kupata top students
• Kupata lowest marks
• Kupata records mpya zaidi
• Kuelewa OFFSET
• Kutumia LIMIT na OFFSET pamoja
• Kuelewa msingi wa pagination
• Kutengeneza queries zinazotumika kwenye mifumo halisi

1. LIMIT ni Nini?

LIMIT ni SQL clause inayotumika kupunguza idadi ya records zinazotolewa na query.

Mfano:

SELECT *
FROM students
LIMIT 5;

Hii ina maana:

Onyesha records 5 tu kutoka table ya students.

2. Structure ya LIMIT

Muundo wa kawaida ni:

SELECT columns
FROM table_name
LIMIT number;

Mfano:

SELECT *
FROM students
LIMIT 10;

Hii itaonyesha records zisizozidi 10.

3. Tutumie Table ya Students

Fikiria tuna table:

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

Ingiza data:

INSERT INTO students (
    student_name,
    gender,
    age,
    class_name
)
VALUES
('Asha Mussa', 'Female', 16, 'Form One'),
('John Peter', 'Male', 17, 'Form Two'),
('Neema Joseph', 'Female', 15, 'Form One'),
('Peter Michael', 'Male', 18, 'Form Three'),
('Anna Charles', 'Female', 17, 'Form Two'),
('Joseph Mussa', 'Male', 16, 'Form One'),
('Rehema John', 'Female', 18, 'Form Three'),
('Michael Peter', 'Male', 17, 'Form Two'),
('Grace Joseph', 'Female', 16, 'Form One'),
('Thomas Charles', 'Male', 19, 'Form Four');

4. Kuonyesha Students Wote

Bila LIMIT:

SELECT *
FROM students;

MySQL inaweza kuonyesha records zote.

Lakini ikiwa tuna students:

10
100
1,000
10,000
100,000

huenda tusihitaji kuonyesha wote kwa wakati mmoja.

Hapo ndipo LIMIT inapokuwa muhimu.

5. Kuonyesha Records 5 Tu

SELECT *
FROM students
LIMIT 5;

Hii itarudisha records zisizozidi tano.

6. Kuonyesha Record Moja Tu

SELECT *
FROM students
LIMIT 1;

Hii inaweza kurudisha record moja tu.

Lakini kumbuka:

Bila ORDER BY, hupaswi kutegemea kuwa record hiyo itakuwa "ya kwanza", "ya mwisho", au "mpya zaidi" kwa maana unayotaka.

Ikiwa order ni muhimu, tumia ORDER BY.

7. LIMIT na ORDER BY

Hii ndiyo combination inayotumika sana.

Mfano:

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 5;

Hii itaonyesha records 5 za mwanzo kulingana na student_id.

8. Records za Mwisho kwa ID

SELECT *
FROM students
ORDER BY student_id DESC
LIMIT 5;

Hii inaweza kuonyesha records 5 zenye IDs kubwa zaidi.

Katika table yenye AUTO_INCREMENT, mara nyingi hizi zinaweza kuwa miongoni mwa records zilizoongezwa mwisho.

Lakini kwa tarehe halisi, ni bora kutumia column kama:

created_at

9. LIMIT na Student Name

Tunaweza kuandika:

SELECT *
FROM students
ORDER BY student_name ASC
LIMIT 5;

Hii itapanga majina A–Z na kuonyesha 5 za mwanzo tu.

10. Students Wenye Age Kubwa Zaidi

SELECT *
FROM students
ORDER BY age DESC
LIMIT 3;

Hii inaweza kuonyesha students 3 wenye age kubwa zaidi.

11. LIMIT na Columns Maalumu

Si lazima kutumia:

SELECT *

Mfano:

SELECT
    student_name,
    age
FROM students
ORDER BY age DESC
LIMIT 3;

12. LIMIT na WHERE

Tunaweza kuchanganya:

SELECT *
FROM students
WHERE gender = 'Female'
LIMIT 3;

Hii itaonyesha Female students wasiozidi 3.

13. WHERE + ORDER BY + LIMIT

Mfano mzuri zaidi:

SELECT *
FROM students
WHERE gender = 'Female'
ORDER BY age DESC
LIMIT 3;

Hii ina maana:

1. Chagua Female students.
2. Wapange kwa age kubwa hadi ndogo.
3. Onyesha 3 tu.

14. Mpangilio wa Query

Kwa sasa kumbuka:

SELECT columns
FROM table_name
WHERE condition
ORDER BY column
LIMIT number;

Mfano:

SELECT
    student_name,
    age
FROM students
WHERE gender = 'Female'
ORDER BY age DESC
LIMIT 3;

15. LIMIT na Table ya Marks

Tutengeneze:

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

Ingiza data:

INSERT INTO marks (
    student_name,
    subject,
    score
)
VALUES
('Asha Mussa', 'Mathematics', 85),
('John Peter', 'Mathematics', 42),
('Neema Joseph', 'Mathematics', 91),
('Peter Michael', 'Mathematics', 55),
('Anna Charles', 'Mathematics', 38),
('Joseph Mussa', 'Mathematics', 72),
('Rehema John', 'Mathematics', 88),
('Michael Peter', 'Mathematics', 64);

16. Top 3 Students kwa Marks

SELECT
    student_name,
    score
FROM marks
ORDER BY score DESC
LIMIT 3;

Result inaweza kuwa:

Neema Joseph     91
Rehema John      88
Asha Mussa       85

17. Lowest 3 Marks

SELECT
    student_name,
    score
FROM marks
ORDER BY score ASC
LIMIT 3;

Hii inaweza kuonyesha marks 3 ndogo zaidi.

18. Top 5 Mathematics Students

Ikiwa table ina subjects tofauti:

SELECT
    student_name,
    score
FROM marks
WHERE subject = 'Mathematics'
ORDER BY score DESC
LIMIT 5;

Huu ni msingi wa report ya:

Top 5 Mathematics Students

19. LIMIT katika Mfumo wa Results

Academic system inaweza kuhitaji:

Top 10 students

Query inaweza kuwa:

SELECT
    student_name,
    score
FROM marks
ORDER BY score DESC
LIMIT 10;

20. LIMIT na Products

Tutengeneze:

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

Ingiza:

INSERT INTO products (
    product_name,
    category,
    price,
    quantity
)
VALUES
('Laptop', 'Electronics', 1500000, 5),
('Mouse', 'Electronics', 25000, 50),
('Keyboard', 'Electronics', 45000, 30),
('Office Chair', 'Furniture', 180000, 10),
('Desk', 'Furniture', 250000, 6),
('Printer', 'Electronics', 450000, 8);

21. Products 3 za Bei Kubwa Zaidi

SELECT *
FROM products
ORDER BY price DESC
LIMIT 3;

22. Products 3 za Bei Ndogo Zaidi

SELECT *
FROM products
ORDER BY price ASC
LIMIT 3;

23. Products Zenye Stock Ndogo

SELECT
    product_name,
    quantity
FROM products
ORDER BY quantity ASC
LIMIT 5;

Hii inaweza kutumika kuonyesha products zinazohitaji restock kwanza.

24. OFFSET ni Nini?

OFFSET inatumika kuruka records fulani kabla ya kuanza kuonyesha result.

Mfano:

SELECT *
FROM students
LIMIT 5 OFFSET 5;

Hii ina maana:

Ruka records 5 za kwanza, kisha onyesha records 5 zinazofuata.

25. LIMIT Bila OFFSET

SELECT *
FROM students
LIMIT 5;

Hii huanza kutoka mwanzo wa result.

26. LIMIT 5 OFFSET 5

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 5 OFFSET 5;

Ikiwa records zimepangwa kwa ID:

1
2
3
4
5
6
7
8
9
10

MySQL itaruka:

1
2
3
4
5

na kuonyesha:

6
7
8
9
10

27. OFFSET 0

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 5 OFFSET 0;

Hii itaonyesha records 5 za mwanzo.

28. Pagination ni Nini?

Pagination ni kugawa records katika pages.

Mfano tuna students 100.

Badala ya kuonyesha wote kwa page moja, tunaweza kufanya:

Page 1 -> students 1–10
Page 2 -> students 11–20
Page 3 -> students 21–30

Hii ndiyo matumizi makubwa ya LIMIT na OFFSET.

29. Page 1

Ikiwa tunataka records 10 kwa page:

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 10 OFFSET 0;

Hii ni:

Page 1

30. Page 2

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 10 OFFSET 10;

Hii inaruka records 10 za kwanza.

31. Page 3

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 10 OFFSET 20;

32. Formula ya OFFSET

Formula muhimu ni:

OFFSET = (Page Number - 1) × Records Per Page

Mfano:

Page = 1
Records Per Page = 10

Offset:

(1 - 1) × 10 = 0

33. Mfano wa Page 2

Page = 2
Records Per Page = 10

Offset:

(2 - 1) × 10 = 10

Query:

SELECT *
FROM students
LIMIT 10 OFFSET 10;

34. Mfano wa Page 5

Page = 5
Records Per Page = 10

Offset:

(5 - 1) × 10 = 40

Query:

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 10 OFFSET 40;

35. Syntax Nyingine ya LIMIT

MySQL pia ina syntax:

LIMIT offset, row_count;

Mfano:

SELECT *
FROM students
LIMIT 5, 10;

Hapa:

5

ni offset.

Na:

10

ni number ya records zinazotakiwa.

Ni sawa kwa wazo na:

SELECT *
FROM students
LIMIT 10 OFFSET 5;

36. Ni Syntax Ipi Rahisi kwa Beginner?

Kwa beginner, hii ni rahisi kusoma:

LIMIT 10 OFFSET 5;

Kwa sababu inaonyesha wazi:

LIMIT = records ngapi
OFFSET = records ngapi zirukwe

37. LIMIT Katika Blog System

Fikiria tuna table:

CREATE TABLE posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    created_at DATETIME
);

Tunaweza kuonyesha latest posts 5:

SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 5;

38. Latest 10 Blog Posts

SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 10;

Hii ndiyo query ambayo unaweza kutumia kwenye sehemu kama:

Recent Posts

39. LIMIT Katika News Website

Homepage inaweza kuonyesha habari 6 tu:

SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 6;

Kisha page nyingine inaweza kuwa na habari zote kwa pagination.

40. LIMIT Katika Admin Dashboard

Dashboard inaweza kuonyesha:

Latest 5 Students
Latest 5 Payments
Latest 5 Users
Latest 5 Posts

Mfano:

SELECT *
FROM students
ORDER BY student_id DESC
LIMIT 5;

41. Latest Records kwa created_at

Njia nzuri zaidi ikiwa una tarehe:

SELECT *
FROM students
ORDER BY created_at DESC
LIMIT 5;

Hii inatumia tarehe halisi ya record.

42. LIMIT na AND

Tunaweza kutumia:

SELECT *
FROM students
WHERE gender = 'Female'
AND age >= 16
ORDER BY age DESC
LIMIT 3;

43. LIMIT na OR

Mfano:

SELECT *
FROM marks
WHERE score >= 80
OR score < 40
ORDER BY score DESC
LIMIT 5;

44. LIMIT na DISTINCT

Tunaweza pia kuandika:

SELECT DISTINCT class_name
FROM students
ORDER BY class_name ASC
LIMIT 3;

Hii itaonyesha class names zisizojirudia, zisizozidi 3.

45. LIMIT na Alias

SELECT
    student_name AS 'Student Name',
    score AS Marks
FROM marks
ORDER BY Marks DESC
LIMIT 5;

46. LIMIT 0

Unaweza kukutana na:

SELECT *
FROM students
LIMIT 0;

Hii haitarudisha records.

Inaweza kutumika katika mazingira maalumu, lakini si matumizi ya kawaida kwa beginner.

47. Ikiwa Table Ina Records Chache Kuliko LIMIT

Fikiria table ina records 3 tu.

Ukiandika:

SELECT *
FROM students
LIMIT 10;

MySQL haitaleta error kwa sababu haina records 10.

Itarudisha records 3 zilizopo.

LIMIT 10 ina maana:

Rudisha hadi records 10.

48. Makosa ya Kawaida – LIMIT Kabla ya ORDER BY

Mfano mbaya:

SELECT *
FROM students
LIMIT 5
ORDER BY student_name ASC;

Sahihi:

SELECT *
FROM students
ORDER BY student_name ASC
LIMIT 5;

49. Makosa ya Kawaida – LIMIT Kabla ya WHERE

Mfano mbaya:

SELECT *
FROM students
LIMIT 5
WHERE gender = 'Female';

Sahihi:

SELECT *
FROM students
WHERE gender = 'Female'
LIMIT 5;

50. Query Order ya Msingi

Mpangilio wa clauses tulizojifunza mpaka sasa ni:

SELECT
FROM
WHERE
ORDER BY
LIMIT

Mfano:

SELECT
    student_name,
    age
FROM students
WHERE gender = 'Female'
ORDER BY age DESC
LIMIT 5;

51. LIMIT Bila ORDER BY

Mfano:

SELECT *
FROM students
LIMIT 5;

Hii ni valid.

Lakini ikiwa unahitaji records maalumu kama:

Top 5
Latest 5
Oldest 5
Highest 5

lazima ueleze order.

Mfano:

ORDER BY score DESC
LIMIT 5;

52. Makosa ya Kawaida – Kudhani LIMIT 1 ni Latest Record

Hii:

SELECT *
FROM students
LIMIT 1;

haimaanishi automatically latest student.

Kwa latest record unaweza kutumia:

SELECT *
FROM students
ORDER BY student_id DESC
LIMIT 1;

Au bora zaidi:

SELECT *
FROM students
ORDER BY created_at DESC
LIMIT 1;

53. Kupata Highest Mark

SELECT
    student_name,
    score
FROM marks
ORDER BY score DESC
LIMIT 1;

Hii inaweza kuonyesha student mwenye score kubwa zaidi.

54. Kupata Lowest Mark

SELECT
    student_name,
    score
FROM marks
ORDER BY score ASC
LIMIT 1;

55. Top 3 Students

SELECT
    student_name,
    score
FROM marks
ORDER BY score DESC
LIMIT 3;

Hii inaweza kuonyesha:

Position 1
Position 2
Position 3

kwa mpangilio wa score.

Kumbuka kuwa ikiwa students wawili wana score sawa, ranking halisi yenye ties itahitaji logic zaidi ambayo tutajifunza baadaye.

56. LIMIT Katika PHP

Katika mfumo wa PHP unaweza kuwa na variable:

$limit = 10

na page:

$page = 2

Kisha concept ya offset ni:

offset = (page - 1) × limit

Kwa page 2:

offset = 10

SQL inaweza kuwa:

SELECT *
FROM students
ORDER BY student_id ASC
LIMIT 10 OFFSET 10;

Katika PHP halisi data za user zinapaswa kuthibitishwa na queries zitengenezwe kwa usalama.

57. Pagination ya Website

Website inaweza kuwa na:

Previous
1
2
3
4
5
Next

Kila page inaweza kuonyesha records 10.

Page 1:

LIMIT 10 OFFSET 0

Page 2:

LIMIT 10 OFFSET 10

Page 3:

LIMIT 10 OFFSET 20

Page 4:

LIMIT 10 OFFSET 30

58. Faida za LIMIT

LIMIT inaweza kusaidia:

• Kupunguza data inayorudishwa
• Kutengeneza pagination
• Kuonyesha top records
• Kuonyesha latest records
• Kupunguza mzigo usio wa lazima
• Kufanya pages ziwe rahisi kwa user kusoma

Lakini performance ya database kubwa inaweza kuhitaji pia indexes na pagination strategies za advanced ambazo tutajifunza baadaye.

59. Mfano wa Mfumo wa Shule

Tafuta Female students 5 wenye age kubwa zaidi:

SELECT
    student_name,
    age,
    class_name
FROM students
WHERE gender = 'Female'
ORDER BY age DESC
LIMIT 5;

60. Mfano wa Mfumo wa Marks

Top 10 Mathematics:

SELECT
    student_name,
    score
FROM marks
WHERE subject = 'Mathematics'
ORDER BY score DESC
LIMIT 10;

61. Mfano wa Shop System

Products 5 za bei kubwa zaidi:

SELECT
    product_name,
    price
FROM products
ORDER BY price DESC
LIMIT 5;

62. Mfano wa Inventory

Products 5 zenye quantity ndogo zaidi:

SELECT
    product_name,
    quantity
FROM products
ORDER BY quantity ASC
LIMIT 5;

63. Mazoezi ya Somo

Tumia table ya students.

Andika queries za:

1. Kuonyesha students 5 tu.
2. Kuonyesha students 3 wenye age kubwa zaidi.
3. Kuonyesha Female students 4 tu.
4. Kuonyesha students 5 kwa majina A–Z.
5. Kuonyesha students 5 wenye IDs kubwa zaidi.
6. Kuonyesha records 5 baada ya kuruka records 5 za kwanza.
7. Kuonyesha records 10 za page ya pili.

64. Challenge ya Somo

Tumia table ya:

products

yenye:

product_id
product_name
category
price
quantity

Andika queries za:

1. Kuonyesha products 5 tu.
2. Kuonyesha products 3 za bei kubwa zaidi.
3. Kuonyesha products 3 za bei ndogo zaidi.
4. Kuonyesha Electronics 5 zenye price kubwa zaidi.
5. Kuonyesha products 5 zenye quantity ndogo zaidi.
6. Kuonyesha page ya pili ikiwa kila page ina products 5.
7. Kuonyesha product moja yenye price kubwa zaidi.

Mfano:

SELECT
    product_name,
    price
FROM products
ORDER BY price DESC
LIMIT 1;

65. Maswali ya Kujipima

Jaribu kujibu:

1. LIMIT inatumika kufanya nini?
2. LIMIT 5 ina maana gani?
3. OFFSET inatumika kufanya nini?
4. LIMIT 10 OFFSET 20 ina maana gani?
5. Unawezaje kupata highest score?
6. Unawezaje kupata lowest score?
7. Unawezaje kupata top 5 students?
8. Kwa nini ORDER BY ni muhimu pamoja na LIMIT?
9. Formula ya pagination OFFSET ni ipi?
10. Page 4 yenye records 10 kwa page ina OFFSET gani?

Jibu la swali la mwisho ni:

(4 - 1) × 10 = 30

Kwa hiyo:

LIMIT 10 OFFSET 30;

66. Muhtasari wa Somo

Katika somo hili tumejifunza:

LIMIT

ambayo inapunguza records zinazotolewa.

Mfano:

SELECT *
FROM students
LIMIT 5;

Tumejifunza pia:

OFFSET

Mfano:

SELECT *
FROM students
LIMIT 5 OFFSET 10;

Tumejifunza kuchanganya:

WHERE
ORDER BY
LIMIT
OFFSET

Mfano:

SELECT
    student_name,
    score
FROM marks
WHERE subject = 'Mathematics'
ORDER BY score DESC
LIMIT 5;

Pia tumeona msingi wa pagination:

OFFSET = (Page Number - 1) × Records Per Page

Sasa tunaweza kuonyesha records chache, top records, latest records na kugawa data kwenye pages.

Somo Linalofuata

Katika Somo la 14 tutajifunza:

UPDATE – Jinsi ya Kubadilisha Data Iliyopo Katika MySQL

Tutajifunza:

• UPDATE
• SET
• Kubadilisha jina
• Kubadilisha phone number
• Kubadilisha marks
• Kubadilisha columns zaidi ya moja
• Kutumia WHERE kwenye UPDATE
• Hatari ya UPDATE bila WHERE
• Ku-update record kwa Primary Key
• Kutumia conditions kwenye UPDATE
• Kuangalia data kabla na baada ya UPDATE
• Makosa ya kawaida
• Mazoezi ya UPDATE

Hili ni somo muhimu sana kwa sababu mifumo halisi lazima iwe na uwezo wa kurekebisha taarifa zilizohifadhiwa kwenye database.