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)
上一篇 2014-08-13 19:45
下一篇 2014-09-17 22:30

相关推荐

  • bash特性及bash脚本编程初步

    bash特性之命令hash 之前我们讲过用户在执行一个命令的时候bash会遍历环境变量$PATH中所有路径来查找执行文件。而命令hash是用来缓存之前用户使用过的命令下次执行的时候直接搜索hash缓存来减少对$PATH变量中路径的遍历次数,从而提高系统运行效率 hash:hash命令     hash:列出 &nbsp…

    Linux干货 2016-12-20
  • N25 第五周作业

    1、显示/boot/grub/grub.conf中以至少一个空白字符开头的行; 2、显示/etc/rc.d/rc.sysinit文件中以#开头,后面跟至少一个空白字符,而后又有至少一个非空白字符的行; 3、打出netstat -tan命令执行结果中以‘LISTEN’,后或跟空白字符结尾的行; 4、添加用户bash, testbash, basher, nol…

    Linux干货 2017-01-08
  • GRUB管理

    对于运维人员来说,想要熟练掌握linux,那么久要对linux的启动流程有一个详细的了解,而今天我们就一起来学习一下linux启动中最重要的一个阶段——GRUB引导阶段。 Linux启动流程 grup: GRand Unified Bootloader  由上图可知,grub属于系统启动过程中一个必须的阶段。而这个阶段又分为了三个小的阶段,分别是s…

    2017-09-02
  • haproxy实现discuz论坛的动静分离和负载均衡

    一、在cs2、cs3、cs4上安装httpd [root@cs2 ~]# yum install httpd [root@cs3 ~]# yum install httpd [root@cs4 ~]# yum install httpd 二…

    Linux干货 2016-06-05
  • shell编程作业

    1、编写脚本/root/bin/systeminfo.sh,显示当前主机系统信息,包括主机名,IPv4地址,操作系统版本,内核版本,CPU型号,内存大小,硬盘大小。 [root@localhost sh.log]# cat systeminfo.sh  #!/bin/bash #author:DYW #显示当前主机系统…

    Linux干货 2016-08-15
  • Linux磁盘和文件系统管理

    Linux磁盘和文件系统管理 linux磁盘管理 一、Linux设备类型     硬盘有机械硬盘(HDD)和固态硬盘(SSD)之分。机械硬盘即是传统普通硬盘,主要由:盘片,磁头,盘片转轴及控制电机,磁头控制器,数据转换器,接口,缓存等     几个部分组成。  &nbs…

    Linux干货 2016-04-11