mysql简单代码（MySQL编程基础）

MySQL客户机1定义的所有会话变量将自动释放，以便节省MySQL服务器的内存空间。

系统会话变量与用户会话变量的共同之处

• 变量名大小写不敏感。

系统会话变量与用户会话变量的区别

• 用户会话变量一般以一个“@”开头；系统会话变量以两个“@”开头。
• 系统会话变量无需定义可以直接使用。

用户会话变量的定义与赋值

一般情况下，用户会话变量的定义与赋值会同时进行。用户会话变量的定义与赋值有两种方法：使用set命令或者使用select语句。

使用set命令定义用户会话变量，并为其赋值。用户会话变量的数据类型是根据赋值运算符 = 右边表达式的计算结果自动分配的。也就是说，等号右边的值（包括字符集和字符序）决定了用户会话变量的数据类型（包括字符集和字符序）。需要使用 :＝ 赋值语句，原因在于 = 是为“比较”保留的。

set @user_variable1＝expression1 [,@user_variable2= expression2 , …]

使用select语句定义用户会话变量，并为其赋值。select语句会产生结果集。

select @user_variable1:＝expression1 [,user_variable2:= expression2 , …]

用于会话变量的定义及赋值（但不会产生结果集）。

select expression1 into @user_variable1, expression2 into @user_variable2,…

局部变量

declare命令专门用于定义局部变量及对应的数据类型。局部变量必须定义在存储程序中（例如函数、触发器、存储过程以及事件中），并且局部变量的作用范围仅仅局限于存储程序中，脱离存储程序，局部变量没有丝毫意义。局部变量主要用于下面三种场合。

• 局部变量定义在存储程序的begin-end语句块（稍后介绍）之间。此时局部变量首先必须使用declare命令定义，并且必须指定局部变量的数据类型。只有定义局部变量后，才可以使用set命令或者select语句为其赋值。
• 局部变量作为存储过程或者函数的参数使用，此时虽然不需要使用declare命令定义，但需要指定参数的数据类型。
• 局部变量也可以用在SQL语句中。数据检索时，如果select语句的结果集是单个值，可以将select语句的返回结果赋予局部变量，局部变量也可以直接嵌入到select、insert、update以及delete语句的条件表达式中。

局部变量与用户会话变量的区别

• 用户会话变量名以 @ 开头，而局部变量名前面没有 @ 符号。
• 局部变量使用declare命令定义（存储过程参数、函数参数除外），定义时必须指定局部变量的数据类型；局部变量定义后，才可以使用set命令或者select语句为其赋值。 用户会话变量使用set命令或select语句定义并进行赋值，定义用户会话变量时无需指定数据类型。诸如“declare @student_no int;”的语句是错误语句，用户会话变量不能使用declare命令定义。
• 用户会话变量的作用范围与生存周期大于局部变量。局部变量如果作为存储过程或者函数的参数，此时在整个存储过程或函数内中有效；如果定义在存储程序的begin-end语句块中，此时仅在当前的begin-end语句块中有效。用户会话变量在本次会话期间一直有效，直至关闭服务器连接。
• 如果局部变量嵌入到SQL语句中，由于局部变量名前没有“@”符号，这就要求局部变量名不能与表字段名同名，否则将出现无法预期的结果。

运算符与表达式

根据运算符功能的不同，可将MySQL的运算符分为算术运算符、比较运算符、逻辑运算符以及位操作运算符。

算术运算符

用于两个操作数之间执行算术运算。常用的算术运算符有： （加）、-（减）、*（乘）、/（除）、%（求余）以及div（求商）等6种运算符。

比较运算符

又称关系运算符，用于比较操作数之间的大小关系，其运算结果要么为true、要么为false、要么为NULL（不确定）。

select 'ab '='ab', ' ab'='ab', 'b'>'a', NULL=NULL,NULL<=>NULL, NULL is NULL;

字符串进行比较时，会截掉字符串尾部的空格字符，然后进行比较。

运算符	含义
=	等于
>	大于
\<	小于
>=	大于等于
\<=	小于等于
<>、!=	不等于
<=>	相等或都等于空

逻辑运算符

又称布尔运算符，对布尔值进行操作，其运算结果要么为true、要么为false、要么为NULL（不确定）。

运算符	含义
not 或!	逻辑非
and 或 &&	逻辑与
or 或 ||	逻辑或
xor	逻辑异或

位运算符

对二进制数据进行操作（如果不是二进制类型的数，将进行类型自动转换），其运算结果为二进制数。使用select语句显示二进制数时，会将其自动转换为十进制数显示。

运算符	含义
&	按位与
|	按位或
^	按位异或
~	按位取反
>>	位右移
<<	位左移

begin-end语句块

delimiter $$ SELECT san_wujiang.姓名 from san_wujiang where san_wujiang.姓名 like '%曹%' $$ delimiter; [开始标签:] begin [局部]变量的声明; 错误触发条件的声明; 游标的声明; 错误处理程序的声明; 业务逻辑代码; end[结束标签];

定义函数

函数可以看作是一个 加工作坊 ，这个 加工作坊 接收 调用者 传递过来的 原料 （实际上是函数的参数），然后将这些 原料 、 加工处理 成 产品 （实际上是函数的返回值），再把 产品 返回给 调用者 。

创建自定义函数的语法格式

create function 函数名（参数1，参数2，…）returns 返回值的数据类型 [函数选项] begin 函数体; return 语句; end;

函数的创建与调用

delimiter // CREATE FUNCTION name_people (name_word varchar(20)) returns varchar(200) begin return (SELECT san_wujiang.`出生年` from san_wujiang where `姓名` = name_word); end // delimiter; SELECT name_people('曹操')

函数的维护

函数的维护包括查看函数的定义、修改函数的定义以及删除函数的定义等内容。

查看函数的定义

show function status;

自定义函数较多，可以进行模糊查询。

show function status like 模式;

查看指定数据库

例如wujiang数据库中的所有自定义函数名。

select name from mysql.proc where db = 'san_wujiang' and type = 'function' ;

查看指定函数名的详细信息

show create function 函数名;

查询函数的相关信息

函数的信息都保存在information_schema数据库中的routines表中，可以使用select语句检索routines表，查询函数的相关信息。

select * from information_schema.routines where routine_name='get_name_fn'

函数定义的修改

由于函数保存的仅仅是函数体，而函数体实际上是一些MySQL表达式，因此函数自身不保存任何用户数据。当函数的函数体需要更改时，可以使用drop function语句暂时将函数的定义删除，然后使用create function语句重新创建相同名字的函数即可。这种方法对于存储过程、视图、触发器的修改同样适用。

函数定义的删除

drop function函数名

条件控制语句

if语句

if语句根据条件表达式的值确定执行不同的语句块。

if 条件表达式1 then 语句块1; [elseif 条件表达式2 then语句块2] ... [else语句块n] end if;

case语句

case语句用于实现比if语句分支更为复杂的条件判断。MySQL中的case语句与C语言、Java语言等高级程序设计语言不同，在高级程序设计语言中，每个case的分支需使用“break”跳出，而MySQL无需使用“break”语句。

case 表达式 when value1 then 语句块1; when value2 then 语句块2; … else 语句块n; end case;

所有控制语句最后必须以 ; 结束。

循环语句

MySQL提供了三种循环语句，分别是while、repeat以及loop。除此以外，MySQL还提供了iterate语句以及leave语句用于循环的内部控制。

while语句

当条件表达式的值为true时，反复执行循环体，直到条件表达式的值为false。

[循环标签:]while 条件表达式 do 循环体; end while [循环标签]; end while后必须以 ;结束。

leave语句

leave语句用于跳出当前的循环语句（例如while语句）。

leave 循环标签;

iterate语句

iterate语句用于跳出本次循环，继而进行下次循环。iterate语句的语法格式如下。

iterate 循环标签;

repeat语句

当条件表达式的值为false时，反复执行循环，直到条件表达式的值为true。

[循环标签:]repeat 循环体; until 条件表达式 end repeat [循环标签];

loop语句

由于loop循环语句本身没有停止循环的语句，因此loop通常使用leave语句跳出loop循环。

[循环标签:] loop 循环体; if 条件表达式 then leave [循环标签]; end if; end loop;

所有控制语句最后必须以 ; 结束。

系统函数

MySQL功能强大的一个重要原因是MySQL内置了许多功能丰富的函数。 本章讲解的所有函数f(x)对数据x进行操作时，都会产生返回结果，并且数据x的值以及x的数据类型都不会发生丝毫变化。

数学函数

三角函数

MySQL提供了pi()函数计算圆周率；radians(x)函数负责将角度x转换为弧度；degrees(x)函数负责将弧度x转换为角度。 MySQL还提供了三角函数，正弦函数sin(x)、余弦函数cos(x)、tan(x)正切函数、余切函数cot(x)、反正弦函数asin(x)、反余弦函数acos(x)以及反正切函数atan(x)。

指数函数及对数函数

MySQL中常用的指数函数有sqrt()平方根函数、pow(x,y) 幂运算函数（计算x的y次方）以及exp(x)函数（计算e的x次方）。 pow(x,y) 幂运算函数还有一个别名函数：power(x,y)，实现相同的功能。 MySQL中常用的对数函数有log(x)函数（计算x的自然对数）以及log10(x)函数（计算以10为底的对数）。

求近似值函数

MySQL提供的round(x)函数负责计算离x最近的整数，round(x,y)函数负责计算离x最近的小数（小数点后保留y位）；truncate(x,y)函数负责返回小数点后保留y位的x（舍弃多余小数位，不进行四舍五入）； format(x,y)函数负责返回小数点后保留y位的x（进行四舍五入）；ceil(x)函数负责返回大于等于x的最小整数；floor(x)函数负责返回小于等于x的最大整数。

随机函数

MySQL提供了rand()函数负责返回随机数。

二进制、十六进制函数

bin(x)函数、oct(x)函数和hex(x)函数分别返回x的二进制、八进制和十六进制数；ascii(c)函数返回字符c的ASCII码（ASCII码介于0～255）；char (c1,c2,c3,…) 函数将c1、c2……的ASCII码转换为字符，然后返回这些字符组成的字符串；conv(x,code1,code2)函数将code1进制的x变为code2进制数。

条件控制函数

if()函数，if(condition,v1,v2)函数中condition为条件表达式，当condition的值为true时，函数返回v1的值，否则返回v2的值。

ifnull()函数，ifnull(v1,v2)函数中，如果v1的值为NULL，则该函数返回v2的值；如果v1的值不为NULL，则该函数返回v1的值。

case函数

case 表达式 when 值1 then 结果1 [ when 值2 then 结果2 ]… [ else 其他值 ] end

系统信息函数

关于MySQL服务实例的函数，version()函数用于获取当前MySQL服务实例使用的MySQL版本号，该函数的返回值与@@version静态变量的值相同。

关于MySQL服务器连接的函数，connection_id()函数用于获取当前MySQL服务器的连接ID，该函数的返回值与@@pseudo_thread_id系统变量的值相同；database()函数与schema()函数用于获取当前操作的数据库。

获取数据库用户信息的函数，user()函数用于获取通过哪一台登录主机、使用什么账户名成功连接MySQL服务器，system_user()函数与session_user()函数是user()函数的别名。current_user()函数用于获取该账户名允许通过哪些登录主机连接MySQL服务器。

日期和时间函数

获取MySQL服务器当前日期或时间 curdate()函数、current_date()函数，curtime()函数、current_time()函数用于用于获取MySQL服务器当前时间； now()函数、current_timestamp()函数、localtime()函数以及sysdate()函数用于获取MySQL服务器当前日期和时间，这四个函数允许传递一个整数值（小于等于6）作为函数参数，从而获取更为精确的时间信息。 curdate()函数、current_date()函数、curtime()函数、current_time()函数、now()函数、current_timestamp()函数、localtime()函数以及sysdate()函数的返回值与时区的设置有关。

获取MySQL服务器当前UNIX时间戳函数，unix_timestamp()函数用于获取MySQL服务器当前UNIX时间戳。unix_timestamp(datetime)函数将日期时间datetime以UNIX时间戳返回，而from_unixtime(timestamp)函数可以将UNIX时间戳以日期时间格式返回。需要注意的是，这些函数的返回值与时区的设置有关。

获取MySQL服务器当前UTC日期和时间函数，utc_date()函数用于获取UTC日期；utc_time()函数用于获取UTC时间。UTC即世界标准时间，中国大陆、中国香港、中国澳门、中国台湾、蒙古国、新加坡、马来西亚、菲律宾、西澳大利亚州的时间与UTC的时差均为 8，也就是UTC 8。这些函数的返回值与时区的设置无关。

获取年、月、日、时、分、秒、微秒等信息的函数，year(x)函数、month(x)函数、dayofmonth(x)函数、hour(x)函数、minute(x)函数、second(x)函数以及microsecond(x)函数分别用于获取日期时间x的年、月、日、时、分、秒、微秒等信息。 另外MySQL还提供了extract(type from x)函数用于获取日期时间x的年、月、日、时、分、秒、微秒等信息，其中type可以分别指定为year、month、day、hour、minute、second、microsecond。

获取月份、星期等信息的函数，monthname(x)函数用于获取日期时间x的月份信息。dayname(x)函数与weekday(x) 函数用于获取日期时间x的星期信息；dayofweek(x) 函数用于获取日期时间x是本星期的第几天（星期日为第一天，以此类推）。

获取年度信息的函数，quarter(x)函数用于获取日期时间x在本年是第几季度；week(x)函数与weekofyear(x)函数用于获取日期时间x在本年是第几个星期；dayofyear(x)函数用于获取日期时间x在本年是第几天。

时间和秒数之间的转换函数 time_to_sec(x)函数，用于获取时间x在当天的秒数；sec_to_time(x)函数用于获取当天的秒数x对应的时间。

日期间隔函数，to_days(x)函数用于计算日期x距离0000年1月1日的天数；from_days(x)函数用于计算从0000年1月1日开始n天后的日期； datediff(x1,x2)函数用于计算日期x1与x2之间的相隔天数；adddate(d,n)函数返回起始日期d加上n天的日期；subdate(d,n)函数返回起始日期d减去n天的日期。

时间间隔函数，addtime(t,n)函数返回起始时间t加上n秒的时间；subtime(t,n)函数返回起始时间t减去n秒的时间。

计算指定日期指定间隔的日期函数，date_add(date,interval 间隔 间隔类型)函数返回指定日期date指定间隔的日期。interval是时间间隔关键字，间隔可以为正数或者负数（建议使用两个单引号括起来）

间隔类型	说明	格式
microsecond	微秒	间隔微秒数
second	秒	间隔秒数
minute	分	钟 间隔分钟数
hour	小时	间隔小时数
day	天	间隔天数
week	星期	间隔星期数
month	月	间隔月数
quarter	季度	间隔季度数
year	年	间隔年数
second microsecond	秒和微秒	秒.微秒
minute_microsecond	分钟和微秒	分钟:秒.微秒
minute_second	分钟和秒	分钟:秒
hour_microsecond	小时和微秒	小时:分钟:秒.微秒
hour_second	小时和秒	小时:分钟:秒
hour minute	小时和分钟	小时:分钟
day_microsecond	日期和微秒	天 小时:分钟:秒.微秒
day_second	日期和秒	天 小时:分钟:秒
day minute	日期和分钟	天 小时:分钟
day_hour	日期和小时	天 小时
year month	年和月	年月(下划线)

时间格式化函数，time_format(t,f)函数按照表达式f的要求显示时间t，表达式f中定义了时间的显示格式，显示格式以%开头。

格式	说明
%H	小时(00······23)
%k	小时(0······23)
%h	小时(01······12)
%I	小时(01·····12)
%1	小时(1······12)
%i	分钟,数字(00······59)
%r	时间,12小时(hh:mm:ss[AP]M)
%T	时间,24小时(hh:mm:ss)
%S	秒(00······59)
%s	秒(00·····59)
%p	AM或PM

日期和时间格式化函数 date_format(d,f)函数按照表达式f的要求显示日期和时间t，表达式f中定义了日期和时间的显示格式，显示格式以%开头。

格式	说明
%W	星期名字(Sunday·...·Saturday)
%D	有英语前缀的月份的日期(1st,2nd,3rd,等等)
%Y	年,数字,4位
0 by	年,数字,2位
%a	缩写的星期名字(Sun····Sat)
%d	月份中的天数,数字(00······31)
%e	月份中的天数,数字(0······31)
%m	月,数字(01······12)
%c	月,数字(1······12)
%b	缩写的月份名字(Jan·····Dec)
%j	一年中的天数(001······366)
%w	一个星期中的天数(0=Sunday······6=Saturday)
%U	星期(0······52),这里星期天是星期的第一天
%u	星期(0······52),这里星期一是星期的第一天
%%	一个文字“%”

字符串函数

字符串基本信息函数，字符串基本信息函数包括获取字符串字符集的函数、获取字符串长度以及获取字符串占用字节数的函数等。

关于字符串字符集的函数，charset(x)函数返回x的字符集；collation(x)函数返回x的字符序。 关于字符串字符集的函数convert(x using charset)函数返回x的charset字符集数据（注意x的字符集没有变化）。

获取字符串长度以及获取字符串占用字节数函数，char_length(x)函数用于获取字符串x的长度；length(x)函数用于获取字符串x的占用的字节数。

加密函数，加密函数包括不可逆加密函数以及加密-解密函数。

不可逆加密函数，password(x)函数用于对x进行加密，默认返回41位的加密字符串；md5(x)函数用于对x进行加密，默认返回32位的加密字符串。

加密-解密函数encode(x,key)函数，使用密钥key对x进行加密，默认返回值是一个二进制数（二进制的位数由x的字节长度决定）；decode(password, key)函数使用密钥key对密码password进行解密。

加密-解密函数aes_encrypt(x,key)函数，使用密钥key对x进行加密，默认返回值是一个128位的二进制数；aes_decrypt(password, key)函数使用密钥key对密码password进行解密。

字符串连接函数，concat(x1,x2,….)函数用于将x1、x2等若干个字符串连接成一个新字符串；concat_ws(x,x1,x2,….)函数使用x将x1、x2等若干个字符串连接成一个新字符串。

字符串裁剪函数，ltrim(x)函数用于去掉字符串x开头的所有空格字符；rtrim(x)函数用于去掉字符串x结尾的所有空格字符；trim(x)函数用于去掉字符串x开头以及结尾的所有空格字符。trim([leading | both | trailing] x1 from x2)函数用于从x2字符串的前缀或者（以及）后缀中去掉字符串x1。 left(x,n)函数以及righ(x,n)函数也用于截取字符串。其中left(x,n)函数返回字符串x的前n个字符；right(x,n)函数返回字符串x的后n个字符。

字符串大小写转换函数，upper(x)函数以及ucase(x)函数将字符串x中的所有字母变成大写字母，字符串x并没有发生变化；lower(x)函数以及lcase(x)函数将字符串x中的所有字母变成小写字母，字符串x并没有发生变化。

填充字符串函数，lpad(x1,len,x2)函数将字符串x2填充到x1的开始处，使字符串x1的长度达到len；rpad(x1,len,x2)函数将字符串x2填充到x1的结尾处，使字符串x1的长度达到len。

取出指定位置的子字符串函数，substring(x,start,length)函数与mid(x,start,length)函数都是从字符串x的第n个位置开始获取length长度的字符串。

在字符串中查找指定子字符串的位置函数，locate(x1,x2)函数、position(x1 in x2)函数以及instr(x2,x1)函数都是用于从字符串x2中获取x1的开始位置。 find_in_set(x1,x2)函数也可以获取字符串x2中x1的开始位置（第几个逗号处的位置），不过该函数要求s2是一个用英文的逗号分隔的字符串。

子字符串替换函数，MySQL提供了两个子字符串替换函数insert(x1,start,length,x2)和replace(x1,x2,x3)。insert(x1,start,length,x2)函数将字符串x1中从start位置开始、长度为length的子字符串替换为x2。replace(x1,x2,x3)函数用字符串x3替换x1中所有出现的字符串x2，最后返回替换后的字符串。

字符串复制函数，字符串复制函数包括repeat(x,n)函数以及space(n)函数。其中repeat(x,n)函数产生一个新字符串，该字符串的内容是字符串x的n次复制；space(n)函数产生一个新字符串，该字符串的内容是空格字符的n次复制。

字符串比较函数，strcmp(x1,x2)函数用于比较两个字符串x1和x2，如果x1>x2函数返回值为1；如果x1=x2函数返回值为0；如果x1<x2函数返回值为-1。

字符串逆序函数，reverse(x)函数返回一个新字符串，该字符串为字符串x的逆序。 最为常用的数据类型转换函数是convert(x,type)与cast(x as type)函数，另外MySQL还提供了“十六进制字符串”转换为“十六进制数”的函数unhex(x)。

convert()函数，convert()函数有两种用法格式：convert(x using charset)函数返回x的charset字符集数据（刚刚讲过，这里不再赘述）。 convert()函数还有另外一种语法格式：convert(x,type)，可以实现数据类型的转换。convert(x,type)函数以type数据类型返回x数据（注意x的数据类型没有变化）。除此以外cast(x as type)函数也实现了convert(x,type)函数相同的功能。

unhex(x)函数，负责将十六进制字符串x转换为十六进制的数值。 条件控制函数的功能是根据条件表达式的值返回不同的值，MySQL中常用的条件控制函数有if()、ifnull()以及case函数。与先前讲解的if语句以及case语句不同，这些函数可以在MySQL客户机中直接调用，可以像max()统计函数一样直接融入到SQL语句中。

其他常用的MySQL函数

获得当前MySQL会话最后一次自增字段值

last_insert_id()函数返回当前MySQL会话最后一次insert或update语句设置的自增字段值。

last_insert_id()函数的返回结果遵循一定的原则。

• last_insert_id()函数仅仅用于获取当前MySQL会话时insert或update语句设置的自增字段值，该函数的返回值与系统会话变量@@last_insert_id的值一致。
• 自增字段值如果是数据库用户自己指定，而不是自动生成，那么last_insert_id()函数的返回值为0。 last_insert_id()函数的返回结果遵循一定的原则。
• 假如使用一条insert语句插入多行记录，last_insert_id()函数只返回第一条记录的自增字段值。
• last_insert_id()函数与表无关。如果向表A插入数据后再向表B插入数据，last_insert_id()函数返回表B的自增字段值。

IP地址与整数相互转换函数，inet_aton(ip)函数用于将IP地址（字符串数据）转换为整数；inet_ntoa(n)函数用于将整数转换为IP地址（字符串数据）。

基准值函数，benchmark(n,expression)函数将表达式expression重复执行n次，返回结果为0。

uuid()函数，uuid()函数可以生成一个128位的通用唯一识别码UUID（Universally Unique Identifier）。UUID码由5个段构成，其中前3个段与服务器主机的时间有关（精确到微秒）；第4段是一个随机数，在当前的MySQL服务实例中该随机数不会变化，除非重启MySQL服务；第5段是通过网卡MAC地址转换得到，同一台MySQL服务器运行多个MySQL服务实例时，该值相等。

,