mysql

数据库操作
show databases;
create database mdb;
drop database mdb;
use mdb;
show table status [from mdb] [like ‘mtb%’] [\G] //查询表
grant 权限 on mdb.* to user [with grant option] ; //授予权限
revoke 权限 on mdb.* to user ; //取消权限
show grants [for user] ;
show variables like “%character%”; //显示编码
exit (退出)

表操作
create table mtb(id int primary key auto_increment,name varchar(50),number int)engine=innodb default charset=utf8; //创建表 引擎i为nnodb,默认字符utf8
show tables;
show columns from mtb;
drop table mtb;
truncate table mtb; //删除表内数据
optimize table mtb; //立即释放表磁盘空间,可在删除数据后使用
alter table mtb rename to tb; //修改表名
alter table mtb engine=myisam; //修改存储引擎
alter table mtb modify number int first | after name; //修改字段位置
alter table mtb drop foreign key keyname; //删除外键

列操作
alter table mtb modify name varchar(50) default ‘’; //修改字段结构
alter table mtb change name nm varchar(50); //修改字段名称及结构
alter table mtb drop nm;
alter table mtb add name varchar(50);

数据操作
insert mtb (name,number’) values (“mname”,1)
delete from mtb [where Clause];
update mtb set name=test,number=1 [where Clause]
select * from mtb [limit N] [offset M]; 从m+1开始n条数据; 等同于limit m, n

事务
bigin;
savepoint point_name;
rollback [to point_name];
commit;
保留点在commit 或 rollback后自动释放, 也可主动释放 release save point point_name;

索引
查询索引:
show index from mtb \G
创建索引:unique关键词可创建唯一索引,需求列值除null外唯一
create [unique] index index_name on mtb(name);
alter table mtb add index index_name(name);
create table mtb2(name varchar(50), index index_name(name));
删除索引:
drop index index_name on mtb;
alter table mtb drop index index_name;
主键索引:
primary key, 常用于id;
单表唯一,列值唯一不为null

临时表temporary
create temporary table temp_tb(name varchar(50));

元信息
SELECT VERSION( ) 服务器版本信息
SELECT DATABASE( ) 当前数据库名 (或者返回空)
SELECT USER( ) 当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES 服务器配置变量

导出

mysqldump -uroot -p --all-databases > dump.txt //备份所有数据库

mysqldump -uroot -p --all-databases mdb1 mdb2  > dump.txt //备份多个数据库

mysqldump -uroot -p  mdb  > dump.txt //备份单个数据库

mysqldump -uroot -p mdb mtb > dump.txt  //备份数据库表

导入

mysql -uroot -p < dump.txt  //导入全部数据库

mysql -uroot -p mdb  < dump.txt  //导入指定数据库或者表(根据dump.txt文件)

使用source导入数据库表
mysql -uroot -p    
123456             //输入密码
use mtb            //使用表
source dump.txt    //导入数据库表

其它主机:

mysqldump -h other-host.com -P port -uroot -p123456 database_name > dump.txt

分页查询:
select * from mtb limit 10000,10; //此方式offset值大时查询耗时高
通过id优化:
select * from mtb where id >=(select id from mtb limit 10000,1) limit 10;

临时表
create temporary table temp_tb(select * from mtb limit 0, 100); //使用查询数据创建临时表

复制表
show create table mtb; //查询建表语句
create table mtb_clone like mtb; //类比创建表
insert mtb_clone select * from mtb; //插入数据
create table mtb_clone (select * from mtb); //使用查询数据创建表
create table mtb_clone (number varchar(50)) (select * from mtb);//查询数据创建表,且定义字段信息

mysql中不能根据表的查询结果直接修改原表
You can’t specify target table for update in FROM clause
错误示例:
delete from mtb where id = (select max(id) from mtb);
这种情况下需要将查询结果封装到另一个表中:
delete from mtb where id = (select mid from (select max(id) mid from mtb) tb);

insert ignore 根据唯一约束判断, 重复忽略, 不重复则插入
insert ignore into mtb(name) values (“david”);

操作符查询执行顺序
from
[inner/left/right] join on
where
group by [with rollup]
having
window functions
select
distinct
union
order by desc/asc
limit offset

like :
% 表示0个或多个字符
_ 表示任意单个字符
[ ] 含有括号内字符中的一个
[^] 不是括号内字符中的一个

union :
连接两表查询结果,
查询的列数量要一致, 列名与前者相同
union all 返回所有结果 可重复
union distinct 删除重复数据, 默认union已删除

函数
replace(field,‘old-string’,‘new-string’)
length(field)
count(field)
sum(field)
avg(field)
convert(field using gbk) // 转码 (gbk编码可用于拼音排序等)
coalesce (a,b,c) //a为空则取b,b为空则取c

变量
select @i:=3 //赋值变量i=3
select @i: //获取值
select @i=3 //判断等式是否成立,正确返回1,错误返回0,未赋值返回null

编号
select (@i:=@i+1),mtb.* from (select @i:=0) k,mtb;
// select @i:=0 定义@i:=0
//(select @i:=0) [as] k 这里需要有alias
//select (@i:=@i+1) 每次查询加1

null处理
is null , is not null , ifnull(field, value),
<=> 比较操作符, 相等或都为null时返回true
注意null与其它值比较时会返回null,如null=null 返回null

regexp 正则匹配