查询数据及运算符

– 多表数据 ，从多张表获取数据
select * from my_int,my_score;

– 动态数据
select * from (select int_1,int_8 from my_int) as int_my;

– 分组统计,：group by将数据按照指定的字段分组之后，只会保留每组的第一条记录
select * from my_student group by class_id;

– 使用聚合函数统计每班人数，最大年龄，最爱身高，平均年龄
select class_id,count(*),max(stu_age),min(stu_height),avg(stu_age) from my_student group by class_id;

– Group_concat()：为了将分组中指定的字段进行合并（字符串拼接）
select class_id,group_concat(stu_name),count(*),max(stu_age),min(stu_height),avg(stu_age) from my_student group by class_id;

– 多分组
select class_id,gender,count(*),max(stu_age),min(stu_height),avg(stu_age) from my_student group by class_id,gender;

– 回溯统计
select class_id,count(*) from my_student group by class_id;
select class_id,count(*) from my_student group by class_id with rollup;

– 多分组回溯统计
select class_id,gender,count(*) from my_student group by class_id,gender with rollup;

– having 子句,having是在group by之后,group by是在where之后
select class_id,count(*) as number from my_student group by class_id having
number >= 2;

– Order by排序：根据校对规则对数据进行排序,默认asc升序
select * from my_student order by my_height asc;

– 记录数限制,纯粹的限制获取的数量：从第一条到指定的数量
select * from my_student limit 2;

– 分页获取数据
select * from my_student limit 0,2;
select * from my_student limit 2,2;

– 在条件判断的时候，还有有对应的比较运算符：计算区间 Between 条件1 and 条件2;
select * from my_student where stu_age between 10 to 15;

– and：逻辑与 or：逻辑或 not：逻辑非

– In：在什么里面，是用来替代=
select * from my_student where stu_id in (‘stu0001’,’stu0004’);

– Is是专门用来判断字段是否为NULL的运算符

– 获取男生身高降序，女生身高升序
– orderby在联合查询中若要生效，必须配合使用limit：而limit后面必须跟对应的限制数量
(select * from my_student where gender = ‘男’ order by stu_height desc)
union
(select * from my_student where gender = ‘女’ order by stu_height asc);

– 交叉连接,产生的结果是笛卡尔积，没有实际应用
select * from my_student cross join my_int;

– 内连接
select * from my_student inner join my_score;
select * from my_student inner join my_score on class_id = student_no;