Mysql 基础_习题二

题目集一

use lianxi;
# 建表
create table if not exists classes
(
    cid   int         not null comment '班级id',
    cname varchar(20) not null comment '班级名称'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

insert into classes(id, name)
values (1, '1班'),
       (2, '2班'),
       (3, '3班'),
       (4, '4班');

#建表
create table if not exists students
(
    studentNo int primary key auto_increment not null,
    name      varchar(10),
    sex       varchar(10),
    hometown  varchar(20),
    age       tinyint(4),
    class_id  int                            not null,
    card      varchar(20)
);

#插入数据
insert into students (name, sex, hometown, age, class_id, card)
values ('王昭君', '女', '北京', 20, 1, '340322199001247654'),
       ('诸葛亮', '男', '上海', 18, 2, '340322199002242354'),
       ('张飞', '男', '南京', 24, 3, '340322199003247654'),
       ('白起', '男', '安徽', 22, 4, '340322199005247654'),
       ('大乔', '女', '天津', 19, 3, '340322199004247654'),
       ('孙尚香', '女', '河北', 18, 1, '340322199006247654'),
       ('百里玄策', '男', '山西', 20, 2, '340322199007247654'),
       ('小乔', '女', '河南', 15, 3, null),
       ('百里守约', '男', '湖南', 21, 1, ''),
       ('妲己', '女', '广东', 26, 2, '340322199607247654'),
       ('李白', '男', '北京', 30, 4, '340322199005267754'),
       ('孙膑', '男', '新疆', 26, 3, '340322199000297655');

3、查询全部学生信息，并按照“性别”排序

select *
from students
order by sex;

4、查询现有学生都来自于哪些不同的省份

select distinct hometown
from students;

5、查询所有男生，并按年龄升序排序

select *
from students
where sex = '男'
order by age;

6、统计共有多少个学生

select count(*)
from students;

7、统计年龄大于20岁的学生有多少个

select count(*)
from students
where age > 20;

8、统计男生的平均年龄

select avg(age)
from students
where sex = '男';

9、查询1班学生中的最大年龄是多少

select max(age)
from students
where class_id = 1;

10、统计2班男女生各有多少人

select sex, count(studentNo)
from students
where class_id = 2
group by sex;

11、统计每个班级中每种性别的学生人数，并按照班级升序排序

select class_id, sex, count(*)
from students
group by class_id, sex
order by class_id;

12、查询年龄最小的学生的全部信息

select *
from students
where age in (
    select min(age)
    from students
);