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
Daily Recommendation
views 5
views 4
views 4