MySQL学习笔记——函数

ALTER TABLE tb_emp
ADD diredate VARCHAR(20);#插入数据
INSERT INTO tb_dept()
VALUE(4,&＃39;市场部&＃39;,&＃39;负责市场工作&＃39;);# concat 连接
SELECT CONCAT(NAME,sex) FROM tb_emp;# UPPER 转换大写
SELECT UPPER(NAME) FROM tb_emp WHERE dept_id&＃61;1;# 返回字符串长度
SELECT LENGTH(NAME) FROM tb_emp WHERE dept_id&＃61;1;# 返回部分字符
SELECT SUBSTR(NAME,2,2) FROM tb_emp WHERE dept_id&＃61;1;# 返回当前
SELECT NOW();# 查询时间是1981年
SELECT * FROM tb_emp
WHERE YEAR(diredate) &＃61; 1981
AND MONTH(diredate) &＃61; 1982;#插入时间
INSERT INTO tb_emp(NAME,sex,age,address,email,dept_id,diredate)
VALUES(&＃39;ZHOU&＃39;,&＃39;男&＃39;,33,&＃39;香港&＃39;,&＃39;ZHOU&＃64;163.com&＃39;,2,&＃39;1988-09-09&＃39;);INSERT INTO tb_emp(NAME,sex,age,address,email,dept_id,diredate)
VALUES(&＃39;CAI&＃39;,&＃39;女&＃39;,30,&＃39;香港&＃39;,&＃39;CAI&＃64;163.com&＃39;,2,NOW());# 条件判断语句
SELECT NAME,sex,age &＃39;原来年龄&＃39;CASE WHEN age IS NULL THEN 100ELSE ageEND AS &＃39;年龄&＃39;
FROM tb_emp;# IFNULL函数 如果字段不为NULL&＃xff0c;则取第二个值&＃xff0c;如果为空&＃xff0c;择取第三个值
SELECT NAME,IFNULL(age,age&＃43;100,100) AS age2 FROM tb_emp;# IFNULL函数 如果字段不为NULL&＃xff0c;则直接去该值&＃xff0c;如果为空&＃xff0c;择取第二个值
SELECT NAME,IFNULL(age,100) AS age2 FROM tb_emp;

# 聚合函数&＃xff0c;也叫组合函数&＃xff0c;忽略空值
SELECT AVG(age) FROM tb_emp;SELECT SUM(age) FROM tb_emp;SELECT MAX(age) FROM tb_emp;SELECT MIN(age) FROM tb_emp;SELECT AVG(age) AS &＃39;平均年龄&＃39;,SUM(age) AS &＃39;总年龄&＃39;,MAX(age) AS &＃39;最高年龄&＃39;,MIN(age) AS &＃39;最低年龄&＃39;
FROM tb_emp WHERE dept_id&＃61;1;# COUNT不统计null&＃xff0c;统计的是行数/记录数
SELECT COUNT(*) FROM tb_emp
SELECT COUNT(email) FROM tb_emp# 不统计重复记录
SELECT COUNT(DISTINCT diredate) FROM tb_emp# 分组统计 GROUP BY
# 每个部门的平均年龄
SELECT dept_id,AVG(age) FROM tb_emp GROUP BY dept_idSELECT dept_id,AVG(age),address FROM tb_emp GROUP BY dept_id,address# 限定查询结果 HAVING 不能使用where,where子句中不可以使用函数
SELECT dept_id,AVG(age) FROM tb_emp GROUP BY dept_id
HAVING AVG(age)>23
ORDER BY AVG(age) DESC;# LIMIT 常用来分页
SELECT * FROM tb_emp LIMIT 5; #查询前5个记录
SELECT * FROM tb_emp LIMIT 5,10; #查询前6-10个记录