MySQL 七十条查询语句精通MySQL面试题_查询出学生的编号,姓名,计算机成绩-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_51596354/article/details/122074884

本文提供七十条MySQL查询语句，涵盖创表、数据添加及进阶查询，包括学生、课程、教师和成绩表的相关操作，帮助读者深入理解MySQL数据库操作并准备面试。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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`) values ('2010009','Hunk',19,'男','湖北武汉',4);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010010','Jim',18,'男','湖北襄樊',3);