Basis and exercise

Select now ();
Select CURDATE ();
Insert into pa_login_logs (party_no, login_time) values (3, now ());
SELECT *, max (login_time) FROM pa_login_logs group by party_no order by max (login_time) desc;
 
 
5. There is a table s_su with three fields, age, name, and score. Now write the SQL statement as required:
1) Select the age group and number of students in groups with scores greater than 90 based on age groups.
Select age, count (*) from s_su where scoure> 90 group by age;
2) Select the age group and the average score of the group with an average score greater than 85 based on the age group.
Select age, avg (scoure) from s_su group by age having avg (scoure)> 90;
3) assume that the table has 100 records, use SQL statements to print the paging records and the records at 50-60.
Mysql: select * from s_su limit 50, 60;
---- This is mainly used for grouping and using functions, and having is used to select a suitable group. Limit is a Mysql professional function, and other databases do not know whether it can be used in general. You can test it.

6. Assume that table A has 6 records and B has 4 records, and the matched field is name. The following SQL statement shows the result:
1) select * from A left join B on A. name = B. name. How many records can be found at most?
A: The maximum and minimum are six.
2) select * from A right join B on A. name = B. name. How many records can be found at most?
A: The maximum and minimum are four.
3), select * from A left jion B on. name = B. name union select * from A left jion B on. name = B. name. How many records are there at most?
A: The maximum and minimum are six.
4), select * from A left jion B on. name = B. name union select * from B left jion A on B. name =. name. How many records are there at most?
A: There are up to 10 records and at least 6 records.
5), select * from A left jion B on. name = B. name union all select * from A left jion B on. name = B. name. How many records are there at most?
A: The maximum and minimum are 12.
6), select * from A left jion B on. name = B. name union select * from B left jion A on B. name =. name. How many records are there at most?
A: The maximum and minimum are 10.
---- Here, the left join and right join are obtained in the SQL statement. The left join uses the left join as the benchmark. Some values are retained. If not, the left join is Null. The same is true for the right join. Now the main problem is the union and union all. union merges the same data and inserts the same data into the left base table, union all is inserted to the left base table, regardless of whether it is the same or not.

 

This article is from "tongxiaoming520"