Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL query sentence complex query example analysis, the article introduces in great detail, has a certain reference value, interested friends must read it!

MySQL is a relational database management system in which relational databases store data in different tables instead of all data in one large warehouse, which increases speed and flexibility. There are often many complex queries in MySQL, such as:

MySQL complex query

1. Group query:

1. Keyword: GROUP BY

Usage: GROUP BY statements are used to combine aggregate functions (such as SUM) to group result sets according to one or more columns, and aggregate functions often need to add GROUP BY statements.

The following two tables are given, one is emp and the other is dept. We operate on these two tables in the following query, as shown in the following figure:

First sheet: emp

Second sheet: dept table

Now let's query the sum of salaries of each department in emp with the following sentence:

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 we use sum ().

3 、 having:

Both where and having 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, to filter the data before grouping, the condition cannot contain aggregate functions, and the where condition is used to display specific rows.

The role of having is to filter groups that meet the criteria, that is, to filter data after grouping, conditions often contain aggregate functions, having conditions are used to display specific groups, or multiple grouping criteria can be used to group.

For example, we want to query the department numbers in the emp table where the total salary is greater than 10000. 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 department whose total salary is greater than 10000 is numbered 20 (the sum of wages is also shown for the sake of understanding).

2. Query with tables:

Query data from two or more tables based on the relationship between the columns in these tables.

1. Inner join (internal connection):

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

Note: inner join deletes all rows that do not match rows in other joined tables from the result, and can only query the information contained in the joined table, so the inner join may lose information, and inner can be omitted.

For example, we join emp and dept tables and query ename and deptno with the following statement:

SELECT emp.ename,dept.deptno FROM emp INNER JOIN dept ON emp.deptno=dept.deptno

There is another way to write it: SELECT emp.ename,dept.deptno from emp,dept where emp.deptno=dept.deptno

Note: there was a deptno of 40 in the dept table, but it was not found after the query. This is because there is no value of 40 in the deptno field in the emp, so the record with the value of 40 in the deptno field in the dept table is automatically deleted when using the innner join connection.

2. External connection:

2.1: left outer join: the result set retains all rows of the left table, but contains only the rows of the second table that match the first table. The corresponding blank row of the second table is placed in a null value.

2.2: right outer join: the result set retains all rows of the right table, but contains only the rows of the second table that match the first table. The corresponding blank row of the second table is placed in a null value.

The same effect can be achieved by exchanging the location of the two tables between the left outer join and the right outer join.

Now let's do a query for grouping and joining tables.

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 the sal (salary sum) of each department in emp. Here you need to use a grouping query, but you also need to query the department name (dname) of the corresponding department, because dname

It is in the dept table, so you should join emp and dept tables.

Idea 1: let's first query all the fields we need and then group them, so connect first and then group them. The sentence is as follows:

SELECT e.deptno. Dname. sum (e.sal) FROM emp e INNER JOIN dept d ON e.deptno=d.deptno GROUP BY d.deptno;) (notice here to make

Using the alias emp, the alias is eJournal dept, and the alias is d)

The second way of writing:

SELECT e.deptnored.dname FROM emp sum (e.sal) dept d WHEREe.deptno=d.deptno GROUP BY d.deptno

The results of both methods are the same, as follows:

Idea 2: we need to query the total salary of each department in emp, treat this result set as a table (called Table 1 here), and then let Table 1 join the dept table to query the corresponding department name (dname).

Step by step 1:SELECT deptno,SUM (sal) FROM emp GROUP BY deptno; this statement queries the sum of the salaries of each department in the emp table. Now let's talk to dept

Table connection:

Step by 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; in this way, the desired result is queried. Note that the xin here is an alias, and the result is as follows:

The code here looks very long, but in fact, the idea is very clear, that is, the first query result is regarded as a table to join another table, this way of thinking is not easy to make mistakes, so write it with more practice.

Very skillful.

3. Pagination:

Keyword: LIMIT

Syntax: select * from tableName conditional limit current page number * page capacity-1, page capacity

Limit is usually used with order by.

For example, we want to query 5-10 records in ascending order by department number in the emp table, showing 5 records per page, with the following statement:

SELECT * FROM emp ORDER BY deptno LIMIT 4 and 5

In this way, you can query the desired results, and note that the last parameter 5 is the page capacity, that is, the number of rows to be displayed on this page (that is, the number of records from the beginning of the page to the end of the page).

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

LIMIT 17 million 10-1 million 10

Four: IN

Keyword: In

The result of the return value of the subquery must have more than one condition and must use IN instead of "="

Note: LIMIT is put at the end.

The above is all the contents of the article "sample Analysis of complex queries in MySQL query sentences". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.