④ Mysql-函数的应用/多表查询（内连接/外连接）

一、函数应用

1) 单行函数

字符函数
① upper(‘x’) 将x中的字符转换为大写
② LOWER(‘x’) 将x中的字符转换为小写

select LOWER(ename) name,LOWER(job) job from emp;

③ length(‘x’) 返回x中的字符数
如:算出员工名字长度/职位名长度

select length(ename) nameno,length(job) jobno from emp;

④ ifnull(X,value)

ifnull(X,value)  如果x为空，返回value，否则返回x

如:查询员工表中的员工的姓名，工资，月收入

select ename,sal,sal+ifnull(comm,0) from emp;

⑤ replace(X,char1,char2)

replace(X,char1,char2) : 将x中的char1替换为char2

如: 将部门表中部门名称中的’A’替换为’b’,查询结果包含部门名称，部门号

select dname,deptno,REPLACE(dname,"A","b") from dept;

⑥ substring(X,start,[length])

substring(X,start,[length])   从x的start位置开始返回长度为length的子字符串

其中的dual为虚拟表
【例】从‘abcdefghijk’中左边返回‘defgh’

select SUBSTRING('abcdefghijk',4,5) from dual;

结果

注意：
start为正数时，从x的左边开始计数，返回length长度的子字符串
start为负数时，从x的右边开始计数，返回length长度的子字符串

如: “abcdefghijk"中输出"ijk”:

select substring("abcdefghijk",-3,3) from dual;

⑥ lpad(X,length,char) 从x的左边使用char将x补齐到长度为length
如: 员工名从左边用 “*” 补齐到10个字符

select ename,LPAD(ename,10,'*') from emp;

⑦ RPAD(X,length,char) 从x的右边使用char将x补齐到长度为length

如: 员工名从右边用 “*” 补齐到10个字符

select ename,RPAD(ename,10,'*') from emp;

⑦ left(X,length) 从x的左边返回length个字符
如: 从 "abcdefg"中从左边返回3个字符:

select LEFT("abcdefg",3) from dual;

⑧ RIGHT(X,length) 从x的右边返回length个字符

如: 从 "abcdefg"中从右边返回3个字符:

select LEFT("abcdefg",3) from dual;

⑨ concat(str1,str2) 将str1和str2连接
如将 “abc” 与 “def” 连接

select concat("abc","def") from dual;

⑨ instr(str,char)

instr(str,char) 返回char在str中第一次出现的位置	

如: 查询员工表中员工姓名中’A’第一次出现的位置

select ename,INSTR(ename,"A") a from emp;

2) 嵌套函数

如：

select RIGHT(LEFT('abcdefg',5),3) from dual;

3) 数字函数

① floor(X)

 floor(X) 返回小于等于x的最大整数

select FLOOR(567.897) from dual;

② ceiling(X)

 ceiling(X) 返回大于等于x的最小整数	

select CEILING(567.897) from dual;

③ mod(X,Y) 返回x除以y的余数
④ round(X,Y) 返回对x精确到y位的结果

【例】查询员工的日薪，查询结果保留两位小数

select sal/30,ROUND(sal/30,2) from emp;

注意：
当y为正数时，表示精确到小数点后第y位
当y为负数时，表示精确到个位前第y位

⑤ truncate(X,Y) 返回对x截取到y位的结果

【例】返回567.789，结果截取到小数点后2位，

select truncate(567.789,2) from dual;

select TRUNCATE(567.789,-2) from dual;

注意：
当y为正数时，表示截取到小数点后第y位
当y为负数时，表示截取到个位前第y位

4) 日期函数

① curdate() 返回当前日期

	select CURDATE();

② curtime() 返回当前时间

	select CURTIME();

③ now() 返回当前日期和时间

	select NOW();

④ last_day(X) 返回日期x所在月份的最后一天的日期

select LAST_DAY("1987-02-03") from dual;

⑤ date_add(X,interval n f)

date_add(X,interval n f)  :返回日期 x 加上间隔时间 n 的结果,f 可以为day/month/year

如: 后33天的日期

select DATE_ADD('1987-02-03',interval 33 day) from dual;

⑥ date_format(X,fmt) 返回日期x按指定格式fmt格式化后的结果
如：返回2021-04-23的月份

select DATE_FORMAT("2021-04-23","%m") from dual;

返回04

select DATE_FORMAT("2021-04-23","%M") from dual;

返回： April

%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

5) 控制流函数

① case

语法：case 属性 when 值1 then 结果1 ......when 值n then 结果n end

如:
查询员工表中的所有员工的姓名，职位，部门号，当部门号为10的时候显示为’财务部’，当部门号为20的时候显示为’研发部’，当部门号为30的时候显示为’销售部’,当部门号为40的时候显示为’运营部’ ,取别名"部门名称"

select ename,job,deptno,case deptno
when 10 then "财务部"
when 20 then "研发部"
when 30 then "销售部"
when 40 then "运营部" end as "部门名称" from emp;

② if
语法：

if(条件,值1,值2) 如果条件为真，返回结果值1；否则，返回结果值2。

如： 查询员工表中所有员工的姓名，工资，如果工资大于等于2500.00，返回’H’，否则返回’L’

select ename,sal,IF(sal>=2500.00,'H','L') "工资等级" from emp order by sal;

6) 分组函数

① count(column) 返回指定列中非NULL值的个数
【例】统计员工表中员工的人数

select COUNT(empno) from emp;

② sum(column) 返回指定列的所有值之和

select SUM(sal+IFNULL(comm,0.00)) from emp;

③ avg(column) 返回指定列的平均值
【例】查询员工表中员工的平均工资

select ROUND(AVG(sal),2) from emp;

④ max(column) 返回指定列的最大值
⑤ MIN(column) 返回指定列的最小值

二、多表查询/内连接/外连接

1) 多表查询

语法：

select  表1.列1,表2.列2,表3.列3,...  from 表1,表2,表3,...  
where  连接条件(n张表联合查询至少需要n-1个连接条件)

2) 内连接

① 等值连接 #主要通过主外键方式实现
【例】查询所有员工的编号，姓名，职位，部门号，部门名称

select emp.empno,emp.ename,emp.job,dept.deptno,dept.dname from emp,dept
where emp.deptno = dept.deptno; #两个表至少要一个条件,不然查询结果有误

② 不等连接 通过条件
【例】查询员工表中所有员工的姓名，工资，工资等级

select e.ename,e.sal,s.grade,s.losal,s.hisal from emp e,salgrade s
where e.sal between s.losal and s.hisal
order by sal;

③ 自连接 #把一张表当作两张表来使用（？标记）
【例】查询员工表中所有员工的编号，姓名，mgr，及mgr的姓名

select m.empno,m.ename,m.mgr "员工表中经理的编号",n.empno "经理表中经理的编号",n.ename from emp m,emp n
where m.mgr = n.empno;

3) 外连接

左外连接、右外连接

语法：select  表1.列1,表2.列2  from  表1 left/right  join 表2  on  表1.列=表2.列;

注意：
左外连接，结果包含左边表的所有数据，右边表显示全部匹配数据
右外连接，结果包含右边表的所有数据，左边表显示全部匹配数据

【例】查询员工表中所有员工的编号，姓名，mgr，及mgr的姓名，要求没有mgr的员工也显示出来

#使用左外连接方法
select w.empno,w.ename,w.mgr,m.ename from emp w left join emp m on w.mgr = m.empno;

#使用右外连接方法
select w.empno,w.ename,w.mgr,m.ename from emp m right join emp w on w.mgr = m.empno;