MySql collection Query

The query result of a SELECT statement is a set of tuples, so the results of multiple SELECT statements can be set. Set Operations mainly include UNION operations, INTERSECT operations, and variance t operations. Note: The number of columns in the query results of the collection operation must be the same, and the corresponding data type must be the same. The data tables in this example include student and SC.

The query result of a SELECT statement is a set of tuples, so the results of multiple SELECT statements can be set. Set Operations mainly include UNION operations, INTERSECT operations, and variance t operations. Note: The number of columns in the query results of the collection operation must be the same, and the corresponding data type must be the same. The data tables in this example include student and SC.

The query result of a SELECT statement is a set of tuples, so the results of multiple SELECT statements can be set.

Set Operations mainly include UNION operations, INTERSECT operations, and variance t operations.

Note: The number of columns in the query results of the collection operation must be the same, and the corresponding data type must be the same.

The data tables in this example include student, SC, and course. For details about the data tables, see EXISTS and NOT EXISTS in the Mysql database.

UNION example:

Example 1.1

Questions: Students of the Computer Science Department and students not older than 19 years old.

SQL statement:

SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19

Query results:

This query is actually a union of all students in the computer department and students not older than 19 years old.

The equivalent SQL statement is:

SELECT * FROM Student WHERE Sdept='CS' OR Sage<=19

Note: although the two SQL statements are equivalent, they are essentially different. The first SQL statement is to SELECT the results twice and then obtain the union of the results; the second SQL statement directly performs a SELECT statement query.

INTERSECT example:

The MySql statement is not currently INTERSECT, so it can only be used as an alternative statement

Example 2.1

Question: Query students not older than 19 in the computer science system.

The corresponding SQL statement should be:

SELECT * FROM Student WHERE Sdept='CS'INTERSECTSELECT * FROM StudentWHERE Sage<=19;

Alternative SQL statements:

SELECT *FROM studentWHERE Sdept = 'CS'AND Sage <=19

Query results:

Example 2.2

Question: Query refers to the students who take course 1 and course 2. (Query the intersection between the student set of Elective Course 1 and the student set of Elective Course 2)

The corresponding SQL statement should be:

SELECT Sno FROM SCWHERE  Cno='1'INTERSECTSELECT Sno FROM SCWHERE Cno='2';

The alternative SQL statement is:

(Use in)

SELECT SnoFROM SCWHERE Cno = '1'AND SnoIN (SELECT SnoFROM SCWHERE Cno = '2')

Or:

(Use EXISTS)

SELECT SnoFROM SC SCXWHERE Cno = '1'AND EXISTS (SELECT SnoFROM SC SCYWHERE Cno = '2'AND SCX.Sno = SCY.Sno)

The query result is:

Or:

(Use join on)

SELECT *FROM SC SCXJOIN SC SCY ON ( SCX.Cno = '1'AND SCY.Cno = '2'AND SCX.Sno = SCY.Sno )

EXCEPT t operation:

Unfortunately, MySql does not support the EXCEPT operation and can only use alternative statements.

Example 3.1

Query the difference between students of the Computer Science Department and students not older than 19 years old.

The corresponding SQL statement is:

SELECT * FROM Student WHERE Sdept='CS'EXCEPT SELECT * FROM Student WHERE Sage<=19;

That is, to query students older than 19 in the computer science system.

Replace the statement:

(It's easy to use WHERE directly, but it doesn't mean it's easy to understand)

SELECT *FROM StudentWHERE Sdept = 'CS'AND Sage >19

The query result is:

Or the replacement statement is:

(Use not in)

SELECT *FROM StudentWHERE Sdept = 'CS'AND Sno NOTIN (SELECT SnoFROM StudentWHERE Sage <=19)

The query result is:

Or use the replacement statement as follows:

(Use not exists)

SELECT *FROM Student SXWHERE Sdept = 'CS'AND NOTEXISTS (SELECT *FROM Student SYWHERE SY.Sage <=19AND SX.Sno = SY.Sno)

The query result is:

Sort the operation results of the Set

The order by clause can only be used to sort the final query results and cannot sort the intermediate results.

In any case, the order by clause can only appear at the end. When sorting the set operation results, the order by clause uses numbers to specify the sorting attribute.

The following is an incorrect syntax:

SELECT * FROM StudentWHERE Sdept='CS'ORDER BY SnoUNIONSELECT * FROM StudentWHERE Sage<=19ORDER BY Sno;

Which of the following statements is true:

SELECT * FROM StudentWHERE Sdept='CS'UNIONSELECT * FROM StudentWHERE Sage<=19ORDER BY 2;

Output result:

If it is written:

SELECT * FROM StudentWHERE Sdept='CS'UNIONSELECT * FROM StudentWHERE Sage<=19ORDER BY 1;

Output result: