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:
| id | name | age | grade |
|---|---|---|---|
| 1 | Amit Sharma | 15 | 10th |
| 2 | Neha Verma | 14 | 9th |
| 3 | Ravi Kumar | 16 | 11th |
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:
| id | name | age | grade |
|---|---|---|---|
| 1 | Amit Sharma | 15 | 10th |
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:
| id | name | age | grade |
|---|---|---|---|
| 1 | Amit Sharma | 16 | 10th |
| 2 | Neha Verma | 14 | 9th |
| 3 | Ravi Kumar | 16 | 11th |
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:
| id | name | age | grade |
|---|---|---|---|
| 1 | Amit Sharma | 16 | 10th |
| 3 | Ravi Kumar | 16 | 11th |
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:
| id | name | age | grade | gender |
|---|---|---|---|---|
| 1 | Amit Sharma | 16 | 10th | NULL |
| 3 | Ravi Kumar | 16 | 11th | NULL |
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:
| average_age |
|---|
| 16 |
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:
| grade | num_students |
|---|---|
| 10th | 1 |
| 11th | 1 |
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.
