MySQL之运算符和函数

MySQL中的函数主要有一下几类:

 字符函数;数值运算符和函数;比较运算符和函数;日期时间函数;信息函数;聚合函数;加密函数

1、字符函数

1、CONCAT()

root@localhost test>SELECT CONCAT('study','MySQL');

可以使用连接符进行输出,如将study和MySQL之间进行连接,输出study-MySQL的形式

root@localhost test>select CONCAT('study','-','MySQL');

首先创建一个存储名字的数据表

root@localhost test>create table name(
    -> first_name varchar(20),
    -> last_name varchar(20)
    -> );

root@localhost test>insert name(first_name,last_name) VALUES ('Tom','.J'),('Jam','.L'),('KoBe','.H');

现在为了获取完整的名字信息,将上述的名字信息输出,可使用CONCAT命令进行输出

root@localhost test>select CONCAT(first_name,last_name) AS full_name  FROM name;

2、CONCAT_WS()

而CONCAT_WS指定连接符,第一个参数为连接符,第二个参数为需要连接的参数1,第三个参数为需要连接的参数2,以此类推

root@localhost test>select CONCAT_WS('|','A','B','C');


3、FORMAT()

FORMAT进行数字字符化,之所以把这个函数归结到字符函数中,是因为其返回结果是一个字符型

root@localhost test>select FORMAT(12560.75456,2);

而如果想保留到整数位,则将‘2’对应的参数位置变成0即可

root@localhost test>select FORMAT(12560.75456,0);

4、UPPER/LOWER

大小写转换,分别是将大写字母转换成小写字母;把消息字母转换成大写字母

root@localhost test>select UPPER('mysql');
root@localhost test>select LOWER('MYSQL');

5、LEFT/RIGHT

从字符串的左侧开始获取;或者从字符串的右侧开始获取,有两个参数

root@localhost test>select LEFT('study-MySQL',5);

root@localhost test>select RIGHT('study-MySQL',5);

同时使用LOWER和RIGHT函数

root@localhost test>select LOWER(RIGHT('study-MySQL',5));

当然还有几个函数


root@localhost test>select LENGTH('study-MySQL');
root@localhost test>select LTRIM('  study-MySQL  ');删除前导空格,指的是删除第一个字符之前的空格
root@localhost test>select RTRIM('  study-MySQL  ');删除后续空格,指的是删除最后一个字符之后的空格
root@localhost test>select TRIM('  study-MySQL  ');删除前导空格和后续空格

此外TRIM函数还有别的用法

root@localhost test>select TRIM(LEADING '?' FROM '??study-MySQL???');

表示删除字符串中的前导的“?”字符,使用关键字LEADING


root@localhost test>select TRIM(TRAILING '?' FROM '??study-MySQL???');

表示删除字符串中的后续的“?”字符,使用关键字TRAILING


删除前导和后续所有的?字符,使用关键字BOTH

root@localhost test>select TRIM(BOTH '?' FROM '??study-MySQL???');

对于??study??MySQL??中间的?如何处理呢?通过使用replace进行替换

root@localhost test>select REPLACE('??My??SQL???','?','');

在替换的时候不是说一个字符只能替换一个字符,可以是一对多,如:

root@localhost test>select REPLACE('??My??SQL???','?','!!');

将一个?替换成2个!如上所示。也可以是多对一,如将两个?换成一个!,如:

root@localhost test>select REPLACE('??My??SQL???','??','!');

因为右边有三个?,因此只能替换两个?,还有一个?成单了,无法替换

对于SUBSTRING是进行字符串的截取,有三个参数,分别是从哪个字符串中截取,从第几位开始截取,以及截取几个参数

root@localhost test>select SUBSTRING('study-MySQL',7,5);

substring(str, pos); substring(str, pos, len)

在字符串study-MySQL中从第7位开始截取,截取5个,注意这里与程序中不同,其下标是从1开始的。

如果只写如两个参数,如下表示从这一位开始一直截取到字符串的结尾

root@localhost test>select SUBSTRING('study-MySQL',7);

root@localhost test>select SUBSTRING('study-MySQL',-5,5);

从字符串的倒数第5位开始,共截取5个字符

root@localhost test>select SUBSTRING('study-MySQL',-5);

从字符串的倒数第5位开始,一直截取到结束

对于LIKE和NOT LIKE的匹配与否的问题

root@localhost test>select 'MySQL' like 'M%';

%这里表示0个或者多个字符


这里的“1”表示 true,但这里没有体现LIKE的强大功能,现在在数据表name中插入一个记录

root@localhost test>insert name VALUES ('JJ%','.M');

现在想找到名字中包含 J 的记录,可以如下:

root@localhost test>select * from name WHERE first_name LIKE '%J%';


那如果想找到名字中包含%就应该

root@localhost test>select * from name WHERE first_name LIKE '%%%';

第一个和最后一个%表示任意字符,中间的%表示需要查找的字符,但是这样MySQL认为这三个都是通配符,因此会找出所有记录,那如何解决呢?

root@localhost test>select * from name WHERE first_name LIKE '%1%%' ESCAPE 1;

告诉系统1后面的这个字符不需要再进行解析,直接认为是标准的%字符就可以了,这里不一定非要为1,只要是数字就可以

另外 “_”匹配的是任意一个字符,“%”匹配的是任意个字符。

2、数值运算符和函数

root@localhost test>select CEIL(3.01);        向上取整,为4
root@localhost test>select FLOOR(3.99);       向下取整,为3
root@localhost test>select 3/4;               普通除法,为0.75
root@localhost test>select 3 DIV 4;           整数除法,向下取整
root@localhost test>select 5 MOD 3;           取余,等价于%取余操作
root@localhost test>select 5.2 MOD 3;         取余位2.2
root@localhost test>select 5 % 3;
root@localhost test>select POWER(2,3);        幂运算,2的3次方
root@localhost test>select ROUND(3.642,2);    保留2位,为3.64
root@localhost test>select ROUND(3.642,1);    保留1位,为3.6
root@localhost test>select ROUND(3.642,0);    只保留到整数,为3.7
root@localhost test>select TRUNCATE(125.89,2);只保留2位,为125.89
root@localhost test>select TRUNCATE(125.89,1);只保留1位,为125.8
root@localhost test>select TRUNCATE(125.89,0);只保留0位,为125
root@localhost test>select TRUNCATE(125.89,-1);为120

3、比较运算符和函数

root@localhost test>select 15 BETWEEN 10 AND 20;

root@localhost test>select 5 IN(2,3,5,7,9);

判断5在不在序列范围之间,在的话输出为1


root@localhost test>insert name VALUE (NULL,'KM');

查看first_name中为空的记录

root@localhost test>select * from name WHERE first_name IS NULL;

4、日期时间函数

root@localhost test>select NOW();      显示当前时间
root@localhost test>select DATE_ADD('2014-3-13',INTERVAL 365 DAY );  2015-03-13
root@localhost test>select DATE_ADD('2014-3-13',INTERVAL 1 YEAR );  2015-03-13
root@localhost test>select DATE_ADD('2014-3-13',INTERVAL -365 DAY );  2013-03-13
root@localhost test>select DATE_ADD('2014-3-13',INTERVAL 3 WEEK);  2013-03-13
root@localhost test>select DATEDIFF('2014-3-6','2014-4-2'); 日期差值运算为-27
root@localhost test>select DATE_FORMAT('2014-4-17','%m/%d/%Y');日期输出为04/17/2014

5、信息函数


root@localhost test>select CONNECTION_ID();
root@localhost test>SELECT DATABASE();

而对于LAST_INSERT_ID,在数据表name中没有id字段,如果要得到last_insert_id必须在数据表中存在一个自动编号的字段


root@localhost test>alter table name ADD id SMALLINT UNSIGNED KEY
    -> AUTO_INCREMENT FIRST;


root@localhost test>INSERT name(first_name,last_name) VALUES ('KKK','.JJJ');

现在新写入的记录的ID为6,如果想要通过函数得到这个id可以用:

root@localhost test>select LAST_INSERT_ID();

如果同时写入多条记录呢,那只会返回第一条记录对应的id

root@localhost test>INSERT name(first_name,last_name) VALUES ('KKK','.JJJ'),('AAA','.BBB');

root@localhost test>select USER(); 当前登录用户
root@localhost test>SELECT VERSION();当前版本信息

6、聚合函数

聚合函数的一个特点是只有一个返回值

root@localhost test>select avg(goods_price) AS avg_price FROM   goods;

计算数据表goods中的平均价格,保留到小数点后的两位

root@localhost test>select ROUND(AVG(goods_price),2) AS avg_price FROM goods;

root@localhost test>select COUNT(goods_id) AS coutsID FROM goods;

root@localhost test>select MAX(goods_price) AS coutsID FROM goods;
root@localhost test>select MIN(goods_price) AS coutsID FROM goods;
root@localhost test>select SUM(goods_price) AS coutsID FROM goods;

6、加密函数


可以对任何一个信息进行MD5的加密,加密后的结果是一个32的数字

root@localhost test>select MD5('admin');

而PASSWORD是进行密码的计算

root@localhost test>select PASSWORD('admin');

如果MySQL中是为了以后的Web页面做准备,尽量使用MD5,而PASSWORD是为了修改客户端密码

root@localhost test>select PASSWORD = PASSWORD('delimater');
将登陆密码修改为 delimater,退出后再次登陆即可生效。


阅读更多
个人分类: MySQL
上一篇MySQL之无限级分类表设计
下一篇MySQL之alter和upate
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭