MySQL query statement complex query

MySQL is a relational database management system. A relational database stores data in different tables instead of placing all data in one large warehouse, which increases speed and flexibility. There are often many complex queries in MySQL. In order to save everyone's time, the editor has summarized some commonly used complex queries.

MySQL complex query

1. Group query:

1. Keyword: GROUP BY

2. Usage: GROUP The BY statement is used in combination with the total function (such as SUM) to group the result set according to one or more columns. The total function often needs to be added GROUP BY statement.

The following are two tables, one is emp, the other is dept, we will operate the following queries on these two tables, as shown below:

First one: empTable

#Second picture: deptTable

##Now we query the total salary of each department of emp, the statement is as follows: ##SELECT deptno,SUM(sal)FROM emp GROUP BY deptno;

The results are as follows:

##Note: Here we query the total salary (sal) of each department, so it should be grouped according to the department number (deptno), so sum() is used for the sum;

##3. having:

where and having both make conditional judgments. Before introducing having, let’s take a look at the difference between where and having.

The function of where

is to remove the rows that do not meet the where condition before grouping the query results, that is, filter the data before grouping. The condition cannot contain the aggregate function , so use the where condition. Show specific rows.

having is used to filter the groups that meet the conditions, that is, filter the data after grouping. The conditions often include aggregate functions, use having Conditions display specific groups, or you can group using multiple grouping criteria.

For example: We want to query the department numbers whose total salary is greater than 10,000 in the emp table. The statement is as follows:

SELECT deptno,SUM(sal)FROM emp GROUP BY deptno HAVING SUM(sal)>10000;

##The results are as follows:

#In this way, it is found that the total salary is greater than The department number of 10,000 is 20, (the total salary is also displayed for understanding).

2. Join table query:

According to two The relationship between the columns in one or more tables, query data from these tables.

1, inner join(inner join(inner join(inner join) Connection):

Syntax: select field name 1, field name 2 from table1 [INNER] join table2 ON table1.Field name=table2.Field name;

##Note: Inner joins are deleted from the results All rows that do not have matching rows in other connected tables can only be queried for information that is owned by the connected tables, so the inner join may lose information, and inner can be omitted.

For example: we connect the two tables emp and dept, query ename and deptno, the statement is as follows: SELECT emp.ename,dept.deptno FROM emp INNER JOIN dept ON emp.deptno=dept.deptno;

Also A way to write: SELECT emp.ename,dept.deptno from emp,dept where emp.deptno=dept.deptno;

#Note: It turns out that there is a deptno of 40 in the dept table, but it is not there after the query. This is because there is no value of 40 in the deptno field in emp, so the dept table is automatically deleted when using innner join. Records with a deptno field value of 40.

2. Outer join:

2.1: Left outer join:

The result set retains all rows from the left table, but only contains rows from the second table that match the first table. The corresponding empty rows of the second table are put into NULL values.

2.2: Right outer join:The result set retains all rows from the right table, but only contains rows from the second table that match the first table. The corresponding empty rows of the second table are put into NULL values.

The left outer join and the right outer join can achieve the same effect by exchanging the positions of the two tables.

##Now we perform a query that uses grouping and join tables together

For example: We want to query the total salary of each department in emp and correspond to the department name in the dept table

Parse this sentence: The query field is sal (total salary) of each department in emp. Group query is used here, but the department name (dname) of the corresponding department is also queried. Since dname

is in the dept table, so we should connect the emp and dept tables.

Idea 1: We first query all the fields we need and then proceed Grouping, so connect first and then group, the statement is as follows:

SELECT e.deptno,d.dname,SUM(e.sal) FROM emp e INNER JOIN dept d ON e.deptno= d.deptno GROUP BY d.deptno;) (Note that the alias

is used here. The alias of emp is e, and the alias of dept is d)

Second way of writing:

SELECT e.deptno,d.dname,SUM(e.sal) FROM emp e ,dept d WHEREe.deptno=d.deptno GROUP BY d.deptno;

The results of these two writing methods are the same, as follows:

# Idea 2: We want to query the total salary of each department of emp, treat this result set as a table (here called table 1), and then let table 1 connect to the dept table Query the corresponding department name (dname);

Step 1: SELECT deptno,SUM(sal) FROM emp GROUP BY deptno; This statement queries the emp table The total salary of each department in , now we connect it with the dept

table:

Step 2: SELECT xin.*,d .dname FROM(SELECT deptno,SUM(sal) FROM emp GROUP BY deptno) xin INNER JOIN dept d ON xin.deptno

=d.deptno;This way you can query the desired The result, note that xin here is an alias, the result is as follows:

#The code here looks very long, but in fact the idea is very clear. Just treat the first query result as one table to connect to another table. This idea is not easy to make mistakes. If you practice writing more, you will become very skilled .

3. Paging:

Keywords: LIMIT

Syntax: select * from tableName condition limit current page number * page capacity -1, Page capacity;

Generally limit is used together with order by

For example, we want to query 5-10 in ascending order of department numbers in the emp table Records, each page displays 5 records, the statement is as follows:

SELECT *FROM emp ORDER BY deptno LIMIT 4,5;

In this way, you can query the desired results. Note that the last parameter 5 is the page capacity, which is the number of rows to be displayed on this page (that is, the record strip from the beginning to the end of this pagenumber).

For example, if we want to query 17 pages of records, each page will display 10 records:

LIMIT 17*10-1,10;

四:IN

##Keyword:In

If the return value of a subquery is more than one condition, IN must be used. "=";

## is not allowed. Note: LIMIT is always placed at the end .

Related recommendations:

How to solve the missing my.ini file in MySQL5.7

Summary of execution issues between mysql max and where

Detailed overview of MySQL statistics

The above is the detailed content of MySQL query statement complex query. For more information, please follow other related articles on the PHP Chinese website!