MySQL优化之表索引、视图、内置函数、预处理、事务处理、存储、触发器

「爱情、让人受尽委屈。」 2022-08-13 11:42 115阅读 0赞

**一、复制表**  
create table bbs2 like bbs;  
insert into bbs2 select \* from bbs;  
  
  
**二、查看表结构**  
desc bbs;  
  
  
**三、表索引**  
1、增加索引  
alter table bbs add index bbs\_title(title);  //索引名称命名规则为表名\_字段名  
  
  
2、查看索引  
show indexes from bbs;  
show indexes from bbs\\G;  
  
  
3、删除索引  
alter table bbs drop index bbs\_index;    
//删除主键索引时，必须先删除auto\_increment属性；  
alter table bbs modify id int unsigned not null;  
alter table bbs drop primary key;  
  
  
4、修改索引  
删除索引，然后重新创建  
  
  
5、索引分类  
a）、普通索引  
index  
b）、主键索引  
primary  
c）、唯一索引  
unique  
d）、全文索引  
fulltext  
e）、外键索引  
foreign key  
  
  
**四、视图**  
1、增加视图  
create view bbs\_view as select \* from bbs where id>1 and id<11;  
  
  
2、删除视图

drop view bbs\_view

3、原表发生改变，视图表也发生改变；

4、查看视图创建命令

show create view view\_name;

**五、内置函数**  
1、字符串函数  
a）、输出字符串  select "dwfwefd";  
b）、连接字符串  select concat('er','erwerw');  
c）、转换成小写  select lcase(concat('er','erweHHHHrw'));   //PHP  strtolower  
d）、转换成大写  select ucase(concat('er','erweHHHHrw'));   //PHP  strtoupper  
e）、长度  select length('lamq');  
f）、去除左边空格 select ltrim('    dsdfdsfds dsfsdf d   ds   ');  
g）、去除右边空格 select rtrim(ltrim('    dsdfdsfds dsfsdf d   ds   '));  
h）、去除两边空格 select trim('    dsdfdsfds dsfsdf d   ds   ');  
i）、重复输出     select repeat('aa',100);  
j）、替换  select replace('eswdf32e34e3re3','3','@');  
k）、截取字符串   select substring('eswdf32e34e3re3',1,10);   //从1开始计数  PHP  substr  
l）、生成空格     select space(10);  
  
  
2、数字函数  
a）、转换进制   select bin(10);  
b）、向上取整   select ceiling(10.2);     //返回11  
c）、向下取整   select floor(10.2);     //返回10  
d）、取最大数   select max(id);  
e）、取最小数   select min(age);  
f）、开平方     select sqrt(10.2);  
g）、返回随机数  select rand();  
  
  
3、日期函数  
a）、返回当前日期  select curdate();  
b）、返回当前时间  select curtime();  //注意调整时区  
c）、返回当前的日期时间  select now();  
d）、返回当前date的UNIX时间戳 select unix\_timestamp();  
e）、返回UNIX时间戳的日期值 select from\_unixtime(1413809093);  
f）、返回日期date为一年中第几周 select week(now());  
g）、返回日期date的年份 select year(now());  
h）、返回起始时间expr和结束时间expr2间天数 select datediff('2014-10-10','2014-10-12');   //-2  
  
  
**六、预处理**  
1、创建预处理  
prepare stmt from 'delete from bbs where id=?';  
set @i= 22;  
execute stmt using @i;  
  
  
  
  
prepare statment2 from 'select \* from bbs where id between ? and ?';  
set @i=10;  
set @j=20;  
execute statment2 ussing @i,@j;  
2、删除预处理  
drop prepare stmt;  
  
  
**七、事务处理**  
1、数据表的引擎必须为InnoDB；  
2、多个步骤为一个过程，任何一环出错则回滚；  
3、关闭自动提交；执行sql语句；提交或者回滚；打开自动提交；  
4、命令行方式一  
set autocommit=0;    //关闭自动提交功能  
delete from bbs where id=11; //删除一行记录  
savepoint p1;  //设置还原点  
delete from bbs where id=12; //删除一行记录  
savepoint p2;  //设置还原点  
rollback to p1; //回滚至还原点p1  
rollback;  //回滚所有操作  
commit;  //正式执行  
set autocommit=1;    //开启自动提交功能  
  
  
4、命令行方式二  
begin;    //关闭自动提交功能，这样末尾就不用管了  
delete from bbs where id=11; //删除一行记录  
savepoint p1;  //设置还原点  
delete from bbs where id=12; //删除一行记录  
savepoint p2;  //设置还原点  
rollback to p1; //回滚至还原点p1  
rollback;  //回滚所有操作  
commit;  //正式执行  
  
  
  
**八、mysql存储**  
1、主要用来批量执行某代码，比如批量插入数据  
2、命令范例  
\\d // //将分号执行修改为双斜杠开始执行；  
create procedure p1()  
begin  
set @i=0;  
where @i<10000 do  
insert into bbs(title,content) values('aaa','bbb');  
set @i=@i+1;  
end while;  
end;  
//  
  
  
  
\\d ;           //修改问默认分号执行语句；  
  
  
call p1;  调用存储过程  
  
  
3、删除存储 drop procedure p1;  
4、拓展  \\d  修改执行符  
  
  
**九、mysql触发器**  
1、当有一张表进行数据的更改，另一张关联表也会发生更改  
2、支持增加、删除、修改  
3、命令范例  
增加范例  
\\d //  
create trigger tg1 before insert on bbs for each row  
begin  
insert into bbs2(id,title,content) values(new.id,new.title,new.content);  
end  
//  
  
  
\\d ;  
insert into bbs(title,content) values('aaa','bbb');   //在向bbs插入数据的同时会向bbs2中插入相同的数据  
  
  
  
  
删除范例  
\\d //  
create trigger tg2 before delete on bbs for each row  
begin  
delete from bbs2 where title=old.title;  
end  
//  
  
  
\\d ;  
delete from bbs where title='aaa';   //同步删除  
  
  
  
修改范例  
\\d //  
create trigger tg3 before update on bbs for each row  
begin  
update bbs2 set title=new.title where id=old.id;  
end  
//  
  
  
\\d ;  
update table bbs set title='yangzi' where id =1;   //同步更改