MySQL multi table query

1, Table schema

  • student(sid,sname,sage,ssex) student table
  • course(cid,cname,tid) schedule
  • sc(sid,cid,score) score sheet
  • teacher(tid,tname) teacher table

2, Create table sql statement

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(11) NOT NULL COMMENT 'Course number',
  `cname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Course name',
  `tid` int(11) NULL DEFAULT NULL COMMENT 'Teacher number',
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (3001, 'English', 1);
INSERT INTO `course` VALUES (3002, 'mathematics', 2);
INSERT INTO `course` VALUES (3003, 'Physics', 3);
INSERT INTO `course` VALUES (3004, 'language', 4);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `sid` int(11) NOT NULL COMMENT 'Student number',
  `cid` int(11) NOT NULL COMMENT 'Course number',
  `score` int(11) NULL DEFAULT NULL COMMENT 'achievement'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (101, 3001, 90);
INSERT INTO `sc` VALUES (102, 3001, 85);
INSERT INTO `sc` VALUES (103, 3001, 76);
INSERT INTO `sc` VALUES (104, 3002, 87);
INSERT INTO `sc` VALUES (105, 3001, 92);
INSERT INTO `sc` VALUES (101, 3002, 81);
INSERT INTO `sc` VALUES (102, 3002, 93);
INSERT INTO `sc` VALUES (103, 3002, 73);
INSERT INTO `sc` VALUES (104, 3002, 65);
INSERT INTO `sc` VALUES (105, 3002, 96);
INSERT INTO `sc` VALUES (101, 3003, 85);
INSERT INTO `sc` VALUES (102, 3003, 76);
INSERT INTO `sc` VALUES (103, 3003, 63);
INSERT INTO `sc` VALUES (104, 3003, 59);
INSERT INTO `sc` VALUES (105, 3003, 56);
INSERT INTO `sc` VALUES (101, 3004, 100);
INSERT INTO `sc` VALUES (102, 3004, 83);
INSERT INTO `sc` VALUES (103, 3004, 75);
INSERT INTO `sc` VALUES (104, 3004, 69);
INSERT INTO `sc` VALUES (105, 3004, 50);
INSERT INTO `sc` VALUES (106, 3001, 60);
INSERT INTO `sc` VALUES (106, 3001, 60);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(11) NOT NULL COMMENT 'Student number',
  `sname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'full name',
  `sage` int(11) NULL DEFAULT NULL COMMENT 'Age',
  `ssex` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Gender',
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, 'Wang Ming', 18, 'female');
INSERT INTO `student` VALUES (102, 'Wang Tian', 19, 'male');
INSERT INTO `student` VALUES (103, 'Zhang San', 18, 'male');
INSERT INTO `student` VALUES (104, 'Li Si', 19, 'female');
INSERT INTO `student` VALUES (105, 'Wang Wu', 20, 'male');
INSERT INTO `student` VALUES (107, 'Wanyuan', 17, 'male');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(11) NOT NULL COMMENT 'Teacher number',
  `tname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'Teacher name',
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, 'Miss Wang');
INSERT INTO `teacher` VALUES (2, 'Miss Li');
INSERT INTO `teacher` VALUES (3, 'Miss Zhao');
INSERT INTO `teacher` VALUES (4, 'Teacher song');

SET FOREIGN_KEY_CHECKS = 1;

3, Query

1. Multi table query syntax

select  Field 1,Field 2... from Table 1,Table 2... [where condition]
  • Query all information of student form and grade sheet

    select * from student,sc where student.sid = sc.sid

Note: when querying multiple tables, you must find the fields related to each other in the two tables and use them as conditions

2. Multi table link query

Multi table join query syntax(a key)
SELECT Field list
    FROM Table 1  INNER|LEFT|RIGHT JOIN  Table 2
ON Table 1.field = Table 2.field;

① Internal connection query (only display qualified data)

  • Query all information of student form and grade sheet

    select * from student inner join sc on student.sid = sc.sid;

The effect of inner join query is the same as that of multi table joint query

② Left outer connection query (data in the left table will be displayed first)

  • Query all information of student form and grade sheet

    select * from student left join sc on student.sid = sc.sid;

All the data in the student form will be displayed, while the data in the grade sheet will be displayed only if it meets the conditions, and the data that does not meet the conditions will be filled with null


③ Right external connection query (the data in the right table will be displayed first)

  • Query all information of student form and grade sheet

    select * from student right join sc on student.sid = sc.sid;

Just opposite to [left outer connection]


④ Full connection query (display all data in the left and right tables)

Full connection query: add data that is not displayed on the left and right sides on the basis of internal connection
 be careful: mysql Full connection is not supported full JOIN keyword
 be careful: however mysql Provided UNION keyword.use UNION Can be achieved indirectly full JOIN function


select * from student left join sc on student.sid = sc.sid
UNION
select * from student right join sc on student.sid = sc.sid;

Note: the difference between UNION and UNION ALL: UNION will remove duplicate data, while UNION ALL will directly display the results

3. Three table query

① Query everyone's English grades

Syntax:
select Table 1.field,Table 2.field,Table 3.field from Table 1 join Table 2 on Table 1.Associated fields = Table 2.Associated fields join Table 3 on Table 2.Associated fields = Table 3.Associated fields where....;


select student.sname,course.cname,sc.score from student join sc on student.sid = sc.sid join course on sc.cid = course.cid where course.cname = "English";

4. Other queries

1. Query the student numbers and scores of all students whose grades in the "3001" course are higher than those in the "3003" course;

SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="3001") a,

      (SELECT sid,score FROM sc WHERE cid="3003") b

     WHERE a.score>b.score AND a.sid=b.sid

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

SELECT sid,AVG(score)

FROM sc

GROUP BY sid HAVING AVG(score)>60

3. Check the student number, name, number of courses selected and total score of all students

select s.sid as Student number,s.sname as full name,count(sc.cid) as Number of courses selected,SUM(sc.score) as Total score
from student s INNER JOIN sc sc
on s.sid=sc.sid
GROUP BY s.sid

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

select count(distinct(Tname))
  from teacher
  where tname like 'Li%';

5. Query the student number and name of the students who have learned the "Teacher Wang" class

SELECT s.sid AS Student number,s.sname AS full name 
FROM student s, sc sc, course c, teacher t 
WHERE s.sid = sc.sid AND sc.cid = c.cid AND c.tid = t.tid AND tname = "Miss Wang"

6. Query the student number and name of the students who have not learned the "Teacher Wang" class

SELECT s.sid, s.sname
FROM student s
WHERE s.sid NOT IN (
SELECT s.sid
FROM student s, sc sc, course c, teacher t
WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="Miss Wang")

First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials

Tags: Back-end Front-end Android Interview

Posted by shanksta13 on Fri, 05 Aug 2022 02:06:36 +0930