昨天和我们合作的公司给我个数据库表格定义(sql server 2005),本人新手 对数据库还不是很熟悉,哪位能帮忙解释一下下面的3段该如何建表啊?我刚才建表后查看的脚本怎么和下面的不太一样?还有就是可不可以直接通过脚本建表?如何建?谢谢
1. 设备变量描述表
CREATE TABLE [GasDeviceInfo] (
[GasMID] [int] IDENTITY (1, 1) NOT NULL ,
[CitTag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Information] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_GasDeviceInfo] PRIMARY KEY CLUSTERED
(
[GasMID]
) ON [PRIMARY]
) ON [PRIMARY]2. 监控数据历史库表CREATE TABLE [GasHistory] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[GasMID] [int] NOT NULL ,
[MTime] [datetime] NOT NULL ,
[MValues] [int] NOT NULL ,
CONSTRAINT [PK_GasHistory] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_GasHistory_GasDeviceInfo] FOREIGN KEY
(
[GasMID]
) REFERENCES [GasDeviceInfo] (
[GasMID]
) ON DELETE CASCADE
) ON [PRIMARY]3. 最新监控数据视图CREATE VIEW dbo.VIEW_NowGasValues
AS
SELECT dbo.GasHistory.GasMID, dbo.GasHistory.ID, dbo.GasHistory.MTime,
dbo.GasHistory.MValues, dbo.GasDeviceInfo.Information
FROM dbo.GasHistory INNER JOIN
dbo.GasDeviceInfo ON
dbo.GasHistory.GasMID = dbo.GasDeviceInfo.GasMID
WHERE (dbo.GasHistory.MTime =
(SELECT MAX(MTime)
FROM dbo.GasHistory))
1. 设备变量描述表
CREATE TABLE [GasDeviceInfo] (
[GasMID] [int] IDENTITY (1, 1) NOT NULL ,
[CitTag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Information] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_GasDeviceInfo] PRIMARY KEY CLUSTERED
(
[GasMID]
) ON [PRIMARY]
) ON [PRIMARY]2. 监控数据历史库表CREATE TABLE [GasHistory] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[GasMID] [int] NOT NULL ,
[MTime] [datetime] NOT NULL ,
[MValues] [int] NOT NULL ,
CONSTRAINT [PK_GasHistory] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_GasHistory_GasDeviceInfo] FOREIGN KEY
(
[GasMID]
) REFERENCES [GasDeviceInfo] (
[GasMID]
) ON DELETE CASCADE
) ON [PRIMARY]3. 最新监控数据视图CREATE VIEW dbo.VIEW_NowGasValues
AS
SELECT dbo.GasHistory.GasMID, dbo.GasHistory.ID, dbo.GasHistory.MTime,
dbo.GasHistory.MValues, dbo.GasDeviceInfo.Information
FROM dbo.GasHistory INNER JOIN
dbo.GasDeviceInfo ON
dbo.GasHistory.GasMID = dbo.GasDeviceInfo.GasMID
WHERE (dbo.GasHistory.MTime =
(SELECT MAX(MTime)
FROM dbo.GasHistory))
caeate table tablename
(
字段一 类型 null||not null,
字段二 类型 null||not null,
....
)
我也是学sql server 不多久,我觉得楼主可以去买一本基础书好好看下,系统学习一下,其实理解起来也挺容易的
打開Microsoft SQL Server Management Studio
左上角有個新增查詢,點一下
然後選擇數據庫,就是你想把這些表和視圖放在哪裏的那個數據庫。
工具欄裏面有個類似ComboBox的控件,點它右邊的小三角即可看到。
或者在你的代碼最前面加上
USE 數據庫名
GO
'CREATE VIEW' 必须是查询批次中的第一个语句。
我执行后出现这句提示,该如何处理呢?
gocreate table t1(id int)
create view v_test
as
select * from t1
go如果是这样的写法,当你编译的时候,就会出现你说的错误提示,所以应该改成下面这样:
use tempdb
gocreate table t1(id int)
go --这里加上gocreate view v_test
as
select * from t1
go
GO
CREATE TABLE [GasDeviceInfo] (
[GasMID] [int] IDENTITY (1, 1) NOT NULL ,
[CitTag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Information] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_GasDeviceInfo] PRIMARY KEY CLUSTERED
(
[GasMID]
) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [GasHistory] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[GasMID] [int] NOT NULL ,
[MTime] [datetime] NOT NULL ,
[MValues] [int] NOT NULL ,
CONSTRAINT [PK_GasHistory] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_GasHistory_GasDeviceInfo] FOREIGN KEY
(
[GasMID]
) REFERENCES [GasDeviceInfo] (
[GasMID]
) ON DELETE CASCADE
) ON [PRIMARY]
CREATE VIEW dbo.VIEW_NowGasValues
AS
SELECT dbo.GasHistory.GasMID, dbo.GasHistory.ID, dbo.GasHistory.MTime,
dbo.GasHistory.MValues, dbo.GasDeviceInfo.Information
FROM dbo.GasHistory INNER JOIN
dbo.GasDeviceInfo ON
dbo.GasHistory.GasMID = dbo.GasDeviceInfo.GasMID
WHERE (dbo.GasHistory.MTime =
(SELECT MAX(MTime)
FROM dbo.GasHistory))
消息 111,级别 15,状态 1,第 29 行
'CREATE VIEW' 必须是查询批次中的第一个语句。
USE aaa
GO
CREATE TABLE [GasDeviceInfo] (
[GasMID] [int] IDENTITY (1, 1) NOT NULL ,
[CitTag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Information] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_GasDeviceInfo] PRIMARY KEY CLUSTERED
(
[GasMID]
) ON [PRIMARY]
) ON [PRIMARY] CREATE TABLE [GasHistory] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[GasMID] [int] NOT NULL ,
[MTime] [datetime] NOT NULL ,
[MValues] [int] NOT NULL ,
CONSTRAINT [PK_GasHistory] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_GasHistory_GasDeviceInfo] FOREIGN KEY
(
[GasMID]
) REFERENCES [GasDeviceInfo] (
[GasMID]
) ON DELETE CASCADE
) ON [PRIMARY]
GOCREATE VIEW dbo.VIEW_NowGasValues
AS
SELECT dbo.GasHistory.GasMID, dbo.GasHistory.ID, dbo.GasHistory.MTime,
dbo.GasHistory.MValues, dbo.GasDeviceInfo.Information
FROM dbo.GasHistory INNER JOIN
dbo.GasDeviceInfo ON
dbo.GasHistory.GasMID = dbo.GasDeviceInfo.GasMID
WHERE (dbo.GasHistory.MTime =
(SELECT MAX(MTime)
FROM dbo.GasHistory))
楼主怎么不好好看我之前给你的解释。