字符串
1、CONCAT(S1,S2,...Sn) :字符串拼接,将S1 , S2 , ... Sn 拼接成一个字符串】
2、LOWER(str) :将字符串str全部转为小写
3、UPPER(str) :将字符串str全部转为大写
4、LPAD(str,n,pad): 左填充,用字符串pad对 str 的左边进行填充,达到 n 个字符
串长度
5、RPAD(str,n,pad): 右填充,用字符串pad 对 str 的右边进行填充,达到 n 个字符
串长度
6、TRIM(str): 去掉字符串头部和尾部的空格
7、SUBSTRING(str,start,len): 返回从字符串str 从 start 位置起的 len 个长度的字符
数值函数
1、CEIL(x): 向上取整
2、FLOOR(x): 向下取整
3、MOD(x,y): 返回x/y 的模
4、RAND(): 返回0~1 内的随机数
5、ROUND(x,y): 求参数x 的四舍五入的值,保留 y 位小数
日期函数
1、CURDATE():返回当前日期
2、CURTIME():返回当前时间
3、NOW():返回当前日期和时间
4、YEAR(date):获取指定date 的年份
5、MONTH(date):获取指定date 的月份
6、DAY(date):获取指定date 的日期
7、DATE_ADD(date, INTERVAL expr type) : 返回一个日期/ 时间值加上一个时间间隔 expr 后的时间值
例: select date_add(now(), INTERVAL 70 YEAR ); //基于现在的时间增加70年
8、DATEDIFF(date1,date2):返回起始时间date1 和 结束时间 date2 之间的天数
流程函数
1、IF(value , t , f) :如果value 为 true ,则返回 t ,否则返回 f
2、IFNULL(value1 , value2):如果value1 不为空,返回 value1 ,否则返回value2
3、CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END:如果val1 为 true ,返回 res1 , ... 否则返回default 默认值
4、CASE [ expr ] WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END:如果expr 的值等于 val 返回
res1 , ... 否则返回 default 默认值
演示如下:
if
select if( false , 'Ok' , 'Error' );
ifnull
select ifnull( 'Ok' , 'Default' );
select ifnull( '' , 'Default' );
select ifnull( null , 'Default' );
case when then else end
需求 : 查询 emp 表的员工姓名和工作地址 ( 北京 / 上海 ----> 一线城市 , 其他 ----> 二线城市 )
具体的 SQL 语句如下 :
select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else
'二线城市' end ) as '工作地址'
from emp;create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95
), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);select
id,
name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )
'数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'
end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'
end ) '语文'
from score;
连接查询
内连接:
相当于查询A 、 B 交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
内连接
隐式内连接
SELECT 字段列表 FROM 表 1 , 表 2 WHERE 条件 ... ;
显示内连接
SELECT 字段列表 FROM 表 1 [ INNER ] JOIN 表 2 ON 连接条件 ... ;
外连接
左外连接
左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表 1 LEFT [ OUTER ] JOIN 表 2 ON 条件 ... ;
右外连接
右外连接相当于查询表 2( 右表 ) 的所有数据,当然也包含表 1 和表 2 交集部分的数据。
SELECT 字段列表 FROM 表 1 RIGHT [ OUTER ] JOIN 表 2 ON 条件 ... ;
案例 :
查询 emp 表的所有数据 , 和对应的部门信息,由于需求中提到,要查询emp 的所有数据,所以是不能内连接查询的,需要考虑使用外连接查询。
表结构 : emp, dept
连接条件 : emp.dept_id = dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
查询dept表的所有数据, 和对应的员工信息
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
自连接
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。我们先来学习一下自连接
的查询语法:
SELECT 字段列表 FROM 表 A 别名 A JOIN 表 A 别名 B ON 条件 ... ;