1. 索引
索引类型：
• 普通索引
• 唯一索引
• 主键索引
• 组合索引
• 全文索引
查询索引语法结构：show index from 表名;
1.1普通索引
普通索引是最基本的索引，它的创建没有任何限制。
在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length。
创建索引时需要注意：
如果指定单列索引长度，length 必须小于这个字段所允许的最大字符个数。
----创建索引
语法结构：
create index 索引名 on 表名(创建索引列名);
示例：
为 emp3 表中的 name 创建一个索引，索引名为 emp3_name_index
create index emp3_name_index on emp3(name);
----修改表添加索引
语法结构：
alter table 表名 add index 索引名(创建索引的列名);
示例：
修改 emp3 表，为 addrees 列添加索引，索引名为 emp3_address_index
alter
----创建表时指定索引列
语法结构：
create table 表名 (
列1 类型 [约束，自动增长等],
列2 类型[...],
index 索引名 (列名[(length)])
) ;
1.2 唯一索引
唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值
----创建唯一索引
create unique index 索引名 on 表名（创建索引列名）;
示例：
为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index
create unique index emp_name_index on emp(name);
----修改表添加唯一索引
alter table 表名 add unique 索引名(创建索引列名)；
示例：
修改 emp 表，为 salary 列添加唯一索引，索引名为 emp_salary_index
alter table emp add unique emp_salary(salary);
----创建表时指定唯一索引
CREATE TABLE 表名 (
COLUMN TYPE ,
PRIMARY KEY (`id`),
UNIQUE index_name (column(length))
)
1.3 主键索引
一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。 （创建逐渐时自动创建主键索引）
----修改表添加主键索引
alter table 表名 add primary key(列名) ;（等同于添加主键）
示例 :
修改 emp 表为 employee_id 添加主键索引
alter table emp add primary key(employee_id)
----创建表时指定主键索引
CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY(column)
)
示例：
创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id 列创建主键索引
create table emp6(
employee_id int primary key auto_increment,
name varchar(20),
address varchar(50)
);
1.4 组合索引
组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则--从左侧开始匹配)。
--最左前缀原则
就是最左优先。
如：我们使用表中的name，address，salary照此顺序 (name，address，salary )创建组合索引，那么想要组合索引生效，我们只能使用如下组合：
name/address/salary
name/address
name/
如果使用 addrees/salary 或者是 salary 则索引不会生效。
----修改添加组合索引
alter table 表名 add index 索引名 (创建索引列1(length),创建索引列2(length)...)
示例 ：
修改 emp6 表，为 name，address 列创建组合索引
alter table emp6 add index emp6_index_n_a(name,address)
----创建表时创建组合索引
CREATE TABLE 表名 (
COLUMN TYPE ,
INDEX index_name (column(length),column(length))
)
示例 ：
创建 emp7 表，包含 emp_id,name,address 列，同时为 name,address 列创建组合索引。
create table emp7(
emp_id int primary key auto_increment ,
name varchar(20),
address varchar(30),
index emp7_index_n_a(name,address)
);
1.5 全文索引
全文索引(FULLTEXT INDEX)主要用来查找文本中的关键字，而不是直接与索引中的值相比较。FULLTEXT 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where语句的参数匹配。FULLTEXT 索引配合 match against 操作使用，而不是一般的 where 语句加 like。
全文索引可以从类型的 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 添加。
注意：对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
----修改添加全文索引
alter table 表名 add FULLTEXT index_content(content);
修改 emp7 表添加 content 列类型为 TEXT
alter table emp7 add COLUMN content text;
示例1 :
修改 emp7 表，为 content 列创建全文索引
alter table emp7 add fulltext emp7_content_fullindex(content)
----创建表时创建全文索引
CREATE TABLE `table` (
COLUMN TYPE ,
FULLTEXT index_name (column)
)
示例：
创建emp8表包含 emp_id列 ，content列该列类型为text ，并为该列添加名为emp8_content_fulltext 的全文索引
create table emp8(
emp_id int primary key auto_increment,
content text,
fulltext emp8_content_fullindex(content)
);
----删除全文索引
1) drop index index_name on 表名;
2) alter table table_name drop index index_name ;
示例:
删除 emp8 表中名为 emp8_content_full 的索引。
drop index emp8_content_fullindex on emp8;
1.6 全文索引的使用
全文索引的使用与其他索引不同。在查询语句中需要使用
match(列名) against(‘词’) 来检索数据
----全文解析器
MySQL 中默认的全文解析器不支持中文分词。
如果数据含有中文需要更换全文解析器 ngram。
-----使用全文索引
select 投影列 FROM 表名 where match(全文索引列名) against(‘词’);
修改 emp8 表，为 content 列创建名为 emp8_content_full 的全文索引
alter table emp8 add fulltext emp8_content_full(content)
向 emp8 表中插入一条数据 content 的值为”hello,bjsxt”
insert into emp8 values(default ,'hello bjsxt')
示例：
查询 emp8 表中内容包含 bjsxt 的数据。
select * from emp8 where match(content) against('bjsxt')
----更换全文解析器
在创建全文索引时可以指定 ngram 解析器
with parser ngram;
删除 emp8 表中的 emp8_content_full 全文索引
drop index emp8_content_full on emp8
示例：
修改 emp8 表，为 content 列添加名称为 emp8_content_full 的全文索引，并指定 ngram全文解析器。
alter table emp8 add fulltext emp8_content_full(content) with parser ngram
向 emp8 表中添加一条数据 content 值为”你好，北京尚学堂”
insert into emp8 values(default ,'你好，北京尚学堂')
示例：
查询 emp8 表中内容包含”北京尚学堂”的数据
select * from emp8 where match(content) AGAINST('北京尚学堂')
2. Mysql中的用户管理
MySQL 是一个多用户的数据库系统，按权限，用户可以分为两种：root 用户：超级管理员，和由 root 用户创建的普通用户
----查看用户（所用户信息位于 mysql 库中的user表中）
select user,host from mysql.user;
----MySQL 创建用户
create user username identified by '密码';
示例：
创建一个 u_sxt 的用户，并查看创建是否成功。
create user u_sxt IDENTIFIED by 'sxt';
----分配权限
新用户创建完后是无法登陆的，需要分配权限。
GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY "密码"
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password'
登陆主机：
% 匹配所有主机
localhost：localhost 不会被解析成 IP 地址，直接通过 UNIXsocket 连接
127.0.0.1：会通过 TCP/IP 协议连接，并且只能在本机访问；
::1：::1 就是兼容支持 ipv6 的，表示同 ipv4 的 127.0.0.1
----权限列表
示例：
为 u_sxt 用户分配只能查询 sxt 库中的 employees 表，并且只能在本机登陆的权限。
grant select ON sxt.employees to 'u_sxt'@'localhost' identified by 'sxt'
----刷新权限
每当调整权限后，通常需要执行以下语句刷新权限
FLUSH PRIVILEGES
----删除用户
drop user 'username'@'localhost' ;
示例 :
删除 u_sxt 用户
drop user 'u_sxt'@'localhost'
另外，还可以通过可视化操作通过 Navicat 工具来管理用户
3. MySQL 分页查询原则
• 在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
• MySQL 分页中开始位置为 0。
• 分页子句在查询语句的最后侧。
----LIMIT 子句
语法格式：
select 投影列 from 表名 where 条件 order by limit 开始位置，查询数量;
示例 :
查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。
select * from employees order by employees_id limit 0,2
----LIMIT OFFSET 子句
语法格式 :
select 投影列 from 表名 where 条件 order by 列 limit 查询数量 offset 开始位置;
示例：
查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条 结果。
select * from employees order by employees_id limit 2 offset 4 ;
4. MySQL 中的执行计划
----MySQL 执行计划
在 MySQL 中可以通过 explain 关键字模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理 SQL 语句的。
----MySQL 整个查询执行过程
• 客户端向 MySQL 服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
• MySQL 根据执行计划，调用存储引擎的 API 来执行查询
• 将结果返回给客户端，同时缓存查询结果
----启动执行计划
explain select 投影列 from 表名 where 条件;
----EXPLAIN 列的解释
--ID
查询执行顺序：
id 值相同时表示从上向下执行
id 值相同被视为一组
如果是子查询，id 值会递增，id 值越高，优先级越高
--select_type
simple:表示查询中不包含子查询或者 union
primary:当查询中包含任何复杂的子部分，最外层的查询被标记成 primary
derived:在 from 的列表中包含的子查询被标记成 derived
subquery:在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery
union:两个 select 查询时前一个标记为 PRIMARY，后一个标记为 UNION。union 出现在 from 从句子查询中，外层 select 标记为 PIRMARY，union 中第一个查询为 DERIVED，第二个子查询标记为 UNION
unionresult：从 union 表获取结果的 select 被标记成 union result 。
--table
显示这一行的数据是关于哪张表的。
--type
显示连接使用了何种类型。从最好到最差的连接类型为 system、const、eq_reg、ref、range、index 和 ALL。
system：表中只有一行数据。属于 const 的特例。如果物理表中就一行数据为 ALL
const ：查询结果最多有一个匹配行。因为只有一行，所以可以被视为常量。const 查询速度非常快，因为只读一次。一般情况下把主键或唯一索引作为唯一条件的查询都是 const
eq_ref：查询时查询外键表全部数据。且只能查询主键列或关联列。且外键表中外键列中数据不能有重复数据，且这些数据都必须在主键表中有对应数据（主键表中数据可以有没有用到的）
ref：相比 eq_ref，不对外键列有强制要求，里面的数据可以重复，只要出现重复的数据取值就是 ref。也可能是索引查询。
range：把这个列当作条件只检索其中一个范围。常见 where 从句中出现 between、<、in 等。
主要应用在具有索引的列中
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比 ALL 更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。
--possible_keys
查询条件字段涉及到的索引，可能没有使用。
--Key
实际使用的索引。如果为 NULL，则没有使用索引。
--key_len
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。
--ref
显示索引的哪一列被使用了，如果可能的话，是一个常量 const。
--rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
--Fitered
显示了通过条件过滤出的行数的百分比估计值。
--extra
MYSQL 如何解析查询的额外信息
5. MySQL 数据库存储引擎介绍
----查看 MySQL 数据库中的数据库存储引擎
show engines;
----MySQL 数据库引擎介绍
5.1 ISAM(Indexed Sequential Access Method)
ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM 的两个主要不足之处在于，它不支持事务处理，也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把 ISAM 用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL 能够支持这样的备份应用程序。
注意：使用 ISAM 时必须经常备份所有实时数据。
5.2 MyISAM
MyISAM 是 MySQL 的 ISAM 扩展格式和缺省的数据库引擎。除了提供 ISAM 里所没有的索引和字段管理的大量功能，MyISAM 还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行 OPTIMIZE TABLE 命令，来恢复被更新机制所浪费的空间。MyISAM 还有一些有用的扩展，例如用来修复数据库文件的 MyISAMCHK 工具和用来恢复浪费空间的 MyISAMPACK 工具。MYISAM 强调了快速读取操作，这可能就是为什么 MySQL 受到了 WEB 开发如此青睐的主要原因：在 WEB 开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和 INTERNET 平台提供商只允许使用MYISAM 格式。
MyISAM 格式的一个重要缺陷就是不能在表损坏后恢复数据。
注意：MyISAM 引擎使用时必须经常使用 Optimize Table 命令清理空间；必须经常备份所有实时数据。工具有用来修复数据库文件的 MyISAMCHK 工具和用来恢复浪费空间的MyISAMPACK 工具。
如果使用该数据库引擎，会生成三个文件：
.frm:表结构信息
.MYD:数据文件
.MYI:表的索引信息
5.3 InnoDB
InnoDB 包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。
如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。
InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID 兼容）存储引擎。InnoDB 锁定在行级并且也在 SELECT 语句提供一个 Oracle 风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在 InnoDB中扩大锁定的需要，因为在 InnoDB 中行级锁定适合非常小的空间。InnoDB 也支持FOREIGNKEY 强制。在 SQL 查询中，你可以自由地将 InnoDB 类型的表与其它 MySQL 的表的类型混合起来，甚至在同一个查询中也可以混合。
InnoDB 是为处理巨大数据量时的最大性能设计，它的 CPU 效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
InnoDB 存储引擎被完全与 MySQL 服务器整合，InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB 存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与 MyISAM 表不同，比如在 MyISAM 表中每个表被存 在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为 2GB 的操作系统 上。
5.4 innodb 与 myisam 区别
5.5 修改数据库级引擎
修改 MySQL 的 my.ini （安装目录）配置文件
default-storage-engine=数据库引擎名称;
重启 MySQL
5.6 修改表级存储引擎
ALTER TBALE tableName engine=InnoDB ;
示例：
查询表的存储引擎
show create table table_name;
