1 use learn_db ; 2
3 # 创建主表
4 create table class_teachers( 5 id int primary key auto_increment not null comment '班主任ID',
6 name varchar(20) not null comment '班主任名字' , 7 gender boolean default 0 comment '0:保密,1:男,2:女' , 8 age int comment '班主任年龄'
9
10 ) ; 11 # 主表插入数据 12 insert into class_teachers(name, gender, age) values ('冰冰', 2, 22), 13 ('丹丹', 2, 25), 14 ('歪歪', 1, 27), 15 ('珊珊', 2, 26), 16 ('小雨', 2, 30); 17
18 # 创建子表 19 create table class_students( 20 id int primary key auto_increment not null comment '学生ID', 21 name varchar(20) not null comment '学生姓名' , 22 teacher_id int not null comment '班主任ID'
23
24 ) engine = innodb ; 25
26 # 子表插入数据 27 insert into class_students(name, teacher_id) values ('学生1', 2), 28 ('学生2', 4), 29 ('学生3', 1), 30 ('学生4', 3), 31 ('学生5', 1), 32 ('学生6', 3), 33 ('学生7', 2) ; 34
35 # 查询主表 36 select * from class_teachers ; 37 # 查询子表 38 select * from class_students ; 39
40 # 创建外键(在子表中创建) 41 drop table class_students; 42 show tables ; 43
44 create table class_students( 45 id int primary key auto_increment not null comment '学生ID', 46 name varchar(20) not null comment '学生姓名' , 47 teacher_id int not null comment '班主任ID' , 48 foreign key (teacher_id) references class_teachers(id) 49
50 ) engine = innodb ; 51
52 insert into class_students(name, teacher_id) values ('学生1', 2), 53 ('学生2', 4), 54 ('学生3', 5), 55 ('学生4', 3), 56 ('学生5', 5), 57 ('学生6', 3), 58 ('学生7', 2) ; 59
60 # teacher 61 select * from class_teachers ; 62 # student 63 select * from class_students ; 64
65
66 # 插如外键 67 show create table class_students2 ; 68 alter table class_students2 add constraint teacher_id foreign key (teacher_id) references class_teachers(id) ; 69 show create table class_students2 ; 70 # 删除外键 71 alter table class_students2 drop foreign key teacher_id ; 72 show create table class_students2 ; 73
74 # 连级删除 75 /*
76 foreign key (子表字段) references + 主表(主表字段) on delete cascade 77 -- 删除连级 78 alter table + 表名 drop foreign key + 外键名 79 -- 连接删除后子表对应字段为Null ,子表的外键不能设置not null 80 alter table + 表名 add constraint + 外键名 foreign key (子表字段) references + 主表(主表字段) 81
82 */
83
84 select * from class_students, class_teachers ; 85 select * from class_teachers, class_students ; 86
87 select * from class_teachers, class_students where class_teachers.id=class_students.teacher_id ; 88 select * from class_students,class_teachers where class_students.teacher_id=class_teachers.id ; 89 select class_students.name, class_teachers.name from class_students,class_teachers where class_students.teacher_id=class_teachers.id and class_students.name='学生3';
