(Uno char(4) primary key not null, Una1 varchar(20) not null, Upas varchar(16) not null, Una2 varchar(30) not null, Usex char(2) null, Uage int null,
Utel varchar(20) null, Umes varchar(30) null) go
create table Cabin/*创建Cabin表*/ (Cno char(2) primary key not null, Ctype varchar(10) not null, Cnum int not null, Cpri1 int not null, Cpri2 int not null) go
create table Flight/*创建Flight表*/ (Fno char(5) primary key not null, Ftype varchar(6) not null, City1 varchar(10) not null, City2 varchar(10) not null, Time1 varchar(20) not null, Time2 varchar(20) not null, Ftim varchar(20) not null) go
create table Airline/*创建Airline表*/ (Ano char(4) primary key not null, Ana varchar(30) not null, Aman varchar(10) not null, Atel varchar(20) not null) go
create table Ticket/*创建Ticket表*/ (Tno char(6) primary key not null, Tna varchar(20) not null, Tpos char(18) not null, Ttel varchar(20) not null) go
create table Twork/*创建Twork表*/ (Wno char(4) primary key not null, Nature char(4) not null, Tno char(6) not null, Uno char(4) not null, Cno char(2) not null, Fno char(5) not null,
11
Ano char(4) not null, Pri int not null) go
create table Notice/*创建Notice表*/ (Nno char(4) primary key not null, Mno char(4) not null,
Nsay varchar(1000) not null, Ntim varchar(30) not null) go
create table People/*创建People表*/ (Pno char(4) primary key not null, Pna varchar(20) not null, Psex char(2) null, Page int null,
Zno char(2) not null, ZW varchar(20) not null) go
create table Moneys/*创建Moneys表*/ (Mno char(4) primary key not null, Mlev char(2) not null, Mnum int not null) go
create table Gmoney/*创建Gmoney表*/ (Mno char(4) not null, Pno char(4) not null, Gtim varchar(20) not null,
constraint PK_G primary key(Mno,Pno)) go
alter table Notice
add constraint FK_N foreign key(Pno) references People(Pno)
图3-2 表创建效果
12
/*插入记录*/
运用以下语句来插入记录，这里不一一列出 use database_name go
insert into table_name
values(
实际操作中，这里用到 insert table1
select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;IMEX=1;HDR=YES;DATABASE=C:\\abc.xls' ,[Sheet1$]) 可以快速导入数据 插入实例：
use minhang go
insert into Ticket
values(20,'刘林',13545091010,420202198807101314) go
insert into Users
values(15,'liulin',00099922,'刘林','男',21,13545091010,'liulin@126.com') go
insert into Twork
values(20,'Sell',20,15,3,'WER56',2,588) go
修改实例：
use minhang go
update Flight set Ftim='7H' where Fno='ABX98' go
update Users set Usex=null where Una2='刘林' go
update Users set Uage=null where Uno=15 go
13
删除实例：
use minhang go
delete Users where Uno=15 go
delete Ticket where Tno=20 go
delete Twork where Wno=20 go
查询实例：
/*查询到广州的航班（条件查询）*/ select*from Flight where City2='广州'
图3-3 查询实例1
/*查询到从成都起飞的航班有哪些（条件查询）*/ select*from Flight where City1='成都'
图3-4 查询实例2
/*查询机型为A380的航班（条件查询）*/ select*from Flight where Ftype='A380'
图3-5 查询实例3
/*查询飞行时间大于小时的航班（条件查询）*/ select*from Flight where Ftim>6
14
图3-6 查询实例4
/*查询姓张的会员（条件查询）*/
select Uno,Una1,Una2 from Users where Una2 like '张%'
图3-7 查询实例5
/*查询年龄在岁以上的会员（条件查询）*/
select Uno,Una1,Una2,Uage from Users where Uage>30
图3-8 查询实例6
/*查询年龄姓李并且名字个字的会员（条件查询）*/ select Uno,Una1,Una2 from Users where Una2 like '李__'
图3-9查询实例7
/*查询不姓李的会员的所有信息（条件查询）*/ select*from Users
where Una2 not like '李%'
15