第七章:运算符与函数
大家好,我是宾哥!今天我将详细的为大家介绍MySQL中的运算符相关知识,希望大家能够从中收获多多!
在数据库操作时,数据的删除,查询,更新等操作都可以使用WHERE条件表达式来对获取到的符合条件的记录进行这些操作,此时就需要利用到MySQL中所提供的运算符。下面将对MySQL中的运算符进行详细的讲解。
本节目标:熟悉MySQL中常用运算符的使用
运算符概述:运算符是告诉MySQL执行特定算术或逻辑操作的符号,运算符连接表达式中各个操作数,其作用是用来指明对操作数所进行的运算。
常用的运算符
1、算术运算符:算术运算符用于各类数值运算
运算符 | 作用 |
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法 |
% | 取余(取模) |
select * from bookinfo; select store, store+1,store-1,store/2, store*2, store%2 from bookinfo;
案例
对读者余额进行统计,查询余额减去押金小于等于0的读者,并显示此读者的身份证号,姓名,电话,余额。
select card_id, name, tel, balance from readerinfo where balance-200<=0;
2、比较运算符
运算符 | 作用 |
>、< | 大于、小于 |
>=、<= | 大于等于、小于等于 |
= | 等于 |
<>(!=) | 不等于 |
IS(NOT)NULL | 判断一个值是否为空(或不为空) |
BETWEEN…AND | 判断一个值是否在两个值之间 |
(NOT)IN | 判断一个值是(或不是)IN列表中的值 |
LIKE | 通配符 |
案例:
1)、查看读者信息表中,余额大于200的读者信息。
select * from readerinfo where balance>200;
2)、查看读者信息表中,余额不等于200的读者信息。
select * from readerinfo where balance <> 200;
3)、查看读者信息表中,年龄不为空的读者信息。
select * from readerinfo where age is not null;
4)、查看读者信息表中,余额在350到450之间的读者信息。
select * from readerinfo where balance between 350 and 450;
5)、查看读者信息表,获取”张飞 ” , ”李月”, “王鹏”这几个读者的信息。
select * from readerinfo where name in('张飞','李月','王鹏');
6)、查看读者信息表,名字是两个字的,姓张的读者。
select * from readerinfo where name like '张_';
7)、查询手机号以”135”开头的读者信息。
select * from readerinfo where tel like '135%';
3、逻辑运算符
运算符 | 作用 |
AND | 逻辑与(并且:条件同时满足) |
OR | 逻辑或(满足任意一个条件) |
NOT | 逻辑非(不满足它所修饰的条件就可以) |
案例:
1)、查询图书信息表,查询书的价格大于50并且库存小于5的图书信息。
select * from bookinfo where price>50 and store<5;
2)、查询图书信息表,查询书的价格大于80或者出版社为’机械工业出版社’的。
select * from bookinfo where price>80 or press = '机械工业出版社';
3)、查询图书信息表,查询书的价格不在50到100之间的图书信息。
select * from bookinfo where price not between 50 and 100;
本节目标:熟悉数值函数的操作。
函数:函数表示对输入参数值返回一个具有特定关系的值,MySQL提供了大量丰富的函数,在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。通过对数据的处理,数据库功能可以变得更加强大,更加灵活的满足不同用户的需求。
数值函数(数学函数)
1)获取整数的函数
CEIL(x):返回大于x的最小整数值。
例:SELECT CEIL (28.55)
结果:29
FLOOR(x):返回小于x的最大整数值。
例:SELECT FLOOR(28.55)
结果:28
2)四舍五入的函数
ROUND(x):返回最接近于参数x的整数,对参数x进行四舍五入。
例:SELECT ROUND(28.55);
结果:29
ROUND(x,y):返回最接近于参数x的值,其值保留到小数点后面的y位,若y为负值,则将保留x值到小数点左边y位。
例:SELECT ROUND(28.55,1), ROUND(28.55,0), ROUND(28.55,-1);
结果:28.6 29 30
3)截断函数
TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0,则结果为整数;若y的值为负数,则截去x小数点左起第y位开始后面所有低位的值。
例:SELECT TRUNCATE (28.55,1),TRUNCATE (28.55,0),TRUNCATE (28.55,-1),
结果:28.5 28 20
4)取模(求余)
MOD(x,y)返回x被y除后的余数。
例:SELECT MOD(11,2);
结果:1
案例:
1、现需要对图书的价格进行粗略统计,要求查看图书信息表中的图书编号,书名,四舍五入书的价格到整数位。
select book_id,book_name,price, round(price) from bookinfo;
2查询图书编号为偶数的图书信息。
select * from bookinfo where mod(book_id,2)=0;
本节目标:熟悉字符函数的使用
字符函数主要用来处理数据库中的字符串数据。
1、字符串连接函数
1)CONCAT(s1,s2,…)返回结果为连接参数产生的字符串,如果任何一个参数为NULL,则返回值为NULL。
例:SELECT CONCAT('hello','world');
结果:helloworld
2)CONCAT_WS(x,s1,s2,…),第一个参数x是其他参数的分隔符,分隔符的位置放在要连接的两个字符串之间,分隔符可以是一个字符串,也可以是其他参数,如果分隔符为NULL,则结果为NULL。
例:SELECT CONCAT_WS('-','hello','world');
结果:hello-world
2、字母转换大小写函数
1)LOWER(str)可以将字符串str中的字母字符全部转换成小写字母。
例:SELECT LOWER('Hello World');
结果:hell world
2)UPPER(str)可以将字符串str中的字母字符全部转换成大写字母。
例:SELECT UPPER('Hello World');
结果:HELLO WORLD
3、求字符串长度的函数
LENGTH(str)返回值为字符串的字节长度。
例:SELECT LENGTH(' hello ');
结果:7
4、删除空格的函数
1)LTRIM(s)返回字符串s,字符左侧空格字符被删除。
2)RTRIM(s)返回字符串s,字符右侧空格字符被删除。
3)TRIM(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、获取指定长度的字符串函数
1)LEFT(s,n)返回字符串s开始的最左边n个字符。
例:SELECT LEFT ('hello world',5);
结果:hello
2)RIGHT(s,n)返回字符串中最右边n个字符。
例:SELECT RIGHT ('hello world',5);
结果:world
7、替换函数
REPLACE(str,from_str,to_str)在字符串str中所有出现的字符串form_str均被to_str替换,然后返回这个字符串。
例:SELECT REPLACE('hello word','word','mysql');
结果:hello mysql
8、格式化函数
FORMAT(x,n)将数字x格式化,并以四舍五入的方式保留小数点后n位,结果以字符串的形式返回,若n为0,则返回结果不含小数部分。
例:SELECT FORMAT(1234.5678,2),FORMAT(1234.5,2),FORMAT(1234.5678,0);
结果:1234.57 1234.50 1235
本节目标:熟悉日期时间函数的使用
日期时间函数主要用来处理日期和时间值。
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 22:22:22
4)、执行日期的加运算
DATE_ADD(date,INTERVAL expr type)其中,data是一个DATATIME或DATA值,用来指定起始时间,expr是一个表达式,用来指定起始日期添加或减去的时间间隔。type为关键字,它指示了表达式被解释的方式,如:YEAR, MONTH, DAY, WEEK, HOUR等。
例:SELECT DATE_ADD('2017-01-01',INTERVAL 5 MONTH);
结果:2017-06-01
5)、计算两个日期之间的间隔天数
DATEDIFF(date1,data2)返回起始时间date1和结束时间date2之间的天数。
例:SELECT DATEDIFF('2017-02-01','2017-01-01')
结果:31
6)、日期格式化
DATA_FORMAT(date,format)根据format指定的格式显示date值。
DATE_FORMAT时间日期格式:
%b:月份的缩写名称(Jan……Dec)
%c:月份,数字形式(0……12)
%m:月份,数字形式(00……12)
%M:月份名称(January……December)
%d:该月日期,数字形式(00……31)
%e:该月日期,数字形式(0……31)
%Y:4位数形式表示年份
%y:2位数形式表示年份
案例:
1.查看当前的系统日期
select curdate()+0;
2.查看当前的系统时间
select curtime()+0;
3.查看当前的系统日期和时间
select now();
4.查看当前的系统日期和时间
select sysdate();
5.日期的加运算
select date_add('2017-01-01', interval 5 month);
6.计算两个日期之间间隔的天数
select datediff('2017-02-10','2017-02-01');
7.日期格式化
select date_format('2017-02-01', '%Y%m');
8.需求一:身份证号为’ 210210199901013333’的用户,今天借了一本图书编号为’ 20150301’的图书,完成下列业务需求:
1、向借阅信息表中插入借书信息,注意借书日期和还书日期。
2、更新图书信息表的库存。
3、更新读者信息表的余额。*/
insert into borrowinfo values(20150301,'210210199901013333',curdate(),date_add(curdate(),interval 1 month),'否'); select * from borrowinfo; select * from bookinfo where book_id = 20150301; update bookinfo set store = store -1 where book_id = 20150301; select * from readerinfo where card_id = '210210199901013333'; select price from bookinfo where book_id = 20150301; update readerinfo set balance = balance - 69 * 0.05 where card_id = '210210199901013333';
9.需求二:查看图书信息表的信息,要求出版日期’如201702’ 这样的形式显示。
select book_id,book_category_id,book_name,author,price,press,date_format(pubdate,'%Y-%m'),store from bookinfo;
本节目标:熟悉聚合函数的使用
有时候并不需要返回实际表中的数据,而只是对获取的数据进行分析和总结。这时候就需要使用聚合函数。
聚合函数(分组函数)
名称 | 描述 |
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(*) from bookinfo;
6.按类别进行分组, 查询每种类别下有多少种图书以及每种类别图书的库存总和。
select book_category_id as '图书类别',count(book_id) as '图书种类', sum(store) as '库存总和' from bookinfo group by book_category_id;
本节目标:熟悉信息函数和加密函数的使用
1、信息系统函数
1).VERSION():返回当期MySQL服务器版本的版本号。
2).CONNETTION_ID():返回MySQL服务器当前连接的次数,每个连接都有各自唯一的ID。
3).DATABASE()和SCHEMA():返回当前的数据库名。
4).USER():获取用户名的函数,返回当前登录的用户名称。
2、加密函数
加密函数主要用来对数据进行加密和界面处理,以保证某些重要数据不被别人获取。
1).MD5():信息摘要算法
MD5(str)函数可以加密字符串,加密后的值以32位十六进制数字的二进制字符串形式返回,若参数为NULL,则返回NULL。
2).PASSWORD():密码算法
PASSWORD(str)从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。
案例:系统信息函数
1.查看当前MySQL服务器版本的版本号
select version();
2.查看MySQL服务器当前连接的次数
select connection_id();
3.查看当前的数据库名
select schema();
4.查看当前登录的用户名
select user();
5.加密函数
select md5('test'); create table myuser( username varchar(10), password varchar(35) ); insert into myuser values('user1',md5('pwd1')); select * from myuser; select * from myuser where username = 'user1' and password = md5('pwd1'); select password('rootpwd'); set password = password('rootpwd'); select user,authentication_string from mysql.user;