MySQL 基础

1、数据库概述及数据准备

1.1、SQL 概述

SQL，一般发音为 sequel，SQL 的全称（Structured Query Language），SQL 用来和数据库打交道，完成和数据库的通信。

SQL是一套标准，但是每一个数据库都有自己的特性，当使用这个数据库特性相关的功能，这时 SQL 语句可能就不是标准了(90%以上的SQL都是通用的)。

1.2、什么是数据库

数据库（DataBase），简称 DB。

数据库是按照特定格式存储数据的一个或一组文件。简单来说就是存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

1.3、什么是数据库管理系统

数据库管理系统（DataBaseManagement），简称 DBMS。

数据库管理系统是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

常见的数据库管理系统：MySQL、Oracle、SqlServer、DB2、Sybase、Informix、InterBase、PostgreSQL等。

1.4、DB、DBMS、SQL 的关系

DBMS 负责执行 SQL 语句，通过执行 SQL 语句来操作 DB 当中的数据。

比如：DBMS -(执行)-> SQL -(操作)-> DB

1.5、MySQL 概述

MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。

MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

1.6、MySQL 的安装

请百度

1.7、SQL 语句的分类

SQL 语句可以分为：

DQL（Data Query Language）：数据查询语言（凡是带有 select 关键字的语句都是查询语句）
DML（Data Manipulation Language）：数据操作语言（凡是对表当中的数据进行增删改的语句都是 DML）
DDL（Data Definition Language）：数据定义语言（DDL 主要操作的是表的结构，凡是带有 create、drop、alter 的语句都是 DDL）
TCL（Transactional Control Language）：事务控制语言（事务提交、事务回滚）
DCL（Data Control Language）：数据控制语言（例如，授权 grant、撤销权限 revoke ...）

1.8、导入演示数据

在 MySQL 中导入 SQL 文件可以使用：source 文件路径

连接 MySQL，打开 cmd 输入 mysql -u root -p ，然后输入密码

C:\Users\admin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

创建 “test” 数据库
```
mysql> create database test;
```
使用 “test” 数据库
```
mysql> use test;
```

导入 SQL 文件

mysql> source D:\Study Nodes\MySql\data.sql;

导入的 SQL 文件为我们创建了这几张表：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |	部门表
| emp            |	员工表
| salgrade       |	工资等级表
+----------------+
3 rows in set (0.01 sec)

1.9、查看这三张表的数据

我们使用 select * from 表名;，就可以查看表中的所有数据。

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

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 * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

1.10、查看这三张表的结构

我们使用 desc 表名; 查看这三张表的结构:

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       | 部门编号
| DNAME  | varchar(14) | YES  |     | NULL    |       | 部门名称
| LOC    | varchar(13) | YES  |     | NULL    |       | 地理位置
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

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

mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int  | YES  |     | NULL    |       | 工资等级
| LOSAL | int  | YES  |     | NULL    |       | 最低工资
| HISAL | int  | YES  |     | NULL    |       | 最高工资
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、MySQL 中的常用命令

2.1、查看 MySQL 的版本

查看 MySQL 数据库的版本号：select version();

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.01 sec)

2.2、查看 MySQL 中有哪些数据库

查看 MySQL 中有哪些数据库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.04 sec)

2.3、使用指定数据库

使用指定数据库：use 数据库名;

mysql> use test;
Database changed

2.4、查询当前使用的数据库

查询当前使用的数据库：select database();

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

2.5、查看当前库中有哪些表

查看当前库中有哪些表：show tables;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |
| emp            |
| salgrade       |
+----------------+
3 rows in set (0.00 sec)

2.6、查看指定库中有哪些表

查看指定库中有哪些表：show tables from 库名;

mysql> show tables from books;
+-----------------+
| Tables_in_books |
+-----------------+
| t_book          |
| t_order         |
| t_order_item    |
| t_user          |
+-----------------+
4 rows in set (0.00 sec)

2.7、查看表的结构

查看表的结构：desc 表名;，这是 describe 表名; 的缩写

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> describe dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.8、查看表的创建语句

查看表的创建语句：show create table 表名;

mysql> show create table emp;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | 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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

2.9、终止一条语句

如果想要终止一条正在编写的语句，可键入 \c 。

mysql> select
    -> \c

注意：在 MySQL 中是不见分号不执行，分号表示结束！如果想要终止一条语句的执行就输入 \c 。

2.10、导入 SQL 文件

导入 sql 文件：source 文件路径

mysql> source D:\Study Nodes\MySql\data.sql;

2.11、退出 MySQL

可使用 \q、quit 或 exit：

mysql> \q
Bye

mysql> quit
Bye

mysql> exit
Bye

2.12、查看 MySQL 支持的存储引擎

使用命令：show engines \G

3、DQL 数据查询语言

3.1、简单查询

3.1.1、查询一个字段

查询一个字段：select 字段名 from 表名;

案例：查询员工姓名

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

select 语句后面跟的是字段名称，select是关键字，select 和字段名称之间采用空格隔开，from 表示将要查询的表，它和字段之间采用空格隔开。

3.1.2、查询多个字段

查询多个字段，字段之间用逗号隔开：select 字段名1, 字段名2 from 表名;

案例：查询员工的编号和姓名

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

查询多个字段，select 中的字段名采用逗号分隔即可。

3.1.3、查询所有字段

查询所有字段有两种方式：

第一种方式：将所有的字段名都写上

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

第二种方式：使用 * 号代替所有字段

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.1.4、给查询的字段起别名

起别名用到的关键字是 as

案例：查询员工的姓名和编号，要求字段名显示为中文

mysql> select empno as '编号', ename as '姓名' from emp;
+------+--------+
| 编号 | 姓名   |
+------+--------+
| 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 |
+------+--------+
14 rows in set (0.00 sec)

注意：起别名只是将查询结果中对应的列名显示为别名，原表列名不会改变。

as 关键字也可以省略变为：select empno '编号', ename as '姓名' from emp;

3.1.5、列可以参与数学运算

当字段参与数学运算时，会将该字段替换为字段中的每条数据进行运算。

案例：计算员工的年薪

mysql> select ename, sal * 12 from emp;
+--------+----------+
| ename  | sal * 12 |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

也可以为运算的字段起个别名

mysql> select ename, sal * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

3.2、条件查询

语法格式：select 字段1,字段2... from 表名 where 条件;

支持如下运算符：

运算符	说明
`=`	等于
`<>` 或 `!=`	不等于
`<`	小于
`<=`	小于等于
`>`	大于
`>=`	大于等于
`between...and...`	两个值之间，等同于`>=... and <=...`
`is null`	为 null（is not null 不为空）
`and`	并且
`or`	或者
`in`	包含，相当于多个 or（not in 不包含）
`not`	not 可以取非，主要和 is、in 一起用
`like`	like 称为模糊查询，支持 % 或 _ 匹配。`%` 匹配任意个字符；`_` 匹配一个字符

3.2.1、`=` 操作符

在 MySQL 中 = 操作符代表示等于，而不是赋值。

案例：查询工资等于 5000 的员工姓名

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

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

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

3.2.2、`<>` 和 `!=` 操作符

在 MySQL 中 <> 和 != 操作符都表示不等于。

案例：查询工资不等于 3000 的员工姓名

mysql> select ename,sal from emp where sal <> 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
12 rows in set (0.00 sec)

mysql> select ename,sal from emp where sal != 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
12 rows in set (0.00 sec)

3.2.3、`<` 操作符

在 MySQL 中 < 操作符表示小于。

案例：查询工资小于 1500 的员工

mysql> select ename,sal from emp where sal < 1500;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
6 rows in set (0.00 sec)

3.2.4、`<=` 操作符

在 MySQL 中 <= 操作符表示小于等于。

案例：查询工资小于等于 1500 的员工

mysql> select ename,sal from emp where sal <= 1500;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
7 rows in set (0.00 sec)

3.2.5、`>` 操作符

在 MySQL 中 > 操作符表示大于。

案例：查询工资大于 1500 的员工

mysql> select ename,sal from emp where sal > 1500;
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
7 rows in set (0.00 sec)

3.2.6、`>=` 操作符

在 MySQL 中 >= 操作符表示大于。

案例：查询工资大于等于 1500 的员工

mysql> select ename,sal from emp where sal >= 1500;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| FORD   | 3000.00 |
+--------+---------+
8 rows in set (0.00 sec)

3.2.7、`between...and...` 操作符

在 MySQL 中 between...and... 操作符表示介于... 和 ... 之间，包括这两个值。

案例：找出工资在 1100 和 3000 之间的员工，包括 1100 和 3000

# 这句话可以翻译为：工资介于 1100 和 3000 之间的员工
mysql> select ename,sal from emp where sal between 1100 and 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
11 rows in set (0.01 sec)

注意：between...and... 不仅可以应用数字，还可以应用于字符串，作用在字符串上是左闭右开，就是包含左边的值，但不包含右边。

3.2.8、`is null` 操作符

在 MySQL 中 is null 操作符表示为 NULL。

找出哪些员工补助为 null

mysql>  select ename,sal,comm from emp where comm is null;
+--------+---------+------+
| ename  | sal     | comm |
+--------+---------+------+
| SMITH  |  800.00 | NULL |
| JONES  | 2975.00 | NULL |
| BLAKE  | 2850.00 | NULL |
| CLARK  | 2450.00 | NULL |
| SCOTT  | 3000.00 | NULL |
| KING   | 5000.00 | NULL |
| ADAMS  | 1100.00 | NULL |
| JAMES  |  950.00 | NULL |
| FORD   | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+
10 rows in set (0.00 sec)

3.2.9、`is not null` 操作符

顾名思义 is not null 操作符就是 is null 和 not 的结合使用，所以表示不为 NULL

案例：找出哪些员工补助不为 null

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

3.2.10、`and` 操作符

在 MySQL 中 and 操作符表示并且。

案例：找出工资在 1100 和 3000 之间的员工，包括 1100 和 3000

# 这句话可以翻译为：工资大于等于 1100 并且小于等于 3000
mysql> select ename,sal from emp where sal >= 1100 and sal <= 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
11 rows in set (0.00 sec)

这个案例之前使用 between...and... 操作符也可以正确的查询出来，现在多种操作符联合使用也可以正确的查询出来。

3.2.11、`or` 操作符

在 MySQL 中 or 操作符表示或者。

案例：找出岗位是 MANAGER 和 SALESMAN 的员工

# 这句话可以翻译为：岗位是 MANAGER 的员工，或者岗位是 MANAGER 的员工
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)

3.2.12、`in` 操作符

在 MySQL 中 in 操作符表示包含的意思，该操作符完全可以被 = 和 or 这两种操作符联合使用来替代。使用 in 会更加简洁。

案例：找出岗位是 MANAGER 和 SALESMAN 的员工

# 这句话可以翻译为：岗位中包含 MANAGER 或 MANAGER 的员工
mysql> select ename,job from emp where job in('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

3.2.12、`not in` 操作符

顾名思义，not in 操作符就是 in 和 not 操作符的结合使用，表示不包含

案例：找出岗位不是 MANAGER 和 SALESMAN 的员工

# 这句话可以翻译为：岗位中不包含 MANAGER 或 MANAGER 的员工
mysql> select ename,job from emp where job not in('MANAGER','SALESMAN');
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+
7 rows in set (0.00 sec)

3.2.13、模糊查询 `like` 操作符

在 MySQL 中 like 操作符表示模糊查询，该操作符要与两个字符一起使用：

% 表示匹配任意个字符

_ 表示匹配任意一个字符

案例：找出名字当中含有 a 的员工

mysql> select ename from emp where ename like '%a%';
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| CLARK  |
| ADAMS  |
| JAMES  |
+--------+
7 rows in set (0.00 sec)

案例：找出名字中第二个字母是 a 的员工

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

案例：找出名字中有下划线的员工，这个时候就需要转移字符 \，通过这个字符将 _ 转为普通字符，而不是代表任意一个字符
```
mysql> select ename from emp where ename like '%\_%';
Empty set (0.00 sec)
```
注意：这句话是正确的，只是因为表中数据没有带 _ 的名字

3.3、排序 `order by`

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升序。

怎么指定升序或者降序呢，asc 表示升序，desc 表示降序。

order by ... 升序；order by ... asc 升序；order by ... desc 降序

案例：按照工资和姓名降序排列

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

注意：如果前面的字段无法完成排序（例如，工资相同）的时候，再根据后面的字段进行排序。

3.4、分组函数（多行处理函数）

分组函数一共有 5 个：

函数名	作用
`count()`	`取得记录数`
`sum()`	`求和`
`avg()`	`取平均值`
`max()`	`取最大的值`
`min()`	`取最小的值`

记住：所有的分组函数都是对 “某一组” 数据进行操作的。

注意：分组函数自动忽略 NULL 值，而且分组函数不能直接使用在 where 子句当中

3.4.1、`count()`

在 MySQL 中 count(字段名) 表示查询字段中数据值不为 NULL 的记录数；count(*) 表示查询表的总记录数。

案例：取得总记录数

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

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

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

注意：count(*) 代表取得表的总记录数，而 count(字段名) 代表取出该字段的记录数，分组函数又会自动忽略 NULL 值，所以只会取出该字段中值不为 NULL 的记录数，这也就是为什么三条语句查询结果不一样的原因。

3.4.2、`sun()`

在 MySQL 中 sun(字段名) 表示对该字段求和

案例：查询工资总和

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

3.4.3、`avg()`

在 MySQL 中 avg(字段名) 表示对该字段求平均值

案例：查询平均工资

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

3.4.4、`max()`

在 MySQL 中 max(字段名) 表示查询出该字段中的最大值

案例：查询最高工资

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

3.4.5、`min()`

在 MySQL 中 min(字段名) 表示查询出该字段中的最小值

案例：查询最低工资

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

3.5、空处理函数 `ifnull()`

在 MySQL 中 ifnull(字段名, 指定一个值) 表示该字段中的 NULL 数据会被当中指定的值来处理。

接下来看一个案例，我们就知道空处理函数 ifnull() 的用法了。

案例：计算每个员工的年薪

# 这句话可以翻译为：每个员工的(工资 + 补助) * 12 就是年薪
mysql> select ename,(sal + comm) * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

但是为什么有的员工的年薪是 NULL 呢，因为在 MySQL 中如果 NULL 值参与了运算，那么运算结果一定为 NULL，显然这是不对的。

案例改进，加入空处理函数

# 如果 comm 的值为 NULL，就被当作 0 来处理，这样一来就避免了 NULL 值参与运算
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.00 sec)

3.6、分组查询

3.6.1、`group by`

在 MySQL 中 group by 关键字表示分组：按照某个字段或者某些字段进行分组

案例：找出每个工作岗位的最高工资的员工

# 这句话可以翻译为按工作岗位进行分组，然后找出每个工作岗位中最高工资的那名员工
mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.01 sec)

注意：因为分组函数是对一组数据数据进行处理，而一张表按照部门分成了很多组，所以分组函数可以单独对组数据进行处理，这样就是为什么叫做分组函数的原因。如果没有对表做分组，整张表就算做是一组。

案例：找出每个部门中不同工作岗位的最高工资。

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

注意：多个字段联合分组，其实就可以把两个字段的数据合并起来进行分组。

3.6.2、`having`

在 MySQL 中 having 的作用是对分组之后的数据再次进行条件过滤。

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

mysql> select job,avg(sal) from emp group by job having avg(sal) > 2000;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
3 rows in set (0.01 sec)

3.6.3、`where` 和 `having` 的选择

在 MySQL 中 where 和 having 都是对查询的结果进行过滤，那我们该选择使用哪一种呢？接下来看个案例。

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

# 使用 where 进行过滤
mysql> select job,max(sal) from emp where sal > 2900 group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
3 rows in set (0.00 sec)

# 使用 having 进行过滤
mysql> select job,max(sal) from emp group by job having max(sal) > 2900;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
3 rows in set (0.00 sec)

可以看到 where 和 having 都可以完成，但是这两条语句的效率怎么样呢？

总结：在使用 where 或 having 都可以正确的完成一条语句的查询时，这个时候应该选择 where ，如果 where 不能直接完成条件过滤，再使用 having 。

3.7、去重关键字 `distinct`

再 MySQL 中 distinct 关键字的作用是：对查询的结果去重

案例：查询员工的上级领导的编号

mysql> select mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7698 |
| 7839 |
| 7698 |
| 7839 |
| 7839 |
| 7566 |
| NULL |
| 7698 |
| 7788 |
| 7698 |
| 7566 |
| 7782 |
+------+
14 rows in set (0.00 sec)

# 使用 distinct 关键字，去除重复数据
mysql> select distinct mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+
7 rows in set (0.00 sec)

可以看出：编号相同的数据都没有了。

3.8、连接查询

连接查询：也可以叫跨表查询，需要关联多个表进行查询，连接查询分为：

内连接
- 等值连接
- 非等值连接
- 全连接
外连接
- 左外连接
- 右外连接
全连接（基本不用）

3.8.1、笛卡尔积现象

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终查询的结果条数是两种表记录条数的乘积。

例如：

mysql> select * from emp, dept;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      | LOC      |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
   ....   .....    .....       ....   ..........    ......      ....       ..       ..   .......      ......
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
56 rows in set (0.01 sec)
# 由于数据太多，使用 ... 代替，就不详细展示了

可以看到，如果不做任何条件限制，出现了 56 条结果，这就是笛卡尔积现象。接下来我们添加上条件：

# e 和 d 是给 两张表起的别名，只是省略了 as 关键字
mysql> select * from emp e, dept d where e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      | LOC      |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+

加了条件，明显数据变少了，那么这算是避免了笛卡尔积现象吗？其实并没有，只是显示的数据条数变少了，匹配次数还是 56 次，只是匹配成功的数据显示了出来。

3.8.2、内连接

特点：联查的表没有主副之分，表表平等，只会显示匹配成功的数据。

语法：select .. from 表1 inner join 表2 on 连接条件

inner 可以省略不写：select .. from 表1 join 表2 on 连接条件

3.8.2.1、内连接——等值连接

内连接之等值连接：最大的特点是：条件是等量关系。

案例：查询每个员工的部门名称，要求显示员工名和部门名

mysql> select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

3.8.2.2、内连接——非等值连接

内连接之非等值连接：最大的特点：条件是非等量关系。

案例：找出每个员工的工资等级

mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.02 sec)

3.8.2.3、内连接——自连接

内连接之自连接：最大的特点是：一张表看作两张表，自己连接自己。

案例：找出每个员工的上级领导

mysql> select a.ename,b.ename from emp a join emp b on a.mgr = b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)

3.8.3、外连接

特点：联查的表有主副之分，主表的信息全部显示，跟副表的信息匹配成功则显示，没匹配成功则补 NULL

3.8.3.1、左外连接

特点：左边的表为主表，右边的表为副表。

语法：select .. from 表1 left outer join 表2 on 连接条件

outer 可以省略不写：select .. from 表1 left join 表2 on 连接条件

案例：找出每个员工的上级领导

mysql> select a.ename,b.ename from emp a left join emp b on a.mgr = b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)

KING 的上级领导没有匹配到，则以 NULL 补全。

3.8.3.2、右外连接

特点：右边的表为主表，左边的表为副表。

语法：select .. from 表1 right outer join 表2 on 连接条件

outer 可以省略不写：select .. from 表1 right join 表2 on 连接条件

案例：找出每个部门的员工

mysql> select e.ename, d.dname from emp e right join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)

OPERATIONS 部门没有员工，则以 NULL 补全。

3.8.4、三张表及以上怎么连接查询

语法：select .. from 表1 join 表2 left join 表3 .. on ..

表示：表 1 先和表 2 进行连接，然后将连接过后的数据当作一张表再和表 3 连接，如果还存在表 4，则再拿和表 3 连接后的数据当作一张表和表 4 连接。

案例：找出每个员工的部门名称以及工资等级

mysql> select
    -> e.ename,d.dname,s.grade
    -> from
    -> emp e
    -> join
    -> dept d
    -> on
    -> e.deptno = d.deptno
    -> join
    -> salgrade s
    -> on
    -> e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
14 rows in set (0.00 sec)

3.9、子查询

子查询的概念就是：select 语句当中嵌套 select 语句，被嵌套的 select 语句是子查询。

子查询可以出现在 select 后面、from 后面、where 后面

select
	..(select 语句)..
from
	..(select 语句)..
where
	..(select 语句)..

3.9.1、`select` 后面嵌套子查询

子查询语句直接出现在 select 关键字后面：会把子查询语句的执行结果当作查询数据直接显示出来。

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

mysql> select
    -> e.ename,
    -> (select d.dname from dept d where e.deptno = d.deptno) dname
    -> from
    -> emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)

3.9.2、`from` 后面嵌套子查询

子查询语句直接出现在 from 关键字后面：会将子查询语句的执行结果当作数据表处理。

案例：找出每个部门平均薪水的薪资等级

mysql> select
    -> t.*,s.grade
    -> from
    -> (select deptno,avg(sal) avg from emp group by deptno) t
    -> join
    -> salgrade s
    -> on
    -> t.avg between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avg         | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.01 sec)

3.9.3、`where` 后面嵌套子查询

子查询语句直接出现在 where 关键字后面：将子查询语句的执行结果当作过滤条件的一部分

案例：找出高于平均薪资的员工信息

mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| 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 |
|  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 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)

3.10、结果集相加 `union`

union 关键字可以将查询的结果集相加到一块显示出来。

案例：找出岗位是 MANAGER 和 SALESMAN 的员工

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

3.11、`limit` 关键字

limit 是 mysql 中特有的关键字，其他数据库没有，不通用。（Oracle 中有一个相同机制的关键字，叫做 rownum）

作用：取结果集中的部分数据

语法：limit 起始位置, 取几条，如果关键字后面只写一个数，则代表从 0 开始取，取写的条数。

案例：取出工资前 5 名的员工

mysql> select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

3.12、DQL 语句的执行顺序

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

4、DDL 数据定义语言

4.1、创建表 `create`

创建表之前建议先判断一下该表存不存在，如果存在就删除此表：drop table if exists 表名;

建表语句的语法格式：

create table 表名(
	字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型,
    字段名4 数据类型,
    ...
);

例如：建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

create table t_student(
	student_id int(10),
	student_name varchar(20),
	sex char(2),
	birthday date,
	email varchar(30),
	classes_id	int(3)	
)

注意：表名在数据库中一般建议以：t_ 或 tbl_ 开始。、

4.1.1、字段的数据类型

关于 MySQL 中字段的数据类型，常用的：

类型	描述
Char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键
Varchar(长度)	变长字符串，存储空间等于实际数据空间
double(有效数字位数，小数位)	数值型
Float(有效数字位数，小数位)	数值型
Int( 长度)	整型
bigint(长度)	长整型
Date	日期型年月日
DateTime	日期型年月日时分秒毫秒
time	日期型时分秒
BLOB	Binary Large OBject（二进制大对象，存储图片、视频等流媒体信息）
CLOB	Character Large OBject（字符大对象，存储大文本。）

4.2、增加表字段 `alter`

在 MySQL 中使用 alter 语句插入字段。

语法格式：alter table 表名 add 字段名字段类型;

4.3、修改字段类型 `alter`

在 MySQL 中也使用 alter 语句修改字段。

语法格式：alter table 表名 modify 字段名字段类型;

4.5、修改字段名 `alter`

在 MySQL 中也使用 alter 语句修改字段。

语法格式：alter table 表名 change 被修改字段名新字段名字段类型;

4.4、删除字段 `alter`

在 MySQL 中也使用 alter 语句删除字段。

语法格式：alter table 表名 drop 字段名;

5、DML 数据操作语言

5.1、添加数据 `insert`

在 MySQL 中使用 inster 语句插入数据。

语法格式：inster into 表名(字段名1, 字段名2, 字段名3...) values(值1, 值2, 值3);

要求：字段的数量和值的数量相同，并且数据类型要对应相同。

5.1.1、一次添加一条数据

向表中添加一条数据，写法一（指定字段的插入，建议使用）：

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

向表中添加一条数据，写法二（省略字段的插入，不建议建议使用）：
```
insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);
```
不建议使用写法二这种方式，因为当数据库表中的字段位置发生改变的时候会影响到该语句。

5.1.2、一次添加多条数据

一次添加多条数据，写法一（指定字段的插入，建议使用）：

insert into 
	emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) 
values
	(9999,'zhangsan','MANAGER', null, null,3000, 500, 10),
	(6666,'lisi','MANAGER', null, null,3100, 600, 10);

一次添加多条数据，写法二（省略字段的插入，不建议建议使用）：

insert into  
	emp 
values
	(9999,'zhangsan','MANAGER', null, null,3000, 500, 10),
	(6666,'lisi','MANAGER', null, null,3100, 600, 10);

5.2、修改数据 `update`

在 MySQL 中使用 update 语句修改数据。

语法格式：update 表名 set 字段名1 = 值1, 字段名2 = 值2 ... where 条件;

注意：如果不加条件，整张表的数据都会被修改。

案例：将 job 为 manager 的员工的工资上涨10%

update emp set sal= sal + sal*0.1 where job = 'MANAGER';

5.3、删除数据 `delete`

在 MySQL 中使用 delete 语句删除数据。

语法格式：delete from 表名 where 条件;

注意：如果不加条件，整张表的数据都会被删除。

案例：删除部门为 10 的员工数据
```
delete from emp where deptno = 10;
```
案例：删除所有数据
```
delete from emp;
```

5.3.1、删除大表（数据量很大的表）数据

删除大表数据（重点）：truncate table 表名

使用该语句删除数据，效率很高。

6、表的复制以及批量插入

6.1、表的复制

语法：create table 表名 as select语句

将 as 关键字后面的 select 查询语句的结果当作一张新表创建出来。

6.2、批量插入

将查询结果当成数据添加到一张表中。

语法： insert into 表名 select语句

要求：字段的数量、顺序、数据类型要一致。

7、约束（Constraint）

7.1、什么是约束，约束的作用

约束用来约束字段中的数据，在创建表的时候，可以给表添加相应的约束。

添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

约束在建表的时候，写在数据类型的后面。

7.2、常见的约束有哪些

常见的约束有：

非空约束（not null）：约束的字段不能为 NULL
唯一约束（unique）：约束的字段不能重复
主键约束（primary key）：约束的字段既不能为 NULL，也不能重复，简称 PK
外键约束（foreign key）：和主键约束联合使用，简称 FK
检查约束（check）：注意，Oracle 数据库有检查约束，但是 mysql 没有，目前 mysql 不支持该约束。

7.2.1、非空约束 `not null`

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

# 判断数据库是否已经存在，如果存在则删除
drop table if exists t_user; 
# 创建表
create table t_user(
    id int,
	username varchar(255) not null,
    password varchar(255)
);
# 添加数据
mysql> insert into t_user(id,password) values (1, '123');
# 报错，字段“username”没有默认值
ERROR 1364 (HY000): Field 'username' doesn't have a default value

7.2.3、唯一约束 `unique`

唯一约束修饰的字段具有唯一性，不能重复。但可以为 NULL。

# 判断数据库是否已经存在，如果存在则删除
drop table if exists t_user; 
# 创建表
create table t_user(
    id int,
	username varchar(255) unique # 列级约束
);
# 添加数据
mysql> insert into t_user values (1, 'zhangsan');
mysql> insert into t_user values (1, 'zhangsan');
# 再次执行添加操作报错，t_user.username 重复
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_user.username'

7.2.3.1、表级约束

表级约束，也就是多个字段联合进行唯一约束。

# 判断数据库是否已经存在，如果存在则删除
drop table if exists t_user; 
# 创建表
create table t_user(
    id int,
	username varchar(255),
    password varchar(255),
    unique(username, password) # 表级约束
);
# 添加数据
insert into t_user values(1, 'zhangsan', '123');
insert into t_user values(1, 'zhangsan', '321');

可以发现：这两个插入语句并没有报错，为什么呢？'zhangsan' 不是重复了吗？

因为 unique(username, password) 添加的是表级约束，这么添加约束会将这两个字段的数据，合并判断是否重复。

注意：not nul 约束没有表记约束，只有列级约束。

7.2.4、主键约束 `primary key`

特点：添加了主键约束的字段中的数据不能为 NULL，也不能重复。

关于主键的常用术语：

主键约束：primary key
主键字段：添加了主键约束的字段，叫做主键字段。
主键值：主键字段中的每一个值都是主键值。

注意：一张表只能添加一个主键约束（必须记住）。

使用列级约束定义主键：

# 判断数据库是否已经存在，如果存在则删除
drop table if exists t_user; 
# 创建表
create table t_user(
    id int primary key, # 列级约束
	username varchar(255),
    password varchar(255)
);

使用表级约束方式定义主键：

# 判断数据库是否已经存在，如果存在则删除
drop table if exists t_user; 
# 创建表
create table t_user(
    id int,
	username varchar(255),
    password varchar(255),
    primary key(id) # 表级约束
);

7.2.4.1、主键的分类

根据主键字段的字段数量来划分：

单一主键（推荐，常用）

复合主键（多个字段联合起来添加一个主键约束叫做符合主键，不建议使用）

# 判断数据库是否已经存在，如果存在则删除
drop table if exists t_user; 
# 创建表
create table t_user(
    id int,
	username varchar(255),
    password varchar(255),
    primary key(id,username) # 复合主键
);

根据主键性质来划分：

自然主键（主键字段就是一个没有和业务有任何挂钩的自然数字段（该字段中存储不重复的自然数），推荐使用）
业务主键（主键字段是和业务挂钩的字段。例如，使用姓名作为主键。不推荐使用）

7.2.4.2、mysql 提供主键值自增

关键字：auto_increment

# 判断数据库是否已经存在，如果存在则删除
drop table if exists t_user; 
# 创建表
create table t_user(
    id int primary key auto_increment, # id 字段自动维护一个自增的数字，从 1 开始，以 1 递增
	username varchar(255),
    password varchar(255)
);

提示：Oracle 中也提供了一个自增机制，叫做序列（sequence）对象。

7.2.5、外键约束 `foreign key`

语法：foreign key(字段名) references 引用的表名(引用的字段)

注意：外键值可以为 NULL，引用的字段不一定要有主键约束，但至少具有唯一约束。

# 班级表
create table t_class(
	cno int primary key, # 主键
    cname varchar(255)
);

# 班级表
create table t_student(
	sno int primary key, # 主键
    sname varchar(255),
    classno int,
    primary key(sno),
    foreign key(classno) references t_class(cno) # 给 classno 字段添加外键
);

注意：班级表的 classno 字段添加了外键，引用的是 t_class 表中的 cno 字段，所以该字段的值必须是 null 或 t_class 表中的 cno 字段中出现过的值。

8、存储引擎（了解）

8.1、完整的建表语句

create table 表名(
	...
) enging = InnoDB default charset = utf8;

建表的时候可以指定存储引擎 enging 也可以指定字符集 charset ，如果不指定就使用默认的存储引擎和字符集。

默认使用的存储引擎的 InnoDB；默认采用的字符集是 utf8.

默认的存储引擎也可在 my.ini 配置文件中使用 default-storage-engine 选项指定。

现有表的存储引擎可使用 alter table 语句来改变：alter table tablename engine1 = INNODB;

8.2、什么是存储引擎？

存储引擎这个名字只有在 MySQL 中存在。（Oracle 中也有对应的机制，但是不叫作存储引擎。Oracle 中没有特殊的名字，就是叫 “表的存储方式”）

MySQL 支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。

每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

8.3、查看 MySQL 支持的存储引擎

使用命令：show engines \G

MySQL 8.0.23 支持的存储引擎有 9 个：

mysql> show engines \G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

8.4、常见的存储引擎

8.4.1、MyISAM 存储引擎

*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

MyISAM 是 MySQL 最常用的存储引擎之一，但这种存储引擎不是 MySQL 默认的，需要自己指定。

MyISAM 采用三个文件组织一张表：

格式文件 — 存储表结构的定义（以 .frm 为后缀名的文件）
数据文件 — 存储表行的内容（以 .MYD 为后缀名的文件）
索引文件 — 存储表上索引（以 .MYI 为后缀名的文件）

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

缺点：不支持事务。

8.4.2、InnoDB 存储引擎

*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES

InnoDB 也是是 MySQL 最常用的存储引擎之一，这种存储引擎是 MySQL 默认的。

特点：

表的结构也存储在以 .frm 为后缀名的文件中。
数据则存储在 tablespace 这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种 InnoDB 存储引擎在 MySQL 数据库崩溃之后提供自动恢复机制。
InnoDB 支持级联删除和级联更新。

8.4.3、MEMORY 存储引擎

*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO

MEMORY 存储引擎以前叫做 HEPA 引擎。

特点：

表的结构也存储在以 .frm 为后缀名的文件中。
表数据及索引被存储在内存中。
不能包含 TEXT 或 BLOB 类型字段。

优点：因为数据存储在内存中，所以查询速度特别快。

缺点：不支持事务；数据容易丢失，断电即失。

9、事务

9.1、什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

例如：银行账户转账，从 A 账户向 B 账户转账 10000 元，需要执行两条 update 语句。

update t_act set balance = balance - 10000 where actname = 'A';
update t_act set balance = balance + 10000 where actname = 'B';

这两条 DML 语句必须同时成功或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条 DML 语句同时成功或者同时失败，那么就需要使用数据库中的 “事务机制”。

和事务相关的只有 DML 语句，因为只有 DML 语句是和数据库表中的数据有关。事务的存在就是为了保证数据的完整性、安全性。

9.2、事务的四大特性

事务的四大特性，ACID：

原子性（A）：事务是最小的工作单元，不可再分。
一致性（C）：事务必须保证多条 DML 语句同时成功或者同时失败。
隔离性（I）：事务 A 与事务 B 直接相互隔离，互不干扰。
持久性（D）：持久性说的是最终数据必须被持久化到硬盘文件中，事务才算成功的结束。

9.3、事务的隔离级别

事务的隔离级别决定了事务之间可见的级别。

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

脏读（Dirty Read）：一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读。
不可重复读（Non-repeatable Read）：在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
幻读（Phantom Read）：幻读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

9.3.1、四个隔离级别

事务的隔离级别理论上包括 4 个：

第一级别：读未提交（read uncommitted），允许一个事务可以看到其他事务未提交的修改，也就是对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
- 这种隔离级别存在的问题：脏读
第二级别：读已提交（read committed），允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的，也就是对方事务提交之后我方才可以读取到。
- 这种隔离级别解决了：脏读
- 这种隔离级别存在的问题：不可重复读
第三级别：可重复读（repeatable read），确保如果在一个事务中执行两次相同的查询语句，都能得到相同的结果，不管其他事务是否提交这些修改。
- 这种隔离级别解决了：不可重复读
- 这种隔离级别存在的问题：幻读，读取到的数据是幻象（备份）
第四级别：序列化读 / 串行化读（serializable）
- 解决了所有问题
- 但是，效率低，需要所有事务排队

Oracle 数据库默认的隔离级别是：读已提交。

MySQL 数据库默认的隔离级别是：可重复读。

9.3.2、演示事务的回滚和提交

MySQL 默认是自动提交（什么是自动提交？只要执行任意一条 DML 语句则提交一次），要想使用事务，进行手动提交或回滚，就需要关闭自动提交。

关闭自动提交：start transaction;

准备表

drop table if exists t_user;

create table t_user(
	id int primary key auto_increment,
    username varchar(255)
);

演示：MySQL 中的事务是支持自动提交的，只要执行一条 DML 语句，就提交一次。

mysql> select * from t_user; # 查询表数据，发现没有数据
Empty set (0.01 sec)

mysql> insert into t_user(username) values('zs'); # 插入数据
Query OK, 1 row affected (0.01 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user; # 查询数据，并没有进行回滚，而是直接提交了
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
1 row in set (0.00 sec)

演示：使用 start transaction; 关闭自动提交机制，并演示回滚 rollback;

mysql> select * from t_user; # 查询数据，发现只有一条数据
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
1 row in set (0.00 sec)

mysql> start transaction; # 关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('ls'); # 插入数据
Query OK, 1 row affected (0.01 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user; # 查询数据，发现并没有插入成功，这说明回滚成功。
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
1 row in set (0.00 sec)

演示：使用 start transaction; 关闭自动提交机制，并演示提交 commit;

mysql> select * from t_user; # 查询数据，发现只有一条数据
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+
1 row in set (0.00 sec)

mysql> start transaction; # 关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('ls'); # 插入数据
Query OK, 1 row affected (0.01 sec)

mysql> commit; # 提交
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user; # 查询数据，发现插入成功，这说明提交成功
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.01 sec)

9.3.3、演示事务的隔离级别

演示事务的隔离级别需要同时开启两个事务进行操作。

设置事务的全局隔离级别：set global transaction isolation level 隔离级别名称;

查看事务的全局隔离级别：

MySQL 5：select @@global.tx_isolation;
MySQL 8：select @@global.transaction_isolation;

9.3.3.1、演示读未提交

设置事务的隔离级别为读未提交 read uncommitted

mysql> set global transaction isolation level read uncommitted; # 设置事务的隔离级别
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.transaction_isolation; # 查看事务的隔离级别
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
1 row in set (0.00 sec)

接下来同时开启两个事务进行操作：

事务 A：

mysql> use test
Database changed
mysql> start transaction; # 1.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user; # 3.查询数据，发现只有两条
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from t_user; # 5.查询数据，发现有三条，事务 B 还没有进行提交的数据已经被读过来了，脏读现象
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
|  4 | ww       |
+----+----------+
3 rows in set (0.00 sec)

事务 B

mysql> use test
Database changed
mysql> start transaction; # 2.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('ww'); # 4.插入数据，但此时还没有提交
Query OK, 1 row affected (0.01 sec)

mysql>

9.3.3.2、演示读已提交

设置事务的隔离级别为读已提交 read committed

mysql> set global transaction isolation level read committed; # 设置事务的隔离级别
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation; # 查看事务的隔离级别
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.00 sec)

接下来同时开启两个事务进行操作：

事务 A：

mysql> use test
Database changed
mysql> start transaction; # 1.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user; # 3.查询数据，发现只有两条
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from t_user; # 5.查询数据，发现还是只有两条
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from t_user; # 7.查询数据，发现多了一条数据
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
|  5 | ww       |
+----+----------+
3 rows in set (0.00 sec)

事务 B

mysql> use test
Database changed
mysql> start transaction; # 2.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('ww'); # 4.插入数据，但未提交
Query OK, 1 row affected (0.01 sec)

mysql> commit; # 6.提交
Query OK, 0 rows affected (0.01 sec)

9.3.3.3、演示可重复读

设置事务的隔离级别为可重复读 repeatable read

mysql> set global transaction isolation level repeatable read; # 设置事务的隔离级别
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation; # 查看事务的隔离级别
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

接下来同时开启两个事务进行操作：

事务 A：

mysql> use test
Database changed
mysql> start transaction; # 1.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user; # 3.查询数据，发现只有两条
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from t_user; # 5.查询数据，发现还是只有两条
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.00 sec)

mysql> select * from t_user; # 7.查询数据，发现还是只有两条
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.00 sec)

事务 B

mysql> use test
Database changed
mysql> start transaction; # 2.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('ww'); # 4.插入数据，但未提交
Query OK, 1 row affected (0.01 sec)

mysql> commit; # 6.提交
Query OK, 0 rows affected (0.01 sec)

9.3.3.4、演示串行化读

设置事务的隔离级别为串行化读 serializable

mysql> set global transaction isolation level serializable; # 设置事务的隔离级别
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation; # 查看事务的隔离级别
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)

接下来同时开启两个事务进行操作：

事务 A：

mysql> use test
Database changed
mysql> start transaction; # 1.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user; # 3.查询数据，发现只有两条
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  3 | ls       |
+----+----------+
2 rows in set (0.00 sec)

mysql> insert into t_user(username) values('zl'); # 4.插入数据，但未提交
Query OK, 1 row affected (0.00 sec)

事务 B

mysql> use test
Database changed
mysql> start transaction; # 2.关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user; # 5.查询数据时，没有反应，是因为该事务在等待事务 A 提交或回滚，然后才能执行该事务

10、索引

10.1、什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

在数据库方面，查询一张表的数据有两种检索方式：

第一种：全表扫描
第二种：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？其实最根本的原理是缩小了扫描的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段，或某些字段添加索引。

10.2、怎么创建和删除索引对象？

主键和具有 unique 约束的字段会自动添加索引，所以根据主键查询效率较高。

创建索引对象：create index 索引名称 on 表名(字段名);

删除索引对象：drop index 索引名称 on 表名;

10.3、查看 sql 语句的执行计划

查看 sql 语句的执行计划：explain

mysql> explain select ename, sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

type 表示扫描类型；type 的值是 all 表示全部扫描。

给薪资 sal 字段添加索引：create index emp_sal_index on emp(sal);

mysql> explain select ename, sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

添加索引后，再次查看 sql 执行计划发现 type 的值变成了 ref 。

10.4、索引的实现原理

索引底层采用的数据结构是：B + Tree

索引的实现原理：通过 B + Tree 缩小扫描范围，底层索引进行了排序、分区，索引会携带数据在表中的 “物理地址”，最终通过索引检索到数据之后，获取到关联的 “物理地址”，通过 “物理地址” 直接定位表中的数据，效率是最高的。

例如：

select ename from emp where ename = 'SMITH' 通过索引检索获取到物理地址之后，转换为 select ename from emp where 物理地址 = XXXX

10.5、索引的分类

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加索引
主键索引：主键上会自动添加索引
唯一索引：有唯一约束的字段会自动添加索引
....

10.6、索引什么时候失效

模糊查询的时候，第一个通配符使用的是 %，的时候，索引会失效。

例如：select ename from emp where ename like '%A%';

10.7、什么时候考虑给字段添加索引？（满足什么条件）

数据量庞大（根据客户的需求，根据线上环境）
该字段很少有 DML 操作（因为字段进行修改操作，索引也需要维护）
该字段经常出现在 where 子句中

11、视图（view）

11.1、什么是视图

站在不同的角度去看数据。（同一张表的数据，通过不同的角度去看待。）

11.2、怎么创建和删除视图

创建视图：create view 视图名 as DQL语句

删除视图：drop view 视图名

注意：只有 DQL 语句才能以视图对象的方式创建出来。

11.3、操作视图

对视图进行增删改查（CRUD），会间接影响到原表数据。

把视图看作一张表，对视图进行操作，和对表进行操作的方式是一样的。

11.4、视图的作用

视图可以隐藏表的实现细节，可以对字段名进行保密。保密级别较高的系统，数据库只对外提供相关的视图，Java 程序员只对视图对象进行 CRUD。

12、DBA 命令（了解）

12.1、新建用户

CREATE USER username IDENTIFIED BY 'password';

说明：

username——你将创建的用户名
password——该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器.。

例如：create user p361 identified by '123'; ——可以登录但是只可以看见一个库 information_schema

12.2、授权

命令详解：

grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;

dbname=*表示所有数据库
tbname=*表示所有表
login ip=%表示任何ip
password为空，表示不需要密码即可登录
with grant option; 表示该用户还可以授权给其他用户

例如：

细粒度授权：首先以 root 用户进入 mysql ，然后键入命令：grant select,insert,update,delete on *.* to p361 @localhost Identified by "123";
- 如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 "%" 。
粗粒度授权：我们测试用户一般使用该命令授权，GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123";

注意：用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123" WITH GRANT OPTION;

privileges 包括：

alter：修改数据库的表
create：创建新的数据库或表
delete：删除表数据
drop：删除数据库/表
index：创建/删除索引
insert：添加表数据
select：查询表数据
update：更新表数据、
all privileges：允许任何操作
usage：只允许登录

12.3 回收权限

命令：revoke privileges on dbname[.tbname] from username;

例如：revoke all privileges on *.* from p361;

刷新权限：flush privileges

12.4、导出数据

在 windows 的 dos 命令窗口中执行：mysqldump 数据库名 [表名]>导出的路径/文件名.sql -uroot -proot

表名是可选项，如果不写表名则表示导出整个数据库，如果写则表示导出表。

12.5、导入数据

使用命令：source sql 文件路径

13、数据库设计三范式

13.1、什么是设计范式

设计范式是设计表的依据。按照这三个范式设计的表不会出现数据冗余。

13.2、三范式

第一范式：任何一张表都应该有主键，并且每一个字段其原子性不可再分。

第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖。

第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

posted @ 2022-06-02 16:56 zxy_1221 阅读(33) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· spring 笔记

· springMVC 笔记

· MySQL

· MySQL命令语法

· mysql入门基础夜间模式(onemark导入的)

阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了

公告

昵称： zxy_1221
园龄： 3年5个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

zxy1221

MySQL 基础

1、数据库概述及数据准备

1.1、SQL 概述

1.2、什么是数据库

1.3、什么是数据库管理系统

1.4、DB、DBMS、SQL 的关系

1.5、MySQL 概述

1.6、MySQL 的安装

1.7、SQL 语句的分类

1.8、导入演示数据

1.9、查看这三张表的数据

1.10、查看这三张表的结构

2、MySQL 中的常用命令

2.1、查看 MySQL 的版本

2.2、查看 MySQL 中有哪些数据库

2.3、使用指定数据库

2.4、查询当前使用的数据库

2.5、查看当前库中有哪些表

2.6、查看指定库中有哪些表

2.7、查看表的结构

2.8、查看表的创建语句

2.9、终止一条语句

2.10、导入 SQL 文件

2.11、退出 MySQL

2.12、查看 MySQL 支持的存储引擎

3、DQL 数据查询语言

3.1、简单查询

3.1.1、查询一个字段

3.1.2、查询多个字段

3.1.3、查询所有字段

3.1.4、给查询的字段起别名

3.1.5、列可以参与数学运算

3.2、条件查询

3.2.1、= 操作符

3.2.2、<> 和 != 操作符

3.2.3、< 操作符

3.2.4、<= 操作符

3.2.5、> 操作符

3.2.6、>= 操作符

3.2.7、between...and... 操作符

3.2.8、is null 操作符

3.2.9、is not null 操作符

3.2.10、and 操作符

3.2.11、or 操作符

3.2.12、in 操作符

3.2.12、not in 操作符

3.2.13、模糊查询 like 操作符

3.3、排序 order by

3.4、分组函数（多行处理函数）

3.4.1、count()

3.4.2、sun()

3.4.3、avg()

3.4.4、max()

3.4.5、min()

3.5、空处理函数 ifnull()

3.6、分组查询

3.6.1、group by

3.6.2、having

3.6.3、where 和 having 的选择

3.7、去重关键字 distinct

3.8、连接查询

3.8.1、笛卡尔积现象

3.8.2、内连接

3.8.2.1、内连接——等值连接

3.8.2.2、内连接——非等值连接

3.8.2.3、内连接——自连接

3.8.3、外连接

3.8.3.1、左外连接

3.8.3.2、右外连接

3.8.4、三张表及以上怎么连接查询

3.9、子查询

3.9.1、select 后面嵌套子查询

3.9.2、from 后面嵌套子查询

3.9.3、where 后面嵌套子查询

3.10、结果集相加 union

3.11、limit 关键字

3.12、DQL 语句的执行顺序

4、DDL 数据定义语言

4.1、创建表 create

3.2.1、`=` 操作符

3.2.2、`<>` 和 `!=` 操作符

3.2.3、`<` 操作符

3.2.4、`<=` 操作符

3.2.5、`>` 操作符

3.2.6、`>=` 操作符

3.2.7、`between...and...` 操作符

3.2.8、`is null` 操作符

3.2.9、`is not null` 操作符

3.2.10、`and` 操作符

3.2.11、`or` 操作符

3.2.12、`in` 操作符

3.2.12、`not in` 操作符

3.2.13、模糊查询 `like` 操作符

3.3、排序 `order by`

3.4.1、`count()`

3.4.2、`sun()`

3.4.3、`avg()`

3.4.4、`max()`

3.4.5、`min()`

3.5、空处理函数 `ifnull()`

3.6.1、`group by`

3.6.2、`having`

3.6.3、`where` 和 `having` 的选择

3.7、去重关键字 `distinct`

3.9.1、`select` 后面嵌套子查询

3.9.2、`from` 后面嵌套子查询

3.9.3、`where` 后面嵌套子查询

3.10、结果集相加 `union`

3.11、`limit` 关键字

4.1、创建表 `create`

4.2、增加表字段 `alter`

4.3、修改字段类型 `alter`

4.5、修改字段名 `alter`

4.4、删除字段 `alter`

5.1、添加数据 `insert`

5.2、修改数据 `update`

5.3、删除数据 `delete`

7.2.1、非空约束 `not null`

7.2.3、唯一约束 `unique`

7.2.4、主键约束 `primary key`

7.2.5、外键约束 `foreign key`