(i) the use of SQL associated queries (various joins)

---restore content starts---

(i) the use of SQL associated queries (various joins)

These days because of work, found that their SQL statement Foundation is not very good, deliberately researched, found that SQL statement is really profound, SQL statement is not only to find out the data you want, more attention to the efficiency of the query, because in the query of large amounts of data often because of the large amount of data, resulting in low efficiency, Coupled with the interaction of the front and back data, resulting in a series of problems such as access delay.

In our daily work often use a lot of query methods, such as nested query, correlation query, sub-query and so on, as far as I am concerned, I feel that relevance query is the most easy to learn, and the most efficient. The following are the associated queries that I summarize:

Associated query:

( 1 ) Inner Connection

The associated query is divided into inner joins (inner join or join) only return rows with equal join fields in two tables
SELECT * FROM table 1 inner JOIN table 2on table 1. Field number = Table 2. Field number
Example of a three-table connection connection:
SELECT * FROM (table 1 inner JOIN table 2on table 1. Field number = Table 2. Field number) INNER JOIN table 3on
Table 1. Field number = Table 3. Field number
Examples of internal connections four tables:
SELECT * FROM (table 1 inner JOIN table 2on table 1. Field number = Table 2. Field number) INNER JOIN table 3on
Table 1. Field number = Table 3. Field number) INNER JOIN table 4on table 1. Field number = Table 4. Field number
A similar query is best used with a numeric field, and its query field must be a primary key, which automatically grows type. Otherwise difficult to succeed, Inner joins can also add where words to narrow down the tedious

( 2 ) Left connection

Left join (left join) returns records that are equal to all records and join fields in the right table
If the fields in the two tables do not correspond exactly to one by one, you can use the left connection and the
Right connection query, one is to include the left all, one is to include all the right
Left join two-table example:
SELECT * FROM table 1 LEFT JOIN table 2on table. field number = Table 2. Field number;
Left join three-table Query example:
Left JOIN table 2 on table 1. Field number = Table 2. field number left JOIN table 3 on
Table 2. Field number = Table 3. Field number

( 3 ) Right Connection

The right join returns records for all records in the right table and the join fields in the left table, and the syntax is the same as left join, which is no longer an example. There is also full connectivity, which is the selection of records with no correlation between the left and right tables.
The self-connected left connection right connection can also be used in the same table, referred to as self-joins, where the usage is typically used for hierarchical data in tables, such as departmental tables, department IDs, department names, and parent department IDs. To query the department name and
All parent department names can be queried like this
Select Department Name, parent department name from Department table alias 1left join Department table alias 2
On alias 1. Department id= alias 2. Parent Department ID

( 4 ) Group Query

The use of GROUP by: his role is to group the corresponding fields:

Examples are as follows:

Create a new student score data sheet:

This is a t_grade table

(1) The total score of the student's overall achievement is queried

Sql:selectt.stuname,sum (T.score) as score from T_grade T GROUP by T.stuname

Result diagram:

(2) Query the student's highest achievement

Sql:selectt.stuname,max (T.score) as score from T_grade T GROUP by T.stuname
Result diagram:

There are often a lot of query relationships in the group queries, and here I don't give an example

Below is a list of the five tables associated with the SQL statements used to correlate queries in a work

Here are all the tables:

(1) Sam_domain

(2) Sam_appsystem

(3) Sam_service

(4) Sam_operation

(5) Nxgjj_user_operation

Query requirements: Query out how many users have access to the generated histogram under each system module:

Sql:

Selecttotal.app_name, COUNT (distinct Total.userid) Total_num from

(

Selectt.domain_code,a.app_code,a.app_name,s.service_code,o.operation_code,u.* from

(

Selectdomain.domain_code from Sam_domain Domain

where Domain.domain_code = ' com.hopesoftnxgjj '

) T

Innerjoin Sam_appsystem A on a.domain_code = T.domain_code

Left join Sam_service s on s.app_code = A.app_code

Left JOIN sam_operation o ono.sam_service_code = S.service_code

Left join nxgjj_user_operation u onu.dest_operation_code = o.operation_code) Total

GROUP BY Total.app_name

Result diagram:

：

The above data are test data;

---restore content ends---

(i) the use of SQL associated queries (various joins)

These days because of work, found that their SQL statement Foundation is not very good, deliberately researched, found that SQL statement is really profound, SQL statement is not only to find out the data you want, more attention to the efficiency of the query, because in the query of large amounts of data often because of the large amount of data, resulting in low efficiency, Coupled with the interaction of the front and back data, resulting in a series of problems such as access delay.

In our daily work often use a lot of query methods, such as nested query, correlation query, sub-query and so on, as far as I am concerned, I feel that relevance query is the most easy to learn, and the most efficient. The following are the associated queries that I summarize:

Associated query:

( 1 ) Inner Connection

The associated query is divided into inner joins (inner join or join) only return rows with equal join fields in two tables
SELECT * FROM table 1 inner JOIN table 2on table 1. Field number = Table 2. Field number
Example of a three-table connection connection:
SELECT * FROM (table 1 inner JOIN table 2on table 1. Field number = Table 2. Field number) INNER JOIN table 3on
Table 1. Field number = Table 3. Field number
Examples of internal connections four tables:
SELECT * FROM (table 1 inner JOIN table 2on table 1. Field number = Table 2. Field number) INNER JOIN table 3on
Table 1. Field number = Table 3. Field number) INNER JOIN table 4on table 1. Field number = Table 4. Field number
A similar query is best used with a numeric field, and its query field must be a primary key, which automatically grows type. Otherwise difficult to succeed, Inner joins can also add where words to narrow down the tedious

( 2 ) Left connection

Left join (left join) returns records that are equal to all records and join fields in the right table
If the fields in the two tables do not correspond exactly to one by one, you can use the left connection and the
Right connection query, one is to include the left all, one is to include all the right
Left join two-table example:
SELECT * FROM table 1 LEFT JOIN table 2on table. field number = Table 2. Field number;
Left join three-table Query example:
Left JOIN table 2 on table 1. Field number = Table 2. field number left JOIN table 3 on
Table 2. Field number = Table 3. Field number

( 3 ) Right Connection

The right join returns records for all records in the right table and the join fields in the left table, and the syntax is the same as left join, which is no longer an example. There is also full connectivity, which is the selection of records with no correlation between the left and right tables.
The self-connected left connection right connection can also be used in the same table, referred to as self-joins, where the usage is typically used for hierarchical data in tables, such as departmental tables, department IDs, department names, and parent department IDs. To query the department name and
All parent department names can be queried like this
Select Department Name, parent department name from Department table alias 1left join Department table alias 2
On alias 1. Department id= alias 2. Parent Department ID

( 4 ) Group Query

The use of GROUP by: his role is to group the corresponding fields:

Examples are as follows:

Create a new student score data sheet:

This is a t_grade table

(1) The total score of the student's overall achievement is queried

Sql:selectt.stuname,sum (T.score) as score from T_grade T GROUP by T.stuname

Result diagram:

(2) Query the student's highest achievement

Sql:selectt.stuname,max (T.score) as score from T_grade T GROUP by T.stuname
Result diagram:

There are often a lot of query relationships in the group queries, and here I don't give an example

Below is a list of the five tables associated with the SQL statements used to correlate queries in a work

Here are all the tables:

(1) Sam_domain

(2) Sam_appsystem

(3) Sam_service

(4) Sam_operation

(5) Nxgjj_user_operation

Query requirements: Query out how many users have access to the generated histogram under each system module:

Sql:

Selecttotal.app_name, COUNT (distinct Total.userid) Total_num from

(

Selectt.domain_code,a.app_code,a.app_name,s.service_code,o.operation_code,u.* from

(

Selectdomain.domain_code from Sam_domain Domain

where Domain.domain_code = ' com.hopesoftnxgjj '

) T

Innerjoin Sam_appsystem A on a.domain_code = T.domain_code

Left join Sam_service s on s.app_code = A.app_code

Left JOIN sam_operation o ono.sam_service_code = S.service_code

Left join nxgjj_user_operation u onu.dest_operation_code = o.operation_code) Total

GROUP BY Total.app_name

Result diagram:

：

The above data are test data;

(i) the use of SQL associated queries (various joins)