/*
Mysql基础操作(优化)
@auther: luowen
@time: 2013-07-26
1.mysql复制表机构和表数据
create table t1 like t2;
insert into t1 select * from t2;
2.mysql创建索引
method 1:
increate [unique] [index] [primary key] indexName on t1(name);
show index from t1;
drop index [unique] [index] [primary key]indexName on t1;
method 2:
alter table t1 add [index][unique][primary key] indexName (column);
alter table t1 drop [index][unique][primary key] indexName;
3.mysql 视图
create view v_t1 as select * from t1 where id > 3;
drop view v_t1;
4.mysql 内置函数
[字符串函数]
1.连接函数
concat select concat('luowe','wen') as luowen;
2.大小写转换
lcase select lcase('SDFSADF') as demo
ucase select ucase('asdfsadf') as demo2
3.长度 length
length select length('sdfsadfsadf');
4.去空
ltrim select ltrim(' sadfsdf')
rtrim select rtrim('asdfsaf ')
5.重复
repleat select repleat('xx',10)
6.替代
replace select replace('linux is very good','linux','php') ;
7.截取
substr select substr('linux is very good',pos,length); // pos 是从1开始
8.生成空格
space select space(10); select concat(space(10),'linux'); //在linux左侧添加了10个空格
[数学函数]
1.二进制函数
bin select bin(10);
2.取整
ceiling select ceiling(2.5) //3
floor select floor(2.5) //2
3.最大最小
max max(col); //聚合使用
min min(col);
4.平方根
sqrt select sqrt(2);
5.随机数
rand select rand(count); //count 选出几个 配合order by 可是实现随机排序
[日期函数]
1.curdate //当前时间
2.curtime
3.now();
4.unix_timestamp
5.week
6.year
7.datediff('日期1','日期2') //计算中间差了几天
5: mysql 的预处理
prepare preTable from "select * from t2 where id > ?";
set @i=3;
execute preTable using @i; //@i替代了prepare 语句中的?号
drop prepare preTable //删除预处理
6:mysql 事务(MyISAM不支持事务,innoDB支持)
set autocommit = 0; //打开事务
//alter table t2 engine innodb;//修改表引擎
delete from t2 where id < 1;
savepoint p1
delect form t2 where id < 3;
savepoint p2;
rollback to p2 //还原到p2的点
rollback //回滚
commit //提交
7:mysql 存储
procedure //一个小段的程序
\d // //将存储设置定界符
create procedure p1()
begin
set @i = 3
while @i <= 100 do
insert into t2(name) values(concat('user',@i));
end while;
end//
show create procedure p2 //查看存储
call p2 //呼叫执行p2
drop prcedure p2
8:mysql 触发器
trigger
\d // //修改定界符
create trigger tg1 before insert on t1 for each row
begin
insert into t2(id) value(new.id);
end
9:auto_increment 恢复1
truncate table; //已经恢复1
delete from table //没有恢复
alter table auto_increment = 1;
[mysql regex]
1.select name,email from table where email regexp '@163[.,]com$'
select name ,email from table from email like '%@163.com' or email like '%@163.com';
[sql optimize]
1.SQL优化步骤
MyISAM ENGINE
show status like 'Com_%'; //查看mysql curd 操作次数
show global status like 'Com_%';//查看服务器启动至今
INNODB ENGINE
show status like "innodb_rows"; //查看innodb引擎的记录
show Status like "slow_querys";//查看慢查询
[mysql 数据导入导出]
mysqldump -uroot -pluowen test > /home/test.sql
select * from table2 in outfile '/home/table2.sql';
load data infile "/home/table2.sql" into table2 t1;
alter table tableName disable keys//导入数据的时候,先关闭索引,就可以一起插入数据后最后加索引
loading the data
alter table tableName enable keys//最后打开所以
set unique_checks=0//关闭唯一索引
set unique_checks=1//恢复唯一索引
