mysql学习之旅（七）——运算符与函数

运算符与函数

mysql的运算符

运算符概念

运算符是告诉MYSQL执行特定算数或裸机操作的符号，运算符连接表达式中各个操作数，其作用是用来指明对操作数所进行的运算。

常用运算符

一、算数运算符

运算符	作用
+	加法
-	法法
*	乘法
/	除法
%	取余

举例：
案例：

二、比较运算符

运算符	作用
>、<	大于、小于
>=、<=	大于等于、小于等于
=	等于
<>(!=)	不等于
IS(NOT) NULL	判断一个值是否为空（或不为空）
BETWEEN…AND	判断一个值是否在两个值之间
(NOT) IN	判断一个值是（或不是）IN列表中的值
LIKE	通配符匹配

例子：
1、SELECT * FROM readerinfo WHERE balance>200;
2、SELECT * FROM readerinfo WHERE balance!=200;
3、SELECT * FROM readerinfo WHERE age IS NOT NULL;
4、ELECT * FROM readerinfo WHERE balance BETWEEN 350 AND 450;
5、SELECT * FROM readerinfo WHERE NAME IS (‘张飞’,‘李月’,‘大华’);
6、SELECT * FROM readerinfo WHERE NAME LIKE ‘张_’;
7、SELECT * FROM readerinfo WHERE tel LIKE ‘134%’;

三、逻辑运算符：

运算符	作用
AND	逻辑与
OR	逻辑或
NOT	逻辑非

案例：
1、SELECT * FROM bookinfo WHERE price>50 AND store<5;
2、SELECT * FROM bookinfo WHERE price>70 OR press=‘机械工业出版社’;
3、SELECT * FROM bookinfo WHERE price NOT BETWEEN 50 AND 100;

mysql的数值函数

什么是函数

函数表示对输入参数值返回一个具有特定关系的值，masql提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作是将会经常用到各种函数。通过对数据的处理，数据库功能可以变得更加强大，更加灵活地满足不同用户的需求。

获取整数的函数

1、CEOL(x)：返回大于x的最小的整数值；
例：SELECT CEIL(28.55); 结果：29

2、FLOOR(x)：返回小于x的最大整数值。
例：SELECT FLOOR(28.55); 结果：28

3、四舍五入的函数
ROUND(x)：返回最接近于参数x的整数，对参数x进行四舍五入。
例：SELECT ROUND(28.55); 结果：29
ROUND(x,y)：返回最接近于参数x的整数，其值保留到小数点后面的y位，若y为负值，则将保留x值到小数点左边y位。
例：SELECT ROUND(28.55，1);
结果：28.6
SELECT ROUND(28.55，0);
结果：29
SELECT ROUND(28.55，-1);
结果：30

4、阶段函数
TRUNCATE(x,y)返回被舍去值小数点后y位的数字x。若y的值为0，则结果为整数，若y的值为负数，则截去x小数点左起第y位开始后面所有低位的值。
例：
SELECT TRUNCATE(28.55,1);
结果：28.5
SELECT TRUNCATE(28.55,0);
结果：28
SELECT TRUNCATE(28.55,-1)
结果：20

5、取模（求余）
MOD(x,y)返回x被y除后的余数
例：
SELECT MOD(11，2);
结果：1
eg：
SELECT CEIL(28.55);
SELECT FLOOR(28.55);
– 四舍五入
SELECT ROUND(28.55);
SELECT ROUND(28.55,1), ROUND(28.55,0), ROUND(28.55,-1);
– 截断函数
SELECT TRUNCATE(28.55,1), TRUNCATE(28.55,0),TRUNCATE(28.55,-1);
– 取模函数
SELECT MOD(11,2);

案例：
1、SELECT book_id,book_name,price,ROUND(price) FROM bookinfo;
2、SELECT * FROM bookinfo WHERE MOD(book_id,2)=0;

字符函数

字符函数主要是用来处理数据库中的字符串数据。

1、字符串连接函数
CONCAT(S1，S2，…)返回结果为连接参数产生的字符串，如果任何一个参数为NULL，则最后的返回值为NULL。
例：SELECT CONCAT(‘hello’,‘world’);
结果：helloworld
CONCAT_WS(x,S1，S2，…)第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间，分隔符可以是一个字符串，也可以是其他参数，如果分隔符为NULL,则结果为NULL。
例：SELECT CONCAT_WS(’-’,‘hello’,‘world’);
结果：hello-world

2、字母转换大小写函数
LOWER(str)可以将字符串str中的字母字符全部转换成小写字母。
例：SELECT LOWER (‘Hello World’);
结果：hello world
UPPER(str)可以将字符串str中的字母字符全部转换成大写字母。
例：SELECT UPPER (‘Hello World’);
结果：HELLO WORLD

3、求字符串长度的函数
LENGTH(str)返回值为字符串的字节长度。
例：SELECT LENGTH (’ Hello ');
结果：7

4、删除空格的函数
LTRIM(s)返回字符串s，字符左侧空格字符被删除；
RTRIM(s)返回字符串s，字符右侧空格字符被删除；
TRIM(s)返回字符串s，字符左右两侧空格字符被删除。

5、截取字符串函数
SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度同len字符相同的子字符串，起始于位置n，n如果是负数，则子字符串的位置起始于字符串结尾的n个字符。
例：SELECT SUBSTRING (’ hello world’,1,5);
结果：hello
例：SELECT SUBSTRING (‘hello world’,-3,2);
结果：rl

6、截取指定长度的字符串函数
LEFT(s,n)返回字符串s开始的最左边n个字符。
例：SELECT LEFT (‘hello world’,5);
结果：hello
RIGHT(s,n)返回字符串s开始的最右边n个字符。
例：SELECT RIGHT (‘hello world’,5);
结果：world

7、替换函数
REPLACE(str,from_str,to_str)在字符串str左右出现的字符串from_str均被to_str替换，然后返回这个字符串。
例：SELECT REPLACE (‘hello world’,‘world’,‘mysql’);
结果：hello mysql

8、格式化函数
FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果不含小数部分。
例：SELECT FORMAT (1234.5678,2);
结果：1234.57
SELECT FORMAT (1234.5,2);
结果：1234.50
SELECT FORMAT (1234.5678,0);
结果：1235

mysql的日期时间函数

日期和时间函数主要是用来处理日期和时间的函数。

1、CURDATE()和CURRENT_DATE()函数作用相同，将当前日期按照‘YYYY-MM-DD’或者YYYYMMDD格式的值返回，具体格式根据函数在字符串或者是数字语境中而定。
例如：SELECT CURDATE();
结果：2017-10-10

2、获取当前时间的函数
CURTIME()和CURRENT_TIME()函数作用相同，将当前时间按照‘HH:MM:SS’或者HHMMSS格式返回，具体格式根据函数在字符串或者是数字语境中而定。
例如：SELECT CURTIME();
结果：21：36：30

3、获取当前日期和时间的函数
NOW()和SYSDATE()两个函数作用相同，均返回当前的日期时间值，格式为’YYYY-MM-DD HH:MM:SS’或者YYYYMMDDHHMMSS格式返回，具体格式根据函数在字符串或者是数字语境中而定。
例如：SELECT NOW();
结果：2017-10-10 21：36：30

4、执行日期的加运算
DATE_ADD(date,INTERVAL expr type)其中，date是一个DATETIME或DATE值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。type为关键词，它指示了表达式被解释的方式，如：YEAR,MONTH,DAY,WEEK,HOUR等。
例如：SELECT DATE_ADD(‘2017-10-10’,INTERVAL 5 MONTH);
结果：2018-03-10

5、计算两个日期之间的间隔天数
DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。
例如：SELECT DATEDIFF(‘2017-10-10’,‘2017-11-10’);
结果：-31

6、日期格式化
DATE_FORMAT(date,format)根据format指定的格式显示date值。
DATE_FORMAT时间日期格式：
%b：月份的缩写名称（Jan…Dec）
%c：月份，数字形式（0…12）
%m：月份名称（Jannuary…December）
%d：该月日期，数字形式（00…31）
%e：该月日期，数字形式（0…31）
%Y：4位数形式表示年份
%y：2位数形式表示年份

案例：
需求一：
1、INSERT INTO borrowinfo VALUES(20150301,‘210210199901013333’,CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 MONTH),‘否’);
2、UPDATE bookinfo SET store=store-1 WHERE book_id=20150301;
3、UPDATE readerinfo SET balance=balance-69*0.05 WHERE card_id=‘210210199901013333’;
需求二：
SELECT book_id,book_category_id,book_name,author,price,press,DATE_FORMAT(pubdate,’%Y%m’),store FROM bookinfo;

mysql的聚合函数

有时候并不需要返回实际表中的数据，而只是对获取的数据进行分析和总结。这时候就需要使用聚合函数。

名称	描述
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

案例：
1、SELECT AVG(price) FROM bookinfo;
2、SELECT SUM(price) FROM bookinfo;
3、SELECT MAX(store) FROM bookinfo;
4、SELECT MIN(store) FROM bookinfo;
5、SELECT COUNT(1) FROM bookinfo;
6、SELECT book_category_id AS ‘图书类别’,COUNT(book_id) AS ‘图书种类’,SUM(store) AS ‘库存总和’ FROM bookinfo GROUP BY book_category_id;

mysql的信息函数与加密函数

系统信息函数

1、VERSION()：返回当前mysql服务器版本的版本号
2、CONNECTION_ID()：返回mysql服务器当前连接的次数，每个连接都有各自唯一的id。
3、DATABASE()和SCHEMA()：返回当前的数据库名
4、USER()：获取用户名的函数，返回当前登录的用户名。
eg:
SELECT VERSION();
SELECT CONNECTION_ID();
SELECT DATABASE();
SELECT SCHEMA();
SELECT USER();

加密函数

加密函数主要用来对数据进行加密和界面处理，以保证某些重要数据不被被人获取。
1、MD5():信息摘要算法
MD5(str)函数可以加密字符串，加密后的值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则返回NULL。一般用于web中字符串加密
eg：
INSERT INTO myuser VALUES(‘user1’,MD5(‘pwd1’));

2、PASSWORD():密码算法
PASSWORD(str)从原名文密码str计算并返回加密后的密码字符串，若参数为NULL，则返回NULL。一般用于mysql中密码加密