数据库学习记录，（数据准备）

 

工具：notepad++

数据表示例

学生表 student{学号，姓名，性别，出生年月日，所在班级}

 

课程表course{客场号，课程名称，教师编号}

成绩表score{学号，课程号，成绩}

教师表teacher{教师编号，教师名字，教师性别，出生年月日，职称，所在部门}

CREATE TABLE student(
    sno VARCHAR(20) PRIMARY key,
    sname VARCHAR(20) not NULL,
    ssex VARCHAR(10) not NULL,
    sbirthday datetime,
    class varchar(20)
);

CREATE TABLE teacher(
    tno VARCHAR(20) PRIMARY key,
    tname VARCHAR(20) not NULL,
    tsex VARCHAR(10) not NULL,
    tbirthday datetime,
    prof VARCHAR(20),
    depart VARCHAR(20) NOT NULL
    
);

#将课程的教师号设置为外键与教师表关联

CREATE TABLE course(
    cno VARCHAR(20) PRIMARY key,
    cname VARCHAR(20) NOT NULL,
    tno VARCHAR(20) NOT NULL,
    FOREIGN key(tno) REFERENCES teacher(tno)
);

#score表设置外键sno和cno,学号和课程号与学生表，课程表关联，再设置将课程号和学号设置为联合主键，防止数据重复

CREATE TABLE score(
    sno VARCHAR(20) not NULL,
    cno VARCHAR(20) not NULL,
    dgree DOUBLE,
    FOREIGN key(sno) REFERENCES student(sno),
    FOREIGN key (cno) REFERENCES course(cno),
    PRIMARY key(sno,cno)
);
#添加学生表
INSERT INTO student VALUES('101','张一','男','1999-10-1','95033');
INSERT INTO student VALUES('102','张二','男','1999-10-2','95033');
INSERT INTO student VALUES('103','张三','女','1999-10-3','95031');
INSERT INTO student VALUES('104','张四','女','1999-10-4','95031');
INSERT INTO student VALUES('105','张五','男','1999-10-5','95032');
INSERT INTO student VALUES('106','张六','女','1999-10-7','95032');
INSERT INTO student VALUES('107','张七','男','1999-10-7','95034');
INSERT INTO student VALUES('108','张八','男','1999-10-8','95034');

INSERT INTO teacher VALUES('801','李一','男','1960-3-1','副教授','计算机系');
INSERT INTO teacher VALUES('802','李二','女','1960-3-2','讲师','计算机系');
INSERT INTO teacher VALUES('803','李三','女','1960-3-3','教授','电子信息');
INSERT INTO teacher VALUES('804','李四','女','1960-3-4','助教','电子信息');

INSERT INTO course VALUES('3-101','C++','801');
INSERT INTO course VALUES('3-102','JAVA','802');
INSERT INTO course VALUES('3-103','PYTHON','803');
INSERT INTO course VALUES('3-104','PHP','804');

INSERT INTO score VALUES('101','3-101','86');
INSERT INTO score VALUES('101','3-102','75');
INSERT INTO score VALUES('101','3-103','88');
INSERT INTO score VALUES('102','3-104','90');
INSERT INTO score VALUES('102','3-102','65');
INSERT INTO score VALUES('102','3-103','76');
INSERT INTO score VALUES('103','3-101','66');
INSERT INTO score VALUES('103','3-104','88');
INSERT INTO score VALUES('103','3-103','99');
INSERT INTO score VALUES('104','3-104','63');
INSERT INTO score VALUES('104','3-103','87');
INSERT INTO score VALUES('104','3-101','79');
INSERT INTO score VALUES('105','3-101','79');
INSERT INTO score VALUES('105','3-102','76');
INSERT INTO score VALUES('105','3-103','99');
INSERT INTO score VALUES('106','3-104','90');
INSERT INTO score VALUES('107','3-104','76');
INSERT INTO score VALUES('108','3-103','88');