mysql

MySQL是多用户多线程的DBMS，默认支持的存储机制是 InnoDB，InnoDB 通过建立行级锁保证事务的完整性，通过共享锁处理 select 语句，以提供事务安全的存储机制。
引擎管理和处理数据，MySQL支持多种引擎：

• InnoDB ：提供可靠的事务处理，但不支持全文本搜索。
• MyISAM：性能高、支持全文本搜索，但不支持事务处理。

工具的安装和使用

命令行应用程序安装教程：mysql-5.7.15-winx64.zip， 安装教程，常用指令

net start/stop mysql　　// 登录/退出mysql
mysql [-h 主机名] -u 用户名 -p  (mysql@sqh)
create user 用户名 identified by '密码'； // 创建用户

图形化交互界面工具：mysql-workbench 或 Navicat for MySQL 或 dbvaver

基本操作

show databases：数据库实例包含的数据库
use/select 数据库名：选择/查看数据库信息
show tables：数据库中包含的数据表
show columns from 表名：表列信息
analyze table 表名; 检查表键是否正确
optimize table 表名; 优化表空间
show processlist；显示活动的线程

关于授权

grant|revoke all privileges on *.* to 'username'@'%' identified by 'pwd' with grant option //授权|回收
flush privileges //刷新权限
show grants for 'username'@'localhost' //查看用户权限

编码信息

SHOW CREATE DATABASE db_name //查看数据库编码
SHOW CREATE TABLE tb_name //查看表编码
SHOW FULL COLUMNS FROM tb_name //查看字段编码
//修改编码
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE tbl_name CHANGE 'column_name' 'column_name' 类型 CHARACTER SET utf8 COLLATE utf8_general_ci
//把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name CHARACTER SET utf8 COLLATE utf8_general_ci 

参见：基础概念

关键字

insert：MySql支持在单个insert语句中插入多个values值，且效率高。
where - having：where 子句过滤行，having 子句过滤组；只能在 having 子句中使用组函数(聚合函数)；
or - in：in 优于 or，速度快，可以包含select子句，与not操作符结合简化SQL语句；
like - regexp：like利用通配符*和_进行字符串匹配，完全匹配列值；regexp，正则表达式，部分匹配列值，利用^xxx$可完全匹配列值，效果等效于like，其中^和$是定位符分别对应首和尾。binary regexp可区分大小写
limit：限制选取的行数，MySQL-5新增 limit ... offset ... 语法；
auto_increment：自动增加，每个表只允许有一列且必须被索引。返回值：（1）last _ insert _ id()； （2）after insert 触发器：

varchar 和 char

• char定长，varchar变长
• char限制255字符，与编码无关，varchar限制65532字符，与编码有关
  Latin1、GBK、UTF8的一个字符分别占用1、2、3字节，相应字符限制65532、32766、21844个

CHARACTER SET utf8 COLLATE utf8_general_ci

• CHARACTER SET：设置数据库/表的字符集编码
• COLLATE：数据库/表校对规则

COLLATE utf8_bin：字符串的每个字符用二进制数据存储，区分大小写
COLLATE utf8_general_ci/cs：表示不/区分大小写

若建表时选择区别大小写的规则，查询时又不想区别，可以用类似WHERE col_name COLLATE utf8_general_ci='xxx'方式改变查询时的校对规则。新建数据库时一般选用utf8_general_ci即可。mysql编码规则

primary key 和 unique

ON UPDATE CURRENT_TIMESTAMP
当前记录有字段更新，即更新该字段为当前时间

time1 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP #自动更新
time2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP (ON UPDATE CURRENT_TIMESTAMP) #自动更新
time3 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP #创建字段更新，后续不自动更新

参考1 参考2

函数

//系统函数
select Now()/Date()/Time();   //时间日期
select Version();   //版本信息
//处理函数
Concat()：字符串拼接
Substring()；截取子串
Locate()：查找子串

多表查询

交叉连接 cross join：笛卡尔乘积，无连接条件；
自然连接 natural join：以 2 表中的所有同名列为连接条件，内部连接的一种

• using 子句连接：显式指定某些同名列为连接条件

左、右、全外连接 left/right/full join：

• on 子句连接：常用连接方式

约束

使用 auto_increment 支持自增长特性，支持除 check 外的 4 种完整性约束：

not null：非空约束
unique：唯一约束
primary key：主键约束
foreign key：外键约束，参照完整性

级联删除：删除主表记录时，关联的从表记录也删除，则需要在建立外键约束的后面增加 on delete cascade 或 on delete set null，前者是级联删除，后者是将从表的关联列的值设置为null。
具体使用可参见：MySQL-约束

索引

模式Schema中的一个数据库对象，从属于数据表。通过快速路径访问方法快速定位数据、加快对表的查询。缺点是索引的存储占用空间、索引的维护有系统开销。

视图

一个或多个数据表中记录的逻辑显示。通过 with check option 子句强制不允许修改视图的数据。

• 提供数据的独立性
• 简化查询
• 限制对数据的访问，提供对相同数据的不同显示

视图的本质是一条被命名的SQL查询语句。利用子查询建立视图，同样可以建立表：

create or replace view 视图名 as 子查询语句
查看视图创建语句：show create view 视图名；
更新视图：create or replace view 视图名；

存储过程

以call调用存储过程。

查看存储过程创建语句：show create procedure 存储过程名；
查看存储过程附加信息：show procedure status like '存储过程名'；

游标

MySql游标只能用于存储过程和函数。
内部定义顺序：局部变量，游标，句柄

触发器

trigger只能关联表，常用的NEW(可更新、可读)表和OLD(只读)表是针对当前触发器的局部表，在高速缓存中存储新插入或删除的行数据的副本。创建：

create trigger 触发器名 after/before insert/delete/update on 表名
for each row 
begin 
  SQL处理语句；
end;

其中，before用于数据验证，保证操作的合法性和待操作数据的正确性。

事务

以start transaction标识事务开始。

全文本搜索

MySql要求被检索的列在创建表时必须加索引，即利用 fulltext(列名) 启用全文本搜索，会自动维护更新该索引，通过 Match(列名) Against('模式串') 执行全文本搜索。

• fulltext速度比like快；
• 明确控制文本匹配，对检索结果按等级值智能排序

查询扩展：模糊匹配，MySql对数据和索引进行2遍扫描完成搜索。 Match(列名) Against('模式串' with query expansion)
布尔文本搜索：无需定义fulltext索引，利用全文本布尔操作符明确控制文本匹配模式，效率低。 Match(列名) Against('模式串' in boolean mode)

问题总结

生产mysql数据统计执行超时问题
在执行sql统计数据时，遇到60s超时问题，报错：Fatal error encountered during data read

"server=ip;user id=username;password='pwd';database=face_his;port=3306;CharSet=utf8;default command timeout=2700" 

需要在连接串中配置执行时间default command timeout，单位秒：参考
至于java中配置连接串超时时间，通常connectTimeout=1000&socketTimeout=60000，详细使用待学习。