sql 查询（分组查询）

分组查询函数

顺序总结 select from join on (where) groupby having orderby

group by

SELECT * FROM studetail;
group by stusex  根据性别进行分组
count() 统计个数
SELECT stusex, count(stusex) FROM studetail group by stusex ;

根据年龄进行分组统计个数
SELECT  stuage,count(*) FROM studetail group by stuage ;
（*）这里代表的是goupby 后面的stuage

根据性别进行分组求平均年龄
SELECT  stusex,avg(stuage)  FROM studetail group by stusex ;

根据性别分组  求最大值  / min  求最小年龄   
SELECT  stusex,max(stuage)  FROM studetail group by stusex ;


sum  求年龄的总和
SELECT  stusex,sum(stuage)  FROM studetail group by stusex ;

#HAVING  
条件的意思 与where差不多
SELECT * FROM studetail WHERE stuage >18;

分组后 还有其他条件  
根据性别进行分组 ，求分组后的平均年龄，获取平均年龄大于18岁的信息
SELECT  stusex,avg(stuage)  FROM studetail group by stusex HAVING avg(stuage) = 18;

根据工作岗位进行分组，求每一个组的平均工资，筛选平均工资大于5000的
SELECT job_position,avg(salary),count(*) FROM emp group by job_position HAVING avg(salary) > 5000 ;
SELECT sum(salary) FROM emp ;
SELECT avg(salary) FROM emp ;
SELECT max(salary) FROM emp ;

HAVING
根据工作岗位进行分组，求每一个组的平均工资，筛选平均工资大于5000的  从大到小排序
order by   desc  asc
SELECT job_position,avg(salary),count(*) FROM emp
group by job_position
HAVING avg(salary) > 5000
order by avg(salary) desc;

SELECT job_position,avg(salary),count(*) FROM emp
group by job_position
order by avg(salary) desc;

limit 限制行号

SELECT job_position,avg(salary),count(*) FROM emp
group by job_position
HAVING avg(salary) > 5000
order by avg(salary) desc
limit 3;


SELECT id,empname,salary FROM emp order by salary desc;

SELECT id,empname,salary FROM emp order by salary desc limit 3;

IFNULL

WITH ROLLUP 分组之后进行job_position求和IFNULL job_position
SELECT ifnull(job_position,"部门总和"),count(job_position) FROM emp
group by job_position  WITH ROLLUP;

SELECT deptid FROM students;
SELECT IFNULL(deptid,"1011") FROM students;

DISTINCT 去重

SELECT DISTINCT empname FROM emp;

left 截取

SELECT   left(salary,4) FROM emp;
SELECT   right(salary,3) FROM emp;
SELECT   substring(salary,1,4) FROM emp;  # 取第一个到第四个

CONCAT

SELECT CONCAT(salary,empname) as namesalry FROM emp;

SELECT NOW(); # 查询当前时间

SELECT * FROM department;
+-----+--------------------+
| did | dname |
+-----+--------------------+
| 102 | 上海交通大学 |
| 103 | 浙江大学 |
| 101 | 清华大学 |
| 105 | 理工学院 |
+-----+--------------------+
SELECT * FROM department join students on ;

+-----+---------+--------+
| sid | sname | deptid |
+-----+---------+--------+
| 1 | 张三 | 101 |
| 2 | 李四 | 102 |
| 3 | 王五 | 103 |
| 4 | 赵六 | 101 |
| 5 | 孙琪 | 102 |
| 6 | 小花 | 103 |
| 8 | 小测 | 105 |
| 9 | c俄式 | NULL |
+-----+---------+--------+

索引

建立约束的时候 会创建索引

show keys from students;
索引  书籍目录
id 

模糊查询

like _一个字符 % 任意给字符

SELECT empname from emp WHERE  empname like "_花";
SELECT empname from emp WHERE  empname like "%花%";