SQL编程
变量
局部变量 declare
全局变量 @a
set @a = 10; 直接赋值
set @a := 'zhoudian';
select @c := 'abc'; 打印并赋值
select @@version;
@@开头是MySQL已经定义好的一些变量。
-
drop procedure if exists sp1;
-
delimiter // -- 修改语句界定符，这样就允许在程序体中使用;
-
create procedure sp1()
-
begin
-
-- 在declare之前不能有非declare语句
-
declare a int; -- declare声明局部变量
-
declare b, c , d float default 3.3;
-
set a = 10; -- 没有赋值时默认为null
-
set b =1.23, c=8.9;
-
select a, b, c, @b;
-
end//
-
delimiter ;
----------------------------------------------
show procedure status\G;
show procedure status like 'sp1' \G;
---------------------------------------------
-
drop procedure if exists sp1;
-
delimiter //
-
create procedure sp1()
-
begin
-
declare a int;
-
declare b dec(5,2) default 10;
-
declare c varchar(32) charset utf8;
-
set a = 10;
-
set b = 314.12;
-
set c = '中国';
-
select a, b, c;
-
end //
-
delimiter ;
-----------------------------------------------
存储过程(数据库中的)，执行速度快
MySQL中的存储过程没有返回值，但是可以通过out参数将值传出
-
drop procedure if exists sp1;
-
delimiter //
-
create procedure sp1(a date , b varchar(20))
-
begin
-
select a, b;
-
end //
-
delimiter ;
-
drop procedure if exists sp1;
-
delimiter //
-
create procedure sp1(sid int, sname varchar(32))
-
begin
-
create table if not exists stu
-
(
-
stuID int,
-
stuName varchar(32)
-
)engine=innodb charset=utf8;
-
insert into stu values(sid,sname);
-
end //
-
delimiter ;
-
-- 使用out传出值,,,,,inout传入传出
-
drop procedure if exists sp1;
-
delimiter //
-
create procedure sp1(out c int)
-
begin
-
select count(1) into c from stu;
-
end //
-
delimiter ;
-
-
call sp1(@c);
-----------------------------------------------------------------
select * from stu;
select found_rows(); -- 上一条语句一共查出了多少条记录
---------------------------------------------------------------------------------
使用php连接数据库
-
create table dept
-
(
-
deptID int,
-
deptName varchar(32)
-
)engine=innodb charset=utf8;
-
-
drop procedure if exists sp1;
-
delimiter //
-
create procedure sp1(a int, b varchar(32))
-
begin
-
insert into dept values(a, b);
-
end //
-
delimiter ;
html页面
-
<html>
-
<body>
-
<form name="frm" action="my.php" method="post">
-
deptID:<input type="text" name="deptID" /><br />
-
deptName:<input type="text" name="deptName" /><br />
-
<input type="submit" value="submit">
-
</form>
-
</body>
-
</html>
php页面
-
<?php
-
$deptID = $_POST['deptID'];
-
$deptName = $_POST['deptName'];
-
-
mysql_connect('localhost:/var/run/mysqld/mysql57.sock','root','');
-
mysql_select_db('ds');
-
-
mysql_query('set names utf8');
-
-
#$sql = "insert into dept values($deptID,'$deptName')";
-
#mysql_query($sql);
-
mysql_query("call sp1($deptID,'$deptName')");
-
?>
阅读(2404) | 评论(2) | 转发(0) |
学习了