MySQL_05-Select query details, mysql_05-select

MySQL_05-Select query details, mysql_05-select

SQL query details

1. Orderby

A) Order by field asc | desc

B) Allow sorting of multiple fields: first sort by the first field. If not, then sort by the second field. And so on.

2. Limit (Limit the number of records obtained)

A) Syntax: limit offset, row_count (offset: offset, subscript starts from 0; row_count: Total number of records ). For example, limit 2 and 3 indicates that three records (2nd to 4th records) are taken from the second record)

3. Distinct

A) Remove duplicate records.

B) Syntax: select distinct field from .......

4. Union

A) combine the queries in two different relationships and return

B) Example: query the instructors in the first and second classes.

(Select teacher from t_class where class_id = '1 ')

Union (all)

(Select teacher from t_class where class_id = "2 ")

C) Note: if the union result contains duplicate data, the duplicate data will be automatically eliminated and the query result will lose data! Solution: the union option all is used. Union all...

D) Note: The sorting of the results of the neutron statements in the union operation

I. Wrap the sub-statement in parentheses. It is not necessary, but it is easy to read and the logic is clearer!

Ii. The substatement order by takes effect only when it is used with limit! Sorting does not work when there is no limit!

E) sort the final result of union (add order by... at the end of the statement)

(Select teacher from t_class where class_id = '1 ')

Union (all)

(Select teacher from t_class where class_id = "2 ")

Order by stu_number desc;

 

5. subquery (*)

A) The subquery statement is enclosed in parentheses.

B) subquery category

I. Position of the subquery: where (after where), from, and exists

Ii. subquery return value format: single value (scalar), column, table

C) subquery example

I. Find out from the instructor table the name of the teacher with the most teaching days: Select t_name from t_teacher where teach_days = (select max (teach_days) from t_teacher );

Note: The following statements may also work.

Select t_name from t_teacher order by teach_days desc limit 1;

However, problems may occur. If the number of instructors has the largest and the same number of teaching days, the results will be missed!

Ii. subquery return set: in, not in, any, all

Iii. If the result returned by the subquery is a row (the row subquery is generally not used much, but you need to know !!!) : Use (filed1, field2,...) to construct a row and compare it with the results returned by the subquery !!!

For example, the instructor table must be used to query the information of other instructors who share the same gender with John.

Select t_name, gender, age, course_name from t_teacher

Where (gender, course_name) = // compare the result of building a row in the subquery (the most used is = and in)

(Select distinct gender, course_name from t_teacher where t_name = 'zhang san' and course_name = 'mysql database Guide ');

Iv. The subquery returns a table (usually used for from subqueries, that is, the results returned by the subquery are used after the from keyword ). Process a complex logic step by step.

V. Exists subquery: exists (subquery), which does not provide data. It is equivalent to a Boolean expression and determines whether data is obtained.

 

 

6. Connection query (*)

A) Inner: Internal data connections require that all data involved in the connection exists before the connection can be executed.

I. Inner join

Ii. Cross join (Cartesian link): No join condition. The size of the result set is equal to the Cartesian product of the result set of the two tables involved in the join.

B) Outer: one or more connections that participate in the connection do not actually exist.

I. Left [outer] join Left outer join (most used in development !!!) : When the left Outer Join occurs, if the data in the left table cannot be connected to the right table, the data in the left table is eventually retained in the result, and the corresponding field in the right table is filled with NULL; if the data in the right table cannot be connected to the data in the left table, the data on the right is discarded.

Ii. Right [outer] join Right outer join

Iii. Full join (not supported currently)

C) Natural natural join (natural join, natural left join, Natural right join): the connection process is completed through mysql's own judgment, and no connection conditions need to be specified. Mysql automatically uses the same fields in multiple tables as the connection conditions.

Select * from t_one natural join t_two;

D) connection conditions

I. On: Connection Condition

Ii. Where: Filter connected data.

Iii. Using: Connection condition. using is used only when the fields connected to the two tables are the same. In general, Using is rarely used, and on is usually used. For example, there are two tables: t_student_main_info (common attributes of students) and t_student_other_info (not commonly used by students). They have the same primary key name and are called ids. In this case, the using keyword can be used. Select * from t_student_main_info join t_student_other_info using (id );

 

Note: The connection Operation Supports multi-Table connections and can be connected without limit, but is rarely used. For example:

Select t_two. *, t_three. * from t_one left join t_two on... Left join t_three on... where...

 

Two examples:

1. Assume that the two tables are team and match

Id	Team_name
2	Php0331
5	Php0228
1	Php0115
7	Php0505

 

Id	Host_id	Guest_id	Time	Result
1	2	1	10: 00	34: 28
2	2	7	15: 00	35: 45
3
4

 

The query result is as follows:

Main Team	Team	Competition time	Competition Results
Php0331	Php0115	10: 00	34: 28
Php0331	Php0505	15: 00	35: 45

 

Because there are two team_name records in each row in the result, you need to connect the team table twice !!!

Match left join team on match. host_id = team. id left join team on match. guest_id = team. id

 

2. Self-connection

There are the following curriculum course

Cno	Cname	Cpno	Credit
1	Database	5	4
2	Mathematics		2
3	Information System	1	4
4	Operating System	6	3
5	Data Structure	7	4
6	Data Processing		2
7	C Language	6	4

Indirect preemptive queries for each course are required:

Select first. cno, second. cpno from course first, course second where first. cpno = second. cno;