MySQL基本操作(2)
1、查询行数 判断表中是否有数据
select count( * ) from student_info ;
这条sql语句的查询结果是 5,表示这个表有5条数据。
select count( * ) from student_info where flag = 0;
我们也可以添加条件,例如 flag = 0 的数据 ,有 4条。
select count (s_sex) from student_info where flag = 0;
查询flag = 0时的s_sex数据,有3条,因为其中有一条数据为空。
2、求和,求平均值,求最大值,最小值
求和函数:
select sum(s_age) as 年龄总和 from student_info where flag = 0;
select sum(s_name) from student_info where flag = 0;
如果没有值,返回0;s_ name 是varchar 类型的是不能进行计算的,所以没有值。
求平均值:
select avg(s_age) as 平均年龄 from student_info where flag = 0;
取最大值,最小值:
select max(s_age) as 最大年龄 from student_info where flag = 0;
select min(s_age) as 最小年龄 from student_info where flag = 0;
3、分组 group by
select s_name,s_age from student_info group by s_age,s_name;
– where 要写在group by 前面
select s_name,s_age from student_info where flag = 0 group by s_age,s_name;
– order by是放在语句的最后面
select s_name,s_age from student_info where flag = 0 group by s_age,s_name order by s_age;
按照s_age 和s_name 分类后 ,就只有两条数据了。
– HAVING 要写在group by后面
select s_name,s_age from student_info group by s_age,s_name HAVING s_age = 15;
4、关联
一对一关联 ,表的一条数据关联附表的一条数据
select * from t_student s,t_performace t where s.student_id = t.student_id;
select * from t_performace where student_id in(select student_id from t_student);
一对多关联,主表的一条数据关联附表的多条数据
select * from t_performace s,t_subject t where s.subject_id = t.subject_id;
多对多,多个表关联(多表联查)
select * from t_student s,t_subject d,t_performace t where s.student_id = t.student_id and d.subject_id = t.subject_id;
select * from t_performace where student_id in(
select student_id from t_student where subject_id in(
select subject_id from t_subject));