这是一步步从头学习MySQL的笔记历程，本人在学习之前只接触过少量SQL Server。
第一天历程：压榨自己的学习能力极限，每天学习新内容之MySQL服务器！（一）
第二天历程：压榨自己的学习能力极限，每天学习新内容之MySQL服务器！（二）
接下来每天都将更新~相信你通过笔记，一定能从零学会MySQL~
废话不多说，开始今天的学习压榨~！
视图
如何创建视图：
格式：CREATE VIEWview_name [(视图的字段column_list)]
CREATE VIEW view_name AS SELECT clause;
视图保存的仅仅是语句本身，但是当视图创建之后，却可以像使用表一样使用它
创建视图的目的就是为了避免某些字段被某些用户查看的
假设我们有表：
比如：把那些CouseID不为空的创建成一个视图，并且没有Totur段。
CREATE VIEW xiake AS SELECT UID,Name,Age,Gender,CouseID FROMknight WHERE CouseID IS NOT NULL;
视图并不是真正的表，他没有保存数据，所以在处理数据的速度上，肯定是慢于真正的基表的。而当我们的基表改变之后，视图创建出来的虚表会立刻改变。
比如我们修改：
UPDATE knight SET CouseID=15WHERE UID=9
这时再去看刚才创建好的视图，发现它已经改变。
往视图中插入数据：
视图(虚表)是可以被插入数据的，但是，插入的所有数据其实是保存在基表之中的，而如果插入的这个数据，在基表中有值没有定义。则有可能报错甚至不让你创建。
WITH CHECK OPTION 在创建数据的时候，可以明确指定让基表中的约束应用到虚表中来。
删除视图：
DROP VIEWview_name
如何实现子查询：
我们SELECT实现了嵌套，我们的SELECT结果是从另一个SELECT的结果中得来的。
比如形式为：
SELECT * FROMSELECT * FROM Knight
SELECT * FROM xxx WHERE SELECT * FROMknight
UPDATE knight SETAge=49 WHERE UID=6
显示这个表中年龄大于所有有年龄人员的平均年龄的：
我们先查询平均年龄：SELECTAVG(Age) FROM knight WHERE Age IS NOT NULL;
发现，平均年龄是33岁
则我们可以使用子查询，一步解决。
SELECTName,Age FROM knight WHERE Age > (SELECT AVG(Age) FROM knight WHERE Age ISNOT NULL);
事物：Transact
什么是事物呢？几个步骤要么同时完成，要么同时都没完成。对于我们SQL来说，则是几个查询语句，要么同时都执行，要么同时都不执行，他们要做一个原子操作，是不可分的。
事物最典型的应用就是在银行里转账。比如A有2000，B有10000，B要转给A3000元。如果刚转走，钱还没到A的时候，银行停电了，这时A还没有加钱，但是B的钱已经减掉了……这时怎么办呢？为了免除这样的差错。事物就出现了。
而事物对于这样的结果处理的方式是：将这3000退回B，让其重新转账。但是对于大型数据库，里面的事物可能含有上万条，所以如果执行到最后出错了，那么回退就要重新完成这上万条的指令。为了避免回退的过长，还可以对事物做“快照”，而这样的“快照”，就叫做Save Point，事物的保存点。
MyISAM是不支持事物的的
支持事物的引擎：InnoDB
只有当一个引擎完全支持ACID的时候，我们才说这个引擎支持事物。
ACID：
A:Atomicity :原子性。事物是一体的。
C:Consistency：一致性。当事物完成以后，A减掉的B一定要加上，这种逻辑，必须是要一致的。体现的其实是结果的一致。
I:Isolation：隔离性：多个事物之间的联系，一个事物发生了改变之后，其他的事物能否发现这样的改变。当正在执行的事物，运行到一条命令发现原有数据库发生改变的时候，它是否接受改变。这取决于数据引擎的隔离级别。对于MySQL来讲，事物的隔离级别是很重要的属性。
D:Durability：持久性：一个事物完成之后，完成的结果要永久保存下来。
MySQL的隔离级别：(MySQL的默认事物隔离级别是3.可重读级别。)
1.读未提交：READUNCOMMITTED（一个事物完成的过程叫做提交的过程）。这种级别是最低的。
2.读提交：READCOMMITTED
3.可重读：REPEATABLE-READ
4.串行化：SERIABLIZABLE
查看默认级别：
SHOW VARIABLES LIKE ‘tx_isolation’则看出级别为 REPEATABLES-READ
隔离的级别越高，事物的安全性越好，但是并发性越差（所能同时执行的事物就越少）。如果事物对你不是很重要，可以降低事物的级别来增加事物的并发性。
四种级别的关系状态：
1.读未提交：假设A事物中有x=0,B事物在运行时要去读x，当5分钟的时候x被A改为了1，此时B立刻获取A=1,当10分钟的时候，A将x回退回了0,则B中的x立刻变为0
2.读提交：假设A事物中有x=0,B事物在运行时要去读x，当5分钟的时候x被A改为了1，此时B获取不到，所以x=0,当10分钟的时候，A事物运行结束，x=1，则B在运行的时候，立刻发现x=1并修改自己的x=1,15分钟的时候，B事物运行结束，提交之后，B中的x=1
3.可重读：无论A中的x如何改变，还是提交之后改变。B在事物的运行中都不管x如何改变，只负责将自己的最初读取的x值为事物的x值运行结束，之后，当提交结束之后，B将x重读为1。
4.串行化：只有一个事物彻底执行完成后，另一个事物才会开始的。当B事物运行的时候，发现A中的x发生了改变，则B不会开始自己事物的运行。直到A事物彻底执行完成。则B再读取A中最新的x值，开始执行。
如何实现两个事物在执行的时候，你执行你的，我执行我的，互相之间发生的数据改变对自己来说是不可见的。而这种类似的机制，我们就叫做多版本并发控制，MACC。
事物的引擎：
要想使用相应的事物，必须给予相应的数据引擎来决定。
事物是隐式的
START TRANSACTION:手动开启事物。开启之后，使用updata之类的命令，只有当你手动结束事物COMMIT的时候，才会保存到表中去。
比如： START TRANSACTION; 开启事物。
SELECT * FROM knight; 查看发现有13行
INSERTINTO knight SET Name=’Shi Zhongyu’,Age=37; 在事物中插入一行；
SELECT* FROM knight ;发现已经插入了一行变为了14行。
ROLLBACK; 回滚操作，直接退回插入前
SELECT* FROM knight; 则发现，又变成了13行
COMMIT; 结束事物，保存结果至表中。
查看事物级别：
SELECT @@SESSION.tx_isolation;
修改会话级别的事物级别：
SET SESSION TRANSACTIONISOLATION LEVEL READ UNCOMMITTED;
查看当前MySQL中的连接进程
SHOW PROCESSLIST
我们在两个事物中操作，去查看其关联样式。就能发现如同刚才叙述的级别效果一样，会产生那样的效果。
MySQL数据同步，锁
什么是数据同步？如果有两个进程或者两个应用需要访问同一个数据的话，这时候就要启用到同步概念。只要一牵扯到同步，就一定要用到锁的概念。锁：两个操作加之于同一个对象的时候如何避免讹误的。
假设现在有两个SQL语句，M正在插入knight，而N则查询knight表，这样，当N查询的时候，查询的其实可能只是一部分内容。那如何解决呢？则锁的机制决定了，一个人在做事的时候，另一个人不能对其操作。
锁是无时无刻不在的，只要有一个人读或者写，则就开始加锁。
锁的分类：
共享锁：读锁，大家都可以同时进行，读是可以允许别人也读的，但是读不允许别人写。
独占锁：写锁，写的时候不允许别人写。也不允许别人读。
而写锁的优先级是比较高的，但是你不能一直在写，过多的读锁是会造成写饥饿的，而过多的写锁也会造成读饥饿的~！
读和写的锁是互斥的，写和写的锁也是互斥的。只有读和读的锁才是共享的
SHOW ENGINE的时候里面就能看到引擎锁
为了保证备份的一致性，在你备份的时候是不能让别人写的。
锁的级别：
表锁，一锁就锁定了一张表（并发级别低）
行锁：一锁只锁定了几行（并发级别相对较高，但是可控制性要麻烦和复杂的多的多）
事物引擎大部分都是行级别的锁。而像MyISAM这种就用的是表级别的锁。
锁的实现：
服务器级别的实现：在服务器上无论你是哪种存储引擎我都加锁（表锁）
存储引擎级别的实现：大部分事务性引擎都可以实现行锁，有的数据库还能实现页锁（页就是内存的空间Page）。
小扩展：页（Page）
我们的内存是有虚拟内存空间的。对于任何一个32位系统的进程，都会认为自己有4G的内存可用。当我们只有512M的内存的时候，怎么办呢？我们只是把程序的需要使用的内存在内存上映射出一块，而实现映射的这个功能就是靠页（Page）来实现的。我们的内存就是按页，来储存的。
对表进行加锁
LOCKTABLES tbl_name READ|WRITE,
1.我们给knight加上一个读锁。
LOCK TABLESknight READ;，则到另一台终端上，发现虽然加锁，但依然可以读取。
2.我们给knight换成一个写锁。
LOCK TABLESknight WRITE;
此时，当我们没有给表写东西的时候，在另一台终端上能读，但是当我们在本地插入一个数据之后，数据表真正被锁，而另一台终端上就发现不能读取了，并且一直在等待中… 而当我们只要在本地一释放锁，则另一终端立刻显示读取的新信息。
对锁进行释放：
UNLOCK TABLES;
为了实现更好的并发性，我们可以实现降低粒度（解析度），来降低锁的级别/范围，这样并发性就会大大提高。
数据引擎：
存储引擎是表级别的概念，所以存储引擎通常也被称为表类型。有的引擎支持事物，有的存储引擎不支持，如果在一个数据库中使用多种含事物和非事物的引擎的话，势必将会对用户在事物的支持上产生麻烦。
常见的存储引擎：
MyISAM/Merge：不支持事物， 表级别的锁，不支持在线备份
InnoDB：支持事物，行级别的锁，支持在线备份（热备）。
MEMORY：内存事物存储引擎。只要存储在这个表中的数据都保存在内存中。内存引擎的主要目的，其实是来提供内存数据库的，不提供数据的持久化存储，所以内存引擎通常用于实现内存表（临时表）。所以MEMORY表也被称作堆表。
Maria：支持事物引擎，支持行级别的锁。当InnoDB被收购之后，那些原有开发InnoDB的人开发了Maria，仍处于开发中……
Falcon：支持事物，也支持热备，行级别的锁，但是需要Mysql 6.0
PBXT：一个商业化的引擎
FEDERATED：联合存储引擎，只是用来联合两个表的，本身并不存储什么数据
NDB：MySQL的集群当中才能够用到的引擎，将集群中的数据在内存中处理，构建这个集群需要5台以上的主机，性能非常好。
Archive：归档存储引擎，用于实现将数据“挖掘”(智能决策，决策支持)用的。很少在修改它们，对于这种很少用到修改的数据，我们可以将其压缩存放。这就是归档存储引擎。
Blackhole：黑洞引擎，类似于Linux中的/dev/null，你存进去的任何数据都会被引擎悄悄的丢弃了。在后面的复制日志，作为转发节点使用。
CSV：存储成文本文件的数据库，主要为了跟其他的文本处理工具进行兼容，也为了移植，但是本身处理效率不高。
MyISAM：
早期的MyISAM是MySQL默认的引擎。现在已经被InnoDB取代。它支持全文索引，支持空间索引机制来索引结构化的数据。而且MyISAM在组织它的数据的时候，结构比较特殊，每一张表都对应了三个文件，
db.opt：定义数据库的选项及其信息
*.frm：format格式的定义
*MYD：My Data：数据保存位置
*MYI：My Index：数据的索引存放位置
MyISAM的重要特性：
1.非事物
2.不支持外键约束
3.支持全文索引（能够支持全文索引的引擎只有MyISAM）,当我们需要在InnoDB引擎中进行全文索引怎么办呢？利用其他的插件，比如Lucene(Java语言构架)或者Sphinx(C语言构架)，都能对Mysql不管什么引擎，对其数据进行快速全文索引的功能。
4.无数据缓存：无法对数据进行缓存，但是对于服务器是可以缓存的。
5.只对索引进行缓存。索引缓存也被称为Key Buffer
6.支持HASH和BTREE索引，HASH一般只在特定场合使用，比如只在Age=30时使用。
7.表级别的锁
8.对于读来说，速度非常快，用于数据仓库是一个非常好的选择。
9.数据可以压缩存放
10.支持在线备份
11.支持最多64个索引（不过这已经足够了）
MyISAM的服务器变量：
key_buffer_size:索引缓存：用于定义MyISAM索引缓存大小,
使用SHOWVARIABLES LIKE ‘key_buffer_size’;来查看当前大小。
即便是我们没有使用MyISAM引擎，你也是要设置这个值的。默认是8M。
concurrent_insert：是否允许并发写入（锁），
如果想使用并发写入，无非就是实现了如果两个写入操作影响的不是相同数据的话，可以允许它并发写于，事先需要Mysql对其判断。而且并发写入要求表操作中存在数据间隙才能实现。
delay_key_write：推迟索引/键的写入操作：ON|OFF
索引是为了加速查询过程的，我们如果对表做一次修改，则索引就必须要重建了，而重建索引很浪费时间，浪费系统资源。而这个值，就是用于延迟索引写操作的，定义不是表一修改就立即进行索引重建。默认是开启的
max_write_lock_count：嗯……这个就不介绍了……可以参照官方文档
preload_buffer_size：用于定义刚启动的时候缓存大小的。默认是32KB
MyISAM的专用管理工具：
myisamchk：实现分析优化并修复MyISAM表的
myisampack：实现压缩MyISAM表的，当压缩完成之后就不能再修改了
myisam_ftdump：显示全文索引的
InnoDB:
具有以下几个特性：
1.支持事物，基于MVCC
2.支持行级别的锁
3.支持外键
4.支持聚簇索引。它是BTREE的一种属性，非聚簇索引，索引和数据是分开存放的，所以找到索引之后索引其实只是一个指针，而聚簇索引，它的索引和数据是在一起的，只要能找到索引则立刻能找到数据，它要比非聚簇索引少一次查找，速度非常快。所以当我们把索引读取到内存，则意味着把数据也读取到内存了。
5.同时支持索引缓存和数据缓存。意味着把用户查询的数据可以直接缓存下来。
6.支持在线非阻塞式的备份，支持热备：不过需要商业化的备份工具来实现
InnoDB的存放：
InnoDB不使用3个文件来保存，而是放在一个完整的表空间ib_data中来存放的。
*.frm innoDB的定义文件
ib_log*：循环写入的两个文件通过“innodb_log_file_size”定义。
相关的服务器参数：
Innodb_data_home_dir：数据文件的存放目录。默认情况下这个跟data_dir是一样的。
Innodb_data_file_path：数据文件的路径（使用的是相对路径）
Innodb_file_per_table：每一个表的表文件路径
Innodb_buffer_pool_size：定义缓存数据和索引的空间大小，用于缓存数据和索引，这个空间一般要求比较大。
Innodb_flush_log_at_trx_commit：定义日志多长时间写到真正的数据文件中。这个选项有3个值，0，关闭，1，启用，2，按需
innodb_log_file_size：事物性日志文件大小（记录的是原子操作），是个循环日志，这个文件特别关键，Innodb的数据文件崩溃的话，自我修复要靠这两个文件。这是个重做日志。这个文件可以定义的稍微大一点。理想值为128-256M之间
存储引擎的状态：SHOWENGINE INNODB STATUS;
Semaphores：引擎级别的属性和信息
Foreign Keyerrors：外键错误信息
Deadlocks：死锁信息。Mysql是可以自动解除死锁的
Transactions：事物相关的信息
File I/O：文件I/O相关信息
Inser buffer andadaptive hash index：索引和Hash索引的信息
log：日志信息
Buffer poll andmemory：缓存相关信息
Row operations：行操作相关信息
用户管理
通过mysql元数据库中的六张表，来定义权限指派。一共有6个重要的文件
user,db,host,tables_priv,columns_priv,procs_priv
user：其中最重要的信息，3个字段：用户，来自哪个主机，密码是什么：user,host,password，password中的值则是加密存储的。
tables_priv：定义表级别的权限的，哪个表哪个用户是否可以执行SELECT等DML语句的。
columns_priv：更细力度的权限控制，不光定义某个表，也能定义某个字段上的权限
procs_priv：定义用户是否具有执行存储过程权限的。
这六张表可以称之为MySQL的授权信息表。
对于Mysql数据，可以授权的权限种类：
1.SELECT,INSERT,UPDATE,DELETE等DML相关的语句。
2.CREATE（仅仅指创建数据库和表的权限） DROP:等DDL语句
3.INDEX ：索引的权限
4.ALTER ：定义用户是否可以修改表结构
5.SHOW DATABASES;用户是否具有查看本服务器上具有访问权限的数据库的权限
6.SUPER：执行管理命令的权限
7.LOCK TABLES：锁权限
8:CREATE VIEW,SHOW VIEW 创建和查看视图的权限
9:CREATE USER 创建和删除用户的权限
10: REPLICATION SLAVE, 主要用于复制的权限，是否具有从主服务器上复制二进制日志内容的权限
REPLICATION CLIENT：主要用于复制的权限，请求主从环境当中用于复制的相关信息的权限。
11:RELOAD：使用重启服务器和重读的权限
12.SHUTDOWN：关闭服务的权限
13.SUPER: 实现让用户实现管理服务器进程的权限
14:GRANT OPTION：当你把一个数据库的权限授予一个用户之后，这个用户是否能够再将权限授予他人？
如何去创建一个用户账号：
CREATE USER user_name@’HOST’IDENTIFIED BY ‘PASSWORD’
指定主机的时候%表示任意主机
比如，创建用户jerry并指定密码123456
CREATE USER jerry@’%’IDENTIFIED BY ‘123456’
FLUSH PRIVILEGES;
当我们创建一个用户之后，这个用户仅仅只有，连接到数据库上，并使用SHOW DATABASES的权限
在主机名和指定的IP地址上我们可以使用通配符
比如：
192.168.0.%
192.168.0.0/255.255.255.0
删除一个用户：
格式：DROP USER username@host
如何给用户授权：
格式：GRANT priv_type ON 数据库对象 [权限级别] TO 用户 [并修改其密码] [设定此用户将获得的权限继续授权]
1.给jerry用户添加各项权限
授予jerry具有执行创建数据库的权限：
GRANT CREATE ON *.* TO ‘jerry’@’%’;
FLUSH PRIVILEGES;
当授予CREATE权限之后，如果想DROP，则没有权限
授权DROP:
GRANT DROP ON *.* TO ‘jerry‘@’%’;
FLUSH PRIVILEGES;
授予查询权限
GRANT SELECT ON *.* TO ‘jerry’@’%’;
FLUSH PRIVILEGES;
2.授予所有的对与hellodb数据库的权限：
GRANT ALL PRIVILEGES ON hellodb.* TO ‘jerry’@‘%‘；
3.授予权限并修改密码：
GRANT priv_list ON db.table TO user@host [IDENTIFIED BY ‘password’]
对象类型：
TABLE 默认就是TABLES
FUNCTION 存储函数
PROCEDURE存储过程
权限级别：
*
*.*
db_name
db_name.tbl.name
tbl_name
db_name.routine_name
当我们再次授予权限的时候，它会附加权限，而不会覆盖原有权限
权限中的“USAGE”就是允许用户连接到服务器上，
查看一个人的权限：
SHOW GRANTS FOR ‘root’@’localhost’
SHOW GRANTS FOR ‘jerry’@’%’
收回权限：
REVOKE [权限类型] [指定对象] [哪个用户]；
1.比如收回jerry的SELECT 权限：
REVOKE SELECT ON hellodb.* FROM ‘jerry’@‘%‘；
FLUSH PRIVILEGES;
限定用户所使用的资源：
这种限定不能立即生效，想要立即生效，要使用：
FLUSH USER_RESOURCES;
常用的限定资源：
WITH GRANT OPTION：设置用户可以向下授权
WITH MAX_QUERIES_PER_HOUR n; ：限定用户每个小时最多执行N次查询
WITH MAX_UPDATES_PER_HOUR n：限定用户每个小时最多执行几次修改操作。
WITH MAX_CONNECTIONS_PER_HOUR n：限定用户一个小时最多连入多少次。
WITH MAX_USER_CONNECTIONS n：限定使用同一个用户账号在同一时间最多能同时连进来几次。
字段级别权限的限定：
常用的字段级别的限定只有SELECT 和UPDATE；
比如：授予查询字段的权限
GRANT SELECT(name) ON db.table TO user@host
授予用户具有执行存储例程的权限：
GRANT EXECUTE ON [对象类型] TO user@host
MySQL数据库的密码忘了怎么办？
1.重新初始化mysql数据库
显然，这不是我们想要的。
2.启用1级别，跳过密码，然后进去重新设定密码。
我们需要先重启服务，在重启服务器的时候需要用一个特殊的选项来启动服务器：
首先我们要知道我们启动mysqld的时候，启动的其实是/$USERBIN/mysqld_safe这个服务。（$USERBIN）
而我们现在需要用这个外加一些参数,并让他在后台运行
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
在这种情况下，要给用户设密码，必须使用修改表的形式来完成，则：
使用mysql命令进入mysql，此时是不需要密码的。
USE mysql
UPDATA user SET Password=PASSWORD(‘redhat’) WHERE User=’root’ AND Host=’localhost’;
UPDATA user SET Password=PASSWORD(‘redhat’) WHERE User=’root’ AND Host=’127.0.0.1’;
明天休息一天，后天继续，后天的内容更加重要，Mysql的备份和数据的复制！
2508
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
