MySQL编程
1.语句结束符
默认有两个:; 和\g(只能在命令行中使用)
可以使用delimiter命令来修改语句结束符,例如:delimiter $$(注意,一般手工修改结束符后再改回默认值;)
2.注释
a) 行注释: # 和--[空格]
b) 块注释: /* */
3.变量
a) 系统变量:show variables [like pattern]; 查看
b) 用户自定义变量:
i.set 变量名=变量值;注意:为了区分用户自定义变量和系统变量,需要在用户自定义变量名称前加@符号。例如set @name=’John’;使用select @变量名;来获取自定义变量。
Set赋值用法的变量值也可是是标量查询的结果,例如:
Set @total=(select count(*) from student);
ii.也可以使用select 字段from表名into @变量名;例如:selectname from studentinto@stuName;
iii.Select @who=’小李’; //关系判断,注意不是赋值操作,判断变量 @who中德值是不是等于“小李”,是的话返回1,否的话返回0.
定义变量的第三种方法:使用 select @变量名:=变量值的方式
例如 select @who:=’小李’; //给变量赋值,并将其检索出来
c) 变量的有效期
会话结束后,变量就失效(即断开连接后,变量失效)
d) 变量的作用域
用户定义的变量是全局的。但在函数内定义的变量则是局部的。
4.数据类型
变量的数据类型与字段的数据类型一致!
5.运算符
同一般编程语言中的运算符基本一致。算数运算符、逻辑运算符。
6.函数
1.内置函数
a)数值处理函数
abs(x):返回绝对值
ceil(x):返回大于x的最小整数
floor(x):返回小于x的最大整数
mod(x,y):返回x与y的模
rand():返回0-1之间的随机数select round(rand()*100);
round(x,y):返回参数x的y位小数的四舍五入结果
truncate(x,y):返回数字x截断为y位小数的结果字符串处理函数
B)字符串处理函数
1.concat(s1,s2....sn):把传入的参数连接成一个字符串
2.insert(str,x,y,insert):从str的x位置开始,替换y长度的字符串为insert
select insert('abcdefg',2,3,'hello');
3.lower(str),upper(str):将字符串转换为大写,小写
4.left(str,x) right(str,x) 返回str左边(右边)x个字符,x为null则返回null
5.lpad(str,n,pad) rpad(str,n,pad) 用pad对字符串str从最左边(右边)进行填充,直到总长度达到n
select name,lpad(name,10,'#'),rpad(name,10,'(') from cats;
6.trim(),ltrim(),rtrim()去掉两边,左边,右边空格
select concat('1',trim(' abc '),'2'),concat('1',ltrim(' abc '),'2'),concat('1',rtrim(' abc '),'2')\G;
7.replace(str,a,b)在字符串str中用字符串b替换所有的字符串a
8.strcmp(s1,s2):如果S1比S2小,返回-1;如果S1比S2大则返回1;如果相等则返回0(比较的是ASC2码)
9.substring(str,x,y)返回字符串str中从位置x起,长度为y的子字符串
C)日期时间
curdate()
curtime()
now(); select curdate();
select unix_timestamp(now());
select from_unixtime(1331110656);
select week(now()),year(now());
select hour(curtime()),minute(curtime());
select monthname(now());
select date_format(now(),"%Y-%m-%d %H:%i:%s");
D)流程控制函数
create table salary(id int,salary decimal(9,2));
if(value,true,false); select id,salary,if(salary>300,'high','low') from salary;
ifnull(t,false) select id,salary,ifnull(salary,0) from salary;
case when[value1] then[result1]...else[default]end;
Select
case when salary<=300 then 'low' else 'high' end
from salary;
E)其他
database() select database(); //返回当前使用的数据库
version() //返回当前数据库的版本
user() //返回当前连接上数据库的用户
inet_aton(ip) 将字符串地址转换为网络地址
password() 对mysql用户加密
md5()对用户密码加密
select * from mysql.user \G;
2.用户自定义函数(注意:函数是绑定数据库的,在某个数据库中建立的函数只能在该数据库中使用,数据库被删除,函数也相应被删除)
语法:
create function 函数名(参数列表)returns 返回值类型
函数体
例子:
drop function if exists sayHello;
delimiter $$//先将结束符改为$$,因为函数体中有分号,如果不将结束符修改一下,mysql会认为中途某个语句就结束,会报错
create function sayHello()returnsvarchar(20)
begin
return 'hello world!';
end
$$//函数也是语句,也需要结束符来结束
delimiter ;//将结束符改为默认!!!
使用 select sayHello();执行
7.流程控制
a)分支语句
if条件1then
语句1
elseif条件2then
语句2
else
上述都不满足时执行的语句
end if;
例子:
drop function if exists func1;
delimiter $$
create function func1() returns varchar(10)
begin
if hour(now())
return 'morning';
else
return 'afternoon';
end if;
end
$$
delimiter ;
b) 循环语句
while 条件do
循环体
end while;
循环的提前终止:
leave:终止整个循环(相当于c语言中的break)
iterate:终止本次循环,进入下一次循环(作用相当于c语言中的continue)
注意:使用leave和iterate时,需要给循环加一个标签,利用标签来终止(相当于c语言中的goto语句需要标号)
标签:while
end while 标签;
例子1:
drop function if exists func2;
delimiter $$
create function func2() returns int
begin
set @i = 1;
set @sum = 0;
while @i <= 10 do
set @sum = @sum + @i;
set @i = @i + 1;
end while;
return @sum;
end
$$
delimiter ;
例子2:
drop function if exists func;
delimiter $$
create function func() returns int
begin
set @i = 1;
set @sum = 0;
w:while @i <= 10 do
if @i = 5 then
leavew;
end if;
set @sum = @sum + @i;
set @i = @i + 1;
end while w;
return @sum;
end
$$
delimiter ;
例子3:
drop function if exists func;
delimiter $$
create function func() returns int
begin
set @i = 1;
set @sum = 0;
w:while @i <= 10 do
if @i = 5 then
set @i = @i + 1;
iterate w;
end if;
set @sum = @sum + @i;
set @i = @i + 1;
end while w;
return @sum;
end
$$
delimiter ;
说明:即使在函数内部声明的变量,如果使用@变量 形式,也是全局变量,在函数外部也可以访问。要想使用局部变量,有以下两种方法:
1.函数参数:格式: 参数名 类型
2.函数声明局部变量:使用declare声明局部变量
declare i int default 0; (局部变量没有@符号)
例1(使用参数):
drop function if exists sayHello2;
delimiter $$
create function sayHello2(user_name varchar(10)) returns varchar(20)
begin
return concat('hello ',user_name);
end
$$
delimiter ;
还可以像下面一样使用函数:
select id,sayHello2(userName) from student;
例2(使用declare声明局部变量):
drop function if exists mySum;
delimiter $$
create function mySum() returns int
begin
declare i int default 1;
declare sum int default 0;
while i<=100 do
set sum=sum+i;
set i=i+1;
end while;
return sum;
end
$$
delimiter ;
应用:
1.存储过程
2.存储函数
3.触发器