MySQL Basic Knowledge Points

What is MySQL?

MySQL is a widely used, open-source relational database management system (RDBMS). Besides relational database there are non relational database the differences are listed below.

The difference between relational database ot non-relational databases

Basic usage of MySQL

1: Connect to MySQL

From the command line:

mysql -u root -p 

-u root: login with the root user

-p: it will prompt you to enter the password

From Python (optional):

import mysql.connector conn = mysql.connector.connect( host="localhost", user="root", password="your_password", database="school_db" ) 

🧠 Explanation: Before doing anything with MySQL, you need to connect. The CLI is the most direct way for local development.

Database Operations

Create a new database:

CREATE DATABASE school_db; 

View all databases:

SHOW DATABASES; 

Delete a database:

DROP DATABASE test_db; 

🧠 Explanation: These commands let you manage the top-level containers of your data.

Table Operations

Create a table:

USE school_db; CREATE TABLE students ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), age INT, email VARCHAR(100) ); 

View table structure:

DESCRIBE students; 

Modify table (add a column):

ALTER TABLE students ADD gender VARCHAR(10); 

Delete table:

DROP TABLE students; 

🧠 Explanation: This is about creating and modifying the structure inside your database.

Data Operations (CRUD)

Insert data:

INSERT INTO students (name, age, email) VALUES ('Alice', 20, 'alice@example.com'); 

Query data:

SELECT * FROM students; 

Update data:

UPDATE students SET age = 21 WHERE name = 'Alice'; 

Delete data:

DELETE FROM students WHERE name = 'Alice'; 

🧠 Explanation: These are the most commonly used commands—managing the actual content of your tables.

Conditional Queries, Sorting, Pagination, Aggregation

Conditional query:

SELECT * FROM students WHERE age > 18; 

Sorting:

SELECT * FROM students ORDER BY age DESC; 

Pagination (show 10 results starting from the 21st):

SELECT * FROM students LIMIT 10 OFFSET 20; 

Aggregation (average age):

SELECT AVG(age) AS avg_age FROM students; 

🧠 Explanation: These help you refine and summarize your results, which is crucial for reports, analytics, or APIs.