【学习笔记】大数据全栈成长计划-第三章:Mysql函数

举报
真爱无敌 发表于 2021/01/13 20:02:13 2021/01/13
【摘要】  第三章: Mysql函数--常见函数/*概念:类似于JAVA的方法,将一组逻辑语句封装在方法体中,对外暴露方法名好处:1、隐藏了实现细节 2、提高代码的重用性调用:select 函数名(实参列表) 【from 表】特点:  1)叫什么(函数名)  2)干什么(函数功能)分类:      1)单行函数          如:concat、length、ifnull等      2)分组函数 ...

 

第三章: Mysql函数

--常见函数
/*
概念:类似于JAVA的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1、隐藏了实现细节 2、提高代码的重用性
调用:select 函数名(实参列表) 【from 表】
特点:  1)叫什么(函数名)  2)干什么(函数功能)
分类:
      1)单行函数
          如:concat、length、ifnull等
      2)分组函数
         功能:做统计使用,又称为统计函数、聚合函数、组函数
常见函数
    字符函数:length, concat, substr, instr, trim, upper, lower, lpad, rpad, replace
    数学函数:round, ceil, floor, truncate, mod
    日期函数:now, curdate, curtime, year, month, monthname, day, hour, minute, second, str_to_date, date_format
    其他函数:version, database, user
    控制函数:if, case
*/

--单行函数

一、字符函数
# 1.length 获取参数值的字节个数
select length('john');

# 2.concat 拼接字符串
select concat(last_name,'_',first_name) 姓名 from employees;

# 3.upper、lower
select upper('john');
select lower('john');

# 4.substr、substring
注意:索引从1开始
select substr('dafdfdsafdsaf',7) out_put;

# 5.instr 返回子串第一次出现的索引,如果找不到返回0
select instr('杨不悔殷六侠爱上了殷六侠','殷八侠') as out_put;

# 6.trim 截取前后空格
select length(trim('     张翠山    ')) as out_put;
select trim('aa' from 'aaaaaaa张aaa翠aaaa山aaaaaaaa') as out_put;

# 7.lpad 用指定的字符实现左填充指定长度
select lpad('殷素素',2,'*') as out_put;

# 8.replace 替换
select replace('周芷若周芷若张无忌周芷若爱上了张无忌','周芷若','赵敏') as out_put;

二、数学函数
# 1.round 四舍五入
select round(-1.55);

# 2.ceil 向上取整,返回>=该参数的最小整数
select ceil(-1.02);

# 3.floor 向下取证,返回<=该参数的最大整数
select floor(-9.99);

# 4.truncate 截断
select truncate(1.69999,1);

# 5.mod 取余
select mod(10,-3)
select 10%3;

三、日期函数

# 1.now  返回当前系统日期+时间
select now();

# 2.curdate 返回当前系统日期,不包含时间
select curdate();

# 3.curtime 返回当前时间,不包含日期
select curtime();

# 可以获取指定的部分,年、月、日、小时、分钟、秒
select YEAR(NOW()) 年;
select YEAR('1998-1-1') 年;
select YEAR(hiredate) 年 from employees;
select MONTH(NOW()) 月;
select MONTHNAME(NOW()) 月;

# 4.str_to_date 将字符通过指定的格式转换成日期
select str_to_date('1998-3-3','%Y-%c-%d) as out_put;

# 5.date_format 将日期转换成字符
select  date_format('2018/6/6', '%Y年%m月%d日') as out_put;


四、其他函数
 
select version();
select database();
select user();

五、流程控制函数

# 1.if函数   if else 的效果
select if(10<5,'大','小');

# 2.case函数  switch case的效果
/* 
JAVA中
switch(变量或表达式) {
  case 常量1: 语句1;break;
  ...
  default: 语句n;break;
}

Mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/

#案例:查询员工的工资情况,如果工资>20000,显示A级别;如果工资>15000,显示B级别;如果工资>10000,显示C级别;否则显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

--分组函数
/*
功能:用作统计使用,又称为聚合函数或统计函数或组函数

分类:
sum 求和、 avg 平均值、max 最大值、 min 最小值、 count 计算个数

特点:
1、sum,avg一般用于处理数值型
      max,min,count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配使用
4、count函数的单独介绍
      一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by 后的字段
*/

# 1.count函数的详细介绍


效率:
MYISAM存储引擎下,count(*)的效率高
INNODB存储引擎下,count(*)和count(1)的效率差不多,比COUNT(字段)要高一些

# 2.和分组函数一同查询的字段有限制


--分组查询
/*
语法:
         select 分组函数,列(要求出现在group by 的后面)
         from  表
          【where 筛选条件】
         group by 分组的列表
          【order by 子句】
注意:查询列表必须特殊,要求是分组函数和group by后出现的字段
特点:1.分组查询中的筛选条件分为两类:

分类
数据源
位置
关键字
分组前筛选
原始表
group by 子句的前面
where
分组后筛选
分组后的结果集
group by 子句的后面
having
          1)分组函数做条件肯定是放在having子句中
          2)能用分组前筛选的,就优先考虑使用分组前筛选
        2. group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求)
        3.也可以添加排序(排序放在整个分组查询的最后)
*/

# 简单的分组查询
select max(salary),job_id from employees group by job_id;
select count(*) ,location_id from departments group by location_id;

# 添加筛选条件
select avg(salary), department_id from employees where email like '%a%' group by department_id;

# 添加复杂的筛选条件
select count(*), department_id from employees group by department_id having count(*) > 2;

# 按表达式或函数分组
select count(*) c, length(last_name) len_name
from employees
group by length(last_name)
having c > 5;

# 按多个字段分组
select avg(salary),department_id,job_id from employees group by job_id,department_id;

# 添加排序
select avg(salary) a,department_id,job_id 
from employees 
where department_id is not null
group by job_id,department_id
having a > 10000
order by a desc;


--连接查询
/*
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询

笛卡尔积现象:表1有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件

分类:
   按年代分类:sql92标准(仅支持内连接), sql99标准【推荐】(支持内连接+外连接+交叉连接)
   按功能分类:内连接(等值连接、非等值连接、自连接),外连接(左外连接,右外连接,全外连接),交叉连接


*/

# 笛卡尔积现象:


#1、等值连接
/*
1)多表等值连接的结果为多表的交集部分
2)n表连接,至少需要n-1个连接条件
3)多表的顺序没有要求
4)一般需要为表起别名
5)可以搭配前面介绍的所有子句使用,比如排序、分组、筛选等
*/
#案例1、
select name, boyName from boys, beauty where beauty.boyfriend_id = boys.id;

#案例2、为表起别名:提高语句的简洁度,区分多个重名的字段
select e.last_name, e.job_id, j.job_title from employees e, jobs j where e.job_id = j.job_id;

#案例3、两个表的顺序是否可以调换?
select e.last_name, e.job_id, j.job_title from  jobs j,employees e  where e.job_id = j.job_id;

#案例4、可以加筛选?
select last_name, department_name, commission_pct from employees e, departments d 
where e.department_id = d.department_id
and e.commission_pct is not null;

#案例5、可以加分组?
select count(*) 个数, city from departments d, locations l
where d.location_id = l.location_id 
group by city;

#案例6、可以加排序?
select job_title, count(*) from employees e, jobs j
where e.job_id = j.job_id 
group by  job_title 
order by count(*) desc;

#案例7、可以实现三表连接?
select last_name, department_name, city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and city like 's%'
order by department_name desc;

#2、非等值连接

#案例1.
select salary, grade_level from employees e, job_grades g
where salary between g.lowest_sal and g.highest_sal and g.grade_level='A';

#3、自连接
#案例
select e.employee_id, e.last_name, m.employee_id, m.last_name
from employees e, employees m
where e.manager_id = m.employee.id;

 

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200