--
--20.2.1节示例
--
创建班级和学生表
CREATE TABLE Class --创建测试表
(
CID INT IDENTITY PRIMARY KEY,
CName VARCHAR(10) NOT NULL
)
GO
CREATE TABLE Student
(
SID INT IDENTITY PRIMARY KEY,
CID INT NOT NULL ,
SName VARCHAR(10) NOT NULL,
CONSTRAINT FK_Student_Class FOREIGN KEY(CID) REFERENCES Class(CID)
)
--插入测试数据
INSERT INTO Class VALUES(1,'01')
INSERT INTO Class VALUES(2,’02')
INSERT INTO Student (CID,SName) VALUES(1,'s11')
INSERT INTO Student (CID,SName) VALUES(1,'s12')
INSERT INTO Student (CID,SName) VALUES(2,'s21')
INSERT INTO Student (CID,SName) VALUES(2,'s22')
INSERT INTO Student (CID,SName) VALUES(2,'s23')
使用内联接查询2表
SELECT *
FROM Student s
INNER JOIN Class c --内联接
ON s.CID=c.CID
--
--20.2.2节示例
--
为班级表和学生表添加数据
SET NOCOUNT ON
--创建大量的测试数据
DECLARE @i INT=3
WHILE @i<1000
BEGIN
INSERT INTO Class VALUES(@i,'01')
DECLARE @j int=0
WHILE @j<10
BEGIN
INSERT INTO Student (CID,SName) VALUES(@i,'s'+CONVERT(VARCHAR(5),@i))
SET @j+=1
END
SET @i+=1
END
GO
CREATE INDEX IX_Student_CID --创建索引
ON Student(CID) include(SID,SName)
--
--20.2.3节示例
--
去掉排序然后联接查询
DROP INDEX IX_Student_CID ON Student --去掉索引
GO
SELECT *
FROM Student s
INNER JOIN Class c
ON s.CID=c.CID
