MySQL 七十条查询语句精通MySQL面试题

创表和添加数据

class,computer,student表

CREATE TABLE `class` (
    `classid` int(11) NOT NULL AUTO_INCREMENT,
    `classname` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`classid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert  into `class`(`classid`,`classname`) values (1,'G1T01');
insert  into `class`(`classid`,`classname`) values (2,'G1T02');
insert  into `class`(`classid`,`classname`) values (3,'G1T03');
insert  into `class`(`classid`,`classname`) values (4,'G1T04');
insert  into `class`(`classid`,`classname`) values (5,'G1T05');
insert  into `class`(`classid`,`classname`) values (6,'G1T06');
insert  into `class`(`classid`,`classname`) values (7,'G1T07');

CREATE TABLE `computer` (
    `studentid` varchar(20) DEFAULT NULL,
    `score` float DEFAULT NULL,
    KEY `studentid` (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert  into `computer`(`studentid`,`score`) values ('2010001',90);
insert  into `computer`(`studentid`,`score`) values ('2010002',80);
insert  into `computer`(`studentid`,`score`) values ('2010003',70);
insert  into `computer`(`studentid`,`score`) values ('2010004',60);
insert  into `computer`(`studentid`,`score`) values ('2010005',75);
insert  into `computer`(`studentid`,`score`) values ('2010006',85);
insert  into `computer`(`studentid`,`score`) values ('2010007',70);
insert  into `computer`(`studentid`,`score`) values ('2010008',60);
insert  into `computer`(`studentid`,`score`) values ('2010009',75);
insert  into `computer`(`studentid`,`score`) values ('2010010',85);

CREATE TABLE `student` (
    `studentid` varchar(20) NOT NULL,
    `studentname` varchar(20) DEFAULT NULL,
    `studentage` int(11) DEFAULT NULL,
    `studentsex` char(10) DEFAULT NULL,
    `studentaddress` varchar(50) DEFAULT NULL,
    `classid` int(11) DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010001','Jack',21,'男','湖北襄樊',1);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010002','Scott',22,'男','湖北武汉',2);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010003','Lucy',23,'女','湖北武汉',3);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010004','Alan',19,'女','湖北襄樊',4);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010005','Bill',20,'男','湖北襄樊',5);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010006','Bob',21,'男','湖北宜昌',6);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010007','Colin',22,'女','湖北襄樊',6);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010008','Fred',19,'男','湖北宜昌',5);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid