• 第13周作业


    1、如何将 hellodb_innodb.sql导入到数据库中

    mysql < hellodb_innodb.sql

    2、在学生表中,查询年龄大于25岁,且为男性的同学的名字和年龄

    select name,age from students where age>25 and gender='m';
    +--------------+-----+
    | name         | age |
    +--------------+-----+
    | Xie Yanke    |  53 |
    | Ding Dian    |  32 |
    | Yu Yutong    |  26 |
    | Shi Qing     |  46 |
    | Tian Boguang |  33 |
    | Xu Xian      |  27 |
    | Sun Dasheng  | 100 |
    +--------------+-----+

    3、在学生表中,以ClassID为分组依据,查询显示每组的平均年龄

    select classid,avg(age) from students where classid is not null group by classid;
    +---------+----------+
    | classid | avg(age) |
    +---------+----------+
    |       1 |  20.5000 |
    |       2 |  36.0000 |
    |       3 |  20.2500 |
    |       4 |  24.7500 |
    |       5 |  46.0000 |
    |       6 |  20.7500 |
    |       7 |  19.6667 |
    +---------+----------+

    4、显示第2题中平均年龄大于30的分组及平均年龄

    select classid,avg(age) as 平均年龄 from (select * from students where age>25 and gender='m') as new where classid is not null group by classid having 平均年龄>30;
    +---------+--------------+
    | ClassID | 平均年龄     |
    +---------+--------------+
    |       2 |      43.0000 |
    |       4 |      32.0000 |
    |       5 |      46.0000 |
    +---------+--------------+

    5、显示以L开头的名字的同学的信息

    第一种: 
    select * from students where name like 'l%';
    +-------+-------------+-----+--------+---------+-----------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID |
    +-------+-------------+-----+--------+---------+-----------+
    |     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
    |    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
    |    17 | Lin Chong   |  25 | M      |       4 |      NULL |
    +-------+-------------+-----+--------+---------+-----------+
    第二种:
    select * from students where substr(name,1,1)='l';
    +-------+-------------+-----+--------+---------+-----------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID |
    +-------+-------------+-----+--------+---------+-----------+
    |     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
    |    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
    |    17 | Lin Chong   |  25 | M      |       4 |      NULL |
    +-------+-------------+-----+--------+---------+-----------+

    6、显示老师ID非空的同学的相关信息

    select * from students where teacherid is not null;
    +-------+-------------+-----+--------+---------+-----------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID |
    +-------+-------------+-----+--------+---------+-----------+
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
    |     2 | Shi Potian  |  22 | M      |       1 |         7 |
    |     3 | Xie Yanke   |  53 | M      |       2 |        16 |
    |     4 | Ding Dian   |  32 | M      |       4 |         4 |
    |     5 | Yu Yutong   |  26 | M      |       3 |         1 |
    +-------+-------------+-----+--------+---------+-----------+

    7、students表中,查询以年龄排序后的数据,并且显示年龄最大的前10位同学的信息

    select * from students order by age desc limit 0,10;
    +-------+--------------+-----+--------+---------+-----------+
    | StuID | Name         | Age | Gender | ClassID | TeacherID |
    +-------+--------------+-----+--------+---------+-----------+
    |    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |
    |     3 | Xie Yanke    |  53 | M      |       2 |        16 |
    |     6 | Shi Qing     |  46 | M      |       5 |      NULL |
    |    13 | Tian Boguang |  33 | M      |       2 |      NULL |
    |     4 | Ding Dian    |  32 | M      |       4 |         4 |
    |    24 | Xu Xian      |  27 | M      |    NULL |      NULL |
    |     5 | Yu Yutong    |  26 | M      |       3 |         1 |
    |    17 | Lin Chong    |  25 | M      |       4 |      NULL |
    |    23 | Ma Chao      |  23 | M      |       4 |      NULL |
    |    18 | Hua Rong     |  23 | M      |       7 |      NULL |
    +-------+--------------+-----+--------+---------+-----------+

    8、students表中,查询年龄大于等于20岁,小于等于25岁的同学的信息

    select * from students where age between 20 and 25;
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
    +-------+---------------+-----+--------+---------+-----------+
    
    第二种
    select * from students where age>=20 and age<=25;
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
    +-------+---------------+-----+--------+---------+-----------+

    9、以ClassID分组,显示每班的同学的人数

    select classid as 班级,count(stuid) as 班级人数 from students where classid is not null group by classid;
    +--------+--------------+
    | 班级   | 班级人数     |
    +--------+--------------+
    |      1 |            4 |
    |      2 |            3 |
    |      3 |            4 |
    |      4 |            4 |
    |      5 |            1 |
    |      6 |            4 |
    |      7 |            3 |
    +--------+--------------+

    10、以ClassID分组,显示其平均年龄大于25的班级

    select classid as 班级,avg(age) as 平均年龄 from students where classid is not null group by classid having 平均年龄>25;
    +--------+--------------+
    | 班级   | 平均年龄     |
    +--------+--------------+
    |      2 |      36.0000 |
    |      5 |      46.0000 |
    +--------+--------------+
  • 相关阅读:
    Java 报错 -source 1.5 中不支持 diamond 运算符
    MacBook Java开发环境的配置
    MacBook 版本控制工具
    版本控制工具 Git SourceTree SSH 连接码云
    接口 请求https接口
    快递 共享电子面单
    快递 已发货订单重新打印电子面单
    SQL Server 分部分项导入后 数据的修改
    Hive数据的存储以及在centos7下进行Mysql的安装
    Hive初体验
  • 原文地址:https://www.cnblogs.com/ssel/p/13547779.html
Copyright © 2020-2023  润新知