Day0-MySQL入门到精通

第1章 数据库入门

安装教程： http://jingyan.baidu.com/article/647f0115c1ee9b7f2148a809.html

windws

如果没有配置成功path，手动配置环境变量：path:C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin(默认安装路径)

linux: export PATH=$PATH:/usr/local/MySQL/bin就OK

操作MySQL:

（1）设置MySQL登录密码：root

（2）登录MySQL

 mysql -h localhost -u root -p

（3）查看数据库

  show databases;

（4）退出：exit;

(4)停止/启动mysql服务（Windows）:

cmd进入命令提示符窗口：（不区分大小写）

启动：net start mysql

停止：net stop mysql

Linux下：

如何启动/停止/重启MySQL

　　一、启动方式

　　1、使用 service 启动：service mysqld start

　　2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start

　　3、使用 safe_mysqld 启动：safe_mysqld&

　　二、停止

　　1、使用 service 启动：service mysqld stop

　　2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop

　　3、mysqladmin shutdown

　　三、重启

　　1、使用 service 启动：service mysqld restart

　　2、使用 mysqld 脚本启动：/etc/inint.d/mysqld restart

----------------------------------------------------------------------------------------------------

【例1-1】使用“\s”命令查看数据库信息：

\s

【例1-2】使用“\u”命令切换数据库：

\u test

【例1-3】使用“use”命令进入数据库：

use  test;

【例1-4】查看当前所选的数据库。 ：

select database();

【例1-5】 查看数据库使用端口

show variables  like 'port';

 【例1-6】 查看当前数据库大小

use  information_schema;

select concat(round(sum(data_length)/(1024*1024),2) + round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size'  from tables 

where table_schema='test';

 【例1-7】 查看当前数据所占空间大小

use  information_schema;

select concat(round(sum(data_length)/(1024*1024),2),'MB') as 'DB Size'

from tables

where table_schema='test';

 【例1-8】  查看索引所占的空间大小

use  information_schema;

select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' 

from tables 

where table_schema='test' ;

 【例1-9】查看数据库编码和校队规则（负责某一字符集下的字符进行比较和排序的结果）：

show variables like 'character%';

show variables like 'collation%';

【例1-10】 查看数据库的所有用户信息

select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;

【例1-11】 查看数据库某个用户的权限。

show grants for 'root'@'localhost';

【例1-12】 查看数据库的最大连接数：

show variables like '%max_connections%';

【例1-13】 查看数据库当前连接数、并发数：

show status like 'Threads%';

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。

Threads_created :代表从最近一次服务启动，已创建线程的数量。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

【例1-14】 查看数据文件存放路径

show variables like '%datadir%';

【例1-15】 修改MySQL的字符集编码：

set character_set_client = utf8;

set character_set_database = utf8;

set  character_set_connection = utf8;

set  character_set_server = utf8;

【例1-16 】显示日志文件路径

 show variables like 'general_log_file';

第2章 数据库和表的基本操作

【例2-1】：创建数据库（默认）

CREATE DATABASE mydb1;

CREATE DATABASE mydb2 CHARACTER SET GBK;  

CREATE DATABASE mydb3 CHARACTER SET  UTF8 COLLATE UTF8_BIN;

【例2-2】:显示数据库

SHOW DATABASES;

【例2-3】:显示创建数据库语句：

SHOW CREATE DATABASE mydb2 ;

【例2-4】:修改数据库的字符集为U8 ,并修改校对规则为：utf8_bin

ALTER DATABASE mydb2 DEFAULT CHARACTER SET utf8  COLLATE utf8_bin;

【例2-5】:删除数据库（数据库维护人员不要做轻易删除，删除之前一定要备份）

 DROP DATABASE mydb2 ;

【例2-6】:创建表

#使用库：

USE  mydb3；

#然后建表：

CREATE TABLE employee(id INT,name VARCHAR(20),gender CHAR(2),birthday DATE,salary double,resume TEXT);  

#然后查看表结构：desc  employee;

#然后查数据库所存在的表   show tables;

 #查看建表语句： show create table  employee; 

MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

【例2-7】：修改表

  # 新增一个(image)字段： ALTER  TABLE employee ADD  image  blob;

  #修改一个字段的长度：ALTER  TABLE employee MODIFY  name  VARCHAR(30);

  #删除一个字段：ALTER  TABLE employee DROP gender;

  #修改表名：RENAME  TABLE  employee TO  user;

  #查看表名是否成功：SHOW TABLES;

  #修改表的字符集编码：ALTER  TABLE  user  character set  utf8;

 #修改字段名称：ALTER  TABLE  user change  name  username  VARCHAR(20);

【例2-8】:删除表

DROP TABLE user;

验证：show tables;

【例2-9】:约束

重新创建员工表：CREATE TABLE employee(id INT,name VARCHAR(20),gender CHAR(2),birthday DATE,salary double,resume TEXT);  

#主键约束：不允许重复且不为空。

           单字段主键：DROP TABLE employee;

                 CREATE TABLE employee(id INT PRIMARY KEY,name VARCHAR(20),gender CHAR(2),birthday DATE,salary double,resume TEXT);  

                 DESC employee;

           多字段主键：DROP TABLE employee;

                                  CREATE TABLE employee(id INT,name VARCHAR(20),gender CHAR(2),birthday DATE,salary double,resume TEXT,PRIMARY KEY (id,name ));  

                          DESC employee;

#非空约束: 不为空

                    DROP TABLE employee;

          CREATE TABLE employee(id INT PRIMARY KEY,name VARCHAR(20),gender CHAR(2) NOT NULL,birthday DATE,salary double,resume TEXT);  

          DESC employee;

#唯一约束：不允许重复

                    DROP TABLE employee;

                    CREATE TABLE employee(id INT PRIMARY KEY,name VARCHAR(20) UNIQUE, gender CHAR(2) NOT NULL,birthday DATE,salary double,resume TEXT);  

#默认约束：给字段加默认值

                      DROP TABLE employee;

                       CREATE TABLE employee(id INT PRIMARY KEY,name VARCHAR(20) DEFAULT '张同乐', gender CHAR(2)  NOT NULL ,birthday DATE,salary double DEFAULT 8888.8 ,resume TEXT);  

                      DESC employee;

#外键约束（用来和其他表建立联系用的、表的外键是另一表的主键, 外键可以有重复的, 可以是空值，保证数据的完整性和一致性。在删除有关联的数据的时候，数据库默认会报错进行阻止）

         建立两种方式

                   第一种方式：

                              CREATE TABLE department(id INT PRIMARY KEY, name VARCHAR(50));  

                              CREATE TABLE employee (id INT PRIMARY KEY,  dept_id INT, name VARCHAR(20) , gender CHAR(4)  NOT NULL ,birthday DATE,salary double,resume VARCHAR(20) , FOREIGN KEY(dept_id) REFERENCES department(id));  

                        创建好数据环境：

              第二种方式：

                          CREATE TABLE department(id INT PRIMARY KEY, name VARCHAR(50));  

                CREATE TABLE employee (id INT PRIMARY KEY,  dept_id INT, name VARCHAR(20) , gender CHAR(4)  NOT NULL ,birthday DATE,salary double,resume VARCHAR(20) );  

                         ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id); 

       约束规则：

• RESTRICT(限制)：如果你想删除的那个主表，它的下面有对应从表的记录，此主表将无法删除。

• CASCADE（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删掉。

• SET NULL：将外键设置为空。

• NO ACTION：什么都不做。

        外键使用规则：

           1，在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便，最好用外键。

           2，用外键要适当，不能过分追求

           3，不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。

【例2-10】:自动增长

                CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT ,name VARCHAR(20) UNIQUE, gender CHAR(2) NOT NULL,birthday DATE,salary double,resume TEXT);  

                DESC employee;

【例2-11】:索引  （ 相当于图书的目录，可以根据目录中的页码快速找到所需的内容）

  术语解释： 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

            优点：可以提高查询速度。

            缺点是创建和维护索引需要耗费时间和减慢写入速度.    

    分类：  ( CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT ,name VARCHAR(20), gender CHAR(2) NOT NULL,birthday DATE,salary double,resume TEXT); )

     #普通索引： 这是最基本的索引，它没有任何限（ MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引）

                    必须用不出错     方式1(直接创建索引)：CREATE INDEX index_name ON table(column(length))

             实例： CREATE INDEX index_name ON employee(name);

             验证：show create table employee;   

                       删除索引：DROP INDEX index_name ON employee;  // ALTER TABLE employee DROP INDEX index_name;

                      方式2(修改表 结构的方式添加索引)  ALTER TABLE table_name ADD INDEX index_name (column(length))

                       实例：  ALTER TABLE employee  ADD INDEX index_name (name);

             方式3（创建表的时候创建索引）

                       DROP TABLE employee;

                       CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT ,name VARCHAR(20)  , gender CHAR(2),birthday DATE,salary double,resume TEXT,INDEX index_name (name(10)));  

               验证：explain  select * from  employee where name ='';

     #唯一性索引：1、创建表的同时创建唯一索引

                              DROP TABLE employee;   

                   CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT ,name VARCHAR(20), gender CHAR(2) NOT NULL,birthday DATE,salary double,resume TEXT, UNIQUE INDEX index_name (name(10) ASC) );  

                  2、直接创建索引，推荐使用

                   删除索引：DROP INDEX index_name ON employee;  // ALTER TABLE employee DROP INDEX index_name;

                   CREATE UNIQUE INDEX index_name ON employee(name(10) ASC );

              3、修改表结构创建索引

                    删除索引：DROP INDEX index_name ON employee;  // ALTER TABLE employee DROP INDEX index_name;

                ALTER TABLE employee ADD UNIQUE index_name (name(10) ASC);

                               验证：show create table employee;  

      #全文索引 （语法同上， 只有MyISAM支持全文，和mysql5.6的 InnoDB支持全文索引）  FULLTEXT

      # 组合索引  （ 就是只从最左面的开始组合） ALTER TABLE employee ADD INDEX index_name_salary(name ,salary)     //索引只有name   和 name_salary 并没有salary

      #空间索引 ：CREATE  TABLE ky(id INT,space Geometry NOT NULL,SPATIAL INDEX  sindex_space(space )) ENGINE = MyISAM;         

 创建规则：

 表的主键、外键必须有索引；

 数据量超过300的表应该有索引；

 经常与其他表进行连接的表，在连接字段上应该建立索引

  经常出现在Where子句中的字段，特别是大表的字段，应该建立索引

  索引应该建在选择性高的字段上；

  索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

  复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

                                   A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

                   B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

                   C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

                   D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

                   E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

  频繁进行数据操作的表，不要建立太多的索引；

 删除无用的索引，避免对执行计划造成负面影响；以上是一些普遍的建立索引时的判断依据。

  备注：一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

比较经典的总结：http://blog.csdn.net/xluren/article/details/32746183

第三章

单表记录的crud （增、删、改、查）

创建好数据环境：外键约束地方。

【例3-1】 增：

插入的三种方式：

1、字段全部插入可以这么写

INSERT INTO employee VALUES ('6', '1', '李六', '男', '1980-04-30', '20000', '教授');

2、插入部分字段：

INSERT INTO employee (id,name,gender) VALUES (7, '宋七', '男');

 3、新增多条数据

INSERT INTO employee VALUES ('8', '3', '胡巴', '男', '1988-05-25', '30000', '专家'),('9', '2', '谢九', '男', '1987-05-11', '40000', '科学家')；

【例3-2】 删：

delete from employee where name='张三';（删除表中名称为张三的记录）

delete from employee;   删除表中所有记录。

删除(删除某个字段为空的列)

delete from table where column is null;

替换（建议能以更新方式的删除效果最好！）

update table

set column=''

where column is null

TRUNCATE TABLE  employee;  清空表中所有记录。   //数据量比比较大的时候，效率是非常高的。

【例3-3】 改        

UPDATE employee SET     salary =5000;    (把所有员工的工资都改成5000元)

UPDATE employee SET     salary =3000 WHERE name='张三';    (把张三的工资改成3000元)

UPDATE employee SET     salary =8000,resume='博士'  WHERE name='张三';  (把张三的工资改成8000元，简历改为博士)

UPDATE employee SET     salary = salary+1000  WHERE name='张三'; (把张三的工资增加一千块钱)

【例3-4】 查：

  SELECT * FROM employee; (查询所有员工信息)

  SELECT  name,salary   FROM employee;      (查询所有员工的姓名和对应的薪资)

  SELECT  DISTINCT  salary   FROM employee;     (过滤表中重复数据))

  SELECT name  salary+10   FROM employee;(在所有员工薪资显示+10元加班费)

  SELECT  *  FROM employee WHERE name='张三';（查询员工张三的详细信息）

  SELECT  *  FROM employee WHERE salary >5000;（查询工资大于5000的所有员工信息）

执行创建好数据环境：employee.sql

   SELECT * FROM employee WHERE salary BETWEEN 4500 AND 8000;  （查询工资4500-8000的员工信息）

   SELECT * FROM employee WHERE salary IN (3500,6000,9000);（查询工资3500,6000,9000 的员工信息）

   INSERT INTO employee VALUES ('6', '1', '李六六', '男', '1980-04-30', '20000', '教授');     SELECT * FROM employee WHERE  name   like '李%'; （查询姓李的员工的信息)

   SELECT * FROM employee WHERE  name   like '李__'; （查询姓李xx员工的信息)

   SELECT * FROM employee WHERE salary >5000 AND resume ='博士'  ;   （查询工资大于5000并且简历等于博士的)

【例3-5】 聚合函数：

  SELECT COUNT(*) FROM employee;   （查公司有多少员工)

  SELECT sum(salary ) AS  总金额  FROM employee;(使用别名显示一个月公司给员工发多少钱)  //SELECT sum(salary) 总金额  FROM employ

  AVG() 平均 MAX() 最大值，MIN() 最小值

【例3-6】 排序：

  SELECT  name,salary   FROM employee ORDER  BY salary ; （对员工的薪资进行排序，默认是升序，为ASC）

  SELECT  name,salary   FROM employee ORDER  BY salary  DESC; （对员工的薪资进行排序为倒叙排序，为DESC）

  SELECT  name,salary   FROM employee  WHERE  name LIKE '李%' ORDER  BY salary  DESC;(带条件的排序，对姓李的员工的薪资进行排序为倒叙排序，为DESC )

【例3-7】 分组：

SELECT * FROM employee GROUP BY gender;（ 按性别进行分组。如果单独使用GROUP BY关键字，查询结果只显示一个分组的第一条记录，意义不大！）

SELECT gender ,GROUP_CONCAT(name) FROM employee GROUP BY gender ;（ 将employee表按照sex字段进行分组查询。使用GROUP_CONCAT()函数将每个分组的name字段的值显示出来）

SELECT gender ,COUNT(gender ) FROM employee GROUP BY gender ;（ 将employee表的sex字段进行聚合查询。）

SELECT gender ,COUNT(gender) FROM employee GROUP BY gender HAVING COUNT(gender)>=3;（ 显示employee表的性别数量 大于3的 ， 使用GROUP BY关键字时 “HAVING 条件表达式”，可以限制输出的结果、）

SELECT * FROM employee GROUP BY dept_id,gender ;（按照多个字段进行分组）

SELECT gender ,COUNT(gender ) FROM employee GROUP BY gender WITH ROLLUP;  （ 记录的最后加上了一条新的记录。该记录的COUNT(gender )列的值正好是上面分组的值的总和）

【例3-8】 限制查询记录数：

SELECT  * FROM  employee LIMIT 0,3;  （查询员工表1-3条条记录）

SELECT  * FROM  employee LIMIT 3,5;  （查询员工表4-6条记录）

【例3-9】 函数汇总：

http://www.jb51.net/article/40179.htm

【例3-10】 起别名：

SELECT  name  FROM employee AS emp    WHERE emp.salary >8000;（给表取别名）

SELECT  name, gender AS sex   FROM employee  WHERE gender='女';(给字段取别名，但是在where条件语句需要用本来的字段名才可以)

第四章

多表关系和操作：

【例4-1】 、一对多/多对一: 在多的一方加入另一方的外键

      添加数据：添加员工表数据的时候，一定要判断部did在部门表中是否存在，不存在先添加部门表。

      删除数据：先删除多的一方在另一方的关联数据，然后在删除另一方的被关联的数据。

【例4-2】、多对多: 使用中间表,分别引用两方的ID 

如下图：学生s1有两个老师，而老师t1,有两个学生t1,t2对应关系如下

添加数据：先添加教师，然后添加学生，然后在添加它俩的中间表。

删除数据：先删除中间表、然后删除老师，在删除学生。

【例4-3】 、一对一（可以在任意一方保存另一方的主键作为外键）:添加数据的时候两张表同时增加相应数据用来 一 一对应。

                    删除一张表的数据，也得同时删除另一张表的数据。

主键同步 两个表中有关系的记录id一致

在一方加入 另一方的外键

在另一方加入外键

【4-4】多表查询*（两张表department、employee）

1、笛卡尔积：把两表的列合并，很多无效数据。

           SELECT * FROM department,employee;

2、内连接：查询出左边表且右边表也有的记录。

           第一种方式：  SELECT * FROM department,employee WHERE department.id = employee.dept_id;

           第二种方方式：  SELECT * FROM department inner join  employee on  department.id = employee.dept_id;

3、左连接： LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行

           SELECT * FROM department left join  employee on  department.id = employee.dept_id;    // department 是左表  且employee是右表。

4、右链接; RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

           SELECT * FROM   employee   right join  department    on  employee.dept_id   = department.id; // employee 是左表  且department 是右表。

5、全连接： 只要其中某个表存在匹配，FULL JOIN 关键字就会返回行 相当于

           SELECT * FROM   employee  full join    department on  employee.dept_id   = department.id;   //报错，mysql不支持全连接。

           另一种实现全连接方式 ：左连接 +union +右链接;

           SELECT * FROM   employee   right join  department    on  employee.dept_id   = department.id  union      SELECT * FROM   employee   left join  department    on  employee.dept_id   = department.id；

6、实战：（查询部门id=3 的名称和其中的员工姓名）

      SELECT  department.name  部门名称 , employee.name 员工姓名 from   department inner join  employee on  department.id = employee.dept_id WHERE dept_id=3;

7、子查询 :当一个查询是另一个查询的条件为子查询。

      子查询 IN （ IN里的子表供外表比较，外表等于子表了，数据就查询出来了。）

       实战：查询存在员工表性别为女的员工的所属部门名：

       SELECT  department.name 部门名称 FROM department WHERE id IN (select dept_id  FROM employee WHERE  gender ='女') ;

     （查询员工表性别不为女的员工的所属部门名）

         SELECT  department.name 部门名称 FROM department WHERE id  NOT IN (select dept_id  FROM employee WHERE  gender ='女') ;

8、子查询 EXISTS（是否存在（只返回ture，false，当返回TRUE的时候在执行外层查询）

     (判断，只要子查询表中存在女性员工就继续查询出部门表里的所有名称)

     SELECT  department.name 部门名称 FROM department WHERE   EXISTS (select dept_id  FROM employee WHERE  gender ='女') ;

 9、进行7,8 总结（ 如果查询的两个表大小相当，那么用in和exists差别不大。 

                        如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in）

 10、子查询 ANY 任意(查询满足条件的部门)

        实战：查出部门表里大于员工表里面的部门ID结果集里任何一值的结果集：

        SELECT  * FROM department WHERE id   >ANY(SELECT dept_id FROM employee);

11、子查询 ALL 全部

        实战：查出部门表里大于员工表里面的部门ID结果集里所有值的结果集。如果没有就为空：

        SELECT  * FROM department WHERE id   >ALL(SELECT dept_id FROM employee);

        在进行验证：INSERT INTO department VALUES ('6', '资产管理部');

        再次查询：  SELECT  * FROM department WHERE id   >ALL(SELECT dept_id FROM employee);  //结果只有上面刚插入的资产管理部

12、子查询 带有比较运算符的（= ！= >＜　．．．．．．．．）

      实战：查询员工张三归属哪个部门：

       SELECT name FROM department  WHERE id = (SELECT dept_id  FROM employee  WHERE name='张三');

第五章

存储过程

详解：http://www.jb51.net/article/70677.htm

          http://blog.csdn.net/wangchao0605/article/details/5935988

          http://www.cnblogs.com/exmyth/p/3303470.html

存储过程：

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象

【例5-1】创建存储过程：

      (防止多条sql语句的分号让存储过程提前结束，用DELIMITER 定义结束符为,,然后在把结束符改为原来英文的分号 ，最后用call 调用存储过程)、

       DELIMITER //     

create procedure proc1()

begin

SELECT * FROM  department;

SELECT * FROM  employee;

end  //

DELIMITER ;                                   

call proc1 ();                            

定义变量(DECLARE myvariable  INT default=100; 并输出：SELECT  myvariable;   )：

 【例5-2】 输出自定义的变量：

 DELIMITER //     

create procedure proc2 ()

begin

DECLARE myvariable  INT default 100;

SELECT  myvariable;

end  //

DELIMITER ;                                   

call proc2(); 

【例5-3】 修改变量的值

方式一：（直接进行修改）

DELIMITER //     

create procedure proc3 ()

begin

DECLARE myvariable  INT default 100;

SELECT  myvariable;

SET myvariable  = myvariable  +20;

SELECT  myvariable;

end  //

DELIMITER ;                                   

call proc3 (); 

方式二：（从表中查询出工资为6000的姓名和性别赋值给变量.s_gender  ,s_name ）

DELIMITER //     

create procedure proc4 ()

begin

DECLARE s_gender  char(4);

DECLARE s_name  varchar(20);

SELECT  gender ,name  into  s_gender , s_name from employee  WHERE  employee.salary = 6000   ;

SELECT  s_gender  ,s_name ;

end  //

DELIMITER ;                                   

call proc4 (); 

【例5-4】定义条件

      定义ERROR 1148(42000) 错误，名称为 command_not_allowed 可以用两种方式 来定义

    条件（方式一）：使用 sqlstate_value

    DECLARE  command_not_allowed   CONDITION  FOR SQLSTATE  '4200';

   条件（方式二）：使用 mysql_error_code

    DECLARE  command_not_allowed   CONDITION  FOR   1148;

【例5-5】 定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：

DECLARE handler_type HANDLER FOR  condition_value[,...] sp_statement  

handler_type:  

    CONTINUE | EXIT | UNDO  

condition_value:  

    SQLSTATE [VALUE] sqlstate_value | condition_name  | SQLWARNING  

       | NOT FOUND  | SQLEXCEPTION  | mysql_error_code 

其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操

【例5-6】 存储过程中光标的使用

    在存储过程中执行查询语句，有时候可能会查询出很多条记录，此时，如果想逐条读取查询结果集中的记录，就需要使用光标（有的书上叫游标）

     简单点就是通过光标从结果集中遍历出你想要的数据。

接下来将介绍光标在存储过程中的使用。

1）声明光标

DECLARE curEmployee CURSOR FOR select name, salary from employee;

注释：curEmployee代表声明的光标名。

2）打开光标

OPEN curEmployee;

3）使用光标

FETCH curEmployee INTO _name,_salary ;

注释：上面代码段的意思是使用名为curEmployee的光标，将查询出来的数据存入到_name和_address变量中。

4）关闭光标

CLOSE curEmployee;

注释：在上面使用光标的位置_name和_salary 这两个变量是之前定义好的，也就是在使用光标之前 就要定义好这两个变量。关闭光标了以后就不能用FETCH这个语句使用光标了。

【例5-7】 流程控制

IF判断

创建一个存储过程，判断变量 V 的值是否为空，输出相对应的信息、

DELIMITER //   

create procedure proc5 ()

begin

DECLARE  V  VARCHAR(2);

IF V  is  null THEN SELECT 'NULL';

ELSE   SELECT 'NOT NULL';

END  IF;

END //

DELIMITER;

call proc5 ();

【例5-8】CASE 判断

DELIMITER //     

create procedure proc6 ()

begin

DECLARE  V  INT;

SET  V =2;

CASE V

          WHEN 1 THEN  SELECT 'value is 1';

          WHEN 2 THEN  SELECT 'value is 2';

          ELSE   SELECT 'not 1 or  2';

 END  CASE;

END //

DELIMITER;

call proc6 ();

【例5-9】循环：

    1、loop循环：  // LEAVE 跳出循环  还有一个 ITERATE 再次循环。

DELIMITER //     

CREATE PROCEDURE doiterate()

BEGIN

         DECLARE P1 INT DEFAULT 0;

         my_loop:LOOP

              SET p1=p1+1;

              IF P1<10 THEN  ITERATE my_loop;

              ELSEIF P1>20  THEN LEAVE my_loop;

             END IF;

             SELECT 'p1 is between 10 and 20 ';

        END LOOP my_loop;

END  //

DELIMITER;

call doiterate();

  2、REPEAT循环：带有条件判断的循环过程（如果为真继续循环，为假结束循环！）类似于java里面的do while 语句。

 DECLARE id INT DEFAULT 0;

         my_repeat:REPEAT

              SET id =id +1;

              UNTIL id>=10;  //当id>=10的时候循环结束。

             END IF;

 ......

            END REPEAT my_repeat;

3、WHILE 循环：（与REPEAT 不同的是，先判断后执行语句）

  DECLARE i INT DEFAULT 0;

  WHILE i<10  DO

  SET i=i+1;

  END WHILE

【例5-10】 查看存储过程：show procedure status；    show procedure status like '%2'；       show  create procedure proc4;     SELECT  * FROM infomation_schema.Routines;

 【例5-11】 调用存储过程：call proc();

【例5-12】 修改存储过程：可以修改存储过程的特性，目前还没有sql支持和修改内部sql语句。

【例5-13】 删除存储过程：drop procedure  proc4

【例5-14】 触发器：（一个特殊的存储过程，实现了封装性的存储过程） 

   详解：http://blog.csdn.net/hireboy/article/details/18079183#comments

第六章事务

事物：

事务是指逻辑上的一组操作，组成着这组操作的各个单元，要不全部成功，要不全部不成功。

mysql 默认就自带事务，一条语句独占一个事务。

【例6-1】初始化银行账户的表结构，和插入a，b用户并各自存了1000块钱

初始化数据：  

CREATE TABLE  account(id  INT primary key auto_increment,name VARCHAR(20),money DOUBLE );

INSERT INTO account VALUES(NULL,'a',1000.0);

INSERT INTO account VALUES(NULL,'b',1000.0);

【例6-2】业务逻辑操作：a 用户向b用户转100块钱：

UPDATE account SET money = money-100 where name='a';

UPDATE account SET money = money+100 where name='b';

【例6-3】 制造异常：a 用户向b用户转100块钱：(当转钱的时候，更新完a账号，在更新b账号的时候，突然黑客侵入改写了sql语句使其出现error！

UPDATE account SET money = money-100 where name='a';

UPDATE account SET money = mon+100 where name='g';   //money 删掉ey

【例6-4】 控制事务(如果异常出错，就进行回滚)：

CREATE PROCEDURE PRO()

BEGIN

    DECLARE t_error INTEGER;

    DECLARE   CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

    START TRANSACTION;

                UPDATE account SET money = money-100 where name='a';

                UPDATE account SET money = money+100 where name='b';

        IF t_error = 1 THEN

            ROLLBACK;

        ELSE

            COMMIT;

        END IF;

END

【例6-5】：事务的核心概念介绍：

事务有以下四个标准属性的缩写ACID，通常被称为：

    1、原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。

    2、 一致性: 确保数据库正确地改变状态后，成功提交的事务。

    3、隔离性: 使事务操作彼此独立的和透明的。

    4、持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。

除了第三点隔离性需要自己操心，其他几点不用操心，数据库已经帮我们完成。

【例6-6】：重点介绍隔离：

隔离性：本质就是多个线程操作同一个资源造成的多线程并发安全问题，加锁可以非常完美的保证隔离性，但是这会造成数据库的性能大大降低！

隔离条件：

          多个事务 并发的修改：必须隔离开。

多个事务的查询:完全不用隔离！

如果一个事务修改，另一个事务查询：

脏读：一个事务读取到另一个事务未提交（没有执行commit之前执行的结果。）的数据。

不可重复读：一个事务多次读取同一条记录，读取的结果不相同（一个事务读取到另一个事务已经提交的数据）

虚读（幻读）：一个事务多次查询整表的数据，由于其他事务新增（删除）记录造成多次查询出的记录数不同（一个事务读取到另一个 事务已提交的数据）

隔离方案:

         在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

Repeatable Read 默认的隔离级别。

安全排序：Serializable > Repeatable Read(使用最多)>Read Committed>Read Uncommitted

性能:Serializable <Repeatable Read<Read Committed<Read Uncommitted

  查询当前的隔离级别：

                                select  @@tx_isolation;

  修改事务隔离级别：set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  mysql隔离级别详解：http://xm-king.iteye.com/blog/770721

第七章   视图（ 由SELECT语句组成的查询定义的虚拟表）

【例7-1】 创建视图:(包含员工表中的员工的姓名和薪资)

   CREATE VIEW v_employee  AS SELECT name ,salary  FROM employee;

   从视图中查询数据：SELECT  * FROM   v_employee  ;（跟正常表操作一样。）

【例7-2】 修改视图（在之前的视图里加个性别）：

    第一种方式：

           CREATE    OR  REPLACE  VIEW v_employee   AS  SELECT name ,gender,salary  FROM employee;

           从视图中查询数据：SELECT  * FROM   v_employee  ;（跟正常表操作一样。）

    第二种方式：

        ALTER VIEW  v_employee AS select  name ,birthday FROM employee;

          从视图中查询数据：SELECT  * FROM   v_employee  ;（跟正常表操作一样。）

【例7-3】 改列名：

        CREATE    OR  REPLACE  VIEW  v_employee(姓名，性别, 薪资)   AS  SELECT name ,gender,salary  FROM employee;

         SELECT  * FROM   v_employee  ;

【例7-4】 多表创建视图:(显示员工姓名，和部门名称)

      CREATE   VIEW v_emp_dep  （姓名,部门） AS  SELECT employee.name,department.name  FROM employee,department  WHERE employee.dept_id=department.id; 

       验证：   SELECT  * FROM   v_emp_dep   ;

【例7-5】 查看视图：

      desc  v_emp_dep; //查看视图结构。

       SHOW TABLE STATUS  LIKE 'v_emp%';  查看视图信息

      show create view   v_emp_dep; //显示视图创建语句

【例7-6】 更新视图：

     UPDATE  v_employee  SET  birthday ='1992-08-11'; 

【例7-7】 视图详解。

 http://blog.itpub.net/28194062/viewspace-772902/  

 http://www.jb51.net/article/36363.htm

第八章高级操作：

【例8-1】 备份

 备份数据库mydb3到电脑c盘根目录下为mydb3.sql文件

 mysqldump -u root -p mydb3 > c:/mydb3.sql          

 手一抖：drop database mydb3; (被删除了)

【例8-2】 还原

 (第一种方式)

    CREATE DATABASE  mydb3;

    quit;

    mysql -u root -p mydb3 <c:/mydb3.sql

 (第二种方式)CREATE DATABASE  mydb3;

                    use mydb3

                    source  c:/mydb3.sql;

【例8-3】备份还原详解

。 http://www.cnblogs.com/zejin2008/p/4638047.html   

【例8-4】用户表详解

   Mysql核心用户表字段详解：http://www.cnblogs.com/Alight/p/4484219.html

【例8-5】 创建普通用户没有任何权限

   创建用户为sysadmin 密码为123+abc的普通账号：

   CREATE USER 'syadmin'@'localhost' IDENTIFIED BY '123+abc'; 

【例8-6】 删除用户

    drop  user 'syadmin'@'localhost' ;

    flush privileges;  (刷新用户表权限)

【例8-7】 创建用户并授权（http://blog.csdn.net/huaishu/article/details/50540814）：

 （推荐使用grant ）

   grant select on mydb3.* to 'bjadmin'@'localhost' identified by '123+abc';     (mydb3.* 为数据库.表名  'bjadmin'：创建的数据库用户名。'localhost' ：主机名、'123+abc' ：数据库用户名对应的密码。)

【例8-8】 修改普通用户密码：

1.用root登录mysql:mysql -u root -p root的密码，进入mysql>状态；

2.mysql> UPDATE user SET password=PASSWORD('新密码') WHERE user='已创建的普通用户名';

3.mysql> grant select on 要授权使用的数据库名.表名  to '普通用户名'@'new_password';

4.mysql> flush privileges;

5、登录普通用户：SET password=PASSWORD('abc');

【例8-8】 修改root用户密码：

       修改root密码：当前windows用户目录下：     

       第一种方式 ：    mysqladmin  -u root -p password   123+abc （新密码：123+abc）

       验证输一遍旧密码就ok！

       第二种方式：

      UPDATE mysql.user SET password=PASSWORD('abc') WHERE user='root' and host='localhost';

      FLUSH PRIVILEGES;

       第三种方式：用root用户登录： SET password=PASSWORD('abc');

【例8-9】权限管理详解  ：

1、http://www.jb51.net/article/65645.htm

2、http://www.cnblogs.com/Richardzhu/p/3318595.html

第九章架构性能扩展篇

【例9-1】mysql 开发规范：（目标：增加索引命中率，规范sql语句增加查询速度：http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html)

1、尽量使用预编译语句进行数据库操作，减少sql编译的时间

       http://www.cnblogs.com/conanwang/p/5924142.html 预编译详解。

2、避免数据类型的隐式转换。

      隐式转换会导致索引失效。（例子：select name,salary from employee where id='5';）  这就是隐式转换，本身id是int类型，在查询的时候，已加引号的字符串查，就会造成隐式转换从而导致索引失效。

3、避免使用双%号的查询条件。如like %123%。

4、一个sql只能利用到复合索引中的一列进行范围查询

5、使用left join 或 not exists 来优化 not in 操作

6、程序连接不同的数据库使用不同的账号，禁止跨库查询！

7、禁止使用select *    必须使用select 《字段列表》 查询

8、禁止使用不含字段列表的insert into 语句。

下列为禁止的：

INSERT INTO employee VALUES ('6', '1', '李六', '男', '1980-04-30', '20000', '教授');

INSERT INTO employee VALUES ('8', '3', '胡巴', '男', '1988-05-25', '30000', '专家'),('9', '2', '谢九', '男', '1987-05-11', '40000', '科学家')；

9、避免使用子查询，可以把子查询优化为join操作。因为子查询的结果集无法使用索引，子查询数据量大则严重影响效率

10、避免使用join 关联太多的表：（因为没join一个表会占一部分内存，会产生临时表操作，影响查询效率），mysq最对允许关联61个表，建议不超过5个！

11、减少数据库的交互次数！合并多个相同的操作到一起。

12、使用in 代替or（但是in 的值不要超过500个）可以利用到索引

13、禁止使用随机排序。order by rand（）    在程序中处理。

14、where 从句中禁止对列进行函数转换和计算，会无法相关列的使用索引，

      举例：where date(createtime) ='20170219';    可以这样  where createtime >='20160219' and createtime < '20170220'

15、在明显不会有重复值使用union all 而不是union   ，union 会把所有数据放到临时表中后在进行去重操作。 union all 不会在对结果集进行去重曹组o

16、拆分复杂得大sql为多个小sql，mysql一个sql只能使用一个cpu进行计算，。

17、超100万行的批量写操作，要分批多次进行操作。大批量的写操作会造成严重的主从延迟。

18、程序账号不能授予super权限，遵循最小原则，只能在一个db下使用，不准跨库，不准有drop权限。

【例9-2】备份

逻辑备份和物理备份。

物理备份是对数据库目录的拷贝，对于内存表只备份结构。  myiam 表数据保存到内存中。

备份分类：全量备份，增量备份。mysql 不支持增量备份，要对mysql进行增量备份，要使用工具。

http://blog.csdn.net/nanaranran/article/details/51777309  mysql比较好的备份工具。

1、创建账号：create user 'backup'@localhost identified by '123456';

2、授权：grant select,reload,lock tables,replication client ,show view,event,process on *.* to 'backup'@'localhost';

3、单个数据库实例进行备份：

  mysqldump -u backup -p  --master-data=2  --single-transaction  --routines  --triggers --events mydb3 >mydb3.sql （如有异常请执行下方操作）

  全部数据库表备份：

  mysqldump -u backup -p  --master-data=2  --single-transaction  --routines  --triggers --events --all-databases >localhost.sql

  赋予写文件的权限：grant file on *.* to 'backup'@'localhost';

4、  备份的数据写入到指定目录的文件中：

  mysqldump -u backup -p  --master-data=2  --single-transaction  --routines  --triggers --events --tab="D:\BACKUP"  mydb3

5、 条件式备份(备份员工表的，第四条到第六条的记录)：

  mysqldump -u backup -p  --master-data=2  --single-transaction  --where  "id>=4 and id <7 "   mydb3 employee >employ_4_6.sql 

6、进行时间点的恢复（全备，二进制日志备份。）：

       1）、全备mydb3：mysqldump -u backup -p  --master-data=2  --single-transaction  --routines  --triggers --events  mydb3  >back_mydb3.sql

       2）、中间有各种删除，修改，故障。

       3）、进行之前全备的恢复：

          mysql -u root -p mydb3<back_mydb3.sql

       4）、然后找到back_mydb3.sql 文件的备份时间点：back_mydb3.sql（CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=3536;）

       5)、mysqlbinlog --base64-output=decode-rows -vv  --start-position=3536  --database=mydb3  mysql-bin.000002   | grep -B DELETE | more (linux)

       6)、show binlog events in 'mysql-bin.000002';   显示二进制日志

       7)、cd(C:\ProgramData\MySQL\MySQL Server 5.5\data) 切换到mysql日志目录。

           根据日志ID等事件的起始来恢复某个时间点的操作sql。

           mysqlbinlog --start-position=3536 --stop-position=3735  --database=mydb3  mysql-bin.000002 > mydb3back.sql   

       8)、导入差异日志进行数据恢复：mysql -u root -p mydb3  < mdb3back.sql

       9)、实时的二进制日志备份：

          建立用户：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost' identified by '123456';

         建立备份文件夹：bak_binlog

         备份命令：mysqlbinlog  --raw  --read-from-remote-server --stop-never  --host  localhost  --port 3306 -u repl -p 123456  mysql-bin.000002    （5.5不支持。）

       10)、查看binlog日志（http://blog.chinaunix.net/uid-16844903-id-3896711.html）

       11)、数据库备份脚本 http://blog.csdn.net/javaweiming/article/details/8529653

       12）、异常：mysqldump : Error: Binlogging on server not active的解决办法(windows 同样适用！)

                 vi /etc/my.cnf

                在 最下方 加入：

                log-bin=mysql-bin   

                重启mysqld生效

【例9-3】复制

  Mysql 内建的复制功能是构建于mysql的大规模，高性能应用的基础，这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个备库来进行数据同步。

  复制功能不仅有利于构建高性能的应用，同时也是高可用性，可扩展性，灾难恢复，备份以及数据仓库等工作的基础。事实上，可扩展性和高可用性通常是相关联的话题。

  mysql 支持两种复制方式：基于行的复制，基于语句的复制。

   注意：将老版本的服务器作为新版本服务器的备库是不行的，因为它可能无法解析新版本采用的新的特性或语法。

   复制详解：

   复制不会增加主库的开销，只要是启用二进制日志带来的开销，但是出于备份或及时从崩溃中恢复的目的，这点开销也是必要的。

   就是当备库读取主库的旧的二进制文件带来的开销。造成网络IO开销。

   通过复制可以将读操作指向备库来获得更好的读扩展，对于写操作并不适合通过复制来扩展写操作。

   通过mysql复制可以将读操作分布到多个服务器上，实现对密集型应用的优化，通过简单的代码就能实现负债均衡，可以简单的机器名做硬编码，或使用dns轮询或者LVS

   使用一个更高版本的mysql 作为备库。

【例9-4】复制概念和实现篇

概念

  1、主库上把数据更改记录到二进制日志中。

  2、备库将主库上的日志复制到自己的中继日志中。

  3、备库读取中继日志中的事件，将其重放到备库数据 之上。

概念 详细：

1、在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制 日志中。

2、mysql会按事务提交的顺序而非每条语句执行的顺序来记录二进制。在记录二进制日志之后，主库会告诉存储引擎可以提交事务了。

3、备库将主库的二进制日志负责到其本地的中继日志中。备库会启动一个IO线程更主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储线程。

     二进制转储线程会读取主库的二进制日志的事件，它不会对事件进行 轮询。如果该线程追赶上了主库，他将进入休眠状态。直到主库发送信息号，通知有其新的事件产生时才会被唤醒，备库的IO线程会将接收到的事件记录到中继日志中。

4、执行最后一步，备库的sql线程，从中继日志中读取事件并在备库执行。从而实现备库数据的更新。

操作：

1、在每台服务器上创建复制账号

2、配置主库，和备库。

3、通知备库链接到主库并从主库复制 数据。

实战：目标： 增加额外的数据库服务器，组建成数据库集群，同一集群中的数据库服务器需要具有相同的数据，集群中的任一服务器宕机后，其它服务器可以取代宕机的服务器。

1、演示环境（windows本地mysql5.5（主库）命名为localhost、centos7.6 mysql5.5（从库）命名为hadoop1）

2、演示环境（centos7.6 mysql5.5（主库 ）命名为hadoop1、centos7.6 mysql5.5（从库 ）命名为hadoop2  ）为主主复制方案做准备！

配置:

1、配置主机windows下:

在my.ini 里增加

log-bin=mysql-bin

server_id =100

2、 登陆虚拟主机名hadoop1：mysql远程账号：root  密码：zhangtongle123

 配置  vi   /etc/my.cnf （打开前面的#号注释 log_bin =mysql-bin，修改server_id =101） 为了区分和标识

 然后在server_id下一行配置： 

 relay_log  = /var/lib/mysql/mysql-relay-bin  (中继日志的位置 和命名)

 log_slave_updates =1 (允许备库将其重放的事件也记录到自身的二进制日志中。)

 read_only =1 （配置为只读权限）

 3、然后重启hadoop1的mysql。

 4、建立账号：*（在主从客户端都建立账号为rep1 密码位123456的账号。）

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO rep1@'192.168.1.%' IDENTIFIED BY '123456';

5、全备主库上mydb3数据库：mysqldump -u backup -p  --master-data=2  --single-transaction  --routines  --triggers --events  mydb3  >back_mydb3.sql

6、然后用mysql可视化工具，navicat  链接到haoop1 主机的从数据库上，创建mydb3数据库，导入back_mydb3.sql 进行数据初始化。

     （备注：因为linux 系统下的mysql是不允许远程的，执行：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'zhangtongle123' WITH GRANT OPTION ; （允许root用户远程登陆）

7、在hadoop1从库上执行如下命令（配置从库到主库的链路：MASTER_HOST ='192.168.1.2  主库IP地址。MASTER_LOG_FILE ：主库的binlog文件名。MASTER_LOG_POS=0 ：log的起始位置。）

     CHANGE MASTER TO MASTER_HOST ='192.168.1.2',MASTER_USER='rep1',MASTER_PASSWORD= '123456',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=0;

8、 启动复制链路命令：然后在hadoop1上执行：start slave;

9、验证：SHOW SLAVE STATUS

    主要查看这两个值：代表成功   

      Slave_IO_Running=Yes

      Slave_SQL_Running=Yes

10、停止主从复制服务： stop slave;（如果重新配置主从功能）

11、 清除掉同步位置：reset slave;

        完全清除同步信息：reset slave all;

【例9-5】KEEPALIVED  (自动切换主从，当主库宕机自动切换到从库，不影响前端应用的使用】

一、环境安装：centos7系统（mysql5.5.28）

 centos7安装mysql5.5x 版本

1、 rpm -qa | grep mariadb（查看自带的mysql）

2、 rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 （卸载自带）

3、 rpm -e --nodeps mariadb-libs-5.5.41-2.el7_0.x86_64 （2出现卸载不掉执行强制卸载 --nodeps）

4、 yum install vim libaio net-tools  （安装mysql依赖）

5、 rpm -ivh  /usr/local/MySQL-server-5.5.28-1.linux2.6.x86_64.rpm

6、 rpm -ivh  /usr/local/MySQL-client-5.5.28-1.linux2.6.x86_64.rpm

7、 cp /usr/share/mysql/my-medium.cnf /etc/my.cnf  （改名为my.cnf作为mysql配置文件）

8、 service mysql start （启动mysql）  //service mysql restart(重启mysql)

9、 mysqladmin -u root password 'zhangtongle123'   //修改root用户密码

10、mysql -u root -p  (登陆mysql需要密码)

11、GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'zhangtongle123' WITH GRANT OPTION ; （允许远程用户登陆）

12、FLUSH PRIVILEGES;（刷新权限）

13、exit（退出）

二、主主复方案制配置调整

  1、避免两个库写冲突，设置自增长的步长 为不一致！

       hadoop1:数据库配置修改：（主库是不允许重启mysql）

       vi  /etc/my.cnf  中加入：

                                               auto_increment_increment=2     //由默认1改为2.

                                               auto_increment_offset=1

      进入mysql：mysql -h localhost -u root -p 

   执行：set global auto_increment_increment=2;

         set global auto_increment_offset=1;

         exit因为gloable管不了session级别，只能退出，重新登录。

   检查：show variables like  'auto%';

      表的形式：id =1,3,5,7,9

       hadoop2:数据库配置修改：

        vi  /etc/my.cnf  中加入：

                                               auto_increment_increment=2     //由默认1改为2.

                                               auto_increment_offset=2    //由1改为2.

      进入mysql：mysql -h localhost -u root -p 

   执行：set global auto_increment_increment=2;

         set global auto_increment_offset=2;

         exit因为gloable管不了session级别，只能退出，重新登录。

   检查：show variables like  'auto%';

       表的形式：id =2,4,6,8,10

  2、配置hadoop1和hadoop2的主主复制：

    1）验证正常读写否配置成功：show variables like  '%read_only%';（如果是on 请去掉my.cnf 的read_only选项）

       (在hadoop1、2上执行)show master  status  查看当前日志状态。

  2） hadoop1和hadoop2  建复制账号：GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO rep1@'192.168.1.%' IDENTIFIED BY '123456';

  3）  CHANGE MASTER TO MASTER_HOST ='192.168.1.202',MASTER_USER='rep1',MASTER_PASSWORD= '123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=274;    //配置主：hadoop1链路。

         备注：（MASTER_HOST 为  hadoop2的IP地址。  MASTER_LOG_FILE  可以在hadoop2上执行：show master  status 获取）

  4） CHANGE MASTER TO MASTER_HOST ='192.168.1.201',MASTER_USER='rep1',MASTER_PASSWORD= '123456',MASTER_LOG_FILE='mysql-bin.000043',MASTER_LOG_POS=641;  //配置从：hadoop2链路。

       备注：（MASTER_HOST 为  hadoop1的IP地址。  MASTER_LOG_FILE  可以在hadoop1上执行：show master  status 获取）

  4）启动这个复制链路：start slave;  //在主：hadoop1 和hadoop2 上执行。

 5）查看这个复制链路：SHOW SLAVE STATUS   

  主要查看这两个值：代表成功   

      Slave_IO_Running=Yes

      Slave_SQL_Running=Yes

      如果出现：  Slave_SQL_Running=no。

  操作如下：

        停止主从复制服务： stop slave;（停止hadoop1.hadoop2）

        清除掉同步位置：reset slave;

        完全清除同步信息：reset slave all;

重新配置：

6）  CHANGE MASTER TO MASTER_HOST ='192.168.1.202',MASTER_USER='rep1',MASTER_PASSWORD= '123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=2447;    //配置主：hadoop1链路。

         备注：（MASTER_HOST 为  hadoop2的IP地址。  MASTER_LOG_FILE  可以在hadoop2上执行：show master  status 获取）

  7） CHANGE MASTER TO MASTER_HOST ='192.168.1.201',MASTER_USER='rep1',MASTER_PASSWORD= '123456',MASTER_LOG_FILE='mysql-bin.000043',MASTER_LOG_POS=2694;  //配置从：hadoop2链路。

       备注：（MASTER_HOST 为  hadoop1的IP地址。  MASTER_LOG_FILE  可以在hadoop1上执行：show master  status 获取）

 8）start  slave （分别开启hadoop1,2）

3、配置keepalived：它是基于ARRP网络协议的。

  1）在haodop1、hadoop2服务器上安装keepalived。

      分别执行：yum install keepalived -y

 4、配置keepalived：

 2） vi   /etc/keepalived/keepalived.conf

 编辑

*（备注：网卡为主机网卡。用ifconfig 查看。查看虚拟网卡绑定情况：ip addr ）

3） mysql 脚本放在/etc/keepalived/ 下：（脚本权限设置里为：0644即可。 777报错 ，暂时无法解决~）

4）systemctl start keepalived.service 启动  （systemctl enable keepalived.service  设置开机自动启动 ）

          keepalived开启，关闭，查看命令:

1;systemctl daemon-reload  重新加载

2:systemctl enable keepalived.service

3:systemctl disable keepalived.service 取消开机自动启动

4:systemctl start keepalived.service

5:systemctl stop keepalived.service停止

6、systemctl status keepalived.service （查看keepalived效果）

5)、模拟hadoop1主机宕机。

     /etc/init.d/mysql stop

6)  外部应用使用虚拟ip：192.168.1.203 :3306

      当192.168.1201 宕机 之后释放keepalived 转换到192.168.1.202上，实现无缝切换。

【例9-7】读写分离

后续想看----请打赏作者----作者也是精心准备的笔记。

因原版本笔记记录在印象笔记--如果想看原版本的朋友请加我。

qq：1071235258  

打赏

微信扫一扫，打赏作者吧～