sql基本语法复习和整理

1.建立存储过程（一个登录的例子）：

 create procedure [dbo].[proc_LoginUser]   //定义存储过程名称

@u_name varchar(30) output,       //定义输出参数
@u_pawd varchar(16),                   //定义输入参数
@result int output, 
@r_kp varchar(500) output,
@s_ip varchar(20),
@s_turename varchar(30) output,
@s_centerid int output
as

//一下就是存储过程的实现
declare @tempID int -- 角色表的ID   //定义变量
set @tempID =-1;      //赋值
update t_sysuser set s_lastlogin =getdate(),s_ip =@s_ip where s_uname =@u_name and s_pwd = @u_pawd and s_isstop = 0
select @result = s_role ,@u_name =s_id,@r_kp=r_kp ,@s_turename =s_turename,@s_centerid=s_centerid from t_sysuser where s_uname =@u_name and s_pwd = @u_pawd and s_isstop = 0
select @tempID = isnull(r_id,-1) from t_role  where r_id =@result
set @result = @tempID

2.建立视图：

CREATE VIEW [dbo].[视图名称]
AS

视图的实现

3.新建一个字段：alter  table 表名 add 字段名  类型  如：alter  table MSG_KeHu add KH_MDId int

4.修改一个字段：ALTER   TABLE   表名  ALTER   COLUMN   字段名   类型   如：ALTER   TABLE   MSG_CaiGouXiangQing   ALTER   COLUMN   CGXQ_Number   float