Study database notes 6. Database notes

Study database notes 6. Database notes

I

Summarized some problem-solving templates, which can be applied in some cases

Model 1 group by + statistical function combination

Example
Calculate the number of items under each topic
Selct cat_id, count (*) from goods group by cat_id;

Idea 1. Use group by to group each group,
2. Calculate the quantity, average score, and maximum score of xx using the statistical function.


Other similar
Every class... Average avg (score)... group by class
Every department... Max (gongzi)... group by dept
Each group... Min (score)... group by xiaozu

With limit, you can retrieve the first few

Model 2 result set + comparison with result set

Example
Find out more than 200 items cheaper than the market price
Select goods_id, goods_name, (market_price-shop_price) as sheng from goods having sheng> = 200;

Idea 1. How much cheaper than xx, use addition and subtraction to calculate the result set
2. Which of the following are cheaper result sets greater than 200?

Other similar
... Cheaper than 100 (a_price-b_price) as sheng... sheng> 100
... More than 2000 expensive (a_price-b_price) as gui... gui> 2000
... Lower 50 + (a_score-b_score) as di... di> 50

Because the result set is used, having is used to obtain

Model 3 order by + limit

Example
Take out the top three limit items with the highest price (in reverse order)
Selct * from goods order by shop_price desc limit 0, 3;

Idea 1. The highest price, sort the price in reverse order
2. Use limit to extract the top three


Other similar
Retrieve the top three who have the best learning skills
Select * from student order by score desc limit 0, 3;

Model 4 result set + group by + result set comparison

Example
Average score of two or more non-pass persons
Select name, sum (score <60) as gk, avg (score) as pj from result group by name having gk> = 2;



Idea 1. Find out the average score of xxx (group by + statistical function combination TEMPLATE 1)
2. Non-pass-by (result set + and result set comparison template 2)


Step 1. select avg (score) from result group by name;
Step 2.1.select sum (score <60) as gk from result group by name
Step 2.2 select sum (score <60) as gk from result group by name having gk> = 2;

Combining them is
Select name, sum (score <60) as gk, avg (score) as pj from result group by name having gk> = 2;



TEMPLATE 4 is a combination of model 1 and Model 2



II

Online interview questions and Solutions

1. Use an SQL statement to query the names of students whose scores are greater than 80 in each course.

Name kecheng fenshu
Zhang San Language 81
James math 75
Li Si language 76
Li Si mathematics 90
Wang Wu language 81
Wang Wu, mathematics 100
Wang Wu English 90

Solution
SELECT name, 'subobject', score, sum (score> 80) as jg from result group by 'name' HAVING jg = (select count (DISTINCT rs. subject) from result rs );

Select COUNT (DISTINCT 'subobject') from result for several courses;

2.
Automatic student ID name course number Course name score
1 2005001 Zhang San 0001 mathematics 69
2 2005002 Li Si 0001 mathematics 89
3 2005001 Zhang San 0001 mathematics 69

Delete redundant student information that is identical except for automatic numbers


Solution
Create table tmp1
SELECT s. code FROM student s GROUP
S. 'name', s. kname, s. scode, s. kcode, s. score;

Delete from student where code not in (select code from tmp1 );

Drop TABLE tmp1;


Deleting redundant information is actually super simple and can be solved by grouping
Group fields. fields that have different content are not grouped into one group.
If both are written in one statement, a temporary table needs to be created to store



Ideas:

It is easy to identify the numbers of redundant information:
Select code from student s group by s. name, s. kname, s. scode, s. kcode, s. score;
There are no redundant numbers. Redundant numbers can be obtained as long as they are not in this statement.

However, the deletion statement cannot delete the content of the above result set (the content of the result set is not in the memory)

Therefore, you need to create a temporary table, that is, the tmp1 above, put the detected content in the temporary table, and then delete the temporary table after use.

3. a table Named department has only one field name and a total of four records, which are a, B, c, and d, corresponding to four ball pairs. Now, four ball pairs are playing, use an SQL statement to display all possible competition combinations.

SELECT * FROM
Department inner join department as
ON
Department. 'name' <> (a. 'name ')