server中局部变量与全局变量的,两表之间数据备份复制的sql语句

在使用PHP编程的时候,我有一个习惯,不太喜欢使用现成的库文件,例如PHPLib或者其它类似的库,在这个系统中,我也打算自己写一个库文件,它需要处理认证、确认email,更新帐号等事情。

局部变量用一个@标识,全局变量用两个@;

SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。

?php function user_change_email ($password1,$new_email,$user_name) {
global $feedback,$hidden_hash_var; if (validate_email($new_email)) {
$hash=md5($new_email.$hidden_hash_var);
file://改变数据库中确认用的无序码值,但不改变email
file://发出一个带有新认证码的确认email
$user_name=strtolower($user_name); $password1=strtolower($password1);
$sql=UPDATE user SET confirm_hash=”$hash” WHERE
user_name=”$user_name” AND password=”. md5($password1) .”;
$result=db_query($sql); if (!$result || db_affected_rows($result) 1)
{ $feedback .= ” ERROR – Incorrect User Name Or Password ”; return
false; } else { $feedback .= ” Confirmation Sent ”;
user_send_confirm_email($new_email,$hash); return true; } } else {
$feedback .= ” New Email Address Appears Invalid ”; return false; } }
function user_confirm($hash,$email) { /*
用户点击认证email的相关连接时,连到一个确认的页面,该页面会调用这个函数,
*/ global $feedback,$hidden_hash_var; file://verify that they didn”t
tamper with the email address $new_hash=md5($email.$hidden_hash_var);
if ($new_hash ($new_hash==$hash)) { file://在数据库中找出这个记录
$sql=SELECT * FROM user WHERE confirm_hash=”$hash”;
$result=db_query($sql); if (!$result || db_numrows($result) 1) {
$feedback .= ” ERROR – Hash Not Found ”; return false; } else {
file://确认email,并且设置帐号为已经激活 $feedback .= ” User Account
Updated – You Are Now Logged In ”;
user_set_tokens(db_result($result,0,”user_name”)); $sql=UPDATE
user SET email=”$email”,is_confirmed=”1” WHERE
confirm_hash=”$hash”; $result=db_query($sql);

申明局部变量语法:declare @变量名 数据类型;例如:declare @num int;

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

<

赋值:有两种方法式

SQL SELECT INTO 语法您可以把所有的列插入新表:

set @num=value; 或 select @num=value;

SELECT *INTO new_table_name [IN externaldatabase] FROM
old_tablename

create table employee(2 ID int,3 name nvarchar (10),4 salary int,5
start_date datetime,6 city nvarchar (10),7 region char (1))8 GO12
insert into employee (ID, name, salary, start_date, city, region)3
values (1, ‘Jason’, 40420, ’02/01/94′, ‘New York’, ‘W’)4 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (2, ‘Robert’,14420, ’01/02/95′,
‘Vancouver’,’N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (3, ‘Celia’, 24020, ’12/03/96′,
‘Toronto’, ‘W’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (4, ‘Linda’, 40620, ’11/04/97′,
‘New York’, ‘N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (5, ‘David’, 80026, ’10/05/98′,
‘Vancouver’,’W’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (6, ‘James’, 70060, ’09/06/99′,
‘Toronto’, ‘N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (7, ‘Alison’,90620, ’08/07/00′,
‘New York’, ‘W’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (8, ‘Chris’, 26020, ’07/08/01′,
‘Vancouver’,’N’)3 GO

(1 rows affected)1 insert into employee (ID, name, salary,
start_date, city, region)2 values (9, ‘Mary’, 60020, ’06/09/02′,
‘Toronto’, ‘W’)3 GO

(1 rows affected)12 select * from employee3 GOID name salary
start_date city region———– ———- ———–
———————– ———- —— 1 Jason 40420 1994-02-01
00:00:00.000 New York W 2 Robert 14420 1995-01-02 00:00:00.000
Vancouver N 3 Celia 24020 1996-12-03 00:00:00.000 Toronto W 4 Linda
40620 1997-11-04 00:00:00.000 New York N 5 David 80026 1998-10-05
00:00:00.000 Vancouver W 6 James 70060 1999-09-06 00:00:00.000 Toronto
N 7 Alison 90620 2000-08-07 00:00:00.000 New York W 8 Chris 26020
2001-07-08 00:00:00.000 Vancouver N 9 Mary 60020 2002-06-09
00:00:00.000 Toronto W

(9 rows affected)123 SELECT Id, Name4 INTO Employee_Temp FROM
Employee WHERE Id 15 GO

(8 rows affected)12 SELECT * FROM Employee_Temp3 GOId Name

如果想获取查询语句中的一个字段值可以用select给变量赋值,如下:

利用select into 做一个临时表

select @num=字段名 from 表名 where

34 CREATE TABLE works_on (emp_no INTEGER NOT NULL,35 project_no
CHAR(4) NOT NULL,36 job CHAR (15) NULL,37 enter_date DATETIME
NULL)3839 insert into works_on values (1, ‘p1’, ‘analyst’,
‘1997.10.1’)40 insert into works_on values (1, ‘p3’, ‘manager’,
‘1999.1.1’)41 insert into works_on values (2, ‘p2’, ‘clerk’,
‘1998.2.15’)42 insert into works_on values (2, ‘p2’, NULL,
‘1998.6.1’)43 insert into works_on values (3, ‘p2’, NULL,
‘1997.12.15’)44 insert into works_on values (4, ‘p3’, ‘analyst’,
‘1998.10.15’)45 insert into works_on values (5, ‘p1’, ‘manager’,
‘1998.4.15’)46 insert into works_on values (6, ‘p1’, NULL,
‘1998.8.1’)47 insert into works_on values (7, ‘p2’, ‘clerk’,
‘1999.2.1’)48 insert into works_on values (8, ‘p3’, ‘clerk’,
‘1997.11.15’)49 insert into works_on values (7, ‘p1’, ‘clerk’,
‘1998.1.4’)50 GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)1 select * from works_on2 GOemp_no project_no job
enter_date———– ———- —————
———————– 1 p1 analyst 1997-10-01 00:00:00.000 1 p3
manager 1999-01-01 00:00:00.000 2 p2 clerk 1998-02-15 00:00:00.000 2
p2 NULL 1998-06-01 00:00:00.000 3 p2 NULL 1997-12-15 00:00:00.000 4 p3
analyst 1998-10-15 00:00:00.000 5 p1 manager 1998-04-15 00:00:00.000 6
p1 NULL 1998-08-01 00:00:00.000 7 p2 clerk 1999-02-01 00:00:00.000 8
p3 clerk 1997-11-15 00:00:00.000 7 p1 clerk 1998-01-04 00:00:00.000

(11 rows affected)12 — Remove duplicate data and create a unique
index34 SELECT emp_no, MAX(enter_date) max_date5 INTO #works_on6
FROM works_on7 GROUP BY emp_no8 HAVING COUNT(*) 19 GO

(3 rows affected)1 DELETE works_on FROM works_on, #works_on2 WHERE
works_on.emp_no = #works_on.emp_no3 AND works_on.enter_date
#works_on.max_date4 GO

(3 rows affected)1 select * from works_on2 GOemp_no project_no job
enter_date———– ———- —————
———————– 1 p3 manager 1999-01-01 00:00:00.000 2 p2 NULL
1998-06-01 00:00:00.000 3 p2 NULL 1997-12-15 00:00:00.000 4 p3 analyst
1998-10-15 00:00:00.000 5 p1 manager 1998-04-15 00:00:00.000 6 p1 NULL
1998-08-01 00:00:00.000 7 p2 clerk 1999-02-01 00:00:00.000 8 p3 clerk
1997-11-15 00:00:00.000

(8 rows affected)

mysql教程中变量不用事前申明,在用的时候直接用@变量名使用就可以了。

注间:select into必须两个表的结果完整一致能也很好的把数据完整备份哦。

第一种用法:set @num=1; 或set @num:=1;
//这里要使用变量来保存数据,直接使用@num变量

第二种用法:select @num:=1; 或 select @num:=字段名 from 表名 where

注意上面两种赋值符号,使用set时可以用=或:=,但是使用select时必须用:=赋值

在sql中,我们常常使用临时表来存储临时结果,对于结果是一个集合的情况,这种方法非常实用,但当结果仅仅是一个数据或者是几个数据时,还要去建一个表,显得就比较麻烦,另外,当一个sql语句中的某些元素经常变化时,比如选择条件,应该使用局部变量。当然ms
sql server的全局变量也很有用。

局部变量

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:, , , , , , , , , , ,

相关文章

网站地图xml地图