mySQL学习入门教程——5.常用的高级操作

五、mysql常用的高级操作

 

一、MySQL表复制
create table t2 like t1;               #复制表结构，t2可以学习到t1所有的表结构
insert into t2 select * from t1;    #复制表数据，但是这样还是会有缺陷，因为没有考虑到列的对应，因为t1与t2的表结构完全一致，所以此次操作才不会出错！
建议：
insert into t3(name) select name from t1; #指定复制的列

二、MySQL索引
1、直接创建索引
create index index_name on table_name(column_list);                #创建普通索引
create unique index index_name on table_name(colume_list);    #创建唯一索引，请在创建唯一索引之前确保该列没有重复值，不然，创建不成功！

2、直接删除索引
drop index index_name on table_name;

3、修改-创建索引
alter table table_name add index [index_name](colum_list);                #创建普通索引
alter table table_name add unique [index_name](column_list);            #创建唯一索引
alter table table_name add primary key [index_name](column_list);   #创建主键索引，如果不添加index_name,则使用column_list作为默认索引名

4、修改-删除索引
alter table table_name drop index index_name;            #删除普通/唯一索引
alter table table_name drop primary key;                      #删除主键索引

【推荐使用方式3、4】
1、查看索引：show index from t1 \G
2、alter table table_name modify id int not null;（没有查到用modify修改索引，貌似是用add做修改？）

三、MySQL视图
视图：通过一个条件，把一部分数据从一张表里面提取出来，形成一张中间表，这张表就是视图
注意：视图随着主表的改变而改变
1、创建视图
create view view_name as select *from table_naem where id > 4 and id <= 10;

3、查看创建了哪些视图
show tables; #视图就是一个中间表
3、查看视图中数据
select * from view_name; #与查看表数据相同

4、删除视图
drop view view_name;

四、MySQL内置函数补充
查看函数作用及简单示例：function_name
e.g.  lcase;

1、字符串函数
1）lcase(“string”)/ucase(“string”)           #转换成小写/大写，与lower(str)/upper(str)作用相同
2）length(“string”)                                   #返回字符串的长度
3）repeat(“string”,n)                                #将字符从重复n次
4）space(n)                                               #生成n个空格

2、数学函数
1）bin(decimal_number)                          #十进制转二进制
2）ceiling(n)                                             #作用与ceil相同，向下取整
3）sqrt(n)                                                  #开平方
4）max(col)/min(col)                               #取最大/最小值，聚合时使用
5）rand()                                                   #生成随机数
select * from table_name order by rand(); #使用rand函数作为排序基准

3、日期函数
1）datediff(expr1,expr2)                             #返回expr1和expr2相差的天数，如果expr1> expr2，则返回正值

 

 ****************************比较高级部分***********************************

五、MySQL预处理语句
1、设置预处理stmt，传递一个数据作为where的判断条件
prepare stmt from “select * from table_name where id > ?”;

2、设置一个变量
set @i = 1;

3、执行预处理语句
execute stmt using @i;

4、删除预处理指令
drop prepare stmt;

六、MySQL事务处理
【注意】MyISAM存储引擎对事务并不支持，应该使用InnoDB存储引擎

set autocommit = 0;              #关闭自动提交  
delete from t1 where id > 4;  
savepoint p1;                        #设置还原点  
delete from t1;  
rollback to p1;                       #回滚到p1还原点  
rollback;                                #回滚到最原始的还原点  
commit ;                                #提交数据到服务器  
set autocommit = 1;                #开启自动提交，关闭事务处理  

七、MySQL存储
1、创建一个存储p1()

mysql>\d //                             #修改定界符为//  
mysql>create procedure p1()  
        ->begin  
        ->set @i = 0;  
        ->while @i < 100 do  
        ->insert into t2(name) values(concat("user",@i));  
        ->set @i = @i + 1;  
       ->end while;  
       ->end;//  
mysql>\d ;  

 

2、执行p1()
call p1();
3、查看procedure的status信息
show procedure status \G

4、查看procedurep1的具体信息
show create procedure p1 \G

八、MySQL触发器
1、创建触发器
#创建一个名为t1的触发器，当向t1表中插入数据时，就引发动作：向t2表中插入数

mysql>\d //  
mysql>create trigger t1 before inserton t1 for each row  
        ->begin  
        ->insert into t2(name) values(new.name);  
        ->end//  
mysql>\d ;  

 

#创建触发器t2，如果表t1删除数据，则引发触发器，表t2中的数据也相应删除

mysql>\d //  
mysql>create trigger t2 before delete on t1 for each row  
       ->begin  
      ->delete from t2 where id =old.id;  
      ->end//  
mysql>\d ;  

 

#创建触发器t3，如果修改表t1则t2中的记录也相应修改

mysql>\d //  
mysql>create trigger t3 before update on t1 for each row  
        ->begin  
        ->update t2 set id =new.id where id = old.id;  
        ->end//  
mysql>\d ;  

 

2、删除触发器
drop trigger trigger_name;

【附】删除表中所有数据：truncatetable_name; #速度更快，同时也可以清空auto_increment列表

九、重排auto_increment值
MySQL中自动增长的ID如何恢复？
1、清空表的时候，不要用delete from table_name;
而是：truncate [table] table_name;

或者
2、清空内容之后直接使用alter命令修改表
  alter table table_name auto_increment = 1;

 

http://blog.csdn.net/zjf280441589/article/details/20217647

http://blog.csdn.net/zjf280441589/article/details/20230427