- 分组查询 group by,group_concat : 将多个字符串连接成一个字符串.分组目的:聚合统计。select的非聚合字段 必须在group by 中
- select 分组 from stu group by 分组;
- select gen ,count(*) from stu group by gen; 查询每种gen的总数。先执行分组,然后执行聚合函数在每个分组数据上
- select gen ,group_concat(gen) as info from stu group by gen; 图1
- select gen,avg(age) from stu group by gen; 查询每个性别的平均身高 图2
- select gen ,group_concat(name),avg(age) from stu group by gen having avg(age)>10; 图3
- 对分组之后的数据进行进一步筛选,使用having 不能使用where
- select gen,count(*) from stu group by gen having gen =1;
- having 和 where的区别
- having 是对分组之后的数据进行进一步筛选,有having必然有group by 反之不一定
- where 是对元数据进行筛选和操作
- 分页查询
- 显示从m到n条数据(m<n),跳过m条数据向后找n条 :select * from stu limit m,n;
- limit 要写在后面,select * from stu order by age limit 1,4;
- 多表查询
- 交叉连接(笛卡尔积查询) select * from table1 , table 2;
- 内连接,根据某个条件在两个表中的值都相同时保留下来,进行合并。on和where都可以
- select s.*,c.* from stu as s join cla as c on c.cid = s.cid; 默认join是内连接
- select s.*,c.* from stu as s inner join cla as c on c.cid = s.cid;
- select s.*,c.* from stu as s inner cross cla as c on c.cid = s.cid;
- 外连接:左外连接和右外连接,
- 左外连接:左表为主表,保留左边全部。不满足条件的部分以null填充
- select s.*,c.* from stu as s left {outer} join cla as c on c.cid = s.cid; outer可以省略
- 右外连接:右表为主表,保留右边全部。不满足条件的部分以null填充
- select s.*,c.* from stu as s right {outer} join cla as c on c.cid = s.cid; outer可以省略