Location>code7788 >text

Mysql commonly used functions grouping, join query

Popularity：775 ℃/2024-10-17 17:52:55

function (math.)

Functions in Mysql are a set of predefined commands used to perform a specific operation and return the result, analogous to methods in Java. Functions in SQL are characterized by theirScope and return resultsMethods are divided into two categories: single-line functions, group functions

one-line function (math.)

Single-line functions are characterized by operating on one line of data and returning only one result. Single-line functions are usually used to process a single record of data

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

character function

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

eg:Query employee's name, number of characters in the name.

SELECT emplyee_name,CHARACTER_LENGTH(emplyee_name) FROM emplyees;
  • CONCAT(S1,S2,…Sn):Concatenate two or more strings

eg:Splice the strings 'aaa','bbb','ccc'.

SELECT CONCAT('aaa','bbb','ccc');
  • UPPER(),LOWER():Case conversion of characters

eg:: query employee email and convert to uppercase display

SELECT UPPER(email) FROM emplyees;
  • substr,substring(S, start, length):Extract the string S from the start position, the length of the length of the word string

eg:extract hello from hello world

SELECT substr('hello world',1,5);`
  • replace(S, old, new): Replace all old with new in string S.

eg:Query the phone number of an employee, and ask to remove the center line '-'.

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

math function

  • ROUND(X):Rounding a floating-point number X

    eg:query employee wages, and its rounded integer values

    SELECT salary,ROUND(salary) FROM employees;

  • CEIL(X):Round up the floating-point number X, i.e., return the smallest integer ≥ X.

    eg:query the employee's salary and round upwards

    SELECT salary,CEIL(salary) FROM employees;

  • FLOOR(X):Round down the floating-point number X, i.e., return the largest integer ≤ X.

    eg:Query the employee's salary and round down.

    SELECT salary,FLOOR(salary) FROM employees;

  • TRUNCATE(X,length):Intercepts the decimal portion of a floating-point number → commonly used for decimal preservation operations

    SELECT TRUNCATE(1.9999,2);->1.99

  • MOD(X,Y): Perform a region operation on two numbers i.e. X%Y

date function

  • NOW(),SYSDATE(): Returns the current system date + time
  • CURDATE():Returns the current system date, excluding time.
  • CURTIME(): Returns the current system time, excluding the date.
  • DATE_FORMAT(date,format): Used to format the date, date is the data to be formatted, format is the format of the mode, format the wildcard number of the following table.
formatting symbol functionality
%Y 4-digit year
%y 2-digit year
%m Month (01, 02, ..., 11, 12)
%c Months (1, 2, ..., 11, 12)
%d Day (01, 02, ...)
%H Hours (24-hour system)
%h Hours (12-hour system)
%i Minutes (00, 01, ..., 58, 59)
%s Seconds (00, 01, ..., 58, 59)

eg:Query the name of the employee, the entry time, the entry time in accordance with the xxxx xx xx output

SELECT DATE_FORMAT(hiredate,'%Ysurname Nian%mmoon%ddate') FROM employees;

Process Control Functions

Flow control functions in SQL selectively return different results based on conditions, which allows the implementation of conditional logic in the query process.

  • IF(expr,true_val,false_val): If the expressionexpris true, the result is returnedtrue_valOtherwise, returnfalse_valresults

eg: if the age of the query is greater than 18 then return result, otherwise return minor

SELECT age,IF(age>=18,'adult','minor');
  • CASESyntax structure: similar to the switch...case structure, used to realize the multi-branch conditional 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:Returns the name of the department based on the department number of the query.

SELECT department_id
CASE department_id
		 WHEN 1 THEN 'Manager's Office'
		 WHEN 2 THEN 'financial department'
		 WHEN 3 THEN 'Logistics Department'
		 ELSE 'unkown'
END AS department_name
FROM departments;
  • Search CASE: The syntax structure is similar to plain CASE, but search CASE can return different values according to many different expression conditions.
SELECT exper1,exper2...,
CASE 
		 WHEN condition 1 THEN result1
		 WHEN condition 2 THEN result2
		 ...
		 ELSE result
END 
FROM table_name;

eg:Query the name of the employee as well as the salary, the salary is issued in accordance with certain rules, the entry time in 2015-01-01 before the employee's salary * 2, the entry time in 2018-01-01 before the employee's salary * 1.5, other 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 wage
FROM employees;

grouping function

Grouping functions, also known as aggregation functions, are used to operate on a set of values and return a single structure

  • COUNT(*):counts the number of non-NULL values in the specified column.SUM(column):: Calculates the sum of the specified columns.AVG(column):: Calculate the average of the specified columns.MAX(colum):Maximum value of the specified column.MIN(colum):Remove the minimum value of the specified column

eg:query all employees salary sum, average value, maximum value, minimum value, the number of employees.

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

grouping inquiry

Grouping queries can group data by one or more columns

Grouping by Individual Fields

  • pass (a bill or inspection etc)GROUP BYKeywords:Group by specified columns
SELECT List
FROM Table
[WHERE Filter Criteria]
GROUP BY Grouping
[ORDER BY sort]

eg:Query the highest salary in each department

SELECT MAX(salary) 
FROM employees 
GROUP BY department_id;

Conditional filtering before grouping

Filtering query results in descending order using WHERE statements between GROUP BY statements

eg:Query each department after the start time in 2010-01-01, and the highest salary of the employee information

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

Conditional filtering after grouping

  • pass (a bill or inspection etc)HAVINGstatement can be found in theGRUOP BYstatement followed by conditional filtering

    eg:Search for departments with more than 120 employees.

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

Grouping by Multiple Fields

  • GRUOP BY statement can be followed by multiple fields to realize multi-field grouping.

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

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

connection query

Connection query is a very important knowledge point in SQL, there is a "connection will not be, all night for nothing", the connection query is also known as multi-table query for theData from two or more tables are combined based on certain correlation conditionsBy joining queries, you can extract data from a table and generate a new result set.

  • 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 join query, n*m data will be generated, so in the conditional query you need to assume the conditions of the join

INNER JOIN

An inner connection is used to return theAll rows of data in the two tables that meet the conditions of the connection, the inner connection can be divided into: equal connection, non-equal connection, self-connection

equivalence connection

Equivalent connection is a common way to connect, which is based on the equality of a column in the two tables to connect

The syntax is structured as follows.

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

eg:Query the name of the employee and the name of the department in which they are located

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

non-equivalent connection

A non-equivalent join is based on the inequality of a column in two tables. For example, greater than, less than, not equal to, etc.

Syntactic structure.

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

included among theseoperatorcan be>,<,≥,≤,≠,BETWEEN…ANDetc.; and

eg:Query employee's salary and salary grade

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

self-connecting

A self-join is a join of the same table. This type of join is often used when working with data that has a hierarchical structure or function recursion in the table.

eg:Query the name of the employee and the corresponding immediate supervisor.

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

external link

An outer join is used to return the rows in the main table that satisfy the join conditions, while retaining the unmatched rows in the other table.

Left/right outer connection

  • Left (right) connection as the name suggests, that is, according to the location of the table to distinguish between the main table, that is, in the left connection that is, the left table, the right connection that is, the right side of the connection

Syntactic structure.

SELECT colum1,colum2... ,
FROM table1 [LEFT|RIGHT]
JOIN ON [JOIN CONDITION].

eg: query the name of the employee and the name of the department, there is no departmental information on the staff should also be queried out!

SELECT employee_name AS Employee Name,department_name AS Name of department
FROM employees e LEFT
JOIN departments d
ON e.department_id=d.department_id;
<Previous Next>