首页 MySql数据库开发课程 MySQL创建关联数据表命令
pay pay

MySQL创建关联数据表命令

日期: 五月 8, 2023, 10:15 a.m.
阅读: 143
作者: Python自学网-村长

摘要: MySQL创建关联数据表命令

'''
1.主表
create table class(id int auto_increment primary key, Class_Name varchar(20) not null, Stu_Num int not null, Is_Fulltime bit not null default 1,Is_Delete bit not null default 0);
2.副表
create table student(id int auto_increment primary key, name varchar(20) not null,age int not null, sex bit not null,classid int not null,Is_Delete bit not null default 0, foreign key(classid) references class(id));

插入一些数据:
insert into class values(0, "python", 39, 1,0),(0, "mysql", 40, 1,0),(0, "java", 27, 1,0);

insert into student values(0, "小明", 19, 1, 3,0),(0, "Tom", 22, 1, 2,0),(0, "wakey", 20, 1, 1,0),(0, "李四", 20, 1, 2,0),(0, "钢铁", 21, 1, 1,0);


select student.name, class.Class_Name, student.age from class inner join student on class.id=student.classid;
inner join是关联的意思,代码意思是从class表中查询学生名和班级名,
但是学生名无法查询,所以关联学生表,on后面的条件是class.id=student.classid

反过来查询
select student.name,class.Class_Name from student inner join class on student.classid=class.id;

题目1:通过学生表查询该学生上的课程是什么,并且是不是全日制的。
select student.name, class.Class_Name, class.Is_Fulltime from student inner join class on student.classid=class.id;
题目2:通过学生表查看所在班级学生人数,
select class.Class_Name, class.Stu_Num from student inner join class on student.classid=class.id;
'''

 

原创视频,版权所有,未经允许,切勿转载,违者必究!
回顶部