一、mysql的学习
1.mysql基本命令学习：1.命令行方式
2.执行外部文件sql语句,有多少命令就执行多少命令
1.dos命令下：mysql -uUserName -pPassword tableName<sqlPath
2.mysql命令下：source sqlPath
2.mysql数据类型
一.数值类型
Mysql支持所有标准SQL中的数值类型，其中包括严格数据类型(INTEGER,SMALLINT,DECIMAL,NUMBERIC)，以及近似数值数据类型(FLOAT,REAL,DOUBLE PRESISION),并在此基础上进行扩展。
扩展后增加了TINYINT,MEDIUMINT,BIGINT这3种长度不同的整形，并增加了BIT类型，用来存放位数据。
整数类型 字节 范围（有符号） 范围（无符号） 用途
TINYINT 1字节 (-128，127) (0，255) 小整数值
SMALLINT 2字节 (-32768，32767) (0，65535) 大整数值
MEDIUMINT 3字节 (-8388608，8388607) (0，16777215) 大整数值
INT或INTEGER 4字节 (-2147483648，2147483647) (0，4294967295) 大整数值
BIGINT 8字节 (-9233372036854775808，9223372036854775807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4字节 (-3.402823466E+38，1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38) 0，(1.175 494 351 E-38，3.402823466E+38) 单精度浮点数值
DOUBLE 8字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
INT 类型:
在 MySQL中支持的5个主要整数类型是TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的
大小是不相同的。
MySQL 以一个可选的显示宽度指示器的形式对 SQL
标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，
MySQL 会在不合规定的值插入表前自动修改为 0。
UNSIGNED
修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。
ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。
FLOAT、DOUBLE 和 DECIMAL 类型
MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE
数值类型用于表示双精度浮点数值。与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数 字，小数点后面带有 3 位数字。对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。
DECIMAL
数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，
而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。
忽略 DECIMAL 数据类型的精度和计数方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段精度设置为 10，计算方法设置为 0。
UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。
二.字符串类型
MySQL 提供了8个基本的字符串类型,分别:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 各SET等多种字符串类型。
可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。
字符串类型 字节大小 描述及存储需求
CHAR 0-255字节 定长字符串
VARCHAR 0-255字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65535字节 二进制形式的长文本数据
TEXT 0-65535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
VARBINARY(M) 允许长度0-M个字节的定长字节符串，值的长度+1个字节
BINARY(M) M 允许长度0-M个字节的定长字节符串
CHAR 和 VARCHAR 类型
CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从
0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。
CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。
CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MYSQL 数据库处理
这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度
(增加一个额外字节来存储字符串本身的长度)来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。
因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。
VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。
TEXT 和 BLOB 类型
对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB
两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、
声音文件等二进制数据类型。
TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。
比指定类型支持的最大范围大的值将被自动截短。
三.日期和时间类型
在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。
根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。
类型 大小(字节) 范围 格式 用途
DATE 4 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
DATE、TIME 和 TEAR 类型
MySQL 用 DATE 和 TEAR 类型存储简单的日期值，使用 TIME
类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串,
DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。
需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。
MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译。因为所有 TEAR 类型的值必须用 4 个数字存储。
MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。
如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。
DATEYIME 和 TIMESTAMP 类型
除了日期和时间数据类型，MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。
这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。
如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。
复合类型
MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。
一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。
ENUM 类型
ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM
类型字段可以从集合中取得一个值或使用 null 值，
除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。
ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，
这个错误值用索引 0 或者一个空字符串表示。
MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。
SET 类型
SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET
类型字段中插入非预定义的值都会使
MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。
一个 SET 类型最多可以包含 64 项元素。在 SET
元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。
并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。
希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。
通过对每种数据类型的用途，物理存储，表示范围等有一个概要的了解。这样在面对具体应用时，就可以根据相应的特来来选择合适的数据类型，使得我们能够争取在满足应用的基础上，
用较小的存储代价换来较高的数据库性能。
2.MySQL运维（高级）
1.触发器：
概念：在表中的某些特定数据变化时自动完成某些查询，可以简化程序，增加程序的灵活性
可以监视某种数据库操作（insert、update、delete），触发（insert、update、delete）
应用场合：1.当向一张表中添加或删除记录时，需要在相关表中进行同步操作；
2.当表上某列数据和其他表中数据有联系时；
3.当需要对某张表进行跟踪时。
触发器四要素：监视地点（table） 监视事件（insert/update/delete） 触发时间（after/before） 触发事件(增删改)
创建触发器语法：delimiter $ 声明执行碰到这个符号才结束
create trigger triggerName
after/before 触发时间
insert/update/delete 监视事件
on table_name 监视地址
for each row
begin sql;
...
sqlN;
end$
重要语句解释：after和before的区别：
after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删
改动作；也就是说先插入订单记录，再更新商品的数量；
before是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作；
for each row ：每一行收到影响，触发器都执行，叫行级触发器（mysql不支持语句级触发器，orcle两种都支持）
触发器引用变量：引用变量来传值
查看已经存在的触发器:show trigger
删除已经存在的触发器：drop trigger triggerName
实例：1.监视地点：table_1,
2.监视事件：insert
3.出发时间：after
4.触发事件：update
实例：
delimiter $
create trigger t1
after
update
on table_3
for each row
begin
update table_1 set score=score-5 where id=1;
end$
/*
create trigger t1
after
update
on table_3
for each row
begin
update table_1 set score=score-new.score where id=new.id;
end$
*/
mysql存储过程学习：存储过程是可以编程的，可以使用变量，表达式，控制结构等来完成复杂功能
过程：封装了若干条语句，调用时，调用时，这些封装体执行
函数：函数有返回值，过程没有返回值
存储过程：把若干条sql语句封装起来，起个名字---过程
把此过程存储在数据库中----存储过程
查看已有的procedure：
show procedure status;
调用存储过程：call procedureName()$
删除存储过程：drop procedure procedureName;
存储过程创建语法：
create procedure procedureName()
begin
sql1..
sql2..
sqlN..
end
引入变量与控制结构：
变量学习(在begin之后声明)：
声明变量格式：declare valueName valueType [default 默认值]
变量运算：变量可以做sql语句中合法的运算，加减乘除
注意：运算的结果如何赋值给；另外的变量？
set valueName :=expression
if..else控制结构:
语法：if condition then
statementbody;
elseif
statementbody;
else
statementbody;
end if;（结束控制语句，不能少）
end;
参数的调用：存储过程的括号里可以传参数，可以创建的时候声明
语法：[in/out/inout] valueName valueType
使用循环结构：
while语法：
while condition do
statementbody;
end while;
参数in和out：
in：输入类型，可以获取输入值
out：输出类型，获取变量的值，相当于获取返回值
inout：两种类型的混合
case结构使用：多条件编程
case case_value
when when_value then statementbody;
when when_value then statementbody;
when when_value then statementbody;
...
...
else statementbody;
end case;
repeat循环：
语句：repeat
statementbody;
.......
.......
until condition end repeat;#跳出循环的条件
游标的概念：一条sql对应多条结果集的资源，取出资源的接口/句柄，就是游标，沿着游标可以一次取出一行
游标声明：declare cursorName cursor for select_statement;
open:打开，open cursorName;
fetch:取值，fetch cursorName into var1,var2,..
close:关闭，close cursorName ；
游标越界标志：1.可以先用sql语句计算出行数，然后结束循环
2.使用continue来解决（这里没有解决）
continue与exit的区别：continue是触发后继续执行================>>>>>>>>>>>>>需要重点理解
exit是触发后不执行
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
CONTINUE
| EXIT
| UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code
用户与权限管理：
用户---->>服务器：分两个阶段：1.检查有没有权限连接上来
依据3个参数：host user password--->
需要验证这三个参数，任何一个不对，都会报错
2.检查有没有权限执行此操作
依据
3.新增用户的操作：用户的权限可以在mysql库db表找到，但是如果具体到表的权限则
在table_priv表找到，db找不到
创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
赋予权限1:GRANT privileges ON databasename.tablename TO
'username'@'host'
常用权限：all,create,drop,insert,delete,update,select
设置和修改密码：
命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");
撤销用户权限：
命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
删除用户：
命令: DROP USER 'username'@'host';
查看用户权限：select * from user where user='usrName';
提示：mysql的权限可以精确到行列级别；
mysql主从复制原理：（需要更加深入了解---->这里没有实现读写分离）
1.主服务器配置binlog
2.从服务器配置relaylog
3.从服务器如何有权读取master的binlog？（binlog比较敏感）
答：需要授权。，master授予slave账号
4.从服务器使用账号连接master
实际操作：两台服务器为例子为ip，100,200
1.200做从服务器
2.100做主服务器
3.保证主从3306端口互通
4.配置主服务器打开binlog
5.show master status
6.server-id=1 #给服务器起唯一的id
7.log-bin=mysql-bin #开启二进制日志
8.binlog-format=mixd/row/statement #指定日志格式
说明：statement：二进制记录执行语句，sql语句
row：二进制记录的是磁盘变化
哪个好？
语句长磁盘变化少，应该用row
语句短，影响大，磁盘变化大，应该用statement
也可以使用mix混合的，由系统根据语句来决定
9.show master status #已经能够充当master服务器
实际例子：
1.mysql主从配置。鄙人是在如下环境测试的：
主数据库所在的操作系统：win7
主数据库的版本：5.0
主数据库的ip地址：192.168.1.111
从数据库所在的操作系统：linux
从数据的版本：5.0
从数据库的ip地址：192.168.1.112
2. 1、确保主数据库与从数据库一模一样。
例如：主数据库里的a的数据库里有b，c，d表，那从数据库里的
就应该有一个模子刻出来的a的数据库和b，c，d表
2、在主数据库上创建同步账号。
GRANT REPLICATION SLAVE,FILE ON *.* TO 'mstest'@'192.168.1.112' IDENTIFIED BY '123456';
192.168.1.112：是运行使用该用户的ip地址
mstest：是新创建的用户名
123456：是新创建的用户名的密码
3、配置主数据库的my.ini(因为是在window下，所以是my.ini不是my.cnf)。
[mysqld]
server-id=1
log-bin=log
binlog-do-db=mstest //
要同步的mstest数据库,要同步多个数据库，就多加几个replicate-db-db=数据库名
binlog-ignore-db=mysql //要忽略的数据库
4、配置从数据库的my.cnf。
[mysqld]
server-id=2
master-host=192.168.1.111
master-user=mstest //第一步创建账号的用户名
master-password=123456 //第一步创建账号的密码
master-port=3306
master-connect-retry=60
replicate-do-db=mstest //
要同步的mstest数据库,要同步多个数据库，就多加几个replicate-db-db=数据库名
replicate-ignore-db=mysql //要忽略的数据库
5、验证是否成功
进入mysql，后输入命令:show slave
status\G。将显示下图。如果slave_io_running和slave_sql_running都为yes，那么表明可以成功同步
6.验证是否成功同步
进入主数据库输入命令:insert into one(name) values('beijing');
然后进入从数据库输入命令：select * from one;
如果此时从数据库有获取到数据，说明同步成功了，主从也就实现了
MySql读写分离：
3.mysql优化：
1.表的优化与列类型选择
1.定长与变长分离，
如id int占4个字节，char(4)占4个字节长度，也是定长，time即每一个单元值占的字节是固定的；
核心且常用字段，宜建成定长，放在一张表，而varchar，text，blob这种变长字段，适合单放一张表，用主键与核心表关联起来
2.常用字段和不常用字段要分离
需要结合网站具体业务来分析，分析字段的查询场景，查询频率低的字段，单折出来
3.在一对多，需要关联统计的字段上添加冗余字段
2.列选择原则
1.字段类型优先级，整型 > date,time>enum,char>varchar>blob,text
列特点分析：
整型：定长，没有国家地区之分，没有字符集的差异
例如，tinyint 1，2,3，4,5<-->char(1) a,b,c,d,e从空间上都是占一个字节，但是order by
排序，前者快，后者需要考虑排序规则
time：定长，运算快，节省空间，考虑时区，写sql时不方便
enum: 能起到约束的目的，内部用整型来存储，到那时与char联合查询时，内部经历串与值的转化
char：定长，考虑字符集和排序规则
varchar：不定长，要考虑字符集的转换与排序时校对集，速度慢
text/Blob:无法使用内存临时表（排序等操作只能在磁盘上进行）
注释：date/time如何选择，直接选择 int unsgined not null,存储时间截
2.够用就可以，不要分配给太多空间
原因：大的字段浪费内存，影响速度
3.尽量避免使用null
null不利于索引，要用特殊的字节来标注
在磁盘上占的空间其实更大，（mysql5.7已经做了优化，但查询仍然不方便）
null不利于查询，where field=null 查不到值 where field is null 或者where field is not null才可以查到
4.索引优化（重点学习）
mysql最常用的索引结构是btree(O(log(n)))，但是总有一些情况下我们为了更好的性能希望能使用别的类型的索引。hash就是其中一种选择，例如我们在通过用户名检索用户id的时候，他们总是一对一的关系，用到的操作符只是=而已，假如使用hash作为索引数据结构的话，时间复杂度可以降到O(1)。不幸的是，目前的mysql版本(5.6)中，hash只支持MEMORY和NDB两种引擎，而我们最常用的INNODB和MYISAM都不支持hash类型的索引。
btree索引和hash索引：
btree：B-Tree索引可以被用在像=,>,>=,<,<=和BETWEEN这些比较操作符上。而且还可以用于LIKE操作符，只要它的查询条件是一个不以
通配符开头的常量。像下面的语句就可以使用索引：
hash：1.它们只能用于对等比较，例如=和<=>操作符（但是快很多）。它们不能被用于像<
这样的范围查询条件。假如系统只需要使用像“键值对”的这样的存储结构，尽量使用hash类型索引。
2.优化器不能用hash索引来为ORDER BY操作符加速。（这类索引不能被用于搜索下一个次序的值）
3.mysql不能判断出两个值之间有多少条数据（这需要使用范围查询操作符来决定使用哪个索引）。假如你将一个MyISAM表转为一
个依靠hash索引的MEMORY表，可能会影响一些语句（的性能）。
4.只有完整的键才能被用于搜索一行数据。（假如用B-tree索引，任何一个键的片段都可以用于查找。我觉得可能意味着带通配
符LIKE操作符会不起作用）。
btree索引常见误区：
1.在where条件常用的列上都加上索引
例如：where id =10 and price>100
给id和price都建立了索引，查询的时候只能用id或price其中一个索引，独立的索引，同时只能用一个，意义不大
2.在多列上建立索引后，查询哪个列，索引都会发挥作用
多列索引上，索引发挥作用，需要满足左前缀的要求-->也就是指定的索引必须精准，不能模糊
索引覆盖：
3.查询大原则--->sql语句优化
1.sql语句的时间花在哪里？
答：等待时间，执行时间；这两个语句不是孤立的，如果单条语句执行快了，对其他语句的锁定也就少了
2.sql语句执行时间花在哪里？
答：查找-->沿着索引查找，慢者可能全表扫描
取出-->查到行后把数据取出来
3.如何查询快？
1.查询快，联合索引顺序，区分度，长度
2.取得快，索引覆盖
3.传输的少，更少的行和列
切分查询：按数据拆分为多次查询
分解查询：按逻辑把多表连接查询分成多个简单sql
4.sql语句的优化思路？
答：不查，少查，高效的查
不查：通过业务逻辑来计算
少查：尽量精准数据，少取行，一般一次性取出10-30条左右
必须要查：尽量使用索引查询，取时尽量少的取列
5.explain的列分析:explain 查询语句
1.select_type:查询类型：查询类型
select_type 1.simple，不含子查询
2.primary，包含子查询或者派生查询
1.subquery，非from查询
2.derived，from型查询
3.union
4.union result
2.table：1.表名
2.起别名就显示别名
3.derived 例如from型子查询
4.null 直接计算得到结果，不走表的查询
3.possible_key:可能用到的索引，注意：系统估计用到的几个索引，但是最终只能用到一个
4.key:最终的索引
5.key_len:使用索引的最大长度
注意：有可能possible_key为null，而key不为null，possible_key分析的是索引用于查找的过程
而最终的key可能是被用于查找排序或者索引覆盖
6.type：指查询的方式，非常重要，是分析查询数据过程的重要依据，可能的值如下
1.all，意味着从表的第一行，往后逐行做全表扫描，运气不好，到最后一行
2.index，比all性能好一点，all扫描的所有数据行，相当于data_all，index扫描所有的索引节点，相当于index_all
3.range，查询时能根据索引做范围的扫描
4.ref，通过索引列，可以直接引用到某些数据行
5.const，system，null 这三个分别指查询优化到常量级别，甚至不需要查找时间
按照主键来查询时，容易出现const，system
7.ref列：指连接查询时，表之间的字段引用关系
8.rows：指估计要扫描多少行
9.extra：
1.index：指用到了索引覆盖，效率非常高
2.using where：是指光靠索引定位不了，还得where判断一下
3.using temporary，是指用了临时表，group by与order by不同列时或group by order by别的表的列
4.using filesort：文件排序（文件可能在磁盘，也可能在内存）
6.in型子查询引出的陷阱（）
7.from型子查询
注意：内层from语句查到的临时表，是没有索引的。
所以from的返回内容要尽量少，需要排序，在内层先排好序
8.强制使用索引
9.count（）优化
10.group by：
1.分组用于统计，而不用与帅选重复数据，用group去重效率比较低
2.group by的列要有索引，可以避免临时表以及文件排序
3.order by的列要和group by要一致，否则也会引起临时表
4.
11.union优化：union操作总是要产生临时表，优化比较棘手
策略：union的子句要尽量具体，也就是查更少的行
子句的结果在内存里并成结果集，需要去重复，就得排列，而加all之后就不需要去重，尽量加all，可以在php中
去重
12.limit及翻页优化
随着offset的增大，limit效率越来越低
优化办法：1.从业务上解决
2.不用offset，用条件查询
select * from tableName limit 4,6
select * from tableName where id>4 limit 6
3.使用索引覆盖加延迟关联的技巧
4.mysql存储引擎学习
1.概述：mysql它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。
2.基本命令：
1.show engines； 显示当前支持的所有引擎信息
2.show create table tableName 显示某个表的存储引擎
3.show variables like '%storage_engine' 查看mysql 默认的存储引擎
4.show table status from databaseName where name="tablename" //准确查看某个数据库中的某一表所使用的存储引擎
5.CREATE TABLE t (i INT) ENGINE = INNODB; 创建表指定引擎
3.MySQL存储引擎的实际应用以及对MySQL数据库中各主要存储引擎的独特特点的描述：
1.MySQL有多种存储引擎：
MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。
MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的MySQL存储引擎，除非
你配置MySQL默认使用另外一个引擎。
1.MEMORY存储引擎提供“内存中”表。
MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这
两个引擎也都被默认包含在MySQL中。
注释：MEMORY存储引擎正式地被确定为HEAP引擎。
2.InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所
有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。
3.EXAMPLE存储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目
的是服务，在 MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。
4.NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。它在MySQL-Max
5.1二进制分发版里提供。这个存储引擎当前只被Linux, Solaris, 和Mac OS X
支持。在未来的MySQL分发版中，我们想要添加其它平台对这个引擎的支持，包括Windows。
5.ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。
6.CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。
7.BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。
8.FEDERATED存储引擎把数据存在远程数据库中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client
API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。
当你创建一个新表的时候，你可以通过添加一个ENGINE 或TYPE 选项到CREATE TABLE语句来告诉MySQL你要创建什么类型的表：
CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY;
虽然TYPE仍然在MySQL 5.1中被支持，现在ENGINE是首选的术语。
2.如何选择最适合你的存储引擎呢？
下述存储引擎是最常用的：
1.MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的MySQL存储引擎之一。注意，
通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。
当使用Myisam引擎建立表时，会在磁盘上建立3个文件，文件名就是表名
例如：表名为，my_table
1.my_table.frm 存储表定义
2.my_table.MYD 存储数据
3.my_table.MYI 存储索引
Myisam无法处理事务，这就意味着有事务处理需求的表，不能使用Myisam存储引擎
比较适合的情况：1.选择密集型的表，Myisam存储引擎在在筛选大量数据时非常迅速，这是它最大的优点
2.插入密集型的表：Myisam的并发插入特性允许同时选择和插入数据。例如：Mysam存储引擎很适合管
理右键或者服务器日志
2.InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。
是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据提供了一个强大的解决
方案，InnoDB还引入了行级锁定和外键约束，在以下场合，使用InnoDB比较理想
1.更新密集型的表：特别适合处理多重并发的更新请求
2.事务：是支持事务的标准mysql引擎
3.自动灾难恢复：InnoDB表能够自动从灾难中恢复
4.外键约束：mysql支持的外键存储引擎只有InnoDB
5.支持自动增加列AUTO_INCREMENT属性
如果需要事务支持，又有较高的并发读取频率，InnoDB是不错的选择
3.BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。
4.Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。
5.Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。
对于诸如数据仓储等VLDB环境十分适合。
6.Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。
7.Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环
境。
8.Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常
工作时间和可用性。
9.Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以
及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。
注意：请记住，对于整个服务器或方案，你并不一定要使用相同的存储引擎，你可以为方案中的每个表使用不同的MySQL存储引擎，这点
很重要。
3.各存储引擎之间的区别
为了做出选择哪一个存储引擎的决定，我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储
引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。一些引擎具有能过促使你做出决定
的独特的功能，我们一会儿再仔细研究这些具体问题。
1.字段和数据类型
虽然所有这些引擎都支持通用的数据类型，例如整型、实型和字符型等，但是，并不是所有的引擎都支持其它的字段类型，特别是BLOG(
二进制大对象)或者TEXT文本类型。其它引擎也许仅支持有限的字符宽度和数据大小。这些局限性可能直接影响到你可以存储的数据，同时
也可能会对你实施的搜索的类型或者你对那些信息创建的索引产生间接的影响。这些区别能够影响你的应用程序的性能和功能，因为你必
须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策。
2.锁定
数据库引擎中的锁定功能决定了如何管理信息的访问和更新。当数据库中的一个对象为信息更新锁定了，在更新完成之前，其它处理不能修
改这个数据(在某些情况下还不允许读这种数据)。锁定不仅影响许多不同的应用程序如何更新数据库中的信息，而且还影响对那个数据的查
询。这是因为查询可能要访问正在被修改或者更新的数据。总的来说，这种延迟是很小的。大多数锁定机制主要是为了防止多个处理更新同
一个数据。由于向数据中插入信息和更新信息这两种情况都需要锁定，你可以想象，多个应用程序使用同一个数据库可能会有很大的影响。
不同的存储引擎在不同的对象级别支持锁定，而且这些级别将影响可以同时访问的信息。得到支持的级别有三种:表锁定、块锁定和行锁定
。
支持最多的是表锁定，这种锁定是在MyISAM中提供的。在数据更新时，它锁定了整个表。这就防止了许多应用程序同时更新一个具体的表。
这对应用很多的多用户数据库有很大的影响，因为它延迟了更新的过程。
页级锁定使用Berkeley DB引擎，并且根据上载的信息页(8KB)锁定数据。当在数据库的很多地方进行更新的时候，这种锁定不会出现什么问
题。但是，由于增加几行信息就要锁定数据结构的最后8KB，当需要增加大量的行，也别是大量的小型数据，就会带来问题。
行级锁定提供了最佳的并行访问功能，一个表中只有一行数据被锁定。这就意味着很多应用程序能够更新同一个表中的不同行的数据，而不
会引起锁定的问题。只有InnoDB存储引擎支持行级锁定。
3.建立索引
建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术。有些技术也许会更适合你
存储的数据类型。
有些存储引擎根本就不支持索引，其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(
例如FEDERATED或者BLACKHOLE引擎)。
4.事务处理
事务处理功能通过提供在向表中更新和插入信息期间的可靠性。这种可靠性是通过如下方法实现的，它允许你更新表中的数据，但仅当应
用的应用程序的所有相关操作完全完成后才接受你对表的更改。例如，在会计处理中每一笔会计分录处理将包括对借方科目和贷方科目数
据的更改，你需要要使用事务处理功能保证对借方科目和贷方科目的数据更改都顺利完成，才接受所做的修改。如果任一项操作失败了，
你都可以取消这个事务处理，这些修改就不存在了。如果这个事务处理过程完成了，我们可以通过允许这个修改来确认这个操作。
5.主要引擎数据对比表
特点 Myisam BDB Memory InnoDB Archive
存储限制 没有 没有 有 64TB 没有
事务安全 支持 支持
锁机制 表锁 页锁 表锁 行锁 行锁
B-Tree 支持 支持 支持 支持
Hash 支持 支持
全文索引 支持
集群索引
数据缓存 支持 支持
索引缓存 支持 支持 支持
数据可压缩 支持 支持
空间使用 低 低 N/A 高 非常低
内存使用 低 低 中等 高 低
批量插入的速度 高 高 高 低 非常高
支持外键 支持
二、sql语法学习
概念：sql语句是结构化查询语言（Structured Query Luanguage），用来访问和处理数据库的标准计算机语言。
对数据库操作的基本语法（sql不区分大小写）：
use databaseName; 选择数据库
set names utf8; 用于设置使用的字符集
重要语法：
select 从数据库提取数据
select columnName1,columnName2,... from tableName 从表中获取数据
select distinct columnName1 from tableName 从一个列中获取唯一的值，将重复出现的值去掉
where语句：
select columnName1,columnName2,... from tableName where operator value 说明：where限制条件语句
where语句中的运算符：=、!=、>、<、>=、<=、between(在某个范围内) 、like(搜索某种方式)、
in(指定针对某个列的多个可能值)。
and--->与操作
or---->或操作
order by语法：用于对结果集按照一个列或者多个列进行排序（默认为升序排列asc，也可以指定降排列desc）
例子：select * from table_1 where score<2000 or teacher="meixi" order by id asc;升序排列；
order by 后面可以跟多个列 order by v1，v2，v3；先按v1来排列，如果有相同的，再按v2来排列，依次类推
update 更新数据库数据
update tableName set col1=value1,col2=value2,col3=value3.. where some_col=some_val;
特别注意：where不能少，用来限定需要更新的列，否则全部的值都会被更新
delete 从数据表中删除数据
delete from tableName where some_col=some_val;
特别注意：where不能少，否则会清空所有记录
insert into 向数据库中插入数据
两种编写方式：1.无需指定要插入数据的列名，只需提供被插入的数值：insert into tableName values(value1,value2,value3,..)
2.指定列名，以及插入的值：insert into tableName(col1,col2,col3,..) values(value1,value2,value3,..)
create database 创建新数据库
alter database 修改数据表
create table 创建表
alter table 变更（修改）表
drop table 删除表
create index 创建索引
drop index 删除索引
select ..limit.. 用于规定返回的记录数目
select * from tableName limit 5;
like 操作符 说明：用在where字句中搜索列中的指定模式
not like反选
select col1,col2 from tableName where col like pattern; //pattern--可以是正则表达式
select * from table_1 where teacher like 'm%'; //找出teacher里面，m开头的"%"为通配符
sql通配符：% 替代0个或多个字符
- 替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist] 不在字符列中的任何单一字符
[!charlist] 不在字符列中的任何单一字符
in操作：in允许在where字句中规定多个值
select colName(s) from tableName where colName in (val1,val2)
select * from table_1 where teacher in ("meixi","fiona");//从teacher列中选择为meixi或者fiona的值
between操作符：用于选取在两个值之间的数据范围内的值,值的类型可以是：数值，文本，日期
not between反选，其他一样
select col(s) from tableName where colName between value1 and value2;
sql别名：通过sql可以为表名称或列名称指定别名---->1.基本上是为了让列名可读性更加强
2.在查询中设计超过一个表
3.查询中使用了函数
4.列名很长或者可读性差
4.需要把两个列或者多个列结合在一起
列别名语法:select colName as aliasName from tableName;
表别名语法：select col(s) from tableName as aliasName;
表别名实例(跨两个表进行查询)：select bA.lie1,bA.lie2 ,bB.lie1,bB.lie2 from A as bA,B as bB where bA.lie1=bB.lie1;
join语句（重点）：用于把来自两个或多个表结合起来，基于这些表的共同字段
常见的join类型：inner join(简单的join)
表联合操作实例：
select table1.id,table2.id from table1 inner join table2 on table1.id=table2.id;
其他的join：left join:即使右表中没有匹配，也从左表中返回行
right join：即使左表中没有匹配，也从右表中返回
full outer join：只要一个表中存在匹配，就返回行
union操作符：合并两个或者多个select语句的结果
要求：union内部的每个select语句必须拥有相同数量的列。列的数据类型必须相同。每个语句中列的顺序必须相同。
默认union过滤掉重复的值，需要查看全部，可以用union all
语法：select col(s) from table1 union select col(s) from table2
select into:从一个表复制数据，然后插入到另一个新的表中
mysql数据库不支持select into但是支持 insert into ...select
拷贝表:create table newTableName select * from OldTableName;
语法：select * into newtable [in externaldb] from table1;
insert into select语句：从一个表复制信息到另外一个已经存在的表中，目标表中任何已经存在的行都不会受到影响
语法：insert into table2 select * from table1; //将table1的复制到table2上
sql约束：用于规定表中的数据规则，如果违反约束行为，就会被约束终止，可以在创建表的时候规定，也可以后面添加
not null: 默认情况下表接受null值，这个约束强制列不接受null
unique： 约束唯一标识数据库表中的每条记录
1.创建时约束：unique(key) 约束的值
2.创建后追加：alter table tableName add unique (key)
3.撤销约束：alter table tableName drop index uc_tableNameID
primary key ：约束唯一标识数据库表中的每条记录
说明：主键列必须包含唯一的值；主键列不能包含null值；每个表都应该有一个主键，并且只能有一个主键；
可以有多个unique，但是只能有一个primary key
语法：
1.创建时约束:primary key(key) 约束的值
2.创建后追加：alter table tableName add primary key (key)
3.撤销约束：alter table tableName drop primary key
foreign key(外键):一个表中的foreign指向另一个表的primary key
语法：
1.创建时约束：foreign key（P_id） references anothertableName(P_id);
2.创建后约束：alter table tableName add foreign key (P_id) references anothertableName(P_id);
3.撤销约束：alter table tableName drop foregn key fk_perOder
check：约束用于限制列中的值的范围
1.创建时约束：check（P_id<100） 里面写约束的条件
2.创建后追加：alter table tableName add check （p_id>0）
3.撤销约束：alter table tableName drop check chk_
default:约束用于向列中插入值,如果没有规定其他的值，那么就会将默认值添加到所有新纪录
语法：
1.创建时约束：default value 默认的值
2.创建后追加约束：alter table tableName alter key（要设置的列） set default value（默认值）
3.撤销约束：alter table tableName alter key（要默撤销的key） drop default
创建索引：create index-->作用：在不遍历整个表的情况下，通过索引查找，可以更快的查询，查询速度更快,可以给多个列建立一个索引
解释：更新一个带索引的表比不带索引的表要麻烦很多，所以最好是只在经常查询的列建立索引
语法：创建重复索引：create index index_name on table_name (col_name1,col_name2..)
创建唯一索引：create unique index index_name on table_name （col_name1,col_name2....）
撤销索引：alter table table_name drop index index_name
删除表：drop table_name
删除数据库:drop databaseName
删除表内容，不删除表：truncate table table_name
alter table语句：用于在已经存在的表中添加，删除，或修改列
1.添加列：alter table table_name add col_name datatype
2.删除列:alter table table_name drop column col_name
3.改变表中的话剧类型：alter table table_name modify column col_name datatype
auto increment:自动的创建主键字段的值，且唯一，默认为1，每次增加自动加1，
也可以创建的时候指定初始值：auto_increment=100 给定初始值为100
mysql：使用auto_increment
sqp视图：视图是可视化的表,视图是基于sql语句结果集的可视化的表。视图包括行、列，就像一个真实的表，视图中的字段就是来自一
个或多个数据库中的真实表中的字段。可以向视图添加sql函数，where以及join语句。
提示：视图总是显示最新的数据，每当用户查询视图时，数据库引擎通过使用视图的sql语句重建数据
创建视图：create view
语句：create view viewName as select col(s) from tableName where condition
更新视图：create or replace view viewName as select col_name(s) from table_name where condition
sql撤销视图：drop view view_name;
sql函数学习：
aggregate函数：avg() 返回平均值（不是数字类型就返回0）
语法：select avg(col_name) from table_name;
count() 返回指定列的值的数目（忽略null）
语法：select count(colName) from tableName;
select count(*) from tableName 返回表中的记录数
select count(distinct colName) from tableName 返回指定列的不同值，过滤掉重复的
first() 返回指定列第一个记录的值
语法：select first(colName) from table_name
注释：只有MS支持，mysql不支持
mysql替代first的语法:select col_name from table_name order by col_name asc limit 1;
last（） 返回指定列最后一个记录的值
select last(col_name) from table_name
只有MS支持last()函数
mysql替代last方法：select col_name from table_name order by col_name desc limit 1;
max（） 返回指定列最大的值
语法：select max(col_name) from table_name
min（） 返回指定列最小的值
语法:select min(col_name) from table_name
sum（） 返回给定指定列值的总和
语法:select sum(col_name) from table_name;
scalar函数：基于输入值，返回一个单一的值
ucase（） 将某个字段转为大写
语法：select ucase（col_name） from table_name;
lcase（） 将某个字段转为小写
语法：select lcase（col_name） from table_name
mid（） 从某个文本字段提取字符
语法：select mid(col_name,start[,length]) from table_name;
说明：col_name：必要，提取字符的字段
start:必要，规定开始位置（起始值是1）
length：可选，要返回的字符数，如果不写，则默认返回剩下所有
len（） 返回某个文本字段的长度
语法：select len(col_name) from table_name;
mysql语法:select length(col_name) from table_name
round（） 对某个数值字段进行指定小数位数的四舍五入
语法:select round(col_name,decimals) from table_name;
说明：col_name:必要，要舍入的字段
decimals：必要，规定要返回的小数位数，如果没有写，则没有小数部分
now（） 返回当前系统的日期和时间
语法：select now() from table_name;
format（） 格式化某个字段的显示方式
语法：select format(col_name,format) from table_name;
说明：col_name:必要，要格式化的字段；
format:必须，规定格式；
例子:select id,name,score data_format(now(),'%y年%m月%d日') as date from table_1;
group by语法:用于结合聚合函数，根据一个列或者多个列队结果集进行分组
语法：select col_name,aggregate_function(col_name) from table_name where col_name operator value
group by col_name
解释：group by后面跟着要依据分类的列属性（分组聚合：后面有多少种类就分成多少种聚合）
having语法：增加having的原因是，where关键字无法与聚合函数一起使用，having子句可以让我们筛选分组后的各组数据。
语法：select col_name,aggr_function(col_name)
from table_name
where col_name operator value group by col_name
having agg_function(col_name) operator value;
实例：
sql语法结束！！！！！！！！！！！！！！
三、其他知识点学习
1.解决dos命令下mysql查询数据乱码问题
步骤：1.第一步：找到安装mysql的目录找到 my.ini 文件，查看默认编码是否为utf-8
2.查看编码的命令：show variables like'%char%';
3.改变原有数据库的编码方式：
set character_set_database=utf8;
set character_set_server=utf8;
set character_set_client=gb2312;
set character_set_connection=gb2312;
set character_set_results=gb2312
4.成功解决乱码问题
234
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
