FULL MySQL + Python SQL Cheatsheet
1. Creating & Using a Database
CREATE DATABASE dbname;
USE dbname;
2. Creating a Table
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
marks INT
);
3. Inserting Data
INSERT INTO students (id, name, city, marks)
VALUES (1, 'Shivansh', 'Delhi', 90);
4. Selecting Data
SELECT * FROM students;
SELECT name, marks FROM students WHERE city = 'Delhi';
5. Sorting with ORDER BY
SELECT * FROM students ORDER BY marks DESC;
6. Updating Records
UPDATE students SET marks = 95 WHERE id = 1;
7. Altering Tables
- Add column: ALTER TABLE students ADD email VARCHAR(50);
- Modify column: ALTER TABLE students MODIFY marks FLOAT;
- Rename column: ALTER TABLE students RENAME COLUMN name TO student_name;
- Drop column: ALTER TABLE students DROP COLUMN email;
8. Deleting Data
- Delete rows: DELETE FROM students WHERE marks < 40;
- Delete all rows: TRUNCATE TABLE students;
- Drop table: DROP TABLE students;
FULL MySQL + Python SQL Cheatsheet
9. Grouping Data
SELECT city, AVG(marks)
FROM students
GROUP BY city
HAVING AVG(marks) > 60;
10. Python MySQL Integration
import mysql.connector
con = mysql.connector.connect(host='localhost', user='root', password='yourpassword', database='dbname')
cur = con.cursor()
cur.execute("SELECT * FROM students")
rows = cur.fetchall()
for row in rows:
print(row)
cur.close()
con.close()
11. From Me (Extra Pro Tips)
- Use `eval(input())` to auto-detect input type in Python (list, int, etc)
- GROUP BY is used with aggregate functions (SUM, COUNT, AVG...)
- Use WHERE before GROUP BY, and HAVING after
- `cursor = con.cursor(buffered=True)` in Python to avoid unread result errors
- `fetchone()`, `fetchmany()`, and `fetchall()` help retrieve results from queries
- Always `commit()` changes after INSERT/UPDATE/DELETE in Python
- Add UNIQUE constraint to avoid duplicates:
ALTER TABLE students ADD UNIQUE(name, city);
