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