MySQL基础

首先下载mysql5.5.30  下载地址http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.30-win32.zip

解压到E盘重命名为MySQL5.5.30进入bin目录下启动mysqld.exe这是mysql服务进程启动之后，在命令窗口登陆mysql

第一次登陆mysql不需要密码所以直接输入命令 mysql  -u  root  -p  下面则会提示输入密码直接回车即可。

如果是登陆远程主机，假设主机为192.168.1.100用户名为root密码为123456 ，只需输入mysql   -h  192.168.1.100  -u   root  -p  123456即可。

下面介绍下常用的命令：

exit（quit）//退出mysql界面

show databases  //显示所有的数据库

show tables //显示所有的表

use   `dbName`  //使用指定的数据库

create  database   `dbName` //创建指定的数据库

create  database   if  not  exists  `dbName`  //如果不存在指定的表则创建（推荐）

drop  database  `dbName`  //删除指定的数据库

drop  database    if  exists   `dbName` //如果存在指定的表则删除（推荐）

delimiter //；将结束标志改为//

show   processlist    //显示哪些线程正在运行（如果您得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接show processlist;只列出前100条，如果想全列出请使用show full processlist;）这个命令中最关键的就是state列，字段解释查询手则

show  status //显示数据库状态  字段解释查询手则

show  variables  (like  'string')//显示mysql里的一些值，详细查询手则

show index from `tbName`;//查看表的索引

drop  index  indexName  on  `tbName`;//删除表的索引

rename  table  table1 to table2,table3 to table1;//重命名表名，可以是一个和多个（rename table `db1`.`table1` to `db2`.`table1`//如果不是同个数据库则表示复制表）

 

mysql里默认的有四个数据库information_schema、mysql、performance_schema、test，MySQL 5.5版本新增一个存储引擎：命名PERFORMANCE_SCHEMA  ,主要用于收集数据库服务器性能参数，Performance的开启很简单，在my.cnf中[mysqld]加入performanc_schema，检查性能数据库是否启动的命令:show  variables  like   'performance_schema'

test数据库是用来测试的没有什么作用可以删除，mysql数据库是用来存放mysql用户信息以及本身数据库信息

首先要对mysql的用户进行管理所以进入mysql数据库的user表查看user表里的信息，因为user表里的字段比较多所以用垂直显示命令为 select * from  user\G，一定要大写的G ，简单的创建用户则为create  user  'userName'，那么user表会多一条记录，简单删除可以为drop  user  'userName'，更精确的授权语句就要用到grant和revoke如下有几个简单例子：

grant  all   on  *.*  to  'username'@'www.host.com'   identified  by  'username' //给username的用户在www.host.com域里授予所有权限还可以写为192.168.1.%（表示在这个ip段内的都行）或者%.host.com（在这个域下面的都行）

grant  select,insert,update,delete  on 'dbName'.'users'  to  'username'@'%'  identified  by 'username'//给username用户在dbname数据库的users表并且在任何主机上授予增删改查权限。

从mysql4.02版本以后可以对mysql用户每小时服务连接次数，更新次数，查询次数，用户连接数设置一个上限如：

grant  all  on  *.*  to  'username'@'%'    identified  by 'username'  with  MAX_QUERIES_PER_HOUR   100   MAX_CONNECTIONS_PER_HOUR  10  MAX_UPDATE_PER_HOUR  50  MAX_USER_CONNECTIONS 0

revoke  all  privileges,grant  option  from 'username'//回收所有username用户的权限

回收权限也可以用delete语句如：delete from  `mysql`.`user` where user='username';但是delete如revoke的区别在于delete虽然删除了记录但是内存里还是有username用户的信息所以这是username用户还可以登录，所以delete后必须用flush命令(flush privileges)重新加载权限表，而revoke是自动重新加载。

更改用户的密码：可以用set  password  for  'username'@'localhost' = password('123456');也可以用grant  usage  on  *.*  to 'username' identified  by  '123456';

如果root密码忘了怎么办：1、关闭mysql  2、在命令窗口进入mysql的bin目录下输入命令：mysqld  --skip_grant_tables来启动mysql，windows下重开一个命令窗口进入bin目录然后键入命令：mysql  -u root这时不需要密码  3、可以输入flush  privileges命令加载权限表(不加载修改密码不会成功)并且来防止其他人在此时登录。  最后修改root密码后重启mysql即可。(介绍两个dos命令  用tasklist来查看有哪些进程在运行，tskill用来杀死某个进程如：tskill  mysqld)

 

Mysql的优化：

1、选取合适的字段：在创建表的时候选取合适的字段，在满足要求的情况下选择字段宽度最小的，如：邮编选择char(6)，并且尽可能的将字段设置为NOT NULL，数据库在比较的时候就不会比较NULL值，某些文本字段比如“省份”、“性别”，最好用ENUM型，因为mysql中ENUM型别当作数值型来处理，比较起来速度很快。

2、连接负载：back_log参数用来设置mysql所有连接数，该值指示了mysql暂时停止回答新请求之前的短时间内有多少个请求放在堆栈中，当然不是值越大越好，如果超过了操作系统中本身队列的大小那就无效了。

3、最大并发连接数：该值可以通过show  status  like 'max_used_connections'来查看最大并发连接数，但是如果值设置越大，内存消耗越多。

4、守护进程用户：mysql守护进程应当按非root用户权限运行，攻击者侵入服务器后能将破坏程度降到最低，

5、表的优化：可以使用optimize table tableName命令来优化，因为数据用久了会产生碎片。

6、使用索引：对经常需要比较，查询的字段设置索引，这样更高效可以使用explain命令来检测索引的信息。

7、优化查询语句：将查询语句优化减小查询次数。

8、使用默认值：进来设置字段的默认值，如果插入的数据都不同则不必设置，设置默认值可以提高insert效率。

 

mysql数据库连接：mysql、mysqli、pdo、adodb。

mysql连接示例

<?php
mysql_connect("localhost","root","123456") or die ('数据库连接失败');
mysql_query("set names 'utf8'");
mysql_select_db("dbName");
$result = mysql_query("select * from tbName");
while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
print_r($row);
}
mysql_close();
?>

mysql_pconnect()是打开一个永久连接，mysql_close()不能关闭它。mysql_fetch_array()的第二个参数类型有三个MYSQL_ASSOC、MYSQL_NUM、MYSQL_BOTH

 

 

alter table tbName auto_increment = 100；修改tbname表的增量为100

show character set；查看mysql数据库支持的字符集

show collation like 'utf8%'；查看mysql校验字符集（后缀的含义_ci大小写不敏感、_cs大小写敏感、_bin二元）

 

数据库索引的使用：

索引的创建能使查询速度更快，不同的表引擎索引存放位置不同，MyISAM类型就存放在数据文件里，索引值存放在索引文件里，对于InnoDB它的索引的数据行与索引值存放在同一个文件中，同一个表空间中。

索引分为四类：主键索引、唯一索引、常规索引、全文索引、

 复合索引例如索引顺序为c1、c2、c3，查询的时候查询c2或c3或c2+c3为条件，那么基本上查询不到创建的复合索引，用到复合索引的情况应为：c1、c1+c2、c1+c2+c3这叫充分利用最左侧前缀。

 

事务处理：

非事务处理的实现可以锁定数据表来解决并发类问题。（即在执行操作的开始和结束分别加上lock tables `tbName` write和unlock tables `tbName` write 语句）

InnoDB和BDB类型的表是支持事务处理的。共有begin、commit、rollback语句构成如：

begin;//开始事务
//插入语句或更新语句或删除语句
commit;//提交事务 或 rollback;//回滚事务
要么事务提交要么事务回滚，事务才结束。

set  autocommit = 0或1分别为：关闭或打开自动提交事务，默认是开启的，如果关闭了当插入、更新、删除后要么commit要么rollback。

 

alter database  `tbName` character  set  utf8  collate  utf8_general_ci;

 

Explain的用法

explain后面一般都是查询语句，查询后的字段有id、select_type、table、type、possible_keys、key、ref、rows、extra。

id：表示Mysql查询的序列号。

select_type：表示查询类型主要有以下几种《》

table：表示查询到的数据库表名称。

type：对表的访问方式，有以下几种

《system，系统表，表示只有一行数据》

《const，读常量，最多只会有一条匹配记录》

《eq_ref，最多匹配一条记录，一般是通过主键或唯一索引》

《ref，表示关联但不是基于主键或唯一索引的查询》

《index_merge，查询中同时使用两个(或更多)索引，然后对索引结果进行合并，再读取表数据》

《index_subquery，子查询中的返回结果字段是一个索引，但不是主键或唯一索引》

《unique_subquery，子查询中的返回结果字段组合是主键或唯一约束》

《rang，使用索引进行范围扫描》

《index，全索引扫描，找扫描索引树，比all快》

《all，全表扫描》

possible_keys：可以利用到的索引，如果没有任何索引则为NULL

key：mysql解释器实际使用的索引。

key_len：被选中索引的索引长度。

ref：字段显示哪些字段和常量被用来和key配合从表中查询记录。

rows：显示mysql认为它执行查询时必须检查的行数。

extra：查询中额外细节。如果出现using filesort：mysql匹配完where语句还需要进行额外的排序才得到结果。所以这种情况建议优化。using temporary，mysql需要创建一个临时表来容纳结果。这种情况建议优化。

type的好坏顺序为：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all;

 

存储过程：

存储过程的参数一般由三部分组成in、out和inout。in表示传入、out表示传出、inout表示既可以传入也可以传出。

>delimiter //
>create  proceduce  pro_id(in  inputid int)
>begin
>select * from tbName  where id = inputid ;
>end  //
>delimiter  ; 

调用则为：call  pro_id(1);删除为：drop  procedure  pro_id;

其他命令：show  procedure  status、show  create  procedure pro_id。

 

 

数据库分区：水平分区和垂直分区。