MySQL基础学习总结（二）

数据处理函数

数据处理函数又被称为单行处理函数

单行处理函数的特点：一个输入对应一个输出。

和单行处理函数相对的是：多行处理函数。（多行处理函数特点：多个输入，对应1个输出！）

常见的单行处理行数

lower 转换小写 select lower(ename) as ename from emp;

+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+

14个输入，最后还是14个输出。这是单行处理函数的特点。

upper 转换大写 select upper(name) as name from t_student;

substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)） select substr(ename, 1, 1) as ename from emp; 注意：起始下标从1开始，没有0.

LEFT (string2 ,length )从 string2 中的左边起取 length 个字符

RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符

找出员工名字第一个字母是A的员工信息？ 第一种方式：模糊查询 select ename from emp where ename like 'A%'; 第二种方式：substr函数 select ename from emp where substr(ename,1,1) = 'A';

学生名字首字母大写？

select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;

注意：字符串拼接一定要用concat而不能用+

像这样不行：select upper(substr(name,1,1)) + substr(name,2,length(name) - 1) from t_student;

concat函数进行字符串的拼接 select concat(empno,ename) from emp;

可以拼接多个concat(……，……，……)

length 取长度 select length(ename) enamelength from emp;

trim 去两边空格

select * from emp where ename = trim(' KING');

RTrim切除右边的空格，LTrim切除左边的空格

str_to_date 将字符串转换成日期 date_format 格式化日期 format 设置千分位

（后面讲）

一个诡异的现象

select 'abc' as bieming from emp; // select后面直接跟“字面量/字面值”

        +---------+
        | bieming |
        +---------+
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        +---------+

select abc from emp; ERROR 1054 (42S22): Unknown column 'abc' in 'field list' 这样肯定报错，因为会把abc当做一个字段的名字，去emp表中找abc字段去了。

结论：select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）。 select 21000 as num from dept;// 21000也是被当做一个字面量/字面值。

    +-------+
    | num   |
    +-------+
    | 21000 |
    | 21000 |
    | 21000 |
    | 21000 |
    +-------+

round(要操作的数， 保留几位小数) 四舍五入

select round(21000.4, 0) as num from dept; //结果和上面一样

select round(1236.567, -1); // 保留到十位。

rand() 生成随机数 在 0 ≤ 随机数 ≤ 1.0 select floor(rand()*100); // 100以内的随机数

如果使用 rand(seed) 返回随机数, 范围 0 ≤ 随机数 ≤ 1.0, 如果 seed 不变，多次执行返回的结果也是不变的

ifnull是空处理函数。专门处理空的，可以将 null 转换成一个具体值

在所有的数据库当中，只要有null参与了数学运算，最终结果就是NULL

计算每个员工的年薪？

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

case..when..then..when..then..else..end 当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。 （注意：不修改数据库，只是将查询结果显示为工资上调） select ename, job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;

select 后可以不接from 当作简单的测试工具

等同于接了from dual;

dual为 亚元表, 系统表 可以作为测试表使用

select round(3.14, 3);

+----------------+
| round(3.14, 3) |
+----------------+
|          3.140 |
+----------------+

select concat('13','57','9');

+-----------------------+
| concat('13','57','9') |
+-----------------------+
| 13579                 |
+-----------------------+

多行处理函数

五个分组函数

多行处理函数也叫分组函数

多行处理函数的特点：输入多行，最终输出一行。

5个： count 计数 sum 求和 avg 平均值 max 最大值 min 最小值

注意： 分组函数在使用的时候必须先进行分组，然后才能用。 如果你没有对数据进行分组，整张表默认为一组。

找出最高工资？ mysql> select max(sal) from emp;

找出最低工资？ mysql> select min(sal) from emp;

计算工资和： mysql> select sum(sal) from emp;

计算平均工资： mysql> select avg(sal) from emp;

计算员工数量？ mysql> select count(ename) from emp;

计算员工数量？ mysql> select count(ename) from emp;

注意：如果count中填的是表达式的话，要加上 or null， 因为 Mysql 中 count() 函数的一般用法是统计字段非空的记录数，利用这个特点来进行条件统计，如果字段是 NULL 就不会统计，但是 false 是会被统计到的

分组函数在使用的时候需要注意哪些？

分组函数自动忽略NULL，你不需要提前对NULL进行处理。

mysql> select sum(comm) from emp;

    +-----------+
    | sum(comm) |
    +-----------+
    |   2200.00 |
    +-----------+

分组函数中count(*) 和 count(具体字段) 有什么区别？

count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。 count(*)：统计表当中的总行数。（只要有一行数据count则++） 每一行记录不可能都为NULL(不存在一行全为null的表)，一行数据中有一列不为NULL，则这行数据就是有效的。

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
​
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

分组函数不能直接使用在where子句中

找出比最低工资高的员工信息。 select ename,sal from emp where sal > min(sal); 表面上意思感觉是没问题，运行一下发现： ERROR 1111 (HY000): Invalid use of group function ????????????????????????????????????????????????????????????????????? 分组函数在使用的时候必须先进行分组，而分组的group by 执行顺序是在where后的，也就是让分组函数 min 先于分组了，所以会报错

所有的分组函数可以组合起来一起用

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

+----------+----------+----------+-------------+----------+
| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+

分组查询

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。 这个时候我们需要使用分组查询，怎么进行分组查询呢？ select ... from ... group by ... 计算每个部门的工资和？ 计算每个工作岗位的平均薪资？ 找出每个工作岗位的最高薪资？ ....

将之前的关键字全部组合在一起，他们的执行顺序？ select ... from ... where ... group by ... order by ...

以上关键字的顺序不能颠倒，需要记住。 执行顺序是什么？ 1.from 2.where 3.group by 4.select 5.order by

为什么分组函数不能直接使用在where后面？ select ename,sal from emp where sal > min(sal);//报错。 因为分组函数在使用的时候必须先分组之后才能使用。 where执行的时候，还没有分组。所以where后面不能出现分组函数。

select sum(sal) from emp; ​ 这个没有分组，为啥sum()函数可以用呢？ ​ 因为select在group by之后执行。

找出每个工作岗位的工资和？ 实现思路：按照工作岗位分组，然后对工资求和。 select job,sum(sal) from emp group by job;

+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| ANALYST   |  6000.00 |
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+
以上这个语句的执行顺序？
先从emp表中查询数据。
根据job字段进行分组。
然后对每一组的数据进行sum(sal)

重点结论： 在一条select语句当中，如果有group by语句的话， select后面只能跟：参加分组的字段，以及分组函数。 其它的一律不能跟。

找出“每个部门，不同工作岗位”的最高薪资？

两个字段联合分组 select deptno, job, max(sal) from emp group by deptno, job;

使用having对分组后的数据进一步过滤

having不能单独使用，having不能代替where，having必须和group by联合使用。

找出每个部门最高薪资，要求显示最高薪资大于3000的？

select deptno, max(sal) from emp group by deptno having max(sal) > 3000;

单表查询大总结

select ​ ... ​ from ​ ... ​ where ​ ... ​ group by ​ ... ​ having ​ ... ​ order by ​ ...

执行顺序？

1.from

2.where

3.group by

4.having

5.select

6.order by

找出除MANAGER岗位之外，每个岗位的平均薪资，要求显示平均薪资大于1500的， 要求按照平均薪资降序排。

select job, avg(sal) from emp where job != 'MANAGER' group by job having avg(sal) > 1500 order by avg(sal);

distinct 关键字

把查询结果去除重复记录【distinct】 注意：原表数据不会被修改，只是查询结果去重。 去重需要使用一个关键字：distinct

distinct只能出现在所有字段的最前方，表示对各字段联合起来的查询结果去重

这样编写是错误的，语法错误。

select ename,distinct job from emp;

统计一下工作岗位的数量？ select count(distinct job) from emp;

连接查询

什么是连接查询？

从一张表中单独查询，称为单表查询。 ​ 多张表联合起来查询数据，被称为连接查询。

根据语法的年代分类： SQL92：1992年的时候出现的语法 SQL99：1999年的时候出现的语法 我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)

根据表连接的方式分类： 内连接： 等值连接 非等值连接 自连接

外连接： ​ 左外连接（左连接） ​ 右外连接（右连接）

什么是笛卡尔积现象

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是 两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个数学现象。）

怎么避免笛卡尔积现象

连接时加条件，满足这个条件的记录被筛选出来！

表起别名。很重要。效率问题。 select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量减少表的连接次数。

内连接

两张表连接时没有主次关系的连接称为内连接

等值连接

案例：查询每个员工所在部门名称，显示员工名和部门名？ emp e和dept d表进行连接。条件是：e.deptno = d.deptno

SQL92语法： select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：inner 可以省略，不省略可读性会好一些 select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

自连接

内连接之自连接 案例：查询员工的上级领导，要求显示员工名和对应的领导名？

emp a 员工表、emp b 领导表

select a.ename 员工, b.ename 领导 from emp a join emp b on a.mgr = b.empno;

外连接

两张表连接时有主次关系

右(外)连接

// outer是可以省略的，带着可读性强。 select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将 这主表的数据全部查询出来，捎带着关联查询左边的表。(如果此表没有对应和主表满足 on 后条件的 ,那次表会自动补充 NULL ) 在外连接当中，两张表连接，产生了主次关系。

左(外)连接

左外连接同理

任何一个右连接都有左连接的写法。 任何一个左连接都有右连接的写法。

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名？

select a.ename '员工', b.ename '领导' from emp a left join emp b on a.mgr = b.empno;

三张表连接如下(四张表同理)

语法： select ... from a join b on a和b的连接条件 join c on a和c的连接条件

案例：找出每个员工的部门名称以及工资等级， 要求显示员工名、部门名、薪资、薪资等级？

select e.ename, d.dname, s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

一条SQL中内连接和外连接可以混合。都可以出现！

案例：找出每个员工的部门名称以及工资等级，还有上级领导， 要求显示员工名、领导名、部门名、薪资、薪资等级？

select e.ename '员工', u.ename '领导', d.dname, e.sal, s.grade from emp e left join emp u on e.mgr = u.empno join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

什么是子查询？

select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询都可以出现在哪里呢？

select ​ ..(select). ​ from ​ ..(select). ​ where ​ ..(select).

where子句中的子查询

案例：找出比最低工资高100的员工姓名和工资？

select ​ ename,sal ​ from ​ emp ​ where ​ sal > min(sal);

ERROR 1111 (HY000): Invalid use of group function ​ where子句中不能直接使用分组函数。

正确：select e.ename, e.sal from emp e where e.sal > (select min(sal)+1000 from emp);

from子句中的子查询

案例：找出每个岗位的平均工资的薪资等级。

select a.*, s.grade from (select job, avg(sal) avg_sal from emp group by job) a join salgrade s on a.avg_sal between s.losal and s.hisal;

子查询中的avg(sal)一定要起个别名才能在内连接中使用

select后面出现的子查询(了解即可)

案例：找出每个员工的部门名称，要求显示员工名，部门名？ select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

select e.ename,e.deptno,(select dname from dept) as dname from emp e; //错误：ERROR 1242 (21000): Subquery returns more than 1 row

注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果， 多于1条，就报错了。！