MYSQL数据库练习题操作（select）大全

2024-07-30 21:46:440 次浏览 投稿

1、创建表表一：student学生use)1、create table student(sno varchar(20) primary key not null comment'学号(主码)',sname varchar(20) not null comment'学生姓名',ssex varchar(20) not null comment'学生性别',sbirthday datetime comment'学生出生年月',class varchar(20) comment'学生所在班级');表（二）Course（课程表）create table course(cno varchar(20) primary key not null comment'课程号(主码)',cname varchar(20) not null comment'课程名称',tno varchar(20) not null comment'教工编号');表（三）Score(成绩表)create table score(id int primary key auto_increment comment'主键自增',sno varchar(20) not null comment'学号',cno varchar(20) not null comment'课程号',degree Decimal(4,1) comment'成绩');表四 teacher(教师表)create table teacher(tno varchar(20) primary key not null comment'教工编号(主码)',tname varchar(20) not null comment'教工姓名',tsex varchar(20) not null comment'教工性别',tbirthday datetime comment'教工出生年月',prof varchar(20) comment'职称',depart varchar(20) not null comment'教工所在部门');2、在表中添加数据学生表数据的插入：insert into student values('108','曾华','男','1977-09-01','95033'),('105','匡明','男','1977-09-01','95031'),('107','王丽','女','1977-09-01','95033'),('101','李军','男','1977-09-01','95033'),('109','王芳','女','1977-09-01','95031'),('103','陆君','男','1977-09-01','95031');课程表数据的插入：insert into course values('3-105','计算机导论','825'),('3-245','操作系统','804'),('6-166','数字电路','856'),('9-888','高等数学','831');成绩表数据的插入：select cno from score group by cno; #找出这个表中所有的班级insert into score(sno,cno,degree) values('103','3-245','86'),('105','3-245','75'),('109','3-245','68'),('103','3-105','92'),('105','3-105','88'),('109','3-105','76'),('101','3-105','64'),('107','3-105','91'),('108','3-105','78'),('101','6-166','85'),('107','6-166','79'),('108','6-166','81');教师表数据的插入：insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系'),('856','张旭','男','1969-03-12','讲师','电子工程系'),('825','王萍','女','1972-05-05','助教','计算机系'),('831','刘冰','女','1977-08-14','助教','电子工程系');3、SELECT查询

select [all | distinct] 字段或表达式列表 [from子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句]；

1、 查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class from student; 2、查询教师所有的单位即不重复的Depart列。select distinct depart from teacher;3、 查询Student表的所有记录。select * from student;4、 查询Score表中成绩在60到80之间的所有记录。select * from score where degree>60 and degree= 5;select cno,count(*),avg(degree) from score where cno like '3%' group by cno having count(*) >= 5;13、查询分数大于70，小于90的Sno列。select sno,degree from score where degree>70 and degree70 and degree (select degree from score where sno = '109' and cno = '3-105');19、查询score中选学多门课程的同学中分数为非最高分成绩的记录。select cno from score where (cno = '3-245' and cno = '3-105') or (cno = '3-245' and cno = '6-166') or (cno = '3-245' and cno = '6-166'); 这是个错误的select * from Score a where Sno in (select Sno from Score group by Sno having count(*)>1 ) and Degree not in (select max(Degree) from Score b where b.Cno = a.Cno)select * from Score a where Sno in (select Sno from Score group by Sno having count(*)>1 ) and Degree not in (select max(Degree) from Score a where Sno in (select Sno from Score group by Sno having count(*)>1 ))20、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。select degree from score where sno = '109' and cno = '3-105';select * from score where degree > (select degree from score where sno = '109' and cno = '3-105');21、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。select sbirthday from student where sno = '108';select sno,sname,sbirthday from student where sbirthday = (select sbirthday from student where sno = '108');22、查询“张旭“教师任课的学生成绩（姓名）。select tno from teacher where tname = '张旭';#找出教师编号select cno from course where tno = (select tno from teacher where tname = '张旭'); #找出课程编号select sno,degree from score where cno = (select cno from course where tno = (select tno from teacher where tname = '张旭'));select student.sno,degree,sname from score join student on score.sno = student.sno where student.sno in (select sno from score where cno = (select cno from course where tno = (select tno from teacher where tname = '张旭')));23、查询考计算机导论的学生成绩select cno from course where cname = '计算机导论'; #找到课程编号3-105select sno,degree from score where cno = (select cno from course where cname = '计算机导论');24、查询李诚老师教的课程名称select tno from teacher where tname = '李诚'; ##找到教师编号select cname from course where tno = (select tno from teacher where tname = '李诚');25、教高等数学的老师是哪个系的select tno from course where cname = '高等数学';select depart from teacher where tno = (select tno from course where cname = '高等数学');26、查询选修某课程的同学人数多于5人的教师姓名。select cno,count(*) from score group by cno having count(*)>=5;#找出课程编号select tno from course where cno = (select cno from score group by cno having count(*)>=5);select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*)>=5));27、查询95033班和95031班全体学生的记录。select * from student group by class having count(*);select * from student order by class desc;28、查询成绩表中存在有85分以上成绩的课程Cno.select cno,degree from score where degree>85;29、查询出“计算机系“教师所教课程的成绩表。select tno,tname from teacher where depart = '计算机系'#查出教师编号select cno from course where tno in (select tno from teacher where depart = '计算机系'); #查出课程编号select sno,cno,degree from score where cno in (select cno from course where tno in (select tno from teacher where depart = '计算机系'));30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.select max(degree) from score where cno = '3-245'; #先把选修编号为3-245课程的同学的最高成绩查询出来select cno,sno,degree from score where cno = '3-105' and degree > (select max(degree) from score where cno = '3-245');31、查询所有教师和同学的name、sex和birthday.select tname as name,tsex as sex,tbirthday as birthday from teacherunionselect sname,ssex,sbirthday from student;32、查询所有“女”教师和“女”同学的name、sex和birthday.select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex = '女'unionselect sname,ssex,sbirthday from student where ssex = '女';33、查询所有成绩比3-105课程平均成绩低的同学的成绩表。select avg(degree) from score where cno = '3-105';select degree from score where degree