mysql city_LuffyCity-MySQL综合练习50实例

1、请创建如下表，并添加相应约束；

2、自行构造测试数据；

新建数据库

创建表

构造测试数据

#Step1-创建数据库LuffyCity_MySQL;

#CREATE DATABASELuffyCity_MySQL CHARSET utf8;

#Step2-创建数据表;

#1、创建年级表class_grade(因为class表的外键要参考class_grade表的gid字段);CREATE TABLEclass_grade (

gidINT NOT NULL PRIMARY KEYAUTO_INCREMENT,

gnameVARCHAR (12) NOT NULL UNIQUE#年级的名称也是唯一值;指定存储引擎为InnoDB，字符编码为utf8后续不再赘述；

) ENGINE= INNODB CHARSET =utf8;

#2、创建班级表class(因为学生表的外键class_id要参考班级表的cid字段);CREATE TABLEclass (

cidINT NOT NULL PRIMARY KEYAUTO_INCREMENT,

captionVARCHAR (16) NOT NULL UNIQUE,

#班级名称也为唯一值;

grade_idINT NOT NULL,CONSTRAINT fk_class_grade FOREIGN KEY (grade_id) REFERENCES class_grade (gid) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE= INNODB CHARSET =utf8;

#3、学生表student(与class表多对一关系);CREATE TABLEstudent (

sidINT NOT NULL PRIMARY KEYAUTO_INCREMENT,

snameVARCHAR (12) NOT NULL,

gender ENUM ('男', '女') DEFAULT '男' NOT NULL,

class_idINT NOT NULL,CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE= INNODB CHARSET =utf8;

#4、老师表teacher(因为课程表的外键teacher_id要参考老师表的tid字段)；CREATE TABLEteacher (

tidINT NOT NULL PRIMARY KEYAUTO_INCREMENT,

tnameVARCHAR (8) NOT NULL) ENGINE= INNODB CHARSET =utf8;

#5、课程表course(因为成绩表的外键course_id要参考课程表的cid字段);CREATE TABLEcourse (

cidINT NOT NULL PRIMARY KEYAUTO_INCREMENT,

cnameVARCHAR (8) NOT NULL UNIQUE,

#课程名唯一值;

teacher_idINT NOT NULL,CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE= INNODB CHARSET =utf8;

#6、成绩表(因为成绩表的外键course_id要参考课程表的cid字段);CREATE TABLEscore (

sidINT NOT NULL UNIQUEAUTO_INCREMENT,

student_idINT NOT NULL,

course_idINT NOT NULL,

scoreINT NOT NULL,CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student (sid) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course (cid) ON DELETE CASCADE ON UPDATE CASCADE,PRIMARY KEY(student_id, course_id) #多字段联合主键;

) ENGINE= INNODB CHARSET =utf8;

#7、班级任职表teach2cls;CREATE TABLEteach2cls (

tcidINT NOT NULL UNIQUEAUTO_INCREMENT,

tidINT NOT NULL,

cidINT NOT NULL,CONSTRAINT fk_teacher1 FOREIGN KEY (tid) REFERENCES teacher (tid) ON DELETE CASCADE ON UPDATE CASCADE,CONSTRAINT fk_class1 FOREIGN KEY (cid) REFERENCES class (cid) ON DELETE CASCADE ON UPDATE CASCADE,PRIMARY KEY(tid, cid)

) ENGINE= INNODB CHARSET =utf8;

#Step3-插入测试数据；

#年级表；创建6个年级；INSERT INTOclass_grade (gname)VALUES('一年级'),

('二年级'),

('三年级'),

('四年级'),

('五年级'),

('六年级');

#班级表；每个年级指定n个班级；INSERT INTOclass (caption, grade_id)VALUES('一年级1班', 1),

('一年级2班', 1),

('一年级3班', 1),

('二年级1班', 2),

('二年级2班', 2),

('三年级1班', 3),

('四年级1班', 4),

('四年级2班', 4),

('四年级3班', 4),

('四年级4班', 4),

('五年级1班', 5),

('六年级1班', 6),

('六年级2班', 6);

#学生表；INSERT INTOstudent (sname, gender, class_id)VALUES('高志粉', '女', 1),

('李静瓶', '女', 2),

('崔晓昭', '男', 3),

('崔晓姗', '女', 4),

('崔晓思', '女', 5),

('崔青良', '男', 6),

('崔晓磊', '男', 7),

('高志国', '男', 8),

('崔晓岩', '女', 9),

('高晨曦', '女', 10),

('陈浩', '男', 11),

('陈浩茹', '女', 10),

('高若曦', '女', 9),

('武倩倩', '女', 12),

('武若冰', '女', 13);INSERT INTOteacher (tname)VALUES('崔树齐'),

('宋俊泽'),

('孙增良'),

('张传伟'),

('邓琼');INSERT INTOcourse (cname, te