第1章 MySQL概述

1.1 前言

DB：数据库，存储数据的仓库

DBMS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理数据库，常见的有MySQL、Oracle、SQL Server

DBS：数据库系统，数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等，是最大的范畴

SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

1.2 MySQL安装

【1】mysql5.5版本的软件获取方式：

链接：https://pan.baidu.com/s/1ocwLwFxfPVXUb4jfKyJsSQ

提取码：关注威❤公宗浩zdb呀获取，回复关键字提取码，我给你白嫖，你给我加个粉撒。

【2】具体安装和卸载教程看视频讲解，这里就不讲解贴图。B站安装视频链接：

https://www.bilibili.com/video/BV1Vy4y1z7EX?p=3&spm_id_from=pageDriver

【3】如果安装过程中出现和我同样的问题，可以参考另外一篇博客：【解决方法】

mysql5.5安装不成功：最后一步未响应

【4】本篇博客的电子文档获取方式：

链接：https://pan.baidu.com/s/1eyw8XZlIRB0fnnqm2D8NIg

提取码：获取方式同上【1】

1.3 常见的指令

查看mysql服务

计算机–>右键–>管理–>服务和应用程序–>服务–>找mysql服务

MySQL的服务，默认是“启动”的状态，只有启动了mysql才能用。默认情况下是“自动”启动，自动启动表示下一次重启操作系统的时候，自动启动该服务。

（1）MySQL的启动和关闭语句

可以用指令开启和关闭mysql服务

net stop MySQL
net start MySQL

注意：需要用管理员身份打开cmd才能！

其他服务的停止和启动也可以通过以上net指令

（2）MySQL的登录语句

用bin目录下的mysql.exe命令来连接mysql数据库服务器

mysql -uroot  -p密码

显示以下则成功！

（3）MySQL的退出语句

exit

以下方式可以隐藏密码登录：

（4）查看MySQL的版本号

select version();

+-----------+
| version() |
+-----------+
| 5.5.36    |
+-----------+
1 row in set (0.01 sec)

（5）查看所有数据库

注意：有分号

show databases;

一开始默认自带四个数据库

（6）使用特定数据库

use 数据库名;

（7）创建数据库

create 数据库名;

（8）查看当前使用的数据库下所有表

show tables;

mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
| t_class               |
| t_student             |
| t_user                |
| t_vip                 |
+-----------------------+
7 rows in set (0.00 sec)

注意：表的行叫记录；列叫字段。每一个字段都有字段名、数据类型、约束等属性。

数据类型：字符串、数据、日期等

约束：约束有很多种，比如唯一性约束。

（9）查看当前使用的数据库

select database();

+-------------+
| database()  |
+-------------+
| bjpowernode |
+-------------+
1 row in set (0.00 sec)

注意： “;”表示语句结束！

注意：\c用来终止一条命令的输入。

注意：以上所有命令都不区分大小写！！！

1.4 SQL语句的五种分类

DQL：数据查询语言：select、from、where

DML：数据操作语言：insert（增）、update（删）、delete（改）。主要是操作表中数据的操作

DDL：数据定义语言：create（增）、drop（删）、alter（改）、truncate。主要是对表结构进行操作。

DCL：数据控制语言：grant（授予）、revoke（撤销权限）

TCL：事务控制语言：commit（事务提交）、rollback（事务回滚）

第2章 DQL语言：数据查询语言

DQL：数据查询语言：select、from、where

2.1 基础查询

（1）数据的导入

source 路径名.sql

注意：路径中不要有中文！！

例如导入bjpowernode.sql，怎么导入？

导入的数据中有三张表：dept是部门表，emp是员工表，salgrade 是工资等级表

（2）查看表中的数据

语法：select * from 表名;

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

（3）查看表的结构

desc 表名;

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    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

（4）简单查询DQL

1、查询一个字段

select 字段名 from 表名;

其中要注意：select和from都是关键字。字段名和表名都是标识符。

案例1.查询部门名字

mysql> SELECT DNAME FROM DEPT;   //注意，不区分大小写，这句也行
mysql> select dname from dept;
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

2、查询多个字段：使用逗号隔开

案例2：查询部门编号和部门名

mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

3、查询所有字段

方法一：把每个字段写上，逗号隔开

select a,b,c,d,e from tablename;

方法二：

select * from tablename;

方法二的缺点：效率低，可读性差；实际开发中不建议使用，后面的学习为了方便用这个

（5）给查询的列起别名

select 字段名 as 字段别名 from 表名;

mysql> select deptno,dname as name from dept;
+--------+------------+
| deptno | name       |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

注意：只是将显示的查询结果显示为name，原表还是：dname

记住：select语句不会对数据进行修改。

as关键字可以省略吗？可以的

mysql> select deptno,dname deptname from dept;
+--------+------------+
| deptno | deptname   |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

假设起别名的时候，别名里面有空格，怎么办？

mysql> select deptno,dname dept name from dept;

DBMS看到这样的语句，进行SQL语句的编译，不符合语法，编译报错。怎么解决？加引号

 select deptno,dname ‘dept name’ from dept; //加单引号
 select deptno,dname “dept name” from dept; //加双引号

mysql> select deptno,dname 'dept name' from dept;
+--------+------------+
| deptno | dept name  |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

注意：在所有的数据库当中，字符串统一使用单引号括起来。

单引号是标准，双引号在oracle数据库中用不了，mysql中可以使用。

案例：计算员工年薪

1.查员工名字和工资：sal * 12

mysql> select ename, sal as monthsal, (sal*12) as yearsal from emp;
+--------+----------+----------+
| ename  | monthsal | yearsal  |
+--------+----------+----------+
| SMITH  |   800.00 |  9600.00 |
| ALLEN  |  1600.00 | 19200.00 |
| WARD   |  1250.00 | 15000.00 |
| JONES  |  2975.00 | 35700.00 |
| MARTIN |  1250.00 | 15000.00 |
| BLAKE  |  2850.00 | 34200.00 |
| CLARK  |  2450.00 | 29400.00 |
| SCOTT  |  3000.00 | 36000.00 |
| KING   |  5000.00 | 60000.00 |
| TURNER |  1500.00 | 18000.00 |
| ADAMS  |  1100.00 | 13200.00 |
| JAMES  |   950.00 | 11400.00 |
| FORD   |  3000.00 | 36000.00 |
| MILLER |  1300.00 | 15600.00 |
+--------+----------+----------+
14 rows in set (0.00 sec)

2.起中文别名加单引号

mysql> select ename, sal '月薪', (sal*12) '年薪' from emp;
+--------+---------+----------+
| ename  | 月薪       | 年薪        |
+--------+---------+----------+
| SMITH  |  800.00 |  9600.00 |
| ALLEN  | 1600.00 | 19200.00 |
| WARD   | 1250.00 | 15000.00 |
| JONES  | 2975.00 | 35700.00 |
| MARTIN | 1250.00 | 15000.00 |
| BLAKE  | 2850.00 | 34200.00 |
| CLARK  | 2450.00 | 29400.00 |
| SCOTT  | 3000.00 | 36000.00 |
| KING   | 5000.00 | 60000.00 |
| TURNER | 1500.00 | 18000.00 |
| ADAMS  | 1100.00 | 13200.00 |
| JAMES  |  950.00 | 11400.00 |
| FORD   | 3000.00 | 36000.00 |
| MILLER | 1300.00 | 15600.00 |
+--------+---------+----------+
14 rows in set (0.00 sec)

2.2 条件查询：where

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

语法格式：

select
  字段1,字段2,字段3....
from 
  表名
where
  条件;

1. 大于、小于、等于、不等于

等于案例1：查询薪资等于800的员工姓名和编号和薪资

mysql> select empno, ename, sal from emp where sal=800;
+-------+-------+--------+
| empno | ename | sal    |
+-------+-------+--------+
|  7369 | SMITH | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)

不等于案例2：查询薪资不等于800的员工姓名和编号和薪资，两种表示方式：

mysql> select empno, ename, sal from emp where sal!=800;
mysql> select empno, ename, sal from emp where sal<>800;  //两句查询的结果都一样
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
13 rows in set (0.00 sec)

大于案例3：查询薪资小于2000的员工姓名和编号和薪资

mysql> select empno, ename, sal from emp where sal>2000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
6 rows in set (0.00 sec)

大于等于案例4：查询薪资大于等于3000的员工姓名和编号和薪资

mysql> select empno, ename, sal from emp where sal>=3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
3 rows in set (0.00 sec)

小于号同理，便不再介绍。

案例：查询SMITH的编号和薪资

select empno,ename,sal from emp where ename='SMITH'; //字符串使用单引号

2. between … and …. 两个值之间

等同于 >= and <=，闭区间

案例：查询薪资在2450和3000之间的员工信息？包括2450和3000

mysql> select empno, ename, sal from emp where sal>=2450 and sal<=3000;
mysql> select empno, ename, sal from emp where sal between 2450 and 3000;  //两句功能一样
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
5 rows in set (0.00 sec)

注意：使用between and必须左小右大；between and是闭区间

3. is null （is not null ）

案例：查询哪些员工的津贴/补助为null

mysql> select empno, ename, sal from emp where comm is null;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7566 | JONES  | 2975.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
10 rows in set (0.00 sec)
mysql> select empno, ename, sal from emp where comm is not null;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7654 | MARTIN | 1250.00 |
|  7844 | TURNER | 1500.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

注意：在数据库当中null不能使用等号进行衡量。需要使用is null因为数据库中的null代表什么也没有，它不是一个值。

4. and、or

案例：查询工作岗位是MANAGER**并且（或者）**工资大于2500的员工信息

mysql> select empno, ename, job, sal from emp where job='MANAGER' and sal>2500;
+-------+-------+---------+---------+
| empno | ename | job     | sal     |
+-------+-------+---------+---------+
|  7566 | JONES | MANAGER | 2975.00 |
|  7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec)
mysql> select empno, ename, job, sal from emp where job='MANAGER' or sal>2500;
+-------+-------+-----------+---------+
| 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 |
+-------+-------+-----------+---------+
6 rows in set (0.00 sec)

注意：and优先级比or高。

select * from emp where sal > 2500 and deptno = 10 or deptno = 20;    //两语句功能一样
select * from emp where (sal > 2500 and deptno = 10) or deptno = 20;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7876 | ADAMS | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);   //这句与上面不一样 
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)

5. in、not in

案例：查询工作岗位是MANAGER或SALESMAN的员工；查询薪资是800和5000的员工信息

mysql> select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec)
mysql> select * from emp where sal in (800,5000);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)

案例：查询薪资不是800,5000,3000的员工信息

mysql> select * from emp where sal not in (800,5000,3000);
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  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 |
|  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 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+
10 rows in set (0.00 sec)

6. like：类似python正则表达式

称为模糊查询，支持%或下划线匹配

%：匹配任意多个字符

下划线：任意一个字符

1. 找出名字中含有O的

mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+
3 rows in set (0.00 sec)

2.找出名字以T结尾的

mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)

3.找出名字以K开始的

mysql> select ename from emp where ename like 'K%';
+-------+
| ename |
+-------+
| KING  |
+-------+
1 row in set (0.00 sec)

4.找出第二个字每是A的

mysql> select ename from emp where ename like '_A%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+
3 rows in set (0.00 sec)

5.找出名字中有“ _”的，转义字符实现

mysql> select name from t_student where name like '%\_%'; // \转义字符。
    +----------+
    | name     |
    +----------+
    | jack_son |
    +----------+

2.3 排序查询：order by

（1）升序：asc

查询所有员工薪资，排序。升序

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

（2）降序：desc

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

（3）多个字段的排序

案例：查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。

案例分析：分主次。sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。

mysql> select ename,sal from emp order by sal asc, ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 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)

（4）按字段位置排序

了解一下，不建议这样排序。因为不健壮，很容易列顺序发生改变。

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
select ename,sal from emp order by 2;   //按照第二列排序  索引从1开始
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

总结：

以上语句的执行顺序必须掌握：
  第一步：from
  第二步：where
  第三步：select
  第四步：order by（排序总是在最后执行！）

2.4 单行处理函数

即数据处理函数。当然还有多行处理函数

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

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

（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 rows in set (0.00 sec)

（2）upper()：转换成大写

select upper(ename) as ename from emp;

（3）substr()

注意：起始索引从1开始

mysql> select substr(ename,1,1) as ename from emp;
+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+
14 rows in set (0.01 sec)

案例：挑选首字母为A的人名

mysql> select ename from emp where ename like 'A%';
mysql> select ename from emp where substr(ename,1,1)='A';   //两句功能一样
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)

（4）length()：求长度

案例：获取名字的长度

mysql> select ename, length(ename) enameLength from emp;
+--------+-------------+
| ename  | enameLength |
+--------+-------------+
| SMITH  |           5 |
| ALLEN  |           5 |
| WARD   |           4 |
| JONES  |           5 |
| MARTIN |           6 |
| BLAKE  |           5 |
| CLARK  |           5 |
| SCOTT  |           5 |
| KING   |           4 |
| TURNER |           6 |
| ADAMS  |           5 |
| JAMES  |           5 |
| FORD   |           4 |
| MILLER |           6 |
+--------+-------------+
14 rows in set (0.01 sec)

（5）concat()：字符串拼接

案例：将empno字段和ename字段拼接起来

mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
| 7566JONES           |
| 7654MARTIN          |
| 7698BLAKE           |
| 7782CLARK           |
| 7788SCOTT           |
| 7839KING            |
| 7844TURNER          |
| 7876ADAMS           |
| 7900JAMES           |
| 7902FORD            |
| 7934MILLER          |
+---------------------+
14 rows in set (0.00 sec)

（6）trim()：去空格

mysql> select * from emp where ename='  KING';
Empty set (0.00 sec)
mysql> select * from emp where ename=trim('  KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

（7）case…when…then…when…then…else…end

案例：当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。

mysql> select ename,job,sal oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec)

注意：字面值后面也可以跟字面值：

select ‘abc’ from emp;

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

（8）round() 四舍五入保留小数

案例：保留整数

mysql> select round(123.456, 0) as result from emp limit 5;   //保留整数
+--------+
| result |
+--------+
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
+--------+
5 rows in set (0.00 sec)
mysql> select round(123.456, 1) as result from emp limit 5;  //四舍五入保留一位小数
+--------+
| result |
+--------+
|  123.5 |
|  123.5 |
|  123.5 |
|  123.5 |
|  123.5 |
+--------+
5 rows in set (0.00 sec)
mysql> select round(123.456, 2) as result from emp limit 5;
+--------+
| result |
+--------+
| 123.46 |
| 123.46 |
| 123.46 |
| 123.46 |
| 123.46 |
+--------+
5 rows in set (0.00 sec)
mysql> select round(123.456, -2) as result from emp limit 5;  //保留到百分位
+--------+
| result |
+--------+
|    100 |
|    100 |
|    100 |
|    100 |
|    100 |
+--------+
5 rows in set (0.00 sec)

（9）rand() 生成随机数

案例：生成1以内的随机数

mysql> select rand() from emp limit 5;
+----------------------+
| rand()               |
+----------------------+
|   0.3314896806436495 |
|  0.41996393764388185 |
|  0.10535067702210571 |
|   0.2668616029125281 |
| 0.018256014229968205 |
+----------------------+
5 rows in set (0.00 sec)

案例：生成100以内的随机数，round和rand组合使用

mysql> select round(rand()*100,0) from emp limit 10;
+---------------------+
| round(rand()*100,0) |
+---------------------+
|                  22 |
|                   4 |
|                  55 |
|                  61 |
|                  40 |
|                  18 |
|                  68 |
|                  85 |
|                  22 |
|                  57 |
+---------------------+
10 rows in set (0.00 sec)

（10）ifnull()：可以将 null 转换成一个具体值

ifnull是空处理函数。专门处理空的。

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

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select ename,sal+comm as salcomm from emp;
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |    NULL |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  |    NULL |
| MARTIN | 2650.00 |
| BLAKE  |    NULL |
| CLARK  |    NULL |
| SCOTT  |    NULL |
| KING   |    NULL |
| TURNER | 1500.00 |
| ADAMS  |    NULL |
| JAMES  |    NULL |
| FORD   |    NULL |
| MILLER |    NULL |
+--------+---------+
14 rows in set (0.00 sec)

注意：NULL只要参与运算，最终结果一定是NULL。为了避免这个现象，需要使用ifnull函数。

（11）ifnull()函数

ifnull函数用法：ifnull(数据, NULL赋予其他值)

例：补助为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)

2.5 多行处理函数

即分组函数

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

1. 五个分组函数

count()：计数

sum()：求和

avg()：平均值

max()：最大值

min()：最小值

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

2. 使用时注意事项

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

例如：求sum时不会把NULL加进去。

第二点：分组函数中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(具体字段)：表示统计该字段下所有不为NULL的元素的总数。

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

第三点：分组函数不能够直接使用在where子句中

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

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

第四点：所有的分组函数可以组合起来一起用

mysql> select sum(sal),max(sal),min(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | max(sal) | min(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |  5000.00 |   800.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.00 sec)

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

【B站老杜】mysql详解(上)