MySQL练习

男娘i 2023-10-10 20:10 38阅读 0赞

**修改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='王萍');