# 创建数据库
create database if not exists school;
# 显示当前服务器中所有的数据库
show databases;
# 显示某个数据库里所有的表格
show tables from 数据库名称;
show tables from demo;
# 显示某个表中所有的列
show columns from 表名称 from 数据库名称;
show columns from info from demo;
# 显示最后一个执行的语句所产生的警告和通知
show warnings;
# 显示最后一个执行语句所产生的的警告和通知
show errors;
# 使用数据库
use 数据库名称;
use school;
# 删除数据库
drop database if exists 数据库名称;
# 创建学生表
create table student(
sno varchar(20) not null,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
sclass varchar(20) not null);
# 创建教师表
create table teacher(
tno varchar(20) not null,
tname varchar(20) not null,
tsex varchar(20) not null default'男',
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null);
# 创建课程表
create table course(
cno varchar(20) not null,
cname varchar(20) not null,
tno varchar(20) not null);
# 创建成绩表
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal(4,1) not null);
# 添加约束
alter table student add constraint primary key(sno);
alter table teacher add constraint primary key(tno);
alter table course add constraint primary key(cno);
alter table course add constraint foreign key(tno) references teacher (tno);
alter table score add constraint primary key(sno, cno);
alter table score add constraint foreign key(sno) references student (sno);
alter table score add constraint foreign key(cno) references course (cno);
# 插入数据
# 学生表
insert into student(sno,sname,ssex,sbirthday,sclass)
values(108,'曾华','男','1977-09-01',95033);
insert into student(sno,sname,ssex,sbirthday,sclass)
values(105,'匡明','男','1975-10-02',95031);
insert into student(sno,sname,ssex,sbirthday,sclass)
values(107,'王丽','女','1976-01-23',95033);
insert into student(sno,sname,ssex,sbirthday,sclass)
values(101,'李军','男','1976-02-20',95033);
insert into student(sno,sname,ssex,sbirthday,sclass)
values(109,'王芳','女','1975-02-10',95031);
insert into student(sno,sname,ssex,sbirthday,sclass)
values(103,'陆君','男','1974-06-03',95031);
# 教师表
insert into teacher(tno,tname,tsex,tbirthday,prof,depart)
values(804,'李诚','男','1958-12-02','副教授','计算机系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart)
values(856,'张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart)
values(825,'王萍','女','1972-05-05','助教','计算机系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart)
values(831,'刘冰','女','1977-08-14','助教','电子工程系');
# 课程表
insert into course(cno,cname,tno)
values('3-105','计算机导论',825);
insert into course(cno,cname,tno)
values('3-245','操作系统',804);
insert into course(cno,cname,tno)
values('6-166','数据电路',856);
insert into course(cno,cname,tno)
values('9-888','高等数学',831);
# 成绩表
insert into score(sno,cno,degree) values(103,'3-245',86);
insert into score(sno,cno,degree) values(105,'3-245',75);
insert into score(sno,cno,degree) values(109,'3-245',68);
insert into score(sno,cno,degree) values(103,'3-105',92);
insert into score(sno,cno,degree) values(105,'3-105',88);
insert into score(sno,cno,degree) values(109,'3-105',76);
insert into score(sno,cno,degree) values(101,'3-105',64);
insert into score(sno,cno,degree) values(107,'3-105',91);
insert into score(sno,cno,degree) values(108,'3-105',78);
insert into score(sno,cno,degree) values(101,'6-166',85);
insert into score(sno,cno,degree) values(107,'6-166',79);
insert into score(sno,cno,degree) values(108,'6-166',81);
# 1. 查询student表中的sname、ssex和sclass列
select sname, ssex, sclass
from student;
# 2. 查询student表中的所有记录
select *
from student;
# 3. 查询teacher表中不重复的depart(系名)
select distinct depart
from teacher;
# 4. 查询teacher表中不同的系名，以及每个系有多少老师
select depart, count(*)
from teacher
group by depart;
# 5. 查询score表中成绩在60到80之间的所有记录
select *
from score
where degree between 60 and 80;
# 6.查询score中成绩为85，86或88的记录
# 方法一
select *
from score
where degree in (85,86,88);
# 方法二
select *
from score
where degree=85 or degree=86 or degree=88;
# 7. 查询student表中95031班或性别为女的同学记录
# 方法一
select *
from student
where sclass='95031' or ssex='女';
# 方法二
select * from student where sclass='95031'
union
select * from student where ssex='女';
# 8. 查询95033和95031班全体学生的记录
select *
from student
where sclass = '95033' or sclass = '95031';
# 9. 查询student表中不姓王的同学记录
select *
from student
where sname not like '王%';
# 10. 以class降序查询student表的所有记录
select *
from student
order by sclass desc;
# 11. 以cno升序、degree降序查询score表的所有记录
select *
from score
order by cno asc, degree desc;
# 12. 以班号和年龄从大到小的顺序查询student表中的全部记录
select *
from student
order by sclass desc, sbirthday asc;
# 13. 查询95031班的学生人数
select count(*)
from student
where sclass='95031';
# 14. 查询student表中最大和最小的sbirthday日期值
select max(sbirthday), min(sbirthday)
from student;
# 15. 查询存在有85分以上成绩的课程cno
select cno
from score
group by cno
having max(degree) > 85;
# 16. 查询score表中的最高分的学生学号和课程号
select sno, cno
from score
where degree = (select max(degree) from score);
# 17. 查询3-105号课程的平均分
select avg(degree)
from score
where cno = '3-105';
# 18. 查询score表中至少有5名学生选修的并以3开头的课程的平均分数
select avg(degree)
from score
where cno like '3%'
group by cno
having count(*) >= 5;
# 19. 查询最低分大于70， 最高分小于90的sno
select sno
from score
group by sno
having min(degree)>70 and max(degree)<90;
# 20. 查询所有学生的姓名（sname)、课程编号（cno）和成绩（degree）
select s.sname, sc.cno, sc.degree
from student s
inner join score sc
on s.sno = sc.sno;
# 21. 查询所有学生的姓名（sname）、课程名（cname）和成绩（degree)
# 方法一
select s.sname, c.cname, sc.degree
from student s
inner join score sc
on s.sno = sc.sno
inner join course c
on sc.cno = c.cno;
# 方法二
select s.sname, c.cname, sc.degree
from student s, score sc, course c
where s.sno = sc.sno and sc.cno = c.cno;
# 22. 查询张旭教师任课的学生成绩
# 方法一
select degree
from score
where cno = (select cno from course where tno =
(select tno from teacher where tname='张旭'));
# 方法二
select sc.degree
from teacher t
inner join course c
inner join score sc
on t.tno = c.tno and c.cno = sc.cno
where tname='张旭';
# 23. 查询同一个系中，不同职称的教师的tname和prof
select a.tname, b.prof
from teacher a
inner join teacher b
on a.depart = b.depart and a.prof <> b.prof;
# 24. 查询出计算机系教师所教课程的成绩
# 方法一
select *
from score
where cno in (select cno from course
where tno in
(select tno from teacher where depart = '计算机系'));
# 备注：单行查询用"=",多行查询"in"
# 方法二
select sc.degree
from score sc
inner join course c
inner join teacher t
on sc.cno = c.cno and c.tno = t.tno
where t.depart = '计算机系';
# 创建grade表
create table grade(
low int(3),
upp int(3),
s_rank char(1));
insert into grade value(90,100,'A');
insert into grade value(80,89,'B');
insert into grade value(70,79,'C');
insert into grade value(60,69,'D');
insert into grade value(0,59,'E');
# 25. 查询所有同学的sno、cno和s_rank列
select s.sno, sc.cno, grade.s_rank
from student s
inner join score sc
inner join grade
on s.sno = sc.sno and sc.degree between low and upp;
# 27. 查询成绩高于学号为109、课程号为3-105的成绩的所有记录
select a.*
from score a
where a.degree > (select b.degree from score b
where b.sno='109' and b.cno='3-105');
# 主查询和子查询涉及同一张表，考虑自连接
# 28. 查询选修3-105课程的成绩高于109号同学成绩的所有同学的sname,cno
select s.sname, sc.cno, sc.degree
from student s
inner join score sc
on s.sno = sc.sno
where sc.cno='3-105'
and sc.degree > (select sc.degree from score sc
where sc.cno='3-105' and sc.sno='109');
# 29. 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
select a.*
from score a
where a.sno in (select b.sno from score b group by b.sno having count(*) > 1)
and a.degree <> (select max(b.degree) from score b where a.cno=b.cno);
# 30. 查询和学号为107的同学同年出生的所有学生的sno,sname和sbirthday
select a.sno, a.sname, a.sbirthday
from student a
where year(a.sbirthday) = (select year(b.sbirthday) from student b
where b.sno='107');
# 31. 查询选修某课程的同学人数多于5人的教师姓名
select t.tname
from teacher t
inner join course c
on t.tno = c.tno
where c.cno in (select cno from score
group by cno having count(*)>5);
# 32. 查询选修编号为3-105课程且成绩至少高于选修编号为3-245的cno、sno和degree，并按degree从高到低次序排
select a.cno, a.sno, a.degree
from score a
where a.cno = '3-105'
and a.degree > (select max(b.degree) from score b where b.cno='3-245')
order by a.degree desc;
# 33. 查询所有女教师和女同学的name、sex和birthday
select tname name, tsex sex, tbirthday birthday from teacher where tsex='女'
union all
select sname name, ssex sex, sbirthday birthday from student where ssex='女';
# 34. 查询成绩比该课程平均成绩低的同学的成绩
select a.*
from score a
where a.degree < (select avg(b.degree) from score b);
# 35. 查询所有任课教师的tname和depart
# 方法一
select tname, depart
from teacher
where tno in (select tno from course);
# 方法二
select t.tname, t.depart
from teacher t
inner join course c
on t.tno = c.tno;
# 36. 查询至少有2名男生的班号
select sclass
from student
where ssex = '男'
group by sclass
having count(*) >= 2;
# 37. 查询男教师及其所上的课程
# 方法一
select t.tname, c.cname
from teacher t
inner join course c
on t.tno = c.tno
where t.tsex = '男';
# 方法二
select t.tname, c.cname
from teacher t, course c
where t.tno = c.tno and t.tsex='男';
# 38. 查询和李军同性别的所有同学sname
select a.sname
from student a
where a.ssex = (select b.ssex from student b where b.sname='李军');
# 39. 查询和李军同性别并同班的同学sname
select a.sname
from student a
where a.ssex = (select b.ssex from student b where b.sname='李军')
and a.sclass = (select c.sclass from student c where c.sname='李军');
# 40. 查询所有选修计算机导论课程的男同学的成绩信息
# 方法一
select sc.*
from student s inner join score sc inner join course c
on s.sno = sc.sno and sc.cno = c.cno
where c.cname = '计算机导论' and s.ssex='男';
# 方法二
select *
from score
where cno = (select cno from course where cname = '计算机导论')
and sno in (select sno from student where ssex = '男');
