MySQL SELECT查询实战：练习题精选，提升你的数据库查询技能

创建一个名叫stu的表
id 主键 自增 非空
name 默认空
age 默认0
height 浮点型
gender 枚举 默认保密
isdelete 默认0
create table stu (
  id tinyint primary key auto_increment,
    name varchar(5) default "",
    age tinyint default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    isdelete tinyint default 0
)
添加数据
insert into stu values
(0,"小明",18,180.00,"女",0),
(0,"小月月",18,180.00,"女",1),
(0,"彭于晏",29,185.00,"男",0),
(0,"刘德华",59,175.00,"男",1),
(0,"黄蓉",38,160.00,"女",0),
(0,"刘亦菲",25,166.00,"女",0),
(0,"程坤",27,181.00,"男",1),
(0,"金星",33,162.00,"中性",0),
(0,"静香",12,180.00,"女",1),
(0,"郭靖",12,170.00,"男",0),
(0,"周杰",34,176.00,"男",0);

1).等值查询:
    查询id为1的学生的所有字段信息
    select * from stu where id = 1;
2).条件比较查询:
    查询id大于3的所有学生的所有字段信息
    select * from stu where id > 3;
3).比较运算与逻辑运算查询:
    查询id大于5并且身高大于170的所有学生的信息
    select * from stu where id > 5 and height > 170;

4).between...and的使用
  
    查询身高在170都180之间的所有学生信息
    select * from stu where height between 170 and 180;
5).成员运算:
  
    查询id值为1, 3, 4的所有学生信息
    select * from stu where id in (1,3,4);
6).空与非空判断:
    查询年龄数据为空的所有学生信息
    select * from stu where age is null;
    查询姓名数据不为空的所有学生信息
    select * from stu where name is not null;

 1. 创建一个表，表名为student  
 create table student (
  id tinyint,
    name varchar(5),
    score tinyint,
    address varchar(5),
    age tinyint,
    gender enum('男','女','保密') 
);
create table student (
  id tinyint primary key auto_increment,
    name varchar(5) not null,
    score tinyint not null,
    address varchar(5) not null,
    age tinyint not null,
    gender enum('男','女','保密') 
);
2. 成功添加数据
insert into student value
(1,'邱路泽',100,'河南',17,'男'),
(2,'宋子怡',69,'河南',19,'女'),
(3,'陈兴',90,'山东',21,'男'),
(4,'汪星竹',88,'江西',26,'女'),
(5,'大悦',73,'山东',26,'男'),
(6,'陈一末',93,'山东',21,'保密');
3. 将表名修改为stu  
alter table student rename stu;
4. 增加字段isdelete 单选 0 1 默认为0  
alter table stu add isdelete enum('0','1') default '0';
5. 给id字段增加主键和自增约束
alter table stu modify id tinyint primary key auto_increment;
6.给name字段增加唯一约束
alter table stu modify name varchar(5) unique;
7. 给age字段增加非空约束
alter table stu modify age tinyint not null;
8. 修改gender字段名为sex，并增加默认约束为男
alter table stu change gender sex enum('男','女','保密') default '男';
9. 分别使用模糊查询和正则查询，查询名字是三个字的学生的信息 
select * from stu where name like '___';
select * from stu where name rlike '^...$';
10. 查询成绩几个的学生的信息，并要求按照成绩由高到低排序
select * from stu order by score desc;
11. 将表中的数据以年龄从高到底排序，如果年龄一致则按照成绩由低到高排序 
select * from stu order by age desc,score;
12. 查询姓陈的学生的信息                                     
select * from stu where name like '陈%';
select * from stu where name rlike '^陈.';
13. 查询河南省的成绩及格的学生的信息                         
select * from stu where address = '河南' and score > 60;
14. 统计性别为男的学生的数量                                 
select count(*) from stu where gender = '男';
15. 统计所有男生的平均成绩、平均年龄  
select avg(score),avg(age) from stu where gender = '男';
16. 统计所有女生的总成绩、总年龄                             
select sum(score),sum(age) from stu where gender = '女';
17. 使用三种方式实现获取id为5、6、7的学生信息                
select * from stu where id = 5 or id = 6 or id = 7;
select * from stu where id in (5,6,7);
select * from stu where id between 5 and 7;
18.查询不同性别对应的人数及人名
select gender,count(*),group_concat(name) from stu group by gender;
19. 查询人数大于等于2人的地区名称及该地区的学生人数
select address,count(*) from stu group by address having count(*) >= 2;
20.查询平均分数大于等于80的地区名称和平均分数
select address,avg(score) from stu group by address having avg(score) >= 80;
21.查询各地区的平均年龄和最大年龄
select address,avg(age),max(age) from stu group by address;
22.查询平均年龄超过20岁的性别及平均年龄
select gender,avg(age) from stu group by gender having avg(age) > 20;