MySQL-入门（二）

最新推荐文章于 2021-08-09 18:38:10 发布

chengzixiao5991

最新推荐文章于 2021-08-09 18:38:10 发布

阅读量227

点赞数

原文链接：https://my.oschina.net/u/4151118/blog/3075430

版权

本部分主要是MySQL的常用函数和高级用法。

一、MySQL排序

排序关键字：order by 排序字段。后面写上要排序字段，排序字段可以有多个，多个采用逗号间隔，order by默认采用升序（asc）排序，可以手动设置为降序(desc)。如果存在where子句，那么order by必须放到where语句后面。

举例：1、单个字段排序：

手动指定按照id号由大到小排序（降序关键字desc）: select id from emp order by id desc;

2、多个字段排序：采用多个字段排序时，如果根据第一个字段排序重复了，会根据第二个字段排序；

按照 id 和age倒序排序：select id,name,age from emp order by id desc,age desc;

二、MySQL常用处理函数

常用函数	用例
lower()：转换为小写	查询员工姓名，将员工姓名全部转换成小写： select lower(ename) as ename from emp;
upper()转换为大写	查询员工姓名，将员工姓名全部转换为大写： select upper(ename) as ename from emp;
substr()截取子串：该函数接收3个参数：substr(被截取字段名称,起始下标,截取长度)，起始下标从1开始。	查询并显示所员工姓名的第二个字母： select substr(ename,2,1) from emp;
ifnull()空值处理该函数接收两个参数： ifnull(字段名,替换值) 在数据库中，有Null参与数学运算的结果一定为Null；为了防止计算结果出现Null，建议先使用ifnull函数预先处理。	查询员工姓名及补助，如果补助为Null设置为0； select ename,ifnull(comm,0) from emp;
case…when…then…else…end	select ename, sal, job, (case job when ‘MANAGER’ then sal * 1.1 when ‘SALESMAN’then sal * 1.5 else sal end) as newsal from emp;
trim()---去除首尾空格：MySQL默认去除字段后面的空格，原因：MySQL语法松散。	取得工作岗位为manager的所有员工： select * from emp where job = trim(' manager ');
round()----四舍五入：该函数接收两个参数round(数字，保留的小数位数)	查看员工薪水保留1位小数： select round(sal,1) from emp;
rand()生成随机数：	会生成一个0~1之间的的随机数，包含0和1。 select rand();
str_to_date()字符串转日期：该函数接收两个参数,str_to_date(‘日期字符串’,’日期格式’)。	使用str_to_date()函数查询02-20-1981年入职的员工: select ename,hiredate from emp where hiredate = str_to_date('02-20-1981','%m-%d-%Y');
date_format()格式化日期：该函数接收两个参数，date_format(日期类型数据,’日期格式’)	查询员工的入职日期，以’10-12-1980’的格式显示到窗口中； select ename,date_format(hiredate,'%m-%d-%Y') hiredate from emp ;

三、MySQL分组和聚合函数

1、聚合函数包括：sum、avg、max、min、count等，组合聚合函数:sum、avg、max、min、count这些函数可以一起使用；

需要注意：

聚合函数在计算时会自动忽略空值，不用手动写sql将空值排除。
聚合函数不能直接写在where语句的后面。

聚合函数	用例
sum()求和函数	取得薪水的合计: select sum(sal) from emp;
avg()取平均值函数	取得平均薪水： select avg(sal) as avgsal from emp;
max()取得最大值函数	取得最晚入职的日期：日期也可以使用max()函数进行比较 select max(hiredate) as lastemp from emp;
min()取得最小值函数	取得薪水最低值： select min(sal) as minsal from emp;
count()取得数据总数	取得补助不为空的员工数：count()函数不会统计数据为null的记录 select count(comm) from emp; 统计没有补助的员工数： select count(*) from emp where comm is null;
组合聚合函数:sum、avg、max、min、count这些函数可以一起使用：	select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

2、分组函数group by

group by后面可以写多个字段，数据库会分别对这些字段进行分组

举例：找出每个职位的最高薪水：按照工作岗位分组：select max(sal) as maxsal from emp group by job;

3、having过滤，作用：如果想对分组的数据进行过滤，需要使用having子句。

举例：找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的：

select job,avg(sal) from emp group by job having avg(sal) > 2000;

注意：能够在where后过滤的数据不要放到having中进行过滤，否则影响SQL询句的执行效率。

where和having区别如下：

where和having都是为了完成数据的过滤，它们后面都是添加条件；
where是在 group by之前完成过滤；
having是在group by之后完成过滤；

4、 select语句关键字顺序总结：

select xxxx from xxxx where xxxx group by xxxx having xxxx order by xxxx

from 将硬盘上的表文件加载到内存
where:将符合条件的数据筛选出来。生成一张新的临时表
group by :根据列中的数据种类分组，将当前临时表划分成若干个新的临时表
having : 可以过滤掉group by生成的不符合条件的临时表
select : 对当前临时表进行整列读取
order by : 对select生成的临时表，进行重新排序，生成新的临时表

四、MySQL连接查询

连接查询：在实际开发中，数据往往是同时存储在多张表中，这些表与表之间存在着关系，我们在检索数据的时候往往需要多张表联合起来检索，这种多表联合检索被称为连接查询。

连接的分类

1、内连接：内连接查询出的数据是两张表的交集，即上图中C所表示的部分。

select d.dname,e.ename from emp e inner join dept d on e.deptno = d.deptno;（inner可省略）

2、左外连接：左外链接如上图中A部分+C部分的内容，即包含左边表的全部行（不管右边的表中是否存在与它匹配的行），和右边表中全部匹配的行。

3、右外链接：右外链接如上图中B部分+C部分的内容，即包含右边表的全部行（不管左边的表中是否存在与它匹配的行），和左边表中全部匹配的行。

4、全外连接：MySQL中不支持

5、自连接：表自己跟自己做连接查询，这种写法叫做自连接。

举例说明：

内连接案例：

查询每个员工所在的部门名称，显示用工姓名和对应的部门名称：
- select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

自连接：查询员工的名称和员工对应的领导名称：

select a.ename empname ,b.ename leadername from emp a join emp b on a.mgr = b.empno;

左、右连接案例：

找出每一个员工对应的部门名称，要求部门名称全部显示（说明要把部门表的数据全显示出来，所有偏向部门表连接）：
- 左连接：其中outer可以省略：（因为部门表在emp表左边）
  - select e.ename,d.dname from dept d left outer join emp e on e.deptno = d.deptno;
- 使用右连接：其中outer可以省略：（因为部门表在emp表右边）
  - select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;
- 说明：为什么使用左右连接？因为如果部门表中出现某个部门，但是员工表中没有员工属于这个部门，那么如果用普通的查询则不会显示这个部门。但是这里要求部门全部显示，所以要偏向部门表连接查询，才能把员工为NULL的部门查出来

五、MySQL查询其他内容

1、子查询：select 语句嵌套 select 语句被称为子查询；

注意：select子句可出现在select、from、where关键字后面，可以将select语句查询出的数据看做是一张新的表。

举例：（重要）找出每个部门的平均薪水，并且要求显示平均薪水的薪水等级：注意，这里的t是括号中select子句构成的新表的别名