BASIC COMMANDS IN MYSQL:

Introduction

MySQL is a powerful ( RDBMS )Relational Database Management System widely used for managing and manipulating data.

First, let's define our employee table:

CREATE TABLE employee (empno INT PRIMARY KEY, name VARCHAR(255), age INT, country VARCHAR(255) );

This table represents basic employee information with columns for employee number (empno), name, age, and country.

1. SELECT - Retrieving Data

The SELECT statement retrieves data from a table. To get all data from the employee table:

SELECT * FROM employee;

Here, * represents all columns. You can also select specific columns:

SELECT empno, name FROM employee;

2. WHERE - Filtering Data

Use the WHERE clause to filter data based on a condition. For example, to get employees older than 25:

SELECT * FROM employee WHERE age > 25;

3. ORDER BY - Sorting Data

Sort data using the ORDER BY clause. To get employees sorted by age in descending order:

SELECT * FROM employee ORDER BY age DESC;

4. INSERT INTO - Adding Data

Add a new employee to the table using the INSERT INTO command:

INSERT INTO employee (empno, name, age, country) VALUES (102, 'Diya', 34, 'India');

5. UPDATE - Modifying Data

Update employee data using the UPDATE statement. For instance, to change the country of employee 101:

UPDATE employee SET country = 'Canada' WHERE empno = 101;

6. DELETE - Removing Data

Remove a record from the table with the DELETE statement. To delete employee 101:

DELETE FROM employee WHERE empno = 101;

7. Aggregate Functions - Analyzing Data

Use aggregate functions like AVG to analyze data. To find the average age of employees:

SELECT AVG(age) AS averageAge FROM employee;

8. GROUP BY - Grouping Data

Group data based on a column using the GROUP BY clause. For example, to count employees by country:

SELECT country, COUNT(*) AS employeeCount FROM employee GROUP BY country;

9. JOIN - Combining Tables

If you have another table, like departments, you can use JOIN to combine data:

SELECT employee.empno, employee.name, employee.country, department.deptName FROM employee JOIN department ON employee.empno = department.empno;

10. ALTER TABLE - Modifying Table Structure

Change the structure of your table using the ALTER TABLE command. To add a new column, such as email:

ALTER TABLE employee ADD COLUMN email VARCHAR(255);

11. SHOW - Displaying Information

Retrieve information about databases, tables, or columns using the SHOW command. To show all tables in the current database:

SHOW TABLES;

12. DROP - Removing Tables and Databases

Remove tables or databases using the DROP command. For example, dropping the employee table:

DROP TABLE employee;

13. LIMIT

Limit the number of rows returned by a query using the LIMIT clause. For example, to retrieve the first five rows:

SELECT * FROM employee LIMIT 5;

This is useful when dealing with large datasets or when you only need a specific subset of the results.

14.MAX and MIN:

Find the maximum and minimum age of employees:

SELECT MAX(age) AS maxAge, MIN(age) AS minAge FROM employee;

15.COUNT:

Count the total number of employees:

SELECT COUNT(*) AS totalEmployees FROM employee;

16.SUM:

Calculate the total age of all employees:

SELECT SUM(age) AS totalAge FROM employee;

17.AVG:

Find the average age of employees:

SELECT AVG(age) AS averageAge FROM employee;

18.Wildcard Examples:

Use % as a wildcard for zero or more characters:

SELECT * FROM employee WHERE name LIKE 'A%';

Use _ as a wildcard for a single character:

SELECT * FROM employee WHERE name LIKE 'A_';

19.IN:

Retrieve employees from specific countries:

SELECT * FROM employee WHERE country IN ('USA', 'India');

20.NOT IN:

Exclude employees from certain countries:

SELECT * FROM employee WHERE country NOT IN ('USA', 'India');

21.BETWEEN:

Retrieve employees within a specific age range:

SELECT * FROM employee WHERE age BETWEEN 25 AND 35;

22.NOT BETWEEN:

Exclude employees outside a certain age range:

SELECT * FROM employee WHERE age NOT BETWEEN 25 AND 35;