1 简介

MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB公司开发，属于 Oracle 旗下产品，是当今最流行的关系型数据库管理系统之一，在 WEB应用方面，MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。

以Linux作为操作系统，Apache 或Nginx作为 Web服务器，MySQL 作为数据库，PHP/Perl/Python作为服务器端脚本解释器。由于这四个软件都是免费或开放源码软件（FLOSS），因此使用这种方式不用花一分钱（除开人工成本）就可以建立起一个稳定、免费的网站系统，被业界称为 “LAMP“或“LNMP”组合。

1.1 常见版本

MySQL 5.x 系列

MySQL 5.5：早期版本，仍被一些服务器使用，提供基本数据库功能，但存在安全漏洞，建议升级。
MySQL 5.6：广泛使用的版本，引入了并行复制、半同步复制、InnoDB性能改进等新特性。
MySQL 5.7：在5.6基础上增强了性能和功能，支持JSON数据类型、多源复制、全文搜索索引增强等。

MySQL 8.0

最新版本，于2018年发布，引入了许多重大改进，如更好的性能、增加的功能和改进的安全性。其中一些新特性包括支持基于角色的访问控制，窗口函数，新的数据字典，以及性能优化，如多线程复制和并行查询。

2 Linux安装MySQL

在Linux系统上安装MySQL可以通过多种方式进行，具体步骤取决于使用的发行版。这里提供一个适用于 Ubuntu系统的示例。

2.1 安装

更新软件包列表

apt update

安装MySQL服务器

apt install mysql-server

查看MySQL服务状态

# 安装完成后，MySQL服务会自动启动
systemctl status mysql

2.2 配置

为了提高安全性，建议运行安全脚本来移除一些默认的不安全设置。
按照提示操作即可，包括设置root密码、移除匿名用户、禁止root远程登录等。

mysql_secure_installation

2.3 测试

mysql -u root -p

输入在安全脚本中设置的root密码后，看见MySQL shell提示符，表示安装成功
没有密码直接回车即可

退出

exit;

3 数据库操作

3.1 数据库

进入MySQL

mysql -u root -p

查看所有数据库

show databases;

创建数据库

create database test default charset utf8;
create database test1 default charset utf8;

删除数据库

drop database test;

选择数据库

use test1;

查看库中所有表

show tables;

查看创建数据库的语句

show create database test1;

3.2 表结构

3.2.1 约束

关键字	描述
auto_increment	自增长
not null	非空
default ‘xx’	默认值
unique	唯一
primary key	主键
froeign key	外键
charset	指定字符集

3.2.2 操作

创建表

create table student(id int auto_increment primary key,name varchar(5) not null,sex varchar(3) default '女',address varchar(50),phone varchar(15) not null unique,age int
);

删除表

drop table student;

改表名

alter table student rename students;

修改列属性

alter table students modify name varchar(10);

修改列名

alter table students change name newname varchar(10);
alter table students change newname name varchar(10);

添加新的列

alter table students add grade float after sex;

查看建表语句

show create table students;

查看表结构

desc students;

3.3 表数据

create table emp(empno    int,ename    varchar(10),job      varchar(9),mgr      int,hiredate varchar(10),sal      float,comm     float,deptno   int
);

insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '17-12-1980', 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '20-02-1981', 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '22-02-1981', 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '02-04-1981', 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN',7698, '28-09-1981', 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '01-05-1981', 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '09-06-1981', 2450, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '19-04-1987', 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '17-11-1981', 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, '08-09-1981', 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '23-05-1987', 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '03-12-1981', 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '03-12-1981', 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '23-01-1982', 1300, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (3399, 'ZX', null, null, '07-08-2025', null, null, null);

create table dept(deptno int,dname  varchar(14),loc    varchar(13)
);

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');

3.3.1 增删改

插入

insert into students(name,sex,phone) values('zs','男',1001);
insert into students(name,sex,phone) values('ls',null,1002);
insert into students(name,sex,phone) values('ls','',1004);

删除

# 删除指定数据
delete from students where phone=1004;
# 删除表中所有数据，自增长继续
delete from students;
# 删除表中所有数据，自增长从头再来
truncate students;

修改

update students set name='ww' where phone=1004;
# 不指定条件修改所有
update students set sex='女';

3.3.2 事务

在MySQL中执行命令时，通常都直接被确定提交了。也就是说用户不用在意此事，所有的命令都会被自动 COMMIT。

取消自动提交

set autocommit=0;
insert into students(name,sex,phone) values('zs','男',1001);
insert into students(name,sex,phone) values('ls',null,1002);

提交

commit;

回滚：未提交的操作，回滚会全部失效

insert into students(name,sex,phone) values('zs','女',1003);
insert into students(name,sex,phone) values('ls',null,1004);
rollback;

回滚点：回滚后，回滚点之后的操作全部失效

insert into students(name,sex,phone) values('zs','女',1003);
savepoint a;
insert into students(name,sex,phone) values('ls',null,1004);
rollback to a;

开启自动提交

set autocommit=1;

3.3.3 查询

查询所有数据

select * from students;

查询指定字段

select id,name,phone from students;

条件查询

select * from students where sex='男';

多条件查询

select * from students where sex='男' or sex='女';

不等

select * from students where sex!='男';
select * from students where sex<>'男';
select * from students where not sex='男';

模糊查询

select * from students where name like '%z%';
select * from students where name like 'z_';

like：模糊匹配
%：任意n个字符
_：任意1个字符

别名

select s.id 序号,s.name 姓名,s.phone 手机号 from students s;

排序

select * from students order by phone desc;
select * from students order by phone asc;

desc：降序
asc：升序，默认选项可不写

去重

select distinct name from students;

统计

select count(*) from students where sex='男';

函数

select max(phone) from students;
select min(phone) from students;
select avg(phone) from students;
select sum(phone) from students;

分组

select sex 性别,count(*) 人数 from students group by sex;

3.3.4 分页查询

语法：limit a,b

a：起始位，不包括它本身，不写默认为0
b：长度

select * from emp order by sal limit 1,5;
select * from emp order by sal limit 5;

3.3.5 多表关联

内联

select d.deptno,d.dname,e.empno,e.ename
from emp e,dept d where e.deptno=d.deptno;
# 效果相同
select d.deptno,d.dname,e.empno,e.ename
from emp e inner join dept d on e.deptno=d.deptno;

左外联

select d.deptno,d.dname,e.empno,e.ename
from emp e left join dept d on e.deptno=d.deptno;

右外联

select d.deptno,d.dname,e.empno,e.ename
from emp e right join dept d on e.deptno=d.deptno;

MySQL没有全外联，可以通过并集实现

select d.deptno,d.dname,e.empno,e.ename
from emp e left join dept d on e.deptno=d.deptno
union
select d.deptno,d.dname,e.empno,e.ename
from emp e right join dept d on e.deptno=d.deptno;

3.3.6 子查询

select * from emp where deptno=(select deptno from dept where loc='CHICAGO'
);

4 数据库备份

4.1 备份类型

物理备份：直接对数据库的数据文件、日志文件、索引文件进行备份
逻辑备份（热备份）：对数据库对象(库、表）以SQL语句的形式导出进行备份

4.2 物理冷备份与恢复

物理冷备份一般用归档或复制命令直接打包数据库文件夹，通常为安装目录下的data文件夹。在进行备份之前需要关闭mysqld服务：

linux: systemctl stop mysqld
windows: net stop mysqld

物理恢复，将备份文件解压缩至原data目录所在目录或覆盖data目录中的文件，回复完成，重新启动mysqI服务。

4.3 逻辑热备份与恢复

可使用mysql自带的备份工具mysqldump进行备份（完全备份、逻辑热备）

4.3.1 备份

不要在mysql中执行，在命令行中执行即可

备份指定库中的一个或多个表（不包含库对象本身）

mysqldump –u 用户 –p 密码 库名 表1 表2 > XXX.sql

备份一个或多个指定库及库中的所有表

mysqldump –u 用户 –p 密码 --databases 库1 库2 > XXX.sql

备份所有库及库中的所有表

mysqldump –u 用户 –p 密码 --all-databases > XXX.sql

mysqidump –u 用户 –p 密码 --all-databases | gzip > XXX.sql.gz

mysqldump常用选项：

选项	说明
–databases(-B)	备份数据库
–all-databases(-A)	备份所有数据库
-d或–no-data	用于备份数据库的结构（表的创建语句），但不包括表中的数据
–flush-logs(-F)	在备份开始前刷新MySQL服务器的日志文件
–single-transaction	使用单一事务来备份数据
–lock-all-tables	备份过程中锁定所有表
–master-data=2	用于备份二进制日志的位置信息，用于设置主从复制
–set-gtid-purged=OFF	用于关闭GTID信息的输出

4.3.2 恢复