mysql主外键设置规则_MySQL-主外键设置

#创建学生表

CREATE TABLE student(

sno VARCHAR(20) NOT NULL COMMENT’学号’,

sname VARCHAR(20) NOT NULL COMMENT’学生姓名’,

ssex VARCHAR(20) NOT NULL COMMENT ‘学生性别’,

sbirthday DATETIME COMMENT’学生出生年月’,

class VARCHAR(20) COMMENT’学生所在班级’,

CONSTRAINT pk_student PRIMARY KEY (sno)

)

#查看表内容

SELECT*FROM student;   //select sno,sname,ssex,sbirthday,class from student;

#创建课程表

CREATE TABLE course(

cno VARCHAR(20) PRIMARY KEY NOT NULL COMMENT ‘课程号’,

cname VARCHAR(20) NOT NULL COMMENT ‘课程名称’,

tno VARCHAR(20) NOT NULL COMMENT ‘教工编号’,

FOREIGN KEY (tno) REFERENCES teacher(tno)

)

#创建成绩表

CREATE TABLE score(

sno VARCHAR(20) NOT NULL COMMENT ‘学号’,

cno VARCHAR(20) NOT NULL COMMENT ‘课程号’,

degree DECIMAL(4,2) COMMENT ‘成绩’,

FOREIGN KEY (sno) REFERENCES student(sno),

FOREIGN KEY (cno) REFERENCES course(cno)

)

#创建教师表

CREATE TABLE teacher(

tno VARCHAR(20) PRIMARY KEY NOT NULL COMMENT ‘教工编号’,

tname VARCHAR(20) NOT NULL COMMENT ‘教师姓名’,

tsex VARCHAR(20) NOT NULL COMMENT ‘教工性别’,

tbirthay DATETIME COMMENT ‘教工出生年月’,

Prof VARCHAR(20) COMMENT ‘职称’,

depart VARCHAR(20) NOT NULL COMMENT ‘教工所在部门’

)

#添加学生信息 (多个输入插入也可以用逗号分隔)

insert into student VALUES(‘108’,‘曾华’,‘男’,‘1977-09-01’,‘95033’);

insert into student VALUES(‘105’,‘匡明’,‘男’,‘1975-10-02’,‘95031’);

insert into student VALUES(‘107’,‘王丽’,‘女’,‘1976-01-23’,‘95033’);

insert into student VALUES(‘101’,‘李军’,‘男’,‘1976-02-20’,‘95033’);

insert into student VALUES(‘109’,‘王芳’,‘女’,‘1975-02-10’,‘95031’);

insert into student VALUES(‘103’,‘陆君’,‘男’,‘1974-06-03’,‘95031’);

s#添加教师表

insert into student teacher VALUES(‘804’,‘李诚’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’);

insert into student teacher VALUES(‘856’,‘张旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’);

insert into student teacher VALUES(‘825’,‘王萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’);

insert into student teacher VALUES(‘831’,‘刘冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’);

#添加课程表

INSERT INTO course VALUES(‘3-105’,‘计算机导论’,‘825’);

INSERT INTO course VALUES(‘3-245’,‘计算机导论’,‘804’);

INSERT INTO course VALUES(‘6-166’,‘数字电路’,‘856’);

INSERT INTO course VALUES(‘9-888’,‘高等数学’,‘831’);

#添加成绩表

INSERT INTO score VALUES(‘103’,‘3-245’,‘86’);

INSERT INTO score VALUES(‘105’,‘3-245’,‘75’);

INSERT INTO score VALUES(‘109’,‘3-245’,‘68’);

INSERT INTO score VALUES(‘103’,‘3-105’,‘92’);

INSERT INTO score VALUES(‘105’,‘3-105’,‘88’);

INSERT INTO score VALUES(‘109’,‘3-105’,‘76’);

INSERT INTO score VALUES(‘102’,‘3-105’,‘64’);

INSERT INTO score VALUES(‘105’,‘3-105’,‘91’);

INSERT INTO score VALUES(‘109’,‘3-105’,‘78’);

INSERT INTO score VALUES(‘103’,‘6-166’,‘85’);

INSERT INTO score VALUES(‘105’,‘6-166’,‘79’);

INSERT INTO score VALUES(‘109’,‘6-166’,‘81’);