MySQL教程（新体系-综合应用实例视频）（第4版） 课件 第7-9章 运算符、表达式和系统函数;查询、视图和索引;过程式对象程序设计

第7章

运算符、表达式和系统函数——常量和变量MySQL+实用教程（第4版）01常

量1．数值常量2．字符串常量3．日期时间常量4．布尔值5．NULL值常

量1．数值常量（1）十进制数常量十进制数常量分为整数常量和浮点数常量，整数常量即不带小数点的十进制数，浮点数常量是使用小数点的数值常量。（2）非十进制数常量非十进制数常量包括b（B）二进制表示和x（X）十六进制表示，它们只能表示整数。有两种表示方式，一种是以0打头，在进制表示符后跟进制数；另一种以进制表示符打头后跟进制数字符串，但十六进制数字需要双数。常

量【例7.1】十进制数常量表示和显示。SELECT1894,-2,+145345234,-2147483648; #（a）整数常量SELECT5.26,1.39,101.5E5,0.5E-2; #（b）小数常量、浮点数常量SELECT152+5.26,78+0.5E-2; #（c）浮点数常量运算SELECT0b1000001,b'1000011',0x4142,x'4344’; #（d）二进制十六进制常量运行结果如图7.1所示。

常

量2．字符串常量字符串常量可以用引号括起来，也可以通过十六进制表达。（1）一般字符串常量一般字符串常量用单引号括起来，例如：'你好，Howareyou!'。其中，ASCII字符用一个字节存储，中文用2个字节存储。（2）Unicode字符串常量Unicode字符串常量前面有一个N标志符，代表SQL-92标准的国际语言（NationalLanguage），用单引号括起字符串。例如：N'你好，Howareyou!'。其中，每个字符（中文或者英文）用两个字节存储。常

量（3）字符串中特殊的字符表示在字符串中不仅可以使用普通的字符，也可使用几个转义序列来表示特殊的字符，见表7.1。每个转义序列以一个反斜杠（“\”）开始。序

列含

义\0一个ASCII零值字节（NUL）字符\n一个回车符\r一个换行符（Windows中使用\r\n作为新行标志）\t一个定位符\b一个退格符\Z一个ASCII为26字符（Ctrl+Z）\'一个单引号（“'”）\"一个双引号(“"”)\\一个反斜线（“\”）\%一个“%”符，用于在正文中搜索包含“%”字符，否则“%”将解释为一个通配符。\_一个“_”符，用于在正文中搜索包含“_”字符，否则“_”将解释为一个通配符。常

量（4）字符串中包含引号字符字符串内包含单引号（'），字符串需要用双引号（"），否则需要用转义字符（\'）表示单引号；字符串内包含双引号（"），字符串需要用单引号（'），否则需要用转义字符（\"）表示双引号。（5）十六进制、二进制表示字符串每对（即2个）十六进制数字被转换为一个字符，不区分大小写。例如：0x4142和x'4142'表示字符串“AB”，x'4D7953514C'表示字符串“MySQL”。每8位（1个字节）二进制数字被转换为一个字符。例如：0b01000001表示字符“A”，b'0100000101000010'表示字符串“AB”。在输出显示时，十六进制和二进制表示被默认为表达字符，而在参与算术运算时则被认为是表达数值。常

量【例7.2】字符串常量表示和显示。SELECT'hello','"hello','hel""lo','hel''lo','\'hello'; #（a）SELECT"'hello","'hel''lo","\"hello\n""hello"; #（b）SELECT-0b1000011,b'1000011'+0,0x1F0+1,-x'01F0'; #（c）SELECT0b01000001,x'4D7953514C'; #（d）运行结果如图7.2所示。

常

量3．日期时间常量满足日期时间要求的字符串它当然本身就是字符串，但当用于日期时间位置、符合日期时间格式要求并且有效时，就会被看成是日期时间常量。日期型常量包括年、月、日，如“2021-06-17”格式。时间型常量包括小时数、分钟数、秒数及微秒数，如“12:30:43.00013”格式。日期时间型常量是日期时间组合，如“2021-06-1712:30:43”。注意，MySQL是按“年-月-日”的顺序表示日期的，中间的间隔符“-”也可以使用如“\”、“@”或“%”等特殊符号。此外，日期时间常量必须有效，如'2021-02-31'的字符串就是错误的日期时间常量。【例7.3】日期、时间常量的表达显示与当前日期、时间获得和显示。SELECT'2021-06-17',CURDATE(); #（a）日期和当前日期SELECT'12:30:43.00013',CURTIME(); #（b）时间和当前时间SELECT'2021-06-1712:30:43',NOW(); #（c）日期时间和当前日期时间运行结果如图7.3所示。

常

量4．布尔值布尔值只包含两个可能的值：TRUE和FALSE。TRUE的数字值为1，FALSE的数字值为0。5．NULL值NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等含义，并且不同于数字类型（0），或字符串类型的空字符串（''）。02变

量1．系统变量2.用户变量变

量1．系统变量MySQL有一些特定的设置，例如，有些定义数据如何被存储，有些影响到处理速度，还有些与日期有关，这些设置就是系统变量。1）系统变量获取和设置系统变量在MySQL服务器启动时就被引入并初始化为默认值。例如：@@VERSION可获得当前使用的MySQL版本，像这样的系统变量的值是不可以改变的。大多数的系统变量应用于SQL语句中必须在名称前加两个@符号，而为了与其他SQL产品保持一致，某些特定的系统变量是要省略这@@符号的。如CURRENT_DATE（系统日期）、CURRENT_TIME（系统时间）、CURRENT_TIMESTAMP（系统时间戳）和CURRENT_USER（SQL用户名）。例如：SELECT@@VERSION,CURRENT_TIME;显示结果如图7.4所示。变

量有些系统变量是可以通过SET语句来修改的：SET[GLOBAL|SESSION]系统变量名=表达式或者SET @@GLOBAL.|@@SESSION.系统变量名=表达式说明：（1）系统变量名称前加GLOBAL关键字或“@@global.”前缀是全局系统变量；而指定了SESSION关键字或“@@session.”前缀的则为会话系统变量。SESSION还有一个同义词LOCAL。如果在使用系统变量时不指定关键字，则默认为会话系统变量。（2）表达式：要给变量赋的值，可以是常量、变量或它们通过运算符组成的式子。（3）如果指定表达式为“DEFAULT”，则恢复为默认值。（4）如果只能使用会话系统变量时设置为全局系统变量，或者只能使用全局系统变量时设置为会话系统变量，均会显示出错信息。MySQL8新增了SETPERSIST命令：SET

PERSIST系统变量名=值;MySQL会将该命令的配置保存到数据目录下的配置文件（f）中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。变

量2）全局系统变量和会话系统变量（1）全局系统变量（指定GLOBAL）：当MySQL启动的时候，全局系统变量就被初始化了，并且应用于此后每一个启动的会话。如果设置GLOBAL来系统变量（需要超级用户权限），则该值被记住，并被用于新的连接，直到服务器重新启动为止。（2）会话系统变量只适用于当前的会话。大多数会话系统变量的名字和全局系统变量的名字相同。当启动会话的时候，默认情况下每个会话系统变量都和同名的全局系统变量的值相同。一个会话系统变量的值是可以改变的，但是这个新的值仅适用于当前正在运行的会话。（3）若改变了全局系统变量的值，同名的会话系统变量的值也保持不变。变

量3）系统变量清单SHOWVARIABLES[LIKE条件] #显示系统变量清单。SHOWGLOBALVARIABLES[LIKE条件] #显示所有全局系统变量SHOWSESSIONVARIABLES[LIKE条件] #显示所有会话系统变量例如：SHOWVARIABLES; #（a）SHOWGLOBALVARIABLESLIKE'table_%'; #（b）SHOWSESSIONVARIABLESLIKE'character_%’; #（c）说明：（a）获得所有系统变量清单，共601个。（b）获得与特定模式匹配的系统变量清单，需使用LIKE子句，通配符为“%”。（c）获得字符集会话系统变量清单。变

量2.用户变量用户自己定义的变量叫用户变量，可在其中保存值，以后再引用它，这样在一个会话中可以将值从一个语句传递到另一个语句。用户变量可用下列语句定义和赋值：SET @变量名=表达式,...说明：（1）变量名由当前字符集的文字和数字字符、“.”、“_”和“$”组成。当变量名中需要包含一些特殊符号（如空格、#等）时，可以使用双引号或单引号将整个变量括起来。@必须放在一个用户变量的前面，以便将它和列名区分开。（2）用户变量的数据类型取决于赋予它的表达式值。表达式可以是常量、已经赋值的用户变量或它们通过运算符组成的式子，也可以是NULL值。（3）没有初始化的用户变量的值为NULL。变

量【例7.4】创建用户变量及其表达式。SET@user1=1,@user2=@user1+1;SELECT@user1,@user2,@user3; #（a）SET@user2=@user1+1;SELECT@user1,@user2; #（b）运行结果如图7.5所示。

变

量（4）只有当一个用户变量已经被创建并初始化后，它才可以用于其他SQL语句中，变量名前必须加上@符号。【例7.5】以“商品名称”作为中间变量查询指定编号的商品信息。USEemarket;SET@cid='1A0101';SET@cname=(SELECT商品名称FROMcommodityWHERE商品编号=@cid);SELECT@cid,@cname; #（a）SELECT商品名称,价格FROMcommodityWHERE商品编号=@cidINTO@cname,@cprice;SELECT@cid,@cname,@cprice; #（b）运行结果如图7.6所示。

说明：（a）先查询“商品编号等于用户变量@cid（'1A0101'）值的“商品名称”存于用户变量@cname中并显示。这种方式SELECT的输出项只能一个，并且，查询出的记录最多只能一个，因为它只能存放一个值。注意，SELECT语句需要括起来。（b）这种方式查询的输出项可以有多个，但INTO后面的项需要一一对应。同时，查询出的记录最多只能一个，因为每一个用户变量只能存放一个值。变

量（5）用其他语句代替SET语句来为用户变量分配一个值，分配符必须为“:=”，而不能用“=”，因为在非SET语句中“=”被视为比较操作符。例如：SELECT@t2:=(@t2:=2)+5ASt2;（6）用户变量可以用于存放数据库的查询结果。第7章

运算符、表达式和系统函数——运算符与表达式01赋值运算符赋值运算符因“=”只有在SET语句中才被作为赋值运算符使用，其他上下文情形下都被视作等于比较运算符，而“:=”则在任何合法的SQL语句中都是赋值运算符，故实际应用中要进行赋值运算，建议优先采用“:=”形式。【例7.6】赋值运算符修改数据库表列值。USEmydb;CREATETABLEtest(t1int);INSERTINTOtestVALUES(3),(5),(7);SELECT*FROMtest;SET@var1:=5;UPDATEtestSETt1=2WHEREt1=@var1;SELECT*FROMtest;02算术运算符算术运算符算术运算符是用于数值型数据运算的，常用的算术运算符见表7.2。运算符作用实例运算符作用实例DIV整数除法7DIV2=3*乘法7*2=14/除法7/2=3.5-减法或负号-7-2=-9%,MOD取模运算7%2=1+加法-7+2=-5（1）算术运算符优先级：先算括号里面的；先负号（-），再*、/、DIV，然后%，最后+和-；同级从左到右。（2）DIV与/的区别是它会丢弃运算结果的小数部分。（3）自动将除法运算结果规格化为固定位数的小数，除数为零时会产生NULL结果。（4）不同进制进行算术运算均转换为十进制。（5）数值字符串被当成数值进行运算，字符串中含非数字字符，其前面部分数字字符当成数值。首字符为非数字，则作为0。例如：SELECT-7/2+1,7DIV2,7MOD2,(7%-2)+0.5*(-0.1),1/(7MOD2-1);#（a）SELECT0b01101+b'11',0x1E+'11',-b'01101'+21+x'1E',21-'3A'-'C3';#（b）算术运算符运行结果如图7.7所示。说明：（a）-7/2+1=-2.5：显示-2.5000。如果显示小数点后面2位数（-2.50），可以进行格式控制。SELECTFORMAT(-7/2+1,2);(7%-2)+0.5*(-0.1)：-2→(7%-2)=1，-0.1→0.5*(-0.1)=-0.05，1-0.05=0.95。1/(7MOD2-1)：7MOD2=1，1-1=0，1/0=NULL。（b）0b01101+b'11'：二进制数值+二进制串，作为二进制数值计算。0x1E+'11'：十六进制数值+十进制串，作为十进制数值计算。-b'01101'+21+x'1E'：二进制串+十进制数值+十六进制串，作为十进制数值计算。21-'3A'-'C3'：十进制数值-打头含数字字符串-打头不含数字字符串，按21-3-0=18。03比较运算符1.等于（=）2.安全等于（<=>）3.不等于（<>或!=）4.小于等于（<=）5.小于（<）6.大于等于（>=）7.大于（>）比较运算符比较运算符用于操作数间的比较运算，运算的结果总是1、0或NULL。它经常用在SELECT语句的WHERE子句中表达査询条件。MySQL中的比较运算符如表7.3所示。运

算

符作

用运

算

符作

用=等于<

小于<=>安全等于>=大于等于<>,!=不等于>

大于<=小于等于

1.等于（=）等于运算符（=）用来判断数字、字符串和表达式是否相等，如果相等返回值为1；否则返回值为0。等于运算符比较时遵循如下规则：1）若两个参数均为数值或者数值表达式，则按照数值进行比较。例如：SET@t=5.001;SELECT1.0=1,@t=5+1e-3,0=1e-90;运行结果如图7.8所示。比较运算符2）若用数值字符串和数字进行比较，则自动将字符串转换为数字后用数值进行比较。例如：SELECT'3'=3,'0.0'=0,'0.05'=5e-2,0.05='5e-2','0.05'='5e-2';运行结果如图7.9所示。其中，因为两边都是字符串，科学计数法表示的数值5e-2就不会转换为数值，而是直接进行字符串比较。用全角数值字符串和数字进行比较，也会先转换成对应数值然后比较。例如：SET@４=3.14159;SELECT3.1416<'4',3.1416<@４;运行结果如图7.10所示。比较运算符3）用非数值字符串和数字进行比较，字符串转换为0，然后进行比较。例如：SET@π=3.14159;SELECT3.14<'π',3.14<@π,'A'=0;运行结果如图7.11所示。4）若两个都是（单引号或双引号括起）字符串，则按照字符串进行比较。只有字符串顺序和个数完全相同，字符串才相等。注意，空格也是字符。例如：SELECT"he"='he','he'=LEFT('he',1)+'e','H'='h','he'='h','h'='h';运行结果如图7.12所示。比较运算符5）ASCII码字符在不同的字符集中存储的代码是相同的，中文在不同的中文字符集中存储的代码是相同的，但在中文字符集和其他非中文字符集代码是不同的。例如：SELECT_utf8'he'=_gbk'he',_utf8'汉'=_gbk'汉';SELECT'he'=N'he',_gbk'汉'=N'汉';运行结果如图7.13所示。

比较运算符6）大小写字符是否相等取决于当前字符排序规则。【例7.7】大小写字符串比较和字符排序规则。（1）显示当前字符排序规则SHOWVARIABLESLIKE'COLLATION_%';运行结果如图7.14所示。其中，collation打头描述各种排序规则，Value中的“ci”表示不区分大小写。（2）字符比较SELECT'ABC'='abc','ABC'=_utf8mb4'abc'COLLATEutf8mb4_0900_as_cs;运行结果如图7.15所示。比较运算符7）元组比较两项对应列的数据项是否相等。【例7.8】元组相等比较。SELECT('1A','苹果')=('1B','梨'), ('1A','苹果')=('1A','梨'),('1A','苹果')=('1A','苹果');运行结果如图7.16所示。说明：第1项：第1列'1A'与'1B'不等，结果为假。第2项：因为第1列相等，单第2列'苹果'和'梨'不等，结果为假。第3项：因为第1列和第2列均相等，结果为真。比较运算符8）若有一个或两个参数为NULL，则比较运算的结果为NULL。例如：SELECT''=NULL,NULL=NULL;运行结果如图7.17所示。9）半角字符和全角字符是不相等的。在GB2312-80编码中键盘上出现的可以打印的字符均有编码，这些字符就跟汉字一样。GB2312内码兼容ASCII码，因为ASCII每一个字节的最高位为0，称为半角字符；汉字每一个字节的最高位为1，不是真正的汉字的符号称为全角字符。所以半角字符和全角字符是不相等的，而且半角字符<全角字符。GBK编码是GB2312-80的扩展。比较运算符【例7.9】半角字符和全角字符比较。SETcharacter_set_client=utf8;SELECT'a'='Ａ','a'<_gbk'Ａ'; #（a）SETcharacter_set_client=gbk;SELECT@@character_set_system; #（b）SELECT'a'='Ａ','a'<'Ａ'; #（c）运行结果如图7.18所示。

说明：（a）客户端字符集character_set_client为utf8，对应默认排序规则不区分大小写。全角字符'Ａ'转换为半角字符'A'后与半角字符'a'相等。而gbk'Ａ'就是全角字符'Ａ'，与半角字符'a'当然不等，而且全角字符'Ａ'编码大于半角字符'a'。（b）设置客户端字符集character_set_client为gbk，对应默认排序规则区分大小写，当前默认的character_set_system字符集为utf8。（c）字符集gbk区分大小写，'Ａ'是全角字符与半角字符'a'当然不等，而且全角字符'Ａ'编码大于半角字符'a'。比较运算符2.安全等于（<=>）在两个操作数均为NULL时，其返回值为1，否则返回值为0。除此之外，它与普通等于运算符（=）作用完全相同。例如：SELECT3<=>ABS(-3),''<=>NULL,NULL<=>NULL,0<=>NULL;运行结果如图7.19所示。3.不等于（<>或!=）不等于运算符（<>或!=）用于数字、字符串、表达式不相等的判断，如果不相等就返回值1，否则返回0。对于元组(a,b)<=(x,y)比较，只要有一个数据项不同，结果就为1。比较运算符4.小于等于（<=）如果左边的操作数小于等于右边的操作数，返回值为1；否则返回值为0。对于元组(a,b)<=(x,y)比较，等效于：(a<x)OR((a=x)AND(b<=y))例如：SELECT('1A','orange')<=('1C','apple'),('1C','orange')<=('1C','apple');运行结果如图7.20所示。说明：第一项：因为第1列'1A'<'1C'，故无须看第2列，直接返回1。第二项：第1列相等（都是“'1C'”），因'orange'>'apple'（字符o大于a），故返回0。比较运算符5.小于（<）小于运算符（<）用来判断左边的操作数是否小于右边的操作数：如果小于，返回值为1；否则返回值为0。对于元组(a,b)<(x,y)等效于：(a<x)OR((a=x)AND(b<y))6.大于等于（>=）大于等于运算符（>=）用来判断左边的操作数是否大于等于右边的操作数：如果大于等于，返回值为1；否则返回值为0。该运算符不能用于判断空值。对于元组(a,b)>=(x,y)等效于：(a>x)OR((a=x)AND(b>=y))7.大于（>）大于运算符（>）用来判断左边的操作数是否大于右边的操作数：如果大于，返回值为1；否则返回值为0。该运算符不能用于判断空值。对于元组(a,b)>(x,y)等效于：(a>x)OR((a=x)AND(b>y))04判断运算符1.ISNULL（ISNULL）和ISNOTNULL2.值是否在范围内：BETWEEN...AND...3.值是否在其中：IN、NOTIN判断运算符1.ISNULL（ISNULL）和ISNOTNULL表达式ISNULL表达式ISNULL表达式ISNOTNULL“表达式ISNULL”或者“表达式ISNULL”中表达式的值为NULL，返回值为1，否则返回值为0。“表达式ISNOTNULL”中表达式的值非空，返回值为1，否则返回值为0。ISNULL(表达式)：系统函数ISNULL(表达式)与“表达式ISNULL”功能相同。例如：SELECTNULLISNULL,ISNULL(100/(2-2)), (''=NULL)ISNOTNULL,(NULL=NULL)ISNOTNULL;运行结果如图7.21所示。说明：（1）表达式“100/(2-2)”除数为0，结果为NULL。（2）“(''=NULL)”和“(NULL=NULL)”皆为空值。（3）ISNULL和ISNULL的作用相同，而ISNULL和ISNOTNULL的返回值正好相反。判断运算符2.值是否在范围内：BETWEEN...AND...表达式BETWEEN最小值AND最大值作用等同于：最小值<=

表达式AND

表达式<=

最大值即，如果表达式值大于或等于最小值且小于或等于最大值，则返回1；否则返回0。【例7.10】表达式值范围判断。（1）数值表达式范围判断SET@x=-1;SELECT@xBETWEEN-2AND0,@x+1BETWEEN0AND2,ABS(@x)BETWEEN-2AND0;运行结果如图7.22所示。判断运算符（2）字符串范围判断SET@x='hello';SELECT @xBETWEEN'A'AND'Z', #（a） @xBETWEEN'hel'AND'help', #（b） @xBETWEEN'good'AND'你好', #（c） @xBETWEEN'HELLO'AND'hello', #（d） @xBETWEEN'z'AND'a'; #（e）运行结果如图7.23所示。说明：（a）不区分大小写。（b）''<'lo'，'l'<'p'。（c）'g'<'h'，'h'<'你'。（d）'H'<='h'，'h'>''。（e）'z'>'h'，'h'>'a'。判断运算符3.值是否在其中：IN、NOTIN值IN(值1,值2,...,值n)值NOTIN(值1,值2,...,值n)IN运算符用来判断操作数是否为IN列表中的其中一个值：如果是，返回值为1；否则返回值为0。NOTIN运算符则正好相反。（1）数值和字符串符合等于比较（=）条件，IN条件才为真。例如：SET@x=-3;SELECT3.14IN(ABS(@x)+0.14),3.14IN(3,3.14159,'π'),'fruit'NOTIN('fish','fruit','apple','orange');运行结果如图7.24所示。判断运算符（2）在“值IN(…)”值为NULL的情况下，即使（…）中存在NULL值，IN的返回值均为NULL。例如：SELECTNULLIN(2,3,5,NULL);运行结果如图7.25所示。值非NULL，但（…）中找不到匹配项并且包含NULL，IN的返回值均为NULL。但是，在已经找到匹配项的情况下，即使（…）中存在NULL值，也能正常返回1。SELECT'3.14'IN(0,3.14159,NULL),3.14IN(3,3.14,NULL);运行结果如图7.26所示。判断运算符（3）“值IN(…)”值尽量避免依赖IN列表中值的隐式类型转换，因为可能会产生非直观的结果而给实际使用带来麻烦。SELECT'a'IN(0),0IN('b');运行结果如图7.27所示。（4）IN、NOTIN还可以用于元组比较。例如：SELECT(7,5)IN((2,3),(5,7)),('1A','苹果','洛川')NOTIN(('1B','梨','砀山'),('1A','苹果','烟台'));运行结果如图7.28所示。05字符串匹配1.字符串通配符匹配：LIKE2字符串是否匹配正则表达式规范：REGEXP字符串匹配1.字符串通配符匹配：LIKE表达式LIKE匹配条件如果表达式满足匹配条件，返回值为1；如果不匹配，则返回0。表达式或匹配条件中任何一个为空，则结果为NULL。可以使用下面两种通配符。'%'：匹配任何数目的字符，甚至包括''字符。'_'：只能匹配一个字符。例如：SELECT'Tyson/泰森鸡胸肉'LIKE'Tyson______', #（a）结果：1'Tyson/泰森鸡胸肉'LIKE'Tyson%'; #（b）结果：1说明：（a）'Tyson______'（6个'_'连）表示匹配以'Tyson'开头后跟6个字符长度的字符串，而“/泰森鸡胸肉”正好是6个字符，满足匹配条件，匹配成功返回1。（b）'Tyson%'表示匹配以字符串'Tyson'开头的字符串，“Tyson/泰森鸡胸肉”满足匹配条件，也返回1。只要以“Tyson”打头后面有没有字符有多少字符均满足条件。字符串匹配【例7.11】字符串通配符匹配测试。SELECT'character_set_client'LIKE'%set_client’; #（a）结果：1SELECT'character_set_client'LIKE'%set\_client’; #（b）结果：1SELECT'character_set\_client'LIKE'%set\_client’; #（c）结果：0SELECT'Tyson/泰森鸡胸肉'LIKE'%__鸡胸肉'; #（d）结果：1说明：（a）'character_set_client'中“_”均为字符串内容，'%set_client'中的“%”和“_”均为匹配描述字符。（b）'%set\_client'中的“\_”为1个内容描述字符“_”转义字符。（c）'character_set\_client'中的“\_”为2个内容描述字符，'%set\_client'的“\_”中的“\”是内容描述字符，而“_”是匹配描述字符。（d）'%__鸡胸肉'中是1个“%”匹配描述字符和2个“_”匹配描述字符。字符串匹配2.字符串是否匹配正则表达式规范：REGEXP通过正则表达式可实现更复杂的字符串匹配。表达式REGEXP匹配条件如果表达式满足匹配条件，返回1；如果不满足，则返回0。若表达式或匹配条件任意一个为空，则结果为NULL。REGEXP运算符在进行匹配时，常用的有下面几种通配符：（1）'^'：匹配以该字符后面的字符开头的字符串。（2）'$'：匹配以该字符后面的字符结尾的字符串。（3）'.'：匹配任何一个单字符。（4）'[...]'：匹配在方括号内的任何字符，例如，[abc]匹配'a'、'b'或'c'。为了命名字符的范围，使用一个“-'”，例如，[a-z]匹配任何字母，而[0-9]匹配任何数字。字符串匹配（5）'*'：匹配零个或多个在它前面的字符，例如，'x*'匹配任何数量的'x'字符，'[0-9]*'匹配任何数量的数字，而'*'匹配任何数量的任何字符。例如：ELECT'mysql'REGEXP'^m','mysql'REGEXP'l$','mysql'REGEXP'm.sql'; #（a）SELECT'mysql'REGEXP'[a-k]','mysql'REGEXP'[l-z]'; #（b）运行结果如图7.29所示。06逻辑运算符和位运算符1.逻辑运算符2.位逻辑运算符和位移动运算符逻辑运算符和位运算符1.逻辑运算符逻辑运算符用于对操作数整体进行逻辑操作，如表7.4所示。运算符作

用逻辑运算表（真：1或者TRUE，假：0或者FALSE）NOT,!逻辑非NOT0=1，NOT1=0，NOTNULL=NULLAND,&&逻辑与0AND0=0,0AND1=0,1AND0=0，1AND1=1，xANDNULL=NULLOR,||逻辑或0OR0=0，0OR1=1，1OR0=1，1OR1=1，1ORNULL=1，0ORNULL=NULLXOR逻辑异或0XOR0=0,0XOR1=1，1XOR0=1，1XOR1=0，xXORNULL=NULL说明：（1）将所有非零数值（例如-98）、数字打头的字符串都看作是1（TRUE），而非数字（例如'Apple苹果'）的字符串则看作是0（FALSE）。（2）从MySQL8.0.17开始，不推荐使用！、&&、||符号，因它们均为软件开发商定义扩展的逻辑运算符，在将来的MySQL版本中将删除对它们的支持。故应将应用程序中使用的！、&&、||符号调整为标准SQL的NOT、AND和OR符号。例如：SELECTNOT-98,NOT0,!NULL,!'Apple苹果',!FALSE,NOT'1';运行结果如图7.30所示。逻辑运算符和位运算符【例7.12】逻辑运算符测试。SET@x=0;SELECT@x+1ANDTRUE,'@x-0.98'AND@x+1,1&&NULL; #（a）SELECT@xORTRUE,@x=1OR'@x',1ORNULL; #（b）SELECT@x-1XORFALSE,'-0.98+x'XORTRUE,1XORNULL; #（c）运行结果如图7.31所示。

逻辑运算符和位运算符2.位逻辑运算符和位移动运算符位运算符是在二进制数上进行计算的运算符，它先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制。MySQL的位运算符见表7.5。运算符作

用实例：x=0b01010011=83y=0b00001111=15~按位取反~x=0b10101100=&按位与x&y=0b00000011=3|按位或x|y=0b01011111=95^按位异或x^y=0b01011100=92<<

左移y<<2=ob00111100=60>>

右移y>>2=0b00000011=3逻辑运算符和位运算符【例7.13】位逻辑运算符和位移动运算符测试。SET@x=83;SET@y=15;SELECT~0-~@x,@x&@y,@x|@y,@x^@y,@y<<2,@y>>2;运行结果如图7.32所示。07表达式和运算符的优先级1.表达式2.运算符的优先级表达式和运算符的优先级1.表达式所谓“表达式”就是操作数（包括常量和变量）、列名函数通过运算符进行有机组合的式子。例如，下列SELECT语句显示项均为表达式：SET@x=7; #（a）SELECT(@x%-2)+0.5*(-0.1); #（b）SELECT3.14IN(ABS(@x)+0.14); #（c）SELECT'mysql'REGEXP'[a-k]'; #（d）说明：（a）赋值表达式。（b）算术表达式。（c）算术表达式。（d）字符串匹配逻辑表达式。表达式和运算符的优先级又例如，操作数据库表SQL语句表达式：USEemarket;CREATETABLEuser( ...

手机号 char(11) NOTNULL UNIQUECHECK(LENGTH(TRIM(手机号))=11ANDLEFT(手机号,1)='1'), #（a） ... CHECK(YEAR(有效期)-CONVERT(SUBSTR(身份证号,7,4),UNSIGNED)>=20) #（b）);SELECT...FROMcommodityWHERE商品编号<'3'AND价格>=100; #（c）UPDATE... SETcommodity_new.价格=commodity_new.价格-10 #（d） WHEREcommodity_temp.原价-commodity_new.价格<15ANDcommodity_new.商品编号=commodity_temp.商品编号; #（e）说明：（a）字符串表达式、比较表达式、逻辑表达式。（b）字符串表达式、算术表达式、比较表达式。（c）比较表达式、逻辑表达式。（d）算术表达式、赋值表达式。（e）算术表达式、比较表达式、逻辑表达式。表达式和运算符的优先级2.运算符的优先级当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序，会影响所得到的运算结果。MySQL运算符的优先级如表7.6所示，级别数字越小表示优先级越高。运

算

符级

别!（逻辑取反）0–（负号）、～（按位取反）1^（按位异或）2*（乘法）、/（除法）、DIV（整数除法）、%,MOD（取模）3+（加法）、–（减法）4<<（位左移）、>>（位右移）5&（按位与）6|（按位或）7比较运算符：=、<=>、<>,!=、<=、<、>=、>、ISNULL、IN、LIKE、REGEXP8BETWEENAND9NOT（逻辑非）10AND,&&（逻辑与）11XOR（逻辑异或）12OR,||（逻辑或）13=（赋值）、:=14表达式和运算符的优先级说明：（1）对表达式求值时，级别高的运算符先进行计算。（2）当一个表达式中有多个运算符级别相同时，根据它们在表达式中的位置，从左至右依次进行计算。但有一例外：赋值（=、:=）是从右至左进行。（3）“=”作为比较运算符与<=>、<>,!=、<=、<、>=、>等具有相同的优先级；但作为赋值运算符时，则与:=优先级相同。（4）一些运算符的优先级和含义取决于SQL模式：如在默认情况下，!的优先级高于NOT。但启用HIGH_NOT_PRECEDENCE后，两者具有相同的优先级。（5）可用括号改变运算符的优先性，先对括号内的表达式求值，然后对括号外的运算符进行运算时使用该值。若表达式中有嵌套的括号，则首先对嵌套最深的表达式求值。第7章

运算符、表达式和系统函数——系统函数系统函数1.数学函数数学函数是用来处理数值型数据的函数，主要有常用运算函数、数制及转换、取接近值函数、幂和对数函数、随机数函数、三角函数等。在有错误产生时，数学函数返回NULL。2.字符串函数字符串函数是专门用来处理字符串数据的，可实现对字符串进行长度统计、比较、获取、定位、改变、合并、替换、删除空格等诸多灵活而强大的功能。字符串函数包括长度统计和比较函数、获取字符串函数、字符串定位函数、改变字符串函数、删除空格函数和生成字符串函数。系统函数3.日期时间函数日期时间函数又分为日期函数和时间函数，分别用来处理日期和时间值。日期函数通常接受date类型的参数，也可使用datetime、timestamp型参数，但会忽略值的时间部分；同样地，时间函数通常以time类型值为参数，也可接受datetime、timestamp类型值，但会忽略其日期部分。此外，许多日期时间函数还可以同时接受描述日期和时间的数字和字符串类型的参数。日期时间函数包括获取当前日期和时间函数、获取日期包含信息函数、获取时间包含信息函数、计算日期所在周函数、日期和时间运算函数、日期时间格式化函数和时间与秒转换函数。4.类型转换函数类型转换函数包括数值转换为进制字符串、进制与对应字符函数、字符串转换为数值和日期和IP地址转换函数。系统函数5.JSON函数JSON函数用于处理JSON数据类型及JSON列记录的基本操作函数。包括JSON基本操作函数、JSON数据检索函数、JSON数据修改函数、获取JSON属性函数和JSON数据转化函数。6.空间数据处理函数空间数据处理函数分为空间对象创建函数、获取空间对象属性、空间对象计算和处理和几何对象判断。MySQL中空间对象都有特定的格式，一般都要将WKT串转换为对应格式，才能使用MySQL提供的空间数据处理函数进行存储计算。7.窗口函数MySQL支持的窗口函数按照功能分为如下几类：序号函数、分布函数、前后函数、头尾函数和其他函数。8.其他函数其他函数包括判断函数、加密解密函数、聚合函数(常用于SELECT…GROUPBY中)和系统信息函数。加密解密函数、聚合函数(常用于SELECT…GROUPBY中)和系统信息函数。第8章

查询、视图和索引——数据库查询MySQL+实用教程（第4版）数据库查询SELECT语句，它的功能非常强大、使用极为灵活，它可以实现对表的选择、投影及连接操作。主干形式如下：SELECT[ALL|DISTINCT|DISTINCTROW]输出项,... [FROM表名|视图名] /*指定数据源*/ [WHERE条件表达式] /*指定查询条件*/ [GROUPBY...] /*指定分组项*/ [HAVING条件] /*指定分组后筛选条件*/ [ORDERBY...] /*指定输出行排列依据项*/ [LIMIT行数] /*指定输出行范围*/ [UNIONSELECT语句] /*数据源联合*/ [WINDOWS...] /*窗口定义*/01选择输出项1．输出项为列名2．输出项为表达式3.输出内容变换4．消除输出项的重复行5．聚合函数6.JSON类型列的部分内容选择输出项1．输出项为列名输出项为一个表中的某些列，各列名之间以逗号分隔。当希望使用自己命名的标题时，可以在列名之后加“AS别名”。【例8.1】查询网上商城数据库（emarket）的商品表（commodity）中所有的商品编号、商品名称、库存量和单价。USEemarket;SELECT商品编号,商品名称,库存量,价格AS商品单价FROMcommodity;查询结果如图8.1所示。选择输出项2．输出项为表达式列名本身就是一个简单表达式，也可以是包含列名的一般表达式。例如，对数字列进行各种计算。为了避免不太直观的表达式作为输出项标题，可以用AS定义一个别名作为新的计算结果列的名称。【例8.2】将1000元以上的商品打8折出售，并同时显示原价和优惠价。USEemarket;SELECTLEFT(商品编号,1)AS类别编号,商品编号,商品名称,价格AS'原

价',FORMAT(价格*0.8,2)AS优惠价 FROMcommodity WHERE价格>1000;查询结果如图8.2所示。说明：（1）LEFT(商品编号,1)为字符串表达式，获得商品编号列第1个字符。（2）不允许在WHERE子句中使用列别名。例如：“WHERE原价>1000”。（3）别名中含有空格时，须用引号括起来，比如AS'原

价'。（4）价格包含两位小数，价格*0.8后就会超过两位，为了显示两位小数，采用FORMAT(价格*0.8,2)函数。选择输出项【例8.3】查询用户（user）表姓名、年龄和身份证是否有效。SELECT姓名, YEAR(NOW())-CONVERT(SUBSTR(身份证号,7,4),UNSIGNED)+1AS年龄,IF(有效期>=NOW(),'有效','过期')AS身份证有效 FROMuser;查询结果如图8.3所示。说明：（1）SUBSTR(身份证号,7,4)是出生年份n，CONVERT(n,UNSIGNED)将n转换为无符号整数n1，YEAR(NOW())为获得当前日期中的年份n2，n2-n1+1就是年龄。（2）IF(有效期>=NOW(),'有效','过期')：有效期（date类型）列与当前日期对比，大于等于为'有效'否则为'过期'。选择输出项3.输出内容变换在对表进行查询时，输出列显示内容可以通过CASE语句进行变换。CASE WHEN条件1THEN表达式1 ...... ELSE表达式nEND【例8.4】在emarket数据库商品分类表（category）中，显示商品分类记录对应的大类。USEemarket;SELECT类别编号,类别名称, CASE WHENLEFT(类别编号,1)='1'THEN'水果' WHENLEFT(类别编号,1)='2'THEN'肉禽' WHENLEFT(类别编号,1)='3'THEN'海鲜水产' ELSE'粮油蛋' ENDAS大类 FROMcategory;选择输出项查询结果如图8.4所示。选择输出项也可以采用IF()函数嵌套变换输出项：IF(逻辑表达式,表达式1,表达式2)功能：如果逻辑表达式为真，函数值为表达式1值，否则为表达式2值。表达式2又可以是IF()函数，以此类推。例如：商品表（commodity）中商品编号前两位对应类别编号，第一位也是大类编号，输出项变换商品大类名称，可以进行下列变换：SELECT IF(LEFT(商品编号,1)='1','水果',IF(LEFT(商品编号,1)='2','肉禽',IF(LEFT(商品编号,1)='3','海鲜水产','粮油蛋')))AS大类,商品编号,商品名称,价格,库存量 FROMcommodity;说明：显示商品表（commodity）所有记录，输出项包括大类、商品编号、商品名称、价格、库存量。大类通过商品编号第一个字符变换得到。选择输出项4．消除输出项的重复行查询结果显示“输出项”可能会出现重复行，可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行。【例8.5】查询orders表中出现的所有帐户。对emarket数据库的订单表（orders）只选择帐户名，由于同一个帐户会多次购物而出现多个订单，消除重复行可以很方便看清当前有哪些帐户购物。USEemarket;SELECT帐户名 FROMorders; #（a）SELECTDISTINCT帐户名FROMorders; #（b）查询结果如图8.5所示。

选择输出项5．聚合函数输出项为表达式中用到的系统函数仅仅是对包含的列值进行处理，而这里的“聚合函数”处理的是查询得到的行和列。下表8.1列出了MySQL常用的聚合函数。函

数

名说

明COUNT求记录行数MAX求最大值MIN求最小值SUM求表达式列的和AVG求表达式列的平均值STD或STDDEV求表达式列中所有值的标准差VARIANCE求表达式列中所有值的方差GROUP_CONCAT产生由属于一组的列值连接组合而成的字符串选择输出项【例8.6】统计commodity表中的商品记录总数，商品价格的最高、最低及平均值，并计算出全部商品的总价值。USEemarket;SELECTCOUNT(商品编号)AS商品总数, MAX(价格)AS最高价,MIN(价格)AS最低价,FORMAT(AVG(价格),2)AS均价,SUM(价格*库存量)AS总价值 FROMcommodity;查询结果如图8.6所示。选择输出项【例8.7】计算commodity表中所有商品价格的方差和标准差。统计学上的标准差等于方差的平方根，所以STDDEV(…)和SQRT(VARIANCE(…))这两个表达式是相等的。方差的计算按以下几个步骤进行。（1）计算相关列的平均值。（2）求列中的每一个值与平均值之差。（3）计算差值的平方的总和。（4）用总和除以（列中的）值的个数得结果。USEemarket;SELECTVARIANCE(价格)AS方差,STDDEV(价格)AS标准差,SQRT(VARIANCE(价格))AS方差的平方根FROMcommodity; #（a）SET@avg=(SELECTAVG(价格)FROMcommodity); #计算价格平均值SELECTSUM(POW(价格-@avg,2))/COUNT(商品编号)AS方差FROMcommodity; #（b）查询结果如图8.7所示。

选择输出项MySQL支持一个特殊的聚合函数GROUP_CONCAT，它返回一个组指定列的所有非NULL值的长字符串，这些值一个接着一个放置，中间用逗号隔开。这个字符串最大长度标准值是1024。【例8.8】罗列出商品分类表（category）中水果大类的商品类别名称。USEemarket;SELECTGROUP_CONCAT(类别名称)FROMcategoryWHERELEFT(类别编号,1)='1';查询结果如图8.8所示。选择输出项6.JSON类型列的部分内容JSON类型列内容是符合“键：值”结构的，输出项中可通过“列名->路径”或者“列名->>路径”及JSON函数指定部分内容。例如：SELECT姓名,常用地址->'$."地址"."位置"'住址FROMuser;查询结果如图8.9所示。02单数据源1.表作为数据源2.表分区作为数据源3.查询作为数据源4.视图作为数据源单数据源SELECT语句的查询对象（即数据源）由FROM子句指定：FROM数据源[AS别名],...数据源:=表名[分区]|视图|查询|连接1.表作为数据源当表作为数据源时，如果查询表位于当前数据库，直接写表名即可；如果查询表不在当前数据库，表名前需要加数据库名前缀，或者使用“USE数据库名”将指定的数据库变成当前数据库再行查询。查询的数据库可以是用户创建的，也可以是MySQL系统的。1）查询用户数据库表信息例如：USEemarket;SELECT*FROMmydb.test;SELECT*FROMcommodityWHERELEFT(商品编号,1)='1';单数据源2）查询系统数据库表信息系统数据库存放服务器、数据库及其对象的有关信息，通过查询可以获得用户关注的信息。例如：（1）查询MySQL支持的字符集：SELECT*FROMinformation_schema.character_sets;（2）通过系统数据库information_schema字典表columns查询mydb数据库mytab表列的字符集和排序规则：USEmydb;SELECTcolumn_name,character_set_name,collation_nameFROMinformation_schema.columnsWHEREtable_name='mytab';（3）通过系统数据库information_schema字典表tables查询指定表的状态信息：SELECT*FROMinformation_schema.tablesWHEREtable_schema='mydb'andtable_name='test';单数据源（4）通过系统数据库information_schema的PARTITIONS表查询用户数据库表分区信息：SELECT PARTITION_NAME分区名称, PARTITION_ORDINAL_POSITION排序, PARTITION_METHOD分区类型, PARTITION_EXPRESSION表达式, PARTITION_DESCRIPTION描述, CREATE_TIME创建时间, TABLE_ROWSAS记录数 FROMinformation_schema.PARTITIONS WHERETABLE_SCHEMA=SCHEMA()ANDTABLE_NAME='youth';单数据源2.表分区作为数据源不指定分区，表的所有记录均为查询数据源；包含分区，则仅将指定分区内的记录作为查询数据源。【例8.9】查询商品表中水果库存量大于10000的商品。（1）对商品表（commodity）查询。USEemarket;SELECT*FROMcommodityWHERELEFT(商品编号,1)='1'AND库存量>10000;单数据源（2）对商品分区表指定分区查询。前面已经对商品表（commodity）的副本商品分区表（commodity_part）进行过分区，这里为方便对比再次列出：ALTERTABLEcommodity_part PARTITIONBYRANGECOLUMNS(商品编号) ( PARTITION水果VALUESLESSTHAN('2'), PARTITION肉禽VALUESLESSTHAN('3'), PARTITION海鲜水产VALUESLESSTHAN('4'), PARTITION粮油蛋VALUESLESSTHAN(MAXVALUE) );按照分区查询，显示结果如图8.10所示：SELECT*FROMcommodity_partPARTITION(水果)WHERE库存量>10000;单数据源（3）对商品分区表不指定分区查询。SELECT*FROMcommodity_partWHERE商品编号<'2'AND库存量>10000;因为WHERE条件包含商品编号列，系统会根据条件匹配分区。这里匹配的也会是水果分区。同理，对于表按HASH和KEY分区的，用户一般不能简单知道记录分区存放情况，所以不能显式指定查询分区数据源，但系统会根据WHERE查询条件是否包含分区列，指定匹配对应的分区进行查询，否则分区就失去意义。所以我们应该将最频繁查询的列（表达式）作为分区依据。单数据源3.查询作为数据源查询作为数据源：FROM(SELECT语句)名称,…将FROM后的查询结果作为数据来源，然后对其进行查询。【例8.10】查询库存量10000以上的水果。SELECT*FROMcommodityWHERELEFT(商品编号,1)='1'AND库存量>10000; #（a）SELECT*FROM(SELECT*FROMcommodityWHERELEFT(商品编号,1)='1')commodity1WHERE库存量>10000; 说明：（a）在commodity表筛选出符合LEFT(商品编号,1)='1'AND库存量>10000条件的记录。（b）查询结果与（a）查询语句的结果相同。这里先从commodity表筛选出符合LEFT(商品编号,1)='1'条件的记录作为数据源（commodity1），然后再在commodity1数据源查询符合“库存量>10000”条件的记录。单数据源4.视图作为数据源视图作为数据源进行查询与表一样，仅仅是视图中的数据记录是由定义它的查询语句决定的。USEemarket;CREATEVIEWcommodity_1 ASSELECT*FROMcommodityWHERELEFT(商品编号,1)='1'; #（a）SELECT*FROMcommodity_1WHERE库存量>10000; #（b）说明：（a）创建视图，视图中的数据是定义它的查询结果，并以视图名“commodity_1”标识。（b）把commodity_1视图作为数据源，对其进行查询。03多数据源1．全连接2．内连接3．外连接4．自然连接5．交叉连接6．径直连接多数据源1．全连接各个数据源之间用逗号分隔就指定了一个全连接，又叫“等值连接”。连接后产生的中间结果是一个新表，它是每个数据源的每行都与其他数据源中的每行交叉产生的所有可能组合，也就是笛卡儿积（每个数据源行数相乘），列包含了所有数据源中出现的列。例如，订单表（orders）包含4列9行（记录），订单项表（orderitems）包含4列13行（记录），如图8.11所示。

多数据源那么下列语句：SELECT*FROMorders,orderitems;将产生9*13=117行（记录），4+4=8列作为输出项。全连接的多表可通过WHERE指定查询条件，输出项前需要以表名作为前缀，表明输出的内容出于哪个表。如果列名在各表中均不相同，输出列名前可不加前缀。【例8.11】查找所有购买过商品的帐户名、订单编号和商品编号。分析：订单表（orders）中包含帐户名和订单编号，订单项表（orderitems）中包含订单编号和商品编号，所以需要把这两个表通过订单编号连接起来进行查询。USEemarket;SELECT帐户名,orderitems.订单编号,商品编号 FROMorders,orderitems WHEREorders.订单编号=orderitems.订单编号;多数据源显示结果如图8.12所示。多数据源说明：（1）这里是在117行（记录）中间结果中查询符合WHEREorders.订单编号=orderitems.订单编号条件的13条记录，输出项从8列选择3列。（2）因为订单编号在两个表中都存在，所以需要加表名作为前缀指定值从哪一个表得到，而帐户名和商品编号只在一个表中存在，可以不加前缀。为了把多个表组成一个有效的整体进行有效查询，通常使用JOIN关键字指定连接类型，把它们连接起来：表名[INNER|CROSS]JOIN表名[连接条件]|表名STRAIGHT_JOIN表名|表名STRAIGHT_JOIN表名ON连接条件|表名LEFT|RIGHT[OUTER]JOIN表名连接条件|表名NATURAL[LEFT|RIGHT[OUTER]]JOIN表名多数据源2．内连接指定了INNER