02_MySQL常用命令
>MySQL函数
常用的函数有：
1. 字符串函数；主要用于处理字符串。
2. 数值函数；主要用于处理数字。
3. 日期和时间函数；主要用于处理日期和事件。
4. 系统信息函数；获取系统信息。
>>数学函数
获取随机数
rand()和rand(x)
获取随机数。这两个函数都会返回0-1之间的随机数，其中rand()函数返回的数是完全随机的，而rand(x)函数返回的随机数值是完全相同的。
获取整数的函数
ceil(x)
函数返回大于或等于数值x的最小整数。
floor(x)
函数返回小于或等于数值x的最大整数。
截取数值函数
truncate(x,y)
返回数值x,保留小数点后y位
eg：truncate(903.343434,2),truncate(903.343,-1) => 903.34 | 900 |
四舍五入函数
round(x)
函数返回值x经过四舍五入操作后的数值。
round(x,y)
返回数值x保留到小数点后y位的值。
未分类数值函数
ABS(x) 返回x的绝对值
BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
EXP(x) 返回值e（自然对数的底）的x次方
GREATEST(x1,x2,...,xn)返回集合中最大的值
LEAST(x1,x2,...,xn) 返回集合中最小的值
LN(x) 返回x的自然对数
LOG(x,y)返回x的以y为底的对数
MOD(x,y) 返回x/y的模（余数）
PI()返回pi的值（圆周率）
SIGN(x) 返回代表数字x的符号的值
SQRT(x) 返回一个数的平方根
ISNULL() 函数用于规定如何处理 NULL 值。Eg: IFNULL(UnitsOnOrder,0)
>>聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
GROUP_CONCAT(id) 默认分隔符是","
GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') 设置分隔符
Eg: 2_1
GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ')
Eg: 2, Submitted (20-JUN-2002). 1, Unpublished
Eg: select id,group_concat(name separator ';') from aa group by id;
注：group_concat()需配合group by一起使用
>>字符串函数
合并字符串函数concat() 和 concat_ws()：
在MySQL中可以通过函数concat()和concat_ws()将传入的参数连接成为一个字符串。
concat(s1, s2,...sn) //该函数会将传入的参数连接起来返回合并的字符串类型的数据。如果其中一个参数为null，则返回值为null. 示例：concat('my','s','ql') => 'mysql'
concat_ws(sep,s1,s2,...sn) //将s1,s2...,sn连接成字符串，并用sep字符间隔。 //分隔符可以是一个字符串，也可以是其他参数。如果分割符为null，则返回结果为null。函数会忽略任何分割符后的参数null。concat_ws('-','020','87658907') => '020-87658907' concat_ws('-','020',null,'87658907') =>'020-87658907'查找字符串位置：
mysql中提供了丰富的函数去查找字符串的位置。分别有find_in_set()函数、field()函数、locate()函数、position()函数和instr()函数。同时还提供了查找指定位置的字符串的函数elt()。
FIND_IN_SET(str,list)
分析逗号分隔的list列表，如果发现str，返回str在list中的位置
eg：find_in_set('mysql','oracle,mysql,db2') => 2
LOCATE (str1 , str )、position(str1 in str) 和 instr(str,str1)
返回参数str中字符串str1的开始位置
eg：locate('sql','mysql'),position('sql' in 'mysql'),instr('mysql','sql') => 3 | 3 | 3 |
elt(n,str1,str2...)
返回指定位置的字符串
eg：elt(1,'mysql','db2','oracle') => 'mysql'
从现有字符串中截取子字符串：
截取子字符串的函数有：left(),right(),substring(),mid();
LEFT(str,x)
返回字符串str中最左边的x个字符
RIGHT(str,x)
返回字符串str中最右边的x个字符
eg：left('mysql',2),right('mysql',3) => my | sql |
substring(str,num,len) mid(str,num,len) substr(string string,num start,num length)
返回字符串str中的第num个位置开始长度为len的子字符串
eg：substring('zhaojd',2,3),mid('zhaojd',2,4) => hao | haoj |
从字符串的第 4 个字符位置（倒数）开始取，直到结束
substring('sqlstudy.com', -4) => .com
从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符
substring('sqlstudy.com', -4, 2) => .c
注：在函数 substring(str,num, len)中， num可以是负值，但 len 不能取负值。
substring_index(str,delim,count) 根据符号截取字符串
substring_index('www.sqlstudy.com.cn', '.', 2) 截取第二个 '.' 之前的所有字符 => www.sqlstudy
substring_index('www.sqlstudy.com.cn', '.', -2) 截取第二个 '.' （倒数）之后的所有字符 => com.cn
substring_index('www.sqlstudy.com.cn', '.coc', 1) 如果在字符串中找不到 delim 参数指定的值，就返回整个字符串 => www.sqlstudy.com.cn
获取字符串长度函数length()和字符数函数char_length()：
length(str) 返回字符串str中的字符数
char_length(str)
eg：length('mysql'),length('汉字'),char_length('mysql'),char_length('汉字') => 5 | 4 | 5 | 4
//字符串‘MySQL'共有5个字符，但是占6个字节空间。这是因为每个字符串都是以\0结束。两个函数都是获取字符串的字符数而不是所占空间大小。
比较字符串大小函数strcmp()：
strcmp(str1,str2); //如果参数str1大于str2，返回1；如果str1小于str2,则返回-1；如果str1等于str2，则返回0；strcmp('abc','abd') => -1字母的大小写转换upper()和lower()：
字母大写转换函数：upper(s); ucase(s);
字母小写转换函数：lower(s); lcase(s);
去除字符串的首尾空格：
去除字符串首尾空格的函数有：ltrim()、rtrim()、trim()
ltrim(str)
返回去掉开始处空格的字符串
rtrim(str)
返回去掉结束处空格的字符串。
trim(str)
返回去掉首尾空格的字符串，可以过滤指定的字符串
完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
简化格式：TRIM([remstr FROM] str)
eg:
SELECT TRIM(' bar '); //默认删除前后空格
SELECT TRIM(LEADING '，' FROM '，，barxxx'); //删除指定首字符 如'，‘
SELECT TRIM(BOTH '，' FROM '，，bar，，，'); //删除指定首尾字符
SELECT TRIM(TRAILING '，' FROM 'barxxyz，，'); //删除指定尾字符 如'，‘
替换字符串函数：
INSERT(str,x,y,instr)
将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
如果参数pos的值超过字符串长度，则返回值为原始字符串str。
如果len的长度大于原来str中所剩字符串的长度，则从位置pos开始进行全部替换。若任何一个参数为null,则返回值为null.
eg：insert('这是mysql数据库系统',3,5,'oracle') => 这是oracle数据库系统
replace(str,substr,newstr)
将字符串str中的子字符串substr用字符串newstr来替换。
eg：replace('这是mysql数据库','mysql','db2') => 这是db2数据库
未分类字符串函数
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
ASCII(char)返回字符的ASCII码值
BIT_LENGTH(str)返回字符串的比特长度
>>日期和时间函数
获取当前日期和时间的函数
now(),current_timestamp(),localtime(),sysdate()
四个函数不仅可以获取当前日期和时间，而且显示的格式也一样。推荐使用now()
eg：now(),current_timestamp(),localtime(),sysdate() => 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 |
curdate()，current_date()
获取当前日期
eg：curdate(),current_date() => 2016-08-25 | 2016-08-25 |
curtime()、current_time()
获取当前时间的函数，推荐使用curtime()
eg：curtime(),current_time() => 16:15:04 | 16:15:04 |
获取日期和时间各部分值
year()函数返回日期中的年份
quarter()函数返回日期属于第几个季度
month()函数返回日期属于第几个月 monthname()函数返回了英文表示的月份
week()函数返回日期属于第几个星期
dayofmonth()函数返回日期属于当前月的第几天
hour()函数返回时间的小时
minute()函数返回时间的分钟
second()函数返回时间的秒。
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
获取指定值的extract()
extract(type from date)
上述函数会从日期和时间参数date中获取指定类型参数type的值。type的取值可以是：year，month，day，hour，minute和second
eg：select now(),extract(year from now()) year,extract(month from now()) month,extract(day from now()) day,extract(hour from now()) hour,extract(mi
nute from now()) minute,extract(second from now()) second => 2016-08-25 16:43:45 | 2016 | 8 | 25 | 16 | 43 | 45 |计算日期和时间的函数
adddate(date,n)函数：该函数计算日期参数date加上n天后的日期。
subdate(date,n)函数：该函数计算日期参数date减去n天后的日期。
subdate(d,interval expr type)：返回日期参数d减去一段时间后的日期，表达式expr决定了时间的长度。参数type决定了所操作的对象。
addtime(time,n):计算时间参数time加上n秒后的时间。
subtime(time,n):计算时间参数time减去n秒后的时间。
eg：curdate(),adddate(curdate(),5),subdate(curdate(),5) => 2016-08-25 | 2016-08-30 | 2016-08-20 |
curdate(),adddate(curdate(),interval '2,3' year_month),subdate(curdate(),interval '2,3' year_month) => 2016-08-25 | 2018-11-25 | 2014-05-25 |
其他未分类函数
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DATEDIFF(date1,date2) 函数返回两个日期之间的天数。
Eg: SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate 结果：1
SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate 结果：-1
DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
- DATE - 格式 YYYY-MM-DD
- DATETIME - 格式: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
- YEAR - 格式 YYYY 或 YY
LAST_DAY(date)日期或日期时间值，并返回对应的值，为每月的最后一天。返回NULL，如果该参数是无效的。
Eg:LAST_DAY('2003-02-05') 结果： 2003-02-28
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。该参数必须是以下值的其中一个：
- FRAC_SECOND。表示间隔是毫秒
- SECOND。秒
- MINUTE。分钟
- HOUR。小时
- DAY。天
- WEEK。星期
- MONTH。月
- QUARTER。季度
- YEAR。年
一些示例：
获取当前系统时间：
返回两个日期值之间的差值(月数)：SELECT PERIOD_DIFF(200302,199802);
在Mysql中计算年龄：
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;
这样，如果Brithday是未来的年月日的话，计算结果为0。
下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee
>>类型转换
转换函数CAST与CONVERT
CAST(value as type)
CONVERT(value, type)
用来获取一个类型的值，并产生另一个类型的值
注：可以转换的数据类型是有限制的。这个类型可以是以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
Date --> String
DATE_FORMAT(date,format) date:需要转换的日期 format:格式化的样式
format样式整理：
年： %Y 显示四位 ： 2015 %y 只显示后两位 ：15
月： %M 月份的英文显示：October %m 月份的阿拉伯显示：01-12 %b 月份的英文缩略显示：Oct %c 月份的阿拉伯显示：1-12
日： %d 阿拉伯显示：00-31 %D 带有英文后缀：1st-31th %e 阿拉伯显示：1-31 %j 年的天：001-366
时： %H ：00-23 %h:01-12 %I:01-12 %k:0-23 %l:1-12
分： %i：00-59
秒： %S：00-59 %s:00-59
微妙： %f
AM/PM:%p
12小时制时间：%r: 02:02:44 PM
24小时制时间： %T： 14:02:44
周： %W：周的英文显示 %w 周的阿拉伯显示 ：0（星期日）-6（星期六） %a 周的英文缩略显示：Mon-
eg:DATE_FORMAT(now(),"%Y-%m-%d %T") 2015-09-01 17:10:52
DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%s %p") 2015-09-01 17:10:52 PM
>>String -->Date
STR_TO_DATE(str,format) str:字符形式的日期 format:格式化样式
把字符串转换为日期。分隔符一致，年月日要一致
eg: STR_TO_DATE('1992-04-12',"%Y-%m-%d") str_to_date('2008-4-2 15:3:28','%Y-%m-%d %H:%i:%s'); 输出：1992-04-12（日期形式）
>>系统信息函数
database()
返回当前数据库名
BENCHMARK(count,expr) 将表达式expr重复运行count次
CONNECTION_ID() 返回当前客户的连接ID
FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数
user()或SYSTEM_USER()
返回当前登陆用户名
version()
返回MySQL服务器的版本
浙公网安备 33010602011771号