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 恢復
不要在mysql中執行，在命令行中執行即可
- 備份了整個庫，進行恢復
mysql –u 用戶 –p < XXX.sql
- 只備份了表，要指定庫恢復
mysql –u 用戶 –p 庫名 < XXX.sql
4.3.3 示例
- 庫的備份與恢復
# 備份庫test1
mysqldump –u root –p --databases test1 > D:/333/test1.sql
# 進入MySQL，手動刪除庫test1
drop database test1;
# 恢復庫test1
mysql –u root –p < D:/333/test1.sql
- 表的備份與恢復
# 備份test1庫中的emp表
mysqldump -u root -p test1 emp > D:/333/test1_emp.sql
# 進入MySQL，手動表emp
drop table test1.emp;
# 恢復表emp
mysql -u root -p test1 < D:/333/test1_emp.sql
![[Oracle] UNPIVOT 列轉行](http://pic.xiahunao.cn/[Oracle] UNPIVOT 列轉行)
：端口無權限)