MySQL Classic face question

Database optimization:
This optimization rule is summed up in 5 levels:
1. Reduce data access (reduce disk access)
2. Return less data (reduce network transmission or disk access)
3. Reduce the number of interactions (reduce network transmission)
4. Reduce server CPU overhead (reduce CPU and memory overhead)
5. Use more resources (increase resources)

What fields do we usually index?
This is a very complex topic that requires a thorough analysis of the business and data before the results can be obtained. Primary keys and foreign keys are usually indexed, and other fields that need to be indexed should meet the following criteria:
1, the field appears in the query condition, and the query condition can use the index;
2, the sentence execution frequency is high, the day will have more than thousands of times;
3. The record set that can be filtered by the field condition is very small, how much is the data filtering ratio suitable?
This does not have a fixed value and needs to be evaluated based on the amount of table data, and the following is an empirical formula that can be used for quick evaluation:
Small table (table with fewer than 10000 records): Filter scale <10%;
Large table: (Filter return Record Count) < (total table record * Single record length)/10000/16
Single Record length ≈ field average content length sum + number of fields

--Current time
Select Curtime ();
--Current date
Select Now ();
--Query Day Data
SELECT * FROM table name where To_days (Time field) =to_days (now ());
--Query this week's data
SELECT * FROM table name WHERE Yearwe--ek (date_format (Time field, '%y-%m-%d ')) = Yearweek (now ());
--Last 7 days
SELECT * FROM table name where Date_sub (Curdate (), INTERVAL 7 day) <= Date (Time field);
--Query this month's data
SELECT * FROM table name where Date_format (Time field, '%y%m ') =date_format (Curdate (), '%y%m ');
--Last month
SELECT * FROM table name WHERE Period_diff (Date_format (now (), '%y%m '), Date_format (Time field name, '%y%m ')) =1

/*-----------------------------------------The first big question--------------------------------------------------*/

S (SO,SN,SD,SA) number, student name, unit, student age

C (CO,CN) Course number, course name

Sc_1 (so,co,g) number, selected course number, academic achievement

/*1 Inquiry Elective course name ' Student number and name ' of ' tax base ' * *
--Method One: Connection query

SELECT s.so,s.sn from S s,c c,sc_1 SC
Where c.cn= ' tax base '
and S.SO=SC. So and sc.co=c.co;

--Method Two: Nested query
SELECT SO,SN from S
where So in (
SELECT so from Sc_1 where co in (
SELECT Co from c where cn= ' tax base '
)
)

/*2 Enquiry Elective Course number ' c002 ' Student name and Affiliation unit ' * *
--Method One: Connection query
SELECT s.sn,s.sd from S s,c c,sc_1 SC
where c.co= ' c002 '
and c.co=sc.co and s.so=sc.so;

--Method Two: Nested query
SELECT SN,SD from S
where So in (
SELECT so from Sc_1 where co in (
SELECT Co from c
where co= ' c002 '
)
)

/*3 query does not take the course number ' c005 ' Student name and Affiliation unit ' * *
SELECT SN,SD from S
where so isn't in
(select so from sc_1 WHERE co= ' C005 ');

/*4 Search for all course participants ' names and their respective units ' * *
SELECT SN,SD from S
where so in
(
SELECT so from sc_1
GROUP by So
Have count (CO) = (SELECT COUNT (CO) from C)
);

/*5 enquiries about the number of students enrolled in the course * *
SELECT COUNT (DISTINCT so) as elective course number from sc_1;

/*6 new elective courses more than 5 students name and unit * *
SELECT SN,SD from S
where so in
(SELECT so from sc_1
GROUP by So
Have COUNT (SO) >5
);

/*-----------------------------------------The second big question--------------------------------------------------*/

S (Sno,sname) Student relations, SNO number, SNAME name

C (cno,chame,cteacher) course relationship, CNO course number, chame course name, Instructor

SC (sno,cno,scgrade) Course selection relationship, Scgrade results

/* Find all students who have not enrolled in the course of Li Ming's teacher * *
SELECT S.sname from student s
Where Sno not in
(SELECT DISTINCT (SC1.CNO) from class C,SC sc1,student s
where C.cno=sc1.cno and Sc1.sno=s.sno
And C.cteacher= ' Li Ming '
);

/* The second question lists the names of students with two or more failed courses and their average scores * *
Select S.sname as student name, AVG (Ssc.scgrade) as average score from student s
, SC SSC, (select Sno from SC
where scgrade<60
GROUP by Sno
Having COUNT ((CNO) >=2)) a
where S.sno=a.sno and Ssc.sno=a.sno
GROUP by S.sno,s.sname;

/* The name of the student who has studied course No. 1th and has studied course 2nd * *
Select S.sno,s.sname from student s
where S.sno in
(
Select Sc2.sno from SC sc2,class C2 Where sc2.cno=c2.cno and C2.cno in (' 1 ', ' 2 ')
GROUP by Sc2.sno
Having COUNT (DISTINCT c2.cno) =2
);

/* Fourth of all students who have a higher score than 2nd in the class 1th,
SELECT Sc1.sno as student number from SC SC1,SC SC2 where
sc1.cno= ' 1 '
and sc2.cno= ' 2 '
and Sc1.sno=sc2.sno
and sc1.scgrade>sc2.scgrade;

/* Fifth list of all student numbers with 1th grades higher than 2nd and class 1th and 2nd results * *

SELECT Sc1.sno as student number, Sc1.scgrade as language score, sc2.scgrade as math score from SC SC1,SC SC2 where
sc1.cno= ' 1 '
and sc2.cno= ' 2 '
and Sc1.sno=sc2.sno
and Sc1.scgrade>sc2.scgrade

MySQL Classic face question