New forms: student, course, teacher and score.

CREATE TABLE student 
( 
        id int NOT NULL AUTO_INCREMENT ,//Self increment
        name nvarchar(32), 
        age int, 
        sex nvarchar(8),
        PRIMARY KEY (`id`)//Primary key
) ;
CREATE TABLE course 
  ( 
     id int NOT NULL AUTO_INCREMENT ,
     name nvarchar(32), 
     teacherId int ,
         PRIMARY KEY (`id`)
  );
CREATE TABLE teacher 
  ( 
     id int NOT NULL AUTO_INCREMENT ,
     name nvarchar(16) ,
         PRIMARY KEY (`id`)
  );
CREATE TABLE score
  ( 
        id int NOT NULL AUTO_INCREMENT , 
        studentId int, 
        courseId int ,
        score int ,
        PRIMARY KEY (`id`)
  ) 

View the table in the current database, SHOW TABLES;

insert data

INSERT INTO <Table Name> (<Column Name 1>, <Column Name 2>, <Column Name 3>,... ) VALUES (<Value 1>, <Value 2>, <Value 3>,... );

INSERT INTO student(name,age,sex) VALUES ("Liu Yi",18,"male"),("Cai Er",19,"female"),("Zhang San",20,"male"),("Li Si",17,"female"),("Wang Wu",21,"male"),("Zhao Liu",22,"female");
INSERT INTO teacher(name) VALUES("Ye Ping"),("He Gao"),("Yang Yan"),("Lei Zhou");
INSERT INTO course(name,teacherId) VALUES ("Chinese",1),("Mathematics",2),("English",3),("Physics",4);
INSERT INTO score(studentId,courseId,score) VALUES (1,1,56),(1,2,78),(1,3,67),(1,4,58),(2,1,79),(2,2,81),(2,3,92),(2,4,68),(3,1,91),(3,2,47),(3,3,88),
                                                                                                        (3,4,56),(4,2,88),(4,3,90),(4,4,93),(5,1,46),(5,3,78),(5,4,53),(6,1,35),(6,2,68),(6,4,71)

Update data

UPDATE < Table Name > SET < Column Name 1 > = Value 1 >, < Column Name 2 > = Value 2 >,... WHERE < Conditions >;

Empty table

DELETE FROM < Table Name > WHERE < Conditions >;

Delete tables

DROP TABLE <Table name>;

Learning in Practice

1. Query the number of all the students whose grades in Course 1 are higher than those in Course 2.

   SELECT s1.studentId FROM (SELECT studentId,score from score WHERE courseId = 1) s1,
                           (SELECT studentId,score from score WHERE courseId = 2) s2 
                            WHERE s1.score>s2.score and s1.studentId = s2.studentId

   

2. Query the number and average score of students whose average score is more than 60

   SELECT studentId,AVG(score) FROM score GROUP BY studentId HAVING AVG(score)>60

   
   GROUP BY statements can group results, often used in aggregate functions such as COUNT, MAX, MIN, SUM, AVG, etc.
   The difference between WHERE and HAVING
   "Where" is a constraint declaration that uses Where to constrain the data from the database, where works before the result is returned, and where cannot use aggregation functions.
   "Having" is a filtering declaration that filters the query results after the query returns the result set. Aggregation functions can be used in Having.

3. Inquire the student number, name, number of courses selected and total scores of all the students.

   SELECT student.id,name,COUNT(score.courseId),SUM(score) FROM 
                           student LEFT JOIN score 
                           on student.id = score.studentId 
                           GROUP BY student.id;
   

   

   LEFT JOIN, RIGHT JOIN, INNER JOIN

   

   A left join B has the same number of records as table A.  
   The number of records connected by A right join B is the same as that of table B.    
   
   

4. Query the number of teachers whose surname is Li.

   SELECT COUNT(DISTINCT(name)) FROM teacher WHERE name like "plum%"

   DISTINCT filters out duplicate values

5. Inquire the student number and name of the students who have not learned "Yeping" teacher's lesson.

   SELECT id,name FROM student WHERE id not in (
       SELECT DISTINCT(score.studentId) FROM score,course,teacher WHERE 
           teacher.`name` = "Ye Ping" and teacher.id = course.teacherId and score.courseId = course.id)

   Logical train of thought: first query the data of Ye Ping, get the corresponding course ID according to id, get all the students. ID of Ye Ping according to the course student relationship table, and use not in to screen.

6. All the students whose grades are lower than those of the course number "001" are enquired for their school numbers and names.

   SELECT st.id,st.name FROM student st,score s WHERE s.courseId = 1 and st.id = s.studentId and 
   EXISTS 
       (SELECT * FROM score s1 WHERE s1.courseId =2 and s1.studentId = s.studentId)

   Execution order: SELECT st.id,st.name FROM student st,score s WHERE s.courseId = 1 and st.id = s.studentId
   Execute the statement after EXISTS
   If it is true, it returns true, and if it is not, it returns false. If true is returned, the result of the line is retained, if false is returned, the line is deleted and the result is returned.

7. To inquire about the student numbers and names of all the classes taught by Teacher Yeping.

   select id,name from student 
         where id in (
             select score.studentId from score ,course ,teacher where score.courseId=course.id and         
                  teacher.id=course.teacherId and teacher.name='Ye Ping' 
             group by 
                 score.studentId having count(score.courseId)
                 =
                 (select count(course.id) from course,teacher where teacher.id=course.teacherId and teacher.name='Ye Ping')); 
   

   Logic of Thought: Similar to Fifth, group by is grouped according to studentId

8. According to the average score, all the students'achievements in the three subjects of "Chinese", "Mathematics" and "English" are shown in the following forms: student ID, database, business management, English, number of effective courses, effective average score.

   SELECT studentId as Student ID 
       ,(SELECT score FROM score WHERE score.id=s.id AND score.courseId='001') AS Chinese
       ,(SELECT score FROM score WHERE score.id=s.id AND score.courseId='002') AS Mathematics 
       ,(SELECT score FROM score WHERE score.id=s.id AND score.courseId='003') AS English 
       ,COUNT(*) AS Number of effective courses, AVG(s.score) AS Average score 
   FROM score AS s 
   GROUP BY studentId 
   ORDER BY avg(-s.score) 

   

performance optimization

1. Use limit 1 when only one row of data is needed
   With limit 1, the Mysql database engine stops searching when it finds a result, instead of continuing to query whether the next one meets the criteria until all records are queried.
2. Choose the right database engine
   Mysql has two engines, MyISAM and InnoDB, which are more common than InnoDB.
   MyISAM, which is suitable for a lot of queries, is not very friendly to a lot of writings. Even a field of update locks the whole table, but the operation of select count(*) is very fast.
3. Replace not in with not exists
   not exists uses the connection, can play the role of the index has been built, not in can not use the index. Not in is the slowest way to compare with each record. It is not recommended for operations with large amounts of data.

affair

Not to be continued