mysql el函数_Mysql的学习（五）-运算符与函数

最新推荐文章于 2024-08-07 20:33:11 发布

原创最新推荐文章于 2024-08-07 20:33:11 发布 · 134 阅读

CC 4.0 BY-SA版权

文章标签：

SELECT store,store+1,store-1,store/2,store*2,store%2 FROM bookinfo;//加减乘除取余SELECT * FROM readerinfo WHERE balance>200//余额大于200SELECT * FROM readerinfo WHERE balance<>200//余额不等于200SELECT * FROM readerinfo WHERE age IS NOT NULL//年龄不为空SELECT * FROM readerinfo WHERE balance BETWEEN 200 AND 300//余额200到300SELECT * FROM readerinfo WHERE NAME IN ('zhangfei','guanyu','liubei')//查询这几个读者的信息SELECT * FROM readerinfo WHERE NAME LIKE '张_'//名字是两个字，第一个字是张SELECT * FROM readerinfo WHERE WHERE tel LIKE '135%';//手机号为135开头的//and与，or或，not非SELECT * FROM readerinfo WHERE price NOT BETWEEN 50 AND 100//不在50到100之间的//各种函数SELECT CEIL(28.55)//29向上取整SELECT FLOOR(28.55)//28SELECT ROUND(28.55)//29四舍五入SELECT ROUND(28.55,1),ROUND(28.55,0),ROUND(28055,-1);//28.6 29 90(x，y)返回最接近x的数，保留小数点y位SELECT TRUNCATE(28.55,1),TRUNCATE(28.55,0),TRUNCATE(28.55,-1);//28.5 2820截断函数SELECT MOD(11,2)//1(X,Y)x被y处后的余数SELECT * FROM bookinfo WHERE MOD(book_id,2)=0;//图书编号为偶数的SELECT CONCAT('-','hello','word');//连接函数,第一个参数为连接符，不加为空.结果为hello-wordSELECT LOWER('helloWord');//

SELECT UPPER('helloWord');//大小写转换SELECT LENGTH('xxx');//长度LTRIM(s),RTRIM(s),TRIM(s)//删除左侧，右侧，两侧空格SELECT LEFT('helloworld',5)//helloSELECT RIGHT('helloworld',5)//worldSELECT REPLACE(str,from_str,to_str)//替换函数SELECT FORMAT(1234.5678,2),FORMAT(1234.5,2),FORMAT(1234.5678,0)//1234.57 1234.50 1235

SELECT CURDATE();//2018-06-19返回当前日期，最后+0则为20180619SELECT CURTIME();//当前时间SELECT NOW();//当前日期和时间SELECT DATE_ADD('2018-06-19',INTERVAL 5 MONTH)//2018-11-19

SELECT DATEDIFF('2017-02-01','2017-01-01');//间隔天数//实例表为图书标号，身份证好，借书日期，归还日期，是否归还插入信息INSERT INTO borrowinfo VALUES(20150301,'11111111111111',CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 motnth),'no');UPDATE bookinfo SET store=store-1 WHERE book_id=20150301;UPDATE readerinfo SET balance=balance-69*0.05 WHERE card_id='11111111111111';SELECT AVG(price) FROM bookinfo//所有图书的平均价格SELECT SUM(price) FROM bookinfo//总价格SELECT MAX(price) FROMbookinfoSELECT MIN(price) FROMbookinfoSELECT book_category AS '图书类别' ,COUNT(book_id) AS '图书种类' ,SUM(store) AS '库存总和' FROM bookinfo GROUP BYbook_category_id;//按类别分组查询每种类别下有多少中图书以及每种类别图书的库存总和。as为起个别名SELECT VERSION();//当前服务器版本号SELECT CONNECTION_ID();//连接次数SELECT DATABASE();//当前数据库名SELECT USER();//当前用户名//MD5加密方式，介绍为下面补图CREATE TABLEmyuesr(

usernameVARCHAR(20),

passworddVARCHAR(20)

);INSERT INTO myuser VALUES('usel',MD5('pwd1'));//表中密码信息被MD5加密SELECT * FROM myuesr//这样是看不到的SELECT * FROM myuser WHERE username = 'usel' AND passwordd=MD5('pwd1');//要通过这样知道，如果查找到了说明密码正确SET PASSWORD =PASSWORD('xxxxx');//通过password修改密码，这也是加密后的SELECT SUBSTRING("hello world',1,5);//hello

SELECT SUBSTRING("hello world',-3,2);//截断字符串 rl

先补一张日期函数格式化的图

然后是函数加密

SELECT store,store+1,store-1,store/2,store*2,store%2 FROM bookinfo;//加减乘除取余

SELECT * FROM readerinfo WHERE balance>200//余额大于200

SELECT * FROM readerinfo WHERE balance<>200//余额不等于200

SELECT * FROM readerinfo WHERE age IS NOT NULL//年龄不为空

SELECT * FROM readerinfo WHERE balance BETWEEN 200 AND 300//余额200到300

SELECT * FROM readerinfo WHERE NAME IN ('zhangfei','guanyu','liubei')//查询这几个读者的信息

SELECT * FROM readerinfo WHERE NAME LIKE '张_'//名字是两个字，第一个字是张

SELECT * FROM readerinfo WHERE WHERE tel LIKE '135%';//手机号为135开头的

//and与，or或，not非

SELECT * FROM readerinfo WHERE price NOT BETWEEN 50 AND 100//不在50到100之间的

//各种函数

SELECT CEIL(28.55)//29向上取整

SELECT FLOOR(28.55)//28

SELECT ROUND(28.55)//29 四舍五入

SELECT ROUND(28.55,1),ROUND(28.55,0),ROUND(28055,-1);//28.6 29 90(x，y)返回最接近x的数，保留小数点y位

SELECT TRUNCATE(28.55,1),TRUNCATE(28.55,0),TRUNCATE(28.55,-1);//28.5 28 20截断函数

SELECT MOD(11,2)//1 (X,Y)x被y处后的余数

SELECT * FROM bookinfo WHERE MOD(book_id,2)=0;//图书编号为偶数的

SELECT CONCAT('-','hello','word');//连接函数,第一个参数为连接符，不加为空.结果为hello-word

SELECT LOWER('helloWord');//

SELECT UPPER('helloWord');//大小写转换

SELECT LENGTH('xxx');//长度

LTRIM(s),RTRIM(s),TRIM(s)//删除左侧，右侧，两侧空格

SELECT LEFT('helloworld',5)//hello

SELECT RIGHT('helloworld',5)//world

SELECT REPLACE(str,from_str,to_str)//替换函数

SELECT FORMAT(1234.5678,2),FORMAT(1234.5,2),FORMAT(1234.5678,0)//1234.57 1234.50 1235

SELECT CURDATE();//2018-06-19返回当前日期，最后+0则为20180619

SELECT CURTIME();//当前时间

SELECT NOW();//当前日期和时间

SELECT DATE_ADD('2018-06-19',INTERVAL 5 MONTH)//2018-11-19

SELECT DATEDIFF('2017-02-01','2017-01-01');//间隔天数

//实例表为图书标号，身份证好，借书日期，归还日期，是否归还插入信息

INSERT INTO borrowinfo VALUES(20150301,'11111111111111',CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 motnth),'no');

UPDATE bookinfo SET store=store-1 WHERE book_id=20150301;

UPDATE readerinfo SET balance=balance-69*0.05 WHERE card_id='11111111111111';

SELECT AVG(price) FROM bookinfo//所有图书的平均价格

SELECT SUM(price) FROM bookinfo//总价格

SELECT MAX(price) FROM bookinfo

SELECT MIN(price) FROM bookinfo

SELECT book_category AS '图书类别' ,COUNT(book_id) AS '图书种类' ,SUM(store) AS '库存总和' FROM bookinfo GROUP BY book_category_id;

//按类别分组查询每种类别下有多少中图书以及每种类别图书的库存总和。as为起个别名

SELECT VERSION();//当前服务器版本号

SELECT CONNECTION_ID();//连接次数

SELECT DATABASE();//当前数据库名

SELECT USER();//当前用户名

//MD5加密方式，介绍为下面补图

CREATE TABLE myuesr(

username VARCHAR(20),

passwordd VARCHAR(20)

);

INSERT INTO myuser VALUES('usel',MD5('pwd1'));//表中密码信息被MD5加密

SELECT * FROM myuesr//这样是看不到的

SELECT * FROM myuser WHERE username = 'usel' AND passwordd=MD5('pwd1');//要通过这样知道，如果查找到了说明密码正确

SET PASSWORD =PASSWORD('xxxxx');//通过password修改密码，这也是加密后的

SELECT SUBSTRING("hello world',1,5);

SELECT SUBSTRING("hello world',-3,2);//截断字符串 rl