系别,学生,班级表 建表及数据

建表

-- 8.1
-- 建表
DROP TABLE IF EXISTS class ;
CREATE TABLE class_(
	classid INT PRIMARY KEY,
	subject VARCHAR(32),
	deptname VARCHAR(32),
	enrolltime INT,
	num INT,
-- 	添加的外键必须是该表的主键
	FOREIGN KEY(deptname) REFERENCES department(deptname)
)

CREATE TABLE student_(
	studentid INT PRIMARY KEY,
	name VARCHAR(32) NOT NULL,
	age SMALLINT CHECK(age>0 AND age<150),
	classid INT,
	FOREIGN KEY(classid) references class_(classid)
)
-- 懒得加id了,设置一个自增长
ALTER TABLE student_
MODIFY studentid INT auto_increment


CREATE TABLE department(
	departmentid INT,
	deptname VARCHAR(32)
)

-- 给表添加主键
ALTER TABLE department
	ADD PRIMARY KEY(deptname)

插入数据

-- 8.2
-- 插入数据
INSERT INTO department VALUES(001,'数学');
INSERT INTO department VALUES(002,'计算机');
INSERT INTO department VALUES(003,'化学');
INSERT INTO department VALUES(004,'中文');
INSERT INTO department VALUES(005,'经济');


INSERT INTO class_ VALUES(101,'软件','计算机',1995,20);
INSERT INTO class_ VALUES(102,'微电子','计算机',1995,20);
INSERT INTO class_ VALUES(111,'无机化学','化学',1995,29);
INSERT INTO class_ VALUES(112,'高分子化学','化学',1996,25);
INSERT INTO class_ VALUES(121,'统计数学','数学',1995,20);
INSERT INTO class_ VALUES(131,'现代语言','中文',1997,20);
INSERT INTO class_ VALUES(141,'国际贸易','经济',1998,40);
INSERT INTO class_ VALUES(142,'国际金融','经济',1995,14);

INSERT INTO student_ VALUES(8101,'张3',18,101);
INSERT INTO student_(name,age,classid) VALUES('张4',12,121);
INSERT INTO student_(name,age,classid) VALUES('张5',13,102);
INSERT INTO student_(name,age,classid) VALUES('张6',22,141);
INSERT INTO student_(name,age,classid) VALUES('张7',55,142);
INSERT INTO student_(name,age,classid) VALUES('张8',18,111);
INSERT INTO student_(name,age,classid) VALUES('张9',33,112);
INSERT INTO student_(name,age,classid) VALUES('张10',44,111);
INSERT INTO student_(name,age,classid) VALUES('张11',18,111);
INSERT INTO student_(name,age,classid) VALUES('张12',22,102);
INSERT INTO student_(name,age,classid) VALUES('张13',15,142);
INSERT INTO student_(name,age,classid) VALUES('李10',44,111);
INSERT INTO student_(name,age,classid) VALUES('李11',18,111);