@[TOC](目录)
目录
目录
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL字符集和排序的排序规则：
字符集utf8 和utf8mb4 的区别:MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字。utf8 只支持最长三个字节的 UTF-8字符，也就是 Unicode 中的基本多文本平面。要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持。那么utf8mb4比utf8多了什么的呢?多了emoji(表情,Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)编码支持.如果实际用途上来看,可以给要用到emoji的库或者说表,设置utf8mb4.比如评论要支持emoji可以用到。
排序规则概念：是指对指定字符集下不同字符的比较规则。排序规则有以下特征：
- 它和字符集（
CHARSET）相关 - 每种字符集都有多种它支持的排序规则
- 每种字符集都会默认指定一种排序规则为默认值。
排序规则作用：排序规则指定后，它会影响我们使用 ORDER BY语句查询的结果顺序，会影响到 WHERE条件中大于小于号的筛选结果，会影响 DISTINCT、GROUP BY、HAVING 语句的查询结果。另外，mysql 建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都和排序规则有关。下面介绍：
utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果。
utf8_unicode_ci准确度高，但校对速度稍慢。如果应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了
utf8mb4_general_ci
ci即case insensitive，不区分大小写。没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致，但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。另外，在比较和排序的时候速度更快。
排序：我们在使用 Navicat for MySQL 或 MySQL Workbench 创建数据库的时候，除了数据库名称和字符集外，我们还可以设置 Collation【排序规则】 （当然我们也可以使用默认的排序规则），那么这里的排序规则到底是干什么的？
- MySQL是开源的，所以你不需要支付额外的费用。
- MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL使用标准的SQL数据语言形式。
- MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
- MySQL对PHP有很好的支持，PHP是目前最流行的Web开发语言。
- MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
- MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
# 创建表
创建数据库：
DROP TABLE IF EXISTS `xxxx`;
CREATE TABLE `xxxx` (
`id` int(11) NOT NULL,
`openTime` bigint(20) DEFAULT 0,
`shows` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table reader(
card_id char(18),
name varchar(10),
sex enum('男','女'),
age tinyint,tel char(11),
balance decimal(7,3)
);修改数据库：alter database character set utf8;
删除数据库：drop database test;或者drop database if exists test;
创建和查看数据库表
查看表结构
show columns from xx; 或者 desc xx;
查看创建表的语句
show create table xx;
表增加新的列
ALTER TABLE `xxx` ADD COLUMN `hasTime` INT(11) DEFAULT 0; COLUMN 可以省略约束条件可选，first和after表示定位插入列位置。增加第一列可以使用FIRST, 非第一列用after
alter table reader add email varchar(30) after tel;
修改表名
alter table reader rename to readerinfo;
修改表的列名：
alter table reader change shows shows2 varchar(30)
修改表数据类型
alter table xxxx MODIFY shows2 int(11)
修改表中列的相对位置
alter table xxxx modify shows2 VARCHAR(10) after openTime;
删除的表的列
alter table reader drop email_bak;
删除一个或多个表
drop table if exists t2,t3;
创建表的时候：ENGINE=InnoDB 含义：使用innodb引擎 InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一 。DEFAULT CHARSET=utf8 含义：数据库默认编码为utf-8；character set可以简写为char set和charset
CREATE TABLE test
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 100;
在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。
alter table tbname auto_increment = x ;
设置表tbname的唯一auto_increment字段起始值从x开始，如果此表数据量很多的话，这样执行起来会很慢.
AUTO_INCREMENT说明：
（1）如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。
（2）把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。
（3）当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。
（4）当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；
情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。
（5）如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。
（6）AUTO_INCREMENT字段必须为唯一的且非null的， 也就是需要为主键。
复制表：
1.复制表结构 CREATE TABLE newuser LIKE user;
2.导入数据 INSERT INTO newuser SELECT * FROM user;
复制表的数据：CREATE TABLE IF NOT EXISTS tb_base_select SELECT * FROM tb_base; 此方法值复制字段属性，其它的主键、索引、表备注、存储引擎都没有复制，即是默认的。
# 约束
约束是一种限制，它通过对表的行为或列的数据做出限制，来确保表的数据的完整性，唯一性。
主键约束 PRIMARY KEY 唯一约束 UNIQUE 非空约束 NOT NULL 默认约束 DEFAULT 外键约束 FOREIGN KEY主键约束(PRIMARY KEY)：主键约束要求主键的列的数据唯一。并且不许为空，主键能够唯一的标识表中的一条记录。创建主键时会自动创建主键索引（聚集索引）。
主键使用自增ID还是UUID:推荐使用自增ID,不要使用UUID.因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降
AUTO_INCREMENT 修饰符：AUTO_INCREMENT修饰符只适用于INT字段，表明MySQL应该自动为该字段生成一个数(每次在上一次生成的数值上面加1)。对于主键，这是非常有用的。因为其允许开发者使用MySQL为每条记录创建唯一的标识符。字段后面跟 COMMENT=‘表注释’，如`id` int(10) not null auto_increment COMMENT=‘表注释’, id再脚本里被反引号包围。
单字段主键：由一个字段（列）组成
1.在定义列的同时指定主键
列名 数据类型 PRIMAY KEY;
2.在列定表的后边指定主键
[constraint<约束名>] PRIMARY KEY(列名);多字段联合主键：复合主键只能在定义表的时候定义。不能后期加。从最左侧的键开始匹配，中间不能中断。如果创建了abc，相当于分别创建了a，ab，abc三个索引。都可以用，如果反过来用下面的card_,book_id也可以，因为mysql底层做了优化，可以转化到联合索引。当Mysql中建立的联合索引， 只要索引中的某一列的值为空时(NULL)，即便其他的字段完全相同，也不会引起唯一索引冲突。原因：唯一约束对NULL值不适用。原因可以这样解释： 比如我们有一个单列的唯一索引，既然实际会有空置的情况，那么这列一定不是NOT NULL的，如果唯一约束对空值也有起作用，就会导致仅有一行数据可以为空，这可能会和实际的业务需求想冲突的，所以通常Mysql的存储引擎的唯一索引对NULL值是不适用的。
PRIMARY KEY(字段1，字段2);
DROP TABLE IF EXISTS `xxxx`;
CREATE TABLE `xxxx` (
`id` int(11) NOT NULL,
`openTime` bigint(20) DEFAULT 0,
`shows` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`, `openTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
删除主键
alter table xxxx drop PRIMARY KEY;
添加主键
alter table xxxx MODIFY id int(11) primary key;
alter table xxxx add primary key(id);
alter table xxxx add constraint pk_id primary key(id);
查询一个表有哪些索引：show index from tablename ：
删除索引：drop index id on mytest唯一约束：一张表中指定的一列的值不能重复，每个值唯一,允许为空，唯一约束可以确保一列或者几列不出现重复值。创建唯一约束，会自动创建一个同名的唯一索引。
定义完列后直接指定唯一约束
列名 数据类型 UNIQUE
定义完所有列之后指定唯一约束
[constraint<约束名>]UNIQUE(列名)
CREATE TABLE `xxxx` (
`id` int(11) NOT NULL,
`openTime` bigint(20) UNIQUE NOT NULL,
`shows` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`, `openTime`),
constraint uk UNIQUE(shows)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table xxxx modify id varchar(20) unique;
alter table xxxx add unique(id);
alter table xxxx add constraint uk unique(id);主键约束和唯一约束的却别：
一个表可以有多个UNIQUE声明，但是只能有一个PRIMARY KEY声明
PRIMARY KEY 不能为空，UNIQUE 列可以为空。
InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。
默认约束：插入数据没有为字段赋值时，会自动赋予字段值。
CREATE TABLE `xxxx` (
`shows` varchar(500) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加
alter table xxxx modify shows varchar(20) DEFAULT "showtime";
ALTER TABLE `xxxx` ADD COLUMN `id` varchar(100) DEFAULT 'xsd';
删除
alter table xxxx modify varchar(20);
alter table xxxx alter COLUMN shows varchar(20);外键约束：在两个表的数据之间建立链接，通过外键约束保证数据的完整性一致性。外键所在的表叫从表（子表），索引主键所在的表叫主表（父表），先有主表再有从表。两个表的对应的字段的字符长度可以不同，但是类型要相同。一个表可以有一个或多个外键，外键可以为空，若不为空，则每一个外键值必须等一另一个表中主键的某个值。
constraint <外键约束名> FOREIGN KEY(列名) references<主表名>（主键）
CREATE TABLE `ssss` (
`id` int PRIMARY KEY,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `xxxx` (
`name` varchar(500) PRIMARY KEY,
`uid` int(11),
constraint fk_id FOREIGN KEY(uid) references ssss(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加
alter table xxxx add FOREIGN KEY(uid) references ssss(id);
删除
alter table xxxx drop FOREIGN KEY fk_id;
从父表删除或更新且自动删除或更新子表中匹配的行
CREATE TABLE `xxxx` (
`name` varchar(500) PRIMARY KEY,
`uid` int(11),
constraint fk_id FOREIGN KEY(uid) references ssss(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;如果创建的时候加上后面on delete cascade，主表删除的时候会更新子表的行
SET FOREIGN_KEY_CHECKS 设置外键约束状态;在MySQL中删除一张表或一条数据的时候，出现
[Err] 1451 -Cannot delete or update a parent row: a foreign key constraint fails (...)
这是因为MySQL中设置了foreign key关联，造成无法更新或删除数据。可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况
我们可以使用
SET FOREIGN_KEY_CHECKS=0;
来禁用外键约束.
之后再用
SET FOREIGN_KEY_CHECKS=1;
来启动外键约束.
查看当前FOREIGN_KEY_CHECKS的值可用如下命令 SELECT @@FOREIGN_KEY_CHECKS;
非空约束(NOT NULL)：指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据的时候，没有指定值，数据库系统会报错。
CREATE TABLE `xxxx` (
`id` int(11) NOT NULL,
`time` int(11),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加非空约束
alter table xxxx modify time int(10) not null;
删除
alter table xxxx modify openTime int(10); 如果指定字段可以为NULL，则MySQL为其设置默认值为NULL。如果是NOT NULL字段，MySQL对于数值类型插入0，字符串类型插入空字符串，时间戳类型插入当前日期和时间，ENUM类型插入枚举组的第一条。非空约束是因为：null值会占用更多的字节,且会在程序中造成很多与预期不符的情况
KEY wh_logrecord_user_name (user_name) 含义 ：user_name字段 创建一个约束名为wh_logrecord_user_name 的key;
给约束取名字, 目的是将来你可以比较容易的删除掉.否则你要自己去查询数据字典表.
检查约束：MySQL 检查约束（CHECK）是用来检查数据表中字段值有效性的一种手段，可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。
mysql> CREATE TABLE tb_emp7
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> CHECK(salary>0 AND salary<100),
-> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
-> );
Query OK, 0 rows affected (0.37 sec)key 是数据库的物理结构，它包含两层意义，一是约束（constraint：偏重于约束和规范数据库的结构完整性），二是索引（index:辅助查询用的）；约束是为了保证表数据的完整性，索引是为了提高查询效率，两者作用不一样！其次种类也不一样。
约束作用：是为了保证数据的完整性而实现的摘自一套机制，它具体的根据各个不同的数据库的实现而有不同的工具(约束)；
约束分类：非空约束，主键约束， 唯一约束，默认约束，外键约束，自增约束，检查约束（CHECK），
索引作用：快速定位特定数据，提高查询效率，确保数据的唯一性，快速定位特定数据；可以加速表和表之间的连接，实现表与表之间的参照完整性，使用分组和排序语句进行数据检索时，可以显著减少分组和排序的时间全文检索字段进行搜索优化；
索引分类：主键索引(primary key);唯一索引(unique);常规索引(index);全文索引(full text);
MySQL对varchar型字段的索引，字段类型不同造成的隐式转换，导致索引失效
index是数据库种一个有序的数据结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储；
UNIQUE KEY `index_key` (`bid`,`uid`) USING BTREE, 这里 USING BTREE 为索引类型
Mysql支持的索引类型：B-TREE索引与HASH索引 ，但是 MYISAM和InnoDB存储引擎只支持BTREE索引；MEMORY和HEAP存储引擎可以支持HASH和BTREE索引。
B-TREE索引以B+树结构存储数据，大大加快了数据的查询速度。
不同点：
1.hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.
2.hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.
因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围。
3.hash索引不支持使用索引进行排序,原理同上。
4.hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性mysql中的回表查询与索引覆盖 - yanggb - 博客园。
5.hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
覆盖索引：如果查询的列值包含在使用的索引中的时候就叫覆盖索引，由mysql的优化器optimizer优化。
6.hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低。
因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.
# 插入，更新，删除数据
插入数据时如果给所有字段赋值就用
insert into xxxx values(1,2,3);
或同时插入多个
insert into xxxx values (1,1,1),(2,2,2);
删除
DELETE FROM 表名称 WHERE 列名称 = 值
更新
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
update 表名称 set 列名称 = 新值, 列名称 = 新值;# 数据类型
TINYINT[( M )] [UNSIGNED] [ZEROFILL] 。 M 表示每个值的位数，范围为从1到64。如果 M 被省略， 默认为1。M指定整数值的显示宽度(例如，INT(4))。该可选显示宽度规定， 用于显示宽度小于指定的列宽度的值时从左侧填满宽度。当结合可选扩展属性ZEROFILL使用时， 默认补充的空格用零代替。例如，对于声明为INT(5) ZEROFILL的列，值4检索为00004 。如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性。BOOL，BOOLEAN 是TINYINT(1)的同义词。zero值被视为假。非zero值视为真。UNSIGNED和ZEROFILL表示两个属性，浮点和定点类型也可以为UNSIGNED，UNSIGNED属性就是将数字类型无符号化。
L代表字符的长度
MySql 5.0 以上的版本：
mysql中一个汉字占多少长度与编码有关：
UTF-8：一个汉字 = 3个字节，英文是一个字节
GBK： 一个汉字 = 2个字节，英文是一个字节
char是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后多一个字符存储使用了多长的空间.
这VARCHAR(100)与VARCHAR(200)真的相同吗?
结果是否定的。虽然他们用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，则不是。其时使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。
在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char.
text也是可变长度字符。
TIMESTAMP和DATETIME的相同点：
两者都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期
TIMESTAMP和DATETIME的不同点：
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
而对于DATETIME，不做任何改变，基本上是原样输入和输出。
timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
关于TIMESTAMP和DATETIME的自动初始化和更新：插入一条数据不指定对应列的值得时候会自动插入当前时间。可以通过查看create表语句看。
在MySQL 5.6.5版本之前，Automatic Initialization and Updating只适用于TIMESTAMP，而且一张表中，最多允许一个TIMESTAMP字段采用该特性。从MySQL 5.6.5开始，Automatic Initialization and Updating同时适用于TIMESTAMP和DATETIME，且不限制数量。
mybatis中不指定java的date的转换类型时，默认把Date默认转换为timestamp。
更新timestamp： update activity set firstTime = "2020-10-28 05:00:00" where uid = 19;
BLOB介绍：BLOB (binary large object)，二进制大对象，是一个可以存储二进制文件的容器。在计算机中，BLOB常常是数据库中用来存储二进制文件的字段类型。BLOB是一
个大文件，典型的BLOB是一张图片或一个声音文件，由于它们的尺寸，必须使用特殊的方式来处理（例如：上传、下载或者存放到一个数据库）。根据Eric Raymond的
说法，处理BLOB的主要思想就是让文件处理器（如数据库管理器）不去理会文件是什么，而是关心如何去处理它。但也有专家强调，这种处理大数据对象的方法是把双
刃剑，它有可能引发一些问题，如存储的二进制文件过大，会使数据库的性能下降。在数据库中存放体积较大的多媒体对象就是应用程序处理BLOB的典型例子。
MySQL中，BLOB是个类型系列，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储文件的最大大小上不同。
TinyBlob 最大 255，Blob 最大 65K，MediumBlob 最大 16M，LongBlob 最大 4G
# 表分区
MySQL支持的分区类型有哪些？
（1）、RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
（2）、LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
（3）、HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
（4）、KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
说明：在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列(BLOB，TEXT类型除外)作为分区键。
一般又两种方式：水平和垂直，水平分区是将表的数据按行分割成不同的文件，而垂直分区则是将表的数据按列分割成不同的数据文件，
水平分区一定要通过某个属性列来分割。比如一张包括每年的历史大事的数据表，数据都存在一张表中，可以按年份字段进行分区，从而让每个分区的数据量适中，避免全表过大。
垂直分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。不过这种情况应该比较少，垂直分区不如直接分表了。
注意：最大分区数目不能超过1024，
表分区有什么好处？
（1）、与单个磁盘或文件系统分区相比，可以存储更多的数据。
（2）、对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
（3）、一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
（4）、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
5）、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
分区表的限制因素
（1）、一个表最多只能有1024个分区。
（2）、 MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
（3）、如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
（4）、分区表中无法使用外键约束。
（5）、MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。
查询表分区：
select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema = schema() and table_name='xxxx';
可以查看有多少个分区
select count(partition_name), table_name from information_schema.partitions where table_schema = schema() and table_name='xxxxx';
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
如何判断当前MySQL是否支持分区
show variables like '%partition%';rang分区：在创建表的时候使用：partition by 类型(字段) ,
Range分区：根据指定某列的范围值进行分区，使用values less than 操作符定义分区
create table xxxx(
id int(11),
name varchar(20)
)
partition by range(id)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (MAXVALUE),
)insert into bookinfo values(20170001,'t3');
select * from bookinfo partition(p3);
一次性删除多个分区，
alter table emp drop partition p1,p2;
alter table 数据库名.表名 drop partition xx;
增加分区:
alter table bookinfo add partition (partition p3 values less than (4000));
alter table bookinfo add partition (partition p3 values in (40));
list分区
根据具体数值分区，每个分区数值不重叠，使用PARTITION BY LIST、VALUES IN关键字。跟Range分区类似，不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。
类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
drop table if exists staff;
create table staff(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null default 0,
store_id int not null default 0
)
partition by list(store_id)(
partition pNorth values in (3,5,6,9,17),
partition pEast values in (1,2,10,11,19,20),
partition pWest values in (4,12,13,14,18),
partition pCentral values in (7,8,15,16)
); Hash分区主要用来确保数据在预先确定数目的分区中平均分布，Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1
drop table if exists staff;
create table staff(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null default 0,
store_id int not null default 0
)
partition by hash(store_id)
partitions 4;或者
partition by hash(year(hired))
partitions 4;KEY分区
Key分区与Hash分区很相似，只是Hash函数不同，定义时把Hash关键字替换成Key即可，同样Key分区也有对应与线性Hash的线性Key分区方法。
drop table if exists staff;
create table staff(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null default 0,
store_id int not null default 0
)
partition by key(store_id)
partitions 4;另外，当表存在主键或唯一索引时可省略Key括号内的列名，Mysql将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错。
# mysql配置文件
MySQL :: MySQL 5.6 Reference Manual :: 17.1.4.4 Binary Log Options and Variables
1.skip-name-resolve：选项可以禁用dns解析，但是，这样不能在mysql的授权表中使用主机名了，只能使用IP。
2. bind-address：msyql默认的bind-address是127.0.0.1，这样的话mysql只接受localhost，错误提示为：ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)，bind-address后面增加远程访问IP地址或者禁掉这句话就可以让远程机登陆访问了。
3.character-set-server=utf8 改字符集为utf8
4.max_binlog_size 如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。创建新的二进制日志。
5.Expire_logs_days :定义了mysql清除过期日志的时间。二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。
6.server-id = n给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次
7.log-bin = name log-bin这个参数不是写路径的，只是写bin-log文件前缀名
8.log_bin 指定是否启用记录二进制日志或者指定一个日志路径
9.binlog-do/ignore-db = dbname 只把给定数据库里的变化情况记入二进制日志文件/不把给定的数据库里的变化记入二进制日志文件。
10.key_buffer索引的缓冲区大小 ，物理内存的1/8
11.innodb_buffer_pool_size ： InnoDB使用一个缓冲池来保存索引和原始数据, 你可以设置这个变量到服务器物理内存大小的80%
12.event_scheduler是MySQL事件调度器的开关，类似于windows操作系统的定时任务的概念，指定某个时间点执行一次定时任务，或者每隔一段时间循环执行定时任务。默认是on（打开）。Off为关闭
13.log_bin_trust_function_creators当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。在主从环境的时候可能会有二进制日志。
14.设置日志格式
binlog_format = mixed
MySQL binlog格式
binlog的格式也有三种：STATEMENT、ROW、MIXED 。
1、STATMENT模式：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
2、基于行的复制(row-based replication, RBR)：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。
优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
3、混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
当设置为0时，存储和比较都是大小写敏感的；当设置为1是存储时会转换为小写，比较时不区分大小写；当设置为2时候，存储时区分大小写，比较的时候转换为小写。此选项对数据库名和数据表名起作用。
lower_case_table_names是个只读变量，不能直接设置生效，必须在配置文件中设置并重启mysqld服务。
# 命令
登录
mysql -uroot -p 以root用户登录mysql 密码为空 p后可跟密码 ，如果服务器运行在登录服务器之外的其它机器上，还需要指定主机名 如：mysql -h host -u user -p
windows下修改密码：mysqladmin -uroot -p 旧密码 password 然后输入两次新密码
linux 下修改密码 ：mysqladmin -u root -p password "新密码" 回车 ，Enter password: 【输入原来的旧密码】
QUIT 或者exit control-D
show databases; 查看数据库 use xxx 使用数据库 show tables;查看表 DESCRIBE（desc） xxx 查看表的结构
安装mysql会为mysql创建单独的mysql账户，防止mysql被攻击然后获取root用户权限。
查看 mysql状态 service mysql status（要root） 关闭数据库服务 service mysql stop service mysql restart service mysql start
mysql -V 查看mysql版本信息 select version(), current_date; 要求服务器告诉它的版本号和当前日期。
mysql查询正在执行的进程 ：show processlist;
在mysql中可以用system 执行系统命令。例如：mysql>system cat /tmp/2.txt
权限
grant select insert on *.* to tomcat@192.168.0.80 identified by ''; //192.168.0.80 设置对应ip的tomcat的select用户不用密码 `
grant all on *.* to tomcat@192.168.0.80 identified by ''; //192.168.0.80 gm的ip 设置对应ip的tomcat用户的所有权限不用密码
查看用户权限（show grants for 用户@主机） https://www.cnblogs.com/yangmingxianshen/p/7997725.html
修改 grant all privileges on *.* to tomcat@127.0.0.1 identified by ''; grant select on *.* to tomcat@192.168.0.80 identified by '';
回收权限 revoke insert,select on *.* from maoxian@localhost;
查询
语句执行顺序：
1.通过FROM子句中找到需要查询的表；
2.通过WHERE子句进行非分组函数筛选判断；
3.通过GROUP BY子句完成分组操作；
4.通过HAVING子句完成组函数筛选判断；
5.通过SELECT子句选择显示的列或表达式及组函数；
6.通过ORDER BY子句进行排序操作。
7.通过limit进行分页
<> 为不等号
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL), 则 IF() 的返回值为 expr2；否则返回值则为 expr3。IF() 的返回值为数字值或字符串值
set @name='cm3333f';
select @id:=1;
set 可以用=号赋值,而select 不行,必须使用:=
分组后在分组内排序、每个分组中取前N条
SELECT 字段1,字段2,字段3,row_num from (
SELECT a*,IF(@bak=分组排序条件1,@rownum:=@rownum+1,@rownum:=1) as row_num, @bak:=分组排序条件1
FROM
( SELECT * from products
order by 分组排序条件1,分组排序条件2 desc ) a , ( SELECT @rownum:=0,@bak:='') b
) c WHERE c.row_num <=2
与(SELECT @rownum:=0,@bak:='')进行全连接，用于将来打序号用1.查询表有多少数据 select count(表中任意属性名) from 表名; COUNT()函数计算行数
count(*) 和count（0）、count（1）是一样的
2.select 查询 where ，LIMIT， group by， AS，order by， select语句 where 放在表名后面 ，其次是groupby 然后是order by（放在前面需要嵌套查询），在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目.
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
如果只给定一个参数等价于limit 0, n 表示返回前n行。
优化：
3.order by/ORDER BY 语句用于根据指定的列对结果集进行排序, 默认按照升序, 如果您希望按照降序对记录进行排序，可以再后面使用再 DESC 关键字 默认是（ASC）
原来我进行嵌套查询的时候子查询出来的的结果是作为一个派生表来进行上一级的查询的，所以子查询的结果必须要有一个别名， 例如下面的as t 但是 as 可以省略
select * from (select iRoleId, dtEventTime, iLevel from RoleLoginout WHERE iRoleId = '1000004' order by iLevel desc) as t group by iRoleId；
4. group by 只出现数据库里第一个匹配的 它可以和其他的HAVING SUM 等函数配合使用。 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计 函数一起使用
select * from (select iRoleId, dtEventTime, iLevel from RoleLoginout order by iLevel desc) as t group by iRoleId HAVING SUM(iLevel)>11500;
select iRoleId, dtEventTime, iLevel, SUM(iLevel) from RoleLoginout where iRoleId < 1000010 group by iRoleId;
select iRoleId, dtEventTime, iLevel, SUM(iLevel) from RoleLoginout where iRoleId < 1000010 group by iRoleId order by iLevel desc;
5.SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; # or 表示或者 and表示并且
AND和OR可以混用，但AND比OR具有更高的优先级。如果你使用两个操作符，使用圆括号指明如何对条件进行分组是一个好主意
6.关键词 DISTINCT 用于返回唯一不同的值。
SELECT DISTINCT 列名称 FROM 表名称
7.模糊查询用 regexp
select * from RoleEventFlow WHERE iRoleId= '8000035' AND vConsumeItems regexp 4058 AND dtEventTime>='2019-11-01 00:00:00' AND dtEventTime <= '2019-11-03 23:59:59' order by dtEventTime desc;
正则查询包含特殊字符
select * from season_shop where buyInfo regexp '"60":';
LOCATE() 函数
1) LOCATE(字符串1,字符串2)
返回字符串1在字符串2中第一次出现的位置，只要字符串2中包含字符串1，那么返回值必然大于0。
2) LOCATE(字符串1,字符串2,start)
返回字符串1在字符串2中第一次出现的位置，从位置start开始算起；如果返回0，表示从start位置开始之后没有了
或者like
8.%: 任意长度任意字符(包括零字符)
SELECT Name FROM students WHERE Name LIKE 'Y%';
9._：任意单个字符
SELECT Name FROM students WHERE Name LIKE 'Y____';
like是完全匹配。rlike和regexp是不完全匹配.
REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词 mysql中的正则表达式不区分大小写 如果想要强制区分。加关键字BINARY使其中一个字符串变为二进制字符串，NOT REGEXP是REGEXP的反义词。
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
10.可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。例如，按升序对动物的种类进行排序，
然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询：
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;注意DESC关键字仅适用于在它前面的列名(birth)；不影响species列的排序顺序。
SELECT name, birth, death, (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5)) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符。比较MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。整个表达式有些难懂，
使用alias (age)来使输出的列标记更有意义。death IS NOT NULL 表示该字段不为null 不能使用death != NULL，因为NULL是特殊的值，不能使用普通比较符来比较
MONTH( )提取月 DAYOFMONTH( ) 提取哪天
请注意在MySQL中，0或NULL意味着假而其它值意味着真。布尔运算的默认真值是1。在GROUP BY中，两个NULL值视为相同。 SELECT 1 IS NULL, 1 IS NOT NULL; # 0 1
11.FROM子句列出两个表，因为查询需要从两个表提取信息。当从多个表组合(联结)信息时，你需要指定一个表中的记录怎样能匹配其它表的记录。这很简单，因为
它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。因为name列出现在两个表中，当引用列时，你一定要指定哪个表。把表名附在列名前即可以实现。
SELECT pet.name,(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark FROM pet, event WHERE pet.name = event.name AND event.type = 'litter'; pet和event 是两个表
你不必有2个不同的表来进行联结。如果你想要将一个表的记录与同一个表的其它记录进行比较，可以将一个表联结到自身
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1, pet AS p2 WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
或者两个不同的表 select * from a,b where a.id = b.id;
列的最大值: SELECT MAX(列名) AS article FROM shop;
拥有某个列的最大值的行 ：
SELECT * FROM shop WHERE price=(SELECT MAX(price) FROM shop);
或者 SELECT * FROM shop ORDER BY price DESC LIMIT 1;
12.LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
select * from a where id = 1 and createTime >= "2021-09-29 00:39:36" and
createTime <= "2021-10-12 00:30:00" and not exists(select distinct id from b where a.id=
b.id and b.name = "haha");
//此为更新语句
UPDATE tableA SET f1=REPLACE(f1, 'abc', 'def') where f1 like '%abc%' and id in
select distinct(id) from tableB where defId in (1,2,3) and createTime >= "2021-09-29 00:39:36" and createTime <= "2021-10-12 00:30:00";
case when end：
一：
select
case
when level = 100 THEN
"manji"
when level > 60 THEN
"chadian"
else
"mei man"
end as levelnew,
level from player limit 100;
二：
SELECT
CASE parent_id
WHEN 0 THEN
'00'
WHEN 1 THEN
'11'
ELSE
'OTHERS'
END AS parent_id_new ,
parent_id ,
type_id ,
type_name
FROM
tdb_goods_types
三：
select
case
when level = 100 THEN
"manji"
when level > 80 and level < 90 THEN
"chadian"
else
"mei man"
end as levelnew,
level from player limit 100;
select S.syctime_day,
sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',
sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',
sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',
sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',
sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'
from statistic_order S where S.syctime_day > '2015-05-01' and S.syctime_day < '2016-08-01'
GROUP BY S.syctime_day order by S.syctime_day asc;
先对一条一条记录进行遍历，group by对日期进行了分类，sum聚合函数对某个日期的值进行求和，重点就在于case...when...then对sum的求和巧妙的加入了条件，当o_source = 'CDE'的时候，计数为1，否则为0；当o_source='SDE'的时候...... 所以sum case when end 更适用于统计数据
13.Linux中MySQL大小写详情：
1、数据库名严格区分大小写
2、表名严格区分大小写的
3、表的别名严格区分大小写
4、变量名严格区分大小写
5、列名在所有的情况下均忽略大小写
6、列的别名在所有的情况下均忽略大小写
7、SQL关键字不区分大小写
14. 修改my.cnf后可能报告 job failed to start
#错误原因：在my.cnf文件里面修改了innodb_log_file_size，
#导致ib_logfile文件的大小与之前存在的文件大小不匹配；
#解决方法：在干净关闭数据库的情况下，删除ib_logfile，然后重启数据库
15.存储过程
PROCEDURE存储过程：可编译的函数，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量 。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。
创建的时候 用 DEFINER=`root`@`localhost` 来定义执行时候的权限。
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `queryaccountgift`(IN XX INT, N `inrolename` varchar(128) character set utf8, OUT XX INT )
BEGIN
IF vkuid IS NOT NULL THEN
XX
ELSE
XX
END IF;
END
;;
DELIMITER ;
select `name` from mysql.proc where name = 'xx' and `type` = 'PROCEDURE';
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `xx`()
BEGIN
DECLARE var1 INT DEFAULT 0;
SELECT x INTO var1 FROM xxx;
IF var1 >= 0 THEN
insert into xxxx(a, b) VALUES
(1, current_timestamp()),
(2, current_timestamp()),
(3, current_timestamp()),
(4, current_timestamp()),
(5, current_timestamp()),
(6, current_timestamp()),
(7, current_timestamp());
END IF;
END ;;
call xx() ;;
DELIMITER ;
DROP PROCEDURE xx;
select `name` from mysql.proc where name = 'xx' and `type` = 'PROCEDURE';在存储过程中可以用 使用SELECT …INTO语句为变量赋值，
SELECT -1000 INTO retcode;
eg：SELECT kid, usedid, arg FROM accountinfo WHERE bid=inbid AND uid=inuserid INTO vkid, vkuid, varg;
DELIMITER 其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。 默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束， 那么回车后，mysql将会执行该命令。如输入下面的语句 mysql> select * from test_table; 然后回车，那么MySQL将立即执行该语句。默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。这种情况下，就需要事先把delimiter换成其它符号，如//或$$。这样只有当//出现之后，mysql解释器才会执行这段语句 .最后要记得设置回默认 DELIMITER ;
以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量，例如：
DECLARE var1 INT DEFAULT 0; DECLARE vkuid INT DEFAULT NULL; 不加default默认值就是null
主要用在存储过程中，或者是给存储传参数中。
call 存储过程名(实参列表) ,调用存储过程或者函数 https://www.jianshu.com/p/bd9393bda8eb
select `name` from mysql.proc where db = 'tlog' and `type` = 'PROCEDURE' //查询存储过程
select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' //查询函数
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;
16.mysql写注释的几种方法
1、单行注释可以用"#"
2、单行注释的第二种写法用 "-- " 注意这个空格"--【空格】" 也就是说“--" 与注释之间是有空格的。
3、多行注释可以用/**/ ，如果在！字符后添加版本号，则仅当MySQL版本大于或等于指定的版本号时，才会执行注释中的语法。如下面的KEY_BLOCK_SIZE注释中的关键字仅由MySQL 5.1.10或更高版本的服务器执行, 如果此格式用于注释存储程序，则注释不会保留在程序主体中.
CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;
17.你可以在一行上输入多条语句，只需要以一个分号间隔开各语句 SELECT VERSION(); SELECT NOW();
不必全在一个行内给出一个命令，较长命令可以输入到多个行中。mysql通过寻找终止分号而不是输入行的结束来决定语句在哪儿结束。
mysql> select user()
-> ,
-> current_date
-> ;如果不想执行正在输入过程中的一个命令可以入\c取消，在你输入\c以后，它切换回到mysql>
-> 等待多行命令的下一行。
'> 等待下一行，等待以单引号(“'”)开始的字符串的结束。
"> 等待下一行，等待以双引号(“"”)开始的字符串的结束。
`> 等待下一行，等待以反斜点(‘`’)开始的识别符的结束。
/*> 等待下一行，等待以/*开始的注释的结束。
18.TO_DAYS(date) 给定一个日期date 或 datetime, 返回一个天数 (从年份0开始的天数 )
19.函数
DATABASE()：MySQL中的DATABASE()函数返回默认或当前数据库的名称。DATABASE()函数返回的字符串或名称使用utf8字符集。如果没有默认数据库，则Database函数返回NULL。
20.MySQL提供了explain命令来查看语句的执行计划,并且可以分析创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因。explain xx（要执行的语句）；如：explain select * from t_order;
有必要解释一下这个长长的表格里每一列的含义：
| id | SELECT识别符。这是SELECT的查询序列号 |
| select_type | SELECT类型,可以为以下任何一种:
|
| table | 输出的行所引用的表 |
| type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
| possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
| key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
| key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
| ref | 显示使用哪个列或常数与key一起从表中选择行。 |
| rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
| filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
| Extra | 该列包含MySQL解决查询的详细信息
|
21. truncate table 表明 ：(截断表) 是清空一个表，是一个DDL语言，效率高，不需要条件，它与delete有如下区别
一、释bai放数据不同
1、truncate table：truncate table 直接du释放数据页，并且在事务日志中也只记录数据页的释放。
2、delete * from：delete * from 是一行一行地释放数据，在事务日志中要记录每一条记录的删除。
二、清空标识不同
1、truncate table：truncate table不仅是删除表里面的数据，而且还会清空表里面主键的标识。
2、delete * from：delete * from仅仅能删除数据，不能清空标识。
三、触发器不同
1、truncate table：truncate table不会激发触发器。
2、delete * from：delete * from会激发触发器。
22. information_schema：
在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权 限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。
information_schema数据库表说明:
1）SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
2）TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
3）COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
4）STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
5）USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
6）SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。
7）TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
8）COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
9）CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
10）COLLATIONS表：提供了关于各字符集的对照信息。
11）COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
12）TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
13）KEY_COLUMN_USAGE表：描述了具有约束的键列。
14）ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
15）VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
16）TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表。
information_schema.tables存储了数据表的元数据信息，下面对常用的字段进行介绍：
1）table_schema: 记录数据库名；
2）table_name: 记录数据表名；
3）engine : 存储引擎；
4）table_rows: 关于表的粗略行估计；
5）data_length : 记录表的大小（单位字节）；
6）index_length : 记录表的索引的大小；
7）row_format: 可以查看数据表是否压缩过；
SELECT CONCAT("TRUNCATE TABLE `", t.TABLE_NAME, '`;') FROM information_schema.`TABLES` t WHERE table_schema='数据库名'; 生成对应数据库的所有表的截断表语句。
23. insert into on DUPLICATE KEY UPDATE 在插入数据库的时候如果主键冲突之后，进行update操作，如下：
INSERT INTO user_admin_t (_id,password) VALUES ('1','第一次插入的密码') , ('2','第二条记录') ON DUPLICATE KEY UPDATE _id = 'UpId', password = 'upPassword';
如果想要同时修改多个记录可以将后面的修改条件改为VALUES（）
insert into a(c1,c2,c3,id) values(99,100,101,'f042fa40cd2a4db6b49994ea084e8dc0') on DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
insert into a(c1,c2,c3,id) values(99,100,101,'f042fa40cd2a4db6b49994ea084e8dc0'),(1,2,3,'e3b223bee17b4b7a820c76520fe98e5e') on DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
# View（视图）
视图（view）是一种虚拟的表，并不在数据库中实际存在。通俗的来说，视图就是执行select语句后返回的结果，对于数据库的用户来说，很多时候，需要的关键信息是来自多张复杂关联表的。这时用户就不得不使用十分复杂的SQL语句进行查询，给用户造成极差的体验感。使用视图之后，可以极大的简化操作，使用视图的用户不需要关心相应表的结构、关联条件等。对于用户来说，视图就是他们想要查询的结果集。使用视图的用户只能访问被允许查询的结果，使用视图可以限制用户访问一些敏感信息列。
视图的修改会体现在原表中，但是下面4种情况不可以修改，会报错。
1.定义视图包含统计函数的情况下。
2.视图定义时使用了group by 。having，disitinct，union语句时
3.定义视图时包含了子查询语句。
4.进行跨越多个表进行变更时
# Event
mysql5.1版本开始引进event概念，event既“时间触发器，与triggers的事件触发不同，event类似与linux crontab计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。
适用范围: 对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用event来处理。例如：使用event在每月的1日凌晨1点自动创建下个月需要使用的三张表。每天清除数据表中的过期的记录。
使用权限：单独使用event调用SQL语句时，查看和创建需要用户具有event权限，调用该SQL语句时，需要用户具有执行该SQL的权限。Event权限的设置保存在mysql.user表和mysql.db表的Event_priv字段中。
查看是否开启：SHOW VARIABLES LIKE 'event_scheduler';
CREATE EVENT 的语法如下：
CREATE EVENT
[IF NOT EXISTS] ---------------------------------------------*标注1
event_name -----------------------------------------------------*标注2
ON SCHEDULE schedule ------------------------------------*标注3
[ON COMPLETION [NOT] PRESERVE] -----------------*标注4
[ENABLE | DISABLE] ----------------------------------------*标注5
[COMMENT 'comment'] --------------------------------------*标注6
DO sql_statement -----------------------------------------------*标注7
标注1：[IF NOT EXISTS]
使用IF NOT EXISTS，只有在同名event不存在时才创建，否则忽略。建议不使用以保证event创建成功。
标注2：event_name
名称最大长度可以是64个字节。名字必须是当前Dateabase中唯一的，同一个数据库不能有同名的event。
标注3：ON SCHEDULE
ON SCHEDULE 计划任务，有两种设定计划任务的方式：
- AT 时间戳，用来完成单次的计划任务。
- EVERY 时间（单位）的数量时间单位[STARTS 时间戳] [ENDS时间戳]，用来完成重复的计划任务。
在两种计划任务中，时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。
在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。
标注4： [ON COMPLETION [NOT] PRESERVE]
ON COMPLETION参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。
标注5：[ENABLE | DISABLE]
参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。
可以用如下命令关闭或开启事件：
ALTER EVENT event_name ENABLE/DISABLE
标注6：[COMMENT 'comment']
注释会出现在元数据中，它存储在information_schema表的COMMENT列，最大长度为64个字节。'comment'表示将注释内容放在单引号之间，建议使用注释以表达更全面的信息。
标注 7: DO sql_statement
DO sql_statement字段表示该event需要执行的SQL语句或存储过程。这里的SQL语句可以是复合语句，例如：
BEGIN
CREATE TABLE test1;//创建表（需要测试一下）
DROP TABLE test2;//删除表
CALL proc_test1();//调用存储过程
END
使用BEGIN和END标识符将复合SQL语句按照执行顺序放在之间。当然SQL语句是有限制的，对它的限制跟函数Function和触发器Trigger 中对SQL语句的限制是一样的，如果你在函数Function 和触发器Trigger 中不能使用某些SQL，同样的在EVENT中也不能使用。明确的来说有下面几个：
LOCK TABLES
UNLOCK TABLES
CREATE EVENT
ALTER EVENT
LOAD DATA
4.3 执行逻辑
For (已建立事件each event that has been created)
If (事件的状态非DISABLE)
And (当前时间在ENDS时间之前)
And (当前时间在STARTS时间之后)
And (在上次执行后经过的时间)
And (没有被执行)
Then:
建立一个新的线程
传递事件的SQL语句给新的线程
(该线程在执行完毕后会自动关闭)
4.4 修改事件
使用ALTER EVENT 来修改事件，具体的ALTER语法如下，与创建事件的语法类似：
ALTER EVENT
event_name
ON SCHEDULE schedule
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement
4.5 删除事件
EVENT使用DROP EVENT语句来删除已经创建的事件，语法如下：
DROP EVENT
[IF EXISTS]
event_name
但当一个事件正在运行中时，删除该事件不会导致事件停止，事件会执行到完毕为止。使用DROP USER和DROP DATABASE 语句同时会将包含其中的事件删除。
例子：
DROP EVENT IF EXISTS `EvtCheckPartitionTask`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `EvtCheckPartitionTask` ON SCHEDULE EVERY 1 DAY STARTS '2016-05-01 03:33:33' ON COMPLETION PRESERVE ENABLE DO call CheckPartition()
;;
DELIMITER ;# 主从环境
https://blog.csdn.net/Virgil_K2017/article/details/90382201
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。 MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
实现MySQL主从复制需要进行的配置：
主服务器：
开启二进制日志
配置唯一的server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号
从服务器：
配置唯一的server-id
使用master分配的用户账号读取master二进制日志
启用slave服务
网址：SQL WHERE 子句
RESET MASTER
官方解释：
Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file.
删除所有的二进制日志，并重新创建一个新的二进制日志
功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作；
【一】RESET MASTER参数
注意reset master 不同于purge binary log的两处地方
1. reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值，使用PURGE BINARY LOGS语句删除binlog没多大影响（前提是，删除的binlog中的events已经传输到slave上）
2. reset master 不能用于有任何slave 正在运行的主从关系的主库。因为在slave 运行时刻 reset master 命令不被支持，reset master 将master 的binlog从000001 开始记录,slave 记录的master log 则是reset master 时主库的最新的binlog,从库会报错无法找的指定的binlog文件。
注：当数据库要清理binlog文件的时候，可以通过操作系统进行删除，也可以运行reset master进行删除。但是如果当前是主数据库，且主从数据库正常的时候，千万不能用这种方式删除。
【使用场景】第一次搭建主从数据库时，用于主库的初始化binglog操作
【二】RESET SLAVE
功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件；
reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。
使用reset slave之前必须使用stop slave 命令将复制进程停止。
但是从库上内存中的change master信息并没有删除，此时，可直接执行start slave，但因为删除了master.info和relay-log.info，它会从头开始接受主的binlog并应用。
使用场景：当原来的主从关系被破坏之后，从库经过重新初始化后直接连接会报 ERROR 1201的错误，运行reset slave后，重新配置主从连接就可以了；
【三】RESET SLAVE ALL
相对于RESET SLAVE，RESET SLAVE ALL还会删除内存中的连接信息，这个时候，执行start slave会报错。
总结：如果是需要删除mysql binlog和relaylog文件的时候，那么通过操作系统的删除或者PURGE命令都可以，但是涉及到mysql主从配置的时候便需要使用RESET MASTER和RESET SLAVE解决问题
# 事务相关
1. 什么是事务?
理解什么是事务最经典的就是转账的栗子,相信大家也都了解,这里就不再说一边了.
事务是一系列的操作,他们要符合ACID特性.最常见的理解就是:事务中的操作要么全部成功,要么全部失败.但是只是这样还不够的。
ACID是什么?
A=Atomicity
原子性,就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作.
C=Consistency
系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态.
I=Isolation
隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.有例外情况.
D=Durability
持久性,一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果
3. 同时有多个事务在进行会怎么样呢?
多事务的并发进行一般会造成以下几个问题:
-
脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
-
不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.
-
幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉
怎么解决这些问题呢?
MySQL的四种隔离级别如下:
-
未提交读(READ UNCOMMITTED)这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).
-
已提交读(READ COMMITTED)其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.
-
REPEATABLE READ(可重复读）可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是 幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.（默认级别）
-
SERIALIZABLE(可串行化)这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所有的操作串行执行,这会导致并发性能极速下降.
MySQL都有哪些锁呢
从锁的类别上来讲,有共享锁和排他锁.
共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.
排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.
InnoDB实现了行级锁,页级锁,表级锁.他们的加锁开销从大大小,并发能力也是从大到小.
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁，mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型 。
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能.
比如:
在上面的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下例所示：
创建tab_with_index表，id字段有普通索引：
mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
mysql> alter table tab_with_index add index id(id);
# 三个范式
第一范式: 每个列都不可以再拆分. 第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分. 第三范式: 非主键列只依赖于主键,不依赖于其他非主键.
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
