new.sql

SQL创建表

FROM dbo.KJJL
GROUP BY KJJL_JLDJ
""
CREATE VIEW dbo.VKJJL_JLZL
AS
SELECT KJJL_JLZL AS grouptype, COUNT(*) AS [count]
FROM dbo.KJJL
GROUP BY KJJL_JLZL
""
CREATE VIEW dbo.VKJRY_CHJB
AS
SELECT KJRY_CHJB AS grouptype, COUNT(*) AS [count]
FROM dbo.KJRY
GROUP BY KJRY_CHJB
""
CREATE PROCEDURE database_back
	@disk char(100)	
	 AS
		bac
	@no int,@name char(10),@sex char(4),@csny datetime,
	@byyx char(50),@xxzy char(20),@zzmm char(10),
	@gzdw char(50),@cszy char(20),@xzzw char(10),@zgxl char(10),
	@zgxw char(10),@jszc char(10),@zcjb char(10),@dwcj char(10),
	@dwxz char(10),@zcgsxl char(30),@dwlb char(10),@dwgj char(10),
	@dwgkhy char(30)
	AS
	if not exists(select * from JBXX)
	set @no = 1001	
	else
	set @no = (select max(JBXX_NO) from JBXX) + 1;
	insert into jbxx
	values(@no,@name,@sex,@csny,
	@byyx,@xxzy,@zzmm,@gzdw,
	@cszy,@xzzw,@zgxl,@zgxw,
	@jszc,@zcjb,@dwcj,@dwxz,
	@zcgsxl,@dwlb,@dwgj,@dwgkhy)
""
CREATE PROCEDURE jbxxname
	@id int,@name char(10) output
 AS
	set @name = (select jbxx_name 
			from jbxx
			where
				jbxx_no = @id)
""
CREATE procedure kjjl_insert 
	@jlzl char(10),@jlmc varchar(50),@jldj char(10),@zspm char(10),@jlsj smalldatetime,@rybh int
	as
		insert into KJJL 
			values(@jlzl,@jlmc,@jldj,@zSpm,@jlsj,@rybh)
""
CREATE procedure kjry_insert 
	@chjb char(10),@rych varchar(50),@sysj smalldatetime,@jlje char(10),@rybh int
	as
		insert into KJRY 
			values(@chjb,@rych,@sysj,@jlje,@rybh)
""
CREATE PROCEDURE xszz_insert
	 @zzname varchar(52),@zw char(10),@rzsj smalldatetime,@rybh int
	 AS
		insert into XSZZ
			values(@zzname,@zw,@rzsj,@rybh)
""
CREATE procedure xxjl_insert 
	@byyx char(50),@xxsj smalldatetime,@xxzy char(10),@xl char(10),@xw char(10),@rybh int
	as
		insert into XXJL 
			values(@byyx,@xxsj,@xxzy,@xl,@xw,@rybh)
""
CREATE PROCEDURE yonghu_check 		/*用户验证*/
	@name varchar(20),@password char(10),@return int output
	 AS
		declare @psd char(10),@id int
		set 	@psd = (select YH_PASSWORD 
					from YONGHU
					where	YH_NAME = @name)
		IF @password  <> @psd
			begin
				set @return =  -1
			end 	
		else
			begin
				set @id = (select YH_ID 
						from YONGHU
						where YH_NAME = @name and  YH_PASSWORD = @password)
				set  @return = (select YH_LEVEL 
					from YONGHU
					where	YH_ID = @id)
			end
""
CREATE PROCEDURE yonghu_IDcheck /*检测用户名是否已经存在	*/
	@ID int,@return int output	
	AS
		if not exists(select * from YONGHU where YH_ID = @ID)
			set @return = 0
		else
			set @return = 1
""
CREATE procedure yonghu_mb /*用户注册*/
	@ID int,@q text,@an text
	as
		declare @num int ;
		set @num = (select max(YH_NUM) from YHMB) + 1;
		insert into YHMB
			values(@num,@ID,@q,@an)
""
CREATE PROCEDURE yonghu_mbIDcheck /*检测密保用户名是否已经存在	*/
	@ID int,@return int output	
	AS
		if not exists(select * from YHMB where YH_ID = @ID)
			set @return = 0
		else
			set @return = 1
""
CREATE PROCEDURE yonghu_mbupdate  /*密保修改*/
	@ID int,@q varchar(50),@a varchar(50)
	AS
		update YHMB set 
			YH_QUESTION = @q,
			YH_ANSWER = @a
		where
			YH_ID = @ID
""
CREATE PROCEDURE yonghu_mmupdate 
	@ID int,@password char(10)
	AS
		update YONGHU 
			set YH_PASSWORD = @password
		where
			YH_ID = @ID
""
CREATE procedure yonghu_mmzh  /*用户密码找回*/
	@ID int,@qu varchar(50),@ans varchar(50),@return int output,@m char(10) output
	as
		declare @q varchar(50),@an varchar(50)
		set @q= (select YH_QUESTION from YHMB where YH_ID=@ID)
		set @an= (select YH_ANSWER from YHMB where YH_ID=@ID)
		if(@q=@qu and @an=@ans)
		begin
			set @m=(select YH_PASSWORD from YONGHU where YH_ID=@ID)
			set @return=1
		end
		else
		begin
			set @return=0
			set @m='a'
		end
""
CREATE PROCEDURE yonghu_namecheck /*检测用户名是否已经存在	*/
	@name char(20),@return int output	
	AS
		if not exists(select * from YONGHU where YH_NAME = @name)
			set @return = 0
		else
			set @return = 1
""
CREATE PROCEDURE yonghu_namecz
	@no int,@return char(10) output	
	AS
		set @return=(select JBXX_NAME from JBXX 
				where JBXX_NO = @no)
""
CREATE PROCEDURE yonghu_PassWordcheck /*检测用户密码是否已经存在	*/
	@p char(6),@return int output	
	AS
		if not exists(select * from YONGHU where YH_PASSWORD = @p)
			set @return = 0
		else
			set @return = 1
""
CREATE PROCEDURE yonghu_PScheck /*检测用户密码是否已经存在	*/
	@ID int,@p char(10),@return int output	
	AS
		if(@p=(select  YH_PASSWORD from YONGHU where YH_ID=@ID)) 
			set @return = 1
		else
			set @return = 0
""
CREATE PROCEDURE yonghu_PWcheck /*检测用户密码是否正确	*/
	@ID int,@p char(10),@return int output	
	as
		declare @pa char(10)
		set @pa=(select YH_PASSWORD 
			from YONGHU where YH_ID=@ID)
		if  (@p=@pa)
			set @return = 1
		else
			set @return = 0
""
CREATE PROCEDURE yonghu_qxcheck
	@ID int,@return int output	
	AS
		set @return=(select YH_LEVEL from YONGHU where YH_ID = @ID)
""
CREATE PROCEDURE yonghu_update 
	@num int,@id int,@name varchar(20),@password char(10),@danwei varchar(20),@level int	 
	AS
		update YONGHU set
			YH_NAME = @name,
			YH_PASSWORD = @password,
			YH_DANW = @danwei,
			YH_LEVEL  = @level,
			YH_ID=@id
		where
			YH_NUM = @num
""
CREATE procedure yonghu_zc /*用户注册*/
	@ID int,@name char(20),@password char(10),@danw char(20),@level int
	as
		declare @num int ;
		if not exists(select * from YONGHU)
			set @num = 0
		else	
			set @num = (select max(YH_NUM) from YONGHU) + 1;
		insert into YONGHU
			values(@num,@name,@password,@danw,@level,@ID)
""
CREATE FUNCTION student1(@classno char(5))  --建立函数student1
  RETURNS @st TABLE		--返回表@st,下面定义其表结构
  ( 
     sno char(5),
     sname char(8),
     cno char(5),
     degree int
  )
AS
BEGIN
  INSERT @st	--向@st中插入满足条件的记录
      SELECT student.sno,student.sname,score.cno,score.degree
      FROM student,score
      WHERE student.sno=score.sno AND student.class=@classno
  RETURN
END
""
CREATE FUNCTION tongji(@id int)
	RETURNS @table TABLE
	(
		[grouptype] char(10),
		[count] int
	)

 AS  
BEGIN 
	if @id = 0
		INSERT  @table
			select *   from VJBXX_Sex
	else if @id = 1
		INSERT  @table
			select *   from VJBXX_ZZMM
	else if @id = 2
		INSERT  @table
			select *   from VJBXX_ZGXL
	else if @id = 3
		INSERT  @table
			select *   from VJBXX_ZGXW
	else if @id = 4
		INSERT  @table
			select *   from VJBXX_DWCJ
	else if @id = 5
		INSERT  @table
			select *   from VJBXX_ZCJB
	else if @id = 6
		INSERT  @table
			select *   from VKJJL_JLZL
	else if @id = 7
		INSERT  @table
			select *   from VKJJL_JLDJ
	else if @id = 8
		INSERT  @table
			select *   from VKJRY_CHJB	 
	return 
END
""
CREATE FUNCTION tonji(@id int)
	RETURNS @table TABLE
	(
		[groluptype] char(20),
		[count] int
	)

 AS  
BEGIN 
	if @id = 0
		INSERT  @table
			select *   from VJBXX_Sex
	else if @id = 1
		INSERT  @table
			select *   from VJBXX_ZZMM
	else if @id = 2
		INSERT  @table
			select *   from VJBXX_ZGXL
	else if @id = 3
		INSERT  @table
			select *   from VJBXX_ZGXW
	else if @id = 4
		INSERT  @table
			select *   from VJBXX_DWCJ
	else if @id = 5
		INSERT  @table
			select *   from VJBXX_ZCJB
	else if @id = 6
		INSERT  @table
			select *   from VKJJL_JLZL
	else if @id = 7
		INSERT  @table
			select *   from VKJJL_JLDJ
	else if @id = 8
		INSERT  @table
			select *   from VKJRY_CHJB	 
	return 
END
""
exec yonghu_zc @ID='100001',@name='admin',@password ='0714=>',@danw ='',@level ='0' 
""