创建学生表、课程表、 成绩表 、教师表

[sql]代码库

/*
使用的是postgresql数据库

Navicat PGSQL Data Transfer

Source Server         : pgsql
Source Server Version : 90502
Source Host           : localhost:5432
Source Database       : test1
Source Schema         : public

Target Server Type    : PGSQL
Target Server Version : 90502
File Encoding         : 65001

Date: 2017-04-01 13:56:59
*/


-- ----------------------------
-- Sequence structure for course_id_seq
-- ----------------------------
DROP SEQUENCE "public"."course_id_seq";
CREATE SEQUENCE "public"."course_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 1
 CACHE 1;
SELECT setval('"public"."course_id_seq"', 1, true);

-- ----------------------------
-- Sequence structure for student_id_seq
-- ----------------------------
DROP SEQUENCE "public"."student_id_seq";
CREATE SEQUENCE "public"."student_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 1
 CACHE 1;
SELECT setval('"public"."student_id_seq"', 1, true);

-- ----------------------------
-- Sequence structure for student_score_id_seq
-- ----------------------------
DROP SEQUENCE "public"."student_score_id_seq";
CREATE SEQUENCE "public"."student_score_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 21
 CACHE 1;
SELECT setval('"public"."student_score_id_seq"', 21, true);

-- ----------------------------
-- Sequence structure for teacher_id_seq
-- ----------------------------
DROP SEQUENCE "public"."teacher_id_seq";
CREATE SEQUENCE "public"."teacher_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 1
 CACHE 1;
SELECT setval('"public"."teacher_id_seq"', 1, true);

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS "public"."course";
CREATE TABLE "public"."course" (
"course_id" int4 DEFAULT nextval('course_id_seq'::regclass) NOT NULL,
"course_name" varchar(60) COLLATE "default",
"teacher_id" int4
)
WITH (OIDS=FALSE)

;
COMMENT ON COLUMN "public"."course"."course_id" IS '课程编号';
COMMENT ON COLUMN "public"."course"."course_name" IS '课程名称';
COMMENT ON COLUMN "public"."course"."teacher_id" IS '教师id';

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO "public"."course" VALUES ('1', '语文', '1');
INSERT INTO "public"."course" VALUES ('2', '数学', '2');
INSERT INTO "public"."course" VALUES ('3', '英语', '3');
INSERT INTO "public"."course" VALUES ('4', '物理', '4');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS "public"."student";
CREATE TABLE "public"."student" (
"student_id" int4 DEFAULT nextval('student_id_seq'::regclass) NOT NULL,
"student_name" varchar(60) COLLATE "default",
"student_sex" varchar(60) COLLATE "default",
"student_age" int4 NOT NULL
)
WITH (OIDS=FALSE)

;
COMMENT ON COLUMN "public"."student"."student_id" IS '学生编号';
COMMENT ON COLUMN "public"."student"."student_name" IS '学生姓名';
COMMENT ON COLUMN "public"."student"."student_sex" IS '学生性别';
COMMENT ON COLUMN "public"."student"."student_age" IS '学生年龄';

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO "public"."student" VALUES ('1', '刘一', '男', '18');
INSERT INTO "public"."student" VALUES ('2', '钱二', '女', '19');
INSERT INTO "public"."student" VALUES ('3', '张三', '男', '17');
INSERT INTO "public"."student" VALUES ('4', '李四', '女', '18');
INSERT INTO "public"."student" VALUES ('5', '王五', '男', '17');
INSERT INTO "public"."student" VALUES ('6', '赵六', '女', '19');

-- ----------------------------
-- Table structure for student_score
-- ----------------------------
DROP TABLE IF EXISTS "public"."student_score";
CREATE TABLE "public"."student_score" (
"student_score_id" int4 DEFAULT nextval('student_score_id_seq'::regclass) NOT NULL,
"student_id" int4,
"course_id" int4,
"score" int4
)
WITH (OIDS=FALSE)

;
COMMENT ON COLUMN "public"."student_score"."student_score_id" IS '学生成绩编号';
COMMENT ON COLUMN "public"."student_score"."student_id" IS '学生id';
COMMENT ON COLUMN "public"."student_score"."course_id" IS '课程id';
COMMENT ON COLUMN "public"."student_score"."score" IS '分数';

-- ----------------------------
-- Records of student_score
-- ----------------------------
INSERT INTO "public"."student_score" VALUES ('1', '1', '1', '56');
INSERT INTO "public"."student_score" VALUES ('2', '1', '2', '78');
INSERT INTO "public"."student_score" VALUES ('3', '1', '3', '67');
INSERT INTO "public"."student_score" VALUES ('4', '1', '4', '58');
INSERT INTO "public"."student_score" VALUES ('5', '2', '1', '79');
INSERT INTO "public"."student_score" VALUES ('6', '2', '2', '81');
INSERT INTO "public"."student_score" VALUES ('7', '2', '3', '92');
INSERT INTO "public"."student_score" VALUES ('8', '2', '4', '68');
INSERT INTO "public"."student_score" VALUES ('9', '3', '1', '91');
INSERT INTO "public"."student_score" VALUES ('10', '3', '2', '47');
INSERT INTO "public"."student_score" VALUES ('11', '3', '3', '88');
INSERT INTO "public"."student_score" VALUES ('12', '3', '4', '56');
INSERT INTO "public"."student_score" VALUES ('13', '4', '2', '88');
INSERT INTO "public"."student_score" VALUES ('14', '4', '3', '90');
INSERT INTO "public"."student_score" VALUES ('15', '4', '4', '93');
INSERT INTO "public"."student_score" VALUES ('16', '5', '1', '46');
INSERT INTO "public"."student_score" VALUES ('17', '5', '3', '78');
INSERT INTO "public"."student_score" VALUES ('18', '5', '4', '53');
INSERT INTO "public"."student_score" VALUES ('19', '6', '1', '35');
INSERT INTO "public"."student_score" VALUES ('20', '6', '2', '68');
INSERT INTO "public"."student_score" VALUES ('21', '6', '4', '71');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS "public"."teacher";
CREATE TABLE "public"."teacher" (
"teacher_id" int4 DEFAULT nextval('teacher_id_seq'::regclass) NOT NULL,
"teacher_name" varchar(60) COLLATE "default"
)
WITH (OIDS=FALSE)

;
COMMENT ON COLUMN "public"."teacher"."teacher_id" IS '教师编号';
COMMENT ON COLUMN "public"."teacher"."teacher_name" IS '教师姓名';

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO "public"."teacher" VALUES ('1', '叶平');
INSERT INTO "public"."teacher" VALUES ('2', '贺高');
INSERT INTO "public"."teacher" VALUES ('3', '杨艳');
INSERT INTO "public"."teacher" VALUES ('4', '周磊');

-- ----------------------------
-- Alter Sequences Owned By 
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table student
-- ----------------------------
ALTER TABLE "public"."student" ADD PRIMARY KEY ("student_id");

-- ----------------------------
-- Primary Key structure for table student_score
-- ----------------------------
ALTER TABLE "public"."student_score" ADD PRIMARY KEY ("student_score_id");

-- ----------------------------
-- Primary Key structure for table teacher
-- ----------------------------
ALTER TABLE "public"."teacher" ADD PRIMARY KEY ("teacher_id");