mysql函数，groupby

单行函数

# abs绝对值
# sign正数返回1, 负数返回-1, 0返回0
# PI()返回圆周率
# ceil,ceiling返回大于等于该数的最小整数
# floor返回小于等于该数的最大整数
# mod返回余数
# truncate截断
# round四舍五入
select abs(-123),sign(-23),PI(),ceil(32.61),ceiling(-42.23),floor(32.32),floor(-43.23),mod(12,5);
# ln,log指自然对数
# exp指以e为底的指数
# log10指以10为底的对数
# log2指以2为底的对数
select ln(exp(2)),log(exp(2)),log10(10),log2(4);
# bin将10进制的数转为2进制表示
# hex将10进制的数转为16进制表示
# oct将10进制的数转为8进制表示
# conv将某一进制的数转为另一进制表示
select bin(10),hex(10),oct(10),conv(10,2,8);

# ascii返回字符串中第一个字符的ascii值
# char_length返回字符数
# length返回字节数(和字符集有关)
# concat连接字符串
select ascii('akos'),char_length('hello'),char_length('我们'),length('hello'),length('我们');
# 将某行的数据删除(该行的maker字段的数据内容是中文)
delete from 表名 where length(maker)!=char_length(maker);
select concat(emp.last_name,' worked for ',mgr.last_name) as "details"
from employees emp
inner join employees mgr on emp.manager_id = mgr.employee_id;
# 字符串索引从1开始
# insert从第二个字符开始,取三个替换为aaaaa
# replace将hello中的ll替换为mm,如果在hello中找不到ll,就不替换,返回hello即可
# upper全部转为大写,lower全部转为小写
select insert('helloworld',2,3,'aaaaa'),replace('hello','ll','mm');
select upper('Hello'),lower('Hello');
# left从左边开始取n个字符
# right从右边开始取n个字符
# n >= char_length(str),则返回该字符串
select left('hello',2),right('hello',3),right('hello',13);
# lpad用*对最左边进行填充,直到字符串的长度为10个字符
# 如果设置的长度小于本身的长度,则会进行截取
select employee_id,last_name,lpad(salary,10,'*'),salary from employees;

# current_date2023-01-11,current_time17:40:50
# now,sysdate,current_timestamp,localtime,localtimestamp2023-01-11 17:40:50
# utc_date2023-01-11,utc_time09:40:50
select current_date(),current_time(),
now(),sysdate(),current_timestamp(),localtime(),localtimestamp(),
utc_date(),utc_time()
from dual;
# unix_timestamp返回当前或指定时间的时间戳
# from_unixtime将时间戳转换为普通格式的时间
select unix_timestamp(),unix_timestamp('2021-10-01'),unix_timestamp('2021-10-01 12:12:32'),
from_unixtime(1673430804),from_unixtime(1633017600)
from dual;

# 流程控制函数
# if(condition,res1,res2)等同于condition ? res1:res2
select last_name,salary,if(salary >= 6000,'高工资','低工资') as "details" from employees;
# 类似于if else
select last_name,salary,case when salary >= 15000 then '白骨精'
							 when salary >= 10000 then '潜力股'
							 when salary >= 8000 then 'jkl'
							 else 'axa' end as "details"
from employees;
# 类似于switch
select last_name,department_id,salary,case department_id when 10 then salary * 1.1
														 when 20 then salary * 1.2
														 when 30 then salary * 1.3
														 else salary * 1.4
														 end as "details"
from employees;

# 加密
# password,encode,decode在mysql8中均已弃用
select password('123'),md5('123'),sha('123');
select encode('明文','seed'),decode(encode('明文','seed'),'seed');
# 信息函数
# mysql版本号,当前连接数,当前所在数据库,用户名,字符集,比较规则
select version(),connection_id(),database(),user(),charset('尚硅谷mysql不错'),collation('尚硅谷mysql不错');
# 函数还有很多,需要的时候再查吧

聚合函数

# avg = sum / count
# 平均值,总和(salary为null的不被计入),最大/最小
select avg(salary),sum(salary),max(salary),min(salary) from employees;
# 有多少条记录(manager_id为null的不算)
select count(manager_id) from employees;
# 表有多少条记录
select count(*),count(1),count(2) from employees;

group by

# 将表中的数据分成若干组
# 各个部门的平均工资,最高工资
select department_id,count(*) as "部门人数",avg(salary),max(salary)
from employees
group by department_id;
# 各个job_id的平均工资
select job_id,avg(salary)
from employees
group by job_id;
# 依据departmen_id和job_id分组
select department_id,job_id,avg(salary)
from employees
group by department_id,job_id;

# select中只能有group by包括的列和聚合函数
# 聚合函数的参数可以不在group by中出现
# error(job_id没有在group by中出现)
select department_id,job_id from employees group by department_id;

# error(manager_id需要出现在group by当中)
# In aggregated query without GROUP BY
# expression #2 of SELECT list contains nonaggregated column 'atguigudb.employees.manager_id';
# this is incompatible with sql_mode=only_full_group_by
select count(manager_id),manager_id from employees;
# ok
select count(manager_id),manager_id from employees group by manager_id;

having

得到符合要求的组

# 部门最高工资大于10000的部门信息
select department_id,max(salary)
from employees
group by department_id
having max(salary) > 10000;
# 部门id为10,20,30中最高工资大于10000的部门信息
# 方式1(推荐,效率优于方式2)
select department_id,max(salary)
from employees
where department_id in(10,20,30)
group by department_id
having max(salary) > 10000;
# 方式2
select department_id,max(salary)
from employees
group by department_id
having max(salary) > 10000 and department_id in (10,20,30);

筛选数据时
where不能使用聚合函数
只有having可以使用聚合函数
没有group by,having也可以使用,但在开发中使用having的前提是使用了group by
having要写在group by的后面

在连接多表中,where先筛选后连接,having先连接后筛选
where比having效率高
where会直接筛选过滤,having要先group by(如果有的话),然后对每一个组进行判断