SQL系列学习 基础数据

//班主任表

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')

 

 

本篇作为基础数据，设想是作为一个学习系列，近期也习惯建立基础数据临时表作为测试数据，即时删除，很方便。可以在表名前面加上#，末尾删除即可；

主要还是记录自己学习的过程，这些基础的知识相信园内已经有很多分享，参考了各位的资料，再作为自学的记录而已，如有差错，请指正