49.3 MySQL Cheat Sheet
Database revision – connect, schema, SELECT family, writes, keys, safe updates, GRANT, backup awareness. Never expose RDS / MySQL to the public internet. Prepared statements pointer: ch29. Chala!
Connect and basics
| Command / SQL | Meaning | Blue note |
|---|---|---|
mysql -u root -p -h 127.0.0.1 | Connect local TCP | Prefer socket/local; never open 3306 to world |
SHOW DATABASES; | List DBs | — |
USE shop; | Select DB | — |
SHOW TABLES; | List tables | — |
DESCRIBE orders; | Columns / keys | Know primary key before UPDATE |
CREATE DATABASE shop; | Create DB | Least-privilege app user – not root in PHP |
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL); | Create table | Constraints beat “fix in app only” |
SELECT forms
| SQL pattern | Meaning | Blue note |
|---|---|---|
SELECT * FROM t LIMIT 10; | Sample rows | Avoid SELECT * in production apps |
SELECT name FROM t WHERE id = 5; | Filter | App layer: parameterised – never string-concat user input |
… ORDER BY price DESC; | Sort | — |
… LIKE 'Ra%'; | Pattern match | Wildcard abuse in SQLi themes – prepared statements |
… BETWEEN 10 AND 50; / IN (1,2,3) | Range / list | — |
SELECT a.*, b.* FROM a JOIN b ON a.id = b.a_id; | JOIN | AuthZ: join must not leak other tenants’ rows |
SELECT city, COUNT(*) FROM t GROUP BY city HAVING COUNT(*) > 2; | Aggregate + HAVING | — |
SELECT AVG(price), MAX(price) FROM t; | Aggregates | — |
INSERT / UPDATE / DELETE / ALTER
| SQL pattern | Meaning | Blue note |
|---|---|---|
INSERT INTO t (name) VALUES ('Raja'); | Insert row | Validate types in app |
UPDATE t SET name='Rani' WHERE id=1; | Update by key | Always WHERE on key; see safe updates |
DELETE FROM t WHERE id=1; | Delete row | Soft-delete patterns in real apps |
TRUNCATE TABLE t; | Empty table fast | Not for casual “cleanup” without backup |
DROP TABLE t; / DROP DATABASE shop; | Destroy | Irreversible without backup |
ALTER TABLE t ADD COLUMN phone VARCHAR(20); | Add column | Migrate with change ticket mindset |
ALTER TABLE t MODIFY COLUMN name VARCHAR(200); | Change type | Test on copy first |
ALTER TABLE t DROP COLUMN phone; | Drop column | Data loss risk |
ALTER TABLE t RENAME COLUMN old TO new; (version-dependent) | Rename | Check MySQL version docs |
Keys, constraints, safe updates, users
| Idea / SQL | Meaning | Blue note |
|---|---|---|
| PRIMARY / FOREIGN / UNIQUE / composite | Key types | Integrity (अखंडता) at DB layer |
NOT NULL / DEFAULT / CHECK / AUTO_INCREMENT | Constraints | Do not rely on UI alone |
SET SQL_SAFE_UPDATES = 1; | Block UPDATE/DELETE without key WHERE | Error 1175 protects fat-finger wipe |
CREATE USER 'app'@'localhost' IDENTIFIED BY '…'; | App user | Host localhost – not % for beginners |
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'app'@'localhost'; | Least privilege | No ALL PRIVILEGES for web app |
FLUSH PRIVILEGES; | Reload grants (when required) | — |
mysqldump -u u -p shop > shop.sql | Logical backup awareness | Encrypt backups; never public S3 without controls |
bind-address = 127.0.0.1 in config | Local-only listen | Pair with SG deny 3306 from internet |
| Red team (attacker) does | Blue team (defender) detects / stops |
|---|---|
| String-concats user input into SQL (SQLi) | Prepared statements / PDO (ch29); WAF as layer; least DB priv |
| Connects as root from PHP | Dedicated app@localhost with minimal GRANT |
Leaves 3306 open on public EIP / 0.0.0.0 |
bind-address=127.0.0.1; SG/firewall; ss -tulpn proof |
| Runs UPDATE without WHERE in panic | SQL_SAFE_UPDATES=1; backups; change tickets |
Ravindra Bagale's Tip
Students run DELETE FROM orders; without WHERE – the table is empty, tears follow. Keep safe updates ON in the lab. In interviews, the SQLi fix = prepared statements, not “I use LIMIT”. The RDS public checkbox = never for Raja-Rani style shops. Got it?
Ravindra Bagale's Tip – मराठी
Students WHERE शिवाय DELETE FROM orders; चालवतात – table empty, अश्रू. Lab मध्ये safe updates ON ठेवा. Interview मध्ये SQLi fix = prepared statements, “I use LIMIT” नाही. RDS public checkbox = Raja-Rani सारख्या shops साठी कधीच नाही. समजले का?
Ravindra Bagale's Tip – हिंदी
Students WHERE के बिना DELETE FROM orders; चला देते हैं – table खाली, आँसू. Lab में safe updates ON रखो. Interview में SQLi fix = prepared statements, “I use LIMIT” नहीं. RDS public checkbox = Raja-Rani जैसी shops के लिए कभी नहीं. समझ आया?
Lab
On OWN host-only MySQL/MariaDB: create DB rajarani_lab, table products, INSERT three rows, SELECT with WHERE/ORDER/LIMIT/JOIN (second tiny table), UPDATE by id with SQL_SAFE_UPDATES=1, create user app@localhost with SELECT/INSERT/UPDATE only. Confirm ss -tulpn shows 3306 on localhost only.