Mysql 数据库优化
1、mysql基础操作
2、常用的sql技巧
3、sql语句优化
4、mysql数据库优化
5、myisam表锁
6、mysql服务器优化
一、mysql基础操作
1.1 mysql 表复制
复制表结构+复制表数据
mysql > create table t3 like t1; mysql > insert into t3 select * from t1;
1.2 mysql 索引
1、alter table 用来创建普通索引,unique索引或者parimary key索引
alter table tab_name add index index_name(column_list) //普通索引 alter table tab_name add unique(column_list) //唯一索引 alter table tab_name add primary key(column_list) //主键索引
2、create index
create index index_name on table_name create unique index index_name on table_name
3、drop index
drop index index_name on table_name;
4、alter table table_name drop
alter table table_name drop index index_name;
alter table table_name drop primary key;
1.3 mysql 视图
创建视图
mysql > create view view_name as select * from table_name; //view 视图的帮助信息 mysql > ? view alter view create view drop view //查看视图 msql > show tables; //删除视图 mysq > drop view view_name;
1.4 mysql 内置函数
字符串函数
concat(string1,string2) //连接字符串 select concat('hello','world') as myname;
LCASE(string1) // 转换成小写 select lcase('mysq') as myname
UCASE(string1) //转换成大写
Length(string1) //string1 长度
ltrim(string1) //去除左边空格
rtirm(string1) //去除右边空格
repeat(string1,count) //把string1 重复 count 次数
replace(string,search_str,replace_str) // 在str中用relace_str替换search_str
substing(string,pos,length) // 从str的pos开始取出length个字符
space(count) //生成count个空格
数学函数
BIN(decimal_number) //十进制转二进制
ceilling(number) //向上取整
floor(number) //向下取整
max(column) //取最大值 ，聚合使用
min(column) // 取最小值 ，聚合使用
sqrt(number) // 开方
rand() //返回0-1的随机数 随机排列 select * from t1 order by rand();
日期函数
curdate() //返回当前日期
curtime() //返回当前时间
now() //返回当前日期时间
unix_timestamp(date) //返回时间戳
from_unixtime() //返回时间戳中的日期值
week(date) //返回日期date一年中的第几周
year(date) //返回日期date中的年份
datediff(d1,d2) //计算日期d1与d2之间相隔的天数
1.5 mysq预处理语句
设置smtm1预处理，传递一个数据作为where判断的条件：
mysql>prepare smtm1 from 'select * from table_name where id >@i'; //设置一个变量： mysql > set @i = 1; //执行stm1 预处理 mysql > execute smtm1 using @i; //设置@i = 5 mysql > set @i = 5; //再次执行smtm1 预处理 mysql > execute smtm1 using @1; //如何删除预处理 mysql > drop prepare smtm1;
1.6 mysql 事务处理
// 关闭自动提交功能 mysql > set autocommit; //从表t1中删除一条记录 mysql > delete from t1 where id =1; //此时做一个p1还原点 mysql > savepoint p1; //再次从t1中删除一条记录 mysql > delete from t1 where id =2; //再做一个p2还原点 mysql > savepoint p2; //此时恢复到p1还原点 mysql > rollback p1; //退回到最原始的还原点 mysql > rollback;
1.7 mysql存储过程
创建一个存储p2();
mysql> \d //; 改变界定符
mysql> create procedure p2()
-> begin
-> set @i = 1;
-> while @i<100 do
-> insert into t2(name) values(concat('user',@i));
-> @i = @i +1;
-> end while ;
-> end //
//执行储存 p2();
mysql > \d ;
mysql > call p2;
//查看procedure p2的status 信息
mysql > show procedure status \G
//查看procedure 的具体信息
mysql > show create procedure p2 \G
1.8 mysql触发器 (trriger)
//修改delimiter 为 //
mysql > \d //;
//创建一个名字为tg1的触发器，当向表t1插入一行数据的时候也向t2插入一条相同的数据
mysql > create trigger tg1 before insert on t1 for each row
->begin
->insert into t2(name) values (new.name);
->end //
//查看创建好的trigger
mysql->show triggers;
//向t1表中插入多条数据
mysql->insert into t1(name) values ('user1'),('user2') .... ;
//如何制作删除表t1后t2表中的数据也跟着删除
mysql > \d //
mysql > create trigger tg2 before delete on t2 for each row
->begin
->delete from t2 where id = old.id;
->end //
mysql>\d ;
//如何制作更改表t1后t2表中的记录跟着改变呢？
mysql > \d //;
mysql > create trigger tg3 before update t1 for each row
->begin
->update t2 set id = new.id where id = old.id
->end //
1.9 重排auto_increment
Mysql 数据库自动增长的Id如何恢复
清空表的时候，不能用
delete from table_name;
而是要用:
truncate table table_name;
这样auto_increment 就恢复成1了；
或者清空内容后直接用alter命令修改表：
alter table table_name auto_increment =1 ;
