mysql

mysql是什么?

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

体系结构

应用层：

Connection Pool(连接池组件)：
权限校验，线程重用，分配管理和释放数据库连接，查询缓存。

Management Services &Utillties(管理服务和实用工具)：
Backup&Recovery(备份和恢复)，Security(安全)，Replication(复制)，Cluster(集群)，Administration(管理)，Instance Manager(数据库实例管理)，Partitioning(分区)，Query，Browser(可视化) Migration Toolkit(数据库迁移)，INFORMATION_SCHEMA(信息数据库)

服务层：
SQL Interface：
1.sql接口组件,接收sql命令,如DML,DDL和存储过程，并将最后结果返回给用户
2.Parser(解析器)-> 分析sql语法的合法性，尝试将sql命令分解成数据结构，分解失败，则提示SQL语句不合理
3.Optimizer(优化器)-> 生成执行计划、分析并选取最优查询路径
4.Caches & Buffers(缓存和缓冲)

存储引擎层：

mysql中的数据基于各种各样的存储引擎存储于文件（或者内存）中，每种存储引擎使用不同的存储规则，索引结构和锁来提供不同的功能和能力。存储引擎是基于表，而不是数据库的。
innoDB
MyISAM
NDB
Memory
Archive
Federated
Maria
其他存储引擎

数据文件层：
文件系统
文件和日志

数据库操作

数据库
1.创建数据库
create database 数据库名;

2.选择数据库
use 数据库名;

3.删除数据库
drop database 数据库名;

数据表
1.创建数据表
CREATE TABLE 数据表名 (字段名 字段类型);

2.删除数据表
DROP TABLE 数据表名 ;

数据操作
1.查询
SELECT field1, field2,…fieldN FROM table_name1, table_name2…
[WHERE condition1 [AND [OR]] condition2…

2.插入
INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );

3.修改
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause];

4.删除
DELETE FROM table_name [WHERE Clause];

字段操作
1.增加
ALTER TABLE 数据表名 ADD 新增字段 字段类型;

2.删除
ALTER TABLE 数据表名 DROP 字段名;

3.修改
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE c c CHAR(10);
（把字段 c 的类型从 CHAR(1) 改为 CHAR(10)）

MySql事务介绍

1.事务介绍

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说同时多个sql语句对不同表的操作。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

2.事务控制语句

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

索引详说

1.什么是索引？

索引是一种特殊的文件(MySql数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针，直接在索引中查找符合条件的选项，加快数据库的查询速度，而不是一行一行去遍历数据后才选择出符合条件的。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。

2.索引类型

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

MySQL支持的索引包括INDEX、UNIQUE、PRIMARY KEY、FULLTEXT类型的索引。

1. 普通索引INDEX：这是最基本的索引类型，而且它没有唯一性之类的限制，多行记录可以包含相同值。
2. 唯一性索引：和“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
3. 主键：主键是一种特殊的唯一性索引，不允许有空值，但它必须指定为“PRIMARY KEY”。一般是在建表的时候同时创建主键索引，也可以通过修改表的方式加入主键。注意：每个表只能有一个主键。
4. 全文索引：全文索引FULLTEXT可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

3.不足之处

索引不是建立的越多、越长越好，过多的使用索引将会造成滥用。

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件，对后续数据库的增删改都需由额外的操作来更新索引。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
3.索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

4.索引操作

创建

1.普通索引

CREATE INDEX indexName ON mytable(username(length)); 
# 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

# 创建表的时候直接指定
CREATE TABLE mytable(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	INDEX [indexName] (username(length)) 
); 

2.唯一索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;

# 创建表的时候直接指定
CREATE TABLE mytable( 
	ID INT NOT NULL,  
	username VARCHAR(16) NOT NULL,  
	UNIQUE [indexName] (username(length))  
);  

删除

DROP INDEX [indexName] ON mytable; 

使用ALTER 命令添加和删除索引

四种方式来添加数据表的索引：
#(1)该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);

#(2)这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);

#(3)添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);

#(4)该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

#添加索引实例
ALTER TABLE testalter_tbl ADD INDEX (c);

#删除索引实例
ALTER TABLE testalter_tbl DROP INDEX c;