mysql自定义函数 多表_mysql通过多表连接，查询多表字段，自定义函数、流程控制...

多表链接查询是从多个表中获取数据(两个表以上)最好不要超过三个表

table_reference [inner] join | {left|right} [outer] join teble_reference on condition_expr

透过查看图书信息表和图书类别表来获取图书编号、图书名称、图书类别

select book_id,book_name,category from book_info inner join book_category on book_info.book_category_id=book_category.category_id;

内链接

根据链接条件从多个表中查询选择数据，显示表中与链接条件相匹配的数据行，组合成新记录。

select col_list from table_name1 inner join on jion_condition1 inner join t3 on join_condition2]where where_conditions;条件是指定两个表都存在的列，inner可以省略

外连接

查询多个表中相关联的行

左外连接 left [outer] join,返回左表满足连接条件的所有记录

select book_id,book_name,category from book_category left outer join book_info on book_category.category_id=book_info.book_category_id;

右外连接right [outer] join

select col_list from t1 left|right [outer] join t2 on join_condition1;

自连接

如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。

查询的数据是来自一张表

select s.category_id as '图书类别编号',s.category as '图书类别名称',p.category as '图书的上级名称' from book_category s left join book_category p on s.parent_id=p.category_id;

多表更新

update talbe1 {inner]join|{left|right}[outer]join table2

on conditional_expr

set col1={expr1|default}

[,col2={expr2|default}]....

[where where_condition];

update reader_info t1 join book_info t2 on t1.card_id=t2.card_id

set actual_return_date = sysdate(),bookfee=datediff(sysdate(),return_date)*0.2,balance=balance-bookfee

where t1.book_id = 20151101 and t1.car_id ='2012011';

多表删除

delete table1[.*],table2[.*]

from table1{inner]join|{left|right}[outer]join table2 on conditional_expr

where where_condition

create table book_category_bak

as

select * from book_category;复制一个book_category表

delete t1 from book_category_bak t1

left join bookinfo_bak_t2

on t1.category_id=t2.book_category_id

where parent_id<>0 and book_id is null;

外连接、内连接、自连接难点；什么时候使用外连接，什么时候使用内连接

-------------------------------------------------------------

自定义函数

函数(存储函数)

1、需要有返回值  2、可以指定0~n个参数

create function function_name([func_parameter])

returns type

[charateristics...] routine_body

charateristics指定存储函数的特性

sqlsecurity{definer|invoker}:指明谁有权限来执行

definer：表示只有定义者才能执行

invoker表示拥有权限的调用者才可以执行，默认情况下，系统指定definer

comment'string':注释信息，可以用来描述存储函数

(routine body)函数体是由SQL代码构成；

函数体可以是简单的SQL语句，比如：简单的查询语句

函数体如果为复合结构需要使用begin...end语句

复合结构可以包含声明、流程控制。

create function ym_date(mydate date)  传参数

returns varchar(15)  返回值类型

return date_format(mydate,'%Y-%m');函数返回值

delimiter//自定义结束符(以//结束)

create function ym_date(mydate date)

returns varchar(15)  返回值类型

begin

return date_format(mydate,'%Y-%m');函数返回值

end//

create function newdate()

returns varchar(20)

return date_format(curdate,'%Y年%m月%d日');

创建有参函数

create function show_name_store(bid int)

returns varchar(50)

return (select concat_ws('----',book_name,store) from book_info where book_id =bid);

当book_id传入时，连接book_name---store

结果:java编程思想---5；

删除自定义函数

drop function [if exists] func_name;

drop function newdate;

流程控制

变量

可以在存储程序(存储过程和函数)中使用变量

在存储程序中变量的作用范围在begin..end之间。

定义变量

declare var_name [,varname]...date_type[default value];

declare num int default 10;

为变量赋值

定义变量之后，通过赋值可以改变变量的默认值。

两种赋值方式：

set var_name=expr[,var_name=expr]...

select col_name[,...] into var_name[,..] talbe_expr

set num=100;

select store into num from bookinfo;从表bookinfo中的列store 的值赋给num;

delimiter //

create function show_name_store(bid int)

returns varchar(50)

begin

declare result varchar(50); 设置变量

(select concat_ws('----',book_name,store) into result from book_info where book_id =bid);赋值变量

return result;

end //

delimiter

流程控制语句

if语句

if condition then

....

[elseif condition then]

....

....

[else]

....

end if;

delimiter //

create function show_level(cid char(18))

returns varchar(10)

begin

declare lev varchar(10);

declare money decimal(7,3);

select balance into money from readerinfo where card_id=cid;

if money>=500 then

set lev='金牌会员';

elseif money>=300 then

set lev='银牌会员';

elseif money>=200 then

set lev='普通会员';

else

set lev='非会员,余额不足';

end if;

return lev;

end //

delimiter;

case语句

case case_expr(表达式)[when_value 是表达式的值

when when_value then statement_list

when when_value then statement_list...

[else statement_list]

end case

delimiter //

create function show_level2(cid chr(18))

returns varchar(10)

begin

declare lev varchar(10);

declare money decimal(7,3);

declare num int;

select balance into money from readerinfo card_id=id;

set num=truncate(money/100,0);

case num

when 0 then set lev='非会员，余额不足';

when 1 then set lev='非会员，余额不足';

when 2 then set lev='普通会员';

when 3 then set lev='高级会员';

when 4 then set lev='高级会员';

else then set lev='金牌会员';

end case;

return lev;

end //

delimiter

case

when expr_condition then statement_list

when expr_condition then statement_list...

else statement_list

end case

循环语句

[while_label:]while condition do

...

end while[while_label]

求n以内的和

delimiter //

create function testfunc(n int)

returns int

begin

declcare num int dafault 0;

declcare sum int default 0;

while num

set num=num+1;

set sum=sum+num;

end while;

return sum;

end //

delimiter;

loop循环语句

该循环没有内置循环条件，但可以通过leave语句退出循环

[loop_label:]loop

statement_list

end loop[loop_label]

leave loop_label

delimiter //

create function testfunc(n int)

returns int

begin

declcare num int dafault 0;

declcare sum int default 0;

lab1:loop

set num=num+1;

set sum=sum+num;

if num>=n then

leave lab1;

end if;

end loop lab1;

return sum;

end //

delimiter;

repeat循环语句

[repeat_label:]repeat

.....

until expr_condition

end repeat[repeat_label]

delimiter //

create function testfunc(n int)

returns int

begin

declcare num int dafault 0;

declcare sum int default 0;

repeat

set num=num+1;

set sum=sum+num;

util num>=n

end repeat;

return sum;

end //

delimiter;