Mysql基础
单行函数
字符函数
1 length
2 concat
3 substr
4 instr
5 trim
6 upper
7 lower
8 lpad
9 rpad
10 replace
数学函数
1 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);//1.57
2 ceil向上取整,返回>=该参数的最小整数
SELECT CELL(-1.02);//-1
3 floor 向下取整
SELECT FLOOR(-9.99);//-10
4 truncate 截断
SELECT TRUNCATE(1.69999,1);//1.6
5 mod取余
SELECT MOD(10,3);//1
SELECT MOD(-10,-3);//-1
被除数为正结果为正,被除数为负,结果为负;
MOD(10,3)== a-int(a/b)*b
日期函数
1 now 返回系统当前的日期+时间
SELECT NOW();
2 curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
3 curtime 返回当前时间,不包含日期
SELECT CURTIME();
4 可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW());
S ELECT YEAR('1998-1-1')
SELECT YEAR(HIREDATE) FROM employees;
SELECT MONTH(NOW()) 月
SELECT MONTHNAME(NOW()) 月
5 将日期格式的字符串转成指定格式的日期
select str_to_date('1998-5-6','%Y-%c-%d') AS out_put
6 查询入职日期位1992–2-3员工的信息
select * from employees where hiredate = '1992--2-3';
select * from employees where hiredate = STR_TO_DATE('4-3 1992');
7 date_format 将日期转换成字符
select DATE_FORMAT(NOW(),'%y年%n月%d日') AS out_put;
8 查询有奖金的员工名和入职日期(月日 年)
select last_name,DATR_FORMAT(hiredate,'%m月、%d日 %y年') 入职日期
FROM employees
WHERE commission_pct is NOT NULL;
其他函数
select version();
select DATABASE();
select USER();
流程控制函数
- if函数:if else 的效果
SELECT IF(10<5,'大',’小‘);
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵',’有奖金,嘻嘻‘ ) 备注 FROM employees;
2.case函数的使用一:switch case 的效果
//java
switch(变量或表达式)
{
case 常量1: 语句1;break;
...
default: 语句n;break;
}
//mysql
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句2;
when 常量2 then要显示的值2或语句2;
···
else 要显示的值n或者语句n;
end
/***
案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门号=60,显示的工资为原工资
***/
SELECT salary 原始工资,department_id,
CASE department_id
when 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE AS 新工资
FROM employees;
3 case函数的使用二:类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then要显示的值2或语句2
···
else要显示的值n或语句n
#案例:
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
SELECT salsry,
CASE
WHEN salary >20000 THEN 'A'
WHEN salary >15000 THEN 'B'
WHEN sarlary>10000THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
分组函数
功能:用作统计,又称为聚合函数或组合函数
分类:
sum 求和 avg平均值 max最大值 min最小值 count 计算个数
特点:
1、sum、avg一般用于处理数值类型
max、min可以处理任何数值
2、以上函数都忽略null值
3、可以和distinct搭配使用去重运算
简单的使用
4 COUNT函数
COUNT(*)统计行数
5 和分组函数一同查询的字段要求是group by后的字段
SELECT SUM(salary)FROM employees;
SELECT AVG(salary)FROM employees;
SELECT MAX(salary)FROM employees;
SELECT MIN(salary)FROM employees;
SELECT COUNT(salary)FROM employees;
2、参数类型
SELECT SUM(last_name),AVG(last_name) FROM employees;
// SUM:0 AVG:0 没有逻辑意义,不建议
SELECT SUM(hiredate),AVG(hiredate) FROM employees;
//无意义 同上
SELECT MAX(last_name),MIN(last_name) FROM employees;
//支持的 max:ZLotkey min:Abel
//字典排序,有可比较性
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
//MAX:2016-03-03 00:00:00 MIN:1992-04-03 00:00:00
SELECT COUNT(commission_pct)FROM employees;
SELECT COUNT (last_name) FROM employees;
//count计算不为null的值
3 是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,
SUM(commission_pct)/105 FROM employees
SELECT COUNT(commission_pct) FROM employees;
由上可得,null没有参与运算
4 和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),SUM(salary) FROM employees;
5 COUNT详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
//
SELECT COUNT(1) FROM employees;
//统计1 的个数
效率:
MYISAM存储引擎下,COUNT(*)效率高
INNODB存储引擎下,COUNT(*)和COUNT(1)效率一样高
6、和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id FROM employees;//逻辑错
分组查询
查询每个部门的平均工资;
特点:
1、分组查询中的筛选条件分为两类
数据源
分组前筛选
分组后筛选
案例1 :
查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
案例2:
查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM department
GROUP BY location_id;
添加筛选条件
案例1:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id
案例2:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加筛选条件
案例1:查询哪个部门的员工个数>2
1 查询每个部门的员工个数
SELECT COUNT(*)
FROM employees
GROUP BY department_id;
2 根据1的结果进行筛选,查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
案例2:查询每个工种有奖金的员工的最高工资>12000 的工种编号和最高工资
1查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id
2根据1结果继续筛选,最高工资>12000
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id
HAVING MAX(salary)>12000
案例3:查询领导编号>102的每个领导手下的最低工资>5000 的领导编号是哪个,以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
GROUP BY manger_id
HAVING MIN(salary)>5000