mysql总结

@[toc]

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• 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系统。

#创建表@[toc]

创建数据库：create database xx或者create database if not exists test2 (default) character set utf8; 

修改数据库：alter database  character set utf8;

删除数据库：drop database test;或者drop database if exists test;

#创建和查看数据库表@[toc]

create table reader(card_id char(18),name varchar(10),sex enum('男','女'),age tinyint,tel char(11),balance decimal(7,3)) ;

查看表结构

show columns from xx; 或者 desc xx;

查看创建表的语句

show create table xx;

表增加新的列

约束条件可选，first和after表示定位插入列位置。

alter table reader add email varchar(30) after tel;

修改表名

alter table reader rename to readerinfo;

修改表的列名：

alter table reader change email2 email_bak varchar(30)

修改表数据类型

alter table reader modify email_bak varchar(25);

修改表中列的相对位置

alter table reader modify balance decimal(7,3) after email;

删除的表的列

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的， 也就是需要为主键。

#约束@[toc]

约束是一种限制，它通过对表的行为或列的数据做出限制，来确保表的数据的完整性，唯一性。

主键约束：准确定义到一行数据。唯一约束：一张表中指定的一列的值不能重复，每个值唯一。默认约束：插入数据没有为字段赋值时，会自动赋予字段值。外键约束：在两个表的数据之间建立链接，通过外键约束保证数据的完整性一致性。

非空约束：

如果指定字段可以为NULL，则MySQL为其设置默认值为NULL。如果是NOT NULL字段，MySQL对于数值类型插入0，字符串类型插入空字符串，时间戳类型插入当前日期和时间，ENUM类型插入枚举组的第一条。

主键约束

主键约束要求主键的列的数据唯一。并且不许为空，主键能够唯一的标识表中的一条记录。下面constraint pk_id 可以省略。省略了系统会给默认的约束名。

AUTO_INCREMENT 修饰符：AUTO_INCREMENT修饰符只适用于INT字段，表明MySQL应该自动为该字段生成一个数(每次在上一次生成的数值上面加1)。对于主键，这是非常有用的。因为其允许开发者使用MySQL为每条记录创建唯一的标识符。字段后面跟 COMMENT=‘表注释’，如`id` int(10) not null auto_increment COMMENT=‘表注释’,   id再脚本里被反引号包围。

单字段主键

多字段联合主键：复合主键只能在定义表的时候定义。不能后期加。

删除主键

添加主键

唯一约束

删除唯一约束

KEY wh_logrecord_user_name (user_name)  含义 ：user_name字段 创建一个约束名为wh_logrecord_user_name 的key;

给约束取名字,   目的是将来你可以比较容易的删除掉.否则你要自己去查询数据字典表.

key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）；

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+树结构存储数据，大大加快了数据的查询速度。

主键约束和唯一约束的却别：

默认约束

外键约束：

外键所在的表叫从表（子表），索引主键所在的表叫主表（父表），先有主表再有从表。两个表的对应的字段的字符长度可以不同，但是类型要相同。

删除外键约束

如果创建的时候加上后面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;

#插入，更新，删除数据@[toc]

插入数据时如果给所有字段赋值就用

insert into bookinfo values(值1, 值2,....);

或

insert into bookinfo (book_id，field2)values(2，value2);

删除数据

DELETE FROM 表名称 WHERE 列名称 = 值

更新数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

#数据类型@[toc]

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代表字符的长度，一个字节存储一个汉字。Varchar：可变字符。

#表分区@[toc]

https://www.cnblogs.com/zhouguowei/p/9360136.html

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';

可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

如何判断当前MySQL是否支持分区

show variables like '%partition%';

rang分区：

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配置文件@[toc]

https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#option_mysqld_log-bin

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不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。在主从环境的时候可能会有二进制日志。

#命令@[toc]

登录

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 [e-mail] identified by ''; //192.168.0.80 设置对应ip的tomcat的select用户不用密码 ` 

grant  all on *.* to [e-mail] 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 [e-mail] identified by '';  grant  select on *.* to [e-mail] identified by '';

回收权限 revoke insert,select on *.* from [e-mail];

查询

1.查询表有多少数据 select count(表中任意属性名) from 表名;   COUNT()函数计算行数

2.select 查询 where ，LIMIT， group by， AS，order by，   select语句 where 放在表名后面 ，其次是groupby 然后是order by（放在前面需要嵌套查询），

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;

或者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(等值连接) 只返回两个表中联结字段相等的行

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 …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。

#在使用LOAD DATA到MySQL的时候，有2种情况：@[toc]

（1）在远程客户端（需要添加选项：--local-infile=1）导入远程客户端文本到MySQL，需指定LOCAL（默认就是ignore）,加ignore选项会放弃数据，加replace选项会更新数据，都不会出现唯一性约束问题。

       local_infile服务器变量指示能否使用load data local infile命令（在远程客户端导入远程客户端文本）--local-infile=0禁用LOCAL 功能 1 为开启

       sqlfile="activetiflow.sql" ${line} 为ip

       ret=`ssh [e-mail]${line} "mysql -utomcat --local-infile=1 <${sqlfile}"`

       mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1  （先登录指定--local-infile=1） 

       LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','              

       FIELDS TERMINATED BY ','  的意思为使用 ',' 分隔

       LOAD DATA LOCAL INFILE '/tmp/2.txt' IGNORE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

       LOAD DATA LOCAL INFILE '/tmp/2.txt' REPLACE INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','

       错误实例：

       LOAD DATA INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

       ERROR 13 (HY000): Can't get stat of '/tmp/2.txt' (Errcode: 2) #由于数据库服务器没有对应的文本文件，所以报错。

       LOAD DATA LOCALINFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

       ERROR 1148 (42000): The used command is not allowed with this MySQL version #进去mysql远程客户端，还需要加--local-infile=1参数指定

（2）在本地服务器导入本地服务器文本到MySQL，不指定LOACL，出现唯一性约束冲突，会失败回滚，数据导入不进去，这个时候就需要加ignore或者replace来导入数据。

       LOAD DATA INFILE '/home/zhuxu/1.txt' IGNOREINTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';  使用IGNORE对于冲突的数据丢弃掉

       LOAD DATA INFILE '/home/zhuxu/1.txt' REPLACEINTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';  使用REPLACE对于冲突的数据进行更新

       错误实例：

       LOAD DATA INFILE '/home/zhuxu/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';

       ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' #出现唯一性约束冲突，会失败回滚

#导入和导出数据库@[toc]

mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据。如：将cdkeys数据库整体导入cdkeys.txt文件，需要有cdkeys.txt文件的权限。

mysqldump -u root -p cdkeys > /data/mysql-files/cdkeys.txt

或者将cdkeys的giftinfo 表导入txt中

mysqldump -u root -p cdkeys giftinfo > /data/mysql-files/cdkeys.txt

如果需要备份所有数据库，可以使用以下命令，--all-databases 选项在 MySQL 3.23.12 及以后版本加入

mysqldump -u root -p --all-databases > database_dump.txt

如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建。

mysql -uroot -p accountgifts < accountgifts.txt

如果你需要将远程服务器的数据拷贝到本地，你也可以在 mysqldump 命令中指定远程服务器的IP、端口及数据库名。请确保两台服务器是相通的.在源主机上执行以下命令，将数据备份到 dump.txt 文件中：

mysqldump -h IP -P port -u root -p database_name > dump.txt

password ****

MySQL 5.0.51中包含的mysqldump命令（根据自4.1.1以来的更改日志版本）确实关闭了外键检查。默认情况下，mysqldump在转储文件的顶部包含以下行：

/*!40014 SET @[e-mail]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

该/*!40014 ... */语法是有条件的评论将在MySQL 4.0.14和以后执行。旧的外键检查设置将在转储文件的末尾恢复：

/*!40014 SET [e-mail]_FOREIGN_KEY_CHECKS */;

或者另一种操作：

导入批量数据 可以先 copy到一个txt文件里 然后awk ‘{printf xxx}’> xx.sql 重定向到一个文件 然后再sql文件里加参数 再执行即可。

use accountgifts; 使用数据库

SET collation_connection = utf8_bin;  collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的 校对规则优先级

// utf8_bin 是将字符串每个字符串用二进制数据编译存储

SET character_set_client = utf8;  客户端使用的编码，如GBK, UTF8 比如你写的sql语句是什么编码的    //还有一个是 character_set_results 查询返回的结果集的编码(从数据库读取的数据是什么编码的)。

SET character_set_connection = utf8;  连接使用的编码

SET autocommit=0; //将autocommit设置为OFF之后，系统默认开始了事务，但是并没有默认帮你提交了事务，因此如果我们在客户端执行insert之后需要提交事务：

INSERT INTO accountinfo(bid, uid, arg) VALUES(0, 25241070, 370);

或者 INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21), (225, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);

SET AUTOCOMMIT=1;//打开自动提交。这样中间的所有操作算作一个事务，如果有一个没成功 不会进行提交操作。

#View（视图）@[toc]

视图（view）是一种虚拟的表，并不在数据库中实际存在。通俗的来说，视图就是执行select语句后返回的结果，对于数据库的用户来说，很多时候，需要的关键信息是来自多张复杂关联表的。这时用户就不得不使用十分复杂的SQL语句进行查询，给用户造成极差的体验感。使用视图之后，可以极大的简化操作，使用视图的用户不需要关心相应表的结构、关联条件等。对于用户来说，视图就是他们想要查询的结果集。使用视图的用户只能访问被允许查询的结果，使用视图可以限制用户访问一些敏感信息列。

视图的修改会体现在原表中，但是下面4种情况不可以修改，会报错。

1.定义视图包含统计函数的情况下。

2.视图定义时使用了group by 。having，disitinct，union语句时

3.定义视图时包含了子查询语句。

4.进行跨越多个表进行变更时

#[e-mail][toc]

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 计划任务，有两种设定计划任务的方式：

1. AT 时间戳，用来完成单次的计划任务。
2. 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 ;

#主从环境@[toc]

https://blog.****.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服务

网址：https://www.w3school.com.cn/sql/sql_where.asp

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解决问题