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条;就报错了。;