Public account: You and the cabin by: Peter Editor: Peter

MySQL50-8-26-30 items

Topics 26 to 30 are described in this passage.

  • Count Counts the number of groups
  • Fuzzy matching
  • A self-join of the same table
  • The use of having

The five topics are:

  • Query the number of students enrolled in each course
  • Select student id and name from all students with only two courses
  • Query number of male and female students
  • The query name containsThe windWord for student information
  • Query the list of students with the same name and count the number of students with the same name

The title 26

The subject requirements

Query the number of students enrolled in each course

The analysis process

And they want to know how many people are taking each course, which is pretty easy

SQL implementation

select 
	c.c_id
	,c.c_name
	,count(s.s_id)
from Course c
join Score s
on c.c_id = s.c_id
group by c.c_id;
Copy the code

The subject of 27

The subject requirements

Select student id and name from all students with only two courses

The analysis process

Courses: Score, the number of courses determined by c_id

Student: the Student

SQL implementation

Having conditions are executed after groups, and WHERE statements are executed before groups

select 
	s.s_id
	,s.s_name
from Student s
join Score sc
on s.s_id = sc.s_id
group by 1.2
having count(sc.c_id) = 2;
Copy the code

Topic 28

The subject requirements

Query number of male and female students

The analysis process

Select * from Student; select * from Student

SQL implementation

Let’s look at the numbers: 4 for both men and 4 for women

-- Your own way
select 
	count(case when s_sex='male' then 1 end) as 'male'
	,count(case when s_sex='woman' then 1 end) as 'woman'
from Student;

-- Reference method
select 
	s_sex
	,count(s_sex) asThe number of ` `from Student
group by s_sex;
Copy the code

The subject of 29

The subject requirements

Query student information whose name contains the word wind

The analysis process

With wind in the name, we use fuzzy matching. The table used is Student

SQL implementation

Let’s see which students have wind in their names

Fuzzy matching: We added % to both sides, taking into account the last name or first name containing wind, although wind names are rare
select * from Student where s_name like"% %" wind;Copy the code

Topic 30

The subject requirements

Query the list of students with the same name and count the number of students with the same name

The analysis process

Find the number of students with the same name and same sex from the Student table

SQL implementation

1, first look at the class student information

There are no students with the same name in the available data, but it is likely that students with the same name will appear in classes as class sizes increase

2. Suppose there are students with the same name and same sex

select 
	a.s_name
	,a.s_sex
	,count(*)
from Student a  -- Self-join of the same table
join Student b
on a.s_id ! = b.s_id   - The connection must not be the same person: The student ID is guaranteed to be unique for each person, and other fields may be repeated
and a.s_sex = b.s_sex  -- Same sex
and a.s_name = b.s_name -- Same name
group by 1.2;
Copy the code