one , Summary analysis
1. Summary of knowledge points
count: Find the number of rows in a column , If you enter in the function 【 Listing 】 Remove null values null Number of subsequent rows ; If input 【*】 All rows will be output
sum: Sum a column of data （ Only columns of data type can be evaluated ）
avg： Average the sum of a column of data （ Can only be evaluated on columns of numeric type ）
max： Find the maximum value of a column of data
min： Find the minimum value of a column of data
2. exercises
1） The query course number is “0002” Total score of
2） Query the number of students who have selected the course
Train of thought analysis ： At first I was from student The total number of students is calculated after the name is de duplicated in the data sheet , But there are two problems with this idea ： First, not every student has taken the course , I ignored the preconditions “ Took the course ”; The second pair “ Student name ” duplicate removal , Not rigorous enough , There will be different people with the same name and surname , Should be right “ Student number ” duplicate removal , The student number is unique .
Correct thinking ： belong “score” Table pair “ Student number ” duplicate removal , And set the final result to “ Number of students ”
two , grouping
1. Summary of knowledge points
1) Data grouping （group by) — Application function (count) — Combination results
2)SQL Running sequence
2. exercises
1) Query the highest and lowest scores of each subject
Problem solving ideas ： grouping , use group by Classification of courses ; Application function , Maximum use max() function , Minimum use min() function ; Final combination result .
2） Query the number of students selected for each course
Problem solving ideas ： grouping , use group by Classification of courses ; Application function , For student statistics count() function .
3） Query male , Number of girls
Problem solving ideas ： Group first , use group by Gender grouping ; Then use count() Function statistics .
three , Specify criteria for grouping results
1. Summary of knowledge points
1）where Only specified “ that 's ok ” Data ,having Yes “ Grouped data ” Specify conditions .
2）SQL Running sequence
2. practice
1） Query average score greater than 60 It is divided into student number and average score
Problem solving ideas ： The average score is the average score of each student , Groups are needed for each ; Average score greater than 60 Dividing is to specify conditions for grouping results .
2） Query the student number of students taking at least two courses
Problem solving ideas ： Grouping student numbers , Calculate the total number of elective courses , The number of filtered courses is greater than or equal to 2 Student ID .
3） Query the list of students with the same name and surname and count the number of students with the same name
Problem solving ideas ： Find students with the same name first , Number of students with the same name . The query result is name and number of people , Then filter out the number of people with the same name >=2 Results .
four , use sql Solve business problems
1. Summary of knowledge points
method ： Translation problems — Learn analytical ideas — Write the corresponding SQL clause
five , Sort query results
1. Summary of knowledge points
1） Descending order （desc): From big to small
Ascending order （asc): from small to large
2） Specify multiple sort column names ： Order from left to right
3） Null value （null) Sort of ： Null values are displayed at the beginning of the query column
4） Retrieves the specified row from the query demerit ：limit
5）SQL Running sequence
2. practice
1） Query the failed courses and arrange them according to the course number from large to small
( remarks ： Marked with red numbers SQL Statement run order ）
2） Query the average score of each course , The results are sorted in ascending order of average score . Same grade point average , Descending by course number .
Problem solving ideas ： Each course requires group by Group courses , GPA ready to use avg() function .
( remarks ： Marked with red numbers SQL Statement run order ）
six , Understand the error message
Common errors
1. stay group by perhaps having Used in select Alias in : Because it's running group by and having
Statement has not been executed select sentence , So I don't know what the alias is .
2. stay where Aggregate functions used in
3. Number of string type
Technology