sql常用函数

平均数avg

select avg(employee.salary) as avg_salary from employee;

最大值max

select max(employee.salary) as max_salary from employee;

最小值min

select min(employee.salary) as min_salary from employee;

求和sum

select sum(employee.salary) as sum_salary from employee;

四舍五入round

select round(avg(employee.salary),2) as avg_salary from employee;

null判空

-- isnull,为空返回1,不为空返回0
-- ifnull(letter,value)为空就返回value值
select isnull(dep_manager_gender),isnull(emp_count),ifnull(dep_manager,'放假啦') from company_manage;

# count计数
-- count计数,统计指定列具有的行数,会筛去null值
-- count(X)
select count(emp_count) from company_manage;
-- count(*)
select count(*) from company_manage;
-- 返回指定列不同值
select count(distinct dep_count) from company_manage;
-- 从employee表中选取年龄在24到27（包括24、27）并且部门是交付和苍盐海的数据
-- 错误写法-->count()函数的用法，可以是count(*)、count(column_name)、count()
select count(age between 24 and 27) as age from employee where dep_name in('交付','苍盐海');
-- 正确写法
select count(*) as age from employee where (age between 24 and 27) and dep_name in('交付','苍盐海');

时间函数

select now() as now;
select current_date as now_date;
select current_time as now_time;
-- now()和current_timestamp是等同的
select current_timestamp as 'current_time';
-- date()函数和time()函数可以提取时间中的年月日、时分秒，并以date、time作为结果集列名
select date(now()) as 'date',time(now()) as 'time';
select date(enter_time) as entry_date from employee;
select employee_name,enter_time,date(enter_time) as entry_date ,time(enter_time) as enter_time from employee;

-- extract()提取指定时间信息
select employee_name,enter_time,extract(hour from enter_time) as enter_hour from employee;
select employee_name,enter_time,extract(minute from enter_time) as enter_minute from employee;
select employee_name,enter_time,extract(second from enter_time) as enter_second from employee;
select employee_name,enter_time,extract(year from enter_time) as enter_year from employee;
select employee_name,enter_time,extract(month from enter_time) as enter_month from employee;
select employee_name,enter_time,extract(day from enter_time) as enter_day from employee;

-- date_format()格式化输出日期,注意：date_format返回的是字符串类型
-- '%Y %m'运行结果示例：2022 08
select employee_name,date_format(enter_time,'%Y %m') as date_format from employee;
-- '%Y %M'运行结果示例：2022 August
select employee_name,date_format(enter_time,'%Y %M') as date_format from employee;
-- 以YYYY-MM-DD HH:mm:SS格式显示日期,示例---> 2022-08-02 12:11:46
select employee_name,date_format(enter_time,'%Y-%m-%d %H:%i:%S') as date_format from employee;

-- 给日期加上一天(推迟一天)
-- 推迟的日期并不会同步到表
select enter_time,date_add(enter_time,interval 1 day) as delay_date from employee;
-- 日期提前一天
select enter_time,date_sub(enter_time,interval 1 day) as new_date from employee;
select enter_time,date_sub(enter_time,interval 2 day) as new_date from employee;
-- 计算到2022-09-13的差值
-- 年
select timestampdiff(year,enter_time,'2022-09-13') as month_diff from employee;
-- 月
select timestampdiff(month,enter_time,'2022-09-13') as month_diff from employee;
-- 日
select timestampdiff(day,enter_time,'2022-09-13') as month_diff from employee;
-- 时分秒
select timestampdiff(hour,enter_time,'2022-09-13') as month_diff from employee;
select timestampdiff(minute,enter_time,'2022-09-13') as month_diff from employee;
select timestampdiff(second,enter_time,'2022-09-13') as month_diff from employee;