DDL--數據定義語言(create, alter, drop)
DML--數據操縱語言(select, insert, delete, update)
DCL--數據控制語言(grant, revoke, commit, rollback)
DQL--數據查詢語言()
\c 停止運行
\s 查看數據庫信息# database
# 創建庫
create database db01 charset utf8;# 查看庫
show databases; # 查看所有庫的庫名
show create database db01; # 單獨查看某一個庫的信息# 修改庫(不能修改庫名)
alter database db01 charset utf8;# 刪除庫
drop database db01;
---------------------------------------------------------------------------------------------
# table
use db01; # 切換庫
select database(); # 查看當前所在的庫# 創建表
create table db01.t01(id int,name char); # 不指定庫,默認創建當前庫下
create table t01(id int,name char);# 查看表
show tables; # 查看當前庫下的所有表
show create table t01; # 查看某一個表的信息
describe t01; # 查看表結構
desc t01; # describe簡寫# 修改表
alter table t01 modify name char(32);# 刪除表
drop table t01;
---------------------------------------------------------------------------------------------
# column
# 增加記錄
insert into db01.t01 values(1,'allen'),(2,'kevin');# 查看記錄
select id,name from db01.t01;# 修改記錄
update db01.t01 set name='collins' where name='kevin';# 刪除記錄
delete from db01.t01; # 刪除整張表的記錄,但是id自增記錄不會清空
delete from db01.t01 where name='collins'; # 刪除指定記錄
truncate t01; # 清除數據,重置表
---------------------------------------------------------------------------------------------
# 創建表的完整語法
create table 表名(
字段名1 類型[(寬度) 約束條件],
字段名2 類型[(寬度) 約束條件],
字段名3 類型[(寬度) 約束條件],
);# 解釋
類型：使用限制字段必須以什么樣的數據類型傳值
約束條件：約束條件是在類型之外添加一種額外的限制# 注意:
同一張表中,字段名是不能相同的
寬度和約束條件可選
字段名和類型是必須的
最后一個字段后面不能加逗號
五、mysql數據庫引擎
六、mysql之sql_mode
# 查看系統變量
show variables;# 查看sql_mode
show variables like "%sql_mode%";
ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION # 設置嚴格模式(STRICT_TRANS_TABLES)
# 在該模式下,如果插入的數據超過限制,則會立即報錯
set global sql_mode='STRICT_TRANS_TABLES'; # 設置全局的sql_mode
set session sql_mode='STRICT_TRANS_TABLES'; # 設置當前連接的sql_mode
# 兩張表之間是一個雙向的多對一的關系,稱之為多對多
# 建立第三章表,表中有一個字段fk左表的id,表中有一個字段fk右表的idcreate table author(id int primary key auto_increment,name char(16),age int
)engine=innodb;create table book(id int primary key auto_increment,name char(16),price decimal(5,2)
)engine=innodb;create table author2book(id int primary key auto_increment,author_id int,book_id int,foreign key(author_id) references author(id) on update cascade on delete cascade,foreign key(book_id) references book(id) on update cascade on delete cascade
)engine=innodb;
二十五、mysql之一對一
idnamephoneqq
1
allen
110
110
2
kevin
120
120
3
collins
119
119
4
mark
114
114
idclass_namecustomer_id
1
python01
1
2
linux01
2
3
python01
3
4
linux02
4
# 一對一只需要在外鍵字段加上unique唯一約束
# 左表的一條記錄唯一對應右表的一條記錄,反之也是一樣的create table customer(id int primary key auto_increment,name char(16),phone char(11),qq char(10)
)engine=innodb;create table student(id int primary key auto_increment,class_name char(16),customer_id int unique,foreign key(customer) references customer(id) on update cascade on delete cascade
)engine=innodb;
二十六、mysql之關聯表練習
# 查找表的關聯關系使用sql語句創建# 1
賬號信息表 用戶組 主機表 主機組
create table usergroup(id int primary key auto_increment,username char(16) unique,password char(16)
)engine=innodb;create table user(id int primary key auto_increment,name char(16),
)engine=innodb;create table hostgroup(id int primary key auto_increment,name char(16)
)engine=innodb;create table host(id int primary key auto_increment,ip char(15) not null default,
)engine=innodb;create table user2usergroup(id int primary key auto_increment,user_id int,usergroup_id int,foreign key(user_id) references d1.user(id) on update cascade on delete cascade,foreign key(usergroup_id) references d1.usergroup(id) on update cascade on delete cascade
)engine=innodb;create table host2hostgroup(id int primary key auto_increment,host_id int,hostgroup_id int,foreign key(host_id) references d1.host(id) on update cascade on delete cascade,foreign key(hostgroup_id) references d1.host(id) on update cascade on delete cascade
)engine=innodb;
s
# 2
班級表 學生表 老師表 課程表 成績表
create table class(id int primary key auto_increment,name char(16)
)engine=innodb;create table student(id int primary key auto_increment,name cahr(16),age int,class_id int,foreign key (class_id) references class(id) on update cascade on delete cascade
)engine=innodb;create table teacher(id int primary key auto_increment,name char(16),age int,gender enum("male","female"),
)engine=innodb;create table course(id int primary key auto_increment,name char(16),teacher_id int,foreign key (teacher_id) references teacher(id) on update cascade on delete cascade,
)engine=innodb;create table score(id int primary key auto_increment,score int,student_id int,course_id int,foreign key (student_id) references student(id) on update cascade on delete cascade,foreign key (course_id) references course(id) on update cascade on delete cascade
)engine=innodb;
二十七、mysql之修改表alter table
語法：
1. 修改表名ALTER TABLE 表名 RENAME 新表名;2. 增加字段ALTER TABLE 表名 ADD 字段名 數據類型 [完整性約束條件…],ADD 字段名 數據類型 [完整性約束條件…];ALTER TABLE 表名 ADD 字段名 數據類型 [完整性約束條件…] FIRST;ALTER TABLE 表名 ADD 字段名 數據類型 [完整性約束條件…] AFTER 字段名;
alter table order add goods_sn varchar(128) unque;
3. 刪除字段ALTER TABLE 表名 DROP 字段名;4. 修改字段ALTER TABLE 表名 MODIFY 字段名 數據類型 [完整性約束條件…];ALTER TABLE 表名 CHANGE 舊字段名 新字段名 舊數據類型 [完整性約束條件…];ALTER TABLE 表名 CHANGE 舊字段名 新字段名 新數據類型 [完整性約束條件…];#創建表
create table t01(id int primary key auto_increment,name char(16),age int,gender enum("male","female"),hobbies set("read","music","running"),birth date,birth_year year,create_time datetime,class_record time
)engine=innodb;# 修改表名 rename
alter table t01 rename test01;# 添加字段 add# 單字段添加[默認從后追加]
alter table test01 add username char(16);# 多字段添加[默認從后追加]
alter table test01 add password char(16),add email char(32);# 指定位置添加字段# 添加字段到第一個位置
alter table test01 add summary text first;# 添加字段到某個字段的后面
alter table test01 add mobile char(11) after id;# 指定多個位置添加
alter table test01 add id_number int first,add ssn int after summary;s# 刪除字段 drop
alter table test01 drop id_number;# 修改字段[類型,約束] modify
alter table test01 modify ssn char(16);# 修改字段名稱[保留原來類型和約束]
alter table test01 change summary biref text;# 修改字段名稱并缺修改數據類型和約束
alter table test01 change age height float(3,2) not null default 1.80;
二十八、mysql之復制表
# 復制表
create table book_copy select * from book; # 復制表結構 + 記錄 (key不會復制: 主鍵、外鍵和索引)# 如果想要key我們可修改
alter table book_copy modify id int primary key auto_increment;# 只復制表結構(key不會復制: 主鍵、外鍵和索引)
create table book_copy_desc select * from book where 1=0; # 條件為假,查不到任何記錄# 復制表結構包括key(不復制記錄)
create table emp01 like emp;
二十九、mysql刪除表
drop table 表名;
三十、mysql之插入數據
# 1.出入完整數據(順序插入)
語法一：
insert into 表名(字段1,字段2,字段3,...字段n) values(value1,value2,value3,...valuen);
語法二:
insert into 表名 values(value1,value2,value3,...valuen); # value包括主鍵id# 2.指定字段插入數據
語法:
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3)# 3.插入多條記錄
語法：
insert into 表名 values
(值1,值2,值3),
(值1,值2,值3),
(值1,值2,值3),
(值1,值2,值3);# 4.插入查詢結果
語法:
insert into 表名(字段1,字段2,字段3) select (字段1,字段2,字段3) from 表2 where 條件;#create table test01(id int primary key auto_increment,username char(16),password char(16)
)engine=innodb;# 正序插入
insert into test01(id,username,password) values(1,"allen","19891213");# 非正序插入
insert into test01(password,username,id) values("19891213","kevin",2);# 插入全部
insert into test01 values(3,"collins","19891213");# 指定字段插入
insert into test01(username,password) values("mark","19891213");# 插入多條記錄
insert into test01 values(5,"mike","19891213"),(6,"lily","19891213");# 插入查詢結果
create table t01 like test01;insert into t01 values(7,"zens","19891213"),(8,"dived","19891213");insert into test01 select * from t01 where id > 6;
三十一、mysql之更新數據
# 語法
update 表名 set 字段1=值1,...字段n=值n where CONDITION[條件] # 不加條件默認修改整個表update test01 set username='hello' where username='mark';
三十二、mysql之刪除數據
# 語法
delete from 表名 where CONDITION[條件];delete from mysql.user where password='';delete from test01 where id<2;
三十三、mysql單表查詢語法和優先級
# 單表查詢完整語法!!!
select distinct 字段1,字段2,...字段n from 表名 where 分組前過濾條件 group by 分組字段 having 分組后過濾條件order by 排序字段limit 限制條數;
# 關鍵字執行優先級!!!
from where group by having select distinct order by limit# 準備數據
company.employee
員工id id int
姓名 emp_name varchar
性別 sex enum
年齡 age int
入職日期 hire_date date
崗位 post varchar
職位描述 post_comment varchar
薪水 salary double
辦公室 office int
e部門編號 dep_id intcreate table employee(id int primary key auto_increment,name char(16),age int,gender enum("male","female"),hire_date date,post char(16),post_comment char(32),salary float,office int,dep_id int
)engine=innodb;insert into employee(name,gender,age,hire_date,post,salary,office,dep_id) values
('egon','male',18,'20170301','foreign office',7300.33,401,1),
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('cl','male',48,'20101111','teacher',10000,401,1),
('yy','female',48,'20150311','sale',3000.13,402,2),
('yaya','female',38,'20101101','sale',2000.35,402,2),
('dd','female',18,'20110312','sale',1000.37,402,2),
('xx','female',18,'20160513','sale',3000.29,402,2),
('gg','female',28,'20170127','sale',4000.33,402,2),
('zy','male',28,'20160311','operation',10000.13,403,3),
('cyj','male',18,'19970312','operation',20000,403,3),
('cyy','female',18,'20130311','operation',19000,403,3),
('cyt','male',18,'20150411','operation',18000,403,3),
('cy','female',18,'20140512','operation',17000,403,3);
三十四、mysql單表查詢之簡單查詢
select * from employee;
select name,gender,age,hire_date,post,salary,office,dep_id from employee;
select name,salary from employee;
三十五、mysql單表查詢之四則運算查詢
# 通過四則運算查詢
select name,salary*12 from employee;select name,salary*12 as annual_salary from employee; # as 設置別名select name,salary*12 annual_salary from employee; # 可以省略asselect name,age+1 from employee;
三十六、mysql單表查詢之定義顯示格式
# 定制顯示格式
# concat() 函數用于連接字符串
concat()函數連接字符串
select concat("name: ",name,"salary_annuel",salary*12) as info from employee;# concat_ws() 第一個參數為分隔符
select concat_ws(':',name,salary*12) as info from employee;# case語句(分支)
select (case when name = 'egon' then name when name ='alex' then concat(name,"_DSB") else concat(name,"SB") end) as new_name from employee;
select case when name='egon' then name when name='alex' then concat(name,'_DSB') else concat(name,'SB') end as new_name from employee;
三十七、mysql單表查詢之where
where字句中可以使用：1. 比較運算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之間
3. in(80,90,100) 值是10或20或30
4. like 'egon%'pattern可以是%或_，%表示任意多字符_表示一個字符
5. 邏輯運算符：在多個條件直接可以使用邏輯運算符 and or not# 1.單條件查詢
select name from employee where post='sale';# 2.多條件查詢
select name,salary from employee where post='teacher' and salary>10000;# 3.關鍵字between and 在什么到什么中間
select name from employee where id between 1 and 10;select name from employee where id not between 1 and 10;# 4.關鍵字is null(判斷某個字段是否為null不能用等號,需要使用is)
select name,post_comment from employee where post_comment is null; # 查詢職位描述為空的select name,post_comment from employee where post_comment is not null;# 5.關鍵字IN集合查詢
select name from employee where id in (1,2,3);select name from employee where id not in (1,2,3);# 6.關鍵字like模糊查詢
# 通配符%
select name from employee where name like 'eg%';# 通配符_
select name from employee where name like "_lex";1.查看崗位是teacher的員工姓名、年齡
select name,age from employee where post ="teacher";2.查看崗位是teacher且年齡大于30歲的員工姓名、年齡
select name,age from employee where age>30;3. 查看崗位是teacher且薪資在9000-1000范圍內的員工姓名、年齡、薪資
select name,age,concat("salary:",salary*12) as salary from employee where salary between 9000 and 10000;4. 查看崗位描述不為NULL的員工信息
select * from employee where post_comment is not null;5. 查看崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資
select name,age,salary from employee where salary in (10000,9000,30000);6. 查看崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資
select name,age,salary from employee where salary not in (10000,9000,30000);7. 查看崗位是teacher且名字是jin開頭的員工姓名、年薪
select name,salary from employee where name like "jin%";
三十八、mysql單表查詢之distinct
# distinct 對查詢結果去重
select distinct post from employee;select distinct dep_id from employee;
三十九、mysql單表查詢之group_by
# 首先明確group by 是在 where 之后使用的
# 注意:可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，
# 如果想查看組內信息，需要借助于聚合函數sql_mode:ONLY_FULL_GROUP_BY
# ONLY_FULL_GROUP_BY的語義就是確定select target list中的所有列的值都是明確語義，簡單的說來，
# 在ONLY_FULL_GROUP_BY模式下，target list中的值要么是來自于聚集函數的結果，
# 要么是來自于group by list中的表達式的值# 設置sql_mole如下操作(我們可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
--------------------------------------------------------------------------------------------
# 單獨使用 group by 關鍵字分組
select post from employee group by post;# group by 和 group_concat()函數使用select post,group_concat(name) from employee group by post;select post,group_concat(salary*12) as salary from employee group by post;# group by 和聚合函數使用
select post,sum(salary) from employee group by post; # 每個職位的總薪水# 聚合函數
# 強調：聚合函數聚合的是組的內容，若是沒有分組，則默認一組,也就是一張表作為一個分組
sum count max min avgselect count(*) from employee; # 所有員工的數量select max(salary) from employee; # 工資最高的員工select min(salary) from employee; # 工資最低的員工select avg(salary) from employee; # 所有員工的平均工資select sum(salary) from employee; # 所有員工的總工資# 練習
1. 查詢崗位名以及崗位包含的所有員工名字
select post,group_concat("member:",name) as member from employee group by post;2. 查詢崗位名以及各崗位內包含的員工個數
select post,count(id) as emp_count from employee group by post;3. 查詢公司內男員工和女員工的個數
select gender, count(id) from employee group by gender;4. 查詢崗位名以及各崗位的平均薪資
select post,avg(salary) from employee group by post;5. 查詢崗位名以及各崗位的最高薪資
select post,max(salary) from employee group by post;6. 查詢崗位名以及各崗位的最低薪資
select post,min(salary) from employee group by post;7. 查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資
select gender,avg(salary) from employee group by gender;
四十、mysql單表查詢之having
#1.Where 發生在分組group by之前，因而Where中可以有任意字段，但是絕對不能使用聚合函數
#2.Having發生在分組group by之后，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數1. 查詢各崗位內包含的員工個數小于2的崗位名、崗位內包含員工名字、個數
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;2. 查詢各崗位平均薪資大于10000的崗位名、平均工資
select post,avg(salary) from employee group by post having avg(salary)>10000;3. 查詢各崗位平均薪資大于10000且小于20000的崗位名、平均工資
select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000;
四十一、mysql單表查詢之order by
# order by
# 語法
select * from employee order by salary; # 默認是升序# 升序
select * from employee order by salary asc;# 降序
select * from employee order by salary desc;# 按多列排序:先按照age排序，如果年紀相同，則按照薪資排序
select * from employee order by age asc,salary desc;1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序
select * from employee order by age asc,hire_date desc;2. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結果按平均薪資升序排列
select post,avg(salary) from employee group by post having avg(salary) >10000 order by avg(salary) asc;3. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結果按平均薪資降序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
# regexp
# 查詢ale開頭的員工
select * from employee where name regexp '^ale';# 查詢on結尾的員工
select * from employee where name regexp 'on$';# 查詢名字有來兩個m的員工
select * from employee where name regexp 'm{2}';1.查看所有員工中名字是jin開頭，n或者g結尾的員工信息
select * from employee where name regexp '^jin.*[ng]$';
四十四、mysql多表查詢之join
# 準備數據
create table dep(id int primary key auto_increment,name char(32))engine=innodb;create table emp(id int primary key auto_increment,name char(16),gender enum("male","female"),age int,dep_id int)engine=innodb;insert into dep values
(200,'technology'),
(201,'Human Resources'),
(202,'Sales'),
(203,'Operation');insert into emp(name,gender,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204);
--------------------------------------------------------------------------------------------
# 語法
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;1.交叉連接
# 不適用任何匹配條件,生成笛卡爾積
select * from dep,emp;2.內連接:只連接匹配的行
select * from emp inner join dep on emp.dep_id=dep.id;
# 建議：使用join語句時，小表在前，大表在后3.外鏈接之左連接:優先顯示左表全部記錄
select * from dep left join emp on dep.id=emp.dep_id;4 外鏈接之右連接:優先顯示右表全部記錄
select * from dep right join emp on dep.id=emp.dep_id;5 全外連接:顯示左右兩個表全部記錄(利用union去重特性)
select * from dep left join emp on dp.id=emp.dep_id union select * from dep right join emp on dep.id=emp.dep_id;6 內連接(NATURAL JOIN):自連接的表要有共同的列名字
# 準備表
create table country(id int primary key auto_increment,name var(32),code int);
create table city(id int primary key auto_increment,name varchar(32),countrycode int);
create table countrylanguage(id int primary key auto_increment,name varchar(32),code int);
通過自連接查詢city所屬國家的語言
select city.name,countrylanguage.name from city natural join countrylanguage;
# 關聯相同的字段
四十五、mysql多表查詢之union
# union 將表進行上下拼接去重
select * from dep union select * from dep;# union all 將表進行上下拼接不去重
select * from dep union all select * from dep;
四十六、mysql多表條件查詢
# 以內連接的方式查詢employee和department表，并且employee表中的age字段值必須大于25,即找出年齡大于25歲的員工以及員工所在的部門
select * from emp inner join dep on emp.dep_id=dep.id where age>25;# 以內連接的方式查詢employee和department表，并且以age字段的升序方式顯示
select * from emp inner join dep on emp.deo_id=dep.id order by age asc;
四十七、mysql之子查詢
# 1.自查詢是將一個查詢語句嵌套在另一個查詢語句中
# 2.內層查詢語句的查詢結果,可以為外層查詢語句提供查詢條件
# 3.子查詢中可以包含:in、not in、any、all、exists、not exists等關鍵字
# 4.還可以包含比較運算符:=、!=、>、<等
---------------------------------------------------------------------------------------------
1.帶in關鍵字的子查詢
# 查詢平均年齡在25歲以上的部門名
select name from dep where id in (select dep_id from employee where age>25);
# 子查詢一定加上括號# 查看技術部員工姓名
select name from emp where dep_id = (select id from dep where name='technology');
select name from emp dep_id in (select id from dep where name='technology');# 查看不足1人的部門名(子查詢得到的是有人的部門id)
select name from dep where id in (select dep_id from emp group by dep_id having count(id)<1);2.帶比較運算的子查詢
# 比較運算符：=、!=、>、>=、<、<=、<># 查詢大于所有人平均年齡的員工名與年齡
select name,age from emp where age > (select avg(age) from emp);#查詢大于部門內平均年齡的員工名、年齡
select name,age from emp as t1 inner join (select dep_id,avg(age) as avg_age from emp group by dep_id) as t2 on t1.dep_id=t2.dep_id where t1.age>t2.avg_age;3.帶exists關鍵字的子查詢
# EXISTS關字鍵字表示存在 在使用EXISTS關鍵字時 內層查詢語句不返回查詢的記錄
# 而是返回一個真假值 True或False# dep表中存在dept_id=203，Ture
select * from emp where exists (select id from dep where id=203);# department表中存在dept_id=205，False
select * from emp where exists (select id from dep where id=205);# 練習
company.employee員工id id int 姓名 emp_name varchar性別 sex enum年齡 age int入職日期 hire_date date崗位 post varchar職位描述 post_comment varchar薪水 salary double辦公室 office int部門編號 depart_id intcreate table emp(id int primary key auto_increment,name char(16),gender enum("male","female"),age int,hire_date date,post char(16),post_comment text,salary float,office int,dep_id int
)engine=innodb;insert into emp(name,gender,age,hire_date,post,salary,office,dep_id) values
('egon','male',18,'20170301','foreign office',7300.33,401,1),
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('cehnglong','male',48,'20101111','teacher',10000,401,1),
('yaiyai','female',48,'20150311','sale',3000.13,402,2),
('yaya','female',38,'20101101','sale',2000.35,402,2),
('dingding','female',18,'20110312','sale',1000.37,402,2),
('xingxing','female',18,'20160513','sale',3000.29,402,2),
('gege','female',28,'20170127','sale',4000.33,402,2),
('zhangye','male',28,'20160311','operation',10000.13,403,3),
('chenyajin','male',18,'19970312','operation',20000,403,3),
('chenyayin','female',18,'20130311','operation',19000,403,3),
('chenyatong','male',18,'20150411','operation',18000,403,3),
('chenyatie','female',18,'20140512','operation',17000,403,3);
# 查詢每個部門最新入職的那位員工# 連表
select * from emp as t1 inner join (select dep_id,max(hire_date) as new_time from emp group by dep_id) as t2 on t1.dep_id=t2.dep_id where t1.hire_date=t2.new_time;select * from emp as t1 inner join (select posdest,max(hire_date) as new_hire from emp group by post) as t2 on t1.post=t2.post where t1.hire_date=t2.new_hire;# 子查詢
select * from emp where id in (select (select id from emp as t2 where t1.post=t2.post order by hire_date desc limit 1) from emp as t1 group by post);
四十八、mysql之pymysql
# 安裝
pip3 install pymysqlimport pymysqlconn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='MYsql891213',database='t001',charset='utf8')# 連接數據庫cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 獲取游標,并將返回值設置為字典sql ='select * from dep;'rows = cursor.execute(sql)print(rows)# 行數# print(cursor.fetchone()) # 取出一個# print(cursor.fetchmany(1)) # 取出指定的數量print(cursor.fetchall())# 取出所有cursor.scroll(1,'absolute')# 絕對移動,相對于一開始的位置往后移動1條cursor.scroll(1,'relative')# 相對移動,相對于你cursor當前的位置往后移動1條print(cursor.fetchall())cursor.close()# 關閉游標conn.close()# 關閉連接# 針對修改的操作每次都需要提交[commit]# 增
sql ='insert into dep(id,name) values(%s,%s)'rows = cursor.execute(sql,(204,'market'))print(cursor.lastrowid)# 獲取當前插入記錄的行數cursor.commit()# 一次插入多行記錄
sql ='insert into dep(id,name) values(%s,%s)'rows = cursor.execute(sql,[(205,'m'),(206,'n')])cursor.commit()# 改
sql ='update dep set name=%s where id=%s'rows = cursor.execute(sql,('x',204))cursor.execute()# 刪
sql 'delete from dep where id=%s'rows = cursor.execute(sql,(204,))cursor.execute()
四十九、mysql之視圖(view)
# 視圖就是通過查詢得到一張虛擬表然后保存下來,下次用的時候直接使用即可# 頻繁使用一張虛擬表,通過創建視圖可以不需要重復查詢# 語法
create view 視圖表名 as 查詢語句;# 1.在硬盤中,視圖只有表結構文件,沒有表數據文件
# 2.視圖通常是用于查詢,盡量不要修改視圖中的數據(修改視圖中數據會導致原表的數據也會跟著改變)# 創建一張視圖表
create view v_dep as select * from dep;# 查詢視圖
select * from v_dep;# 修改視圖
# 語法 ALTER VIEW 視圖名稱 AS SQL語句
# 本質就是修改sql語句
alter view v_dep as select * from dep where id >200;# 刪除視圖
drop view v_dep;
五十、mysql之觸發器
# 在滿足對表進行【增、刪、改】操作的情況下,會觸發的功能
# 觸發器專門針對我們對某一張表的增刪改的行為,這類行為一旦執行就會觸發觸發器的執行,即自動運行別外一段sql代碼,# 創建觸發器
# 語法：
CREATE TRIGGER 觸發器名 BEFORE/AFTER INSERT/DELETE/UPDATE ON 表名 FOR EACH ROW
BEGINsql代碼
END;# 準備表
create table cmd(id int primary key auto_increment,user char(16),priv char(16),cmd char(16),sub_time datetime,success enum("yes","no")
)engine=innodb;create table errlog(id int primary key auto_increment,err_cmd char(16),err_time datetime
)engine=innodb;# 插入之前
# delimiter // 更換mysql的結束符
delimiter //
create trigger tri_before_insert_cmd before insert on cmd for each row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;# 插入之后
delimiter //
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;# 更新之前
delimiter //
create trigger tri_before_update_cmd before update on cmd for each row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;# 更新之后
delimiter //
create trigger tri_after_update_cmd after update on cmd for eache row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;# 刪除之前
delimiter //
create trigger tri_before_delete_cmd before delete on cmd for each row
begin
if old.success='no' then
insert into errlog(err_cmd,err_time) values(old.cmd,old.sub_tiem)l
end if;
end//
delimiter ;# 刪除之后
delimiter //
create trigger tri_after_delete_cmd after delete on cmd for each row
begin
if old.success='no' then
insert into errlog(err_cmd,err_time) values(old.cmd,old.sub_time);
end if;
end//
delimiter ;
# 注意:NEW表示即將插入的數據行，OLD表示即將刪除的數據行。# 使用觸發器
# 觸發器無法由用戶直接調用，而知由于對表的【增/刪/改】操作被動引發的# 查看觸發器
# 語法
SHOW TRIGGERS
show create trigger 觸發器名# 刪除觸發器
# 語法
drop trigger 觸發器名;drop trigger tri_before_insert_cmd;
五十一、mysql之事務(原子操作)
01.什么是事務
開啟一個事務可以包含一些sql語句,這些sql語句要么同時成功
要么同時失敗,稱之為事務的原子性
02.事務的作用#準備表
create table user(id int primary key auto_increment,name char(16),balance int
)engine=innodb;insert into user(name,balance) values('allen',1000),('kevin',1000),('collins',1000);# 原子操作
# 語法
start transaction;
sql代碼
rollback;
commit;start transaction;
update user set balance=900 where name='allen';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='collins';
rollback; # 回滾意味著回到一面sql語句修改之前
commit; # 提交本次操作[只要不執行commit數據都不會保存到硬盤中]
五十二、mysql存儲過程
# 存儲過程:包含了一系列可真行的sql語句,存儲過程存放與mysql中,通過調用它的名字可以執行起=其內部的一堆sql
# 三種開發模式
1.應用程序:只需要開發應用程序的邏輯mysql:編寫好存儲過程,以供應用程序調用優點:開發效率,執行效率都高缺點:擴展性差2.應用程序:開發應用程序的邏輯和編寫原生sqlmysql:只需正常運行優點:擴展性高缺點:開發效率,執行效率都低3.應用程序:除了開發應用程序的邏輯,不需要編寫原生sql,使用別人編寫好的框架ormmysql:優點:不用再編寫原生sql,開發效率高
# 創建存儲過程
# 語法
delimiter //
# 定義存儲過程
create procedure p1()
begin
sql代碼
end//
delimiter ;
# 調用存儲過程
call p1();
---------------------------------------------------------------------------------------------
# 無參數
delimiter //
create procedure p1()
begin
select * from dep;
end//
delimiter ;call p1();
---------------------------------------------------------------------------------------------
# 有參數
# in 僅用于傳入參數用
# out 僅用于返回值用
# inout 既可以傳入又可以當作返回值# 設置變量
set @x=1;# 查看變量
select @x;delimiter //
create procedure p1(in m int,in n int,out result int)
# (in/out/inout 變量名 聲明數據類型)
begin
select name from dep where id > m and id < n;
set res=1;
end//
delimiter ;
---------------------------------------------------------------------------------------------
# 查看存儲過程
show create procedure p1;
# 查看所有存儲過程
show procedure status;
---------------------------------------------------------------------------------------------
# 如果使用存儲過程
1.在mysql中調用
set @result=0
call p1(1,3,@result); # out/inout必須傳入一個變量,傳一個固定值會報錯
select @result; # 查看返回值2.在python程序中調用
import pymysqlconn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='MYsql891213',database='t001',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p02', (200, 202, 0)) # @_p02_0=200 @_p02_1=202 @_p02_2=0
print(cursor.fetchall())
cursor.execute('select @_p02_2') # 查看返回值結果
print(cursor.fetchall())
cursor.close()
conn.close()
---------------------------------------------------------------------------------------------
# 刪除存儲過程
drop procedure 存儲過程名;drop procedure p1;
---------------------------------------------------------------------------------------------
# 注意:視圖觸發器存儲過程都存放在創建的數據庫中# 存儲過程事務的使用
delimiter //
create procedure x01(out res int)
begin
declare exit handler for sqlexception
begin
set res=1;
rollback;
end;
declare exit handler for sqlwarning
begin
set res=2;
rollback;
end;
start transaction;
update user set balance=900 where name='allen';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='collins';
commit;
set res=0;
end//
delimiter ;
# 定義一個異常處理
# 注意:declare...handler語句必須出現在變量或條件聲明的后面
語法 DECLARE condition_name CONDITION FOR [condition_type]
condition_name參數表示異常的名稱
condition_type參數異常類型
# 基本格式
# 未命名
BEGINDECLARE CONTINUE HANDLER FOR 1051
END;
# 有命名
BEGINDECLARE no_such_table CONDITION FOR 1051;DECLARE CONTINUE HANDLER FOR no_such_table
END;
# 異常處理
DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement
handler_type: CONTINUE|EXIT|UNDO
handler_type為錯誤處理方式，參數為3個值之一；
CONTINUE表示遇到錯誤不處理，繼續執行；
EXIT表示遇到錯誤時馬上退出；
UNDO表示遇到錯誤后撤回之前的操作，MySQL暫不支持回滾操作
# 作用域
begni..end內，哪果錯誤處理定義在begin ... end內，則在該begin...end之外的錯誤不會被捕獲。
它能夠捕獲其它儲過程的錯誤。
---------------------------------------------------------------------------------------------
condition_value: SQLSTATE [VALUE] sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
condition_value表示錯誤類型；SQLSTATE [VALUE] sqlstate_value為包含5個字符的字符串錯誤值；
condition_name表示DECLARE CONDITION定義的錯誤條件名稱；
SQLWARNING匹配所有以01開頭的SQLSTATE錯誤代碼；
NOT FOUND匹配所有以02開頭的SQLSTATE錯誤代碼；
SQLEXCEPTION匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯誤代碼；
mysql_error_code匹配數值類型錯誤代碼
---------------------------------------------------------------------------------------------
# 例子
//方法一：捕獲sqlstate_value異常
//這種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為"42S02"，執行CONTINUE操作，并輸出"NO_SUCH_TABLE"信息
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';//方法二：捕獲mysql_error_code異常
//這種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，執行CONTINUE操作，并輸出"NO_SUCH_TABLE"信息；
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';//方法三：先定義條件，然后捕獲異常
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';//方法四：使用SQLWARNING捕獲異常
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';//方法五：使用NOT FOUND捕獲異常
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';//方法六：使用SQLEXCEPTION捕獲異常
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
五十五、mysql之流程控制(條件語句)
# 大前提mysql中的函數只能在sql語句中使用# if
delimiter //
create procedure p01()
begin
declare i int default 0;
# 聲明一個變量為整形默認值為0
if i =1 then
select 1;
elseif i=2 then
select 2;
else
select 3;
end if;
end//
delimiter ;# case
select
(case when name='allen' then namewhen name='kevin' thenconcat('hey',name)elseconcat('hello',name)end) from user;
五十六、mysql之流程控制(循環語句)
# 大前提mysql中的函數只能在sql語句中使用
delimiter //
create procedure auto_insert()
begin
declare i int default 1;
while (i<100001)do
insert into bench_index values(i,concat('allen',i),18,concat('allen',i,'@live.com'));
set i=i+1;
end while;
end//
delimiter ;
五十七、mysql之date_format
create table blog(id int primary key auto_increment,name char(16),sub_time datetime
)engine=innodb;# date_format格式化時間
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
五十八、mysql之set
# set
# 語法
# set語句可用于向系統變量或用戶變量賦值
SET @var_name = expr [, @var_name = expr]# 使用select語句來定義：
SELECT @var_name := expr [, @var_name = expr] ...select t1.id,t1.name,t2.ct from category as t1 inner join (select category_id,count(id) as ct from article where user_id=1 group by category_id) as t2 on t1.id=t2.category_id;select t1.id,t1.name,t2.ct from tag as t1 inner join (select tag_id,count(id) as ct from article_tag group by tag_id) as t2 on t1.id=t2.tag_id where t1.user_id=1;select t1.title,t1.summary,t1.status,t2
select * from tag where id=1;
t1.id,t1.title,t1.summary,t1.like_num,t1.comment_num,t3.name,t1.status,t1.is_hot,t1.create_time from article as t1 inner join (select article_id from article_tag where tag_id=1) as t2 on t1.id=t2.article_id inner join (select id,name from category) as t3 on t1.category_id=t3.id;select t1.id,t1.title,t1.summary,t1.like_num,t1.comment_num,t3.name,t1.status,t1.is_hot,t1.create_time from article as t1 inner join (select article_id from article_tag where tag_id=1) as t2 on t1.id=t2.article_id inner join (select id,name from category) as t3 on t1.category_id=t3.id;select t1.id,t1.title,t1.summary,t1.like_num,t1.comment_num,t2.name,t1.status,t1.is_hot,t1.create_time from article as t1 inner join category as t2 on t1.category_id=t2.id where date_format(t1.create_time,"%Y-%m")=%s
五十九、mysql之do
do sleep(5) # 睡眠5秒
SELECT c.Score, b.Rank FROM Scores c INNER JOIN ( SELECT Score, ( @i := @i + 1 ) AS Rank FROM ( SELECT Score FROM Scores GROUP BY Score ORDER BY Score DESC ) a, ( SELECT @i := 0 ) AS it ) b ON c.Score = b.Score ORDER BY c.Score DESC
六十、mysql之if、ifnull、nullif
ifnull
# MySQL IFNULL函數是MySQL控制流函數之一，它接受兩個參數，如果不是NULL，則返回第一個參數。 否則，IFNULL函數返回第二個參數
# 例子
select ifnull(1,0);
--return 1
select ifnull('',0);
--return 0
nullif
# NULLIF函數是接受2個參數的控制流函數之一。如果第一個參數等于第二個參數，則NULLIF函數返回NULL，否則返回第一個參數
# 例子
select nullif(1,1);
--return null
select nullif(1,2);
--return 1
if
# 在mysql中if()函數的用法類似于java中的三目表達式，其用處也比較多，具體語法如下:IF(expr1,expr2,expr3)，如果expr1的值為true，則返回expr2的值，如果expr1的值為false,則返回expr3的值
# 列子
select name,if(gender=0,'male','female') as gender from g01;
六十一、mysql之創建函數
# 格式
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGINset N=N-1;RETURN (# Write your MySQL query statement below.select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset N),null));
END
1. 首先進入本機的源文件目錄
cd /usr/local/src2. 使用wget下載官方yum源的rpm包：
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm3. 安裝rpm包：
rpm -ivh mysql57-community-release-el7-11.noarch.rpm4. 再次使用yum來安裝mysql-server:
yum install -y mysql-server
可以看到這次不再提示安裝Mariadb了5. 安裝完成后，啟動mysqld服務：
systemctl start mysqld查看是否成功啟動：
systemctl status mysqld
ps aux|grep mysqld6. 設置mysqld服務開機自啟動：
systemctl enable mysqld7. 使用初始密碼登錄
由于MySQL從5.7開始不允許首次安裝后，使用空密碼進行登錄，系統會隨機生成一個密碼以供管理員首次登錄使用，這個密碼記錄在/var/log/mysqld.log文件中，使用下面的命令可以查看此密碼：
cat /var/log/mysqld.log|grep 'A temporary password'2017-11-12T13:35:37.013617Z 1 [Note] A temporary password is generated for root@localhost: bkv,dy,)o7Ss最后一行冒號后面的部分bkv,dy,)o7Ss就是初始密碼。
使用此密碼登錄MySQL:mysql -u root -p
8. 更改默認密碼:
set password for 'root'@'localhost'=password('PASSWORD');
六十四、mysql恢復root賬號
# 解決誤操作刪除root賬號時,恢復(1)mysqld_safe --skip-grant-tables --skip-networking &# &表示后臺運行# 創建一個用戶 insert into mysql.user values ('localhost','root',PASSWORD('123'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N');(2)# 創建root用戶并設置授權權限 - 加上with grant optiongrant all on *.* to root@'localhost' identified by '123' with grant option;
1.什么是SQL結構化的查詢語句
2.sql的種類
# DDL(Data Definition Language):數據定義語言庫對象:庫名字、庫屬性開發規范庫名小寫(1) create創建庫:create database|schema# 規范的建庫語句create database if not exists db01 character set=utf8 collate=utf8_general_ci;if not exists:表示數據庫不存在時創建,否則不做操作character set:指定字符集show charset; # 查看所有字符集collate:校驗規則ci:大小寫不敏感cs|bin:大小敏感show collation; # 查看所有校驗規則創建表:create table# 例子create table t01(id int primary key auto_increment)engine=innodb;(2) alter修改定義的庫alter database# 例子alter database db01 charset gbk; # 修改字符編碼修改定義的表alter table# 例子alter table t01 add name char(16);(3) drop 刪除定義的庫drop database# 例子drop database db01;刪除定義的表drop table# 例子drop table t01;
# DCL(Data Control Language):數據控制語言,針對權限進行控制(1) grant # 授權# 例子授權root@10.0.0.1用戶所有權限(非超級管理員)grant all on *.* to root@'10.0.0.1' identified by 'PASSWORD';授權超級管理員grant all on *.* to root@'10.0.0.1' identified by 'PASSWORD' with grant option;其他參數(擴展)max_queries_per_hour：一個用戶每小時可發出的查詢數量max_updates_per_hour：一個用戶每小時可發出的更新數量max_connetions_per_hour：一個用戶每小時可連接到服務器的次數max_user_connetions：允許同時連接數量# 語法grant 權限... on 庫.表 用戶[@'host'] [identified by 'PASSWORD']# 限制級別單數據:on db01.*單數據庫單表:on db01.t01單數據庫單表單字段:grant select(name) on db01.t01(2) revoke # 收回權限# 例子收回select權限revoke select on *.* root@'10.0.0.1';查看權限show grant for root@'10.0.0.1';語法revoke 權限... on 用戶[@'host'];
# DML(Data Manipulation Language):數據操縱語言(1) insert # 新增記錄# 例子常規用法,插入數據insert into t01 values(1,'allen',18,'male');規范用法,插入數據insert into t01(name,age,gender) values('kevin',18,'male');插入多條數據insert into t01(name,age,gender) values('collins',18,'female'),('lily',18,'female');(2) update # 更新記錄# 例子不規范update t01 set name='mike'規范uodate修改update t01 set name='mike' where id=10;如果要修改全部update t01 set name='mike' where 1=1;(3) delete # 刪除流# 例子不規范delete from t01;規范刪除(危險)delete from t01 where id=1;DDL刪除表truncate table t01;# 注意:不推薦直接刪除數據(1) 使用update作為刪除,添加一個狀態字段(2) 使用觸發器 trigger
# DQL(Data Query Language):數據查詢語言(1) select# 完整語法select distinct field... from TABLE where CONDITION group by field having CONDITION order by field [asc|desc] limit num[,num|offset num];# 連表查詢 join:natural join:自連接inner join: 內連接left join:左連接right join:右連接union:union:去重合并union all:不去重合并MySQL 提供了一個 EXPLAIN 命令, 它可以對 SELECT 語句進行分析, 并輸出 SELECT 執行的詳細信息, 以供開發人員針對性優化# 例子explain select name from t01 where id=1;explain等級(null,system,const,eq_ref,ref,range,index,all) # 查詢速度從高到低all : 即全表掃描index : 按索引次序掃描，先讀索引，再讀實際的行，結果還是全表掃描，主要優點是避免了排序。因為索引是排好的。range:以范圍的形式掃描。explain select * from a where a_id > 1\Gref:非唯一索引訪問(只有普通索引)create table a(a_id int not null, key(a_id));insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);explain select * from a where a_id=1\Geq_ref:使用唯一索引查找(主鍵或唯一索引)const:常量查詢在整個查詢過程中這個表最多只會有一條匹配的行，比如主鍵 id=1 就肯定只有一行，只需讀取一次表數據便能取得所需的結果，且表數據在分解執行計劃時讀取。當結果不是一條時，就會變成index或range等其他類型system:系統查詢null:優化過程中就已經得到結果，不在訪問表或索引
六十八、mysql之主從復制(單機多實例)
# 環境準備[mysql3006]port=3306
[mysql3007]port=3307
[mysql3008]port=3308
[mysql3009]port=3309
# 主庫操作[mysql3006]1、修改配置文件vim /etc/my.cnf[mysqld]#主庫server_id為1,server_id不能與從庫重復server_id=1#開啟binlog日志log_bin=mysql-bin2、重啟主庫mysqlsystemctl restart mysqld3、查看Positionshow master status;# 記錄 Position3、創建主從復制用戶grant replication slave on *.* to rep@'%' identified by 'MYsql@891213';flush privileges;# 從庫操作[mysql3007]修改配置文件vim /etc/my.cnf[mysqld]#主庫server_id為1,從庫server_id不能與主庫重復server_id=7[mysql3008]修改配置文件vim /etc/my.cnf[mysqld]#主庫server_id為1,從庫server_id不能與主庫重復server_id=8[mysql3009]修改配置文件vim /etc/my.cnf[mysqld]#主庫server_id為1,從庫server_id不能與主庫重復server_id=91、設置從庫與主庫進行通信# 格式CHANGE MASTER TO MASTER_HOST='master_host_name',MASTER_USER='replication_user_name',MASTER_PASSWORD='replication_password',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position;# 執行change master(創建IO,SQL線程)CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='rep',MASTER_PASSWORD='MYsql@891213',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;2、啟動主從start slave;# stop slave; 關閉主從3、查看從庫狀態show slave status;# 顯示如下表示主從啟動成功Slave_IO_Running: YesSlave_SQL_Running: Yes
# 注意開啟主從復制前,對主庫做全備份,保證主從數據一致性# 主庫mysqldump -A -uroot -p > /tmp/full.sql# 從庫source /tmp/full.sql
# 軟件介紹
MHA能夠在較短的時間內實現自動故障檢測和故障轉移，通常在10-30秒以內;在復制框架中，MHA能夠很好地解決復制過程中的數據一致性問題，由于不需要在現有的replication中添加額外的服務器，僅需要一個manager節點，而一個Manager能管理多套復制，所以能大大地節約服務器的數量;另外，安裝簡單，無性能損耗，以及不需要修改現有的復制部署也是它的優勢之處。MHA還提供在線主庫切換的功能，能夠安全地切換當前運行的主庫到一個新的主庫中(通過將從庫提升為主庫),大概0.5-2秒內即可完成。MHA由兩部分組成：MHA Manager（管理節點）和MHA Node（數據節點）。MHA Manager可以獨立部署在一臺獨立的機器上管理多個Master-Slave集群，也可以部署在一臺Slave上。當Master出現故障時，它可以自動將最新數據的Slave提升為新的Master,然后將所有其他的Slave重新指向新的Master。整個故障轉移過程對應用程序是完全透明的。
# 工作流程
(1) 把宕機的master二進制日志保存下來
(2) 找到binlog位置點最近的slave(可以固定指定某一臺salve)
(3) 在binlog位置點最近的salve上用的relay.log(和master差異日志)修復其他slave
(4) 將宕機的master上保存下來的binlog二進制日志恢復到與其位置點最近的slave上
# 為什么不先把宕機的master的binlog日志恢復到與其位置點最近的slave上呢,因為內存命中率
# 例如:位置點最近slave數據庫量是:70%,其他slave的數據量是60%,相差10%,如果先從binlog日志恢復位置點最近slave,此時位置點最近slave數據量為100%,與其他slave數據量相差40%,
(5) 將含有最新位置點binlog所在的slave提升為master
(6) 將其他slave重新指向新提升的master,并開啟主從復制
# mha工具
MHA軟件由兩部分組成,Manager工具包和Node工具包
1.Manager工具包主要包括:masterha_check_ssh #檢查MHA的ssh-keymasterha_check_repl #檢查主從復制情況masterha_manger #啟動MHAmasterha_check_status #檢測MHA的運行狀態masterha_master_monitor #檢測master是否宕機masterha_master_switch #手動故障轉移masterha_conf_host #手動添加server信息masterha_secondary_check #建立TCP連接從遠程服務器masterha_stop #停止MHA
2.Node工具包主要包括:save_binary_logs #保存宕機的master的binlogapply_diff_relay_logs #識別relay log的差異filter_mysqlbinlog #防止回滾事件purge_relay_logs #清除中繼日志
# MHA優點總結
(1) Masterfailover and slave promotion can be done very quickly自動故障轉移快
(2) Mastercrash does not result in data inconsistency主庫崩潰不存在數據一致性問題
(3) Noneed to modify current MySQL settings (MHA works with regular MySQL)不需要對當前mysql環境做重大修改
(4) Noneed to increase lots of servers不需要添加額外的服務器(僅一臺manager就可管理上百個replication)
(5) Noperformance penalty性能優秀，可工作在半同步復制和異步復制，當監控mysql狀態時，僅需要每隔N秒向master發送ping包(默認3秒)，所以對性能無影響。你可以理解為MHA的性能和簡單的主從復制框架性能一樣。
(6) Works with any storage engine只要replication支持的存儲引擎，MHA都支持，不會局限于innodb
