Download
1 / 7
70 likes | 209 Views
一阶段： About SQL. SQL has 3 struct models ,inner model( includes Stored file ) ,outer model(includes view) and model(includes base table).
E N D
一阶段：About SQL • SQL has 3 struct models ,inner model( includes Stored file ) ,outer model(includes view) and model(includes base table). • SQL有3个模式，内模式（存储文件），外模式（包括视图）和模式（包括基本表）base table existed in the database .A base table have a Stored file.but a base table may has many indexs which also stored in the Stored file. • 一个表只对应一个存储文件，但可以对应多个索引。索引也存在存储文件中。 • However view isn’t existed in the database .it is derived from the base table .so ,we can say it is just a invisival table. • 视图不独立存在于数据库中，是从基本表导出来的，可以说它是一个虚表。
第三阶段：举例子 This is the view If you had created a view，And if you want to select informations from the tables ,you can easily get the information throught the views. 如果你已经定义了一个视图，你可以通过视图很容易的得到你想查询的信息，如右图： SELECT * FROM cardInfo WHERE (DATEDIFF(Day,getDate(),openDate)<DATEPART(weekday,openDate)) SELECT DISTINCT cardID FROM transInfo WHERE transMoney=(SELECT Max(transMoney) FROM transInfo) SELECT customerName as 客户姓名,telephone as 联系电话 FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1) SELECT,.., FROM userInfo INNER JOIN cardInfo ON userInfo.customerID =cardInfo.customerID WHERE balance<200
第四阶段:展示如何建立课本第82页中的3个表 一 建Student表 二 增加表主键约束PK_Sno Create table [dbo].[Student] [Sno] [nchar] (20) not null, [Sname] [nchar] (20) not null, [Ssex] [nchar] (20) not null, [Sage] [int] (20) not null, [Sdept] [nvarchar] (20) not null, Alter table [dbo].[Student] Add Constraint PK_Sno Pramiry key (Sno)
一 建Course表 二 增加表主键约束PK_Sno Create table [dbo].[Course] [Cno] [nchar] (20) not null, [Cname] [nchar] (20) not null, [Cpno] [nchar] (20) not null, [Ccredit] [int] (20) not null, Alter table [dbo].[Student] Add Constraint PK_Sno Pramiry key (Cno)
二 增加表外键约束 一 建SC表 Alter table [dbo].[Student] Add Constraint FK_Cno Foreign key (Cno) References Course（ Cno ）Add Constraint FK_SnoForeign key (Sno)References Student(Sno) Create table [dbo].[SC] [Cno] [nchar] (20) not null, [Sno] [nchar] (20) not null, [Grade] [int] (20) not null,
