MySQL命令行基本命令

  1. 列出当前数据库管理系统中有哪些数据库。
1
show databases;

image.png

  1. 创建数据库，起名bjpowernode。
1
create database bjpowernode;

image.png

  1. 使用bjpowernode数据库。
1
use bjpowernode;

image.png

  1. 查看当前用的是哪个数据库。
1
select database();

image.png

  1. 查看当前数据库中有哪些表。
1
show tables;

image.png
image.png

  1. 删除数据库bjpowernode。
1
drop database bjpowernode;

image.png

  1. 退出mysql
    1. exit
    2. quit
    3. ctrl + c
  2. 查看当前mysql版本
1
select version();

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

数据库表的概述

name age gender
张三 20 男
李四 22 女
  • 以上就是数据库表格的直观展示形式。
  • 表是数据库存储数据的基本单元，数据库存储数据的时候，是将数据存储在表对象当中的。为什么将数据存储在表中呢？因为表存储数据非常直观。
  • 任何一张表都有行和列：
    • 行：记录（一行就是一条数据）
    • 列：字段（name字段、age字段、gender字段）
  • 每个字段包含以下属性：
    • 字段名：name、age、gender都是字段的名字
    • 字段的数据类型：每个字段都有数据类型，比如：字符类型、数字类型、日期类型
    • 字段的数据长度：每个字段有可能会有长度的限制
    • 字段的约束：比如某些字段要求该字段下的数据不能重复、不能为空等，用来保证表格中数据合法有效

初始化数据

sql脚本：文件名是.sql，并且该文件中编写了大量的SQL语句，执行sql脚本程序就相当于批量执行SQL语句。

  • 执行SQL脚本文件，初始化数据库
    • 第一步：命令窗口登录mysql
    • 第二步：创建数据库bjpowernode（如果之前已经创建就不需要再创建了）：create database bjpowernode;
    • 第三步：使用数据库bjpowernode：use bjpowernode;
    • 第四步：source命令执行sql脚本，注意：source命令后面是sql脚本文件的绝对路径。

  image.png

  • 第五步：查看是否初始化成功，执行：show tables;

  image.png

  • 使用其他的mysql客户端工具也可以执行sql脚本，比如navicat。使用source命令执行sql脚本的优点：可支持大文件。

查询DQL专题

简单查询

查一个字段

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

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

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

1
select empno from emp;

image.png
案例2：查询公司中所有员工姓名

1
SELECT ENAME FROM EMP;

image.png
在mysql命令行客户端中，sql语句没有分号是不会执行的：
image.png
末尾加上“;”就执行了：
image.png
以上sql虽然以分号结尾之后执行了，但是报错了，错误信息显示：语法错误。
假设一个SQL语句在书写过程中出错了，怎么终止这条SQL呢？\c
image.png

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

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

1
select ename, sal from emp;

image.png

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

1
select ename, sal * 12 from emp;

image.png

查询时字段可起别名

我们借用一下之前的SQL语句

1
select ename, sal * 12 from emp;

image.png
以上的查询结果列名“sal * 12”可读性较差，是否可以给查询结果的列名进行重命名呢？

as关键字

  • 使用as关键字
1
select ename, sal * 12 as yearsal from emp;

image.png
通过as关键字起别名后，查询结果列显示yearsal，可读性增强。

省略as关键字

  • 其实as关键字可以省略，只要使用空格即可
1
select ename, sal * 12 yearsal from emp;

image.png

  • 通过以上测试，得知as可以省略，可以使用空格代替as，但如果别名中有空格呢？

别名中有空格

1
select ename, sal * 12 year sal from emp;

image.png
可以看出，执行报错了，说语法有问题，这是为什么？分析一下：SQL语句编译器在检查该语句的时候，在year后面遇到了空格，会继续找from关键字，但year后面不是from关键字，所以编译器报错了。怎么解决这个问题？记住：如果别名中有空格的话，可以将这个别名使用双引号或者单引号将其括起来。

1
2
select ename, sal * 12 "year sal" from emp;
select ename, sal * 12 'year sal' from emp;

image.png
在mysql中，字符串既可以使用双引号也可以使用单引号，但还是建议使用单引号，因为单引号属于标准SQL。

别名中有中文

  • 如果别名采用中文呢？
1
select ename, sal * 12 年薪 from emp;

image.png
别名是中文是可以的，但是对于低版本的mysql来说会报错，需要添加双引号或单引号。我们当前使用的mysql版本是：8.0.24

条件查询

通常在进行查询操作的时候，都是查询符合某些条件的数据，很少将表中所有数据都取出来。怎么取出表的部分数据？需要在查询语句中添加条件进行数据的过滤。常见的过滤条件如下：

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

条件查询语法格式

1
2
3
4
5
6
select 
  ...
from
  ...
where
  过滤条件;

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

等于、不等于

等于 =

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

1
2
3
4
5
6
select 
  empno,ename
from
  emp
where
  sal = 3000;

image.png
案例2：查询员工FORD的岗位及月薪

1
2
3
4
5
6
select
	job, sal
from
	emp
where
	ename = 'FORD';

image.png
存储在表emp中的员工姓名是FORD，全部大写，如果在查询的时候，写成全部小写会怎样呢？

1
2
3
4
5
6
select
	job, sal
from
	emp
where
	ename = 'ford';

image.png

通过测试发现，即使写成小写ford，也是可以查询到结果的，不过这里需要注意的是：在Oracle数据库当中是查询不到数据的，Oracle的语法要比MySQL的语法严谨。对于SQL语句本身来说是不区分大小写的，但是对于表中真实存储的数据，大写A和小写a还是不一样的，这一点Oracle做的很好。MySQL的语法更随性。另外在Oracle当中，字符串是必须使用单引号括起来的，但在MySQL当中，字符串可以使用单引号，也可以使用双引号，如下：

1
2
3
4
5
6
select
	job, sal
from
  emp
where
  ename = "FORD";

image.png
案例3：查询岗位是MANAGER的员工编号及姓名

1
2
3
4
5
6
select
  empno, ename
from
  emp
where
  job = 'MANAGER';

image.png

  • 任务：查询工资级别是1的最低工资以及最高工资

不等于 <> 或 !=

判断非等量关系，支持字符串、数字、日期类型等。不等号有两种写法，第一种<>，第二种!=，第二种写法和Java程序中的不等号相同，第一种写法比较诡异，不过也很好理解，比如<>3，表示小于3、大于3，就是不等于3。你get到了吗？
案例1：查询工资不是3000的员工编号、姓名、薪资

1
2
3
4
5
6
select
  empno,ename,sal
from
  emp
where
  sal <> 3000;

image.png
案例2：查询工作岗位不是MANAGER的员工姓名和岗位

1
2
3
4
5
6
select
  ename,job
from
	emp
where
	job <> 'MANAGER';

image.png

  • 任务：查询不在部门编号为10的部门工作的员工信息

大于、大于等于、小于、小于等于

大于 >

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

1
2
3
4
5
6
select 
  ename, sal
from
  emp
where
  sal > 3000;

image.png

大于等于 >=

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

1
2
3
4
5
6
select 
  ename, sal
from
  emp
where
  sal >= 3000;

image.png

小于 <

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

1
2
3
4
5
6
select 
  ename, sal
from
  emp
where
  sal < 3000;

image.png

小于等于 <=

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

1
2
3
4
5
6
select 
  ename, sal
from
  emp
where
  sal <= 3000;

image.png

and

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

1
2
3
4
5
6
select
  ename,sal
from
  emp
where
  sal >= 3000 and sal <= 5000;

image.png
image.png

  • 任务：找出工资级别为2~4（包含2和4）的最低工资和最高工资。

or

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

1
2
3
4
5
6
select 
  ename, job
from
  emp
where
  job = 'MANAGER' or job = 'SALESMAN';

image.png
image.png

注意：这个题目描述中有这样一句话：MANAGER和SALESMAN，有的同学一看到“和”，就直接使用“and”了，因为“和”对应的英文单词是“and”，如果是这样的话，就大错特错了，因为and表示并且，使用and表示工作岗位既是MANAGER又是SALESMAN的员工，这样的员工是不存在的，因为每一个员工只有一个岗位，不可能同时从事两个岗位。所以使用and是查询不到任何结果的。如下

1
2
3
4
5
6
select 
  ename, job
from
  emp
where
  job = 'MANAGER' and job = 'SALESMAN';

image.png

  • 任务：查询20和30部门的员工信息。

and和or的优先级问题

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

1
2
3
4
5
6
select
  ename,sal,deptno
from
  emp
where
  sal < 1500 and deptno = 20 or deptno = 30;

image.png
认真解读题意得知：薪资小于1500是一个大前提，要找出的是薪资小于1500的，满足这个条件的前提下，再找部门编号是20或30的，显然以上的运行结果中出现了薪资为1600的，为什么1600的会出现呢？这是因为“sal < 1500 and deptno = 20”结合在一起了，“depnto = 30”成了一个独立的条件。会导致部门编号为30的所有员工全部查询出来。我们应该让“deptno = 20 or deptno = 30”结合在一起，正确写法如下：

1
2
3
4
5
6
select
  ename,sal,deptno
from
  emp
where
  sal < 1500 and (deptno = 20 or deptno = 30);

image.png

  • 任务：找出薪资小于1500的，并且工作岗位是CLERK和SALESMAN的员工姓名、薪资、岗位。

between…and…

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

1
2
3
4
5
6
select 
  ename,sal
from
  emp
where
	sal between 1600 and 3000;

image.png
采用左大右小的方式：

1
2
3
4
5
6
select 
  ename,sal
from
  emp
where
	sal between 3000 and 1600;

image.png
没有查询到任何数据，所以在使用的时候一定要注意：左小右大。

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

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

is null、is not null

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

is null

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

1
2
3
4
5
6
select
  ename,sal,comm
from
  emp
where
  comm is null;

image.png
我们使用等号，尝试一下：

1
2
3
4
5
6
select
  ename,sal,comm
from
  emp
where
  comm = null;

image.png
查询不到任何数据，所以判断是否为空，不能用等号。

is not null

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

1
2
3
4
5
6
select
  ename,sal,comm
from
  emp
where
  comm is not null;

image.png

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

1
2
3
4
5
6
select
  ename,sal,job
from
  emp
where
  job = 'MANAGER' or job = 'SALESMAN';

image.png
第二种：使用in

1
2
3
4
5
6
select
  ename,sal,job
from
  emp
where
  job in('MANAGER', 'SALESMAN');

image.png
案例2：找出薪资是1500/1600/3000的员工姓名、工作岗位

1
2
3
4
5
6
select
  ename,job
from
  emp
where
  sal in(1500, 1600, 3000);

image.png

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

1
2
3
4
5
6
select 
  ename,job
from
  emp
where
  job <> 'MANAGER' and job <> 'SALESMAN';

image.png
第二种：使用not in

1
2
3
4
5
6
select 
  ename,job
from
  emp
where
  job not in('MANAGER', 'SALESMAN');

image.png

in、not in 与 NULL

先来看一下emp表中的数据

1
select * from emp;

image.png
通过表中数据观察到，有4个员工的津贴不为NULL，剩下10个员工的津贴都是NULL。
写这样一条SQL语句：

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

image.png
为什么以上执行结果只有一条记录呢？分析一下：
首先你要知道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);

image.png
以上的执行结果奇怪了，为什么没有查到任何数据呢？我们分析一下：
首先你要知道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。

in和or的效率问题

or的效率为O(n)，而in的效率为O(log n), 当n越大的时候效率相差越明显（也就是说数据量越大的时候，in的效率越高）

模糊查询like

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

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

在模糊查询中，通配符主要包括两个：一个是%，一个是下划线_。其中%代表任意多个字符。下划线_代表任意一个字符。
案例1：查询员工名字以’S’开始的员工姓名

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

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

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

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

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

image.png

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

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

image.png
案例5：查询学员名字中含有下划线的。
执行以下SQL语句，先准备测试数据：

1
2
3
4
5
6
7
8
9
drop table if exists student;
create table student(
  id int,
  name varchar(255)
);
insert into student(id,name) values(1, 'susan');
insert into student(id,name) values(2, 'lucy');
insert into student(id,name) values(3, 'jack_son');
select * from student;

image.png
查询学员名字中含有下划线的，执行以下SQL试试：

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

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

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

image.png

排序操作

排序操作很常用，比如查询学员成绩，按照成绩降序排列。排序的SQL语法：

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

单一字段升序

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

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

image.png

单一字段降序

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

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

image.png

默认采用升序

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

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

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

1
select empno,ename from emp order by ename;

image.png

多个字段排序

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

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

image.png

where和order by的位置

找出岗位是MANAGER的员工姓名和薪资，按照薪资升序排列。

1
select ename,sal from emp where job = 'MANAGER' order by sal asc;

image.png
通过这个例子主要是想告诉大家：where先执行，order by语句是最后执行的。

distinct去重

查询工作岗位

1
select job from emp;

image.png
可以看到工作岗位中有重复的记录，如何在显示的时候去除重复记录呢？在字段前添加distinct关键字。

1
select distinct job from emp;

image.png
注意：这个去重只是将显示的结果去重，原表数据不会被更改。
接下来测试一下，在distinct关键字前添加其它字段是否可以？

1
select ename, distinct job from emp;

分析一下：ename是14条记录，distinct job是5条记录，可以同时显示吗？
image.png
报错了，通过测试得知，distinct只能出现在所有字段的最前面。

当distinct出现后，后面多个字段一定是联合去重的（多个字段如果都相同就只出现一次)

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

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

数据处理函数

关于select语句，我们之前都是这样写：select 字段名 from 表名; 其实，这里的字段名可以看做“变量”，select后面既然可以跟变量，那么可以跟常量吗，尝试一下：
image.png
通过以上sql的测试得知，select后面既可以跟变量，又可以跟常量。
以上三条SQL中前两条中100和’abc’都是常量，最后一条SQL的abc没有添加单引号，它会被当做某个表的字段名，因为没有这个字段所以报错。

字符串相关

转大写upper和ucase

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

image.png
还有一个和upper函数功能相同的函数ucase，也可以转大写，了解一下即可：

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

image.png

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

image.png

转小写lower和lcase

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

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

image.png
image.png

截取字符串substr

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

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

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

image.png

获取字符串长度length

image.png
注意：一个汉字是2个长度。

获取字符的个数char_length

image.png

字符串拼接

语法：concat(‘字符串1’, ‘字符串2’, ‘字符串3’….)
拼接的字符串数量没有限制。
image.png
注意：在mysql8之前，双竖线||也是可以完成字符串拼接的。但在mysql8之后，||只作为逻辑运算符，不能再进行字符串拼接了。

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

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

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

image.png
mysql中可以使用+进行字符串的拼接吗？不可以，在mysql中+只作加法运算，在进行加法运算时，会将加号两边的数据尽最大的努力转换成数字再求和，如果无法转换成数字，最终运算结果通通是0

去除字符串前后空白trim

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

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

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

image.png
去除后置0

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

image.png
前置0和后置0全部去除

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

image.png

数字相关

rand()和rand(x)

rand()生成0到1的随机浮点数。
image.png
rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。
image.png
image.png

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

round(x) 四舍五入，保留整数位，舍去所有小数
image.png
round(x,y) 四舍五入，保留y位小数
image.png

truncate(x, y)舍去

y是保留几位小数

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

ceil与floor

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

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

image.png

空处理

ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。
ifnull(comm, 0)，表示如果员工的津贴是NULL时当做0处理。
在SQL语句中，凡是有NULL参与的数学运算，最终的计算结果都是NULL：
image.png
看这样一个需求：查询每个员工的年薪。（年薪 = (月薪 + 津贴) * 12个月。注意：有的员工津贴comm是NULL。）
image.png

以上查询结果中显示SMITH等人的年薪是NULL，这是为什么，这是因为SMITH等人的津贴comm是NULL，有NULL参与的数学运算，最终结果都是NULL，显然这个需要空处理，此时就用到了ifnull函数：
image.png

日期和时间相关函数

获取当前日期和时间

image.png
image.png
image.png

image-20250528163048586now()和sysdate()的区别：

  • now()：获取的是执行select语句的时刻。
  • sysdate()：获取的是执行sysdate()函数的时刻。

获取当前日期

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

  • curdate()
  • current_date()
  • current_date

获取当前时间

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

  • curtime()
  • current_time()
  • current_time

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

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

date_add函数

date_add函数的作用：给指定的日期添加间隔的时间，从而得到一个新的日期。
date_add函数的语法格式：date_add(日期, interval expr 单位)，例如：
image.png
以’2023-01-03’为基准，间隔3天之后的日期：’2023-01-06’
image.png

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

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

请分析下面这条SQL语句所表达的含义：
image.png
以上SQL表示：以2022-10-01 10:10:10为基准，在这个时间基础上添加-1微秒，也就是减去1微秒。
以上SQL也可以采用date_sub函数完成，例如：
image.png
另外，单位也可以采用复合型单位，例如：

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

如果单位采用复合型的话，expr该怎么写呢？例如单位采用：day_hour，假设我要表示3天2小时之后，怎么写？
image.png
‘3,2’这个应该很好理解，表示3天2个小时之后。’3,2’和day_hour是对应的。

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
image.png
注意：在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，会自动转换成该格式的字符串：
image.png

str_to_date函数

该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。（char类型转换成date类型）
假设有一个学生表t_student，学生有一个生日的字段，类型是date类型：

1
2
3
4
5
6
drop table if exists t_student;
create table t_student(
  name varchar(255),
  birth date
);
desc t_student;

我们要给这个表插入一条数据：姓名zhangsan，生日85年10月1日，执行以下insert语句：
image.png
错误原因：日期值不正确。意思是：birth字段需要一个日期，你给的这个字符串’10/01/1985’我识别不了。这种情况下，我们就可以使用str_to_date函数进行类型转换：
image.png
image.png
当然，如果你提供的日期字符串格式能够被mysql解析，str_to_date函数是可以省略的，底层会自动调用该函数进行类型转换：
image.png
如果日期格式符合以上的几种格式，mysql都会自动进行类型转换的。

dayofweek、dayofmonth、dayofyear函数

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

last_day函数

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

datediff函数

计算两个日期之间所差天数：
image.png
时分秒不算，只计算日期部分相差的天数。

timediff函数

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

if函数

如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”：

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

例如：如果工资高于3000，则输出1，反之则输出0
image.png
再例如：如果名字是SMITH的，工资上调10%，其他员工工资正常显示。
image.png
再例如：工作岗位是MANAGER的工资上调10%，是SALESMAN的工资上调20%，其他岗位工资正常。
image.png
上面这个需求也可以使用：case.. when.. then.. when.. then.. else.. end来完成：
image.png

cast函数

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

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

image.png
image.png
image.png
image.png

加密函数

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

分组函数

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

  • 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(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执行后会报错。

image.png

having

having写在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函数的使用：
image.png
group_concat函数的使用：
image.png
学习了这两个函数之后，自己可以尝试写出来吗？

总结单表的DQL语句

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

连接查询

什么是连接查询

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

连接查询的分类

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

笛卡尔积现象

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

内连接

什么叫内连接

内连接.png
满足条件的记录才会出现在结果集中。

内连接之等值连接

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

1
2
3
4
5
6
7
8
select
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;

注意：inner可以省略。
image.png

内连接之非等值连接

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

1
2
3
4
5
6
7
8
select
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

image.png

内连接之自连接

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

1
2
3
4
5
6
7
8
select
	e.ename 员工名, l.ename 领导名
from
	emp e
join
	emp l
on
	e.mgr = l.empno;

image.png
思路：
将emp表当做员工表 e
image.png
将emp表当做领导表 l
image.png
可以发现连接条件是：e.mgr = l.empno（员工的领导编号=领导的员工编号）
注意：KING这个员工没有查询出来。如果想将KING也查询出来，需要使用外连接。

外连接

什么叫外连接

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

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

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

1
2
3
4
5
6
7
8
select
  d.*,e.ename
from
  dept d
left outer join
  emp e
on
  d.deptno = e.deptno;

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

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

还是上面的案例，可以写作右连接。

1
2
3
4
5
6
7
8
select
  d.*,e.ename
from
  emp e
right outer join
  dept d
on
  d.deptno = e.deptno;

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

1
2
3
4
5
6
7
8
select 
  e.ename 员工名,l.ename 领导名 
from 
  emp e 
left join 
  emp l 
on
  e.mgr = l.empno;
1
2
3
4
5
6
7
8
select 
  e.ename 员工名,l.ename 领导名 
from 
  emp l 
right join 
  emp e 
on
  e.mgr = l.empno;

image.png

全连接

什么是全连接？
MySQL不支持full join。oracle数据库支持。
全连接.png
两张表数据全部查询出来，没有匹配的记录，各自为对方模拟出NULL进行匹配。
客户表：t_customer
image.png
订单表：t_order
image.png
案例：查询所有的客户和订单。

1
2
3
4
5
6
7
8
select 
 c.*,o.* 
from 
 t_customer c 
full join 
 t_order o 
on 
 c.cid = o.cid;

多张表连接

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

1
2
3
4
5
6
7
8
9
10
11
12
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;

image.png

子查询

什么是子查询

  1. select语句中嵌套select语句就叫做子查询。
  2. 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后面不能直接使用分组函数。
可以使用子查询：

1
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;

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

1
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;

image.png

select后面使用子查询

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

image.png

exists、not exists

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

主要应用场景：

  • EXISTS 可以与 SELECT、UPDATE、DELETE 一起使用，用于检查另一个查询是否返回任何行；
  • EXISTS 可以用于验证条件子句中的表达式是否存在；
  • EXISTS 常用于子查询条件过滤，例如查询有订单的用户等。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
drop table if exists t_customer;
drop table if exists t_order;

create table t_customer(
  customer_id int,
  customer_name varchar(32)
);

create table t_order(
  order_id int,
  order_price decimal(5,1),
  customer_id int
);

insert into t_customer(customer_id,customer_name) values(1,'zhangsan');
insert into t_customer(customer_id,customer_name) values(2,'lisi');
insert into t_customer(customer_id,customer_name) values(3,'wangwu');

insert into t_order(order_id, order_price, customer_id) values(10, 1000.0, 1);
insert into t_order(order_id, order_price, customer_id) values(20, 2000.0, 1);
insert into t_order(order_id, order_price, customer_id) values(30, 3000.0, 2);
insert into t_order(order_id, order_price, customer_id) values(40, 4000.0, 2);

commit;
select * from t_customer;
select * from t_order;

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

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

在这个查询语句中，子查询用于检查是否有订单与每个客户相关联。如果子查询返回至少一行，则表示该顾客已经下过订单，并返回此客户的所有信息，否则该顾客将不被包含在结果中。

以下是这个查询语句的执行过程：

  1. 首先查询表 t_customer 中的所有顾客信息（以下简称为顾客表）；
  2. 对于顾客表中的每一行，都执行一次子查询，子查询查询该顾客有没有订单，如果有，则在结果集中保留该顾客信息；如果没有，则将该顾客排除；
  3. 最终返回有订单顾客的所有信息。

除了 EXISTS，也可以使用 NOT EXISTS 条件从 SELECT、UPDATE、DELETE 语句中获取子查询的返回结果。NOT EXISTS 用于检查一个子查询是否返回任何行，如果没有行返回，那么 NOT EXISTS 将返回 true。

例如，我们想要查找所有没有下过订单的顾客，可以使用以下 sql 语句：

1
select * from t_customer c where not exists(select * from t_order o where o.customer_id=c.customer_id);

在这个查询语句中，如果没有任何与顾客相关联的订单，则 NOT EXISTS 子查询将返回一个空结果集，这时候 WHERE 条件为 true，并将返回所有顾客信息。如果顾客有订单，则 NOT EXISTS 子查询的结果集将不为空，WHERE 条件为 false，则不会返回该顾客的信息。

总之，无论是 EXISTS 还是 NOT EXISTS，都是非常有用的 SQL 工具。可以通过它们来结合子查询来动态过滤查询结果，使 SQL 查询变得更加灵活和高效。

in和exists区别

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

  1. IN 操作符是根据指定列表中的值来判断是否满足条件，而 EXISTS 操作符则是根据子查询的结果是否有返回记录集来判断。
  2. EXISTS 操作符通常比 IN 操作符更快，尤其是在子查询返回记录数很大的情况下。因为 EXISTS 只需要判断是否存在符合条件的记录，而 IN 操作符需要比对整个列表，因此执行效率相对较低。
  3. IN 操作符可同时匹配多个值，而 EXISTS 只能匹配一组条件。

下面是一个简单的示例，用于演示 IN 和 EXISTS 之间的区别。假设我们有两个表 orders 和 products，orders 表中记录了订单信息，products 表中记录了商品信息。现在我们想查询所有“手机”和“平板电脑”这两种商品中，至少有一笔订单销售了 $1000 以上的商品：

使用 IN 操作符：

1
2
3
4
5
6
7
8
SELECT *
FROM products
WHERE product_name IN ('手机', '平板电脑')
AND product_id IN (
  SELECT product_id
  FROM orders
  WHERE order_amount > 1000
);

使用 EXISTS 操作符：

1
2
3
4
5
6
7
8
9
SELECT *
FROM products
WHERE product_name IN ('手机', '平板电脑')
AND EXISTS (
  SELECT *
  FROM orders
  WHERE orders.product_id = products.product_id
  AND order_amount > 1000
);

总之，IN 和 EXISTS 都是用于条件过滤的操作符，但其实现方式和性能特点都不同，需要根据具体情况进行选择和使用。

union&union all

不管是union还是union all都可以将两个查询结果集进行合并。
union会对合并之后的查询结果集进行去重操作。
union all是直接将查询结果集合并，不进行去重操作。（union all和union都可以完成的话，优先选择union all，union all因为不需要去重，所以效率高一些。）
image.png
image.png
案例：查询工作岗位是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。
两个结果集合并时，列数量要相同：
image.png

limit

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

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

1
select ename,sal from emp limit 5;
  1. 查询工资排名在前5名的员工（limit是在order by执行之后才会执行的）
1
select ename,sal from emp order by sal desc limit 5;
  1. 通用的分页sql

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

表相关

创建表

语法格式：

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

例如：创建学生表

1
2
3
4
5
create table t_student(
  no int,
  name varchar,
  gender char(1) default '男'
);

插入数据

语法格式：

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

字段名和值要一一对应。类型要一一对应，数量要一一对应。
字段名也可以省略，如果字段名省略就表示把所有字段名都写上去了，并且顺序和建表时的顺序相同。

删除表

语法格式：

1
drop table 表名;

或者

1
drop table if exists 表名;

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

MySQL数据类型

数据类型（data_type）是指系统中所允许的数据的类型。数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。
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个）

日期和时间类型

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个字符。
固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。m表示列的长度，范围是 0～255 个字符。
例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当插入的字符长度大于4，则报错（除非超过4个长度之后都是空格字符，则空格字符会自动被删除用来保证插入的成功）。

varchar

varchar(m)：m长度是0~16383个字符
长度可变的字符串。varchar 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。
例如，varchar(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。varchar在值保存和检索时尾部的空格仍保留。
1708647300729-2181603b-e620-4be8-bab6-1b9c029989d8.png

text

text类型：

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

enum

enum类型：

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

set

set类型：

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

二进制类型

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

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

增删改表结构DDL

创建一个学生表

1
2
3
4
5
create table t_student(
  no bigint,
  name varchar(255),
  age int 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 删

语法格式：

1
2
3
4
5
# 将所有记录全部删除
delete from 表名;

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

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

1
truncate table 表名;

update 改

语法格式：

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

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

约束constraint

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

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

非空约束

语法格式：

1
2
3
4
5
create table t_stu(
  no int,
  name varchar(255) not null,
  age int
);

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

检查约束

1
2
3
4
5
6
create table t_stu(
  no int,
  name varchar(255),
  age int,
  check(age > 18)
);

唯一性约束

语法格式：

1
2
3
4
5
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255) unique
);

email字段设置为唯一性，唯一性的字段值是可以为NULL的。但不能重复。以上在字段后面添加的约束，叫做列级约束。
当然，添加约束还有另一种方式：表级约束：

1
2
3
4
5
6
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(email)
);

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

1
2
3
4
5
6
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(name,email)
);

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

1
2
3
4
5
6
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  constraint t_stu_name_email_unique unique(name,email)
);

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

主键约束

  1. 主键：primary key，简称PK
  2. 主键约束的字段不能为NULL，并且不能重复。
  3. 任何一张表都应该有主键，没有主键的表可以视为无效表。
  4. 主键值是这行记录的身份证号，是唯一标识。在数据库表中即使两条数据一模一样，但由于主键值不同，我们也会认为是两条完全的不同的数据。
  5. 主键分类：
    1. 根据字段数量分类：
      1. 单一主键（1个字段作为主键）==>建议的
      2. 复合主键（2个或2个以上的字段作为主键）
    2. 根据业务分类：
      1. 自然主键（主键和任何业务都无关，只是一个单纯的自然数据）===>建议的
      2. 业务主键（主键和业务挂钩，例如：银行卡账号作为主键）
  6. 单一主键（建议使用这种方式）
1
2
3
4
5
create table t_student(
  id bigint primary key,
  sno varchar(255) unique,
  sname varchar(255) not null
)
  1. 复合主键（很少用，了解）
1
2
3
4
5
6
create table t_user(
  no int,
  name varchar(255),
  age int,
  primary key(no,name)
);
  1. 主键自增：既然主键值是一个自然的数字，mysql为主键值提供了一种自增机制，不需要我们程序员维护，mysql自动维护该字段
1
2
3
4
create table t_vip(
  no int primary key auto_increment,
  name varchar(255)
);

外键约束

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

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

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

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

  1. 外键约束：foreign key，简称FK。
  2. 添加了外键约束的字段中的数据必须来自其他字段，不能随便填。
  3. 假设给a字段添加了外键约束，要求a字段中的数据必须来自b字段，b字段不一定是主键，但至少要有唯一性。
  4. 外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键很少用。
  5. a表如果引用b表中的数据，可以把b表叫做父表，把a表叫做子表。
    1. 创建表时，先创建父表，再创建子表。
    2. 插入数据时，先插入父表，在插入子表。
    3. 删除数据时，先删除子表，再删除父表。
    4. 删除表时，先删除子表，再删除父表。
  6. 如何添加外键：
1
2
3
4
5
6
7
8
9
10
11
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) 
);
  1. 级联删除

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

1
2
3
4
5
6
7
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 
);
1
2
3
4
###删除约束
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;
  1. 级联更新
1
2
3
4
5
6
7
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 
);
  1. 级联置空
1
2
3
4
5
6
7
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 
);

三范式

什么是数据库设计三范式

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

三范式

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

image.png

  2. 应该这样设计：

image.png

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

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

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

image.png

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

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

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

image.png

一对多怎么设计

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

多对多怎么设计

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

一对一怎么设计

两种方案：

  1. 第一种：主键共享

image.png

  1. 第二种：外键唯一

image.png

最终的设计

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

视图

  1. 只能将select语句创建为视图。
  2. 创建视图
1
create or replace view v_emp as select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
  1. 视图作用
    1. 如果开发中有一条非常复杂的SQL，而这个SQL在多处使用，会给开发和维护带来成本。使用视图可以降低开发和维护的成本。
    2. 视图可以隐藏表的字段名。
  2. 修改视图
1
alter view v_emp as select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno;
  1. 删除视图
    1. drop view if exists v_emp;
  2. 对视图增删改（DML：insert delete update）可以影响到原表数据。

事务

事务概述

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

事务四大特性：ACID

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

演示MySQL事务

在dos命令窗口中开启MySQL事务：start transaction; 或者：begin;
回滚事务：rollback;
提交事务：commit;
只要执行以上的rollback或者commit，事务都会结束。
MySQL默认情况下采用的事务机制是：自动提交。所谓自动提交就是只要执行一条DML语句则提交一次。

事务隔离级别

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

查看与设置隔离级别

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

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

设置事务隔离级别：

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

不同现象

脏读

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

不可重复读

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

幻读

指在事务执行过程中，前后两次相同的查询条件得到的结果集不一致，可能会变多或变少。

隔离级别

读未提交（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;
image.png
mysql> insert into a values(4);
mysql> select * from a;
image.png

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

读提交（READ COMMITTED）

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

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

1
set global transaction isolation level read committed;

演示：

A事务 B事务
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a;
image.png
mysql> insert into a values(4);
mysql> select * from a;
image.png
mysql> commit;
mysql> select * from a;
image.png

通过以上测试看出，A事务只能读取到B事务提交之后的数据。这种隔离级别解决了脏读问题，但肯定是存在不可重复读和幻读问题。因为只要事务B进行了增删改操作之后并提交了，事务A读取到的数据肯定是不同的。即：不可重复读和幻读都存在。

可重复读（REPEATABLE READ）

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

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

1
set global transaction isolation level repeatable read;

演示：

A事务 B事务
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select empno,ename,sal from emp where empno=7369;
image.png
mysql> update emp set ename=’SMITH’,sal=8000 where empno=7369;
mysql> commit;
mysql> select empno,ename,sal from emp where empno=7369;
image.png

通过以上测试得知：当事务隔离级别设置为可重复读时，避免了不可重复读问题。

那么在MySQL当中，当事务隔离级别设置为可重复读时，能够避免幻读问题吗？测试一下：

事务A 事务B
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a;
image.png
mysql> insert into a values(5);
mysql> commit;
mysql> select * from a;
image.png

通过以上测试得知：当事务隔离级别设置为可重复读时，也避免了幻读问题。是完全避免了幻读问题吗？并不是。请看以下测试：

事务A 事务B
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a;
image.png
mysql> insert into a values(6);
mysql> commit;
mysql> select * from a for update;
image.png

通过以上测试得知：当事务隔离级别设置为可重复读，MySQL会尽最大努力避免幻读问题，但这种隔离级别无法完全避免幻读问题。

串行化（SERIALIZABLE）

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

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

1
set global transaction isolation level serializable;

演示：

事务A 事务B
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a;
image.png
mysql> insert into a values(7);
mysql> select * from a;
image.png
mysql> commit;
image.png

通过以上测试得知：当事务隔离级别设置为串行化时，事务只能排队执行，不支持并发。

可重复读的幻读问题

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

  • 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题。
  • 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

快照读是如何解决幻读的

什么是快照读？普通的select语句都是采用的快照读。顾名思义：在整个事务的处理过程中，执行相同的一个select语句时，每次都是读取的快照。（快照指的是固定的某个时刻的数据，就像现实世界中的拍照一样，把那个美好的时刻留下来）。也就是说，当事务隔离级别是可重复读，并且执行的select语句是一个普通的select语句时，都会采用快照读的方式读取数据，底层实现原理是：

  • 底层由 MVCC（多版本并发控制）实现，实现的方式是开始事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好的避免了幻读问题。

演示：

事务A 事务B
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a; //快照读
image.png
mysql> insert into a values(5);
mysql> commit;
mysql> select * from a; //快照读
image.png

当前读是如何解决幻读的

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

假如有这样的数据：
image.png
SQL语句是这样写的：

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

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

演示：

事务A 事务B
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a where id between 2 and 4 for update; // 当前读
image.png

出现幻读的两种情况

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

第一种产生幻读的场景

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

事务A 事务B
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a;
image.png
mysql> insert into a values(5);
mysql> commit;
mysql> select * from a for update; // 产生了幻读
image.png
第二种产生幻读的场景

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

事务A 事务B
mysql> use powernode
mysql> use powernode
mysql> start transaction;
mysql> start transaction;
mysql> select * from a;
image.png
mysql> insert into a values(6);
mysql> commit;
mysql> update a set id=100 where id=6; //主要是因为这个SQL语句的执行触发了当前读
mysql> select * from a; // 产生了幻读
image.png

总结可重复读的幻读问题

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

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

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

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

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

DBA命令

新建用户

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

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

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

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

采用以上方式新建的用户没有任何权限：系统表也只能看到以下两个
image.png
使用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表）

1
2
3
4
5
# 将所有库所有表的查询权限赋予本地用户java1
grant select,insert,delete,update,create on *.* to 'java1'@'localhost';

# 将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地址’;

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

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

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

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

修改用户的密码

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

1
2
3
4
5
# 本地用户修改密码
alter user 'java1'@'localhost' identified by '456';

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

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

修改用户名

1
2
3
4
5
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数据库之前进行）
1
2
3
4
5
# 导出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之前进行）
1
2
3
4
# 现在登录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