03-查询练习

准备数据

-- 学生表
-- student、学号、姓名、性别、出生日期、所在班级
create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(20) not null,
	sbirthday datetime,
	class varchar(20)
	);

-- 教师表
-- Teacher、教师编号、教师姓名、教师性别、出生日期、职称、所在部门
create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(10) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
);

-- 课程表
-- Course、课程号、课程名称、教师编号、
create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);

-- 成绩表
-- Score、学号、课程号、成绩
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','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-10-02','95031');
insert into student values('103','王丽','女','1976-01-23','95033');
insert into student values('104','李军','男','1976-09-20','95033');
insert into student values('105','王芳','女','1975-02-10','95031');
insert into student values('106','陆君','男','1974-06-03','95031');
insert into student values('107','王尼玛','男','1976-09-20','95033');
insert into student values('108','张全蛋','女','1975-02-10','95031');
insert into student values('109','赵铁柱','男','1974-06-03','95031');

#添加教师表
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

#添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');

#成绩表
insert into score values('103','3-105','92');
insert into score values('103','3-245','86');
insert into score values('103','6-166','85');
insert into score values('105','3-105','88');
insert into score values('105','3-245','75');
insert into score values('105','6-166','79');
insert into score values('109','3-105','76');
insert into score values('109','3-245','68');
insert into score values('109','6-166','81');

image-20211013112350496

image-20211013110415317

image-20211013110355188

image-20211013112528025

查询练习

-- 1.查询student表的所有记录

mysql> select * from student;

image-20211012161300913

-- 2.查询student表的所有记录的sname、ssex和class列

mysql> select sname, ssex, class from student;

image-20211012161314363

-- 3.查询教师所有的单位即不重复的depart列

-- distinct排除重复字段
mysql> select distinct depart from teacher;

image-20211012161441766

-- 4.查询score表中成绩60到80之间的所有记录

-- 查询区间between...and...
mysql> select * from score where degree between 60 and 80;

-- 或者直接使用运算符比较
mysql> select * from score where degree > 60 and degree < 80;

image-20211012161603803

-- 5.查询score表中成绩为85，86或88的记录

-- 表示或者关系的查询 in

mysql> select * from score where degree in(85,86,88);

image-20211012161811534

-- 6.查询student表中"95031"班或性别为"女"的同学记录

-- or 表示或者  and 表示并且

mysql> select * from student where class='95031' or ssex='女';

image-20211012161912214

-- 7.以class降序查询student表的所有记录

-- 升序 asc、降序 desc

mysql> select * from student order by class desc;

image-20211012162117247

-- 8.以cno升序、degree降序查询score表的所有记录

mysql> select * from score order by cno asc, degree desc;

image-20211012162300128

-- 9.查询"95031"班的学生人数

-- 统计 count

mysql> select count(*) from student where class='95031';

image-20211012162347261

-- 10.查询score表中的最高分的学生学号和课程号（子查询或者排序）

-- 首先，找到最高分
select max(degree) from score;

-- 找到最高分的sno和cno
mysql> select sno,cno from score where degree=(select max(degree) from score);

image-20211012162450305

-- 排序的做法
mysql> select sno,cno,degree from score order by degree;

image-20211012163045523

-- limit第一个数字表示从多少开始，第二个数字是到哪位停止
mysql> select sno,cno,degree from score order by degree desc limit 0,1;

image-20211012163226710

-- 11.查询每门课的平均成绩

select * from course;
mysql> select degree from score where cno='3-105';
-- avg() 计算平均数
mysql> select avg(degree) from score where cno='3-105';

image-20211013081300127

-- 计算每门课
-- group by 分组
mysql> select avg(degree) from score group by cno;

image-20211013081626046

-- 最终结果
mysql> select cno,avg(degree) from score group by cno;

image-20211013081810702

-- 12.查询score表中至少有2名学生选修的并且以3开头的课程的平均分数

-- select cno,avg(degree),count(*) from score 
-- group by cno 			grouo by分组
-- having count(cno)>=2 	分组条件
-- and cno like '3%';		like 模糊查询

mysql> select cno,avg(degree),count(*) from score group by cno
    -> having count(cno)>=2 and cno like '3%';

image-20211013082331795

-- 13.查询分数大于70，小于90的sno列

mysql> select sno,degree from score
    -> where degree>70 and degree<90;

image-20211013082620133

mysql> select sno,degree from score
    -> where degree between 70 and 90;

image-20211013082727731

-- 14.查询所有学生的sname、cno和degree列

mysql> select sname,cno,degree from student,score
    -> where student.sno=score.sno;

image-20211013083336718

-- 所有的学生及他们的学号
mysql> select sno,sname from student;

image-20211013083510387

-- 有成绩的学生的学号、课程和成绩
mysql> select sno,cno,degree from score;

image-20211013083817537

-- 15.查询所有学生的sno、cname和degree列

mysql> select sno,cname,degree from course,score
    -> where course.cno = score.cno;

image-20211013084215447

-- 课程号和课程名
mysql> select cno,cname from course;

image-20211013084355486

-- 课程号、学号及对应的成绩
mysql> select cno,sno,degree from score;

image-20211013084509401

-- 16.查询所有学生的sname、cname和degree列

-- sname ->student
-- cname ->course
-- degree ->score

mysql> select sname,cname,degree from student,course,score
    -> where student.sno=score.sno
    -> and course.cno=score.cno;

image-20211013084926673

mysql> select sname,cname,degree,score.sno,score.cno from student,course,score
    -> where student.sno=score.sno
    -> and course.cno=score.cno;

image-20211013085038939

-- 17.查询"95031"班学生每门课的平均分

-- select * from student where class='95031';
-- select sno from student where class='95031';

mysql> select * from score where sno in(select sno from student where class='95031');

image-20211013090019562

mysql> select cno,avg(degree)
    -> from score
    -> where sno in(select sno from student where class='95031')
    -> group by cno;

image-20211013090216298

-- 18.查询选修"3-105"课程的成绩高于"109"号同学"3-105"成绩的所有同学的记录

-- select degree from score where sno='109' and cno='3-105';

mysql> select * from score
    -> where cno='3-105'
    -> and degree>(select degree from score where sno='109' and cno='3-105');

image-20211013092945620

-- 19.查询成绩高于学号为"109"、课程号为"3-105"的成绩的所有记录

mysql> select * from score
    -> where degree>(select degree from score where sno='109' and cno='3-105');

image-20211013094012737

-- 20.查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列

mysql> select * from student where sno in (108,101);

image-20211013094213832

mysql> select year(sbirthday) from student where sno in (108,101);

image-20211013094334015

mysql> select sno,sname,sbirthday from student
    -> where year(sbirthday)
    -> in (select year(sbirthday) from student where sno in (108,101));

image-20211013094618155

-- 21.查询"张旭"教师任课的学生成绩

-- 查询"张旭"教师的教师编号
select tno from teacher where tname = '张旭';
-- 查询"张旭"教师任课的课程号
select cno from course where tno=(select tno from teacher where tname = '张旭');
-- 查询"张旭"教师任课的学生成绩
mysql> select * from score where cno=(select cno from course where tno=(select tno from teacher where tname = '张旭'));

image-20211013100514982

-- 22.查询选修某课程的同学人数多于5人的教师姓名

-- 插入数据
insert into score values('101','3-105','90');
insert into score values('102','3-105','91');
insert into score values('104','3-105','89');

select tno from course where cno=(select cno from score group by cno having count(*)>5);

mysql> select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));

image-20211013102734031

-- 23.查询95033班和95031班全体学生的记录

-- 插入数据
insert into student values('110','张飞','男','1974-06-03','95038');

mysql> select * from student where class in ('95031','95033');

image-20211013103108311

-- 24.查询存在有85分以上成绩的课程Cno

mysql> select cno,degree from score where degree>85;

image-20211013103256931

-- 25.查询出"计算机系"教师所教课程的成绩表

mysql> select * from teacher where depart='计算机系';

image-20211013103421561

mysql> select cno from course where tno in (select tno from teacher where depart='计算机系');

image-20211013104158283

mysql> select * from score where cno in(select cno from course where tno in (select tno from teacher where depart='计算机系'));

image-20211013103832551

-- 26.查询"计算机系"与"电子工程系"不同职称的教师的tname和prof

select prof from teacher where depart='电子工程系';

select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系');

select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');

-- union 求并集

mysql> 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='计算机系');

image-20211013104656851

-- 27.查询选修编号为"3-105"课程且成绩至少高于选修编号为"3-245"的同学成绩的cno、sno和degree并按degree从高到低次序排序

mysql> select * from score where cno='3-245';

image-20211013104827517

mysql> select * from score where cno='3-105';

image-20211013105149208

-- 至少->大于其中至少一个, any

mysql> select * from score
    ->  where cno='3-105'
    ->  and degree>any(select degree from score where cno='3-245')
    ->  order by degree desc;

image-20211013105745186

-- 28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的cno、sno和degree

-- 且 -> all 表示所有的关系

mysql> select * from score
    ->  where cno='3-105'
    ->  and degree>all(select degree from score where cno='3-245');

image-20211013105929493

-- 29.查询所有教师和同学的name、sex和birthday

-- 别名 -> as

mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher
    -> union
    -> select sname,ssex,sbirthday from student;

image-20211013110638288

-- 30.查询所有"女"教师和"女"同学的name、sex和birthday

mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex='女'
    -> union
    -> select sname,ssex,sbirthday from student where ssex='女';

image-20211013110753399

-- 31.查询成绩比该课程平均成绩低的同学的成绩表

mysql> select cno,avg(degree) from score group by cno;

image-20211013110844138

select * from score;

image-20211013111505870

mysql> select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);

image-20211013111137130

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

-- 在课程表中安排了课程
select * from course;

image-20211013111632309

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

image-20211013111715167

-- 33.查询至少有两名男生的班号
mysql> select class from student where ssex='男'
    -> group by class
    -> having count(*)>1;

image-20211013111852685

-- 34.查询student表中不姓"王"的同学记录

mysql> select * from student where sname not like '王%';

image-20211013112001834

-- 35.查询student表中每个学生的姓名和年龄

-- 年龄=当前年份-出生年份
mysql> select year(now());

image-20211013133401814

mysql> select year(sbirthday) from student;

image-20211013133505667

mysql> select sname,year(now())-year(sbirthday) as '年龄' from student;

image-20211013133555374

-- 36.查询student表中最大和最小的sbirthday日期值

mysql> select sbirthday from student order by sbirthday;

-- max min
mysql> select max(sbirthday) as '最大',min(sbirthday) as '最小' from student;

image-20211013134016153

-- 37.以班号为年龄从大到小的顺序查询student表中的全部记录

mysql> select * from student order by class desc,sbirthday;

image-20211013134310484

-- 38.查询"男"教师及其所上的课程

mysql> select * from teacher where tsex='男';

image-20211013134413055

mysql> select * from course where tno in(select tno from teacher where tsex='男');

image-20211013134520476

-- 39.查询最高分同学的sno、cno和degree列

mysql> select max(degree) from score;
mysql> select * from score where degree=(select max(degree) from score);

image-20211013134631398

-- 40.查询和"李军"同性别的所有同学的sname

mysql> select ssex from student where sname='李军';

image-20211013135010293

mysql> select sname from student where ssex=(select ssex from student where sname='李军');

image-20211013135052338

-- 41.查询和"李军"同性别并同班的同学sname

mysql> select sname from student
    -> where ssex=(select ssex from student where sname='李军')
    -> and class=(select class from student where sname='李军');

image-20211013135342911

-- 42.查询所有选修"计算机导论"课程和"男"同学的成绩表

mysql> select * from student where ssex='男';

image-20211013135550303

mysql> select * from course where cname='计算机导论';

image-20211013135619274

mysql> select * from score
    -> where cno=(select cno from course where cname='计算机导论')
    -> and sno in (select sno from student where ssex='男');

image-20211013135651810

-- 43.使用如下命令建立了一个grade表,查询所有同学的sno、cno和grade列

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');

mysql> select sno,cno,grade from score,grade where degree between low and upp;

image-20211013135933243

posted @ 2021-10-14 16:21  萘汝  阅读(96)  评论(0)  编辑  收藏  举报
我发了疯似的祝你好!