select bookName from myrank
union
select bookName from mybook;

select bookName from myrank
union all
select bookName from mybook;

--將mybook表的數(shù)據(jù)復(fù)制到mybookcopy表中，mybookcopy表無(wú)需我們創(chuàng)建，執(zhí)行這條語(yǔ)句后會(huì)自動(dòng)創(chuàng)建mybookcopy表
select * into mybookcopy from mybook;
--復(fù)制多個(gè)表的列到新表中
select mybook.bookName,myuser.userName into newtable from mybook join myuser on mybook.userID=myuser.userId

--創(chuàng)建表時(shí)在列的后面加上NOT NULL關(guān)鍵字則添加了非空的約束
create table mytable
(
myID int NOT NULL,
myName nvarchar(32) NOT NULL
)
--創(chuàng)建表時(shí)在列的后面加上UNIQUE關(guān)鍵字則添加了唯一的約束
create table mytable2
(
myID int NOT NULL UNIQUE,
myName nvarchar(32) NOT NULL
)
--當(dāng)表被創(chuàng)建好后，可以使用alter來(lái)新增唯一性約束
alter table mytable2 add constraint nameunique unique(myName)
--使用alter和drop來(lái)刪除唯一性約束
alter table mytable2 drop nameunique

--將mytable2刪除后重新創(chuàng)建，并設(shè)置myID為主鍵
create table mytable2
(
myID int NOT NULL,
myName nvarchar(32) NOT NULL,
constraint myprimary primary key(myID)
)
--而且在這個(gè)地方我還發(fā)現(xiàn)當(dāng)刪除數(shù)據(jù)表后，如果馬上新建名字和字段一樣的表會(huì)直接將剛剛刪除的表重新顯示，原有的數(shù)據(jù)還在，只是添加了主鍵
--刪除主鍵
alter table mytable2 drop myprimary
--新增主鍵
alter table mytable2 add constraint myprimary primary key(myID,myName)

--首先刪除上面mytable2的主鍵，創(chuàng)建mytable3，并設(shè)置myName為主鍵
create table mytable3
(
myName nvarchar(32) NOT NULL PRIMARY KEY,
age int NOT NULL
)
--然后設(shè)置mytable2的myName為外鍵,刪除方式和上面差不多，也是采用drop關(guān)鍵字
alter table mytable2 add constraint myforeign foreign key(myName) references mytable3(myName)

--創(chuàng)建表時(shí)在列的后面加上check關(guān)鍵字則添加了約束
create table mytable3
(
myID int NOT NULL check (myID>0),
myName nvarchar(32) NOT NULL
)
--新增check約束
alter table mytable3 add constraint mycheck check(myName!='hh')
--刪除check約束
alter table mytable3 drop mycheck

--創(chuàng)建表時(shí)在列的后面加上default關(guān)鍵字,只是這個(gè)地方SQL2008做得還不夠好,添加一行數(shù)據(jù)如果不填myName它會(huì)顯示NULL,關(guān)了再查看才為fyz
create table mytable3
(
myID int NOT NULL check (myID>0),
myName nvarchar(32) default'fyz'
)
--刪除default約束
alter table mytable3 drop constraint DF__mytable3__myName__2B3F6F97;
--新增default約束
alter table mytable3 add default'hhh' for myName;

select c.name from sysconstraints a 
inner join syscolumns b on a.colid=b.colid 
inner join sysobjects c on a.constid=c.id 
where a.id=object_id('mytable3') 
and b.name='myName'

--創(chuàng)建索引,可以使用重復(fù)的值
create index myIdindex on mytable3(myId);
--創(chuàng)建不允許有重復(fù)值，一個(gè)索引只能對(duì)應(yīng)這唯一的一行
create unique index myuniqueIndex on mytable3(myId);

--使用drop可以刪除索引、表、數(shù)據(jù)庫(kù)等
drop index myIdindex on mytable3;
drop index mytable3.myuniqueIndex;
--tip:如果我們需要清空表里的數(shù)據(jù)而不刪除表本身
truncate table myrank;
--alter table可用于在已有的表中添加、刪除和修改列
alter table mybook add bookOther nvarchar(32);
alter table mybook alter column bookOther nvarchar(64);
alter table mybook drop column bookOther;

--設(shè)置auto-increment主鍵字段,identity是設(shè)置主鍵的關(guān)鍵字，第一個(gè)1表示從1開(kāi)始，第二個(gè)1表示每條記錄遞增1
create table mytable4
(
myId int identity(1,1) primary key,
myname nvarchar(16)
)

insert into mytable4 values('hh');
insert into mytable4 (myname) values (null);
--查找為空的行和不為空的行
select * from mytable4 where myname is null;
select * from mytable4 where myname is not null;
--isnull()函數(shù)，這樣如果某行的myname為null，則會(huì)輸出fyz
select myId,ISNULL(myname,'fyz') as myaname from mytable4;