MySQL 常用函数和语句
DDL语句
修改表的某个字段定义:alter table tableName modify clounmnName column_definition [first|After col_name]
增加表字段:alter table tablename add column column_definition [first|after col_name]
eg:
1 | alter table emp add column age int(3); |
删除表字段:alter table tablename drop column col_name
修改字段名:alter table tablename change [column] old_col_name column_definition [First|after col_name]
eg:
alter table emp change age age1 int(4)
修改表名称alter table tablename rename [to new tablename]
按照sal排序后查询从第二条记录开始的3条记录limit:select * from emp order by sal limit 1,3.
1 | select concat('drop table test1',table_name,';') and table_name like 'tmp%' |
bit类型的数据列用bin(),hex()来显示
mysql 只给表中的第一个timestamp类型设置默认值为系统日期，如果有第二个timestamp类型，则默认为0值。规定timestamp类型字段只能有一列的默认值为current_timestamp;timestamp 和时区有关，插入的时候先转换成本地时区后存放，从数据库取出来时也同样需要将日期转换成本地时区后显示。
查看时区: show variables like 'time_zone'
表中的第一个timestamp自动设置为系统时间。如果插入时为null，该列自动设置为当前的日期和时间
函数
基础函数
LPAD(str,n,pad); RPAD(str,n,pad):用字符串pad对str最左边和最右边进行填充，知道长度为n个长度为止。
LTRIM(str); RTRIM(str):去掉字符串str左侧和右侧空格
REPEAT(str,n):返回str重复n次
REPLACE（str,a,b): 用字符串b替换字符串str中所有出现的字符串
STRCMP(s1,s2):比较s1，s2的ASCII码值得大小
TRIM(str): 去掉str左右两端的空格
SUBSTRING(str,x,y):返回从字符串str中的第x位置起y个字符长度的子串
COUNT(*),COUNT(id): 如果为*，就是返回总行数；如果只是某个列，特别注意如果该列有值为null，那么count不计数
数值函数
ABS(x)函数:返回x的绝对值
CEIL(x)函数:返回大于x的最小整数
FLOOR(x)函数:返回小于x的最大整数，和CEIL的用法刚好相反
MOD(x,y):返回x/y的模
RAND()函数:返回0~1内的随机值
ROUND(x,y)函数:返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)函数:返回数字x截断为y位小数的结果
日期与时间函数
curdate():返回当前日期，只包含年月日
curtime():返回当前时间，只包含时分秒
now():返回当前的日期和时间，年月日时分秒全都包含
UNIX_timestamp(date):返回日期date的UNIX时间戳，Unix_timestamp(now())
FROM_unixtime(unixtime):返回unixtime时间戳的日期值，和unix_timestamp(date)互为逆操作
week(date),year(date):前者返回所给的日期是一年的第几周，后者返回所给的日期是哪一年
hour(time)和minute(time):前者返回所给时间的小时，后者返回所给时间的分钟
比如当前时间为23:34,selec hour(curtime()),minute(curtime())==>23 34
monthname(date):返回date的英文名称,四月份的话,select monthname(curdate()) ==> April
Date_format(date,fmt) : 将date格式化成fmt格式，select date_format(now(),’%M,%D,%Y’)
date_add(date,INTERVAL expr type): 返回与所给日期date相差INTERVAL时间段的日期
datediff(date1,date2):用来计算两个日期之间相差的天数
流程函数
IF(value,t,f): select if(salary>2000,’high’,’low’) from salary; 如果value成立，则显示为t，否则为f
ifnull（value1，value2) 如果value1为null则显示为value2
case when [value] then [result].. else[default] END : 与if-else差不多 select case when id<5 then ‘err1’ else ‘err>5’ end from city_tbl
其它函数
database():返回当前数据库名
version():返回版本
user():返回当前登录用户
inet_aton(IP):返回IP地址的网络字节序表示：select init_aton(‘192.168.1.1’)==》3232235777
inet_ntoa(num):返回网络字节序的IP地址标识：select inet_ntoa(3232235777)==>192.168.1.1
password(str):返回字符串str的加密版本，一个41位长的字符串
MD5(str):返回字符串的str的MD5值，常用来对应用中的数据进行加密
show engines:返回存储引擎列表
show variables like 'have%':返回have为前缀的所有系统变量
alter table *** auto_increment=n; 设置强制自动增长列的初始值，该强制默认值存储在内存中，如果该值在使用之前数据库重启，那么需要在数据库启动后重新设置
对于innoDB表，自动增长列必须是索引
对于text,blob字段的表，如果进场做删除和修改记录，要定时执行optimize table对表进行碎片整理
help ‘show engines’:如果忘记了某个方法，某个关键字，都可以用help来帮助
