MySQL底子 [六] - 内置函数+复合查询+表的内连和外连
内置函数一般要用select调用内置函数
日期函数
https://i-blog.csdnimg.cn/direct/a901be03ae9145d7996f273bb6aad6f5.png
current_date函数
current_date函数用于获取当前的日期。如下：
https://i-blog.csdnimg.cn/direct/9cc97b6fc817470f99e01ab399f91f00.png
current_time函数
current_time函数用于获取当前的时间。如下：
https://i-blog.csdnimg.cn/direct/50e8de217fbf4a17b0fe8a3f61103a17.png
now函数
now函数用于获取当前的日期时间。如下：
https://i-blog.csdnimg.cn/direct/fcb1eb46c97d43ceaf8aa273c37c69bb.png
date函数
date函数用于获取当前的日期时间。如下：
https://i-blog.csdnimg.cn/direct/0012eac5965e43eba711690c40421d71.png
date_add函数
date_add函数用于在日期的底子上添加日期或时间。如下：
https://i-blog.csdnimg.cn/direct/62a58036a5fe4ac781184b3479a7c27f.png
如果在date_add函数中添加的日期/时间为负值，则相当于在日期的底子上减去日期/时间。如下：
https://i-blog.csdnimg.cn/direct/a0cfcea11e3340df8d37ab0316302328.png
date_sub函数
date_sub函数用于在日期的底子上减去日期或时间。如下：
https://i-blog.csdnimg.cn/direct/6a26008a7e3d4f89b5b0be53dd41b690.png
如果在date_sub函数中减去的日期/时间为负值，则相当于在日期的底子上添加日期/时间。如下：
https://i-blog.csdnimg.cn/direct/8ce932088fe54c39bb2b44be88a0ffdf.png
datediff函数
datediff函数用于获取两个日期的差，单位是天。如下：
https://i-blog.csdnimg.cn/direct/350651d1dd18426c86ad8eb8f2dd8c89.png
综合案例
创建一个评论表，表中包含自增长的主键id、昵称、评论内容和评论时间。如下：
https://i-blog.csdnimg.cn/direct/b5e3b36aa6944bc98351af993715ba05.png
向表中插入一些数据，插入时直接通过now函数指明评论时间。如下：
https://i-blog.csdnimg.cn/direct/b4b73d6643474da99af30326ed80883e.png
在显示评论信息时，如果只想显示评论的日期而不显示评论的时间，可以在查询sendtime字段时，通过date函数截取sendtime的日期部门进行显示。如下：
https://i-blog.csdnimg.cn/direct/a478169dc7e244ff81b072f832b68a40.png
再不定时向表中插入一些数据。如下：
https://i-blog.csdnimg.cn/direct/83790a6716844f3289e38f482cb6f218.png
在显示评论信息时，如果要查询2分钟内发布的评论信息，现实就是要筛选出评论时间加上2分钟后大于当前时间的评论，这时需要同时借助date_add和now函数。如下：
https://i-blog.csdnimg.cn/direct/a457af83a4fc4e3382e4b5d50e52ffe8.png
字符串函数
https://i-blog.csdnimg.cn/direct/52d935e30f01412b9acd7f7c57c379ba.png
charset函数
现有如下员工表，要求获取员工表中ename列利用的字符集。如下：
https://i-blog.csdnimg.cn/direct/4466711e85cc4f7e8b926f514f63f313.png
在查询员工表中的信息时，利用charset函数获取ename列利用的字符集即可。如下：
https://i-blog.csdnimg.cn/direct/7108ded28a4f4d9fba20e5fe33c882fc.png
concat函数
现有如下成绩表，要求以“XXX的语文是XX分，数学是XX分，英语是XX分”的格式显示成绩表中的信息。如下：
https://i-blog.csdnimg.cn/direct/98272f1204c9410eb78eb13f7a89a930.png
在查询成绩表中的信息时，利用concat函数按要求进行字符串连接即可。如下：
https://i-blog.csdnimg.cn/direct/7419fa73e79045269b4c200e61d2172f.png
instr函数
instr函数用于获取一个字符串在另一个字符串中初次出现的位置，如果没有出现则返回0。如下：
https://i-blog.csdnimg.cn/direct/8d881f0d56cd40bcbca2c8d0b0017470.png
ucase函数
ucase函数用于获取转换成大写后的字符串。如下：
https://i-blog.csdnimg.cn/direct/1906dfc73aae45138c0520cd77fe3c93.png
lcase函数
lcase函数用于获取转换成小写后的字符串。如下：
https://i-blog.csdnimg.cn/direct/8f89ea37ff1b40b48570719c686d0513.png
left函数
left函数用于从字符串的左边开始，向后截取指定个数的字符。如下：
https://i-blog.csdnimg.cn/direct/6d937c4fe5184fd2bf51b473da8fcf59.png
length函数
length函数用于获取字符串占用的字节数。如下：
https://i-blog.csdnimg.cn/direct/3e868c04747f4b4c82d1c141019d74ee.png
阐明一下： 对于多字节字符来说，不同编码中一个字符所占的字节个数是不同的，好比utf8中一个字符占用3个字节，而gbk中一个字符占用2个字节。
replace函数
replace函数用于将字符串中的指定子字符串更换成另一个字符串，例如将员工表中所闻名字中的“S”更换成“上海”。如下：
https://i-blog.csdnimg.cn/direct/b41422c841dd4dacbeb0dac8ac0c40b9.png
strcmp函数
strcmp函数用于逐字符按照ASCII码比力两个字符串的巨细，两个字符串巨细相称返回0，前者大返回1，后者大返回-1。如下：
https://i-blog.csdnimg.cn/direct/bbd8699d1fd346459d24e797cb1cf367.png
需要注意的是，strcmp函数在比力时是不区分巨细写的。如下：
https://i-blog.csdnimg.cn/direct/db25886d472a46ca9b11301005a3263c.png
ltrim、rtrim和trim函数
数学函数
其他函数
复合查询（重点）
基本查询
对同一张表做笛卡尔积
https://i-blog.csdnimg.cn/direct/0162d98377544173a1bb5640df251a89.png
all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
先用基本的方法写
https://i-blog.csdnimg.cn/direct/266a569f88f94730819b15a8d1691a69.png
多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子 查询则是指查询返回多个列数据的子查询语句
查询和SMITH的部门和岗位完全雷同的所有雇员，不含SMITH本人
查询思路：先查询SMITH属于哪个部门的，然后再用多列子查询，查询出来和SMITH的部门和岗位完全雷同的所有雇员，末了再清除SMITH本人
mysql> select ename from EMP where (deptno, job)=
(select deptno, job from EMP where ename='SMITH') and ename <> 'SMITH'; https://i-blog.csdnimg.cn/direct/7ca99b613cc642c18a27fb447b8e1ebf.png 也可以用 in 把 和SMITH的部门和岗位完全雷同的所有雇员 看成一对
https://i-blog.csdnimg.cn/direct/34fbd1511f384b7787d0396679cd641e.png总结：目前全部的子查询，全部都在where子句中，充当判断条件。任何时刻，查询出来的临时布局，本质在逻辑上也是表布局。MySQL中一切皆表
在from子句中利用子查询
显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
思途经程：第一步:先查找所有部门的平均薪资，然后再分组查找每个部门的平均薪资，末了再把每个部门的平均薪资的结果充当个临时表，搭配from。注意要起别名
https://i-blog.csdnimg.cn/direct/8f344da61628416280d32276a267a674.png
第二步：有些数据是没意义的，就好比SMITH是20号部门的，你给她个10号部门的平均薪资，是没故意义的，所以我们要用笛卡尔积给去除掉
https://i-blog.csdnimg.cn/direct/79640c4be6f349318fd08f2b72106d8a.png 末了在进行筛选大于平均工资的
https://i-blog.csdnimg.cn/direct/78b41969ec03459d81eedd5ffc986490.png
附加条件：趁便显示在哪个地方工作
部门的工作所在是在dept中，所以是先用笛卡尔积，我们上面按照需要筛选出来的员工跟部门表进行归并，起个别名为t1
https://i-blog.csdnimg.cn/direct/f60a973f49bc4cdf907382d0d7845b9e.png
然后去掉无效信息
https://i-blog.csdnimg.cn/direct/c83d08da8b7046acb56fac115e34ffae.png
再按照所要求的信息。需要符合要求的员工名称，部门所在，部门
https://i-blog.csdnimg.cn/direct/42fa444ea4a340d9bafb0a5dc6fb1d20.png
查找每个部门工资最高的人的姓名、工资、部门、最高工资
select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
(select max(sal) ms, deptno from EMP group by deptno) tmp
where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms; 肯定是先聚合，先把每个部门工资的薪资先找到
https://i-blog.csdnimg.cn/direct/dd3a677080ee4bafb80e39d134aeda51.png
把这个结果作为临时表与emp表进行笛卡尔积组合
https://i-blog.csdnimg.cn/direct/061a81456d20438b82e144e61d16b667.png
然后进行筛选去掉无效信息
https://i-blog.csdnimg.cn/direct/d05223feba7d4d4b83162cbedd52582d.png
末了按照要求
https://i-blog.csdnimg.cn/direct/1edc71cb102743a28b3a862a64a4f18f.png
显示每个部门的信息（部门名，编号，地址）和职员数量
方法1：利用多表（不保举，因为为了要照顾group by语法布局，还需要对多个数据进行分组）
select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) '部门人数' from EMP, DEPT
where EMP.deptno=DEPT.deptno
group by DEPT.deptno,DEPT.dname,DEPT.loc; 过程：先统计每个部门有多少人，然后按照部门分组
https://i-blog.csdnimg.cn/direct/f83d61bbdb38453ebf65da810dda4491.png
然后进行笛卡尔积，把两个表放在一起，去除无效信息
https://i-blog.csdnimg.cn/direct/6b2099ec389d4393b870d43942321657.png
末了再按照要求
https://i-blog.csdnimg.cn/direct/320008ac9f27424f9888649a827ab3c3.png
方法2：利用子查询
先进行分组计算每个部门的人数，然后进行聚合
https://i-blog.csdnimg.cn/direct/72980cb42fbb4f7dbc8907df1e3fb110.png
末了再按照要求
https://i-blog.csdnimg.cn/direct/d7ddba149d244054a8843d8447b42239.png
解决多表题目标本质：想办法将多表转换成单表，所以mysql中，所有select的题目全部都可以转换成单表题目(多表查询的引导思想)
归并查询（用得不多）
在现实应用中，为了归并多个select的执行结果，可以利用聚集操作符 union，union all
https://i-blog.csdnimg.cn/direct/ba483a07aeba47208a4d0eae792d88ee.png
union
该操作符用于取得两个结果集的并集。当利用该操作符时，会自动去掉结果会集的重复行。
将工资大于2500或职位是MANAGER的人找出来
mysql> select ename, sal, job from EMP where sal>2500 union
-> select ename, sal, job from EMP where job='MANAGER';//去掉了重复记录 步调如下，union会帮你去重
https://i-blog.csdnimg.cn/direct/46f8f5fc20544bee810f18a96e50b52e.png
union all
该操作符用于取得两个结果集的并集。当利用该操作符时，不会去掉结果会集的重复行。
案例：将工资大于25000或职位是MANAGER的人找出来
mysql> select ename, sal, job from EMP where sal>2500 union all
-> select ename, sal, job from EMP where job='MANAGER'; https://i-blog.csdnimg.cn/direct/7642c23dc02145309666055d0965fd45.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]