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
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