Multi table join query
Join queries are divided into two categories. One is to use the join predicate to join, and the other is to use the keyword join
Join predicate:
Format: select < output field list > from table 1, table 2 Where < Table 1 Field name 1 > < join predicate > < Table 2 Field Name2 >
Connection field: must be comparable
Connection predicates include: =, <, < =, >, > =,! = < >, etc. when the comparator is =, it is called equivalent connection. When the join predicate is an unequal operator, it is called unequal join
[[example] query students' basic information and score information. SELECT a.* , b.* FROM student a, score b WHERE a.id=b.student_id
Natural connection:
Natural join is a special equivalent connection. It requires that the components to be compared in the two relationships must be the same attribute group, and the duplicate attribute column of {must be removed from the result
For multi table query, if the referenced column is shared by the queried table, the table name must be used to specify the table from which the column is referenced. The specified method is: table name List. Conversely, the table name is not necessarily required to specify the source
[[example] query students' basic information and score information. Equivalent connection: SELECT * FROM student a, score b WHERE a.student_id = b.student_id Natural connection: select * from tab_student a natural join tab_score b;
Composite conditional connection:
When a multi table join query contains multiple join conditions, it is called compound conditional join
[[example] query the student's student number, name, course name and score information.
SELECT a.student_id,student_name,course_name,grade
FROM student a, score b, course c
WHERE a.student_id = b.student_id
AND b.course_id = c.course_id
Self connection:
A table that joins itself is called a self join
Self join can be used to find rows with the same field values in the same table. When using self join, you need to specify two aliases for the table, and use aliases to specify the source of all referenced columns
[Example] find the student number, course number and grade of students with the same course score.
SELECT a.student_id,b.student_id,a.course_id,a.grade
FROM score a, score b
WHERE a.grade=b.grade
AND a.student_id<>b.student_id
AND a.course_id=b.course_id
Keyword join connection:
The JOIN keyword is used to connect tables, which enhances the connection ability and flexibility of tables
Format:
Select < output field list > from table name1 < connection type > table Name2 on < connection condition > [< connection type > table name3 on < connection condition >]
Parameter Description:
1. Table name 1, table name 2 and table name 3 are used to indicate the tables to be connected
2. on: used to indicate connection conditions
3. Connection types: inner join
External connection: outer} join
External connections are divided into:
left outer join
right ， outer ， join
full # outer # join
Internal connection:
Equivalent connection, unequal connection and natural connection belong to inner connection. Join keyword to realize inner join -- merge two tables according to the join conditions specified by on, and return the rows that meet the conditions
[[example] query students' basic information and score information
SELECT a.* , b.*
FROM student a INNER JOIN score b
ON a.student_id = b.student_id
In the inner connection, the inner can be omitted. After using the inner connection, you can still use the where clause to filter the connected records
[[example] query the basic information and score information of students with student number 0801101.
SELECT a.student_id, student_name, class_id,
sex, born_date, address, tel, resume,
course_id, grade
FROM student a INNER JOIN score a
ON a.student_id=b.student_id
WHERE a.student_id= '0801101'
External connection:
The returned by the external join to the query result set contains not only the rows that meet the join conditions, but also all data rows in the left table (when the left external join), the right table (when the right external join) or two edge join tables (all external join).
Left outer connection:
The query output result displays the qualified data rows and the unqualified data rows in the data table on the left.
Format: from ， left table name ， left [outer] join ， right table name ， on ， join condition
[Example] find the student number, name, course number and grade of all students. The information of students without course grade should also be displayed.
SELECT a.student_id, student_name, course_id, grade
FROM student a LEFT OUTER JOIN score b
ON a.student_id=b.student_id
Oracle Abbreviation:
SELECT a.student_id, student_name, course_id, grade
FROM student a,score b
where a.student_id=b.student_id(+)
Right outer connection:
The query output result displays the qualified data and the unqualified data rows in the data table on the right.
Format: from ， left table name right [outer] join ， right table name ， on ， join condition
[Example] query the student's student number, class number and class name. Records that are not successfully connected in the class table should also be displayed.
SELECT a.student_id, class.class_id, class_name
FROM student a RIGHT OUTER JOIN class b
ON a.class_id=b.class_id
Oracle Abbreviation:
SELECT a.student_id, class.class_id, class_name
FROM student a ,class b where
a.class_id(+)=b.class_id
All external connections:
The query output result displays the qualified data rows, and the unqualified data rows in the data table on the right and left.
Format: from ， left table name ， full [outer] join ， right table name ， on ， join condition
[For example] query the student number, course name and grade of the student, and the records that are not successfully connected in the two tables should be displayed (all external connections are adopted)).
SELECT student_id, course_name, grade
FROM score a FULL OUTER JOIN course b
ON a.course_id=b.course_id
Cross connect:
Cross join is also called unrestricted join, which combines two tables without any constraints. Mathematically, it's the Cartesian product of two tables. The number of rows of the result set obtained after cross connection is the product of the number of rows of the two connected tables
[Example] query the basic information of students and their grades (using cross connection). SELECT * FROM student CROSS JOIN score SELECT * FROM student , score
Nested subquery:
A subquery is a select query nested in the where or having clause of a select, nsert, update, or delete statement, or nested in other subqueries
Subqueries can be nested, which can decompose complex price comparison queries into several simple queries. A select -- from -- where statement is called a query block. A query in which a query block is nested in another where clause or a condition in a where having phrase is called a nested query
External query: SELECT class_name
FROM class
WHERE class_id IN
Internal query (SELECT class_id)
(subquery) FROM student
WHERE student_name = "Zhang Xiaoyun"
)
Execution order of nested queries:
First, execute the lowest internal query (i.e. sub query). Its query results are not displayed, but passed to the outer query. The query conditions used for external query are executed in the order from inside to outside.
Sub queries can be used:
1. In a set query using {in} or {not in}
2. In update, delete and insert statements
3. When using comparison operators
4. When using any or all +
5. When using the existence test introduced by exists or not exists
6. Where there are expressions
Subquery with predicate {in} or {not in}:
In sub query takes the results of the sub query as the conditions of the external query to judge whether a value in the external query belongs to the result set of the sub query
Format: < expression > [not] in (subquery)
[[example] find the student number, name and class number of students who have taken course 1002.
SELECT student_id, student_name, class_id
FROM student
WHERE student_id IN
(SELECT student_id
FROM score
WHERE course_id='1002')
Subquery with comparison operator:
When the subquery returns a single value, the subquery can be introduced by a comparison operator (=, <, < =, >, > =,! = or < >). When the subquery may return mu lt iple values, the comparison operator is used in combination with "all" and "any"
Format: expression {comparison operator} {all} or {any} (subquery)
All: the expression should be compared with all the values in the result set of the sub query. Only when the expression meets the comparison relationship with each value will it return true; otherwise, it will return false
Any: as long as the expression satisfies the comparison relationship with any value in the result set of the subquery, it returns true; otherwise, it returns false
[[example] query the student number, course number and score of students who have taken course 1001 and whose score is higher than that of student 0801103 in course 1001.
SELECT student_id, course_id, grade
FROM score
WHERE course_id='1001'
AND grade > (SELECT grade
FROM score
WHERE student_id='0801103'
AND course_id='1001')
Subqueries in insert, delete, and update statements:
Subqueries can be nested in insert, delete and update statements to insert the results of subqueries into new tables or set conditions for deleting and modifying records
INSERT operation: the combination of INSERT and SELECT statements can INSERT batch records into the specified table
Format:
Insert [into] < table name > [(< field 1 > [, < field 2 >...]]
[Example] create a new student table st_info，Requirements: include three fields: student number, name and remarks, and then student The corresponding field values in the table are inserted into the table st_info Middle, last displayed st_info Records in the table. CREATE TABLE st_info ( Student number char(10) PRIMARY KEY, full name char(8), remarks char(30) ) -- INSERT INTO st_info(Student number,full name,remarks) SELECT student_id, student_name,resume FROM student -- SELECT * FROM st_info
SELECT [(< field a > [, < field b >...]]
FROM < table name >
[where < conditional expression >]
Modification:
Subquery is nested with update. Subquery is used to specify modification conditions
[[example] increase the grade of 1001 course of class 08011 by 5 points.
UPDATE score
SET grade=grade+5
WHERE student_id in
(SELECT student_id
FROM student
WHERE class_id='08011')
Delete operation:
Subquery is nested with delete. Subquery is used to specify deletion conditions
[Example] delete the student record of No. 1001 course.
DELETE student
WHERE student_id not in
(SELECT student_id
FROM score
WHERE
course_id='1001' )
Related sub query:
1. Sub queries that can reference any table in an external query are called related sub queries
2. When executing related sub queries, the data of external queries should be used. The external query first selects data to provide to the sub query, then the sub query compares the price of the data, and then returns the query results to its external query after execution
3. Relational operators AND logical operators (EXISTS, AND, home, ANY, ALL) are usually used for related sub queries
4. Exists indicates existence. The subquery with the ， exists ， quantifier does not return any actual data. It only produces the logical TRUE value or the ， logical FALSE value. If the subquery result is not empty, the outer WHERE clause returns TRUE, otherwise it returns FALSE
5. EXISTS can also be used in combination with NOT, that is, NOT EXISTS. Its return value is just opposite to EXISTS
Format: [not] exist (subquery)