7,774
社区成员




--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:DVD
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'DVD')
AND type in (N'U'))
DROP TABLE DVD
GO
---->建表
create table DVD([ID] int,[name] varchar(8),[price] int,[pubID] varchar(4))
insert DVD
select 1,'非诚勿扰',100,'NULL' union all
select 2,'唐山地震',200,'NULL' union all
select 3,'西游记',300,'NULL'
GO
--> 查询结果
SELECT * FROM DVD
--> 删除表格
--DROP TABLE DVD
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:Published
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Published')
AND type in (N'U'))
DROP TABLE Published
GO
---->建表
create table Published([ID] int,[name] varchar(6),[addtime] datetime)
insert Published
select 1,'华谊','2010-09-16' union all
select 2,'新画面','2010-09-16' union all
select 3,'中影','2010-09-16'
GO
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:如下表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'如下表')
AND type in (N'U'))
DROP TABLE 如下表
GO
---->建表
create table 如下表([ID] int,[name] varchar(8),[price] int,[type] varchar(4),[Actor] varchar(4),[Pub] varchar(4),[picurl] varchar(8),[addtime] datetime)
insert 如下表
select 1,'非诚勿扰',100,'喜剧','葛优','华谊','pic1.jpg','2010-09-16' union all
select 2,'唐山地震',200,'灾难','徐帆','华谊','pic2.jpg','2010-09-16' union all
select 3,'西游记',300,'科幻','朱琳','中影','pic3.jpg','2010-09-16'
GO
--> 查询结果
SELECT * FROM Published
--> 删除表格
--DROP TABLE Published
--> 查询结果
SELECT * FROM 如下表
--> 删除表格
--DROP TABLE 如下表
--语句如下:
UPDATE DVD
SET pubID = P.ID
FROM DVD AS D JOIN 如下表 AS R
ON D.ID=R.ID
JOIN Published AS P ON R.Pub=P.name