mysql查询

–mysql查询练习.sql

–学生表
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbir datetime,
class varchar(20)
);

–教师表
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbir datetime,
prof varchar(20)not null,
depart varchar(20)not null
);
–课程表
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20)not null,
foreign key(tno) references teacher(tno)
);

–成绩表
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno)references student(sno),
foreign key(cno)references course(cno),
primary key(sno,cno)
);

–添加数据
#添加学生信息
insert into student values(‘101’,‘张三’,‘男’,‘1997-09-14’,‘54545’);
insert into student values(‘102’,‘李四’,‘男’,‘1987-05-14’,‘54545’);
insert into student values(‘103’,‘王五’,‘女’,‘1987-03-04’,‘58745’);
insert into student values(‘104’,‘赵六’,‘男’,‘1998-02-14’,‘54965’);
insert into student values(‘105’,‘王芳’,‘女’,‘1999-10-01’,‘54545’);
insert into student values(‘106’,‘李俊’,‘男’,‘1996-03-05’,‘54536’);
insert into student values(‘107’,‘王尼玛’,‘男’,‘1968-01-14’,‘54335’);
insert into student values(‘108’,‘张全蛋’,‘女’,‘1979-10-01’,‘45995’);
insert into student values(‘109’,‘赵铁柱’,‘男’,‘1966-03-05’,‘54116’);

–添加教师数据
insert into teacher values(‘801’,‘李承’,‘男’,‘1977-12-02’,‘副教授’,‘计算机系’);
insert into teacher values(‘802’,‘张旭’,‘男’,‘1987-06-02’,‘讲师’,‘电子工程系’);
insert into teacher values(‘803’,‘王金平’,‘女’,‘1996-02-02’,‘助教’,‘计算机系’);
insert into teacher values(‘804’,‘刘冰冰’,‘女’,‘1987-07-05’,‘助教’,‘电子工程系’);

–课程表数据
insert into course values(‘3-105’,‘计算机导论’,‘801’);
insert into course values(‘3-245’,‘操作系统’,‘802’);
insert into course values(‘6-166’,‘数字电路’,‘803’);
insert into course values(‘9-888’,‘高等数学’,‘804’);

–成绩表
insert into score values(‘103’,‘3-245’,‘86’);
insert into score values(‘105’,‘3-245’,‘75’);
insert into score values(‘109’,‘3-245’,‘68’);
insert into score values(‘103’,‘3-105’,‘92’);
insert into score values(‘105’,‘3-105’,‘88’);
insert into score values(‘109’,‘3-105’,‘76’);
insert into score values(‘103’,‘6-166’,‘64’);
insert into score values(‘105’,‘6-166’,‘91’);
insert into score values(‘109’,‘6-166’,‘78’);
insert into score values(‘103’,‘9-888’,‘85’);
insert into score values(‘105’,‘9-888’,‘79’);
insert into score values(‘109’,‘9-888’,‘81’);

–查询练习
1.查询student表的所有记录
select * from student;

2.查询指定的列的数据
select sname,ssex,class from student;

3.查询教师所有的单位即不重复的depart列
distinct 排除重复
select distinct depart from teacher;

4.查询成绩在60到80之间的记录
–查询区间 between … and …;
select * from score where degree between 60 and 80;
或者
select * from score where degree>60 and degree<80;

5.查询成绩为85、86或者88的记录
–表示或的关系 in关键字
select * from score where degree in(85,86,88);

6.查询student表中“54245”班或者性别为女的同学记录
–or关键字
select * from student where class=“54245” or ssex=“女”;

7.以class降序查询student表的所有数据
–升序(asc)(默认)、降序(desc)
select * from student order by class desc;

8.以cno升序，degree降序查询score表的所有数据
select * from score order by cno asc,degree desc;

9.查询“45455”班的学生人数
–统计count
select count(*) from student where class=“45455”;

10.查询score表中的最高分的学生学号和课程号 (子查询或者排序)
select sno,cno from score where degree=(select max(degree) from score);

–排序的写法(存在缺点，如果有两个最高分会出错)
select sno,cno from score order by degree desc limit 0,1

11.查询每门课的平均成绩
select * from course;

–avg()
select avg(degree) from score where cno=“3-105”;

在一个sql语句里面写
group by 分组
select cno,avg(degree) from score group by cno;

12.查询score表中至少有两名学生选修并以3开头的课程的平均分数
like 模糊查询
select cno,avg(degree), count(*) from score group by cno having count(cno)>=2 and cno like ‘3%’;

13.查询分数大于70，小于90的sno列
select sno,degree from score where degree>70 and degree<90;

14.查询所有学生的sname,cno,degree列
select sname,cno,degree from student ,score where student.sno=score.sno;

15。查询所有学生的sno,cname,degree列
select sno,cname,degree from course,score where course.cno=score.cno;

16.查询所有学生的sname,cname,degree
select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

再增加几个字段
select sname,cname,degree,student.sno as stu_sno,score.sno,course.cno as cou_cno,score.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;

17.查询“58745”班的学生每门课的平均分
select sno from student where class=“58745”;
select * from score where sno in (select sno from student where class=“58745”);
select cno,avg(degree) from score where sno in(select sno from student where class=“58745”) group by cno;

–avg()要与group by搭配使用

18.查询选修“3-105”课程的成绩高于“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”) and cno=“3-105”;

19.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score where degree >(select degree from score where sno=“109” and cno=“3-105”);

20.查询和学号为108,101的同学同年出生的所有学生的sno,sname,sbir列
select * from student where sno in (101,108);
select year(sbir) from student where sno in (101,108);
–year可以提取时间的年份的数字
select * from student where year(sbir) in (select year(sbir) from student where sno in (101,108));

21.查询“张旭”教师任课的学生的成绩(多层嵌套的子查询)
select tno from teacher where tname=“张旭”;
select cno from course where tno=(select tno from teacher where tname=“张旭”);
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname=“张旭”));

22.查询选修某课程的同学人数多于5人的教师姓名；
select cno from score group by cno having count()>5;–查询课程数大于五的课程号
select * from teacher;
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));

23.查询“54545”和“58745”班的全体学生的记录
select * from student where class in (‘54545’,‘58745’);

24.查询存在有85分以上成绩的课程cno;
select cno from score where degree>85;

25.查询出“计算机系”教师所教课程的成绩表
select * from teacher where depart=“计算机系”;
select * from course where tno in(select tno from teacher where depart=“计算机系”);
select * from score where cno in(select cno from course where tno in(select tno from teacher where depart=“计算机系”));

26.查询计算机系与电子工程系不同职称教师的tname和prof
–union 求两个语句的并集
select * from teacher where depart=“计算机系” and prof not in(select prof from teacher where depart=‘电子工程系’)
union
select * from teacher where depart=“电子工程系” and prof not in(select prof from teacher where depart=‘计算机系’);

27.查询选修编号为“3-105“的课程且成绩至少高于选修编号为”3-245“的同学的cno,sno,degree
–至少就要用any
select * from score
where cno=‘3-105’
and degree>any(select degree from score where cno=‘3-245’)
order by degree desc;

28.查询选修编号为”3-105“且成绩高于选修编号”3-245“课程的同学的cno,sno.degree
–且就要用all
select * from score
where cno=‘3-105’
and degree>all(select degree from score where cno=‘3-245’);

29.查询所有教师和同学的name,sex,birthday
–别名
select tname as name,tsex as sex,tbir as birthday from teacher
union
select sname,ssex,sbir from student;

30.查询所有女教师和女同学的name,sex,birthday
select tname as name,tsex as sex,tbir as birthday from teacher where tsex=‘女’
union
select sname,ssex,sbir from student where ssex=‘女’;

31.查询成绩比该课程平均成绩低的同学的成绩表
select cno ,avg(degree) from score group by cno;

select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
–a表是全部的成绩，b表是平均成绩，用a表的成绩和b表相互比较

32.查询所有任课教师的tanme和depart

–课程表里安排了课程
select * from course;

select tname,depart from teacher where tno in(select tno from course);

33.查询至少有两名男生的班号（group by 后面的字段和要搜索的字段一致）
select class from student where ssex=‘男’ group by class having count(*)>1;

34.查询student表中不姓王的同学记录
select * from student where sname not like ‘王%’;

35.查询student表中每个学生的姓名和年龄
–year(now())现在的年份
select sname,year(now())-year(sbir) as ‘年龄’ from student;

36.查询student表中最大和最小sbir的值
select max(sbir) as ‘最大’,min(sbir) as ‘最小’ from student;

37.以班号和年龄从大到小的顺序查询student表中的全部记录
–排序优先级，先排序班级，在排序年龄
select * from student order by class desc,sbir;

38.查询“男”教师及其所上的课程
select * from course where tno in(select tno from teacher where tsex=‘男’);

39.查询最高分同学的sno,cno和degree
select max(degree) from score;
select * from score where degree=(select max(degree) from score);

40.查询和“李军”同性别的所有学生的sname
select ssex from student where sname=‘李俊’;
select sname from student where ssex=(select ssex from student where sname=‘李俊’);

41.查询和“李俊”同性别且同班的同学sname;
select sname from student where ssex=(select ssex from student where sname=‘李俊’) and class=(select class from student where sname=‘李俊’);

42.查询所有选修“计算机导论”课程的“男”同学的成绩表
select * from score
where cno=(select cno from course where cname=‘计算机导论’)
and sno in(select sno from student where ssex=‘男’);

43.创建一个等级表
create table grade(
low int(3),
upp int(3),
grade 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’);
查询所有同学的sno,cno,grade
select sno,cno,grade from score,grade where degree between low and upp;