MySql运算符和函数.ppt

MySql运算符,算术运算符比较运算符逻辑运算符位运算符,算术运算符,+加法-减法*乘法/除法%取余,算术运算符,如果第一位是数字的字符串被用于一个算术运算中，则它被转换为这个数字的值。如果一个包含字符和数字混合的字符串不能被正确地转换为数字，则转换为0select500+00002,8.9*10;select90aa+0,aa90+0,10 x*1,pq*1;,练习,有一家玩具公司4种产品的成本价(厂商价CTM)、批发价(给零售商的价CTR)、零售价(消费者的价格CTC)和当前存货量createtabletoys(idint(4)notnullauto_increment,itemvarchar(20)notnull,ctmfloat(6,2)notnull,ctrfloat(6,2),ctcfloat(6,2)notnull,quantityint(6),primarykey(id);测试数据insertintotoysvalues(null,Huggybear,5.00,7.40,9.90,300);insertintotoysvalues(null,Coloringbook,3.00,5.00,7.99,2000);insertintotoysvalues(null,Boardgame,12.00,20.00,39.90,1430);insertintotoysvalues(null,ActivityCD-ROM,2.00,6.00,17.49,3780);,练习,为厂商查看每一产品的利润selectitem,ctr-ctmfromtoys;为零售商查看利润selectitem,ctc-ctrfromtoys;求出每个产品在厂家生产它和消费者购买它这段时间内价格上涨的情况selectitem,ctc-ctm,(ctc-ctm)/ctm*100fromtoys;得到厂商以产品作为分类的库存货值selectitem,ctm*quantityfromtoys;由厂商提供给零售商的每种产品的折扣selectitem,ctc-ctr,(ctc-ctr)/ctm*100fromtoys;,比较运算符,一个比较运算的结果总是1(真)、0(假)、NULL(不确定)=等于或!=不等于、=小于、小于等于、大于、大于等于between存在于指定范围in存在于指定集合isnull为nullisnotnull不为nulllike通配符匹配regexp或rlike正则表达式匹配,比较运算符,select6=6aa,6=6aa;select2=2,2.3=2.4,2.415=2.415000;selecta=a,a=A,A=B,apple=around;select77,78;selectnullnull,0null,00;selectapple=Apple,binaryapple=Apple;,比较运算符,bwtween验证一个值是否在一个指定范围内select10between0and10,10between11and100;selectybetweenxandz,zbetweenxandy;可以添加NOT逻辑运算符对一个between比较取反select99notbetween10and50,fnotbetweenaandz;in运算符用于检验一个值是否包含在一个指定的值集合中select7in(1,2,3,4,5,6,7,8);selectredin(red,green,blue);用binary区分大小写selectbinaryredin(Red,green,blue);,比较运算符,当求值表达式或不匹配集合中的值有NULL时，IN运算符返回NULLselectnullin(1,2),nullin(1,2,null);对IN运算符取反select3notin(1,2);测试实际NULL值存在或不存在使用isnull或isnotnullselect88isnull,0isnull,nullisnull,0=nullisnull;,逻辑运算符,在SQL中，所有逻辑操作符的求值所得结果均为TRUE、FALSE或NULL(UNKNOWN)。在MySQL中，它们体现为1(TRUE)、0(FALSE)和NULL。,NOT!,当操作数为0时，所得值为1；当操作数为非零值时，所得值为0，而当操作数为NOTNULL时，所得的返回值为NULL。SELECTNOT10;SELECTNOT0;SELECTNOTNULL;SELECT!(1+1);SELECT!1+1;,ANDSELECT1|0;SELECT0|0;SELECT0|NULL;SELECT1|NULL;,XOR,逻辑XOR。当任意一个操作数为NULL时，返回值为NULL。SELECT1XOR1;SELECT1XOR0;SELECT1XORNULL;SELECT1XOR1XOR1;,控制流程函数,CASEvalueWHENcompare-valueTHENresultWHENcompare-valueTHENresult.ELSEresultENDCASEWHENconditionTHENresultWHENconditionTHENresult.ELSEresultEND在第一个方案的返回结果中，value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE部分，则返回值为NULL。,控制流程函数,SELECTCASE11WHEN1THENoneWHEN2THENtwoELSEmoreEND;SELECTCASEWHEN10THENtrueELSEfalseEND;SELECTCASEBINARYBWHENaTHEN1WHENbTHEN2END;,IF(expr1,expr2,expr3),如果expr1是TRUE(expr10andexpr1NULL)，则IF()的返回值为expr2;否则返回值则为expr3。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。SELECTIF(12,2,3);SELECTIF(1str2返回1,str1=str2反回0，str1str2返回-1),STRCMP(expr1,expr2),如果字符串相同，STRCMP()返回0，如果第一参数根据当前的排序次序小于第二个，返回-1，否则返回1。selectSTRCMP(text,text2);selectSTRCMP(text2,text);selectSTRCMP(text,text);,数学函数,ABS(X):返回X的绝对值selectABS(-32);MOD(N,M)或%:返回N被M除的余数。selectMOD(15,7);select15%7;FLOOR(X):返回不大于X的最大整数值。selectFLOOR(1.23);selectFLOOR(-1.23);CEILING(X):返回不小于X的最小整数值。selectCEILING(1.23);selectCEILING(-1.23);ROUND(X):返回参数X的四舍五入的一个整数。selectROUND(1.58);selectROUND(-1.58);,字符串函数,ASCII(str):返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。selectASCII(2);selectASCII(2);selectASCII(dx)CONCAT(str1,str2,.):返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。selectCONCAT(My,S,QL);selectCONCAT(My,NULL,QL);selectCONCAT(14.3);LENGTH(str):返回字符串str的长度。selectLENGTH(text);,字符串函数,LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.selectLOCATE(bar,foobarbar);selectLOCATE(xbar,foobar);INSTR(str,substr):返回子串substr在字符串str中的第一个出现的位置。selectINSTR(foobarbar,bar);selectINSTR(xbar,foobar);LEFT(str,len):返回字符串str的最左面len个字符。selectLEFT(foobarbar,5);RIGHT(str,len):返回字符串str的最右面len个字符。selectRIGHT(foobarbar,4);,字符串函数,SUBSTRING(str,pos):从字符串str的起始位置pos返回一个子串。selectSUBSTRING(Quadratically,5);TRIM(str):返回字符串str，所有前缀或后缀被删除了。selectTRIM(bar);LTRIM(str):返回删除了其前置空格字符的字符串str。selectLTRIM(barbar);RTRIM(str):返回删除了其拖后空格字符的字符串str。selectRTRIM(barbar);REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替。selectREPLACE(,w,Ww);REPEAT(str,count):返回由重复countTimes次的字符串str组成的一个字符串。如果count=0，返回一个空字符串。如果str或count是NULL，返回NULL。selectREPEAT(MySQL,3);,字符串函数,REVERSE(str):返回颠倒字符顺序的字符串str。selectREVERSE(abc);INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。selectINSERT(whatareyou,5,3,is);,日期和时间函数,DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一,7=星期六)。selectDAYOFWEEK(1998-02-03);selectDAYOFWEEK(now();WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二,6=星期天)。selectWEEKDAY(1997-11-05);DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。selectDAYOFMONTH(1998-02-03);DAYOFYEAR(date):返回date在一年中的日数,在1到366范围内。selectDAYOFYEAR(1998-02-03);MONTH(date):返回date的月份，范围1到12。selectMONTH(1998-02-03);DAYNAME(date):返回date的星期名字。selectDAYNAME(1998-02-05);MONTHNAME(date):返回date的月份名字。selectMONTHNAME(1998-02-05);QUARTER(date):返回date一年中的季度，范围1到4。selectQUARTER(98-04-01);,日期和时间函数,WEEK(date,first):对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。selectWEEK(2009-02-20);selectWEEK(2009-02-20,0);selectWEEK(2009-02-20,1);,日期和时间函数,YEAR(date):返回date的年份，范围在1000到9999。selectYEAR(98-02-03);HOUR(time):返回time的小时，范围是0到23。selectHOUR(10:05:03);MINUTE(time):返回time的分钟，范围是0到59。selectMINUTE(98-02-0310:05:03);SECOND(time):回来time的秒数，范围是0到59。selectSECOND(10:05:03);,日期和时间函数,DATE_ADD(date,INTERVALexprtype)DATE_SUB(date,INTERVALexprtype)SELECT1997-12-3123:59:59+INTERVAL1SECOND;SELECTINTERVAL1DAY+1997-12-31;SELECT1998-01-01-INTERVAL1SECOND;SELECTDATE_ADD(1997-12-3123:59:59,INTERVAL1SECOND);SELECTDATE_ADD(1997-12-3123:59:59,INTERVAL1:1MINUTE_SECOND);SELECTDATE_SUB(1998