Mysql_ Data Query _ connection query

Connection Query 1. Connection (join)

　　also called θ connection, from the Cartesian product of two relationships, select a tuple that satisfies a certain condition between attributes.

　　The connection operation of the equivalent connection: θ = "=" is called an equivalent connection. From the generalized Cartesian product of relation R and S, select the tuple with equal value of a and B property.

Natural connection: A special equivalent connection. The component that requires comparison in a relationship must be an attribute group of the same name , and the duplicate attributes are removed from the result.

Outer joins: The suspension tuple is also saved in the result relationship, and Null is added to the other attributes. OUTER JOIN

Left OUTER join: Leave only the suspension tuple of the left R relationship. Left OUTER JOIN

Right outer join: only the suspension tuple of the right s relationship is reserved. Right OUTER JOIN

2. Connection query:

Queries that design more than two tables at the same time are called connection queries. Is the most important query in the database, including equivalent connection query, natural connection query, non-equivalent connection query, self-connection query, outer join query and compound conditional connection query.

1. Equivalent and non-equivalent connection query:

SELECT student.*,sc.* from Student,sc WHERE student.sno=sc.   Sno; Equivalent connection

SELECT Student.sno,sname,ssex,sage,sdept,cno,grade Natural Connection

From STUDENT,SC WHERE student.sno=sc. Sno;

An SQL statement can complete both the selection and the join query, which is a compound condition consisting of a join predicate and a selection predicate.

SELECT Student.sno,sname,ssex,sage,sdept,cno,grade
From STUDENT,SC WHERE student.sno=sc. Sno and SC. Grade>90;

An optimization of the execution of the query, first selected from the SC cno= ' 2 ' and grade>90 of the tuple formed an intermediate relationship, and then the student to meet the connection conditions of the tuple to connect to get the final result relationship.

2. Self-Connection:

Check for a first class lesson

SELECT first. Cno,second. Cno from Course first,course SECOND WHERE first. Cpno=second. Cno;

Two aliases were taken for course Fist,second

3. External connection:

Saves the suspended tuple in the result set.

SELECT Student.sno,sname,ssex,sage,sdept,cno,grade

From Student to OUTER JOIN SC on (STUDENT.SNO=SC. Sno);

4. Multi-table connection:

More than one table is involved.

SELECT Student.sno,sname,cname,grade

From Student,sc,course WHERE STUDENT.SNO=SC. Sno and SC. Cno=course.cno;

Mysql_ Data Query _ connection query