MySQL数据库 3.3 常用函数查询教案

教案首页课程名称MySQL数据库开发本节课题任务3.3常用函数查询授课方式理实一体化教学参考及教具姜云桥主编.MySQL数据库开发.临科院：教务处,2023计算机MySQL数据库软件多媒体教学系统资料包教学目标及基本要求熟练使用算术运算符按照需求熟练使用各种函数掌握数学函数的使用掌握字符串函数的使用掌握日期和时间函数的使用培养自主学习能力教学重点教学难点算术运算符、数学函数、日期和时间函数、字符串函数使用算术运算符和函数进行查询、统计、格式设置教学小结作业及要求按照任务书做好预习任务使用运算符和函数进行查询、统计、格式设置教后反思注意：用微课视频每个典型操作做示范

教案用纸教学内容、方法和过程附记任务3.3常用函数查询【任务背景】在数据管理过程中，经常要使用运算符和函数进行数据处理。例如，进行简单的数学运算、比较运算，求总成绩、最高分、最低分、平均分，根据参加工作的时间计算工龄，查询年龄在30～40岁之间的老师，根据成绩判断是否及格，格式化时间、日期等，这就要使用到相关运算符和函数。【任务要求】本任务从从认识MySQL支持的算术运算符和函数着手，学习算术运算符和函数的实际应用。学习内容主要包括算术运算符、数学函数、日期和时间函数、字符串函数等。【任务必备知识】MySQL数据库中提供了很丰富的函数。这些内部函数可以帮助用户更加方便地处理表中的数据。MySQL函数包括数学函数、字符串函数、日期和时间函数、等。select语句及其条件表达式都可以使用这些函数。同时，INSERT、UPDATE和DELETE语句及其条件表达式也可以使用这些函数。运算符是用来连接表达式中各个操作数的符号，其作用是指明对操作数所进行的运算。MySQL数据库支持使用运算符。通过运算符，可以使数据库的功能更加强大。而且，可以更加灵活的使用表中的数据。MySQL运算符包括4类，分别是算术运算符、比较运算符、逻辑运算符和位运算符。3.3.1算术运算符算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括加、减、乘、除、求余。1．“+”运算符“+”运算符用于获得一个或多个值的和。 MySQL>SELECT3+2,1.5+3.8256;2．“–”运算符“–”运算符用于从一个值中减去另一个值，并可以更改参数符号。 MySQL>SELECT100-200,0.24-0.12,-2,-32.5;3．“*”运算符“*”运算符用来获得两个或多个值的乘积。 MySQL>SELECT3*8,-22.5*3.6,8*0;4．“/”运算符“/”运算符用来获得一个值除以另一个值得到的商。 MySQL>SELECT3/5,96/12,128/0.2,1/0;除以零的除法是不允许的，MySQL会返回NULL。5．“%”运算符“%”运算符用来获得一个或多个除法运算的余数。 Mysql>SELECT5%2,16%4,7%0;3.3.2比较运算符比较运算符(又称关系运算符)，用于比较两个表达式的值，其运算结果为逻辑值，可以为3种之一：1(真)、0(假)及NULL(不能确定)。1.“=”运算符“=”相等返回1，不等返回0，空值不能使用等号和不等号判断。 mysql>SELECT'A'='B',1+1=2,'X'='x';在默认情况下，MySQL不区分大小写,所以’X’=‘x’的结果为真，’A’=NULL不能判断。 mysql>SELECT'A'=NULL,NULL=NULL,NULL=0;2．“<=>”运算符“<=>”与“=”作用相等，唯一区别是它可以用来判断空值。 mysql>SELECT5<=>5,5<=>6,'a'<=>'a',NULL<=>NULL,NULL<=>0; NULL可以判断，NULL不等于0。3．“>”和“>=”、“<”和“<=”运算符“>”用来判断左边的操作数是否大于右边的操作数。如果大于，返回1；否则返回0。“>=”用来判断左边的操作数是否大于等于右边的操作数。如果大于等于，返回1.如果小于，返回0。“<”用来判断左边的操作数是否小于右边的操作数。如果小于，返回1；否则，返回0。“<=”用来判断左边的操作数是否小于等于右边的操作数。如果小于等于，返回1；否则，返回0。NULL与NULL进行比较仍然返回NULL。 mysql>SELECT3<5,8<3,'A'>='B',3.15>=3.150,NULL>NULL;4．“<>”、“!=”运算符用于进行数字字符串和表达式不相等的判断，当NULL与NULL用“<>”比较时仍然返回NULL。 mysql>SELECTNULL<>NULL,NULL<>0,0<>0;mysql>SELECT1<>0,'2’<>2,5!=5,3-2!=4-3,NULL<>NULL;查找D001系以外的学生。 mysql>SELECT*FROMstudentsWHEREd_no!='D001’;5．“ISNULL”、“ISNOTNULL”和“ISNULL”运算符用来判断操作数是否为空值。为空时返回1，不为空返回0。 MYSQL>SELECTNULLISNULL,ISNULL(18),ISNULL(NULL),30ISNOTNULL;查找地址为NULL为的学生。 MYSQL>SELECT*FROMSTUDENTSSWHEREphoneISNULL;6．“IN”运算符IN运算符可以判断操作数是否落在某个集合中。表达式“x1in(值1，值2，…,值n)”，如果x1等于其中任何一个值，返回1，不是则返回0。MySQL>SELECT5IN( 查找编号D001或D002系院的学生。 MySQL>SELECT*FROMstudentsWHERED_NOIN('D001','D002');可以添加NOT逻辑运算符对一个IN进行取反。 Mysql>SELECT5NOTIN(1,10) 查找编号D001或D002院系以外的其他的学生。mysql>SELECT*FROMstudentsWHEREprojectWHERED_NOIN(‘D001’,‘D002’);7．“BETWEENAND”运算符“BETWEENAND”运算符。可以判断操作数是否落在某个取值(包含取值)范围内。mysql>SELECT10BETWEEN0AND10,50BETWEEN0AND100;查找出生日期在1982年6月份出生的学生。 MYSQL>SELECT*FROMstudentsWHEREbirthdaybetween'1992-6-1'and'1992-6-30';查找成绩在80至90分的学生信息。 mysql>SELECT*FROMSCOREWHEREREPORTBETWEEN80AND90;可以添加NOT逻辑运算符对一个BETWEEN进行取反。 MYSQL>SELECT'B'NOTBETWEEN'A'AND'Z',99NOTBETWEEN1AND100;8．“LIKE”运算符“LIKE”用来匹配字符串。表达式“x1likes1”,如果x1与字符串s1匹配，结果返回1。 “%”匹配任意个字符，“_”匹配一个字符。SELECT'CHINA'LIKE'CHINA'，'MYSQL'LIKE'MY%'，'APPLE'LIKE'A_';查找在学院路住的学生。 mysql>SELECT*FROMSTUDENTSSWHEREaddresslike'%学院路%';查找姓张的学生。 mysql>SELEECT*FROMSTUDENTSSWHERES_NAMELIKE'张%’;查找单名单姓的学生。 mysql>SELECT*FROMSTUDENTSSWHEREs_namelike'__';9．“REGEXP”运算符“REGEXP”也用来匹配字符串，但其中使用正则表达式匹配。 查询姓名中最后一位字符是“明”的学生信息。mysql>SELECT*FROMSTUDENTSSWHERES_NAMEREGEXP'明$';说明 “$”表示匹配结尾部分。查找姓名中含有明字符的员工信息 mysql>SELECT*FROMSTUDENTSSWHERES_NAMEREGEXP'.明';3.3.3逻辑运算符逻辑运算符用来判断表达式的真假。逻辑运算符的返回结果只有TRUE(1)和FALSE(0)。如果表达式是真，结果返回1。如果表达式是假，结果返回0。逻辑运算符又称为布尔运算符。MySQL中支持4种逻辑运算符。这4种逻辑运算符分别是与、或、非和异或。1．NOT运算符逻辑非运算，它对跟在它后面的逻辑测试判断取反，把真变假，假变真。 mysql>SELECTNOT0,NOT(‘A’=‘B’),NOT(3.14=3.140);2．AND(&&)运算符逻辑与运算，测试逻辑条件(两个或以上)的值全部为真，而且不为NUUL值，则结果为真，否则为假。mysql>SELECT(3=1)AND(12>10),(‘c’=‘C’)AND(‘c’<‘d’)AND(1=1);&&也是逻辑与运算AND符，例如， mysql>SELECT(3=1)&&(12>10),(‘c’=‘C’)&&(‘c’<‘d’)&&(1=1);mysql>SELECT1&&1; mysql>SELECT1&&0; mysql>SELECT0&&NULL; mysql>SELECT1&&NULL;3.OR(||)运算符逻辑或运算，测试逻辑条件(两个或以上)的值有一个为真，而且不为NUUL值则结果为真，全为假则结果为假。mysql>SELECT(3=1)OR(12>10)OR('c'='C')OR(1=1);||也是逻辑或运算符，例如， mysql>SELECT1||1; mysql>SELECT1||0; mysql>SELECT0||NULL; mysql>SELECT1||NULL;4．XOR运算符逻辑异或运算。如果包含的值或表达式一个为真，而另一个为假并且不是NULL，那么它返回真值，否则返回假值。mysql>SELECT('A’='a’)XOR(1+1=3),(3+2=5)XOR(‘A’>‘B’);3.3.4位运算符位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，然后进行位运算。再将计算结果从二进制数变回十进制数。MySQL中支持6种位运算符。这6种位运算符分别是按位与、按位或、按位异或、按位左移、按位右移和按位取反。1．位与、位或、位异或mysql>SELECT3&2,2|3;从students表中筛选出学号为奇数的学生信息。 mysql>SELECT

*

FROM

studentsWHERE

(s_no

&

0x01);

这样就把s_no为奇数的记录选出来了。2．位右移、位左移(1)位右移。 mysql>select100>>5;(2)位左移。 mysql>select100<<5;3．位取反mysql>select~1,~18446744073709551614;3.2.1.5运算符的优先级当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。在一个表达式中按先高(优先级数字小)后低(优先级数字大)的顺序进行运算。3.2.2认识和使用函数MySQL数据库中提供了很丰富的函数。这些内部函数可以帮助用户更加方便地处理表中的数据。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数和格式化函数等。SELECT语句及其条件表达式都可以使用这些函数。同时，INSERT、UPDATE和DELETE语句及其条件表达式也可以使用这些函数。3.2.5数学函数数学函数是MySQL中常用的一类函数。主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等，1．求绝对值、圆周率mysql>SELECTABS(8),ABS(-10.2),PI();2．求平方根、求余数mysql>SELECTSQRT(32),SQRT(3),MOD(8,3);3．获得一组数中的最大值和最小值mysql>SELECTGREATEST(100,12,66,0),LEAST(4,5,6),LEAST(5.34,null,9);当某个数为NULL时，则返回NULL值。4．FLOOR()和CEILING()函数FLOOR()用于获得小于一个数的最大整数值，CEILING()函数用于获得大于一个数的最小整数值，例如：mysql>SELECTFLOOR(-3.62),CEILING(-3.62),FLOOR(18.8),CEILING(18.8);5．获取随机数mysql>SELECTRAND(),RAND(),RAND(3),RAND(3);说明 RAND()返回的数是完全随机的，而RAND(x)函数的x相同时，它被用作种子值，返回的值是相同的。随机顺序查找students的学生信息。 mysql>SELECT*FROMteachersORDERBYRAND();可以看到，找到的信息并不是按学号大小顺序排列，而是按随机顺序。6．四舍五入mysql>SELECTROUND(3.8),ROUND(2.5),ROUND(-2.14,1),ROUND(3.66,0);ROUND(x)返回离x最近的整数，也就是对x进行四舍五入处理。ROUND(x,y)返回x保留到小数点后y位的值，在截取时进行四舍五入处理。求学生的成绩的平均值，并四舍五入取小数点后一位。 mysql>SELECTs_no,ROUND(AVG(score),1)FROMscoreGROUPBYs_no;7．符号函数mysql>SELECTSIGN(8),SIGN(-6/3),SIGN(3-3);8．角度与弧度相互转换的函数RADIANS(x)函数的作用是将角度转换为弧度；DEGREES(x)函数的作用是将弧度转换为角度。这两个函数互为反函数。mysql>SELECTDEGREES(1),RADIANS(180);9．正弦、余弦、正切函数mysql>SELECTSIN(2),COS(2),TAN(RADIANS(60));10．反正弦、反余弦、反正切函数mysql>SELECTASIN(1),ACOS(-1),ATAN(DEGREES(45)); 如果使用的是角度而不是弧度，可以使用DEGREES()和RADIANS()函数进行转换。11．对数运算函数mysql>SELECT

LOG(2),LOG(-2),LOG10(2),LOG10(-2);

12．幂运算函数Mysql>SELECT

POW(3,2),POW(3,-2),EXP(2);

3.2.2.2聚合函数聚合函数也叫作分组计算函数。这些函数，常常是与GROUPBY子句一起使用的函数，作用是对聚合在组内的行进行计算。如果在不包含GROUPBY子句的语句中使用聚合函数，它等价于聚合所有行。1．COUNT(expr)返回由SELECT语句检索出来的行的非NULL值的数目。COUNT(*)在它返回的检索出来的行数目上有些不同，它不管内容中是否包含NULL值，均进行统计。如果SELECT从一个表检索，或没有检索出其他列并且没有WHERE子句，COUNT(*)被优化以便快速地返回。例如，统计学生人数。mysql>selectCOUNT(*)AS'学生人数'fromstudents; 统计选修了MySQL课程的学生人数。mysql>selectCOUNT(*)AS‘选修MYSQL人数’fromscore wherec_no=(selectc_nofromcourse wherec_name='MYSQL'); COUNT(DISTINCTexpr,[expr...])返回一个无重复值的数目。 查找有几位学生选修了课程。 Mysql>selectCOUNT(DISTINCTs_no)fromscore;如果忽略DISTINCT如下。 mysql>selectCOUNT(s_no)fromscore;可以看到有42条记录数，但存在一个学生选修了多门课的情况，即存在重复的学号记录。2．AVG(expr)返回expr的平均值。 统计各位学生的平均分。 mysql>selects_no,AVG(report)fromscoreGROUPBYs_no;查询学生科目成绩与学科平均成绩的差距。 mysql>selects_no,c_no,report,AVG(report),report-AVG(report)fromscoreGROUPBYc_no;3．MIN(expr)、MAX(expr)返回expr的最小或最大值。MIN()和MAX()可以有一个字符串参数，在这种的情况下，他们返回最小或最大的字符串值。统计各门课程的最高分和最低分 mysql>selectc_no,MIN(report),MAX(report)

fromscore

GROUPBYc_no;4．SUM(expr)返回expr的总和。注意，如果返回的集合没有行，它返回NULL。 统计各位学生的总成绩。 mysql>SELECTs_no,SUM(report)FROMscoreGROUPBYs_no;3.2.6字符串函数1．CONCAT(str1,str2,...) 返回来自于参数连接的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。数字参数会被变换为等价的字符串形式。Mysql>selectCONCAT('I','LOVE','MyQL');mysql>selectCONCAT('My',NULL,'QL');2．LEFT(str,len)返回字符串str的最左面len个字符。 mysql>selectLEFT('MySQLChina',5);返回students表中各位学生的地址栏中前3位地址： SELECTs_no,LEFT(ADDRESS,3)FROMSTUDENTS;3．RIGHT(str,len)返回字符串str的最右面len个字符。mysql>selectRIGHT(‘MySQLChina’,5);4．SUBSTRING(str,pos,len)和MID(str,pos,len)从字符串str返回长度为len个字符的子串，从位置pos开始。 mysql>selectSUBSTRING(‘ABCDEFGHIJK’,2,6);分别返回students表中所有学生的姓氏和名字。Mysql>SELECTMID(s_name,1,1)AS姓,SUBSTRING(s_name,2,LENGTH(s_name)-1)AS名FROMSTUDENTS;5．REPEAT(str,count)返回由字符串str重复count次组成的一个字符串。如果count<=0，返回一个空字符串。如果str或count是NULL，返回NULL。mysql>selectREPEAT('MySQL',3);3.2.7日期和时间函数日期和时间函数主要用于处理表中的日期和时间数据。日期和时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数和计算时间的函数等。1．获取当前日期使用CURDATE()和CURRENT_DATE()函数获取当前日期。mysql>SELECTCURDATE(),CURRENT_DATE();2．获取当前时间使用CURTIME()和CURRENT_TIME()函数获取当前时间。 mysql>SELECTCURTIME(),CURRENT_TIME();3．返回当前日期和时间的函数NOW()、CURRENT_TIMESTAMP()、LOCALTIME()和SYSDATE()函数都用来获取当前的日期和时间。mysql>SELECTNOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE();

4．返回年份、季度、月份和日期函数YEAR()函数分析一个日期值并返回其中年的部分；QUARTER(d)函数返回日期d是本年第几季度，值的范围是1～4。MONTH()函数分析一个日期值并返回其中关于月的部分，值的范围是1～12；DAY()函数分析一个日期值并返回其中关于日期的部分，值的范围是1～31。mysql>SELECTNOW(),YEAR(NOW()),QUARTER(NOW()),MONTH(NOW()),DAY(NOW());在STUDENTS表求出D001学院的学生年龄。mysql>SELECTs_no,YEAR(NOW())-YEAR(BIRTHDAY)AS年龄FROMSTUDENTSWHERED_NO=‘D001’;5．返回指定日期在一年、一星期及一个月中的序数DAYOFYEAR()、DAYOFWEEK()和DAYOFMONTH()函数分别返回指定日期在一年、一星期及一个月中的序数。mysql>SELECTDAYOFYEAR(20150512),DAYOFMONTH(‘2015-05-12’),DAYOFWEEK(NOW());6．获取星期的函数(1)DAYNAME(d)函数返回日期d是星期几，显示其英文名，如Monday、Tuesday等。(2)DAYOFWEEK(d)函数也返回日期d是星期几，1表示星期日，2表示星期一，依次类推。(3)WEEKDAY(d)函数也返回日期d是星期几，0表示星期一，1表示星期二，依次类推。其中，参数d可以是日期和时间，也可以是日期。 mysql>SELECT

DAYNAME(NOW()),DAYOFWEEK(‘2015-12-30’),WEEKDAY(‘2014-10-1’);7．获取星期数的函数WEEK(d)函数和WEEKOFYEAR(d)函数都是计算日期d是本年的第几个星期。返回值的范围是1～53。mysql>SELECTWEEK(NOW()),WEEKOFYEAR(‘2015-1-1’);8．获取天数的函数DAYOFYEAR(d)函数日期d是本年的第几天；DAYOFMONTH(d)函数返回日期d是本月的第几天。mysql>SELECTNOW(),DAYOFYEAR(NOW()),DAYOFMONTH(20150101);距离2014年高考还有多少天。 mysql>SELECTDAYOFYEAR(20140607)-DAYOFYEAR(NOW())AS'高考倒计时(天)';9．返回指定时间的小时、分钟、秒钟mysql>SELECTCURTIME(),HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());10．对日期和时间进行算术操作DATE_ADD()和DATE_SUB()函数可以对日期和时间进行算术操作，它们分别用来增加和减少日期值，使用的关键字如表3.2.6所示。表3.2.8DATE_ADD()函数和DATE_SUB()函数使用的关键字DATE_ADD()和DATE_SUB()函数的语法格式为： DATE_ADD|DATE_SUB(date,INTERVALintkeyword) Date表示日期和时间，INTERVAL关键字表示一个时间间隔。再过20天是什么日期。 mysql>SELECTDATE_ADD(NOW(),INTERVAL20DAY);30分钟前是什么时间。 mysql>SELECTDATE_SUB('2014-10-110:25:35',INTERVAL30MINUTE);11．DATEDIFF函数。DATEDIFF(d1,d2)函数计算两个日期相隔的天数。 7月1日放假，离放假还有多少天。 mysql>SELECTDATEDIFF('2014-7-1',NOW());12．日期和时间格式化的函数DATE_FORMAT()和TIME_FORMAT()函数可以用来格式化日期和时间值。 语法格式如下。DATE_FORMAT/TIME_FORMAT(date|time,fmt) 其中，date和time是需要格式化的日期和时间值，fmt是日期和时间值格式化的形式，表3.2.7列出了MySQL中的日期/时间格式化代码。关键字间隔值的格式关键字间隔值的格式%a缩写的星期名(Sun，Mon…)%pAM或PM%b缩写的月份名(Jan，Feb…)%r时间，12小时的格式%d月份中的天数%S秒(00，01)%H小时(01，02…)%T时间，24小时的格式%I分钟(00，01…)%w一周中的天数(0，1)%j一年中的天数