MySql教程

MySQL数据库之一

本文主要是介绍MySQL数据库之一，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、概述

写在前面：最近感觉自己的SQL语句写的好菜，必须来复习一下。在网上找到了一门课程，感觉讲的不错，所以用来当做联系笔记。

软件行业每天都在产生和存储大量的数据，如果说数据都在内存中进行存储的话，那么肯定是不可行的。因为仅仅是靠内存来存储数据是不合理的，所以必须在一个可以持久存储数据的地方。在计算机中磁盘就是我们数据存储的地方。

1、内存是有限的，存储的数量也是有上限；
2、在内存当中存储数据不是太安全，因为计算机一断电或者是出现bug，那么将会导致数据丢失；

基于上面的考虑，所以将数据存储到计算机硬盘当中去，也就是以文件的形式来进行存储。所以我们程序操作内存和操作磁盘相比有一定的效率性。肯定是操作内存比较快，我们的应用程序操作磁盘还需要经过操作系统这一层从磁盘中读取数据，所以效率低一点，这也是以后再性能优化方面的起始点。

MySQL就可以实现将数据存储到计算机硬盘中的一款数据库管理系统软件。

我们也可以自定义实现将程序中产生的数据保存到磁盘中去，利用流的方式来进行存储。

但是有了MySQL，将原来的通过“流”的思想来保存变成了通过简单的SQL语句就可以来进行实现。

数据库管理系统是用来管理数据的，可以很方便的利用SQL语句来对数据进行管理。

DBMS------SQL-----TABLE

1.1、概念

SQL、DB、DBMS之间的关系是什么？

SQL：结构化查询语言，是标准通用的语言。

DB：Database数据库，计算机中的数据库是以文件格式的形式存在；

DBMS:Database  Management System数据库管理系统（常见的有MySQL、oracle、SqlServer等等）

SQL和java一样，都是一门高级语言，SQL语句在执行的时候，会先经过编译，然后再执行SQL，这点和java代码是非常类似的。

SQL语句的编译是由DBMS来进行编译、执行的，底层我们是看不到的，是由DBMS来进行帮助我们来进行执行的。

今天需要学习的是MySQL关系型数据库，因为在MySQL中，表和表之间是有关系的，表和表之间所组成的关系组成了数据库。看一下下面的三张表之间的关系。

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept表：

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

salgrade表：

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

这里的表是存在着关系的。比如说：可以看到Smith坐在的部门是20，Smith的工资等级是1等等信息，所以每张表之间是具有关系的。

总结：

DBMS通过执行SQL来操作DB中的数据

2、表

表是数据库的基本组成单元，所有的数据最终都由表格的形式来进行展示，目的是为了可读性强、理解性好。

如下，可以很清晰的展示出对应的信息。

学号 姓名 年龄
1 张三 18
2 李四 23
3 王五 20

一张表包括行和列。行又被称之为数据/记录，列又被称之为字段。

比如说第一行的：张三的学号是1，年龄是18岁，可以很清晰的用来进行展示，这一行叫做数据（data）或者是一行记录；

学号这一列的叫做字段，上面表中有三个字段：学号、姓名、年龄

2.1、字段应该有哪些属性？

字段名   数据类型  相关约束

比如说上面的学号应该是int，姓名的数据类型应该是varchar，年龄的数据类型应该是int。

小结一下：用表来展示数据更加的直观易懂；每行叫做数据或者叫记录，每列叫做字段，字段有对应的名字、数据类型和约束。

3、SQL语句分类

DQL：数据查询语言。查询语句，凡是select都是查询语句

DML：数据操作语言。insert、delete、update，对表中的**数据**进行增删改。比如说将张三的年龄进行修改、增加一行记录、删除一行记录等等；

DDL：数据定义语言。create、drop、alter，对表**结构**的增删改。比如说是字段的数据类型、删除一个字段或者是添加一个字段等等；

TCL：事务控制语言。commit提交事务，rollback回滚事务。

DCL：数据控制语言。授权、撤销权限等等权利。超级管理员给哪个用户来分配权限等

3.1 查看表结构

查看表结构的信息

desc 表名;

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |
| LOSAL | int(11) | YES  |     | NULL    |       |
| HISAL | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

在命令行导入SQL脚本:

source +SQL脚本路径

source E:\bjpowernode.sql

查看建表语句

show create table 表名;

所以在命令行中执行一整套流程下来是：

1、net start mysql;
2、mysql -uroot -pxxxx;
3、show databases;
4、use 库名；
5、show tables;
6、show create table 表名；
7、desc 表名；
8、select * from 表名；

3.2查看表中数据

查看每张表中的数据

mysql> select * from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

3.3、简单查询语句

查询指定表的对应字段的值

select 字段1，字段2，字段3.... from 表名;

从salgrade表中查询losal、hisal字段所对应的值，这里显示的是一张虚拟表或者说是临时表，losal和hisal是表中的临时字段。

mysql> select losal,hisal from salgrade;
+-------+-------+
| losal | hisal |
+-------+-------+
|   700 |  1200 |
|  1201 |  1400 |
|  1401 |  2000 |
|  2001 |  3000 |
|  3001 |  9999 |
+-------+-------+
5 rows in set (0.00 sec)

查询员工表中的每个员工的年薪

可以看到查询的字段，在临时表/虚拟表中显示的字段就是查询时的字段。

mysql> select empno,ename,sal*12 from emp;
+-------+--------+----------+
| empno | ename  | sal*12   |
+-------+--------+----------+
|  7369 | SMITH  |  9600.00 |
|  7499 | ALLEN  | 19200.00 |
|  7521 | WARD   | 15000.00 |
|  7566 | JONES  | 35700.00 |
|  7654 | MARTIN | 15000.00 |
|  7698 | BLAKE  | 34200.00 |
|  7782 | CLARK  | 29400.00 |
|  7788 | SCOTT  | 36000.00 |
|  7839 | KING   | 60000.00 |
|  7844 | TURNER | 18000.00 |
|  7876 | ADAMS  | 13200.00 |
|  7900 | JAMES  | 11400.00 |
|  7902 | FORD   | 36000.00 |
|  7934 | MILLER | 15600.00 |
+-------+--------+----------+

说明：

1、字段可以参与数学运算；
2、参与查询的字段，在临时表中显示的就是对应的字段。但是这样子操作有时候不方便直观的展示，所以可以给临时表中的字段起别名    

mysql> select empno,ename,sal*12 as annualpay from emp;
+-------+--------+-----------+
| empno | ename  | annualpay |
+-------+--------+-----------+
|  7369 | SMITH  |   9600.00 |
|  7499 | ALLEN  |  19200.00 |
|  7521 | WARD   |  15000.00 |
|  7566 | JONES  |  35700.00 |
|  7654 | MARTIN |  15000.00 |
|  7698 | BLAKE  |  34200.00 |
|  7782 | CLARK  |  29400.00 |
|  7788 | SCOTT  |  36000.00 |
|  7839 | KING   |  60000.00 |
|  7844 | TURNER |  18000.00 |
|  7876 | ADAMS  |  13200.00 |
|  7900 | JAMES  |  11400.00 |
|  7902 | FORD   |  36000.00 |
|  7934 | MILLER |  15600.00 |
+-------+--------+-----------+

可以看到给临时表中的sal*12来进行显示的时候，起了个别名，然后在临时表中进行显示的时候就是对应的annualpay来进行显示的。

查询出所有字段的值

select * from 指定表;

但是实际工作中，并不建议使用这种方式。因为这种方式会造成效率低下，在DBMS进行执行的时候，首先会将“*”转换成表中的所有字段来进行查询。所有在实际工作中进行查询的时候，直接使用表中的所有字段来代替星号来进行使用，用来提高查询效率。在使用mybatis的时候只会将需要的字段都给写上，而不是去使用所谓的星号来进行代替。

mysql> select * from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

用所有的字段来代替星号，来进行展示，用来提升查询效率。

mysql> select empno,ename,job,mgr,hiredate,sal,comm,deptno from 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.4、条件查询

从表中查询出符合条件的字段的值。使用where来进行条件查询。

语法格式：

select 								执行顺序
    字段1，字段2，字段3.....		  第三步
from 
    表名							  第一步
where 
    条件；							  第二步

友情提醒：

第一步：查询的时候首先确定是从哪张表中来进行查询；
第二步：确定查询的条件是什么；

案例1：查询工资为5000的员工的姓名

mysql> select ename from emp where sal = 5000;
+-------+
| ename |
+-------+
| KING  |
+-------+
1 row in set (0.00 sec)

案例2：查询Smith的工资

mysql> select sal from emp where ename = 'smith';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)

查看ename的数据类型是varchar，所以等号后面的值需要加上单引号。在MySQL和oracle中，字符串都是需要使用单引号括起来的。

案例3：查询工资高于3000的员工

mysql> select empno,ename from emp where sal > 3000;
+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+
1 row in set (0.00 sec)

案例4：查询工资在2000到3000之间的员工

mysql> select ename,sal from emp where sal between 2000 and 3000;
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
+-------+---------+
5 rows in set (0.00 sec)

使用between A and B的时候，要注意A是小于B的，并且范围是在[A,B]之间的数据。

案例5：查询出没有津贴的员工

mysql> select ename,comm from emp where comm is null;
+--------+------+
| ename  | comm |
+--------+------+
| SMITH  | NULL |
| JONES  | NULL |
| BLAKE  | NULL |
| CLARK  | NULL |
| SCOTT  | NULL |
| KING   | NULL |
| ADAMS  | NULL |
| JAMES  | NULL |
| FORD   | NULL |
| MILLER | NULL |
+--------+------+
10 rows in set (0.00 sec)

注意：

1、在MySQL中，null不是值，不是一个值，不是一个值，不是一个值，表示的什么都没有，表示的是空；和0是有区别的，0是一个具体的值；
2、既然不是一个值，那么就不能够使用>、<、<=、>=或者是<>来进行条件匹配，那么如果需要进行使用的话，需要使用is null或者是is not    null来进行匹配

案例6：查询津贴不为null的员工

mysql> select ename,comm from emp where comm is not null;
+--------+---------+
| ename  | comm    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+
4 rows in set (0.00 sec)

可以看到null和0是有区别的，不为null的津贴中出现了0这个值。

null的使用方式只有两种：

is null;
is not null;

只有这两种使用方式。

案例7：查询工作岗位是manager和salesman的员工

mysql> select ename,job from emp where job = 'manager' or job = 'salesman';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

注意：

尽管需求中说的是“和”，但是SQL语句中写的是or，说明了将要根 据实际的场景来判断是使用and还是or来进行选择

案例8：找出薪资大于1000的并且部门是20或者是30的员工

mysql> select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30;
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| JAMES  |  950.00 |     30 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+
10 rows in set (0.00 sec)

发现上面是存在着问题的，因为james的工资根本就没有1000块钱。那么说明这条SQL语句是存在着问题的。

因为在DBMS中，and和or存在着优先级问题，and的优先级比or的优先级高。在进行执行的时候sal > 1000 and deptno = 20会先进行执行，然后再执行or deptno = 30，那么将会得到james的工资为950的也会出现。但是要求中明确指出的是大前提是工资得要大于1000块钱，所以在大前提的情况下再去执行小前提的条件。

mysql> select ename,sal,deptno from emp where sal > 1000 and ( deptno = 20 or deptno = 30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+
9 rows in set (0.00 sec)

错误使用优先级的顺序将会避开对应的查询出来的错误的结果集。

使用下面这种方式可以简单的避免掉可能出现的问题：

mysql> select ename,sal,deptno from emp where sal > 1000 and deptno in (20,30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+
9 rows in set (0.00 sec)

案例9：查询员工姓名中包含K的员工

mysql> select * from emp where ename like '%K%';
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

3.5、排序查询

格式

select 											第二步
    字段1，字段2，字段3..
from											第一步
    表名
order by 字段1 asc，字段2 desc，字段3 asc,...      第三步

说明：

1、order是排序的意思，by指的是通过哪个字段来进行排序，排序的规则是降序desc还是升序asc；
2、按照上面的执行步骤，首先确定的是哪些字段参与，然后确定哪些字段是要参与排序的    
3、在进行排序的时候，以第一个字段为主，后面的只是按照对应的辅助（以第一个字段查询出来的值可能存在相等的，所以需要根据第二种来排序）;

需求1：按照工资升序，找出员工名和薪资

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

需求2：按照工资降序排列，当工资相等的时候，按照名字来进行升序排序

mysql> select ename,sal from emp order by sal desc,ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| FORD   | 3000.00 |
| SCOTT  | 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 |
+--------+---------+
14 rows in set (0.00 sec)

可以看到sal为3000的两个人，ford和scott，按照名字进行升序的时候，ford的首字母是小于scott首字母，所以ford排在前面。
但是可以看到king是排在最前面的，因为按照排序的时候，sal是起着主导作用的，ename只是说当sal相等的时候，这个时候才发挥作用。

需求3：找出工作岗位是salesman的员工，按照薪资进行降序排列

mysql> select ename,job,sal from emp where job = 'salesman' order by sal desc;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+
4 rows in set (0.00 sec)

3.6、分组函数

所有的分组函数都是对某一组数据进行操作的，所以需要确定的是如何来进行分组。

分组函数通常都是和group by来进行联合使用的。任何一条分组函数都是在group by 执行只有才执行；当一条SQL语句中没有group by的时候，那么默认整张表为一组。而分组函数又是在where子句后面进行执行的。

分组函数还有另外一个名字叫做多行处理函数。它的特点是：输入多行，输出一行

分组函数一共有五个，如下：

count:计数
sum:求和
avg:求平均数
max:求最大值
min:求最小值

案例0：求每个工作岗位的最高薪资

mysql> select max(sal),job from emp group by job;
+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|  3000.00 | ANALYST   |
|  1300.00 | CLERK     |
|  2975.00 | MANAGER   |
|  5000.00 | PRESIDENT |
|  1600.00 | SALESMAN  |
+----------+-----------+
5 rows in set (0.00 sec)

但是这里又衍生出来一个新的问题：

select max(sal),job,ename from emp group by job;

这么写的话对吗？

在oracle中是错的，但是在MySQL就可以执行成功。但是执行出来的结果毫无意义。

看下面的案例：

mysql> select max(sal),job,ename from emp group by job;
+----------+-----------+-------+
| max(sal) | job       | ename |
+----------+-----------+-------+
|  3000.00 | ANALYST   | SCOTT |
|  1300.00 | CLERK     | SMITH |  // 明显是错误数据！！！
|  2975.00 | MANAGER   | JONES |
|  5000.00 | PRESIDENT | KING  |
|  1600.00 | SALESMAN  | ALLEN |
+----------+-----------+-------+
5 rows in set (0.00 sec)

结论：所以通常在from前面的内容要么是分组函数，要么是参与分组的字段，或者是二者结合。如果此时此刻出现了其他的字段，那么可能会造成结果没有意义的结果。

为什么说是：输入多行，输出一行，看下面案例

mysql> select sal from emp;
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+
14 rows in set (0.00 sec)

看一下使用分组函数：

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

sum函数会将sal这一列中所有的值都相加起来，变成一个值来记性输出

分组函数会自动忽略点字段里面的为null/空

mysql> select comm from emp;
+---------+
| comm    |
+---------+
|    NULL |
|  300.00 |
|  500.00 |
|    NULL |
| 1400.00 |
|    NULL |
|    NULL |
|    NULL |
|    NULL |
|    0.00 |
|    NULL |
|    NULL |
|    NULL |
|    NULL |
+---------+
14 rows in set (0.00 sec)

使用分组函数来进行测试

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

也就是说将以下几条数据查询出来

|  300.00 |
|  500.00 |
| 1400.00 |
|    0.00 |

然后进行统计了，而没有将为null的计算进来。

案例2：查询出来所有员工的总工资(测试sum函数是否会将null来给忽略掉)

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

结果真的是将所有的值给查询出来了，既然查询出来的是一个值。那么回顾下上面说的内容，如果说null参与了运算，那么最终的结果也应该是null值，但是这里并不是null，说明这里没有将null算进来，那么也就说明了sum函数或者说是分组函数会忽略null。

案列3：count(*)和count(具体字段)的区别

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

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

可以发现上面的特点，count(*)查询出来的是总记录条数，而count(comm)查询出来的是值不为null的数据。

案例4：求出每个部门不同岗位的最高薪资

这里牵扯到了多个分组字段（在之前的学习过程中，我们只用到了一个分组字段来进行分组，没有遇到多个分组字段）

我们首先将所有的需要的字段都查询出来，然后看下里面的东西。

+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     20 | CLERK     |  800.00 |
|     30 | SALESMAN  | 1600.00 |
|     30 | SALESMAN  | 1250.00 |
|     20 | MANAGER   | 2975.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | MANAGER   | 2850.00 |
|     10 | MANAGER   | 2450.00 |
|     20 | ANALYST   | 3000.00 |
|     10 | PRESIDENT | 5000.00 |
|     30 | SALESMAN  | 1500.00 |
|     20 | CLERK     | 1100.00 |
|     30 | CLERK     |  950.00 |
|     20 | ANALYST   | 3000.00 |
|     10 | CLERK     | 1300.00 |
+--------+-----------+---------+

那么在这个基础之上，按照部门和工作岗位来进行分组。

+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     10 | MANAGER   | 2450.00 |
|     10 | PRESIDENT | 5000.00 |
|     10 | CLERK     | 1300.00 |
|     20 | CLERK     |  800.00 |
|     20 | CLERK     | 1100.00 |    
|     20 | MANAGER   | 2975.00 |
|     20 | ANALYST   | 3000.00 |
|     20 | ANALYST   | 3000.00 |
|     30 | SALESMAN  | 1600.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | SALESMAN  | 1500.00 |    
|     30 | MANAGER   | 2850.00 |
|     30 | CLERK     |  950.00 |
+--------+-----------+---------+

通过上面可以看出来，分组的时候，我们需要将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 |
+--------+-----------+----------+

对比上面的数据，发现这里得到的结果是正确的。

案例五：找出每个部门最高薪资大于2900的

第一反应是使用where来进行过滤，但是我们使用having来使用下：

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

都说这个执行比where执行效率低！为什么效率低？来分析一下，我们是先经过deptno来进行分组，分完组之后得到了数据，但是这个时候又对得到的结果进行了过滤。那么不从一开始直接对不满足条件的记录给过滤掉呢？这就是使用where的好处。

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

二者结果是一样的。

这里只是学习了一下having的使用。但是有时候where可能会解决不了过滤的问题，那么这个时候就需要使用到having来进行解决了。

案例6：找出每个部门的平均薪资大于2000的

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

此时，不能够使用where，因为分组函数的执行顺序是在where之后的，不能够在where语句中写分组函数。

总结下：having是对分完组之后的数据进行过滤，而where是在分组之前进行过滤。

所以能在where中过滤的在where中过滤，不能再where中过滤的，才考虑使用having来进行过滤。

总结：

1、分组函数也叫多行处理函数，其特点是：输入一行，输出多行；
2、分组函数会自动忽略字段里面为null的，然后进行统计；
3、count(*)和count(某一个字段)的区别是什么？
    count(*)很明显统计的是所有的记录的条数
    count(某一个字段)统计的是某一个字段的，如果是null的话，那么会自动忽略掉
    所以说：count(*)>=count(某一个字段)

单行处理函数：

既然有多行处理函数，那么就存在着单行处理函数。相对应多行处理函数的特点，那么单行处理函数的特点就是：输入一行，输出一行

在所有的数据库中，需要记住的一点是：只要null参与了数据运算，那么最终的值也一定会是null，这是硬性规定

案例1：查询出来每个员工的年薪

mysql> select ename,(comm+sal)*12 from emp;
+--------+---------------+
| ename  | (comm+sal)*12 |
+--------+---------------+
| 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)

很显然，上面的数据是不正确的，因为从第一个就可以看出来，Smith的工资是有的，但是这里显示的是null，那么需要使用到ifnull函数来进行处理了。

ifnull()：空处理函数

mysql> select ename,(sal+ifnull(comm,0))*12 from emp;
+--------+-------------------------+
| ename  | (sal+ifnull(comm,0))*12 |
+--------+-------------------------+
| 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.04 sec)

可以看到对应的有以上几条数据，发现其中是没有null出现的。

分析下单行处理函数的特点：

select ename,(sal+ifnull(comm,0))*12 from emp;
1、当查询出来的comm的值为null的时候，才会执行这一条；
2、发现一行执行一次，并非是将所有的都直接的给查询计算出来；

3.7、group by和having

group by的意思是说根据哪个字段来进行分组。having和group by是两兄弟，永远在一起，使用了having，那么前面一定要有group by,having的出现就是为了group by分组之后进行过滤之后执行。

having的意思是根据条件来经过二次过滤，为什么说是二次过滤？因为where会领先于having来进行第一次过滤。having是对分完组后的数据再次进行过滤

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

根据工作岗位来进行分组，可以发现一共是有五个工作岗位的。

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

因为分组函数常常是和group by进行连用的，所以将sum、avg、max、min、count函数称之为分组函数；如果在一条SQL语句中没有出现group by函数，那么默认的是将整张表当做是一个组来进行划分

可以看下以下的两条SQL语句的区别：

-- 没有group by，默认整张表为一个组来进行查询。查询出来的是一个组中的最高的数据
mysql> select ename,max(sal),job from emp ;
+-------+----------+-------+
| ename | max(sal) | job   |
+-------+----------+-------+
| SMITH |  5000.00 | CLERK |
+-------+----------+-------+
1 row in set (0.00 sec)
-----------------------------------------------------------
-- 根据Job来进行分组，查询出来的是每个组中最高的数据。
mysql> select ename,max(sal),job from emp group by job;
+-------+----------+-----------+
| ename | max(sal) | job       |
+-------+----------+-----------+
| SCOTT |  3000.00 | ANALYST   |
| SMITH |  1300.00 | CLERK     |
| JONES |  2975.00 | MANAGER   |
| KING  |  5000.00 | PRESIDENT |
| ALLEN |  1600.00 | SALESMAN  |
+-------+----------+-----------+
5 rows in set (0.00 sec)

注意：

group by是在where执行之后才会进行执行的，所以如果说在where字句中出现了分组查询，那么在执行的时候是会发生错误的。

案例1：查询员工工资高于平均工资的员工

mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function -- 发生了这个错误，因为分组函数还没有进行分组，就直接使用了分组函数

对上面的改进方法：

mysql> select ename,sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.03 sec)

注意：当一条SQL语句中出现了group by，那么筛选的字段中只能出现参与分组的字段和分组函数（里面的字段不限）

如下显示的：

mysql> select ename,max(sal) from emp group by job;
+-------+----------+
| ename | max(sal) |
+-------+----------+
| SCOTT |  3000.00 |
| SMITH |  1300.00 |
| JONES |  2975.00 |
| KING  |  5000.00 |
| ALLEN |  1600.00 |
+-------+----------+
5 rows in set (0.00 sec)

可以看到Smith的工资明显是错误的，所以在group by中出现的字段如何在筛选的时候是没有的，那么将会查出来无效的数据。比如说这里的Smith的工资明显是错误的，不符合愿意。

案例2：查询每个工作岗位的平均工资

mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

案例3：查询每个部门不同的工作岗位的最高薪资

多个字段可以联合起来进行分组。

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.00 sec)

可以将（deptno,job）作为一个字段来进行看待，可以更好的来进行理解。

案例4：找出每个部门的最高薪资，要求显示薪资大于2900的数据

分析：面对这种复杂的数据，首先将需求拆分成为可以操作的步骤
第一步：找出每个部门的最高薪资
    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.00 sec)
 
（其实到了这一步，突然想明白max(sal)虽然是一个分组函数，但是最终显示的时候将其作为临时表中的一个字段就可以了）        
第二步：将显示出来的数据大于2900的数据显示出来
    mysql> select deptno,max(sal) from emp group by deptno having max(sal) > 2900;
    +--------+----------+
    | deptno | max(sal) |
    +--------+----------+
    |     10 |  5000.00 |
    |     20 |  3000.00 |
    +--------+----------+
    2 rows in set (0.00 sec)

注意：使用having来进行SQL查询的时候，这种效率比较低。因为已经查询出来了薪资是2850了，结果通过having又将其过滤掉了，那么还不如使用where来对其先进行过滤掉。所以说能使用where来进行过滤掉的，首先使用where而不是having

mysql> select deptno,max(sal) from emp where sal > 2900 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
+--------+----------+
2 rows in set (0.00 sec)

说起来这个group by，那么接下来就不得不说一下distinct关键字

使用语法：

1、只能出现在所有查询字段的最前面；

2、distinct关键字出现在所有字段最前面的时候，后面的字段表示的一组作为条件

select job,distinct deptno from emp;

distinct关键字后面跟着字段，如果说deptno都是不一样的，那么这样得到的结果和job显示的行数是一样的。比如说，有5行不同的deptno，job也是有五行不一样的。但是distinct表示的是不同的，那么使用了distinct之后，表示的是deptno显示的行数可能少于5行，但是要和一个5行的job来进行显示的话，那么是显示不出来的。所以distinct关键字必须要出现在select之后。

为什么distinct关键字可以出现在select之后，后面的字段表示的是分组？都知道distinct是去重的，那么后面显示的字段要和去重的字段保持一致。说明了也要对distinct关键字修饰的字段之后再次进行去重。

案例：

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

3.8、单表查询总结：

					 执行顺序
select 	
    字段				第五步：将哪些字段来进行筛选
from
    表名				第一步：需要对哪张表来进行查询
where
    条件				第二步：第一次经过条件过滤。所以如果这里没有经过分组，那么分组函数在这里是无法执行的 
group by
    字段				第三步：对哪个字段来进行分组
having
    条件				第四步：如果说第一次条件过滤过的结果不符合需求，再次进行过滤
order by
    字段				第六步：对筛选出来的字段进行排序显示
这篇关于MySQL数据库之一的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航