数据库

发表于2025-04-01|更新于2026-02-04|笔记

|总字数:37.8k|阅读时长:133分钟|浏览量:

数据库概述

什么是数据库

数据库对应的英文单词是DataBase，简称DB
java的端口号：8080，mysql的3306

数据库类型

关系型数据库
- 关系型数据库是依据关系模型来创建的数据库。所谓关系模型就是“一对一、一对多、多对多”等关系模型
非关系型数据库（NoSQL）

数据库管理系统

数据库管理系统（Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
数据库管理系统是数据库系统的核心组成部分，主要完成对数据库的操作与管理功能，实现数据库对象的创建、数据库存储数据的查询、添加、修改与删除操作和数据库的用户管理、权限管理等。
常见的数据库管理系统有：MySQL、Oracle、DB2、MS SQL Server、SQLite、PostgreSQL、Sybase等。

什么是SQL

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
DBMS（软件）-> SQL（语言）-> DB（文件）
SQL的分类
- DQL（最重要）
  - 数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。
- DDL（对表结构进行操作）
  - 数据定义语言 (Data Definition Language, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始，现在被纳入 SQL 指令中作为其中一个子集。
- DML
  - 数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。
- DCL
  - 数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

登录MySQL

修改MySQL的root账户密码：ALTER USER ‘root‘@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;

本地登录

如果mysql的服务是启动的，打开dos命令窗口，输入：mysql -uroot -p，回车，然后输入root账户的密码

![(image-20250315165831211.png)

解释“mysql -uroot -p”：
mysql是一个命令，在bin目录下，对应的命令文件是mysql.exe，如果将bin目录配置到环境
变量path中，才可以在以上位置使用该命令。
-uroot 表示登录的用户是root，u实际上是user单词的首字母。
-p 表示登录时使用密码，p实际上是password单词的首字母。

也可以将密码以明文的形式写到-p后面，这样做可能会导致你的密码泄露

远程登录

假设mysql安装在A机器上，现在你要在B机器上连接mysql数据库，此时需要使用远程登录，远程登录时加上远程机器的ip地址即可

-h中的h实际上是host单词的首字母。在-h后面的是远程计算机的ip地址。
127.0.0.1是计算机默认的本机IP地址。
127.0.0.1又可以写作：localhost，他们是等效的。
注意：mysql默认情况下root账户是不支持远程登录的，其实这是一种安全策略，
为了保护root账户的安全。如果希望root账户支持远程登录，这是需要进行设置的。

mysql8 开放root账户远程登录权限（危险动作）

第一步：现在本地使用root账户登录mysql

第二步：use mysql;

第三步：update user set host = ‘%’ where user = ‘root’;

第四步：flush privileges;

第03章初始化数据

MySQL命令行基本命令

列出当前数据库管理系统中有哪些数据库。

1	show databases;

创建数据库，起名bjpowernode。

1	create database bjpowernode;

使用bjpowernode数据库。

1	use bjpowernode;

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

1	select database();

查看当前数据库中有哪些表。

1	show tables;

删除数据库bjpowernode。

1	drop database bjpowernode;

退出mysql
1. exit
2. quit
3. ctrl + c
查看当前mysql版本

1	select version();

还可以使用mysql.exe命令来查看版本信息（在没有登录mysql之前使用）：mysql –version

数据库表的概述

name	age	gender
张三	20	男
李四	22	女

行是row，列是column

任何一张表都有行和列：
- 行：记录（一行就是一条数据）
- 列：字段（name字段、age字段、gender字段）
每个字段包含以下属性：
- 字段名：name、age、gender都是字段的名字
- 字段的数据类型：每个字段都有数据类型，比如：字符类型、数字类型、日期类型
- 字段的数据长度：每个字段有可能会有长度的限制
- 字段的约束：比如某些字段要求该字段下的数据不能重复、不能为空等，用来保证表格中数据合法有效

初始化测试数据

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT(DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
);
CREATE TABLE EMP(EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
);

CREATE TABLE SALGRADE( GRADE INT,
	LOSAL INT,
	HISAL INT
);

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); 
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10); 
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); 
commit;

什么是sql脚本：文件名是.sql，并且该文件中编写了大量的SQL语句，执行sql脚本程序就相当于批量执行SQL语句。
创建文件：bjpowernode.sql，把以上SQL语句全部复制到sql脚本文件中。
执行SQL脚本文件，初始化数据库
- 第一步：命令窗口登录mysql
- 第二步：创建数据库bjpowernode（如果之前已经创建就不需要再创建了）：create database bjpowernode;
- 第三步：使用数据库bjpowernode：use bjpowernode;
- 第四步：source命令执行sql脚本，注意：source命令后面是sql脚本文件的绝对路径。
- 第五步：查看是否初始化成功，执行：show tables;
使用其他的mysql客户端工具也可以执行sql脚本，比如navicat。使用source命令执行sql脚本的优点：可支持大文件。

熟悉测试数据

查看表结构：desc或describe，语法格式：desc（简写）或describe +表名

第04章查询DQL专题

简单查询

查一个字段

查询一个字段说的是：一个表有多列，查询其中的一列。
语法格式：select 字段名 from 表名;

select和from是关键字，不能随便写
一条SQL语句必须以“;”结尾
如果需要关键字做标识符创建，需要加上`关键字`
对于SQL语句来说，大小写都可以
字段名和表名属于标识符（起名字的），按照表的实际情况填写，不知道字段名的，可以使用desc命令查看表结构

案例1：查询公司中所有员工编号

1	select empno from emp;

在mysql命令行客户端中，sql语句没有分号是不会执行的：末尾加上“;”就执行了：

假设一个SQL语句在书写过程中出错了，怎么终止这条SQL呢？\c

查多个字段

查询多个字段时，在字段名和字段名之间添加“,”即可。
语法格式：select 字段名1,字段名2,字段名3 from 表名;
案例1：查询员工编号以及员工姓名。

1	select empno, ename from emp;

查所有字段

查询所有字段的可以将每个字段都列出来查询，也可以采用“*”来代表所有字段
案例1：查询员工的所有信息

1	select * from emp;

查询时字段可参与数学运算

在进行查询操作的时候，字段是可以参与数学运算的，例如加减乘除等。
案例1：查询每个员工的月薪

1	select ename, sal from emp;

案例2：查询每个员工的年薪（月薪 * 12）

1	select ename, sal * 12 from emp;

任务：查询每个员工月薪加1000之后的年薪

1	select ename, (sal + 1000)*12 from emp;

查询时字段可起别名

as可以省略，但是名字里不能有空格，需要有空格的情况就要加单引号

查询日期之类的也需要单引号

1
2

select ename, (sal + 1000)*12 as yearsal from emp;//as可以省略，但是名字里不能有空格，需要有空格的情况就要加单引号
select empno 员工编号,ename 员工姓名 from emp;

条件查询

条件	说明
=	等于
<>或!=	不等于
>=	大于等于
<=	小于等于
>	大于
<	小于
between…and…	等同于 >= and <=
is null	为空
is not null	不为空
<=>	安全等于（可读性差，很少使用了）。
and 或 &&	并且
or 或 \|\|	或者
in	在指定的值当中
not in	不在指定的值当中
exists
not exists
like	模糊查询

条件查询语法格式

select 
  ...
from
  ...
where
  过滤条件;
  比如
  select job,sal from emp where ename = 'FORD';

过滤条件放在where子句当中，以上语句的执行顺序是：
第一步：先执行from
第二步：再通过where条件过滤
第三步：最后执行select，查询并将结果展示到控制台;

等于 =

判断等量关系，支持多种数据类型，比如：数字、字符串、日期等。
案例1：查询月薪3000的员工编号及姓名

select 
  empno,ename
from
  emp
where
  sal = 3000;

安全等于（简单了解）

<=>安全等于，用的很少，可读性差

<=>安全等于可作为普通运算符=

不等于 <> 或 !=

案例1：查询工资不是3000的员工编号、姓名、薪资

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

大于 >、大于等于 >=、小于 <、小于等于 <=、and、or

案例：找出薪资大于3000的员工姓名、薪资

select 
  ename, sal
from
  emp
where
  sal > 3000;

and表示并且，还有另一种写法：&&
案例：找出薪资大于等于3000并且小于等于5000的员工姓名、薪资。

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

or表示或者，还有另一种写法：||
案例：找出工作岗位是MANAGER和SALESMAN的员工姓名、工作岗位

select 
  ename, job
from
  emp
where
  job = 'MANAGER' or job = 'SALESMAN';

and和or的优先级问题

and和or同时出现时，and优先级较高，会先执行，如果希望or先执行，这个时候需要给or条件添加小括号。另外，以后遇到不确定的优先级时，可以通过添加小括号的方式来解决。对于优先级问题没必要记忆。
案例：找出薪资小于1500，并且部门编号是20或30的员工姓名、薪资、部门编号。
先来看一下错误写法：

1	select ename,sal,deptno from emp where sal <1500 and deptno = 20 or deptno = 30;

这里的意思是找出薪资小于1500并且编号是20的，再或者编号30的，就会输出一些大于1500的人

正确写法如下：

select
  ename,sal,deptno
from
  emp
where
  sal < 1500 and (deptno = 20 or deptno = 30);

between…and…

between…and…等同于 >= and <=
做区间判断的，包含左右两个边界值。
它支持数字、日期、字符串等数据类型。
between…and…在使用时一定是**左小右大**。左大右小时无法查询到数据。
between…and… 和 >= and <=只是在写法结构上有区别，执行原理和效率方面没有区别。
案例：找出薪资在1600到3000的员工姓名、薪资

1 2	select ename,sal from emp where sal >= 1600 and sal <= 3000; select ename,sal from emp where sal bwtween 1600 and 3000;//使用后

任务：查询在1982-01-23到1987-04-19之间入职的员工

注意：以上SQL语句中日期需要加上单引号。

is null、is not null

判断某个数据是否为null，不能使用等号，只能使用 is null
判断某个数据是否不为null，不能使用不等号，只能使用 is not null
在数据库中null不是一个值，不能用等号和不等号衡量，null代表什么也没有，没有数据，没有值

is null

案例1：找出津贴为空的员工姓名、薪资、津贴。

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

is not null

案例2：找出津贴不为空的员工姓名、薪资、津贴

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

in、not in

in

job in(‘MANAGER’,’SALESMAN’,’CLERK’) 等同于 job = ‘MANAGER’ or job = ‘SALESMAN’ or job = ‘CLERK’
sal in(1600, 3000, 5000) 等同于 sal = 1600 or sal = 3000 or sal = 5000
in后面有一个小括号，小括号当中有多个值，值和值之间采用逗号隔开
sal in(1500, 5000)，需要注意的是：这个并不是说薪资在1500到5000之间，in不代表区间，表示sal是1500的和sal是5000的
案例1：找出工作岗位是MANAGER和SALESMAN的员工姓名、薪资、工作岗位
第一种：使用or

select
  ename,sal,job
from
  emp
where
  job = 'MANAGER' or job = 'SALESMAN';

第二种：使用in

select
  ename,sal,job
from
  emp
where
  job in('MANAGER', 'SALESMAN');

任务：找出部门编号是10和20的员工编号、姓名。（要求使用两种方案）

1 2	select empno,ename from emp where deptno=10 or deptno=20; select empno,ename from emp where deptno in(10,20);

not in

job not in(‘MANAGER’,’SALESMAN’) 等同于 job <> ‘MANAGER’ and job <> ‘SALESMAN’
sal not in(1600, 5000) 等同于 sal <> 1600 and sal <> 5000
案例：找出工作岗位不是MANAGER和SALESMAN的员工姓名、工作岗位
第一种：使用and

select 
  ename,job
from
  emp
where
  job <> 'MANAGER' and job <> 'SALESMAN';

第二种：使用not in

select 
  ename,job
from
  emp
where
  job not in('MANAGER', 'SALESMAN');

in、not in 与 NULL

写这样一条SQL语句：

1	select * from emp where comm in(NULL, 300);

为什么以上执行结果只有一条记录呢？分析一下：

首先你要知道in的执行原理实际上是采用=和or的方式，也就是说，以上SQL语句实际上是：

1	select * from emp where comm = NULL or comm = 300;

其中NULL不能用等号=进行判断，所以comm = NULL结果是false，然而中间使用的是or，所以comm = NULL被忽略了。所以查询结果就以上一条数据。
通过以上的测试得知：in是自动忽略NULL的。

再写这样一条SQL语句：

1	select * from emp where comm not in(NULL, 300);

以上的执行结果奇怪了，为什么没有查到任何数据呢？我们分析一下：
首先你要知道not in的执行原理实际上是采用<>和and的方式，也就是说，以上SQL语句实际上是：

1	select * from emp where comm <> NULL and comm <> 300;

其中NULL的判断不能使用<>，所以comm <> NULL结果是false，由于后面是and，and表示并且，comm <> NULL已经是false了，所以and右边的就没必要运算了，comm <> NULL and comm <> 300的整体运算结果就是false。所以查询不到任何数据。
通过以上测试得知，not in是不会自动忽略NULL的，所以在使用not in的时候一定要提前过滤掉NULL。

模糊查询like

模糊查询又被称为模糊匹配，在实际开发中使用较多，比如：查询公司中所有姓张的，查询岗位中带有经理两个字的职位等等，这些都需要使用模糊查询。
模糊查询的语法格式如下：

1	select .. from .. where 字段 like '通配符表达式';

在模糊查询中，通配符主要包括两个：一个是%，一个是下划线_。
其中%代表任意多个字符。下划线_代表任意一个字符。

案例1：查询员工名字以’S’开始的员工姓名

1	select ename from emp where ename like 'S%';s后面有多个字符

案例2：查询员工名字以’T’结尾的员工姓名

1	select ename from emp where ename like '%T';

案例3：查询员工名字中含有’O’的员工姓名

1	select ename from emp where ename like '%O%';

案例4：查询员工名字中第二个字母是’A’的员工姓名

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

案例5：查询学员名字中含有下划线的。

1	select * from student where name like '%_%';

查询结果不是我们想要的，之所以将所有数据全部显示，因为下划线代表任意单个字符，如果想让这个下划线变成一个普通的下划线字符，就要使用转义字符了，在mysql当中转义字符是“\”，这个和java语言中的转义字符是一样的：

1	select * from student where name like '%\_%';

排序操作

排序的SQL语法：order by是通过，通过字段排序，asc升序desc降序

1	select .. from .. order by 字段 asc/desc

单一字段升序

查询员工的编号、姓名、薪资，按照薪资升序排列。

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

单一字段降序

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

默认采用升序

1	select empno,ename,sal from emp order by sal;

查询员工的编号、姓名，按照姓名升序排列。

1	select empno,ename from emp order by ename;

多个字段排序

查询员工的编号、姓名、薪资，按照薪资升序排列，如果薪资相同的，再按照姓名升序排列。

1 2	select empno,ename,sal from emp order by sal asc, ename asc; select empno,ename,sal from emp order by 3 asc, 2 asc;//可读性差，数字表示第几列的意思

逗号靠前的起主导作用，只有相同的才会执行逗号后面的排序语句

where和order by的位置

找出岗位是MANAGER和salesman的员工姓名、工作和年薪，按照薪资降序排列。

1	select ename,job,sal*12 as yearsal from emp where job in('manager','salesman') order by yearsal desc;

目前学习过的顺序：先from，再where，再select，再order by

distinct去重

去除重复记录，只是显示的时候去重，不影响数据库，在字段前添加distinct关键字。

1	select distinct job from emp;

接下来测试一下，在distinct关键字前添加其它字段是否可以？

1	select ename, distinct job from emp;

报错了，通过测试得知，distinct只能出现在所有字段的最前面。

当distinct出现后，后面多个字段一定是联合去重的，我们来做两个练习就知道了：

练习1：找出公司中所有的工作岗位

练习2：找出公司中不同部门的不同工作岗位。

数据处理函数（单行处理函数）

关于select语句，我们之前都是这样写：select 字段名 from 表名; 这里的字段名可以看做“变量”，也可以看做常量

字符串相关

转大写upper和ucase

# 查询所有员工名字，以大写形式展现
select upper(ename) as ename from emp;
# 查询所有员工姓名，以大写形式展现
select ucase(ename) as ename from emp;

1 2	# 查询员工smith的岗位、薪资（假如你不知道数据库表中的人名是大写、小写还是大小写混合） select ename, job, sal from emp where upper(ename) = 'SMITH';

转小写lower和lcase

很简单，不再赘述，直接上代码：

1
2
3

# 查询员工姓名，以小写形式展现
select lower(ename) as ename from emp;
select lcase(ename) as ename from emp;

截取字符串substr

语法：substr(‘被截取的字符串’, 起始下标, 截取长度)

有两种写法：
第一种：substr(‘被截取的字符串’, 起始下标, 截取长度)
第二种：substr(‘被截取的字符串’, 起始下标)，当第三个参数“截取长度”缺失时，截取到字符串末尾
注意：起始下标从1开始，不是从0开始。（1表示从左侧开始的第一个位置，-1表示从右侧开始的第一个位置。）

练习：找出员工名字中第二个字母是A的

1 2	select ename from emp where ename like '_A%'; select ename from emp where substr(ename, 2, 1) = 'A';

获取字符串长度length

统计字节的数量，一个汉字是两个字节

获取字符的个数char_length

统计字符的数量

字符串拼接

语法：concat(‘字符串1’, ‘字符串2’, ‘字符串3’….)
拼接的字符串数量没有限制。

注意：在mysql8之前，双竖线||也是可以完成字符串拼接的。但在mysql8之后，||只作为逻辑运算符，不能再进行字符串拼接了。

1	select 'abc' \|\| 'def' \|\| 'xyz';

mysql8之后，|| 只作为“或者”运算符，例如：找出工资高于3000或者低于900的员工姓名和薪资：

1	select ename, sal from emp where sal > 3000 \|\| sal < 900;

去除字符串前后空白trim

1	select concat(trim(' abc '), 'def');

默认是去除前后空白，也可以去除指定的前缀后缀，例如：
去除前置0

1	select trim(leading '0' from '000111000');

去除后置0

1	select trim(trailing '0' from '000111000');

前置0和后置0全部去除

1	select trim(both '0' from '000111000');

数字相关

rand()和rand(x)

rand()生成0到1的随机浮点数。

1	select rand();

rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。
也就是给一个固定的值可以永远拿到一个固定随机数

round(x)和round(x,y)四舍五入

round(x) 四舍五入，保留整数位，舍去所有小数

1	select round(3.54);#返回4

round(x,y) 四舍五入，保留y位小数

1	select round(3.55,1);#返回3.6

truncate(x, y)舍去

1	select truncate(9.999,2);#返回9.99

以上SQL表示保留两位小数，剩下的全部舍去。

ceil与floor

数字处理函数除了以上的之外，还有ceil和floor函数：

ceil函数：返回大于或等于数值x的最小整数
floor函数：返回小于或等于数值x的最大整数

空处理

null与任何数进行运算的结果都为null

ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。

看这样一个需求：查询每个员工的年薪。（年薪 = (月薪 + 津贴) * 12个月。注意：有的员工津贴comm是NULL。）

下面这样就可以把null转换成0，就可以正常显示

1	select ename,sal,((sal + ifnull(comm,0)) * 12) as yearsal from emp;

日期和时间相关函数

获取当前日期和时间

now()和sysdate()的区别：

now()：获取的是执行select语句的时刻。（select now()的这个select）
sysdate()：获取的是执行sysdate()函数的时刻。

获取当前日期

获取当前日期有三种写法，掌握任意一种即可：

curdate()
current_date()
current_date

获取当前时间

获取档期时间有三种写法，掌握其中一种即可：

curtime()
current_time()
current_time

获取单独的年、月、日、时、分、秒

select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());

注意：这些函数在使用的时候，需要传递一个日期参数给它，它可以获取到你给定的这个日期相关的年、月、日、时、分、秒的信息。
一次性提取一个给定日期的“年月日”部分，可以使用date()函数，例如：

一次性提取一个给定日期的“时分秒”部分，可以使用time()函数，例如：

date_add函数

date_add函数的作用：给指定的日期添加间隔的时间，从而得到一个新的日期。
date_add函数的语法格式：date_add(日期, interval expr 单位)，例如：

以’2023-01-03’为基准，间隔3天之后的日期：’2023-01-06’

以’2023-01-03’为基准，间隔3个月之后的日期：’2023-04-03’
详细解释一下这个函数的相关参数：

日期：一个日期类型的数据
interval：关键字，翻译为“间隔”，固定写法
expr：指定具体的间隔量，一般是一个数字。也可以为负数，如果为负数，效果和date_sub函数相同。就是减多少天的意思
单位：
- year：年
- month：月
- day：日
- hour：时
- minute：分
- second：秒
- microsecond：微秒（1秒等于1000毫秒，1毫秒等于1000微秒）
- week：周
- quarter：季度

另外，单位也可以采用复合型单位，例如：

SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND：几分几秒之后
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE：几小时几分之后
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR：几天几小时之后
YEAR_MONTH：几年几个月之后

如果单位采用复合型的话，expr该怎么写呢？假设要表示3年2月之后

date_format日期格式化函数

将日期转换成具有某种格式的日期字符串，通常用在查询操作当中。（date类型转换成char类型）
语法格式：date_format(日期, ‘日期格式’)
该函数有两个参数：

第一个参数：日期。这个参数就是即将要被格式化的日期。类型是date类型。
第二个参数：指定要格式化的格式字符串。
- %Y：四位年份
- %y：两位年份
- %m：月份（1..12）
- %d：日（1..30）
- %H：小时（0..23）
- %i：分（0..59）
- %s：秒（0..59）

例如：获取当前系统时间，让其以这个格式展示：2000-10-11 20:15:30

注意：在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，底层会自动转换成该格式的字符串：

如果是需要展示两位年份，年月日之间用/隔开：

1	select ename,sal,date_format(hiredate,'%y/%m/%d') as hiredate from emp;

str_to_date函数

该函数的作用是将char类型的日期字符串转换成日期类型date，常使用在插入和修改操作当中。（char类型转换成date类型）

我们要给表插入一条数据：姓名zhangsan，生日85年10月1日，执行以下insert语句：

错误原因：日期值不正确。意思是：birth字段需要一个日期，你给的这个字符串’10/01/1985’我识别不了。这种情况下，我们就可以使用str_to_date函数进行类型转换：

当然，如果你提供的日期字符串格式能够被mysql解析，str_to_date函数是可以省略的，底层会自动调用该函数进行类型转换：

dayofweek、dayofmonth、dayofyear函数

意思是现在的周二是一周的第三天（周日是第一天）

dayofweek：一周中的第几天（17），周日是1，周六是7。
dayofmonth：一个月中的第几天（131）
dayofyear：一年中的第几天（1~366）

last_day函数

获取给定日期所在月的最后一天的日期：

datediff函数

计算两个日期之间所差天数：

timediff函数

计算两个日期所差时间，例如日期1和日期2所差10:20:30，表示差10小时20分钟30秒。

if函数

如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”：类似三目运算符

1	SELECT IF(500<1000, "YES", "NO");

例如：如果工资高于3000，则输出1，反之则输出0

1	select ename,sal,if(sal>3000,1,0) as result from emp;

再例如：如果名字是SMITH的，工资上调10%，其他员工工资正常显示。

1	select ename,if(ename='smith', sal * 1.1, sal) as sal from emp;

再例如：工作岗位是MANAGER的工资上调10%，是SALESMAN的工资上调20%，其他岗位工资正常。

select ename,sal,job,if(job='manager', sal*1.1,if(job='salesman', sal*1.2, sal)) as newsal from emp;#如果前面的不是manager，后面的if就会执行，去看看是不是salesman，其他的正常

上面这个需求也可以使用：case.. when.. then.. when.. then.. else.. end来完成：类似于Switch

cast函数

cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型
语法：cast(值 as 数据类型)
例如：cast(‘2020-10-11’ as date)，表示将字符串’2020-10-11’转换成日期date类型。
在使用cast函数时，可用的数据类型包括：

date：日期类型
time：时间类型
datetime：日期时间类型
signed：有符号的int类型（有符号指的是正数负数）
char：定长字符串类型（varchar是可变长字符串）
decimal：浮点型

保留5位有效数字且有一个小数位，有效数字是不包含0的

加密函数

md5函数，可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的：

窗口函数

具体可以看企业真题

MySQL 8.0及以上版本中支持如下常用的窗口函数：

ROW_NUMBER()：排名函数，返回当前结果集中每个行的行号；
1
row_number() over(order by sal asc)
RANK()：排名函数，计算分组结果中的排名，相同的行排名相同且没有空缺，下一个行排名跳过空缺；
DENSE_RANK()：排名函数，计算分组结果中的排名，相同的行排名相同，排名连续，没有空缺；
NTILE()：将分组结果等分为指定的组数，计算每组的大小；

LAG()：返回分组内前一行的值；

1
2
3

lag(指定字段) ovar(排序规则)

例如：lag(sal) over(order by sal asc)

LEAD()：返回分组内后一行的值；

语法同上
FIRST_VALUE()：返回分组内第一个值；
LAST_VALUE()：返回分组内最后一个值；
AVG()、SUM()、COUNT()、MIN()、MAX()：聚合函数，可以配合OVER()进行窗口操作。

需要注意的是，MySQL的窗口

分组函数

分组函数的执行原则：先分组，然后对每一组数据执行分组函数。如果没有分组语句group by的话，整张表的数据自成一组。

前面提到的数据处理函数是单行处理函数，输入一条就输出一条。

分组函数包括五个：（不能用在where后面）

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

max

找出员工的最高薪资

1	select max(sal) from emp;

min

找出员工的最低工资

1	select min(sal) from emp;

avg

计算员工的平均薪资

1	select avg(sal) from emp;

sum

计算员工的工资和

1	select sum(sal) from emp;

计算员工的津贴之和

1	select sum(comm) from emp;

重点：所有的分组函数都是自动忽略NULL的。

count

统计员工人数

1
2
3

select count(ename) from emp;
select count(*) from emp;
select count(1) from emp;

count(*)和count(1)的效果一样，统计该组中总记录行数。
count(ename)统计的是这个ename字段中不为NULL个数总和。
count (*)记录的是所有的数一共有多少行，不会忽略null
例如：count(comm) 结果是 4，而不是14

1	select count(comm) from emp;

统计岗位数量

1	select count(distinct job) from emp;

分组函数组合使用

select count(*),max(sal),min(sal),avg(sal),sum(sal) from emp;

分组函数注意事项

分组函数不能直接使用在where子句当中

select ename,job from emp where sal > avg(sal); 这个会报错的
原因：分组的行为是在where执行之后才开始的。

分组查询

group by

按照某个字段分组，或者按照某些字段联合分组。注意：group by的执行是在where之后执行。
语法：
group by 字段
group by 字段1,字段2,字段3….
找出每个岗位的平均薪资

1	select job, avg(sal) from emp group by job;

找出每个部门最高工资

1	select deptno,max(sal) from emp group by deptno;

找出每个部门不同岗位的平均薪资

1	select deptno,job,avg(sal) from emp group by deptno,job;

当select语句中有group by的话，select后面只能跟分组函数或参加分组的字段

1	select ename,deptno,avg(sal) from emp group by deptno; // 这个SQL多了个ename，执行后会报错。

having

having写在group by的后面，如果不合group by一起用会报错，当你对分组之后的数据不满意，可以继续通过having对分组之后的数据进行过滤。
where的过滤是在分组前进行过滤。
使用原则：尽量在where中过滤，实在不行，再使用having。越早过滤效率越高。

*找出除20部分之外，其它部门的平均薪资。

1 2	select deptno,avg(sal) from emp where deptno<>20 group by deptno; // 建议 select deptno,avg(sal) from emp group by deptno having deptno <> 20; // 不建议

查询每个部门平均薪资，找出平均薪资高于2000的。

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

组内排序

案例：找出每个工作岗位的工资排名在前两名的。
substring_index函数的使用：也就是截到点出现的第几次

group_concat函数的使用：字符串拼接，分组后对每一组的empno进行拼接

1	select substring_index(group_concat(empno order by sal desc), ',', 2) from emp group by job;

MySQL行转列

MySQL行转列又叫做数据透视。什么叫做行转列？将原本横向排列的数据透视成纵向排列的数据，进而进行计算、分析、展示等操作。

使用case when+group by完成

例子可以看企业真题

总结单表的DQL语句

select …5
from …1
where …2
group by …3
having …4
order by …6
重点掌握一个完整的DQL语句执行顺序。

连接查询

什么是连接查询

从一张表中查询数据称为单表查询。
从两张或更多张表中联合查询数据称为多表查询，又叫做连接查询。
什么时候需要使用连接查询？
1. 比如这样的需求：员工表中有员工姓名，部门表中有部门名字，要求查询每个员工所在的部门名字，这个时候就需要连接查询。

连接查询的分类

根据语法出现的年代进行分类：
1. SQL92（这种语法很少用，可以不用学。）
2. SQL99（我们主要学习这种语法。）
根据连接方式的不同进行分类：
1. 内连接
  1. 等值连接
  2. 非等值连接
  3. 自连接
2. 外连接
  1. 左外连接（左连接）
  2. 右外连接（右连接）
3. 全连接

笛卡尔积现象

当两张表进行连接查询时，如果没有任何条件进行过滤，最终的查询结果条数是两张表条数的乘积。为了避免笛卡尔积现象的发生，需要添加条件进行筛选过滤。
需要注意：添加条件之后，虽然避免了笛卡尔积现象，但是匹配的次数没有减少。
为了SQL语句的可读性，为了执行效率，建议给表起别名。

例子：拿员工姓名和部门名称做连接

emp e是给emp表起个别名叫e，然后e的ename

只能拿员工名字与四个部门名挨个匹配，员工14个与部门4个不能直接去连

内连接

什么叫内连接

满足条件的记录才会出现在结果集中。

内连接之等值连接

连接时，条件为等量关系。
案例：查询每个员工所在的部门名称，要求显示员工名、部门名。

emp取名e，dept取名d，join是连接，on加条件

select
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;
如果要找工资大于2000的在后面加上where就可以
where sal > 2000;

注意：inner可以省略。

‘

内连接之非等值连接

连接时，条件是非等量关系。
案例：查询每个员工的工资等级，要求显示员工名、工资、工资等级。

select
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
#e的工资介于s的最高和最低之间的,注意小的要在前面

内连接之自连接

连接时，一张表看做两张表，自己和自己进行连接。
案例：找出每个员工的直属领导，要求显示员工名、领导名。

必须把等量关系先思考清楚：员工.领导编号 = 领导.员工编号，分成两个表，等于就把他们放在一起

1	select e.ename 员工, l.ename 领导 from emp e join emp l on e.mgr = l.empno;

外连接

什么叫外连接

内连接是满足条件的记录查询出来。也就是两张表的交集。
外连接是除了满足条件的记录查询出来，再将其中一张表的记录全部查询出来，另一张表如果没有与之匹配的记录，自动模拟出NULL与其匹配。
左外连接：

右外连接：

外连接之左外连接（左连接）

案例：查询所有部门信息，并且找出每个部门下的员工。

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
#这是内连接
#把ename和dname改成*就可以显示所有信息
select e.*,d.* from emp e join dept d on e.deptno = d.deptno;

如果要把inner join右边的全显示就是右外连接

1	select e.,d. from emp e right outer join dept d on e.deptno = d.deptno;

如果把上面的改成左外连接

1	select e.,d. from dept d left outer join emp e on e.deptno = d.deptno;

注意：outer可以省略。
任何一个左连接都可以写作右连接。

案例：找出所有员工的上级领导，要求显示员工名和领导名。

1	select e.ename 员工,l.ename 领导 from emp e left join emp l on e.mgr=l.empno;

外连接之右外连接（右连接）

看上面

全连接

什么是全连接？
MySQL不支持full join。oracle数据库支持。

多张表连接

三张表甚至更多张表如何进行表连接
案例：找出每个员工的部门，并且要求显示每个员工的薪资等级。

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;

子查询

什么是子查询

select语句中嵌套select语句就叫做子查询。
select语句可以嵌套在哪里？
1. where后面、from后面、select后面都是可以的。

1
2
3

select ..(select)..
from ..(select)..
where ..(select)..

where后面使用子查询

案例：找出高于平均薪资的员工姓名和薪资。
错误的示范：

1	select ename,sal from emp where sal > avg(sal);

错误原因：where后面不能直接使用分组函数。
可以使用子查询：

#第一步：找出平均薪资
select avg(sal) from emp;#返回了2073.214286
#第二步：找出高于平均薪资的员工
select ename,sal from emp where sal > 2073.214286;
#两个结合起来就可以了，后面的平均薪资替换上
select ename,sal from emp where sal > (select avg(sal) from emp);

from后面使用子查询

小窍门：from后面的子查询可以看做一张临时表。
案例：找出每个部门的平均工资的等级。
第一步：先找出每个部门平均工资。

1	select deptno, avg(sal) avgsal from emp group by deptno;

第二步：将以上查询结果当做临时表t，t表和salgrade表进行连接查询。条件：t.avgsal between s.losal and s.hisal

select * from salgrade;#是salgrade表

select t.*,s.grade from t join salgrade s on t.avgsal between s.losal and s.hisal;#把salgrade当做s表，然后t的avgsal去进行对比，不过这里会报错，因为没有表t，就直接把上面的代码复制进去就行

select t.*,s.grade from (select deptno, avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;

select后面使用子查询

1	select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

exists、not exists

在 MySQL 数据库中，EXISTS（存在）用于检查子查询的查询结果行数是否大于0。如果子查询的查询结果行数大于0，则 EXISTS 条件为真。（即存在查询结果则是true。）

主要应用场景：

EXISTS 可以与 SELECT、UPDATE、DELETE 一起使用，用于检查另一个查询是否返回任何行；
EXISTS 可以用于验证条件子句中的表达式是否存在；
EXISTS 常用于子查询条件过滤，例如查询有订单的用户等。

在另一个文档里给出了创建数据库的代码。

现在我们来看一个简单的案例，假设我们要查询先前有过订单的顾客，而订单信息保存在 t_order 表中，顾客信息保存在 t_customer 表中。我们可以使用以下 sql 语句：

#用in去实现
select customer_id from t_order;#是查询顾客里订单的id
select distinct custome_id from t_order;
select * from t_customer where customer_id in (select distinct customer_id from t_order);

#用exists
select * from t_customer c where exists(select * from t_order o where o.customer_id=c.customer_id);

#用not exists这里就会返回为false的，也就是不相等的结果
select * from t_customer c where not exists(select * from t_order o where o.customer_id=c.customer_id);

exists后面的子查询如果是为true，就可以去查询符合条件的

in和exists区别（可能面试）

IN 和 EXISTS 都是用于关系型数据库查询的操作符。不同之处在于：

IN 操作符是根据指定列表中的值来判断是否满足条件，而 EXISTS 操作符则是根据子查询的结果是否有返回记录集来判断。
in的前面有字段名，然后会把小括号里的值一个一个进行判断，exists前面没有字段名，根据整个结果的返回值判断
EXISTS 操作符通常比 IN 操作符更快，尤其是在子查询返回记录数很大的情况下。因为 EXISTS 只需要判断是否存在符合条件的记录，而 IN 操作符需要比对整个列表，因此执行效率相对较低。
IN 操作符可同时匹配多个值，而 EXISTS 只能匹配一组条件。

union&union all

不管是union还是union all都可以将两个查询结果集进行合并。
union会对合并之后的查询结果集进行去重操作。
union all是直接将查询结果集合并，不进行去重操作。（union all和union都可以完成的话，优先选择union all，union all因为不需要去重，所以效率高一些。）

案例：查询工作岗位是MANAGER和SALESMAN的员工。

1
2
3

select ename,sal from emp where job='MANAGER'
union all
select ename,sal from emp where job='SALESMAN';

以上案例采用or也可以完成，那or和union all有什么区别？考虑走索引优化之类的选择union all，其它选择or。

两个结果集合并时，列数量要相同：

limit

limit作用：查询第几条到第几条的记录。通常是因为表中数据量太大，需要分页显示。
limit语法格式：
1. limit 开始下标, 长度
案例：查询员工表前5条记录

1	select ename,sal from emp limit 0, 5;

如果下标是从0开始，可以简写为：

1	select ename,sal from emp limit 5;

查询工资排名在前5名的员工（limit是在order by执行之后才会执行的）

1	select ename,sal from emp order by sal desc limit 5;

通用的分页sql

假设每页显示3条记录：pageSize = 3
第1页：limit 0, 3
第2页：limit 3, 3
第3页：limit 6, 3
第pageNo页：limit (pageNo - 1)*pageSize, pageSize

pageNo就是当前页，-1获得上一页，然后乘页需要的数量

35个DQL练手题

第05章表相关

创建表

语法格式：

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

例如：创建学生表

create table t_student(
  no int,
  name varchar(10),
  gender char(1) default '男'
);

插入数据

语法格式：

1	insert into 表名(字段名1, 字段名2, 字段名3,......) values (值1,值2,值3,......);

字段名和值要一一对应。类型要一一对应，数量要一一对应。

1	insert into t_user(no,name,gender) values(1,'jack','男');

只要插入成功就会让数据多一行

字段名也可以省略，如果字段名省略就表示把所有字段名都写上去了，并且顺序和建表时的顺序相同。

1	insert into t_user values(6,'zhaoliu','男');

删除表

语法格式：

1	drop table 表名;

或者

1	drop table if exists 表名;

判断是否存在这个表，如果存在则删除。避免不存在时的报错。

MySQL数据类型

MySQL 的数据类型可以分为整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制类型等。

整数类型

tinyint：1个字节（微小整数）
smallint：2个字节（小整数）
mediumint：3个字节（中等大小的整数）
int（integer）：4个字节（普通大小整数）
bigint：8个字节（大整数）

浮点数类型

float：4个字节，单精度（最多5位小数）
double：8个字节，双精度（最多16位小数）

定点数类型

decimal：定点数类型。底层实际上采用字符串的形式存储数字。
语法：decimal(m, d)
例如：decimal(3, 2) 表示3个有效数字，2个小数。（有效数字最多65个，小数位最多30个）

#比如在创表的时候写上a decimal(3,2)
#然后插入数据
insert into xxx(a) values(10);#10要是保存就是10.00，会超出3有效数字
insert into xxx(a) values(1.123456789);#这里的小数会四舍五入1.12

日期和时间类型

year：1个字节，只存储年，格式YYYY
time：3个字节，只存储时间，格式HH:MM:SS / HHMMSS
date：3个字节，只存储年月日，格式：YYYY-MM-DD
datetime：8个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1000年公元9999年）
timestamp：4个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1980年公元2040年）或者格式为 YYYYMMDDHHMMSS（采用这种格式不需要使用单引号，当然你使用单引号也可以）

字符串类型

char

**char(m)：**m长度是0~255个字符

固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。插入的字符长度大于给定的就会报错

varchar

**varchar(m)：**m长度是0~16383个字符
长度可变的字符串。varchar 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

例如，varchar(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。varchar在值保存和检索时尾部的空格仍保留。

text

text类型：

tinytext 表示长度为 255字符的 TEXT 列。
text 表示长度为 65535字符的 TEXT 列。
mediumtext 表示长度为 16777215字符的 TEXT 列。
longtext 表示长度为 4294967295 或 4GB 字符的 TEXT 列。

enum

这个和下面的set都是输入的值只能是规定的那几个

1
2
3

create table t_data_type08(
season enum('spring','summer')#插入时就只能插其中的一个
);

enum类型：

语法：<字段名> enum(‘值1’,’值2’,…)
该字段插入值时，只能是指定的枚举值。

set

set类型：

语法：<字段名> set(‘值1’,’值2’,’值3’,…) 注意：值不可重复。
该字段插入值时，只能是指定的值。

二进制类型

BLOB类型：二进制大对象，可以存储图片、声音、视频等文件。

blob：小的，最大长度65535个字节
mediumblob：中等的，最大长度16777215个字节
longblob：大的，最大长度4GB的字节

增删改表结构DDL

创建一个学生表

create table t_student(
  no bigint,
  name varchar(255),
  age int comment '年龄'#comment是注释的意思
);

查看建表语句

1	show create table 表名;

修改表名

1	alter table 表名 rename 新表名;

新增字段

1	alter table 表名 add 字段名数据类型;

修改字段名

1	alter table 表名 change 旧字段名新字段名数据类型;

修改字段数据类型

1	alter table 表名 modify column 字段名数据类型;

删除字段

1	alter table 表名 drop 字段名;

DML语句

当我们对表中的数据进行增删改的时候，称它为DML语句。（数据操纵语言），主要包括：insert、delete、update

insert 增

语法格式：

1	insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);

表名后面的小括号当中的字段名如果省略掉，表示自动将所有字段都列出来了，并且字段的顺序和建表时的顺序一致。
一般为了可读性强，建议把字段名写上。

1	insert into 表名 values(值1,值2,值3,...);

一次可以插入多条记录：

1	insert into t_stu(no,name,age) values(1,'jack',20),(2,'lucy',30);

delete 删

语法格式：

# 将所有记录全部删除
delete from 表名;

# 删除符合条件的记录
delete from 表名 where 条件;

以上的删除属于DML的方式删除，这种删除的数据是可以通过事务回滚的方式重新恢复的，但是删除的效率较低。（这种删除是支持事务的。）
另外还有一种删除表中数据的方式，但是这种方式不支持事务，不可以回滚，删了之后数据是永远也找不回来了。这种删除叫做：表被截断。
注意：这个语句删除效率非常高，巨大的表，瞬间干掉所有数据。但不可恢复。

1 2	#这种删除不属于dml操作 truncate table 表名;

update 改

语法格式：

1	update 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3 where 条件;

如果没有更新条件的话，所有记录全部更新。

约束constraint

创建表时，可以给表的字段添加约束，可以保证数据的完整性、有效性。比如大家上网注册用户时常见的：用户名不能为空。对不起，用户名已存在。等提示信息。
约束通常包括：

非空约束：not null
检查约束：check
唯一性约束：unique
主键约束：primary key
外键约束：foreign key

非空约束

语法格式：

create table t_stu(
  no int,
  name varchar(255) not null,
  age int
);

name字段不能为空。插入数据时如果没有给name指定值，则报错。

检查约束

create table t_stu(
  no int,
  name varchar(255),
  age int,
  check(age > 18)#大于18岁的才可以插入
);

唯一性约束

语法格式：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255) unique#如果里面有重复的就不能插入了
  email varchar(255) unique not null#就是不能重复也不能为空
);

email字段设置为唯一性，唯一性的字段值是可以为NULL的。但不能重复。以上在字段后面添加的约束，叫做列级约束。

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(email)
);

使用表级约束可以为多个字段添加联合唯一。

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(name,email)#如果name和email都一样就不行，任意一个一样可以
);

创建约束时也可以给约束起名字，将来可以通过约束的名字来删除约束：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  constraint t_stu_name_email_unique unique(name,email)#constraint后面的就是约束名字
);

所有的约束都存储在一个系统表当中：table_constraints。这个系统表在这个数据库当中：information_schema

主键约束

主键：primary key，简称PK
主键约束的字段不能为NULL，并且不能重复。
任何一张表都应该有主键，没有主键的表可以视为无效表。
主键值是这行记录的身份证号，是唯一标识。在数据库表中即使两条数据一模一样，但由于主键值不同，我们也会认为是两条完全的不同的数据。
主键分类：
1. 根据字段数量分类：
  1. 单一主键（1个字段作为主键）==>建议的
  2. 复合主键（2个或2个以上的字段作为主键）
2. 根据业务分类：
  1. 自然主键（主键和任何业务都无关，只是一个单纯的自然数据）===>建议的
  2. 业务主键（主键和业务挂钩，例如：银行卡账号作为主键）
单一主键（建议使用这种方式）

create table t_student(
  id bigint primary key,
  sno varchar(255) unique,
  sname varchar(255) not null
)

复合主键（很少用，了解）

create table t_user(
  no int,
  name varchar(255),
  age int,
  primary key(no,name)
);

主键自增：既然主键值是一个自然的数字，mysql为主键值提供了一种自增机制，不需要我们程序员维护，mysql自动维护该字段

create table t_vip(
  no int primary key auto_increment,
  name varchar(255)
);

外键约束

有这样一个需求：要求设计表，能够存储学生以及学校信息。
1. 第一种方案：一张表

这种方式会导致数据冗余，浪费空间。

  2. 第二种方案：两张表：一张存储学生，一张存储学校

t_student 表

如果采用以上两张表存储数据，对于学生表来说，sno这个字段的值是不能随便填的，这个sno是学校编号，必须要求这个字段中的值来自学校表的sno。
为了达到要求，此时就必须要给t_student表的sno字段添加外键约束了。

外键约束：foreign key，简称FK。
添加了外键约束的字段中的数据必须来自其他字段，不能随便填。
假设给a字段添加了外键约束，要求student字段中的数据必须来自b字段，school字段不一定是主键，但至少要有唯一性。
外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键很少用。

建表是有顺序的，a表如果引用b表中的数据，可以把b表叫做父表(student)，把a表叫做子表。

创建表时，先创建父表，再创建子表。
插入数据时，先插入父表，在插入子表。
删除数据时，先删除子表，再删除父表。
删除表时，先删除子表，再删除父表。

#先创建父表
create table t_school(
    ->  sno int primary key,
    ->  sname varchar(255)
    -> );
#插入数据
insert into t_school values(1,'北京二中');
insert into t_school values(2,'南开大学附属中学');

#创建子表
create table t_student(
    ->  id int primary key auto_increment,
    ->  name varchar(255),
    ->  age int,
    ->  school_no int,
    ->  constraint t_student_school_no_fk foreign key(school_no) references t_school(sno)
    -> );#constraint给约束起名，foreign key是指定外键，references是引用表里唯一的东西
    
#插入多条数据（略
insert into t_student(name,age,school_no) values('jack',20,1);#如果1改成3，就会报错，因为school只有1和2

如何添加外键：

create table t_school( 
  sno int primary key, 
  sname varchar(255) 
); 
create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) 
);

级联删除

创建子表时，外键可以添加：on delete cascade，这样在删除父表数据时，子表会级联删除。谨慎使用。

#继续上面的信息，删掉约束
alter table t_student drop constraint t_student_school_no_fk;
###添加约束，和下面的一样
alert table t_student add constraint t_student_sno_fk foreign key(school_no) references t_school(sno) on delete cascade;

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete cascade 
);

###删除约束
alert table t_student drop foreign key t_student_sno_fk;
###添加约束
alert table t_student add constraint t_student_sno_fk foreign key(sno) references t_school(sno) on delete cascade;

级联更新：父改了的时候子也会改

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on update cascade 
);

#接着最上面的表，然后约束删掉再加上on update cascade
alter table t_student add constraint t_student_school_no_fk foreign key(school_no) references t_school(sno) on update cascade;
#更新数据
update t_school set sno=332 where sname='北京二中';

级联置空

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete set null 
);

把school删掉，student表的school_no会是null

第06章三范式

什么是数据库设计三范式

数据库表设计的原则。教你怎么设计数据库表有效，并且节省空间。

这些毕竟是理论，在实际开发中为了满足客户需求，有时候会拿空间或者冗余去换取速度

三范式（背）

第一范式：任何一张表都应该有主键，每个字段是原子性的不能再分
1. 以下表的设计不符合第一范式：无主键，并且联系方式可拆分。

  2. 应该这样设计：

第二范式：建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖
1. 以下表存储了学生和老师的信息

虽然符合第一范式，但是违背了第二范式，学生姓名、老师姓名都产生了部分依赖。导致数据冗余。

  2. 以下这种设计方式就是符合第二范式的：

第三范式：建立在第二范式基础上的，非主键字段不能传递依赖于主键字段
1. 以下设计方式就是违背第三范式的

以上因为产生了传递依赖，导致班级名称冗余。

  2. 以下这种方式就是符合第三范式的：

一对多怎么设计

口诀：一对多两张表，多的表加外键。

多对多怎么设计

多对多三张表，关系表添加外键。

一对一怎么设计

两种方案：

第一种：主键共享

第二种：外键唯一

最终的设计

最终以满足客户需求为原则，有的时候会拿空间换速度。

第07章视图

就是如果多处都需要使用同一条sql语句，可以把这个语句创建成视图，这样不管增删改查，只要对这个视图进行操作，其他的地方就会同步。

只能将select语句创建为视图。

创建视图

1
2
3

create view myview01 as select empno,ename,sal from emp;#as后面的是DQL语句，以视图方式创建

create or replace view myview01 as select empno,ename from emp;#前面的是创建或替换的意思，如果有myview01这个视图了就会替换成新的

视图作用
1. 如果开发中有一条非常复杂的SQL，而这个SQL在多处使用，会给开发和维护带来成本。使用视图可以降低开发和维护的成本。
2. 视图可以隐藏表的字段名。

修改视图

1	update myview01 set ename='老板' where empno=7839;

删除视图

1
2
3

delete from myview01 where empno=7839;#删除视图里的一个数据

drop view myview01;#删除视图

对视图增删改（DML：insert delete update）可以影响到原表数据。

第08章事务transaction

事务概述

事务是一个最小的工作单元。在数据库当中，事务表示一件完整的事儿。
一个业务的完成可能需要多条DML语句共同配合才能完成，例如转账业务，需要执行两条DML语句，先更新张三账户的余额，再更新李四账户的余额，为了保证转账业务不出现问题，就必须保证要么同时成功，要么同时失败，怎么保证同时成功或者同时失败呢？就需要使用事务机制。
也就是说用了事务机制之后，在同一个事务当中，多条DML语句会同时成功，或者同时失败，不会出现一部分成功，一部分失败的现象。
事务只针对DML语句有效：因为只有这三个语句是改变表中数据的。
1. insert
2. delete
3. update

事务四大特性：ACID

原子性（Atomicity）：是指事务包含的所有操作要么全部成功，要么同时失败。原子性也是最小的事务，不可再分
一致性（Consistency）：是指事务开始前，和事务完成后，数据应该是一致的。例如张三和李四的钱加起来是5000，中间不管进行过多少次的转账操作(update)，总量5000是不会变的。这就是事务的一致性。
隔离性（Isolation）：隔离性是当多个⽤户并发访问数据库时，⽐如操作同⼀张表时，数据库为每⼀个⽤户开启的事务，不能被其他事务的操作所⼲扰，多个并发事务之间要相互隔离。
持久性（Durability）：持久性是指⼀个事务⼀旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

演示MySQL事务

在sql里，是自动提交事务的，如果需要关闭，在dos命令窗口中开启MySQL事务：start transaction; 或者：begin;开启手动提交
回滚事务：rollback;
提交事务：commit;
只要执行以上的rollback或者commit，事务都会结束。commit是成功事务，硬盘上的文件内容会被彻底改变，rollback是把之前的历史记录全部清空，当做没有发生
MySQL默认情况下采用的事务机制是：自动提交。所谓自动提交就是只要执行一条DML语句则提交一次。每次提交完都要再开启一次。

事务隔离级别

隔离级别从低到高排序：读未提交 < 读提交 < 可重复读 < 串行化**
**不同隔离级别会存在不同的现象，现象按照严重性从高到低排序：脏读 > 不可重复读 > 幻读

读未提交：读到没有提交的数据
读提交：同上
可重复读：读到了一条数据，读完之后当前的事物只要不结束，这条数据就会永远不变，可以重复读，不被影响
串行化：不支持事物并发，事物a和事物b必须排队执行

查看与设置隔离级别

mysql默认的隔离级别：可重复读（REPEATABLE READ）。oracle默认是读提交

查看当前会话（窗口）的隔离级别：select @@transaction_isolation;
查看全局的隔离级别：select @@gobal.transaction_isolation;

设置事务隔离级别：

会话级：set session transaction isolation level read committed;
全局级：set global transaction isolation level read committed;

不同现象

脏读

指的是一个事务读取了另一个事务尚未提交的数据，即读取了另一个事务中的脏数据（Dirty Data）。在此情况下，如果另一个事务回滚了或者修改了这些数据，那么读取这些脏数据的事务所处理的数据就是不准确的。

不可重复读

指在一个事务内，多次读取同一个数据行，得到的结果可能是不一样的。这是由于其他事务对数据行做出了修改操作，导致数据的不一致性。

幻读

指在事务执行过程中，前后两次相同的查询条件得到的结果集不一致，可能会变多或变少。
比如a在进行查询语句，本来要查3条，结果b执行了一条插入，就变成了查四条

隔离级别

读未提交（READ UNCOMMITTED）

A事务与B事务，A事务可以读取到B事务未提交的数据。这是最低的隔离级别。几乎两个事务之间没有隔离。这种隔离级别是一种理论层面的，在实际的数据库产品中，没有从这个级别起步的。
当事务隔离级别是读未提交时，三种现象都存在：脏读，不可重复读，幻读。
我们可以开启两个DOS命令窗口，模拟两个事务，演示一下这种隔离级别。三种现象中最严重的是脏读，我们只需要演示脏读问题即可，因为存在脏读的话，就一定存在不可重复读和幻读问题。

将全局事务隔离级别设置为：READ UNCOMMITTED

1	set global transaction isolation level read uncommitted;

开启两个DOS命令窗口来模拟两个事务：A事务与B事务。

A事务	B事务
mysql> use powernode
	mysql> use powernode
mysql> start transaction;
	mysql> start transaction;
mysql> select * from a;

	mysql> insert into a values(4);
mysql> select * from a;

通过以上测试，可以看到，A事务读取到了B事务还没有提交的数据。这种现象就是脏读。

读提交（READ COMMITTED）

A事务与B事务，A事务可以读取到B事务提交之后的数据。Oracle数据库默认的就是这种隔离级别。

将数据库的全局事务隔离级别设置为读提交：READ COMMITTED

1	set global transaction isolation level read committed;

还是上面图表的内容，但是b表必须要commit提交后a表才可以读到，但a表依旧不可重复读，而且b表插入时a表可以显示

可重复读（REPEATABLE READ）

这个隔离级别是MySQL数据库默认的。
A事务和B事务，A事务开启后，读取了某一条记录，然后B事务对这条记录进行修改并提交，A事务读取到的还是修改前的数据。这种隔离级别称为可重复读。

将数据库全局隔离级别修改为可重复读：

1	set global transaction isolation level repeatable read;

但是当事务隔离级别设置为可重复读，MySQL会尽最大努力避免幻读问题，但这种隔离级别无法完全避免幻读问题。

1 2	select * from t_school;#快照读 select * from t_school for update;#当前读，会出现幻读问题

串行化（SERIALIZABLE）

种隔离级别最高，避免了所有的问题，缺点是效率低，因为这种隔离级别会导致事务排队处理，不支持并发。

设置数据库全局隔离级别为串行化：

1	set global transaction isolation level serializable;

可重复读的幻读问题

在上面讲解过程中我提到，MySQL默认的隔离级别可重复读，在很大程度上避免了幻读问题（并不能完全解决），那么它是如何解决幻读问题的呢，解决方案包括两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题。
针对当前读（select … for update 等语句，在delete、insert、update操作时会自动调用一次当前读获取最新的数据），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。
记录锁：在另一个事务中删数据会删不掉，必须a事务结束才可以
间隙锁：如果选择的范围在2-4之间，而数据只有2和4，b表想插个3就无法被插入，因为会有间隙锁给锁住

快照读是如何解决幻读的

快照类似于缓存，第一次读的时候把数据留在了缓存里，第二次读直接是读缓存里的那个数据而不是最新的，要么两次都是快照读，要么两次都是当前读，如果一次快照一次当前就会出现幻读问题

底层由 MVCC（多版本并发控制）实现，实现的方式是开始事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好的避免了幻读问题。
多版本并发的多版本是：当前数据库表中的数据的版本是1.0，每执行一次insert/delete/update这些dml操作，则版本更新一次，insert1.1,delete1.2,update1.3
undo是撤销，redo是重做

当前读是如何解决幻读的

当前读，顾名思义：每一次都读取最新的数据。当前读包括：update、delete、insert、select…for update。这个很好理解，因为增删改的时候都要基于最新的数据进行增删改。
而select…for update原理是：对查询范围内的数据进行加锁，不允许其它事务对这个范围内的数据进行增删改。也就是说这个select语句范围内的数据是不允许并发的，只能排队执行，从而避免幻读问题。
select…for update加的锁叫做：next-key lock。我们可以称其为：间隙锁 + 记录锁。间隙锁用来保证在锁定的范围内不允许insert操作。记录锁用来保证在锁定的范围内不允许delete和update操作。

假如有这样的数据：

SQL语句是这样写的：

1	select * from a where id between 2 and 4 for update;

那么id在[2-4]区间的所有记录行被锁定，不能插入3是通过间隙锁来搞定的。不能修改或删除2和4是通过记录锁来搞定的。

出现幻读的两种情况

在同一个事务处理过程中，如果前后两次都采用快照读，或者都采用当前读，则不会出现幻读问题。如果第一次使用快照读，后面使用了当前读，则会出现幻读问题。

第一种产生幻读的场景

A事务与B事务。在A事务中第一次查询使用快照读，B事务插入数据。然后在A事务中第二次查询使用当前读。则会产生幻读现象。

第二种产生幻读的场景

事务A与事务B，在事务A中第一次查询使用快照读，在事务B中插入一条数据，然后在事务A中更新（底层自动当前读）事务B插入的那条记录，最后在事务A中再次使用快照读。则会发生幻读现象。

总结可重复读的幻读问题

MySQL的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

我举例了两个发生幻读场景的例子。

第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。
要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

第09章 DBA命令

数据库管理员

新建用户

使用自带的数据库

1	use mysql;

有个系统表保存着系统用户

1	show tables like 'user';

1	select user,host from user;#里面的localhost是本地用户

创建一个用户名为java1，密码设置为123的本地用户：

1	create user 'java1'@'localhost' identified by '123';

创建一个用户名为java2，密码设置为123的外网用户：

1	create user 'java2'@'%' identified by '123';

采用以上方式新建的用户没有任何权限：系统表也只能看到以下两个

使用root用户查看系统中当前用户有哪些？

1	select user,host from mysql.user;

给用户授权

授权语法：grant [权限1，权限2…] on 库名.表名 to ‘用户名‘@’主机名/IP地址’;
（意思是：给后面的这个用户授前面的权限，但是只能作用在这个单独的表上）
给本地用户授权：grant [权限1，权限2…] on 库名.表名 to ‘用户名‘@’localhost’;
给外网用户授权：grant [权限1，权限2…] on 库名.表名 to ‘用户名‘@’%’;
所有权限：all privileges
细粒度权限：select、insert、delete、update、alter、create、drop、index(索引)、usage(登录权限)……
库名可以使用 * ，它代表所有数据库
表名可以采用 * ，它代表所有表
也可以提供具体的数据库和表，例如：powernode.emp （powernode数据库的emp表）

# 将所有库所有表的查询权限赋予本地用户java1
grant select,insert,delete,update,create on *.* to 'java1'@'localhost';
flush privileges;#要记得这步，不然不起作用

# 将powernode库中所有表的所有权限赋予本地用户java1
grant all privileges on powernode.* to 'java1'@'localhost';

授权后必须刷新权限，才能生效：flush privileges
查看某个用户拥有哪些权限？
show grants for ‘java1‘@’localhost’
show grants for ‘java2‘@’%’

with grant option：

1 2	# with grant option的作用是：java2用户也可以给其他用户授权（自己的权限）了。 grant select,insert,delete,update on . to 'java2'@'%' with grant option;

撤销用户权限

revoke 权限 on 数据库名.表名 from ‘用户‘@’IP地址’;

# 撤销本地用户java1的insert、update、delete权限
revoke insert, update, delete on powernode.* from 'java1'@'localhost'

# 撤销外网用户java2的insert权限
revoke insert on powernode.* from 'java2'@'%'

撤销权限后也需要刷新权限：flush privileges

注意：撤销权限时 “数据库名.表名” 不能随便写，要求和授权语句时的 “数据库名.表名” 一致。

修改用户的密码

具有管理用户权限的用户才能修改密码，例如root账户可以修改其他账户的密码：

# 本地用户修改密码
alter user 'java1'@'localhost' identified by '456';

# 外网用户修改密码
alter user 'java2'@'%' identified by '456';

修改密码后，也需要刷新权限才能生效：flush privileges
以上是MySQL8版本以后修改用户密码的方式。

修改用户名

rename user '原始用户名'@'localhost' to '新用户名'@'localhost';
rename user '原始用户名'@'localhost' to '新用户名'@'%';

rename user 'java1'@'localhost' to 'java11'@'localhost';
rename user 'java11'@'localhost' to 'java123'@'%';

flush privileges;

删除用户

1 2	drop user 'java123'@'localhost'; drop user 'java2'@'%';

flush privileges;

数据备份

导出数据（请在登录mysql数据库之前进行）

# 导出powernode这个数据库中所有的表
# 指令 需要导出的表 到 什么路径 用户名 密码 导出时采用的字符编码
mysqldump powernode > e:/powernode.sql -uroot -p1234 --default-character-set=utf8

# 导出powernode中emp表的数据
mysqldump powernode emp > e:/powernode.sql -uroot -p1234 --default-character-set=utf8

导入数据第一种方式：（请在登录mysql之前进行）

# 现在登录mysql状态下新建一个数据库
create database powernode;
# 在登录mysql之前执行以下命令
mysql powernode < e:/powernode.sql -uroot -p1234 --default-character-set=utf8

导入数据第二种方式：（请在登录mysql之后操作）

1
2
3

create  database powernode;
use powernode;
source d:/powernode.sql

第10章 MySQL客户端工具

注释：/* */

创建数据库的字符集一般是utf8mb4（不是必须），排序规则utf8mb4_general_ci

放大字体：按住ctrl+滚轮

第11章企业真题

看文件

第12章存储过程

什么是存储过程？

存储过程可称为过程化SQL语言，是在普通SQL语句的基础上增加了编程语言的特点，把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中，通过逻辑判断、循环等操作实现复杂计算的程序语言。
换句话说，存储过程其实就是数据库内置的一种编程语言，这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来，执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行，所以一个存储过程也可以看做是一组为了完成特定功能的SQL 语句集。
每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库当中，一次编译永久有效。并且每一个存储过程都有自己的名字。客户端程序通过存储过程的名字来调用存储过程。
在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

存储过程的优点和缺点？

优点：速度快。

1	缺点：移植性差。编写难度大。维护性差。

缺点：移植性差。编写难度大。维护性差。

1
2

- 每一个数据库都有自己的存储过程的语法规则，这种语法规则不是通用的。一旦使用了存储过程，则数据库产品很难更换，例如：编写了mysql的存储过程，这段代码只能在mysql中运行，无法在oracle数据库中运行。
- 对于数据库存储过程这种语法来说，没有专业的IDE工具（集成开发环境），所以编码速度较低。自然维护的成本也会较高。

第一个存储过程

存储过程的创建

create procedure p1()
begin
	select empno,ename from emp;
end;

存储过程的调用

1	call p1();

如果使用dos可能会出现分号就执行的情况，这时候delimiter命令可以把执行语句给更改

存储过程的查看

/*
查看建表过程的语句
*/
show create table emp;

/*
查看创建存储过程的语句
*/
show create procedure p1;

通过系统表information_schema.ROUTINES查看存储过程的详细信息：

在mysql当中，只要创建一个存储过程对象，在information_schema.ROUTINES系统表当中就会增加一条记录，这条记录是专门描述该存储过程对象状态的
information_schema.ROUTINES 是 MySQL 数据库中一个系统表，存储了所有存储过程、函数、触发器的详细信息，包括名称、返回值类型、参数、创建时间、修改时间等。

information_schema.ROUTINES 表中的一些重要的列包括：

SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表。
ROUTINE_SCHEMA：存储过程所在的数据库名称。
ROUTINE_NAME：存储过程的名称。
ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。
ROUTINE_DEFINITION：存储过程的定义语句。
CREATED：存储过程的创建时间。
LAST_ALTERED：存储过程的最后修改时间。
DATA_TYPE：存储过程的返回值类型、参数类型等。

#查询表结构
desc information_schema.ROUTINES;

#然后查系统表的结构
select SPECIFIC_NAME from information_schema.ROUTINES where ROUTINE_NAME='p1';

存储过程的删除

1	drop procedure if exists p1;

delimiter命令

在 MySQL 中，delimiter 命令用于改变 MySQL 解释语句的定界符。MySQL 默认使用分号 ; 作为语句的定界符。而使用 delimiter 命令可以将分号 ; 更改为其他字符，从而可以在 SQL 语句中使用分号 ;。

1 2	delimiter // 这样就可以把;执行改为//执行

MySQL的变量

mysql中的变量包括：系统变量、用户变量、局部变量。

系统变量

MySQL 系统变量是指在 MySQL 服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，以满足不同的需求。比如想把dml的自动提交给改成手动提交，就可以用系统变量。
MySQL 系统变量可以具有全局（global）或会话（session）作用域。

全局作用域是指对所有连接和所有数据库都适用；
会话作用域是指只对当前连接和当前数据库适用。

查看系统变量：variables是变量的意思

show [global|session] variables;

show [global|session] variables like '';模糊查找

select @@[global|session.]系统变量名;

注意：没有指定session或global时，默认是session。

/*
查看所有的系统变量
*/
show variables;
show global variables;

/*
隐约知道某个系统变量的名字，但是不知道具体的全名，可以使用like进行模糊查找
*/
show variables like '%char%';

/*
当知道具体的系统变量名的时候，也可以使用以下方式来查看系统变量的值
*/
select @@global.autocommit;
select @@session.autocommit;
select @@autocommit;默认是session会话级
/*
在这里，当前会话的自动提交机制还没有关闭，是支持自动提交的
*/
insert into dept(deptno,dname,loc) values (50,'销售部','北京');

/*
将当前会话的自动提交机制关闭
*/
set session autocommit = 0;
set autocommit = 0;

/*
在这里，当前会话的自动提交机制关闭了
*/
insert into dept(deptno,dname,loc) values(60,'研发部','上海');
commit;

/*修改全局作用域的自动提交机制，将其关闭*/
set global autocommit = 0;

设置系统变量

1
2
3

set [global | session] 系统变量名 = 值;

set @@[global | session.]系统变量名 = 值;一般不用这个

注意：无论是全局设置还是会话设置，当mysql服务（就是彻底停止）重启之后，之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久修改的效果。（my.ini是MySQL数据库默认的系统级配置文件，默认是不存在的，需要新建，并参考一些资料进行配置。）
windows系统是my.ini
linux系统是my.cnf
my.ini文件通常放在mysql安装的根目录下，如下图：

这个文件通常是不存在的，可以新建，新建后例如提供以下配置：

1 2	[mysqld] autocommit=0

这种配置就表示永久性关闭自动提交机制。（不建议这样做。）

用户变量

用户自定义的变量。只在当前会话有效。所有的用户变量‘@’开始。

给用户变量赋值

set @name = 'jackson';
set @age := 30;
set @gender := '男', @addr := '北京大兴区';
select @email := 'jackson@123.com';
select sal into @sal from emp where ename ='SMITH';
#这个就是把查询名字为史密斯的工资赋值给@sal

读取用户变量的值

1	select @name, @age, @gender, @addr, @email, @sal;

注意：mysql中变量不需要声明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null

局部变量

在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。

变量的声明

1	declare 变量名数据类型 [default ...];#默认值可以不加

变量的数据类型就是表字段的数据类型，例如：int、bigint、char、varchar、date、time、datetime等。
注意：declare通常出现在begin end之间的开始部分。

变量的赋值

1
2
3

set 变量名 = 值;
set 变量名 := 值;不加冒号也可以
select 字段名 into 变量名 from 表名 ...;

例如：以下程序演示局部变量的声明、赋值、读取：

drop procedure if exists p3();#如果存在p3则删除

create PROCEDURE p2()
begin 
	/*声明变量*/
	declare emp_count int default 0;
	/*声明变量*/
	declare sal double(10,2) default 0.0;
	/*给变量赋值*/
	select count(*) into emp_count from emp;
	/*给变量赋值*/
	set sal := 5000.0;#不加冒号也可以
	/*读取变量的值*/
	select emp_count;
	/*读取变量的值*/
	select sal;
end;

1
2
3

call p2();
select emp_count,sal;#会报错，因为这个变量只是在存储过程里的局部变量，在外面无法访问
select @emp_count,@sal;#这个可以访问，但访问的是用户变量，没有赋值的为null

if语句

语法格式：和java比，elseif中间会有个空格

if 条件 then
......
elseif 条件 then
......
elseif 条件 then
......
else
......
end if;

案例：员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”。

drop procedure if exists p4;
create procedure p4()
begin

	/* 声明一个局部变量，用来存储月薪 */
	declare sal int default 0;

	/* 声明一个局部变量，用来存储等级 */
	declare grade varchar(20);

	/* 给局部变量sal赋值 */
	set sal = 7000;
	if sal>10000 then set grade = '高收入';
	elseif sal >6000 then set grade ='中收入';
	else set grade='低收入';
	end if;
	select grade;
end;
/* 调用存储过程 */
call p4();

参数

存储过程的参数包括三种形式：

in：入参（未指定时，默认是in），用来接收调用者传过来的数据
out：出参，用来保存整个存储过程的执行结果
inout：既是入参，又是出参

public class Test{
    //定义方法
   public static int sum(int a,int b){//这个a和b就是入参，被传的参数
       int c = a+b ;
       return c;//c就是出参
   }
}

案例：员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”。

drop procedure if exists p5;

create procedure p5(in sal int,out grade varchar(20))
BEGIN
	if sal>10000 then set grade = '高收入';
	elseif sal >6000 then set grade ='中收入';
	else set grade='低收入';
	end if;
END;

在这里怎么获取到这个存储过程的执行结果呢？在存储过程外面只能访问到用户变量，而上面的grade是局部变量，就需要在call里写一个用于接收out出来的变量，怎么起名字都可以

1 2	call p5(7000,@salgrade); select @salgrade;

case语句

语法格式：

case 值
	when 值1 then
	......
	when 值2 then
	......
	when 值3 then
	......
	else
	......
end case;

case#不加值的话when后边就是个布尔语句
	when 条件1 then
	......
	when 条件2 then
	......
	when 条件3 then
	......
	else
	......
end case;

案例：根据不同月份，输出不同的季节。3 4 5月份春季。6 7 8月份夏季。9 10 11月份秋季。12 1 2 冬季。其他非法。

drop procedure if exists p8;

create procedure p8(in month int, out season varchar(100))
begin 
	case month
		when 3 then set season := '春季';
		when 4 then set season := '春季';
		when 5 then set season := '春季';
		when 6 then set season := '夏季';
		when 7 then set season := '夏季';
		when 8 then set season := '夏季';
		when 9 then set season := '秋季';
		when 10 then set season := '秋季';
		when 11 then set season := '秋季';
		when 12 then set season := '冬季';
		when 1 then set season := '冬季';
		when 2 then set season := '冬季';
		else set season := '非法月份';
	end case;
end;

/* case的第二周语法格式*/
create procedure p8(in month int, out season varchar(100))
begin 
	case 
		when month = 3 or month = 4 or month = 5 then 
			set season := '春季';
		when  month = 6 or month = 7 or month = 8  then 
			set season := '夏季';
		when  month = 9 or month = 10 or month = 11  then 
			set season := '秋季';
		when  month = 12 or month = 1 or month = 2  then 
			set season := '冬季';
		else 
			set season := '非法月份';
	end case;
end;

1 2	call p8(9, @season); select @season;

while循环

语法格式：

1
2
3

while 条件 do
	循环体;
end while;

案例：传入一个数字n，计算1~n中所有偶数的和。

create procedure p9(in n int,out sum int)
begin
	ser sum = 0;j 
	while n > 0 do#确定不为0
  		if n % 2 = 0 then#嵌套if语句
    		set sum := sum + n;
  		end if;
  		set n := n - 1;
	end while;#这里结束循环
	select sum;
end;

1	call mypro(10);

repeat循环

语法格式：

repeat
	循环体;
	until 条件
end repeat;

注意：和while的区别是repeat条件成立时结束循环。

案例：传入一个数字n，计算1~n中所有偶数的和。

create procedure p10(in n int, out sum int)
begin 
	set sum := 0;
	repeat 
		if n % 2 = 0 then 
		  set sum := sum + n;
		end if;
		set n := n - 1;
		until n <= 0    #结束条件
	end repeat;
end;

1 2	call p10(10, @sum); select @sum;

loop循环

语法格式：

create procedure mypro()
begin 
	declare i int default 0;
  mylp:loop #开始，mylp是起的名字
		set i := i + 1;
		if i = 5 then 
			leave mylp;#这是结束循环体,相当于break，不加就是死循环
		end if;
		select i;
	end loop; #结束
end;

create procedure mypro()
begin 
	declare i int default 0;
  mylp:loop 
		set i := i + 1;
		if i = 5 then 
			iterate mylp;#相当于java的continue，结束本次循环，执行下一次循环
		end if;
		if i = 10 then 
		  leave mylp;
		end if;
		select i;
	end loop;
end;

案例：输出1-5，然后又添加新的需求：输出1234 6789

drop procedure if exists p11;

create procedure p11()
begin
	declare i int default 0;
	
	myloop:loop
		set i = i +1;#这里就无限增长
		/*if i = 6 then leave myloop;是1-5*/
		if i = 5 then
		iterate myloop;#i为5就跳过这次循环到下一次
		end if;
		
		if i = 10
		leave myloop;#为10就停止
		end if;
		
		select i;
	end loop;
end;
call p11;

游标cursor

游标（cursor）可以理解为一个指向结果集中某条记录的指针，允许程序逐一访问结果集中的每条记录，并对其进行逐行操作和处理。

声明游标的语法：

1	declare 游标名称 cursor for 查询语句;

打开游标的语法：

1	open 游标名称;

通过游标取数据的语法：

1	fetch 游标名称 into 变量[,变量,变量......]

关闭游标的语法：

1	close 游标名称;

案例：从dept表查询部门编号和部门名，创建一张新表dept2，将查询结果插入到新表中。

drop procedure if exists p12;

create procedure p12()
begin 
	/*声明变量*/
	declare dept_no int;
	declare dept_name varchar(255);
	/*声明游标*/
	declare dept_cursor cursor for select deptno,dname from dept;

	/*创建新表*/
	drop table if exists dept2;
	create table dept2(
		deptno int,
		dname varchar(255)
	);
	
	/*打开游标*/
	open dept_cursor;

	/*通过游标取数据*/
	/*fetch dept_cursor into dept_no,dept_name;*/
	
	/*查看以下变量的值*/
	/*select dept_no,dept_name;*/
	
	/*使用循环从游标中取数据*/
	while true do
		fetch dept_cursor into dept_no,dept_name;
		/*插入到新表*/
		insert into dept2(deptno,name) values(dept_no,dept_name);
	end while;
	
	/*关闭游标*/
	close dept_cursor;
end;

call mypro();

捕捉异常并处理

语法格式：这里要填三个空，DECLARE (1) HANDLER FOR (2)(3)

1	DECLARE handler_name HANDLER FOR condition_value action_statement

handler_name 表示异常处理程序的名称，重要取值包括：
1. CONTINUE：发生异常后，程序不会终止，会正常执行后续的过程。(捕捉)
2. EXIT：发生异常后，终止存储过程的执行。（上抛）
condition_value 是指捕获的异常，重要取值包括：
1. SQLSTATE sqlstate_value，可以自定义，例如：SQLSTATE ‘02000’
2. SQLWARNING，代表所有01开头的SQLSTATE
3. NOT FOUND，代表所有02开头的SQLSTATE
4. SQLEXCEPTION，代表除了01和02开头的所有SQLSTATE
action_statement 是指异常发生时执行的语句，例如：CLOSE cursor_name

给之前的游标添加异常处理机制：

/*存储过程中的异常处理机制。异常的捕捉和处理
	实现的功能：以上的p12存储过程中有异常发生了，我们要处理一下，如果发生的异常是notfound，则关闭游标，并且退出存储过程*/
drop procedure if exists p12;

create procedure p12()
begin 
	declare dept_no int;
	declare dept_name varchar(255);
	declare dept_cursor cursor for select deptno,dname from dept;

	/*通常在这个位置进行异常的处理*/
	declare exit handler for not found close dept_cursor;
	drop table if exists dept2;
	create table dept2(
		deptno int,
		dname varchar(255)
	);
	
	open dept_cursor;

	while true do
		fetch dept_cursor into dept_no,dept_name;
		insert into dept2(deptno,name) values(dept_no,dept_name);
	end while;
	close dept_cursor;
end;

call mypro();

存储函数

存储函数：带返回值的存储过程。参数只允许是in（但不能写显示的写in）。没有out，也没有inout。
语法格式：

CREATE FUNCTION 存储函数名称(参数列表) RETURNS 数据类型 [特征]
BEGIN
	--函数体
	RETURN ...;
END;

“特征”的可取重要值如下：

deterministic：用该特征标记该函数为确定性函数（什么是确定性函数？每次调用函数时传同一个参数的时候，返回值都是固定的，比如传100返回120，第二次如果为123就不是确定性的）。这是一种优化策略，这种情况下整个函数体的执行就会省略了，直接返回之前缓存的结果，来提高函数的执行效率。
no sql：用该特征标记该函数执行过程中不会查询数据库，如果确实没有查询语句建议使用。告诉 MySQL 优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数，这样就可以避免不必要的查询消耗产生，从而提高性能。
reads sql data：用该特征标记该函数会进行查询操作，告诉 MySQL 优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时 MySQL 还会针对该函数的查询进行优化器缓存处理。

案例：计算1~n的所有偶数之和

/*
存储函数
案例：计算1-n的所有偶数之和
*/
drop function if exists sum_fun;

create function sum_fun(n int) returns int DETERMINISTIC
begin 
	declare result int default 0;
	while n>0 do
		if n%2 = 0 then 
			set result = result +n;
		end if;
		set n = n - 1;
	end while;
	return result;
end;

/*调用存储函数*/
set @result = sum_fun(100);
select @result;

触发器

MySQL 触发器是一种数据库对象，它是与表相关联的特殊程序。它可以在特定的数据操作（例如插入（INSERT）、更新（UPDATE）或删除（DELETE））触发时自动执行。MySQL 触发器使数据库开发人员能够在数据的不同状态之间维护一致性和完整性，并且可以为特定的数据库表自动执行操作。

触发器的作用主要有以下几个方面：

强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。
数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。
执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。

MySQL 触发器分为两种类型: BEFORE 和 AFTER。BEFORE 触发器在执行 INSERT、UPDATE、DELETE 语句之前执行，而 AFTER 触发器在执行 INSERT、UPDATE、DELETE 语句之后执行。

创建触发器的语法如下：FOR EACH ROW是解释一下

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW
BEGIN
-- 触发器执行的 SQL 语句
END;

其中：

trigger_name：触发器的名称
BEFORE/AFTER：触发器的类型，可以是 BEFORE 或者 AFTER
INSERT/UPDATE/DELETE：触发器所监控的 DML 调用类型
table_name：触发器所绑定的表名
FOR EACH ROW：表示触发器在每行受到 DML 的影响之后都会执行
触发器执行的 SQL 语句：该语句会在触发器被触发时执行

需要注意的是，触发器是一种高级的数据库功能，只有在必要的情况下才应该使用，例如在需要实施强制性业务规则时。过多的触发器和复杂的触发器逻辑可能会影响查询性能和扩展性。

关于触发器的NEW和OLD关键字：
在 MySQL 触发器中，NEW 和 OLD 是两个特殊的关键字，用于引用在触发器中受到修改的行的新值和旧值。具体而言：

NEW：在触发 INSERT 或 UPDATE 操作期间，NEW 用于引用将要插入或更新到表中的新行的值。
OLD：在触发 UPDATE 或 DELETE 操作期间，OLD 用于引用更新或删除之前在表中的旧行的值。

通俗的讲，NEW 是指触发器执行的操作所要插入或更新到当前行中的新数据；而 OLD 则是指当前行在触发器执行前原本的数据。

案例：当我们对dept表中的数据进行insert delete update的时候，请将这些操作记录到日志表当中，日志表如下：

drop table if exists oper_log;

create table oper_log(
  id bigint primary key auto_increment,
  table_name varchar(100) not null comment '操作的哪张表',
  oper_type varchar(100) not null comment '操作类型包括insert delete update',
  oper_time datetime not null comment '操作时间',
  oper_id bigint not null comment '操作的那行记录的id',
  oper_desc text comment '操作描述'
);

触发器需求：当向dept表当中insert插入数据之后（after），在oper_log表中记录日志

drop trigger if exists trigger_dept_insert;

create trigger trigger_dept_insert
/* 触发规则 插入一次触发一次*/
after insert on dept for each row 
begin
	/*一但触发之后要执行的sql语句
		id是自增的，后面的concat连接字符串*/
	insert into oper_log(id, table_name,oper_type,oper_time,oper_id,oper_desc) values 
	(null,'dept','insert',now(),new.deptno,concat('插入数据:deptno=',new.deptno,'dname=',new.dname,',loc=',new.loc));
end;

触发器需求：更新dept表之后，在oper_log中记录日志

drop trigger if exists trigger_dept_update;

create trigger trigger_dept_update after update on dept for each row 
begin
	insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values 
	(null,'dept','update',now(),new.deptno,
	concat('更新数据，更新前:deptno=',old.deptno,',dname=',old.dname,'loc=',old.loc,',更新后：deptno=',new.deptno,'dname=',new.dname,',loc=',new.loc));
end;

第13章存储引擎

存储引擎概述

MySQL存储引擎决定了数据在磁盘上的存储方式和访问方式。不同的存储引擎实现了不同的存储和检索算法，因此它们在处理和管理数据的方式上存在差异。

MySQL常见的存储引擎包括InnoDB、MyISAM、Memory、Archive等。每个存储引擎都有自己的特点和适用场景。

例如，

InnoDB引擎支持事务和行级锁定，适用于需要高并发读写的应用；
MyISAM引擎不支持事务，但适用于读操作较多的应用；
Memory引擎数据全部存储在内存中，适用于对读写速度要求很高的应用等等。

选择适合的存储引擎可以提高MySQL的性能和效率，并且根据应用需求来合理选择存储引擎可以提供更好的数据管理和查询功能。

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

Support是Yes的表示支持该存储引擎。当前MySQL的版本是8.0.33
MySQL默认的存储引擎是：InnoDB

指定和修改存储引擎

指定存储引擎

在MySQL中，你可以在创建表时指定使用的存储引擎。通过在CREATE TABLE语句中使用ENGINE关键字，你可以指定要使用的存储引擎。

以下是指定存储引擎的示例：

1	CREATE TABLE my_table (column1 INT, column2 VARCHAR(50)) ENGINE = InnoDB;

如果你不显式指定存储引擎，MySQL将使用默认的存储引擎。默认情况下，MySQL 8的默认存储引擎是InnoDB。

修改存储引擎

在MySQL中，你可以通过ALTER TABLE语句修改表的存储引擎。下面是修改存储引擎的示例：

1	ALTER TABLE my_table ENGINE = MyISAM;

在这个例子中，我们使用ALTER TABLE语句将my_table表的存储引擎修改为MyISAM。

请注意，在修改存储引擎之前，你需要考虑以下几点：

修改存储引擎可能需要执行复制表的操作，因此可能会造成数据的丢失或不可用。确保在执行修改之前备份你的数据。
不是所有的存储引擎都支持相同的功能。要确保你选择的新存储引擎支持你应用程序所需的功能。
修改表的存储引擎可能会影响到现有的应用程序和查询。确保在修改之前评估和测试所有的影响。
ALTER TABLE语句可能需要适当的权限才能执行。确保你拥有足够的权限来执行修改存储引擎的操作。

总而言之，修改存储引擎需要谨慎进行，且需要考虑到可能的影响和风险。建议在进行修改之前进行适当的测试和备份。

常用的存储引擎及适用场景

在实际开发中，以下存储引擎是比较常用的：

InnoDB：
1. MySQL默认的事务型存储引擎
2. 支持ACID事务
3. 具有较好的并发性能和数据完整性
4. 支持行级锁定。
5. 适用于大多数应用场景，尤其是需要事务支持的应用。
MyISAM：
1. 是MySQL早期版本中常用的存储引擎
2. 支持全文索引和表级锁定
3. 不支持事务
4. 由于其简单性和高性能，在某些特定的应用场景中会得到广泛应用，如读密集的应用。
MEMORY：
1. 称为HEAP，是将表存储在内存中的存储引擎
2. 具有非常高的读写性能，但数据会在服务器重启时丢失。
3. 适用于需要快速读写的临时数据集、缓存和临时表等场景。
CSV：
1. 将数据以纯文本格式存储的存储引擎
2. 适用于需要处理和导入/导出CSV格式数据的场景。
ARCHIVE：
1. 将数据高效地进行压缩和存储的存储引擎
2. 适用于需要长期存储大量历史数据且不经常查询的场景。

第14章索引

什么是索引（index）

索引是一种能够提高检索（查询）效率的提前排好序的数据结构。例如：书的目录就是一种索引机制。索引是解决SQL慢查询的一种方式。

索引的创建和删除

主键会自动添加索引

主键字段会自动添加索引，不需要程序员干涉，主键字段上的索引被称为主键索引

unique约束的字段自动添加索引

unique约束的字段也会自动添加索引，不需要程序员干涉，这种字段上添加的索引称为唯一索引

给指定的字段添加索引

建表时添加索引：

CREATE TABLE emp (
    ...
    name varchar(255),
    ...
    INDEX idx_name (name)
);

如果表已经创建好了，后期给字段添加索引

1	alter table emp ADD INDEX idx_name (name);

也可以这样添加索引：

1	create index idx_name on emp(name);

删除指定字段上的索引

1	ALTER TABLE emp DROP INDEX idx_name;

查看某张表上添加了哪些索引

1	show index from 表名;

索引的分类

不同的存储引擎有不同的索引类型和实现：

按照数据结构分类：
- B+树索引（mysql的InnoDB存储引擎采用的就是这种索引）采用 B+树的数据结构
- Hash 索引（仅 memory 存储引擎支持）：采用哈希表的数据结构
按照物理存储分类：
- 聚集索引：索引和表中数据在一起，数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引。
- 非聚集索引：索引和表中数据是分开的，索引是独立于表空间的，一张表可以有多个非聚集索引。
按照字段特性分类：
- 主键索引（primary key）
- 唯一索引（unique）
- 普通索引（index）
- 全文索引（fulltext：仅 InnoDB和MyISAM 存储引擎支持）
按照字段个数分类：
- 单列索引、联合索引（也叫复合索引、组合索引）

MySQL索引采用了B+树数据结构

常见的树相关的数据结构包括：

二叉树
红黑树
B树
B+树

区别：树的高度不同。树的高度越低，性能越高。这是因为每一个节点都是一次I/O

二叉树

如果不给id字段添加索引，默认进行全表扫描，那至少要进行10次磁盘IO。可以给id字段添加索引，假设该索引使用了二叉树这种数据结构，这个二叉树是这样的

如果这个时候要找id=10的数据，需要的IO次数是？4次。效率显著提升了。

但是MySQL并没有直接使用这种普通的二叉树，这种普通二叉树在数据极端的情况下，效率较低。比如下面的数据

如果给id字段添加索引，并且该索引底层使用了普通二叉树，这棵树会是这样的：

你虽然使用了二叉树，但这更像一个链表。查找效率等同于链表查询O(n)【查找算法的时间复杂度是线性的】。查找效率极低。
因此对于MySQL来说，它并没有选择这种数据结构作为索引。

红黑树（自平衡二叉树）

通过自旋平衡规则进行旋转，子节点会自动分叉为2个分支，从而减少树的高度，当数据有序插入时比二叉树数据检索性能更好。

给id字段添加索引，并且该索引使用了红黑树数据结构，那么会是这样：

如果查找id=10的数据，磁盘IO次数为：5次。效率比普通二叉树要高一些。

但是如果数据量庞大，例如500万条数据，也会导致树的高度很高，磁盘IO次数仍然很多，查询效率也会比较低。

因此MySQL并没有使用红黑树这种数据结构作为索引。

B Trees（B树）

B Trees首先是一个自平衡的。
B Trees每个节点下的子节点数量 > 2。
B Trees每个节点中也不是存储单个数据，可以存储多个数据。
B Trees又称为平衡多路查找树。

B Trees分支的数量不是2，是大于2，具体是多少个分支，由阶决定。例如：

3阶的B Trees，一个节点下最多有3个子节点，每个节点中最多有2个数据。
4阶的B Trees，一个节点下最多有4个子节点，每个节点中最多有3个数据。
5阶（5, 4）
6阶（6, 5）
….
16阶（16, 15）【MySQL采用了16阶】

采用B Trees，你会发现相同的数据量，B Tree 树的高度更低。磁盘IO次数更少。

更加详细的存储是这样的，请看下图

在B Trees中，每个节点不仅存储了索引值，还存储该索引值对应的数据行。
并且每个节点中的p1 p2 p3是指向下一个节点的指针。

B Trees数据结构存在的缺点是：不适合做区间查找，对于区间查找效率较低。假设要查id在[3~7]之间的，需要查找的是3,4,5,6,7。那么查这每个索引值都需要从头(根）节点开始向下找。
因此MySQL使用了B+ Trees解决了这个问题。

B+ Trees（B+ 树）

B+ Trees 相较于 B Trees改进了哪些？

B+树将数据都存储在叶子节点中。并且叶子节点之间使用链表连接，这样很适合范围查询。
B+树的非叶子节点上只有索引值，没有数据，所以非叶子节点可以存储更多的索引值，这样让B+树更矮更胖，提高检索效率。

**经典面试题：**mysql为什么选择B+树作为索引的数据结构，而不是B树？

非叶子节点上可以存储更多的键值，阶数可以更大，更矮更胖，磁盘IO次数少，数据查询效率高。
所有数据都是有序存储在叶子节点上，让范围查找，分组查找效率更高。
数据页之间、数据记录之间采用链表链接，让升序降序更加方便操作。

**经典面试题：**如果一张表没有主键索引，那还会创建B+树吗？
当一张表没有主键索引时，默认会使用一个隐藏的内置的聚集索引（clustered index）。这个聚集索引是基于表的物理存储顺序构建的，通常是使用B+树来实现的。

（老杜说如果没有主键索引之类的，会有一个看不见的row_id列，这个采用的也是b+树

其他索引及相关调优

Hash索引

支持Hash索引的存储引擎有：

InnoDB（不支持手动创建Hash索引，系统会自动维护一个自适应的Hash索引）
- 对于InnoDB来说，即使手动指定了某字段采用Hash索引，最终show index from 表名的时候，还是BTREE。
Memory（支持Hash索引）

Hash索引底层的数据结构就是哈希表。一个数组，数组中每个元素是链表。和java中HashMap一样。哈希表中每个元素都是key value结构。key存储索引值，value存储行指针。

原理如下：

如果name字段上添加了Hash索引idx_name

Hash索引长这个样子：

Hash索引优缺点：

优点：只能用在等值比较中，效率很高。例如：name=’孙悟空’
缺点：不支持排序，不支持范围查找。

聚集索引和非聚集索引

按照数据的物理存储方式不同，可以将索引分为聚集索引（聚簇索引）和非聚集索引（非聚簇索引）。

存储引擎是InnoDB的，主键上的索引属于聚集索引。
存储引擎是MyISAM的，任意字段上的索引都是非聚集索引。

InnoDB的物理存储方式：当创建一张表t_user，并使用InnoDB存储引擎时，会在硬盘上生成这样一个文件：

t_user.ibd （InnoDB data表索引 + 数据）
t_user.frm （存储表结构信息）

MyISAM的物理存储方式：当创建一张表t_user，并使用MyISAM存储引擎时，会在硬盘上生成这样一个文件：

t_user.MYD （表数据）
t_user.MYI （表索引）
t_user.frm （表结构）

注意：从MySQL8.0开始，不再生成frm文件了，引入了数据字典，用数据字典来统一存储表结构信息，例如：

information_schema.TABLES （表包含了数据库中所有表的信息，例如表名、数据库名、引擎类型等）
information_schema.COLUMNS（表包含了数据库中所有表的列信息，例如列名、数据类型、默认值等）

聚集索引的原理图：（B+树，叶子节点上存储了索引值 + 数据）

非聚集索引的原理图：（B+树，叶子节点上存储了索引值 + 行指针）

聚集索引的优点和缺点：

优点：聚集索引将数据存储在索引树的叶子节点上。可以减少一次查询，因为查询索引树的同时可以获取数据。
缺点：对数据进行修改或删除时需要更新索引树，会增加系统的开销。

二级索引

二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。
有表t_user，id是主键。age是非主键。在age字段上添加的索引称为二级索引。（所有非主键索引都是二级索引）

二级索引的数据结构：

二级索引的查询原理：
假设查询语句为：

1	select * from t_user where age = 30;

为什么会“回表”？因为使用了select *，查到了age=30后会得到主键=2，然后底层执行查主键=2的数据再返回。
避免“回表【回到原数据表】”是提高SQL执行效率的手段。例如：select id from t_user where age = 30; 这样的SQL语句是不需要回表的。

覆盖索引

覆盖索引（Covering Index），顾名思义，是指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。
当使用覆盖索引时，MySQL可以直接通过索引，也就是索引上的数据来获取所需的结果，而不必再去查找表中的数据。这样可以显著提高查询性能。

假设有一个用户表（user）包含以下列：id, username, email, age。

常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能，可以创建一个覆盖索引，包含（username, email）这两列。

创建覆盖索引的SQL语句可以如下：

1	CREATE INDEX idx_user_username_email ON user (username, email);

当执行以下查询时：

1	SELECT email FROM user WHERE username = 'lucy';

MySQL可以直接使用覆盖索引（idx_user_username_email）来获取查询结果，而不必再去查找用户表中的数据。这样可以减少磁盘I/O并提高查询效率。而如果没有覆盖索引，MySQL会先使用索引（username）来找到匹配的行，然后再回表查询获取邮箱，这个过程会增加更多的磁盘I/O和查询时间。

索引下推

索引下推（Index Condition Pushdown）是一种 MySQL 中的优化方法，它可以将查询中的过滤条件下推到索引层级中处理，从而减少回表次数，优化查询性能。

具体来说，在使用索引下推时，MySQL 会在索引的叶节点层级执行查询的过滤条件，过滤掉无用的索引记录，仅返回符合条件的记录的主键，这样就可以避免查询时回表读取表格的数据行，从而缩短了整个查询过程的时间。

假设有以下表结构：

表名：users

id	name	age	city
1	John	25	New York
2	Alice	30	London
3	Bob	40	Paris
4	Olivia	35	Berlin
5	Michael	28	Sydney

现在我们创建了一个多列索引：（索引下推通常是基于多列索引的。）

1	ALTER TABLE users ADD INDEX idx_name_city_age (name, city, age);

假设我们要查询年龄大于30岁，并且所在城市是”London”的用户，假设只给age字段添加了索引，它就不会使用索引下推。传统的查询优化器会将所有满足年龄大于30岁的记录读入内存，然后再根据城市进行筛选。

使用索引下推优化后，在索引范围扫描的过程中，优化器会判断只有在城市列为”London”的情况下，才会将满足年龄大于30岁的记录加载到内存中。这样就可以避免不必要的IO和数据传输，提高查询性能。

具体的查询语句可以是：

1	SELECT * FROM users WHERE age > 30 AND city = 'London';

在执行这个查询时，优化器会使用索引下推技术，先根据索引范围扫描找到所有满足条件的记录，然后再回到原数据表中获取完整的行数据，最终返回结果。

单列索引（单一索引）

单列索引是指对数据库表中的某一列或属性进行索引创建，对该列进行快速查找和排序操作。单列索引可以加快查询速度，提高数据库的性能。

举个例子，假设我们有一个学生表（student），其中有以下几个列：学生编号（student_id）、姓名（name）、年龄（age）和性别（gender）。

如果我们针对学生表的学生编号（student_id）列创建了单列索引，那么可以快速地根据学生编号进行查询或排序操作。例如，我们可以使用以下SQL语句查询学生编号为123456的学生信息：

1	SELECT * FROM student WHERE student_id = 123456;

由于我们对学生编号列建立了单列索引，所以数据库可以直接通过索引快速定位到具有学生编号123456的那一行记录，从而加快查询速度。

复合索引（组合索引）

复合索引（Compound Index）也称为多列索引（Multi-Column Index），是指对数据库表中多个列进行索引创建。

与单列索引不同，复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键，以提高多列条件查询的效率。

举个例子，假设我们有一个订单表（Order），其中包含以下几个列：订单编号（OrderID）、客户编号（CustomerID）、订单日期（OrderDate）和订单金额（OrderAmount）。

如果我们为订单表的客户编号和订单日期这两列创建复合索引（CustomerID, OrderDate），那么可以在查询时同时根据客户编号和订单日期来快速定位到匹配的记录。

例如，我们可以使用以下SQL语句查询客户编号为123456且订单日期为2021-01-01的订单信息：

1	SELECT * FROM Order WHERE CustomerID = 123456 AND OrderDate = '2021-01-01';

由于我们为客户编号和订单日期创建了复合索引，数据库可以使用这个索引来快速定位到符合条件的记录，从而加快查询速度。复合索引的使用能够提高多列条件查询的效率，但需要注意的是，复合索引的创建和维护可能会增加索引的存储空间和对于写操作的影响。

相对于单列索引，复合索引有以下几个优势：

减少索引的数量：复合索引可以包含多个列，因此可以减少索引的数量，减少索引的存储空间和维护成本。
提高查询性能：当查询条件中涉及到复合索引的多个列时，数据库可以使用复合索引进行快速定位和过滤，从而提高查询性能。
覆盖查询：如果复合索引包含了所有查询需要的列，那么数据库可以直接使用索引中的数据，而不需要再进行表的读取，从而提高查询性能。
排序和分组：由于复合索引包含多个列，因此可以用于排序和分组操作，从而提高排序和分组的性能。

索引的优缺点

索引是数据库中一种重要的数据结构，用于加速数据的检索和查询操作。它的优点和缺点如下：

优点：

提高查询性能：通过创建索引，可以大大减少数据库查询的数据量，从而提升查询的速度。
加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率。
减少磁盘IO：索引可以减少磁盘IO的次数，这对于磁盘读写速度较低的场景，尤其重要。

缺点：

占据额外的存储空间：索引需要占据额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。
增删改操作的性能损耗：每次对数据表进行插入、更新、删除等操作时，需要更新索引，会导致操作的性能降低。
资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。

何时用索引

在以下情况下建议使用索引：

频繁执行查询操作的字段：如果这些字段经常被查询，使用索引可以提高查询的性能，减少查询的时间。
大表：当表的数据量较大时，使用索引可以快速定位到所需的数据，提高查询效率。
需要排序或者分组的字段：在对字段进行排序或者分组操作时，索引可以减少排序或者分组的时间。
外键关联的字段：在进行表之间的关联查询时，使用索引可以加快关联查询的速度。

在以下情况下不建议使用索引：

频繁执行更新操作的表：如果表经常被更新数据，使用索引可能会降低更新操作的性能，因为每次更新都需要维护索引。
小表：对于数据量较小的表，使用索引可能并不会带来明显的性能提升，反而会占用额外的存储空间。
对于唯一性很差的字段（重复字段），一般不建议添加索引。当一个字段的唯一性很差时，查询操作基本上需要扫描整个表的大部分数据。如果为这样的字段创建索引，索引的大小可能会比数据本身还大，导致索引的存储空间占用过高，同时也会导致查询操作的性能下降。

总之，索引需要根据具体情况进行使用和权衡，需要考虑到表的大小、查询频率、更新频率以及业务需求等因素。

第15章 mysql优化

MySQL优化手段

MySQL数据库的优化手段通常包括但不限于：

SQL查询优化：这是最低成本的优化手段，通过优化查询语句、适当添加索引等方式进行。并且效果显著。
库表结构优化：通过规范化设计、优化索引和数据类型等方式进行库表结构优化，需要对数据库结构进行调整和改进
系统配置优化：根据硬件和操作系统的特点，调整最大连接数、内存管理、IO调度等参数
硬件优化：升级硬盘、增加内存容量、升级处理器等硬件方面的投入，需要购买和替换硬件设备，成本较高

我们主要掌握：SQL查询优化

SQL性能分析工具

查看数据库整体情况

通过以下命令可以查看当前数据库在SQL语句执行方面的整体情况：status状态

show global status like 'Com_select';//会显示查询的次数，以此类推
show global status like 'Com_insert';
show global status like 'Com_delete';
show global status like 'Com_update';

show global status like 'Com_______';//7个

这些结果反映了从 MySQL 服务器启动到当前时刻，所有的 SELECT 查询总数。对于 MySQL 性能优化来说，通过查看 Com_select 的值可以了解 SELECT 查询在整个 MySQL 服务期间所占比例的情况：

如果 Com_select 次数过高，可能说明查询表中的每条记录都会返回过多的字段。
如果 Com_select 次数很少，同时insert或delete或update的次数很高，可能说明服务器运行的应用程序过于依赖写入操作和少量读取操作。

总之，通过查看 Com_select 的值，可以了解 MySQL 服务器的长期执行情况，并在优化查询性能时，帮助我们了解 MySQL 的性能瓶颈。

慢查询日志

慢查询日志文件可以将查询较慢的DQL语句记录下来（比如select超过三秒），便于我们定位需要调优的select语句。
通过以下命令查看慢查询日志功能是否开启：

/*
慢查询日志
1. 默认情况下，慢查询日志功能是关闭的。查看慢查询日志功能是否开启
*/
show global variables like 'slow_query_log';

慢查询日志功能默认是关闭的。请修改my.ini文件来开启慢查询日志功能，在my.ini的[mysqld]后面添加如下配置：

注意：slow_query_log=1表示开启慢查询日志功能，long_query_time=3表示：只要SELECT语句的执行耗时超过3秒则将其记录到慢查询日志中。
重启mysql服务。再次查看是否开启慢查询日志功能：

尝试执行一条时长超过3秒的select语句：

1	select empno,ename,sleep(4) from emp where ename='smith';

慢查询日志文件默认存储在：C:\dev\mysql-8.0.36-winx64\data 目录下，默认的名字是：计算机名-slow.log
通过该文件可以清晰的看到哪些DQL语句属于慢查询：

show profiles

通过show profiles可以查看一个SQL语句在执行过程中具体的耗时情况。帮助我们更好的定位问题所在。

查看当前数据库是否支持 profile操作：

1	select @@have_profiling;

查看 profiling 开关是否打开：

1	set profiling = 1;

可以执行多条DQL语句，然后使用 show profiles; 来查看当前数据库中执行过的每个SELECT语句的耗时情况。

select empno,ename from emp;
select empno,ename from emp where empno=7369;
select count(*) from emp;
show profiles;

查看某个SQL语句语句在执行过程中，每个阶段的耗时情况：

1	show profile for query 4;

想查看执行过程中cpu的情况，可以执行以下命令：

1	show profile cpu for query 4;

explain

explain命令可以查看一个DQL语句的执行计划，根据执行计划可以做出相应的优化措施。提高执行效率。

1	explain select * from emp where empno=7369;

id

id反映出一条select语句执行顺序，id越大优先级越高。id相同则按照自上而下的顺序执行。

1	explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;

由于id相同，反映出三张表在执行顺序上属于平等关系，执行时采用，先d，再e，最后s。

1	explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal=(select sal from emp where ename='ford');

反映出，先执行子查询，然后让e和d做表连接。

select_type

反映了mysql查询语句的类型。常用值包括：

SIMPLE：表示查询中不包含子查询或UNION操作。这种查询通常包括一个表或是最多一个联接（JOIN）
PRIMARY：表示当前查询是一个主查询。（主要的查询）
UNION：表示查询中包含UNION操作
SUBQUERY：子查询
DERIVED：派生表（表示查询语句出现在from后面）

/*
找出每个部门平均工资的工资等级
*/
explain select s.grade,t.* from (select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
//from后面的这个查询语句就是派生表

table

反映了这个查询操作的是哪个表。

type

反映了查询表中数据时的访问类型，常见的值：

NULL：效率最高，一般不可能优化到这个级别，只有查询时没有查询表的时候，访问类型是NULL。例如：select 1;
system：通常访问系统表的时候，访问类型是system。一般也很难优化到这个程序。
const：根据主键或者唯一性索引查询，索引值是常量值时。explain select * from emp where empno=7369;
eq_ref：根据主键或者唯一性索引查询。索引值不是常量值。
ref：使用了非唯一的索引进行查询。
range：使用了索引，扫描了索引树的一部分。
index：表示用了索引，但是也需要遍历整个索引树。
all：全表扫描

效率最高的是NULL，效率最低的是all，从上到下，从高到低。

优化就是从下往上优化

possible_keys

这个查询可能会用到的索引

key

实际用到的索引

key_len

反映索引中在查询中使用的列所占的总字节数。

rows

查询扫描的预估计行数。

Extra

给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好地理解查询执行的过程。

索引优化

加索引 vs 不加索引

将这个sql脚本初始化到数据库中（初始化100W条记录）：t_vip.sql
根据id查询（id是主键，有索引）：

1	select * from t_vip where id = 900000;#0s

根据name查询（name上没有索引）：

1	select * from t_vip where name='4c6494cb';#0.157s

给name字段添加索引：

1	create index idx_t_user_name on t_vip(name);

再次根据name查询（此时name上已经有索引了）：

1	select * from t_vip where name='4c6494cb';# 0s

最左前缀原则

假设有这样一张表：

create table t_customer(
	id int primary key auto_increment,
	name varchar(255),
	age int,
	gender char(1),
	email varchar(255)
);

添加了这些数据：

insert into t_customer values(null, 'zhangsan', 20, 'M', 'zhangsan@123.com');
insert into t_customer values(null, 'lisi', 22, 'M', 'lisi@123.com');
insert into t_customer values(null, 'wangwu', 18, 'F', 'wangwu@123.com');
insert into t_customer values(null, 'zhaoliu', 22, 'F', 'zhaoliu@123.com');
insert into t_customer values(null, 'jack', 30, 'M', 'jack@123.com');

添加了这样的复合索引：

1	create index idx_name_age_gender on t_customer(name,age,gender);

最左前缀原则：当查询语句条件中包含了这个复合索引最左边的列 name 时，此时索引才会起作用。

explain select * from t_customer where name='zhangsan' and age=20 and gender='M';/* 完全使用了索引，索引长度1033个字节 */

explain select * from t_customer where name='zhangsan' and age=20; /* 部分使用了索引，索引长度1028 ，说明gender在索引当中占用5个字节的长度 */

explain select * from t_customer where name='zhangsan'; /* 部分使用了索引，索引长度1023，说明age在索引当中占用5个字节的长度 */

explain select * from t_customer where age=20 and gender='M' and name='zhangsan'; /* 完全使用了索引，索引长度1033个字节，这里顺序不一样也不影响*/

explain select * from t_customer where gender='M' and age=20; /* 因为条件不符合最左前缀原则，没有name条件出现，所以不会使用索引，进行全表扫描 */

explain select * from t_customer where name='zhangsan' and gender='M'; /* 部分使用了索引，只有name字段使用索引，因为中间断开了，导致gender没有使用索引，就会有性能折损*/

explain select * from t_customer where name='zhangsan' and gender='M' and age=20; /* 完全使用了索引 */

explain select * from t_customer where name='zhangsan' and age>20 and gender='M'; /* 当使用范围查找的时候，范围条件右侧的列不会使用索引。 */

explain select * from t_customer where name='zhangsan' and age>=20 and gender='M'; /* 建议范围条件中添加等号，这样可以让索引完全生效 */

范围查询时，在“范围条件”右侧的列索引会失效：

验证：

1	explain select * from t_customer where name='zhangsan' and age>20 and gender='M';

验证结果：name和age列索引生效。gender列索引无效。

怎么解决？建议范围查找时带上“=”

1	explain select * from t_customer where name='zhangsan' and age>=20 and gender='M';

索引失效情况

索引列参加了运算，索引失效

1	explain select * from t_emp where sal > 5000;#可以

1	explain select * from t_emp where sal*10 > 50000;#不行

索引列进行模糊查询时以 % 开始的，索引失效

1	explain select * from t_emp where name like '张%';#可以

1	explain select * from t_emp where name like '%飞';#不行

索引列是字符串类型，但查询时省略了单引号，索引失效

1	explain select * from t_emp where age='20';#可以

1	explain select * from t_emp where age=20;#不行

查询条件中有or，只要有未添加索引的字段，索引失效

1	explain select * from t_emp where name='张三' or sal=5000;#可以

将t_emp表sal字段上的索引删除：

1	alter table t_emp drop index idx_t_emp_sal;

再次验证：

1	explain select * from t_emp where name='张三' or sal=5000;

验证结果：索引失效

当查询的符合条件的记录在表中占比较大，索引失效

就是符合条件的记录占了比如百分之八十，和全表扫描没什么区别了，不用是有可能比全表扫描效率还低，会可能有个回表操作

复制一张新表：emp2

1	create table emp2 as select * from emp;

给sal添加索引：

1	alter table emp2 add index idx_emp2_sal(sal);

验证：

1
2
3

explain select * from emp2 where sal > 800;#不走索引
explain select * from emp2 where sal > 1000;#不走索引
explain select * from emp2 where sal > 2000;#走索引

关于is null和is not null的索引失效问题

其实就是null在表里占得比例大就全表扫描，占得比例小就使用索引

给emp2的comm字段添加一个索引：

1	create index idx_emp2_comm on emp2(comm);

将emp2表的comm字段值全部更新为NULL：

1	update emp2 set comm=null;

验证此时条件使用is null是否走索引：

1	explain select * from emp2 where comm is null;#不走索引

将emp2表的comm字段全部更新为非NULL：

1 2	update emp2 set comm=100; explain select * from emp2 where comm is null;#走索引

指定索引

当一个字段上既有单列索引，又有复合索引时，我们可以通过以下的SQL提示来要求该SQL语句执行时采用哪个索引：

use index(索引名称)：建议使用该索引，只是建议，底层mysql会根据实际效率来考虑是否使用你推荐的索引。
ignore index(索引名称)：忽略该索引
force index(索引名称)：强行使用该索引

查看 t_customer 表上的索引：

1	show index from t_customer;

可以看到name age gender三列添加了一个复合索引。
现在给name字段添加一个单列索引：

1	create index idx_name on t_customer(name);

看看以下的语句默认使用了哪个索引：

1	explain select * from t_customer where name='zhangsan';

通过测试得知，默认使用了联合索引。

如何建议使用单列索引idx_name：

1	explain select * from t_customer use index(idx_name) where name='zhangsan';

如何忽略使用符合索引 idx_name_age_gender：

1	explain select * from t_customer ignore index(idx_name_age_gender) where name='zhangsan';

如何强行使用单列索引idx_name：

1	explain select * from t_customer force index(idx_name) where name='zhangsan';

覆盖索引

覆盖索引我们在讲解索引的时候已经提到过了，覆盖索引强调的是：在select后面写字段的时候，这些字段尽可能是索引所覆盖的字段，这样可以避免回表查询。尽可能避免使用 select *，因为select * 很容易导致回表查询。（本质就是：能在索引上检索的，就不要再次回表查询了。）

例如：有一张表 emp3，其中 ename,job添加了联合索引：idx_emp3_ename_job，以下这个select语句就不会回表：

drop table if exists emp3;#删除emp3
create table emp3 as select * from emp;#从emp创建emp3
alter table emp3 add constraint emp3_pk primary key(empno);#为emp3的empno添加主键
create index idx_emp3_ename_job on emp3(ename,job);#添加联合索引
explain select empno,ename,job from emp3 where ename='KING';#符合最左原则，都被索引覆盖，不会回表
explain select empno,ename,job,sal from emp3 where ename='KING';#sal不在索引中，会回表

面试题：t_user表字段如下：id,name,password,realname,birth,email。表中数据量500万条，请针对以下SQL语句给出优化方案：

1	select id,name,realname from t_user where name='鲁智深';

这里会想到给name添加索引，如果只给name添加索引，realname并不在索引覆盖范围内，底层会进行大量的回表查询，效率较低，建议给name和realname两个字段添加联合索引，这样大大减少回表操作，提高查询效率。

前缀索引

如果一个字段类型是varchar或text字段，字段中存储的是文本或者大文本，直接对这种长文本创建索引，会让索引体积很大，怎么优化呢？可以将字符串的前几个字符截取下来当做索引来创建。这种索引被称为前缀索引，例如：

1
2
3

drop table if exists emp4;
create table emp4 as select * from emp;
create index idx_emp4_ename_2 on emp4(ename(2));#把ename的前两个字符作为索引

**知识储备：**对于索引来说，当索引值越唯一，唯一性就越好，性能越高

使用前缀索引时，需要通过以下公式来确定使用前几个字符作为索引：

1	select count(distinct substring(ename,1,前几个字符)) / count(*) from emp4;#把ename从1开始到几结束，除行数

以上查询结果越接近1，表示索引的效果越好。（原理：做索引值的话，索引值越具有唯一性效率越高），ename，前几个字符和emp4是可以更改的

单列索引和复合索引怎么选择

当查询语句的条件中有多个条件，建议将这几个列创建为复合索引，因为创建单列索引很容易回表查询。
例如分别给emp5表ename，job添加两个单列索引：

create table emp5 as select * from emp;
alter table emp5 add constraint emp5_pk primary key(empno);

create index idx_emp5_ename on emp5(ename);
create index idx_emp5_job on emp5(job);

执行以下查询语句：

explain select empno,ename,job from emp5 where ename='SMITH' and job='CLERK';#这里推荐使用两个，但实际只使用了ename索引，如果extra为useing index才是使用索引，where是回表

ename和job都出现在查询条件中，可以给emp6表的ename和job创建一个复合索引：

create table emp6 as select * from emp;
alter table emp6 add constraint emp6_pk primary key(empno);

create index idx_emp6_ename_job on emp6(ename,job);
explain select empno,ename,job from emp6 where ename='SMITH' and job='CLERK';

对于以上查询语句，使用复合索引避免了回表，因此这种情况下还是建议使用复合索引。

注意：创建索引时应考虑最左前缀原则，主字段并且具有很强唯一性的字段建议排在第一位

由于ename是主字段，并且ename具有很好的唯一性，建议将ename列放在最左边。因此这两种创建复合索引的方式

索引创建原则

表数据量庞大，通常超过百万条数据。
经常出现在where，order by，group by后面的字段建议添加索引。
创建索引的字段尽量具有很强的唯一性。
如果字段存储文本，内容较大，一定要创建前缀索引。
尽量使用复合索引，使用单列索引容易回表查询。
如果一个字段中的数据不会为NULL，建议建表时添加not null约束，这样优化器就知道使用哪个索引列更加有效。
不要创建太多索引，当对数据进行增删改的时候，索引需要重新重新排序。
如果很少的查询，经常的增删改不建议加索引。

SQL优化

order by的优化

explain查看一个带有order by的语句时，Extra列会显示：using index 或者 using filesort，区别是什么？

using index: 表示使用索引，因为索引是提前排好序的。效率很高。
using filesort：表示使用文件排序，这就表示没有走索引，对表中数据进行排序，排序时将硬盘的数据读取到内存当中，在内存当中排好序。这个效率是低的，应避免。

准备数据：

drop table if exists workers;

create table workers(
  id int primary key auto_increment,
  name varchar(255),
  age int,
  sal int
);

insert into workers values(null, '孙悟空', 500, 50000);
insert into workers values(null, '猪八戒', 300, 40000);
insert into workers values(null, '沙和尚', 600, 40000);
insert into workers values(null, '白骨精', 600, 10000);

此时name没有添加索引，如果根据name进行排序的话这种方式效率较低：

给name添加索引再根据name排序：

1 2	create index idx_workers_name on workers(name); explain select id,name from workers order by name;

这样效率则提升了。

如果要通过age和sal两个字段进行排序，最好给age和sal两个字段添加复合索引，不添加复合索引时：
按照age升序排，如果age相同则按照sal升序

1	explain select id,age,sal from workers order by age,sal;

这样效率是低的。
给age和sal添加复合索引再按照age升序排，如果age相同则按照sal升序：

1 2	create index idx_workers_age_sal on workers(age, sal); explain select id,age,sal from workers order by age,sal;

这样效率提升了

在B+树上叶子结点上的所有数据默认是按照升序排列的，如果按照age降序，如果age相同则按照sal降序，也会走索引，因为B+树叶子结点之间采用的是双向指针。可以从左向右（升序），也可以从右向左（降序），效率一样高

如果一个升序，一个降序会怎样呢？

1	explain select id,age,sal from workers order by age asc, sal desc;

可见age使用了索引，但是sal没有使用索引。怎么办呢？可以针对这种排序情况创建对应的索引来解决：

1	create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);

创建的索引如下：A表示升序，D表示降序

再次执行：

通过测试得知，order by也遵循最左前缀法则。

order by 优化原则总结：

排序也要遵循最左前缀法则。
使用覆盖索引。
针对不同的排序规则，创建不同索引。（如果所有字段都是升序，或者所有字段都是降序，则不需要创建新的索引）
如果无法避免filesort，要注意排序缓存的大小，默认缓存大小256KB，可以修改系统变量 sort_buffer_size ：

1	show variables like 'sort_buffer_size';

group by优化

创建empx表，job字段上没有索引，根据job进行分组，查看每个工作岗位有多少人：

1	select job,count(*) from empx group by job;

使用了临时表，效率较低。

给job添加索引：

1 2	create index idx_empx_job on empx(job); explain select job,count(*) from empx group by job;

效率提升了。group by也同样遵循最左前缀法则。

我们再来测试一下，如果将部门编号deptno（复合索引的最左列）添加到where条件中，效率会不会提升：

1	explain select sal, count(*) from empx where deptno=10 group by sal;

效率有提升的，这说明了，group by确实也遵循最左前缀法则。（where中使用了最左列）

limit优化

数据量特别庞大时，取数据时，越往后效率越低，怎么提升？mysql官方给出的解决方案是：使用覆盖索引+子查询的形式来提升效率。这是分别从一百万五百万九百万中取两条数据的速度

怎么解决？使用覆盖索引，加子查询
使用覆盖索引：速度有所提升

然后把这个表看做表t，通过id和进行表连接，使用子查询形式取其他列的数据

通过测试，这种方式整体效率有所提升。

主键优化

主键设计原则：

主键值不要太长，二级索引叶子结点上存储的是主键值，主键值太长，容易导致索引占用空间较大。
尽量使用auto_increment生成主键。尽量不要使用uuid做主键，因为uuid不是顺序插入。
最好不要使用业务主键，因为业务的变化会导致主键值的频繁修改，主键值不建议修改，因为主键值修改，聚集索引一定会重新排序。
在插入数据时，主键值最好是顺序插入，不要乱序插入，因为乱序插入可能会导致B+树叶子结点频繁的进行页分裂与页合并操作，效率较低。
1. 主键值对应聚集索引，插入主键值如果是乱序的，B+树叶子结点需要不断的重新排序，重排过程中还会频繁涉及到页分裂和页合并（可以看老杜的视频有讲解）的操作，效率较低。
2. B+树上的每个节点都存储在页（page）中。一个页面中存储一个节点。
3. MySQL的InnoDB存储引擎一个页可以存储16KB的数据。
4. 如果主键值不是顺序插入的话，会导致频繁的页分裂和页合并。在一个B+树中，页分裂和页合并是树的自动调整机制的一部分。当一个页已经满了，再插入一个新的关键字时就会触发页分裂操作，将页中的关键字分配到两个新的页中，同时调整树的结构。相反，当一个页中的关键字数量下降到一个阈值以下时，就会触发页合并操作，将两个相邻的页合并成一个新的页。如果主键值是随机的、不是顺序插入的，那么页的利用率会降低，页分裂和页合并的次数就会增加。由于页的分裂和合并是比较耗时的操作，频繁的分裂和合并会降低数据库系统的性能。因此，为了优化B+树的性能，可以将主键值设计成顺序插入的，这样可以减少页的分裂和合并的次数，提高B+树的性能。在实际应用中，如果对主键值的顺序性能要求不是特别高，也可以采用一些技术手段来减少页分裂和合并，例如B+树分裂时采用“延迟分裂”技术，或者通过调整页的大小和节点的大小等方式来优化B+树的性能。

insert优化

insert优化原则：

批量插入：数据量较大时，不要一条一条插入，可以批量插入，当然，建议一次插入数据不超过1000条

1	insert into t_user(id,name,age) values (1,'jack',20),(2,'lucy',30),(3,'timi',22);

mysql默认是自动提交事务，只要执行一条DML语句就自动提交一次，因此，当插入大量数据时，建议手动开启事务和手动提交事务。不建议使用数据库事务自动提交机制。
主键值建议采用顺序插入，顺序插入比乱序插入效率高。
超大数据量插入可以考虑使用mysql提供的load指令，load指令可以将csv文件中的数据批量导入到数据库表当中，并且效率很高，过程如下：
- 第一步：登录mysql时指定参数

1	mysql --local-infile -uroot -p1234

第二步：开启local_infile功能

1	set global local_infile = 1;

第三步：执行load指令

use powernode;

create table t_temp(
  id int primary key,
  name varchar(255),
  password varchar(255),
  birth char(10),
  email varchar(255)
);

load data local infile 'E:\\powernode\\05-MySQL高级\\resources\\t_temp-100W.csv' into table t_temp fields terminated by ',' lines terminated by '\n';

count(*)优化

分组函数count的使用方式：

count(主键)
- 原理：将每个主键值取出，累加
count(常量值)
- 原理：获取到每个常量值，累加
count(字段)
- 原理：取出字段的每个值，判断是否为NULL，不为NULL则累加。
count(*)
- 原理：不用取值，底层mysql做了优化，直接统计总行数，效率最高。

结论：如果你要统计一张表中数据的总行数，建议使用 count(*)

注意：

对于InnoDB存储引擎来说，count计数的实现原理就是将表中每一条记录取出，然后累加。如果你想真正提高效率，可以自己使用额外的其他程序来实现，例如每向表中插入一条记录时，在redis数据库中维护一个总行数，这样获取总行数的时候，直接从redis中获取即可，这样效率是最高的。
对于MyISAM存储引擎来说，当一个select语句没有where条件时，获取总行数效率是极高的，不需要统计，因为MyISAM存储引擎维护了一个单独的总行数。

update优化

当存储引擎是InnoDB时，表的行级锁是针对索引添加的锁，如果索引失效了，或者不是索引列时，会提升为表级锁。
什么是行级锁？就是两个表要修改同一行的数据时，a表没有结束之前b表的事务会被卡主，当a表提交结束后b表才会继续执行。

当然，如果更新的不是同一行数据，事务A和事务B可以并发：

行级锁是对索引列加锁，以上更新语句的where条件是id，id是主键，当然有索引，所以使用了行级锁，如果索引失效，或者字段上没有索引，则会升级为表级锁，表级锁就是更改同一张表的比如下面

因此，为了更新的效率，建议update语句中where条件中的字段是添加索引的。

文章作者: Shelter

文章链接: http://localhost:4000/posts/14ea6df7.html

Java sql

数据库概述

什么是数据库

数据库类型

数据库管理系统

什么是SQL

登录MySQL

本地登录

远程登录

第03章 初始化数据

MySQL命令行基本命令

数据库表的概述

初始化测试数据

熟悉测试数据

第04章 查询DQL专题

简单查询

查一个字段

查多个字段

查所有字段

查询时字段可参与数学运算

查询时字段可起别名

条件查询

条件查询语法格式

等于 =

安全等于（简单了解）

不等于 <> 或 !=

大于 >、大于等于 >=、小于 <、小于等于 <=、and、or

and和or的优先级问题

between…and…

is null、is not null

is null

is not null

in、not in

in

not in

in、not in 与 NULL

模糊查询like

排序操作

单一字段升序

单一字段降序

默认采用升序

多个字段排序

where和order by的位置

distinct去重

数据处理函数（单行处理函数）

字符串相关

转大写upper和ucase

转小写lower和lcase

截取字符串substr

获取字符串长度length

获取字符的个数char_length

字符串拼接

去除字符串前后空白trim

数字相关

rand()和rand(x)

round(x)和round(x,y)四舍五入

truncate(x, y)舍去

ceil与floor

空处理

日期和时间相关函数

获取当前日期和时间

获取当前日期

获取当前时间

获取单独的年、月、日、时、分、秒

date_add函数

date_format日期格式化函数

str_to_date函数

dayofweek、dayofmonth、dayofyear函数

last_day函数

datediff函数

timediff函数

if函数

cast函数

加密函数

窗口函数

分组函数

max

min

avg

sum

计算员工的津贴之和

第03章初始化数据

第04章查询DQL专题

第05章表相关

第06章三范式