精华内容
下载资源
问答
  • MySQL数据库从入门到实战课

    万人学习 2019-12-31 14:09:39
    为什么说每一个程序员都应该学习MySQL? 根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。 使用量大同时,掌握MySQL早已是运维、DBA的必备技能,甚至部分IT开发岗位也要求对数据库...
  • MySQL学习总结

    千次阅读 2019-05-21 14:39:29
    MySQL学习总结 前言 春节期间看了一本mysql书《MySQL数据库应用从入门到精通》 觉得这本书相对简单、基础、实用、全面,我们大多数人喜欢搞一些高深的东西,而忽视一些简单基础的东西,在工作当中我们犯错的...

    MySQL学习总结

     

    前言

    春节期间看了一本mysql书《MySQL数据库应用从入门到精通》

    觉得这本书相对简单、基础、实用、全面,我们大多数人喜欢搞一些高深的东西,而忽视一些简单基础的东西,在工作当中我们犯错的地方往往是那些简单基础的地方,有的时候一些基础的DDL、DML并不一定是信手拈来,有些概念和用法也并不一定掌握的很准确,还需要百度的帮助。

    下面简单的总结些容易犯错或者是容易模糊的概念和用法分享给大家,抽时间整理了下 以便后续review,目前完成了两篇《基础篇》《操作应用篇》,还有一篇《数据库管理篇》主要是安全、日志、性能、维护的知识。后续整理完补发给大家,希望大家能从中受益一点点,也希望大家多分享,共同切磋、共同进步。

    第1篇 MySQL数据库基础篇

    1. 概念

    1. 数据库(DataBase,DB:是指长期保存在计算机的存储设备上,按照一定规则组织起来,可以被各种用户或应用共享的数据集合。

    2. 数据库管理系统(DataBase Management System,DBMS:是一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。当前比较流行和常用的数据库管理系统有Oracle、MySQL、SQL Server和DB2等。

    3. 数据库系统(DataBase System,DBS:是指在计算机系统中引入数据库后的系统,通常由计算机硬件、软件、数据库管理系统和数据管理员组成。

     

    在通常情况下,经常会用数据库来表示它们使用的数据库软件。这经常会引起混淆,确切地说,数据库软件应该为数据库管理系统,数据库是通过数据库管理系统创建和操作的容器。

     

    2. 数据库管理系统提供的功能

    数据定义语义(Data Definition Language,DDL:数据库管理系统提供了数据定义语言定义数据库涉及各种对象,定义数据的完整性约束,保密限制等约束。

    数据操作语言(Data Manipulation Language,DML:数据库管理系统提供了数据操作语言实现对数据的操作。基本的数据操作有两类:检索(查询)和更新(插入、删除和更新)。

    数据控制语言(Data Control Language,DCL:数据库管理系统提供了数据控制语言实现对数据库的控制,包含数据完整性控制、数据安全性控制和数据库的恢复等。

    什么是 SQL其发音为字母S-Q-L或sequel [ˈsikwəl],是Structure QueryLanguage(结构化查询语言)的缩写,是目前广泛使用的关系数据库标准语言。

     

    3. 安装和配置

    MySQL基于客户端---服务器(C\S)的数据库管理系统,即服务器软件和客户端软件。

     

    服务器软件是负责所有数据访问和处理的一个软件,而关于数据添加、删除等所有请求都来自于客户端软件。

    ü  服务器端软件为MySQL数据库管理系统,可以在本地计算机上或者具有访问权限的远程服务器上安装该软件。

    ü  客户端软件为可以操作MySQL服务器的软件。

     

    5.MySQL的版本

    目前MySQL数据库按照用户群分为社区版(Community Server)和企业版(Enterprise)

     

    从MySQL版本5开始,开始支持触发器、师徒、存储过程等数据库对象。

    常见软件版本:

    ü  GA(General Availablity):官方推崇广泛使用的版本。

    ü  RC(Release Candidate):候选版本的意思,该版本深思最接近正式版的版本。

    ü  Alpha和Bean都属于测试中版本,其中Alpha是指内侧版本,Bean是指公测版本。

     

    注意:如果MySQL安装在服务器上,一定要选择“Add Firewall exception for this port”复选框,这样就可以在同一网络内的用户可以访问该端口;

     

    如果MySQL安装在服务器上,需要选择“Enable root access from remove machines”复选框来设置可以让远程计算机通过用户root来登陆MySQL。

     

    6.MySQL目录说明

    l  Bin文件夹:存放可执行文件。

    l  Include文件夹:存放头文件。

    l  Lib文件夹:存放库文件。

    l  Share文件夹:存放字符集、语言等信息。

     

    各个.ini文件的含义如下:

    my.ini文件:MySQL软件正在使用的配置文件。

    l  my-huge.ini文件:当MySQL软件为超大型数据库时时用的配置文件。

    l  my-innodb-heavy-4G.ini:当MySQL软件的存储引擎为InnoDB,而且内存不小于4GB时使用的配置文件。

    l  my-large.ini:当MySQL软件为大型数据库时使用的配置文件。

    l  my-medium.ini:当MySQL软件为中型数据库时使用的配置文件。

    l  my-small.ini:当MySQL软件为小型数据库时使用的配置文件。

    l  my-template.ini:配置文件模板。

     

    在DOS窗口查看window是系统已经启动的服务命令:

    net start

    net start MySQL  启动

    net stop MySQL  停止

     

    DOS窗口连接MySQL

    mysql –h 127.0.0.1 –u root –p

     

    执行上面命令时,如果出现 mysql 不是内部或外部命令,也不是可运行的程序或批处理文件。

    说明在安装时没有勾选“Include Bin Directory in windows PATH”复选框。

    可以在通过设置环境变量path来完成,变量值为MySQL安装的目录到\bin目录;

     

    提示:如果是免安装版的也可以设置成为windows服务,自己查找下资料吧,也是很简单。

    My.ini中添加[WindowsMySQLServer]

    Server=” C:\\mysql\\bin\\mysqld.exe”

    运行中:C:\\mysql\\bin\\mysqld.exe–install

     

     

    第2篇 MySQL数据库操作和应用篇

    1.数据库和数据库对象

    数据库是一种可以通过某种方式存储数据库对象的容器。

     

    各个系统数据库作用:

    l  information_schema:主要存储了系统中的一些数据库对象信息,例如用户表信息、列信息、权限信息、字符集信息和分区信息等。

    l  performance_schema:主要存储数据库服务器性能参数。

    l  mysql:主要存储了系统的用户权限信息。

    l  test:该数据库为MySQL数据库管理系统自动创建的测试数据库,任何用户都可以使用。

     

    所谓数据库对象是存储、管理和使用数据的不同结构形式,主要包含表、视图、存储过程、函数、触发器和事件等。

     

    2.存储引擎

    在MySQL中查看数据库的存储引擎:

    ü  show engines;   或者 showengines \G  或者  show engines \g

    ü  show variables like ‘have%’;  查看所支持的存储引擎。

    ü  show variables like ‘storage_engine%’;  查询默认存储引擎。

     

    注:在具体执行SQL语句中,可以用“;”、“\g”和“\G”符号表示语句结束。

    以“;”、“\g”结束符作用一样,而“\G”符号除了表示语句结束外,还可以使得结果显示更美观,相当于格式化的作用。

     

    MySQL 5.5支持9种存储引擎,分别为FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、InnoDB和PERFORMANCE_SCHEMA。

     

    主要介绍MyISAM、InnoDb和MEMORY三种存储引擎特性的对比:

    l  MyISAM:不支持事物、也不支持外键,所以访问速度比较快。因此对事物完整性没有要求并以访问为主的应用适合使用该存储引擎。

    l  InnoDB:支持具有提交、回滚和崩溃恢复能力的事物,所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要进行频繁的更新、删除操作,同时还对事物的完整性要求比较高,需要实现并发控制,此时适合使用该存储引擎。

    l  MEMORY:使用内存来存储数据,因此访问速度快,但没有安全保障。如果应用中涉及数据比较小,需要进行快速的访问,则适合使用该存储引擎。

     

    3.数据类型

    浮点数类型

    l  FLOAT(4字节)

    l  DOUBLE(8字节)

    当存储小数数据,两者皆可,但需要精确到小数点后10位以上,就需要选择DOUBLE类型。

    定点数类型

    l  DEC(M,D)    

    l  DECIMAL(M,D)

    字节M+2  最大和最小值取值范围与DOUBLE一样。但是有效取值范围由MD来决定。

    如果存储小数数据,除了可以选择FLOATDOUBLE类型外,还可选择DECDECIMAL类型,当要求小数数据精确度非常高时,则可选择DECDECIMAL类型,它们的精度比DOUBLE类型还要高。

     

    位类型

    l  BIT(M)字节是M,M的取值范围为1---8,该类型的存储空间是根据精度决定的。

     

    日期和时间类型

    l  表示年月日,一般使用DATE类型。(4字节)

    l  表示年月日时分秒,DATETIME类型。(8字节)

    l  需要经常插入或者更新日期为当前系统时间,TIMESTAMP类型。(4字节)

    l  时分秒,TIME类型。(3字节)

    l  年份,YEAR类型。因为该类型比DATE类型占用更少的空间。(1字节)

    注:要根据实际应用来选择满足需求的最小存储的日期类型。

    ü  如果只需要存储“年份”,则可以选择存储字节为1的YEAR类型。

    ü  如果要存储年月日时分秒,并且年份的取值可能比较久远,最好使用DATETIME类型而不是TIMESTAMP类型,因为前者比后者所表示的日期范围要长一些。

    ü  如果存储的日期需要让不同时区的用户使用,则可以使用TIMESTAMP类型,因为只有该类型日期能够跟实际时区相对应。

    字符串类型

    l  CHAR(M)

    l  VARCHAR(M)

    VARCHAR类型的长度是可变得,范围0---65535。

    如果需要存储少量字符串,则可以选择CHAR和VARCHAR类型,至于选择哪个?需要判断所存储字符串长度是否经常变换,如果经常变化则可以选择VARCHAR类型,否则选择CHAR类型。

     

    4.表的操作

    表是一种很重要的数据库对象,是组成数据库的基本元素,由若干个字段组成,主要用来实现存储数据记录。

    表中的数据库对象包含

    l  列(Column) 也叫属性列,创建表时,必须指定列的名字和数据类型。

    l  索引(Index) 指根据指定的数据库表列建立起来的顺序,提供快速访问数据的途径且可监督表的数据,使其索引所指向的列中的数据不重复。

    l  触发器(Triger)是指用户定义的事物命令的集合。

    查看表结构语句

    l  DESCRIBLE/DESC table_name;  查看表定义

    l  show create table table_name \G 查看表详细定义

    增加字段

    1.        在表的最后一个位置增加字段

    alter table table_name add 属性名 属性类型;

    2.        在表的第一个位置增加字段

    Alter table table_name add 属性名 属性类型 first

    3.        在表指定字段之后增加字段

    Alter table table_name add 属性名 属性类型 after 属性名;

    删除字段

    4.        Alter table table_name drop 属性名;

    修改字段

    5.        Alter table table_name modify 属性名数据类型;

    6.        修改字段的名字:alter table table_name change 旧属性名 新属性名 旧数据类型;

     

    7.        同时修改字段的名字和属性:alter table table_name change 旧属性名 新属性名 新数据类型;

    8.        修改字段的顺序:alter table table_name modify 属性名1 数据类型 first|after 属性名2;

    5.索引操作

    根据索引的存储类型,分为B型树索引(BTREE)和哈希索引(HASH)。

    注:InnoDB和MyISAM存储引擎支持BTREE类型索引,MEMORY存储引擎支持HASH类型索引,默认为前者索引。

    查看帮助文档,MySQL支持6种索引,普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

    一般下面情况适合创建索引:

    l  经常被查询的字段,即在where子句中出现的字段。

    l  在分组的字段,即在groupby子句中出现的字段。

    l  存在依赖关系的子表和父表之间的联合查询,即主键或外键字段。

    l  设置唯一完整性约束的字段。

    一般下面情况不适合创建索引:

    l  在查询中很少被使用的字段。

    l  拥有许多重复值的字段。

     

    创建索引

    1.      创建表时创建普通索引:

    Create table table_name(

    属性名  数据类型,

    …………

    普通索引:Index|key[索引名] (属性名1 (长度)   ASC|DESC)

    唯一索引:uniqueindex|key [索引名] (属性名1 (长度)   ASC|DESC)

    全文索引:fulltext index|key [索引名] (属性名1 (长度)   ASC|DESC) ;  engine=MyISAM

    );

    注:只能在存储引擎MyISAM的数据库表上创建全文索引,在默认情况下,全文索引的搜索执行方式为不区分大小写,如果全文索引所关联的字段为二进制数据类型,则以区分大小写的搜索方式执行。

     

    2.       在已存在的表上创建普通索引:

    Create index 索引名 on 表名属性名 (长度)  ASC|DESC

    3.      通过SQL语句ALTER TABLE创建普通索引:

    Alter table table_name add index|key 索引名 属性名(长度)  ASC|DESC

    注:在创建索引时,可以指定索引的长度。这是因为不同存储引擎定义了表的最大索引数和最大索引长度。MySQL所支持的存储引擎对每个表至少支持16个索引,总索引长度至少为256字节。

     

    查看索引

    l  查看表中的索引是否创建成功:Show create table table_name \G;

    l  查看表中索引是否被启用:explainselect * from table_name where 属性名=1;

     

     

    6.视图操作

    视图:本质上是一种虚拟表,其内容与真实表相似,包含一系列带有名称的列和行数据。但是,视图并不是在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用基本表,并且在具体引用视图时动态生成。

     

    视图的特点:

    l  视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系。

    l  视图是由基本表(实表)产生的表(虚表)。

    l  视图的建立和删除不影响基本表。

    l  对视图内容的更新(添加、删除和修改)直接影响基本表。

    l  当视图来自多个基本表时,不允许添加和删除数据。

    注:MySQL数据库管理系统从5.0.1版本开始提供视图新特性。

     

    创建视图

    Create viewview_name  as  查询语句;

     

    查看视图

    l  进入数据库view,查看该数据库里所有表名和视图名。

    Use view ;

    Show tables;

    l  查看视图详细信息

    Show table status [from db_name] [like ‘pattern’]

    Show table status from view \G    返回表示表和视图各种信息的各种字段。

    Show table status FROM view LIKE “view_name” \G  查看指定视图的详细信息。

    l  查看视图定义信息

    Show create view view_name

    l  查看视图设计信息

    DESCRIBE | DESC view_name

    l  通过系统表查看视图信息

    系统数据库information_schema 中存在一个包含视图信息的表格views,可以通过查看表格views来查看所有视图的相关信息。

    Use information_schema;

    Select * from views where table_name=’view_name’ \G

     

    修改视图

    l  Use view ;

    Create or replace view view_name as 查询语句; 此法先删除原视图在重新创建

    l  Alter 语句修改视图

    Alter view view_name as 查询语句

    7.触发器操作

    l  创建有一条执行语句的触发器

    Create triggertrigger_name

                BEFORE|AFTER trigger_EVENT

                   ON TABLE_NAME FOR EACHROW  trigger_STMT

    Trigger_EVENT 包括 insert、update、delete;

    l  创建包含多条执行语句的触发器

    Create  trigger  trigger_name

    BEFORE|AFTER   trigger_EVENT

         ON TABLE_NAME   FOR EACH ROW

              BEGIN

              Trigger_STMT

              END

    在关键字BEGIN和END之间为所要执行的多个执行语句的内容,语句之间用分号隔开。

     

    注:在MySQL软件中,一般情况下用“;”符号作为语句的结束符号,可是在创建触发器的时候,需要用到“;”符号作为执行语句的结束符号。为了解决该问题,可以使用关键字DELIMITER语句,例如“DELIMITER$$”,可以用来实现将结束符合设置成“$$”。

     

    DELIMITER $$

    CREATE TRIGGER  tri_diarytime2

        AFTER  INSERT

             ON  t_dept  FOR EACH ROW

                   BEGIN

                       INSER INTO t_diaryVALUES(null, ‘t_dept’,now());

                                INSERINTO t_diary VALUES(null, ‘t_dept’,now());

           END

           $$

    DELIMITER ;

     

    上述语句中首先通过“DELIMITER $$”语句设置结束符号为“$$”,然后在关键字BEGIN和END之间编写了执行语句列表,最后通过“DELIMITER;”语句将结束符号还原成默认结束符号“;”。

     

    查看触发器

    l  通过 SHOWTRIGGERS语句查看触发器

    Show triggers \G

    l  通过查看系统表triggers实现查看触发器

    于系统数据库information_schema中存在一个存储所有触发器信息的系统表triggers。

    USE information_schema;

    Select * from triggers \G  查询系统表triggers中的所有记录

    SELECT * FORM triggers WHERE  TRIGGER_NAME=’tri_diarytime2’  \G  查询具体触发器对象。

     

    9.     表查询数据记录

    注:在具体使用关键字IN时,查询的集合中如果存在NULL,则不会影响查询;如果使用关键字NOT IN,查询的集合中如果存在NULL,则不会有任何的查询结果。

     

    SELECT ename FROM  t_employee  WHERE empno  IN (1,2,3,4,NULL);  查询有结果集

     

    SELECT ename FROM  t_employee  WHERE empno  NOT  IN (1,2,3,4,NULL);没有查询结果集。

     

    LIKE 关键字支持的通配符如下:

    l  “_”通配符,该通配符值能匹配但个字符。

    l  “%”通配符,该通配符值可以匹配任意长度的字符串,即可以是0个字符,1个字符,也可以很多个字符。

    LIKEA%  和 LIKE a%   查询结果是一样的,MySQL软件不仅对于关键字不区分大小写,对于字段数据记录也不区分大小写。

    对于LIKE关键字,如果匹配“%%”则表示查询所有数据记录。

     

    排序数据记录查询

    MySQL软件中关键字ORDER BY 默认的排序顺序为升序。

    注:在MySQL软件中,如果字段的值为空值(NULL),则该值为最小值,因此在降序排序中将最后显示(即最后一行);在升序排序中则将最先显示(即第一行)。

     

    多字段排序

    具体运行过程中,首先按照第一个字段进行排序,如果遇到值相同的字段则会按照第二个字段进行排序,依次进行类推。

     

    限制数据记录查询数量

    SELECT field1  field2 fieldn   FROM table_name WHERE  CONDITION  LIMIT OFFSET_START, ROW_COUNT

    关键字LIMIT来限制数据查询结果数量,其中参数OFFSET_START表示数据记录的起始偏移量,参数ROW_COUNT表示显示的行数。

    对于MySQL软件提供的关键字LIMIT,如果不指定初始位置,默认值为0,表示从第一条记录开始显示。

     

     

    统计函数和分组数据记录查询

    l  COUNT(*)使用方式:对表中记录统计,不管表字段中包含的是NULL值还是非NULL值。

    l  COUNT(field)使用方式:指定字段的记录进行统计,统计时忽略NULL值,但不忽略值为0的数据记录。

    l  AVG(field)使用方式:平均值计算,忽略NULL值,但是没有忽略0数据记录。

    l  SUM(field)使用方式:计算指定字段值之和,忽略NULL值,不忽略值为0的记录。

    l  MAX(field)、MIN(field):忽略NULL值,不忽略值为0的记录。

    注:MySQL中统计函数,如果所操作的表中没有任何数据记录,则COUNT()函数会返回数据0,而AVG()、SUM()、MAX()、MIN()函数则会返回NULL。

    在具体进行分组查询时,分组所依据的字段上的值一定要具有重复值,否则将没有任何实际意义。

     

    分组数据查询----实现统计功能分组查询

    MySQL软件如果只实现简单的分组查询,是没有任何实际意义的。因为关键字GROUP BY单独使用时,默认查询出每个分组中随机一条记录,具有很大的不确定性。。分组关键字建议与统计函数一起使用。

    想显示分组中的字段,可以通过函数GROUP_CONCAT()来实现。

    SELECT GROUP_CONCAT(field)

     FROM  table_name 

          WHERE  CONDITION

          GROUP  BY field

     

    分组数据查询------实现HAVING字句限定分组查询

     

    SELECT function(field)

     FROM  table_name 

          WHERE  CONDITION

          GROUP  BY field1,field2,……..fieldn

          HAVING  CONDITION;  ----- (AVG(sal)>2000)

     

    多表数据记录查询

    l  UNION :查询结果集直接合并,并去掉重复数据记录。

    l  UNION ALL :查询结果集直接合并,没有去掉重复数据记录。

    为什么使用子查询

    例如:SELECT * FROM t_dept t, t_emp e WHERE t.deptno = e.deptno ;

    首先会对两个表进行笛卡儿积操作,然后在选取符合匹配条件的数据记录。进行笛卡儿积操作时,会生成连个数据表数据记录数的乘积条数据记录,如果这两张表的数据记录比较大,则在进行笛卡儿积操作时会造成死机。

    对于有经验的用户,首先会通过统计函数查看所操作表笛卡儿积后的数据记录数,然后才会进行多表查询。

     

     

    因此多表查询一般会经过如下步骤:

    ü  通过统计函数(COUNT())查询所关联表笛卡儿积后的数据记录数,具体SQL语句如下:

    SELECT COUNT(*) FROM t_dept , t_emp  ;

    ü  如果查询到的数据记录数MySQL软件可以接受,然后才进行多表连接查询,否则就应该考虑通过其他方式来实现。

    为了解决查询到笛卡儿积后的数据记录数远远大于MySQL软件可接受的范围,MySQL提供了子查询来实现多表查询。

    所谓子查询,就是在一个查询中嵌套了其他的若干查询,即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。理论上子查询可以出现在查询语句的任意位置,但实际开发中,子查询经常出现在WHERE和FROM子句中。

    l  WHERE子句中的子查询:该位置处的子查询一般返回单行单列、多行单列、单行多列数据记录。

    l  FROM子句中的子查询:该位置处的子查询一般返回多行多列数据记录,可以当做一张临时表。

    注:此处关于笛卡儿积、多表操作等不多介绍了,自己找找相关资料吧。

     

    10.             MySQL运算符

    l  算术运算符;

    加(+)、减(-)、乘(*)、除(/ (DIV))、除(% (MOD));

    注:所有的算术运算符都可以同时运算多个操作数,但是除运算符(/和DIV)和求模运算符(%和MOD)的操作数最好是两个。在MySQL中对于除和模操作,如果除数为0将是非法运算,返回结果为NULL。·

    l  比较运算符;

    1.       等于:=(< = >):判断数值、字符串和表达式等是否相等。如果相等则返回1;否则返回0。依据字符的ASCII码来进行判断。= 不能操作NULL值,< = > 可以操作NULL。  SELECT  NULL < = > NULL , NULL = NULL ; 结果返回 1 、NULL。

    2.       不等于:!=(<>):判断不相等,如果不相等则返回1,否则返回0。这两个比较运算符不能操作NULL(空值)。

    3.       “>”、“>=”、“<”、“<=”比较运算符主要判断数值、字符串和表达式等的相关比较,如果表达式成立则返回1,否则将返回0。不能操作NULL。

    4.       实现特殊功能比较运算符:MySQL支持的模式字符

    ^ :匹配字符串的开始部分。

    $ :匹配字符串的结束部分。

    . :匹配字符串中的任意一个字符。

    * :匹配字符,包含0个和1个。

    + :匹配字符,包含1个。

    [字符集合] :匹配字符集合中的任意一个字符。

    [^字符集合] :匹配字符集合外的任意一个字符。

    字符串{N} :字符串出现N次。

    字符串{M,N}:字符串出现至少M次,最多N次。

    l  逻辑运算符;

    AND(&&):与;

    OR(||):或;

    NOT(!):非;

    XOR:亦或;

    l  位运算符;

    &:按位与;

    |:按位或;

    ~:按位取反;

    ^:按位亦或;

    <<:按位左移;

    >>:按位右移;

    11.             MySQL常用函数

    l  字符串函数:处理字符串;

    l  数值函数:处理数字;

    l  日期函数:处理日期和时间;

    EXTRACT();获取指定值的函数:EXTRACT(type  FROM date)

    SELECT NOW() 当前日期和时间,

    EXTRACT(YEAR  FROM NOW()) 年,

    EXTRACT(MONTH  FROM NOW()) 月,

    EXTRACT(DAY  FROM NOW()) 日,

    EXTRACT(HOUR  FORM NOW())小时 ,

    EXTRACT(MINUTE  FROM NOW()) 分,

    EXTRACT(SECOND  FROM NOW())秒 ;

    l  系统信息函数:获取MySQL软件的系统信息;

    SELECT  VERSION() 版本号,  DATABASE() 数据库名,  USER () 用户名;

     

    注:此章节函数很多,就不一一列举说明了,也不难自己遇到了查下资料就好了。

     

    12.             存储过程和函数的操作

    存储过程和函数可以简单的理解为一条或多条SQL语句的集合,查看帮助文档可以发现,存储过程和函数就是事件经过编译并存储在数据库中的一段SQL语句集合。

    存储过程和函数的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行。而存储过程和函数的执行,则需要手工调用存储过程和函数的名字并需要制定相应的参数。

    存储过程和函数的区别:函数必须有返回值,而存储过程则没有。存储过程的参数类型远远多于函数参数类型。

     

     

    存储过程和函数优点:

    l  允许标准组件式编程,提高了SQL语句的重用性、共享性和可移植性。

    l  能够实现较快的执行速度,能够减少网络流量。

    l  可以被作为一种安全机制来利用。

    缺陷:

    l  编写比单句SQL语句复杂,需要用户具有更高的技能和更丰富的经验。

    l  需要创建这些数据库对象的权限。

     

    创建存储过程和函数

    l  存储过程:CREATE  PROCEDURE procedure_name ([procedure_paramter[,…..]])

    [characteristic…]  routine_body

    l  函数:CREATE  FUNCTION function_name ([function_paramter[,…..]])

    [characteristic…]  routine_body

     

    Characteristic 存储过程或函数的特性,routine_body参数表示存储过程或函数的SQL语句代码,可以用BEGIN…END来标志SQL语句的开始和结束。

     

    创建存储过程示例:

    DELIMITER $$

    CREATE PROCEDURE   proce_employee_sal()

    COMMENT ‘查询所有雇员的工资’

    BEGIN

    SELECT  sal

    FROM  t_employee;

    END $$

    DELIMITER ;

     

    创建函数示例:

    DELIMITER $$

    CREATE  FUNCTION   func_employee_sal(empnoINT(11))

    RETURNS DOUBLE(10,2)

    COMMENT ‘查询所有雇员的工资’

    BEGIN

             RETURN (SELECT  sal

    FROM  t_employee

    WHEREt_employee.empno=empno);

    END $$

    DELIMITER ;

     

    注:关于存储过程和函数的表达式在这里就不介绍了。

     

    使用光标(游标)

     MySQL软件的查询语句可以返回多条记录结果,那么在表达式中如何遍历这些记录结果呢?MySQL软件提供了光标(游标)来实现。

    l  声明光标

    DECLARE  cursor_nameCURSOR FOR select_statement  ;

    l  打开光标

    OPEN cursor_name 

    l  使用光标

    FETCH cursor_name   INTO  var_name  [,var_name] …

    l  关闭光标

    CLOSE   cursor_name

     

    示例:

    1.      声明:DECLARE  cursor_employee  CURSOR  FOR  SELECT sal  FROM  t_employee;

    2.      打开:OPEN  cursor_employee ;

    3.      执行:FETCH  cursor_employee  INTO  employee_sal  ;

    4.      关闭:CLOSE  cursor_employee ;

     

    查询存储过程和函数(三种方式)

    l  存储过程状态信息:SHOW  PROCEDURE STATUS  [ LIKE ‘proce_employee_sal]  \G

    函数状态信息:SHOW  FUNCTION  STATUS [ LIKE ‘proce_employee_sal]  \G

    l  查看系统表information_schema.routines  详细信息

    USE information_schema ;

    SELECT  * FROM  routines \G

    SELECT  * FROM ROUTINES  WHERE  SPECIFIC_NAME = ‘proce_employee_sal ’ \G

    l  SHOW  CREATE  PROCEDURE 查看定义信息

    存储过程:SHOW  CREATE  PROCEDURE proce_name \G

    函数:SHOW  CREATE  FUNCTION func_name \G

    修改

    ALTER关键字

    删除

      DROP关键字

    来自自己的qq空间,转到CSDN

    展开全文
  • MySQL学习 - MySQL安装和MySQL配置

    千次阅读 2019-03-06 16:34:08
    1.下载MySQL 2.设置MySQL配置文件 3.配置MySQL环境变量 4.启动MySQL之前常见的问题 5.MySQL启动命令 6.登录MySQL 7.修改MySQL密码 8.退出登录 1.下载MySQL MySQL下载链接:...

    目录

    1.下载MySQL

    2.设置MySQL配置文件

    3.配置MySQL环境变量

    4.启动MySQL之前常见的问题

    5.MySQL启动命令

    6.登录MySQL

    7.修改MySQL密码

    8.退出登录


    1.下载MySQL

    MySQL下载链接:https://dev.mysql.com/downloads/mysql/

    2.设置MySQL配置文件

    下载完后,我们将 zip 包解压到相应的目录,这里我将解压后的文件夹放在 D:\Program Files\mysql-8.0.15下。

    打开刚刚解压的文件夹  D:\Program Files\mysql-8.0.15 ,在该文件夹下创建 my.ini 配置文件,编辑 my.ini 配置以下基本信息:

    [mysql]
    # 设置mysql客户端默认字符集
    default-character-set=utf8
     
    [mysqld]
    # 设置3306端口
    port = 3306
    # 设置mysql的安装目录
    basedir=D:\Program Files\mysql-8.0.15
    # 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
    # datadir=C:\\web\\sqldata
    # 允许最大连接数
    max_connections=20
    # 服务端使用的字符集默认为8比特编码的latin1字符集
    character-set-server=utf8
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB

    3.配置MySQL环境变量

    打开我的电脑->属性->高级->环境变量,在系统变量里选择PATH,在其后面添加: 你的mysql bin文件夹的路径 (如: D:\Program Files\mysql-8.0.15\bin ),注意是追加,不是覆盖 ,然后确定

    image

    4.启动MySQL之前常见的问题

    C:\Windows\System32的目录下找到cmd.exe 右键已管理员身份运行cmd:

    一定要去找C盘的cmd.exe用管理员打开,路径必须到mysql的bin文件下

    cd D:\Program Files\mysql-8.0.15\bin

    没管理员权限会报以下错误

    D:\Program Files\mysql-8.0.15\bin>mysqld --install
    Install/Remove of the Service Denied!

    没cd进入到mysql的bin文件下会报以下错误

    D:\Program Files\mysql-8.0.15>net start mysql
    发生系统错误 2。
    
    
    系统找不到指定的文件。

    5.MySQL启动命令

    • 初始化数据库  
    mysqld --initialize --console

    执行完成后,会输出 root 用户的初始默认密码,如:

    ...
    2018-04-20T02:35:05.464644Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: APWCY5ws&hjQ
    ...

    APWCY5ws&hjQ 就是初始密码,后续登录需要用到,你也可以在登陆后修改密码。

    • 输入以下安装命令:
    mysqld install
    • 启动MySQL
    net start mysql
    • 关闭MySQL

    net stop mysql

    6.登录MySQL

    当 MySQL 服务已经运行时, 我们可以通过 MySQL 自带的客户端工具登录到 MySQL 数据库中, 首先打开命令提示符, 输入以下格式的命名:

    mysql -h 主机名 -u 用户名 -p

    参数说明:

    • -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
    • -u : 登录的用户名;
    • -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

    如果我们要登录本机的 MySQL 数据库,只需要输入以下命令即可:

    mysql -u root -p

    按回车确认, 如果安装正确且 MySQL 正在运行, 会得到以下响应:

    Enter password:

    若密码存在, 输入密码登录, 不存在则直接按回车登录。登录成功后你将会看到 Welecome to the MySQL monitor... 的提示语。

    然后命令提示符会一直以 mysq> 加一个闪烁的光标等待命令的输入。

    7.修改MySQL密码

    ALTER USER 'root'@'localhost' identified by '123456' ; 

    8.退出登录

    输入 exit 或 quit 退出登录。

    展开全文
  • java图书管理系统+mysql

    2019-02-21 11:57:28
    简单的一个图书管理系统,采用SpringMVC三层架构,数据库采用mysql数据库,JDBC查询,初学者练手学习不错的选择,数据库文件在项目里面,解压出来打开就看到了。
  • Java银行管理系统+MySQL

    2016-08-22 11:08:20
    JAVA+Mysql编写的银行管理系统 包括开户、存取、转账、改密、销户、 通过数据库进行增删改查、本资源仅供参考学习、版权所有、严禁侵权!
  • 学习Mysql之后,在以前的文件系统上的学生管理系统上进行的改进版本。经过本人测试,可以使用。分享给大家,希望可以对大家有所帮助。
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    MySQL 面试题 MySQL 涉及的内容非常非常非常多,所以面试题也容易写的杂乱。当年,我们记着几个一定要掌握的重心: 重点的题目添加了【重点】前缀。 索引。 锁。 事务和隔离级别。 因为 MySQL 还会有部分内容和...

    MySQL 面试题

    MySQL 涉及的内容非常非常非常多,所以面试题也容易写的杂乱。当年,我们记着几个一定要掌握的重心:

    重点的题目添加了【重点】前缀。

    1. 索引。
    2. 锁。
    3. 事务和隔离级别。

    因为 MySQL 还会有部分内容和运维相关度比较高,所以本文我们分成两部分【开发】【运维】两部分。

    • 对于【开发】部分,我们需要掌握。
    • 对于【运维】部分,更多考验开发的知识储备情况,当然能回答出来是比较好的,特别是对于高级开发工程师、架构师等。

    开发

    为什么互联网公司一般选择 MySQL 而不是 Oracle?

    免费、流行、够用。

    ? 当然,这个回答要稍微润色下。不过一般,很少问这个问题了。

    数据库的三范式是什么?什么是反模式?

    艿艿:重点在于反模式的回答。实际开发中,不会严格遵守三范式。

    胖友直接看 《服务端指南 数据存储篇 | MySQL(07) 范式与反模式》

    MySQL 有哪些数据类型?

    MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。具体可以看看 《MySQL 数据类型》 文档。

    • 正确的使用数据类型,对数据库的优化是非常重要的。

    ? MySQL 中 varchar 与 char 的区别?varchar(50) 中的 50 代表的涵义?

    • 1、varchar 与 char 的区别,char 是一种固定长度的类型,varchar 则是一种可变长度的类型。
    • 2、varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样,但后者在排序时会消耗更多内存,因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)。所以,实际场景下,选择合适的 varchar 长度还是有必要的。

    ? int(11) 中的 11 代表什么涵义?

    int(11) 中的 11 ,不影响字段存储的范围,只影响展示效果。具体可以看看 《MySQL 中 int 长度的意义》 文章。

    ? 金额(金钱)相关的数据,选择什么数据类型?

    • 方式一,使用 int 或者 bigint 类型。如果需要存储到分的维度,需要 *100 进行放大。
    • 方式二,使用 decimal 类型,避免精度丢失。如果使用 Java 语言时,需要使用 BigDecimal 进行对应。

    ? 一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 MySQL 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15?

    • 一般情况下,我们创建的表的类型是 InnoDB ,如果新增一条记录(不重启 MySQL 的情况下),这条记录的 ID 是18 ;但是如果重启 MySQL 的话,这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中,所以重启数据库或者对表 OPTIMIZE 操作,都会使最大 ID 丢失。
    • 但是,如果我们使用表的类型是 MyISAM ,那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面,重启 MYSQL 后,自增主键的最大 ID 也不会丢失。

    最后,还可以跟面试官装个 x ,生产数据,不建议进行物理删除记录。

    ? 表中有大字段 X(例如:text 类型),且字段 X 不会经常更新,以读为为主,请问您是选择拆成子表,还是继续放一起?写出您这样选择的理由

    • 拆带来的问题:连接消耗 + 存储拆分空间。

      如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO ,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。

    • 不拆可能带来的问题:查询性能。

      如果能容忍不拆分带来的查询性能损失的话,上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择。

    实际场景下,例如说商品表数据量比较大的情况下,会将商品描述单独存储到一个表中。即,使用拆的方案。

    MySQL 有哪些存储引擎?

    MySQL 提供了多种的存储引擎:

    • InnoDB
    • MyISAM
    • MRG_MYISAM
    • MEMORY
    • CSV
    • ARCHIVE
    • BLACKHOLE
    • PERFORMANCE_SCHEMA
    • FEDERATED

    具体每种存储引擎的介绍,可以看看 《数据库存储引擎》

    ? 如何选择合适的存储引擎?

    提供几个选择标准,然后按照标准,选择对应的存储引擎即可,也可以根据 常用引擎对比 来选择你使用的存储引擎。使用哪种引擎需要根据需求灵活选择,一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎,将会提高整个数据库的性能。

    1. 是否需要支持事务。

    2. 对索引和缓存的支持。

    3. 是否需要使用热备。

    4. 崩溃恢复,能否接受崩溃。

    5. 存储的限制。

    6. 是否需要外键支持。

      艿艿:目前开发已经不考虑外键,主要原因是性能。具体可以看看 《从 MySQL 物理外键开始的思考》 文章。

    目前,MySQL 默认的存储引擎是 InnoDB ,并且也是最主流的选择。主要原因如下:

    • 【最重要】支持事务。
    • 支持行级锁和表级锁,能支持更多的并发量。
    • 查询不加锁,完全不影响查询。
    • 支持崩溃后恢复。

    在 MySQL5.1 以及之前的版本,默认的存储引擎是 MyISAM ,但是目前已经不再更新,且它有几个比较关键的缺点:

    • 不支持事务。
    • 使用表级锁,如果数据量大,一个插入操作锁定表后,其他请求都将阻塞。

    艿艿:也就是说,我们不需要花太多力气在 MyISAM 的学习上。

    ? 请说明 InnoDB 和 MyISAM 的区别

    InnoDBMyISAM
    事务支持不支持
    存储限制64TB
    锁粒度行锁表锁
    崩溃后的恢复支持不支持
    外键支持不支持
    全文检索5.7 版本后支持支持

    更完整的对比,可以看看 《数据库存储引擎》「常用引擎对比」 小节。

    ? 请说说 InnoDB 的 4 大特性?

    艿艿:貌似我面试没被问过…反正,我是没弄懂过~~

    • 插入缓冲(insert buffer)
    • 二次写(double write)
    • 自适应哈希索引(ahi)
    • 预读(read ahead)

    ? 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢?

    对于 SELECT COUNT(*) FROM table 语句,在没有 WHERE 条件的情况下,InnoDB 比 MyISAM 可能会慢很多,尤其在大表的情况下。因为,InnoDB 是去实时统计结果,会全表扫描;而 MyISAM 内部维持了一个计数器,预存了结果,所以直接返回即可。

    详细的原因,胖友可以看看 《高性能 MySQL 之 Count 统计查询》 博客。

    ? 各种不同 MySQL 版本的 Innodb 的改进?

    艿艿:这是一个选择了解的问题。

    MySQL5.6 下 Innodb 引擎的主要改进:

    1. online DDL
    2. memcached NoSQL 接口
    3. transportable tablespace( alter table discard/import tablespace)
    4. MySQL 正常关闭时,可以 dump 出 buffer pool 的( space, page_no),重启时 reload,加快预热速度
    5. 索引和表的统计信息持久化到 mysql.innodb_table_stats 和 mysql.innodb_index_stats,可提供稳定的执行计划
    6. Compressed row format 支持压缩表

    MySQL5.7 下 Innodb 引擎的主要改进:

    • 1、修改 varchar 字段长度有时可以使用

      这里的“有时”,指的是也有些限制。可见 《MySQL 5.7 online ddl 的一些改进》

    • 2、Buffer pool 支持在线改变大小

    • 3、Buffer pool 支持导出部分比例

    • 4、支持新建 innodb tablespace,并可以在其中创建多张表

    • 5、磁盘临时表采用 innodb 存储,并且存储在 innodb temp tablespace 里面,以前是 MyISAM 存储

    • 6、透明表空间压缩功能

    重点】什么是索引?

    索引,类似于书籍的目录,想找到一本书的某个特定的主题,需要先找到书的目录,定位对应的页码。

    MySQL 中存储引擎使用类似的方式进行查询,先去索引中查找对应的值,然后根据匹配的索引找到对应的数据行。

    ? 索引有什么好处?

    1. 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
    2. 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则正好降低了排序的成本。

    ? 索引有什么坏处?

    1. 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。
    2. 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

    ? 索引的使用场景?

    • 1、对非常小的表,大部分情况下全表扫描效率更高。

    • 2、对中大型表,索引非常有效。

    • 3、特大型的表,建立和使用索引的代价随着增长,可以使用分区技术来解决。

      实际场景下,MySQL 分区表很少使用,原因可以看看 《互联网公司为啥不使用 MySQL 分区表?》 文章。

      对于特大型的表,更常用的是“分库分表”,目前解决方案有 Sharding Sphere、MyCAT 等等。

    ? 索引的类型?

    索引,都是实现在存储引擎层的。主要有六种类型:

    • 1、普通索引:最基本的索引,没有任何约束。

    • 2、唯一索引:与普通索引类似,但具有唯一性约束。

    • 3、主键索引:特殊的唯一索引,不允许有空值。

    • 4、复合索引:将多个列组合在一起创建索引,可以覆盖多个列。

    • 5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。

    • 6、全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎。

      常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。

    具体的使用,可以看看 《服务端指南 数据存储篇 | MySQL(03) 如何设计索引》

    ? MySQL 索引的“创建”原则?

    注意,是“创建”噢。

    • 1、最适合索引的列是出现在 WHERE 子句中的列,或连接子句中的列,而不是出现在 SELECT 关键字后的列。

    • 2、索引列的基数越大,索引效果越好。

      具体为什么,可以看看如下两篇文章:

    • 3、根据情况创建复合索引,复合索引可以提高查询效率。

      因为复合索引的基数会更大。

    • 4、避免创建过多的索引,索引会额外占用磁盘空间,降低写操作效率。

    • 5、主键尽可能选择较短的数据类型,可以有效减少索引的磁盘占用提高查询效率。

    • 6、对字符串进行索引,应该定制一个前缀长度,可以节省大量的索引空间。

    ? MySQL 索引的“使用”注意事项?

    注意,是“使用”噢。

    • 1、应尽量避免在 WHERE 子句中使用 !=<> 操作符,否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

      注意,column IS NULL 也是不可以使用索引的。

    • 2、应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:SELECT id FROM t WHERE num = 10 OR num = 20

    • 3、应尽量避免在 WHERE 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

    • 4、应尽量避免在 WHERE 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。

    • 5、不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    • 6、复合索引遵循前缀原则。

    • 7、如果 MySQL 评估使用索引比全表扫描更慢,会放弃使用索引。如果此时想要索引,可以在语句中添加强制索引。

    • 8、列类型是字符串类型,查询时一定要给值加引号,否则索引失效。

    • 9、LIKE 查询,% 不能在前,因为无法使用索引。如果需要模糊匹配,可以使用全文索引。

    关于这块,可以看看 《服务端指南 数据存储篇 | MySQL(04) 索引使用的注意事项》 文章,写的更加细致。

    ? 以下三条 SQL 如何建索引,只建一条怎么建?

    WHERE a = 1 AND b = 1
    WHERE b = 1
    WHERE b = 1 ORDER BY time DESC
    
    
    • 以顺序 b , a, time 建立复合索引,CREATE INDEX table1_b_a_time ON index_test01(b, a, time)
    • 对于第一条 SQL ,因为最新 MySQL 版本会优化 WHERE 子句后面的列顺序,以匹配复合索引顺序。

    ? 想知道一个查询用到了哪个索引,如何查看?

    EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

    使用方法,在 SELECT 语句前加上 EXPLAIN 就可以了。 《MySQL explain 执行计划详细解释》

    【重点】MySQL 索引的原理?

    解释 MySQL 索引的原理,篇幅会比较长,并且网络上已经有靠谱的资料可以看,所以艿艿这里整理了几篇,胖友可以对照着看。

    下面,艿艿对关键知识做下整理,方便胖友回顾。

    几篇好一点的文章:

    《MySQL索引背后的数据结构及算法原理》

    《MySQL 索引原理》

    《深入理解 MySQL 索引原理和实现 —— 为什么索引可以加速查询?》

    MySQL 有哪些索引方法?

    在 MySQL 中,我们可以看到两种索引方式:

    什么是 B-Tree 索引?

    B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

    • 系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
    • InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB,可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ,在 MySQL 中可通过如下命令查看页的大小:
    mysql> show variables like 'innodb_page_size';
    
    • 而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘 I/O 次数,提高查询效率。

    B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree,首先定义一条记录为一个二元组 [key, data] ,key 为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。对于不同的记录,key值互不相同。

    一棵 m 阶的 B-Tree 有如下特性:

    1. 每个节点最多有 m 个孩子。
      • 除了根节点和叶子节点外,其它每个节点至少有 Ceil(m/2) 个孩子。
      • 若根节点不是叶子节点,则至少有 2 个孩子。
    2. 所有叶子节点都在同一层,且不包含其它关键字信息。
    3. 每个非叶子节点包含 n 个关键字信息(P0,P1,…Pn, k1,…kn)
      • 关键字的个数 n 满足:ceil(m/2)-1 <= n <= m-1
      • ki(i=1,…n) 为关键字,且关键字升序排序。
      • Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ,但都大于 k(i-1) 。

    B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree:

    B-Tree 的结构

    • 每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的 key 和三个指向子树根节点的 point ,point 存储的是子节点所在磁盘块的地址。两个 key 划分成的三个范围域,对应三个 point 指向的子树的数据的范围域。
    • 以根节点为例,key 为 17 和 35 ,P1 指针指向的子树的数据范围为小于 17 ,P2 指针指向的子树的数据范围为 [17~35] ,P3 指针指向的子树的数据范围为大于 35 。

    模拟查找 key 为 29 的过程:

    • 1、根据根节点找到磁盘块 1 ,读入内存。【磁盘I/O操作第1次】
    • 2、比较 key 29 在区间(17,35),找到磁盘块 1 的指针 P2 。
    • 3、根据 P2 指针找到磁盘块 3 ,读入内存。【磁盘I/O操作第2次】
    • 4、比较 key 29 在区间(26,30),找到磁盘块3的指针P2。
    • 5、根据 P2 指针找到磁盘块 8 ,读入内存。【磁盘I/O操作第3次】
    • 6、在磁盘块 8 中的 key 列表中找到 eky 29 。

    分析上面过程,发现需要 3 次磁盘 I/O 操作,和 3 次内存查找操作。由于内存中的 key 是一个有序表结构,可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数,使每次磁盘 I/O 取到内存的数据都发挥了作用,从而提高了查询效率。

    什么是 B+Tree 索引?

    B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用 B+Tree 实现其索引结构。

    从上一节中的 B-Tree 结构图中可以看到,每个节点中不仅包含数据的 key 值,还有 data 值。而每一个页的存储空间是有限的,如果 data 数据较大时将会导致每个节点(即一个页)能存储的 key 的数量很小,当存储的数据量很大时同样会导致 B-Tree 的深度较大,增大查询时的磁盘 I/O 次数,进而影响查询效率。在 B+Tree 中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的 key 值数量,降低 B+Tree 的高度。

    B+Tree 相对于 B-Tree 有几点不同:

    • 非叶子节点只存储键值信息。
    • 所有叶子节点之间都有一个链指针。
    • 数据记录都存放在叶子节点中。

    将上一节中的 B-Tree 优化,由于 B+Tree 的非叶子节点只存储键值信息,假设每个磁盘块能存储 4 个键值及指针信息,则变成 B+Tree 后其结构如下图所示:

    B+Tree 的结构

    • 通常在 B+Tree 上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有 22 条数据记录,看不出 B+Tree 的优点,下面做一个推算:

    • InnoDB 存储引擎中页的大小为 16KB,一般表的主键类型为 INT(占用4个字节) 或 BIGINT(占用8个字节),指针类型也一般为 4 或 8 个字节,也就是说一个页(B+Tree 中的一个节点)中大概存储 16KB/(8B+8B)=1K 个键值(因为是估值,为方便计算,这里的 K 取值为〖10〗^3)。也就是说一个深度为 3 的 B+Tree 索引可以维护10^3 *10^3 *10^3 = 10亿 条记录。
    • 实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree 的高度一般都在 2~4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

    B+Tree 有哪些索引类型?

    在 B+Tree 中,根据叶子节点的内容,索引类型分为主键索引非主键索引

    • 主键索引的叶子节点存的数据是整行数据( 即具体数据 )。在 InnoDB 里,主键索引也被称为聚集索引(clustered index)。
    • 非主键索引的叶子节点存的数据是整行数据的主键,键值是索引。在 InnoDB 里,非主键索引也被称为辅助索引(secondary index)。

    辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,需要进过两步:

    • 首先,InnoDB 存储引擎会遍历辅助索引找到主键。
    • 然后,再通过主键在聚集索引中找到完整的行记录数据。

    另外,InnoDB 通过主键聚簇数据,如果没有定义主键,会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个主键作为聚簇索引。

    再另外,可能有胖友有和艿艿的一样疑惑,在辅助索引如果相同的索引怎么存储?最终存储到 B+Tree 非子节点中时,它们对应的主键 ID 是不同的,所以妥妥的。如下图所示:

    相同的索引怎么存储

    聚簇索引的注意点有哪些?

    聚簇索引表最大限度地提高了 I/O 密集型应用的性能,但它也有以下几个限制:

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    什么是索引的最左匹配特性?

    当 B+Tree 的数据项是复合的数据结构,比如索引 (name, age, sex) 的时候,B+Tree 是按照从左到右的顺序来建立搜索树的。

    • 比如当 (张三, 20, F) 这样的数据来检索的时候,B+Tree 会优先比较 name 来确定下一步的所搜方向,如果 name 相同再依次比较 age 和 sex ,最后得到检索的数据。
    • 但当 (20, F) 这样的没有 name 的数据来的时候,B+Tree 就不知道下一步该查哪个节点,因为建立搜索树的时候 name 就是第一个比较因子,必须要先根据 name 来搜索才能知道下一步去哪里查询。
    • 比如当 (张三, F) 这样的数据来检索时,B+Tree 可以用 name 来指定搜索方向,但下一个字段 age 的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是 F 的数据了。

    这个是非常重要的性质,即索引的最左匹配特性。

    MyISAM 索引实现?

    MyISAM 索引的实现,和 InnoDB 索引的实现是一样使用 B+Tree ,差别在于 MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

    MyISAM 索引与 InnoDB 索引的区别?

    • InnoDB 索引是聚簇索引,MyISAM 索引是非聚簇索引。
    • InnoDB 的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
    • MyISAM 索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
    • InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效。

    【重点】请说说 MySQL 的四种事务隔离级别?

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    事务就是对一系列的数据库操作(比如插入多条数据)进行统一的提交或回滚操作,如果插入成功,那么一起成功,如果中间有一条出现异常,那么回滚之前的所有操作。

    这样可以防止出现脏数据,防止数据库数据出现问题。

    事务的特性指的是?

    指的是 ACID ,如下图所示:

    事务的特性

    1. 原子性 Atomicity :一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
    2. 一致性 Consistency :在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束触发器级联回滚等。
    3. 隔离性 Isolation :数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
    4. 持久性 Durability :事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

    事务的并发问题?

    实际场景下,事务并不是串行的,所以会带来如下三个问题:

    • 1、脏读:事务 A 读取了事务 B 更新的数据,然后 B 回滚操作,那么 A 读取到的数据是脏数据。
    • 2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务 A 多次读取的过程中,对数据作了更新并提交,导致事务 A 多次读取同一数据时,结果不一致。
    • 3、幻读:系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级,但是系统管理员 B 就在这个时候插入了一条具体分数的记录,当系统管理员 A 改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

    小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表。

    MySQL 事务隔离级别会产生的并发问题?

    • READ UNCOMMITTED(未提交读):事务中的修改,即使没有提交,对其他事务也都是可见的。

      会导致脏读。

    • READ COMMITTED(提交读):事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

      会导致不可重复读。

      这个隔离级别,也可以叫做“不可重复读”。

    • REPEATABLE READ(可重复读):一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。

      会导致幻读。

    • SERIALIZABLE(可串行化):强制事务串行执行。

    事务隔离级别脏读不可重复读幻读
    读未提交(read-uncommitted)
    读已提交(read-committed)
    可重复读(repeatable-read)是(x)
    串行化(serializable)
    • MySQL 默认的事务隔离级别为可重复读(repeatable-read) 。
    • 上图的 <X> 处,MySQL 因为其间隙锁的特性,导致其在可重复读(repeatable-read)的隔离级别下,不存在幻读问题。也就是说,上图 <X> 处,需要改成“否”!!!!
    • ? 记住这个表的方式,我们会发现它是自左上向右下是一个对角线。当然,最好是去理解。
    • 具体的实验,胖友可以看看 《MySQL 的四种事务隔离级别》

    【重点】请说说 MySQL 的锁机制?

    表锁是日常开发中的常见问题,因此也是面试当中最常见的考察点,当多个查询同一时刻进行数据修改时,就会产生并发控制的问题。MySQL 的共享锁和排他锁,就是读锁和写锁。

    • 共享锁:不堵塞,多个用户可以同时读一个资源,互不干扰。
    • 排他锁:一个写锁会阻塞其他的读锁和写锁,这样可以只允许一个用户进行写入,防止其他用户读取正在写入的资源。

    ? 锁的粒度?

    • 表锁:系统开销最小,会锁定整张表,MyIsam 使用表锁。
    • 行锁:最大程度的支持并发处理,但是也带来了最大的锁开销,InnoDB 使用行锁。

    ? 什么是悲观锁?什么是乐观锁?

    1)悲观锁

    它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

    在悲观锁的情况下,为了保证事务的隔离性,就需要一致性锁定读。读取数据时给加锁,其它事务无法修改这些数据。修改删除数据时也要加锁,其它事务无法读取这些数据。

    2)乐观锁

    相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。

    而乐观锁机制在一定程度上解决了这个问题。乐观锁,大多是基于数据版本( Version )记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

    什么是死锁?

    多数情况下,可以认为如果一个资源被锁定,它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时,其中每个进程拥有的锁都是其他进程所需的,由此造成每个进程都无法继续下去。简单的说,进程 A 等待进程 B 释放他的资源,B 又等待 A 释放他的资源,这样就互相等待就形成死锁。

    虽然进程在运行过程中,可能发生死锁,但死锁的发生也必须具备一定的条件,死锁的发生必须具备以下四个必要条件:

    • 互斥条件:指进程对所分配到的资源进行排它性使用,即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源,则请求者只能等待,直至占有资源的进程用毕释放。
    • 请求和保持条件:指进程已经保持至少一个资源,但又提出了新的资源请求,而该资源已被其它进程占有,此时请求进程阻塞,但又对自己已获得的其它资源保持不放。
    • 不剥夺条件:指进程已获得的资源,在未使用完之前,不能被剥夺,只能在使用完时由自己释放。
    • 环路等待条件:指在发生死锁时,必然存在一个进程——资源的环形链,即进程集合 {P0,P1,P2,•••,Pn} 中的 P0 正在等待一个 P1 占用的资源;P1 正在等待 P2 占用的资源,……,Pn 正在等待已被 P0 占用的资源。

    下列方法有助于最大限度地降低死锁:

    • 设置获得锁的超时时间。

      通过超时,至少保证最差最差最差情况下,可以有退出的口子。

    • 按同一顺序访问对象。

      这个是最重要的方式。

    • 避免事务中的用户交互。

    • 保持事务简短并在一个批处理中。

    • 使用低隔离级别。

    • 使用绑定连接。

    ? MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的??

    InnoDB 是基于索引来完成行锁。例如:SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE

    • FOR UPDATE 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么 InnoDB 将完成表锁,并发将无从谈起。

    【重要】MySQL 查询执行顺序?

    MySQL 查询执行的顺序是:

    (1)     SELECT
    (2)     DISTINCT <select_list>
    (3)     FROM <left_table>
    (4)     <join_type> JOIN <right_table>
    (5)     ON <join_condition>
    (6)     WHERE <where_condition>
    (7)     GROUP BY <group_by_list>
    (8)     HAVING <having_condition>
    (9)     ORDER BY <order_by_condition>
    (10)    LIMIT <limit_number>
    

    具体的,可以看看 《SQL 查询之执行顺序解析》 文章。

    【重要】聊聊 MySQL SQL 优化?

    可以看看如下几篇文章:

    另外,除了从 SQL 层面进行优化,也可以从服务器硬件层面,进一步优化 MySQL 。具体可以看看 《MySQL 数据库性能优化之硬件优化》

    编写 SQL 查询语句的考题合集

    MySQL 数据库 CPU 飙升到 500% 的话,怎么处理?

    当 CPU 飙升到 500% 时,先用操作系统命令 top 命令观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,并进行相关处理。

    如果此时是 IO 压力比较大,可以使用 iostat 命令,定位是哪个进程占用了磁盘 IO 。

    如果是 mysqld 造成的,使用 show processlist 命令,看看里面跑的 Session 情况,是不是有消耗资源的 SQL 在运行。找出消耗高的 SQL ,看看执行计划是否准确, index 是否缺失,或者实在是数据量太大造成。一般来说,肯定要 kill 掉这些线程(同时观察 CPU 使用率是否下降),等进行相应的调整(比如说加索引、改 SQL 、改内存参数)之后,再重新跑这些 SQL。

    也可以查看 MySQL 慢查询日志,看是否有慢 SQL 。

    也有可能是每个 SQL 消耗资源并不多,但是突然之间,有大量的 Session 连进来导致 CPU 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等。

    ? 在 MySQL 服务器运行缓慢的情况下输入什么命令能缓解服务器压力?

    1)检查系统的状态

    通过操作系统的一些工具检查系统的状态,比如 CPU、内存、交换、磁盘的利用率,根据经验或与系统正常时的状态相比对,有时系统表面上看起来看空闲,这也可能不是一个正常的状态,因为 CPU 可能正等待IO的完成。除此之外,还应观注那些占用系统资源(CPU、内存)的进程。

    • 使用 sar 来检查操作系统是否存在 IO 问题。
    • 使用 vmstat 监控内存 CPU 资源。
    • 磁盘 IO 问题,处理方式:做 raid10 提高性能 。
    • 网络问题,telnet 一下 MySQL 对外开放的端口。如果不通的话,看看防火墙是否正确设置了。另外,看看 MySQ L是不是开启了 skip-networking 的选项,如果开启请关闭。

    2)检查 MySQL 参数

    • max_connect_errors
    • connect_timeout
    • skip-name-resolve
    • slave-net-timeout=seconds
    • master-connect-retry

    3)检查 MySQL 相关状态值

    • 关注连接数
    • 关注下系统锁情况
    • 关注慢查询(slow query)日志

    Innodb 的事务与日志的实现方式

    ? 有多少种日志?

    • redo 日志
    • undo 日志

    ? 日志的存放形式?

    • redo:在页修改的时候,先写到 redo log buffer 里面, 然后写到 redo log 的文件系统缓存里面(fwrite),然后再同步到磁盘文件(fsync)。
    • undo:在 MySQL5.5 之前,undo 只能存放在 ibdata* 文件里面, 5.6 之后,可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata* 之外。

    ? 事务是如何通过日志来实现的,说得越深入越好

    艿艿:这个流程的理解还是比较简单的,实际思考实现感觉还是蛮复杂的。

    基本流程如下:

    • 因为事务在修改页时,要先记 undo ,在记 undo 之前要记 undo 的 redo, 然后修改数据页,再记数据页修改的 redo。 redo(里面包括 undo 的修改)一定要比数据页先持久化到磁盘。
    • 当事务需要回滚时,因为有 undo,可以把数据页回滚到前镜像的状态。
    • 崩溃恢复时,如果 redo log 中事务没有对应的 commit 记录,那么需要用 undo 把该事务的修改回滚到事务开始之前。如果有 commit 记录,就用 redo 前滚到该事务完成时并提交掉。

    MySQL binlog 的几种日志录入格式以及区别

    ? 各种日志格式的涵义

    binlog 有三种格式类型,分别如下:

    1)Statement

    每一条会修改数据的 SQL 都会记录在 binlog 中。

    • 优点:不需要记录每一行的变化,减少了 binlog 日志量,节约了 IO,提高性能。(相比 row 能节约多少性能与日志量,这个取决于应用的 SQL 情况,正常同一条记录修改或者插入 row 格式所产生的日志量还小于 Statement 产生的日志量,但是考虑到如果带条件的 update 操作,以及整表删除,alter 表等操作,ROW 格式会产生大量日志,因此在考虑是否使用 ROW 格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的 IO 性能问题。)

    • 缺点:由于记录的只是执行语句,为了这些语句能在 slave 上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在 slave 得到和在 master 端执行时候相同 的结果。另外 MySQL 的复制,像一些特定函数功能,slave 可与 master 上要保持一致会有很多相关问题(如 sleep() 函数,last_insert_id(),以及 user-defined functions(udf) 会出现问题)。

    • 使用以下函数的语句也无法被复制:

      • LOAD_FILE()

      • UUID()

      • USER()

      • FOUND_ROWS()

      • SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

        同时在 INSERT …SELECT 会产生比 RBR 更多的行级锁 。

    2)Row

    不记录 SQL 语句上下文相关信息,仅保存哪条记录被修改。

    • 优点:binlog 中可以不记录执行的 SQL 语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或 function ,以及 trigger 的调用和触发无法被正确复制的问题。
    • 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条 Update 语句,修改多条记录,则 binlog 中每一条修改都会有记录,这样造成 binlog 日志量会很大,特别是当执行 alter table 之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

    3)Mixedlevel

    是以上两种 level 的混合使用。

    • 一般的语句修改使用 Statement 格式保存 binlog 。
    • 如一些函数,statement 无法完成主从复制的操作,则采用 Row 格式保存 binlog 。

    MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式,也就是在 Statement 和 Row 之间选择 一种。

    新版本的 MySQL 中对 row level 模式也被做了优化,并不是所有的修改都会以 row level 来记录。

    • 像遇到表结构变更的时候就会以 Statement 模式来记录。
    • 至于 Update 或者 Delete 等修改数据的语句,还是会记录所有行的变更,即使用 Row 模式。

    ? 适用场景?

    在一条 SQL 操作了多行数据时, Statement 更节省空间,Row 更占用空间。但是, Row 模式更可靠。

    因为,互联网公司,使用 MySQL 的功能相对少,基本不使用存储过程、触发器、函数的功能,选择默认的语句模式,Statement Level(默认)即可。

    ? 结合第一个问题,每一种日志格式在复制中的优劣?

    • Statement 可能占用空间会相对小一些,传送到 slave 的时间可能也短,但是没有 Row 模式的可靠。
    • Row 模式在操作多行数据时更占用空间,但是可靠。

    所以,这是在占用空间和可靠之间的选择。

    如何在线正确清理 MySQL binlog?

    MySQL 中的 binlog 日志记录了数据中的数据变动,便于对数据的基于时间点和基于位置的恢复。但日志文件的大小会越来越大,占用大量的磁盘空间,因此需要定时清理一部分日志信息。

    # 首先查看主从库正在使用的binlog文件名称
    show master(slave) status
    
    # 删除之前一定要备份
    purge master logs before'2017-09-01 00:00:00'; # 删除指定时间前的日志
    purge master logs to'mysql-bin.000001'; # 删除指定的日志文件
    
    # 自动删除:通过设置binlog的过期时间让系统自动删除日志
    show variables like 'expire_logs_days'; # 查看过期时间
    set global expire_logs_days = 30; # 设置过期时间
    

    MySQL 主从复制的流程是怎么样的?

    MySQL 的主从复制是基于如下 3 个线程的交互(多线程复制里面应该是 4 类线程):

    • 1、Master 上面的 binlog dump 线程,该线程负责将 master 的 binlog event 传到 slave 。
    • 2、Slave 上面的 IO 线程,该线程负责接收 Master 传过来的 binlog,并写入 relay log 。
    • 3、Slave 上面的 SQL 线程,该线程负责读取 relay log 并执行。
    • 4、如果是多线程复制,无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制, SQL 线程只做 coordinator ,只负责把 relay log 中的 binlog 读出来然后交给 worker 线程, woker 线程负责具体 binlog event 的执行。

    ? MySQL 如何保证复制过程中数据一致性?

    • 1、在 MySQL5.5 以及之前, slave 的 SQL 线程执行的 relay log 的位置只能保存在文件( relay-log.info)里面,并且该文件默认每执行 10000 次事务做一次同步到磁盘, 这意味着 slave 意外 crash 重启时, SQL 线程执行到的位置和数据库的数据是不一致的,将导致复制报错,如果不重搭复制,则有可能会导致数据不一致。
      • MySQL 5.6 引入参数 relay_log_info_repository,将该参数设置为 TABLE 时, MySQL 将 SQL 线程执行到的位置存到 mysql.slave_relay_log_info 表,这样更新该表的位置和 SQL 线程执行的用户事务绑定成一个事务,这样 slave 意外宕机后,slave 通过 innodb 的崩溃恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。
    • 2、MySQL 5.6 引入 GTID 复制,每个 GTID 对应的事务在每个实例上面最多执行一次, 这极大地提高了复制的数据一致性。
    • 3、MySQL 5.5 引入半同步复制, 用户安装半同步复制插件并且开启参数后,设置超时时间,可保证在超时时间内如果 binlog 不传到 slave 上面,那么用户提交事务时不会返回,直到超时后切成异步复制,但是如果切成异步之前用户线程提交时在 master 上面等待的时候,事务已经提交,该事务对 master 上面的其他 session 是可见的,如果这时 master 宕机,那么到 slave 上面该事务又不可见了,该问题直到 5.7 才解决。
    • 4、MySQL 5.7 引入无损半同步复制,引入参 rpl_semi_sync_master_wait_point,该参数默认为 after_sync,指的是在切成半同步之前,事务不提交,而是接收到 slave 的 ACK 确认之后才提交该事务,从此,复制真正可以做到无损的了。
    • 5、可以再说一下 5.7 的无损复制情况下, master 意外宕机,重启后发现有 binlog 没传到 slave 上面,这部分 binlog 怎么办???分 2 种情况讨论, 1 宕机时已经切成异步了, 2 是宕机时还没切成异步??? 这个怎么判断宕机时有没有切成异步呢??? 分别怎么处理???

    ? MySQL 如何解决主从复制的延时性?

    5.5 是单线程复制,5.6 是多库复制(对于单库或者单表的并发操作是没用的),5.7 是真正意义的多线程复制,它的原理是基于 group commit, 只要 master 上面的事务是 group commit 的,那 slave 上面也可以通过多个 worker线程去并发执行。 和 MairaDB10.0.0.5 引入多线程复制的原理基本一样。

    ? 工作遇到的复制 bug 的解决方法?

    5.6 的多库复制有时候自己会停止,我们写了一个脚本重新 start slave 。

    ? 你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?

    主从一致性校验有多种工具 例如 checksum、mysqldiff、pt-table-checksum 等。

    聊聊 MySQL 备份方式?备份策略是怎么样的?

    具体的,胖友可以看看 《MySQL 高级备份策略》 。主要有几个知识点:

    • 数据的备份类型

      • 【常用】完全备份

        这是大多数人常用的方式,它可以备份整个数据库,包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间,所以,一般推荐一周做一次完全备份。

      • 增量备份

        它是只备份数据库一部分的另一种方法,它不使用事务日志,相反,它使用整个数据库的一种新映象。它比最初的完全备份小,因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。

      • 【常用】事务日志备份

        事务日志是一个单独的文件,它记录数据库的改变,备份的时候只需要复制自上次备份以来对数据库所做的改变,所以只需要很少的时间。为了使数据库具有鲁棒性,推荐每小时甚至更频繁的备份事务日志。

      • 文件备份

        数据库可以由硬盘上的许多文件构成。如果这个数据库非常大,并且一个晚上也不能将它备份完,那么可以使用文件备份每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储,所以这种备份不是很常用。

    • 备份数据的类型

      • 热备份
      • 温备份
      • 冷备份
    • 备份工具

      • cp
      • mysqldump
      • xtrabackup
      • lvm2 快照

    MySQL 几种备份方式?

    MySQL 一般有 3 种备份方式。

    1)逻辑备份

    使用 MySQL 自带的 mysqldump 工具进行备份。备份成sql文件形式。

    • 优点:最大好处是能够与正在运行的 MySQL 自动协同工作,在运行期间可以确保备份是当时的点,它会自动将对应操作的表锁定,不允许其他用户修改(只能访问)。可能会阻止修改操作。SQL 文件通用方便移植。
    • 缺点:备份的速度比较慢。如果是数据量很多的时候,就很耗时间。如果数据库服务器处在提供给用户服务状态,在这段长时间操作过程中,意味着要锁定表(一般是读锁定,只能读不能写入数据),那么服务就会影响的。

    2)物理备份

    艿艿:因为现在主流是 InnoDB ,所以基本不再考虑这种方式。

    直接拷贝只适用于 MyISAM 类型的表。这种类型的表是与机器独立的。但实际情况是,你设计数据库的时候不可能全部使用 MyISAM 类型表。你也不可能因为 MyISAM 类型表与机器独立,方便移植,于是就选择这种表,这并不是选择它的理由。

    • 缺点:你不能去操作正在运行的 MySQL 服务器(在拷贝的过程中有用户通过应用程序访问更新数据,这样就无法备份当时的数据),可能无法移植到其他机器上去。

    3)双机热备份。

    当数据量太大的时候备份是一个很大的问题,MySQL 数据库提供了一种主从备份的机制,也就是双机热备。

    • 优点:适合数据量大的时候。现在明白了,大的互联网公司对于 MySQL 数据备份,都是采用热机备份。搭建多台数据库服务器,进行主从复制。

    数据库不能停机,请问如何备份? 如何进行全备份和增量备份?

    可以使用逻辑备份和双机热备份。

    • 完全备份:完整备份一般一段时间进行一次,且在网站访问量最小的时候,这样常借助批处理文件定时备份。主要是写一个批处理文件在里面写上处理程序的绝对路径然后把要处理的东西写在后面,即完全备份数据库。
    • 增量备份:对 ddl 和 dml 语句进行二进制备份。且 5.0 无法增量备份,5.1 后可以。如果要实现增量备份需要在 my.ini 文件中配置备份路径即可,重启 MySQL 服务器,增量备份就启动了。

    ? 你的备份工具的选择?备份计划是怎么样的?

    视库的大小来定,一般来说 100G 内的库,可以考虑使用 mysqldump 来做,因为 mysqldump 更加轻巧灵活,备份时间选在业务低峰期,可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小,压缩之后更小)。

    100G 以上的库,可以考虑用 xtrabackup 来做,备份速度明显要比 mysqldump 要快。一般是选择一周一个全备,其余每天进行增量备份,备份时间为业务低峰期。

    备份恢复时间是多长?

    物理备份恢复快,逻辑备份恢复慢。

    这里跟机器,尤其是硬盘的速率有关系,以下列举几个仅供参考:

    • 20G 的 2 分钟(mysqldump)
    • 80G 的 30分钟(mysqldump)
    • 111G 的 30分钟(mysqldump)
    • 288G 的 3 小时(xtrabackup)
    • 3T 的 4 小时(xtrabackup)

    逻辑导入时间一般是备份时间的 5 倍以上。

    备份恢复失败如何处理?

    首先在恢复之前就应该做足准备工作,避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错,再根据报错的提示来进行相应的调整。

    ? mysqldump 和 xtrabackup 实现原理?

    1)mysqldump

    mysqldump 是最简单的逻辑备份方式。

    • 在备份 MyISAM 表的时候,如果要得到一致的数据,就需要锁表,简单而粗暴。
    • 在备份 InnoDB 表的时候,加上 –master-data=1 –single-transaction 选项,在事务开始时刻,记录下 binlog pos 点,然后利用 MVCC 来获取一致的数据,由于是一个长事务,在写入和更新量很大的数据库上,将产生非常多的 undo ,显著影响性能,所以要慎用。
    • 优点:简单,可针对单表备份,在全量导出表结构的时候尤其有用。
    • 缺点:简单粗暴,单线程,备份慢而且恢复慢,跨 IDC 有可能遇到时区问题

    2)xtrabackup

    xtrabackup 实际上是物理备份+逻辑备份的组合。

    • 在备份 InnoDB 表的时候,它拷贝 ibd 文件,并一刻不停的监视 redo log 的变化,append 到自己的事务日志文件。在拷贝 ibd 文件过程中,ibd文件本身可能被写”花”,这都不是问题,因为在拷贝完成后的第一个 prepare 阶段,xtrabackup 采用类似于 Innodb 崩溃恢复的方法,把数据文件恢复到与日志文件一致的状态,并把未提交的事务回滚。
    • 如果同时需要备份 MyISAM 表以及 InnoDB 表结构等文件,那么就需要用 flush tables with lock 来获得全局锁,开始拷贝这些不再变化的文件,同时获得 binlog 位置,拷贝结束后释放锁,也停止对 redo log 的监视。

    如何从 mysqldump 产生的全库备份中只恢复某一个库、某一张表?

    具体可见 《MySQL 全库备份中恢复某个库和某张表以及 mysqldump 参数 –ignore-table 介绍》 文章。

    聊聊 MySQL 集群?

    艿艿:这块艿艿懂的少,主要找了一些网络上的资料。

    ? 对于简历中写有熟悉 MySQL 高可用方案?

    我一般先问他现在管理的数据库架构是什么,如果他只说出了主从,而没有说任何 HA 的方案,那么我就可以判断出他没有实际的 HA 经验。

    不过这时候也不能就是断定他不懂 MySQL 高可用,也许是没有实际机会去使用,那么我就要问 MMM 以及 MHA 以及 MM + keepalived 等的原理、实现方式以及它们之间的优势和不足了,一般这种情况下,能说出这个的基本没有。

    • MMM 那东西好像不靠谱,据说不稳定,但是有人在用的,和 mysql-router 比较像,都是指定可写的机器和只读机器。
    • MHA 的话一句话说不完,可以搜索下相关博客。

    聊聊 MySQL 安全?

    感兴趣的胖友,可以看看:

    MySQL 有哪些日志?

    • 错误日志:记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。

    • 二进制文件:记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。(定期删除日志,默认关闭)。

      就是我们上面看到的 MySQL binlog 日志。

    • 查询日志:记录了客户端的所有语句,格式为纯文本格式,可以直接进行读取。(log 日志中记录了所有数据库的操作,对于访问频繁的系统,此日志对系统性能的影响较大,建议关闭,默认关闭)。

    • 慢查询日志:慢查询日志记录了包含所有执行时间超过参数long_query_time(单位:秒)所设置值的 SQL 语句的日志。(纯文本格式)

      重要,一定要开启。

    另外,错误日志和慢查询日志的详细解释,可以看看 《MySQL 日志文件之错误日志和慢查询日志详解》 文章。

    聊聊 MySQL 监控?

    你是如何监控你们的数据库的?

    监控的工具有很多,例如 Zabbix ,Lepus ,我这里用的是 Lepus

    对一个大表做在线 DDL ,怎么进行实施的才能尽可能降低影响?

    使用 pt-online-schema-change ,具体可以看看 《MySQL 大表在线 DML 神器–pt-online-schema-change》 文章。

    另外,还有一些其它的工具,胖友可以搜索下。

    展开全文
  • 学习MySQL这一篇就够了

    万次阅读 多人点赞 2020-07-28 17:21:34
    DBS:数据库管理系统,又称为数据库软件或者数据库产品,用于创建和管理DB,常见的有MySQL、Oracle、DB2、SQL Server SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流...

    目录


    配套资料,免费下载
    链接:https://pan.baidu.com/s/1WmxBogBdP2yyCSe6YPm6Hg
    提取码:y287
    复制这段内容后打开百度网盘手机App,操作更方便哦

    第一章 数据库概述

    1.1、数据库的好处

    1. 将数据持久化到本地
    2. 提供结构化查询功能

    1.2、数据库的常见概念

    1. DB:数据库,存储数据的仓库
    2. DBMS:数据库管理系统,又称为数据库软件或者数据库产品,用于创建和管理数据库,常见的有MySQL、Oracle、SQL Server
    3. DBS:数据库系统,数据库系统是一个通称,包括数据库、数据库管理系统、数据库管理人员等,是最大的范畴
    4. SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言

    1.3、数据库的存储特点

    1. 数据存放到表中,然后表再放到库中
    2. 一个库中可以有多张表,每张表具有唯一的表名用来标识自己
    3. 表中有一个或多个列,列又称为“字段”,相当于Java中“属性”
    4. 表中的每一行数据,相当于Java中“对象”

    1.4、数据库的常见分类

    1. 关系型数据库:MySQL、Oracle、DB2、SQL Server
    2. 非关系型数据库:
      • 键值存储数据库:Redis、Memcached、MemcacheDB
      • 列存储数据库:HBase、Cassandra
      • 面向文档的数据库:MongDB、CouchDB
      • 图形数据库:Neo4J

    1.5、SQL语言的分类

    1. DQL:数据查询语言:select、from、where
    2. DML:数据操作语言:insert、update、delete
    3. DDL:数据定义语言:create、alter、drop、truncate
    4. DCL:数据控制语言:grant、revoke
    5. TCL:事务控制语言:commit、rollback

    第二章 MySQL概述

    2.1、MySQL的背景

    MySQL的前身是属于MySQL AB,08年被SUN公司收购,09年SUN公司又被Oracle公司收购

    2.2、MySQL的优点

    1. 成本低、开源免费
    2. 性能高、移植性好
    3. 体积小、便于安装

    2.3、MySQL的安装

    01

    02

    03

    04

    05

    06

    07

    08

    09

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    2.4、MySQL的启动

    net start MySQL
    

    2.5、MySQL的停止

    net stop MySQL
    

    2.6、MySQL的登录

    mysql -h主机名 -P端口号 -u用户名 -p密码
    

    2.7、MySQL的退出

    exit
    

    第三章 DQL语言

    3.1、基础查询

    一、语法

    SELECT 查询列表 FROM 表名;
    

    二、特点

    1. 查询列表可以是字段、常量、函数、表达式
    2. 查询结果是一个虚拟表

    三、示例

    1、查询单个字段

    SELECT 字段名 FROM 表名;
    

    2、查询多个字段

    SELECT 字段名,字段名 FROM 表名;
    

    3、查询所有字段

    SELECT * FROM 表名;
    

    4、查询常量

    SELECT 常量值;
    

    注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要

    5、查询函数

    SELECT 函数名(实参列表);
    

    6、查询表达式

    SELECT 100/25;
    

    7、起别名

    1SELECT 字段名 AS "别名" FROM 表名;
    

    注意:别名可以使用单引号、双引号引起来,当只有一个单词时,可以省略引号,当有多个单词且有空格或特殊符号时,不能省略,AS可以省略

    8、去重复

    SELECT DISTINCT 字段名 FROM 表名;
    

    9、做加法

    1SELECT 数值+数值; 直接运算
    2SELECT 字符+数值; 首先先将字符转换为整数,如果转换成功,则继续运算,如果转换失败,则默认为0,然后继续运算
    3SELECT NULL+数值; NULL和任何数值参与运算结果都是NULL
    

    10、【补充】ifnull函数

    功能:判断某字段或表达式是否为null,如果为null,返回指定的值,否则返回原本的值

    SELECT IFNULL(字段名, 指定值) FROM 表名;
    

    11、【补充】isnull函数

    功能:判断某字段或表达式是否为null,如果是null,则返回1,否则返回0

    SELECT ISNULL(字段名) FROM 表名;
    

    3.2、条件查询

    一、语法

    SELECT 查询列表 FROM 表名 WHERE 筛选条件;
    

    二、分类

    1. 条件运算符:>、>=、<、<=、=、<=>、!=、<>
    2. 逻辑运算符:and、or、not
    3. 模糊运算符:
      • like:%任意多个字符、_任意单个字符,如果有特殊字符,需要使用escape转义
      • between and
      • not between and
      • in
      • is null
      • is not null

    三、演示

    1、查询工资>12000的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE salary > 12000 ;
    

    2、查询工资>=14000的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE salary >= 14000 ;
    

    3、查询工资<12000的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE salary < 12000 ;
    

    4、查询工资<=14000的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE salary <= 14000 ;
    

    5、查询员工编号=100的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE employee_id = 100 ;
    

    6、查询员工编号<=>100的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE employee_id <=> 100 ;
    

    注意:=只能判断普通类型的数值,而<=>不仅可以判断普通类型的数值还可以判断NULL

    7、查询员工编号!=100的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE employee_id != 100 ;
    

    8、查询员工编号<>100的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE employee_id <> 100 ;
    

    注意:!=和<>都是判断不等于的意思,但是MySQL推荐使用<>

    9、查询工资>12000&&工资<18000的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE salary > 12000 AND salary < 18000 ;
    

    10、查询工资<=12000||工资>=18000的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE salary <= 12000 OR salary >= 18000 ;
    

    11、查询工资<=12000||工资>=18000的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE NOT (salary > 12000 AND salary < 18000) ;
    

    12、查询员工名中第一个字符为B、第四个字符为d的员工信息

    SELECT 
      *
    FROM
      employees 
    WHERE last_name LIKE 'B__d%' ;
    

    13、查询员工编号在100到120之间的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE employee_id BETWEEN 100 AND 120 ;
    

    14、查询员工编号不在100到120之间的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE employee_id NOT BETWEEN 100 AND 120 ;
    

    15、查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

    SELECT 
      last_name,
      job_id 
    FROM
      employees 
    WHERE job_id IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;
    

    注意:in列表的值类型必须一致或兼容,in列表中不支持通配符%和_

    16、查询没有奖金的员工名和奖金率

    SELECT 
      last_name,
      commission_pct 
    FROM
      employees 
    WHERE commission_pct IS NULL ;
    

    17、查询有奖金的员工名和奖金率

    SELECT 
      last_name,
      commission_pct 
    FROM
      employees 
    WHERE commission_pct IS NOT NULL ;
    

    注意:=、!=不能用来判断NULL、而<=>、is null 、 is not null可以用来判断NULL,但注意<=>也可以判断普通类型的数值

    3.3、排序查询

    一、语法

    SELECT 
      查询列表 
    FROM
      表 
    【WHERE 筛选条件】
    ORDER BY 排序列表 【asc | desc;
    

    二、注意

    1. 排序列表可以是单个字段、多个字段、别名、函数、表达式
    2. asc代表升序,desc代表降序,如果不写,默认是asc
    3. order by的位置一般放在查询语句的最后(除limit语句之外)

    三、示例

    1、按单个字段排序:查询员工信息,要求按工资降序

    SELECT 
      * 
    FROM
      employees 
    ORDER BY salary DESC ;
    

    2、按多个字段查询:查询员工信息,要求先按工资降序,再按员工编号升序

    SELECT 
      * 
    FROM
      employees 
    ORDER BY salary DESC, employee_id ASC ;
    

    3、按别名排序查询:查询员工信息,要求按员工年薪升序

    SELECT 
      *,
      salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪 
    FROM
      employees 
    ORDER BY 年薪 ASC ;
    

    4、按函数排序查询:查询员工信息,要求按员工名字的长度降序

    SELECT 
      LENGTH(last_name),
      last_name 
    FROM
      employees 
    ORDER BY LENGTH(last_name) DESC ;
    

    5、按表达式排序:查询员工信息,要求按员工年薪升序

    SELECT 
      *,
      salary * 12 * (1+ IFNULL(commission_pct, 0)) 
    FROM
      employees 
    ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0)) ASC ;
    

    3.4、单行函数

    一、语法

    SELECT 函数名(实参列表)FROM 表】;
    

    二、分类

    1. 字符函数

      • concat:连接字符
      • substr:截取子串
      • replace:替换字符
      • upper:变大写
      • lower:变小写
      • lpad:左填充
      • rpad:右填充
      • length:获取字节长度
      • trim:去除前后空格
      • instr:获取子串第一次出现的索引
      1、注意MySQL中的索引是从1开始的
      
    2. 数学函数

      • round:四舍五入
      • ceil:向上取整
      • floor:向下取整
      • mod:取模运算(a-a/b*b)
      • truncate:保留小数的位数,不进行四舍五入
      • rand:获取随机数,返回0-1之间的小数
    3. 日期函数

      • now:返回当前日期+时间
      • curdate:返回当前日期
      • curtime:返回当前时间
      • year:返回年
      • month:返回月
      • day:返回日
      • hour:小时
      • minute:分钟
      • second:秒
      • monthname:以英文形式返回月
      • datediff:返回两个日期相差的天数
      • date_format:将日期转换成字符
      • str_to_date:将字符转换成日期
      格式符:
      %Y:四位的年份
      %y:二位的年份
      %m:二位的月份(0102...12%c:一位的月份(12...12%d:日(0102...31%H:小时(24小时制)
      %h:小时(12小时制)
      %i:分钟(000102...59%s:秒(000102...59
    4. 控制函数

      • if:判断函数
      • case:分支函数
      1IF(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
      
      2case的格式一:
          CASE 变量或字段或表达式 
            WHEN 常量1 THEN1 
            WHEN 常量2 THEN2
            ...
            ELSE 值n 
          END ;
          
      3case的格式二:
          CASE
            WHEN 条件1 THEN1 
            WHEN 条件2 THEN2
            ...
            ELSE 值n 
          END
      
    5. 其它函数

      • version:当前数据库的版本
      • database:当前打开的数据库
      • user:当前登录的用户
      • password(‘字符’):返回该字符的密码形式
      • md5(‘字符’):返回该字符的md5加密形式

    三、演示

    1、concat

    SELECT CONCAT('Hello',' ','World') AS out_put;
    

    2、substr

    #截取从指定索引处后面所有字符
    SELECT SUBSTR('李莫愁爱上了陆展元',7)  AS out_put;
    
    #截取从指定索引处指定字符长度的字符
    SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS out_put;
    

    3、replace

    SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
    

    4、upper

    SELECT UPPER('john') AS out_put;
    

    5、lower

    SELECT LOWER('john') AS out_put;
    

    6、lpad

    SELECT LPAD('殷素素',10,'*') AS out_put;
    

    7、rpad

    SELECT RPAD('殷素素',10,'*') AS out_put;
    

    8、length

    SELECT LENGTH('john') AS out_put;
    

    9、trim

    #删除指定字符的左右空格
    SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;
    
    #删除指定字符的指定字符
    SELECT TRIM('aaa' FROM 'aaaaaaaaa张翠山aaaaaaaaa')  AS out_put;
    

    10、instr

    SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
    

    注意:返回子串第一次出现的索引,如果找不到返回0

    11、round

    #默认四舍五入
    SELECT ROUND(-1.55) AS out_put;
    
    #指定小数位数
    SELECT ROUND(1.567,2) AS out_put;
    

    注意:四舍五入和符号无关

    12、ceil

    SELECT CEIL(-1.02) AS out_put;
    

    注意:向上取整,返回>=该参数的最小整数

    13、floor

    SELECT FLOOR(-9.99) AS out_put;
    

    注意:向下取整,返回<=该参数的最大整数

    14、mod

    SELECT MOD(10,3) AS out_put;
    

    15、truncate

    SELECT TRUNCATE(1.69999,1) AS out_put;
    

    16、rand

    SELECT RAND() AS out_put;
    

    17、now

    SELECT NOW() AS out_put;
    

    18、curdate

    SELECT CURDATE() AS out_put;
    

    19、curtime

    SELECT CURTIME() AS out_put;
    

    20、year

    SELECT YEAR(NOW());
    

    21、month

    SELECT MONTH(NOW());
    

    22、day

    SELECT DAY(NOW());
    

    23、hour

    SELECT HOUR(NOW());
    

    24、minute

    SELECT MINUTE(NOW());
    

    25、second

    SELECT SECOND(NOW());
    

    26、monthname

    SELECT MONTHNAME(NOW()) 月名;
    

    27、datediff

    SELECT DATEDIFF('1995-2-7','1995-2-6') AS out_put;
    

    28、date_format

    SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;
    

    29、str_to_date

    SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
    

    30、if

    SELECT IF(10 < 5, '大', '小') AS out_put;
    

    31、case的格式一

    /*
    案例:查询员工的工资,要求
    	部门号=30,显示的工资为1.1倍
    	部门号=40,显示的工资为1.2倍
    	部门号=50,显示的工资为1.3倍
    	其它部门,显示的工资为原工资
    */
    
    SELECT 
      salary 原始工资,
      department_id,
      CASE
        department_id 
        WHEN 30 THEN salary * 1.1 
        WHEN 40 THEN salary * 1.2 
        WHEN 50 THEN salary * 1.3 
        ELSE salary 
      END AS 新工资 
    FROM
      employees ;
    

    32、case的格式二

    /*
    案例:查询员工的工资情况
        如果工资>20000,显示A级别
        如果工资>15000,显示B级别
        如果工资>10000,显示C级别
        否则,显示D级别
    */
    
    SELECT 
      salary,
      CASE
        WHEN salary > 20000 THEN 'A' 
        WHEN salary > 15000 THEN 'B' 
        WHEN salary > 10000 THEN 'C' 
        ELSE 'D' 
      END AS 工资级别 
    FROM
      employees ;
    

    33、version

    SELECT VERSION();
    

    34、database

    SELECT DATABASE();
    

    35、user

    SELECT USER();
    

    36、password(‘字符’)

    SELECT PASSWORD('123456');
    

    37、md5(‘字符’)

    SELECT MD5('123456');
    

    3.5、分组函数

    一、语法

    SELECT 函数名(实参列表)FROM 表】;
    

    二、分类

    1. sum:求和

    2. avg:平均值

    3. max:最大值

    4. min:最小值

    5. count:计算个数

    1、sum、avg一般用于处理数值型,max、min、count可以处理任何类型
    
    2、以上分组函数都忽略null3、可以和distinct搭配实现去重的运算:select sum(distinct 字段) from;
    
    4、一般使用count(*)用作统计行数
    
    5、和分组函数一同查询的字段要求是group by后的字段
    

    三、演示

    1、sum

    SELECT SUM(salary) FROM employees;
    

    2、avg

    SELECT AVG(salary) FROM employees;
    

    3、max

    SELECT MAX(salary) FROM employees;
    

    4、min

    SELECT MIN(salary) FROM employees;
    

    5、count

    SELECT COUNT(salary) FROM employees;
    

    3.6、分组查询

    一、语法

    SELECT 
      查询列表 
    FROM
      表 
    【where 筛选条件】 
    GROUP BY 分组的字段 
    【having 分组后的筛选】
    【order BY 排序的字段】 ;
    

    二、特点

    1、和分组函数一同查询的字段必须是group by后出现的字段
    
    2、筛选分为两类:分组前筛选和分组后筛选
                	针对的表				 语句位置			   连接的关键字
    	分组前筛选	 分组前的原始表			group bywhere
    	分组后筛选	 分组后的结果集			group byhaving
    	
    3、分组可以按单个字段也可以按多个字段
    
    4、分组可以搭配着排序使用
    

    三、演示

    1、查询每个工种的员工平均工资

    SELECT 
      AVG(salary),
      job_id 
    FROM
      employees 
    GROUP BY job_id ;
    

    2、查询每个位置的员工部门个数

    SELECT 
      COUNT(*),
      location_id 
    FROM
      departments 
    GROUP BY location_id ;
    

    3、查询有奖金的每个领导手下员工的平均工资

    SELECT 
      AVG(salary),
      manager_id 
    FROM
      employees 
    WHERE commission_pct IS NOT NULL 
    GROUP BY manager_id ;
    

    4、查询哪个部门的员工个数>5

    SELECT 
      COUNT(*),
      department_id 
    FROM
      employees 
    GROUP BY department_id 
    HAVING COUNT(*) > 5 ;
    

    5、查询每个工种有奖金的员工的最高工资>6000的最高工资和公众编号,按最高工资升序

    SELECT 
      MAX(salary) m,
      job_id
    FROM
      employees 
    WHERE commission_pct IS NOT NULL 
    GROUP BY job_id 
    HAVING m > 6000 
    ORDER BY m ;
    

    6、查询每个工种每个部门的最低工资并按最低工资降序

    SELECT 
      MIN(salary),
      job_id,
      department_id 
    FROM
      employees 
    GROUP BY job_id, department_id
    ORDER BY MIN(salary) DESC ;
    

    3.7、连接查询

    一、含义

    连接查询又称多表查询,当查询的字段来自于多个表时,就会用到连接查询

    二、注意

    笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
    
    发生原因:没有有效的连接条件
    如何避免:添加有效的连接条件
    

    三、分类

    1. 按年代分类
      • sql92标准:支持内连接
      • sql99标准:支持内连接、部分外连接(左外、右外)、交叉连接
    2. 按功能分类
      • 内连接
        • 等值连接
        • 非等值连接
        • 自连接
      • 外连接
        • 左外连接
        • 右外连接
        • 全外连接
      • 交叉连接

    四、sql92标准演示

    1、sql92标准:等值连接

    #查询员工名和对应的部门名
    
    SELECT 
      last_name,
      department_name 
    FROM
      employees,
      departments 
    WHERE employees.`department_id` = departments.`department_id` ;
    

    2、sql92标准:非等值连接

    #查询员工的工资和工资级别
    
    SELECT 
      salary,
      grade_level 
    FROM
      employees e,
      job_grades g 
    WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
    

    3、sql92标准:自连接

    #查询员工名和它对应上级的名称
    
    SELECT 
      e.employee_id,
      e.last_name,
      m.employee_id,
      m.last_name 
    FROM
      employees e,
      employees m 
    WHERE e.`manager_id` = m.`employee_id` ;
    

    3.8、sql99标准

    一、语法

    SELECT 
      查询列表 
    FROM1 别名1 
    【连接类型】 JOIN2 别名2 ON 连接条件 
    【where 分组前筛选条件】
    【group BY 分组列表】
    【having 分组后筛选条件】
    【order BY 排序列表】 ;
    

    二、连接类型

    1. 内连接:inner
    2. 外连接
      • 左外连接:left 【outer】(左边的是主表)
      • 右外连接:right 【outer】(右边的是主表)
      • 全外连接:full 【outer】(两边都是主表,但是MySQL不支持全外连接、Oracle支持)
    3. 交叉连接:cross(交叉连接其实是用sql99语法实现笛卡尔乘积)

    三、演示

    1、内连接:等值连接

    #查询员工名和对应的部门名
    
    SELECT 
      last_name,
      department_name 
    FROM
      departments d 
    INNER JOIN employees e ON e.`department_id` = d.`department_id` ;
    

    2、内连接:非等值连接

    #查询员工的工资和工资级别
    
    SELECT 
      salary,
      grade_level 
    FROM
      employees e 
    INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;
    

    3、内连接:自连接

    #查询员工名和它对应上级的名称
    
    SELECT 
      e.last_name,
      m.last_name 
    FROM
      employees e 
    INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;
    

    4、外连接:左外连接

    #查询哪个部门没有员工
    
    SELECT 
      d.`department_name`
    FROM
      departments d 
    LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` 
    WHERE e.`employee_id` IS NULL ;
    

    5、外连接:右外连接

    #查询哪个部门没有员工
    
    SELECT 
      d.`department_name`
    FROM
      employees e 
    RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` 
    WHERE e.`employee_id` IS NULL ;
    

    6、交叉连接

    #使用交叉连接进行笛卡尔乘积查询
    
    SELECT 
      b.*,
      bo.* 
    FROM beauty b 
    CROSS JOIN boys bo ;
    

    3.9、子查询

    一、含义

    嵌套在其它语句内部的select语句称为子查询或内查询,外面的语句可以是insert、delete、update、select等,一般select作为外面语句较多,外面如果为select语句,则此语句称为外查询或主查询

    二、分类

    1. 按出现的位置划分
      • select后面:标量子查询
      • from后面:表子查询
      • where或having后面
        • 标量子查询
        • 列子查询
        • 行子查询
      • exists后面
        • 标量子查询
        • 列子查询
        • 行子查询
        • 表子查询
    2. 按结果集行列数划分
      • 标量子查询(单行子查询):结果集为一行一列
      • 列子查询(多行子查询):结果集为多行一列
      • 行子查询:结果集为多行多列
      • 表子查询:结果集为多行多列

    三、特点

    1. 子查询放在小括号内
    2. 子查询一般放在条件的右侧
    3. 子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
    4. 标量子查询,一般搭配着单行操作符使用:>、>=、<、<=、!=、<>、=、<=>
    5. 列子查询,一般搭配着多行操作符使用:in、not in、any、some、all、exits

    四、演示

    1、select后面

    #查询每个部门的员工个数
    
    SELECT 
      d.*, (
      SELECT 
        COUNT(*) 
      FROM
        employees e 
      WHERE e.department_id = d.`department_id`
    ) 个数 
    FROM
      departments d ;
    

    2、from后面

    #查询每个部门平均工资的工资等级
    
    SELECT 
      ag_dep.*,
      g.`grade_level` 
    FROM (
      SELECT 
        AVG(salary) ag,
        department_id 
      FROM
        employees 
      GROUP BY department_id
    ) ag_dep 
    INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal ;
    

    3、where或having后面

    标量子查询:查询最低工资的员工姓名和工资

    SELECT 
      last_name,
      salary 
    FROM
      employees 
    WHERE salary = (
    	SELECT MIN(salary) FROM employees
    ) ;
    

    列子查询:

    #查询所有是领导的员工姓名
    
    SELECT 
      last_name 
    FROM
      employees 
    WHERE employee_id IN (
    	SELECT DISTINCT manager_id FROM employees
    ) ;
    
    #返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary
    
    SELECT 
      employee_id,
      last_name,
      job_id,
      salary 
    FROM
      employees 
    WHERE salary < ANY (
      SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
    ) AND job_id <> 'IT_PROG' ;
    
    #返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
    
    SELECT 
      employee_id,
      last_name,
      job_id,
      salary 
    FROM
      employees 
    WHERE salary < ALL (
      SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
    ) AND job_id <> 'IT_PROG' ;
    

    行子查询:查询员工编号最小并且工资最高的员工信息

    SELECT 
      * 
    FROM
      employees 
    WHERE (employee_id, salary) = (
    	SELECT MIN(employee_id), MAX(salary) FROM employees
    ) ;
    

    4、exists后面

    #查询有员工的部门名
    
    SELECT 
      department_name 
    FROM
      departments d 
    WHERE EXISTS (
    	SELECT * FROM employees e
    	WHERE e.`department_id` = d.`department_id`
    ) ;
    

    3.10、分页查询

    一、语法

    SELECT 
      查询列表 
    FROM1 别名1
    【连接类型】 JOIN2 别名2 ON 连接条件 
    【WHERE 分组前的筛选】
    【GROUP BY 分组字段】 
    【HAVING 分组后的筛选 】
    【ORDER BY 排序字段 ASC|DESCLIMIToffset, 】size ;
    

    二、特点

    1. limit语句放在查询语句的最后
    2. offset代表起始索引,起始索引从0开始,size代表条目个数
    3. 分页语句:select 查询列表 from 表 limit (page-1)*size,size;

    三、演示

    #查询前五条员工信息
    
    SELECT * FROM  employees LIMIT 0,5;
    

    3.11、联合查询

    一、语法

    查询语句1
    unionall】
    查询语句2
    unionall...
    

    二、特点

    1. 要查询的结果来自于多个表且多个表没有直接的连接关系,但查询的信息一致时,可以使用联合查询
    2. 要求多条查询语句的查询列数是一致的
    3. 要求多条查询语句的查询的每一列的类型和顺序最好一致
    4. union关键字默认去重,如果使用union all可以包含重复项

    三、演示

    #查询中国用户中男性的信息以及外国用户中年男性的用户信息
    
    SELECT id,cname FROM t_ca WHERE csex='男'
    UNION ALL
    SELECT t_id,tname FROM t_ua WHERE tGender='male';
    

    第四章 DML语言

    4.1、插入语句

    一、语法

    #方式一:
    INSERT INTO 表名(字段名,...) VALUES(,...);
    
    #方式二:
    INSERT INTO 表名 SET 字段名=,字段名=,...;
    

    二、特点

    1. 要求值的类型和字段的类型要一致或兼容
    2. 字段的个数和顺序不一定与原始表中的字段个数和顺序一致,但必须保证值和字段一一对应
    3. 假如表中有可以为null的字段,注意可以通过以下两种方式插入null值:①字段和值都省略、②字段写上,值使用null
    4. 字段和值的个数必须一致
    5. 字段名可以省略,默认所有列
    6. 方式一支持一次插入多行,语法如下:INSERT INTO 表名【(字段名,…)】 VALUES(值,…),(值,…),…;
    7. 方式一支持子查询,语法如下:INSERT INTO 表名 查询语句;

    三、演示

    1、方式一:插入数据

    INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) 
    			VALUES(15,'唐艺昕','女','1997-12-05','15633029014',NULL,2);
    

    2、方式二:插入数据

    INSERT INTO beauty SET 
        id = 19,name = '张倩倩',
        sex = '女',
        borndate = '1997-12-05',
        phone = '15633029014',
        photo = NULL,
        boyfriend_id = 3 ;
    

    4.2、修改语句

    一、语法

    1、单表更新
    UPDATE 表名 SET=,... WHERE 查询条件;
    
    2、多表更新
    #sql92语法: 
    UPDATE1 别名,2 别名 
    SET=,
      ...
    WHERE 连接条件 AND 筛选条件 ;
    
    #sql99语法: 
    UPDATE1 别名 
    INNER | LEFT | RIGHT JOIN2 别名 ON 连接条件
      SET=,
      ...
    WHERE 筛选条件 ;
    

    二、演示

    1、单表更新

    #修改beauty表中姓唐的女神的电话为13899888899
    
    UPDATE beauty SET phone = '13899888899' WHERE NAME LIKE '唐%';
    

    2、多表更新

    #修改张无忌的女朋友的手机号为13899888899,魅力值为1000
    sql92语法:
    UPDATE 
      boys bo,
      beauty b 
      SET b.`phone` = '13899888899',
          bo.`userCP` = 1000 
    WHERE bo.`id` = b.`boyfriend_id` AND bo.`boyName` = '张无忌' ;
    
    #修改张无忌的女朋友的手机号为13899888899,魅力值为1000
    sql99语法:
    UPDATE 
      boys bo 
    INNER JOIN beauty b ON bo.`id` = b.`boyfriend_id`
      SET b.`phone` = '13899888899',
          bo.`userCP` = 1000 
    WHERE bo.`boyName` = '张无忌' ;
    

    4.3、删除语句

    一、语法

    1、单表删除 
    DELETE FROM 表名 【WHERE 筛选条件 】;
    
    2、多表删除(级联删除)
    sql92语法: 
    DELETE1的别名,2的别名 
    FROM1 别名,2 别名 
    WHERE 连接条件 AND 筛选条件 ;
    
    sql99语法: 
    DELETE1的别名,2的别名 
    FROM1 别名 
    INNER | LEFT | RIGHT JOIN2 别名 ON 连接条件 
    WHERE 筛选条件 ;
    

    二、演示

    1、单表删除

    #删除手机号以9结尾的女神信息
    
    DELETE FROM beauty WHERE phone LIKE '%9';
    

    2、多表删除

    #删除黄晓明的信息以及他女朋友的信息
    #sql92语法:
    DELETE
      b,
      bo
    FROM
      beauty b,
      boys bo
    WHERE b.`boyfriend_id` = bo.`id` AND bo.`boyName` = '黄晓明' ; 
    
    #删除黄晓明的信息以及他女朋友的信息
    #sql99语法:
    DELETE 
      b,
      bo 
    FROM
      beauty b 
    INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id` 
    WHERE bo.`boyName` = '黄晓明' ;
    

    第五章 DDL语言

    5.1、库的管理

    5.1.1、库的创建

    CREATE DATABASEIF NOT EXISTS】 库名 【 CHARACTER SET 字符集名】;
    

    5.1.2、库的修改

    #它已经被废弃
    RENAME DATABASE 旧库名 TO 新库名;
    
    #修改库字符集
    ALTER DATABASE 库名 CHARACTER SET 字符集名;
    

    5.1.3、库的删除

    DROP DATABASEIF EXISTS】 库名;
    

    5.2、表的管理

    5.2.1、表的创建

    CREATE TABLEIF NOT EXISTS】 表名 (
      字段名 字段类型 【约束】,
      字段名 字段类型 【约束】,
      ...
      字段名 字段类型 【约束】
    ) ;
    

    5.2.2、表的修改

    1、添加列
    ALTER TABLE 表名 ADD COLUMN 列名 类型 【FIRST|AFTER 字段名】;
    
    2、修改列的类型或约束
    ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 【新约束】;
    
    3、修改列名
    ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;
    
    4、删除列
    ALTER TABLE 表名 DROP COLUMN 列名;
    
    5、修改表名
    ALTER TABLE 表名 RENAMETO】 新表名;
    

    5.2.3、表的删除

    方式一:DROP TABLEIF EXISTS】 表名;
    
    方式二:TRUNCATE TABLEIF EXISTS】 表名;
    

    5.2.4、表的复制

    1、复制表的结构
    CREATE TABLE 表名 LIKE 旧表;
    
    2、复制表的某些字段
    CREATE TABLE 表名 
    SELECT 字段1,字段2,... FROM 旧表 WHERE 0;
    
    3、复制表的结构+数据
    CREATE TABLE 表名 
    SELECT 查询列表 FROM 旧表 【WHERE 筛选条件】;
    
    4、复制表的某些字段+数据
    CREATE TABLE 表名 
    SELECT 字段1,字段2,... FROM 旧表 【WHERE 筛选条件】;
    

    5.3、数据类型

    5.3.1、数值型

    一、类型

    类型TINYINTSMALLINTMEDIUMINTINT/INTEGERBIGINT
    字节12348

    二、特点

    1. 都可以设置无符号和有符号,默认有符号,通过unsigned设置无符号
    2. 如果超出了范围,会报out or range异常,插入临界值(该类型的最大值或最小值即为临界值)
    3. 长度可以不指定,默认会有一个长度,长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill,并且默认变为无符号整型
    4. 如果对数据没有特殊要求,则优先考虑使用INT/INTEGER

    5.3.2、浮点型

    一、类型

    1. 定点数
      • DEC(M,D) :M+2字节
      • DECIMAL(M,D):M+2字节
    2. 浮点数
      • FLOAT(M,D) :4字节
      • DOUBLE(M,D):8字节

    二、特点

    1. M代表整数部位+小数部位的个数,D代表小数部位
    2. 如果超出范围,则报out or range异常,并且插入临界值(该类型的最大值或最小值即为临界值)
    3. M和D都可以省略,但对于定点数,M默认为10,D默认为0
    4. 如果精度要求较高,则优先考虑使用定点数

    5.3.3、字符型

    一、类型

    类型CHARVARCHARBINARYVARBINARYENUMSETTEXTBLOB
    描述固定长度字符可变长度字符二进制字符串二进制字符串枚举集合文本二进制大型对象

    二、特点

    1. char:固定长度的字符,写法为char(M),最大长度不能超过M,其中M可以省略,默认为1
    2. varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略
    3. 如果对数据没有特殊要求,则优先考虑使用VARCHAR

    5.3.4、日期型

    一、类型

    类型YEARDATETIMEDATETIMETIMESTAMP
    描述年份日期时间日期+时间日期+时间

    二、特点

    1. TIMESTAMP比较容易受时区、语法模式、版本的影响,更能反映当前时区的真实时间,而DATETIME则只能反映出插入时的当地时区
    2. TIMESTAMP支持的时间范围较小,DATETIME的取值范围:1000-1-1 — 9999-12-31
    3. TIMESTAMP的属性受Mysql版本和SQLMode的影响很大
    4. 如果对数据没有特殊要求,则优先考虑使用DATETIME

    5.4、常见约束

    一、含义

    约束是一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性

    二、分类

    1. NOT NULL:非空,该字段的值必填
    2. UNIQUE:唯一,该字段的值不可重复
    3. DEFAULT:默认,该字段的值不用手动插入有默认值
    4. CHECK:检查,MySQL不支持
    5. PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null
    6. FOREIGN KEY:外键,该字段的值引用了另外的表的字段

    三、特点

    主键和唯一

    #不同点:
    1、一个表至多有一个主键,但可以有多个唯一
    2、主键不允许为空,唯一可以为空
    
    #相同点:
    1、都具有唯一性
    2、都支持组合键,但不推荐
    

    外键

    1、用于限制两个表的关系,从表的字段值引用了主表的某字段值
    2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求
    3、主表的被引用列要求是一个key(一般就是主键)
    4、插入数据,先插入主表;删除数据,先删除从表
    
    
    可以通过以下两种方式来删除主表的记录
    #方式一:级联删除
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
    
    #方式二:级联置空
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
    

    四、演示

    1、创建表时添加约束

    CREATE TABLE 表名(
    	字段名 字段类型 NOT NULL,#非空
        字段名 字段类型 DEFAULT,#默认
    	字段名 字段类型 PRIMARY KEY,#主键
    	字段名 字段类型 UNIQUE,#唯一
    	CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表(被引用列)
    ) ;
    

    注意:

    1. 列级约束支持:非空、默认、主键、唯一,不可以起约束名
    2. 表级约束支持:主键、唯一、外键,可以起约束名,但是在MySQL中对主键无效
    3. 列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求

    2、修改表时添加或删除约束

    1、非空
    添加非空(列级约束)
    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NOT NULL;
    删除非空
    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;
    
    2、默认
    添加默认(列级约束)
    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 DEFAULT;
    删除默认
    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;
    
    3、主键
    添加主键(列级约束)
    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 PRIMARY KEY;
    添加主键(表级约束)
    ALTER TABLE 表名 addCONSTRAINT 约束名】 PRIMARY KEY(字段名);
    删除主键
    ALTER TABLE 表名 DROP PRIMARY KEY;
    
    4、唯一
    添加唯一(列级约束)
    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 UNIQUE;
    添加唯一(表级约束)
    ALTER TABLE 表名 addCONSTRAINT 约束名】 UNIQUE(字段名);
    删除唯一
    ALTER TABLE 表名 DROP INDEX 索引名;
    
    5、外键
    添加外键(表级约束)
    ALTER TABLE 表名 addCONSTRAINT 约束名】 FOREIGN KEY(字段名) REFERENCES 主表(被引用列);
    删除外键
    ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
    

    5.5、自增长列

    一、含义

    自增长列又称为标识列,它可以不用手动的插入值,系统提供默认的序列值

    二、特点

    1. 不用手动插入值,可以自动提供序列值,默认从1开始,步长为1,如果要更改起始值,第一次手动插入值,后续使用NULL,如果要更改步长,修改更改系统变量:SET auto_increment_increment = 值;
    2. 一个表至多有一个自增长列
    3. 自增长列只能支持数值型
    4. 自增长列必须为一个key

    三、演示

    1、创建表时添加自增长列

    CREATE TABLE 表名 (
      字段名 字段类型 约束 AUTO_INCREMENT
    ) ;
    

    2、修改表时添加或删除自增长列

    添加自增长列
    ALTER TABLEMODIFY COLUMN 字段名 字段类型 约束 AUTO_INCREMENT;
    
    删除自增长列
    ALTER TABLEMODIFY COLUMN 字段名 字段类型 约束;
    

    第六章 DCL语言

    关于授权的权限列表:

    image-20200809204539412

    6.1、创建用户

    CREATE USER 用户名@'IP地址' IDENTIFIED BY '密码';
    注意:'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
    

    6.2、删除用户

    DROP USER 用户名@'IP地址';
    注意:'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
    

    6.3、用户授权

    GRANT 权限1,权限2,...... ON 数据库名.* TO 用户名@'IP地址' IDENTIFIED BY '密码';
    注意:所有的数据库就用*.*,所有的权限就用all或者all privileges
    

    6.4、撤销授权

    REVOKE 权限1,权限2,...... ON 数据库名.* FROM 用户名@'IP地址' IDENTIFIED BY '密码';
    注意:所有的数据库就用*.*,所有的权限就用all或者all privileges
    

    6.5、刷新授权

    FLUSH PRIVILEGES;
    

    6.6、查看授权

    SHOW GRANTS FOR 用户名@'IP地址';
    注意:'IP地址'可以设置为localhost(代表本机)或者'%'(代表允许所有IP地址登录)
    

    6.7、修改密码

    #修改密码
    SET PASSWORD = PASSWORD('123456');
    #登录授权
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
    #刷新授权
    FLUSH PRIVILEGES;
    

    6.8、忘记密码

    1、可以在配置文件里加上 skip-grant-tables ,注意写到[mysqld]参数组下,表示跳过授权
    2、重启MySQL再登录就不需要密码,进去改密码,改完后,直接 FLUSH PRIVILEGES; 就可以使用新密码来登录了
    (例:UPDATE mysql.user SET PASSWORD=PASSWORD("123456") WHERE USER="root" AND HOST="localhost";)
    3、改完后记得去掉配置文件例的 skip-grant-tables,重新启动MySQL服务
    4、再使用新的密码登录就可以了
    

    第七章 TCL语言

    7.1、事务

    一、含义

    一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行

    二、特点(ACID)

    1. 原子性:一个事务是不可再分割的整体,要么都执行要么都不执行
    2. 一致性:一个事务的执行不能破坏数据库数据的完整性和一致性
    3. 隔离性:一个事务不受其它事务的干扰,多个事务是互相隔离的
    4. 持久性:一个事务一旦提交了,则永久的持久化到本地

    三、分类

    1. 隐式事务:没有明显的开启和结束,本身就是一条事务可以自动提交,比如insert、update、delete
    2. 显式事务:具有明显的开启和结束,例如以下格式:
    1、开启事务
    set autocommit=0;#关闭自动提交
    start transaction;#开启事务机制
    
    2、编写一组逻辑sql语句
    注意:sql语句支持的是insertupdatedelete
    
    【设置回滚点,可选项】
    savepoint 回滚点名;
    
    3、结束事务
    提交:commit;
    回滚:rollback;
    回滚到指定的地方: rollback to 回滚点名;
    

    7.2、事务并发(读问题)

    一、事物的并发问题如何发生?

    多个事务同时操作同一个数据库的相同数据时

    二、事务的并发问题都有哪些?

    1. 脏读:一个事务读到了另一个事务还未提交的update数据,导致多次查询的结果不一样
    2. 不可重复读:一个事务读到了另一个事务已经提交的update数据,导致多次查询结果不一致
    3. 幻读:一个事务读到了另一个事务已经提交的insert数据,导致多次查询的结果不一样

    三、事物的并发问题如何解决?

    通过设置隔离级别来解决并发问题

    四、隔离级别

    隔离级别隔离描述脏读不可重复读幻读
    READ UNCOMMITTED读未提交×××
    READ COMMITTED读已提交××
    REPEATABLE READ可重复读×
    SERIALIZABLE串行化

    五、注意问题

    1. mysql 中默认第三个隔离级别 REPEATABLE READ
      oracle中默认第二个隔离级别 READ COMMITTED
      
    2. 查看隔离级别 SELECT @@tx_isolation;
      设置隔离级别 SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
      

    7.3、丢失更新(写问题)

    一、定义

    在事务的隔离级别内容中,能够了解到两个不同的事务在并发的时候可能会发生数据的影响。细心的话可以发现事务隔离级别章节中,脏读、不可重复读、幻读三个问题都是由事务A对数据进行修改、增加,事务B总是在做读操作。如果两事务都在对数据进行修改则会导致另外的问题:丢失更新。

    二、解决

    1. 悲观锁:认为两个事务更新操作一定会发生丢失更新
      • 解决:通过在语句后边添加for update来实现行级上锁,所以又称为“行级锁”,例如:select * from t_account t wheret.id=‘1’ for update;
    2. 乐观锁:认为事务不一定会产生丢失更新,让事务进行并发修改,不对事务进行锁定
      • 解决:由程序员自己解决,可以通过给数据表添加自增的version字段或时间戳timestamp,进行数据修改时,数据库会检测version字段或者时间戳是否与原来的一致,若不一致,抛出异常或者重新查询

    三、注意

    对于账户交易建议直接使用悲观锁,数据库的性能很高,并发度不是很高的场景两者性能没有太大差别。如果是交易减库存的操作可以考虑乐观锁,保证并发度。

    第八章 高级部分

    8.1、索引

    一、含义

    索引(index)是帮助MySQL高效获取数据的一种有序的数据结构

    二、特点

    1. 优势:
      • 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本
      • 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗
    2. 劣势:
      • 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的
      • 虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE。因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息

    三、语法

    1、创建

    CREATEUNIQUE|FULLTEXT|SPATIAL 】 INDEX 索引名称 ON 表名(字段列表);
    

    2、修改

    先删除,在创建
    

    3、删除

    DROP INDEX 索引名称 ON 表名;
    

    4、查看

    SHOW INDEX FROM 表名;
    

    5、alter命令

    #该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL
    ALTER TABLE 表名 ADD PRIMARY KEY(字段列表); 
    	
    #这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)
    ALTER TABLE 表名 ADD UNIQUE 索引名称(字段列表);
    	
    #添加普通索引,索引值可以出现多次。
    ALTER TABLE 表名 ADD INDEX 索引名称(字段列表);
    	
    #该语句指定了索引为FULLTEXT,用于全文索引	
    ALTER TABLE 表名 ADD FULLTEXT 索引名称(字段列表);
    

    四、注意

    索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引:

    1. 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合
    2. 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗。另外索引过多的话,MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价
    3. 对查询频次较高,且数据量比较大的表建立索引
    4. 使用唯一索引,区分度越高,使用索引的效率越高
    5. 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率
    6. 使用组合索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。例如:CREATE INDEX idx_name_email_status ON tb_seller(name,email,status); 就相当于对name 创建索引;对name , email 创建了索引;对name , email, status 创建了索引

    8.2、视图

    一、含义

    MySQL在5.1以后推出了视图(VIEW),本身是一个虚拟表,它的数据来自于表,通过执行时动态生成

    二、特点

    1. 简化sql语句
    2. 提高了sql的重用性
    3. 保护基表的数据,提高了安全性

    三、语法

    1、创建

    CREATE VIEW 视图名
    AS
    查询语句;
    

    2、修改

    #方式一:
    CREATE OR REPLACE VIEW 视图名
    AS
    查询语句;
    
    #方式二:
    ALTER VIEW 视图名
    AS
    查询语句;
    

    3、删除

    DROP VIEW 视图1,视图2,...;
    

    4、查看

    #方式一:
    DESC 视图名;
    
    #方式二:
    SHOW CREATE VIEW 视图名;
    

    四、注意

    视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新:

    1. 包含分组函数、group by、distinct、having、union、join
    2. 常量视图
    3. where后的子查询用到了from中的表
    4. 用到了不可更新的视图

    8.3、存储过程

    8.3.1、语法

    一、含义

    存储过程,类似于Java中的方法,它是一组预先编译好的SQL语句的集合,理解成批处理语句

    二、特点

    1. 简化sql语句
    2. 提高了sql的重用性
    3. 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率

    三、语法

    1、创建

    #标准格式如下:
    DELIMITER $
    CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型,...)
    BEGIN
    	存储过程体(一组合法的SQL语句)
    END $
    DELIMITER ;
    
    #参数模式in:参数类型是输入的
    #参数模式out:参数类型是输出的
    #参数模式inout:参数类型既可以输入也可以输出
    
    #调用in模式的参数: CALL sp1('Hello,World');
    #调用out模式的参数: SET @name; CALL sp1(@name); SELECT @name;
    #调用inout模式的参数: SET @name=值; CALL sp1(@name); SELECT @name;
    

    2、修改

    先删除,在创建
    

    3、删除

    DROP PROCEDURE 存储过程名;
    

    4、查看

    SHOW CREATE PROCEDURE 存储过程名;
    

    8.3.2、变量

    一、分类

    1. 系统变量
      • 全局变量
      • 会话变量
    2. 自定义变量
      • 用户变量
      • 局部变量

    二、语法

    1、全局变量

    描述:服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效,但不能跨重启

    1、查看所有系统变量
    SHOW GLOBAL VARIABLES;
    
    2、查看满足条件的部分系统变量
    SHOW GLOBAL VARIABLES LIKE '%char%';
    
    3、查看指定的系统变量的值
    SELECT @@global 系统变量名;
    
    4、为某个系统变量赋值
    方式一: SET GLOBAL 系统变量名=;
    方式二: SET @@global 系统变量名=;
    

    2、会话变量

    描述:服务器为每一个连接的客户端都提供了系统变量,作用域为当前的连接(会话)

    1、查看所有系统变量
    SHOWSESSION 】 VARIABLES;
    
    2、查看满足条件的部分系统变量
    SHOWSESSION 】 VARIABLES LIKE '%char%';
    
    3、查看指定的系统变量的值
    SELECTSESSION 】系统变量名;
    
    4、为某个系统变量赋值
    SETSESSION 】系统变量名=;
    

    3、用户变量

    描述:针对于当前连接(会话)生效

    位置:可以在begin end里面,也可以放在外面

    1、声明并赋值
    SET @变量名=;SET @变量名:=;SELECT @变量名:=;
    
    2、更新值
    方式一:
    	SET @变量名=;SET @变量名:=;SELECT @变量名:=;
    方式二:
    	SELECT xxx INTO @变量名 FROM;
    	
    3、查看值
    SELECT @变量名;
    

    4、局部变量

    描述:作用域:仅仅在定义它的begin end中有效
    位置:只能放在begin end中,而且只能放在第一句

    1、声明并赋值
    DECLARE 变量名 类型 【 DEFAULT 值 】;
    
    2、更新值
    方式一:
    	SET 变量名=;SET 变量名:=;SELECT @变量名:=;
    方式二:
    	SELECT xxx INTO 变量名 FROM;
    	
    3、查看值
    SELECT 变量名;
    

    8.3.3、分支结构

    8.3.3.1、if结构

    一、语法

    注意:只能应用在begin end中

    IF 条件1 THEN 语句1;
    ELSEIF 条件2 THEN 语句2;
    ELSEIF 条件3 THEN 语句3;
    ....ELSE 语句n;END IF;
    

    二、演示

    #创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
    DELIMITER $
    CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
    BEGIN
    	DECLARE ch CHAR DEFAULT 'A';
    	IF score>90 THEN SET ch='A';
    	ELSEIF score>80 THEN SET ch='B';
    	ELSEIF score>60 THEN SET ch='C';
    	ELSE SET ch='D';
    	END IF;
    	RETURN ch;
    END $
    DELIMITER ;
    
    #函数调用
    SELECT test_if(87)$
    
    8.3.3.2、case结构

    一、语法

    注意:只能应用在begin end中

    语法1CASE 表达式或字段
    WHEN1 THEN 语句1;
    WHEN2 THEN 语句2;
    ...ELSE 语句n;ENDCASE;
    
    语法2CASE 
    WHEN 条件1 THEN 语句1;
    WHEN 条件2 THEN 语句2;
    ...ELSE 语句n;ENDCASE;
    

    二、演示

    #创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
    DELIMITER $
    CREATE FUNCTION test_case (score FLOAT) RETURNS CHAR 
    BEGIN
    	DECLARE ch CHAR DEFAULT 'A' ;
    	
    	CASE 
    	WHEN score>90 THEN SET ch='A';
    	WHEN score>80 THEN SET ch='B';
    	WHEN score>60 THEN SET ch='C';
    	ELSE SET ch='D';
    	END CASE;
    	
    	RETURN ch;
    END $
    DELIMITER ;
    
    #函数调用
    SELECT test_case(56);
    

    8.3.4、循环结构

    一、分类

    image-20200728162123754

    二、演示

    8.3.4.1、while结构
    #批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
    #删除过程
    DROP PROCEDURE IF EXISTS test_while;
    
    #定义过程
    DELIMITER $
    CREATE PROCEDURE test_while(IN insertCount INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	a:WHILE i<=insertCount DO
    		#LEAVE代表Java中的break关键字;ITERATE代表Java中的continue关键字
    		IF i>20 THEN LEAVE a;
    		END IF;
    		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
    		SET i=i+1;
    	END WHILE a;
    END $
    DELIMITER ;
    
    #调用过程
    CALL test_while(100);
    
    8.3.4.2、repeat结构
    #批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
    #删除过程
    DROP PROCEDURE IF EXISTS test_repeat;
    
    #定义过程
    DELIMITER $
    CREATE PROCEDURE test_repeat(IN insertCount INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	a:REPEAT 
    		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
    		SET i=i+1;
    	UNTIL i>20		
    	END REPEAT a;
    END $
    DELIMITER ;
    
    #调用过程
    CALL test_repeat(100);
    
    8.3.4.3、loop结构
    #批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
    #删除过程
    DROP PROCEDURE IF EXISTS test_loop;
    
    #定义过程
    DELIMITER $
    CREATE PROCEDURE test_loop(IN insertCount INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	a:LOOP 
    		#LEAVE代表Java中的break关键字;ITERATE代表Java中的continue关键字
    		IF i>20 THEN LEAVE a;
    		END IF;
    		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
    		SET i=i+1;
    	END LOOP a;
    END $
    DELIMITER ;
    
    #调用过程
    CALL test_loop(100);
    

    8.3.5、游标

    一、含义

    游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理

    二、语法

    1、声明游标

    DECLARE 游标名称 CURSOR FOR select结果集;
    

    2、打开游标

    OPEN 游标名称;
    

    3、获取游标

    FETCH 游标名称 INTO 变量列表(需要跟select结果集的字段对应);
    

    4、关闭游标

    CLOSE 游标名称;
    

    三、演示

    建数据表以用来演示

    CREATE TABLE emp (
      id INT (11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR (50) NOT NULL COMMENT '姓名',
      age INT (11) COMMENT '年龄',
      salary INT (11) COMMENT '薪水',
      PRIMARY KEY (`id`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
    
    INSERT INTO emp(id,NAME,age,salary) 
    VALUES
    (NULL,'金毛狮王',55,3800),
    (NULL,'白眉鹰王',60,4000),
    (NULL,'青翼蝠王',38,2800),
    (NULL,'紫衫龙王',42,1800);
    

    循环获取该表的内容

    DELIMITER $
    CREATE PROCEDURE getAllRowsForEmpTable()
    BEGIN
      DECLARE e_id INT(11);
      DECLARE e_name VARCHAR(50);
      DECLARE e_age INT(11);
      DECLARE e_salary INT(11);
      DECLARE has_data INT DEFAULT 1;
      
      DECLARE emp_result CURSOR FOR SELECT * FROM emp;
      DECLARE EXIT HANDLER FOR NOT FOUND SET has_data=0;
      
      OPEN emp_result;
      
      REPEAT
        FETCH emp_result INTO e_id,e_name,e_age,e_salary;
        SELECT CONCAT('id=',e_id , ', name=',e_name, ', age=', e_age, ', salary=',e_salary) as emp_info;
        UNTIL has_data = 0
      END REPEAT;
      
      CLOSE emp_result;
    END$
    DELIMITER ; 
    

    打开命令行调用查看

    CALL getAllRowsForEmpTable();
    

    8.4、函数

    一、含义

    其实函数就是一个有返回值的过程

    二、语法

    1、创建

    DELIMITER $
    CREATE FUNCTION 函数名(参数名 参数类型) 
    RETURNS 返回类型
    BEGIN
    	函数过程体(一组合法的SQL语句)
    END $
    DELIMITER ;
    

    2、修改

    先删除,在创建
    

    3、删除

    DROP FUNCTION 函数名;
    

    4、查看

    SHOW CREATE FUNCTION 函数名;
    

    5、调用

    SELECT 函数名(实参列表);
    

    三、注意

    1. 函数体中肯定需要有return语句
    2. 存储过程和函数的区别在于函数必须有返回值,而存储过程没有

    8.5、触发器

    一、含义

    MySQL在5.0以后推出了触发器(TRIGGER),触发器是与表有关的数据库对象,指在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在MySQL触发器还只支持行级触发,不支持语句级触发

    二、分类

    触发器类型NEW 和 OLD的使用
    INSERT 型触发器NEW 表示将要或者已经新增的数据
    UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
    DELETE 型触发器OLD 表示将要或者已经删除的数据

    三、语法

    1、创建

    DELIMITER $
    CREATE TRIGGER 触发器名称 
    BEFORE/AFTER INSERT/UPDATE/DELETE
    ON 表名称 
    【 FOR EACH ROW--行级触发器
    BEGIN
    	触发器过程体(一组合法的SQL语句)
    END $
    DELIMITER ;
    

    2、更新

    先删除,在创建
    

    3、删除

    DROP TRIGGER 触发器名称;
    

    4、查看

    SHOW TRIGGERS;
    

    四、演示

    创建数据表以用来演示

    CREATE TABLE emp (
      id INT (11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR (50) NOT NULL COMMENT '姓名',
      age INT (11) COMMENT '年龄',
      salary INT (11) COMMENT '薪水',
      PRIMARY KEY (`id`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
    INSERT INTO emp(id,NAME,age,salary) 
    VALUES
    (NULL,'金毛狮王',55,3800),
    (NULL,'白眉鹰王',60,4000),
    (NULL,'青翼蝠王',38,2800),
    (NULL,'紫衫龙王',42,1800);
    
    CREATE TABLE emp_logs (
      id INT (11) NOT NULL AUTO_INCREMENT,
      operation VARCHAR (20) NOT NULL COMMENT '操作类型, insert/update/delete',
      operate_time DATETIME NOT NULL COMMENT '操作时间',
      operate_id INT (11) NOT NULL COMMENT '操作表的ID',
      operate_params VARCHAR (500) COMMENT '操作参数',
      PRIMARY KEY (`id`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
    

    编写触发器进行测试

    创建 insert 型触发器,完成插入数据时的日志记录:

    DELIMITER $
    CREATE TRIGGER emp_logs_insert_trigger
    AFTER INSERT 
    ON emp 
    FOR EACH ROW 
    BEGIN
      INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'insert',NOW(),new.id,CONCAT('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
    END $
    DELIMITER ;
    

    创建 update 型触发器,完成更新数据时的日志记录:

    DELIMITER $
    CREATE TRIGGER emp_logs_update_trigger
    AFTER UPDATE 
    ON emp 
    FOR EACH ROW 
    BEGIN
      INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'update',NOW(),new.id,CONCAT('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
    END $
    DELIMITER ;
    

    创建delete 行的触发器 , 完成删除数据时的日志记录:

    DELIMITER $
    CREATE TRIGGER emp_logs_delete_trigger
    AFTER DELETE 
    ON emp 
    FOR EACH ROW 
    BEGIN
      INSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(NULL,'delete',NOW(),old.id,CONCAT('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
    END $
    DELIMITER ;
    

    依次调用以下的语句,查看emp_logs是否有触发器保存的日志记录

    INSERT INTO emp(id,NAME,age,salary) VALUES(NULL, '光明左使',30,3500);
    
    UPDATE emp SET age = 39 WHERE id = 3;
    
    DELETE FROM emp WHERE id = 5;
    

    8.6、事件

    一、含义

    MySQL在5.1以后推出了事件调度器(Event Scheduler),与triggers的事件触发不同,event类似与linux crontab计划任务,用于时间触发

    二、特点

    1. 事件是一组SQL集合,简单说就是mysql中的定时器,时间到了就执行
    2. 事件由一个特定的线程来管理的,也就是所谓的事件调度器,但是事件不能直接调用,通过单独或调用存储过程使用,在某一特定的时间点,触发相关的SQL语句或存储过程

    三、语法

    1、创建

    MySQL事件调度器event_scheduler负责调用事件,它默认是关闭的。这个调度器不断地监视一个事件是否要调用, 要创建事件,必须打开调度器。

    #开启事件调度器(任意一个都可以)
    SET GLOBAL event_scheduler = ON; 
    SET @@global.event_scheduler = ON; 
    SET GLOBAL event_scheduler = 1; 
    SET @@global.event_scheduler = 1; 
    
    #关闭事件调度器(任意一个都可以)
    SET GLOBAL event_scheduler = OFF; 
    SET @@global.event_scheduler = OFF; 
    SET GLOBAL event_scheduler = 0; 
    SET @@global.event_scheduler = 0;
    
    DELIMITER $
    CREATE EVENT 事件名称
    ON SCHEDULE 执行时间和频率
    -- 执行时间和频率有两种形式AT和EVERY
    -- 	使用 AT     关键字只会执行一次,格式如下:
    -- 	AT CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
    -- 	使用 EVERY  关键字指定时间间隔,格式如下:
    -- 	EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]
    
    -- 可选项,使用 STARTS 关键字指定开始时间,格式如下:
    -- STARTS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
    -- 可选项,使用 ENDS   关键字指定结束时间,格式如下:
    -- ENDS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
    
    -- [ON COMPLETION [NOT] PRESERVE] 
    -- 可选项,设定event的生命
    --		ON COMPLETION NOT PRESERVE :即计划任务执行完毕后自动drop该事件(默认)
    --		ON COMPLETION PRESERVE     :即计划任务执行完毕后不会drop该事件
    
    -- [ENABLE | DISABLE] 
    -- 可选项,设定event的状态
    --		ENABLE	:表示系统尝试执行这个事件(默认)
    --		DISABLE	:表示系统尝试关闭这个事件
    
    -- [COMMENT 'comment'] 
    -- 可选项,设定event的备注
    DO
    BEGIN
            /* 在这里写SQL语句或者调用存储过程 */
    END$
    DELIMITER ;
    

    2、修改

    先删除,在创建
    

    3、删除

    DROP EVENT 事件名称;
    

    4、查看

    SHOW EVENTS;
    

    5、调用

    #启用事件
    ALTER EVENT 事件名称 ENABLE;
    
    #禁用事件
    ALTER EVENT 事件名称 DISABLE;
    

    四、注意

    1. 默认创建事件存储在当前库中,也可显示指定事件创建在哪个库中
    2. 通过show events只能查看当前库中创建的事件
    3. 事件执行完即释放,如立即执行事件,执行完后,事件便自动删除,多次调用事件或等待执行事件可以查看到
    4. 如果两个事件需要在同一时刻调用,mysql会确定调用他们的顺序,如果要指定顺序,需要确保一个事件至少在另一个事件1秒后执行
    5. 对于递归调度的事件,结束日期不能在开始日期之前
    6. select可以包含在一个事件中,然而他的结果消失了,就好像没执行过

    第九章 数据备份与恢复

    9.1、数据导出

    1、导出某张数据表

    #格式:mysqldump -u账户 -p密码 数据库名 数据表名 > 文件保存路径
    mysqldump -uroot -p123456 test emp > D:/emp.sql
    

    2、导出整个数据库

    #格式:mysqldump -u账户 -p密码 数据库名 > 文件保存路径
    mysqldump -uroot -p123456 test > D:/test.sql
    

    9.2、数据导入

    1、导入某张数据表

    #格式:mysql -u账户 -p密码
    #     use 数据库名;
    #     source 文件保存路径
    mysql -uroot -p123456
    >use test;
    >source D:/emp.sql
    

    2、导入整个数据库

    #格式:mysql -u账户 -p密码 < 文件保存路径
    mysql -uroot -p123456 < test.sql
    

    第十章 安装MySQL单实例

    10.1、准备虚拟机

    虚拟机配置:全新系统,桌面版

    image-20200824234229205

    虚拟机地址:进入系统,可上网

    image-20200824225209337

    连接的工具:自行百度,在下载

    SecureCRT:7.0.0

    10.2、删除自带库

    [root@caochenlei ~]# rpm -qa | grep mysql
    mysql-libs-5.1.73-8.el6_8.x86_64
    [root@caochenlei ~]# rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps
    

    10.3、安装依赖包

    [root@caochenlei ~]# yum -y install numactl perl libaio wget
    

    10.4、下载服务端

    下载:

    [root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm
    

    查看:

    [root@caochenlei ~]# ll MySQL-server-5.6.49-1.el6.x86_64.rpm
    -rw-r--r--. 1 root root 65467136 6月   3 13:36 MySQL-server-5.6.49-1.el6.x86_64.rpm
    

    10.5、安装服务端

    [root@caochenlei ~]# rpm -ivh MySQL-server-5.6.49-1.el6.x86_64.rpm
    

    10.6、下载客户端

    下载:

    [root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-client-5.6.49-1.el6.x86_64.rpm
    

    查看:

    [root@caochenlei ~]# ll MySQL-client-5.6.49-1.el6.x86_64.rpm
    -rw-r--r--. 1 root root 21584380 6月   3 13:36 MySQL-client-5.6.49-1.el6.x86_64.rpm
    

    10.7、安装客户端

    [root@caochenlei ~]# rpm -ivh MySQL-client-5.6.49-1.el6.x86_64.rpm
    

    10.8、启动数据库服务

    [root@caochenlei ~]# service mysql start
    Starting MySQL.Logging to '/var/lib/mysql/caochenlei.err'.
    ..                                                         [确定]
    

    10.9、查看初始化密码

    [root@caochenlei ~]# cat /root/.mysql_secret
    # The random password set for the root user at Mon Aug 24 23:06:53 2020 (local time): E4qT_4CJ9YNf0daG
    

    10.10、修改初始化密码

    登录:

    [root@caochenlei ~]# mysql -uroot -pE4qT_4CJ9YNf0daG
    

    修改:

    mysql> set password = password('123456');
    Query OK, 0 rows affected (0.01 sec)
    

    10.11、远程的授权

    授权:

    mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
    Query OK, 0 rows affected (0.00 sec)
    

    刷新:

    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    

    退出:

    mysql> exit
    Bye
    

    10.12、开放防火墙

    [root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
    [root@caochenlei ~]# /etc/rc.d/init.d/iptables save
    iptables:将防火墙规则保存到 /etc/sysconfig/iptables:     [确定]
    

    10.13、添加自启动

    [root@caochenlei ~]# chkconfig --add mysql
    [root@caochenlei ~]# chkconfig mysql on
    

    10.14、测试连接

    image-20200824232120251

    10.15、关闭系统

    [root@caochenlei ~]# poweroff
    
    Broadcast message from root@caochenlei
            (/dev/pts/1) at 23:24 ...
    
    The system is going down for power off NOW!
    

    第十一章 安装MySQL主从复制

    11.1、准备虚拟机

    复制CentOS 6 64 bit-1 > CentOS 6 64 bit-2

    image-20200824233313817

    启动CentOS 6 64 bit-1:

    image-20200824233645840

    启动CentOS 6 64 bit-2:选择“我已复制该虚拟机”

    image-20200824234110546

    11.2、主从复制介绍

    主从复制允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。

    11.3、主从复制架构

    实现目标:一主一从

    image-20200825003631169

    11.4、主节点配置

    编辑:

    注意:192.168.206.128为主节点

    [root@caochenlei ~]# vi /usr/my.cnf
    

    添加:

    注意:在[mysqld]下一行添加以下信息

    #日志名称
    log-bin=mysql-bin
    #日志格式:row、statement、mixed
    binlog-format=row
    #服务器ID标识
    server-id=1
    #刷写事务日志
    innodb_flush_log_at_trx_commit=1
    #执行写入1次同步1次
    sync_binlog=1
    #及时更新master_info
    sync_master_info=1
    

    重启:

    [root@caochenlei ~]# service mysql restart
    Shutting down MySQL...                                     [确定]
    Starting MySQL.....                                        [确定]
    

    连接:

    [root@caochenlei ~]# mysql -uroot -p123456
    

    授权:

    mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
    Query OK, 0 rows affected (0.03 sec)
    

    刷新:

    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    

    查看:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 397
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 
    1 row in set (0.00 sec)
    

    11.5、从节点配置

    编辑:

    注意:192.168.206.129为从节点

    [root@caochenlei ~]# vi /usr/my.cnf
    

    添加:

    注意:在[mysqld]下一行添加以下信息

    #日志名称
    log-bin=mysql-bin
    #日志格式:row、statement、mixed
    binlog-format=row
    #服务器ID标识
    server-id=2
    #是否只读
    read_only=1
    

    停止:

    [root@caochenlei ~]# service mysql stop
    Shutting down MySQL..                                      [确定]
    

    删除:

    注意:这里不能直接重启mysql是因为它们的auto.cnf中的server-uuid是一样的,在进行接下来的配置的时候会失败,所以要删除从库的server-uuid,让它在启动的时候自动生成一个全新的server-uuid

    [root@caochenlei ~]# rm -f /var/lib/mysql/auto.cnf
    

    启动:

    [root@caochenlei ~]# service mysql start
    Starting MySQL...                                          [确定]
    

    连接:

    [root@caochenlei ~]# mysql -uroot -p123456
    

    执行:

    注意:MASTER_LOG_FILE=show master status的File、MASTER_LOG_POS=show master status的Position

    CHANGE MASTER TO
    MASTER_HOST='192.168.206.128',
    MASTER_PORT=3306,
    MASTER_USER='root',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=397;
    

    开启:

    mysql> start slave;
    Query OK, 0 rows affected (0.03 sec)
    

    查看:

    注意:重点检查Master_Log_File、Read_Master_Log_Pos是否和主节点的信息一致,Slave_IO_Running、Slave_SQL_Running是否为YES

    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.206.128
                      Master_User: root
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 397
                   Relay_Log_File: caochenlei-relay-bin.000002
                    Relay_Log_Pos: 283
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 397
                  Relay_Log_Space: 461
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: d7200ffb-e698-11ea-87e0-000c29e99cce
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
    1 row in set (0.02 sec)
    

    11.6、连接测试

    测试连接:192.168.206.128(master)

    image-20200825014102540

    创建数据库:

    CREATE DATABASE `mytest` CHARACTER SET utf8 ;
    

    创建数据表:

    CREATE TABLE `mytest`.`myuser` (
      `id` BIGINT NOT NULL AUTO_INCREMENT,
      `username` VARCHAR (20) NOT NULL,
      `password` VARCHAR (20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ;
    

    添加数据集:

    INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('1', 'zhangsan', '123456'); 
    INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('2', 'lisi', '123456'); 
    INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('3', 'wangwu', '123456'); 
    

    查看数据集:

    use mytest;
    SELECT * FROM myuser;
    

    连接测试:192.168.206.129(slave)

    image-20200825014729798

    查看数据集:

    USE mytest; 
    SELECT * FROM myuser;
    

    最终的结论:

    我们会发现我们只在192.168.206.128(master)创建数据库、创建表、添加数据,而它的从节点会自动的拉取主节点的内容,这就是所谓的主从复制了。

    第十二章 安装MySQL读写分离

    12.1、环境准备

    复制CentOS 6 64 bit-2 > CentOS 6 64 bit-3

    注意:因为CentOS 6 64 bit-2已经是配置好的slave节点了,我们只需要做少量修改就能实现一主两从的架构了,所以我们复制CentOS 6 64 bit-2,当然,你要是时间允许,机器性能允许,您也可以重新按照主从复制的步骤在搭建一台slave,在这里我们就不搭建了,只是进行一下复制修改,复制的时候,注意关机,先关闭slave,再关闭master,再复制,再重命名,先启动master,在启动slave,在启动新复制的那台slave

    image-20200825111116521

    启动CentOS 6 64 bit-3:选择“我已复制该虚拟机”

    image-20200825142527374

    12.2、读写分离介绍

    以上已经完成了主从复制(一主一从)配置,然而这只是一个基本配置,加上一个proxysql实现MySQL读写分离,proxysql支持MySQL协议的的数据库代理,程序访问的不再是具体的数据库,而是proxysql这个代理程序,用户请求发向proxysql,如果是写请求发往主节点,读请求发下从节点组中,以此实现读写分离一定程序上减轻了主数据库的io压力。

    12.3、读写分离架构

    实现目标:一主两从,基于上文中的一主一从,我们重新复制一个从节点,进行少量修改即可

    image-20200825201051126

    12.4、修改从节点二

    注意:主节点:CentOS 6 64 bit-1【128】、从节点一:CentOS 6 64 bit-2【129】、从节点二:CentOS 6 64 bit-3【130】

    停止:

    [root@caochenlei ~]# service mysql stop
    Shutting down MySQL..                                      [确定]
    

    删除:

    [root@caochenlei ~]# rm -f /var/lib/mysql/auto.cnf
    

    修改:

    [root@caochenlei ~]# vi /usr/my.cnf
    
    #服务器ID标识
    server-id=3
    

    启动:

    [root@caochenlei ~]# service mysql start
    Starting MySQL.......                                      [确定]
    

    登录:

    [root@caochenlei ~]# mysql -uroot -p123456
    

    查看:

    USE mytest; 
    SELECT * FROM myuser;
    

    测试:

    第一步:在主节点(192.168.206.128)插入数据

    INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('9', 'xiaojiu', '123456');
    
    USE mytest; 
    SELECT * FROM myuser;
    

    第二步:在从节点(192.168.206.129)查看数据

    USE mytest; 
    SELECT * FROM myuser;
    

    第三步:在从节点(192.168.206.130)查看数据

    USE mytest; 
    SELECT * FROM myuser;
    

    注意:如果说从节点一或者从节点二有一个不正常工作,也就是不能主从复制,主要是它们的服务器ID标识复制的时候是一样的,开机因为已经添加了自启动,所以肯定会冲突,有一个不能够连接到主节点,只要这个时候重启一下不能正常工作的那台机器的mysql服务就行了,因为从节点二我们已经修改server-id=3了,这样它们两个从节点肯定不会冲突了,到这里,一主两从的模式就搭建好了,接下来就是配置读写分离了。

    12.5、安装proxysql

    设下载源:

    cat << EOF | tee /etc/yum.repos.d/proxysql.repo
    [proxysql_repo]
    name= ProxySQL YUM repository
    baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
    gpgcheck=1
    gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
    EOF
    

    安装依赖:

    [root@caochenlei ~]# yum install -y mysql-libs perl-DBI perl-DBD-MySQL
    

    开始安装:

    [root@caochenlei ~]# yum install -y proxysql-2.0.13-1
    

    开放端口:

    [root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 6032 -j ACCEPT
    [root@caochenlei ~]# /etc/rc.d/init.d/iptables save
    iptables:将防火墙规则保存到 /etc/sysconfig/iptables:[确定]
    
    [root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 6033 -j ACCEPT
    [root@caochenlei ~]# /etc/rc.d/init.d/iptables save
    iptables:将防火墙规则保存到 /etc/sysconfig/iptables:[确定]
    

    添加自启动:

    [root@caochenlei ~]# chkconfig --add proxysql
    [root@caochenlei ~]# chkconfig proxysql on
    

    删除文件:

    [root@caochenlei ~]# rm -f /etc/proxysql.cnf
    

    新增文件:

    [root@caochenlei ~]# vi /etc/proxysql.cnf
    
    datadir="/var/lib/proxysql"
    errorlog="/var/lib/proxysql/proxysql.log"
    admin_variables=
    {
            #本地登录的账号和密码
            admin_credentials="admin:admin"
            #本地登录的IP和端口号
            mysql_ifaces="0.0.0.0:6032"
    }
    mysql_variables=
    {
            threads=4
            max_connections=2048
            default_query_delay=0
            default_query_timeout=36000000
            have_compress=true
            poll_timeout=2000
            #远程登录的IP和端口号
            interfaces="0.0.0.0:6033"
            default_schema="information_schema"
            stacksize=1048576
            server_version="5.5.30"
            connect_timeout_server=3000
            #监控账号的账号和密码
            monitor_username="monitor"
            monitor_password="monitor"
            monitor_history=600000
            monitor_connect_interval=60000
            monitor_ping_interval=10000
            monitor_read_only_interval=1500
            monitor_read_only_timeout=500
            ping_interval_server_msec=120000
            ping_timeout_server=500
            commands_stats=true
            sessions_sort=true
            connect_retries_on_failure=10
    }
    #以下的配置我们会采用动态语句的形式配置
    mysql_servers =
    (
    )
    mysql_users:
    (
    )
    mysql_query_rules:
    (
    )
    scheduler=
    (
    )
    mysql_replication_hostgroups=
    (
    )
    

    注意:6032是连接proxysq的管理端口,6033是对外提供服务的端口。

    启动服务:

    [root@caochenlei ~]# service proxysql start
    Starting ProxySQL: 2020-08-25 20:30:00 [INFO] Using config file /etc/proxysql.cnf
    2020-08-25 20:30:00 [INFO] Using OpenSSL version: OpenSSL 1.1.1d  10 Sep 2019
    2020-08-25 20:30:00 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
    DONE!
    

    12.6、删除自带库

    [root@caochenlei ~]# rpm -qa | grep mysql
    mysql-libs-5.1.73-8.el6_8.x86_64
    [root@caochenlei ~]# rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps
    

    12.7、安装依赖包

    [root@caochenlei ~]# yum -y install numactl perl libaio wget
    

    12.8、下载服务端

    下载:

    [root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-server-5.6.49-1.el6.x86_64.rpm
    

    查看:

    [root@caochenlei ~]# ll MySQL-server-5.6.49-1.el6.x86_64.rpm
    -rw-r--r--. 1 root root 65467136 6月   3 13:36 MySQL-server-5.6.49-1.el6.x86_64.rpm
    

    12.9、安装服务端

    [root@caochenlei ~]# rpm -ivh MySQL-server-5.6.49-1.el6.x86_64.rpm
    

    12.10、下载客户端

    下载:

    [root@caochenlei ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/MySQL-client-5.6.49-1.el6.x86_64.rpm
    

    查看:

    [root@caochenlei ~]# ll MySQL-client-5.6.49-1.el6.x86_64.rpm
    -rw-r--r--. 1 root root 21584380 6月   3 13:36 MySQL-client-5.6.49-1.el6.x86_64.rpm
    

    12.11、安装客户端

    [root@caochenlei ~]# rpm -ivh MySQL-client-5.6.49-1.el6.x86_64.rpm
    

    12.12、启动数据库服务

    [root@caochenlei ~]# service mysql start
    Starting MySQL.Logging to '/var/lib/mysql/caochenlei.err'.
    ..                                                         [确定]
    

    12.13、查看初始化密码

    [root@caochenlei ~]# cat /root/.mysql_secret
    # The random password set for the root user at Tue Aug 25 20:31:19 2020 (local time): IFIn7pcTznYpoLm1
    

    12.14、修改初始化密码

    登录:

    [root@caochenlei ~]# mysql -uroot -pIFIn7pcTznYpoLm1
    

    修改:

    mysql> set password = password('123456');
    Query OK, 0 rows affected (0.01 sec)
    

    退出:

    mysql> exit
    Bye
    

    12.15、创建两个账号

    这个操作是在master节点进行的,创建完成后,会自动复制到从节点,方便proxysql后来的监控和远程登录的认证连接

    注意:可以使用 select user,host from mysql.user; 查看所有用户

    为proxysql创建监控账号(主要做监控使用):

    create user 'monitor'@'%' identified by 'monitor';
    grant all privileges on *.* to 'monitor'@'%' identified by 'monitor';
    flush privileges;
    

    为proxysql创建远程账号(主要是远程登录):

    create user 'proxysql'@'%' identified by 'proxysql';
    grant all privileges on *.* to 'proxysql'@'%' identified by 'proxysql';
    flush privileges;
    

    退出:

    mysql> exit
    Bye
    

    12.16、配置proxysql

    这个操作是在安装proxysql的这台机器上的操作,6032端口的默认账号密码为admin,如果想要修改请在第12.5步修改,然后重启。

    [root@caochenlei ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin>'
    

    分别执行以下语句

    # 配置主从复制的信息
    insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(10,'192.168.206.128',3306,1,1000,10,'write mysql');
    insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'192.168.206.129',3306,1,10000,10,'read mysql');
    insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(20,'192.168.206.130',3306,1,10000,10,'read mysql');
    
    # 本地主机登录的账号:默认就是admin:admin不用再配置
    
    # 配置远程登录的账号:之前在master主节点中已经创建了
    insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql',1,20,1);
    
    # 设置转发规则
    INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
    INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);
    
    # 立刻加载配置
    load mysql servers to runtime;
    load mysql users to runtime;
    load mysql variables to runtime;
    load mysql query rules to runtime;
    
    # 配置写到磁盘
    save mysql servers to disk;
    save mysql users to disk;
    save mysql variables to disk;
    save mysql query rules to disk;
    

    执行完成以后退出

    12.17、连接proxysql

    注意:如果远程连接不上,请把防火墙全部关掉试试,如果不行,请重新启动一下proxysql,还是不行,请重头排查一下问题,或者查看日志tail /var/lib/proxysql/proxysql.log

    第一种:远程命令行连接测试

    主节点、两个从节点随便找一台测试一下:

    ##连接
    [root@caochenlei ~]# mysql -uproxysql -pproxysql -h192.168.206.131 -P6033
    
    ##查看所有数据库
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | mytest             |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.05 sec)
    

    第二种:远程图形化窗口连接

    注意这个端口是6033而不是3306,账号和密码我之前设置的都是proxysql

    image-20200825212223601

    12.18、查询监控数据

    如果想要查看sql语句在哪个数据库执行的,在proxysql机器上请执行以下语句

    [root@caochenlei ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin>'
    admin> select * from stats_mysql_query_digest;
    

    第十三章 安装MySQL分片集群

    13.1、环境准备

    我们使用之前的读写分离时候配置的一主两从这三台服务器,至于那个读写分离的数据库中间件proxysql服务器就用不着了,直接关机就行了,以下三台依次从主节点开始启动即可也就是CentOS 6 64 bit-1、CentOS 6 64 bit-2、CentOS 6 64 bit-3

    image-20200825111116521

    13.2、分片集群介绍

    如今随着互联网的发展,数据的量级也是撑指数的增长,从GB到TB到PB。对数据的各种操作也是愈加的困难,传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性,减少对事务的支持,减少对复杂查询的支持,来获取性能上的提升。

    但是,在有些场合NoSQL一些折衷是无法满足使用场景的,就比如有些使用场景是绝对要有事务与安全指标的。这个时候NoSQL肯定是无法满足的,所以还是需要使用关系性数据库。如果使用关系型数据库解决海量存储的问题呢?此时就需要做数据库集群,为了提高查询性能将一个数据库的数据分散到不同的数据库中存储。简单来说,就是指通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)上面,以达到分散单台设备负载的效果。而这其中,我们需要使用一个数据库中间件来完成,它就是Mycat。

    13.3、分片集群架构

    实现目标:一主两从需要三组,再加上一个数据库中间件服务器,一共需要10台

    image-20200825220142607

    如何演示:但实际上呢,这个分片集群是在主从复制模式上进行的演进,说白了,上边的3组一主两从,随便拿出来一个都是一个主从复制模式,那我们前一章已经完成了一主两从的学习,也就是已经配置好了这三台服务器,我们要是再拷贝出来两组就显得太麻烦了,学习嘛,怎么简单怎么来,现在我们手头只有一组,那我们就在这一组上进行演示,也就是一组上创建三个数据库,我们学习的架构就是下边这样了和上边的效果一模一样,如下图:

    image-20200826093129272

    13.4、Mycat的介绍

    Mycat 背后是阿里曾经开源的知名产品——Cobar。Cobar 的核心功能和优势是 MySQL 数据库分片,此产品曾经广为流传,据说最早的发起者对 MySQL 很精通,后来从阿里跳槽了,阿里随后开源的 Cobar,并维持到 2013 年年初,然后,就没有然后了。

    Cobar 的思路和实现路径的确不错。基于 Java 开发的,实现了 MySQL 公开的二进制传输协议,巧妙地将自己伪装成一个 MySQL Server,目前市面上绝大多数 MySQL 客户端工具和应用都能兼容。比自己实现一个新的数据库协议要明智的多,因为生态环境在哪里摆着。

    Mycat 是基于 cobar 演变而来,对 cobar 的代码进行了彻底的重构,使用 NIO 重构了网络模块,并且优化了 Buffer 内核,增强了聚合,Join 等基本特性,同时兼容绝大多数数据库成为通用的数据库中间件。

    简单的说,MyCAT就是一个新颖的数据库中间件产品,支持mysql集群或者mariadb集群,提供高可用性数据分片集群。你可以像使用mysql一样使用mycat。对于开发人员来说根本感觉不到mycat的存在。

    它支持的数据库产品非常的多如:

    image-20200825215043387

    13.5、Mycat的分片

    分片就是指通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)上面,以达到分散单台设备负载的效果。

    数据的切分(Sharding)根据其切分规则的类型,可以分为两种切分模式。

    (1)一种是按照不同的表(或者Schema)来切分到不同的数据库(主机)之上,这种切分可以称之为数据的垂直(纵向)切分

    img

    (2)另外一种则是根据表中的数据的逻辑关系,将同一个表中的数据按照某种条件拆分到多台数据库(主机)上面,这种切分称之为数据的水平(横向)切分。

    image-20200825215350981

    13.6、Mycat的安装

    官方网址:Mycat

    默认端口:8066

    JDK:要求jdk必须是1.7及以上版本

    MySQL:推荐mysql是5.5及以上版本

    安装JDK8:

    #查看软件
    rpm -qa | grep java
    rpm -qa | grep jdk
    
    #批量卸载
    rpm -qa | grep jdk | xargs rpm -e --nodeps
    rpm -qa | grep java | xargs rpm -e --nodeps
    
    #在线安装
    yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
    
    #通过yum方式安装默认安装在/usr/lib/jvm文件下,看看你自己安装的具体是哪个版本
    ll /usr/lib/jvm/
    
    #编辑/etc/profile文件,在文件最后写入环境变量,然后保存退出
    vi /etc/profile
    
    # java environment
    export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.262.b10-0.el6_10.x86_64
    export JRE_HOME=$JAVA_HOME/jre
    export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/jre/lib/tools.jar:$JRE_HOME/lib:$CLASSPATH
    export PATH=$JAVA_HOME/bin:$PATH
    
    #使环境变量生效
    source /etc/profile
    
    #查看安装是否成功,这两个都能出来就安装成功了
    [root@caochenlei ~]# java -version
    openjdk version "1.8.0_262"
    OpenJDK Runtime Environment (build 1.8.0_262-b10)
    OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)
    [root@caochenlei ~]# javac -version
    javac 1.8.0_262
    

    下载:

    注意:失效的话,请自行百度,然后上传到虚拟机

    [root@caochenlei ~]# wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
    

    解压:

    [root@caochenlei ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
    

    安装:

    [root@caochenlei ~]# mv mycat /usr/local
    

    添加防火墙:

    注意:mycat数据端口默认为8066,管理端口默认为9066,我们都需要对外开放

    [root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 8066 -j ACCEPT
    [root@caochenlei ~]# /sbin/iptables -I INPUT -p tcp --dport 9066 -j ACCEPT
    [root@caochenlei ~]# /etc/rc.d/init.d/iptables save
    iptables:将防火墙规则保存到 /etc/sysconfig/iptables:[确定]
    

    添加域名解析:

    [root@caochenlei ~]# vi /etc/hosts
    
    127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
    ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
    
    #配置当前主机名称的域名解析
    192.168.206.128 caochenlei
    
    #配置一主两从服务的域名解析
    192.168.206.128 master1
    192.168.206.129 slave1
    192.168.206.130 slave2
    

    常用命令:

    • /usr/local/mycat/bin/mycat start 启动
    • /usr/local/mycat/bin/mycat stop 停止
    • /usr/local/mycat/bin/mycat console 前台运行,可以显示日志,新手建议使用
    • /usr/local/mycat/bin/mycat restart 重启服务
    • /usr/local/mycat/bin/mycat pause 暂停
    • /usr/local/mycat/bin/mycat status 查看启动状态

    13.7、Mycat的配置

    第一步:登录主节点创建三个数据库分别是db1、db2、db3

    CREATE DATABASE `db1`CHARACTER SET utf8; 
    CREATE DATABASE `db2`CHARACTER SET utf8; 
    CREATE DATABASE `db3`CHARACTER SET utf8; 
    

    第二步:修改schema.xml文件

    删除原有配置

    [root@caochenlei ~]# rm -f /usr/local/mycat/conf/schema.xml
    

    新增我们配置

    [root@caochenlei ~]# vi /usr/local/mycat/conf/schema.xml
    
    <?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    	<schema name="STOREDB" checkSQLschema="false" sqlMaxLimit="100">
    		<!-- rule采用按主键范围分片,主键名为id(默认) -->
    		<table name="TB_USERS" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
    		<!-- rule采用按哈希一致分片,主键名为order_id(自己修改) -->
    		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur-order" />
    	</schema>
    
    	<dataNode name="dn1" dataHost="OneMasterAndTwoSlave" database="db1" />
    	<dataNode name="dn2" dataHost="OneMasterAndTwoSlave" database="db2" />
    	<dataNode name="dn3" dataHost="OneMasterAndTwoSlave" database="db3" />
    
    	<dataHost name="OneMasterAndTwoSlave" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<!-- 心跳语句 -->
    		<heartbeat>select user()</heartbeat>
    		<!-- 一主两从 -->
    		<writeHost host="hostM1" url="master1:3306" user="root" password="123456">
    			<readHost host="hostS1" url="slave1:3306" user="root" password="123456" />
    			<readHost host="hostS2" url="slave2:3306" user="root" password="123456" />
    		</writeHost>
    	</dataHost>
    </mycat:schema>
    

    第三步:修改server.xml文件

    server.xml几乎保存了所有mycat需要的系统配置信息,最常用的是在此配置用户名、密码及权限。

    [root@caochenlei ~]# vi /usr/local/mycat/conf/server.xml
    

    在system中添加UTF-8字符集设置,否则存储中文会出现问号。

    <property name="charset">utf8</property>
    

    修改user的设置 , 我们这里为STOREDB数据库设置了两个用户。把它之前的两个用户删掉,把咱们自己设置的放进去。

    <user name="test">
    	<property name="password">test</property>
    	<property name="schemas">STOREDB</property>
    </user>
    <user name="root">
    	<property name="password">root</property>
    	<property name="schemas">STOREDB</property>
    </user>
    

    第四步:修改auto-sharding-rang-mod.txt文件

    修改主键范围分片大小

    删除:

    [root@caochenlei ~]# rm -f /usr/local/mycat/conf/auto-sharding-rang-mod.txt
    

    新增:

    [root@caochenlei ~]# vi /usr/local/mycat/conf/auto-sharding-rang-mod.txt
    
    0-500M=0
    500M1-1000M=1
    1000M1-1500M=2
    

    注意:以上意思是主键id在0-500M的数据放在分片1中,500M1-1000M的数据放到分片2中,1000M1-1500M的数据放到分片3中,超过1500M1,就报错

    第五步:修改rule.xml文件

    [root@caochenlei ~]# vi /usr/local/mycat/conf/rule.xml
    

    新增哈希订单主键,因为默认主键名都是id,要想是自己定义的,就拷贝一份,重新起个名,然后修改columns成你数据表的那个主键名称就行了

            <tableRule name="sharding-by-murmur-order">
                    <rule>
                            <columns>order_id</columns>
                            <algorithm>murmur</algorithm>
                    </rule>
            </tableRule>
    

    修改分片数量,因为现在有db1、db2、db3个分片,找到<function name="murmur"中的count,修改为3,如以下所示:

            <function name="murmur"
                    class="io.mycat.route.function.PartitionByMurmurHash">
                    <property name="seed">0</property><!-- 默认是0 -->
                    <property name="count">3</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 -->
                    <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就是虚拟节点数是物理节点数的160倍 -->
                    <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重,没有指定权重的节点默认是1。以properties文件的格式填写,以从0开始到count-1的整数值>也就是节点索引为key,以节点权重值为值。所有权重值必须是正整数,否则以1代替 -->
                    <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
                            用于测试时观察各物理节点与虚拟节点的分布情况,如果指定了这个属性,会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件,没有默认值,如果不
    指定,就不会输出任何东西 -->
            </function>
    

    13.8、Mycat的启动

    在启动之前,我们需要做一个密码重置的工作,也不知道这是不是个BUG,但是,你要不做密码重置,很可能Mycat它启动不起来,我也是醉了,就这一个问题,搞了我一晚上的心态,操作步骤如下:

    依次在主节点、从节点一、从节点二执行以下四条语句:

    use mysql;
    update user set password = password('123456') where user = 'root';
    flush privileges;
    set password for 'root' = password('123456');
    #如果上句报错使用下边这个,不报错就算了
    set password = password('123456');
    

    然后再启动Mycat

    [root@caochenlei ~]# /usr/local/mycat/bin/mycat start
    Starting Mycat-server...
    

    13.9、Mycat的测试

    注意:在这里我使用图形化界面来操作,先测试是否连通,如果不能连上,请检查虚拟机防火墙是否关闭,那两个用户是否配对,如果Mycat重启不成功,请使用ps -ef | grep mycat查看,然后使用kill强制杀死,再然后重新启动,如果不知道什么问题,请使用前台运行,它会显示出错误信息,然后再解决

    image-20200825235336310

    创建用户表,然后插入数据,直接拷贝以下SQL代码到窗口,然后运行,必须一步一步来并且不要进行窗口操作对连接Mycat的那个连接,因为会报错

    #第一步:删表
    USE `STOREDB`;
    DROP TABLE IF EXISTS `TB_USERS`;
    
    #第二步:建表
    CREATE TABLE `TB_USERS` (
      `id` bigint(20) NOT NULL,
      `username` varchar(20) NOT NULL,
      `password` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    #第三步:插入数据
    insert  into `TB_USERS`(`id`,`username`,`password`) values (1,'zhangsan1','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (2,'zhangsan2','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (3,'zhangsan3','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (5000001,'lisi1','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (5000002,'lisi2','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (5000003,'lisi3','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (10000001,'wangwu1','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (10000002,'wangwu2','123456');
    insert  into `TB_USERS`(`id`,`username`,`password`) values (10000003,'wangwu3','123456');
    

    创建订单表,然后插入数据,直接拷贝以下SQL代码到窗口,然后运行,必须一步一步来并且不要进行窗口操作对连接Mycat的那个连接,因为会报错

    #第一步:删表
    USE `STOREDB`;
    DROP TABLE IF EXISTS `TB_ORDER`;
    
    #第二步:建表
    CREATE TABLE `TB_ORDER` (
      `order_id` varchar(30) NOT NULL,
      `create_time` datetime NOT NULL,
      `pay_time` datetime NOT NULL,
      `total_fee` bigint(20) NOT NULL,
      `user_id` varchar(50) NOT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    #第三步:插入数据
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261157265358046058','2017-08-26 11:57:26','2017-08-26 12:48:22',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261221397410698125','2017-08-26 12:21:39','2017-08-26 12:48:22',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261234474784646997','2017-08-26 12:34:47','2017-08-26 12:48:22',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('201708261247443132289031','2017-08-26 12:47:44','2017-08-26 12:48:22',3,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918773291320152064534354','2017-10-13 17:39:57','2017-08-26 12:48:22',5,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918780410236788736453543','2017-10-13 18:08:14','2017-10-13 18:09:36',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918806410983137280453453','2017-10-13 19:51:33','2017-10-13 19:51:47',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918833486129815552274522','2017-10-13 21:39:08','2017-08-26 12:48:22',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('918835712999055360745325','2017-10-13 21:47:59','2017-10-13 21:49:28',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('919055625042825216545334','2017-10-14 12:21:50','2017-08-26 12:48:22',1,'lijialong');
    insert  into `TB_ORDER`(`order_id`,`create_time`,`pay_time`,`total_fee`,`user_id`) values ('919059761058607104453433','2017-10-14 12:38:16','2017-10-14 12:38:24',2,'lijialong');
    

    13.10、Mycat的效果

    重开一个窗口,连接主节点,依次查看,db1、db2、db3上的TB_USERS、TB_ORDER,你会发现数据是均匀分布的,这就实现了我们最终的分片集群了,具体效果图如下:

    db1>TB_USERS

    image-20200826020118355

    db2>TB_USERS

    image-20200826020137393

    db3>TB_USERS

    image-20200826020201015

    db1>TB_ORDER

    image-20200826020226630

    db2>TB_ORDER

    image-20200826020245571

    db3>TB_ORDER

    image-20200826020257914

    13.11、Mycat的踩坑

    1. Mycat只是一个数据库中间件,它并不是一个真正的数据库,所以用窗口不能直接操作,需要使用SQL语句
    2. Mycat依赖于JDK1.7+,我建议使用JDK1.8
    3. Mycat只是帮你做了数据分片,真正的主从复制还是靠你自己实现
    4. 当你创建表的时候,Mycat默认会把表名转换为大写,所以你创建的时候就直接把表名大写和数据库名大写,否则可能会找不到表,因为大小写不对应
    5. 当你的主键字段不是id的时候,直接在ruls.xml中拷贝一份相对应类型的tableRule,然后改个规则名称和字段名称就行了,然后再引用自定义的那个
    展开全文
  • php+mysql成绩查询系统 信息查询系统

    热门讨论 2013-01-09 14:05:58
    php+mysql成绩查询系统,简单的系统,在用户验证方面还重装很多不足,仅供初学者学习交流之用,下载好请先导入数据库文件
  • mysql学习经验&学习资料下载

    千次阅读 2018-09-12 01:49:48
    先给出我的核心观点:学习,无论你是学什么,也无论你有没有基础。思考永远是第一位的,有些知识你没接触过不要紧,用不着害怕,也没必要害怕。重要的是一秒钟也不要停止思考,问题要想透彻,正所谓磨刀不误砍柴工。...
  • jsp+mysql实现的博客系统

    热门讨论 2014-02-25 10:07:21
    采用java技术和mysql数据库技术开发的比克系统,是比较完善的博客系统开发案例,包含博客的发布,博客管理,用户评论,账号管理等功能,是学习的非常好的案例。
  • MySQL数据库学习

    万次阅读 多人点赞 2018-08-13 20:41:03
    虽然笔者从事的是Android客户端的开发,平时和数据库打的交道并不多,但是我们对于数据库这一块的学习还是很重要的,今天笔者想总结下MySQL关系型数据库的一些常用知识点 数据库概述 一、常见的概念 数据库...
  • 库存管理系统JAVA+MYSQL

    2014-05-13 20:40:42
    JAVA库存管理系统,一个很好的学习例子。也可用作简单的库存管理。
  • 数据库DB 与 数据库管理系统DBMS DBMS 流行度(popularity)排名 关系型数据库 RDBMS 非关系型数据库 NRDBMS SQL 文件系统与数据库系统 共享文件系统 与 C/S系统 我们该做什么
  • 如何学习mysql

    千次阅读 2019-03-26 17:30:21
    所以今天踏上了mysql学习之路,和你们谈谈我是怎么学习它的。 以下内容是我个人对于mysql学习路线和笔记 如有错误之处, 还请大神及时纠正。 要想了解任何一个东西 首先我们要对它有个基本认识 1.它是干嘛的 ...
  • 基于PHP MYSQL的学生作业管理系统

    热门讨论 2011-11-21 14:46:57
    基于PHP,MYSQL的学生作业管理系统,毕业设计文档,源代码,截图,文献,都已经完成
  • MySql学习系列(一)

    千次阅读 2019-05-12 17:24:02
    1.1MySQL 软件安装及数据库基础 1.软件安装及服务器设置。 教程 http://www.runoob.com/mysql/mysql-install.html 登录 MySQL: 当 MySQL 服务已经运行时, 我们可以通过 MySQL 自带的客户端工具登录到 MySQL ...
  • "基于JSP的网上订餐系统-java网上订餐系统源代码系统演示 1.包含源程序,数据库脚本。 2.课题设计仅供参考学习使用,可以在此基础上进行扩展完善。 代码已经上传github,下载地址...
  • MySQL语句学习

    千次阅读 2020-04-21 00:30:28
    学习数据库命令之前首先了解一下MySQL,首先MySQL是DBMS(数据库管理系统),DBMS是位于用户与操作系统之间的一层数据管理软件。DB(数据库)是指:长期储存在计算机内的、有组织的、可共享的大量数据集合。DBS...
  • 尚硅谷MySQL高级学习笔记

    万次阅读 多人点赞 2019-08-18 11:25:03
    数据库MySQL学习笔记高级篇 文章目录数据库MySQL学习笔记高级篇写在前面1. mysql的架构介绍mysql简介概述高级MysqlmysqlLinux版的安装mysql配置文件mysql逻辑架构介绍mysql存储引擎2. 索引优化分析性能下降SQL慢常见...
  • 手把手实现Java图书管理系统(附源码)

    万次阅读 多人点赞 2020-02-03 11:43:43
    基于SSM的图书管理系统的设计与实现 本课程演示的是一套基于Java的SSM框架实现的图书管理系统,主要针对计算机相关专业的正在做毕设的学生与需要项目实战练习的java人群。 详细介绍了图书管理系统的实现,包括: 1....
  •  MSI格式的可以直接点击安装,按照它给出的安装提示进行安装,Windows操作系统下一般MySQL将会安 装在C:\Program Files\MySQL该目录中。  ZIP格式是自己解压,解压缩之后其实MySQL就可以使用了,但是要进行配置。...
  • 企业进销存管理系统源码,技术涉及JAVA+MySQL。 功能包括 进货管理、销售管理、库存管理、客户资料管理、商品资料管理、供应商资料管理等。 适用于企业的进销存管理和广大编程爱好者学习
  • MySQL数据库从入门到精通实战教程

    千人学习 2020-02-03 14:19:52
    MySQL数据库从入门到精通实战教程,本课程使用豆瓣电视剧频道作为教学案例,可以让零基础学员快速的掌握MySQL数据库,顺利进入Java学习通道。同时本课程也是Java初级工程师必备科目。
  • Java+MySQL + jdbc +学生信息管理系统源码,适合初学者下载使用,欢迎大家下载,交流!
  • MySQL基础学习(一)

    万次阅读 2018-05-25 13:46:03
    MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等...
  • 课程二:《高性能MySQL实战课》主要从高可用篇、MySQL8.0新特性篇,性能优化篇,面试篇四个角度展开讲解,帮助大家发挥MySQL的最佳性能的优化方法,掌握如何处理海量业务数据和高并发请求 【你能收获到什么?...
  • Python开发企业级标准环境搭建

    千人学习 2019-12-29 10:08:08
    都说工欲善其事必先利其器,本课程讲帮助小白,和刚转行python程序员,从无到有,搭建python相关生产环境,课程设计两套系统,linux + windows环境下的软件安装。软件安装范围有pycharm , anaconda , sublime ,jupyter ...
  • MySQL数据库系统学习(一)

    万次阅读 多人点赞 2017-11-11 13:55:18
    克服mysql难关
  • MySQL作为一个数据存储系统,核心功能为存储数据和读取数据。在数据存储方面,MySQL是基于文件系统或者说是磁盘来进行数据保存的,即数据都是保存为磁盘上的一个个文件;在数据读取方面,MySQL作为一个关系型数据库...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 264,511
精华内容 105,804
关键字:

mysql系统学习

mysql 订阅