Karatasi ya Maelezo ya MySQL
Rejeleo linalotafutika na linaloweza kuchapishwa la MySQL — hoja, joins, fahirisi, vitendakazi, aina za data na amri za usimamizi. Bila malipo.
Kuuliza (querying)
10SELECT * FROM users;
Chagua columns na rows zote
SELECT id, name FROM users;
Chagua columns mahususi
SELECT DISTINCT city FROM users;
Thamani za kipekee pekee
WHERE age > 18 AND active = 1
Chuja rows kwa masharti
WHERE name LIKE 'A%'
Mlinganisho wa muundo (% = herufi zozote)
WHERE id IN (1, 2, 3)
Linganisha thamani yoyote kwenye seti
WHERE age BETWEEN 18 AND 30
Kichujio cha masafa jumuishi
ORDER BY created_at DESC
Panga kushuka
LIMIT 10 OFFSET 20
Gawanya matokeo kwa kurasa
WHERE email IS NULL
Linganisha thamani za null
Joins
8INNER JOIN orders ON orders.user_id = users.id
Rows zinazolingana kwenye majedwali yote mawili
LEFT JOIN orders ON ...
Rows zote za kushoto + zinazolingana
RIGHT JOIN orders ON ...
Rows zote za kulia + zinazolingana
CROSS JOIN colors
Cartesian product
SELF JOIN: FROM emp e JOIN emp m ON e.mgr = m.id
Unganisha jedwali na lenyewe
USING (user_id)
Unganisha kwa column yenye jina sawa
UNION / UNION ALL
Changanya seti za matokeo
SELECT u.*, o.total FROM users u JOIN orders o
Pa majedwali alias kwa uwazi
Mkusanyiko (aggregation)
9COUNT(*)
Hesabu rows
SUM(amount)
Jumla ya column ya namba
AVG(price)
Thamani ya wastani
MIN(price), MAX(price)
Ndogo zaidi na kubwa zaidi
GROUP BY country
Kusanya rows kwa aggregation
HAVING COUNT(*) > 5
Chuja kwenye makundi yaliyokusanywa
GROUP_CONCAT(name)
Unganisha thamani zilizokusanywa
COUNT(DISTINCT city)
Hesabu thamani za kipekee
ROW_NUMBER() OVER (ORDER BY id)
Upangaji wa window function
Kubadilisha data
8INSERT INTO users (name) VALUES ('Sam');
Ingiza row moja
INSERT INTO users (name) VALUES ('A'),('B');
Ingiza rows nyingi
UPDATE users SET active = 1 WHERE id = 5;
Sasisha rows zinazolingana
DELETE FROM users WHERE id = 5;
Futa rows zinazolingana
TRUNCATE TABLE logs;
Ondoa rows zote haraka
INSERT ... ON DUPLICATE KEY UPDATE
Upsert kwa key ya kipekee
REPLACE INTO users ...
Futa + ingiza wakati wa mgongano
INSERT IGNORE INTO ...
Ruka rows zinazoweza kuleta kosa
Schema / DDL
9CREATE TABLE users (id INT PRIMARY KEY);
Tengeneza jedwali jipya
AUTO_INCREMENT
Column ya kuhesabu otomatiki
ALTER TABLE users ADD COLUMN age INT;
Ongeza column
ALTER TABLE users DROP COLUMN age;
Ondoa column
ALTER TABLE users MODIFY name VARCHAR(100);
Badilisha aina ya column
DROP TABLE IF EXISTS users;
Futa jedwali
FOREIGN KEY (user_id) REFERENCES users(id)
Rejea jedwali lingine
ON DELETE CASCADE
Sambaza ufutaji kwa watoto
CREATE TABLE t2 LIKE t1;
Nakili muundo wa jedwali
Indexes na keys
8PRIMARY KEY (id)
Kitambulishi cha kipekee cha row
UNIQUE (email)
Lazimisha thamani za kipekee
CREATE INDEX idx_name ON users (name);
Harakisha utafutaji kwenye column
CREATE INDEX idx_ab ON t (a, b);
Index ya mchanganyiko (columns nyingi)
DROP INDEX idx_name ON users;
Ondoa index
FULLTEXT (body)
Index ya utafutaji wa full-text
EXPLAIN SELECT ...;
Onyesha mpango wa query
SHOW INDEX FROM users;
Orodhesha indexes za jedwali
Aina za data
9INT, BIGINT, TINYINT
Aina za integer kwa ukubwa
DECIMAL(10, 2)
Namba kamili za fixed-point
VARCHAR(255)
String ya urefu unaobadilika
TEXT, LONGTEXT
Vipande vikubwa vya maandishi
DATE, DATETIME, TIMESTAMP
Aina za tarehe na muda
BOOLEAN (TINYINT(1))
True/false zilizohifadhiwa kama 0/1
JSON
Column ya hati ya JSON ya asili
ENUM('a', 'b')
Moja kati ya seti maalum ya thamani
UNSIGNED
Kibadilishaji cha namba zisizo hasi
Type sizes & limits
20TINYINT
1 byte: -128..127 (0..255 UNSIGNED)
SMALLINT
2 bytes: -32,768..32,767 (0..65,535)
MEDIUMINT
3 bytes: -8.39M..8.39M (0..16.78M)
INT
4 bytes: -2.15B..2.15B (0..4.29B)
BIGINT
8 bytes: ±9.22×10^18 (0..1.84×10^19)
DECIMAL(M, D)
Exact: M up to 65 digits, D up to 30
FLOAT / DOUBLE
4 / 8 bytes approximate floating point
CHAR(M)
Fixed length, 0..255 characters
VARCHAR(M)
0..65,535 bytes (shared 64KB row limit)
TINYTEXT
Up to 255 bytes (~255 chars)
TEXT
Up to 65,535 bytes (64 KB)
MEDIUMTEXT
Up to 16,777,215 bytes (16 MB)
LONGTEXT
Up to 4,294,967,295 bytes (4 GB)
BLOB types
TINY/—/MEDIUM/LONG: 255B..4GB (binary)
JSON
Stored as LONGBLOB, up to ~4 GB
ENUM / SET
ENUM: 65,535 members; SET: 64 members
DATE
1000-01-01 to 9999-12-31 (3 bytes)
DATETIME
1000 to 9999, microseconds (5–8 bytes)
TIMESTAMP
1970-01-01 to 2038-01-19 UTC (4 bytes)
TIME / YEAR
±838:59:59 / 1901..2155
Functions
9NOW(), CURDATE()
Datetime / tarehe ya sasa
DATE_FORMAT(d, '%Y-%m-%d')
Panga tarehe
DATEDIFF(a, b)
Siku kati ya tarehe mbili
CONCAT(a, ' ', b)
Unganisha strings
COALESCE(a, b, 'n/a')
Thamani ya kwanza isiyo null
IFNULL(x, 0)
Badilisha null na chaguo-msingi
CASE WHEN x > 0 THEN '+' ELSE '-' END
Usemi wa masharti
CAST(x AS CHAR)
Badilisha aina ya thamani
ROUND(x, 2)
Zungusha hadi desimali
Transactions na utawala
9START TRANSACTION;
Anza transaction
COMMIT;
Hifadhi transaction
ROLLBACK;
Tengua transaction
SAVEPOINT sp1;
Weka pointi ya rollback
SHOW TABLES;
Orodhesha majedwali kwenye database
DESCRIBE users;
Onyesha muundo wa jedwali
SHOW PROCESSLIST;
Orodhesha connections zinazoendelea
GRANT ALL ON db.* TO 'u'@'%';
Toa ruhusa za mtumiaji
mysqldump -u root db > db.sql
Hifadhi nakala ya database (CLI)
Hakuna kipengele kinacholingana na “:q”.
Unahitaji msaada?
Umepata tatizo na zana hii? Tujulishe timu yetu.