Fourth MySQL advanced query (ii)

Fourth MySQL advanced query (ii) I. EXISTS sub-query

Before executing the Create or DROP statement, you can use the EXISTS statement to determine whether the database pair is present and the return value is true or false. In addition, exists can also be used as a subquery for where statements, with the following syntax:

SELECT ... from table name WHERE EXISTS (sub- query);

The argument after the EXISTS keyword is an arbitrary subquery, and if the subquery does not return a row, the result of the exists subquery is true, at which point the outer query statement is executed. If the exists subquery result is false, the outer statement no longer executes the query.

Example: Check the logic Java course most recent exam, if the score has more than 80, then show scores ranked in the top 5 students and scores.

SELECT ' Studentno ' as study number, ' Studentresult ' result from ' result '

WHERE EXISTS (

SELECT * from ' result ' WHERE ' subjectno ' = (

SELECT ' Subjectno ' from ' Subject ' WHERE ' subjectname ' = ' Logic Java '

) and ' examdate ' = (

SELECT MAX (' examdate ') from ' result ' WHERE ' subjectno ' = (

SELECT ' Subjectno ' from ' subject '

WHERE ' subjectname ' = ' Logic Java ')

) and ' Studentresult ' >80)

and ' subjectno ' = (SELECT ' Subjectno ' from ' subject '

WHERE ' subjectname ' = ' Logic Java ')

ORDER by ' Studentresult ' DESC LIMIT 5;

Two. NOT EXISTS subquery

EXISTS and in, while allowing the addition of the NOT keyword to implement a reverse operation, not EXISTS means that there is no

Example: Check logic Java course a recent exam, if all failed to pass the exam (60 pass line), the examination is considered difficult, calculate the average score of +5.

Analysis: Everyone failed to pass the exam, "there is no student score greater than 60", you can use not exists detection.

SELECT AVG (studentresult) +5 as average from Resulf

WHERE not EXISTS (

SELECT * from ' result ' WHERE ' subjectno ' = (

SELECT ' Subjectno ' from ' Subject ' WHERE ' subjectname ' = ' Logic Java '

) and ' examdate ' = (

SELECT MAX (' examdate ') from ' result ' WHERE ' subjectno ' = (

SELECT ' Subjectno ' from ' subject '

WHERE ' subjectname ' = ' Logic Java ')

) and ' Studentresult ' >60)

and ' subjectno ' = (SELECT ' Subjectno ' from ' subject '

WHERE ' subjectname ' = ' Logic Java ')

and ' examdate ' = (

SELECT MAX (' examdate ') from ' result ' WHERE ' subjectno ' = (

SELECT ' Subjectno ' from ' subject '

WHERE ' subjectname ' = ' Logic Java ')

)

Three. Considerations for sub-queries

1. 1. A subquery statement can be nested where any expression in an SQL statement appears.

Syntax: SELECT * FROM ( subquery) as the alias of the table;

A) if the subquery result is a single row, you can not specify the alias for the column.

B) if the subquery result is multiple rows and columns, you must specify an alias for the table.

1. 2. A table that appears only in a subquery and does not appear in the parent query cannot be included in the output column.

Common error: SELECT * FROM (SELECT * from ' table name ') ; Error

The error of this subquery is that the FROM clause of the main query statement is a subquery that returns data that is larger than a single column, and should be assigned an alias for the subquery result set. The correct code is as follows:

Syntax: SELECT * FROM (SELECT * from ' table name ') as TEMP (alias);

Fourth MySQL advanced query (ii)