<> This article continues the previous query 【2】, The tables are still the three tables . This article mainly summarizes group query ( Continued ), join query . Still show first SQL sentence , How to show the test results again .

<> one ,GROUP BY clause ( Continued )

where Clause and having The difference of phrases lies in the object of action ,where Clause scope basic table or view , Select the tuple that meets the condition .having Phrases act on groups , Select the group that meets the criteria .

example 1: Query average score is greater than or equal to 86 Student number and average score

<> Wrong query statement :
select Sno,avg(Grade) from SC where avg(Grade)>=86 group by Sno;
The contents of the error report are as follows , The reason is that where The aggregate function cannot be used as a conditional expression in clause

<> Correct query statement :
select Sno,avg(Grade) from SC group by Sno having avg(Grade)>=86;

<> two , join query

Join query is the most important query in relational database , Including equivalent connection query , Natural join query , Non equivalent join query , Self connection query , Outer join query and compound conditional join query .

<>1. Equivalent and non equivalent join queries

Connection query where The conditions used to join two tables in a join clause are called join conditions or join predicates
. among , When the connected operator is = Time of day , It is called equivalent connection . Using other operators is called non equivalent join . Column names in join predicates are called join fields . Each connection field type in a connection condition must be comparable , But the names don't have to be the same .

example 1: Inquire about each student and their elective courses
The connection between the two tables is through common properties Sno Realized
select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;

In the above , Prefixes all attribute names with table names , Avoid confusion , If the property name is unique , Can be omitted . And when querying, the Student and SC It's all added * number , If only one is written , It can be found that the effect is the same , Because the result query is based on SC Of the table , because SC In the table Sno Either empty or from Student Existing in table Sno

A possible procedure for a supplementary DBMS to perform this connection operation , In order to better understand the internal work and principle of the database , More convenient to remember : First, in the table Student First tuple found in , Then scan from the beginning SC surface , One by one query and Student Of the first tuple Sno Equal SC tuple , When you find it, you will Student The first tuple in is spliced with that tuple , Form a tuple in the result table .SC After all tables are searched , Look again Student Second tuple in , And then scan from the beginning , One by one query to meet the conditions , Repeat the process repeatedly , Until it's done . This is
The idea of nested loop join algorithm .
The efficiency of the algorithm is low , Here we introduce a more efficient algorithm , It's called sort merge (SORT MERGE)
, Briefly describe its principle : First, sort the table by connection properties , Then from the table 1 Starts with the first tuple in , In the table 2 Find the tuple equal to its connection property in , Until it doesn't conform , The search stops . Then from the table 1 Start with the second tuple of , In the table 2 The last search continues , Until it doesn't conform . The algorithm is compared with nested loop , Check the table 2 The number of traversal searches is much less , The second only needs to be done once , And the first one has a table 1 tuple , You have to traverse the table once 2.

example 2: Example 1 It's done with natural connections

<> Natural connection : In the equivalent join, the duplicate attribute columns in the target column are removed .
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC where

The comparison with unnatural connection is as follows :

example 3: Query elective 2 Course No. 1 with a grade of 86 The student numbers and names of all students with above scores
select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Cno=
'2' and SC.Grade>=90;

<>2. Self connection

Join operations can not only be performed between two tables , It can also be a table connected to itself , Called the self join of a table .

example 1: Query the indirect prerequisite of each course ( That is, the prerequisite course of the prerequisite course )

Because it is operating on the same table , And find the connection conditions , This requires aliasing the table , Prevent confusion . yes Course Two aliases , One is Course1, One is Course2. The data in the two tables are the same , Actually, they are Course surface
select Course1.Cno,Course2.Cpno from Course Course1,Course Course2 where

<>3. External connection

In normal connection operation , Only tuples that meet the join conditions can be output as results , Those who are not satisfied are given up , Doing so may discard some of the data we care about , External connection can solve this problem . An outer join usually selects a table as the principal , When output in this way , The data in the table will be output ( Include the line whose result is empty when the query is connected ).

example 1: Rewrite the query for each student and their elective courses
Want to see each student , In this case Student The main table is more suitable , So you can use the left link
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left outer join
You can see the different views in the figure below

in addition , have access to USING To remove duplicate values from the results , But in SQL Server It's not allowed in China , stay mysql It's OK .

Variant : Use right outer connection
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student right outer
join SC on(Student.Sno=SC.Sno);

The comparison is as follows : The top is the left outer connection , Below is the right outer link

<>4. Multi table connection

The join operation can be two table join , A table is connected to itself , You can also connect more than two tables , This is called multi table join . Which tables of data are generally involved , Just write a few tables , At the same time, the conditions of connection should be given .

example : Query the student number of each student , full name , Elective course name and grade
be careful , Connection conditions should not be less , Otherwise, it doesn't correspond
select Student.Sno,Sname,Cname,Grade from Student,SC,Course where Student.Sno=
SC.Sno and SC.Cno=Course.Cno;

Although it is a multi table operation , Multiple tables involved , But in essence, it does the join operation of two tables first , Then connect it with the third table ( The first two tables are the same as the third table , This is actually a join query operation of two tables , And so on )

<> three , Curriculum comprehension

The content of this experiment is relatively simple , What the teacher said was very clear , Some also demonstrated it in person , So do it yourself , And combined with the examples in the textbook , It will be much easier . It's not hard to get the expected results , But there is still a lot to remember , Such as the basic format , There are also some details ,where Cannot use aggregate function in clause , Still need to strengthen practice and memory , I hope I can learn better , More efficient !
<> People often feel that the preparation stage is a waste of time , Only when the real opportunity comes , And I don't have the ability to grasp it , It's a waste of time to realize that you're not prepared .—— France : Roman · Roland