MySQL8数据库教程教学课件(共9章)第7章--MySQL-8运算符、表达式和系统函数.pptx

第7章 MySQL 8运算符、表达式和系统函数常量和变量目录01常 量1数值常量2字符串常量3日期时间常量4布尔值5NULL值常 量1数值常量（1）十进制数常量十进制数常量分为整数常量和浮点数常量整数常量即不带小数点的十进制数，例如：1894，-2，+145345234，2147483648浮点数常量是使用小数点的数值常量，例如：5.26，1.39，101.5E5，0.5E-2例如：SELECT 152+5.26,78+0.5E-2;（2）非十进制数常量非十进制数常量包括b（B）二进制表示和x（X）十六进制表示，它们只能表示整数有两种表示方式，一种是以0打头，在进制表示符后跟进制数；另一种以进制表示符打头后跟进制数字符串，但十六进制数字需要双数常 量2字符串常量字符串常量可以用引号括起来，也可以通过十六进制表达1）一般字符串常量一般字符串常量用单引号括起来，例如：你好，How are you!其中，ASCII字符用一个字节存储，中文用2个字节存储2）Unicode字符串常量Unicode字符串常量前面有一个N标志符，代表SQL-92标准的国际语言（National Language），用单引号括起字符串。

例如：N你好，How are you!其中，每个字符（中文或者英文）用两个字节存储常 量（3）字符串中特殊的字符表示在字符串中不仅可以使用普通的字符，也可使用几个转义序列来表示特殊的字符，见表7.1每个转义序列以一个反斜杠（“”）开始序序 列列含含 义0一个ASCII零值字节（NUL）字符n一个回车符r一个换行符（Windows中使用rn作为新行标志）t一个定位符b一个退格符Z一个ASCII 为26字符（Ctrl+Z）一个单引号（“”）一个双引号(“”)一个反斜线（“”）%一个“%”符，用于在正文中搜索包含“%”字符，否则“%”将解释为一个通配符一个“_”符，用于在正文中搜索包含“_”字符，否则“_”将解释为一个通配符常 量（4）字符串中包含引号字符字符串内包含单引号（），字符串需要用双引号（），否则需要用转义字符（）表示单引号；字符串内包含双引号（），字符串需要用单引号（），否则需要用转义字符（）表示双引号例如：SELECT hello,hello,hello,hello,hello;SELECT hello,hello,hellonhello;（5）十六进制、二进制表示字符串每对（即2个）十六进制数字被转换为一个字符，不区分大小写。

例如：0 x4142和x4142表示字符串“AB”，x4D7953514C表示字符串“MySQL”每8位（1个字节）二进制数字被转换为一个字符例如：0b01000001表示字符“A”，b0100000101000010表示字符串“AB”在输出显示时，十六进制和二进制表示被默认为表达字符，而在参与算术运算时则被认为是表达数值例如：SELECT-0b1000011,b1000011+0,0 x1F0+1,-x01F0;#（a）SELECT 0b01000001,x4D7953514C;#（b）常 量3日期时间常量满足日期时间要求的字符串它当然本身就是字符串，但当用于日期时间位置、符合日期时间格式要求并且有效时，就会被看成是日期时间常量4布尔值布尔值只包含两个可能的值：TRUE和FALSETRUE的数字值为1，FALSE的数字值为05NULL值NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等含义，并且不同于数字类型（0），或字符串类型的空字符串（）目录02变 量1系统变量2.用户变量变 量1系统变量1）系统变量获取和设置系统变量在MySQL服务器启动时就被引入并初始化为默认值。

例如：VERSION可获得当前使用的MySQL版本，像这样的系统变量的值是不可以改变的大多数的系统变量应用于SQL语句中必须在名称前加两个符号，而为了与其他SQL产品保持一致，某些特定的系统变量是要省略这符号的如CURRENT_DATE（系统日期）、CURRENT_TIME（系统时间）、CURRENT_TIMESTAMP（系统时间戳）和CURRENT_USER（SQL用户名）例如：SELECT VERSION,CURRENT_TIME;有些系统变量是可以通过SET语句来修改的：SET GLOBAL|SESSION 系统变量名=表达式或者SETGLOBAL.|SESSION.系统变量名=表达式变 量2）全局系统变量和会话系统变量（1）全局系统变量（指定GLOBAL）：当MySQL启动的时候，全局系统变量就被初始化了，并且应用于此后每一个启动的会话如果设置GLOBAL来系统变量（需要超级用户权限），则该值被记住，并被用于新的连接，直到服务器重新启动为止2）会话系统变量只适用于当前的会话大多数会话系统变量的名字和全局系统变量的名字相同当启动会话的时候，默认情况下每个会话系统变量都和同名的全局系统变量的值相同。

一个会话系统变量的值是可以改变的，但是这个新的值仅适用于当前正在运行的会话3）若改变了全局系统变量的值，同名的会话系统变量的值也保持不变变 量3）系统变量清单显示系统变量清单：SHOW VARIABLES LIKE 条件#显示系统变量清单SHOW GLOBAL VARIABLES LIKE 条件#显示所有全局系统变量SHOW SESSION VARIABLES LIKE 条件#显示所有会话系统变量例如：SHOW VARIABLES;#（a）SHOW GLOBAL VARIABLES LIKE table_%;#（b）SHOW SESSION VARIABLES LIKE character_%;#（c）变 量【例7.1】将无索引排序内存容量控制的全局系统变量SORT_BUFFER_SIZE的值改为3MB，会话值改为5MB1）设置会话系统变量SORT_BUFFER_SIZESELECT GLOBAL.SORT_BUFFER_SIZE,SESSION.SORT_BUFFER_SIZE;#（a）SET SESSION.SORT_BUFFER_SIZE=1024*1024*5;#（b）SET GLOBAL SORT_BUFFER_SIZE=1024*1024*3;#（b）SELECT GLOBAL.SORT_BUFFER_SIZE,SESSION.SORT_BUFFER_SIZE;#（b）SET SORT_BUFFER_SIZE=DEFAULT;#（c）SELECT GLOBAL.SORT_BUFFER_SIZE,SESSION.SORT_BUFFER_SIZE;#（c）2）系统变量SORT_BUFFER_SIZE说明。

SELECT查询中需要排序（ORDER BY x），但x无法使用索引时，通过SORT_BUFFER_SIZE变量控制排序占用的内存容量，从而提高查询性能USE mydb;SELECT SQL_NO_CACHE*FROM youth WHERE ID 10000 ORDER BY ID DESC;因为ORDER BY ID中ID为主键，所以SORT_BUFFER_SIZE大小改变对上面查询没有影响SELECT SQL_NO_CACHE*FROM youth WHERE ID 10000 ORDER BY Name DESC;变 量3）SET_VAR使用SET_VAR语法可动态调整部分参数，有利于提升该语句性能例如：USE mydb;SELECT/*+SET_VAR(sort_buffer_size=16M)*/*FROM test;INSERT/*+SET_VAR(foreign_key_checks=OFF)*/INTO test(id,c2)VALUES(5,EE);DELETE/*+SET_VAR(foreign_key_checks=OFF)*/FROM test WHERE id=5;SELECT *FROM test;变 量2.用户变量用户自己定义的变量叫用户变量，可在其中保存值，以后再引用它，这样在一个会话中可以将值从一个语句传递到另一个语句。

用户变量可用下列语句定义和赋值：SET变量名=表达式,.说明：（1）变量名由当前字符集的文字和数字字符、“.”、“_”和“$”组成当变量名中需要包含一些特殊符号（如空格、#等）时，可以使用双引号或单引号将整个变量括起来必须放在一个用户变量的前面，以便将它和列名区分开2）用户变量的数据类型取决于赋予它的表达式值表达式可以是常量、已经赋值的用户变量或它们通过运算符组成的式子，也可以是NULL值3）没有初始化的用户变量的值为NULL例7.2】创建用户变量及其表达式SET user1=1,user2=user1+1;SELECT user1,user2,user3;#（a）SET user2=user1+1;SELECT user1,user2;#（b）变 量（4）只有当一个用户变量已经被创建并初始化后，它才可以用于其他SQL语句中，变量名前必须加上符号例7.3】以“商品名称”作为中间变量查询指定编号的商品信息USE emarket;SET cid=1A0101;SET cname=(SELECT 商品名称 FROM commodity WHERE 商品编号=cid);SELECT cid,cname;#（a）SELECT 商品名称,价格 FROM commodity WHERE 商品编号=cid INTO cname,cprice;SELECT cid,cname,cprice;#（b）（5）用其他语句代替SET语句来为用户变量分配一个值，分配符必须为“:=”，而不能用“=”，因为在非SET语句中“=”被视为比较操作符。

例如：SELECT t2:=(t2:=2)+5 AS t2;（6）用户变量可以用于存放数据库的查询结果第7章 MySQL 8运算符、表达式和系统函数运算符与表达式目录01赋值运算符赋值运算符因“=”只有在SET语句中才被作为赋值运算符使用，其他上下文情形下都被视作等于比较运算符，而“:=”则在任何合法的SQL语句中都是赋值运算符，故实际应用中要进行赋值运算，建议优先采用“:=”形式例7.4】赋值运算符修改数据库表列值USE mydb;CREATE TABLE test(t1 int);INSERT INTO test VALUES(3),(5),(7);SELECT*FROM test;SET var1:=5;UPDATE test SET t1=2 WHERE t1=var1;SELECT*FROM test;目录02算术运算符算术运算符算术运算符是用于数值型数据运算的，常用的算术运算符见表7.2运算符运算符作用作用实例例DIV整数除法7 DIV 2=3/除法7/2=3.5%,MOD取模运算7%2=1*乘法7*2=14-减法或负号-7-2=-9+加法-7+2=-5（1）算术运算符优先级：先算括号里面的；先负号（-），再*、/、DIV，然后%，最后+和-；同级从左到右。

2）DIV与/的区别是它会丢弃运算结果的小数部分3）自动将除法运算结果规格化为固定位数的小数，除数为零时会产生NULL结果4）不同进制进行算术运算均转换为十进制5）数值字符串被当成数值进行运算，字符串中含非数字字符，其前面部分数字字符当成数值首字符为非数字，则作为0例如：SELECT-7/2+1,7 DIV 2,7 MOD 2,(7%-2)+0.5*(-0.1),1/(7 MOD 2-1);#（a）SELECT 0b01101+b11,0 x1E+11,-b01101+21+x1E,21-3A-C3;#（b）目录03比较运算符1.等于（=）2.安全等于（）3.不等于（或!=）4.小于等于（=）5.小于（=）7.大于（）比较运算符比较运算符用于操作数间的比较运算，运算的结果总是1、0或NULL它经常用在SELECT语句的。