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