MySQL知识杂记

MySQL

 

数据库里的两个引擎

myISAM          数据访问索引方法（索引顺序存取方法）

INNODB          事务

 

外挂插件式存储引擎是MySQL的特点

 

mysql的索引在创建表时默认有哪些：

index type

default

btree

hash

rtree

 

存储引擎

决定了数据库的查找、删除等的驱动方式

 

服务器最大连接数

65536-1025           （-减号）

MySQL ServerInstance Configuration Wizard   （安装时的配置）

引擎选择处，第一项：既有myISAM又有INODB

第二项：既有myISAM又有INODB，INODB被优化

第三项：没有事务引擎

Enable Strict Mode

在输入空字符串时也能视为空处理,'',''

create an anonymous account

创建匿名用户

enable root access from remote machines

启用root准许它从远程客户端机器上面进行访问

安全起见，不要选择此项

 

 

MySQL内的UTF8没有中横线

超级管理员的用户名是：root

 

SQL           结构化查询语言

 

概念层次

数据库系统，数据库服务器

数据库

数据表        (数据表是真正存放数据的地方，每个数据表可以根据不同的业务来创建                  不同的结构)

 

 

 

 

DDL

数据库定义语言

DML

数据库操作语言

增加

修改

删除

DQL

数据库查询语言

 

SQL命令

database

create database DBname;        创建数据库语法

show databases;                     查看所有数据库

drop database DBname;                删除数据库

use DBname;                          使用数据库，切换数据库

 

table

show tables;

show create table 表名;                 查看创建对应表的命令

create table 表名(字段1 类型1(长度)，字段2  类型2);   创建表

// create table one(id int , name varchar(30) , sex char(20));

engine=innodb default charset=utf8     加在表命令后面，引擎和字符集设置

describe 或 desc     表名                           查看表信息、列信息

 

系统默认数据库：

Mysql                    mysql的服务器的信息

information _schema    运行信息

test                            测试库【准许所有用户访问，建议删除】

cluster                   集群信息【没设置集群则不存在】

 

数据类型:

varchar 字符串类型，必须加长度

char           字符串类型，必须加长度

int       可加长度，也可不加

 

sql错误信息有两种

1、已知错误，能提出具体的提示信息

can't create database '***',databaseexists

2、位置，语法错误，会给出near提示

near

 

DDL

修改字段的相关SQL语法:

 

alter table 表名 修改的关键字       关键字之后的语法        可选的【after|first】

 

修改字符集

alter table users default charset=utf8;

修改引擎

alter table engine=innodb;

 

关键字：

modify      修改字段属性

alter table kelingkeli modify id int(8);        #将id字段的int(10)改为int(8)

alter table kelingkeli modify id int(8)default 888;        #修改id字段的默认值

alter table kelingkeli modify id char(8)first;           #将id移动到第一列并改变属性

alter table kelingkeli modify id int(8)after username;        #移动到username

 

add           增加字段

alter table kelingkeli add ip int(10)not null;          #不加位置的时候，自动为最后一个字段之后

alter table kelingkeli add zctimeint(10) after id;     #在id之后增加新的字段

alter table users add some int(10) notnull first;

 

drop         删除字段

alter table users drop ip;

 

change           修改字段属性，也可以修改字段名

change 原名 新名类型 位置

alter table users change ip newip int(8)after id;

 

rename           修改表名

alter table users rename customer;

 

 

 

 

 

DML

增加

插入一条

insert into  表名(字段1，字段2，字段n) values(值1，值2，值n)

 

插入多条

insert into  表(字段1，字段2，字段n) values(值1，值2，值n),(值1，值2，值n),(值1，值2，值n)...........

insert into one(id,name,sex)values(4,'mm','man'),(5,'nn','women'),(6,'cc','some');

 

省略表字段的插入方法

insert into  表名 values(与表的字段顺序关系一样的，一个也不能少的值);

 

变形形式

1、创建表的时候插入数据

create table hello select * from one;     #把one表所有记录查出赋值给创建的表hello

2、查询数据插入到指定表中

insert into two select * from one;        #将one表所有记录查出插入到表two

 

在插入的时候结构和数据类型要一致

 

如果在插入时，表当中如果有可空字段和自动增长字段，可以不用写，能够减少SQL语句的复杂性；它们的值会变为空或自动增长；

自动增长

需要将自动增长列设为主键【primary key】或 唯一键

create table users(

id int primary key auto_increment,

username char(30) not null default'fuck',

password char(32) null

)engine=myisam default charset=latin1,

自动默认值

如果表示可空和非空的有默认值的字段，在插入数据的时候，如果不写该字段会自动显示为默认值

 

 

修改、更新(update)

 

update  表名 set 字段1 =值1，字段2=值2，字段n=值n 必须加where条件1 and 条件2 and 条件n

update users set name='zhang',sex='nv' where id=3and name<=>null;

update users set name='zhang' where id>5;

 

where name <=> null   #<=>是安全等于，只有此符号能跟null做比较

 

更新时，同时更新两个表或多个表，如果条件准许，则把一个表的字段的值，赋值给另外一个表的某个字段

update one,hit setone.name=hit.name where one.id=hit.id;

 

应用

增加论坛帖子点击数

update hit set hits=hits+1 where id=1;

 

 

删除

 

delete from 表名 where

 

 

DQL

查询

 

select 字段as 别名 from 表名 【where 条件】 order by 字段 asc|desc

 

select * from zd order by id desc;

 

排序

order by 字段1 排序关键词,字段2 排序关键词,字段n 排序关键词

在order by排序当中默认为asc升序

 

 

置顶(以多个字段排序)

update zd set istop=1 where id=2;

select * from zd order by istop desc,iddesc;

 

限制数量

limit offset (偏移量) ，length(查询数量)

从什么地方开始找，找几个值

select * from table limit 2; #让记录只出现2条

select * from table limit 0,2;    #同上

 

已知：用户表有18条记录，每页显示5条记录

如何分页：   ceil(18/5)得到4页

第1页   偏移0   取5条

第2页   偏移5   取5条

第3页   偏移15 取5条

第5也   偏移15 取5条

偏移值 =(页码-1)*每页显示条数

 

group by    分组

如果有相同记录，只取出同组中一条记录

create table fz(name varchar(30),

sf varchar(30)

);

select * from fz group by sf;

 

 

having  二次过滤

加了where条件之后还可以加having

where从未知结果中筛选取到结果集，having从已知结果中二次筛选取目标

 

select * from one where id>1 havingname='namex';

 

 

多表联合查询

笛卡尔乘积

 

select * from user,tiezi whereuser.id=tiezi.uid;

 

#别名

select u.name,t,.id,t.title,t.uid fromuser as u,tiezi as t where u.id=t.uid;

 

select u.name as uname,t.id as tid,t.title as ttitle, t.uid as tuid from user as u,tiezi as t where u.id=t.uid;

 

 

查询子集

 

#麻烦方法   select* from hello where id=2 or id=5 or id=7;

 

在多选删除的时候，有一个复选框，如果点的都是ID，最终传过去的是一个数组，数组格式如下：

$arr = Array(2,5,7)

#想要的效果select * from hello where id in(2,5,7);

join(',',$arr);

select * from hello where id in($arr);

 

下面两句话等价：

select * from hello where id in(2);

select * from hello where id=2;

 

在子集当中可以直接插入sql语句

select * from hello where id in(selectid from user);

 

多选删除

 delete from user where id in(1,2,3);

 

删除最大值

 

 

 

1、如果不写字段可以写上*查询所有字段

 

2、结果中的顺序关系，以select语句中字段顺序一样

select name,sex,id from one;

 

3、输出别名

select name as '姓名',sex as '性别',id as 'userid'

 

4、在写的时候最好写上字段名，需要哪些字段写哪些，这样能够减少网络开销和I/O开销

 

5、where条件   <  > <=  >=  !=  and  or

 

6、distinct只显示不重复的记录，重复记录只会显示出来一条

查询时不显示重复记录

select distinct id from table

7、可以在字段处或者where条件等地方用上函数

avg

sum

count

max

select max(id) from one

min

 

 

 

 

 

 

 

 

 

MySQL运算符

select 1+1,5*2,4/2,10%9,9-8;

select 9 div 3;        #除法

select mod(10,2);   #取余

 

<=> 安全的等于，用等号是算不出空的

select * from hello where name=null;        #这样算不出空的name字段

select * from hello wherename<=>null;    #正确方法

 

 

between     查询某个区间

id between 5 and 8;

select * from hello where id between 5and 8;等价于select * from hello where id>=5 and id<=8;

 

is null        某个字段的值为空

select * from hello where id is null;

is not null 某个字段的值不准许为空

select * from hello where id is notnull;

 

like      模糊查询，匹配，通配符%

字段like后面接上一个字符串的'%要查询的关键词%',表示前面可有可无的任意字     符

select * from hello where password like'%口%';

like有个问题就是速度慢，当查询会话过多，服务器无法承载

Sphinx 分布式索引搜索服务器，将查询分流至其他服务器，可以解决此问题

 

Regexp 、RLIKE

被匹配 Regexp 正则表达式    正则匹配的运算符

select '[e-mail]' regexp '[a-z0-9A-Z-]+@'

 

逻辑运算符

 

位运算符

&

|

^

~

>> 位右移   相当于除以2

<< 位左移   相当于乘以2

 

 

 

注意：

命令不区分大小写，但通常把命令大写，名字小写，为了方便区分

 

IP地址可以使用整型存

 

没有网络没有手册的时候，想快速查看语法，直接使用?空格+关键字

 

=号的作用 set 中赋值，where中判断等于否

 

在写update和delete的时候一定要加where条件，不然就把整个表的所有记录都改变了

 

delete 后面直接跟from，可以把delete from看成一个整体

 

使用多表查询或多表更新时，一定要把两个表名都写上

 

 

 

MySQL函数

concat()     字符串连接

select concat(id,'-',name) from table;

insert(str,start,length,replace)       字符串替换函数

将字符串str从start位置到length出替换为replace

select insert('abcdefghijk',2,10,'y');

lower('str') 变为小写     

upper()      变为大写

left            返回最左边的指定个字符

select left('sxxdedsees,fdsfeesf',5)       

right                返回最右边的指定个字符

lpad(str,n,pad)      对最左边填充，填充至指定长度个字符

rpad(str,n,pad)       对最右边填充，填充至指定长度个字符

ltrim                清除字符串最左边空格

rtrim               清除字符串最右边空格

trim                清除左右两边的空格

repeat        重复填充

select repeat('x',10);

replace       替换字符串

select repeat('strstrstrstr','s','a')     将str中的s替换为a

strcmp       比较字符串

selectstrcmp('a','b')          #-1

 

substring('str',start,length)      截取字符串

select substring('abcdefghijklmn',2,10);

 

abs      求绝对值

ceil       进一法取整

floor          舍去法取整

rand          随机数

round   取余

truncate(x,y);   返回x截断为y位小数的结果

select truncate(1.54242,2);     #返回1.54

 

日期时间函数

curdate

curtime      当前时间

year                返回当前时间的年份

select year(now());      

week          返回当前时间的星期数

hour                返回当日小时数

minute       返回分钟

monthname     返回当前月份英文名

date_format(now(),'%j')    格式化返回时间

 

date_add() 返回增加时间之后的时间

select date_add(now(),interval +1,year);    #1年之后的时间

select date_add(now(),interval -1,year);           #1年之前的时间

 

datediff(expr,expr2);    返回起始时间和结束时间的间隔天数

select datediff(now(),'2011-3-22');

 

now

unix_timestamp(now())           当前时间unix时间戳

from_unixtime       根据unix时间戳取得时间

select from_unixtime(12312341);

 

流程控制函数

 

if(value,'t','f')         value为真返回t，为假返回f

select if(salary,'有工资','没工资') from table;

ifnull(value,'str');    value为空则返回str值

select ifnull(salary,'xxx');

case when        条件判断

select case when salary(字段名)<=500 then '小于500' else '判断失败' end from table ;

select case salary when 3200 then '工资太低' when 5000 then '马马乎乎' else '其他情况';

 

其他函数

 

database()  返回当前使用的库名

select database();

version()          当前版本

user()        当前用户和所在主机

inet_aton()    返回当前IP地址的数字表示。可以把ip地址变换为整数

与php当中的ip2long功能一样（ip转换成长整型）

inet_ntoa() 将数字ip转换为ip地址

与php当中的long2ip功能一样

password         返回当前str的加密版本

md5                返回字符串str 的md5值

 

 

 

引擎

重点了解myisam和innodb的不同

 

show engines 查看支持的引擎

 

myisam

myisam是mysql的默认存储引擎，myisam不支持事务，也不支持外键，其优势就是访问速度快，对事务完整性没有要求或者以select，insert为主的应用其他都可以使用。

使用该引擎可以将索引文件放在不同的目录下，平均硬盘的i/o获得更高的访问速度

myisam引擎产生三个文件：

 

.frm                存储结构的文件

.myi                存储索引的文件

.myd          存储数据的文件

 

myisam表容易损坏可以使用命令repair     table来修复表，该命令可以用在论坛后台里的数据库优化

 

特点：产生大量空洞和碎片 optimize  table (删除数据记录后，.myd文件大小不会改变，会被好多碎片填充)

 

 

 

 

 

myisam的3种不同存储方式

静态表（固定长度）

静态表中的所有字段都是非变长字段，每个记录长度都是固定的，忧点是存储非常迅速，容易缓存，出现故障容易修复

动态表

压缩表

 

innodb

同时插入多个数据的时候返回第一个插入的id

memory    临时的内存

创建一个临时的内存表：create table aoe engine=memory select *from antoher table;

速度快，使用后删除即可

 

 

特点

MyISAM

InnoDB

MEMORY

存储限制

有

64TB

有

事务安全

 

支持

 

锁机制

表锁

行锁

表锁

B树索引

支持

支持

支持

哈希索引

 

 

支持

全文索引

支持

 

 

集群索引

 

支持

支持

数据缓存

 

支持

支持

索引缓存

支持

支持

 

数据可压缩

支持

 

 

空间使用

低

高

N/A

内存使用

低

高

中等

批量插入速度

高

低

高

支持外键

 

支持

 

 

 

 

 

 

 

 

 

 

 

 

mysql支持的数据类型

可以是用 ? 类型名 查看数据范围

 

数值类型：   整型、浮点

字符串类型

时间类型

 

字符串类型

char 定长类型，无论存多少长度，都按设定长度插入

密码使用char存（md5之后长度变为32）

用户名使用char存（不超过15，经常用来做搜索）

varchar 变长类型，按照插入数据自动设定长度

 

blob    

用来存2进制文件（电影、图片）

enum

枚举类型

可以强制指定只准插入某些值，有约定性

create table sex(my enum('f','m'));

set

集合类型

枚举只能固定存一个值，而set可以同时插入多个值

create table bian(my set('m','f','t'));

insert into bian values('m','f');

 

命令行管理

关闭服务：Net stop mysql

开启服务：Net start mysql

可以用compmgmt.msc  命令打开计算机电脑【管理】

 

登录数据库:

mysql -uroot -p123456

 

Your MySQL connection id is 6

MySQL连接次数

 

-h  -help         获得帮助

quit      或 exit  退出

\c              输入命令时用来清除之前输入（清除预先写好的命令）

;或\g          执行命令

 

 

如何在MySQL插入中文

 

 

工具管理

mysql front 可以将excel或access数据导入数据库

mysql administrator     强大的数据库备份功能，可以热备份(不停止服务器就可备份)

 

restore还原

 

 

 

utf8-general-ci

 

set names utf8;      #my.ini 客户端，结果集，连接字符集，校验字符集

 

 

索引     

 

Mysql的索引在创建表的时候可选的类型有哪些：

 

Hash

Btree

Rtree

 

主键索引      快速定位数据，相当于书中的页码

 

索引           就相当于目录，但是数据库当中的索引能够实时更新，让数据的查找                      速度更快(将所有设为index的列的页面建立成一个目录)

 

where子句中的字段设为索引

 

全文索引      能够让搜索的效率变的更高

 

唯一索引      会检测这个数据是否已经存在，如果已经存在禁止再次插入相同内容

 

 

散列和md5

创建2两个列，一个列时需要取的内容，另一列是md5加密后的定长数据，查找时查找散列速度快

解决字符集问题

一，html的页面编码utf-8

二，meta 字符集也弄为utf-8

三，php脚本也设为utf-8

四，如果有header头也设为utf-8

五，库，表，列字符集全部统一

六，客户端

七，结果字集

八，连接字符集

九，校验字符集

PHP连接MySQL数据库

 

一、连接数据库

mysql_connect()非持久连接       mysql_pconnect()持久连接

二、判断错误

mysql_errno()  返回错误编码      mysql_error()返回操作产生的文本错误信息

传资源参数时，就检查该资源的错误信息

当不传资源参数时，只检查距离最近的资源的错误信息

 

if(mysql_errno()){

 

           exit('对不起，数据库连接失败!错误信息——'.mysql_error());

}

 

三、选择库

mysql_select_db()

四、设置字符集

mysql_set_charset('utf8');

五、准备SQL语句

sql语句

$sql = 'insert into ';

六、执行SQL语句

mixed mysql_query(); //返回bool(增删该)或结果集(查)

$result = mysql_query($sql);

 

echo mysql_insert_id();           //返回上一步insert操作生成的自增id

七、判断是否执行正常或者是遍历数据

if($result){

echo '执行成功';

}else{

echo '执行失败';

}

 

mysql_affected_rows — 取得前一次 MySQL 操作所影响的记录行数

if($result &&mysql_affected_rows())

八、关闭数据库连接

mysql_close();或mysql_close($conn);

 

 

函数：

mysql_fetch_array       传入一个结果集返回索引和关联在一起的数组，索引下标为字段的顺序，而关联下标为字段名

如果只想使用索引数组，可以使用第二个参数MYSQL_NUM，mysql_fetch_array($result,MYSQL_NUM);         与mysql_fetch_row()函数的效果一样，可以理解为等价

如果只想使用关联数组，可以使用第二个参数MYSQL_ASSOC，mysql_fetch_array($result,MYSQL_ASSOC); 与mysql_fetch_assoc()函数的效果一样。

默认的mysql_fetch_array()  的默认参数为MYSQL_BOTH;

 

 

mysql_fetch_object()

 

mysql_num_rows($result);      //返回查询出的行数

 

mysql_stat       //取得当前系统状态

 

mysql_fetch_field   遍历字段

while($o = mysql_fetch_field($result)){

echo $o->name;

}

 

分页：【重点】

     

//总页数=条数/每页显示数

 

// 偏移量=(页码数-1)*每页显示数

     

      //18条记录 5条  应该分出4页出来

      //

      //第1页  偏移    0    5

      //

      //第2页   偏移   5   5

      //

      //第3页         10    5

      //

      //第4页         15   5

      //

 

为了搞定分页的页码，我人为的在index.php后面接上一个get传参：?page=1

如果用户只写了index.php呢，现在应该显示第几页，应该显示第1页。

 

如果没有get传参，传page的时候，我做一个if..判断

 

$page=Empty($_GET['page'])?1:(int)$_GET['page'];

 

 

注意：

insert into 插入多条数据的时候得到的是第一条记录的自增id，不是最后一条的自增id

 

mysql_query    只要sql语句正确，mysql服务器响应了，它就会返回true，即使这句话没有执行成功

 

高手和菜鸟的区别在于细节，设计表的时候写表前缀

usernamechar(15)

passwordchar(32)  (md5加密后32位)

 

 

 

视图view

视图是一张虚拟的表，视图发生修改，主表也发生修改

 

创建视图

create view someview as select * from tablewhere area='山东';

 

删除视图

DROP VIEW `v_menu`

 

查看视图

show create view `someview`

 

 

触发器

当删除或修改一个表时，将其更改记录到警报表

 

 

 

 

 

 

MySQL优化

存储过程

解决问题：

sql语句特别长

需要循环写入

 

语法：

create procedure 名字(输入/输出/输入输出  名字  类型，.......)

语句类型(contains sql | no sql |)

Begin

中间写sql语句

End

/*----------------------------------*/

delimiter $$                            修改定界符

//存储过程名为my44demo，输入2个类型(开始和结束，数据类型为整形)，输出1个类型fount为发现多少行，能够调到总共查询了多少行，数据类型为整形

createprocedure my44demo(in st int,in en int,out found int)

//进行读取的sql语句

reads sql data

begin

selectid,username,password,createtime,createip from user where id between st and en;

//将found_rows()赋值给found

selectfound_rows() into found;

end

$$

 

调用：

call my44demo(3,8,@x)     //输出数据用@x

select @x

 

查看：

show procedure status;

show procedure status\G;   //横向查看

show create procedure my44demo\G;  查看创建语句

 

 

 

创建函数

create function jiafa(one int,two int)

retruns int

no sql

begin

//定义变量

declare total int default 0;

select one+two into total;

return total;

end

$$

调用函数

select jiafa(5,8);

 

循环

create procedure myuser(in u varchar(50))

modifies sql data

begin

declare i int default 0;

while i<1000

do

insert into44demo_user(username,password,createtime,createip)

values(u,'123456',123,123);

set i=i+1;

end while;

end

 

 

 

mysql主从服务器【binlog日志】

 

把数据进行读写分流，写入专门找写入服务器，读取找读取服务器；

需要进行主从复制（二进制文件复制）

 

主服务器产生binlog日志(记录着增、删、改的一切)，然后同步到从服务器上

修改配置文件

vi /etc/my.cnf         配置文件

log-bin=mysql-bin       //将注释去掉

binlog-format=mixed

 

bin-log日志存储在/mysql/var/目录下，以mysql-bin.0000x格式存在

./mysqlbinlog命令专门用来查看binlog日志和恢复binlog日志

使用该命令时会有字符集错误，在查看binlog日志时使用一个命令,--no-defaults

 

reset master

重置主服务器，会将所有的log日志全部删掉，一切重新开始

show master status

查看主服务器状态，显示当前在哪个日志文件，哪个position上

flush logs

多一个mysql-bin.00006文件，刷新服务器的LOG日志，关闭原有LOG日志，开启一个新日志，在主从同步前，使用此命令

 

导入数据

mysql-uroot -p 44demo< ./var/mysqlbinlog --no-defaults

./mysqlbinlog--no-defaults ../var/mysql-bin.00003 | mysql -uroot -p

 

当日志中含有删除语句时，恢复为空解决方法

./mysqlbinlog--no-defaults --start-position=102 --stop-position=304../var/mysql-bin.00004|mysql -uroot -p

 

--satrt-position=    //写定位点

--stop-position=     //写结束定位点，到该点停止，不包括该点

--start-date=         //写开始时间

--stop-date=          //写结束时间

 

 

步骤：

1、停掉主服务器

2、将主服务器里面的库导出

3、设置bin-log开启

4、将库导入到从服务器

5、在主服务器上面设置权限

6、在从服务器上面和主上面改配置文件

7、同步成功

 

主：192.168.80.252

从:192.168.80.251

 

设置权限

Grant 权限 on 库.表 to 用户 @ 主机 identifiedby 密码

 

增加一个从服务器登陆用户

grantall on *.* to user@192.168.80.251identified by

 

在从服务器登陆

./mysql-uuser -p -h192.168.80.252

 

设置同步的权限为：

grantreplication slave on *.* to user@192.168.80.251identified by

 

导出数据

./mysqldump-uroot -p 库名 > 路径

 

ssh传输

scp把当前服务器的某文件通过SSH传送到另外的服务器上面

scp/root/hello.sql 192.168.80.251:/root/haha.sql

 

导入数据（从）

mysql-uroot -p 库名 <sql文件

 

在实验前，将服务器的整个状态，全部归零，两边都进行

resetmaster;

flushlogs;

 

服务器标号不能冲突vi/etc/my.conf

server-id= 1    //主服务器标号

server-id= 2    //从服务器标号

master-host= 192.168.80.252            //从

master-user=                                     //从

master-password                                //从

 

关掉进程

Pkillmysqld           //从

 

启动mysql

./mysqld_safe--user=mysql &        //从

 

 

查看从服务器状态

showslave status\G

一下2个参数啊都为yes才成功

slave_IO_Running:No

slave_SQL_Running:Yes

 

如果不同步，通过changemaster to 来进行指定主服务器的相关参数

关闭从服务器stopslave,然后进行修改

changemaster to

MASTER_HOST=    ,

MASTER_USER=    ,

MASTER_PASSWORD=      ;

开启从服务器startslave

 

PHP端控制，主服务器写，从服务器读

 

 

 

 

 

 

 

MySQL分库分表

解决单表数据量过大

1、mysql集群会自动完成分库分表

 

2、变形虫是一个数据库的中间件，使用中间件技术完成

类似：

新浪的云平台

 

3、自己写model类

 

截取username字符串,根据截取字符生成数据表，通过路由算法指定表

截取的是MD5值，然后通过hash进行平均

 

产生的问题：

分页怎么办

用nosql单独实现对某个用户的总数进行计数

如何进行扩展表，如原来有36张表，需要扩展到1024张表

数据存两份，汇总表写一份，分表写一份，汇总表只进行增删改，不进行查询，2份数据同步，需要扩展时直接从分表进行算法处理

 

 

 

 

 

 

 

MySQL分区

 

纵切

不常用的数据，放到NoSQL，一次查询的时候不去查这些内容

横切

分区与分表的区别

分区是在逻辑层面进行的水平分表，对于应用程序而言还是一张表

分库分表

分区

数值固定选择range,list,数值不固定使用hash

range   分区           values less than

list       分区           values in ()

hash     分区

 

 

 

 

 

 

 

MySQL优化

 

 

 

 

 

表复制

create table t2 like t1;        //复制表结构

insert into t2(name) select * from t1;        //复制表数据

 

replace into t2 select 1,"user4";      //当id冲突时insertinto会报错，而replaceinto不会报错

 

 

索引

 

alter table t2 add index in_name(name)     //添加索引

alter table t2 add unique un_age(age)        //创建唯一索引un_age

alter table t3 add primary key(id);             //创建主键索引

 

show index from 表名;                              //查看索引

 

alter table t3 drop primary key;                      //删除主键索引

alter table t2 drop index un_age;              //删除

 

 

 

插入多行数据

insert into t1(name)values('user1'),('user2'),('user3');

 

 

函数

//随机排序

select * from t1 order by rand();

 

//去最大最小id

select max(id) from t1;

 

//unix时间戳

select unix_timestamp();

//时间戳转成日期类型

select from_unixtime(1231244);

 

select month("2012-2-10")       //返回月份2

select datediff("2012-10-10","2012-10-15")     //返回两个日期相差时间

 

where date>2012-10-10and date<2012-10-15

 

 

 

预处理

 

 

事务处理

alter table t1 engine=innodb;        //改变引擎

set autocommit=0;                       //关闭自动提交

delete from t1;

//rollback;后还有数据

commit      //提交

 

savepoint p1;   创建还原点p1

rollback to p1;       //恢复还原点p1

回滚到之前的还原点，其后的还原点全部消失

 

 

存储

插入1000个用户

 

 

重排自增列表

truncate t1;     //删除表的全部记录，全部擦除，下次自增id从1开始，比delete速度快，delete是一行一行删

 

alter table t1 auto_increment=1;

 

 

 

 

select * from t1 where mail regexp"163|126"; //使用正则匹配网易邮箱

 

//分组聚合,withrollup语句进行合计

select class,sum(score) from t1 group byclass with rollup;

 

 

 

常用技巧

如何使用bit_and和bit_or进行用户购买商品和购买商品类别的计算

 

 

 

 

 

 

 

 

 

使用帮助查找命令关键字

? opti%                 //%补全

OPTIMIZE

 

 

 

sql语句优化

最直接的优化方法是增加索引，在频繁使用的字段里加索引，但不宜过多，增加索引过多，空间消耗巨大(索引将近是存储内容的2倍)

 

 

 

服务器运行以来查询的次数

show global status like"%com_select%";

登陆以来的查询次数

show status like "%com_select%";

 

show global status like"%com_insert%";

 

 

optimize table t2;   //优化表

表使用时间久，应该经常进行优化，该优化能将delete掉的数据空间回收