To get us started with some useful MySQL commands I asked ChatGTP the following: "provide a basic employee table containing fake data with columns id, name, department and salary where the department information is in another table".
Lets go ahead and make a database called useful_mysql_commands and import the script below:
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);
INSERT INTO departments (name)
VALUES
('Sales'),
('Marketing'),
('Technology');
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
department_id INT,
salary INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
INSERT INTO employees (name, department_id, salary)
VALUES
('John Doe', 1, NULL),
('Jane Doe', 2, 65000),
('Bob Smith', 3, 75000),
('Alice Johnson', 1, 65000),
('Michael Davis', 2, 75000),
('Sarah Wilson', 3, 55000),
('William Brown', 1, 75000),
('Emma Wilson', 2, 65000),
('David Johnson', 3, 55000),
('Natalie Davis', 1, 65000),
('Richard Wilson', 2, 75000),
('Angela Johnson', 3, 55000),
('Christopher Davis', 1, 65000),
('Ashley Brown', 2, 75000),
('Brian Wilson', 3, 55000),
('Margaret Johnson', 1, 65000),
('Evelyn Davis', 2, 75000),
('Dorothy Wilson', 3, 55000),
('Joseph Brown', 1, 65000),
('Ava Johnson', 2, 75000),
('Daniel Davis', 3, 55000),
('Ruth Wilson', 1, 65000),
('Ryan Johnson', 2, 75000),
('Edward Davis', 3, 55000),
('Maria Brown', 1, 75000),
('Samantha Johnson', 2, 65000),
('Jenna Davis', 3, 55000),
('Harold Wilson', 1, 65000),
('Isabelle Johnson', 2, 75000),
('Brandon Davis', 3, 55000),
('Madison Brown', 1, 65000),
('Keith Johnson', 2, 75000),
('Deborah Wilson', 3, 55000),
('Mark Davis', 1, 65000),
('Olivia Johnson', 2, 75000),
('Avery Davis', 3, 55000),
('Aiden Wilson', 1, 65000),
('Lucas Johnson', 2, 75000),
('Ethan Davis', 3, 55000),
('Mia Brown', 1, 75000),
('William Johnson', 2, 65000),
('Sofia Davis', 3, 55000),
('Mason Wilson', 1, 65000),
('Emily Johnson', 2, 75000),
('Ella Davis', 3, 55000);
Great, you might of noticed that our first record is null regarding John Doe's salary (which is intended), now that our Database is ready and our records have been imported, let's take a look at some useful MySQL commands
1. COALESCE
The COALESCE function is a function in MySQL used to return the first non-NULL value from a list of expressions. It is often used to substitute missing or NULL values with a default value.
Example:
Suppose we have a table named employees with columns id, name, department, and salary. Some of the salary values are missing in the table, and we want to replace the missing values with the value R10,000.
To replace the missing salary values with R10,000, we can use the following query:
SELECT
id,
name
COALESCE(salary, 10000) as salary
FROM
employees;
In this query, the COALESCE function is used to substitute missing or NULL salary values with R10,000. The resulting table will have the missing salary values replaced with R10,000.
In conclusion, the COALESCE function is an important function in MySQL that can help you manage missing or NULL values in your data by providing a default value.
2. Simple Join
A join is a way to combine rows from two or more tables based on a related column between them. In MySQL, we have several types of join like inner join, left join, right join, and full outer join.
Example:
Using the two tables named employees and departments, To get the information of employees along with their respective departments, we can use the following query:
SELECT
employees.name,
departments.name
FROM
employees
JOIN
departments
ON
employees.department_id = departments.id;
3. Group By
The GROUP BY clause is used to group rows with similar values into summary rows. It is often used with aggregate functions like SUM, AVG, MAX, MIN, COUNT, etc.
Example:
To get the average salary of employees grouped by departments, we can use the following query:
SELECT
departments.name,
AVG(salary)
FROM
employees
JOIN
departments
ON
employees.department_id = departments.id
GROUP BY
departments.name;
4. Having Clause on Group By
The HAVING clause is used to filter the grouped result set and limit the number of groups returned. It is used after the GROUP BY clause and works like a filter on the groups.
Example:
To get the average salary of employees grouped by departments where the average salary is greater than R60,000, we can use the following query:
SELECT
departments.name,
AVG(salary)
FROM
employees
JOIN
departments
ON
employees.department_id = departments.id
GROUP BY
departments.name;
HAVING
AVG(salary) > 60000;
5. Indexes what they are and how to create one
Indexes are a common way to improve the performance of queries in MySQL. By creating an index on one or more columns of a table, you can speed up searches and reduce the amount of data that needs to be scanned.
Here's how you can create an index on the employees table:
CREATE INDEX idx_employee_name ON employees (name);
In this example, we're creating an index on the name column of the employees table. The idx_employee_name is the name of the index, which you can choose to be anything meaningful.
If you frequently run queries that search for employees by name, this index will significantly improve the performance of those queries. For example, here's a query that searches for all employees with a name containing 'John':
SELECT
*
FROM
employees
WHERE
name
LIKE '%John%';
With the index in place, this query will run much faster and be more efficient than without the index.
It's important to note that while indexes can improve query performance, they can also slow down updates and inserts, since the database has to maintain the index whenever data is changed. So it's important to consider the trade-off and choose which columns to index carefully.
