mysql的一些基本操作

1.备份数据库：

mysqldump -uroot -p hbnx --default-character-set=utf8 --hex-blob >/tmp/hbnx.sql

 

2.还原数据库：

mysql -uroot -p hbnx < /tmp/hbnx.sql

或者

进入到数据库操作界面后

source /tmp/hbnx.sql;

3.给用户赋权限

grant all privileges on *.* to root @'%' identified by '123456';

flush privileges;

 

4.执行一个SQL，但SQL语句过大或者语句中含有BLOB或者longblob字段。

比如，图片数据的处理
解决方案

在my.ini文件中添加或者修改以下变量：
max_allowed_packet = 512M  (也可以设置自己需要的大小)

max_allowed_packet 参数的作用是，用来控制其通信缓冲区的最大长度。 

mysql根据配置文件会限制server接受的数据包大小。

有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。

查看目前配置

show VARIABLES like '%max_allowed_packet%';

 

5.mysql常用函数

把时间类型转换成字符类型：to_char(date_time,'yyyy-mm-dd') 

把字符类型转换成时间类型：str_to_date(now(),'%Y-%m-%d %H:%i:%s')

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

参考 http://www.w3school.com.cn/sql/func_date_format.asp

 

unix_timestamp

将时间转化为时间戳。（date 类型数据转换成 timestamp 形式整数）

 

 select unix_timestamp();  返回当前时间的毫秒数

 select unix_timestamp('2013-01-01 10:10:10'); 返回指定时间的毫秒数

 

from_unixtime

将timestamp 形式整数 转化为 date类型

 

 select from_unixtime(1355272360);     ---》 2012-12-12 08:32:40

 select from_unixtime(1355272360,'%Y%m%d');    ---》 20121212

 

DATE_SUB() 函数从指定日期减去指定的时间间隔:

获取两天前的日期：DATE_sub(now(), interval 2 day)

 

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差，其结果的单位由interval 参数给出。该参数必须是以下值的其中一个：

 

1. FRAC_SECOND。表示间隔是毫秒
2. SECOND。秒
3. MINUTE。分钟
4. HOUR。小时
5. DAY。天
6. WEEK。星期
7. MONTH。月
8. QUARTER。季度
9. YEAR。年

TIMESTAMPADD(interval,int_expr,datetime_expr)

将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。式中的interval和上文中列举的取值是一样的。

 select TIMESTAMPADD(MINUTE,60,'2012-08-24 09:00:00');  ---》 2012-08-24 10:00:00

 

自定义排序函数field

比如 order by FIELD(service_type, 4,1,0,2,6)，service_type按4，1，0，2，6排序

 

INSTR(字段名, 字符串)

这个函数返回字符串在某一个字段的内容中的位置, 没有找到字符串返回0，否则返回位置（从1开始）

如：select INSTR('baa3sdfsf' ,'aa');  结果为2

 

LOCATE(substr,str) 

返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0

LOCATE(substr,str,pos) 
返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0

SELECT LOCATE('bar', ‘foobarbar',5); --->7

 

 

 IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。

 

 MID()函数

SQL MID() 函数用于得到一个字符串的一部分。这个函数被MySQL支持，但不被MS SQL Server和Oracle支持。在SQL Server， Oracle 数据库中，我们可以使用 SQL SUBSTRING函数或者 SQL SUBSTR函数作为替代。

MID() 函数语法为：

SELECT MID(ColumnName, Start [, Length])
FROM TableName

注：字符串从1开始，而非0，Length是可选项，如果没有提供，MID()函数将返回余下的字符串。

 

 ORD() 函数

ORD() 函数返回字符串第一个字符的ASCII 值。

语法： ORD(string)

 

LPAD(str,len,padstr)
返回字符串str，左填充用字符串padstr填补到len字符长度。 如果str为大于len长，返回值被缩短至len个字符(即，不能超过 len 长)。

 

四舍五入函数ROUND(x,y)

select round(0.45,1) ---》 0.5

 

TRUNCATE(x,y)函数

返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。

select TRUNCATE(0.45,1) ---》0.4