温习mysql函数 & 连接查询

最新推荐文章于 2024-10-18 02:41:46 发布

原创最新推荐文章于 2024-10-18 02:41:46 发布 · 1.2k 阅读

23 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库

字符串

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默认值

演示如下：

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 条件 ... ;