MySQl 4-4 -- database query 4 -- subquery

1. in a subquery condition, you can use the results of another query as part of the condition. For example, you can determine whether the column value is equal to the value in a query result set, A query that is part of a query condition is called a subquery. SQL standards allow multi-layer nesting of SELECT statements to represent complex queries. In addition to SELECT statements, subqueries can also be used in INSERT, UPDATE, and DELETE statements. Subqueries are usually used IN combination with IN and EXIST predicates and comparison operators. 2. IN subquery is used to determine whether a given value is IN the subquery result set. The format is expression [NOT] IN (subquery) www.2cto.com, where subquery is a subquery. If expression is equal to a value IN the subquery result table, TRUE is returned for the IN predicate. Otherwise, FALSE is returned. If NOT is used, the returned value is the opposite. For example, find the name and student ID of the student who has selected the course number 206 in the XSCJ database. SELECT name, student id from xs where student id in (SELECT student id from XS_KC WHERE course number = '000000'); Note: When executing a SELECT statement containing a subquery, the system first executes the subquery, generates a result table, and then executes the query. In this example, run the subquery: SELECT student id from XS_KC WHERE course number = '000000'. Then, a table containing only the student ID column is obtained, in XS_KC, each row with 206 course names has one row in the result table. If the student ID column value of a row in the XS table is equal to any value in the subquery result table, the row is selected. Note: Only one column of data can be returned IN subqueries. For more complex queries, you can use nested subqueries. For example, you can find the name, student ID, and professional name of a student who has not selected discrete mathematics. SELECT name, student ID, Major name from xs where student id not in (SELECT student id from XS_KC WHERE course No. IN (SELECT course No. from kc where Course name = 'discrete mat ')); www.2cto.com 3. the subquery can be considered as an extension of the IN subquery. It allows the expression value to be compared with the subquery result IN the following format: expression {<| <= | >|>= |! ==|<>}{ ALL | SOME | ANY} (subquery) Where expression is the expression to be compared, and subquery is a subquery. ALL, SOME, and ANY indicate the limitations on comparison operations. If the result set of a subquery returns only one row of data, you can use the comparison operator to directly compare the data. ALL indicates that the expression must be compared with each value in the subquery result set. TRUE is returned only when the expression and each value meet the comparison relationship. Otherwise, FALSE is returned; SOME or ANY is a synonym, indicating that if the expression matches a value in the subquery result set, TRUE is returned. Otherwise, FALSE is returned. For example, find the student ID, name, Major name, and date of birth for students older than all computer systems in the XS table. SELECT student ID, name, Major name, date of birth from xs where Date of Birth <ALL (SELECT Date of Birth fromxs where professional name = 'compute'); 4. EXISTS subquery EXISTS predicates are used to test whether the results of the subquery are empty tables. If the result set of the subquery is not empty, EXISTS returns TRUE; otherwise, FALSE. EXISTS can also be used in combination with NOT, that is, NOTEXISTS. Its return value is exactly the opposite of EXIST. The format is [NOT] EXISTS (subquery). For example, find the Student name of the course No. 206. SELECT name from xs where exists (SELECT * FROM XS_KC WHERE student ID = XS. student id and course number = '000000'); www.2cto.com this example is different from the previous subquery example: in the previous example, the inner query is only processed once AND a result set is obtained, the outer query is processed in sequence. In this example, the inner query must be processed multiple times, because the inner query and XS. student ID. Different rows of the XS table in the outer query have different student ID values. This type of subquery is called a related subquery, because the condition of the subquery depends on some values in the outer query. The processing process is: first, find the first row of the XS table in the outer query, and process the inner query based on the row's student ID column value. If the result is not blank, the WHERE condition is true, take out the name value of the row as a row in the result set, and then find the 2nd, 3 ,... Repeat the preceding process until all rows in the XS table are searched. Note: The result is blank because no course is selected. 5. mySQL distinguishes between four types of subqueries: A subquery that returns a table is a table subquery, and a subquery that returns a row with one or more values is a row subquery; one or more rows are returned, but only one value on each row is a column subquery; only one value is returned for a scalar query. In terms of definition, each scalar quantum query is a column subquery and a row subquery. All the subqueries described above belong to column subqueries. In addition, subqueries can be used in other clauses of SELECT statements. Table subqueries can be used in the FROM clause, but an alias must be defined for the intermediate table generated by the subquery. 6. subqueries can also be defined after the SELECT keyword. For example, you can find the names and student IDs of all female students in the XS table and the age difference between them and students 081101. SELECT student ID, name, YEAR (date of birth)-YEAR (SELECT Date of birth from xs where student ID = '000000') AS age gap from xs where gender = '0 '; note: In this example, only one value is returned from the subquery, so this is a scalar query. The YEAR function is used to retrieve the YEAR of the DATE type. In the WHERE clause, you can also compare a row of data with the results in the row subquery using comparison operators. For example, find the student ID and name that are the same as the Gender and total credits of the student No. 081101. SELECT student ID, name from xs where (gender, total credits) = (SELECT gender, total credits from xs where student ID = '000000'); 7. union can be used to combine the results of many SELECT statements into a result set. Syntax format: SELECT... UNION [ALL | DISTINCT] SELECT... [UNION [ALL | DISTINCT] SELECT...] note: The SELECT statement www.2cto.com is a regular selection statement, but it must comply with the following rules: ● the selected columns at the corresponding position of each SELECT statement should have the same number and type. For example, the first column selected by the first statement should be of the same type as the first column selected by other statements. ● Only the last SELECT statement can use into outfile. ● HIGH_PRIORITY cannot be used with SELECT statements that are part of the UNION operation. ● The order by and LIMIT clauses can only be specified at the end of the entire statement, and parentheses should be added for a single SELECT statement. Sorting and limiting the number of rows takes effect for the entire final result. 8. When UNION is used, the column name used in the first SELECT statement is used for the result. MySQL automatically removes duplicate rows from the final result, so the additional DISTINCT is redundant, but it is allowed in syntax according to SQL standards. To obtain ALL matched rows, you can specify the keyword ALL. For example, query the information of two students whose student ID is 081101 and whose student ID is 081210. SELECT student ID, name, Major name, gender, date of birth, total credits from xs where student ID = '000000' union select student ID, name, Major name, gender, date of birth, total credits from xs where student ID = '000000'; 9. the select statement of the handler statement is usually used to return a set of rows. MySQL also supports another database query statement: the handler statement, which can browse the data in the table in one row, it is also an SQL standard. This is a special MySQL statement. This statement is only applicable to MyISAM and InnoDB tables. To use this statement, you must first use the handler open statement to open a table, then use the handler read Statement to browse the rows of the opened table, and then use the handler close statement to close opened table 1 ). you can use the handler open statement to open a table. Syntax format: HANDLER table name OPEN [AS new table name] Description: if an alias is used when a table is opened, aliases are also used for other statements for further table access. 2) The handler read Statement in the browser table is used to browse the data rows of an opened table. Syntax format: HANDLER table name READ {FIRST | NEXT} [WHERE where_condition] [LIMIT...] note: www.2cto.com ● FIRST | NEXT: The two keywords are the read Statement of the HANDLER statement. "FIRST" indicates reading the FIRST line and "NEXT" indicates reading the NEXT line. ● WHERE clause: If you want to return rows that meet specific conditions, you can add a WHERE clause. The WHERE clause has the same function as the WHERE clause in the SELECT statement, however, the WHERE clause cannot contain subqueries, built-in functions, BETWEEN, LIKE, And IN operators. ● LIMIT clause: if the LIMIT clause is not used, the HANDLER statement takes only one row of data in the table. To read multiple rows of data, add the LIMIT clause. Here, the LIMIT clause is different from the LIMIT clause in the SELECT statement. The LIMIT clause in the SLECT statement is used to LIMIT the total number of rows in the result. The LIMIT clause here is used to specify the number of rows that can be obtained by the HANDLER statement. 10. Since there are no other statements, the order of rows when reading a row of data is determined by MySQL. To display data in a certain order, you can specify the index in the handler read Statement. Syntax format: (1) HANDLER table name READ index name {=|>=|<=|<}( value1, value2 ,...) [WHERE condition] [LIMIT...] (2) HANDLER table name READ index name {FIRST | NEXT | PREV | LAST} [WHERE condition] [LIMIT...] note: The first method is to use the comparison operator to specify a value for the index and read the table from a row of data that meets the condition. If it is a multi-column index, multiple values are combined and separated by commas. Value1 and value2 are the values specified for the index. The second method is to use keywords to read rows. FIRST indicates the FIRST row, NEXT indicates the NEXT row, prev indicates the last row, and last indicates the last row. 11. After reading the opened table rows, you must use the handler close statement to close the table. Syntax format: HANDLER table name close www.2cto.com example: one row browses the content that meets the requirements in the KC table, requiring the first row of data with a credit greater than 4. First open the table: USEXSCJ; (specify the database and open the xskj database) handler KC open; (open the kc table) read the first row meeting the conditions: handler KC readFIRST WHERE credit> 4; read the NEXT row: handler KC read NEXT; close the table: handler KC close; Author: tianyazaiheruan