MySQL数据库优化详解

mysql表复制                                                                              

复制表结构+复制表数据

mysql> create table t3 like t1;
mysql> insert into t3 select * from t1;

mysql索引                                                                                 

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADDPRIMARY KEY (column_list)
Create Index
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
drop index
DROP INDEX index_name ON talbe_name
alter table table drop
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

mysql视图                                                                                

创建视图

mysql> create view v_t1 as select * from t1 where id>4 and id<11;
Query OK,0 rows affected (0.00 sec)

view视图的帮助信息

mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW

查看视图

mysql> show tables;

删除视图v_t1

mysql> drop view v_t1;

mysql内置函数                                                                           

字符串函数

CONCAT (string2 [,… ]) //连接字串
LCASE (string2 ) //转换成小写
UCASE (string2 ) //转换成大写
LENGTH (string ) //string长度
LTRIM (string2 ) //去除前端空格
RTRIM (string2 ) //去除后端空格
REPEAT (string2,count ) //重复count次
REPLACE (str,search_str,replace_str ) //在str中用replace_str替换search_str
SUBSTRING (str,position [,length ]) //从str的position开始,取length个字符
SPACE(count) //生成count个空格

数学函数

BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
FLOOR (number2 ) //向下取整
MAX(num1,num2) //取最大值
MIN(num1,num2) //取最小值
SQRT(number2) //开平方
RAND() //返回0-1内的随机值

日期函数

CURDATE() //返回当前日期
CURTIME() //返回当前时间
NOW() //返回当前的日期时间
UNIX_TIMESTAMP(date) //返回当前date的UNIX日间戳
FROM_UNIXTIME() //返回UNIX时间戳的日期值
WEEK(date) //返回日期date为一年中的第几周
YEAR(date) //返回日期date的年份
DATEDIFF(expr,expr2) //返回起始时间expr和结束时间expr2间天数

mysql预处理语句                                                                        

设置stmt1预处理,传递一个数据作为一个where判断条件

mysql> prepare stmt1 from 'select * from t1 where id>?';

设置一个变量
mysql> set @i=1;
执行stmt1预处理
mysql> execute stmt1 using @i;
设置@i为5
mysql> set @i=5;
再次去执行stmt1
mysql> execute stmt1 using @i;
如何删除预处理stmt1
mysql> drop prepare stmt1;

mysql事务处理                                                                         

--关闭自动提交功能
mysql> set autocommit=0;
--从表t1中删除了一条记录
mysql> delete from t1 where id=11;
--此时做一个p1还原点:
mysql> savepoint p1;
--再次从表t1中删除一条记录:
mysql> delete from t1 where id=10;
--再次做一个p2还原点:
mysql> savepoint p2;
--此时恢复到p1还原点，当然后面的p2这些还原点自动会失效: 
mysql> rollback to p1;
--退回到最原始的还原点:
mysql> rollback ;

mysql存储                                                                                

创建一个存储p1()

mysql> d //
mysql> create procedure p1()
-> begin
-> set @i=0;
-> while @i<10 do
-> select @i;
-> set @i=@i+1;
-> end while;
-> end;
-> //

执行存储p1()

mysql> d ;
mysql> call p1();
--查看procedure p1()的status信息
mysql> show procedure statusG
--查看procedure p1()的具体信息:
mysql> show create procedure p1G

mysql触发器                                                                              

修改delimiter为//
mysql> d //
创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据
 
mysql> create trigger tg1 before insert on t1 for each ro
>begin
>insert into t2(id) values(new.id); 
>end//
--准备两个空表t1和t2
mysql> select * from t1;
mysql> select * from t2;
--向t1表中插入多条数据:
mysql> insert into t1 values(1),(2),(3),(4);

如何制作删除表t1后t2表中的记录也会跟着删除呢
mysql>d //
mysql> create trigger tg2 beforedelete on t1 for each row 
>begin delete from t2 where id=old.id;
>end//
mysql>d ;
如何制作更改表t1后t2表中的记录跟着个性呢
mysql>d //
mysql> create trigger tg3 beforeupdate on t1 for each row 
>begin update t2 set id=new.id where id=old.id; 
>end//
mysql>d ;
查看触发器
mysql> show triggers;

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!