A Comprehensive Guide to MySQL Queries

MySQL is a widely-used relational database management system that allows for the creation, modification, and management of databases using SQL (Structured Query Language). In this guide, we’ll cover the essential MySQL queries with examples and tables to help you master the basics.

Creating a Database

Before you can create tables, you need a database. Use the following query to create one:

CREATE DATABASE my_database;

2. Using a Database

To start working with a database, you need to select it:

USE my_database;

3. Creating a Table

Now, let’s create a table named students:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

4. Inserting Data

To insert data into the students table, use the INSERT query:

INSERT INTO students (name, age, grade)
VALUES ('Amit Sharma', 15, '10th'),
       ('Neha Verma', 14, '9th'),
       ('Ravi Kumar', 16, '11th');

5. Selecting Data

Retrieve all records from the students table using the SELECT query:

SELECT * FROM students;

Example Output:

6. Filtering Data

Use the WHERE clause to filter data. For example, to find students in the 10th grade:

SELECT * FROM students WHERE grade = '10th';

Example Output:

7. Updating Data

To update existing records, use the UPDATE query. For example, to change Amit Sharma’s age to 16:

UPDATE students
SET age = 16
WHERE name = 'Amit Sharma';

Example Output:

8. Deleting Data

To delete records, use the DELETE query. For example, to remove Neha Verma from the table:

DELETE FROM students WHERE name = 'Neha Verma';

Example Output:

9. Altering a Table

You can modify the structure of an existing table using the ALTER TABLE query. For example, to add a gender column:

ALTER TABLE students ADD gender VARCHAR(10);

Example Table Structure:

10. Joining Tables

If you have another table, courses, you can join it with the students table. First, create the courses table:

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

Insert some data into the courses table:

INSERT INTO courses (course_name, student_id)
VALUES ('Mathematics', 1),
       ('Science', 1),
       ('History', 3);

Now, join the tables to see which students are enrolled in which courses:

SELECT students.name, courses.course_name
FROM students
JOIN courses ON students.id = courses.student_id;

11. Aggregate Functions

Use aggregate functions to perform calculations on your data. For example, to find the average age of students:

SELECT AVG(age) AS average_age FROM students;

Example Output:

12. Grouping Data

Group data using the GROUP BY clause. For example, to find the number of students in each grade:

SELECT grade, COUNT(*) AS num_students
FROM students
GROUP BY grade;

Example Output:

Conclusion

These essential MySQL queries provide a solid foundation for working with relational databases. By mastering these commands, you can efficiently manage and manipulate your data, allowing you to build robust applications and perform detailed data analysis.