MySQL_02SQL单表查询

MySQL_02SQL单表查询

1.导入数据及查看表结构

1.1导入演示数据

通过如下命令将已经写好的演示数据导入数据库中：

mysql> source + sql文件绝对路径

1.2查看导入的表

1.3查看导入的三个表中的数据

使用如下命令即可查看表中所有数据

mysql> select * from 表名;

1.4查看三个表的结构

使用如下命令即可查看一个表的结构。

mysql> desc 表名;

2.简单查询

2.1查询一个字段

mysql> select 字段名 from 表名;

注意：

  • select和from都是关键字。
  • 字段名和表名都是标识符。

强调：

  • SQL语句是通用的，在MySQL中能用，在Oracle中也能用。
  • 所有的SQL语句都必须以“;”结尾。
  • SQL语句不区分大小写。

查询部门表中的部门名字：

mysql> select dname from dept;

2.2查询多个字段

使用逗号隔开字段名：

mysql> select ename,job,sal from emp;

2.3查询所有字段

第一种方式：把表中所有字段都写上：

mysql> select deptno,dname,loc from dept;

第二种方式：使用 *

mysql> select * from dept;

这种方式的缺点：

  • 效率低
  • 可读性差

在实际开发中，不建议这样写。只是自己使用的话没问题。

可以快速查看全表数据。

2.4给查询结果的列名起别名

有时我们需要给查询结果的数据的字段名起一个别名，使用格式：

mysql> select dname as oldname,loc as oldloc from dept;

使用as关键字起别名。

注意：

  1. 只是将显示的查询结果列名显示为别名，表中的列名还是原来的列名。

  2. select语句永远都不会对表进行修改操作。

  3. as关键字可以省略

  4. 假设起别名时，别名中有空格，别名必须用单引号括起来，不然会报错。

    • mysql> select dname as 'old name' from dept;

数据库中的字符串都是采用单引号括起来。这是标准的。

双引号在数据库中是不标准的。

2.5字段使用数学表达式

计算所有员工的年薪。

可以在薪水字段后面直接乘12：

mysql> select ename,sal*12 as '年薪' from emp;

+--------+----------+
| ename  | 年薪        |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.01 sec)

3.条件查询

3.1什么是条件查询？

条件查询不是将表中所有的数据都查出来，而是查出符合指定条件的数据。

条件查询需要用到where语句，where必须放到from语句表的后面。格式如下：

select 字段 from 表名 where 条件;

支持如下运算符：

运算符 说明
= 等于
!=或<> 不等于
<和<= 小于和小于等于
>和>= 大于和大于等于
between...and... 在两个值之间，等同于>= and <=。(前闭后闭)
is null 为空(is not null 表示不为空)
and 并且
or 或
in 包含，in(1,10) 表示1和10，并非1到10(相当于多个or)
not not 可以取非，主要用在is或in中
like 模糊查询，支持%或下划线匹配。%：匹配任意个字符；下划线：一个下划线匹配一个字符。

3.2通过条件查询emp表

emp表：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

3.2.1”=“查询

查询工作为CLERK的员工信息：

select empno,ename,job,sal from emp where job = 'CLERK';

查询结果：

+-------+--------+-------+---------+
| empno | ename  | job   | sal     |
+-------+--------+-------+---------+
|  7369 | SMITH  | CLERK |  800.00 |
|  7876 | ADAMS  | CLERK | 1100.00 |
|  7900 | JAMES  | CLERK |  950.00 |
|  7934 | MILLER | CLERK | 1300.00 |
+-------+--------+-------+---------+

3.2.2“>”查询

查询薪水高于2000的员工信息：

select empno,ename,job,sal from emp where sal > 2000;

查询结果：

+-------+-------+-----------+---------+
| empno | ename | job       | sal     |
+-------+-------+-----------+---------+
|  7566 | JONES | MANAGER   | 2975.00 |
|  7698 | BLAKE | MANAGER   | 2850.00 |
|  7782 | CLARK | MANAGER   | 2450.00 |
|  7788 | SCOTT | ANALYST   | 3000.00 |
|  7839 | KING  | PRESIDENT | 5000.00 |
|  7902 | FORD  | ANALYST   | 3000.00 |
+-------+-------+-----------+---------+

3.2.3”between...and...“查询

查询薪水在1300到3000之间的员工信息：

第一种方式：通过>= and <= 来实现

select empno,ename,sal from emp where sal >= 1300 and sal <= 3000;

查询结果：

+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7566 | JONES  | 2975.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
8 rows in set (0.00 sec)

第二种方式：通过between...and...来实现

注意要保证较小的数据在前,不然会查询失败。

select empno,ename,sal from emp where sal between 1300 and 3000;

查询结果：

+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7566 | JONES  | 2975.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+

3.2.4“is null”查询

查询comm为null的员工信息：

在数据库中null不能使用等号进行衡量，需要使用is null。

因为在数据库中，null代表什么都没有，不是一个值，不能比较。

1)如果使用等号来衡量：

select empno,ename,comm from emp where comm = null;

结果：

Empty set (0.00 sec)

2)使用is null来查询：

select empno,ename,comm from emp where comm is null;

查询结果：

+-------+--------+------+
| empno | ename  | comm |
+-------+--------+------+
|  7369 | SMITH  | NULL |
|  7566 | JONES  | NULL |
|  7698 | BLAKE  | NULL |
|  7782 | CLARK  | NULL |
|  7788 | SCOTT  | NULL |
|  7839 | KING   | NULL |
|  7876 | ADAMS  | NULL |
|  7900 | JAMES  | NULL |
|  7902 | FORD   | NULL |
|  7934 | MILLER | NULL |
+-------+--------+------+
10 rows in set (0.00 sec)

3)如果要查询不为null的，则用is not null：

select empno,ename,comm from emp where comm is not null;

结果：

+-------+--------+---------+
| empno | ename  | comm    |
+-------+--------+---------+
|  7499 | ALLEN  |  300.00 |
|  7521 | WARD   |  500.00 |
|  7654 | MARTIN | 1400.00 |
|  7844 | TURNER |    0.00 |
+-------+--------+---------+

3.2.5”and”和“or“查询

1)查询工作为SALESMAN，并且薪水大于1300的员工：使用and实现。

select empno,ename,job,sal from emp where job = 'salesman' and sal > 1300;

查询结果：

+-------+--------+----------+---------+
| empno | ename  | job      | sal     |
+-------+--------+----------+---------+
|  7499 | ALLEN  | SALESMAN | 1600.00 |
|  7844 | TURNER | SALESMAN | 1500.00 |
+-------+--------+----------+---------+

2)查询工作为CLARK或者薪水大于3000的员工：通过or来实现。

select empno,ename,job,sal from emp where job = 'clerk' or sal > 3000;

查询结果：

+-------+--------+-----------+---------+
| empno | ename  | job       | sal     |
+-------+--------+-----------+---------+
|  7369 | SMITH  | CLERK     |  800.00 |
|  7839 | KING   | PRESIDENT | 5000.00 |
|  7876 | ADAMS  | CLERK     | 1100.00 |
|  7900 | JAMES  | CLERK     |  950.00 |
|  7934 | MILLER | CLERK     | 1300.00 |
+-------+--------+-----------+---------+

3)查询工资大于2500，并且部门编号为10或20的员工：通过and和or联合实现。

and和or一起使用的话，有优先级问题，会优先执行and。

比如下列语句会先执行and，然后再执行or。

代表的含义是查询工资大于2500并且部门编号为10的员工，或者部门编号为20的所有员工。

select empno,ename,sal,deptno from emp where sal > 2500 and deptno = 10 or deptno = 20;

结果：

+-------+-------+---------+--------+
| empno | ename | sal     | deptno |
+-------+-------+---------+--------+
|  7369 | SMITH |  800.00 |     20 |
|  7566 | JONES | 2975.00 |     20 |
|  7788 | SCOTT | 3000.00 |     20 |
|  7839 | KING  | 5000.00 |     10 |
|  7876 | ADAMS | 1100.00 |     20 |
|  7902 | FORD  | 3000.00 |     20 |
+-------+-------+---------+--------+

显然不符合我们的需求。正确写法如下：

select empno,ename,sal,deptno from emp where sal > 2500 and (deptno = 10 or deptno = 20);

结果：

+-------+-------+---------+--------+
| empno | ename | sal     | deptno |
+-------+-------+---------+--------+
|  7566 | JONES | 2975.00 |     20 |
|  7788 | SCOTT | 3000.00 |     20 |
|  7839 | KING  | 5000.00 |     10 |
|  7902 | FORD  | 3000.00 |     20 |
+-------+-------+---------+--------+

3.2.6”in“查询

in表示包含，相当于多个or。

查询部门编号为10或者30的员工：

使用or来实现：

select empno,ename,deptno from emp where deptno = 10 or deptno = 30;

结果：

+-------+--------+--------+
| empno | ename  | deptno |
+-------+--------+--------+
|  7499 | ALLEN  |     30 |
|  7521 | WARD   |     30 |
|  7654 | MARTIN |     30 |
|  7698 | BLAKE  |     30 |
|  7782 | CLARK  |     10 |
|  7839 | KING   |     10 |
|  7844 | TURNER |     30 |
|  7900 | JAMES  |     30 |
|  7934 | MILLER |     10 |
+-------+--------+--------+

使用in来实现：

select empno,ename,deptno from emp where deptno in(10,30);

结果：

+-------+--------+--------+
| empno | ename  | deptno |
+-------+--------+--------+
|  7499 | ALLEN  |     30 |
|  7521 | WARD   |     30 |
|  7654 | MARTIN |     30 |
|  7698 | BLAKE  |     30 |
|  7782 | CLARK  |     10 |
|  7839 | KING   |     10 |
|  7844 | TURNER |     30 |
|  7900 | JAMES  |     30 |
|  7934 | MILLER |     10 |
+-------+--------+--------+

3.2.7”like“模糊查询

支持%或下划线匹配。

  • %匹配多个字符。
  • 下划线匹配任意一个字符。

1)查询所有名字中带有“A”的员工，可以用ename like '%A%'

select ename from emp where ename like '%A%';

结果：

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| CLARK  |
| ADAMS  |
| JAMES  |
+--------+

2)查询所有名字以“A”开头的员工，可以用ename like 'A%'

select ename from emp where ename like 'A%';

结果:

+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+

3)查询所有名字以“R”结尾的员工，可以用ename like '%R'

select ename from emp where ename like '%R';

结果：

+--------+
| ename  |
+--------+
| TURNER |
| MILLER |
+--------+

4)查询所有员工名字中第二个为"A"的，可以用ename like '_A%'

select ename from emp where ename like '_A%';

结果：

+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+

5)查询所有员工名字中第三个为"A"的，可以用ename like '__A%'

select ename from emp where ename like '__A%';

结果：

+-------+
| ename |
+-------+
| BLAKE |
| CLARK |
| ADAMS |
+-------+

6)如果想找出所有员工中，名字带有”_“的

#select ename from emp where ename like '%_%';无法查询到
select ename from emp where ename like '%\_%';#转义字符\

4.排序

通过“order by”语句可以实现排序，语法是：

select 字段名 from 表名 order by 需要排序的字段名;

默认升序。

4.1升序

查询所有员工的工资，按升序排列。

#select ename,sal from emp order by sal;//默认升序
select ename,sal from emp order by sal asc;#也可以指定升序

结果：

+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

4.2降序

查询所有员工的工资，按降序排列。

select ename,sal from emp order by sal desc;

结果：

+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

4.3按照多个字段排序

查询员工工号、名字和薪水，要求按薪水升序排列，如果薪水一样，再按照员工号降序排列。

select empno,ename,sal from emp order by sal asc,empno desc;

sal在前，起主导作用。只有当sal相等时，才会按empno排列。

结果：

+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7900 | JAMES  |  950.00 |
|  7876 | ADAMS  | 1100.00 |
|  7654 | MARTIN | 1250.00 |
|  7521 | WARD   | 1250.00 |
|  7934 | MILLER | 1300.00 |
|  7844 | TURNER | 1500.00 |
|  7499 | ALLEN  | 1600.00 |
|  7782 | CLARK  | 2450.00 |
|  7698 | BLAKE  | 2850.00 |
|  7566 | JONES  | 2975.00 |
|  7902 | FORD   | 3000.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
+-------+--------+---------+

4.4根据查询结果字段的位置也可以排序

select empno,ename,sal from emp order by 3;#3表示查询结果中第三列的字段sal

按照查询结果的第3列sal进行排序。

不建议在开发中这样写，因为不健壮。

列的顺序很容易发生改变，列顺序改变后，3代表的字段就不一样了。

结果：

+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7900 | JAMES  |  950.00 |
|  7876 | ADAMS  | 1100.00 |
|  7521 | WARD   | 1250.00 |
|  7654 | MARTIN | 1250.00 |
|  7934 | MILLER | 1300.00 |
|  7844 | TURNER | 1500.00 |
|  7499 | ALLEN  | 1600.00 |
|  7782 | CLARK  | 2450.00 |
|  7698 | BLAKE  | 2850.00 |
|  7566 | JONES  | 2975.00 |
|  7902 | FORD   | 3000.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
+-------+--------+---------+

5.综合练习案例

找出工资在1250到3000之间的员工信息，要求按照薪资降序排列.

select empno,ename,sal 
from emp 
where sal between 1250 and 3000 
order by sal desc;

结果：

+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7902 | FORD   | 3000.00 |
|  7788 | SCOTT  | 3000.00 |
|  7566 | JONES  | 2975.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7499 | ALLEN  | 1600.00 |
|  7844 | TURNER | 1500.00 |
|  7934 | MILLER | 1300.00 |
|  7654 | MARTIN | 1250.00 |
|  7521 | WARD   | 1250.00 |
+-------+--------+---------+

关键字顺序不可改变：

select...
from...
where...
order by...

以上语句的执行顺序：

  1. from：找表
  2. where：条件过滤
  3. select：查出来
  4. order by：排序输出(排序总是在最后执行)

通过from找表，然后通过where过滤，过滤后select查出来，最后order by排序输出。

6.数据处理函数(单行处理函数)

6.1什么是单行处理函数

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

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

与单行处理函数相对应的是：多行处理函数。

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

6.2常见的单行处理函数

函数名 描述
lower 转换为小写
upper 转换为大写
substr 截取字符串。(substr(待截取字符串,起始下标,截取的长度))
length 取长度
trim 去空格
str_to_date 将字符串转换为日期
date_format 设置千分位
round 四舍五入
rand() 生成随机数
ifnull 可以将null转换为一个具体值

6.3字符大小写转换

lower函数可以将字符转换为小写

upper函数可以将字符转换为大写

查询所有员工的名字，要求以小写的形式输出

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与lower用法相同，依葫芦画瓢即可。

6.4截取字符串

substr可以实现字符串的截取，格式如下：

substr(待截取字符串,起始下标,截取的长度)

起始下标从1开始，没有0。如：

select substr(ename,1,1) as ename from emp;

结果：

+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+

查询首字母为“A”的名字

第一种方式：模糊查询

select ename from emp where ename like 'A%';

第二种方式：substr函数

select ename from emp where substr(ename,1,1) = 'A';

结果：

+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+

6.5进行一个字符串的拼接

concat函数可以实现两个字符串的拼接。

将员工号与名字拼接在一起：

select concat(empno,ename) as result from emp;

结果：

+------------+
| result     |
+------------+
| 7369SMITH  |
| 7499ALLEN  |
| 7521WARD   |
| 7566JONES  |
| 7654MARTIN |
| 7698BLAKE  |
| 7782CLARK  |
| 7788SCOTT  |
| 7839KING   |
| 7844TURNER |
| 7876ADAMS  |
| 7900JAMES  |
| 7902FORD   |
| 7934MILLER |
+------------+

6.6获取字符串长度

length可以获取字符串的长度。

获取所有员工名字的长度：

select length(ename) as length from emp;

结果：

+--------+
| length |
+--------+
|      5 |
|      5 |
|      4 |
|      5 |
|      6 |
|      5 |
|      5 |
|      5 |
|      4 |
|      6 |
|      5 |
|      5 |
|      4 |
|      6 |
+--------+

6.7综合练习：将所有员工的名字首字母转换为小写

1.首先，我们需要获取所有员工名字的首字母，并转换为小写：

select lower(substr(ename,1,1)) from emp;

+--------------------------+
| lower(substr(ename,1,1)) |
+--------------------------+
| s                        |
| a                        |
| w                        |
| j                        |
| m                        |
| b                        |
| c                        |
| s                        |
| k                        |
| t                        |
| a                        |
| j                        |
| f                        |
| m                        |
+--------------------------+

2.然后，我们可以获取后面剩余的字符串

select substr(ename,2,length(ename) - 1) from emp;

+-----------------------------------+
| substr(ename,2,length(ename) - 1) |
+-----------------------------------+
| MITH                              |
| LLEN                              |
| ARD                               |
| ONES                              |
| ARTIN                             |
| LAKE                              |
| LARK                              |
| COTT                              |
| ING                               |
| URNER                             |
| DAMS                              |
| AMES                              |
| ORD                               |
| ILLER                             |
+-----------------------------------+

3.最后，我们就可以将两者拼接到一起：

select concat(lower(substr(ename,1,1)),substr(ename,2,length(ename) - 1)) as newname from emp;

结果：

+---------+
| newname |
+---------+
| sMITH   |
| aLLEN   |
| wARD    |
| jONES   |
| mARTIN  |
| bLAKE   |
| cLARK   |
| sCOTT   |
| kING    |
| tURNER  |
| aDAMS   |
| jAMES   |
| fORD    |
| mILLER  |
+---------+

6.8去空格

假设有如下语句：

select ename from emp where job = '    manager';

会查询不到数据，原因是字符串中有多于空格。

我们可以使用trim来去除空格的影响：

select ename from emp where job = trim('    manager');

+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
+-------+

6.9四舍五入

首先，我们要了解一个特性，就是如果select后面直接跟字面值或者字面量，就会出现如下情况：

mysql> select 'aaa' from emp;
+-----+
| aaa |
+-----+
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
| aaa |
+-----+
14 rows in set (0.01 sec)

mysql> select 999 from emp;
+-----+
| 999 |
+-----+
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
| 999 |
+-----+
14 rows in set (0.00 sec)

然后我们就可以测试round函数了。

round函数可以对数据进行四舍五入。

语法格式：round(待四舍五入的数,0)

  • 其中“0”表示保留到整数位；
  • 如果是“1”则表示保留1位小数，“2”表示保留2位小数；
  • 如果是“-1”则表示保留到十分位，“-2”表示保留到百分位。
mysql> select round(123.456,0) as result from emp;
+--------+
| result |
+--------+
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
+--------+
14 rows in set (0.00 sec)

6.10生成随机数

rand()可以生成随机数：

mysql> select rand() as result from emp;
+---------------------+
| result              |
+---------------------+
| 0.22870165782859703 |
|  0.4892243896510679 |
|  0.7600170055031935 |
| 0.33241188929640864 |
|  0.3820084607061077 |
|  0.9128066663749578 |
|  0.4180079804901108 |
| 0.35161993405932557 |
|  0.5040757595599403 |
| 0.46551902635536996 |
|  0.8153678838306739 |
|  0.6802823708209045 |
|  0.9553082333461458 |
|  0.7356940850016606 |
+---------------------+

生成100以内的随机数：

mysql> select round(rand()*100,0) as result from emp;
+--------+
| result |
+--------+
|     33 |
|     39 |
|     99 |
|     78 |
|     93 |
|     31 |
|     74 |
|     78 |
|     68 |
|      4 |
|     18 |
|     78 |
|     36 |
|     44 |
+--------+

6.11null处理函数

在emp表中，有薪水(sal)和补助(comm)这两个字段，其中comm中存在null。

mysql> select ename,sal,comm from emp;
+--------+---------+---------+
| ename  | sal     | comm    |
+--------+---------+---------+
| SMITH  |  800.00 |    NULL |
| ALLEN  | 1600.00 |  300.00 |
| WARD   | 1250.00 |  500.00 |
| JONES  | 2975.00 |    NULL |
| MARTIN | 1250.00 | 1400.00 |
| BLAKE  | 2850.00 |    NULL |
| CLARK  | 2450.00 |    NULL |
| SCOTT  | 3000.00 |    NULL |
| KING   | 5000.00 |    NULL |
| TURNER | 1500.00 |    0.00 |
| ADAMS  | 1100.00 |    NULL |
| JAMES  |  950.00 |    NULL |
| FORD   | 3000.00 |    NULL |
| MILLER | 1300.00 |    NULL |
+--------+---------+---------+
14 rows in set (0.00 sec)

现在我们现在需要计算所有员工的年薪，那么结果为：

mysql> select ename,(sal + comm) * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

为什么会出现这种情况呢？

在所有数据库中，只要有null参与的数学运算，结果都为null。

为了避免这种情况，我们可以使用ifnull函数。

ifnull函数用法：ifnull(数据,被当作的值)

如果“数据”为null的时候，就将其当作后面的值。

#ifnull(comm,0) 如果comm为null，那么将其当作0
mysql> select ename,(sal + ifnull(comm,0)) * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.01 sec)

6.12 case...when...then...when...then...else...end

当员工的部门编号为10时，工资上调10%；当员工的部门编号为20时，工资下调20%；其他部门不变。

select ename,deptno,sal as oldsal,
( case deptno when 10 then sal * 1.1 when 20 then sal * 0.8 else sal end) as newsal 
from emp;

结果：


+--------+--------+---------+---------+
| ename  | deptno | oldsal  | newsal  |
+--------+--------+---------+---------+
| SMITH  |     20 |  800.00 |  640.00 |
| ALLEN  |     30 | 1600.00 | 1600.00 |
| WARD   |     30 | 1250.00 | 1250.00 |
| JONES  |     20 | 2975.00 | 2380.00 |
| MARTIN |     30 | 1250.00 | 1250.00 |
| BLAKE  |     30 | 2850.00 | 2850.00 |
| CLARK  |     10 | 2450.00 | 2695.00 |
| SCOTT  |     20 | 3000.00 | 2400.00 |
| KING   |     10 | 5000.00 | 5500.00 |
| TURNER |     30 | 1500.00 | 1500.00 |
| ADAMS  |     20 | 1100.00 |  880.00 |
| JAMES  |     30 |  950.00 |  950.00 |
| FORD   |     20 | 3000.00 | 2400.00 |
| MILLER |     10 | 1300.00 | 1430.00 |
+--------+--------+---------+---------+
14 rows in set (0.00 sec)

7.分组函数(多行处理函数)

7.1分组函数简述

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

共有5个分组函数：

函数名 描述
max 最大值
min 最小值
avg 平均值
sum 求和
count 计数

注意：

  • 分组函数在使用的时候不需先进行分组，然后才能用。
  • 如果没有对数据进行分组，整张表默认为一组。

7.2分组函数的使用

计算所有员工中工资最多的：

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

计算所有员工中工资最少的：

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

计算平均工资：

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.01 sec)

计算工资和：

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

计算员工数量：

mysql> select count(ename) as count from emp;
+-------+
| count |
+-------+
|    14 |
+-------+
1 row in set (0.00 sec)

7.3分组函数会自动处理null

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+
1 row in set (0.00 sec)

7.4count(*)和count(具体字段)的区别

count(*)：

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.01 sec)

count(具体字段)：

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

count(*)：统计表中总行数。

  • 只要有一行数据count则++
  • 一个表中，每一行数据不可能全部都为null。只要一行数据中有一列不为null，这行数据就是有效的。

count(具体字段)：表示统计该字段下所有不为null的元素。

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

找出比最低工资高的员工信息：

mysql> select * from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function

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

8.分组查询(重点)

8.1什么是分组查询？

在实际的应用中，可能有这样的需求：需要先分组，然后对每一组中的数据进行操作。

如：

  1. 计算每个部门的工资和。
  2. 计算每个部门的平均工资。
  3. 计算每个部门的最高工资。

此时，我们需要分组查询，格式如下：

select 字段 from 表名 group by 分组字段;

8.2所有关键字的执行顺序

select...
from...
where...
group by...
order by...

执行顺序：

  1. from
  2. where
  3. group by
  4. select
  5. order by

为什么分组函数无法直接在where后面使用呢？

mysql> select * from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function

因为分组函数在使用的时候必须先进行分组，而where执行时，还没有进行分组，故where后面不能跟分组函数。

那么为什么分组函数可以直接在select后面使用呢？

select sum(sal) from emp;

如果一个语句中没有手动group by分组的话，会默认整张表为一组，而select语句在group by之后执行，故可以在select后面直接跟分组函数。

8.3找出每个工作岗位的工资和

mysql> 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 |
+-----------+----------+
5 rows in set (0.00 sec)

以上语句的执行顺序：

  1. 先从emp表中查询出数据
  2. 再根据job将查询出的数据进行分组
  3. 最后对每一组的数据进行求和sum(sal)

假如我们在上面的语句中加入ename字段：

mysql> select ename,job,sum(sal) from emp group by job;
+-------+-----------+----------+
| ename | job       | sum(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST   |  6000.00 |
| SMITH | CLERK     |  4150.00 |
| JONES | MANAGER   |  8275.00 |
| KING  | PRESIDENT |  5000.00 |
| ALLEN | SALESMAN  |  5600.00 |
+-------+-----------+----------+
5 rows in set (0.01 sec)

以上语句在mysql中可以执行，但是毫无意义。

以上语句在oracle中执行报错。

  • oracle的语法比mysql严格

重点结论：

在一条select语句中，如果使用了group by，那么select后面只能跟参与分组的字段以及分组函数，其他的一律不能跟。

8.4找出每个部门的最高薪资

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.01 sec)

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

两个字段联合分组。

先将deptno和job进行联合分组，再从联合分组中选出工资最高的。

mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.05 sec)

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

having不能单独使用，只能跟在group by后面使用。

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

第一步：找出每个部门的最高薪资：

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.01 sec)

第二步：要求显示最高薪资高于3000：

having可以对分完组后的数据进一步过滤：

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

结果：

+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

但这样方式的执行效率较低。

实际上可以先将大于3000的都找出来，然后再进行分组

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

结果：

+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.01 sec)

优化策略：优先选择where，where实在用不了时，再选择having。

无法使用where的情况：

找出每个部门的平均工资，要求显示平均工资高于2500的。

mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

这种情况要求我们先求出平均工资，这就必须要用到分组函数，而where后面不能直接跟分组函数，故只能用having。

9.去除重复记录

把查询结果中重复的记录去除可以使用distinct关键字。

注意：原表数据不会修改，只是查询的结果去重。

1)distinct出现在单个字段前：

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

2)distinct前不能有字段，不然会报语法错误。

比如：

mysql> select deptno,distinct job from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1

原因是：查询deptno有14条记录，查询disdtinct job只有5条记录，放在一起一定会报错。

3)如果distinct出现在多个字段之前，表示多个字段联合起来去重：

mysql> select job,deptno from emp order by job,deptno;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| ANALYST   |     20 |
| ANALYST   |     20 |
| CLERK     |     10 |
| CLERK     |     20 |
| CLERK     |     20 |
| CLERK     |     30 |
| MANAGER   |     10 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
+-----------+--------+
14 rows in set (0.01 sec)

mysql> select distinct job,deptno from emp order by job,deptno;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| ANALYST   |     20 |
| CLERK     |     10 |
| CLERK     |     20 |
| CLERK     |     30 |
| MANAGER   |     10 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
+-----------+--------+
9 rows in set (0.00 sec)

10.单表查询总结

所有的关键字：

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

以上关键字只能按照这个顺序排列，不能颠倒。

以上关键字执行顺序：

  1. from
  2. where
  3. group by
  4. having
  5. select
  6. order by

比如从某张表中查询数据：

  1. 先找到这张表
  2. 然后经过where条件筛选出有价值的数据
  3. 然后将这些筛选出来的数据进行分组
  4. 分完组后可以通过having进一步筛选
  5. 然后通过select数据查询出来
  6. 最后进行排序输出。

综合案例：

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

结果：

mysql> select job,avg(sal)
    -> from emp
    -> where job != 'manager'
    -> group by job
    -> having avg(sal) > 1500
    -> order by avg(sal) desc;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+
2 rows in set (0.01 sec)
posted @ 2021-07-16 21:58  TSCCG  阅读(95)  评论(0)  编辑  收藏  举报