mysql的一些基础知识点

DDL: 数据定义语句 (create alter drop)

DML: 数据操作语句 (insert updatedelete)

DQL: 数据查询语句(select)

DCL: 数据控制语句(grant revoke commitrollback)

备份数据库表中的数据

  mysqldump -u用户名-p数据库名>文件名.sql

 恢复数据库

  Source 文件名.sql

显示数据库语句：

SHOW DATABASES

显示数据库创建语句：

SHOW CREATE DATABASEdb_name

数据库删除语句：

DROP DATABASE  [IF EXISTS] db_name

mysql有六处使用了字符集，分别为：client、connection、database、results、server、system。

nclient是客户端使用的字符集。

nconnection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。      

ndatabase是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。   

nresults是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。      

nserver是服务器安装时指定的默认字符集设定。      

nsystem是数据库系统使用的字符集设定。

① MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求

② InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

③ MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。 

如何选择表的存储引擎

①如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择

②一般来说，如果需要事务支持，并且有较高的并发读写频率，InnoDB是不错的选择。

③Heap 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态.) 

■事务隔离级别

脏读（dirtyread)：当一个事务读取另一个事务尚未提交的修改时，产生脏读。

不可重复读（nonrepeatableread)：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。

幻读（phantomread)：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

1.查看当前会话隔离级别

 select @@tx_isolation;

 2.查看系统当前隔离级别

 select @@global.tx_isolation;

 3.设置当前会话隔离级别

 set sessiontransaction isolation level repeatable read;

 4.设置系统当前隔离级别

 set globaltransaction isolation level repeatable read;

 5. mysql默认的事务隔离级别是repeatable read ,一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足绝大部分项目需求）

l建立索引
create[UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
alter table table_name ADD INDEX [index_name] (index_col_name,...)

  添加主键(索引)ALTER TABLE 表名 ADDPRIMARY KEY(列名,..);联合主键

l删除索引
DROP INDEX index_name ONtbl_name;
alter table table_name drop indexindex_name;

  删除主键(索引)比较特别:alter tablet_b drop primary key;

l查询索引(均可)
show index(es) fromtable_name;
show keys from table_name;
desctable_Name;

索引的使用

l查询要使用索引最重要的条件是查询条件中需要使用索引。

l下列几种情况下有可能使用到索引：
1，对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2，对于使用like的查询，查询如果是 ‘%aaa’不会使用到索引

  ‘aaa%’会使用到索引。

l下列的表将不使用索引：
1，如果条件中有or，即使其中有条件带索引也不会使用。
2，对于多列索引，不是使用的第一部分，则不会使用索引。
3，like查询是以%开头
4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’)
5，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

l查看索引的使用情况
show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

lMySQL客户端连接成功后，通过使用show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。
下面的例子：
show status like‘Com_%’;
其中Com_XXX表示XXX语句所执行的次数。
重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

l还有几个常用的参数便于用户了解数据库的基本情况。
Connections：试图连接MySQL服务器的次数
Uptime：服务器工作的时间（单位秒）
Slow_queries：慢查询的次数 (默认是慢查询时间10s)

SQL语句优化-定位慢查询

l在默认情况下mysql不记录慢查询日志，需要在启动的时候指定

 bin\mysqld.exe - -safe-mode  - -slow-query-log [mysql5.5 可以在my.ini指定]

 bin\mysqld.exe–log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

l通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。
show variables like ‘long_query_time’;
set long_query_time=2;

l测试语句

 select * from empe,dept d where e.empno=123451  and e.deptno=d.deptno;

 查看慢查询日志：默认为数据目录data中的host-name-slow.log。低版本的mysql需要通过在开启mysql时使用--log-slow-queries[=file_name]来配置

SQL语句优化-explain分析问题  

nExplain select * from empwhere ename=“zrlcHd”
会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描出的行数(估算的行数)
Extra:执行情况的描述和说明

l大批量插入数据(MySql管理员) 了解
对于MyISAM：
alter table table_name disable keys;
loading data//insert语句;
alter table table_name enable keys;
对于Innodb：
1，将要导入的数据按照主键排序
2，setunique_checks=0,关闭唯一性校验。
3，setautocommit=0,关闭自动提交。

l优化groupby 语句
默认情况，MySQL对所有的groupby col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

l有些情况下，可以使用连接来替代子查询。
因为使用join，MySQL不需要在内存中创建临时表。(讲解)

l如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引(与环境相关 讲解)

select * from 表名 where条件1=‘’ or 条件2=‘tt’

选择合适的数据类型

l在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性。deciaml不要用float

l对于存储引擎是MyISAM的数据库,如果经常做删除和修改记录的操作，要定时执行optimize table table_name;功能对表进行碎片整理。

l日期类型要根据实际需要选择能够满足应用的最小存储的早期类型

createtable bbs(id int,con varchar(1024) , pub_timeint);

date(‘Ymd’,时间-3*24*60*60); 2038年-1-19