MySQL-10-mysql编程

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 @变量名；  例如：select name from student into @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() returns varchar(20)

begin

return 'hello world!';

end

$$    //函数也是语句，也需要结束符来结束

delimiter ;   //将结束符改为默认！！！

使用 select sayHello();  执行

 

7.流程控制

a) 分支语句

if 条件1 then

语句1

elseif 条件2 then

语句2

      else

上述都不满足时执行的语句

end if;

例子：

drop function if exists func1;

delimiter $$

create function func1() returns varchar(10)

begin

if hour(now()) < 12 then

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 

 leave w;

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.触发器