Source: About Data Analysis and Visualization

As a data analyst, you need to spend a lot of time every day to analyze and dig out the hidden information in the data to find new values, and nowadays most companies store their data in Mysql database.

Query all rows of data
This is also the most basic SQL query, suppose we have an employee table "Employee", which contains a series of employee information, including salary, country, etc.

SELECT * FROM Employee;
Query the data of the specified row
We go a step further and want to query the data of a specific row, here we are querying the employee ID, employee last name and employee first name, the code is as follows

SELECT EmployeeID, LastName, FirstNameFROM Employee;
Filtering data
We want to filter the data further by adding the corresponding conditions in the SQL, for example, we want to see the employees whose country is Germany, the code is as follows

SELECT * FROM Employee WHERE Location = 'Germany';
Multiple conditions to filter the data
In the above example, we only added one condition, but what if there is more than one condition?

SELECT * FROM EmployeeWHERE salary > 10000 AND Location = 'UK';
De-duplication
We sometimes query data with duplicate values, we can use DISTINCT to de-duplicate

SELECT DISTINCT EmployeeID,SalaryFROM Employee;
Sorting
For example, if we want to sort by the employee's name, the code is as follows

SELECT EmployeeID, FirstNameFROM EmployeeORDER BY FirstName
More than one column of data needs to be sorted
So if we need to sort more than one row of data, how do we do it? The code is as follows

SELECT EmployeeID, FirstNameFROM EmployeeORDER BY FirstName DESC,EmployeeID;
Where DESC is the abbreviation of descending, the above agent logic is to sort the FirstName of the employee in descending order and the EmployeeID column in ascending order

Filtering out data that is not null
If we want to filter out data that is not null, for example, we are targeting the FirstName column, the code is as follows

SELECT EmployeeID, FirstNameFROM EmployeeWHERE FirstName IS NOT NULL;
The beauty of wildcards
The role of wildcards is to facilitate our data filtering, for example, the FirstName column contains "a" data, the

SELECT EmployeeID, FirstNameFROM EmployeeWHERE FirstName LIKE '%a%';
CONCAT() function
concat() function is used to concatenate two strings to form a single string, for example, we can FirstName and LastName the two columns of data to concatenate, so that you can each employee's full name it

SELECT CONCAT(FirstName, ' ', LastName)FROM Employee;
Concatenation
If we combine the results of multiple SQL queries together, we need to use the UNION ALL operator, for example, we combine the "last_name" column in the employee table and the "last_name" column in the student table. " column in the student table to display

SELECT last_name FROM EmployeeUNION ALLSELECT last_name FROM Student;
Intersection
The INTERSECT operator is used to find the intersection of the results of multiple SQL statements, and the code is as follows

SELECT last_name FROM EmployeeINTERSECTSELECT last_name FROM Student;
Mathematical operations
For numeric data, we can also add, subtract, multiply and divide mathematical operations, for example

SELECT TotalSalary - GrossSalaryFROM Employee;
But only if the data in both columns are numeric, integer or floating point

Calculate the total number of rows of data
Here we use the COUNT() method, for example, when we want to calculate how many student_id's there are

SELECT COUNT(student_id)FROM student;
Average
The AVG() method is used to calculate the average, for example, to calculate the average salary, the code is as follows

SELECT AVG(Salary)FROM Employee;
Maximum value
The maximum value is used in the MAX() method, for example, to find out which employee has the most salary, the code is as follows

SELECT MAX(Salary)FROM Employee;
Minimum value
Directly on the code it

SELECT MIN(Salary)FROM Employee;
Inserting Data
If we want to insert new data into the table, the code is as follows

INSERT INTO Employee (EmployeeID, FirstName, LastName)VALUES ('A0001', 'Dom', 'Werler'),
Updating Data
For example, if we want to change the data in the Salary column of the Employee table to 2000, the code is as follows

UPDATE Employee SET Salary = 20000;
Create a new table
For example, we want to create a new employee table, the call is CREATE TABLE method, and each column in the table need to write the column name and its data type, the code is as follows

CREATE TABLE Employee(EmployeeID int, Firstname varchar(50));
Delete all the data in the table
DELETE FROM Employee;
Delete a table
All the data in the table are deleted, we can continue to add real data to it, but if you directly delete the table, you can not perform the operation of adding new data, because after all, the table is gone, the code is as follows

DROP TABLE Employee;