Grouping and connection query function implementation in Mysql

function

In Mysql, functions are a set of predefined instructions that are used to perform specific operations and return results. They can be compared to methods in Java. In SQL, functions are based on theirRange of action and return resultsMethods are divided into two categories: single-line functions and grouped functions

Single-line functions

A single-line function is characterized by operating on a row of data and returning only one result. A single-line function is usually used to process a single record data.

• Single-line functions can be divided into: character functions, mathematical functions, other functions, and process control functions

Character functions

• CHAR_LENGTH(S),LENGTH(S):Returns the length of the string

eg: Check the employee's name and number of names.

SELECT emplyee_name,CHARACTER_LENGTH(emplyee_name) FROM emplyees;

• CONCAT(S1,S2,…Sn):Concatenate more than two strings

eg:Stipped strings 'aaa','bbb','ccc'.

SELECT CONCAT('aaa','bbb','ccc');

• UPPER(),LOWER():Convert characters to uppercase and lowercase

eg:: Check the employee's email address and convert it to capitalization display

SELECT UPPER(email) FROM emplyees;

• substr,substring(S, start, length):Extract the string S starting from the start position and the length is length string

eg: Extract hello in hello world

SELECT substr('hello world',1,5);`

• replace(S, old, new):Replace all old with new in string S

eg: Check the employee phone number and ask to remove the horizontal line in the middle ’-’

SELECT REPLACE(phone_number, '-', '') FROM emplyees;

Mathematical functions

ROUND(X):Rounding the floating point number X

eg: Check employee salary and its rounded integer value

SELECT salary,ROUND(salary) FROM employees;

CEIL(X):Round the floating point number X upward, that is, return the minimum integer ≥X

eg: Check the employee's salary and get the order upward

SELECT salary,CEIL(salary) FROM employees;

FLOOR(X):Round the floating point number X downward, that is, return the maximum integer ≤X

eg: Check the employee's salary and collect the downward order

SELECT salary,FLOOR(salary) FROM employees;

TRUNCATE(X,length):Intercept the decimal part of a floating point number → commonly used for retaining decimal operations

SELECT TRUNCATE(1.9999,2);->1.99

• MOD(X,Y): Perform regional operations on two numbers, namely X%Y

Date function

• NOW(),SYSDATE(): Return to the current system date + time
• CURDATE():Returns the current system date, excluding time
• CURTIME(): Return to the current system time, excluding date
• DATE_FORMAT(date,format):Used to format dates, date is the data to be formatted, format is the formatted pattern, and the formatted wildcard symbols are as follows:

eg: Check the employee's name and job opening time, and the job opening time is output according to the xxx month xxx year xxx year xxx

SELECT DATE_FORMAT(hiredate,'%Y year %m month %d day') FROM employees;

Process control function

The process control function selectively returns different results in SQL according to conditions, which allows the implementation of conditional logic during querying.

• IF(expr,true_val,false_val):If expressionexprTrue, return the resulttrue_val, otherwise returnfalse_valResults

eg: If the query is older than 18, return adult, otherwise return minor

SELECT age,IF(age>=18,'adult','minor');

• CASESyntax structure: Similar to switch…case structure, used to implement multi-branch condition selection

SELECT exper1,exper2...,
CASE exper1
		 WHEN value1 THEN result1
		 WHEN value2 THEN result2
		 WHEN value3 THEN result3
		 ...
		 ELSE result
END
FROM table_name;

eg: Return the department name according to the query department number

SELECT department_id
CASE department_id
		 WHEN 1 THEN 'Manager'
		 WHEN 2 THEN 'Treasury Department'
		 WHEN 3 THEN 'logistics department'
		 ELSE 'unkown'
END AS department_name
FROM departments;

• Search CASE: The syntax structure is similar to naive CASE, but search CASE can return different values ​​according to various expression conditions.

SELECT exper1,exper2...,
CASE 
		 WHEN condition 1 THEN result1
		 WHEN condition 2 THEN result2
		 ...
		 ELSE result
END 
FROM table_name;

eg: Check the employee's name and salary, and the salary will be paid according to certain rules. Employee's salary before 2015-01-01*2, employee's salary before 2018-01-01*1.5, others will remain unchanged.

SELECT 
employee_name,hiredate,salary Original salary,
CASE
    WHEN hiredate<'2015-01-01' THEN salary*2
		WHEN hiredate<'2018-01-01' THEN salary*1.3
		ELSE salary
END AS New salary
FROM employees;

Grouping functions

Grouping functions are also called aggregate functions, which operate on a set of values ​​and return a single structure.

• COUNT(*):Calculate the number of non-NULL values ​​in the specified column,SUM(column): Calculate the sum of the specified columns,AVG(column): Calculate the average number of specified columns,MAX(colum):Take out the maximum value of the specified column,MIN(colum): Take out the minimum value of the specified column

eg: Check the sum of all employees’ wages, average, maximum, minimum, and number of employees;

SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(*) FROM employees;

Group query

Grouping queries can group data based on one or some columns

Group by single field

• passGROUP BYKeyword: Group by specified column

SELECT List
FROM surface
[WHERE Filter criteria]
GROUP BY Grouping
[ORDER BY Sort]

eg: Check the maximum salary of each department

SELECT MAX(salary) 
FROM employees 
GROUP BY department_id;

Conditional filtering before grouping

Use WHERE statements to filter query results in descending order between GROUP BY statements

eg: Check the information of employees whose entry time in each department is after 2010-01-01 and whose salary is the highest

SELECT *
FROM employees
WHERE hiredate >'2010-01-01'
GROUP BY department_id;

Conditional filtering after grouping

passHAVINGThe statement can be inGRUOP BYConditional filtering after the statement

eg: Inquiry of departments with more than 120 employees

SELECT *
FROM employees
GROUP BY department_id
HAVING COUNT(*)>120;

Group by multiple fields

• Multiple fields can be enclosed after the GROUP BY statement to implement multi-field grouping

eg: Check the average salary of male and female employees in each department

SELECT department_id,sex,AVG(salary) AS Average salary
FROM employees
GROUP BY department_id,sex;

Connection query

Connection query is a very important knowledge point in SQL, so there is "no connection, no matter how long you can spend all night", and connection query is also called multi-table query, which is used to convertThe data of more than two tables are combined together based on certain related conditions, Through join query, data can be extracted from the table and a new result set can be generated

• Cartesian product phenomenon:Suppose there are two tables, Table 1 has n rows of data and Table 2 has m rows of data,When using a connection query, n*m pieces of data will be generated, so when a condition query, you need to assume the condition of the connection

Inner connection (INNER JOIN)

Inner connection is used to returnAll rows of data in the two tables that meet the join conditions, internal connections can be divided into: equal value connection, non-equal value connection, and self-connection

Equivalent connection

Equivalent connection is a common connection method, which is connected based on the equality conditions of a column in two tables.

Its syntax structure is as follows:

SELECT colum1,colum2,....,
FROM table1 
INNER JOIN table2
ON  = ;

eg: Check the employee's name and department name

SELECT employee_name AS Employee name,department_name AS Department name
FROM employees e	
INNER JOIN departments d ON 
e.department_id=d.department_id;

Non-equal value connection

Non-equal value connections are connected based on unequal conditions in a column in two tables. For example, greater than, less than, not equal to, etc.

Syntax structure:

SELECT colum1,colum2,....,
FROM table1 
INNER JOIN
ON  <operator> ;

inoperatorCan be>,<,≥,≤,≠,BETWEEN…ANDetc;

eg: Check employee wages and salary levels

SELECT ,j.grade_level
FROM employees e
INNER JOIN job_grades j
ON  BETWEEN j.lowest_sal AND j.higest_sal;

Self-connection

Self-join refers to a join in the same table. This join is usually used to process data with hierarchical structures or recursive relationships in a table.

eg: Check the employee's name and corresponding direct leader

SELECT t1.employee_name AS staff,t2.employee_name AS lead
FROM employees t1
INNER JOIN employees t2
ON t1.manager_id=t2.employee_id;

External connection

External join is used to return rows in the main table that meet the join conditions, while retaining rows that do not match in another table.

Left/right external connection

• As the name suggests, the main table is distinguished according to the table position, that is, the left join is the left table, and the right join is the right side.

Syntax structure:

SELECT colum1,colum2...,
FROM table1 [LEFT|RIGHT]
JOIN ON [Connection conditions];

eg: Check the employee's name and department name. Employees who do not have department information must also find it.

SELECT employee_name AS Employee name,department_name AS Department name
FROM employees e LEFT
JOIN departments d
ON e.department_id=d.department_id;

This is the article about the commonly used function grouping and connection query in Mysql. For more related Mysql grouping and connection query content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!