未加星标

Mariadb之SELECT语法练习

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
一 、导入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

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: 数据数据库
分页:12
转载请注明
本文标题:Mariadb之SELECT语法练习
本站链接:http://www.codesec.net/view/534847.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(94)