new.sql

SQL创建表

"
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tongji]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[tongji]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tonji]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[tonji]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student1]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[student1]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gzjl_insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[gzjl_insert]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jbxx_insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[jbxx_insert]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jbxxname]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[jbxxname]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[kjjl_insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[kjjl_insert]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[kjry_insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[kjry_insert]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xszz_insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xszz_insert]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxjl_insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xxjl_insert]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_check]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_check]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_IDcheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_IDcheck]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_mb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_mb]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_mbIDcheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_mbIDcheck]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_mbupdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_mbupdate]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_mmupdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_mmupdate]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_mmzh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_mmzh]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_namecheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_namecheck]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_namecz]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_namecz]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_PassWordcheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_PassWordcheck]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_PScheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_PScheck]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_PWcheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_PWcheck]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_qxcheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_qxcheck]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_update]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yonghu_zc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yonghu_zc]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[database_back]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[database_back]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[database_restore]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[database_restore]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VJBXX_DWCJ]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VJBXX_DWCJ]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VJBXX_Sex]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VJBXX_Sex]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VJBXX_ZCJB]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VJBXX_ZCJB]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VJBXX_ZGXL]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VJBXX_ZGXL]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VJBXX_ZGXW]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VJBXX_ZGXW]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VJBXX_ZZMM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VJBXX_ZZMM]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VKJJL_JLDJ]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VKJJL_JLDJ]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VKJJL_JLZL]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VKJJL_JLZL]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VKJRY_CHJB]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VKJRY_CHJB]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[COUNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[COUNT]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GZJL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GZJL]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JBXX]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JBXX]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KJJL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KJJL]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KJRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KJRY]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[QX]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[QX]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XSZZ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XSZZ]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XXJL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XXJL]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YHMB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[YHMB]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[YONGHU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[YONGHU]


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tabletemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tabletemp]


CREATE TABLE [dbo].[COUNT] (
	[COUNT_ID] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[COUNT_VALUE] [int] NOT NULL 
) ON [PRIMARY]


CREATE TABLE [dbo].[GZJL] (
	[GZJL_GZDW] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[GZJL_GZTIME] [smalldatetime] NULL ,
	[GZJL_CSZY] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[GZJL_XZZW] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[GZJL_JSZC] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[GZJL_RYBH] [int] NOT NULL 
) ON [PRIMARY]


CREATE TABLE [dbo].[JBXX] (
	[JBXX_NO] [int] NOT NULL ,
	[JBXX_NAME] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[JBXX_SEX] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_CSNY] [datetime] NULL ,
	[JBXX_BYXX] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_XXZY] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_ZZMM] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_GZDW] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_CSZY] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_XZZW] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_ZGXL] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_ZGXW] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_JSZC] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_ZCJB] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_DWCJ] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_DWXZ] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_ZCGSXL] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_DWLB] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_DWGJ] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[JBXX_DWGKHY] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]


CREATE TABLE [dbo].[KJJL] (
	[KJJL_JLZL] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[KJJL_JLMC] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[KJJL_JLDJ] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[KJJL_ZSPM] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[KJJL_JLSJ] [smalldatetime] NULL ,
	[KJJL_RYBH] [int] NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[KJRY] (
	[KJRY_CHJB] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[KJRY_RYCH] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[KJRY_SYSJ] [smalldatetime] NULL ,
	[KJRY_JLJE] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[KJRY_RYBH] [int] NULL 
) ON [PRIMARY]


CREATE TABLE [dbo].[QX] (
	[QX_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[QX_LEVEL] [int] NOT NULL 
) ON [PRIMARY]


CREATE TABLE [dbo].[XSZZ] (
	[XSZZ_ZZNAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[XSZZ_ZW] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[XSZZ_RZSJ] [smalldatetime] NULL ,
	[XSZZ_RYBH] [int] NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[XXJL] (
	[XXJL_BYYX] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[XXJL_XXSJ] [smalldatetime] NULL ,
	[XXJL_XXZY] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[XXJL_XL] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[XXJL_XW] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[XXJL_RYBH] [int] NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[YHMB] (
	[YH_NUM] [int] NOT NULL ,
	[YH_ID] [int] NOT NULL ,
	[YH_QUESTION] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[YH_ANSWER] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[YONGHU] (
	[YH_NUM] [int] NOT NULL ,
	[YH_NAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[YH_PASSWORD] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[YH_DANW] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[YH_LEVEL] [int] NOT NULL,
	[YH_ID] [int] NOT NULL 
) ON [PRIMARY]


CREATE TABLE [dbo].[tabletemp] (
	[grouptype] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[count] [int] NULL 
) ON [PRIMARY]
""
CREATE VIEW dbo.VJBXX_DWCJ
AS
SELECT JBXX_DWCJ AS grouptype, COUNT(*) AS [count]
FROM dbo.JBXX
GROUP BY JBXX_DWCJ
""
CREATE VIEW dbo.VJBXX_Sex
AS
SELECT JBXX_SEX AS grouptype, COUNT(*) AS [count]
FROM dbo.JBXX
GROUP BY JBXX_SEX
""
CREATE VIEW dbo.VJBXX_ZCJB
AS
SELECT JBXX_ZCJB AS grouptype, COUNT(*) AS [count]
FROM dbo.JBXX
GROUP BY JBXX_ZCJB
""
CREATE VIEW dbo.VJBXX_ZGXL
AS
SELECT JBXX_ZGXL AS grouptype, COUNT(*) AS [count]
FROM dbo.JBXX
GROUP BY JBXX_ZGXL
""
CREATE VIEW dbo.VJBXX_ZGXW
AS
SELECT JBXX_ZGXW AS grouptype, COUNT(*) AS [count]
FROM dbo.JBXX
GROUP BY JBXX_ZGXW
""
CREATE VIEW dbo.VJBXX_ZZMM
AS
SELECT JBXX_ZZMM AS grouptype, COUNT(*) AS [count]
FROM dbo.JBXX
GROUP BY JBXX_ZZMM
""
CREATE VIEW dbo.VKJJL_JLDJ
AS
SELECT KJJL_JLDJ AS grouptype, COUNT(*) AS [count]