mysql存储过程相关备忘

1.导出存储过程，对存储过程进行备份
mysqldump -h 127.0.0.1 -u root -p -n -t -d -R --triggers=false my_db_name >c:\bakproc.sql
2.获取当前日期的年月日
date_format(now(), '%Y')
date_format(now(), '%m')
date_format(now(), '%d')
3.命令行远程连接数据库
mysql -h server_ip -u username -p;
回车输入用户密码即可。
4.常用方法
字符串连接concat
结果集逗号分隔GROUP_CONCAT(查询列名)
locate(concat(',', subString, ','), string)
5.替换
select REPLACE(original_string,substring_for_find,'') from demo_table_name
select LOCATE(substring_for_find,original_string) indexof,
concat(left(original_string,LOCATE(substring_for_find,original_string)-1),
substring(original_string,(LOCATE(substring_for_find,original_string)+LENGTH(substring_for_find)),LENGTH(original_string)-LENGTH(substring_for_find)))
from demo_table_name
6.排序联合
(select colname1,colname2 from tablename1 order by order_colname limit 100)
union
(select colname1,colname2 from tablename2 order by order_colname limit 100)
union
(select colname1,colname2 from tablename3 order by order_colname limit 100)
7.按日期分组统计
select date_format(from_unixtime(dateline) ,'%Y-%m-%d') count_date,date_format(from_unixtime(dateline),'%h') count_hour,
max(usercount) max_usercount,min(usercount) min_usercount
from table_name
group by date_format(from_unixtime(dateline),'%Y-%m-%d'),date_format(from_unixtime(dateline),'%h')
8.获取扩展名
select substring('sssss.dd.ddd.doc',LOCATE('.','sssss.dd.ddd.doc',LENGTH('sssss.dd.ddd.doc')-4)+1)
9.转为整数
select cast(sum(ifnull(current_star,0))-sum(ifnull(used_star,0))as signed)
10.判断日期在一天内
select * from tablename where datediff(now(),from_unixtime(date_colName))=0
11.备份库
mysqldump -h 127.0.0.1 -u root -p my_db_name >c:\bakdatabase.sql
12.恢复
mysql -h 127.0.0.1 -u root -p my_db_name < c:\bakdatabase.sql
13.转float字段为int
cast(columenName as signed Int)
14.显示数据库变量
show variables like "%timeout%";
15.查询某个表的当前自增量
SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'table';
16.修改表的自增量
ALTER TABLE tablename AUTO_INCREMENT=10000;
17.获取当前日期所属星期的周一的日期
select date_add(date(now()), interval -weekday(now()) day);
18.获取当前日期所属星期的周日的日期
select date_add(date(now()), interval (6-weekday(now())) day);
19.查找指定字符串截取与表中其他列值比较
select id,description,pa,pd,pw,pt from table_name where
(pa>0 and ifnull(substring(description,LOCATE('%',description)-2,2),0)!=pa/100)
or (pd>0 and ifnull(substring(description,LOCATE('%',description)-2,2),0)!=pd/100)
or (pw>0 and ifnull(substring(description,LOCATE('%',description)-2,2),0)!=pw/100)
or (pt>0 and ifnull(substring(description,LOCATE('%',description)-2,2),0)!=pt/100)
20.复制表结构
（1）CREATE TABLE A LIKE B
此种方式在将表B复制到A时候会将表B完整的字段结构和索引复制到表A中来。
（2）CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0
此种方式只会将表B的字段结构复制到表A中来，但不会复制表B中的索引到表A中来。这种方式比较灵活可以在复制原表表结构的同时指定要复制哪些字段，并且自身复制表也可以根据需要增加字段结构。
两种方式在复制表的时候均不会复制权限对表的设置。比如说原本对表B做了权限设置，复制后，表A不具备类似于表B的权限。
21.INSERT ... ON DUPLICATE KEY UPDATE Syntax
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
22.ERROR 1690 (22003): BIGINT value is out of range
http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html
mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;