Mysql基础

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();

流程控制函数

1. 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