MySQL练习

建库

create database demo01;

建表

use demo01;

CREATE TABLE students(    
    sno       VARCHAR(3) NOT NULL,
    sname     VARCHAR(4) NOT NULL,
    ssex      VARCHAR(2) NOT NULL,
    sbirthday DATETIME,
    class     VARCHAR(5)
)


CREATE TABLE courses(
    cno       VARCHAR(5)  NOT NULL,
    cname     VARCHAR(10) NOT NULL,
    tno       VARCHAR(10) NOT NULL
 )


CREATE TABLE scores(
    sno       VARCHAR(3)     NOT NULL,
    cno       VARCHAR(5)     NOT NULL,
    degree    NUMERIC(10, 1) NOT NULL
)

CREATE TABLE teachers(
    tno        VARCHAR(3)  NOT NULL,
    tname      VARCHAR(4)  NOT NULL, 
    tsex       VARCHAR(2)  NOT NULL,
    tbirthday  DATETIME    NOT NULL, 
    prof       VARCHAR(6),
    depart     VARCHAR(10) NOT NULL
)

插入数据

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

题目

## 查询Student表中的所有记录的Sname、Ssex和Class列
select sname,ssex,class from students;

## 查询教师所有的单位即不重复的Depart列
select distinct depart from teachers;

## 查询Student表的所有记录
select * from students;

## 查询Score表中成绩在60到80之间的所有记录
select * from scores where degree between 60 and 80;

## 查询Score表中成绩为85，86或88的记录
select * from scores where degree in(85,86,88);

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

## 查询以Class降序查询Student表的所有记录
select * from students order by class desc;

## 查询以Cno升序、Degree降序查询Score表的所有记录
select * from scores order by cno asc,degree desc;

## 查询“95031”班的学生人数
select count(*) as sum from students where class = '95031';  #本身where 就是进行分组，所以可以进行聚合

## 查询Score表中的最高分的学生学号和课程号
select sno,cno from scores order by degree desc limit 1;

## 查询‘3-105’号课程的平均分
select avg(degree) from scores where cno='3-105';

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

## 查询最低分大于70，最高分小于90的Sno列
select sno from scores group by sno having max(degree)<=90 and min(degree)>=70;

## 查询所有学生的Sname、Cno和Degree列
select st.sname, sc.cno,sc.degree from students st left join scores sc on st.sno=sc.sno;

## 查询所有学生的Sno、Cname和Degree列
select sc.sno,co.cname,sc.degree from scores sc left join courses co on sc.cno = co.cno;

## 查询所有学生的Sname、Cname和Degree列
select st.sname,co.cname,sc.degree from students st left join scores sc on st.sno = sc.sno left join courses co on sc.cno = co.cno;

## 查询“95033”班所选课程的名称及课程平均分
select co.cname,avg(sc.degree) from courses co  join scores sc on co.cno = sc.cno  join students st on sc.sno = st.sno group by cname,class having class = '95033';

## 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
select * from students st left join scores sc on st.sno = sc.sno where cno = '3-105' and degree>(select degree from scores where sno ='109' and cno='3-105');

## 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
select * from scores group by sno having count(cno)>1 and degree != max(degree);

## 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列
select sno,sname,sbirthday from students where sbirthday = (select sbirthday from students where sno = '108');

## 查询“张旭“教师任课的学生成绩
select sc.degree,co.cname,st.sname,te.tname from scores sc left join courses co on sc.cno = co.cno left join students st on sc.sno = st.sno left join teachers te on co.tno = te.tno where te.tname = '张旭';

## 查询选修某课程的同学人数多于5人的教师姓名
select te.tname  from teachers te left join courses co on te.tno = co.tno left join scores sc on co.cno = sc.cno group by sc.cno having count(sc.cno)>5;

## 查询95033班和95031班全体学生的记录
select * from students where class in ('95033','95031');

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

## 查询出“计算机系“教师所教课程的成绩表
select degree , depart,cname from scores sc join courses c on sc.cno = c.cno join teachers t on c.tno = t.tno where t.depart = '计算机系';

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

## 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序
select cno,sno,degree from scores  having cno = '3-105' and degree > (select min(degree) from scores where cno= '3-245')order by degree desc ;

## 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select s1.sno,s1.cno,s1.degree from scores s1 join scores s2 on s1.sno = s2.sno where s1.degree>s2.degree and s1.cno = '3-105'and s2.cno='3-245';

## 查询所有教师和同学的name、sex和birthday.
select sname name,ssex sex,sbirthday birthday from students union select tname,tsex,tbirthday from teachers;

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

## 查询成绩比该课程平均成绩低的同学的科目和成绩表
select sno,s1.cno,degree from scores s1 join (select cno,avg(degree) avg from scores group by cno) s2 on s1.cno = s2.cno and s1.degree < s2.avg;

## 查询所有任课教师的Tname和Depart.
select tname ,depart from teachers join courses c on teachers.tno = c.tno

## 查询至少有2名男生的班号
select class from students group by class,ssex having count(ssex)>=2 and ssex = '男';

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

## 查询Student表中每个学生的姓名和年龄
select sname ,year(now())-year(sbirthday) as age from students;

## 查询Student表中最大和最小的Sbirthday日期值
select max(date (sbirthday)) ,min(date (sbirthday)) from students;

## 查询以班号和年龄从大到小的顺序查询Student表中的全部记录
select * from students order by class desc,sbirthday asc ;

## 查询“男”教师及其所上的课程
select tname,cname from teachers left join courses c on teachers.tno = c.tno where tsex = '男';

## 查询最高分同学的Sno、Cno和Degree列
select * from scores order by degree desc limit 1;

## 查询各科最高分同学的Sno、Cno和Degree列
select * from scores group by cno having degree >=max(degree);

## 查询和“李军”同性别的所有同学的Sname.
select sname from students where ssex = (select ssex from students where sname ='李军') and sname !='李军';

## 查询和“李军”同性别并同班的同学Sname.
select sname from students t1 join (select ssex,class from students where sname = '李军') t2 on t1.ssex = t2.ssex and t1.class = t2.class and sname != '李军';

## 查询所有选修“计算机导论”课程的“男”同学的成绩表
select sc.sno,sc.cno,sc.degree from scores sc join courses c on sc.cno = c.cno join students s on sc.sno = s.sno where cname = '计算机导论' and ssex = '男';