使用T-sql语句，在数据库PE中创建一个名称为“运动员基本信息表”的关系表

下面的脚本已经测试过了，可以使用，注意修改下数据库名和所有者

其实你可以通过图形化工具创建表，然后在表上右键-创建存储过程-Create就可以得到脚本了

创建表的脚本

USE [PE]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo][AthleteBasicInformation](

[AthleteID] [char](8) COLLATE Chinese_PRC_CI_AS NOT NULL,

[TeamID] [int] NOT NULL,

[Name] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,

[Sex] [char](2) COLLATE Chinese_PRC_CI_AS NULL,

[Age] [int] NULL,

[Profession] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,

CONSTRAINT [PK_AthleteBasicInformation] PRIMARY KEY CLUSTERED

(

[AthleteID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [a]

GO

ALTER TABLE [dbo][AthleteBasicInformation] WITH CHECK ADD CONSTRAINT [sage_ch] CHECK (([age]>(18)))

创建视图的脚本

USE [a]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo][View_SelectMaleGymnast]

AS

SELECT AthleteID, TeamID, Name, Sex, Age, Profession

FROM dboAthleteBasicInformation

WHERE (Sex = '1') AND (Profession = '体 *** ')

GO

EXEC syssp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 100]

Begin DesignProperties =

Begin PaneConfigurations =

Begin PaneConfiguration = 0

NumPanes = 4

Configuration = "(H (1[40] 4[20] 2[20] 3) )"

End

Begin PaneConfiguration = 1

NumPanes = 3

Configuration = "(H (1 [50] 4 [25] 3))"

End

Begin PaneConfiguration = 2

NumPanes = 3

Configuration = "(H (1 [50] 2 [25] 3))"

End

Begin PaneConfiguration = 3

NumPanes = 3

Configuration = "(H (4 [30] 2 [40] 3))"

End

Begin PaneConfiguration = 4

NumPanes = 2

Configuration = "(H (1 [56] 3))"

End

Begin PaneConfiguration = 5

NumPanes = 2

Configuration = "(H (2 [66] 3))"

End

Begin PaneConfiguration = 6

NumPanes = 2

Configuration = "(H (4 [50] 3))"

End

Begin PaneConfiguration = 7

NumPanes = 1

Configuration = "(V (3))"

End

Begin PaneConfiguration = 8

NumPanes = 3

Configuration = "(H (1[56] 4[18] 2) )"

End

Begin PaneConfiguration = 9

NumPanes = 2

Configuration = "(H (1 [75] 4))"

End

Begin PaneConfiguration = 10

NumPanes = 2

Configuration = "(H (1[66] 2) )"

End

Begin PaneConfiguration = 11

NumPanes = 2

Configuration = "(H (4 [60] 2))"

End

Begin PaneConfiguration = 12

NumPanes = 1

Configuration = "(H (1) )"

End

Begin PaneConfiguration = 13

NumPanes = 1

Configuration = "(V (4))"

End

Begin PaneConfiguration = 14

NumPanes = 1

Configuration = "(V (2))"

End

ActivePaneConfig = 0

End

Begin DiagramPane =

Begin Origin =

Top = 0

Left = 0

End

Begin Tables =

Begin Table = "AthleteBasicInformation"

Begin Extent =

Top = 6

Left = 38

Bottom = 121

Right = 169

End

DisplayFlags = 280

TopColumn = 0

End

End

End

Begin SQLPane =

End

Begin DataPane =

Begin ParameterDefaults = ""

End

End

Begin CriteriaPane =

Begin ColumnWidths = 11

Column = 1440

Alias = 900

Table = 1170

Output = 720

Append = 1400

NewValue = 1170

SortType = 1350

SortOrder = 1410

GroupBy = 1350

Filter = 1350

Or = 1350

Or = 1350

Or = 1350

End

End

End

' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'View_SelectMaleGymnast'

GO

EXEC syssp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'View_SelectMaleGymnast'

[PointLastUpdate] [datetime] NOT NULL CONSTRAINT [DF_LastUpdate_RegDate] DEFAULT (getdate()),

去掉NOT NULL。

mssql 触发器自动更新

CREATE TRIGGER 入库表修改 ON 入库表

FOR UPDATE

AS

SET NOCOUNT ON

if update(入库数量)

begin

update 库存表 set 库存数量=isnull(a入库数量,0)-isnull(b入库数量,0)-isnull(c出库数量,0)

from 入库表 a,deleted b,出库表 c where a型号=b型号 and a型号=c型号

update 库存表 set 库存数量=isnull(a入库数量,0)+isnull(b入库数量,0)-isnull(c出库数量,0)

from 入库表 a,inserted b,出库表 c where a型号=b型号 and a型号=c型号

end

SET NOCOUNT OFF

go

CREATE TRIGGER 出库表修改 ON 出库表

FOR UPDATE

AS

SET NOCOUNT ON

if update(出库数量)

begin

update 库存表 set 库存数量=isnull(a入库数量,0)-isnull(c出库数量,0)+isnull(b出库数量,0)

from 入库表 a,deleted b,出库表 c where a型号=b型号 and a型号=c型号

update 库存表 set 库存数量=isnull(a入库数量,0)-isnull(c出库数量,0)-isnull(b出库数量,0)

from 入库表 a,inserted b,出库表 c where a型号=b型号 and a型号=c型号

end

SET NOCOUNT OFF

你先用mangerment studio弄，在生成个create脚本就好了呀。。。

USE [master]

GO

/ Object: Database [DBEX] Script Date: 06/19/2014 11:25:12 /

CREATE DATABASE [DBEX] ON PRIMARY

( NAME = N'DBEX', FILENAME = N'C:\db\DBEXmdf' , SIZE = 5120KB , MAXSIZE = 20480KB , FILEGROWTH = 2048KB )

LOG ON

( NAME = N'DBEX_log', FILENAME = N'C:\db\DBEX_logldf' , SIZE = 3072KB , MAXSIZE = 2048GB , FILEGROWTH = 20%)

GO

ALTER DATABASE [DBEX] SET COMPATIBILITY_LEVEL = 100

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [DBEX][dbo][sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [DBEX] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [DBEX] SET ANSI_NULLS OFF

GO

ALTER DATABASE [DBEX] SET ANSI_PADDING OFF

GO

ALTER DATABASE [DBEX] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [DBEX] SET ARITHABORT OFF

GO

ALTER DATABASE [DBEX] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [DBEX] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [DBEX] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [DBEX] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [DBEX] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [DBEX] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [DBEX] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [DBEX] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [DBEX] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [DBEX] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [DBEX] SET DISABLE_BROKER

GO

ALTER DATABASE [DBEX] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [DBEX] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [DBEX] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [DBEX] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [DBEX] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [DBEX] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [DBEX] SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE [DBEX] SET READ_WRITE

GO

ALTER DATABASE [DBEX] SET RECOVERY FULL

GO

ALTER DATABASE [DBEX] SET MULTI_USER

GO

ALTER DATABASE [DBEX] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [DBEX] SET DB_CHAINING OFF

GO

朋友，因为对ACCESS不是非常清楚其数据类型，我暂时用SQL中的方式帮你解决此问题：

先创建表：

/ Object: Table [dbo][Chart] Script Date: 09/30/2010 12:39:49 /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo][Chart](

[id] [int] IDENTITY(1,1) NOT NULL,

[employee] [varchar](50) NULL,

[Date] [varchar](50) NULL,

CONSTRAINT [PK_Chart_1] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/ Object: StoredProcedure [dbo][procGetInfoByDate] Script Date: 09/30/2010 12:39:47 /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create proc [dbo][procGetInfoByDate]

@beginDate varchar(5),

@endDate varchar(5)

as

begin

select from Chart where Chart[Date] between convert(varchar(50),'201002030'+@beginDate) and

convert(varchar(50),'201002030'+@endDate)

end

GO

方法一：直接写SQL语句：

select from Chart where Chart[Date] between convert(varchar(50),'201002030'+'1') and

convert(varchar(50),'201002030'+'2')

方法二：用存储过程，这种方法更适合你的需求

create proc procGetInfoByDate

@beginDate varchar(5),

@endDate varchar(5)

as

begin

select from Chart where Chart[Date] between convert(varchar(50),'201002030'+@beginDate) and

convert(varchar(50),'201002030'+@endDate)

end

调用存储过程：

procGetInfoByDate 1,2

希望能为你提供借鉴！

以上就是关于使用T-sql语句，在数据库PE中创建一个名称为“运动员基本信息表”的关系表全部的内容，包括:使用T-sql语句，在数据库PE中创建一个名称为“运动员基本信息表”的关系表、sql 2000 数据库 21行 语法错误 求救、大神们,求问,我目前数据库有三张表,入库,库存,出库.有个字段是数量,如何让三张表关联自动更新等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！