MySQL Database Full Course – Somo la 13: LIMIT na OFFSET – Jinsi ya Kupunguza Idadi ya Records Katika MySQL
Karibu kwenye Somo la 13 la MySQL Database Full Course kwa Kiswahili.
Katika somo lililopita tulijifunza kutumia:
ORDER BYkupanga data kwa:
ASCna:
DESCSasa tutajifunza jinsi ya kuonyesha idadi maalumu tu ya records badala ya kurudisha records zote kutoka kwenye table.
Command muhimu tutakayotumia ni:
LIMITPia tutajifunza:
OFFSETHizi 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
LIMITni nini - Kuonyesha record moja tu
- Kuonyesha records 5
- Kuonyesha records 10
- Kuchanganya
LIMITnaORDER 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,000huenda 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_at9. 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:
- Chagua Female students.
- Wapange kwa age kubwa hadi ndogo.
- 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 8517. 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 Students19. LIMIT katika Mfumo wa Results
Academic system inaweza kuhitaji:
Top 10 studentsQuery 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
10MySQL itaruka:
1
2
3
4
5na kuonyesha:
6
7
8
9
1027. 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–30Hii 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 130. 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 PageMfano:
Page = 1
Records Per Page = 10Offset:
(1 - 1) × 10 = 033. Mfano wa Page 2
Page = 2
Records Per Page = 10Offset:
(2 - 1) × 10 = 10Query:
SELECT *
FROM students
LIMIT 10 OFFSET 10;34. Mfano wa Page 5
Page = 5
Records Per Page = 10Offset:
(5 - 1) × 10 = 40Query:
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:
5ni offset.
Na:
10ni 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 zirukwe37. 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 Posts39. 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 PostsMfano:
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
LIMITMfano:
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 5lazima 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 3kwa 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 = 10na page:
$page = 2Kisha concept ya offset ni:
offset = (page - 1) × limitKwa page 2:
offset = 10SQL 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
NextKila page inaweza kuonyesha records 10.
Page 1:
LIMIT 10 OFFSET 0Page 2:
LIMIT 10 OFFSET 10Page 3:
LIMIT 10 OFFSET 20Page 4:
LIMIT 10 OFFSET 3058. 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:
- Kuonyesha students 5 tu.
- Kuonyesha students 3 wenye age kubwa zaidi.
- Kuonyesha Female students 4 tu.
- Kuonyesha students 5 kwa majina A–Z.
- Kuonyesha students 5 wenye IDs kubwa zaidi.
- Kuonyesha records 5 baada ya kuruka records 5 za kwanza.
- Kuonyesha records 10 za page ya pili.
64. Challenge ya Somo
Tumia table ya:
productsyenye:
product_id
product_name
category
price
quantityAndika queries za:
- Kuonyesha products 5 tu.
- Kuonyesha products 3 za bei kubwa zaidi.
- Kuonyesha products 3 za bei ndogo zaidi.
- Kuonyesha Electronics 5 zenye price kubwa zaidi.
- Kuonyesha products 5 zenye quantity ndogo zaidi.
- Kuonyesha page ya pili ikiwa kila page ina products 5.
- 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:
LIMITinatumika kufanya nini?LIMIT 5ina maana gani?OFFSETinatumika kufanya nini?LIMIT 10 OFFSET 20ina maana gani?- Unawezaje kupata highest score?
- Unawezaje kupata lowest score?
- Unawezaje kupata top 5 students?
- Kwa nini ORDER BY ni muhimu pamoja na LIMIT?
- Formula ya pagination OFFSET ni ipi?
- Page 4 yenye records 10 kwa page ina OFFSET gani?
Jibu la swali la mwisho ni:
(4 - 1) × 10 = 30Kwa hiyo:
LIMIT 10 OFFSET 30;66. Muhtasari wa Somo
Katika somo hili tumejifunza:
LIMITambayo inapunguza records zinazotolewa.
Mfano:
SELECT *
FROM students
LIMIT 5;Tumejifunza pia:
OFFSETMfano:
SELECT *
FROM students
LIMIT 5 OFFSET 10;Tumejifunza kuchanganya:
WHERE
ORDER BY
LIMIT
OFFSETMfano:
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 PageSasa 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:
UPDATESET- Kubadilisha jina
- Kubadilisha phone number
- Kubadilisha marks
- Kubadilisha columns zaidi ya moja
- Kutumia
WHEREkwenye 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.
🚀 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.