MySQL练习

修改1

第十八题没有理解题意，已改正。

修改2
第七题以改正。

---

create database db1;
use db1;
show tables ;
# 创建学生表
create table student(
    sid int primary key AUTO_INCREMENT comment '学生编号',
    sname varchar(8) not null comment '学生姓名',
    sgender varchar(6) not null comment '学生性别',
    sbirthday datetime comment '学生生日',
    c_id int(4) comment '学生班级编号'
) comment '学生表';
# 创建教师表
create table teacher(
    tid int primary key AUTO_INCREMENT comment '教师编号',
    tname varchar(8) not null comment '教师姓名',
    tgender varchar(6) not null comment '教师性别',
    tbirthday datetime comment '教师生日',
    tprof varchar(10) not null comment '教师岗位',
    tdepart varchar(12) not null comment '教师系别'
) comment  '教师表';
# 创建课程表
create table course(
    cid int primary key AUTO_INCREMENT comment '课程编号',
    cname varchar(18) not null comment '课程名称',
    t_id int comment '课程教师编号'
)comment '课程表';

# 创建成绩表
create table score(
    id int comment '学生编号',
    c_id int comment '课程编号',
    degree int comment '分数'
)comment '成绩表';

# 插入学生表数据
insert into
    student
values
    (108 ,'曾华' ,'男' ,'1977-09-01',95033),
    (105 ,'匡明' ,'男' ,'1975-10-02',95031),
    (107 ,'王丽' ,'女' ,'1976-01-23',95033),
    (101 ,'李军' ,'男' ,'1976-02-20',95033),
    (109 ,'王芳' ,'女' ,'1975-02-10',95031),
    (103 ,'陆君' ,'男' ,'1974-06-03',95031);


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

# 插入课程表数据
insert into
    course
values
     (1 ,'计算机导论',825),
     (2,'操作系统' ,804),
     (3 ,'数据电路' ,856),
     (4 ,'高等数学' ,831);

# 插入成绩表数据
insert into
    score
values
    (103,2,86),(105,2,75),(109,2,68),(103,1,92),(105,1,88),
    (109,1,76),(101,1,64),(107,1,91),(108,1,78),(101,3,85),
    (107,4,79),(108,3,81);

# 3、查询Student表中的所有记录的Sname、Sgender和c_id列。
select sname,sgender,c_id from student;

# 4、查询教师表中所有的系别即不重复的tdepart列。
select distinct tdepart from teacher;   # 去重关键字：DISTINCT需要添加在所查字段的最前方

# 5、查询Student表的所有记录。
select * from student;

# 6、查询Score表中成绩在60到80之间的所有记录。
select * from score where degree >= 60 and degree <= 80;

# 7、查询Score表中成绩为85，86或88的记录。
select ~~degree as '分数'~~  from score where degree IN(85,86,88);	# 错误
select * from score where degree IN(85,86,88);	# 正确

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

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

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

# 11、查询“95031”班的学生人数。
select count(*) as '人数' from student where c_id=95031;

# 12、查询Score表中的最高分的学生学号和课程号。
select
    id as '学生编号',
    c_id as '课程编号'
from
    score
where
    (select
        max(degree)
    from
        score
    ) = degree;

# 13、查询每门课的平均成绩。
select
    co.cname as '课程编号' , avg(sc.degree) as '平均分数'
from
    score as sc
inner join
    course as co
on
    sc.c_id = co.cid
group by
    sc.c_id;

# 14、查询“95033”班学生的平均分。
select
    avg(se.degree) as '平均分'
from
    score as se
join
    student as st
on
    st.c_id=95033;

# 15、查询“张旭“教师任课的学生成绩。
select
    sc.degree as '学生成绩'
from
    teacher as te
inner join
    course as co
on
    te.tname='张旭' and te.tid=co.t_id
left join
    score as sc
on
    co.cid=sc.c_id;

# 16、查询95033班和95031班全体学生的记录
select
    *
from
    student
where
    c_id IN (95033,95031);

# 17、查询存在有85分以上成绩的课程编号
select
    distinct co.cid
from
    course as co
join
    score as sc
on
    sc.degree > 85;

# 18、查询至少有2名男生的班号

# 错误，不符合题意
~~select
    student.c_id as '班级编号',
    count(sgender) as number
from
    student
group by
    c_id
having
    number > 2;~~ 
   
# 正确
select
    c_id as '班级' ,
    count(sgender) as number
from
    student
where
    sgender = '男'
group by
    c_id
having
    number >= 2;

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

# 20、查询Student表中每个学生的姓名和年龄
select
    st.sname as '姓名',
    TIMESTAMPDIFF(YEAR,st.sbirthday,curdate()) as '年龄'
from
    student as st;

# 21、查询“男”教师及其所上的课程
select
    te.tname as '姓名',
    co.cname as '课程'
from
    teacher as te
inner join
    course as co
on te.tgender='男' and te.tid=co.t_id;

# 22、查询每一门科目最高分同学的id、c_id、degree列
select
    id,c_id,degree
from
    score
where
    degree IN (
        (select
                max(degree)
            from
                score as sc
            group by
                c_id
        )
);

# 23、查询姓“王”的学生名字
select sname as '姓名' from student where sname like '王%';

# 24、查询刘冰老师所教的课程名称
select
    co.cname
from
    teacher as te
inner join
    course as co
on
    te.tname='刘冰'and te.tid=co.t_id;

# 25、查询教师编号大于王萍的教师信息
select
    *
from
    teacher as te
where
    te.tid > (select tid from teacher where tname='王萍');