[database] MySQL multi table query (I)

Exercise four ( One ) Multi-table query

One 、 Link query

1. Check the student number and total score of female students

Let's take a look first jxgl Tables in the database ：

 

Check the student number and total score of female students , We found that sc There is no gender column in the table ,student There is a gender in the table , however student There are no grades in the table .

Therefore, through this step, we will introduce the main content of this chapter ----> Multi-table query ;

Convenient observation , I'll stick it here first sc Table and student surface

 

 

The operation is as follows ：

 

There is the following situation , Why? ？

MySQL Of sql_mode yes only_full_group_by When , Without using group by also select If the aggregation function appears later , So by all select All should use aggregate functions ( For example, in this case sum function ). Otherwise, such errors will be reported ;

Tips： stay select The specified field must either be included in group by After statement , As a basis for grouping ; Or it's included in the aggregate function

 

2. Query the course number and grade selected by Li Yong

The course number and grade are in sc table , But the student's name is in student table , Therefore, it is multi table query

So it's straightforward to operate ：

 

Maybe I think it's not bad , But take a closer look , In fact, there are problems ：select What's the matter sc The whole of the watch ！

There is a problem ！！！！！！！！！！！！！

 

 

Then let's take a look at the previous choice ：

3. Query the course name of the course taught by Mr. Li Xin

First of all, the course name is course table ：

 

Then the teacher's name is in teacher table ：

 

But when we look at these two tables, we can easily find , There are no fields on these two tables that can be matched , So an exception is needed teaching surface ：

 

Do something ：

 

4. Check the course number and course name of the course given by the female teacher

 

 

5. Check the last name “ king ” The name of the course the students learned

Wildcards are needed here % 了

 

6. Check options “ database ” Course and grade in 80  To 90  Student number and grades between

 

7. Check options “C03” The average age of the students in the course

 

But if Party A is tricky , Just two decimal places , Need cast Functions like this ：

 

But Party A thinks the name of this field is too long , Change to average age

 

8. The query learning course is named “ database ” Student ID and name of

 

9. Inquire about ” Li Xin ” The course number of the teacher , The student number and grades of the students taking their courses

 

10.   The query is on page  3 The course name of the course offered in the semester , Student number and grades of students taking their courses