Mariadb数据库简单操作
2018-05-28 本文已影响0人
gw章
一、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 |
+--------------+-----+
