MySql运算符和函数.ppt

MySql运算符,算术运算符 比较运算符 逻辑运算符 位运算符,算术运算符,+ 加法 - 减法 * 乘法 / 除法 % 取余,算术运算符,如果第一位是数字的字符串被用于一个算术运算中，则它被转换为这个数字的值。 如果一个包含字符和数字混合的字符串不能被正确地转换为数字，则转换为0 select 500+00002,8.9*10; select 90aa+0,aa90+0,10x*1,pq*1;,练习,有一家玩具公司4种产品的成本价(厂商价CTM)、批发价(给零售商的价CTR)、零售价(消费者的价格CTC)和当前存货量 create table toys(id int(4) not null auto_increment,item varchar(20) not null,ctm float(6,2) not null, ctr float(6,2),ctc float(6,2) not null,quantity int(6), primary key(id); 测试数据 insert into toys values(null,Huggy bear,5.00,7.40,9.90,300); insert into toys values(null,Coloring book,3.00,5.00,7.99,2000); insert into toys values(null,Board game,12.00,20.00,39.90,1430); insert into toys values(null,Activity CD-ROM,2.00,6.00,17.49,3780);,练习,为厂商查看每一产品的利润 select item,ctr-ctm from toys; 为零售商查看利润 select item,ctc-ctr from toys; 求出每个产品在厂家生产它和消费者购买它这段时间内价格上涨的情况 select item,ctc-ctm,(ctc-ctm)/ctm*100 from toys; 得到厂商以产品作为分类的库存货值 select item,ctm*quantity from toys; 由厂商提供给零售商的每种产品的折扣 select item,ctc-ctr,(ctc-ctr)/ctm*100 from toys;,比较运算符,一个比较运算的结果总是1(真)、0(假)、NULL(不确定) = 等于 或!= 不等于 、= 小于、小于等于、大于、大于等于 between 存在于指定范围 in 存在于指定集合 is null 为null is not null 不为null like 通配符匹配 regexp或rlike 正则表达式匹配,比较运算符,select 6=6aa,6=6aa; select 2=2,2.3=2.4,2.415=2.415000; select a=a,a=A,A=B,apple=around; select 77,78; select nullnull,0null,00; select apple=Apple,binary apple=Apple;,比较运算符,bwtween验证一个值是否在一个指定范围内 select 10 between 0 and 10,10 between 11 and 100; select y between x and z, z between x and y; 可以添加NOT逻辑运算符对一个between比较取反 select 99 not between 10 and 50,f not between a and z; in运算符用于检验一个值是否包含在一个指定的值集合中 select 7 in (1,2,3,4,5,6,7,8); select red in (red,green,blue); 用binary区分大小写 select binary red in (Red,green,blue);,比较运算符,当求值表达式或不匹配集合中的值有NULL时，IN运算符返回NULL select null in(1,2),null in(1,2,null); 对IN运算符取反 select 3 not in (1,2); 测试实际NULL值存在或不存在使用is null或is not null select 88 is null,0 is null, null is null, 0=null is null;,逻辑运算符,在SQL中，所有逻辑操作符的求值所得结果均为 TRUE、FALSE或 NULL (UNKNOWN)。在 MySQL中，它们体现为 1 (TRUE)、 0 (FALSE)和 NULL。,NOT !,当操作数为0 时，所得值为 1 ；当操作数为非零值时，所得值为 0 ，而当操作数为NOT NULL时，所得的返回值为 NULL。 SELECT NOT 10; SELECT NOT 0; SELECT NOT NULL; SELECT ! (1+1); SELECT ! 1+1;,AND &,逻辑AND。当所有操作数均为非零值、并且不为NULL时，计算所得结果为 1 ，当一个或多个操作数为0 时，所得结果为 0 ，其余情况返回值为 NULL 。 SELECT 1 ,OR |,逻辑 OR。当两个操作数均为非 NULL值时，如有任意一个操作数为非零值，则结果为1，否则结果为0。当有一个操作数为NULL时，如另一个操作数为非零值，则结果为1，否则结果为 NULL 。假如两个操作数均为 NULL，则所得结果为 NULL。 SELECT 1 | 1; SELECT 1 | 0; SELECT 0 | 0; SELECT 0 | NULL; SELECT 1 | NULL;,XOR,逻辑XOR。当任意一个操作数为 NULL时，返回值为NULL。 SELECT 1 XOR 1; SELECT 1 XOR 0; SELECT 1 XOR NULL; SELECT 1 XOR 1 XOR 1;,控制流程函数,CASE value WHEN compare-value THEN result WHEN compare-value THEN result . ELSE result END CASE WHEN condition THEN result WHEN condition THEN result . ELSE result END 在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。,控制流程函数,SELECT CASE 11 WHEN 1 THEN one WHEN 2 THEN two ELSE more END; SELECT CASE WHEN 10 THEN true ELSE false END; SELECT CASE BINARY B WHEN a THEN 1 WHEN b THEN 2 END;,IF(expr1,expr2,expr3),如果 expr1 是TRUE (expr1 0 and expr1 NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。 SELECT IF(12,2,3); SELECT IF(1str2返回1,str1=str2反回0，str1str2返回-1),STRCMP(expr1,expr2),如果字符串相同，STRCMP()返回0，如果第一参数根据当前的排序次序小于第二个，返回-1，否则返回1。 select STRCMP(text, text2); select STRCMP(text2, text); select STRCMP(text, text);,数学函数,ABS(X):返回X的绝对值 select ABS(-32); MOD(N,M)或%:返回N被M除的余数。 select MOD(15,7); select 15 % 7; FLOOR(X):返回不大于X的最大整数值。 select FLOOR(1.23); select FLOOR(-1.23); CEILING(X):返回不小于X的最小整数值。 select CEILING(1.23); select CEILING(-1.23); ROUND(X) :返回参数X的四舍五入的一个整数。 select ROUND(1.58); select ROUND(-1.58);,字符串函数,ASCII(str):返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。 select ASCII(2); select ASCII(2); select ASCII(dx) CONCAT(str1,str2,.):返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。 select CONCAT(My, S, QL); select CONCAT(My, NULL, QL); select CONCAT(14.3); LENGTH(str):返回字符串str的长度。 select LENGTH(text);,字符串函数,LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0. select LOCATE(bar, foobarbar); select LOCATE(xbar, foobar); INSTR(str,substr):返回子串substr在字符串str中的第一个出现的位置。 select INSTR(foobarbar, bar); select INSTR(xbar, foobar); LEFT(str,len):返回字符串str的最左面len个字符。 select LEFT(foobarbar, 5); RIGHT(str,len):返回字符串str的最右面len个字符。 select RIGHT(foobarbar, 4);,字符串函数,SUBSTRING(str,pos):从字符串str的起始位置pos返回一个子串。 select SUBSTRING(Quadratically,5); TRIM(str):返回字符串str，所有前缀或后缀被删除了。 select TRIM( bar ); LTRIM(str):返回删除了其前置空格字符的字符串str。 select LTRIM( barbar); RTRIM(str):返回删除了其拖后空格字符的字符串str。 select RTRIM(barbar ); REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替。 select REPLACE(, w, Ww); REPEAT(str,count):返回由重复countTimes次的字符串str组成的一个字符串。如果count = 0，返回一个空字符串。如果str或count是NULL，返回NULL。 select REPEAT(MySQL, 3);,字符串函数,REVERSE(str):返回颠倒字符顺序的字符串str。 select REVERSE(abc); INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。 select INSERT(whatareyou, 5, 3, is);,日期和时间函数,DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, 7=星期六)。 select DAYOFWEEK(1998-02-03); select DAYOFWEEK(now(); WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, 6= 星期天)。 select WEEKDAY(1997-11-05); DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。 select DAYOFMONTH(1998-02-03); DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。 select DAYOFYEAR(1998-02-03); MONTH(date):返回date的月份，范围1到12。 select MONTH(1998-02-03); DAYNAME(date):返回date的星期名字。 select DAYNAME(“1998-02-05“); MONTHNAME(date) :返回date的月份名字。 select MONTHNAME(“1998-02-05“); QUARTER(date):返回date一年中的季度，范围1到4。 select QUARTER(98-04-01);,日期和时间函数,WEEK(date,first):对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。 select WEEK(2009-02-20); select WEEK(2009-02-20,0); select WEEK(2009-02-20,1);,日期和时间函数,YEAR(date):返回date的年份，范围在1000到9999。 select YEAR(98-02-03); HOUR(time):返回time的小时，范围是0到23。 select HOUR(10:05:03); MINUTE(time):返回time的分钟，范围是0到59。 select MINUTE(98-02-03 10:05:03); SECOND(time):回来time的秒数，范围是0到59。 select SECOND(10:05:03);,日期和时间