MySQL上机题目演练25道练习增强你的SQL技能（mysql上机题目）

MySQL上机题目演练：25道练习增强你的SQL技能！

SQL（Structured Query Language）是一种标准化的关系型数据库管理系统语言，是所有关系型数据库管理系统的基础语言。MySQL是一种开源的关系型数据库管理系统，广泛应用于网络平台的数据存储和管理中。为了提高SQL技能，我们可以通过上机题目演练来加强自己的技能，下面提供了25道题目供大家练习。

1. 查询有哪些表

“`mysql

show tables;

2. 查询student表中的前10条记录

```mysql
select * from student limit 10;

3. 查询student表中“学号”和“姓名”两列数据

“`mysql

select number, name from student;

4. 查询student表中“学号”和“姓名”两列数据并按“学号”升序排序

```mysql
select number, name from student order by number asc;

5. 查询student表中共有多少条记录

“`mysql

select count(*) from student;

6. 查询student表中“班级”不同的记录有哪些

```mysql
select distinct class from student;

7. 查询student表中“班级”为“高一1班”的记录有哪些

“`mysql

select * from student where class=’高一1班’;

8. 查询student表中“性别”为“女”的记录有哪些，并按“年龄”升序排序

```mysql
select * from student where gender='女' order by age asc;

9. 查询student表中“出生年月”在“1990年1月1日”之后的记录有哪些

“`mysql

select * from student where birthday > ‘1990-01-01’;

10. 查询student表中“姓名”以“李”字开头的记录有哪些

```mysql
select * from student where name like '李%';

11. 查询student表中“入学时间”在“2015年9月1日”~“2016年1月1日”之间的记录有哪些

“`mysql

select * from student where admission_time between ‘2015-09-01’ and ‘2016-01-01’;

12. 统计student表中各个班级的学生人数

```mysql
select class, count(*) from student group by class;

13. 查询大于20岁的学生信息，并计算学生人数和平均年龄

“`mysql

select count(*), avg(age) from student where age > 20;

14. 查询student表中每个学生的所在城市及不同城市的人数

```mysql
select city, count(*) from student group by city;

15. 查询高一1班和高一2班中年龄最大和最小的学生

“`mysql

select * from student where class in (‘高一1班’,’高一2班’) and age in (select max(age) from student where class in (‘高一1班’,’高一2班’)) or age in (select min(age) from student where class in (‘高一1班’,’高一2班’));

16. 查询student表中，每个班级年龄最大的学生信息

```mysql
select * from student where (class, age) in (select class, max(age) from student group by class);

17. 查询student表中，男女生比例

“`mysql

select gender, count(*)/(select count(*) from student) as rate from student group by gender;

18. 查询student表中，每个城市年龄最小且入学时间最早的学生信息

```mysql
select * from student where (city, age, admission_time) in (select city, min(age), min(admission_time) from student group by city);

19. 查询各个班级年龄最大和最小的差距

“`mysql

select max(age) – min(age) from student group by class;

20. 查询student表中，年龄相同的学生人数

```mysql
select age, count(*) from student group by age;

21. 查询student表中，班级之间平均年龄的差距

“`mysql

select s1.class, s2.class, avg(s2.age)-avg(s1.age) from student s1 join student s2 on s1.class

22. 查询各个班级的男生/女生比例

```mysql
select class, gender, count(*)/(select count(*) from student where class=s.class and gender=g.gender) as rate from (select distinct class from student) s join (select distinct gender from student) g join student on student.class=s.class and student.gender=g.gender group by class, gender;

23. 查询各个班级人数排名前十的年龄

“`mysql

select s1.class, s1.age from student s1 join (select class, count(*) as cnt from student group by class order by cnt desc limit 10) s2 on s1.class = s2.class order by s1.class, s1.age;

24. 查询student表中，各个性别的最高分数的学生信息

```mysql
select * from student where (gender, score) in (select gender, max(score) from student group by gender);

25. 查询student表中，平均分最高的前三名学生信息

“`mysql

select * from student order by score desc limit 3;

以上是25道MySQL上机题目的练习，通过不断的练习可以提高自己的SQL技巧和思维能力，掌握更多的SQL语法知识。希望大家可以多加尝试和练习，在实际应用中提高自己的数据库设计和管理能力。