SQL Server下建表
建立部门表
CREATE TABLE departments(
departID INT PRIMARY KEY,
department VARCHAR(40) NOT NULL
)
建立专业表
CREATE TABLE majors(
majorID INT PRIMARY KEY,
major VARCHAR(40) NOT NULL,
departID INT NOT NULL REFERENCES departments(departID)
)
注意专业表有个外键指向部门表的departid
建立学生表
CREATE TABLE students(
studID INT PRIMARY KEY,
name VARCHAR(10) NOT NULL,
gender VARCHAR(4),
departID INT REFERENCES departments(departID),
majorID INT REFERENCES majors(majorID)
)
注意学生表有个外键指向部门表的departid，还有个外键指向专业表的majorid
插入数据
INSERT INTO departments VALUES(10, '泰山派')
INSERT INTO departments VALUES(20, '衡山派')
INSERT INTO departments VALUES(30, '华山派')
INSERT INTO departments VALUES(40, '嵩山派')
INSERT INTO departments VALUES(50, '恒山派')
INSERT INTO departments VALUES(60, '日月神教')
INSERT INTO departments VALUES(70, '少林派')
INSERT INTO departments VALUES(80, '武当派')
INSERT INTO majors VALUES(3001, '华山剑法', 30)
INSERT INTO majors VALUES(3002, '紫霞神功', 30)
INSERT INTO majors VALUES(3003, '独孤九剑', 30)
INSERT INTO majors VALUES(5001, '恒山剑法', 50)
INSERT INTO majors VALUES(6001, '吸星大法', 60)
INSERT INTO majors VALUES(7001, '少林七十二绝技', 70)
INSERT INTO majors VALUES(7002, '易筋经', 70)
INSERT INTO majors VALUES(8001, '太极剑法', 80)
INSERT INTO students VALUES(3000001,'令狐冲','男', 30, 3003)
INSERT INTO students VALUES(3000002,'岳不群','男', 30, 3002)
INSERT INTO students VALUES(3000003,'岳灵珊','女', 30, 3001)
INSERT INTO students VALUES(5000001,'仪琳','女', 50, 5001)
INSERT INTO students VALUES(6000001,'任我行','男', 60, 6001)
INSERT INTO students VALUES(7000001,'方证','男', 70, 7002)
INSERT INTO students VALUES(8000001,'冲虚','男', 80, 8001)
单表数据
添加描述
添加描述
添加描述
联合查询
添加描述
我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！
