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

相关推荐

  • Linux文件系统的最基本数据结构:inode和block

    Linux文件系统的最基本数据结构:inode和block 为什么有inode和block inode block 创建目录或文件 使用场景 查看本机的文件系统信息 Linux文件系统的最基本数据结构:inode和block 为什么有inode和block 由于Linux系统是多用户多的,所以文件系统类型多样化是在所难免的。从ext2开始,是将文件属性和文件…

    Linux干货 2016-09-06
  • Linux网络命令学习

    Linux网络命令之网络环境查看命令:ifconfig:查看与配置网络状态命令(只能看到IP地址和子网掩码)ifup 网卡设备名:启用该网卡设备,如:ifup eth0和ifdown lonetstat 选项:  -t:列出TCP协议端口  -u:列出UDP协议端口  -l:仅列出在监听状态网络服务  -a:查看所有连…

    Linux干货 2017-06-25
  • linux权限管理

    linux文件权限    文件的属性和权限的设置对于linux系统来说是相当重要的,如果对这一块的知识没有一个深入的了解,你的系统就有可能有很大的危险。那么首先我们来看一看文件的属性所对应的都是什么意思吧。 上图第一个字符是d,他代表了文件的类型,d的意思就是目录文件,那么除了目录文件还有什么文件呢?    …

    Linux干货 2016-08-04
  • vim的基本使用

    前言:     Vim是一个类似于Vi的著名的功能强大、高度可定制的文本编辑器,在Vi的基础上改进和增加了很多特性。VIM是自由软件。    Vim普遍被推崇为类Vi编辑器中最好的一个,事实上真正的劲敌来自Emacs的不同变体。1999 年Emacs被选为Linuxworld文本…

    Linux干货 2017-07-30
  • N26_第二周作业

    一、Linux上的文件管理类命令都有哪些,其常用的使用方法及其相关示例演示。 文件管理命令:cp,mv,rm 1、cp命令 功能说明:复制copy语法格式:        单源复制:  cp [OPTION]… [-T] SOURCE DEST  …

    Linux干货 2017-02-21
  • 关于大型网站技术演进的思考(八):存储的瓶颈(8)

    原文出处: 夏天的森林  在开始本篇主要内容前,我们一起看看下面的几张截图,首先是第一张图,如下图所示: 这是一家电商网站的首页,当我们第一次打开这个首页,网站会弹出一个强制性的对话框,让用户选择货物配送的地址,如果是淘宝和京东的话,那么这个选择配货地址的选项是在商品里,如下图是淘宝的选择配送地点: 下图是京东选择配货地点: 那么图一跟京…

    2015-03-11