Mariadb之SELECT语法练习

一 、导入hellodb.sql生成数据库后实现以下操作

    # mysql -uroot -h172.16.23.23 -pcento.123 < hellodb.sql
    mysql> SHOW DATABASES; 可以列出已存在的数据库 
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | NODE1              |
    | RJYY               |
    | hellodb            |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    mysql> USE hellodb;
    mysql> SHOW TABLES; 
    +-------------------+
    | Tables_in_hellodb |
    +-------------------+
    | classes           |
    | coc               |
    | courses           |
    | scores            |
    | students          |
    | teachers          |
    | toc               |
    +-------------------+

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

    mysql> 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 |
    +--------------+-----+

    2、 以ClassID为分组依据,显示每组的平均年龄;

    mysql> SELECT avg(age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID ;
    +----------+---------+
    | avg(age) | ClassID |
    +----------+---------+
    |  20.5000 |       1 |
    |  36.0000 |       2 |
    |  20.2500 |       3 |
    |  24.7500 |       4 |
    |  46.0000 |       5 |
    |  20.7500 |       6 |
    |  19.6667 |       7 |
    +----------+---------+

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

    mysql> SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL  GROUP BY ClassID HAVING avg(Age) > 30;
    +----------+---------+
    | avg(Age) | ClassID |
    +----------+---------+
    |  36.0000 |       2 |
    |  46.0000 |       5 |
    +----------+---------+

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

    mysql> 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 |
    +-------+-------------+-----+--------+---------+-----------+

    5、 显示TeacherID非空的同学的相关信息;

    mysql> 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 |
    +-------+-------------+-----+--------+---------+-----------+

    6、 以年龄排序后,显示年龄最大的前10位同学的信息;

    mysql> SELECT * FROM students ORDER BY Age DESC LIMIT 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 |
    +-------+--------------+-----+--------+---------+-----------+

    7、 查询年龄大于等于20岁,小于等于25岁的同学的信息;用三种方法;

    mysql> SELECT * FROM students WHERE Age >=20 AND Age <=25;    
    mysql> SELECT * FROM students WHERE Age  BETWEEN 20 AND 25; 
    mysql> SELECT * FROM students WHERE Age  IN (20,21,22,23,24,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 |
    +-------+---------------+-----+--------+---------+-----------+

二、  导入hellodb.sql,以下操作在students表上执行

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

    mysql> SELECT count(StuID),ClassID FROM students GROUP BY ClassID ;  
    +--------------+---------+
    | count(StuID) | ClassID |
    +--------------+---------+
    |            2 |    NULL |
    |            4 |       1 |
    |            3 |       2 |
    |            4 |       3 |
    |            4 |       4 |
    |            1 |       5 |
    |            4 |       6 |
    |            3 |       7 |
    +--------------+---------+

    2、以Gender分组,显示其年龄之和;

    mysql> SELECT sum(Age),Gender FROM students GROUP BY Gender ;
    +----------+--------+
    | sum(Age) | Gender |
    +----------+--------+
    |      190 | F      |
    |      495 | M      |
    +----------+--------+

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

    mysql> SELECT avg(Age),ClassID FROM students GROUP BY ClassID HAVING avg(Age) > 25;
    +----------+---------+
    | avg(Age) | ClassID |
    +----------+---------+
    |  63.5000 |    NULL |
    |  36.0000 |       2 |
    |  46.0000 |       5 |
    +----------+---------+

    4、以Gender分组,显示各组中年龄大于25的学员的年龄之和;

    mysql> SELECT sum(Age),Gender FROM students WHERE Age > 25 GROUP BY Gender ;
    +----------+--------+
    | sum(Age) | Gender |
    +----------+--------+
    |      317 | M      |
    +----------+--------+

三、 导入hellodb.sql,完成以下题目:

    1、显示前5位同学的姓名、课程及成绩;

    mysql> SELECT s.Name,courses.Course,scores.Score FROM (select * from students limit 5) 
    AS s  LEFT JOIN scores ON scores.StuID = s.StuID LEFT JOIN courses ON scores.CourseID =courses.CourseID;
    mysql> SELECT s.name,sc.course,sc.score FROM (SELECT * FROM students LIMIT 5 ) 
    AS s LEFT JOIN (SELECT scores.stuid,courses.course,scores.score FROM scores LEFT JOIN courses ON 
    courses.CourseID=scores.CourseID)AS sc ON s.StuId=sc.StuID;
    +-------------+----------------+-------+
    | name        | course         | score |
    +-------------+----------------+-------+
    | Shi Zhongyu | Kuihua Baodian |    77 |
    | Shi Zhongyu | Weituo Zhang   |    93 |
    | Shi Potian  | Kuihua Baodian |    47 |
    | Shi Potian  | Daiyu Zanghua  |    97 |
    | Xie Yanke   | Kuihua Baodian |    88 |
    | Xie Yanke   | Weituo Zhang   |    75 |
    | Ding Dian   | Daiyu Zanghua  |    71 |
    | Ding Dian   | Kuihua Baodian |    89 |
    | Yu Yutong   | Hamo Gong      |    39 |
    | Yu Yutong   | Dagou Bangfa   |    63 |
    +-------------+----------------+-------+

    2、显示其成绩高于80的同学的名称及课程;

    mysql> SELECT Name,Course,Score FROM (students LEFT JOIN scores ON students.StuID=scores.StuID ) 
    LEFT JOIN courses ON courses.CourseID=scores.CourseID WHERE Score > 80; 
    +-------------+----------------+-------+
    | Name        | Course         | Score |
    +-------------+----------------+-------+
    | Shi Zhongyu | Weituo Zhang   |    93 |
    | Shi Potian  | Daiyu Zanghua  |    97 |
    | Xie Yanke   | Kuihua Baodian |    88 |
    | Ding Dian   | Kuihua Baodian |    89 |
    | Shi Qing    | Hamo Gong      |    96 |
    | Xi Ren      | Hamo Gong      |    86 |
    | Xi Ren      | Dagou Bangfa   |    83 |
    | Lin Daiyu   | Jinshe Jianfa  |    93 |
    +-------------+----------------+-------+

    3、求前8位同学每位同学自己两门课的平均成绩,并按降序排列;

    mysql> SELECT Name,avg(Score) FROM (SELECT  * FROM students LIMIT 8) AS rj LEFT JOIN scores AS
    jr ON rj.StuID=jr.StuID GROUP BY Name ORDER BY avg(Score) DESC;
    +-------------+------------+
    | Name        | avg(Score) |
    +-------------+------------+
    | Shi Qing    |    96.0000 |
    | Shi Zhongyu |    85.0000 |
    | Xi Ren      |    84.5000 |
    | Xie Yanke   |    81.5000 |
    | Ding Dian   |    80.0000 |
    | Lin Daiyu   |    75.0000 |
    | Shi Potian  |    72.0000 |
    | Yu Yutong   |    51.0000 |
    +-------------+------------+

4、显示每门课程课程名称及学习了这门课的同学的个数;

    mysql> SELECT courses.Course,count(rj.StuID) FROM scores AS rj LEFT JOIN courses ON courses.CourseID=rj.CourseID GROUP BY rj.CourseID;
    +----------------+-----------------+
    | Course         | count(rj.StuID) |
    +----------------+-----------------+
    | Hamo Gong      |               3 |
    | Kuihua Baodian |               4 |
    | Jinshe Jianfa  |               1 |
    | Taiji Quan     |               1 |
    | Daiyu Zanghua  |               2 |
    | Weituo Zhang   |               2 |
    | Dagou Bangfa   |               2 |
    +----------------+-----------------+

四、 思考题 

1、如何显示其年龄大于平均年龄的同学的名字?

    mysql> SELECT Name,Age FROM students WHERE Age > (SELECT avg(Age) FROM students);
    +--------------+-----+
    | Name         | Age |
    +--------------+-----+
    | Xie Yanke    |  53 |
    | Ding Dian    |  32 |
    | Shi Qing     |  46 |
    | Tian Boguang |  33 |
    | Sun Dasheng  | 100 |
    +--------------+-----+

2、如何显示其学习的课程为第1、2,4或第7门课的同学的名字? 

    mysql> SELECT rj.Name,scores.CourseID FROM students AS rj LEFT JOIN scores ON scores.StuID = rj.StuID WHERE scores.CourseID IN (1,2,4,7);
    +-------------+----------+
    | Name        | CourseID |
    +-------------+----------+
    | Shi Zhongyu |        2 |
    | Shi Potian  |        2 |
    | Xie Yanke   |        2 |
    | Ding Dian   |        2 |
    | Yu Yutong   |        1 |
    | Yu Yutong   |        7 |
    | Shi Qing    |        1 |
    | Xi Ren      |        1 |
    | Xi Ren      |        7 |
    | Lin Daiyu   |        4 |
    +-------------+----------+

3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学?

    mysql> SELECT students.name,students.age,tp.classid,tp.vg FROM students,(SELECT classid,COUNT(stuid) 
    AS cs,AVG(age) AS vg FROM students GROUP BY classid HAVING cs >=3) AS tp WHERE  students.age>tp.vg AND students.classid=tp.classid;
    +---------------+-----+---------+---------+
    | name          | age | classid | vg      |
    +---------------+-----+---------+---------+
    | Shi Potian    |  22 |       1 | 20.5000 |
    | Xie Yanke     |  53 |       2 | 36.0000 |
    | Ding Dian     |  32 |       4 | 24.7500 |
    | Yu Yutong     |  26 |       3 | 20.2500 |
    | Yuan Chengzhi |  23 |       6 | 20.7500 |
    | Xu Zhu        |  21 |       1 | 20.5000 |
    | Lin Chong     |  25 |       4 | 24.7500 |
    | Hua Rong      |  23 |       7 | 19.6667 |
    | Huang Yueying |  22 |       6 | 20.7500 |
    +---------------+-----+---------+---------+

4、统计各班级中年龄大于全校同学平均年龄的同学。

    mysql> SELECT rj.Name,rj.Age FROM students AS rj LEFT JOIN classes AS jr ON  
    rj.ClassID=jr.ClassID WHERE rj.ClassID=jr.ClassID AND Age > (SELECT AVG(Age) FROM students);
    +--------------+-----+
    | Name         | Age |
    +--------------+-----+
    | Xie Yanke    |  53 |
    | Ding Dian    |  32 |
    | Shi Qing     |  46 |
    | Tian Boguang |  33 |
    +--------------+-----+

原创文章,作者:renjin,如若转载,请注明出处:http://www.178linux.com/68765

(0)
renjinrenjin
上一篇 2014-08-13
下一篇 2014-09-17

相关推荐

  • 配置路由实例

    路由的分类和管理 路由:跨网络通信; 路由分类:     主机路由     网络路由     默认路由 路由管理命令: route 命令是在本地 IP 路由表中显示和修改条目网络命令; 查看:route -n 添加:route add ro…

    Linux干货 2016-09-12
  • N21沉舟13周作业

    1、建立samba共享,共享目录为/data,要求:(描述完整的过程)   1)共享名为shared,工作组为magedu;   2)添加组develop,添加用户gentoo,centos和ubuntu,其中gentoo和centos以develop为附加组,ubuntu不属于develop组;密码均为用户名;   3)添加s…

    Linux干货 2016-10-24
  • day6总结

    主要内容: useradd与usermod对比 groupadd与groupmod对比 passwd与chage对比 gpasswd与groupmems对比 切换用户 chown与chgrp用法 一般权限与特殊权限 chmod用法   useradd创建用户的初始信息存放在/etc/login.defs和/etc//default/useradd文…

    系统运维 2016-08-08
  • Linux系统启动过程及其修复过程简析

    Linux组成 Linux: kernel+rootfs     kernel: 进程管理、内存管理、网络管理、驱动程序、文件系统、安全功能     rootfs:程序和glibc     库:函数集合, function, 调用接口(头文…

    Linux干货 2016-09-19
  • 马哥教育网络班22期+第11周课程练习

    1、详细描述一次加密通讯的过程,结合图示最佳。 Bob先利用单向加密算法提取当前数据的指纹(特征码),再用自己的私钥加密数据指纹并附加于数据尾部, 然后利用对称加密将整个文件加密,之后用Alice的公钥加密对称加密密钥附加于尾部。 Alice收到数据后,先用自己的私钥解密,得到对称加密密钥,之后用对称加密密钥解密,然后用Bob的公钥 解密得到数据指纹,并且验…

    Linux干货 2016-11-14
  • N22-第一周作业

    1、描述计算机的组成及其功能    组成:        硬件:            CPU:运算器、控制器、寄存器、缓存     &nb…

    Linux干货 2016-08-15