Mariadb之SELECT语法练习

kang • • Linux干货

一 、导入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 |
    +--------------+-----+


 

原创文章，作者：kang，如若转载，请注明出处：http://www.178linux.com/79182

赞 (1)
kangkang
0
上一篇 2016-12-17
下一篇 2016-12-17

相关推荐

  • 基于mysql数据库的日志分析系统

    现如今，日志已经成为了我们分析系统及相关服务的一个重要工具。而日志也具有其相对较为固定的格式以便于进行统计查询。其大致格式如下： 日期时间            主机      &…

    Linux干货 2016-10-23

  • RHCE基础实验

    实验：配额实现 1. 启用配额功能 vim /etc/fstab defaults usrquota,grpquota mount -o remount /home 2. 创建配额数据库 quotacheck -cug /home 3. 启用数据库 quotaon /home quotaon -p /home 查看 4. edquota wang setqu…

    Linux干货 2017-05-02

  • 计算机组成及Linux入门知识

    计算机的基本组成: 存储器:　　　　　实现记忆功能的部件用来存放计算程序及参与运算的各种数据 运算器:　　　　　负责数据的算术运算和逻辑运算即数据的加工处理 控制器:　　　　　负责对程序规定的控制信息进行分析,控制并协调输入,输出操作或内存访问 输入设备:　　　　实现计算程序和原始数据的输入 输出设备:　　　　实现计算结果输出 组成的联系: 图一 图二 计算…

    Linux干货 2016-09-16

  • Bind编译安装详解

    Bind编译安装详解 Bind是一款开放源码的DNS服务器软件，由美国加州大学Berkeley分校开发和维护的，全名为Berkeley Internet Name Domain它是目前世界上使用最为广泛的DNS服务器软件，支持各种unix平台和windows平台。现今互联网上最常使用的DNS服务器软件，使用BIND作为服务器软件的DNS服务器约占所有DNS服…

    Linux干货 2016-07-22
  • 面向对象的实例方法，类方法和静态方法 Linux干货

    面向对象的实例方法，类方法和静态方法

    类方法和静态方法 普通函数 class Person: def normal_method(): print(‘normal’) Person.normal_method() # Person().normal_method() print(Person.__dict__) Person.normal_method()可以，是因为这个方法只是被Person这…

    2017-11-11

  • Linux Bash Shell练习

    Linux Bash Shell练习 1、写一个脚本,完成以下功能： 假设某目录(/etc/rc.d/rc3.d/)下分别有K开头的文件和S开头的文件若干 显示所有以K开头的文件的文件名，并且给其附加一个stop字符串 显示所有以S开头的文件的文件名，并且给其附加一个start字符串 分别统计S开头和K开头的文件各有多少 #!/bin/bash # for&…

    Linux干货 2016-12-17
电话咨询
在线咨询