一、mariadb服务安装

---

mysql命令行工具（在linux系统中用来管理登录数据库或执行数据库命令的工具）格式：mysql [options] [database]:

常用选项如下：

-u：用户名，默认为root用户；
-h:远程主机地址，默认为localhost,客户端连接服务器端，服务器会反解客户端的IP为主机名，关闭功能可使用（skip_name_resolve=ON)
-p:标识用户的密码
-P:服务器监听的端口，默认为3306；
-S,--socket=/PATH/TO/mysql.sock:套接字文件路径；
-D,--database=DB_NAME:连接到服务器端之后，设定此处指明的数据库为默认数据库；
-e,--execute='SQL STATEMENT':连接至服务器并让其执行命令后直接返回；

安装数据库：

yum install -y mariadb-server    #安装数据库mariadb    
mysql_secure_installation        #对数据库进行初始化的安全加固操作；

数据库命令行的简单操作：

导入hellodb.sql生成数据库，在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

MariaDB [hellodb]> 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 |
    +--------------+-----+

以ClassID为分组依据，显示每组的平均年龄；

MariaDB [hellodb]> SELECT ClassID,avg(age) FROM students GROUP BY ClassID;
        +---------+----------+
        | ClassID | avg(age) |
        +---------+----------+
        |    NULL |  63.5000 |
        |       1 |  20.5000 |
        |       2 |  36.0000 |
        |       3 |  20.2500 |
        |       4 |  24.7500 |
        |       5 |  46.0000 |
        |       6 |  20.7500 |
        |       7 |  19.6667 |
        +---------+----------+

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

MariaDB [hellodb]> SELECT ClassID,avg(age) AS Aging FROM students GROUP BY ClassID HAVING Aging>30;
    +---------+---------+
    | ClassID | Aging   |
    +---------+---------+
    |    NULL | 63.5000 |
    |       2 | 36.0000 |
    |       5 | 46.0000 |
    +---------+---------+

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

MariaDB [hellodb]> 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 |
    +-------+-------------+-----+--------+---------+-----------+

显示TeacherID非空的同学的相关信息；

MariaDB [hellodb]> 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 |
    +-------+-------------+-----+--------+---------+-----------+

以年龄排序后，显示年龄最大的前10位同学的信息；

MariaDB [hellodb]> 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 |
        +-------+--------------+-----+--------+---------+-----------+

查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

MariaDB [hellodb]> SELECT * FROM students WHERE Age IN(20,21,22,23,24,25);
MariaDB [hellodb]> SELECT * FROM students WHERE aGE BETWEEN 20 AND 25;
MariaDB [hellodb]> 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 |
    +-------+---------------+-----+--------+---------+-----------+

以ClassID分组，显示每班的同学的人数；

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

以Gender分组，显示其年龄之和；

MariaDB [hellodb]> SELECT Gender,sum(age) FROM students GROUP BY Gender;
    +--------+----------+
    | Gender | sum(age) |
    +--------+----------+
    | F      |      190 |
    | M      |      495 |
    +--------+----------+

以ClassID分组，显示其平均年龄大于25的班级；

MariaDB [hellodb]> SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(age)>25;
    +---------+----------+
    | ClassID | avg(age) |
    +---------+----------+
    |    NULL |  63.5000 |
    |       2 |  36.0000 |
    |       5 |  46.0000 |
    +---------+----------+

以Gender分组，显示各组中年龄大于25的学员的年龄之和；

MariaDB [hellodb]> SELECT NT.Gender,sum(NT.age) FROM (SELECT Gender,age FROM students WHERE Age > 25) AS NT GROUP BY Gender;
    +--------+-------------+
    | Gender | sum(NT.age) |
    +--------+-------------+
    | M      |         317 |
    +--------+-------------+

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

MariaDB [hellodb]> SELECT st.Name,co.Course,sc.Score FROM students st,courses co,scores sc WHERE st.StuID=sc.StuID AND sc.CourseID=co.CourseID LIMIT 8;
    +-------------+----------------+-------+
    | 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 |
    +-------------+----------------+-------+

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

MariaDB [hellodb]> SELECT st.Name,sc.Score FROM students st,courses co,scores sc WHERE st.StuID=sc.StuID AND sc.CourseID=co.CourseID AND sc.Score > 80;
    +-------------+-------+
    | Name        | Score |
    +-------------+-------+
    | Shi Zhongyu |    93 |
    | Shi Potian  |    97 |
    | Xie Yanke   |    88 |
    | Ding Dian   |    89 |
    | Shi Qing    |    96 |
    | Xi Ren      |    86 |
    | Xi Ren      |    83 |
    | Lin Daiyu   |    93 |
    +-------------+-------+

求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

MariaDB [hellodb]> SELECT st.Name,sc2.avg_score FROM (SELECT StuID,avg(Score) AS avg_score FROM scores GROUP BY StuID) AS sc2,students st WHERE st.StuID=sc2.StuID ORDER BY avg_score DESC LIMIT 8;
    +-------------+-----------+
    | 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 |
    +-------------+-----------+

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

MariaDB [hellodb]> SELECT co2.Course,count(co2.StuID) FROM (SELECT co.Course,sc.StuID FROM courses co,scores sc WHERE co.CourseID=sc.CourseID) AS co2 GROUP BY co2.Course;
    +----------------+------------------+
    | Course         | count(co2.StuID) |
    +----------------+------------------+
    | Dagou Bangfa   |                2 |
    | Daiyu Zanghua  |                2 |
    | Hamo Gong      |                3 |
    | Jinshe Jianfa  |                1 |
    | Kuihua Baodian |                4 |
    | Taiji Quan     |                1 |
    | Weituo Zhang   |                2 |
    +----------------+------------------+

显示其年龄大于平均年龄的同学的名字

MariaDB [hellodb]> 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 |
    +--------------+-----+

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

MariaDB [hellodb]> SELECT st.Name,co2.CourseID,co2.Course FROM (SELECT * FROM courses WHERE CourseID IN(1,2,4,7)) AS co2,students st,scores sc WHERE st.StuID=sc.StuID AND sc.CourseID=co2.CourseID;
    +-------------+----------+----------------+
    | Name        | CourseID | Course         |
    +-------------+----------+----------------+
    | Yu Yutong   |        1 | Hamo Gong      |
    | Shi Qing    |        1 | Hamo Gong      |
    | Xi Ren      |        1 | Hamo Gong      |
    | Shi Zhongyu |        2 | Kuihua Baodian |
    | Shi Potian  |        2 | Kuihua Baodian |
    | Xie Yanke   |        2 | Kuihua Baodian |
    | Ding Dian   |        2 | Kuihua Baodian |
    | Lin Daiyu   |        4 | Taiji Quan     |
    | Yu Yutong   |        7 | Dagou Bangfa   |
    | Xi Ren      |        7 | Dagou Bangfa   |
    +-------------+----------+----------------+

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

MariaDB [hellodb]> SELECT Name,Age FROM (SELECT Class,cl.ClassID FROM students AS st,classes AS cl WHERE st.ClassID=cl.ClassID GROUP BY Class HAVING count(Name) >=3) AS cl,students AS st WHERE cl.ClassID=st.ClassID AND Age > (SELECT avg(Age) FROM students);
    +--------------+-----+
    | Name         | Age |
    +--------------+-----+
    | Xie Yanke    |  53 |
    | Ding Dian    |  32 |
    | Tian Boguang |  33 |
    +--------------+-----+

各班级中年龄大于全校同学平均年龄的同学

MariaDB [hellodb]> SELECT Name,Age FROM students AS st,classes AS cl WHERE st.ClassID=cl.ClassID AND Age > (SELECT avg(Age) FROM students);
    +--------------+-----+
    | Name         | Age |
    +--------------+-----+
    | Xie Yanke    |  53 |
    | Ding Dian    |  32 |
    | Shi Qing     |  46 |
    | Tian Boguang |  33 |
    +--------------+-----+