MySQL入门一（数据库简介、条件查询、排序、函数、分组查询）

本文链接：https://blog.csdn.net/BoldBoy_/article/details/140255065

该笔记是B站上的视频重抄了一遍，加深记忆与方便回顾。
MySQL入门视频

一、数据库概述

1.1、什么是是数据库？

数据库：DataBase，简称DB。是按照一定格式存储数据的一些文件的组合。

数据库管理系统：DataBase Management System，简称DBMS。是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统有：MySQL、Oracle、MS SqlServer、DB2、sybase等。

SQL：Structured Query Language，结构化查询语言。SQL语言由DBMS负责执行，最终完成对数据库中数据的增删改查。
SQL是一套标准，其可以在MySQL中使用，也能在Oracle中使用，在DB2中也能使用。

三者关系：DBMS - -执行- -> SQL - -操作- -> DB。

1.2、MySQL的前世今生

MySQL由荷兰公司MySQL AB开发，在2008年初，Sun公司收购了MySQL AB公司，在2009年，Oracle公司收购了Sun公司，最终将MySQL并入Oracle的数据库产品线。目前MySQL是开源免费的。

1.3、MySQL安装

这里不赘述安装流程，版本为8.0.26。

C:\Users\PC>mysql --version
mysql  Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)

buffer_pool_size设置的2G

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+
1 row in set (0.00 sec)

1.4、mysql常用命令

退出mysq

mysql> exit
Bye

查看所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

选择数据库

mysql> use mysql;
Database changed

查看数据库下有哪些表

mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
......

创建数据库

mysql> create database demo_db default charset=utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo_db            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

查看数据库版本号

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)

查看当前使用的是哪个数据库

mysql> select database();
+--------------------+
| database()         |
+--------------------+
| performance_schema |
+--------------------+
1 row in set (0.00 sec)

1.5、表

表（table）是一种结构化的文件，可以用来存储特定类型的数据。表中有几个概念：列、行、主键等。如：

姓名	性别	年龄
张三	男	20
李四	女	22

每一行称为记录
每一列称为字段

1.6、SQL语句的分类

DQL：数据查询语言（Data Query Language）

select…

DML：数据操作语言（Data Manipulation Language）

insert 增
delete 删
update 改

DDL：数据定义语言（Data Definition Language）

create 新建
drop 删
alter 改

TCL：事务控制语言（Transactional Control Language）

commit 事务提交
rollback 事务回滚

DCL：数据控制语言Data Control Language

grant 授权
revoke 撤销权限

1.7、导入数据

bjpowernode.sql 使用source命令将该sql文件导入到数据库中形成数据库和表数据。
注意不要有中文！！

mysql> create database bjpowernode default charset=utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> use bjpowernode;
Database changed
mysql> source  C:\Users\PC\Desktop\bjpowernode.sql;
-- 删除数据库
mysql> drop database demo_db;
Query OK, 0 rows affected (0.03 sec)

3张表
dept：部门表
emp：员工表
salgrade：工资等级表

mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
+-----------------------+

各表中的数据：

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

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 salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

1.8、表结构描述

查询表的结构，而不是表中的数据。

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |部门编号
| DNAME  | varchar(14) | YES  |     | NULL    |       |部门名字
| LOC    | varchar(13) | YES  |     | NULL    |       |地理位置
+--------+-------------+------+-----+---------+-------+
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |员工编号
| ENAME    | varchar(10) | YES  |     | NULL    |       |员工名称
| JOB      | varchar(9)  | YES  |     | NULL    |       |工作岗位
| MGR      | int         | YES  |     | NULL    |       |上级编号
| HIREDATE | date        | YES  |     | NULL    |       |入职时间
| SAL      | double(7,2) | YES  |     | NULL    |       |薪资
| COMM     | double(7,2) | YES  |     | NULL    |       |补助
| DEPTNO   | int         | YES  |     | NULL    |       |部门编号
+----------+-------------+------+-----+---------+-------+
mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int  | YES  |     | NULL    |       |工资等级
| LOSAL | int  | YES  |     | NULL    |       |最低工资
| HISAL | int  | YES  |     | NULL    |       |最高工资
+-------+------+------+-----+---------+-------+

二、常用简单命令

2.1、查看mysql版本

C:\Users\PC>mysql --version
mysql  Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)

C:\Users\PC>mysql -V
mysql  Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)

2.2、创建数据库

-- 使用mtf8字符集
mysql> create database db_demo default charset=utf8mb4;
Query OK, 1 row affected (0.01 sec)
-- 使用数据库
mysql> use db_demo;
Database changed

2.3、查询当前使用的数据

mysql> select database();
+------------+
| database() |
+------------+
| db_demo    |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)

2.4、终止一条语句

-- 如果想要终止一条正在编写的语句，可以使用:\c
mysql> create database db_demo\c
mysql>

2.5、退出mysql

-- 可以使用\q，quit或exit
mysql> \q
Bye

mysql> quit
Bye

mysql> exit
Bye

2.6、查看所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| bjpowernode        |
| db_demo            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
-- 使用其中一个数据库
mysql> use bjpowernode;
Database changed

2.7、查看其他数据库的表

mysql> show tables from db_demo;
+-------------------+
| Tables_in_db_demo |
+-------------------+
| t1                |
+-------------------+

2.8、查看表的创建语句

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

三、MySQL简单查询

3.1 查询一个字段

查询一个字段：员工姓名

mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+

3.2 查询多个字段

查询多个字段：员工编号和姓名

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

3.3 查询全部字段

查询全部字段：建议把表中的所有字段都列出来，这样清晰且可读性强，也可以使用*代替。

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

3.4 计算员工的年薪

SQL语句可以对字段值进行计算，如通过月薪求取年薪。
在select语句中可以使用运算符。

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

3.5 将查询出来的字段显示为中文

可以使用as关键字对字段进行重新命名，这个命名只在回显时可见，并不会修改原表名称。

mysql> select empno, ename, sal*12 as '年薪' from emp;
+-------+--------+----------+
| empno | ename  | 年薪     |
+-------+--------+----------+
|  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 |
+-------+--------+----------+

四、条件查询

where关键字后面的语句
where后面的查询语句支持一些运算符，如下：

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between…and…	两个值之间，等同于 >= and <=
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in 不在这个范围中）
not	not可以取非，主要用在 is 或 in 中
like	like称为模糊查询，支持%或下划线匹配（%匹配任意个字符；下划线：一个下划线只匹配一个字符）

4.1 等号操作符

查询薪水为5000的员工

mysql> select empno, ename , sal from emp where sal = 5000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7839 | KING  | 5000.00 |
+-------+-------+---------+

当精准查询的值是字符串时，要使用 单引号 或 双引号 括起来。
MySQL默认不区分大小写。

mysql> select empno, ename, job from  emp where job = 'MANAGER';
mysql> select empno, ename, job from  emp where job = 'manager';
+-------+-------+---------+
| empno | ename | job     |
+-------+-------+---------+
|  7566 | JONES | MANAGER |
|  7698 | BLAKE | MANAGER |
|  7782 | CLARK | MANAGER |
+-------+-------+---------+

Windows和Linux中默认的是否区分大小写是不同的，MySQL是否区分大小写可以通过 my.ini 配置文件进行修改。操作如下：

# 0表示区分大小写，1表示不区分大写
lower_case_table_names = 0

MySQL在Linux中数据库名、表名、列名、别名大小写规则是这样的：

数据库名和表名是严格区分大小写的；
表的别名是严格区分大小写的；
列名与列的别名在所有情况下都是忽略大小写的；
变量名也是严格区分大小写的，
MySQL在Windows下都不区分大小写；

4.2 <>操作负

在MySQL中不等于操作不建议使用!=，建议使用<>。
查询工资不等于5000的员工信息

mysql> select * from emp where sal <> 5000;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  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 |
+-------+--------+----------+------+------------+---------+---------+--------+

4.3 between…and…操作符

查询工资在1600到3000的员工信息

mysql> select * from emp where sal between 1600 and 3000;
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-04-02 | 2975.00 |   NULL |     20 |
|  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 |
|  7902 | FORD  | ANALYST  | 7566 | 1981-12-03 | 3000.00 |   NULL |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+

注意：between…and…是包含最大值和最小值的。

4.4 is null

null是空的意思，注意：空字符串并不代表null，如果想要查询null的字段，使用is null来筛选。
查询津贴为空的员工;

mysql> select * from emp where comm is null;
+-------+--------+-----------+------+------------+---------+------+--------+
| 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 |
|  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 |
|  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 |
+-------+--------+-----------+------+------------+---------+------+--------+

4.5 and

查询岗位为manager并且薪资在2500以上的员工

mysql> select * from emp where job = 'manager' and sal > 2500;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+---------+------+------------+---------+------+--------+

4.6 or

查询岗位为manager或者salesman的员工信息：

mysql> select * from emp where job = 'manager' or  job = 'salesman';
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+----------+------+------------+---------+---------+--------+

4.7 表达式优先级

运算符的优先级记起来太麻烦了，直接用小括号来指定优先级。and的优先级比or高。
查询薪水大于1800，并且部门代码为20或30的。如果不加这个小括号，就会导致查询出来小于等于1800的。

mysql> select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+

4.8 in

查询job为manager或者salesman的员工信息。

mysql> select * from emp where job in ('manager', 'salesman');
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+----------+------+------------+---------+---------+--------+

4.9 not

查询薪资不包含1600和3000的员工信息：

mysql> select * from emp where not (sal = 1600 or sal = 3000);
mysql> select * from emp where sal not in (1600, 3000);
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  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 |
|  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 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

查询津贴不为空的员工信息

mysql> select * from emp where comm is not null;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

4.10 like

%是指任意数量的任意字符：查询名字以M开头的员工信息

mysql> select * from emp where ename like 'M%';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

_是指一个任意字符：查询名字中第二个字符是A的

mysql> select * from emp where ename like '_A%';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

五、排序数据

排序使用order by子句，后面跟上排序的字段，排序字段可以放多个，使用逗号（，）隔开。
默认采用升序，如果有where子句，order by要放在where后面。

5.1 单一字段排序

按照薪资由小到大排序，我们可以看到默认是升序的：

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

按照多个字段排序，先按照job排序，再按照sal排序（先按照职位升序排，职位相同的情况下再按照薪资升序排）：

mysql> select * from emp order by job, sal;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.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 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+

5.2 手动指定排序顺序

asc：表示升序
按照薪资升序排：

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

desc：表示降序
按照薪资降序排：

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

5.3 多个字段排序

先按照岗位升序，当岗位相同时，再按照薪资降序排

mysql> select * from emp order by job asc, sal desc;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  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 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+

5.4 使用字段的位置来排序

按照薪资升序（数字表示第几列字段，这里索引不是从0开始，是从1开始）：
不建议使用该方法，含糊不明，且不具有健壮性。

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

六、函数

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

函数名	含义
Lower	转换小写
upper	转换大写
substr	取子串substr(被截取的字符串,起始下标,截取长度)
length	取长度
substr	去空格
ste_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换成一个具体的值

6.1 lower

将员工的姓名转成小写显示，14个输入，14个输出。

mysql> select lower(ename) name from emp;
+--------+
| name   |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+

6.2 upper

mysql> select 'abc';
+-----+
| abc |
+-----+
| abc |
+-----+
mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC          |
+--------------+

6.3 substr 截取字符串

substr(被截取的字符串，起始下标，截取长度)
查询名字以M开头的所有员工，不使用模糊查询

mysql> select * from emp where substr(ename,1,1) = upper('m');
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

6.4 concat函数进行字符串的拼接

把工号和姓名拼接一下

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

6.5 length

mysql> select length(ename) as enamelength from emp;
+-------------+
| enamelength |
+-------------+
|           5 |
|           5 |
|           4 |
|           5 |
|           6 |
|           5 |
|           5 |
|           5 |
|           4 |
|           6 |
|           5 |
|           5 |
|           4 |
|           6 |
+-------------+

6.6 trim

去掉前后的空格

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

6.7 str_to_date(必须严格按照标准输出)

6.8 date_formate

6.9 format

6.10 round

四舍五入（round(小数, 数值)）数值表示保留几位小数。
如果数值是负数，就继续往前保留，如-1即时保留到十位。

mysql> select round(123.456, 0) as res from emp;
+-----+
| res |
+-----+
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
+-----+

6.11 rand()

生成随机数（rand()会生成0~1范围的随机小数）
生成100以内的随机数

mysql> select rand()*100, round(rand()*100, 0) from emp;
+--------------------+----------------------+
| rand()*100         | round(rand()*100, 0) |
+--------------------+----------------------+
|  82.94638077071531 |                   11 |
|   8.62455797253601 |                    9 |
|  17.34920220202692 |                   61 |
|  52.01384774596789 |                   78 |
|  32.12031501542806 |                   28 |
|  42.16138323970268 |                   28 |
| 11.983474075778828 |                   77 |
|  48.23358594275414 |                   11 |
|   8.58689389059962 |                   11 |
| 29.048738096886073 |                   12 |
|  74.64400648571925 |                   36 |
| 56.797508016971406 |                   76 |
|  7.356621611264182 |                   10 |
|  28.59826612155723 |                   13 |
+--------------------+----------------------+

6.12 case…when…then…else…end

当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。
（注意：不修改数据库，只是将查询结果显示为工资上调）

mysql> select ename, job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALEMMAN' 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 | 1600.00 |
| WARD   | SALESMAN  | 1250.00 | 1250.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1250.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 | 1500.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 |
+--------+-----------+---------+---------+

6.13 ifnull

可以将null转换成一个具体的值
在所有数据库中，只要有null参与的数学运算，最终结果都是null。
计算每个人的薪资加奖金会发现有null值，因为有的人的奖金是null。

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

转换一下，把null值替换成0

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

多行处理函数

多行处理函数：输入多行，最终输出一行。
注意：

分组函数在使用时必须先分组，然后才能使用。
如果你没有对数据进行分组，那么整张表会看成一个组。

5个常用分组函数：

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

找出最高的薪资？

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

找出最低的薪资？

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

计算所有的工资和？

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

计算平均工资？

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

计算员工数量？

mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
|         14 |
+------------+

分组函数在使用时的注意点？

分组函数自动忽略null值，不需要提前对null进行处理；
count(*)表述表里一共有多少行，count(字段)之统计该字段非null的行数有多少；
分组函数不能直接使用在where子句中；
如：找出所有比最低工资高的员工信息。
原因是执行where子句时还没有对表进行分组，所以会报错。

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

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

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

七、分组查询（重要）

7.1 什么是分组查询？

在实际应用中会出现按照某种要求分组，然后对分组的数据再进行处理，这个时候就用到了分组函数。

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

上述语句的执行顺序是：

from 先查表
where 使用where筛选条件进行全表初筛
group by 然后进行分组
select 查哪些字段
order by 然后排序

练习：

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

执行顺序：

先从emp表中查询数据；
根据job字段进行分组；
然后对每一组进行求和。

mysql> select job, sum(sal) from emp group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+

mysql> select ename, job, sum(sal) from emp group by job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bjpowernode.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

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

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

先按照部门编号分组，然后再求每组中的最大值。

mysql> select deptno, max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     30 |  2850.00 |
|     10 |  5000.00 |
+--------+----------+

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

把两个字段联合起来看，联合分组。

mysql> select deptno, job, max(sal) from emp group by deptno, job order by deptno;
+--------+-----------+----------+
| 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 |
+--------+-----------+----------+

having可以对分组后的数据进一步筛选

having子句不能单独使用，必须和group by联合使用。

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

mysql> select job, max(sal) from emp group by job having max(sal) > 3000;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| PRESIDENT |  5000.00 |
+-----------+----------+

其实这样检索的效率是比较低的，分完组再找大于3000的，不如直接先把小于等于3000的使用where子句过滤掉，这样效率会高很多。

mysql> select job, max(sal) from emp where sal > 3000 group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| PRESIDENT |  5000.00 |
+-----------+----------+

优化策略：当where和having同时存在时，优先考虑where，where过滤不了的，再使用having进行过滤。

找出每个部门平均工资，显示平均工资大于2500的？

mysql> select deptno, avg(sal) from emp  group by deptno having avg(sal) > 2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

select查询语句顺序：

from从某张表中查询
先经过where进行初筛数据
使用group by进行分组
使用having对分组后再进行筛选
然后使用select查询你想要的字段
最后order by排序。

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

distinct去重

注意：去重只会改变查询结果，并不会改变表中的原始数据。

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

distinct字段只能出现在所有字段的最前方

mysql> select ename, 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

distinct出现在两个字段前，表示两个字段联合起来去重。可以发现 job + deptno 是没有重复的。

mysql> select distinct job, deptno from emp order by deptno;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     10 |
| MANAGER   |     10 |
| PRESIDENT |     10 |
| ANALYST   |     20 |
| CLERK     |     20 |
| MANAGER   |     20 |
| CLERK     |     30 |
| MANAGER   |     30 |
| SALESMAN  |     30 |
+-----------+--------+

统计一下工作岗位的数量？
一共有5种岗位！

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+