MySQL编程基础知识
常量
按照MySQL的数据类型进行划分,可将常量划分为字符串常量、数值常量、十六进制常量、日期时间常量、二进制常量以及NULL。
字符串常量
- 用单引号或双引号括起来的字符序列
数值常量
- 分为整数常量和小数常量
日期时间常量
- 符合特殊格式的字符串(时间常量:‘14:30:24’;日期时间常量:‘2018-05-12 14:28:24’)
布尔值
- true、false
- select语句显示时转换为字符串’1’和’0’显示
二进制常量
- 表示方法:b+二进制字符串(0、1),例如
b'1101'
- select语句显示时会转换为字符串再显示
十六进制常量
- 表示方法1:x+十六进制字符串(0~9、a ~f)(不区分大小写),例如
x'4D7953514C'
; - 表示方法2:0x+十六进制数(不用引号),例如
0x4D7953514C
- select语句显示时会转换为字符串再显示
hex()
:将一个字符串或数字转换为十六进制格式的字符串,例如:heax('MySQL')
→ \rightarrow →'4D7953514C'
NULL
- 表示“值不确定”、“没有值”
- NULL值参与算术运算、比较运算、逻辑运算时,结果仍为NULL
用户自定义变量
用户会话变量
用户会话变量与当前会话密切相关:
- MySQL客户机1定义了会话变量,会话期间,该会话变量一直有效;
- MySQL客户机2不能访问MySQL客户机1定义的会话变量;
- MySQL客户机1关闭或者MySQL客户机与服务器断开连接后,MySQL客户机1定义的所有变量将被释放,以节省MySQL服务器内存。
系统会话变量与用户会话变量
- 相同:变量名大小写不敏感;
- 区别:用户会话变量以一个@开头,系统会话变量以两个@开头;系统会话变量无需定义即可使用。
用户会话变量的定义赋值
方法一:使用set变量定义用户会话变量,并为其赋值
set @user_variable1=expression1 [,@user_variable2=expression2,...]
- user_variable1、user_variable2为用户会话变量名,expression1、expression2可以是常量、变量或表达式。
- set命令可同时定义多个变量,中间用逗号隔开。
- 用户会话变量的数据类型根据赋值运算符=右边的表达式的计算结果自动分配,重新赋予变量不同类型的值时,变量的数据类型也会跟着改变。
方法二:使用select语句定义用户会话变量并为其赋值
# 第一种语法格式:产生结果集
select @user_variable1:=expression1 [,user_variable2:=expression2,...]
# 第二种语法格式:不产生结果集
select expression1 into @user_variable1,expression2 into @user_variable2,...
用户会话变量与SQL语句
如果select语句的结果集是单个值,可以将select语句的返回结果赋予用户回话变量。
- 方法一:
set @student_count=(select count(*) from student);
- 方法二:产生结果集
select @student_count:=(select count(*) from student);
- 方法三:产生结果集
select @student_count:=count(*) from student;
- 方法四:
select count(*) into @student_count from student;
- 方法五:
select count(*) from student into @student_count;
- 用户会话变量可以直接嵌入到select、insert、update以及delete语句的条件表达式中。
set @student_no='2012001';
select * from student where student_no=@student_no;
局部变量
declare variable_name variable_type
- declare专门用于定义局部变量及对应的数据类型;
- 局部变量必须定义在存储程序(函数、触发器、存储过程、事务等)中,且局部变量的作用范围仅限于存储程序中。
局部变量主要用于以下3种场合:
- 局部变量定义在存储程序的begin-end语句块之间。此时,局部变量必须首先使用declare命令定义,并且必须指定其数据类型。只有定义局部变量后,才可以使用set命令或select命令为其赋值。
- 局部变量作为存储过程或者函数的参数使用。此时不需要使用declare命令定义,但需要指定参数的数据类型。
- 局部变量可以用在存储程序的SQL语句中。数据检索时,如果select语句的结果集是单个值,则可以将select语句的返回结果赋予局部变量。局部变量也可以直接嵌入到select、insert、update及delete语句的条件表达式中。
用户会话变量与局部变量区别
- 用户会话变量名以@开头,局部变量名前没有@符号。
- 局部变量使用declare命令定义(存储过程参数、函数参数除外),定义时必须指定局部变量的数据类型,局部变量定义后,才可以使用set命令或select语句为其赋值。用户会话变量使用set命令或select语句定义并进行赋值,定义用户回话变量时无需指定数据类型(用户会话变量是弱类型)。
- 用户会话变量的作用范围与生存周期大于局部变量。局部变量如果作为存储过程或者函数的参数使用,则在整个存储过程或者函数内中有效;如果定义在存储程序的begin-end语句块中,则仅在当前的begin-end语句块内有效。用户会话变量再本次会话期间一直有效,直至关闭服务器。
- 如果局部变量嵌入到SQL语句中,由于局部变量前没有@符号,这就要求局部变量名不能与表字段名同名。
运算符与表达式
- 运算符时数据操作的符号;
- 表达式指的是将操作数(如变量、常量、函数等)用运算符按一定的规则连接起来的有意义的式子。
算术运算符
加 | 减 | 乘 | 除 | 求余 | 求商 |
---|---|---|---|---|---|
+ | - | * | / | % | div |
- 当算术运算符的操作数为NULL时,表达式的结果为NULL。
- MySQL日期(或时间)类型的数据本身是一个数值类型,因此可以进行简单的算术运算。
select '2013-01-31' + interval '22' day,'2012-01-31' - interval '22' day;
比较运算符/关系运算符
比较运算符用于比较操作数之间的大小关系,其运算结果要么为true,要么为false,要么为NULL。
使用select语句显示布尔值true或false,会将其自动转换为字符串‘1’或‘0’再进行显示。
等于 | 大于 | 小于 | 大于等于 | 小于等于 | 不等于 | 相等或都等于空 | 是否为NULL | 是否在区间内 | 是否在集合内 | 模式匹配 | 正则模式匹配 |
---|---|---|---|---|---|---|---|---|---|---|---|
= | > | < | >= | <= | <>或!= | <=> | is null | between…and… | in | like | regexp |
- 字符串进行比较时,会截掉字符串尾部的空格字符,然后进行比较。
- null=null:null;null<=>null:1
逻辑运算符/布尔运算符
逻辑运算符对布尔值进行操作,其运算结果要么为true,要么为false,要么为NULL。
逻辑非 | 逻辑与 | 逻辑或 | 逻辑异或 |
---|---|---|---|
not或! | and或&& | or或|| | xor |
- 严格意义上讲,between…and…运算、in运算、like以及regexp运算既是比较运算,又是逻辑运算。
位运算符
位运算符对二进制数据进行操作(如果不是二进制类型的数,将进行类型自动转换),其运算结果为二进制数。使用select语句显示二进制数时,会将其自动转换为十进制数显示。
按位与 | 按位或 | 按位异或 | 按位取反 | 位右移 | 位左移 |
---|---|---|---|---|---|
& | | | ^ | ~ | >> | << |
begin-end语句块
有些时候,为了完成某个功能,多余MySQL表达式密不可分,可以使用‘begin’和‘end;’将这些表达式包含起来形成语句块,语句块中表达式之间使用“;”隔开。
[开始标签:]begin
[局部]变量的声明;
错误触发条件的声明;
游标的声明;
错误处理程序的声明;
业务逻辑代码;
end[结束标签];
- 开始标签与结束标签名称必须相同。
- 一个begin-end语句块可以包含新的begin-end语句块。
- 允许在一个begin-end语句块内使用leave语句跳出该语句块。
- 在MySQL中,单独使用begin-end语句块没有任何意义,只有将begin-end语句块封装到存储过程、函数、触发器以及事件等存储程序内部才有意义。
重置命令结束标记
将当前MySQL客户机的命令结束标记临时的设为$$
delimiter $$
自定义函数
函数的创建与调用
创建自定义函数的语法格式:
create function 函数名(参数1,参数2,...) returns 返回值的数据类型
[函数选项]
begin
函数体;
return语句;
end;
函数选项是由以下一种或几种选项组合而成的:
language sql
| [not] deterministic
| { contains sql | no sql | reads sql data | modifies sql data }
| sql security { definer | invoker }
| comment '注释'
language sql
:默认选项,用于说明函数体使用SQL语言编写。deterministic
(确定性):当函数返回不确定值时,该选项是为了防止“复制”时的不一致性。如果函数总是对同样的输入参数产生同样的结果,则它被认为是“确定的”,否则就是“不确定的”。例如,函数返回系统当前时间,返回值是不确定的,如果既没有给定deterministic,也没有给定not deterministic,默认的就是not deterministic。contains sql
:表示函数体中不包含读或写数据的语句(例如set命令等);no sql
:表示函数体中不包含SQL语句。reads sql data
:表示函数体中包含select查询语句,但不包含更新语句。modifies sql data
:表示函数体中包含更新语句。如果上述选项没有明确指定,默认是contains sql。sql security
:用于指定函数的执行许可。definer
:表示该函数只能由创建者调用。invoker
:表示该函数可以被其他数据库用户嗲用,默认值是definer。comment
:为函数添加功能说明等注释信息。
- 自定义函数是数据库的对象,因此,创建自定义函数时,需要指定该自定义函数隶属于哪个数据库。
- 同一个数据库内,自定义函数名不能与已有的函数名(包括系统函数名)重名,建议在自定义函数名中统一添加前缀“fn_”或后缀“_fn”。
- 函数的参数无需使用declare命令定义,但它仍然是局部变量,且必须提供参数的数据类型。自定义函数如果没有参数,则使用空参数“()”即可。
- 函数必须指定返回值数据类型,且须与return语句中的返回值的数据类型相近(长度可以不同)。
- 自定义函数使用select语句时,该select语句不能产生结果集。
示例:创建函数row_no_fn(),为查询结果集添加行号
delimiter $$
create function row_no_fn() returns int
no sql
begin
set @row_no=@row_no+1;
return @row_no;
end;
$$
delimiter ;
调用函数,在查询结果集中加入行号
set @row_no=0;
select row_no_fn() 行号,student_no,student_name from student;
示例:根据学生的学号查询学生的姓名
delimiter $$
create function get_name_fn(student_no1 int) returns char(20)
reads sql data
begin
declare student_name1 char(20);
select student_name into student_name1 from student where student_no=student_no1;
return student_name1;
end;
$$
delimiter ;
函数的维护
查看函数的定义
- 查看当前数据库中所有自定义函数的信息:
show function status;
- 查看当前数据库中自定义函数的信息(模糊查询):
show function status like 模式;
- 查看指定数据库中的所有自定义函数名:
show name from mysql.proc where db='choose' and type='function';
- 查看指定函数名的详细信息:
show create function 函数名;
- 函数的信息都保存在information_schema数据库中的routines表中,可以使用select语句检索routines表,查询函数的相关信息:
select * from information_schema.routines where routine_name='get_name_fn'\G
。
函数定义删除
- 删除自定义函数:
drop function 函数名
函数定义的修改
由于函数保存的仅仅是函数体,而函数体实际上是一些MySQL表达式,因此函数自身不保存任何用户数据。当函数的函数体需要更改时,可以使用drop function语句暂时将函数的定义删除,然后使用create function语句重新创建相同名字的函数即可。这种方法对于存储过程、视图、触发器的修改同样适用。
条件控制语句
if 语句
if 条件表达式1 then 语句块1;
[elseif 条件表达式2 then 语句块2]...
[else 语句块n]
end if;
case语句
case 表达式
when values1 then 语句块1;
when values2 then 语句块2;
when values3 then 语句块3;
...
else 语句块n;
end cases;
循环语句
- 当循环次数确定时,通常使用while循环语句
- 当循环次数不确定时,通常使用repeat语句或loop语句
while语句
[循环标签:]while 条件表达式 do
循环体;
end while [循环标签];
- 当条件表达式的值为true时,反复执行循环体,直到条件表达式的值为false。
repeat语句
[循环标签:]repeat
循环体;
until 条件表达式
end repeat [循环标签];
- 当条件表达式的值为false时,反复执行循环体,直到条件表达式的值为true。
loop语句
[循环标签:]loop
循环体;
if 条件表达式 then
leave [循环标签];
end if;
end loop;
- loop循环语句本身没有停止循环的语句,因此loop通常借助leave语句跳出loop循环。
跳出循环
leave语句
leave 循环标签;
- 跳出当前的循环语句。
iterate语句
iterate 循环标签;
- 跳出本次循环。
系统函数
数学函数
三角函数 | 计算圆周率 | pi() |
---|---|---|
将角度x转换为弧度 | radians() | |
将弧度x转换为角度 | degree() | |
正弦、余弦、正切、余切 | sin(x)、cos(x)、tan(x)、cot(x) | |
反正弦、反余弦、反正切 | asin(x)、acos(x)、atan(x) | |
指数函数及对数函数 | 平方根函数 | sqrt(x) |
幂运算函数(x的y次方) | pw(x,y)、power(x,y) | |
指数函数 | exp(x) | |
对数函数 | log(x)、log10(x)、log2(x) | |
求近似值函数 | 计算离x最近的整数 | round(x) |
计算离x最近的小数,小数点后保留y位 | round(x,y) | |
返回小数点后保留y位的x(舍弃多余小数位,不进行四舍五入) | truncate(x,y) | |
返回小数点后保留y位的x(进行四舍五入) | format(x,y) | |
返回大于等于x的最小整数 | ceil(x) | |
返回小于等于x的最大整数 | floor(x) | |
随机函数 | 返回随机数 | rand(x) |
进制函数 | 返回x的二进制、八进制、十六进制数 | bin(x)、oct(x)、hex(x) |
返回字符c的ASCII码(ASCII码介于0~255) | ascii(x) | |
将c1、c2...的ASCII码转换为字符,然后返回这些字符组成的字符串 | char(c1,c2,c3,...) | |
将code1进制的x变为code2进制数 | conv(x,code1,code2) |
- 示例:使用rand()随机函数对结果集随机排序
select * from student order by rand();
- bin()、oct()函数的参数须为整数;hex()函数的参数可以是数值型的数,也可以是字符串;ascii()函数的参数是一个字符。使用select语句显示这些函数的返回值时,返回值将自动转换为十进制数显示。
字符串函数
字符串函数在对字符串进行操作时,字符集、字符序的设置至关重要。同一个字符串函数对同一个字符串进行操作时,如果字符集或字符序设置不同,那么操作结果也可能不同。
字符串基本信息函数 | 返回x的字符集 | charset(x) |
---|---|---|
返回x的字符序 | collation(x) | |
返回x的charset字符集数据(x的字符集没变) | convert(x using charset) | |
获取字符串x的长度 | char_length(x) | |
获取字符串x占用的字节数 | length(x) | |
加密函数 | 对x进行加密,默认返回41位的加密字符串(不可逆) | password(x) |
对x进行加密,默认返回32位的加密字符串(不可逆) | md5(x) | |
使用密钥key对x进行加密,默认返回一个二进制数(二进制的位数由x的字节长度决定);使用密钥key对密码password解密 | encode(x,key)、decode(password,key) | |
使用密钥key对x进行加密,默认返回一个128位的二进制数;使用密钥key对密码password解密 | aes_encrypt(x,key)、aes_decrypt(password,key) | |
字符串连接函数 | 将x1、x2等若干字符串连接成一个新字符串 | concat(x1,x2,...) |
使用x将x1、x2等若干个字符串连接成一个新字符串 | concat_ws(x,x1,x2,...) | |
MySQL服务实例在ansi模式下,||表示管道符号,可以连接字符串;MySQL服务实例在strict模式下,||表示逻辑或。修改服务实例模式:set_sql_mode='ansi'; | ||
字符串裁剪函数 | 去掉字符串x开头的所有空格字符 | ltrim(x) |
去掉字符串x结尾的所有空格字符 | rtrim(x) | |
去掉字符串x开头及结尾的所有空格字符 | trim(x) | |
从x2字符串的前缀或者(以及)后缀中去掉字符串x1 | trim([leading|both|trailing] x1 from x2) | |
返回字符串x的前n个字符 | left(x,n) | |
返回字符串x的后n个字符 | right(x,n) | |
字符串大小写转换函数 | 将字符串x中的所有字母变成大写字母,字符串x不变 | upper(x)、ucase(x) |
将字符串x中的所有字母变成小写字母,字符串x不变 | lower(x)、lacses(x) | |
填充字符串函数 | 将字符串x2填充到x1的开始处,使字符串x1的长度达到len | lpad(x1,len,x2) |
将字符串x2填充到x1的结尾处,使字符串x1的长度达到len | rpad(x1,len,x2) | |
子字符串操作函数 | 从字符串x的第start个位置开始获取len长度的字符串 | substring(x,start,length)、mid(x,start,length) |
从字符串x2中获取字符串x1的开始位置 | locate(x1,x2)、position(x1 in x2)、instr(x2,x1) | |
从字符串x2中获取字符串x1的开始位置(第几个逗号处的位置)要求x2是英文逗号分隔的字符串 | find_in_set(x1,x2) | |
将字符串x1从start位置开始length长度的子字符串替换为x2 | insert(x1,start,length,x2) | |
用字符串x3替换x1中出现的所有字符串x2 | Replace(x1,x2,x3) | |
截取字符串x中出现count次delimiter分隔符的子字符串(count>0左边截取,count<0右边截取) | substring_index(x,delimiter,count) | |
字符串复制函数 | 字符串x复制n次产生一个新字符串 | repeat(x,n) |
空格字符串复制n次 | space(n) | |
字符串比较函数 | x1>x2返回1,x1=x2返回0,x1小于x2返回-1 | strcmp(x1,x2) |
字符串逆序函数 | 返回字符串x的逆序 | reverse(x) |
数据类型转换函数
数据类型转换函数 | 以type数据类型返回x数据(x的数据类型没变) | convert(x,type)、cast(x as type) |
---|---|---|
将十六进制字符串x转换为十六进制的数值 | unhex(x) |
条件控制函数
当condition的值为true时,返回v1的值,否则返回v2的值 | if(condition,v1,v2) |
---|---|
如果v1的值为null函数返回v2的值,如果v1的值不是null函数返回v1的值 | ifnull(v1,v2) |
符合哪个值返回哪个结果 | case 表达式 when 值1 then 结果1 [when 值2 then 结果2]...[else 其他值] end |
系统信息函数
MySQL服务实例 | 当前MySQL服务实例使用的MySQL版本号(与静态变量@@version的值相同) | version() |
---|---|---|
MySQL服务器连接 | 获取当前MySQL服务器的连接ID(与系统变量@@pseudo_thread_id值相同) | connection_id() |
获取当前操作数据库 | database()、schema() | |
获取数据库用户信息 | 获取通过一台登录主机,使用说明账户名成功连接MySQL服务器 | user()、system_user()、session_user() |
获取该账户名允许通过哪些登录主机连接MySQL服务器 | current_user() |
日期时间函数
- UNIX时间戳是从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数。
- UTC即世界标准时间。
获取MySQL服务器当前日期或时间 | 获取MySQL服务器当前日期 | curdate()、current_date() |
---|---|---|
获取MySQL服务器当前时间 | curtime()、current_time() | |
获取MySQL服务器当前日期和时间 | now()、current_timestamp()、localtime()、sysdate() | |
获取MySQL服务器当前UNIX时间戳 | unix_timestamp() | |
将日期时间datetime以UNIX时间戳格式返回 | unix_timestamp(datetime) | |
将UNIX时间戳以日期时间格式返回 | from_unixtime(timestamp) | |
获取UTC日期 | utc_date() | |
获取UTC时间 | utc_time() | |
获取日期或时间某一具体信息 | 获取日期或时间的年月日时分秒微秒 | year(x)、month(x)、dayofmonth(x)、hour(x)、minute(x)、second(x)、microsecond(x) |
extract(type from x) | ||
获取日期时间x的月份信息(如May) | monthname(x) | |
获取日期时间x的星期信息 | dayname(x) (返回值如Monday) | |
weekday(x)(返回整数,Monday对应0) | ||
获取日期时间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距离0000年1月1日的天数 | to_days(x) |
计算从0000年1月1日开始n天后的日期 | from_days(n) | |
计算日期x1与x2之间的间隔天数 | datediff(x1,x2) | |
返回起始日期d加上n天的日期 | adddate(d,n) | |
返回起始日期d减去n天的日期 | subdate(d,n) | |
时间间隔函数 | 从起始时间t加上n秒的时间 | addtime(t,n) |
从起始时间t减去n秒的时间 | subtime(t,n) | |
计算指定日期指定间隔的日期函数 | 返回指定日期date指定间隔的日期 | date_add(date,interval 间隔 间隔类型) |
日期和时间格式化函数 | 按照表达式f的要求显示时间t | time_format(t,f) |
按照表达式f的要求显示日期和时间d | date_format(d,f) |
说明:
- date_add(date,interval 间隔 间隔类型):
- interval是时间间隔关键字
- 间隔 可以为正数或者负数(建议使用两个单引号括起来)
- 时间、日期间隔类型
- time_format(t,f)
- 表达式f中定义了时间显示格式,显示格式以%开头
- date_format(d,f)
- 表达式f定义了日期和时间的显示格式,显示格式以%开头
- 日期和时间常用的格式
题目实例
换座位
对于所有座位 id 是奇数的学生,修改其 id 为 id+1,如果最后一个座位 id 也是奇数,则最后一个座位 id 不修改。对于所有座位 id 是偶数的学生,修改其 id 为 id-1。
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND counts = id THEN id
ELSE id - 1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;
第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e.salary
FROM
employee e
WHERE
(SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e1.salary
FROM
employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e1.salary
FROM
employee e1, employee e2
WHERE
e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
FROM
employee, (SELECT @r:=0, @p:=NULL)init
ORDER BY
salary DESC) tmp
WHERE rnk = N
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
END
《MySQL数据库基础与实例教程》——孔祥盛