sql小习题

来源：互联网发布：yy踢人软件编辑：程序博客网时间：2024/06/04 23:24

1.创建一张学生表，包含学号，姓名，性别，家庭住址，联系电话信息
create table students
(id int primary key AUTO_INCREMENT,
n_um int,
age char,
sex char,
address char,
telphone CHAR
)
2.修改学生表结构，增加学历字段
alter table students add education char(25)
3.随机插入3条学生信息
Insert into students values('','4120','21','男','地址','13131313')
Insert into students values('','4120','21','男','地址','13131313')
Insert into students values('','4120','21','男','地址','13131313')

4.查询出年龄小于20岁，学历为本科的所有学员信息
select *from students where age < '20' and education = '本科'
5.统计所有学生的男女人数
select sex,count(sex) from students GROUP BY sex
6.统计学生年龄在15-20,20-25,25-30三个区间的人数分别为多少
select case when (age >=15 and age <=20) then '15-20' when (age >=20 and age <=25) then '20-25' when (age >=25 and age <=30) then '25-30' else '30-' end 'eag_layer', count(*) emps from students group by case when (age >=15 and age <=20) then '15-20' when (age >=20 and age <=25) then '20-25' when (age >=25 and age <=30) then '25-30' else '30-' end order by 1

0 0