# SQL系列学习 基础数据

2017-10-20 12:02:07来源:cnblogs.com作者:Kier@L人点击

//班主任表

CREATE TABLE [dbo].[teacher](
[id] [int] IDENTITY(1,1) NOT NULL primary key,
[name] [varchar](50) NOT NULL,
[sj] [datetime] default(getdate()) not NULL

)

//课室表

CREATE TABLE [class](
[id] [int] IDENTITY(1,1) NOT NULL primary key,
[teacherid] [int] NOT NULL,
[name] [varchar](50) NOT NULL,
[sj] [datetime] default(getdate()) not NULL

//学员表

CREATE TABLE [dbo].[student](

[id] [int] IDENTITY(1,1) NOT NULL primary,
[classid] [int] NOT NULL,
[name] [varchar](50) NOT NULL,
[sj] [datetime] default(getdate())  not NULL ,

)

//插入班主任表,并获取对应的班主任ID
DECLARE @t1 int ,@t2 int,@t3 int
INSERT INTO teacher(name) VALUES('Teacher Jack')
SELECT @t1=IDENT_CURRENT('teacher')
INSERT INTO teacher(name) VALUES('Teacher Mary')
SELECT @t2=IDENT_CURRENT('teacher')
INSERT INTO teacher(name) VALUES('Teacher Larry')
SELECT @t3=IDENT_CURRENT('teacher')

//插入课室表,并获取对应课室ID
DECLARE @c1 int ,@c2 int,@c3 int
insert into class(teacherid,name) VALUES(@t1,'Photography')
SELECT @c1=IDENT_CURRENT('class')
insert into class(teacherid,name) VALUES(@t2,'Soft')
SELECT @c2=IDENT_CURRENT('class')
insert into class(teacherid,name) VALUES(@t3,'Chinese')
SELECT @c3=IDENT_CURRENT('class')

//根据课室表,插入学生表
INSERT INTO student(classid,name) VALUES(@c1,'Harry')
INSERT INTO student(classid,name) VALUES(@c1,'Kate')
INSERT INTO student(classid,name) VALUES(@c1,'Charlotter')
INSERT INTO student(classid,name) VALUES(@c1,'Catherine')
INSERT INTO student(classid,name) VALUES(@c2,'George')
INSERT INTO student(classid,name) VALUES(@c2,'Joe')
INSERT INTO student(classid,name) VALUES(@c2,'Leo')
INSERT INTO student(classid,name) VALUES(@c2,'Kitty')
INSERT INTO student(classid,name) VALUES(@c3,'May')
INSERT INTO student(classid,name) VALUES(@c3,'River')
INSERT INTO student(classid,name) VALUES(@c3,'Holly')
INSERT INTO student(classid,name) VALUES(@c3,'Eden')
INSERT INTO student(classid,name) VALUES(@c3,'Json')
INSERT INTO student(classid,name) VALUES(@c3,'Alina')