快速上手MySql && MySql GUI工具 SQLyog Community （4）

/*

一、常用函数：
1）字符串函数
#连接字符串
SELECT * FROM tb_dept;
SELECT CONCAT(NAME,loc) FROM tb_dept;
upper转换大写
length返回字符串长度
substr(str,pos,n)从str字符串中从pos位置开始的n个位置复制
2）数值函数
3）日期和时间函数
now();返回当前日期和时间
year();month();

4）流程函数 
条件判断：case  
    when XX then XX
    else XX
      end as XX
 
ifnull()函数，如果字段不为空，则取第一个值；如果为空，取第二个值。
if()函数，如果字段不为空，则直接返回第二个值；如果为空，返回第三个值。
5）其他常用函数

二、组函数
1）聚合函数对一组值进行运算，并返回单个值。也叫组合函数。
-COUNT(*列名) 统计行数，不统计null
-AVG(数值类型列名) 平均值
-SUM(数值类型列名) 求和
-MAX（列名） 最大值
-MIN（列名） 最小值
2）除了COUNT()以外，聚合函数都会忽略NULL值。

*/
SELECT MAX(sal) AS 工资总额,MAX(sal) AS 最高工资,
MIN(sal) AS 最低工资,AVG(sal) AS 平均工资,
FROM tb_emp;

SELECT COUNT(*) FROM tb_emp;#统计行数，不统计null

SELECT COUNT(comm) FROM tb_emp;#不统计null

SELECT COUNT(deptno) FROM tb_emp;

SELECT COUNT(DISTINCT deptno) FROM tb_emp;

SELECT AVG(comm) FROM tb_emp;#组函数会忽略空值
SELECT IFNULL(comm,0) FROM tb_emp;#如果字段不为空，则取第一个值；如果为空，取第二个值。

SELECT AVG(IFNULL(comm,0)) FROM tb_emp;

/*

分组统计

（1）GROUP BY子句真正作用在于与各种聚合函数配合使用。用来对查询出来的数据进行分组。
（2）分组的函数是把该列具有相同值得多条记录当做一组记录处理，最后只输出一条记录。
（3）分组函数忽略空值。
（4）结果急隐式按升序排列，如果需要改变排序方式可以使用Order by子句。


SELECTcolumn,group_function
FROMtable
[WHEREcondition]
[GROUP BYgroup_by_expression]
[ORDER BYcolumn];


分组函数的重要规则
1）如果使用了分组函数，或者使用GROUPBY的查询：出现在SELECT列表中的字段，
要么出现在组合函数里，要么出现在GROUP BY子句中。
2）GROUP BY子句的字段可以不出现在SELECT列表当中。
3）使用集合函数可以不使用GROUP BY子句，此时所有的查询结果作为一组。

组函数的错误用法
1）不能再WHERE子句中限制组
2）限制组必须使用HAVING子句
3）不能再WHERE子句中使用组函数

*/

#每个部门平均工资
SELECT deptno,AVG(sal) FROM tb_emp GROUP BY deptno;
#每个部门员工数
SELECT deptno,COUNT(*) FROM tb_emp GROUP BY deptno

SELECT deptno, AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM tb_emp
GROUP BY deptno;

SELECT deptno,job,AVG(sal) FROM tb_emp GROUP BY deptno,job;
SELECT deptno,job,AVG(sal) FROM tb_emp WHERE deptno=10 GROUP BY deptno,job;

/*
限定组的结果：HAVING子句，用来对分组后的结果再进行条件过滤
SELECTcolumn,group_function
FROMtable
[WHEREcondition]
[GROUP BYgroup_by_expression]
[ORDER BYcolumn];
[HAVINGgroup_condition]
[ORDER BYcolumn];

*/

SELECT deptno, AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM tb_emp
#where AVG(sal)>2000#where子句不可以使用组函数 
GROUP BY deptno
HAVING AVG(sal)>2000;

/*HAVING 和 WHERE的区别
WHERE是在分组前进行条件过滤，HAVING子句是在分组后进行条件过滤；
WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数；
*/

SELECT deptno, AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM tb_emp
#where AVG(sal)>2000#where子句不可以使用组函数 
GROUP BY deptno
HAVING AVG(sal)>2000
ORDER BY AVG(sal) ASC;
/*
查询结果限定
1）在SELECT语句最后可以用LIMIT来限定查询结果返回的起始记录和总数量。（MySql特有）
2）SELECT ...LIMIT offset_start,row_count;
offset_start：第一个返回记录行的偏移量，默认为0
row_count：要返回记录行的最大数目

*/
#LIMIT，常用来分页
SELECT * FROM tb_emp LIMIT 5;#检索前五个记录
SELECT * FROM tb_emp LIMIT 5,10;#检索记录行6~15

/*下面这条语句应该是错的.
因为根据“如果使用了分组函数，或者使用GROUPBY的查询：出现在SELECT列表中的字段，要么出现在组合函数里，要么出现在GROUP BY子句中。
但是此处却运行成功。
*/
SELECT deptno,sal,COUNT(*)
FROM tb_emp
GROUP BY deptno
HAVING sal>800