mysql查询语句

#创建数据库examination
CREATE DATABASE IF NOT EXISTS `examination`;

#使用数据库examination
USE examination;

#创建学生信息表student
CREATE TABLE IF NOT EXISTS `student` (
  `sno` VARCHAR (20) NOT NULL PRIMARY KEY COMMENT '学号',
  `sname` VARCHAR (20) NOT NULL COMMENT '姓名',
  `ssex` VARCHAR (20) NOT NULL COMMENT '性别',
  `sbirthday` DATETIME COMMENT '出生日期',
  `class` VARCHAR (20) COMMENT '班级'
) ;

#创建教师信息表teacher
CREATE TABLE IF NOT EXISTS `teacher` (
  `tno` VARCHAR (20) NOT NULL PRIMARY KEY 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 '科系'
) ;

#创建课程表course
CREATE TABLE IF NOT EXISTS `course` (
  `cno` VARCHAR (20) NOT NULL PRIMARY KEY COMMENT '课程编号',
  `cname` VARCHAR (20) NOT NULL COMMENT '课程名称',
  `tno` VARCHAR (20) NOT NULL COMMENT '授课教师编号',
  CONSTRAINT fk_course_tno FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`)
) ;

#创建成绩表score
CREATE TABLE IF NOT EXISTS `score` (
  `sno` VARCHAR (20) NOT NULL COMMENT '学生学号',
  `cno` VARCHAR (20) NOT NULL COMMENT '课程编号',
  `degree` NUMERIC (4, 1) COMMENT '成绩',
  CONSTRAINT fk_score_sno FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
  CONSTRAINT fk_score_cno FOREIGN KEY (`cno`) REFERENCES `course` (`cno`)
) ;
#------------------------------------------------------------------------------------------------

#向student表中添加数据
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (108 ,'曾华'
,'男' ,'1977-09-01','95033');
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (105 ,'匡明'
,'男' ,'1975-10-02','95031');
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (107 ,'王丽'
,'女' ,'1976-01-23','95033');
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (101 ,'李军'
,'男' ,'1976-02-20','95033');
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (109 ,'王芳'
,'女' ,'1975-02-10','95031');
INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (103 ,'陆君'
,'男' ,'1974-06-03','95031');

#向teacher表中添加数据
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','助教','电子工程系');

#向course表中添加数据
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);

#向score表中添加数据
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和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 between 60 and 80;

select * from score where degree >=60 and degree <= 80;
#5、 查询score表中成绩为85，86或88的记录。
select * from score where degree in(85,86,88);

#6、 查询student表中“95031”班或性别为“女”的同学记录。
select * from student
where class = '95031' or ssex = '女';

#7、 以class降序查询student表的所有记录。
select * from student
order by class desc;

#8、 以cno升序、degree降序查询score表的所有记录。
select * from score
order by cno, degree desc ;

#9、 查询“95031”班的学生人数。
select count(*)
from student
where class = '95031'
group by class;

#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 avg(degree)
from score
group by cno;

#12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree)
from score
group by cno
having count(*) > 5 and cno like '3%';


select count(distinct cno)
from score;

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

#14、查询所有学生的sname、cno和degree列。
select st.sname, sc.cno, sc.degree
from score sc join student st
on sc.sno = st.sno;

#15、查询所有学生的sno、cname和degree列。
select sc.sno, co.cname, sc.degree
from score sc join course co
on sc.cno = co.cno;

#16、查询所有学生的sname、cname和degree列
select st.sname, so.degree, co.cname
from student st join score so
on st.sno = so.sno
join course co
on co.cno = so.cno;

#17、 查询“95033”班学生的平均分。
select avg(degree)
from score sc join student st
on sc.sno = st.sno
where class = '95033';

#18、 假设使用如下命令建立了一个grade表：
CREATE TABLE IF NOT EXISTS `grade` (
  `low` INT (3) COMMENT '底限',
  `upp` INT (3) COMMENT '上限',
  `rank` CHAR(1) COMMENT '等级'
) ;

#向grade表中添加数据
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和rank列。
select st.sno, sc.cno, grade.rank
from student st join score sc on st.sno = sc.sno
join grade on (sc.degree > grade.low and sc.degree < grade.upp);

#19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select student.*
from student join score s on student.sno = s.sno
where cno = '3-105' and degree > (select degree from score where cno = '3-105' and sno = 109);

# 20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select *
from (select *
       from score
      where degree not in (select max(degree) from score group by cno)) t1
WHERE sno not in (select sno
                  from score
                  group by sno
                  having  count(sno)<=1)
order by sno;

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

#22、查询和学号为107的同学同年出生的所有学生的sno、sname和sbirthday列。
select sno, sname, sbirthday
from student
where sbirthday = (select sbirthday from student where sno = '107');

#23、查询“张旭“教师任课的学生成绩。
select degree
from score s join course c on s.cno = c.cno
where c.tno = (select tno from teacher where tname = '张旭');

#24、查询选修某课程的同学人数多于5人的教师姓名。
select tname
from teacher t join course c on t.tno = c.tno
join score s on c.cno = s.cno
group by s.cno
having count(*) > 5;

#25、查询95033班和95031班全体学生的记录。
select *
from student
where class = '95033' or class = '95031';

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

#27、查询出“计算机系“教师所教课程的成绩表。
select s.*
from score s join course c on c.cno = s.cno
join teacher t on t.tno = c.tno
where depart = '计算机系';

#28、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。
select tname,prof
from Teacher
where Depart in ('计算机系','电子工程系') and
Prof not in
(select prof from Teacher where Depart='电子工程系' and Prof in (select Prof from Teacher where Depart='计算机系'));

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

select cno,sno,degree
from score
where cno = '3-105' and degree >
                        (select max(degree) from score where cno = '3-245')
order by degree desc;

#30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree.
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 sname 教师和学生姓名,ssex,sbirthday
from student union select tname,tsex,tbirthday from teacher ;

#32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname 女教师和女同学姓名,ssex, sbirthday
from student
where ssex = '女' union select tname,tsex,tbirthday from teacher
where tsex = '女';

#33、 查询成绩比该课程平均成绩低的同学的成绩表。
select *
from score s
where s.degree<(select avg(degree) from score e where e.cno=s.cno group by cno);

#34、 查询所有任课教师的tname和depart.
select t.depart,t.tname
from teacher t where t.tno in(select tno from course c where c.cno in ( select cno from score group by cno));

select tname, depart
from teacher t join course c on t.tno = c.tno
join score s on c.cno = s.cno
group by s.cno;

#35 、 查询所有未讲课的教师的tname和depart.
select t.depart,t.tname
from teacher t where t.tno not in(select tno from course c where c.cno in ( select cno from score group by cno));

#36、查询至少有2名男生的班号。
select class
from student
group by class
having count(ssex = '男') > 2;

select class from student where ssex='男' group by class having count(*)>=2 ;

#37、查询student表中不姓“王”的同学记录。
select *
from student
where sname not like '王%';

#38、查询student表中每个学生的姓名和年龄。
select sname,(year(now()) - year(sbirthday))
from student;

#39、查询student表中最大和最小的sbirthday日期值。
select max(sbirthday),min(sbirthday) from student ;

#40、以班号和年龄从大到小的顺序查询student表中的全部记录。
select *
from student
order by class desc,(year(now()) - year(sbirthday)) desc, month(sbirthday),day(sbirthday);

#41、查询“男”教师及其所上的课程。
select tname,cname
from teacher t join course c on t.tno = c.tno
where t.tsex = '男';

#42、查询最高分同学的sno、cno和degree列。
select sno, cno, degree
from score
where degree = (select max(degree) from score);

#43、查询和“李军”同性别的所有同学的sname.
select sname
from student
where ssex = (select ssex from student where sname = '李军');

#44、查询和“李军”同性别并同班的同学sname.
select sname
from student
where ssex = (select ssex from student where sname = '李军') and class = (select class from student where sname = '李军');

#45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
#1
select sc.*
from score sc join student st on st.sno = sc.sno
join course c on c.cno = sc.cno
where cname = '计算机导论' and ssex = '男';
#2
select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course where cname='计算机导论');