MySQL：内置函数+复合查询+表的内连和外连

内置函数一般要用select调用

一、内置函数

1.1 日期函数

1、获得年月日：current_date/data(now)

 

 

2、获得时分秒：current_time

 

3、获得时间戳：（他会给我们转化成日期+时间的格式显示）current_timestamp

4、返回datatime参数的日期部分 data

5、获取当前时间 now

6、在日期的基础上加日期：date_add

7、在日期的基础上减去时间：date_sub

8、计算两个日期之间相差多少天：datediff

 

案例：留言表

创建留言表 

 

插入数据

 显示所有留言信息，发布日期只显示日期，不用显示时间

 

查询在2分钟内发布的帖子  

select * from msg where date_add(sendtime, interval 2 minute) > now();

1.2 字符串函数 

1、获取emp表的ename列的字符串 charset（一般是在乱码的时候确认一下mysql本身的编码格式以及插入到mysql表里面的特定字段为什么会乱）

 

2、要求显示student表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分” concat

3、返回在字符串中的出现位置 instr （因为不存在是返回0，所以他的下标是从1开始的）

4、转化成大写ucase

5、转化成小写 lcase

 

6、 从左取length个 left

7、 从右取lenth个字符 right

 

8、求学生表中学生姓名占用的字节数 length

注意：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字 节字符则算作一个字节。比如：字母，数组算作一个字节，中文表示多个字节数（与字符集编码有关）

9、 将EMP表中所有名字中有S的替换成'上海'replace

10、截取EMP表中ename字段的第二个到第三个字符substring（下标从1开始，不加length的话默认就是全部）

11、以首字母小写的方式显示所有员工的姓名

12、去掉前空格（ltrim）后空格（rtrim）前后空格（trim）   （中间空格不会去掉        ）

1.3 数学函数 

1、绝对值abs

 

2、转二进制bin 

3、转16进制hex

 

4、进制转化conv 

 

5、向上取整ceiling

 4种取整方案：四舍五入（数学计算）、向上取整（银行）、向下取整（工龄）、零向取整（语言很常见）

6、向下取证floor

 

7、格式化，保留小数位数format

8、返回随机浮点数，范围【0.0,1.0） rand

想要0-99就*100

用format控制随机数精度

9、取模，求余mod

关于负数取模怎么处理，可以去看看C语言深度解剖

1.4 其他函数

1、user() 查询当前用户

 

2、database()显示当前正在使用的数据库

3、md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串（用户密码不能在数据库表里面明文保存！！万一表结构被盗取了就泄密了！）

未来用户输入密码的时候也会转成md5看看是否匹配

4、password()函数，MySQL数据库使用该函数对用户加密

 

5、ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值

二、复合查询（重点）

2.1 基本查询

1、查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%';

2、按照部门号升序而雇员的工资降序排序 

select * from EMP order by deptno asc, sal desc;

3、使用年薪进行降序排序  

select ename,sal,comm,sal*12+ifnull(comm,0) '年薪' from EMP order by 年薪 desc;

4、显示工资最高的员工的名字和工作岗位 

    直接聚合的话名字和工作岗位无法出现，所以要用子查询把最高工资的员工聚合统计出来，然后再去查他的名字和岗位

select ename, job from EMP where sal = (select max(sal) from EMP);

5、显示工资高于平均工资的员工信息+

select ename, sal from EMP where sal>(select avg(sal) from EMP);

6、显示每个部门的平均工资和最高工资 

select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;

7、显示平均工资低于2000的部门号和它的平均工资 

select deptno, avg(sal) avg_sal from EMP group by deptno having avg_sal<2000;

8、显示每种岗位的雇员总数，平均工资 

select job,count(*), format(avg(sal),2) from EMP group by job;

2.2 多表笛卡尔积

       实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。

1、显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询 

笛卡尔积：将数据进行穷举组合

其实我们只要emp表中的deptno = dept表中的deptno字段的记录  

select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;

列名如果唯一的话就不需要前导 

2、显示部门号为10的部门名，员工名和工资 

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;

3、显示各个员工的姓名，工资，及工资级别

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

2.3 自连接

自连接是指在同一张表连接查询（需要对表做重命名）

场景：你的领导也在员工表里，有自己的工号 

案例：显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

子查询：

select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');

多表查询： 

-- 使用到表的别名
--from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别 

select leader.empno,leader.ename from emp leader, emp worker 
where leader.empno = worker.mgr and worker.ename='FORD';

2.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

2.4.1 单行子查询

返回一行记录的子查询

案例：显示SMITH同一部门的员工  

select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');

2.4.2 多行子查询

返回多行记录的子查询

1、in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

select ename,job,sal,deptno from emp where job in 
(select distinct job from emp where deptno=10) and deptno<>10;

 附加要求：并且知道对应的员工属于哪一个部门的名字

select ename,job,sal,dname from
（select ename,job,sal,deptno from emp where job in 
(select distinct job from emp where deptno=10) and deptno<>10;）
and deptno <>10) as tmp ,dept where dept.deptno=tmp.deptno;

       说明了子查询不仅可以出现在where后面，也可以出现在from后面 （把子查询当成一个临时表使用）

2、all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename, sal, deptno from EMP where sal > all
(select sal from EMP where deptno=30);

3、any关键字；显示工资比部门30的任意一名员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

select ename, sal, deptno from EMP where sal > any
(select sal from EMP where deptno=30);

2.4.3 多列子查询

       单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子 查询则是指查询返回多个列数据的子查询语句

案例：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人  

mysql> select ename from EMP where (deptno, job)=
(select deptno, job from EMP where ename='SMITH') and ename <> 'SMITH';

也可以用in 把他看成一对 

2.4.4 在from子句中使用子查询

      子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。（任何时刻，查询出来的临时结构，本质上在逻辑上也是表结构   mysql一切皆表）

1、显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

//获取各个部门的平均工资，将其看作临时表
select ename, deptno, sal, format(asal,2) from EMP, 
 (select avg(sal) asal, deptno dt from EMP group by deptno) tmp
where EMP.sal > tmp.asal and EMP.deptno=tmp.dt;

附加条件：顺便显示在哪个地方工作

2、查找每个部门工资最高的人的姓名、工资、部门、最高工资  

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;

3、 显示每个部门的信息（部门名，编号，地址）和人员数量

 方法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;

 方法2：使用子查询

//1. 对EMP表进行人员统计
select count(*), deptno from EMP group by deptno;
//2. 将上面的表看作临时表
select DEPT.deptno, dname, mycnt, loc from DEPT, 
(select count(*) mycnt, deptno from EMP group by deptno) tmp
where DEPT.deptno=tmp.deptno;

2.4.5 合并查询（用得不多）

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all

2.4.5.1 union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

案例：将工资大于2500或职位是MANAGER的人找出来  

mysql> select ename, sal, job from EMP where sal>2500 union 
 -> select ename, sal, job from EMP where job='MANAGER';//去掉了重复记录

2.4.5.2 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';

注意：两个select的列信息和属性必须一样！！！ 

三、表的内连和外连（重点）

表的连接分为内连和外连 

3.1 内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

 语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

案例：显示SMITH的名字和部门名称  

//用前面的写法
select ename,dname from EMP,DEPT where EMP.deptno=DEPT.deptno and ename='SMITH';

以下这种写法逻辑更清晰，前一部分是做笛卡尔积的，后面一部分是进一步做条件筛选

//用标准的内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and 
ename='SMITH';

and和where都可以 

3.2 外连接

外连接分为左外连接和右外连接

3.2.1 左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。  （以左侧表为主进行拼接）

语法：

select 字段名  from 表名1 left join 表名2 on 连接条件

案例：查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来  

//建两张表
create table stu (id int, name varchar(30));//学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int);//成绩表
insert into exam values(1, 56),(2,76),(11, 8);

//当左边表和右边表没有匹配时，也会显示左边表的数据
select * from stu left join exam on stu.id=exam.id; 

3.2.2 右外连接

如果联合查询，右侧的表完全显示我们就说是右外连接。  

语法：

select 字段 from 表名1 right join 表名2  on 连接条件；

案例1： 对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来

select * from stu right join exam on stu.id=exam.id;

 

案例2：列出部门名称和这些部门的员工信息，同时列出没有员工的部门

方法一：
select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;
方法二：
select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;