【SQL】MySQL之SQL语句练习

最新推荐文章于 2024-04-14 11:10:08 发布

有梦想的水手啊

最新推荐文章于 2024-04-14 11:10:08 发布

阅读量116

点赞数

分类专栏： SQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_45806009/article/details/114692635

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这篇是在网上找的关于SQL查询语句的练习，下面是我在MySQL上实现的代码。

练习题的链接（博客园-翟喵儿）: link.

有些题由于对题意的理解不同，所以和链接里的答案不太相同。

下面开始放代码：

表格建立和数据输入

#创建表格
create table student (
sno char(3) not null primary key,
sname char(8) not null,
ssex char(2) not null,
sbirthday datetime,
class char(5)
);
create table teacher(
tno char(3) not null primary key,
tname char(4) not null,
tsex char(2) not null,
tbirthday datetime,
prof char(6),
depart varchar(10) not null
);
create table course(
cno char(5) not null primary key,
cname char(10) not null,
tno char(3) not null,
foreign key(tno) references teacher(tno)
);
create table score(
sno char(3) not null,
cno char(5) not null,
degree decimal(4,1),
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);

#插入数据
insert into student values
('108','曾华','男','1977-09-01','95033'),
('105','匡明','男','1975-10-02','95031'),
('107','王丽','女','1976-01-23','95033'),
('101','李军','男','1976-02-20','95033'),
('109','王芳','女','1975-02-10','95031'),
('103','陆君','男','1974-06-03','95031');
insert into teacher values
('804','李诚','男','1958-12-02','副教授','计算机系'),
('856','张旭','男','1969-03-12','讲师','电子工程系'),
('825','王萍','女','1972-05-05','助教','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系');
insert into course values
('3-105','计算机导论','825'),
('3-245','操作系统','804'),
('6-166','数字电路','856'),
('9-888','高等数学','831');
insert into score 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);

查询

#1
select sname,ssex,class from student;
#2
select distinct depart from teacher;
#3
select * from student;
#4
select * from score where degree between 60 and 80;
#5
select * from score where degree in(85,86,88);
#6
select * from  student where class='95031' or ssex='女';
#7
select * from student order by class desc;
#8
select * from score order by cno,degree desc;
#9
select count(*) from student where class='95031';
#10
select sno,cno from score where degree=(select max(degree) from score);
#11
select cno,avg(degree) from score group by cno;
#12
select cno,avg(degree) from score 
group by cno having count(*)>=5 and cno like '3%'; 
#13
select distinct sno from score where degree>70 and degree<90;
#14
select sname,cno,degree from score,student
where score.sno=student.sno;
#15
select sno,cname,degree from score,course where course.cno=score.cno;
#16
select sname,cname,degree from score,course,student
where course.cno=score.cno and student.sno=score.sno;
#17
select cno,avg(degree) from score,student 
where score.sno=student.sno and class='95033' group by cno;
#18 由于rank在MySQL是保留字，不能作列名，使用r_ank
create table grade(low int(3),upp int(3),r_ank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
select sno,cno,r_ank from score,grade where degree>=low and degree<=upp;
#19
select * from score 
where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
#20 放弃，实在不理解题目是要干什么
#21
select * from score where degree>(
select degree from score where sno='109' and cno='3-105');
#22
select sno,sname,sbirthday from student where year(sbirthday)=(
select year(sbirthday) from student where sno='108');
#23
select sno,degree from score where cno in(
select cno from course where tno in(
select tno from teacher where tname='张旭'));
#24
select tname from teacher where tno in(
select tno from course where cno in(
select cno from score group by cno having count(*)>5));
#25
select * from student where class='95033' or class='95031';
#26
select distinct cno from score where exists(
select * from score where degree>85); 
#27
select * from score where cno in(
select cno from course where tno in(
select tno from teacher where depart='计算机系'));
#28 即去掉职称相同的行
select * from teacher where prof not in(
select prof from teacher where depart='计算机系'and prof in(
select prof from teacher where depart='电子工程系'));
#29
select * from score where cno='3-105' and degree>(
select min(degree) from score where cno='3-245');
#30
select * from score where cno='3-105' and degree>(
select max(degree) from score where cno='3-245');
#31
select sname name,ssex sex, sbirthday birthday from student
union
select tname name,tsex sex, tbirthday birthday from teacher;
#32
select sname name,ssex sex, sbirthday birthday from student where ssex='女'
union
select tname name,tsex sex, tbirthday birthday from teacher where tsex='女';
#33
select * from score a where degree<(select avg(degree) from score b group by cno having a.cno=b.cno);
#34
select tname,depart from teacher where tno in(
select tno from course);
#35
select tname,depart from teacher where tno not in(
select tno from course);
#36
select class from student where ssex='男' group by class having count(*)>=2;
#37
select * from student where sname not like'王%';
#38
select sname,year(now())-year(sbirthday) age from student;
#39
select max(sbirthday),min(sbirthday) from student;
#40
select * from student order by class desc, year(now())-year(sbirthday) desc;
#41
select tname,cname from teacher,course 
where teacher.tno=course.tno and tsex='男';
#42
select sno,cno,max(degree) from score group by cno;
#43
select sname from student where ssex=(
select ssex from student where sname='李军');
#44
select sname from student where ssex=(
select ssex from student where sname='李军')
and class=(select class from student where sname='李军');
#45
select * from score,student where score.sno=student.sno
and ssex='男' and cno=(
select cno from course where cname='计算机导论');