MySQL query statement complex query

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:

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.

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);

#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 .

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