MySQL数据库笔记2

– 查询学生的个数
select count(height) from students; – 聚合函数不统计null值
– 可以使用非空字段进行统计，比如: 推荐大家使用主键列
select count(id) from students;
– 通用的写法
select count(*) from students;

– 查询女生的编号最大值
select max(id) from students where gender = ‘女’;

– 查询未删除的学生最小编号

select min(id) from students where is_delete = 0;

– 查询男生的总身高
select sum(height) from students where gender = ‘男’;

– 平均身高
select sum(height) / count(*) from students where gender = ‘男’;

– 求男生的平均身高
– 使用ifnull 判断这个值如果是null使用默认提供的值
select avg(ifnull(height, 0)) from students where gender = ‘男’;

– 查询性别的种类

select gender from students group by gender;
select distinct gender from students;

– 根据name和gender字段进行分组, 查看name和gender的分组信息
select name, gender from students group by name, gender;

– 根据gender字段进行分组， 查询每个分组的姓名信息
– group_concat显示每个分组的指定字段的信息
select gender, group_concat(name) from students group by gender;

– 统计不同性别的平均年龄
select gender, avg(age) from students group by gender;

– 统计不同性别的人的个数
select gender, count(*) from students group by gender;

– 根据gender字段进行分组，统计分组条数大于2的
select gender, count() from students group by gender having count() > 2;

– 根据gender字段进行分组，汇总总人数
select gender, count(*) from students group by gender with rollup;

– 根据gender字段进行分组，汇总所有人的年龄
select gender, group_concat(age) from students group by gender with rollup;

– 使用内连接查询学生表与班级表
select * from students as s inner join classes as c on s.c_id = c.id;

– 使用左连接查询学生表与班级表
select * from students as s left join classes as c on s.c_id = c.id;

– 使用右连接查询学生表与班级表
select * from students as s right join classes as c on s.c_id = c.id

– 使用自连接查询省份和城市信息
select c.id, c.title, c.pid, p.title from areas c inner join areas p on c.pid = p.id where p.title = ‘河北省’;

– 查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);

– 查询学生在班的所有班级名字
select name from classes where id in (select c_id from students);

– 查找年龄最大,身高最高的学生
select * from students where (age, height) = (select max(age),max(height) from students);

– 为学生版的cls_id字段添加外键约束
alter table students add foreign key (c_id) references classes(id);
– 创建学校表
create table school(id int not null primary key auto_increment, name varchar(30));

– 创建老师表添加学校外键
create table teacher(id int not null primary key auto_increment, name varchar(30), s_id int, foreign key (s_id) references school(id));

– 删除外键
alter table drop foreign key teacher_ibfk_1;