Mysql数据库总结

基本语句
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password'；‘localhost’允许从本机登陆 ，‘%’允许从远程登陆
DROP USER ‘user_name’@‘host’
SET PASSWORD FOR ‘username’@‘host’ = PASSWORD(‘newpassword’)；
GRANT privileges ON databasename.tablename TO ‘username’@‘host’ privileges：表示要授予什么权力，例如可以有 select ， insert ，delete，update等，如果要授予全部权力，则填 ALL
REVOKE privileges ON database.tablename FROM ‘username’@‘host’；
CREATE DATABASE 数据库名;
DROP DATABASE 数据库名;
USE 数据库名;
CREATE TABLE table_name (column_name column_type);
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M] OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
DELETE FROM table_name [WHERE Clause]
ALTER TABLE table_name COLUMN column_name INTEGER NOT NULL;
ALTER TABLE table_name DROP COLUMN column_name ;

表类型/存储引擎
MySQL为用户提供5种不同的表类型，称为BDB、HEAP、ISAM、MERGE、MyIASM和InnoDB。DBD归为事务安全类，而其他为非事务安全类型
Berkeley DB(BDB)表是支持事务处理的表，由Sleepycat软件公司(http://www.sleepycat.com)开发。它提供MySQL用户期待已久的功能-事务控制。事务控制在任何数据库系统中都是一个极有价值的功能，因为它们确保一组命令能成功地执行。
HEAP表是内存表，MySQL中存取数据最快的表。这是因为他们使用存储在动态内存中的一个哈希索引。另一个要点是如果MySQL或服务器崩溃，数据将丢失。
ISAM表是早期MySQL版本的缺省表类型，直到MyIASM开发出来。建议不要再使用它。
MyISAM是MySQL的缺省表类型。它基于IASM代码，但有很多有用的扩展。MyIASM表小于IASM表，所以使用较少资源，在不同的平台上二进制层可移植，更大的键码尺寸，更大的键码上限。
MERGE是一个有趣的新类型，在3.23.25之后出现。一个MERGE表实际上是一个相同MyISAM表的集合，合并成一个表，主要是为了效率原因。这样可以提高速度、搜索效率、修复效率并节省磁盘空间。
InnoDB是默认的事务型存储引擎，也是最重要，使用最广泛的存储引擎。在没有特殊情况下，一般优先使用InnoDB存储引擎。具有四个特性：插入缓冲(insert buffer)；二次写(double write)；自适应哈希索引(ahi)；预读(read ahead)

范式
第一范式（1NF）数据表的每一列都要保持它的原子特性，也就是列不能再被分割。
只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息
第二范式（2NF）属性必须完全依赖于主键。
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的,第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要我们设计一个主键来实现(这里的主键不包含业务逻辑)。即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况
第三范式（3NF） 所有的非主属性不依赖于其他的非主属性
满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。很多时候，我们为了满足第三范式往往会把一张表分成多张表

列的字符串类型
SET
BLOB
ENUM
CHAR
TEXT
VARCHAR
char和varchar都是用来存储字符串的，但是他们保持和检索的方式不同。char是属于固定长度的字符类型，而varchar是属于可变长度的字符类型。由于char是固定长度的所以它的处理速度比varchar快很多。但是缺点是浪费存储空间，读取char类型数据时候时如果尾部有空格会丢失空格，所以对于那种长度变化不大的并且对查询速度有较高要求的数据可以考虑使用char类型来存储。
BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。

连接
INNER JOIN（内连接/等值连接/自然连接）：获取两个表中字段匹配关系的记录。 SELECT * FROM table1_name t1 INNER JOIN table2_t2 name on t1.column=t2.column;
LEFT JOIN（左外连接）：获取左表所有记录，若右表没有对应匹配的记录用NULL填充。
RIGHT JOIN（右外连接）：用于获取右表所有记录，若左表没有对应匹配的记录用NULL填充。
交叉连接 返回两个表的笛卡尔积

空值
= 和 != 运算符是不起作用的，必须使用 IS NULL 和 IS NOT NULL

处理重复数据
防止表中出现重复数据：在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。
过滤重复数据：使用 DISTINCT 关键字 SELECT DISTINCT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M]
删除重复数据：创建新表，用过滤查询获取不重复的数据写入新表，删除旧表，新表更名为旧表名

备份
冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；mysqldump 常用来做温备，所以我们首先需要对想备份的数据施加读锁，
mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_date +%F.sql
--single-transaction: 基于此选项能实现热备InnoDB表；因此，不需要同时使用--lock-all-tables；
--master-data=2 记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的
--databases hellodb 指定备份的数据库
mysql < /backup/hellodb_2013-09-08.sql
热备（hot backup）：备份的同时，业务不受影响。
完全备份：full backup，备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。多个备份间不会数据重复，恢复时需要完全备份和全部增量备份链。
差异备份：differential backup 上次完全备份以来改变了的数据。备份大小持续增长，多个备份间数据重复.恢复时只需要基础备份.
SELECT table INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
LOAD DATA INFILE '/tmp/result.txt' INTO TABLE table ;

视图
优点
简单化，数据所见即所得
安全性，用户只能查询或修改他们所能见到得到的数据
逻辑独立性，可以屏蔽真实表结构变化带来的影响
缺点
性能相对较差，简单的查询也会变得稍显复杂
修改不方便，特变是复杂的聚合视图基本无法修改
{ CREATE|REPLACE } [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [{column_list}] AS SELECT_STATEMENT [WITH [CASCADED | LOCAL |] CHECK OPTION]
CREATE|REPLACE ： 【创建|替换已创建的】视图
ALGORITHM ： 视图算法
1.UNDEFINED 系统自动选择算法
2.MERGE使用的视图语句与视图定义合并起来
3.TEMPTABLE 结果存入临时表，然后用临时表执行语句
view_name ： 视图名称
column_list ： 属性列
SELECT_STATEMENT ：SELECT语句
[WITH [CASCADED | LOCAL |] CHECK OPTION] 表示视图在更新时保证在视图的权限范围内
1.CASCADED 默认值 更新视图时要满足所有相关视图和表的条件，
2.LOCAL表示更新视图时满足该视图本身定义的条件即可

事务
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务是必须满足4个条件ACID：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
ISOLATION_READ_UNCOMMITTED：读未提交数据，这是事务最低的隔离级别，在并发的事务中，它允许一个事务可以读到另一个事务未提交的更新数据。（会出现脏读，不可重复读和幻读）
ISOLATION_READ_COMMITTED：读已提交数据，保证在并发的事务中，一个事务修改的数据提交后才能被另外一个事务读取到。（会出现不可重复读和幻读）
ISOLATION_REPEATABLE_READ：可重复读，这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻读。一般是采用“快照”的方式来实现的。
ISOLATION_SERIALIZABLE：事务被处理为顺序执行。这是花费最高，但也是最可靠的事务隔离级别。能有效的避免脏读、不可重复读、幻读
脏读：一个事务读取到另一事务未提交的更新新据。当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据， 那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作也可能是不正确的。
不可重复读：在同一事务中，多次读取同一数据返回的结果有所不同。换句话说就是，后续读取可以读到另一事务已提交的更新数据。相反，“可重复读”在同一事务中多次读取数据时，能够保证所读数据一样，也就是，后续读取不能读到另一事务已提交的更新数据。
幻读：事务T1执行一次查询，然后事务T2新插入一行记录，这行记录恰好可以满足T1所使用的查询的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

存储过程
存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
delimiter 将语句的结束符号从分号;临时改为两个//(可以是自定义)
delimiter //
create procedure out_param2(in p_in int,out p_out int)
begin
select p_out;
set p_out=p_in+1;
select p_out;
end
//
delimiter ;
set @p_in=1;
call out_param2(@p_in, @p_out);

分页
limit 起始行，显示数量
数据少时 select * from table limit (pageNo-1)pageSize, pageSize;
数据量大时 '''SELECT * FROM table WHERE id <= (SELECT id FROM table ORDER BY id descLIMIT (pageNo-1)pageSize,1) ORDER BY id asc LIMIT pageSize''';

索引
索引是存储引擎快速找到记录的一种数据结构.任何标准表最多可以创建16个索引列
索引类型
创建索引时如果是blob 和 text 类型，必须指定length
普通索引 CREATE INDEX indexName ON table(column);
主键索引 一般是在建表的时候同时创建主键索引
唯一索引 CREATE UNIQUE INDEX indexName ON table(column);
组合索引 CREATE INDEX indexName ON table(column1, column2, column3);
全文索引 ALTER TABLE table ADD FULLTEXT (column); 作用于CHAR、VARCHAR、TEXT数据类型的列。
SHOW INDEX FROM table;
DROP INDEX indexName ON table;

Mysql缓存
Mysql缓存机制就是缓存sql文本及缓存结果，用KV形式保存在服务器内存中，如果运行相同的sql,服务器直接从缓存中去获取结果，不需要在再去解析、优化、执行sql。如果这个表修改了，那么使用这个表中的所有缓存将不再有效，查询缓存值得相关条目将被清空。表中得任何改变是值表中任何数据或者是结构的改变，包括insert,update,delete,truncate,alter table,drop table或者是drop database 包括那些映射到改变了的表的使用merge表的查询，对于频繁更新的表，查询缓存不合适，对于一些不变的数据且有大量相同sql查询的表，查询缓存会节省很大的性能。
命中条件
缓存存在一个hash表中，通过查询SQL，查询数据库，客户端协议等作为key,在判断命中前，mysql不会解析SQL，而是使用SQL去查询缓存，SQL上的任何字符的不同，如空格，注释，都会导致缓存不命中。如果查询有不确定的数据like now(),current_date()，那么查询完成后结果者不会被缓存，包含不确定的数的不会放置到缓存中。
工作流程
1.服务器接收SQL，以SQL和一些其他条件为key查找缓存表
2.如果找到了缓存，则直接返回缓存
3.如果没有找到缓存，则执行SQL查询，包括原来的SQL解析，优化等。
4.执行完SQL查询结果以后，将SQL查询结果缓存入缓存表
缓存失败
当某个表正在写入数据，则这个表的缓存（命中缓存，缓存写入等）将会处于失效状态，在Innodb中，如果某个事务修改了这张表，则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存
缓存参数配置
query_cache_type: 是否打开缓存
可选项

1. OFF: 关闭
2. ON: 总是打开
3. DEMAND: 只有明确写了SQL_CACHE的查询才会吸入缓存
   query_cache_size: 缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同(感觉这个应该跟文件系统的blcok大小有关)
   query_cache_min_res_unit: 分配内存块时的最小单位大小
   query_cache_limit: MySQL能够缓存的最大结果,如果超出,则增加 Qcache_not_cached的值,并删除查询结果
   query_cache_wlock_invalidate: 如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回
   select SQL_CACHE column from table;
   select SQL_NO_CACHE column from table;

八种锁
行锁（Record Locks）
间隙锁（Gap Locks）
临键锁（Next-key Locks）
共享锁/排他锁（Shared and Exclusive Locks）
意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）
插入意向锁（Insert Intention Locks）
自增锁（Auto-inc Locks）
预测锁

函数
CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串 合并多个字符串
LOWER(s) 将字符串 s 的所有字母变成小写字母
REPEAT(s,n) 将字符串 s 重复 n 次
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s) 将字符串s的顺序反过来
ABS(x) 返回 x 的绝对值　　
AVG(expression) 返回一个表达式的平均值，expression 是一个字段
COUNT(expression) 返回查询的记录总数，expression 参数是一个字段或者 * 号
MAX(expression) 返回字段 expression 中的最大值
MIN(expression) 返回字段 expression 中的最小值
CURDATE() 返回当前日期
CURRENT_TIME 返回当前时间

参考:
MySQL大数据量分页查询方法及其优化 https://www.cnblogs.com/geningchao/p/6649907.html
MySQL——索引基础 https://www.cnblogs.com/songwenjie/p/9410009.html
mysql 缓存机制 https://blog.csdn.net/qzqanzc/article/details/80418125
MySQL视图篇，视图的优缺点以及如何创建视图 https://baijiahao.baidu.com/s?id=1623365360611120859&wfr=spider&for=pc
mysql事务隔离级别及传播机制 https://www.cnblogs.com/xxoome/p/10081741.html
MySQL常见的七种锁详细介绍 https://blog.csdn.net/Saintyyu/article/details/91269087
MySQL 函数 https://www.runoob.com/mysql/mysql-functions.html
MySQL三大范式 https://www.cnblogs.com/gongcheng-/p/10901824.html