学习手工创建表,表关系以及用exists 来查询

时间:2022-02-11 03:09:03

---创建表a

If exists(select * from sysobject where [name]=='a' and xType = 'u')

Begin

  Drop table aa

End

Begin

  Create table aa

  (id int,

   cplpdid int, 

   [name] nchar(50),

   Constraint pk_id Primary key Clustered(id),)

  Insert into aa

  select 1,3,'s'

  union all

  select 2,4,"w"

  union all

  select 3.5,'Q'

  union all

  select 4,6,'D'

End

IF EXISTS (select * from sysobjects where [name] = 'bb' and xtype='U') BEGIN
   DROP TABLE bb
END

BEGIN  
   CREATE TABLE bb
  (
    cplpdid INT,
    [user] NVARCHAR(50),
    CONSTRAINT cplpdid PRIMARY KEY CLUSTERED(cplpdid)
  )

INSERT into bb
  SELECT 1,'aaa'
  UNION ALL
  SELECT 2, 'bbb'
  UNION ALL
  SELECT 3,'ccc'
  UNION ALL
  SELECT 4,'ddd'
 end

Alter table aa with nocheck add constraint FK_aa_cplpdid Foreign key (cplpdid) Reference bb (cplpdid)

select * from aa where not exists (select cplpdid from bb where bb.cplpdid = a.cplpid)

select * from aa where aa.cplpdid not in(select cplpdid from bb)

http://www.cnblogs.com/tiantiansunny/articles/3555941.html