<> 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
Student.Sno=SC.Sno;
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
Course1.Cpno=Course2.Cno;
<>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
SCon(Student.Sno=SC.Sno);
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
Technology