云计算MySQL

MySQL

mysql数据库的基础

数据库新系统

数据与信息
    联系
        数据和信息之间是相互联系的。数据是反映客观事物属性的记录，是信息的具体表现形式。数据经过加工处理之后，就成为信息；而信息需要经过数字化转变成数据才能存储和传输。


数据库存储单元--服务器
数据库系统的构成
    存储单元
        数据库中的数据按一定的数学模型组织，描述和存储，具有极小的冗余，较高的数据独立性和易扩展性，并可为各种用户共享

    硬件
        数据库系统的硬件组成包括构成计算机系统的各种物理设备，想CPU，内存和磁盘这些必要的硬件设备，当然，空调，应急电源，传感器等包括系统之内，硬件的配置应满足整个数据库系统的需求

    软件
        数据库系统的软件操作系统，数据库管理系统及应用程序。数据库管理系统是数据库系统的核心软件，在操作系统的支持工作，科学地组织和存储数据，高效获取和维护数据

    人员
        数据库系统的正常运行离不开人员的维护和优化，数据库系统只有在专业人员的手中才能发挥其出色的性能。


数据库系统特点
    实现数据库共享
        数据共享是指所用户可以同时存储和读取数据库中的数据，也是只用户可以使用各种方式通过接口调用数据库

    减少系统和数据的冗余
        和文件需要相比，数据库系统实现数据共享，避免了有用户自建立应用文件造成的大量数据重复和系统冗余，保持了数据的一致性，性能更加稳定

    数据实现集中控制
        相对于文件系统中数据处于分散状态，同一用户或不同用户或不同用户在处理数据过程中，文件之间无关系的管理方式，数据库系统可对数据进行集中控制和管理，并通过数据模型来表示各种数据和组织之间的联系

    保持数据的安全，完整合并并发
        数据库系统可以实现安全性控制（防止数据丢失，错误更新和越权使用），完整性控制（保证数据的正确性）并发控制（既能在同一数据周期内允许对数据实现）

    故障恢复

数据库类型
数据模型
层次模型
数据库层次模型就想一棵树，主干上有着不同的分支，描述一对多的层次关系非常自然直观，容易理解。
优点：层次分明，表与信息描述的很清晰
缺点：查询需要一层一层查询，不夸表查询。对于非层次关系，如多对多关系，一个节点具有多个父节点等，使用层次模型表示容易出现关系混乱。

    网状模型
        优点：数据连接直接
        缺点：表与表关系描述关系复杂
        允许一个以上的节点父节点
        一个节点可以有多余一个的父节点
        定义：网状模型绝缘油层次模型更复杂的网状结构


关系型数据库
    关系型数据库产品
        Oracle 数据库
        SQL Server 数据库
        Access 数据库

    能够处理以上的表，并且能够进行多表联查，也是使用最多的
    虽然网状和层次模型已经可以很好地解决数据的集中和共享问题，但他们在数据独立和抽象级别上仍有很大欠缺。
    特点
        存储结构
            sql：增删改查（insert，delete，update，select）
            关系型数据库按照结构化的方法存储数据，每个数据表的行列都必须对应的字段（也就是先定义号表的结构），在根据表的结果存入数据。

        扩展方式
            关系型数据库将数据存储在数据表中，因此数据出自的瓶颈就出现多张数据表的操作中，而且数据表越多问题就越严重。

        查询方式
        事务性
            ACID规则：
                原子性（atomicity）：要么成功要么失败
                一致性（consistency）：不管如何操作，总体不变
                隔离性（lsolation）：所有用户操互不影响
                持久性（durability）：所有操作都会保存到相关日志文件
                    它可以满足对事务性要求较高或者需要进行复杂数据查询的任务需求，而且可以充分满足数据库操作的高性能和操作稳定性的要求。 关系型数据库强调数据的强一致性，可以控制事务原子性细粒度，并且一旦操作有误或者有需要，可以回滚事务



        读写性能
            关系型数据库十分强调数据的一致性，并为此降低读写性能，付出了巨大的代价。虽然关系型数据库存储数据和处理数据的可靠性很不错，但一旦面对海量数据的处理，效率就会变得很低，特别是遇到高并发读写的时候，性能会下降得非常厉害。



面向对象模型
    描述信息，特性

非关系型数据库
    非关系型数据库也被称为 NOSQL（ Not Only SQL）数据库。它的产生并不是要彻底否定关系型数据库，而是作为传统数据库的一个有效补充。非关系型数据库的数据存储不需要固定的表结构，一般情况下也不存在对数据的连续操作。相对于关系型数据库，非关系型数据库在大数据存取上具备无法比拟的性能优势。
    常见的非关系型数据库产品有以下 3 种。
        Memcached
        Redis
        mongoDB


为什么使用MySQL
    MySQL 属于中小型关系型数据库管理系统，目前被广泛地应用到互联网上的大、中、小型网站中，相比于其他数据库产品，其特点如下：
        （1）与 Oracle 相比，MySQL由于体积小、安装成本低、易于维护、速度快、总体拥有成本低、开放源码且无版权制约，成为商业数据库的首选。
        （2）可以使用SQL访问与管理 MySQL 数据库。
        （3） MySQL历史悠久，社区及用户非常活跃，遇到问题可以寻求帮助。
        （4）品牌口碑效应使得企业无须考虑就直接使用LAMP、 LNMP流行架构。
        （5） MySQL同时提供多种应用程序编程接口（Application Programming Interface，API），支持多种操作系统，并支持多种开发语言，特别对流行的 PHP 语言有很好的支持。

mysql数据库操作

SQL语句（结构化查询语言）

SQL语句分为4个类别
    DDL语句（数据定义语言）
        用在定义或改变表的结构数据类型。表之间的链接和约束工作上。常用的语句关键字包括create（创建），drop（删除数据库），alter（修改）---对象是数据库的操作

    DML语句（数据操纵语言）
        用于添加，删除，更新和查询数据库记录，常用的语句关键字有insert（插入），update（修改），delete，select（查询）分别代表表插入，更新与删除，是开发以数据中心的应用程序必会使用到的指令---对象是表

    DCL语句（数据控制语言）
        应用对数据访问权限进行控制。定义数据库，表，字段，用户的访问权限和安全级别。主要关键字包括，grant（授权）。revoke（取消授权）---对象是数据库用户

    DQL语句（数据程序语言）
        主要用于对数据的查询，主要关键字包括select（查询），from。where跟上条件查询

数据类型（MySQL支持所有标准的SQL数据类型，包括严格数据类型）
整数：int ，tinyint
字符串：char，varchar
日期：year，date，
数值类型
整数类型，浮点类型，定点数类型，位类型
char和varchar的不同之处在于MySQL处理指示器的方式。char把指示器大小视为值的大小，在长度不足的情况下用空格。
在text和blob的存储剧哦检索过程中怒存在大小写转换，当未运行在严格模式下时
不同：text是大小写不敏感的，而blob是大小写敏感的

日期和时间类型
    需要表示年月日，使用date
    需要表示年月日时分秒，使用datetime
    需要插入或者更新日期为当前系统时间，使用timestamp
    MySQL  -u用户名  -p密码
    show    database  ---查看所有数据库
    create   database---数据库，

存储引擎
定义：存储引擎是数据库底层的组件，是数据库的核心。 使用存储引擎可以创建、查询、更新、删除数据库。
如何选择
因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。
存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

    存储空间
        MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
        InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

库操作
创建库
数据库名称是唯一的，且区分大小写
可以有字母，数字，下划线，@，#，$组成
不能使用关键字
不能单独使用数字

创建数据库
    指定默认的字符编码为UTF-8

查看库
    show   databases；
    select   databases；查询当前使用的数据库
    create  database   数据库名；创建数据库
    use  数据库名；  使用数据库
    select  database （）；查询当前使用的是一个数据库
    drop    database  数据库名称；代表删除数据库

创建表
    create  table     表名字（字段1   字段类型，字段2   字段类型）；
    create  table     表名字（字段1   字段类型   not   null，字段2   字段类型）；
    create  table     表名字（字段1   字段类型    not   null   auto_increment   primary   key，字段2   字段类型）；
    show  create table   表名字。  查看表属性
    desc    表名   查询表结构
    drop  table  表名；    代表删除整张表，命令一旦执行这个表就不存在了
    select * from  表名；查询表的所有内容
    delete  删除表的结构
    not     null 表示不允许为空，也就是这个字段必须有值
    auto_increment    表示自动增长，也就是这个值随着操作依次递增
    primary  key     主键
    insert into   插入

表的高级操作
数据的插入
添加一条数据insert into 表名（字段1，字段2，子字段3） values（字段1的值，字段2的值，字段3的值）；
添加多条数据insert into 表名（字段1，字段2，子字段3） values（字段1的值，字段2的值，字段3的值），（字段1的值，字段2的值，字段3的值），（字段1的值，字段2的值，字段3的值）；

删除表内容
    delete  from  表名  where  条件；（只能删除表数据，不能删除表）
    所有数据：delete   from   表名；如果某一列设置为自动增长，就是数据全部删除，那么数据也不会重新开始，而是继续往后加
    truncate  表名；删除表内容 （如果某一列设置为自动增长，全部数据都被删除，那么数据会重新开始）
    delete  from  表名   where   条件；

修改数据表
    alter  table  原表名  rename  新表名；代表修改表名
    alter   针对的是表结构
    alter  table   表名  change   原来字段   新的字段   新字段的数据类型；代表修改表字段
    alter  table  modify  字段   新的数据类型 ；  代表修改字段类型
    alter  table  modify  字段1  数据类型  first  字段2；代表把表中字段1放在字段2前面；
    alter  table  modify  字段1  数据类型  first  字段2；代表把表中字段1放在字段2前面；
    添加：alter table 表格名称 add 字段名称 数据类型 约束条件
    删除：alter table 表格名称 drop 字段名称

更新数据
    update 是针对表数据
    update  表名 set  字段=新的数据  where 条件；代表修改表数据
    update  表名  set  字段=新数据；代表修改所有的字段数据

mysql数据库多表查询

简介

前面已经介绍了单表的查询操作，在数据库中有时为了提高数据的检索和存储效率，会将一些内容量比较大的表拆分成多个子表，并通过一些特殊的属性将这些子表关联起来。
一对一关系
    主表和从表，主表的一条数据对应从表的一条数据，两个表一一对应
    数据表的一对一关系主要应用于以下几个方面。
        （1）分割列数较多的表。
        （2）为了加强数据安全性而隐藏数据表中的一部分内容。
        （3）保存临时数据，当不需要时可以直接删除从表，减少了操作步骤


一对多关系
    定义
        在一对多关系中，主键数据表中只允许有一条记录与其关系表中的一条或者多条记录相关联（也可以没有记录与之相关联）。另外，关系表中的一条记录只能对应主键数据表中的一条记录。
        一对多关系可以简单地理解为一张表中的一条记录可以对应另外一张表中的一条或者多条记录，也可以没有记录与之关联。但是反过来，另外一张表的一条记录只能对应第一张表的一条记录。这与现实生活中父子的关系很像，主键所在的表可以称为父表，与其对应的关系表称为子表。为了使读者可以深入理解，接下来通过具体案例演示数据表的一对多关系
        主表和从表，珠宝的一条数据可以第一页从表的一条或者多条数据，反着看表从表的一条或者多条数据只能对应主表的一条数据，也可以没有对应的值


多对一
    其实就是一对多反着来看

多对多
    主表和从表，主表的一条数据可以和从表的多条数据关联，同样从表的一条数据也可以和主表的多条数据关系，主表和从表需要有个‘中间人’，来连接查询，中间人的主表也可以和主表关联也可以和从表关联

合并结果集
仅仅适用在两张表结构一直的表进行查询，如果合并查询两张表结构不一致，那么以左边这种为准
union
selectfrom test1 union selectfrom test2；查询的内容如果有相同的那么只显示一次，不同的都显示出来
union all 合并查询，把两张表的数据合并在一起，不管是否有相同的字段都会显示出来，不会去重
selectfrom test1 union all selectfrom test2；
笛卡儿积
就是把两张表的数据的所有组成形式排列处理
select 查询字段 from 表名1 cross join 表2；
select emp.empno，emp.ename,dept.deptno,dept.dname from emp cross join dept;(emp.empno代表emp这张表的empno字段)
select emp.empno，emp.ename,d.deptno,d.dname from emp e cross join dept d;

外连接
外连接查询与内连接查询不同的是，内连接查询的返回结果只包含符合查询条件和连接条件的数据，而外连接查询可以返回没有关联的数据，返回结果不仅包含符合条件的数据，而且包含左表或右表或两个表中的所有数据

内连接
inner join
select 字段 from 表1 inner join 表2 on 条件规则；
select e.empno, emp.empno，emp.ename,d.deptno,d.dname from emp e join dept d on e.deptno=d.deptno where empno=1;
MySQL的INNER JOIN子句可以将一个表中的行与其他表中的行进行匹配，并允许从两个表查询包含列的行记录，效果与CROSS JOIN语句类似，
需要注意以下问题
（1）需要在FROM子句中指定主表。
（2）理论上讲，INNER JOIN子句可以连接多个其他表，但是，为了获得更好的性能，建议连接表的数量不要超过3个。
（3）连接条件需要出现在INNER JOIN子句的ON关键字之后，连接条件是将主表中的行与其他表中的行进行匹配的规则。

外连接
外连接查询与内连接查询不同的是，内连接查询的返回结果只包含符合查询条件和连接条件的数据，而外连接查询可以返回没有关联的数据，返回结果不仅包含符合条件的数据，而且包含左表或右表或两个表中的所有数据。

左外连接
select*from 表1 left join 表2 on 条件规则
左外连接以左表为基准，查询结果中不仅显示左表满足条件的数据，还显示不满足条件的数据（左表的数据全部显示），而右表只保留满足条件的数据，不满足条件的显示为空。

右外连接
right join right join 表2 on 条件规则；

自然连接 natural join（自动匹配字段相同的，或者数据类型相同的）
select 字段 from 表1 natural 【left/right】 join 表2；

嵌套查询
嵌套查询又被称为子查询，其语句特点是在SELECT语句中包含SELECT语句。子查询可以在WHERE关键字后作为查询条件或在FROM关键字后作为表来使用，接下来详细讲解子查询的相关内容。

子查询作为查询条件
首先执行子查询语句，把查询后端结果作为匹配的条件
select 字段 from 表名 where 字段 >/</=（子查询语句select 字段 from 表名 where 条件）
子查询作为表使用，在from语句后面添加，也是先执行查询语句
在复杂查询中，子查询往往作为查询条件来使用，它可以嵌套在一个SELECT语句中，SELECT语句放在WHERE关键字后。执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的过滤条件

子查询作为表
前面讲解了子查询作为查询条件来使用，子查询还可以作为表来使用。SELECT子句放在FROM关键字后，执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的数据源使用。

mysql数据库单表查询

数据库单表查询

基础查询
单独查询某个字段：select 字段 from 表名；（查询的字段有多个，字段与字段之间用逗号隔开 ）
image
条件查询
selectfrom 表名 where 条件1 and 条件2；满足两个条件
selectfrom 表名 where 条件1 or 条件2；结果是两个条件满足其中一个即可
高级查询
image
排序查询
数据查询完成后，查询结果会按默认的顺序进行排序。用户如果想自定义输出结果的顺序，就需要在语句中加入ORDER BY关键字，其具体的语法格式如下所示。
select 字段名1,字段名2,… from 表名 order by 字段名1 [ASC｜DESC], 字段名2 [ASC｜DESC]…
语法格式中，“字段名1,字段名2,…”是要查询的字段，ORDER BY关键字后的字段名是指定排序的字段。ASC和DESC是可选参数，其中ASC表示按升序排序，DESC表示按降序排序，如果不写该参数，则默认按升序排序。
order by 字段
select * from 表名 order by score 字段;升序
select * from score order by score desc;降序
聚合函数
1.COUNT()函数
COUNT()函数中有3个可选参数，其中“”是返回行数，包含NULL；“列名”指定特定的列，不包含 NULL；COUNT(1)与COUNT()返回的结果是一样的，如果数据表没有主键，则COUNT(1)的执行效率会高一些。
select count（）from score;列出表的总行数，或者1表的意思意识都是列出总行数
列出表某一列的总行数：select count(字段) from score;查看一列的总值就写哪字段
求和：select sum(字段) from 表名;
求平均值：
求某一列平均值：select avg(字段) from score；
求最大值：select max(score) from score ；
最小值：select min(score) from score ；
分组查询 group by
select 字段名1,字段名2,… from 表名 group by 字段名1,字段名2,…;（select gender,count() from student group by gender;）统计性别字段总行数，并且按照性别进行分组
having子句
select 字段名1，字段名2，… from 表名 group by 字段名1，字段名2，… [having 条件表达式];
having 字段 数据过滤（select sid,count(score) from score group by sid having count(score) >2;）查询sorce表sid字段，并且统计成绩的总行数，按照sid进行分组，并且果粒橙到成绩总行数大于2的
.LIMIT分页
select 字段名1,字段名2,… from 表名 limit [m,]n;
select from 表名 order by 字段 limit 值1，值2;（值1表示跳过多少行，值2表示取后面多少行）
selectfrom sorce order by sorce limit 1,3；查询sorce表，成绩按照从小到大排序，并且跳过前一个值取后面三个值
LIMIT关键字后的第一个参数m为可选项，代表起始索引，若不指定该值，则使用默认值0，代表第一条记录；第二个参数n为必选项，代表从第m+1条记录开始取n条记录。
多表查询
当查询的内容在某一个范围里面：selectfrom 表名 where 字段 in（值1，值2，值3，）；（值自己指定）
当查询的内容不在某一个范围里面：selectfrom 表名 where 字段 not in（值1，值2，值3，）；（值自己指定）
当查询的子段为空：selectfrom 表名 where 子段 is null ；
当查询的子段不为空：selectfrom 表名 where 子段 is not null ；
当值在 … 范围里面：selectfrom 表名 where 字段 between 值1 and 值2；（在。。。。包含了值1和值2 ）
当值不在 … 范围里面：select*from 表名 where 字段 not between 值1 and 值2；
模糊查找：
select * from student where sname like ‘_____’;查询student表中的名字有五个字符，（一个_表示一个字符）
select * from student where sname like ‘%w%’;查询student表中名字有w的字符
select * from student where sname like ‘w’;查询student表中字符有五位并且第三位是w的值
distinct sid from student ；查询student表中sid字段，并且去掉重复
当前文档47条主题 共1018字

mysql数据库的恢复与备份

备份原则

备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。衡量备份恢复有两个重要的指标：恢复点目标(RPO)和恢复时间目标(RTO)，前者重点关注能恢复到什么程度，而后者则重点关注恢复需要多长时间。这篇文章主要讨论MySQL的备份方案，重点介绍几种备份方式的原理，包括文件系统快照(LVM)，逻辑备份工具Mysqldump，Mydumper，以及物理备份工具Xtrabackup，同时会详细讲解几种方案的优缺点，以及可能遇到的问题。

数据库备份
根据服务是否在线
热备份
温备份
冷备份

根据备份的方式
    物理备份
    逻辑备份

根据备份的数据量
    完全备份（全量备份）
        对所有的数据进行备份
        优点：可以实现全部数据的恢复，数据备份的跟全面
        缺点：本地速度慢，成本高

    增量备份
        对第一次进行全备，后面数据值备份每天新加的内容
        优点：备份速度快
        缺点：数据一旦丢失无法挽回

    差异备份
        备份的是第一次数据已经最后一次数据的差异部分，备份速度介于全量和增量之间

备份数据
1.禁止非innodb表更新
2.禁止所有表的ddl
优化点：
1.不会被大查询堵塞(关闭表)；
2.不会堵塞innodb表的读取和更新，这点非常重要，对于业务表全部是innodb的情况，则备份过程中DML完全不受损。
LOCK BINLOG FOR BACKUP作用：
获取一致性位点
1.禁止对位点更新的操作优
化点：1.允许DDl和更新，直到写binlog为止。
备份很简单直接tar一个命令就OK了，然后就开始还原（只还原到备份那一刻）：
mkdir /backup
cd /var/lib/mysql
tar -cf /backup/date +%F.gz *
#开始还原
systemctl stop mysqld
rm -rf /var/lib/mysql/*
tar -xf /backup/2020-04-14.gz -C /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
#登录测试数据库是否还在
mysql -p’Root@321’
show databases;

tar备份
创建备份目录
进行tar备份，压缩的格式之间定，压缩的目录是数据存放位置的目录（yum安装的数据库，数据存放位置/var/lib/mysql,如果是源码安装那就是你自己定的）如忘记数据存放位置可以使用find查找，课程数据库名也可以找到
模拟数据丢失，关闭服务，删除数据存放位置的所有内容
恢复数据，tar xf 压缩包 -C /
开启服务验证

mysqldump进行备份
单个数据库备份：mysqldump -u用户名 -p密码 数据库名字 >文件名
全部数据备份：mysqldump -u用户名 -p密码 --all -databases >文件名

数据全量备份，mariadb-backup
备份数据库：mariabackup --backup --target-dir=/root/fullbackup --user=root --password=123456
备份数据：mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=123456
模拟数据丢失，机内数据存放位置，/var/lib/mysql 删除使用内容
恢复数据：mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=123456（–copy-back代表恢复数据，–target-dair指定全量备份数据存放的位置）
修改数据存放位置的所有者和所属组，chown -R mysql。mysql /var/lib/mysql
重启服务生效：systemctl restart mariadb
登录数据库验证

增量备份
全量备份数据库：mariabackup --backup --target-dir=/root/fullbackup --user=root --password=123456
增量备份数据：mariabackup --backup --target-dir=/root/inc1 --incremental-basedir=/root/fullbackup/ --user=root --password=123456（–target-dir指定增量备份目录位置）
全量备份数据：mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=123456
合并全量备份数据和增量备份数据：mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=123456 --incremental-dir=/root/inc1/ --apply-log-only
模拟数据丢失：，删除数据存放位置的内容
恢复数据：mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=123456
修改数据存放位置的所有和所属组：chown -R mysql.mysql /var/lib/mysql
重启服务生效：systemctl restart mariadb
登录数据库验证

mysql数据库的数据的完整性

简介

数据表是数据存储的基础单元，在从外界向数据表输入数据时可能会由各种原因造成输入数据的无效或者错误。为了防止数据库中存在不符合语义规定的数据或者由错误信息的输入/输出造成无效操作， MySQL 提供多种方法来保证数据的完整性。数据的完整性主要分为实体完整性、域完整性和引用完整性。

主键与主键约束
主键规则：唯一性（整个表只能有一个）
非空性（主键的字段不能为空）
主键（又称为主关键字）是用于唯一确定表中每一行数据的标识符，是表中某一列或者多列的组合，多个列组成的主键称为复合主键。
（1）唯一性：每个表中只能存在一个主键，且主键的值能唯一标识表中的每一行，就像每个人的身份证号码是不同的，能唯一标识每一个人。
（2）非空性：主键可以由多个字段组成，且不受数据类型的限制。另外，字段所在的列中不能存在空值（NULL）。
需要注意的是，主键表示的是一个实体，而主键约束是针对这个实体所设定的规则或属性。
创建表添加主键：create table 表名（字段1，数据类型 primary key，字段2 数据类型）；
表已经存在添加主键：alter table 表名 add primary key （列名）；
删除表已经存在:alter table 表名 drop primary key；

唯一约束
可以为空
整个表可以有唯一约束
唯一约束用于限制不受主键约束的列上数据的唯一性，与主键约束不同的是，唯一约束可以为空值且一个表中可以放置多个唯一约束。MySQL中可以使用UNIQUE关键字添加唯一约束。
create table 表名（字段名 数据库类型 unique …）;
修改：alter table 表名 add unique（列名）；
删除：drop index（需要删除唯一索性的字段 index_name ）on（表名 tbl_name）;或alter table （tbl_name表名） drop index （index_name需要删除唯一索引的字段）;

自动增长
在创建表时，表中的id字段的值一般从1开始，当需要插入大量的数据时，这种做法不仅比较烦琐，而且还容易出错。为此，可以将id字段的值设置为自动增长。
create table 表名（字段名 数据类型 primary key AUTO_INCREMENT,字段2 数据类型）；
auto_increment（一旦添加自动增长列，这个字段就不需要添加值）
表已经存在添加自动增长：alter table 表名 modify 字段名 数据类型 AUTO_INCREMENT；

.域完整性
非空约束
create table 表名（字段名 数据类型 null,。。。。）；
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
非空性 not null 一旦把某个字段添加了not null，那么这个字段必须要有内容不能为空值

.默认值约束
    default   默认值约束用于为数据表中的某个字段设置默认值，例如，订单的创建时间如果不进行手动填写，则默认为当前时间。在MySQL中使用DEFAULT关键字设置默认值约束
    create table 表名（字段名 数据类型 default 默认值,。。。。）；
    ALTER TABLE 表名 MODIFY 字段名 数据类型 default 默认值;

引用完整性
表已经创建进行修改：alter table 表名 add foreign key（外键字段名）references 主表表名（主键字段名）；
外键：一旦创建，那么删除主表的时候必须删除外键所在的表再去删除主表
创建表的时候 添加外键：create table 表名（字段1 数据类型，字段2 数据类型，foreign key（在表中的字段中选择）references 主表名（主表主键字段））；
删除外键：alter table 表名 drop foreige key 外键名；（show create table 表名； 查看外键名字）

索引
定义：
如果把数据表比喻成一本书，那么索引就是这本书的目录，它可以帮助用户实现根据目录中的页码快速找到对应的内容。如果不添加索引，数据库每次执行查询操作时，都会从表的头部开始遍历数据，这无疑降低了数据库的运行效率。MySQL 中的索引分为普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引、组合索引等。接下来将介绍生产环境中常用的普通索引和唯一索引。

普通索引
    普通索引是最基本的索引类型，它主要的作用是加快对数据的访问速度。
    create index 索引名 on 表名 （字段名[（长度）]）；
    create  table  表名（字段1 数据类型 ，字段2   数据类型，index  [索引名字]（需要设置为索引的字段））；
    查看索引是否创建成功：show  index  from  表名\G
    表已经存在创建索引：create  index   索引  on   表名  字段；
    删除索引：drop   index  索引名字  on  表名；

唯一索引
    MySQL中使用UNIQUE关键字为某列定义唯一索引，被定义唯一索引的列不允许存在重复的值。当有新的数据插入时，MySQL会自动检查新记录中的字段值是否已经存在于数据表中，如果已经存在，系统将拒绝这条数据的插入。唯一索引不仅可以保证数据的唯一性，而且可以简化MySQL对索引的管理工作，使索引变得简单高效。
    创建表的时候添加：create table 表名（字段名 数据类型，。。。。unique index [索引名]（字段名[（长度）]））；
    表已经存在添加唯一索引：create unique index 索引名 on 表名 （字段名[（长度）]）；
    删除唯一索引：alter table 表名 drop index 索引名；

索引过多引发的问题
    在MySQL中合理地创建索引不仅可以极大地提高在数据库中获取所需信息的速度，而且能提高服务器处理相关搜索请求的效率。建立索引的优点如下。
        （1）可以加快数据的检索速度。
        （2）可以保证数据库表中每一行数据的唯一性。
        （3）加速表和表之间的连接。
        （4）在使用分组或者排序子句时，可以减少查询中分组和排序的时间。
        虽然索引在检索效率上具有诸多的积极作用，但过多的索引也会引起一些不必要的问题。
        （1）在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
        （2）过多的索引需要占大量的物理空间。
        （3）当对数据进行增、删、改操作时，用户也需要对索引进行相应的维护，降低了数据的维护速度。小红

mysql数据库的权限与账户管理

权限表

MySQL服务器将用户的登录数据以权限表的形式存储到系统默认的数据库中，当用户访问数据库时，系统会将登录用户的数据与存储在数据库中的相关数据进行信息比对，信息一致则登录成功，否则登录失败。权限表由mysql_install_db脚本初始化，其中存储用户权限的信息表主要有 user、db、host、tables_priv、columns_priv和procs_priv。
 select version();
MySQL 5.7默认的数据库包括information_schema、mysql、performance_schema和sys（MySQL5.6 默认的数据库为information_schema、mysql、performance_schema、test）
mysql数据库中关于系统访问权限和授权信息的表如下。
    （1）user：包含用户账户和全局权限以及其他非权限列表（安全配置选项列和资源控制选项列）。
    （2）db：数据库级别的权限表。
    （3）tables_priv：表级别的权限表。
    （4）columns_priv：列级权限表。
    （5）procs_priv：存储过程和函数权限表。
    （6）proxies_priv：代理用户权限表。

performance_schema 数据库中的表可以分为以下几类。
    （1）设置表：主要用于配置和显示监视特征。
    （2）当前事件表：该表包含每个线程的最新事件，按层次不同，可分为阶段事件（events_stages_current）、语句事件（events_statements_current）和事务事件（events_transactions_current）。
    （3）历史记录表：与当前事件表具有相同的结构，但包含更多行。如果需要更改历史记录表的大小，可以在服务器启动时设置适当的系统变量。例如，如果需要设置等待事件历史记录表的大小，可以设置performance_schema_events_waits_history_size和performance_schema_events_waits_history_long_size。
    （4）汇总表：主要包含按事件组聚合的信息，包括已从历史记录表中丢弃的事件。
    （5）实例表：记录了要检测的对象类型，当服务器使用检测对象时，会产生一个事件。这类表提供了事件名称和说明性注释或状态信息。
    （6）杂项表：不属于任何其他表组。
    需要注意，performance_schema数据库主要收集的是系统性能的数据，而information_schema数据库主要存储的是系统方面的元数据，两者要加以区分。

sys
    sys数据库所有的数据来自performance_schema数据库，主要是为了将performance_schema数据库的复杂度降低，让数据库管理员（Database Administrator，DBA）能更好地阅读库中的内容并了解库的运行情况。

账户管理
1．登录数据库
用户可以通过使用SQL命令并在语句后面指定登录主机名以及用户名和密码来登录数据库，另外，使用mysql --help命令可以查看MySQL命令的帮助信息。MySQL登录命令中常用的参数如下。
（1）-h：使用该参数可以指定主机名（hostname）或IP地址，如果不指定，默认为localhost（本地主机）。
（2）-u：使用该参数可以指定登录的用户（user）。
（3）-p：使用该参数可以指定登录密码（password）。如果该参数后面有一字段，则该字符串将作为用户的密码直接登录；如果该参数后面没有内容，则会在登录的时候提示输入密码。需要注意，该参数后面的字符串和-p之间不能存在空格。
（4）-P：使用该参数可以指定登录MySQL的端口号（Port），如果不指定，默认为3306端口。一旦端口号发生修改必须使用 -P指定
（5）-e：使用该参数可以指定执行的SQL语句。需要注意，如果设置了该参数，登录后执行-e后面的命令（或SQL语句）后将自动退出。mysql -u用户 -p密码 -e‘需要执行的命令’
（6）数据库名：可以在命令的最后指定数据库名。

退出数据库
    使用exit命令退出
    使用quit命令退出
    使用\q 命令退出
    另外Ctrl+z（停止这个会话，把会话放在后台，可以通过jobs查看后台作业，通过执行fg把后台的会话调用到前台）或Ctrl+c（终止这个会话）

创建与删除用户
    create  user  ‘用户名’@‘主机名/IP地址’ [indentified   by ]
    语法：CREATE USER <用户名与登陆主机> identified by [password]
    在使用 CREATE USER 语句创建用户时应该注意以下几点。
        （1）如果使用CREATE USER语句时没有为用户指定登录口令（密码），那么MySQL允许该用户不使用口令登录系统。在实际的应用场景中，出于对数据安全的考虑，不建议采用这种做法。
        （2）用户使用CREATE USER语句时必须拥有MySQL服务器中MySQL数据库的INSERT权限或全局CREATE USER权限。
        （3）使用CREATE USER语句创建一个用户后，系统自身的MySQL数据库的 user 表中会添加一条新记录。如果创建的用户已经存在，则语句执行时会出现错误。
        （4）新创建的用户拥有的权限很少，只允许登录MySQL服务器或进行不需要权限的操作，如使用SHOW语句查询所有存储引擎和字符集的列表等。
        （5）如果两个用户具有相同的用户名，但是具有不同的主机名，MySQL会将他们视为不同的用户，并允许为这两个用户分配不同的权限集合。

     刷新权限flush privileges;
    删除：drop user '用户名'@'主机地址
        delete from mysql.user where host='主机地址' and user='用户名'

修改账户密码
    通过mysqladmin命令修改
        mysqladmin -u 用户名 -h localhost -p '当前密码' password '新密码'

    通过SET命令修改
        set password for ‘用户名’@‘IP地址’ =password（‘新密码’）;
        set password=password('新密码');（当前登录的用户是谁就代表修改的是谁的密码）

    使用 ALTER 语句修改密码
        alter user ‘用户名’@‘客户端来源IP地址’ identified by ‘新密码’;（需要进入数据库内执行）

    使用UPDATE命令修改密码
        update mysql.user set authentication_string=password('新密码') where user=‘用户名’ and host=‘主机地址’;


忘记密码
    首先停止数据库服务
    修改数据库主配置文件，在配置文件中添加skip-grant-tables=1
    重启数据库服务
    登录到数据库mysql，修改密码
    修改主配置文件把添加的内容删掉，重启服务生效
    以新密码登录

删除与修改用户
    重命名：rename user '用户名'@‘主机地址’ to ‘新用户名’@‘IP地址’;

用户权限的详细说明如下所示。
    （1）CREATE权限和DROP权限允许用户创建新数据库（表）和删除（移掉）已有数据库（表）。
    （2）SELECT权限、INSERT权限、UPDATE权限和DELETE权限允许用户在数据库现有的表上进行操作。
    （3）INDEX权限允许用户创建或删除索引。如果用户具有某个表的CREATE权限，也可以在CREATE TABLE语句中加入索引定义项。
    （4）ALTER权限允许用户更改表的结构和重新命名表。
    （5）CREATE ROUTINE权限允许用户创建要保存的程序（函数），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。
    （6）GRANT权限允许用户给其他用户授权。
    （7）FILE权限允许用户使用LOAD DATA INFILE和SELECT… INTO OUTFILE等语句读写服务器上的文件，但不能将原有文件覆盖。
    MySQL中也可以使用MySQLadmin程序或SQL语句设定其他权限用于管理性操作，具体如下所示。
    （1）reload 命令与flush-privileges命令实现的功能相似，可以使服务器将授权表重新读入内存。refresh命令可以清空所有的表并关闭（或打开）记录文件。
    （2）shutdown命令可以关闭服务（只能通过MySQLadmin程序执行）。
    （3）processlist命令可以显示服务器内执行的线程信息。
    （4）kill命令可以终止其他用户连接数据库或更改服务器的操作方式。

授予权限
    用户存在：grant on <数据库.表> to <'username'@'hostname'> [identified by] ['password'] [with grant option];
    创建用户：grant   需要授权的权限   on   数据.表 to  ‘用户名’@‘主机名’indtdied  by  密码；

语法格式中，各参数的含义如下。
    privileges：表示权限类型。
    数据库.表：表示指定数据库中的某张表。另外，还可以使用符号“”表示不指定。例如，<数据库名.>表示指定数据库中的所有事物；<数据库名.存储过程>表示指定数据库中的存储过程；<.>表示所有数据库中的所有事物。
    username：表示用户名。
    hostname：表示客户端来源 P地址。
    identified by：可选项，表示为用户设置密码。
    password：表示用户的新密码，一般与indentified by参数搭配使用。

查看权限
    show grants for 'username'@'hostname';

取消权限
    revoke on <数据库.表> from <‘username’@'hostname'>;

mysql数据库的主从复制

简介

数据库运行时，一些因素可能会导致服务运行不正常，用户访问数据受阻。对于互联网公司，尤其是购物网站而言，这种情况造成的损失是无法估量的。因此，对数据库进行“备份”也是必不可少的操作。当主要的数据库死机时，系统能够快速地切换到备用的数据库上。本章将详细介绍数据库集群中的主从复制原理和操作流程。

主从复制原理

I/O线程主要将主数据库上的日志复制到自己的中继日志中，SQL线程主要用于读取中继日志中的事件，并将其重放到从数据库之上

复制过程
    （1）主服务器上面的任何修改都会通过自己的I/O线程保存在二进制日志里。
    （2）从服务器上面也会启动一个I/O线程，通过配置好的用户名和密码连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个中继日志的末端，并将读取到的主服务器端的二进制日志的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚地告诉主服务器：我需要某个二进制日志的某个位置之后的日志内容，请发给我。
    （3）从服务器的SQL线程检测到中继日志中新增加了内容后，会马上解析日志中的内容，并在自身执行。需要注意，每个从服务器都会收到主服务器二进制日志中的全部内容的副本，除非另行指定，否则，从服务器将执行来自主服务器二进制日志文件的所有的操作语句。另外，从服务器每次进行同步时，都会记录二进制日志坐标（坐标包含文件名和从主服务器上读取的位置，即master-info），以便下次连接使用。由于每个从服务器分别记录了当前二进制日志的位置，因此可以断开从服务器的连接，重新连接，然后恢复处理。

基本架构
    在 MySQL 的主从复制集群中，主数据库既要负责写操作又要负责为从数据库提供二进制日志，这无疑增加了主数据库的压力。此时可以将二进制日志只给某一个从服务器使用，并在该从服务器上开启二进制日志，将该从服务器二进制日志分发给其他的从服务器；或者，这个从服务器不进行数据的复制，只负责将二进制日志转发给其他的从服务器。这样，不仅可以减少主服务的压力，还可以提高整体架构的性能。
        一主多从原理如图所示

        一主多从架构如图 所示。

复制模式
    ySQL主从复制的方式可以分为异步复制、同步复制和半同步复制，3种方式
        1．异步复制
            异步复制为MySQL默认的复制方式， 主数据库执行完客户端提交的事务后会立即将结果返给客户端，并不关心从数据库是否已经接收并进行了处理。 从日志的角度讲，在主数据库将事务写入二进制日志文件后，主数据库只会通知dump线程发送这些新的二进制日志文件，然后主数据库就会继续处理提交操作，并不考虑这些二进制日志已经传到每个从数据库节点上。在使用异步复制模式时，如果主数据库崩溃，可能会出现主数据库上已经提交的事务并没有传到从数据库上的情况，如果此时将从数据库提升为主数据库，很有可能导致新主数据库上的数据不完整。

        2．同步复制
            同步复制是指主数据库向从数据库发送一个事务,并且所有的从数据库都执行了该事务后才会将结果提交给客户端。因为需要等待所有的从数据库执行完该事务，所以在使用同步复制时，主数据库完成一个事务的时间会被拉长，系统性能受到严重影响。

        3．半同步复制
            半同步复制介于同步复制与异步复制之间，主数据库只需要等待至少一个从数据库节点收到并且更新二进制日志到中继日志文件即可，不需要等待所有从数据库给主数据库反馈。如此一来，不仅节省了很多时间，而且提高了数据的安全性。另外， 由于整个过程产生通信，所以建议在低延时的网络中使用半同步复制。

一主一从复制
主从复制流程
必须保证两台主机操作系统和数据库安装版本一致
给两台主机名，并且关闭两边防火墙和selinux
给两台主机同时安装数据库（yum安装）
修改两边主机的配置文件
master：
/etc/my.cnf.d/mariadb-server.cnf
log_bin=mysql-bin 开启二进制
binlog_ignore_db=mysql 不记录数据库的二进制日志
server_id=200 配置主库服务id（主库的服务id必须比从库服务id小）
重启服务

slave：
    /etc/my.cnf.d/mariadb-server.cnf 
    21 log_bin=mysql-bin 开启二进制
    22 binlog_ignore_db=mysql 不记录数据库的二进制日志
    23 server_id=200 配置主库服务id（主库的服务id必须比主库服务id大）
    重启服务

5.登陆主库创建用户并且授权
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';(GRANT REPLICATION代表给某一个用户授权)
6.登陆从库进行指定主库是谁
change master to master_host='192.168.101.120',master_user='slave',master_password='123456';（指定主库的服务器在哪里， master_host代表指定的主库IP地址，master_user代表同步是哪一个用户身份登陆进去同步，master_password用户密码）
7.开启主从同步
8.验证，主库随便创建数据库或者表在从库查看是否出现

多主多从复制

mysql数据库的读写分离

简介

数据库运行时，一些因素可能会导致服务运行不正常，用户访问数据受阻。对于互联网公司，尤其是购物网站而言，这种情况造成的损失是无法估量的。因此，对数据库进行“备份”也是必不可少的操作。当主要的数据库死机时，系统能够快速地切换到备用的数据库上。本章将详细介绍数据库集群中的主从复制原理和操作流程。

数据库代理
在单一的主从数据库架构中，前端应用通过数据库的IP地址或者指定端口向后端请求相应的数据。当面对较多的数据库服务器时，Web请求需要在这些服务器之间进行判断，以便找到哪一台服务上保存着自己想要的数据。面对数据请求高峰，这种判断不仅会带来大量的请求等待，而且有可能会造成整个系统的瘫痪。

基本原理

现实生活中，许多企业会通过在不同的地域招募代理商来提高品牌的宣传速度和市场占有率。随着互联网的不断发展，人们也会通过淘宝、京东这种“代理”平台来满足自己的消费需求。在互联网领域，系统管理员在面对数据库的多主集群时，也会使用代理服务器来实现数据的分发和资源的合理应用。代理服务器是网络信息的中转站，是信息“交流”的使者

常见的数据库中间件

随着市场的发展和技术的更新，产生了许多不同的数据库中间件，国内企业中目前使用较多的数据库中间件有以下几种。
（1）MySQL Proxy：MySQL官方提供的数据库中间件。
（2）Atlas：奇虎360团队在MySQL Proxy的基础上进行的二次开发。
（3）DBProxy：美团点评在Atlas的基础上进行的二次开发。
（4）Amoeba：早期阿里巴巴使用的数据库中间件。
（5）Cobar：由阿里巴巴团队进行维护和开发。
（6）Mycat：由阿里巴巴团队进行维护和开发。

9.来到第三台主机安装读写分离解析器 yum -y install lua*
10.下载读写分离压缩包，进行解压/usr/local
11.修改包名mv 解压包 mysql-proxy
12.添加环境变量： export PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
13.编辑服务启动配置文件，进入mysql-proxy目录编辑vim mysql-proxy.conf
[mysql-proxy]
plugins=proxy 插件是谁
proxy-address=192.168.101.140:4040 中间件主机的IP地址以及端口号
proxy-backend-addresses=192.168.101.120:3306 主库主机的IP地址以及端口号
proxy-read-only-backend-addresses=192.168.101.130:3306 从库主机的IP地址以及端口号
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 解析器配置文件的位置（默认不变）
log-file=/usr/local/mysql-proxy/mysql-proxy.log 中间件日志文件位置
log-level=debug 日志级别
keepalive=true 开启高可用
daemon=true 开启守护进程
14.启动服务mysql-proxy --defaults-file=/解压位置/mysql-proxy/mysql-proxy.conf 肯定报错（赋予权限，chmod 660 mysql-proxy.conf）
15.重新再次启动mysql-proxy --defaults-file=/解压位置/mysql-proxy/mysql-proxy.conf
16.修改解析器配置文件最小连接数为1（find / -name *lua）min字段后面的数字改为1即可
17.重启服务，pkill mysqk-proxy杀死进程，启动mysql-proxy --defaults-file=/解压位置/mysql-proxy/mysql-proxy.conf
18.进入主库和从库授权
主库和从库都要执行grant all on . to ‘mysqlproxy’@'%'identified by ‘123456’;（中间件的用户名可自己指定）
刷新权限flush privileges;
19.来到读写分离主机安装数据库服务端yum -y install mariadb-server 安装完启动服务
20.验证读写分离通过 mysql -umysqlproxy -p -P 4040 -h 读写分离所在的主机地址