mysql 常用工具：
phpmyadmin
mysql workbench

一、mysql的存储引擎

mysql支持多种存储引擎。
查看方法：show engines;
语句的结尾可以使用“；”，也可以用“\g”或“\G” 用\G结果更美观

1.InnoDB引擎：
1）支持自动增长列。自动增长列值不能为空，值唯一，且必须是表的主键。
插入数据时该列不输入值、输入0或NULL，插入的值都是自动增长后的值；
如果插入某个确定值，且该值在以前没出现过，就直接插入。
2）支持外键。删除、更新父表时，子表必须有相应变化。
InnoDB引擎中创建的表的表结构存储在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path表空间中。

InnoDB提供了良好的事务管理、崩溃修复能力和并发控制。但读写效率稍差，占用的数据空间相对比较大。

使用环境：
更新密集的表适合多重并发的更新请求
事务是管理敏感数据（金融、用户信息等）等的必须软件
自动灾难恢复虽然MyISAM表也能提供灾难修复，但其过程要长很多

2.MyISAM引擎
是基于ISAM存储引擎发展起来的。
MyISAM存储引擎的表存储成3个文件。文件名与表名相同，扩展名包括frm、myd、myi
frm存储表结构
myd存储数据
myi存储索引

MyISAM引擎的表支持3中存储格式，静态型、动态型和压缩型。
（1）MyISAM静态型
如果表列大小都是静态的（未使用xBLOB、xTEXT、varchar），mysql就是用静态MyISAM格式。这种表性能高，维护和访问以预定义格式存储的数据时开销很低，但分配的空间大。
（2）MyISAM动态型
有表列是动态的，mysql自动使用动态格式。动态表占用空间比静态表小，但性能差。因为某个字段内容的修改可能导致位置移动，并导致碎片产生。随着数据集中的碎片增加，访问性能下降。
修复方法为：
1）尽可能使用静态数据类型
2）经常使用optimize table语句整理碎片
（3）MyISAM压缩型
创建在整个应用程序生命周期内都只读的表。可以使用myisampack工具将其转换为MyISAM压缩表。

MyISAM引擎占用空间小，速度快，但不支持事务完整性和并发性

3.MEMORY引擎
是Mysql中特殊的引擎。使用存储在内存中的内容来创建表，所有数据放在内存中。

每个基于memory引擎的表实际对应一个磁盘文件。文件名为表名.frm，存储结构。内容存在内存中。服务器需要足够内存来维持memory存储引擎的表的使用。不需要使用的表应该释放掉。

memory引擎的索引类型默认是hash索引，比B树索引块。

该引擎通常很少用到。重启机器或关机会导致数据消失。基于memory存储引擎的表生命周期较短，通常是一次性的。

创建该引擎的出发点是速度，但memory表大小收到max_rows和max_heap_table_size限制，max_rows在建表时指定，max_heap_table_size默认16MB。

memory表不支持varchar、blob、text数据类型，必须是定长的。
使用场景为：暂时性的表、如果突然丢失也没关系的表

4.选择存储引擎
InnoDB引擎：用于事务处理应用程序。对事务要求比较高、要求并发或者数据更新频繁，应该选择InnoDB引擎。
MyISAM引擎：管理非事务表，提供高速存储和检索，及全文搜索功能。用于插入新数据和读出记录效率高；应用完整性、并发性要求低时也可以选择。
MEMORY引擎：所有数据在内存，速度快，安全性差。适合快速读写、安全性要求低的场景。表不能太大。

二、数据类型

1.数字类型
根据长度选择，按范围和所占字节数：
TINYINT、BIT、BOOL所占字节数分别为1、2、3、4，取值范围就是2的N次方除以2，N为8、16、24、32。

浮点型包括FLOAT\DOUBLE\DECIMAL

2.字符串类型
普通类型:char varchar 0~255
可变类型：text blob 分别适合于长文本和二进制数据
特殊类型：set enum

3.日期和时间
datetime
date
timestamp
time
year

三、基本操作
建库：create database xxxx;
查看库：show databases；
选择库：use databae；
删库：drop database xxxx；

建表：create [temporary ] table [if not exists] xxx
[(列名属性，……)] 基本和oracle相同
查看表结构： show [full] columns from 表名 [from 数据库名];
  show [full] columns from 数据库名.表名;
  describe 表名；
  describe 表名列名；
修改结构：alter table xxx
注：使用alter修改表列，必须将数据全部删除后才可以执行（不这样会报错吗？）
重命名表：rename table xxx to xxxx；
删表：drop table [if exists] xxx；删除不存在的表会报错

注：
1)mysql的group by不要求所选的列必须在group by子句中，如：
select bookname,avg(price),type from tb_book group by type;
2)order by 可以对结构进行限制，如：
select * from t order by id desc limit 3;
3)用limit限制查询结果，还可以从查询结果的中间部分取值：
select * form. t where id limit 1,4; 表示从第二条开始查询四条记录。

在查询语句中，mysql的正则表达式与oracle不太一样。但符号含义一样
如：
^ 表示以xxxxx开头
$ 表示以xxxxx结尾
. 表示任意字符，包括回车和换行
[字符集合] 匹配“字符集合”中的任意一个字符
[^字符集合] 匹配“字符集合”以外的任意一个字符
A|B|C 匹配A\B\C中的任意一个字符串
* 匹配多个该符号前面的字符，包括1、0
+ 匹配多个该符号前面的字符，包括1
字符串{N} 匹配字符串出现N次
字符串{M,N} 匹配字符串出现最少M次，最多N次

四、函数
1.字符串函数
当前日期\时间\日期和时间：curdate()、curtime()、now()

2.系统信息函数
version()数据库版本
connection_id() 服务器连接数
database(),schema() 当前数据库名
user(),system_user(),session_user(),current_user() 当前用户
charset(str) 字符串str的字符集
collation(str) 获取字符串str的字符排列方式
last_insert_id() 最近生成的auto_increment的值

3.加密函数
password(str) 加密后数据是不可逆的
md5(str) 用于普通数据加密
encode(str,pswd_Str) 用pswd_Str为str加密。结果是二进制数，需要blob类型字段保存
decode(crypt_str,pswd_Str) 用pswd_str为crypt_str加密。crypt_str是encode函数加密后的二进制数据

五、查询优化

1.索引

分析查询优化： explain 数据库查询语句

mysql创建索引与oracle基本一样。

索引字段上使用like '%xxx'的形式查询也不会用到索引。
（多列索引必须使用前导列才可以用到吗？？？？）
使用or关键字的查询，需要or两边的条件都有索引，才可以用到索引

2.分析查询语句
explain select xxx；
describe select xxx；（可缩写为desc）

3.查询高速缓存
用户通过select查询数据时，结果集被存到特殊的高速缓存中。首次查询后再次做相同查询，mysql可以从高速缓存检索结果。

查看是否开启高速缓存：
show variables like '%query_cache%';
显示的结果中
have_query_cache表示服务器是否配置了高速缓存；
query_cache_size 分配给告诉缓存的空间（MB）
query_cache_type 高速缓存开启状态。0或OFF表示高速缓存关闭；1或ON表示高速缓存打开了；2或DEMAND表示根据需要运行的是否为sql_cache选项的select语句提供查询高速缓存。

使用高速缓存的例子：
select sql_cache * from xxxx;
不适用高速缓存可以应用关键字sql_no_cache

4.优化多表查询
嵌套语句可以采用以下的select语句进行优化：
select @avgage:=avg(age) from studentinfo;
select distinct name from studentinfo where age>@avgage;
也就是将某个查询结果非给临时变量，并在以后的查询中调用该变量。

5.临时表
mysql的临时表是针对单一会话的。会话结束，临时表就被删除了。临时表只存储在内存中。

六、mysql索引

所有存储引擎最少支持每个表16个索引。

mysql索引分为普通索引、唯一索引（union）、全文索引（fulltext）、单列索引、多列索引和空间索引等。
空间索引只能创建在空间数据类型上，只有MyISAM引擎支持空间检索，且检索字段不能为空。
只有MyISAM引擎支持全文索引。

在建表语句中包含了索引创建语句。
create table t(
id int(11) auto_increment primary key not null,
tel varchar(50),
index tel_num(tel(20))
);

表示在tel字段创建字段长度为20的索引，名称为tel_num
要查看表t的建表语句，可以执行如：show create table t；

在已创建的表上建立索引：
create [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name(属性 [(length)][ASC|DESC]) ;

或
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name(属性 [(length)][ASC|DESC]) ;

删除索引：
drop index index_name on table_name;

七、视图

1.查看是否有权限创建视图
select selece_priv,create_view_priv from mysql.user where user='用户名';

创建语句：
CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW XXX
AS SELECT ……
[WITH [CASCADED|LOCAL] CHECK OPTION];

ALGORITHM表示视图选择的算法
with check option表示更新视图时保证在该视图权限范围内

注意：
如果加了or replace选项，需要drop view权限；
select语句不能包含from子句中的子查询、引用系统或用户变量、引用预处理语句参数；

2.查看视图
desc 视图名；

show table status like '视图名'; 可以看到存储引擎、数据长度等

show create view；显示视图的创建语句（定义）

3.修改视图
create or replace view xxx as select ……

alter view修改视图的定义

4.更新视图
update xxxx ……；
其实就是对基表的更新

更新视图也会有很多限制，最好对基表进行操作，而不是视图。

5.删除视图
drop view if exists xxx [restrict|cascade];

ITPub博客

mysql初识（一）

最新文章